--
-- Test Scenario
--
-- Problem: Different results on Linux & Windows. The two 
--	    queries under consideration are at the end of
--	    the script.
--

DROP DATABASE test_postgis;
CREATE DATABASE test_postgis;
\c test_postgis

CREATE  TABLE roads ( ID int4, NAME varchar(128) );

CREATE OR REPLACE FUNCTION AddGeometryColumn(varchar,varchar,varchar,varchar,integer,varchar,integer)
	RETURNS text
	AS
$$
DECLARE
	catalog_name alias for $1;
	schema_name alias for $2;
	table_name alias for $3;
	column_name alias for $4;
	new_srid alias for $5;
	new_type alias for $6;
	new_dim alias for $7;
	rec RECORD;
	sr varchar;
	real_schema name;
	sql text;

BEGIN

	-- Verify geometry type
	IF ( NOT ( (new_type = 'GEOMETRY') OR
			   (new_type = 'GEOMETRYCOLLECTION') OR
			   (new_type = 'POINT') OR
			   (new_type = 'MULTIPOINT') OR
			   (new_type = 'POLYGON') OR
			   (new_type = 'MULTIPOLYGON') OR
			   (new_type = 'LINESTRING') OR
			   (new_type = 'MULTILINESTRING') OR
			   (new_type = 'GEOMETRYCOLLECTIONM') OR
			   (new_type = 'POINTM') OR
			   (new_type = 'MULTIPOINTM') OR
			   (new_type = 'POLYGONM') OR
			   (new_type = 'MULTIPOLYGONM') OR
			   (new_type = 'LINESTRINGM') OR
			   (new_type = 'MULTILINESTRINGM') OR
			   (new_type = 'CIRCULARSTRING') OR
			   (new_type = 'CIRCULARSTRINGM') OR
			   (new_type = 'COMPOUNDCURVE') OR
			   (new_type = 'COMPOUNDCURVEM') OR
			   (new_type = 'CURVEPOLYGON') OR
			   (new_type = 'CURVEPOLYGONM') OR
			   (new_type = 'MULTICURVE') OR
			   (new_type = 'MULTICURVEM') OR
			   (new_type = 'MULTISURFACE') OR
			   (new_type = 'MULTISURFACEM')) )
	THEN
		RAISE EXCEPTION 'Invalid type name - valid ones are:
	POINT, MULTIPOINT,
	LINESTRING, MULTILINESTRING,
	POLYGON, MULTIPOLYGON,
	CIRCULARSTRING, COMPOUNDCURVE, MULTICURVE,
	CURVEPOLYGON, MULTISURFACE,
	GEOMETRY, GEOMETRYCOLLECTION,
	POINTM, MULTIPOINTM,
	LINESTRINGM, MULTILINESTRINGM,
	POLYGONM, MULTIPOLYGONM,
	CIRCULARSTRINGM, COMPOUNDCURVEM, MULTICURVEM
	CURVEPOLYGONM, MULTISURFACEM,
	or GEOMETRYCOLLECTIONM';
		RETURN 'fail';
	END IF;


	-- Verify dimension
	IF ( (new_dim >4) OR (new_dim <0) ) THEN
		RAISE EXCEPTION 'invalid dimension';
		RETURN 'fail';
	END IF;

	IF ( (new_type LIKE '%M') AND (new_dim!=3) ) THEN
		RAISE EXCEPTION 'TypeM needs 3 dimensions';
		RETURN 'fail';
	END IF;


	-- Verify SRID
	IF ( new_srid != -1 ) THEN
		SELECT SRID INTO sr FROM spatial_ref_sys WHERE SRID = new_srid;
		IF NOT FOUND THEN
			RAISE EXCEPTION 'AddGeometryColumns() - invalid SRID';
			RETURN 'fail';
		END IF;
	END IF;


	-- Verify schema
	IF ( schema_name IS NOT NULL AND schema_name != '' ) THEN
		sql := 'SELECT nspname FROM pg_namespace ' ||
			'WHERE text(nspname) = ' || quote_literal(schema_name) ||
			'LIMIT 1';
		RAISE DEBUG '%', sql;
		EXECUTE sql INTO real_schema;

		IF ( real_schema IS NULL ) THEN
			RAISE EXCEPTION 'Schema % is not a valid schemaname', quote_literal(schema_name);
			RETURN 'fail';
		END IF;
	END IF;

	IF ( real_schema IS NULL ) THEN
		RAISE DEBUG 'Detecting schema';
		sql := 'SELECT n.nspname AS schemaname ' ||
			'FROM pg_catalog.pg_class c ' ||
			  'JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace ' ||
			'WHERE c.relkind = ' || quote_literal('r') ||
			' AND n.nspname NOT IN (' || quote_literal('pg_catalog') || ', ' || quote_literal('pg_toast') || ')' ||
			' AND pg_catalog.pg_table_is_visible(c.oid)' ||
			' AND c.relname = ' || quote_literal(table_name);
		RAISE DEBUG '%', sql;
		EXECUTE sql INTO real_schema;

		IF ( real_schema IS NULL ) THEN
			RAISE EXCEPTION 'Table % does not occur in the search_path', quote_literal(table_name);
			RETURN 'fail';
		END IF;
	END IF;


	-- Add geometry column to table
	sql := 'ALTER TABLE ' ||
		quote_ident(real_schema) || '.' || quote_ident(table_name)
		|| ' ADD COLUMN ' || quote_ident(column_name) ||
		' geometry ';
	RAISE DEBUG '%', sql;
	EXECUTE sql;


	-- Delete stale record in geometry_columns (if any)
	sql := 'DELETE FROM geometry_columns WHERE
		f_table_catalog = ' || quote_literal('') ||
		' AND f_table_schema = ' ||
		quote_literal(real_schema) ||
		' AND f_table_name = ' || quote_literal(table_name) ||
		' AND f_geometry_column = ' || quote_literal(column_name);
	RAISE DEBUG '%', sql;
	EXECUTE sql;


	-- Add record in geometry_columns
	sql := 'INSERT INTO geometry_columns (f_table_catalog,f_table_schema,f_table_name,' ||
										  'f_geometry_column,coord_dimension,srid,type)' ||
		' VALUES (' ||
		quote_literal('') || ',' ||
		quote_literal(real_schema) || ',' ||
		quote_literal(table_name) || ',' ||
		quote_literal(column_name) || ',' ||
		new_dim::text || ',' ||
		new_srid::text || ',' ||
		quote_literal(new_type) || ')';
	RAISE DEBUG '%', sql;
	EXECUTE sql;


	-- Add table CHECKs
	sql := 'ALTER TABLE ' ||
		quote_ident(real_schema) || '.' || quote_ident(table_name)
		|| ' ADD CONSTRAINT '
		|| quote_ident('enforce_srid_' || column_name)
		|| ' CHECK (ST_SRID(' || quote_ident(column_name) ||
		') = ' || new_srid::text || ')' ;
	RAISE DEBUG '%', sql;
	EXECUTE sql;

	sql := 'ALTER TABLE ' ||
		quote_ident(real_schema) || '.' || quote_ident(table_name)
		|| ' ADD CONSTRAINT '
		|| quote_ident('enforce_dims_' || column_name)
		|| ' CHECK (ST_NDims(' || quote_ident(column_name) ||
		') = ' || new_dim::text || ')' ;
	RAISE DEBUG '%', sql;
	EXECUTE sql;

	IF ( NOT (new_type = 'GEOMETRY')) THEN
		sql := 'ALTER TABLE ' ||
			quote_ident(real_schema) || '.' || quote_ident(table_name) || ' ADD CONSTRAINT ' ||
			quote_ident('enforce_geotype_' || column_name) ||
			' CHECK (GeometryType(' ||
			quote_ident(column_name) || ')=' ||
			quote_literal(new_type) || ' OR (' ||
			quote_ident(column_name) || ') is null)';
		RAISE DEBUG '%', sql;
		EXECUTE sql;
	END IF;

	RETURN
		real_schema || '.' ||
		table_name || '.' || column_name ||
		' SRID:' || new_srid::text ||
		' TYPE:' || new_type ||
		' DIMS:' || new_dim::text || ' ';
