Using the MySQL database

The SME Server provides a standard method for performing MySQL database initialization and migration. This is done by creating files in the /etc/e-smith/sql/init/ directory. These files are run automatically when MySQL is started, and deleted if they run successfully.

A separate MySQL database and one or more database users should be created for each application. The database password should be stored in the configuration database and either retrieved from the configuration database by the application or passed to the application via an httpd.conf fragment. The password should be automatically generated, unique to this server and this application, and stored as a property in the configuration database for later use in database scripts.

The MySQL root is automatically generated and configured for command-line MySQL use by the root system user. The MySQL root password should only be used for database maintenance such as creating and deleting databases and performing database backups.

First choose a name for your database, as well as a username to access the data. For example, let's say your database is called loggerdemo, the username is loguser and the password is $loggerdemo{DbPassword}. A migrate fragment like this might be used to create the password:

{
    my $rec = $DB->get('loggerdemo')
        || $DB->new_record('loggerdemo', {type => 'service'});

    my $password = $rec->prop('DbPassword');
    return "" if $password;

    use MIME::Base64;

    my $rand     = sprintf("%08d", int(1_000_000_000 * rand()));
    my $password = MIME::Base64::encode($rand, "");

    $rec->set_prop('DbPassword', $password);
}

Then create a template which generates a file in the /etc/e-smith/sql/ directory, and put the relevant SQL commands in that file. The SQL commands should set up the application's username and retrieve the database password from the configuration database. It creates the new MySQL database and any tables required by your application. Write these SQL commands using the IF NOT EXISTS clause so that they do nothing if the tables have already been created. For example, you might create the template /etc/e-smith/templates/etc/e-smith/sql/loggerdemo-create-schema.sql with the following contents:

# Create the user account and password. (This is harmless if the
# user account and password already exist.) Note the reference
# to the 'loggerdemo' database which will be created in the next
# few statements.
  
USE mysql;
  
REPLACE INTO user (host, user, password)
    VALUES ('localhost', 'loguser', PASSWORD ('{ $loggerdemo{DbPassword} }'));
  
REPLACE INTO db (host, db, user,
                   select_priv, insert_priv, update_priv,
                   delete_priv, create_priv, drop_priv )
     VALUES ('localhost', 'loggerdemo', 'loguser',
             'Y', 'Y', 'Y', 'Y', 'Y', 'Y');
  
FLUSH PRIVILEGES;
  
# Create 'loggerdemo' database. (Do nothing if the database
# already exists.)
  
CREATE DATABASE IF NOT EXISTS loggerdemo;
  
# Create log_entry table within the 'loggerdemo' database.
# (Do nothing if the table already exists.)
  
USE loggerdemo;
  
CREATE TABLE IF NOT EXISTS log_entry
(
    entry_message  varchar(60),
    entry_time     datetime
);

Include the migrate fragment and your template in your RPM. Note that the password generated in this way is unique to this server and this application and automatically stored in the configuration database for later use. This means that it is backed up and restored through the normal server operations.

In the post-installation section of your RPM, expand the template, and run the /etc/rc.d/init.d/mysql.init script. For example the post-installation section of your RPM SPEC file might look like this:

%post
expand-template /etc/e-smith/sql/init/loggerdemo-create-schema.sql
/etc/rc.d/init.d/mysql.init
true

Installing this RPM will create the /etc/e-smith/sql/loggerdemo-create-schema.sql templates (because it is part of the RPM), and the post-installation actions will expand the template and run the mysql.init script, which will execute the schema creation commands and delete the generated file. When the RPM installation is finished, the database schema will have been created, and the MySQL database will be ready to process SQL commands from your application.

It is also possible to perform MySQL initialization in languages other than SQL, for example if the logic is better suited to another language, simply by creating a file in the /etc/e-smith/sql/init/ directory. The file must be executable and not have a .sql extension. For example, the template expansion might generate this file:

#! /bin/sh

exec mysql < /home/httpd/html/horde/scripts/db/mysql_create_tables.sql

You can use the templates.metadata mechanism to ensure the correct permissions on the generated file. Remember, the files are removed from the /etc/e-smith/sql/init/ directory if they run successfully.

It is important to think through what will happen when your application is installed, uninstalled, reinstalled, or upgraded. The instructions described above do not specify any uninstallation procedure - therefore the database tables will be left unchanged if your application is removed, reinstalled, or upgraded. If you want the data to be deleted when the application is removed, create a post-uninstallation script using the same technique as the post-installation script.

The instructions above apply to an application with a schema that does not evolve. If you create a new version of your application that requires schema changes, your post-installation script will have to migrate the database from the old to the new schema. In that case you have two options. Say the original version of the application is 1.0, and the new version is 1.1.

1. The first option is to release two versions of the 1.1 application - one for new installations (containing SQL commands to create a new schema), and a second version for upgrading 1.0 installations (containing SQL commands to upgrade the 1.0 schema). The RPM mechanism allows you to specify dependencies to ensure that only the correct version of each RPM can be installed on a given SME Server.

2. The better option is to change the template so that it includes the appropriate MySQL code to query the database and automatically determine whether to migrate an existing schema or create a new one. The e-smith-horde package contains a number of MySQL database initialisation and migration scripts which may be useful to study.