Database - Creating

From TNG Wiki
Jump to: navigation, search

TNG comes with detailed installation instructions for installing the software. Those instructions apply to virtually any site that TNG is being installed on. But different techniques are required for creating databases on different hosts.

The TNG software install readme.html might be able to create your database if the user name you are logged in with has the authoridy to do so. If you only get back Please wait, attempting to process... and it just appears to hang, you may not have authority to create the database if it does not exist. The following should help you create the required database that you specify in the readme.html

The general procedure is

  1. Use your web browser to go to your Hosting Service Provider's administration panel
  2. Select an option for managing databases
  3. Make sure you are working with MySQL, version 5 (Assuming that you are installing TNGv10 or above.)
  4. Create a database and assign it a name.
  5. Define an Encoding and a Collation (These terms are described below)
  6. Define a database user. See Database User for more information, or associate an existing user to the newly define database.

You will probably have to invoke a different web-based application, such as phpMyAdmin for some of these steps. Most hosting packages come with phpMyAdmin, though others may provide similar tools such as MySQLDumper. And you will almost never use a hosting wizard or a program like phpMyAdmin to create your database tables, since the tables are created during the TNG software installation process. (See Installing TNG)

At least one hosting provider that is used for a number of TNG sites - Simply Hosting -offers TNG hosting as a specific hosting package, and will set up your database and install TNG for you. If you use such a service, you don't really need the step-by-step instructions in this article, but you should understand the database terminology and at least be able to help the hosting provider identify the database character encoding and collation sequence that is best for your audience.

Database Terminology

  • TNG uses "Relational Database" technology, which organizes databases into "Tables" (aka "Record Types"), which are composed of "Rows" (aka "Records") and "Columns" (aka "Fields" or "Attributes"). To administer TNG effectively, you'll need to have reasonable awareness of TNG's database tables, and the notion of "records" in those tables. But, unless you get into programming or more detailed database maintenance, you don't really have to worry about database fields (which are related to the "fields" you see in web page forms, but aren't quite the same thing.)
  • Though there are many relational database products, and, in theory, TNG could be adapted to to many of them, TNG is tied quite closely to a free database product called "MySQL".
  • Sometimes - even on its home page, MySQL is mistakenly referred to as a "Database", rather than as "database software" or a "Database Management System". I can't really explain why.
  • On your database server, you must have access to a "Database Instance". A "Database Instance" may actually contain more than one database; even more than one TNG database. Your Database Instance is essentially defined by a host name. On a web-based server, your ISP will tell you what your database host name is. On a PC-based server, your host name is almost always "localhost".
  • A "Database" is a named collection of tables. Most of the time, separate applications have separate databases, but it is entirely possible for two applications such as, say, TNG and MediaWiki to share a database as long as none of their table names overlap. (Or perhaps more accurately, "as long as they don't have conflicting database defintions".) It might be reasonable for two applications to share a database because they actually do share some tables.
  • You should have some control over the name of your database. You hosting provider may impose a name prefix, but probably won't create the database name for you.
  • When separate applications such as TNG and MediaWiki share the same database, it common for a person to refer to the "TNG database", and the "MediaWiki database" as two separate things. But in such a case, the "TNG database" is a really just a logical set of tables within the actual database, and the "MediaWiki database" is another logical set of tables in the actual database. The database itself doesn't know that it contains tables for two different applications. It just knows that it has a name, and that it contains the tables that it has been told about.

Character Sets & Collations

To handle many different languages, a variety of letters in different languages, and different sorting rules for different situations, you must define

  • A Character Set Encoding (a way of translating characters to binary numbers), and
  • A Collation Sequence (sorting rules), or just "Collation"

for your database. See Database Collation - Explain Choosing

Though it is not MySQL's default encoding, UTF-8 is the most flexible encoding to use in your database. Some factors listed below may drive you to use "latin1" rather than UTF-8.

