* @throws SchemaPermissionException
*/
public void initializeSystemTables() throws SQLException, SchemaPermissionException
{
// check if the system tables exist
ConnectionWrapper cw = connectionPool.getConnectionWrapper();
try
{
int existingSchema = 0;
// check if the version table exist
if (tableExists(Defaults.SCHEMA_VERSION_TABLENAME, cw))
{
// get the existing schema version
String query = "SELECT VERSION FROM " + Defaults.SCHEMA_VERSION_TABLENAME;
PreparedStatement ps = cw.prepareStatement(query);
Tools.logFine(ps);
ResultSet rs = ps.executeQuery();
if (rs.next())
{
existingSchema = rs.getInt(1);
}
rs.close();
ps.close();
}
else
{
String createString = "CREATE TABLE " + Defaults.SCHEMA_VERSION_TABLENAME + " (VERSION "
+ adapter.getIntegerTypeKeyword() + ")";
PreparedStatement ps = cw.prepareStatement(createString);
Tools.logFine(ps);
ps.execute();
ps.close();
if (adapter.isRequiresCommitAfterTableCreation())
{
cw.commit();
}
// insert the current version
String commandString = "INSERT INTO " + Defaults.SCHEMA_VERSION_TABLENAME + " (VERSION ) values (?)";
ps = cw.prepareStatement(commandString);
ps.setInt(1, schemaTypeVersion);
Tools.logFine(ps);
ps.execute();
ps.close();
}
if (existingSchema < schemaTypeVersion)
{
upgradeSchema(existingSchema, cw);
}
else if (existingSchema > schemaTypeVersion)
{
throw new SQLException("Database schema is version " + existingSchema + " but Conserve is version "
+ schemaTypeVersion);
}
if (!tableExists(Defaults.IS_A_TABLENAME, cw))
{
if (!this.createSchema)
{
throw new SchemaPermissionException(Defaults.IS_A_TABLENAME
+ " does not exist, but can't create it.");
}
String createString = "CREATE TABLE " + Defaults.IS_A_TABLENAME + " (SUPERCLASS "
+ adapter.getVarCharIndexed() + ",SUBCLASS " + adapter.getVarCharIndexed() + ")";
PreparedStatement ps = cw.prepareStatement(createString);
Tools.logFine(ps);
ps.execute();
ps.close();
// create an index on the superclass name, since this is the
// one we
// will be searching for most frequently
String commandString = "CREATE INDEX " + Defaults.IS_A_TABLENAME + "_SUPERCLASS_INDEX on "
+ Defaults.IS_A_TABLENAME + "(SUPERCLASS" + adapter.getKeyLength() + ")";
ps = cw.prepareStatement(commandString);
Tools.logFine(ps);
ps.execute();
ps.close();
}
if (!tableExists(Defaults.HAS_A_TABLENAME, cw))
{
if (!this.createSchema)
{
throw new SchemaPermissionException(Defaults.HAS_A_TABLENAME
+ " does not exist, but can't create it.");
}
String commandString = "CREATE TABLE " + Defaults.HAS_A_TABLENAME + " (OWNER_TABLE "
+ adapter.getVarCharIndexed() + ", OWNER_ID " + adapter.getLongTypeKeyword() + ", "
+ Defaults.RELATION_NAME_COL + " " + adapter.getVarCharKeyword() + ", PROPERTY_TABLE "
+ adapter.getVarCharIndexed() + ", PROPERTY_ID " + adapter.getLongTypeKeyword()
+ ", PROPERTY_CLASS " + adapter.getVarCharIndexed() + ")";
PreparedStatement ps = cw.prepareStatement(commandString);
Tools.logFine(ps);
ps.execute();
ps.close();
// create an index on the tablename/id combinations, since
// this is
// the one we
// will be searching for most frequently
commandString = "CREATE INDEX " + Defaults.HAS_A_TABLENAME + "_OWNER_INDEX on "
+ Defaults.HAS_A_TABLENAME + "(OWNER_TABLE" + adapter.getKeyLength() + ",OWNER_ID)";
ps = cw.prepareStatement(commandString);
Tools.logFine(ps);
ps.execute();
ps.close();
commandString = "CREATE INDEX " + Defaults.HAS_A_TABLENAME + "_PROPERTY_INDEX on "
+ Defaults.HAS_A_TABLENAME + "(PROPERTY_TABLE" + adapter.getKeyLength() + ",PROPERTY_ID)";
ps = cw.prepareStatement(commandString);
Tools.logFine(ps);
ps.execute();
ps.close();
}
if (!tableExists(Defaults.ARRAY_TABLE_NAME, cw))
{
if (!this.createSchema)
{
throw new SchemaPermissionException(Defaults.ARRAY_TABLE_NAME
+ " does not exist, but can't create it.");
}
if (adapter.isSupportsIdentity())
{
PreparedStatement ps = cw
.prepareStatement("CREATE TABLE " + Defaults.ARRAY_TABLE_NAME + " (" + Defaults.ID_COL + " "
+ adapter.getIdentity() + " PRIMARY KEY, " + Defaults.COMPONENT_TABLE_COL + " "
+ adapter.getVarCharIndexed() + ", " + Defaults.COMPONENT_CLASS_COL + " "
+ adapter.getVarCharIndexed() + " )");
Tools.logFine(ps);
ps.execute();
ps.close();
}
else
{
// the adapter does not support identity
// check if we can use a trigger
if (adapter.isSupportsTriggers())
{
// create the table as usual
PreparedStatement ps = cw.prepareStatement("CREATE TABLE " + Defaults.ARRAY_TABLE_NAME + " ("
+ Defaults.ID_COL + " " + adapter.getLongTypeKeyword() + " PRIMARY KEY, "
+ Defaults.COMPONENT_TABLE_COL + " " + adapter.getVarCharIndexed() + ", "
+ Defaults.COMPONENT_CLASS_COL + " " + adapter.getVarCharIndexed() + " )");
Tools.logFine(ps);
ps.execute();
ps.close();
createTriggeredSequence(cw, Defaults.ARRAY_TABLE_NAME);
}
else
{
throw new RuntimeException(
"Database engines without both autoincrements and triggers are not supported at this time.");
}
}
// create an index on the id, as this is the one we
// will be searching for most frequently
String commandString = "CREATE INDEX " + Defaults.ARRAY_TABLE_NAME + "_INDEX on "
+ Defaults.ARRAY_TABLE_NAME + "(" + Defaults.ID_COL + ")";
PreparedStatement ps = cw.prepareStatement(commandString);
Tools.logFine(ps);
ps.execute();
ps.close();
}
if (!tableExists(Defaults.ARRAY_MEMBER_TABLE_NAME_ARRAY, cw))
{
if (!this.createSchema)
{
throw new SchemaPermissionException(Defaults.ARRAY_MEMBER_TABLE_NAME_ARRAY
+ " does not exist, but can't create it.");
}
if (adapter.isSupportsIdentity())
{
StringBuilder create = new StringBuilder("CREATE TABLE ");
create.append(Defaults.ARRAY_MEMBER_TABLE_NAME_ARRAY);
create.append("(");
create.append(Defaults.ID_COL);
create.append(" " + adapter.getIdentity() + " PRIMARY KEY, ");
create.append(Defaults.ARRAY_POSITION);
create.append(" INT, ");
create.append(Defaults.COMPONENT_CLASS_COL);
create.append(" ");
create.append(adapter.getVarCharIndexed());
create.append(", ");
create.append(Defaults.VALUE_COL);
create.append(" ");
create.append(adapter.getLongTypeKeyword());
create.append(",");
create.append(Defaults.ARRAY_MEMBER_ID);
create.append(" ");
create.append(adapter.getLongTypeKeyword());
create.append(", FOREIGN KEY(");
create.append(Defaults.ARRAY_MEMBER_ID);
create.append(") REFERENCES ");
create.append(Defaults.ARRAY_TABLE_NAME);
create.append("(");
create.append(Defaults.ID_COL);
create.append("))");
PreparedStatement ps = cw.prepareStatement(create.toString());
Tools.logFine(ps);
ps.execute();
ps.close();
}
else
{
// the adapter does not support identity
// check if we can use a trigger
if (adapter.isSupportsTriggers())
{
// create the table as usual
StringBuilder create = new StringBuilder("CREATE TABLE ");
create.append(Defaults.ARRAY_MEMBER_TABLE_NAME_ARRAY);
create.append("(");
create.append(Defaults.ID_COL);
create.append(" ");
create.append(adapter.getLongTypeKeyword());
create.append(" PRIMARY KEY, ");
create.append(Defaults.ARRAY_POSITION);
create.append(" INT, ");
create.append(Defaults.COMPONENT_CLASS_COL);
create.append(" ");
create.append(adapter.getVarCharIndexed());
create.append(", ");
create.append(Defaults.VALUE_COL);
create.append(" ");
create.append(adapter.getLongTypeKeyword());
create.append(",");
create.append(Defaults.ARRAY_MEMBER_ID);
create.append(" ");
create.append(adapter.getLongTypeKeyword());
create.append(", FOREIGN KEY(");
create.append(Defaults.ARRAY_MEMBER_ID);
create.append(") REFERENCES ");
create.append(Defaults.ARRAY_TABLE_NAME);
create.append("(");
create.append(Defaults.ID_COL);
create.append("))");
String createString = create.toString();
PreparedStatement ps = cw.prepareStatement(createString);
Tools.logFine(ps);
ps.execute();
ps.close();
// create the triggered sequence
createTriggeredSequence(cw, Defaults.ARRAY_MEMBER_TABLE_NAME_ARRAY);
}
else
{
throw new RuntimeException(
"Database engines without both autoincrements and triggers are not supported at this time.");
}
}
}
// commit, return connection to pool
cw.commitAndDiscard();
}
catch (SQLException e)
{
cw.rollbackAndDiscard();
throw e;
}
}