DB2PrimaryKeyFinderTestSetup.java

/*
 *    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 Public
 *    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 Public License for more details.
 */
package org.geotools.data.db2;

import java.sql.Connection;
import java.sql.SQLException;

import org.geotools.jdbc.JDBCPrimaryKeyFinderTestSetup;

public class DB2PrimaryKeyFinderTestSetup extends JDBCPrimaryKeyFinderTestSetup {

    protected DB2PrimaryKeyFinderTestSetup() {
        super(new DB2TestSetup());
    }
   
    
    @Override
    protected void createMetadataTable() throws Exception {
        Connection con = getDataSource().getConnection();
        con.prepareStatement("CREATE TABLE \""+DB2TestUtil.SCHEMA.toUpperCase()+ "\".gt_pk_metadata ( " + "table_schema VARCHAR(32) not null, "
                + "table_name VARCHAR(32) NOT NULL, " + "pk_column VARCHAR(32) NOT NULL, "
                + "pk_column_idx INTEGER, " + "pk_policy VARCHAR(32), " + "pk_sequence VARCHAR(64)," 
                + "primary key (table_schema, table_name, pk_column)" 
                + ")").execute();
        con.close();
    }

    @Override
    protected void dropMetadataTable() throws Exception {
        Connection con = getDataSource().getConnection();
        try {                
                //DB2TestUtil.dropTable(DB2TestUtil.SCHEMA, "gt_pk_metadata_table", con);
                DB2TestUtil.dropTable(DB2TestUtil.SCHEMA.toUpperCase(), "gt_pk_metadata".toUpperCase(), con);
        } catch (SQLException e) {              
        }        
        con.close();
        
    }

    @Override
    protected void createSequencedPrimaryKeyTable() throws Exception {
        Connection con = getDataSource().getConnection();
        con.prepareStatement("CREATE TABLE \""+DB2TestUtil.SCHEMA+ "\".\"seqtable\" ( \"key\" integer not null,  "
                + "\"name\" VARCHAR(256), \"geom\" db2gse.ST_GEOMETRY, primary key (\"key\") )").execute();
        
        DB2Util.executeRegister(DB2TestUtil.SCHEMA, "seqtable", "geom", DB2TestUtil.SRSNAME, con);
        
        con.prepareStatement("CREATE SEQUENCE "+getSquenceNameQuoted()+ " AS INTEGER  start with 1").execute();

        con.prepareStatement( "INSERT INTO \""+DB2TestUtil.SCHEMA+ "\".\"seqtable\" (\"key\", \"name\",\"geom\" ) VALUES (" + 
            "next value for "+getSquenceNameQuoted()+",'one',NULL)").execute();
        con.prepareStatement( "INSERT INTO \""+DB2TestUtil.SCHEMA+ "\".\"seqtable\" (\"key\", \"name\",\"geom\" ) VALUES (" +
            "next value for "+getSquenceNameQuoted()+",'two',NULL)").execute();
        con.prepareStatement( "INSERT INTO \""+DB2TestUtil.SCHEMA+ "\".\"seqtable\" (\"key\", \"name\",\"geom\" ) VALUES (" + 
             "next value for "+getSquenceNameQuoted()+",'three',NULL)").execute();

        con.prepareStatement("INSERT INTO \""+DB2TestUtil.SCHEMA.toUpperCase()+ "\".gt_pk_metadata VALUES"
                + "('"+ DB2TestUtil.SCHEMA+ "', 'seqtable', 'key', 0, 'sequence', '"+ getSquenceName()+"')").execute();
        con.close();
    }

    @Override
    protected void dropSequencedPrimaryKeyTable() throws Exception {
        Connection con = getDataSource().getConnection();
        try {                
                DB2Util.executeUnRegister(DB2TestUtil.SCHEMA, "seqtable", "geom",  con);
                DB2TestUtil.dropTable(DB2TestUtil.SCHEMA, "seqtable", con);
                DB2TestUtil.dropSequence(DB2TestUtil.SCHEMA, "pksequence", con);
        } catch (SQLException e) {              
        }        
        con.close();

    }

