Database Configuration

The PTV xServer is delivered with simple databases (H2) to store jobs and sessions.

In rare cases like power failure the H2 databases may become corrupt. To repair them do the following steps on a command line:

        cd <XSERVER_HOME>/data/db
        java -cp ../../jdbc/h2-2.1.210.jar org.h2.tools.Recover
        rename the job.mv.db and session.mv.db database files
        java -cp ../../jdbc/h2-2.1.210.jar org.h2.tools.RunScript -url jdbc:h2:./job -script job.h2.sql -checkResults
        java -cp ../../jdbc/h2-2.1.210.jar org.h2.tools.RunScript -url jdbc:h2:./session -script session.h2.sql -checkResults
        

Alternative Databases

Other databases can be used as well, as long as there is a jdbc driver in the top level folder jdbc.

The JDBC driver for Microsoft SQL Server is, for example, named mssql-jdbc-*.jre8.jar. To use this database put the driver into the jdbc folder, adjust the job-management-db.xml and session-management-db.xml files in the conf folder as follows:

        <property name="dataSourceProperties">
            <props>
                <prop key="url">jdbc:sqlserver://$hostname:$port;databaseName=$databaseName</prop>
                <prop key="user">$user</prop>
                <prop key="password">$password</prop>
            </props>
        </property>
        

Depending on the database it is necessary to adapt the property connectionTestQuery.

Job database structure

For job management nearly any JDBC database can be used. It is required that the BLOB API is fully implemented by the JDBC interface.

The structure of the necessary database tables xserver_jobs and cleanup has to be defined as follows:

        CREATE TABLE xserver_jobs
        (
           id varchar(36) NOT NULL,
           xserver varchar(18),
           method varchar(50),
           version varchar(20),
           status varchar(20) NOT NULL,
           elapsed bigint,
           progress blob,
           result blob,
           finishtime bigint,
           fetchtime bigint,
           lastupdatetime bigint,
           userid varchar(36)
        );

        CREATE INDEX xserver_jobs_idx2 ON xserver_jobs(finishtime);
        CREATE INDEX xserver_jobs_idx1 ON xserver_jobs(status);
        CREATE UNIQUE INDEX xserver_jobs_primary_key ON xserver_jobs(id);
        CREATE INDEX xserver_jobs_idx4 ON xserver_jobs(lastupdatetime);
        CREATE INDEX xserver_jobs_idx3 ON xserver_jobs(fetchtime);

        CREATE TABLE cleanup
        (
           executiontimestamp bigint
        );

        INSERT INTO cleanup (executiontimestamp) VALUES (0);
        

Depending on the database it is necessary to adapt the data types.

Session database structure

For job management nearly any JDBC database can be used. It is required that the BLOB API is fully implemented by the JDBC interface.

The structure of the necessary database table xserver_session is defined as follows:

        CREATE TABLE xserver_session
        (
           id varchar(36) NOT NULL,
           session blob,
           date bigint,
           version varchar(20)
        );

        CREATE UNIQUE INDEX xserver_session_id_index ON xserver_session(id);
        CREATE INDEX xserver_session_date_index ON xserver_session(date);
        

Depending on the database it is necessary to adapt the data types.