END;
$$
LANGUAGE 'plpgsql' VOLATILE STRICT;




CREATE OR REPLACE FUNCTION geometry_in(cstring)
	RETURNS geometry
	AS 'C:/Program Files/PostgresPlus/8.4SS/bin/postgis-1.4','LWGEOM_in'
	LANGUAGE 'C' IMMUTABLE STRICT;

CREATE OR REPLACE FUNCTION geometry_out(geometry)
	RETURNS cstring
	AS 'C:/Program Files/PostgresPlus/8.4SS/bin/postgis-1.4','LWGEOM_out'
	LANGUAGE 'C' IMMUTABLE STRICT;

CREATE OR REPLACE FUNCTION geometry_analyze(internal)
	RETURNS bool
	AS 'C:/Program Files/PostgresPlus/8.4SS/bin/postgis-1.4', 'LWGEOM_analyze'
	LANGUAGE 'C' VOLATILE STRICT;

CREATE OR REPLACE FUNCTION geometry_recv(internal)
	RETURNS geometry
	AS 'C:/Program Files/PostgresPlus/8.4SS/bin/postgis-1.4','LWGEOM_recv'
	LANGUAGE 'C' IMMUTABLE STRICT;

CREATE OR REPLACE FUNCTION geometry_send(geometry)
	RETURNS bytea
	AS 'C:/Program Files/PostgresPlus/8.4SS/bin/postgis-1.4','LWGEOM_send'
	LANGUAGE 'C' IMMUTABLE STRICT;

