/* Script to create PostgreSQL/PostGIS test tables/views for Quantum GIS to check internationalization, special characters, column types/limits
 * 
 * 3/2/2008 Steven Mizuno
 * 
 * This file must be saved with UTF-8 encoding or compatible. It must be run using a PostgreSQL database utility, such as pgAdmin III, that can handle UTF-8 encoding.
 * 
 * replace @SCHEMA with the schema name you wish to use
 * replace @TABLE with the table name you wish to use
 * (be careful with the view name as it has '_v' tacked on the end of the table's name it is a view for)
 * 
 * One schema/table name to use: "Test Я.Я$"."Test Я.Я#"  This has some punctuation, uppercase and international characters.
 * 
 * The 'Я' (Cyrillic upper case ya) character used in various places is not present in Latin-1 so it should be obvious if there is an encoding problem somewhere.
 * 
 * Not all limits are actually at the limit of the data types, especially float types.
 * Some types really don't have limits.
 * 
 * Not all data types are put in the tables.
 * 
 * At present, the boolean type column is dropped as this type doesn't work in QGIS.
 */


begin;

create schema "@SCHEMA";
grant usage on schema "@SCHEMA" to public;

create table "@SCHEMA"."@TABLE"
(
	"I.D. Я"	serial4 primary key, 
	"Name.name"	character varying(50), 
	"Description Я"	text, 
	int2_col	smallint, 
	int4_col	integer, 
	int8_col	bigint, 
	"character.col"	character(20), 
	float4_col	real, 
	float8_col	double precision, 
	numeric_col	numeric(10,4),
	macaddr_col	macaddr, 
	inet_col	inet, 
	boolean_col	boolean,
	date_col	date
);

select addgeometrycolumn('@SCHEMA', '@TABLE', 'The.GEOM Я', 4326, 'POINT', 2);

grant select on "@SCHEMA"."@TABLE" to public;

comment on table "@SCHEMA"."@TABLE" is 'a test table for QGIS    Я';
comment on column "@SCHEMA"."@TABLE"."I.D. Я" is 'the primary key    Я';
comment on column "@SCHEMA"."@TABLE"."Name.name" is 'the name';
comment on column "@SCHEMA"."@TABLE"."Description Я" is 'the description';
comment on column "@SCHEMA"."@TABLE"."int2_col" is 'int2 column';
comment on column "@SCHEMA"."@TABLE"."int4_col" is 'int4 column';
comment on column "@SCHEMA"."@TABLE"."int8_col" is 'int8 column';
comment on column "@SCHEMA"."@TABLE"."character.col" is 'character column';
comment on column "@SCHEMA"."@TABLE"."float4_col" is 'float4 column';
comment on column "@SCHEMA"."@TABLE"."float8_col" is 'float8 column';
comment on column "@SCHEMA"."@TABLE"."numeric_col" is 'numeric column';
comment on column "@SCHEMA"."@TABLE"."macaddr_col" is 'macaddr column';
comment on column "@SCHEMA"."@TABLE"."inet_col" is 'inet column';
comment on column "@SCHEMA"."@TABLE"."boolean_col" is 'boolean column';
comment on column "@SCHEMA"."@TABLE"."date_col" is 'date column';
comment on column "@SCHEMA"."@TABLE"."The.GEOM Я" is 'the geometry column    Я';
--comment on column "@SCHEMA"."@TABLE"."" is '';

create view "@SCHEMA"."@TABLE_v" as select * from "@SCHEMA"."@TABLE";
grant select on "@SCHEMA"."@TABLE_v" to public;


insert into "@SCHEMA"."@TABLE" ("Name.name", "Description Я", int2_col, int4_col, int8_col, "character.col", float4_col, float8_col, numeric_col, macaddr_col, inet_col, boolean_col, date_col, "The.GEOM Я") values ('lower limits','the lowest value for a column as applicable',-32768,-2147483648,-9223372036854775808,'',1e-37,1e-307,-999999.9999,'00:5d:4a:f2:7c:99','192.168.0.1/24',false,'today',geometryfromtext('POINT(-90 45)',4326));

