001: package org.geotools.data.postgis;
002:
003: import java.io.IOException;
004: import java.sql.Connection;
005: import java.sql.ResultSet;
006: import java.sql.Statement;
007:
008: /**
009: * Sets up various dummy tables/sequences, for extension.
010: *
011: * @author Cory Horner, Refractions Research
012: */
013: public class AbstractPostgisOnlineTestCase extends
014: PostgisOnlineTestCase {
015:
016: public static final String TEST_SCHEMA = "gt_test";
017:
018: protected PostgisDataStore ds;
019:
020: /** simple table with serial (int4) primary key */
021: final protected String table1 = "tmp_pgtest1";
022: /** simple table with int4 primary key and sequence as default value */
023: final protected String table2 = "tmp_pgtest2";
024: /** simple table with bigserial (int8) primary key */
025: final protected String table3 = "tmp_pgtest3";
026: /** simple table with int8 primary key and sequence as default value */
027: final protected String table4 = "tmp_pgtest4";
028: /** simple table with serial (int4) primary key, WITHOUT OIDS, and space in name */
029: final protected String table5 = "tmp_pgtest 5";
030: /** simple table with int4 primary key, sequence as default value, WITHOUT OIDS, and space in name */
031: final protected String table6 = "tmp_pgtest 6";
032: /** just like table1, but will be inserted in a different schema */
033: final protected String table7 = "tmp_pgtest_7";
034:
035: protected void connect() throws Exception {
036: super .connect();
037: ds = (PostgisDataStore) dataStore;
038: //create dummy tables
039: Statement st = getConnection().createStatement();
040: dropTables(st);
041: purgeGeometryColumns(st);
042: createTables(st);
043: setupGeometryColumns(st);
044: st.close();
045: }
046:
047: protected void disconnect() throws Exception {
048: Statement st = getConnection().createStatement();
049: purgeGeometryColumns(st);
050: dropTables(st);
051: st.close();
052: //ds.getConnectionPool().close(); //is this killing our other tests?
053: super .disconnect();
054: }
055:
056: public Connection getConnection() throws Exception {
057: return ds.getDataSource().getConnection();
058: }
059:
060: protected void setupGeometryColumns(Statement st) throws Exception {
061: //subclasses should override if they want more or less geometry columns
062: String preSql = "INSERT INTO geometry_columns (f_table_catalog, f_table_schema, f_table_name, f_geometry_column, coord_dimension, srid, type) VALUES ('',";
063: String postSql = ", 'the_geom', 2, 4326, 'POINT')";
064: String sql = preSql + "'public', '" + table1 + "'" + postSql;
065: st.execute(sql);
066: sql = preSql + "'public', '" + table2 + "'" + postSql;
067: st.execute(sql);
068: sql = preSql + "'public', '" + table3 + "'" + postSql;
069: st.execute(sql);
070: sql = preSql + "'" + TEST_SCHEMA + "', '" + table7 + "'"
071: + postSql;
072: st.execute(sql);
073: }
074:
075: protected void purgeGeometryColumns(Statement st) throws Exception {
076: String sql = "DELETE FROM geometry_columns WHERE f_table_name LIKE 'tmp_pgtest%'";
077: st.execute(sql);
078: }
079:
080: protected void createTables(Statement st) throws Exception {
081: createTable1(st);
082: createTable2(st);
083: createTable3(st);
084: createTable4(st);
085: createTable5(st);
086: createTable6(st);
087: createTestSchema(st);
088: createTable7(st);
089: }
090:
091: protected void createTestSchema(Statement st) throws Exception {
092: String sql = "CREATE SCHEMA " + TEST_SCHEMA;
093: st.execute(sql);
094: }
095:
096: protected void createTable1(Statement st) throws Exception {
097: String sql = "CREATE TABLE " + table1 + "("
098: + "fid serial NOT NULL,"
099: + "name varchar(10), the_geom geometry, "
100: + "CONSTRAINT " + table1 + "_pkey PRIMARY KEY (fid)"
101: + ") WITH OIDS;";
102: st.execute(sql);
103: }
104:
105: protected void createTable2(Statement st) throws Exception {
106: String sql = "CREATE SEQUENCE "
107: + table2
108: + "_fid_seq INCREMENT 1 MINVALUE 1 "
109: + "MAXVALUE 9223372036854775807 START 1001 CACHE 1;"
110: + "CREATE TABLE "
111: + table2
112: + "("
113: + "fid int4 NOT NULL DEFAULT nextval('"
114: + table2
115: + "_fid_seq'::text), name varchar(10), the_geom geometry, "
116: + "CONSTRAINT " + table2 + "_pkey PRIMARY KEY (fid)"
117: + ") WITH OIDS;";
118: st.execute(sql);
119: }
120:
121: protected void createTable3(Statement st) throws Exception {
122: String sql = "CREATE TABLE " + table3 + "("
123: + "fid bigserial NOT NULL, "
124: + "name varchar(10), the_geom geometry, "
125: + "CONSTRAINT " + table3 + "_pkey PRIMARY KEY (fid)"
126: + ") WITH OIDS;";
127: st.execute(sql);
128: }
129:
130: protected void createTable4(Statement st) throws Exception {
131: String sql = "CREATE SEQUENCE " + table4
132: + "_fid_seq INCREMENT 1 MINVALUE 1 "
133: + "MAXVALUE 9223372036854775807 START 1000001 CACHE 1;"
134: + "CREATE TABLE " + table4 + "("
135: + "fid int8 NOT NULL DEFAULT nextval('" + table4
136: + "_fid_seq'::text)," + "name varchar(10),"
137: + "CONSTRAINT " + table4 + "_pkey PRIMARY KEY (fid)"
138: + ") WITH OIDS;";
139: st.execute(sql);
140: }
141:
142: protected void createTable5(Statement st) throws Exception {
143: String sql = "CREATE TABLE \"" + table5 + "\" ("
144: + "fid serial NOT NULL," + "name varchar(10),"
145: + "CONSTRAINT \"" + table5
146: + "_pkey\" PRIMARY KEY (fid)" + ") WITHOUT OIDS;";
147: st.execute(sql);
148: }
149:
150: protected void createTable6(Statement st) throws Exception {
151: String sql = "CREATE SEQUENCE \"" + table6
152: + "_fid_seq\" INCREMENT 1 MINVALUE 1 "
153: + "MAXVALUE 9223372036854775807 START 1001 CACHE 1;"
154: + "CREATE TABLE \"" + table6 + "\" ("
155: + "fid int4 NOT NULL DEFAULT nextval('\"" + table6
156: + "_fid_seq\"'::text)," + "name varchar(10),"
157: + "CONSTRAINT \"" + table6
158: + "_pkey\" PRIMARY KEY (fid)" + ") WITHOUT OIDS;";
159: st.execute(sql);
160: }
161:
162: protected void createTable7(Statement st) throws Exception {
163: String sql = "CREATE TABLE " + TEST_SCHEMA + "." + table7 + "("
164: + "fid serial NOT NULL,"
165: + "name varchar(10), the_geom geometry, "
166: + "CONSTRAINT " + table7 + "_pkey PRIMARY KEY (fid)"
167: + ") WITH OIDS;";
168: st.execute(sql);
169: }
170:
171: protected void dropTables(Statement st) throws Exception {
172: dropTable(st, table1);
173: dropTable(st, table2);
174: dropSequence(st, table2 + "_fid_seq");
175: dropTable(st, table3);
176: dropTable(st, table4);
177: dropSequence(st, table4 + "_fid_seq");
178: dropTable(st, table5);
179: dropTable(st, table6);
180: dropSequence(st, table6 + "_fid_seq");
181: dropTable(st, table7);
182: dropSequence(st, TEST_SCHEMA + "." + table7 + "_fid_seq");
183: dropSchema(st, TEST_SCHEMA);
184: }
185:
186: protected void dropTable(Statement st, String tableName)
187: throws Exception {
188: String sql = "SELECT schemaname, tablename FROM pg_tables WHERE tablename = '"
189: + tableName + "'";
190: ResultSet rs = st.executeQuery(sql);
191: boolean exists = rs.next();
192: String schemaName = "public";
193: if (exists) {
194: schemaName = rs.getString(1);
195: }
196: rs.close();
197: if (exists) {
198: sql = "DROP TABLE \"" + schemaName + "\".\"" + tableName
199: + "\"";
200: st.execute(sql);
201: }
202: }
203:
204: protected void dropSequence(Statement st, String sequenceName)
205: throws Exception {
206: String sql = "SELECT COUNT(relid) FROM pg_statio_all_sequences WHERE relname = '"
207: + sequenceName + "'";
208: ResultSet rs = st.executeQuery(sql);
209: rs.next();
210: int exists = rs.getInt(1);
211: rs.close();
212: if (exists > 0) {
213: sql = "DROP SEQUENCE \"" + sequenceName + "\"";
214: st.execute(sql);
215: }
216: }
217:
218: protected String getFixtureId() {
219: return "postgis.typical";
220: }
221:
222: protected void dropSchema(Statement st, String schemaName)
223: throws Exception {
224: String sql = "SELECT nspname FROM pg_namespace WHERE nspname = '"
225: + schemaName + "'";
226: ResultSet rs = st.executeQuery(sql);
227: boolean exists = rs.next();
228: rs.close();
229: if (exists) {
230: sql = "DROP SCHEMA " + schemaName;
231: st.execute(sql);
232: }
233: }
234: }
|