And most TNG databases can use the collation utf8_swedish_ci. (The utf8 prefix is, not surprisingly, associated with UTF-8 as the encoding. The ci suffix stands for "Case-Insensitive". It's generally best to run TNG with case-insensitive collating.) Again, see Database Collation - Explain Choosing for more information about other collating sequences.

Among the considerations you may need in order to decide what Character Set and Collation you need are:

Desktop genealogy program

If you have a desktop genealogy program from which you will export data to be loaded into TNG, that programs's encoding capability may drive your database encoding. You will want to make sure that your desktop genealogy program supports UTF-8 in its Gedcom exports. The most current versions of most desktop genealogy programs do support UTF-8, but not all do.

ASCII Editor

To managing a TNG site, you will (at least) occasionally need to edit text files, and you want to make sure that your editor can save files as UTF-8 without a BOM (byte-order-mark), and ideally convert files from ANSI character encoding to UTF-8. TextWrangler on the Macintosh and Notepad++ on Windows PC's can both support UTF-8 encoding without BOM.

Accented characters

Will you be using languages or names from languages that use accented characters?

While it is possible to display accented characters in ANSI or ISO-8859-1, provided your database is using latin1, and your gedcom is exported as ANSI, it would be better at the outset to use UTF-8 if this is possible, especially since PHP 5.6 and above force UTF-8 for display.

phpMyAdmin

phpMyAdmin is a free and open-source web browser application for administering, querying, and updating MySQL databases. Only one of the procedures below depends on phpMyAdmin throughout, but most of them turn to phpMyAdmin at one point or another. Here is the initial phpMyAdmin screen:

Database-creating-phypmyadmin1.png

Note that the left panel lists several databases, including some "internal" databases that may not be visible on all sites. But there are three application databases, including two for different versions of TNG. (Note that TNG needs two database, but the particular hosting environment on which this screen shot was taken is a testing environment that has two different TNG sites in order to test and run two different versions of TNG.)

In the larger panel on the right (with several sub-panels), you see the "General Setting" panel where a collation has been selected. That collation will be used for databases that you create with phpMyAdmin. The various database-creation wizards on different web hosts may or may not use that collation.

You can also see in the MySQL subpanel that this screenshot was taken on a server running version 5.5.16-log of MySQL, and that the Server hostname is "localhost".

Collation Sequence

If you need to review and/or update the database collation sequence, you must select your database from the list on the left side of the page (where a database named tng10.1 is circled above), and then select the Operations tab (which is only visible once you've selected a database). You'll then see this screen:

Database-creating-phypmyadmin-operations.png

where the left-hand panel now shows a list of the tables in the database named tng10.1, and the collation sequence for that database is outlined in red. Note that "latin_swedish_ci" is not the same as "utf8-swedish-ci".

If you need help selecting a TNG collation, please read Selecting your TNG Database Collation.

Database Users

When specifying the Database username in the TNG readme.html for the full install, the database username must have authority to access the database and the necessarily privileges to create, update, and delete tables and their attributes, for the Information saved, connection verified! message to be returned.

If you only get back Please wait, attempting to process... and it just appears to hang, you may not have authority to create the database if it does not exist, or the username specified is not associated with the databasewith the appropriate authority.

On some hosts, you cannot use phpMyAdmin to create or edit users; in fact, on some, you cannot even create users - one user is created for you when you define the database, and that's the only user you may have. You might be able to user your hosting service MySQL Database in their cPanel to create the needed database username or associate a previously created username to the new database after it is created.

Also, on some installations, some users are created independently of databases, and given rights to all databases, and on other installations, the users are created after a database has been selected, and have rights only to that database

But considering installations where you have some control over users, click the Privileges tab (which is circled in the phpMyAdmin home page screenshot above). You'll then see this screen (which focuses on the right panel):

Database-creating-phpmyadmin-privileges-nodb.png

Note that, in this tng10.1 database, there is a "root" user (which is special user that, on some servers, is created by default, and has all possible privileges), a user named "superuser", and a user named "tngadmin", which is the user to be used by TNG.

The "root" user is often created without a password, so you should click on the Edit Privileges link next to the root user to add a password if necessary. Note that is it necessary for the database username to have a password in several environments, for example Joomla and MediaWiki. (You may have to log in again with that password the next time you run phpMyAdmin.)

Create a User

  1. Select a database in the left-hand panel, and
  2. Click on the Privileges tab again (if you can).
    At that point, you'll see a user list similar to the screenshot above, but focused on users with rights to the database you selected.
  3. Then click on the Add a new User link. You'll see this panel:
  • Enter a user name. Don't use the username that your ISP gave to log into the server administrative interface.
  • The host you are specifying here is the identity of computers that are allowed to access the database. So you usually want this value to be localhost so that arbitrary computers out on the web cannot reach the database. In installations where the database is on a different server from the web site, this field will need to contain the name of the web server. But on those sorts of installations, you probably won't have rights to create users.
  • Enter a secure password.
  • Select the options to Grant all privileges on the database you selected. Note that the phrase "Grant all privileges" is poorly worded. It doesn't "give all possible privileges to this user", it means "Select some privileges below, and then assign them to this user for this database only"
  • You can go ahead and Check All Global Privileges, but then it is probably wise to uncheck Grant, Replication Client, Replication Slave, and Create User in the Administration group

Then click on the Go button to save the user.

Database-creating-phpmyadmin-adduser.png

Change a User

Click on the Edit Privileges link next to the user you want to change. A pop-up window will appear that is very similar to the Add A New User panel shown just above The vertical scrolling of this window might be a little confusing.

You cannot change the user name, but you can scroll down past the Global Privileges to change the user's password.

The Global privileges are the same ones you see for Adding A New User, and as with a new user, it is probably wise to uncheck Grant, Replication Client, Replication Slave, and Create User" in the Administration group

Creating your database

Here are some instructions for creating a database in different hosting environments:

Simply Hosting

From the Simply Hosting administrative interface, you can select the MySQL Database Wizard to create the database. Enter the database name you want to use and click the Create Database button.

Create Database Wizard

Because you cannot specify the collation sequence in the wizard, you need to confirm and possibly change the collation sequence, using the phpMyAdmin procedure illustrated above.

ICDSoft

On ICDSoft, in the MySQL Database Manager screen, you can specify both the database name and collation sequence:

  1. Enter the MySQL Databases section
  2. Scroll down to the Create new database section
  3. Enter a database name (Note that on ICDSoft your account name is prepended)
  4. Select the collation you want

ICDSoft create db upd.png

After the database is created, you should then

  1. Create a new MySQL user and/or
  2. Associate the new database with an existing MySQL user

that TNG will use to connect to your database so that you do not you use your account username to connect

ICDSoft create associate user.png

GoDaddy

(See GoDaddy's Help Page for creating a database])

  1. Log in to your Account Manager
  2. Click Web Hosting
  3. Next to the hosting account you want to use, click Manage
  4. In the Databases section of the Hosting Control Panel, click the icon for MySQL.
  5. Click Add
  6. Complete the fields to specify
    • A description of your database
    • Your database name,
    • A username and password, which TNG will then use to access the database
    • Your PHP version
  7. Click OK to create the database (which defaults to the UTF-8 character encoding)
  8. Then, click the Edit icon next to the name of the database you just created to make sure that the database has the collation sequence you want.

Local WampServer

To create a new TNG database using phpMyAdmin in a WampServer environment:

  1. Launch phpMyAdmin from the WampServer icon in the system tray
  2. Select the "Databases" tab along the top of the right-hand panel, which is circled on the phpMyAdmin home page screen shot included earlier in this article.
  3. Enter your desired database name in the Create new database field
  4. Select your desired collation sequence from the pull-down list.
  5. Click the Create button

phpMyAdmin will create a database, and existing users who have rights to all databases will have rights to the new database. If you need a database user, follow the instruction above under the phpMyAdmin heading

Related Links