/*
* GeoTools - The Open Source Java GIS Toolkit
* http://geotools.org
*
* (C) 2002-2008, Open Source Geospatial Foundation (OSGeo)
*
* This library is free software; you can redistribute it and/or
* modify it under the terms of the GNU Lesser General
* License as published by the Free Software Foundation;
* version 2.1 of the License.
*
* This library is distributed in the hope that it will be useful,
* but WITHOUT ANY WARRANTY; without even the implied warranty of
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
* Lesser General License for more details.
*/
package org.geotools.data.db2;
import java.sql.Connection;
import java.sql.SQLException;
import org.geotools.jdbc.JDBCPrimaryKeyTestSetup;
public class DB2PrimaryKeyTestSetup extends JDBCPrimaryKeyTestSetup {
protected DB2PrimaryKeyTestSetup() {
super(new DB2TestSetup());
}
@Override
protected void createAutoGeneratedPrimaryKeyTable() throws Exception {
Connection con = getDataSource().getConnection();
String stmt = "create table "+DB2TestUtil.SCHEMA_QUOTED+
".\"auto\" (\"key\" int generated always as identity (start with 1, increment by 1),\"name\" varchar(255), \"geom\" DB2GSE.ST_GEOMETRY, primary key (\"key\"))";
con.prepareStatement(stmt ).execute();
DB2Util.executeRegister(DB2TestUtil.SCHEMA, "auto", "geom", DB2TestUtil.SRSNAME, con);
con.prepareStatement( "INSERT INTO "+DB2TestUtil.SCHEMA_QUOTED+".\"auto\" (\"name\",\"geom\") VALUES ('one',NULL)" ).execute();
con.prepareStatement( "INSERT INTO "+DB2TestUtil.SCHEMA_QUOTED+".\"auto\" (\"name\",\"geom\") VALUES ('two',NULL)" ).execute();
con.prepareStatement( "INSERT INTO "+DB2TestUtil.SCHEMA_QUOTED+".\"auto\" (\"name\",\"geom\") VALUES ('three',NULL)" ).execute();
con.close();
}
private String getSquenceName() {
return "seq_key_SEQUENCE";
}
private String getSquenceNameQuoted() {
return DB2TestUtil.SCHEMA_QUOTED+".\""+getSquenceName()+"\"";
}
@Override
protected void createSequencedPrimaryKeyTable() throws Exception {
Connection con = getDataSource().getConnection();
con.prepareStatement("CREATE SEQUENCE "+getSquenceNameQuoted()+ " AS INTEGER start with 1").execute();
con.prepareStatement("create table "+DB2TestUtil.SCHEMA_QUOTED+
".\"seq\" (\"key\" int not null,\"name\" varchar(255), \"geom\" DB2GSE.ST_GEOMETRY, primary key (\"key\"))").execute();
DB2Util.executeRegister(DB2TestUtil.SCHEMA, "seq", "geom", DB2TestUtil.SRSNAME, con);
con.prepareStatement( "INSERT INTO "+DB2TestUtil.SCHEMA_QUOTED+".\"seq\" VALUES (next value for "+getSquenceNameQuoted()+",'one',NULL)" ).execute();
con.prepareStatement( "INSERT INTO "+DB2TestUtil.SCHEMA_QUOTED+".\"seq\" VALUES (next value for "+getSquenceNameQuoted()+",'two',NULL)" ).execute();
con.prepareStatement( "INSERT INTO "+DB2TestUtil.SCHEMA_QUOTED+".\"seq\" VALUES (next value for "+getSquenceNameQuoted()+",'three',NULL)" ).execute();
con.close();
}
@Override
protected void createNonIncrementingPrimaryKeyTable() throws Exception {
Connection con = getDataSource().getConnection();
con.prepareStatement("create table "+DB2TestUtil.SCHEMA_QUOTED+
".\"noninc\" (\"key\" int not null,\"name\" varchar(255), \"geom\" DB2GSE.ST_GEOMETRY, primary key (\"key\"))").execute();
DB2Util.executeRegister(DB2TestUtil.SCHEMA, "noninc", "geom", DB2TestUtil.SRSNAME, con);
con.prepareStatement( "INSERT INTO "+DB2TestUtil.SCHEMA_QUOTED+".\"noninc\" VALUES (1,'one',NULL)" ).execute();
con.prepareStatement( "INSERT INTO "+DB2TestUtil.SCHEMA_QUOTED+".\"noninc\" VALUES (2,'two',NULL)" ).execute();
con.prepareStatement( "INSERT INTO "+DB2TestUtil.SCHEMA_QUOTED+".\"noninc\" VALUES (3,'three',NULL)" ).execute();
con.close();
}
@Override
protected void createMultiColumnPrimaryKeyTable() throws Exception {
Connection con = getDataSource().getConnection();
con.prepareStatement("create table "+DB2TestUtil.SCHEMA_QUOTED+
".\"multi\" (\"key1\" int not null, \"key2\" varchar(255) not null,\"name\" varchar(255) , \"geom\" DB2GSE.ST_GEOMETRY, primary key (\"key1\",\"key2\"))").execute();
DB2Util.executeRegister(DB2TestUtil.SCHEMA, "multi", "geom", DB2TestUtil.SRSNAME, con);
con.prepareStatement( "INSERT INTO "+DB2TestUtil.SCHEMA_QUOTED+".\"multi\" VALUES (1,'x','one',NULL)" ).execute();
con.prepareStatement( "INSERT INTO "+DB2TestUtil.SCHEMA_QUOTED+".\"multi\" VALUES (2,'y','two',NULL)" ).execute();
con.prepareStatement( "INSERT INTO "+DB2TestUtil.SCHEMA_QUOTED+".\"multi\" VALUES (3,'z','three',NULL)" ).execute();
con.close();
}
@Override
protected void createNullPrimaryKeyTable() throws Exception {
//set up table
Connection con = getDataSource().getConnection();
con.prepareStatement("CREATE TABLE "+DB2TestUtil.SCHEMA_QUOTED+".\"nokey\" ( \"name\" varchar(255) )").execute();
// insert data
con.prepareStatement("INSERT INTO "+DB2TestUtil.SCHEMA_QUOTED+".\"nokey\" (\"name\") VALUES ( 'one' )").execute();
con.prepareStatement("INSERT INTO "+DB2TestUtil.SCHEMA_QUOTED+".\"nokey\" (\"name\") VALUES ( 'two' )").execute();
con.prepareStatement("INSERT INTO "+DB2TestUtil.SCHEMA_QUOTED+".\"nokey\" (\"name\") VALUES ( 'three' )").execute();
con.close();
}
private String getUniqueIndexName() {
return "uniq_key_INDEX";
}
private String getUniqueIndexNameQuoted() {
return DB2TestUtil.SCHEMA_QUOTED+".\""+getUniqueIndexName()+"\"";
}
@Override
protected void createUniqueIndexTable() throws Exception {
Connection con = getDataSource().getConnection();
con.prepareStatement("create table "+DB2TestUtil.SCHEMA_QUOTED+
".\"uniq\" (\"key\" int not null,\"name\" varchar(255), \"geom\" DB2GSE.ST_GEOMETRY)").execute();
con.prepareStatement("CREATE UNIQUE INDEX " + getUniqueIndexNameQuoted() + " ON "
+DB2TestUtil.SCHEMA_QUOTED+".\"uniq\"(\"key\")").execute();
DB2Util.executeRegister(DB2TestUtil.SCHEMA, "uniq", "geom", DB2TestUtil.SRSNAME, con);
con.prepareStatement( "INSERT INTO "+DB2TestUtil.SCHEMA_QUOTED+".\"uniq\" VALUES (1,'one',NULL)" ).execute();
con.prepareStatement( "INSERT INTO "+DB2TestUtil.SCHEMA_QUOTED+".\"uniq\" VALUES (2,'two',NULL)" ).execute();
con.prepareStatement( "INSERT INTO "+DB2TestUtil.SCHEMA_QUOTED+".\"uniq\" VALUES (3,'three',NULL)" ).execute();
con.close();
}
@Override
protected void dropAutoGeneratedPrimaryKeyTable() throws Exception {
dropSpatialTable("auto");
}
@Override
protected void dropSequencedPrimaryKeyTable() throws Exception {
dropSpatialTable("seq");
Connection con = getDataSource().getConnection();
try {
DB2TestUtil.dropSequence(DB2TestUtil.SCHEMA, getSquenceName(), con);
} catch (SQLException e) {}
con.close();
}
@Override
protected void dropNonIncrementingPrimaryKeyTable() throws Exception {
dropSpatialTable("noninc");
}
@Override
protected void dropMultiColumnPrimaryKeyTable() throws Exception {
dropSpatialTable("multi");
}
private void dropSpatialTable(String tableName) throws Exception {
Connection con = getDataSource().getConnection();
try {
DB2Util.executeUnRegister(DB2TestUtil.SCHEMA, tableName, "goem", con);
DB2TestUtil.dropTable(DB2TestUtil.SCHEMA, tableName, con);
} catch (SQLException e) {
}
con.close();
}
@Override
protected void dropNullPrimaryKeyTable() throws Exception {
dropSpatialTable("nokey");
}
@Override
protected void dropUniqueIndexTable() throws Exception {
dropSpatialTable("uniq");
}
}