Database MySQL

Creation of database schema and users

Execute the following SQL script as database user root (e.g. via MySQL Workbench):

-- create schema goco;
create schema goco;
create user goco identified by 'goco';
REVOKE ALL PRIVILEGES,GRANT OPTION from goco;
GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE, SHOW VIEW, CREATE, ALTER, INDEX, DROP, REFERENCES ON goco.* TO 'goco';

-- create schema quartz
create schema quartz;
create user quartz identified by 'quartz';
REVOKE ALL PRIVILEGES,GRANT OPTION from quartz;
GRANT SELECT, INSERT, UPDATE, DELETE ON quartz.* TO 'quartz';

Leave the passwords for the users goco and quartz for the moment, until the initial installation is performed and verified. After that you can change the passwords as described in Wie kann ich das Datenbank-Passwort ändern?

Creation of quartz tables (Job-Scheduling)

Execute the following script (it creates tables for schema "quartz", which does the nightly batch job scheduling:

create_quartz_tables.sql

Import of the database

  1. Download the initial DB file (Save Link As...) as provided by us in the download area bereitgestellt haben (mysql-dbload-initial.sql).

  2. Import the DB file into MySQL (e.g. via MySQL Workbench, menu Server --> Import --> Import From Self-Contained File). Choose "goco" as Default Target Schema.

  3. The import screen of MySQL Workbench will show a green progress bar after a few seconds after a successful import. To verify this, refresh the schemas on the left sidebar, open schema "goco", und check that it contains tables (prefix co_).

Adjust context.xml of Application Server for MySQL

To configure the database connection on the Application Server, open the file context.xml (in <Tomcat-Directory>/conf). Add the following lines directly after the element <context>. This creates the database data-sources for schemas "goco" and "quartz":

    <Resource
        name="gocoTenantDs"
        factory="com.gocompliant.encryptedDs.EncryptedDataSourceFactory"
        auth="Container"
        type="javax.sql.DataSource"
        driverClassName="com.mysql.jdbc.Driver"
        url="jdbc:mysql://localhost:3306/goco?allowPublicKeyRetrieval=true&amp;useSSL=false&amp;serverTimezone=Europe/Zurich"
        username="goco"
        password="922f591c101ab822305be286a532c196"
        maxActive="20"
        minIdle="0"
        maxIdle="0"
        minEvictableIdleTimeMillis="14400000"
        maxWait="-1" />
    <Resource
        name="quartz"
        factory="com.gocompliant.encryptedDs.EncryptedDataSourceFactory"
        auth="Container"
        type="javax.sql.DataSource"
        driverClassName="com.mysql.jdbc.Driver"
        url="jdbc:mysql://localhost:3306/quartz?allowPublicKeyRetrieval=true&amp;useSSL=false&amp;serverTimezone=Europe/Zurich"
        username="quartz"
        password="2259b33c5588618ee0742b2ced9998e6"
        maxActive="20"
        minIdle="0"
        maxIdle="0"
        minEvictableIdleTimeMillis="14400000"
        maxWait="-1" />

Alternative to MySQL: Database Oracle

Creation of database users, tablespaces and quartz tables

  1. Login as a user with role DBA on to the Oracle instance that you have set up for the GoCompliant ToolSuite.
  2. Execute the following SQL script:
    01_create_users.sql
    This creates the users "goco" and "quartz" and some grants. Leave the passworts as provided for the moment until the initial installation is performed and verified. After that, you can change the passwords as described in Wie kann ich das Datenbank-Passwort ändern?
    The user "goco" receives grants für CREATE TABLE/VIEW. In doing so, you enable the software to upgrade the database structure automatically on update-releases.
  3. The following script create an initial 500MB tablespace for users "goco" and "quartz":
    02_example_create_tablespaces.sql
    This script is intended as an example and should be adjusted by you. The space requirements of the GoCompliant ToolSuite is mostly determined by the attachments that will be uploaded by the users (Limit is 20MB per Datei), and therefore depends on your user requirements. As a rough guide we can say that a middle-sized installation (<100 Benutzer) should be running fine with 1-3 GB for a few years.
  4. The following script creates helper tables for quartz, used by the nightly job scheduling:
    03_create_quartz_tables.sql

Creation of GoCompliant table scructure and initial data

Download the following scripts (right click → Save Link As...) that we have provided in the download area, and execute them. You can execute the scripts either with a DBA role user or as user "goco". The scripts are UTF-8 encoded, in order to treat German/French/Italian special characters correctly. Make sure that the DB server and client are using UTF-8 encoding before executing the script 05_goco_insert_init_data.sql.

  • 04_goco_create_tables.sql
  • 05_goco_insert_init_data.sql
  • 06_goco_insert_customer_data.sql

Optionally: Replacing the "initialuser" user

Usually we provide some initially preconfigured users (corresponding to your windows user ids) via script 06_goco_insert_customer_data.sql, so you will be able to access the application via Active Directory Single Sign-On. If this is not the case (we will mention it in the delivery mail), you need to replace the "initialuser" User ID with your Windows User ID. Replace in the following script YOUR_WINDOWS_USER_ID with your User ID and execute it:

update goco.co_employee set user_id = 'YOUR_WINDOWS_USER_ID' where user_id = 'initialuser';
commit;

Adjust context.xml of Application Server for Oracle

To configure the database connections on the Application Server to, open the file context.xml (in <Tomcat-Directory>/conf). Add the following lines directly after the element <context>.

IMPORTANT: Replace YOUR_SID both times by your Oracle SID.

    <Resource
        name="gocoTenantDs"
        factory="com.gocompliant.encryptedDs.EncryptedDataSourceFactory"
        auth="Container"
        type="javax.sql.DataSource"
        driverClassName="oracle.jdbc.OracleDriver"
        url="jdbc:oracle:thin:@localhost:1521:YOUR_SID"
        username="goco"
        password="922f591c101ab822305be286a532c196"
        maxActive="20"
        minIdle="0"
        maxIdle="0"
        minEvictableIdleTimeMillis="14400000"
        maxWait="-1" />
    <Resource
        name="quartz"
        factory="com.gocompliant.encryptedDs.EncryptedDataSourceFactory"
        auth="Container"
        type="javax.sql.DataSource"
        driverClassName="oracle.jdbc.OracleDriver"
        url="jdbc:oracle:thin:@localhost:1521:YOUR_SID"
        username="quartz"
        password="2259b33c5588618ee0742b2ced9998e6"
        maxActive="20"
        minIdle="0"
        maxIdle="0"
        minEvictableIdleTimeMillis="14400000"
        maxWait="-1" />

Installation of the software on the Application Server

Installation of the war-file

  1. Stop the Tomcat Service.
  2. Delete the directory <Tomcat-Directory>/webapps/ROOT.
  3. Download the .war file from the download area. The naming convention for the war file is as follows:
    gocompliant-ics-{version}-{login-mechanism}-{http-or-https}.war
    The normally installed variants are:
    Login-Mechanism: ssoad (Single Sign-On via Active Directory)
    HTTP-oder-HTTPS-Variant: httpallowed
    We recommend to start with this variant, e.g. gocompliant-ics-2.4.0-ssoad-httpallowed.war
  4. Copy the .war file to<Tomcat-Directory>/webapps, e.g. C:\Program Files\Apache Software Foundation\Tomcat 9.0\webapps
  5. Rename the .war file to ROOT.war
  6. Restart the Tomcat Service
  7. Brose to http://localhost orhttp://localhost:8080 and verify that the GoCompliant application is running. If you have chosen an SSO login mechanism you should be logged in automatically (your Windows User ID needs to correspond with one of the preconfigured users on the database). Otherwise you should see a login page.

After the installation

Before officially rolling out the application you should configure the most important system parameters. A descriptino of these parameters can be found in section Systemparameter.

If you would like to use the LDAP (Active Directory) synchronisation, you will find a description of the relevant parameters in section LDAP (Active Directory) Systemparameter.

If you want to run your application via https and have a corresponding certificate, please follow these instructions: Wie kann ich HTTPS einrichten bzw. erzwingen?


Verwandte Seiten