Database Setup Guide

There exists 3 different Database Systems that are supported by PowerFolder: MSSQL, PostgreSQL, MySQL.

You are free to choose which database you want to use.

Install MSSQL Server

Microsoft provides a guide for installing SQL Server. Generally the guide provides the most basic steps to install SQL Server. A few changes need to be applied during the installation:

• Configuring the database collation
• Enabling mixed-mode authentication

Configuring the database collation

Enabling mixed-mode authentication

Microsoft SQL Server support Windows authentication based logins, however for PowerFolder Server it is necessary to create a separate user and password which can access the PowerFolder Server database. During the installation of SQL Server it is possible to enable mixed-mode authentication, which supports external logins with dedicated users created within the SQL Server.

Create a SQL Server database and user

1. Download/install SQL Server Management Studio to connect to the database server.
2. Right-click on Security > Logins to create a new user.
3. Right-click on Databases to create a new database and assign the new user, we've just created as an owner of the new database.

Setup the JDBC driver

PowerFolder Server needs to be loaded with the correct JDBC driver to establish a connection with the Microsoft SQL Server:

1. Download the MS JDBC drive for SQL
2. Create a sub-directory called /libs under the directory, where you installed the PowerFolder Server software, namely where the PowerFolder-Server.jar file resides.
3. Download .zip or tar.gz depending upon your OS.
4. After extraction go in the extracted folder and under sub-folder jars you will find out the file mssql-jdbc-12.8.0.jre11.jar (JRE 11 supports the openJDK version of PF server).  
5. Copy mssql-jdbc-12.8.0.jre11.jar from the package downloaded into the /libs sub-directory created in the step before.
6. Restart PowerFolder Server.

Setup the database connection

Once we added the driver for JDBC driver to connect to the SQL Server, the next step is to tell PowerFolder Server to which database to connect and which credentials to use:

1. Open the PowerFolder.config file and add the entries below. Apply the changes which are necessary to match the setup of the SQL Server database and user you performed in step three of this guide.

   hibernate.connection.url=jdbc:sqlserver://localhost:1433;databaseName=powerfolderdb;
   hibernate.connection.username=powerfolderuser
   hibernate.connection.password=powerfolderpass
   hibernate.dialect=de.dal33t.powerfolder.server.db.sql.SQLAddNVarCharDialect
   hibernate.c3p0.max_size=500
   
   

    The database.connections.max parameter can be adjusted according to the maximum number of connections your database server accepts.

2. If you want to use the database connection without SSL and self signed certificate please use the following connection string:

   hibernate.connection.url=jdbc:sqlserver://localhost:1433;databaseName=pf;encrypt=false;trustServerCertificate=false
   

3. Start PowerFolder Server. Now it should automatically fill the new database with tables and start to use it.

Install MySQL Server 

The way to install MySQL Server depends on the operating system where you are installing it. In the official MySQL documentation there are MySQL Server Installation Guides for almost all operating systems available:

• Installing MySQL on Microsoft Windows
• Installing MySQL on Linux

[mysqld]
...
character-set-server=utf8
...

[mysqld]
...
default-storage-engine=INNODB
...

[mysqld]
...
max_connections                 = 3000
max_connect_errors              = 3000
long_query_time                 = 5
...

[mysqld]
...
innodb-buffer-pool-size			= 2757M
...

Create a MySQL Database and User

1. Use your MySQL super user (the "root" account) to login with the "mysql" command line tool:

   mysql -u root -p

2. Create an empty database and a dedicated user for PowerFolder server:

   CREATE DATABASE powerfolder;
   CREATE USER 'powerfolderuser'@'localhost' IDENTIFIED BY 'powerfolderpass';

3. Grant privileges to access the new database and a password for that user (MySQL 5):

   GRANT ALL PRIVILEGES ON powerfolder.* TO 'powerfolderuser'@'localhost' IDENTIFIED BY 'powerfolderpass';

   Replace "powerfolderuser" and "powerfolderpass" with a username and password of your choice. If PowerFolder Server is not running on the same server as your MySQL database server, replace "localhost" with the hostname or IP address of the PowerFolder server.

4. Grant privileges to access the new database (MySQL 8):

   GRANT ALL PRIVILEGES ON powerfolder.* TO 'powerfolderuser'@'localhost';

Setting UTC time zone in MYSQL database

In the Sommer and Winter time changes there comes issues in the database connections and the communication between the PowerFolder-Server and the database can also be affected if following changes are not carried out:

1. Direct your database server so that it can recognize the timezone

   mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root -p mysql

2. Set now the UTCin the MYSQL console

   SET GLOBAL time_zone = UTC;

3. Set the time zone as UTC also in the MYSQL-Configuration file

   default-time-zone = 'UTC'

Setup the JDBC driver

PowerFolder Server needs to be loaded with the correct JDBC driver to establish a connection with the MySQL Database Server:

1. Download the v8.0.28 Connector/J for MySQL. (MySQL 8)
2. Download the v5.1.47 JDBC Database Driver for MySQL. (MySQL v5.X)
3. Create a sub-directory called /libs under the directory, where you installed the PowerFolder Server software, namely where the PowerFolder-Server.jar file resides.
4. Extract the mysql-connector-java-<version>-bin.jar (where <version> is the actual release number) from the package downloaded into the /libs sub-directory created in the step before.
5. Restart PowerFolder Server.

Setup the database connection

