Create DDL

[aaime]

Issue by etj
Thursday Nov 28, 2013 at 11:49 GMT
Originally opened as https://github.com/geosolutions-it/geofence/issues/84

---

It is needed that the DDL file be automatically created according to the DBMS profile.

This is used in production environments to create the schema for GeoFence DB.

[aaime]

Comment by etj
Thursday Nov 28, 2013 at 11:54 GMT

---

Note that files in https://github.com/geosolutions-it/geofence/tree/master/doc/setup/sql were created automatically by hibernate and then copied to that dir manually every time the model changed.

Also note that the Oracle Spatial directives were fixed by hand in those scripts, since the schema names created by the used hibernate version were wrong.

[etj]

Here another problem related to the creation of the schema in an Oracle DB:
http://sourceforge.net/p/geoserver/mailman/message/34502447/

During startup of geofence I see the following in my tomcat log:

17:25:09,357 ERROR SchemaUpdate:212 - Unsuccessful: create table FDOTWEBSVC.gf_layer_attributes (details_id number(19,0) not null, access_type varchar2(255 char), data_type varchar2(255 char), name varchar2(255 char) not null, primary key (details_id, name), unique (details_id, name))
17:25:09,358 ERROR SchemaUpdate:213 - ORA-02261: such unique or primary key already exists in the table

If I execute the SQL that's shown in Oracle's sqlDev I see this error output:
SQL Error: ORA-02261: such unique or primary key already exists in the table
02261. 00000 - "such unique or primary key already exists in the table"
*Cause: Self-evident.
*Action: Remove the extra key.

We should probably completely deprecate the automatic schema generation and rely on the DDL only, at least for Oracle.