-- A script to create a number of tables in a PostGIS enabled

-- PostgreSQL database that test the ability of Qgis to load said

-- tables.

-- $Id:$

-- These are all in the public schema (need to do a set for a non-public shema)

-- normal table

-- Should load

CREATE TABLE test0 (id int4 primary key) WITHOUT OIDS;

SELECT addgeometrycolumn('public','test0','the_geom',4326,'LINESTRING',2);

INSERT INTO test0 (id, the_geom) values (0, geomfromtext('LINESTRING(150 -43, 160 -44)',4326));

-- no select permission on table

-- Should NOT load

CREATE TABLE test1 (id int4);

SELECT addgeometrycolumn('public','test1','the_geom',4326,'LINESTRING',2);

INSERT INTO test1 (id, the_geom) values (0, geomfromtext('LINESTRING(150 -43, 160 -44)',4326));

REVOKE SELECT ON test1 FROM public;

-- no primary key on table, but has an oid

-- Should load

CREATE TABLE TEST2 (id int4) WITH OIDS;

SELECT addgeometrycolumn('public','test2','the_geom',4326,'LINESTRING',2);

INSERT INTO test2 (id, the_geom) values (0, geomfromtext('LINESTRING(150 -43, 160 -44)',4326));

-- primary key of non int4 type, but has an oid

-- Should load

CREATE TABLE test3 (id double precision PRIMARY KEY) WITH OIDS;

SELECT addgeometrycolumn('public','test3','the_geom',4326,'LINESTRING',2);

INSERT INTO test3 (id, the_geom) values (0, geomfromtext('LINESTRING(150 -43, 160 -44)',4326));

-- no primary key, no oid, no other field of type int4 with unique constraint

-- Should NOT load

CREATE TABLE test4 (id double precision) WITHOUT OIDS;

SELECT addgeometrycolumn('public','test4','the_geom',4326,'LINESTRING',2);

INSERT INTO test4 (id, the_geom) values (0, geomfromtext('LINESTRING(150 -43, 160 -44)',4326));

-- no primary key, no oid, but an int4 column with a unique constraint

-- Should load

CREATE TABLE test5 (id int4 UNIQUE) WITHOUT OIDS;

SELECT addgeometrycolumn('public','test5','the_geom',4326,'LINESTRING',2);

INSERT INTO test5 (id, the_geom) values (0, geomfromtext('LINESTRING(150 -43, 160 -44)',4326));

-- no primary key, no oid, but an int4 column, but without a unique

-- constraint, but unique data

-- Should load

CREATE TABLE test6 (id int4) WITHOUT OIDS;

SELECT addgeometrycolumn('public','test6','the_geom',4326,'LINESTRING',2);

INSERT INTO test6 (id, the_geom) values (0, geomfromtext('LINESTRING(150 -43, 160 -44)',4326));

INSERT INTO test6 (id, the_geom) values (1, geomfromtext('LINESTRING(151 -43, 161 -44)',4326));

-- no primary key, no oid, but an int4 column, but without a unique

-- constraint, and duplicate data

-- Should NOT load

CREATE TABLE test7 (id int4) WITHOUT OIDS;

SELECT addgeometrycolumn('public','test7','the_geom',4326,'LINESTRING',2);

INSERT INTO test7 (id, the_geom) values (0, geomfromtext('LINESTRING(150 -43, 160 -44)',4326));

INSERT INTO test7 (id, the_geom) values (0, geomfromtext('LINESTRING(151 -43, 161 -44)',4326));

-- no primary key, no oid, but a non int4 column with a unique constraint

-- Should NOT load

CREATE TABLE test8 (id double precision NOT NULL UNIQUE) WITHOUT OIDS;

SELECT addgeometrycolumn('public','test8','the_geom',4326,'LINESTRING',2);

INSERT INTO test8 (id, the_geom) values (0.0, geomfromtext('LINESTRING(150 -43, 160 -44)',4326));

