-- This file inserts all the appropriate cite data into postgis to run with
-- WFS 1.0.0 Cite Unit test

-- Inspired From OGC CITE: http://cite.opengeospatial.org/te2/wfs-1.0.0-r0/data/data-wfs-1.0.0.zip
-- With several modifications
-- NOTA: Datas are based on a PostGIS 2.0 dump.


drop table "Nulls";
drop table "Points";
drop table "Other";
drop table "Lines";
drop table "Polygons";
drop table "MLines";
drop table "MPolygons";
drop table "MPoints";
drop table "Seven";
drop table "Fifteen";
drop table "Updates";
drop table "Inserts";
drop table "Deletes";
drop table "Locks";

delete from geometry_columns where f_table_name = 'Nulls' ;
delete from geometry_columns where f_table_name = 'Points' ;
delete from geometry_columns where f_table_name = 'Other' ;
delete from geometry_columns where f_table_name = 'Lines' ;
delete from geometry_columns where f_table_name = 'Polygons' ;
delete from geometry_columns where f_table_name = 'MLines' ;
delete from geometry_columns where f_table_name = 'MPolygons' ;
delete from geometry_columns where f_table_name = 'MPoints' ;
delete from geometry_columns where f_table_name = 'Seven' ;
delete from geometry_columns where f_table_name = 'Fifteen' ;
delete from geometry_columns where f_table_name = 'Updates' ;
delete from geometry_columns where f_table_name = 'Inserts' ;
delete from geometry_columns where f_table_name = 'Deletes' ;
delete from geometry_columns where f_table_name = 'Locks' ;


--
-- Create SQL structure
--

CREATE TABLE "Seven" ();
ALTER TABLE "Seven" ADD COLUMN "name" character varying;
SELECT AddGeometryColumn('Seven', 'boundedBy', 32615, 'POLYGON', 2); 
SELECT AddGeometryColumn('Seven', 'pointProperty', 32615, 'POINT', 2); 


CREATE TABLE "Nulls" ();
ALTER TABLE "Nulls" ADD COLUMN "description" character varying;
ALTER TABLE "Nulls" ADD COLUMN "name" character varying;
SELECT AddGeometryColumn('Nulls', 'boundedBy', 32615, 'POLYGON', 2); 
ALTER TABLE "Nulls" ADD COLUMN "integers" integer;
ALTER TABLE "Nulls" ADD COLUMN "dates" date;
SELECT AddGeometryColumn('Nulls', 'pointProperty', 32615, 'POINT', 2); 



CREATE TABLE "Deletes" ();
SELECT AddGeometryColumn('Deletes', 'boundedBy', 32615, 'POLYGON', 2); 
ALTER TABLE "Deletes" ADD COLUMN "id" character varying;
SELECT AddGeometryColumn('Deletes', 'pointProperty', 32615, 'POINT', 2); 


CREATE TABLE "Inserts" ();
SELECT AddGeometryColumn('Inserts', 'boundedBy', 32615, 'POLYGON', 2); 
ALTER TABLE "Inserts" ADD COLUMN "id" character varying;
SELECT AddGeometryColumn('Inserts', 'pointProperty', 32615, 'POINT', 2); 


CREATE TABLE "Updates" ();
SELECT AddGeometryColumn('Updates', 'boundedBy', 32615, 'POLYGON', 2); 
ALTER TABLE "Updates" ADD COLUMN "id" character varying;
SELECT AddGeometryColumn('Updates', 'pointProperty', 32615, 'POINT', 2); 


CREATE TABLE "Points" ();
ALTER TABLE "Points" ADD COLUMN "id" character varying;
SELECT AddGeometryColumn('Points', 'pointProperty', 32615, 'POINT', 2); 


CREATE TABLE "Lines" ();
ALTER TABLE "Lines" ADD COLUMN "id" character varying;
SELECT AddGeometryColumn('Lines', 'lineStringProperty', 32615, 'LINESTRING', 2); 