Once we added the JDBC driver to connect to the MySQL Database Server, the next step is to tell PowerFolder Server to which database to connect and which credentials to use:

1. Open the PowerFolder.config file and add the entries below. Apply the changes which are necessary to match the setup of the MySQL Database and User you performed in step three of this guide.

   hibernate.connection.url=jdbc:mysql://localhost:3306/powerfolder?serverTimezone=UTC&useLegacyDatetimeCode=false 
   hibernate.connection.username=powerfolderuser
   hibernate.connection.password=powerfolderpass
   hibernate.dialect=org.hibernate.dialect.MySQL5InnoDBDialect
   hibernate.c3p0.max_size=500
   
   * Without SSL:
   hibernate.connection.url=jdbc:mysql://localhost:3306/powerfolder?serverTimezone=UTC&useLegacyDatetimeCode=false&useSSL=false

    The database.connections.max parameter can be adjusted according to the maximum number of connections your database server accepts.

2. Start PowerFolder Server. Now it should automatically fill the new database with tables and start to use it.

 After the changes in the MYSQL-Console, Configuration and PowerFolder.config please restart the MSQL-Service and PowerFolder-Server also.

Install PostgreSQL Server

The way to install PostgreSQL Server depends on the operating system where you are installing it. In the official PostgreSQL documentation there are guides for the installation available.

Please make sure that the PostgreSQL Server will listen on the hostname and port, which you are going to configure later on in the PowerFolder configuration. Furthermore you've to allow the user created below to access the database created below in the PostgreSQL network configuration.

Create a PostgreSQL Database and User

1. Use your PostgreSQL super user (the "postgres" account) to login with the "psql" command line tool:

   psql -h localhost -p 5432 -U postgres

2. Create a new user account:

   CREATE USER powerfolderuser WITH PASSWORD 'powerfolderpass';

    You might need to use backslashes for escaping special characters in the password.
    Replace "powerfolderuser" and "powerfolderpass" with a username and password of your choice.
    

3. Create an empty database:

   CREATE DATABASE powerfolder;

4. Create a user privileged to access the new database and a password for that user:

   GRANT ALL PRIVILEGES ON DATABASE powerfolder to powerfolderuser;

Setup the JDBC driver

PowerFolder Server needs to be loaded with the correct JDBC driver to establish a connection with the PostgreSQL Database Server:

1. Download the JDBC Database Driver for PostgreSQL.
2. Create a sub-directory called /libs under the directory, where you installed the PowerFolder Server software, namely where the PowerFolder-Server.jar file resides.
3. Move the postgresql-<version>.jdbc41.jar (where <version> is the actual release number) to the /libs sub-directory created in the step before.
4. Restart PowerFolder Server.

Setup the database connection

Once we added the JDBC driver to connect to the PostgreSQL Database Server, the next step is to tell PowerFolder Server to which database to connect and which credentials to use:

1. Open the PowerFolder.config file and add the entries below. Apply the changes which are necessary to match the setup of the PostgreSQL Database and User you performed in step three of this guide.

   hibernate.connection.url=jdbc:postgresql://localhost:5432/powerfolder
   hibernate.connection.username=powerfolderuser
   hibernate.connection.password=powerfolderpass
   hibernate.dialect=org.hibernate.dialect.PostgreSQLDialect
   hibernate.c3p0.max_size=500

    The database.connections.max parameter can be adjusted according to the maximum number of connections your database server accepts.

2. Start PowerFolder Server. Now it should automatically fill the new database with tables and start to use it.

Install MariaDB Server

Depending on systems the MariaDB can be installed using following instructions:

• Installing MariaDB on Microsoft Windows
• Installing MariaDB on Linux or Linux guide

Please follow the steps as stated in the documentation and you can also repeat the steps of installation of MySQL for installing a MariaDB Server.

collation-server = utf8mb4_general_ci
init-connect = 'SET NAMES utf8mb4'
character-set-server = utf8mb4
SET FOREIGN_KEY_CHECKS=0 

Setup the MariaDB Connector/J

PowerFolder Server needs to be loaded with the correct MariaDB Connector/J to establish a connection with the MySQL Database Server:

1. Download the java file MariaDB Connector/J (v 2.7.3)
2. Create a sub-directory called /libs under the directory, where you installed the PowerFolder Server software, namely where the PowerFolder-Server.jar file resides.
3. Extract the mariadb-java-client-<version> (where <version> is the actual release number) from the package downloaded into the /libs sub-directory created in the step before.
4. Restart PowerFolder Server.

Setup the database connection

Once we added the JDBC driver to connect to the MariaDB Database Server, the next step is to tell PowerFolder Server to which database to connect and which credentials to use:

1. Open the PowerFolder.config file and add the entries below. Apply the changes which are necessary to match the setup of the Maria Database and User you performed in step three of this guide.

   hibernate.connection.url=jdbc:mysql://localhost:3306/powerfolder
   hibernate.connection.username=powerfolderuser
   hibernate.connection.password=powerfolderpass
   hibernate.dialect=org.hibernate.dialect.MySQL5InnoDBDialect
   hibernate.c3p0.max_size=500

2.  The database.connections.max parameter can be adjusted according to the maximum number of connections your database server accepts.

3. Start PowerFolder Server. Now it should automatically fill the new database with tables and start to use it.

 After the changes in the MYSQL-Console, Configuration and PowerFolder.config please restart the MSQL-Service and PowerFolder-Server also.