-- a primary key that covers more than one column, and no oid

-- Should NOT load

CREATE TABLE test9 (id1 int4, id2 int4, PRIMARY KEY (id1, id2)) WITHOUT OIDS;

SELECT addgeometrycolumn('public','test9','the_geom', 4326, 'LINESTRING',2);

INSERT INTO test9 (id1, id2, the_geom) values (0, 0, geomfromtext('LINESTRING(150 -43, 160 -44)',4326));

-- a primary key that covers more than one column, and no oid, but with a

-- unique constraint on another int4 column

-- Should load

CREATE TABLE test10 (id1 int4, id2 int4, id3 int4 NOT NULL UNIQUE, PRIMARY KEY (id1, id2)) WITHOUT OIDS;

SELECT addgeometrycolumn('public','test10','the_geom', 4326, 'LINESTRING',2);

INSERT INTO test10 (id1, id2, id3, the_geom) values (0, 0, 0, geomfromtext('LINESTRING(150 -43, 160 -44)',4326));

-- a primary key that covers more than one column, but with an oid

-- Should load

CREATE TABLE test11 (id1 int4, id2 int4, PRIMARY KEY (id1, id2)) WITH OIDS;

SELECT addgeometrycolumn('public','test11','the_geom', 4326, 'LINESTRING',2);

INSERT INTO test11 (id1, id2, the_geom) values (0, 0, geomfromtext('LINESTRING(150 -43, 160 -44)',4326));

-- a proper table, but with no data in it

-- Should load

CREATE TABLE test12 (id int4 primary key) WITHOUT OIDS;

SELECT addgeometrycolumn('public', 'test12', 'the_geom', 4326, 'LINESTRING', 2);

-- And now the views...

-- Note views which refer to tables that are loaded only due to their

-- having an oid column will not load because the view doesn't have

-- access to the oid (unless it is explicitly included in the view).

-- Should not load

CREATE VIEW v_test1 AS SELECT * from test1;

-- Should load

CREATE VIEW v_test2 AS SELECT *, oid from test2;

-- Should load

CREATE VIEW v_test3 AS SELECT *, oid from test3;

-- Should not load

CREATE VIEW v_test4 AS SELECT * from test4;

-- Should load

CREATE VIEW v_test5 AS SELECT * from test5;

-- Should load

CREATE VIEW v_test6 AS SELECT * from test6;

-- Should not load

CREATE VIEW v_test7 AS SELECT * from test7;

-- Should not load

CREATE VIEW v_test8 AS SELECT * from test8;

-- Should not load

CREATE VIEW v_test9 AS SELECT * from test9;

-- Should load

CREATE VIEW v_test10 AS SELECT * from test10;

-- Should not load

CREATE VIEW v_test11 AS SELECT *, oid from test11;

-- Should load

CREATE VIEW v_test12 AS SELECt * from test12;

-- and how to delete the tables

SELECT dropgeometrytable('public','test0');

SELECT dropgeometrytable('public','test1');

SELECT dropgeometrytable('public','test2');

SELECT dropgeometrytable('public','test3');

SELECT dropgeometrytable('public','test4');

SELECT dropgeometrytable('public','test5');

SELECT dropgeometrytable('public','test6');

SELECT dropgeometrytable('public','test7');

SELECT dropgeometrytable('public','test8');

SELECT dropgeometrytable('public','test9');

SELECT dropgeometrytable('public','test10');

SELECT dropgeometrytable('public','test11');

SELECT dropgeometrytable('public','test12');

DROP VIEW v_test1;

DROP VIEW v_test2;

DROP VIEW v_test3;

DROP VIEW v_test4;

DROP VIEW v_test5;

DROP VIEW v_test6;

DROP VIEW v_test7;

DROP VIEW v_test8;

DROP VIEW v_test9;

DROP VIEW v_test10;

DROP VIEW v_test11;

DROP VIEW v_test12;