The SAFE stores state in a MySQL database. The connection to this database needs to be configured using properties. This can either be done explicitly:
db_name=jdbc:mysql://localhost/safedb?characterEncoding=utf8
db_driver=com.mysql.jdbc.Driver
db_username=database-username
db_password=database-password
Or a pool of database connections can be configured in tomcat https://tomcat.apache.org/tomcat-7.0-doc/jndi-datasource-examples-howto.html#MySQL_DBCP_Example and the corresponding resource name set as a configuration property:
connection.pool=safeDB
As the pool is configured in the Context it is often convenient to set this parameter in the same file as an init-parameter.
You usually need to add the jar-file containing the database driver to the lib directory of the tomcat deployment.
Whichever way you configure the database the access account usually needs full access to the database the application will use.
Mysql> GRANT ALL ON safedb.* TO 'database-username'@'localhost' IDENTIFIED BY “database-password”;
We would recommend that the InnoDB database engine is used by default and the mysql parameter innodb_file_per_table is set to place each database table in its own file. If you are going to be using the helpdesk code set the max_allowed_packet parameter to be large enough to hold the largest email message you intend to support.
The database should use case insensitive matching and we recommend the use of the UTF8 character set.
Mysql> ALTER DATABASE safedb CHARACTER SET utf8 COLLATE utf8_general_ci;
The application will usually create all tables that it needs within the database (This can be turned off by setting the property service.feature.auto_create.tables=off ). In addition we write the code to be tolerant of database schema changes to make it easier to upgrade application versions without requiring downtime. Newer versions of the application can use databases created for older versions (though if new fields are required in an existing table the corresponding new functionality will remain disabled until the required field is created). Similarly the code is written to be tolerant of unexpected database fields so older versions should continue to run even after the required field has been created.
Some of the configuration changes described below will automatically change the database specification. If you make the configuration changes before starting the application for the first time the tables will be created appropriate to those settings. If the settings are changed after the tables have been created you will have to use the table edit forms to make the appropriate changes.