Creating and filling MySQL database tables

In a previous posting I summarized the steps required to start your own private instance of the MySQL daemon. Hibernate needs a few things more than just the bare DBMS instance, however. You will need a database, at least one table in it, and a user with permission to access that table. The user is not strictly needed. The user «root» has full permission to do anything. And this is precisely the reason why to create a dedicated user.

All the stuff following is not fit for a production environment, due to the lax security precautions.

Creating a database and and user

Creating a database is very simple. Connect to your daemon:

mysql -u root mysql

This will only work if the Unix socket or IP-address and port are specified in either a configuration file or by a environment variable. Then, from within the mysql client, create a database,

create database simple;

and a user:

create user 'hibernate'@'localhost';

My user «hibernate» may only connect from the local host. He has no password set, though. I am not going to change that, as it is more convenient during development.

Then grant him full permission on the database you created earlier:

grant all privileges on simple.* to 'hibernate'@'localhost';

The MySQL manual explains the whole topic on creating and confining users in greater detail.

Filling the database

Initializing a database is a cumbersome and error-prone task. It usually involves sending quite a few SQL commands to the DBMS instance. All the repeatedly needed stuff better goes into a plain text file for re-use. By convention one with the file extension «sql». I have one sql file that creates the tables. It is named «simple_db_create_tables.sql» and looks something like this:

drop table if exists songs;
drop table if exists albums;

create table albums (
  id integer primary key auto_increment,
  title varchar(20),
  publisher varchar(20)
);

create table songs (
  id integer primary key auto_increment,
  title varchar(40),
  artist varchar(20),
  performer varchar(20),
  album integer,

  foreign key (album) references albums (id)
);

Yes, it is about music, as my Android example. Another sql script loads sample data from a text file. It utilizes the «load data» statement for that purpose. My sample data is arranged as list of comma-separated values, one file per table to be filled, e.g. in this example one for albums and one for songs. The album data file contains this:

Contains sample values for the "simple" database table "albums".
Values separated by comma. First two lines ignored.
1, Distraction Pieces, Speech Development
2, A Canticle for Leibowitz, "Blackstone Audio, Inc."

The «load data» statement is quite flexible as to the structure of the input data. My CSV files are loaded by statements like this:

load data local infile 'simple_db_albums.csv'
      replace
      into table simple.albums
      fields terminated by ','
      ignore 2 lines;

Did you wonder why the data file above explicitly sets the «id» field for each row? Should not «auto_increment» accomplish that? Well, it would. Just leave the first field empty and it kicks in. But «id» is a foreign key used by the table «songs». That means each song must give the exact id of its album. Furthermore, subsequent loading of the CSV-file would happily insert row after row of the same two albums into the database, each having a different id.

All scripts can be executed with the «mysql» command-line tool, e.g.:

mysql -u hibernate simple < simple_db_create_tables.sql

The tutorial chapter of the MySQL documentation explains creating and filling tables much more thoroughly.

Eclipse SQL Explorer

All the things above—and much more—can be accomplished conveniently from within Eclipse with the help of the Eclipse SQL Explorer extension. Installing the JDBC-driver for MySQL (or any other DBMS) is—while not overly complicated–also not exactly trivial. Mark E. DeYoung tells you what needs to be done.

SQL Explorer sucks however when it comes to executing «load data» statements, though not entirely to its own fault. The file-name given to that statement can be either an absolute or a relative path. In the former case the behavior of «load data» is pretty obvious. In the latter case however, the path is interpreted relative to the directory where the executing command—the mysql client or in this case Eclipse—has been started. With the mysql client you would simply change directory to where the data file resides, but as for Eclipse this is not within you power. Most likely Eclipse had been started with your home directory as current working directory, and this is where «load data» would resolve the relative path from. Setting the path of «load data» relative to your home directory will most likely not be viable because anyone else sharing the sql script would have to have the data file at the exact same relative path within their home directories too. For the same  reason absolute paths fail altogether.
In consequence, it means that for loading data the «mysql» command is still the best.

With tables created and filled, one can proceed to finally accessing them from a Java program. But not in this posting.

Updates

08/14/2012, 21:00 UTC Changed «grant all privileges on simple…» to «grant all privileges on simple.*…» Otherwise MySQL would interpret «simple» as a table name in the currently selected database, which would most likely be the «mysql» database.
Also, my MySQL user is no longer called «eclipse» because it is mostly not Eclipse accessing my database. Chose «hibernate» instead.