waarp/WaarpR66 — pull request #111 by bcarlin (2014-06-17 17:38:54+00:00)
Title: Fixes model upgrade queries for Postgresql

It is quite impossible to create a new instance that uses a PostgreSQL database with the few latest versions :

the "initidb" command goes well :
```
$ java -cp 'lib/WaarpR66-2.4.28.jar:lib/*' org.waarp.openr66.server.ServerInitDatabase client.xml -initdb
...snip...
CREATE TABLE MULTIPLEMONITOR (COUNTCONFIG INTEGER NOT NULL , COUNTHOST INTEGER NOT NULL , COUNTRULE INTEGER NOT NULL , HOSTID VARCHAR(8096) PRIMARY KEY )
CREATE TABLE CONFIGURATION (READGLOBALLIMIT BIGINT NOT NULL , WRITEGLOBALLIMIT BIGINT NOT NULL , READSESSIONLIMIT BIGINT NOT NULL , WRITESESSIONLIMIT BIGINT NOT NULL , DELAYLIMIT BIGINT NOT NULL , UPDATEDINFO INTEGER NOT NULL , HOSTID VARCHAR(8096) PRIMARY KEY )
CREATE TABLE HOSTCONFIG (BUSINESS TEXT NOT NULL , ROLES TEXT NOT NULL , ALIASES TEXT NOT NULL , OTHERS TEXT NOT NULL , UPDATEDINFO INTEGER NOT NULL , HOSTID VARCHAR(8096) PRIMARY KEY )
CREATE TABLE HOSTS (ADDRESS VARCHAR(8096) NOT NULL , PORT INTEGER NOT NULL , ISSSL BOOLEAN NOT NULL , HOSTKEY BYTEA NOT NULL , ADMINROLE BOOLEAN NOT NULL , ISCLIENT BOOLEAN NOT NULL , ISACTIVE BOOLEAN NOT NULL , ISPROXIFIED BOOLEAN NOT NULL , UPDATEDINFO INTEGER NOT NULL , HOSTID VARCHAR(8096) PRIMARY KEY )
CREATE TABLE RULES (HOSTIDS TEXT , MODETRANS INTEGER , RECVPATH VARCHAR(8096) , SENDPATH VARCHAR(8096) , ARCHIVEPATH VARCHAR(8096) , WORKPATH VARCHAR(8096) , RPRETASKS TEXT , RPOSTTASKS TEXT , RERRORTASKS TEXT , SPRETASKS TEXT , SPOSTTASKS TEXT , SERRORTASKS TEXT , UPDATEDINFO INTEGER , IDRULE VARCHAR(8096) PRIMARY KEY )
CREATE TABLE RUNNER (GLOBALSTEP INTEGER NOT NULL , GLOBALLASTSTEP INTEGER NOT NULL , STEP INTEGER NOT NULL , RANK INTEGER NOT NULL , STEPSTATUS CHAR(3) NOT NULL , RETRIEVEMODE BOOLEAN NOT NULL , FILENAME VARCHAR(8096) NOT NULL , ISMOVED BOOLEAN NOT NULL , IDRULE VARCHAR(8096) NOT NULL , BLOCKSZ INTEGER NOT NULL , ORIGINALNAME VARCHAR(8096) NOT NULL , FILEINFO TEXT NOT NULL , TRANSFERINFO TEXT NOT NULL , MODETRANS INTEGER NOT NULL , STARTTRANS TIMESTAMP NOT NULL , STOPTRANS TIMESTAMP NOT NULL , INFOSTATUS CHAR(3) NOT NULL , UPDATEDINFO INTEGER NOT NULL , OWNERREQ VARCHAR(8096) NOT NULL , REQUESTER VARCHAR(8096) NOT NULL , REQUESTED VARCHAR(8096) NOT NULL , SPECIALID BIGINT NOT NULL , CONSTRAINT runner_pk PRIMARY KEY (OWNERREQ,REQUESTER,REQUESTED,SPECIALID))
CREATE INDEX IDX_RUNNER ON RUNNER (STARTTRANS, OWNERREQ, STEPSTATUS, UPDATEDINFO, GLOBALSTEP, INFOSTATUS, SPECIALID)
CREATE SEQUENCE RUNSEQ MINVALUE -9223372036854775807 START WITH -9223372036854775807
End creation
Load done
```

