.. _Database:

Database
========

MapStore supports 3 types of database:

- `H2 Database Engine <https://www.h2database.com/html/main.html>`_
- `PostgreSQL <https://www.postgresql.org/>`_
- `Oracle Database <https://www.oracle.com/database/>`_

In a production environment, the H2 database is NOT RECOMMENDED.

In the following section the user can see how to configure a postgreSQL database ready for production.
For oracle configuration or for other advanced options please refer the `official documentation <https://mapstore.readthedocs.io/en/latest/developer-guide/database-setup/>`_

Creation
--------

To create and configure the database for MapStore in postgreSQL, you will need to:


- Create the schema and the tables, using the script available `here <https://github.com/geosolutions-it/geostore/tree/master/doc/sql>`_

.. code-block:: bash

  # Usually you have a postgres user that can execute these commands
  sudo su postgres

  # Create the database and user
  psql -U postgres -c "CREATE DATABASE geostore;"
  psql -U postgres -c "CREATE user geostore LOGIN PASSWORD 'geostore' NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE;"

  # Download the sql script files
  wget https://raw.githubusercontent.com/geosolutions-it/geostore/master/doc/sql/001_setup_db.sql
  wget https://raw.githubusercontent.com/geosolutions-it/geostore/master/doc/sql/002_create_schema_postgres.sql

  # execute the SQL scripts
  psql -U postgres -d geostore -f ./001_setup_db.sql
  psql -h localhost -U geostore -d geostore -f ./002_create_schema_postgres.sql


- **make you sure** that the user geostore has access to the database from the address of MapStore application. You can give permission by editing `pg_hba.conf <https://www.postgresql.org/docs/9.1/auth-pg-hba-conf.html>`_

For instance, if your MapStore is hosted on 192.168.12.10, you can provide the access adding this line to ``pg_hba.conf``

.. code-block:: text

  # TYPE  DATABASE        USER            ADDRESS                 METHOD
  host    geostore        geostore        192.168.12.10/32        md5



Configuration
-------------

In the ``WEB-INF/classes`` directory of ``mapstore.war`` you can find a file named ``geostore-datasource-ovr.properties`` that contains the database configuration.

Here a sample setup for postgreSQL:

.. code-block:: properties

  # Setup driver and dialect for PostgreSQL database
  geostoreDataSource.driverClassName=org.postgresql.Driver
  geostoreVendorAdapter.databasePlatform=org.hibernate.dialect.PostgreSQLDialect

  # Connection parameters
  geostoreDataSource.url=jdbc:postgresql://192.168.12.100:5432/geostore
  geostoreDataSource.username=geostore
  geostoreDataSource.password=geostore
  geostoreEntityManagerFactory.jpaPropertyMap[hibernate.default_schema]=geostore

  # Automatic create-update database mode
  geostoreEntityManagerFactory.jpaPropertyMap[hibernate.hbm2ddl.auto]=validate

  # Other options
  geostoreVendorAdapter.generateDdl=true
  geostoreVendorAdapter.showSql=false

.. note ::
  Editing these files directly is not suggested because it may be cancelled on the next re-deploy of ``mapstore.war`` (for instance due to an update).

  For this reason MapStore allows you to configure a *system property* named ``geostore-ovr`` to pass to the JVM with the path to the file to use for the configuration above.
  In the next sections the externalization will be explained more in detail.

  .. code-block:: bash

      # here the path to the ovr file
      GEOSTORE_OVR_FILE=file:///var/lib/tomcat/conf/geostore-ovr.properties

      # add the env. variable 'geostore-ovr' to JAVA_OPTS
      JAVA_OPTS="-Dgeostore-ovr=$GEOSTORE_OVR_FILE [other opts]"