# Creating EPSG database for HSQL

The following instructions are for module maintainers. They explain how to update the `epsg-hsql` plugin with latest EPSG database. Users don't need to read this page - the `epsg-hsql` plugin should work "out of the box" without any particular action required.

The following gives some hints for creating a HSQL database and populate it with EPSG data from the SQL scripts.

- Copy the `sqltool.rc` file in the developper's home directory and edit it URL path provided in this file.

- Prepare a copy of EPSG's SQL scripts and modify them for HSQL syntax. The following Ant file can be used for this purpose. As a safety, add the following lines at the end of every scripts:

  >     COMMIT;
  >     SHUTDOWN;

- There is one change that the above Ant script doesn't performs. Column creation in the form "`columnName UNIQUE`" must move the "`UNIQUE`" keyword at the end of table creation in the form "`UNIQUE (columnName)`". This change needs to be done by hand in the "`EPSG_Tables.sql`" file. Example:

  > Before | After
  > --- | ---
  >     CREATE CACHED TABLE epsg_change (
  >     change_id     DOUBLE PRECISION NOT NULL UNIQUE,
  >     report_date   DATE NOT NULL,
  >     action        VARCHAR);
  >     CREATE CACHED TABLE epsg_change (
  >     change_id     DOUBLE PRECISION NOT NULL,
  >     report_date   DATE NOT NULL,
  >     action        VARCHAR,
  >     UNIQUE(CHANGE_ID) );

- Creates the database using the following commands:

  >     java org.hsqldb.util.SqlTool --autoCommit EPSG-admin EPSG_Tables.sql
  >     java org.hsqldb.util.SqlTool --autoCommit EPSG-admin EPSG_Data.sql
  >     java org.hsqldb.util.SqlTool --autoCommit EPSG-admin EPSG_FKeys.sql
  >     java org.hsqldb.util.SqlTool --autoCommit EPSG-admin dump.sql

  where "`dump.sql`" is a small file containing the following instructions:

  >     SCRIPT 'EPSG.sql';
  >     SHUTDOWN COMPACT;

- Edit the `EPSG.sql` script as below:

  - Remove the `SET DELAY 60` statement; 60 is already the default.
  - Remove the `CREATE USER "SA"` and `GRANT DBA TO SA` statements. This user will exists before the script is run.
  - Remove the `CREATE SCHEMA PUBLIC` and `SET SCHEMA PUBLIC` statements. This schema will exists before the script is run.
  - In the table `EPSG_DATUM`, change the type of column `REALIZATION_EPOCH` to `INTEGER`.
  - Copy all `ALTER TABLE … FOREIGN KEY` statements to the end of the script.
  - Remove all `CONSTRAINT … FOREIGN KEY` from the `CREATE TABLE` statements at the begining of the script, and move them as `ALTER TABLE … FOREIGN KEY` statements at the end of the script.
  - Add the `CREATE INDEX …` statements provided here at the end of file. You may remove the comments, semi-collons and extra spaces for saving spaces (they appears in the above file only for readability).
  - Add `SHUTDOWN COMPACT` statement at the end of file.

- Run the following commands:

  >     java org.geotools.referencing.factory.epsg.Compactor

- Copy the file created in the above step into the `org.geotools.referencing.factory.epsg` package directory using "`EPSG.sql`" filename.

- Edit the version number in the `FactoryOnHSQL.VERSION` field.

- Finally, create the JAR file as usual (`mvn install`), which should includes the `EPSG.sql` script.