    @Override
    protected void createPlainTable() throws Exception {
        Connection con = getDataSource().getConnection();

        con.prepareStatement("CREATE TABLE \""+DB2TestUtil.SCHEMA+ "\".\"plaintable\" ( \"key1\" int, \"key2\" int, "
                + "\"name\" VARCHAR(256), \"geom\" db2gse.ST_GEOMETRY)").execute();
        
        DB2Util.executeRegister(DB2TestUtil.SCHEMA, "plaintable", "geom", DB2TestUtil.SRSNAME, con);
        

        con.prepareStatement("INSERT INTO \""+DB2TestUtil.SCHEMA+ "\".\"plaintable\" VALUES (1, 2, 'one', NULL)").execute();
        con.prepareStatement("INSERT INTO \""+DB2TestUtil.SCHEMA+ "\".\"plaintable\" VALUES (2, 3, 'two', NULL)").execute();
        con.prepareStatement("INSERT INTO \""+DB2TestUtil.SCHEMA+ "\".\"plaintable\" VALUES (3, 4, 'three', NULL)").execute();
        con.close();                

    }

    @Override
    protected void dropPlainTable() throws Exception {
        Connection con = getDataSource().getConnection();
        try {                
                DB2Util.executeUnRegister(DB2TestUtil.SCHEMA, "plaintable", "geom",  con);
                DB2TestUtil.dropTable(DB2TestUtil.SCHEMA, "plaintable", con);
        } catch (SQLException e) {              
        }        
        con.close();                
    }

    @Override
    protected void createAssignedSinglePkView() throws Exception {
        Connection con = getDataSource().getConnection();
        con.prepareStatement("CREATE VIEW \""+DB2TestUtil.SCHEMA+ "\".\"assignedsinglepk\" AS SELECT * FROM \""+DB2TestUtil.SCHEMA+ "\".\"plaintable\"").execute();
        con.prepareStatement("INSERT INTO \""+DB2TestUtil.SCHEMA.toUpperCase()+ "\".gt_pk_metadata VALUES"
                + "('"+DB2TestUtil.SCHEMA+ "', 'assignedsinglepk', 'key1', 0, 'assigned', NULL)").execute();
        con.close();
    }

    @Override
    protected void dropAssignedSinglePkView() throws Exception {
        Connection con = getDataSource().getConnection();
        try {                
                DB2TestUtil.dropView(DB2TestUtil.SCHEMA, "assignedsinglepk", con);
        } catch (SQLException e) {              
        }        
        con.close();
        
    }

    @Override
    protected void createAssignedMultiPkView() throws Exception {
        Connection con = getDataSource().getConnection();
        con.prepareStatement("CREATE VIEW \""+DB2TestUtil.SCHEMA+ "\".\"assignedmultipk\" AS SELECT * FROM \""+DB2TestUtil.SCHEMA+ "\".\"plaintable\"").execute();
        con.prepareStatement("INSERT INTO \""+DB2TestUtil.SCHEMA.toUpperCase()+ "\".gt_pk_metadata VALUES"
                + "('"+DB2TestUtil.SCHEMA+ "', 'assignedmultipk', 'key1', 0, 'assigned', NULL)").execute();
        con.prepareStatement("INSERT INTO \""+DB2TestUtil.SCHEMA.toUpperCase()+ "\".gt_pk_metadata VALUES"
                + "('"+DB2TestUtil.SCHEMA+ "', 'assignedmultipk', 'key2', 1, 'assigned', NULL)").execute();
        con.close();

    }

    @Override
    protected void dropAssignedMultiPkView() throws Exception {
        Connection con = getDataSource().getConnection();
        try {                
                DB2TestUtil.dropView(DB2TestUtil.SCHEMA, "assignedmultipk", con);
        } catch (SQLException e) {              
        }        
        con.close();
    }

    private String getSquenceName() {
        return "pksequence";
    }
    
    private String getSquenceNameQuoted() {
        return DB2TestUtil.SCHEMA_QUOTED+".\""+getSquenceName()+"\"";
    }

}