CREATE TABLE "Polygons" ();
ALTER TABLE "Polygons" ADD COLUMN "id" character varying;
SELECT AddGeometryColumn('Polygons', 'polygonProperty', 32615, 'POLYGON', 2); 


CREATE TABLE "MPoints" ();
ALTER TABLE "MPoints" ADD COLUMN "id" character varying;
SELECT AddGeometryColumn('MPoints', 'multiPointProperty', 32615, 'MULTIPOINT', 2); 


CREATE TABLE "MLines" ();
ALTER TABLE "MLines" ADD COLUMN "id" character varying;
SELECT AddGeometryColumn('MLines', 'multiLineStringProperty', 32615, 'MULTILINESTRING', 2); 


CREATE TABLE "MPolygons" ();
ALTER TABLE "MPolygons" ADD COLUMN "id" character varying;
SELECT AddGeometryColumn('MPolygons', 'multiPolygonProperty', 32615, 'MULTIPOLYGON', 2); 


CREATE TABLE "Fifteen" ();
ALTER TABLE "Fifteen" ADD COLUMN "name" character varying;
SELECT AddGeometryColumn('Fifteen', 'boundedBy', 32615, 'POLYGON', 2); 
SELECT AddGeometryColumn('Fifteen', 'pointProperty', 32615, 'POINT', 2); 


CREATE TABLE "Locks" ();
SELECT AddGeometryColumn('Locks', 'boundedBy', 32615, 'POLYGON', 2); 
ALTER TABLE "Locks" ADD COLUMN "id" character varying;
SELECT AddGeometryColumn('Locks', 'pointProperty', 32615, 'POINT', 2); 


CREATE TABLE "Other" ();
ALTER TABLE "Other" ADD COLUMN "description" character varying;
ALTER TABLE "Other" ADD COLUMN "name" character varying;
SELECT AddGeometryColumn('Other', 'boundedBy', 32615, 'POLYGON', 2); 
SELECT AddGeometryColumn('Other', 'pointProperty', 32615, 'POINT', 2); 
ALTER TABLE "Other" ADD COLUMN "string1" character varying NOT NULL;
ALTER TABLE "Other" ADD COLUMN "string2" character varying;
ALTER TABLE "Other" ADD COLUMN "integers" integer;
ALTER TABLE "Other" ADD COLUMN "dates" date;

--
-- Add Primary keys
--
alter table "Deletes" add column pkey serial;
alter table "Deletes" add primary key (pkey);

alter table "Fifteen" add column pkey serial;
alter table "Fifteen" add primary key (pkey);

alter table "Inserts" add column pkey serial;
alter table "Inserts" add primary key (pkey);

alter table "Lines" add column pkey serial;
alter table "Lines" add primary key (pkey);

alter table "Locks" add column pkey serial;
alter table "Locks" add primary key (pkey);

alter table "MLines" add column pkey serial;
alter table "MLines" add primary key (pkey);

alter table "MPoints" add column pkey serial;
alter table "MPoints" add primary key (pkey);

alter table "MPolygons" add column pkey serial;
alter table "MPolygons" add primary key (pkey);

alter table "Nulls" add column pkey serial;
alter table "Nulls" add primary key (pkey);

alter table "Other" add column pkey serial;
alter table "Other" add primary key (pkey);

alter table "Points" add column pkey serial;
alter table "Points" add primary key (pkey);

alter table "Polygons" add column pkey serial;
alter table "Polygons" add primary key (pkey);

alter table "Seven" add column pkey serial;
alter table "Seven" add primary key (pkey);

alter table "Updates" add column pkey serial;
alter table "Updates" add primary key (pkey);


--
-- Data for Name: Deletes
--