insert into "@SCHEMA"."@TABLE" ("Name.name", "Description Я", int2_col, int4_col, int8_col, "character.col", float4_col, float8_col, numeric_col, macaddr_col, inet_col, boolean_col, date_col, "The.GEOM Я") values ('upper limits','the highest value for a column as applicable',32767,2147483647,9223372036854775807,'abcdefghijklmnopqrst',1e37,1e308,999999.9999,'00:5d:4a:f2:7c:99','192.168.0.1/24',true,'3/1/2008',geometryfromtext('POINT(-91 46)',4326));

insert into "@SCHEMA"."@TABLE" ("Name.name", "Description Я", int2_col, int4_col, int8_col, "character.col", float4_col, float8_col, numeric_col, macaddr_col, inet_col, boolean_col, date_col, "The.GEOM Я") values ('zero values','zero values as applicable',0,0,0,null,0,0,0.0,'00:00:00:00:00:00','0.0.0.0/32',null,null,geometryfromtext('POINT(-91 45)',4326));

insert into "@SCHEMA"."@TABLE" ("Name.name", "Description Я", int2_col, int4_col, int8_col, "character.col", float4_col, float8_col, numeric_col, macaddr_col, inet_col, boolean_col, date_col, "The.GEOM Я") values ('special values 1','float NaN',0,0,0,null,'NaN','NaN',0.0,'00:00:00:00:00:00','0.0.0.0/32',null,null,geometryfromtext('POINT(-90 45.5)',4326));

insert into "@SCHEMA"."@TABLE" ("Name.name", "Description Я", int2_col, int4_col, int8_col, "character.col", float4_col, float8_col, numeric_col, macaddr_col, inet_col, boolean_col, date_col, "The.GEOM Я") values ('special values 2','float -Infinity',0,0,0,null,'-Infinity','-Infinity',0.0,'00:00:00:00:00:00','0.0.0.0/32',null,null,geometryfromtext('POINT(-90 45.25)',4326));

insert into "@SCHEMA"."@TABLE" ("Name.name", "Description Я", int2_col, int4_col, int8_col, "character.col", float4_col, float8_col, numeric_col, macaddr_col, inet_col, boolean_col, date_col, "The.GEOM Я") values ('special values 3','float Infinity',0,0,0,null,'Infinity','Infinity',0.0,'00:00:00:00:00:00','0.0.0.0/32',null,null,geometryfromtext('POINT(-90 45.75)',4326));

insert into "@SCHEMA"."@TABLE" ("Name.name", "Description Я", int2_col, int4_col, int8_col, "character.col", float4_col, float8_col, numeric_col, macaddr_col, inet_col, boolean_col, date_col, "The.GEOM Я") values ('integer 1+','the integer values larger than the maximum for the next smaller int type applicable',256,32768,2147483648,null,null,null,null,null,null,null,null,geometryfromtext('POINT(-90.25 46)',4326));

insert into "@SCHEMA"."@TABLE" ("Name.name", "Description Я", int2_col, int4_col, int8_col, "character.col", float4_col, float8_col, numeric_col, macaddr_col, inet_col, boolean_col, date_col, "The.GEOM Я") values ('integer 1-','the integer values smaller than the minimum for the next smaller int type applicable',-257,-32769,-2147483649,null,null,null,null,null,null,null,null,geometryfromtext('POINT(-90.75 46)',4326));

/* blank insert statements to add addtional rows
insert into "@SCHEMA"."@TABLE" ("Name.name", "Description Я", int2_col, int4_col, int8_col, "character.col", float4_col, float8_col, numeric_col, macaddr_col, inet_col, boolean_col, date_col, "The.GEOM Я") values ('','',,,,'',,,,'','',,,geometryfromtext('POINT(,)'));

insert into "@SCHEMA"."@TABLE" ("Name.name", "Description Я", int2_col, int4_col, int8_col, "character.col", float4_col, float8_col, numeric_col, macaddr_col, inet_col, boolean_col, date_col, "The.GEOM Я") values ('','',,,,'',,,,'','',,,geometryfromtext('POINT(,)'));
*/

--insert into "@SCHEMA"."@TABLE" () values ();


alter table "@SCHEMA"."@TABLE" drop column boolean_col;	-- 3/2/2008 - boolean doesn't work in QGIS

commit;


/* clean up the test set up
drop view "@SCHEMA"."@TABLE_v";
select dropgeometrycolumn('@SCHEMA','@TABLE','The.GEOM Я');	-- remove entry from geometry_columns table
drop schema "@SCHEMA" cascade;	-- be sure this is what you wnat to do!
 */


