Creation and Configuration of Databases

The creation of databases and the configuration of remote databases is a part of the general PMOD configuration. It is opened by calling the

button from the PMOD Toolbar, and selecting the DATABASE tab in the appearing dialog.

To create a new database please proceed as follows.

Derby JDBC Database Creation (default)

1. Add new data source: Both types of databases are called a data source. Activating the Add new data source button pops up a dialog to request a name for the new data source (eg. Pmod). The new data source is added to the list selection and must be configured in the lower section. Please do not change the data source name after the database has been in use.
2. Use JDBC connection: Only JDBC databases can be created, so this radio button must be selected.
3. Database Connection: This entry defines the database access. The first part jdbc:derby: is the driver specification. The Database Name is the name by which the data source is known to the JavaDB. In principle the name can differ from the data source name, but this is not recommended.
4. Database Path: The directory where JavaDB can save the database tables (in a subdirectory named according the database name). ./DATABASES represents the DATABASES directory in Pmod3.1/data.
5. File Storage Area Path: This is the path of the root directory in which the data files will be stored. ./DBDATA/Pmod/ represents the directory in Pmod3.1/data/DBDATA/Pmod.
6. Create Data Base: Activating this button finally creates the JDBC database. Connectivity can be tested with the Echo button.

Note: After the installation of Pmod3.1, the Pmod database illustrated above is already available and contains different types of example data, unless the example database was not selected for installation.

mySQL JDBC Database Creation (deprecated)

1. Add new data source: Both types of databases are called a data source. Activating the Add new data source button pops up a dialog to request a name for the new data source (eg. Pmod). The new data source is added to the list selection and must be configured in the lower section. Please do not change the data source name after the database has been in use.
2. Use JDBC connection: Only JDBC databases can be created, so this radio button must be selected.
3. Database Connection: Configure as follows:
   
   The first part is the driver jdbc:mysql. The second part is the host on which the mySQL server is running, in the example above the same machine PMOD is running on, thus localhost. The third part is the mySQL database name, Pmod. In principle the database name can differ from the data source name, but this is not recommended.
4. User:Password: Fields to specify a user who has administrator privileges in the addressed mySQL installation and his password. The standard setting of mySQl is root and an empty password. If mySQL requires to enter a non-empty password for root, please specify it in this password field.
5. File Storage Area Path: This is the path of the root directory in which the data files will be stored. ./DBDATA/Pmod/ represents the directory in Pmod3.1/data/DBDATA/Pmod.
6. Create Data Base: Activating this button finally creates the JDBC database. Connectivity can be tested with the Echo button.

Note: You can create different SQL databases to collect data for different projects. They must, of course, have different names and should most likely save the data files in different directories.

PMOD allows publishing a local data base to other PMOD installations by a server program called Transaction Server (see below how to set up the transaction server). To address such a transaction server database, a data source must be defined as follows:

Access to Remote Transaction Server Databases

1. Add new data source: Again, a new data source must be created and adequately named.
2. Use Transaction Server: This radio button must be selected, and the transaction server address and port entered.
   
   In this example, PMOD is installed on a host called DB-Server where a transaction server for a particular JDBC database has been started listening on port 5100. As the database is already existing, the initialization step is not necessary.
3. Note the two boxes Secure and Compressed. Please configure the communication with the same properties as the transaction server is expecting (see below).
4. Whether the transaction server is running, connectivity can be tested with the ECHO button.

Multiple Access to Derby JDBC Database

It is the nature of embedded databases as a Derby JDBC database that they can only be accessed by a single process. Therefore, if you start two instances of PMOD, only the first one will be able to access the database directly. The second one will not get access and show an error message

The solution for this case is to publish the database by a transaction server and access the data through it. In this way, an arbitrary number of processes can access the database.

Note: The default configuration of Pmod3.1 already includes a transaction server definition of the Pmod database. The transaction server will be automatically started with the first interactive PMOD session. Access to Pmod through the transaction server is configured as the PmodTS database.

Database De-Activation

Sometimes there is a need to temporarily deactivate a data source, eg. because a remote system is down. In this case the box Inactive can be checked to avoid lengthy timeouts.

Default Database

The database which is shown in the data source list when the configuration is saved will serve as the default database. Therefore, you are recommended to set the list entry to the most frequently used database before closing. This setting can be overridden by user-specific database preferences.

Database Upgrading

After updating a PMOD installation there might be a need to adjust the data structure of the existing databases for using them further. To do so, select a JDBC data source and activate the Update button.
Note: Transaction server data sources must be upgraded on the remote installation.

Database Backup

The SQL database tables of a local JDBC data source can be backed up using the buttons shown below.

The Backup path can be specified with the SQL Backup Properties button.

An interactive backup can then be performed with the Save SQL Backup button. It can be restored with the Restore SQL Backup button.

Automatic backups based on a scheduling are also supported. Initially the scheduling section is gray. Only when a local transaction server has been defined and started, and the Automatically box is checked, it becomes active. At the scheduled times the database tables are saved to the specified directory, provided that the local transaction server is running.

CAUTION: The database backup described above only saves the SQL database tables to a safe place, not the data files themselves. To backup the data please manually copy all information in the configured File Storage Area Path to the backup place.