The following happens when trying to submit a transfer:
```sh
$ java -cp 'lib/WaarpR66-2.4.28.jar:lib/*' org.waarp.openr66.client.SubmitTransfer client.xml -to foo -file install.log -rule test
17/06/2014/16:12:55.534 INFO [ROOT] [main] Default level: INFO
17/06/2014/16:12:55.975 INFO [org.waarp.openr66.configuration.FileBasedConfiguration] [main] org.waarp.openr66.configuration.FileBasedConfiguration.loadDatabase(FileBasedConfiguration.java:1717) : Database connection: Admin:true NoCommitAdmin:true
17/06/2014/16:12:55.976 INFO [org.waarp.openr66.configuration.FileBasedConfiguration] [main] org.waarp.openr66.configuration.FileBasedConfiguration.loadDatabase(FileBasedConfiguration.java:1721) : DefaultTransactionIsolation: 2 MaxConnections: 8192 MaxStatements: 0
1.1.0 to 2.4.13? true
CREATE TABLE HOSTCONFIG (BUSINESS TEXT NOT NULL , ROLES TEXT NOT NULL , ALIASES TEXT NOT NULL , OTHERS TEXT NOT NULL , UPDATEDINFO INTEGER NOT NULL , HOSTID VARCHAR(8096) PRIMARY KEY )
17/06/2014/16:12:56.004 ERROR [org.waarp.common.database.DbRequest] [main] org.waarp.common.database.DbRequest.query(DbRequest.java:190) : SQL Exception Request:CREATE TABLE HOSTCONFIG (BUSINESS TEXT NOT NULL , ROLES TEXT NOT NULL , ALIASES TEXT NOT NULL , OTHERS TEXT NOT NULL , UPDATEDINFO INTEGER NOT NULL , HOSTID VARCHAR(8096) PRIMARY KEY ) ERREUR: la relation « hostconfig » existe déjà
17/06/2014/16:12:56.005 ERROR [org.waarp.common.database.DbSession] [main] org.waarp.common.database.DbSession.error(DbSession.java:340) : SQLException: ERREUR: la relation « hostconfig » existe déjà SQLState: 42P07VendorError: 0
org.waarp.common.database.exception.WaarpDatabaseSqlException: SQL Exception Request:CREATE TABLE HOSTCONFIG (BUSINESS TEXT NOT NULL , ROLES TEXT NOT NULL , ALIASES TEXT NOT NULL , OTHERS TEXT NOT NULL , UPDATEDINFO INTEGER NOT NULL , HOSTID VARCHAR(8096) PRIMARY KEY )
at org.waarp.common.database.DbRequest.query(DbRequest.java:194)
at org.waarp.openr66.database.model.DbModelPostgresql.upgradeDb(DbModelPostgresql.java:335)
at org.waarp.openr66.database.model.DbModelPostgresql.needUpgradeDb(DbModelPostgresql.java:466)
at org.waarp.openr66.server.ServerInitDatabase.upgradedb(ServerInitDatabase.java:281)
at org.waarp.openr66.configuration.FileBasedConfiguration.loadDatabase(FileBasedConfiguration.java:1751)
at org.waarp.openr66.configuration.FileBasedConfiguration.setSubmitClientConfigurationFromXml(FileBasedConfiguration.java:2363)
at org.waarp.openr66.client.AbstractTransfer.getParams(AbstractTransfer.java:230)
at org.waarp.openr66.client.SubmitTransfer.main(SubmitTransfer.java:131)
Caused by: org.postgresql.util.PSQLException: ERREUR: la relation « hostconfig » existe déjà
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2161)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1890)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:559)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:403)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:331)
at org.waarp.common.database.DbRequest.query(DbRequest.java:186)
... 7 more
17/06/2014/16:12:56.026 ERROR [org.waarp.openr66.server.ServerInitDatabase] [main] org.waarp.openr66.server.ServerInitDatabase.upgradedb(ServerInitDatabase.java:286) : Database schema is not up to date: you must run ServerInitDatabase with the option -upgradeDb
17/06/2014/16:12:56.026 ERROR [org.waarp.openr66.configuration.FileBasedConfiguration] [main] org.waarp.openr66.configuration.FileBasedConfiguration.setSubmitClientConfigurationFromXml(FileBasedConfiguration.java:2364) : Cannot load Database configuration
17/06/2014/16:12:56.027 ERROR [org.waarp.openr66.client.SubmitTransfer] [main] org.waarp.openr66.client.AbstractTransfer.getParams(AbstractTransfer.java:232) : Needs a correct configuration file as first argument
17/06/2014/16:12:56.029 ERROR [org.waarp.openr66.client.SubmitTransfer] [main] org.waarp.openr66.client.SubmitTransfer.main(SubmitTransfer.java:132) : Wrong initialization
Wrong initialization
17/06/2014/16:12:56.345 INFO [org.waarp.common.database.DbSession] [main] org.waarp.common.database.DbSession.disconnect(DbSession.java:492) : Current cached connection: -1
```

