Difference between revisions of "Database - Creating"
(updated TNG Commmunity Forum links) |
Robinrichm (talk | contribs) (major update) |
||
Line 1: | Line 1: | ||
− | |||
− | |||
− | |||
{{Languages}} | {{Languages}} | ||
− | |||
{{TNGver | {{TNGver | ||
|notes= The Creating Database procedure is for creating a brand new database.<br /> | |notes= The Creating Database procedure is for creating a brand new database.<br /> | ||
Line 9: | Line 5: | ||
<br />Note that while the database might show as UTF-8, you need to check the collation sequence which is what determine whether the tables are defined as UTF-8 or latin1. Most host are still defaulting to using latin1. | <br />Note that while the database might show as UTF-8, you need to check the collation sequence which is what determine whether the tables are defined as UTF-8 or latin1. Most host are still defaulting to using latin1. | ||
}} | }} | ||
+ | {|align=right | ||
+ | |__TOC__ | ||
+ | |} | ||
+ | The TNG software comes with detailed installation instructions in a readme.html file. Those instructions apply to virtually any site that TNG is being installed on. But different techniques are required for creating databases on different hosts. | ||
− | + | On a localhost [[WampServer]] installation, you may be able to use the TNG readme.html to create the database, but most hosting services require that you create your database before you install TNG. | |
− | + | TNG now requires that you use MySQL 5. Because MySQL 5 uses utf8 definitions for its fields you may want to consider creating the database as UTF-8 from the start and avoid a future conversion. However, you should consider whether you have all the tools you need to maintain and support your data in UTF-8 encoding. | |
You should also create a database user for your new database, so that you do not use your web hosting account username and password for connecting to the database as an additional security measure. See [[Database User]] | You should also create a database user for your new database, so that you do not use your web hosting account username and password for connecting to the database as an additional security measure. See [[Database User]] | ||
− | == | + | == Just what is a "Database" == |
+ | Beware of the many different meanings for the term "Database" and the phrase "Creating a Database" | ||
+ | * Sometimes, SQL Server is called a "Database" as opposed to the more correct "Database Management System" or "Database Management System Product". | ||
+ | * On your database server, you must have access to a "Database Instance" (which is sometimes just called a "Database"). 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 (such as WAMPServer), your host name is almost always "localhost". | ||
+ | * A "Database" is a named collection of database tables. Most of the time, separate applications have separate databases, but it is entirely possible for, say TNG and, say MediaWiki to share a database as long as none of their table names overlap. You can call you TNG database whatever you want. "TNG" is often a good choice. | ||
+ | * 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 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 tables. | ||
+ | |||
+ | == 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 and | ||
+ | * A Collation (sorting rules) | ||
+ | for your database. See [[Database Collation - Explain Choosing]] | ||
+ | |||
+ | Though it is not the default MySql encoding, UTF-8 is the most flexible encoding to use in your database. Most databases can use the utf8-swedish-ci collating. (The ci suffix stands for case-insensitive. It's generally best to run TNG with case-insensitive collating.) | ||
+ | Among the considerations you may need to apply to a decision about what Character Set and Collation you need are: | ||
=== Desktop genealogy program === | === 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 === | === 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-plus-plus|Notepad++]] on Windows PC's can both support UTF-8 encoding without BOM. | |
=== Accented characters === | === Accented characters === | ||
Line 34: | Line 47: | ||
== Defining your database == | == Defining your database == | ||
− | The following are a | + | The following are a examples of how to create a database: |
+ | |||
=== Simply Hosting === | === Simply Hosting === | ||
Line 46: | Line 60: | ||
==== Change Collation Sequence ==== | ==== Change Collation Sequence ==== | ||
− | Because you cannot specify the collation sequence, | + | Because you cannot specify the collation sequence in the wizard, you need to change the collation sequence after the database is created, and before you create any tables. To do so, use your web browser to go to your Simply Hosting's administrative interface and run '''phpMyAdmin''', the relational database management tool that Simply Hosting (and many other ISP's) make available for users. Here is the initial phpMyAdmin screen: |
− | + | ||
− | + | [[Image:database-creating-phypmyadmin1.png|600px]] | |
− | + | ||
− | + | 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. In the larger right on the right (with several sub-panels), you see the "General Setting" panel where a collation has been selected. That's not the one you want. That value is used when new databases are created, but it may not be used by the Wizard you ran in the previous step. | |
− | + | ||
− | + | 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 the Server hostname is "localhost". (That is, this screenshot is from phpMyAdmin running on a local PC, not on a Simply Hosting server. But it looks the same.) | |
− | + | ||
+ | To change the collation of your database, 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 Privileges tab (which is circled above). You'll then see this screen: | ||
+ | |||
+ | [[Image:database-creating-phypmyadmin2.png|600px]] | ||
+ | |||
+ | 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" and, if you wanted utf8-swedish-ci", you'll need to change the selection and click on "Go". | ||
− | | | + | If you need help selecting a TNG collation, please read [[Database_Collation_-_Explain_Choosing|Selecting your TNG Database Collation]]. |
=== ICDSoft === | === ICDSoft === | ||
− | On [http://www.icdsoft.com/ | + | On [http://www.icdsoft.com/ ICDSoft], |
− | + | # Log into your hosting account Control Panel | |
− | + | # Enter the MySQL Databases section | |
− | + | # Select the MySQL5 tab | |
+ | # At the bottom, enter a database name and the collation you want | ||
+ | # Then enter a user will full privileges that the TNG software can use. | ||
+ | |||
+ | [[image:ICDSoft_create_database.jpg|600px||ICDSoft Create Database]] | ||
+ | |||
=== GoDaddy === | === GoDaddy === | ||
− | The following | + | The following is a recap of the steps for creating a database on a GoDaddy web host. (See GoDaddy's [https://www.godaddy.com/help/create-mysql-or-ms-sql-databases-36 Help Page for creating a database]]) |
− | + | # Log in to your Account Manager | |
− | + | # Click '''Web Hosting''' | |
− | + | # Next to the hosting account you want to use, click '''Manage''' | |
− | + | # In the '''Databases''' section of the Hosting Control Panel, click the icon for ''MySQL''. | |
− | + | # Click '''Add''' | |
− | + | # Complete the fields to specify | |
− | + | #* A description of your database | |
− | + | #* Your database name, | |
− | + | #* The username and password that TNG will need in order to access the database | |
− | + | #* select '''radio button for php version''' (defaults to 4.1), | |
− | + | click '''OK''' to create database | |
− | *select '''radio button for php version''' (defaults to 4.1), click '''OK''' to create database | ||
− | |||
*You can ignore the Open Manager link and/or button, as you will not typically need to set anything there. '''Encoding defaults to UTF-8'''. | *You can ignore the Open Manager link and/or button, as you will not typically need to set anything there. '''Encoding defaults to UTF-8'''. | ||
Line 86: | Line 108: | ||
=== Local WampServer === | === Local WampServer === | ||
− | + | To create a new TNG database using '''phpMyAdmin''' on a WampServer environment: | |
− | + | # Launch phpMyAdmin from the WampServer icon in the system tray | |
* enter the database name in the Create new database field | * enter the database name in the Create new database field | ||
* select the collation sequence from the pull down | * select the collation sequence from the pull down | ||
Line 93: | Line 115: | ||
* use the TNG readme.html to create the tables | * use the TNG readme.html to create the tables | ||
− | + | [[image:WampServer_create_database.jpg|500px]] | |
− | |||
− | |||
− | |||
− | [[image:WampServer_create_database.jpg|500px | ||
− | |||
{| border="0" cellspacing="5" cellpadding="2" width="100%" class="wikitable" | {| border="0" cellspacing="5" cellpadding="2" width="100%" class="wikitable" |
Revision as of 08:25, 11 December 2015
The TNG software comes with detailed installation instructions in a readme.html file. Those instructions apply to virtually any site that TNG is being installed on. But different techniques are required for creating databases on different hosts.
On a localhost WampServer installation, you may be able to use the TNG readme.html to create the database, but most hosting services require that you create your database before you install TNG.
TNG now requires that you use MySQL 5. Because MySQL 5 uses utf8 definitions for its fields you may want to consider creating the database as UTF-8 from the start and avoid a future conversion. However, you should consider whether you have all the tools you need to maintain and support your data in UTF-8 encoding.
You should also create a database user for your new database, so that you do not use your web hosting account username and password for connecting to the database as an additional security measure. See Database User
Just what is a "Database"
Beware of the many different meanings for the term "Database" and the phrase "Creating a Database"
- Sometimes, SQL Server is called a "Database" as opposed to the more correct "Database Management System" or "Database Management System Product".
- On your database server, you must have access to a "Database Instance" (which is sometimes just called a "Database"). 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 (such as WAMPServer), your host name is almost always "localhost".
- A "Database" is a named collection of database tables. Most of the time, separate applications have separate databases, but it is entirely possible for, say TNG and, say MediaWiki to share a database as long as none of their table names overlap. You can call you TNG database whatever you want. "TNG" is often a good choice.
- 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 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 tables.
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 and
- A Collation (sorting rules)
for your database. See Database Collation - Explain Choosing
Though it is not the default MySql encoding, UTF-8 is the most flexible encoding to use in your database. Most databases can use the utf8-swedish-ci collating. (The ci suffix stands for case-insensitive. It's generally best to run TNG with case-insensitive collating.) Among the considerations you may need to apply to a decision about 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.
Defining your database
The following are a examples of how to create a database:
Simply Hosting
Create Database Wizard
On Simply Hosting you can select MySQL Databases Wizard to create the database. Enter the database name you want to use and click the Create Database button.
Change Collation Sequence
Because you cannot specify the collation sequence in the wizard, you need to change the collation sequence after the database is created, and before you create any tables. To do so, use your web browser to go to your Simply Hosting's administrative interface and run phpMyAdmin, the relational database management tool that Simply Hosting (and many other ISP's) make available for users. Here is the initial phpMyAdmin screen:
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. In the larger right on the right (with several sub-panels), you see the "General Setting" panel where a collation has been selected. That's not the one you want. That value is used when new databases are created, but it may not be used by the Wizard you ran in the previous step.
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 the Server hostname is "localhost". (That is, this screenshot is from phpMyAdmin running on a local PC, not on a Simply Hosting server. But it looks the same.)
To change the collation of your database, 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 Privileges tab (which is circled above). You'll then see this screen:
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" and, if you wanted utf8-swedish-ci", you'll need to change the selection and click on "Go".
If you need help selecting a TNG collation, please read Selecting your TNG Database Collation.
ICDSoft
On ICDSoft,
- Log into your hosting account Control Panel
- Enter the MySQL Databases section
- Select the MySQL5 tab
- At the bottom, enter a database name and the collation you want
- Then enter a user will full privileges that the TNG software can use.
GoDaddy
The following is a recap of the steps for creating a database on a GoDaddy web host. (See GoDaddy's Help Page for creating a database])
- Log in to your Account Manager
- Click Web Hosting
- Next to the hosting account you want to use, click Manage
- In the Databases section of the Hosting Control Panel, click the icon for MySQL.
- Click Add
- Complete the fields to specify
- A description of your database
- Your database name,
- The username and password that TNG will need in order to access the database
- select radio button for php version (defaults to 4.1),
click OK to create database
- You can ignore the Open Manager link and/or button, as you will not typically need to set anything there. Encoding defaults to UTF-8.
Provided by Doug on the TNG Community Forum
Local WampServer
To create a new TNG database using phpMyAdmin on a WampServer environment:
- Launch phpMyAdmin from the WampServer icon in the system tray
- enter the database name in the Create new database field
- select the collation sequence from the pull down
- click the Create button
- use the TNG readme.html to create the tables
which should return the database created message |
Database User
As an added security precaution, once you have created your database, you should create a Database User