INSERT INTO "Deletes" VALUES ('0103000020677F000001000000050000000000000080841E410000000080841E410000000080841E410000000010861E410000000010861E410000000010861E410000000010861E410000000080841E410000000080841E410000000080841E41', 'td0001', '0101000020677F00000000000048851E410000000048851E41', 1);
INSERT INTO "Deletes" VALUES ('0103000020677F000001000000050000000000000080841E410000000080841E410000000080841E410000000010861E410000000010861E410000000010861E410000000010861E410000000080841E410000000080841E410000000080841E41', 'td0002', '0101000020677F00000000000048851E410000000048851E41', 2);
INSERT INTO "Deletes" VALUES ('0103000020677F000001000000050000000000000080841E410000000080841E410000000080841E410000000010861E410000000010861E410000000010861E410000000010861E410000000080841E410000000080841E410000000080841E41', 'td0003', '0101000020677F00000000000048851E410000000048851E41', 3);


--
-- Data for Name: Fifteen
--

INSERT INTO "Fifteen" VALUES (NULL, '0103000020677F000001000000050000000000000080841E410000000080841E410000000080841E410000000010861E410000000010861E410000000010861E410000000010861E410000000080841E410000000080841E410000000080841E41', '0101000020677F00000000000048851E410000000048851E41', 1);
INSERT INTO "Fifteen" VALUES (NULL, '0103000020677F000001000000050000000000000080841E410000000080841E410000000080841E410000000010861E410000000010861E410000000010861E410000000010861E410000000080841E410000000080841E410000000080841E41', '0101000020677F00000000000048851E410000000048851E41', 2);
INSERT INTO "Fifteen" VALUES (NULL, '0103000020677F000001000000050000000000000080841E410000000080841E410000000080841E410000000010861E410000000010861E410000000010861E410000000010861E410000000080841E410000000080841E410000000080841E41', '0101000020677F00000000000048851E410000000048851E41', 3);
INSERT INTO "Fifteen" VALUES (NULL, '0103000020677F000001000000050000000000000080841E410000000080841E410000000080841E410000000010861E410000000010861E410000000010861E410000000010861E410000000080841E410000000080841E410000000080841E41', '0101000020677F00000000000048851E410000000048851E41', 4);
INSERT INTO "Fifteen" VALUES (NULL, '0103000020677F000001000000050000000000000080841E410000000080841E410000000080841E410000000010861E410000000010861E410000000010861E410000000010861E410000000080841E410000000080841E410000000080841E41', '0101000020677F00000000000048851E410000000048851E41', 5);
INSERT INTO "Fifteen" VALUES (NULL, '0103000020677F000001000000050000000000000080841E410000000080841E410000000080841E410000000010861E410000000010861E410000000010861E410000000010861E410000000080841E410000000080841E410000000080841E41', '0101000020677F00000000000048851E410000000048851E41', 6);
INSERT INTO "Fifteen" VALUES (NULL, '0103000020677F000001000000050000000000000080841E410000000080841E410000000080841E410000000010861E410000000010861E410000000010861E410000000010861E410000000080841E410000000080841E410000000080841E41', '0101000020677F00000000000048851E410000000048851E41', 7);
INSERT INTO "Fifteen" VALUES (NULL, '0103000020677F000001000000050000000000000080841E410000000080841E410000000080841E410000000010861E410000000010861E410000000010861E410000000010861E410000000080841E410000000080841E410000000080841E41', '0101000020677F00000000000048851E410000000048851E41', 8);
INSERT INTO "Fifteen" VALUES (NULL, '0103000020677F000001000000050000000000000080841E410000000080841E410000000080841E410000000010861E410000000010861E410000000010861E410000000010861E410000000080841E410000000080841E410000000080841E41', '0101000020677F00000000000048851E410000000048851E41', 9);
INSERT INTO "Fifteen" VALUES (NULL, '0103000020677F000001000000050000000000000080841E410000000080841E410000000080841E410000000010861E410000000010861E410000000010861E410000000010861E410000000080841E410000000080841E410000000080841E41', '0101000020677F00000000000048851E410000000048851E41', 10);
INSERT INTO "Fifteen" VALUES (NULL, '0103000020677F000001000000050000000000000080841E410000000080841E410000000080841E410000000010861E410000000010861E410000000010861E410000000010861E410000000080841E410000000080841E410000000080841E41', '0101000020677F00000000000048851E410000000048851E41', 11);
INSERT INTO "Fifteen" VALUES (NULL, '0103000020677F000001000000050000000000000080841E410000000080841E410000000080841E410000000010861E410000000010861E410000000010861E410000000010861E410000000080841E410000000080841E410000000080841E41', '0101000020677F00000000000048851E410000000048851E41', 12);
INSERT INTO "Fifteen" VALUES (NULL, '0103000020677F000001000000050000000000000080841E410000000080841E410000000080841E410000000010861E410000000010861E410000000010861E410000000010861E410000000080841E410000000080841E410000000080841E41', '0101000020677F00000000000048851E410000000048851E41', 13);
INSERT INTO "Fifteen" VALUES (NULL, '0103000020677F000001000000050000000000000080841E410000000080841E410000000080841E410000000010861E410000000010861E410000000010861E410000000010861E410000000080841E410000000080841E410000000080841E41', '0101000020677F00000000000048851E410000000048851E41', 14);
INSERT INTO "Fifteen" VALUES (NULL, '0103000020677F000001000000050000000000000080841E410000000080841E410000000080841E410000000010861E410000000010861E410000000010861E410000000010861E410000000080841E410000000080841E410000000080841E41', '0101000020677F00000000000048851E410000000048851E41', 15);


