-- To create database:
-- > sudo su -l postgres
-- > createdb <db-name>
-- > createlang plpgsql <db-name>
-- > psql -d <db-name> -f /usr/share/postgresql-8.3-postgis/lwpostgis.sql
-- > psql -d <db-name> -f /usr/share/postgresql-8.3-postgis/spatial_ref_sys.sql
-- > psql -d <db-name> -f isi_schema.sql

-- ************
-- datasets
-- ************

CREATE TABLE datasets (
    id serial PRIMARY KEY
    ,name varchar not null
	,creator_id integer
);

-- ************
-- features Table
-- This is the original table that contains records of
-- names.
-- ************

CREATE TABLE features (
    id serial primary key
    ,feature_id serial
	,feature_type_id integer
	,creator_id integer
	,dataset_id integer
    ,name character varying
    ,the_geom geometry
    ,CONSTRAINT enforce_dims_the_geom CHECK ((ndims(the_geom) = 2))
    ,CONSTRAINT enforce_srid_the_geom CHECK ((srid(the_geom) = 4326))
);


-- Add geometry to geometry columns
INSERT INTO geometry_columns 
	(f_table_catalog,f_table_schema,f_table_name,f_geometry_column,coord_dimension,srid,type) 
	VALUES ('','public','features','the_geom',2,4326,'GEOMETRY');
 

-- ************
-- FeatureTypes table
-- ************

create table feature_types (
	id              serial primary key
	,name           varchar
	,description    varchar
	,style_map       varchar
);

-- #1 - Ice Feature
INSERT INTO feature_types (name,description,style_map) VALUES ('ice feature', NULL, '{}');
-- #2 - Crack
INSERT INTO feature_types (name,description,style_map) VALUES ('crack', '<p><h3>Tidal cracks</h3></p>
<p>Tidal cracks are cracks that form in the ice due to the influence of daily and monthly tidal stages,
 as influenced by the phases of the moon.  There are several different kinds of tidal cracks that are
 described in each community.</p>
<p>The differences are generally based on: <ul><li>location of origin (or end) of the crack;</li>
 <li>whether or not the opening refreezes;</li> <li>and, the direction or length of the crack.</li></ul></p>
<p>These cracks tend to form in the same place every year, as they are strongly influenced by local
 geography (topography and bathymetry), and tidal zone dynamics.  Because of these semi-regular openings
 in the sea ice, cracks are important habitat for a number of marine mammals, and hunting destinations,
 as well as considerations for hazardous conditions.</p>'
 ,'{default:{strokeColor:"#ffffff"}}');
