The MySQL Database

Many databases are out there from which to choose. Some of the best ones are even free! MySQL was chosen as a demonstration database in this book because it’s fast, popular, stable, and standards compliant. Oh, yeah—it’s also free! Seriously, MySQL is able to handle many concurrent users and store millions of records. It’s multithreaded, allowing many concurrent users, as well as able to distribute the database server application across multiple CPUs. Here we’ll cover the basics of getting an instance of the MySQL server up and running. This section relates to MySQL-specific tools and features, so if you already have another database vendor in mind, you can skip this section.

Installing MySQL

The first thing to do is to navigate to www.mysql.com and download the latest stable version, which at the time of this writing was the MySQL4.0.14b production release. For Windows users, simply download the binary distribution, unzip it, and then double-click the setup.exe file and install using the default settings. That’s it, done. However, if you’re using Unix or another platform, chances are the installation and configuration instructions are more complex and are probably as lengthy as this chapter. If you’re using such a platform, you’ll have to consult the MySQL installation documentation.

This section assumes you are using a Windows 2000/XP operating system and the installation target directory is c:\mysql. No matter which Windows platform you’re on, once the installation has completed, add c:\mysql\bin to the PATH system environment variable, located in the control panel for the Windows users.

Starting the Server

When the installation is complete and the PATH variable has been set, open a shell or DOS prompt and type the mysqld command to start the server:

mysql> mysqld 

If the server started correctly without error, nothing should print to the console, and the prompt will be unavailable, because mysqld.exe is now running in that shell.

Connecting a Client

To connect to the server, open another shell (the first shell is hosting the server process) and invoke the mysql command:

mysql> mysql 

A password may be required to start the server, depending on the database configuration. If the logon is successful, the following lines will be printed to the console:

Welcome to the MySQL monitor.  Commands end with ; or \g. Your MySQL connection id is 25338 to server version: 4.0.14-log Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer.

If everything so far has gone well, the client should now be connected to the database server, and the server is now ready to receive commands from the client. To test your connection, try a simply query with the SELECT command:

mysql> SELECT version(); +—————————+ | version()        | +—————————+ | 4.0.14-max-debug | +—————————+ 1 row in set (0.00 sec) 

If the SELECT was executed successfully and the version information is printed to the console, it’s safe to say the database server is up and running, and the client is connected and has access to the data in the database. To get help at any time, type the following command:

mysql> help 

Building a Database

Immediately after installing the MySQL database server, there’s not much to look at. However, a couple of databases are already running. To view a list of the currently active databases, use the SHOW command:

mysql> SHOW databases; +—————+ | Database | +—————+ | mysql    | | test     | +—————+ 2 rows in set (0.00 sec)

The mysql database contains system-level information, and its use is beyond the scope of this chapter. The test database is simply there for practice and learning. A new database can be created using the CREATE command:

mysql> CREATE DATABASE gamedb; Query OK, 1 row affected (0.01 sec)

To confirm that the new database was successfully created, use the SHOW command again to list the database instances running on the server:

mysql> SHOW databases; +—————+ | Database | +—————+ | gamedb   | | mysql    | | test     | +—————+ 3 rows in set (0.00 sec)

The client (at least from this shell) can be connected to, or operate on, only one database at a time. The process of switching focus from one database context to another involves using the USE command:

mysql> USE gamedb; Database changed

At this point, commands will be executed within the context of the gamedb database. The database is, of course, empty, because it was just created. Tables will have to be created and populated with data before things get off the ground. It should be noted that although a shell is being used to send commands to the database, scripts can be written to do the same things, such as creating databases and tables. In fact, it’s recommended that the database be created from a script. This way, if a change needs to be made, it can be made to the script itself without having to type individual commands into a shell, one by one. Database scripts can be saved into text files and run any time.

Now that the game database exists, it’s time to create a couple of database tables. The following code will create a player and an item table. The player table will contain a foreign key to the item table. The following SQL code will create the two tables.

CREATE TABLE player(     id SMALLINT UNSIGNED NOT NULL,     name CHAR(60) NOT NULL,     health SMALLINT NOT NULL,     item SMALLINT UNSIGNED NOT NULL REFERENCES item(id),     PRIMARY KEY (id) ); CREATE TABLE item (     id SMALLINT UNSIGNED NOT NULL,     name CHAR(60) NOT NULL,     power SMALLINT NOT NULL,     PRIMARY KEY (id) );

To verify that the tables have been created, use the SHOW command:

mysql> SHOW tables; +—————————+ | Tables_in_gamedb | +—————————+ | item             | | player           | +—————————+ 2 rows in set (0.00 sec)

The DESCRIBE command can also be used to view details (the keys are shown in bold):

mysql> DESCRIBE player; +————+———————————+———+——-+————-+———-+ | Field  | Type                 | Null | Key | Default | Extra | +————+———————————+———+——-+————-+———-+ | id     | smallint(5) unsigned |      | PRI | 0       |       | | name   | char(60)             |      |     |         |       | | health | smallint(6)          |      |     | 0       |       | | item   | smallint(5) unsigned |      |     | 0       |       | +————+———————————+———+——-+————-+———-+ 4 rows in set (0.00 sec) mysql> DESCRIBE item; +———-+———————————+———+——-+————-+———-+ | Field | Type                 | Null | Key | Default | Extra | +———-+———————————+———+——-+————-+———-+ | id    | smallint(5) unsigned |      | PRI | 0       |       | | name  | char(60)             |      |     |         |       | | power | smallint(6)          |      |     | 0       |       | +———-+———————————+———+——-+————-+———-+ 3 rows in set (0.00 sec)

Before anything useful can be done with the tables, they must be populated with data. Using the SQL INSERT statement, you can easily insert a few records using the following commands.

INSERT INTO item values(1,"Dagger",5); INSERT INTO item values(2,"Sword",12); INSERT INTO item values(3,"Axe",20); INSERT INTO player values(1,"Will",75,1); INSERT INTO player values(2,"Shawn",100,2); INSERT INTO player values(3,"Chad",87,2);

SQL commands will be covered in more detail shortly. For now, these simple commands should be readable enough to get the point across. Now that a few records have been placed in the tables, the SELECT statement can be used to verify/view the data:

mysql> SELECT * FROM item; +——+————+———-+ | id | name   | power | +——+————+———-+ |  1 | Dagger |     5 | |  2 | Sword  |    12 | |  3 | Axe    |    20 | +——+————+———-+ 3 rows in set (0.00 sec) mysql> SELECT * FROM player; +——+———-+————+———+ | id | name  | health | item | +——+———-+————+———+ |  1 | Will  |    100 |    1 | |  2 | Shawn |    100 |    2 | |  3 | Chad  |    100 |    2 | +——+———-+————+———+ 3 rows in set (0.00 sec)

When the session needs to be ended, the client may be disconnected from the server by issuing the QUIT command:

mysql> quit 

Online Resources

For the complete MySQL Manual online visit: www.mysql.com/documentation

For information on technical support from MySQL developers, visit: www.mysql.com/support

For information on MySQL books, utilities, consultants, and so on, visit: www.mysql.com/portal



Practical Java Game Programming
Practical Java Game Programming (Charles River Media Game Development)
ISBN: 1584503262
EAN: 2147483647
Year: 2003
Pages: 171

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net