--
-- Data for Name: Lines
--

INSERT INTO "Lines" VALUES ('t0001', '0102000020677F0000020000000000000074861E4100000000E4841E41000000003C871E4100000000AC851E41', 1);


--
-- Data for Name: MLines
--

INSERT INTO "MLines" VALUES ('t0004', '0105000020677F00000200000001020000000200000000000000248B1E4100000000E4841E4100000000EC8B1E4100000000AC851E4101020000000200000000000000248B1E4100000000AC851E4100000000EC8B1E4100000000E4841E41', 1);


--
-- Data for Name: MPoints
--

INSERT INTO "MPoints" VALUES ('t0003', '0104000020677F00000200000001010000000000000094891E4100000000E4841E410101000000000000005C8A1E4100000000AC851E41', 1);


--
-- Data for Name: MPolygons
--

INSERT INTO "MPolygons" VALUES ('t0005', '0106000020677F0000020000000103000000010000000400000000000000B48C1E4100000000E4841E4100000000188D1E410000000048851E41000000007C8D1E4100000000E4841E4100000000B48C1E4100000000E4841E410103000000010000000500000000000000B48C1E410000000048851E4100000000B48C1E4100000000AC851E4100000000188D1E4100000000AC851E4100000000188D1E410000000048851E4100000000B48C1E410000000048851E41', 1);


--
-- Data for Name: Nulls
--

INSERT INTO "Nulls" VALUES (NULL, NULL, '0103000020677F000001000000050000000000000080841E410000000080841E410000000080841E410000000010861E410000000010861E410000000010861E410000000010861E410000000080841E410000000080841E410000000080841E41', NULL, NULL, NULL, 1);


--
-- Data for Name: Other
--

INSERT INTO "Other" VALUES ('A Single Feature used to test returning of properties', 'singleFeature', '0103000020677F000001000000050000000000000080841E410000000080841E410000000080841E410000000010861E410000000010861E410000000010861E410000000010861E410000000080841E410000000080841E410000000080841E41', '0101000020677F00000000000048851E410000000048851E41', 'always', 'sometimes', 7, '2002-12-02', 1);


--
-- Data for Name: Points
--

INSERT INTO "Points" VALUES ('t0000', '0101000020677F00000000000048851E410000000048851E41', 1);