And when trying to upgrade the database model:
```
$ java -cp 'lib/WaarpR66-2.4.28.jar:lib/*' org.waarp.openr66.server.ServerInitDatabase client.xml -upgradeDb
17/06/2014/16:13:23.279 INFO [ROOT] [main] Default level: INFO
17/06/2014/16:13:23.734 INFO [org.waarp.openr66.configuration.FileBasedConfiguration] [main] org.waarp.openr66.configuration.FileBasedConfiguration.loadDatabase(FileBasedConfiguration.java:1717) : Database connection: Admin:true NoCommitAdmin:true
17/06/2014/16:13:23.735 INFO [org.waarp.openr66.configuration.FileBasedConfiguration] [main] org.waarp.openr66.configuration.FileBasedConfiguration.loadDatabase(FileBasedConfiguration.java:1721) : DefaultTransactionIsolation: 2 MaxConnections: 8192 MaxStatements: 0
1.1.0 to 2.4.13? true
CREATE TABLE HOSTCONFIG (BUSINESS TEXT NOT NULL , ROLES TEXT NOT NULL , ALIASES TEXT NOT NULL , OTHERS TEXT NOT NULL , UPDATEDINFO INTEGER NOT NULL , HOSTID VARCHAR(8096) PRIMARY KEY )
17/06/2014/16:13:23.760 ERROR [org.waarp.common.database.DbRequest] [main] org.waarp.common.database.DbRequest.query(DbRequest.java:190) : SQL Exception Request:CREATE TABLE HOSTCONFIG (BUSINESS TEXT NOT NULL , ROLES TEXT NOT NULL , ALIASES TEXT NOT NULL , OTHERS TEXT NOT NULL , UPDATEDINFO INTEGER NOT NULL , HOSTID VARCHAR(8096) PRIMARY KEY ) ERREUR: la relation « hostconfig » existe déjà
17/06/2014/16:13:23.762 ERROR [org.waarp.common.database.DbSession] [main] org.waarp.common.database.DbSession.error(DbSession.java:340) : SQLException: ERREUR: la relation « hostconfig » existe déjà SQLState: 42P07VendorError: 0
org.waarp.common.database.exception.WaarpDatabaseSqlException: SQL Exception Request:CREATE TABLE HOSTCONFIG (BUSINESS TEXT NOT NULL , ROLES TEXT NOT NULL , ALIASES TEXT NOT NULL , OTHERS TEXT NOT NULL , UPDATEDINFO INTEGER NOT NULL , HOSTID VARCHAR(8096) PRIMARY KEY )
at org.waarp.common.database.DbRequest.query(DbRequest.java:194)
at org.waarp.openr66.database.model.DbModelPostgresql.upgradeDb(DbModelPostgresql.java:335)
at org.waarp.openr66.database.model.DbModelPostgresql.needUpgradeDb(DbModelPostgresql.java:466)
at org.waarp.openr66.server.ServerInitDatabase.upgradedb(ServerInitDatabase.java:281)
at org.waarp.openr66.configuration.FileBasedConfiguration.loadDatabase(FileBasedConfiguration.java:1751)
at org.waarp.openr66.configuration.FileBasedConfiguration.setConfigurationInitDatabase(FileBasedConfiguration.java:1975)
at org.waarp.openr66.server.ServerInitDatabase.main(ServerInitDatabase.java:120)
Caused by: org.postgresql.util.PSQLException: ERREUR: la relation « hostconfig » existe déjà
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2161)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1890)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:559)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:403)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:331)
at org.waarp.common.database.DbRequest.query(DbRequest.java:186)
... 6 more
17/06/2014/16:13:23.789 ERROR [org.waarp.openr66.server.ServerInitDatabase] [main] org.waarp.openr66.server.ServerInitDatabase.upgradedb(ServerInitDatabase.java:286) : Database schema is not up to date: you must run ServerInitDatabase with the option -upgradeDb
17/06/2014/16:13:23.789 ERROR [org.waarp.openr66.configuration.FileBasedConfiguration] [main] org.waarp.openr66.configuration.FileBasedConfiguration.setConfigurationInitDatabase(FileBasedConfiguration.java:1976) : Cannot load Database configuration
17/06/2014/16:13:23.790 ERROR [org.waarp.openr66.server.ServerInitDatabase] [main] org.waarp.openr66.server.ServerInitDatabase.main(ServerInitDatabase.java:122) : Needs a correct configuration file as first argument
17/06/2014/16:13:23.894 INFO [org.waarp.common.database.DbSession] [main] org.waarp.common.database.DbSession.disconnect(DbSession.java:492) : Current cached connection: -1
```

The upgrade error also occurs when trying to upgrade "regularly" a database (e.g. from Waarp R66 v2.4.22 to v2.2.28).

This patch makes all SQL queries idempotent (as it is in `DbModelH2` and `DbModelMysql`).
The queries are compatible with PostgreSQL 8.x and 9.X, except for the creation of the table `hostconfig`: the `IF NOT EXISTS` clause has been added to `CREATE TABLE` in version 9.1 (in this case, I added a test to verify the version run by the database server).