-- #3 - Open
INSERT INTO feature_types (name,description,style_map) VALUES ('open', '<p><h3>Open areas</h3></p>
<p>Areas within the sea ice that do not freeze over in the winter, are referred to as sarvait or
 aukkarniit by local experts (depending on local dialects), and as polynyas by sea ice researchers.
 These are areas of open water that are surrounded by sea ice, and they do not freeze over throughout
 the winter.  However, sometimes there are community-specific descriptions of polynyas that do freeze
 over temporarily.</p>
<p>These areas are described locally as being kept open by strong currents, which keeps the water and
 ice moving around so that it does not have a chance to freeze.  These strong currents can be caused
 by shallow areas, narrow areas between islands or in smaller straits, or general water circulation
 patterns.  The size or stability of polynya edges are also strongly influenced by the time of day
 (connected to tidal cycles) or month (connected to lunar cycles), and can have thin and dangerous ice
 surrounding them.  These are areas where great caution has to be practiced, and a keen awareness of
 weather and tidal conditions is important to get to and from these areas safely.</p>
<p>This strong water movement not only keeps the area open, which is attractive to marine mammals
 seeking areas to breathe, and it also means that it provides light and nutrients to small organisms
 or crustaceans on the sea floor. This creates a rich and productive habitat for microscopic organisms,
 as well as fish within the water, and migratory seabirds.  This makes polynyas attractive to marine
 mammals and birds for feeding, which also makes them good hunting destinations for hunters.</p>','{}');
-- #4 - 
INSERT INTO feature_types (name,description,style_map) VALUES ('floe edge', '<p><h3>Floe edges</h3></p>
<p>The floe edge, or sinaaq in Inuktitut, generally refers to the edge of the landfast ice (tuvaq),
 where this solid ice attached to land meets the open water.  This open water may also have moving ice
  that has broken up, or multi-year ice within it, so the floe edge is always changing.</p>
<p><b>Why is the floe edge important to communities?</b></p>
<ul><li><b>It is an indicator of seasonal or long term change:</b></li>The floe edge position
 (e.g. distance from the community, or the shoreline) is used to evaluate freezing or melting progress.
 It is also used to compare changes between years.<li><b>It is an important habitat for marine mammals:</b>
</li>Marine mammals need to breathe air, so the location of the floe edge defines how far some mammals
 will travel under water.  It is an area they return to for air and food, and gather to (mate, breed,
 etc-be specific with function). This also makes the floe edge an important hunting destination.<li>
<b>It is constantly influenced by winds and tides:</b></li>The edge is always changing, growing outward
 as it freezes, and breaking off with the winds or tides. It is influenced by moving ice being pushed
 into it, or blown away from the edge.  This makes it a dangerous area to travel, at the same time as
 making it an important hunting destination (see above).<li><b>It is an aid for navigation:</b>
</li>The edge of the ice tends to occur in the same general area and pattern each year, and community
 members are well aware of its position. This means the floe edge can be used to help navigate on the
 ice. This can be used directly because the edge of the ice can be seen and used to follow in specific
 directions. It can be used indirectly because the open water tends to reflect more darkly in the clouds,
 and so it can be used as a gauge of direction even from far away when one cannot see the edge.</ul>
<p>You can learn more about the descriptions or timing of floe edge variations in each community by browsing
 the sea ice maps for Cape Dorset, Igloolik, or Pangnirtung. All of the lines shown in the maps were drawn
 by local community experts during interviews, based on their experiences with traveling along or close to
 the floe edge near their communities.  It was often highlighted by elders and hunters that the floe edge
 changes every year, and so the variations shown in the maps reflect different peoples experiences with
 local floe edges over the years.  It also changes frequently over a season (as noted above), something
 that is very hard to show on maps like this.  Some local experts drew different floe edge lines to show
 different stages of freezing or melting, and others showed variations in different years (especially
 highlighting the differences between past and recent conditions - see section on Sea Ice Changes for
 timeline references). Ultimately, you really have to experience it, and use the sea ice frequently, to
 understand these seasonal changes.</p>','{}');
-- #5 - 
INSERT INTO feature_types (name,description,style_map) VALUES ('melt', '<p><h3>Areas that melt early</h3></p>
<p>Polynyas are one of the most important areas to consider in the spring, as they tend to melt earlier
 than areas with solid ice cover.  They get larger and larger as the spring warmth begins to melt the
 surrounding ice - helped by the continual strong movements of water underneath.  However, there are
 other areas that also tend to melt early in the spring, which require important safety considerations.</p>
<p><b>Where does early melt of sea ice occur, and why does does this area melt before others?</b><ul>
<li><strong>Where rivers drain into the ocean</strong> - With snow melting on the land, ice in the river
 begins to melt in the spring and start flowing into the sea.  The added freshwater from the river causes
 the sea ice to melt faster than even the warmth of the sun or snow melt on the ice along.</li>
<li><strong>Along the shoreline</strong> - The land warms at a faster rate than the water, and so the
 warmth of the land melts the shoreline ice earlier.</li><li><strong>Along the floe edge</strong> - The
 thinner ice at the edge of the floe of ice melts, or starts breaking off.</li><li><strong>Areas with
 strong currents or large tidal variations</strong> - The currents or tides create strong water movements
 below the ice, that can wear away the ice from underneath. This combined with melt on the surface of the
 ice means that these areas tend to melt faster and open up earlier in the spring than other ice-covered
 areas.</li></ul></p>
<p><b>And when might early melt of sea ice occur?</b><ul><li><strong>In windy conditions</strong> -
 Prevailing winds may cause the ice to break, or the winds may secure it to the land fast ice.</li>
<li><strong>During a full moon</strong> - Currents are stronger when the moon is full, commonly referred
 to as the piturniq time of the month in Inuktitut, which means that the ice can wear out/melt faster from
 underneath due to the faster water movements.</li></ul>','{}');
-- #6 - 
INSERT INTO feature_types (name,description,style_map) VALUES ('ice ridge', NULL,'{}');
-- #7 - 
INSERT INTO feature_types (name,description,style_map) VALUES ('physical feature', NULL, '{}');
-- #8 - 
INSERT INTO feature_types (name,description,style_map) VALUES ('reef', NULL, '{}');
-- #9 - 
INSERT INTO feature_types (name,description,style_map) VALUES ('Northern Radio Station', NULL, '{}');
-- #10 - 
INSERT INTO feature_types (name,description,style_map) VALUES ('landmark', NULL, '{}');
-- #11 - 
INSERT INTO feature_types (name,description,style_map) VALUES ('Inuksuks', NULL, '{}');
-- #12 - 
INSERT INTO feature_types (name,description,style_map) VALUES ('travel', '<p><h3>Travel routes</h3></p>
<p>Before moving into permanent settlements, Inuit use to live in different places following
 seasonal changes, availability of animals, and personal and community preferences. To go
 to different places (e.g. fishing and hunting grounds, camps, other settlements), Inuit would
 travel across the land and sea ice (or open water, in the summer). The trails that Inuit followed
 were well known routes, followed every year, generation after generation. Most of these routes are
 still used today. These well-established routes usually are the most efficient ways of reaching a
 particular destination. More importantly, they are true social networks, as Inuit meet while traveling,
 and they can also get help, should the need arise. Land trails follow precise itineraries, often
 following valleys, frozen creeks and lakes, and other areas where traveling is smooth and snow conditions
 adequate. Sea ice trails may vary from year to year, according to the characteristics of the ice, but
 they also follow similar routes according to prior knowledge of dangerous and safe areas to travel.
 The layout of the trails on the ice also depends on the time of the year, as thin ice or open water will
 require that hunters take detours to reach land. Changes on sea ice conditions have a considerable effect
 on traveling patterns.</p>
<p>Some travel routes are relatively short, and connect camps and settlements to hunting or fishing
 grounds, or to other camps. Some routes are very long, connecting distant camps or, nowadays, settlements.
 If one was to put together all Inuit trails on a map, the Arctic would look like an often dense network of
 trails, connecting communities from east to west and from south to north (to get a sense of this, zoom out
 from any of the community-specific maps showing travel routes). The Nunavut communities that were part of
 <a href="project_background.html">ISIUOP</a> are and have been connected through trails or boat traveling.
 Clyde River and Igloolik have been connected through a series of alternative trails traversing the mountainous
 central part of Baffin Island, and the sea ice of North Foxe Basin. Pangnirtung and Clyde are also connected
 through a coastal trail, and Cape Dorset has been linked to the southern camps and settlements of southern
 Baffin Island (e.g. Kimmirut and Iqaluit), which are in turn connected to Pangnirtung. Clyde River and Igloolik,
 in turn, are connected through networks of trails to the communities of Northern Baffin Island, and Igloolik
 to other southern and western communities.</p>
<p>Some trails have changed due to transformations in lifestyles and in transportation means. Dog sled travelers,
 for instance, preferred to use coastal trails, which offered better opportunities for shoeing the runners, and
 more food for the dogs. These trails also went through other coastal camps, providing a social and safety
 network for the travelers. Using snowmobiles, on the contrary, there is more interest in appropriate travel
 conditions and efficiency. However, even land trails are usually entangled with sea ice travel. This is
 mainly because fiords and bays offer excellent travel conditions.</p>
<p>Many of the trails have been used since time immemorial. There are historical records of Inuit using the
 same trails in the 1820s, but some of them are much older. Since the trails get covered with new snow, or
 disappear after the summer melt, the itineraries are retained in people’s  memories, and transmitted from
 generation to generation.</p>','{}');
-- #13 - 
INSERT INTO feature_types (name,description,style_map) VALUES ('hunting', NULL, '{}');
-- #14 - 
INSERT INTO feature_types (name,description,style_map) VALUES ('camp', '<p><h3>Camps</h3></p>
<p>Inuit moved to different locations according to the seasons. There were locations in which
 Inuit used to gather on a regular basis, from small summer camps in land in search of caribou,
 to the large winter and spring camps at the edge of the ice. Winter and spring camps were positioned
 at strategic locations, not far from where sea mammals could be hunted. Particularly good locations
 for hunting were ice leads and open water. Some spring camps were also located on the sea ice itself.
 Inuit in Igloolik remember the times where people would camp on the ice at Agiuppiniq, not far from
 the current settlement. Most camps were situated around places where hunting and/or fishing were
 possible. Although Inuit today do not use those camps on a daily basis, many of them are regularly
 visited today, as people reach hunting and fishing destinations from the settlement. Some of those
 locations are still used today as outpost camps.</p>','{}');
-- #15 - 
INSERT INTO feature_types (name,description,style_map) VALUES ('mine', NULL, NULL);
-- #16 - 
INSERT INTO feature_types (name,description,style_map) VALUES ('usage feature', NULL, NULL);
-- #17 - 
INSERT INTO feature_types (name,description,style_map) VALUES ('meeting place', NULL, NULL);
-- #18 - 
INSERT INTO feature_types (name,description,style_map) VALUES ('fishing', NULL, NULL);
-- #19 - 
INSERT INTO feature_types (name,description,style_map) VALUES ('gathering', NULL, NULL);
-- #20 - 
INSERT INTO feature_types (name,description,style_map) VALUES ('mineral claim', NULL, NULL);

-- ************
-- Modules table
-- ************

create table modules (
	id              serial primary key
	,name           varchar
	,extent         box2d
	,description    varchar
);

-- #1
INSERT INTO modules (name,extent,description) VALUES (
	'Cape Dorset Sea Ice'
	,ST_SetSRID(ST_MakeBox2D(ST_Point(-78.96973, 63.12457),ST_Point(-73.11401 ,65.72611)),4326)
	,E'<div class="intro">
 <b>Welcome to the Cape Dorset Sea Ice Map</b>
</div>
<div class="instructions">
<p>
 To get started, choose one or more types of ice feature to display on the map.
</p>
<p>
 Clicking on a map feature will bring up additional information along with information 
 about the event at which the feature was documented.
</p>
<p>
 Follow the links to the event to browse other features or multi-media connected with the event.
</p>
<p>
 Follow the links to people to see other related events.
</p>
<p>
 You may also search the database using the field above.
</p>
<p>
 Audio and video content on this page may require the 
 <a href="http://apple.com/quicktime/download/">Quicktime</a> software 
 in order to play correctly.
</p>
<p>
 Windows XP users may need to download and install a unicode font that 
 supports syllabic characters in order to see them properly. 
 Inuktitut fonts are available to download from 
 <a href="http://www.tiro.com/syllabics/resources/index.html">Tiro Typeworks</a> 
 and can be installed by extracting them from the downloaded archive file and 
 then dragging the font files into the "C:\\WINDOWS\\Fonts" folder.
</p>
</div>'
);
-- #2
INSERT INTO modules (name,extent,description) VALUES (
	'Igloolik'
	,ST_SetSRID(ST_MakeBox2D(ST_Point(-87.60498,67.98287),ST_Point(-78.04688,70.91664)),4326)
	,E'<div class="intro">
 <b>Welcome to the Igloolik Sea Ice Map</b>
</div>
<div class="instructions">
<p>
 To get started, choose one or more types of ice feature to display on the map.
</p>
<p>
 Clicking on a map feature will bring up additional information along with information 
 about the event at which the feature was documented.
</p>
<p>
 Follow the links to the event to browse other features or multi-media connected with the event.
</p>
<p>
 Follow the links to people to see other related events.
</p>
<p>
 You may also search the database using the field above.
</p>
<p>
 Audio and video content on this page may require the 
 <a href="http://apple.com/quicktime/download/">Quicktime</a> software 
 in order to play correctly.
</p>
<p>
 Windows XP users may need to download and install a unicode font that 
 supports syllabic characters in order to see them properly. 
 Inuktitut fonts are available to download from 
 <a href="http://www.tiro.com/syllabics/resources/index.html">Tiro Typeworks</a> 
 and can be installed by extracting them from the downloaded archive file and 
 then dragging the font files into the "C:\\WINDOWS\\Fonts" folder.
</p>
</div>'
);
-- #3
INSERT INTO modules (name,extent,description) VALUES (
	'Pangnirtung'
	,ST_SetSRID(ST_MakeBox2D(ST_Point(-68.76343,64.70380),ST_Point(-63.64380,66.65733)),4326)
	,E'<div class="intro">
 <b>Welcome to the Pangnirtung Sea Ice Map</b>
</div>
<div class="instructions">
<p>
 To get started, choose one or more types of ice feature to display on the map.
</p>
<p>
 Clicking on a map feature will bring up additional information along with information 
 about the event at which the feature was documented.
</p>
<p>
 Follow the links to the event to browse other features or multi-media connected with the event.
</p>
<p>
 Follow the links to people to see other related events.
</p>
<p>
 You may also search the database using the field above.
</p>
<p>
 Audio and video content on this page may require the 
 <a href="http://apple.com/quicktime/download/">Quicktime</a> software 
 in order to play correctly.
</p>
<p>
 Windows XP users may need to download and install a unicode font that 
 supports syllabic characters in order to see them properly. 
 Inuktitut fonts are available to download from 
 <a href="http://www.tiro.com/syllabics/resources/index.html">Tiro Typeworks</a> 
 and can be installed by extracting them from the downloaded archive file and 
 then dragging the font files into the "C:\\WINDOWS\\Fonts" folder.
</p>
</div>'
);

-- ************
-- Persons
-- Table that contains all known persons.
-- ************

CREATE TABLE persons (
    id serial PRIMARY KEY
    ,name varchar not null
    ,email varchar unique not null
	,creator_id integer
	,dataset_id integer
);

-- ************
-- Roles
-- Table that contains all roles that persons can take.
-- ************

CREATE TABLE roles (
    id serial PRIMARY KEY
    ,name varchar not null
	,creator_id integer
);

insert into roles (name) values ('Interviewer');

-- ************
-- Contributions table
-- ************

create table contributions (
	id              serial primary key
	,title          varchar
	,notes          varchar
	,filename       varchar
	,original_filename varchar
	,mimetype       varchar
	,create_ts      timestamp
	,related_to     integer
	,likes          integer
	,dislikes       integer
	,creator_id     integer
	,dataset_id     integer
);

-- ************
-- FileUseTypes table
-- ************

create table file_use_types (
	id              serial primary key
	,name           varchar
);

SELECT pg_catalog.setval('file_use_types_id_seq', 1, true);
COPY file_use_types (id, name) FROM stdin;
1	hover_sound
\.

-- ************
-- Events table
-- ************

create table events (
	id              serial primary key
	,event_type_id  integer not null
	,description    varchar
	,date           bigint
	,creator_id     integer
	,dataset_id     integer
);

-- ************
-- EventTypes table
-- ************

create table event_types (
	id              serial primary key
	,name           varchar
);

insert into event_types (name) values ('Interview');
insert into event_types (name) values ('Workshop');

-- ************
-- relations table
-- ************

create table relations (
	id                 serial primary key
	,feature_id        integer
	,person_id         integer
	,role_id           integer
	,contribution_id   integer
	,event_id          integer
	,file_use_type_id  integer
	,creator_id        integer
	,dataset_id        integer
);

-- ************
-- Users
-- Table that contains all user credentials.
-- ************

CREATE TABLE users (
    id serial PRIMARY KEY
    ,name varchar not null
    ,email varchar unique not null
    ,password varchar not null
    ,group_id int not null default 10 -- group_id : 0=>anonymous  1=>admin  other=>regular persons
);

insert into users (name,email,password,group_id) values ('Administrator','admin','admin',1);
insert into users (name,email,password) values ('Test User','test','test');
insert into users (name,email,password,group_id) values ('Anonymous User','anonymous','anonymous',0);

-- ***********************
--
-- View hover_sounds
--
-- ***********************

CREATE VIEW hover_sounds AS
SELECT 
	relations.feature_id AS feature_id
	,relations.dataset_id AS dataset_id
	,contributions.filename AS filename 
	,contributions.mimetype AS mimetype 
	FROM relations,contributions 
	WHERE 
	relations.contribution_id=contributions.id 
	AND relations.file_use_type_id=1;


-- ***************
-- dbsec_tables Table
-- ***************

CREATE TABLE dbsec_tables (
    id serial primary key
    ,logical_name character varying
    ,group_id integer
    ,physical_name character varying
    ,priority integer default 0
    ,options character varying
);


-- *****************
-- dbsec_columns Table
-- *****************

CREATE TABLE dbsec_columns (
    id serial primary key
    ,logical_name character varying
    ,group_id integer
    ,column_name character varying
    ,read boolean
    ,write boolean
    ,options character varying
);

-- *****************
-- Load DbSec data
-- *****************

-- *** users

-- admin
INSERT INTO dbsec_tables (logical_name,group_id,physical_name,priority,options) 
	VALUES ('users',1,'users',0,'query:true,insert:true,update:true,delete:true');
INSERT INTO dbsec_columns (logical_name,group_id,column_name,read,write,options) 
	VALUES ('users',1,'id',true,false,'onInsert:{incrementInteger:"users_id_seq"}');
INSERT INTO dbsec_columns (logical_name,group_id,column_name,read,write,options) 
	VALUES ('users',1,'password',false,false,'');
INSERT INTO dbsec_columns (logical_name,group_id,column_name,read,write,options) 
	VALUES ('users',1,'group_id',false,false,'');
	
-- regular users
INSERT INTO dbsec_tables (logical_name,group_id,physical_name,priority,options) 
	VALUES ('users',10,'users',0,'query:true,insert:true,update:true,delete:false');
INSERT INTO dbsec_columns (logical_name,group_id,column_name,read,write,options) 
	VALUES ('users',10,'id',true,false,'onInsert:{incrementInteger:"users_id_seq"}');
INSERT INTO dbsec_columns (logical_name,group_id,column_name,read,write,options) 
	VALUES ('users',10,'password',false,false,'');
INSERT INTO dbsec_columns (logical_name,group_id,column_name,read,write,options) 
	VALUES ('users',10,'group_id',false,false,'');
	
-- anonymous users
INSERT INTO dbsec_tables (logical_name,group_id,physical_name,priority,options) 
	VALUES ('users',0,'users',0,'query:true,insert:false,update:false,delete:false');
INSERT INTO dbsec_columns (logical_name,group_id,column_name,read,write,options) 
	VALUES ('users',0,'id',true,false,'onInsert:{incrementInteger:"users_id_seq"}');
INSERT INTO dbsec_columns (logical_name,group_id,column_name,read,write,options) 
	VALUES ('users',0,'password',false,false,'');
INSERT INTO dbsec_columns (logical_name,group_id,column_name,read,write,options) 
	VALUES ('users',0,'group_id',false,false,'');
INSERT INTO dbsec_columns (logical_name,group_id,column_name,read,write,options) 
	VALUES ('users',0,'email',false,false,''); -- supress e-mail address from anonymous users


-- *** features

-- admin
INSERT INTO dbsec_tables (logical_name,group_id,physical_name,priority,options) 
	VALUES ('features',1,'features',0,'query:true,insert:true,update:true,delete:true');
INSERT INTO dbsec_columns (logical_name,group_id,column_name,read,write,options) 
	VALUES ('features',1,'id',true,false,'onInsert:{incrementInteger:"features_id_seq"}');
INSERT INTO dbsec_columns (logical_name,group_id,column_name,read,write,options) 
	VALUES ('features',1,'feature_id',true,false,'onInsert:{incrementInteger:"features_feature_id_seq"}');
INSERT INTO dbsec_columns (logical_name,group_id,column_name,read,write,options) 
	VALUES ('features',1,'creator_id',true,false,'onInsert:{assignVariable:"user.id"}');
INSERT INTO dbsec_columns (logical_name,group_id,column_name,read,write,options) 
	VALUES ('features',1,'dataset_id',true,false,'onInsert:{assignValue:1},where:[{comparison:"EQUAL",value:1}]');
	
-- regular users
INSERT INTO dbsec_tables (logical_name,group_id,physical_name,priority,options) 
	VALUES ('features',10,'features',0,'query:true,insert:true,update:true,delete:false');
INSERT INTO dbsec_columns (logical_name,group_id,column_name,read,write,options) 
	VALUES ('features',10,'id',true,false,'onInsert:{incrementInteger:"features_id_seq"}');
INSERT INTO dbsec_columns (logical_name,group_id,column_name,read,write,options) 
	VALUES ('features',10,'feature_id',true,false,'onInsert:{incrementInteger:"features_feature_id_seq"}');
INSERT INTO dbsec_columns (logical_name,group_id,column_name,read,write,options) 
	VALUES ('features',10,'creator_id',true,false,'onInsert:{assignVariable:"user.id"}');
INSERT INTO dbsec_columns (logical_name,group_id,column_name,read,write,options) 
	VALUES ('features',10,'dataset_id',true,false,'onInsert:{assignValue:1},where:[{comparison:"EQUAL",value:1}]');
	
-- anonymous users
INSERT INTO dbsec_tables (logical_name,group_id,physical_name,priority,options) 
	VALUES ('features',0,'features',0,'query:true,insert:false,update:false,delete:false');
INSERT INTO dbsec_columns (logical_name,group_id,column_name,read,write,options) 
	VALUES ('features',0,'id',true,false,'onInsert:{incrementInteger:"features_id_seq"}');
INSERT INTO dbsec_columns (logical_name,group_id,column_name,read,write,options) 
	VALUES ('features',0,'feature_id',true,false,'onInsert:{incrementInteger:"features_feature_id_seq"}');
INSERT INTO dbsec_columns (logical_name,group_id,column_name,read,write,options) 
	VALUES ('features',0,'creator_id',true,false,'onInsert:{assignVariable:"user.id"}');
INSERT INTO dbsec_columns (logical_name,group_id,column_name,read,write,options) 
	VALUES ('features',0,'dataset_id',true,false,'onInsert:{assignValue:1},where:[{comparison:"EQUAL",value:1}]');
	
-- *** relations

-- admin
INSERT INTO dbsec_tables (logical_name,group_id,physical_name,priority,options) 
	VALUES ('relations',1,'relations',0,'query:true,insert:true,update:true,delete:true');
INSERT INTO dbsec_columns (logical_name,group_id,column_name,read,write,options) 
	VALUES ('relations',1,'id',true,false,'onInsert:{incrementInteger:"relations_id_seq"}');
INSERT INTO dbsec_columns (logical_name,group_id,column_name,read,write,options) 
	VALUES ('relations',1,'creator_id',true,false,'onInsert:{assignVariable:"user.id"}');
INSERT INTO dbsec_columns (logical_name,group_id,column_name,read,write,options) 
	VALUES ('relations',1,'dataset_id',true,false,'onInsert:{assignValue:1},where:[{comparison:"EQUAL",value:1}]');
	
-- regular user
INSERT INTO dbsec_tables (logical_name,group_id,physical_name,priority,options) 
	VALUES ('relations',10,'relations',0,'query:true,insert:true,update:true,delete:false');
INSERT INTO dbsec_columns (logical_name,group_id,column_name,read,write,options) 
	VALUES ('relations',10,'id',true,false,'onInsert:{incrementInteger:"relations_id_seq"}');
INSERT INTO dbsec_columns (logical_name,group_id,column_name,read,write,options) 
	VALUES ('relations',10,'creator_id',true,false,'onInsert:{assignVariable:"user.id"}');
INSERT INTO dbsec_columns (logical_name,group_id,column_name,read,write,options) 
	VALUES ('relations',10,'dataset_id',true,false,'onInsert:{assignValue:1},where:[{comparison:"EQUAL",value:1}]');
	
-- anonymous users
INSERT INTO dbsec_tables (logical_name,group_id,physical_name,priority,options) 
	VALUES ('relations',0,'relations',0,'query:true,insert:false,update:false,delete:false');
INSERT INTO dbsec_columns (logical_name,group_id,column_name,read,write,options) 
	VALUES ('relations',0,'id',true,false,'onInsert:{incrementInteger:"relations_id_seq"}');
INSERT INTO dbsec_columns (logical_name,group_id,column_name,read,write,options) 
	VALUES ('relations',0,'creator_id',true,false,'onInsert:{assignVariable:"user.id"}');
INSERT INTO dbsec_columns (logical_name,group_id,column_name,read,write,options) 
	VALUES ('relations',0,'dataset_id',true,false,'onInsert:{assignValue:1},where:[{comparison:"EQUAL",value:1}]');
	
-- *** contributions

-- admin
INSERT INTO dbsec_tables (logical_name,group_id,physical_name,priority,options) 
	VALUES ('contributions',1,'contributions',0,'query:true,insert:true,update:true,delete:true');
INSERT INTO dbsec_columns (logical_name,group_id,column_name,read,write,options) 
	VALUES ('contributions',1,'id',true,false,'onInsert:{incrementInteger:"contributions_id_seq"}');
INSERT INTO dbsec_columns (logical_name,group_id,column_name,read,write,options) 
	VALUES ('contributions',1,'creator_id',true,false,'onInsert:{assignVariable:"user.id"}');
INSERT INTO dbsec_columns (logical_name,group_id,column_name,read,write,options) 
	VALUES ('contributions',1,'dataset_id',true,false,'onInsert:{assignValue:1},where:[{comparison:"EQUAL",value:1}]');
	
-- regular user
INSERT INTO dbsec_tables (logical_name,group_id,physical_name,priority,options) 
	VALUES ('contributions',10,'contributions',0,'query:true,insert:true,update:true,delete:false');
INSERT INTO dbsec_columns (logical_name,group_id,column_name,read,write,options) 
	VALUES ('contributions',10,'id',true,false,'onInsert:{incrementInteger:"contributions_id_seq"}');
INSERT INTO dbsec_columns (logical_name,group_id,column_name,read,write,options) 
	VALUES ('contributions',10,'creator_id',true,false,'onInsert:{assignVariable:"user.id"}');
INSERT INTO dbsec_columns (logical_name,group_id,column_name,read,write,options) 
	VALUES ('contributions',10,'dataset_id',true,false,'onInsert:{assignValue:1},where:[{comparison:"EQUAL",value:1}]');
	
-- anonymous users
INSERT INTO dbsec_tables (logical_name,group_id,physical_name,priority,options) 
	VALUES ('contributions',0,'contributions',0,'query:true,insert:false,update:false,delete:false');
INSERT INTO dbsec_columns (logical_name,group_id,column_name,read,write,options) 
	VALUES ('contributions',0,'id',true,false,'onInsert:{incrementInteger:"contributions_id_seq"}');
INSERT INTO dbsec_columns (logical_name,group_id,column_name,read,write,options) 
	VALUES ('contributions',0,'creator_id',true,false,'onInsert:{assignVariable:"user.id"}');
INSERT INTO dbsec_columns (logical_name,group_id,column_name,read,write,options) 
	VALUES ('contributions',0,'dataset_id',true,false,'onInsert:{assignValue:1},where:[{comparison:"EQUAL",value:1}]');

-- *** persons

-- admin
INSERT INTO dbsec_tables (logical_name,group_id,physical_name,priority,options) 
	VALUES ('persons',1,'persons',0,'query:true,insert:true,update:true,delete:true');
INSERT INTO dbsec_columns (logical_name,group_id,column_name,read,write,options) 
	VALUES ('persons',1,'id',true,false,'onInsert:{incrementInteger:"persons_id_seq"}');
INSERT INTO dbsec_columns (logical_name,group_id,column_name,read,write,options) 
	VALUES ('persons',1,'creator_id',true,false,'onInsert:{assignVariable:"user.id"}');
INSERT INTO dbsec_columns (logical_name,group_id,column_name,read,write,options) 
	VALUES ('persons',1,'dataset_id',true,false,'onInsert:{assignValue:1},where:[{comparison:"EQUAL",value:1}]');
	
-- regular user
INSERT INTO dbsec_tables (logical_name,group_id,physical_name,priority,options) 
	VALUES ('persons',10,'persons',0,'query:true,insert:true,update:true,delete:false');
INSERT INTO dbsec_columns (logical_name,group_id,column_name,read,write,options) 
	VALUES ('persons',10,'id',true,false,'onInsert:{incrementInteger:"persons_id_seq"}');
INSERT INTO dbsec_columns (logical_name,group_id,column_name,read,write,options) 
	VALUES ('persons',10,'creator_id',true,false,'onInsert:{assignVariable:"user.id"}');
INSERT INTO dbsec_columns (logical_name,group_id,column_name,read,write,options) 
	VALUES ('persons',10,'dataset_id',true,false,'onInsert:{assignValue:1},where:[{comparison:"EQUAL",value:1}]');
	
-- anonymous users
INSERT INTO dbsec_tables (logical_name,group_id,physical_name,priority,options) 
	VALUES ('persons',0,'persons',0,'query:true,insert:false,update:false,delete:false');
INSERT INTO dbsec_columns (logical_name,group_id,column_name,read,write,options) 
	VALUES ('persons',0,'id',true,false,'onInsert:{incrementInteger:"persons_id_seq"}');
INSERT INTO dbsec_columns (logical_name,group_id,column_name,read,write,options) 
	VALUES ('persons',0,'creator_id',true,false,'onInsert:{assignVariable:"user.id"}');
INSERT INTO dbsec_columns (logical_name,group_id,column_name,read,write,options) 
	VALUES ('persons',0,'dataset_id',true,false,'onInsert:{assignValue:1},where:[{comparison:"EQUAL",value:1}]');


-- *** roles

-- admin
INSERT INTO dbsec_tables (logical_name,group_id,physical_name,priority,options) 
	VALUES ('roles',1,'roles',0,'query:true,insert:true,update:true,delete:true');
INSERT INTO dbsec_columns (logical_name,group_id,column_name,read,write,options) 
	VALUES ('roles',1,'id',true,false,'onInsert:{incrementInteger:"roles_id_seq"}');
INSERT INTO dbsec_columns (logical_name,group_id,column_name,read,write,options) 
	VALUES ('roles',1,'creator_id',true,false,'onInsert:{assignVariable:"user.id"}');
	
-- regular user
INSERT INTO dbsec_tables (logical_name,group_id,physical_name,priority,options) 
	VALUES ('roles',10,'roles',0,'query:true,insert:true,update:true,delete:false');
INSERT INTO dbsec_columns (logical_name,group_id,column_name,read,write,options) 
	VALUES ('roles',10,'id',true,false,'onInsert:{incrementInteger:"roles_id_seq"}');
INSERT INTO dbsec_columns (logical_name,group_id,column_name,read,write,options) 
	VALUES ('roles',10,'creator_id',true,false,'onInsert:{assignVariable:"user.id"}');
	
-- anonymous users
INSERT INTO dbsec_tables (logical_name,group_id,physical_name,priority,options) 
	VALUES ('roles',0,'roles',0,'query:true,insert:false,update:false,delete:false');
INSERT INTO dbsec_columns (logical_name,group_id,column_name,read,write,options) 
	VALUES ('roles',0,'id',true,false,'onInsert:{incrementInteger:"roles_id_seq"}');
INSERT INTO dbsec_columns (logical_name,group_id,column_name,read,write,options) 
	VALUES ('roles',0,'creator_id',true,false,'onInsert:{assignVariable:"user.id"}');


-- *** datasets

-- admin
INSERT INTO dbsec_tables (logical_name,group_id,physical_name,priority,options) 
	VALUES ('datasets',1,'datasets',0,'query:true,insert:true,update:true,delete:true');
INSERT INTO dbsec_columns (logical_name,group_id,column_name,read,write,options) 
	VALUES ('datasets',1,'id',true,false,'onInsert:{incrementInteger:"datasets_id_seq"}');
INSERT INTO dbsec_columns (logical_name,group_id,column_name,read,write,options) 
	VALUES ('datasets',1,'creator_id',true,false,'onInsert:{assignVariable:"user.id"}');
	
-- regular user
INSERT INTO dbsec_tables (logical_name,group_id,physical_name,priority,options) 
	VALUES ('datasets',10,'datasets',0,'query:true,insert:false,update:false,delete:false');
INSERT INTO dbsec_columns (logical_name,group_id,column_name,read,write,options) 
	VALUES ('datasets',10,'id',true,false,'onInsert:{incrementInteger:"datasets_id_seq"}');
INSERT INTO dbsec_columns (logical_name,group_id,column_name,read,write,options) 
	VALUES ('datasets',10,'creator_id',true,false,'onInsert:{assignVariable:"user.id"}');
	
-- anonymous users
INSERT INTO dbsec_tables (logical_name,group_id,physical_name,priority,options) 
	VALUES ('datasets',0,'datasets',0,'query:true,insert:false,update:false,delete:false');
INSERT INTO dbsec_columns (logical_name,group_id,column_name,read,write,options) 
	VALUES ('datasets',0,'id',true,false,'onInsert:{incrementInteger:"datasets_id_seq"}');
INSERT INTO dbsec_columns (logical_name,group_id,column_name,read,write,options) 
	VALUES ('datasets',0,'creator_id',true,false,'onInsert:{assignVariable:"user.id"}');


-- *** feature_types

-- admin
INSERT INTO dbsec_tables (logical_name,group_id,physical_name,priority,options) 
	VALUES ('feature_types',1,'feature_types',0,'query:true,insert:true,update:true,delete:true');
INSERT INTO dbsec_columns (logical_name,group_id,column_name,read,write,options) 
	VALUES ('feature_types',1,'id',true,false,'onInsert:{incrementInteger:"feature_types_id_seq"}');
	
-- regular user
INSERT INTO dbsec_tables (logical_name,group_id,physical_name,priority,options) 
	VALUES ('feature_types',10,'feature_types',0,'query:true,insert:false,update:false,delete:false');
INSERT INTO dbsec_columns (logical_name,group_id,column_name,read,write,options) 
	VALUES ('feature_types',10,'id',true,false,'onInsert:{incrementInteger:"feature_types_id_seq"}');
	
-- anonymous users
INSERT INTO dbsec_tables (logical_name,group_id,physical_name,priority,options) 
	VALUES ('feature_types',0,'feature_types',0,'query:true,insert:false,update:false,delete:false');
INSERT INTO dbsec_columns (logical_name,group_id,column_name,read,write,options) 
	VALUES ('feature_types',0,'id',true,false,'onInsert:{incrementInteger:"feature_types_id_seq"}');


-- *** event_types

-- admin
INSERT INTO dbsec_tables (logical_name,group_id,physical_name,priority,options) 
	VALUES ('event_types',1,'event_types',0,'query:true,insert:true,update:true,delete:true');
INSERT INTO dbsec_columns (logical_name,group_id,column_name,read,write,options) 
	VALUES ('event_types',1,'id',true,false,'onInsert:{incrementInteger:"event_types_id_seq"}');
	
-- regular user
INSERT INTO dbsec_tables (logical_name,group_id,physical_name,priority,options) 
	VALUES ('event_types',10,'event_types',0,'query:true,insert:true,update:true,delete:false');
INSERT INTO dbsec_columns (logical_name,group_id,column_name,read,write,options) 
	VALUES ('event_types',10,'id',true,false,'onInsert:{incrementInteger:"event_types_id_seq"}');
	
-- anonymous users
INSERT INTO dbsec_tables (logical_name,group_id,physical_name,priority,options) 
	VALUES ('event_types',0,'event_types',0,'query:true,insert:false,update:false,delete:false');
INSERT INTO dbsec_columns (logical_name,group_id,column_name,read,write,options) 
	VALUES ('event_types',0,'id',true,false,'onInsert:{incrementInteger:"event_types_id_seq"}');


-- *** events

-- admin
INSERT INTO dbsec_tables (logical_name,group_id,physical_name,priority,options) 
	VALUES ('events',1,'events',0,'query:true,insert:true,update:true,delete:true');
INSERT INTO dbsec_columns (logical_name,group_id,column_name,read,write,options) 
	VALUES ('events',1,'id',true,false,'onInsert:{incrementInteger:"events_id_seq"}');
INSERT INTO dbsec_columns (logical_name,group_id,column_name,read,write,options) 
	VALUES ('events',1,'creator_id',true,false,'onInsert:{assignVariable:"user.id"}');
INSERT INTO dbsec_columns (logical_name,group_id,column_name,read,write,options) 
	VALUES ('events',1,'dataset_id',true,false,'onInsert:{assignValue:1},where:[{comparison:"EQUAL",value:1}]');
	
-- regular user
INSERT INTO dbsec_tables (logical_name,group_id,physical_name,priority,options) 
	VALUES ('events',10,'events',0,'query:true,insert:true,update:true,delete:false');
INSERT INTO dbsec_columns (logical_name,group_id,column_name,read,write,options) 
	VALUES ('events',10,'id',true,false,'onInsert:{incrementInteger:"events_id_seq"}');
INSERT INTO dbsec_columns (logical_name,group_id,column_name,read,write,options) 
	VALUES ('events',10,'creator_id',true,false,'onInsert:{assignVariable:"user.id"}');
INSERT INTO dbsec_columns (logical_name,group_id,column_name,read,write,options) 
	VALUES ('events',10,'dataset_id',true,false,'onInsert:{assignValue:1},where:[{comparison:"EQUAL",value:1}]');
	
-- anonymous users
INSERT INTO dbsec_tables (logical_name,group_id,physical_name,priority,options) 
	VALUES ('events',0,'events',0,'query:true,insert:false,update:false,delete:false');
INSERT INTO dbsec_columns (logical_name,group_id,column_name,read,write,options) 
	VALUES ('events',0,'id',true,false,'onInsert:{incrementInteger:"events_id_seq"}');
INSERT INTO dbsec_columns (logical_name,group_id,column_name,read,write,options) 
	VALUES ('events',0,'creator_id',true,false,'onInsert:{assignVariable:"user.id"}');
INSERT INTO dbsec_columns (logical_name,group_id,column_name,read,write,options) 
	VALUES ('events',0,'dataset_id',true,false,'onInsert:{assignValue:1},where:[{comparison:"EQUAL",value:1}]');


-- *** hover_sounds

-- admin
INSERT INTO dbsec_tables (logical_name,group_id,physical_name,priority,options) 
	VALUES ('hover_sounds',1,'hover_sounds',0,'query:true,insert:false,update:false,delete:false');
	
-- regular users
INSERT INTO dbsec_tables (logical_name,group_id,physical_name,priority,options) 
	VALUES ('hover_sounds',10,'hover_sounds',0,'query:true,insert:false,update:false,delete:false');
	
-- anonymous users
INSERT INTO dbsec_tables (logical_name,group_id,physical_name,priority,options) 
	VALUES ('hover_sounds',0,'hover_sounds',0,'query:true,insert:false,update:false,delete:false');

CREATE TABLE adhoc_queries (
		id serial primary key
		,label varchar
		,stmt varchar
		);
		
INSERT INTO adhoc_queries (label,stmt) VALUES (
	'contributions from id'
	,'select persons.id,persons.last_name,persons.first_name,persons.media_id,contributions.id as contribution_id,contributions.filename,contributions.mimetype,contributions.story,contributions.translated_story,contributions.caption from persons,contributions where persons.id = (select person_id from relations where role_id = ? and event_id = (select event_id from relations where contribution_id = ? and dataset_id = 1) and dataset_id = 1) and contributions.id=? and persons.dataset_id = 1 and contributions.dataset_id = 1'
	);
INSERT INTO adhoc_queries (label,stmt) VALUES (
	'feature extent from person id'
	,'select ST_XMin(ST_Extent(features.the_geom)) as minx,ST_XMax(ST_Extent(features.the_geom)) as maxx,ST_YMin(ST_Extent(features.the_geom)) as miny,ST_YMax(ST_Extent(features.the_geom)) as maxy from features INNER JOIN (relations AS rel1 INNER JOIN relations AS rel2 ON (rel1.event_id = rel2.event_id)) ON (rel2.feature_id = features.id) WHERE (rel1.person_id = ? AND rel1.dataset_id = ?)'
	);
INSERT INTO adhoc_queries (label,stmt) VALUES (
	'feature ids from person id'
	,'select features.id,features.feature_type_id from features INNER JOIN (relations AS rel1 INNER JOIN relations AS rel2 ON (rel1.event_id = rel2.event_id)) ON (rel2.feature_id = features.id) WHERE (rel1.person_id = ? AND rel1.dataset_id = ?);'
	);
INSERT INTO adhoc_queries (label,stmt) VALUES (
	'module info from id'
	,'select name,description,ST_XMin(extent) as minx,ST_YMin(extent) as miny,ST_XMax(extent) as maxx,ST_YMax(extent) as maxy from modules WHERE id = ?'
	);
	