--
-- Data for Name: Polygons
--

INSERT INTO "Polygons" VALUES ('t0002', '0103000020677F000001000000050000000000000004881E4100000000E4841E410000000004881E4100000000AC851E4100000000CC881E410000000048851E4100000000CC881E4100000000E4841E410000000004881E4100000000E4841E41', 1);


--
-- Data for Name: Seven
--

INSERT INTO "Seven" VALUES (NULL, '0103000020677F000001000000050000000000000080841E410000000080841E410000000080841E410000000010861E410000000010861E410000000010861E410000000010861E410000000080841E410000000080841E410000000080841E41', '0101000020677F00000000000048851E410000000048851E41', 1);
INSERT INTO "Seven" VALUES (NULL, '0103000020677F000001000000050000000000000080841E410000000080841E410000000080841E410000000010861E410000000010861E410000000010861E410000000010861E410000000080841E410000000080841E410000000080841E41', '0101000020677F00000000000048851E410000000048851E41', 2);
INSERT INTO "Seven" VALUES (NULL, '0103000020677F000001000000050000000000000080841E410000000080841E410000000080841E410000000010861E410000000010861E410000000010861E410000000010861E410000000080841E410000000080841E410000000080841E41', '0101000020677F00000000000048851E410000000048851E41', 3);
INSERT INTO "Seven" VALUES (NULL, '0103000020677F000001000000050000000000000080841E410000000080841E410000000080841E410000000010861E410000000010861E410000000010861E410000000010861E410000000080841E410000000080841E410000000080841E41', '0101000020677F00000000000048851E410000000048851E41', 4);
INSERT INTO "Seven" VALUES (NULL, '0103000020677F000001000000050000000000000080841E410000000080841E410000000080841E410000000010861E410000000010861E410000000010861E410000000010861E410000000080841E410000000080841E410000000080841E41', '0101000020677F00000000000048851E410000000048851E41', 5);
INSERT INTO "Seven" VALUES (NULL, '0103000020677F000001000000050000000000000080841E410000000080841E410000000080841E410000000010861E410000000010861E410000000010861E410000000010861E410000000080841E410000000080841E410000000080841E41', '0101000020677F00000000000048851E410000000048851E41', 6);
INSERT INTO "Seven" VALUES (NULL, '0103000020677F000001000000050000000000000080841E410000000080841E410000000080841E410000000010861E410000000010861E410000000010861E410000000010861E410000000080841E410000000080841E410000000080841E41', '0101000020677F00000000000048851E410000000048851E41', 7);


--
-- Data for Name: Updates
--

INSERT INTO "Updates" VALUES ('0103000020677F000001000000050000000000000080841E410000000080841E410000000080841E410000000010861E410000000010861E410000000010861E410000000010861E410000000080841E410000000080841E410000000080841E41', 'tu0001', '0101000020677F00000000000048851E410000000048851E41', 1);
INSERT INTO "Updates" VALUES ('0103000020677F000001000000050000000000000080841E410000000080841E410000000080841E410000000010861E410000000010861E410000000010861E410000000010861E410000000080841E410000000080841E410000000080841E41', 'tu0002', '0101000020677F00000000000048851E410000000048851E41', 2);
INSERT INTO "Updates" VALUES ('0103000020677F000001000000050000000000000080841E410000000080841E410000000080841E410000000010861E410000000010861E410000000010861E410000000010861E410000000080841E410000000080841E410000000080841E41', 'tu0003', '0101000020677F00000000000048851E410000000048851E41', 3);
INSERT INTO "Updates" VALUES ('0103000020677F000001000000050000000000000080841E410000000080841E410000000080841E410000000010861E410000000010861E410000000010861E410000000010861E410000000080841E410000000080841E410000000080841E41', 'tu0004', '0101000020677F00000000000048851E410000000048851E41', 4);