CREATE TYPE geometry (
	internallength = variable,
	input = geometry_in,
	output = geometry_out,
	send = geometry_send,
	receive = geometry_recv,
	delimiter = ':',
	analyze = geometry_analyze,
	storage = main
);




CREATE TABLE geometry_columns (
	f_table_catalog varchar(256) not null,
	f_table_schema varchar(256) not null,
	f_table_name varchar(256) not null,
	f_geometry_column varchar(256) not null,
	coord_dimension integer not null,
	srid integer not null,
	type varchar(30) not null,
	CONSTRAINT geometry_columns_pk primary key (
		f_table_catalog,
		f_table_schema,
		f_table_name,
		f_geometry_column )
) WITH OIDS;


CREATE OR REPLACE FUNCTION ST_SRID(geometry)
	RETURNS int4
	AS 'C:/Program Files/PostgresPlus/8.4SS/bin/postgis-1.4','LWGEOM_getSRID'
	LANGUAGE 'C' IMMUTABLE STRICT;

CREATE OR REPLACE FUNCTION ST_NDims(geometry)
	RETURNS smallint
	AS 'C:/Program Files/PostgresPlus/8.4SS/bin/postgis-1.4', 'LWGEOM_ndims'
	LANGUAGE 'C' IMMUTABLE STRICT;


SELECT AddGeometryColumn( '', '', 'roads', 'geom', -1, 'GEOMETRY', 2 );



CREATE OR REPLACE FUNCTION length(geometry)
	RETURNS FLOAT8
	AS 'C:/Program Files/PostgresPlus/8.4SS/bin/postgis-1.4', 'LWGEOM_length_linestring'
	LANGUAGE 'C' IMMUTABLE STRICT;


CREATE OR REPLACE FUNCTION GeometryFromText(text, int4)
	RETURNS geometry
	AS 'C:/Program Files/PostgresPlus/8.4SS/bin/postgis-1.4','LWGEOM_from_text'
	LANGUAGE 'C' IMMUTABLE STRICT;


INSERT INTO ROADS (ID,GEOM,NAME )
VALUES
(4,GeometryFromText('LINESTRING(0 0,10 10)',-1),'Fifth Avenue');


CREATE TABLE buildings (
	 id INTEGER PRIMARY KEY,
	 data text);

CREATE OR REPLACE FUNCTION GeometryType(geometry)
	RETURNS text
	AS 'C:/Program Files/PostgresPlus/8.4SS/bin/postgis-1.4', 'LWGEOM_getTYPE'
	LANGUAGE 'C' IMMUTABLE STRICT;

SELECT AddGeometryColumn('', '','buildings','the_geom','-1','POLYGON',2); 

CREATE OR REPLACE FUNCTION area(geometry)
	RETURNS FLOAT8
	AS 'C:/Program Files/PostgresPlus/8.4SS/bin/postgis-1.4','LWGEOM_area_polygon'
	LANGUAGE 'C' IMMUTABLE STRICT;

INSERT INTO buildings VALUES ( 7, 'Office du Tourisme',GeometryFromText( 'POLYGON((117.36 77.6,128.71 77.60,128.71 59.49,117.36 59.49,117.36 77.6))', -1 ) ); 



--
-- Difference in results with respect to Windows & Linux over here...
--
SELECT area(the_geom) FROM buildings;
SELECT length(geom) FROM roads;
