root/trunk/components/dsl/resources/ome/dsl/data.vm

--
-- Copyright 2006 University of Dundee. All rights reserved.
-- Use is subject to license terms supplied in LICENSE.txt
--

--
-- This file was generated by dsl/resources/ome/dsl/data.vm
--

begin;
set constraints all deferred;

--
-- First, we install a unique constraint so that it is only possible
-- to go from versionA/patchA to versionB/patchB once.
--
alter table dbpatch add constraint unique_dbpatch unique (currentVersion, currentPatch, previousVersion, previousPatch);

--
-- Since this is a table that we will be using in DB-specific ways, we're also going
-- to make working with it a bit simpler.
--
alter table dbpatch alter id set default nextval('seq_dbpatch');
alter table dbpatch alter permissions set default -35;
alter table dbpatch alter message set default 'Updating';

--
-- Then, we insert into the patch table the patch (initialization) which we are currently
-- running so that if anything goes wrong, we'll have some record.
--
insert into dbpatch (currentVersion, currentPatch, previousVersion, previousPatch, message)
             values ('@DBVERSION@',  @DBPATCH@,    '@DBVERSION@',   0,             'Initializing');

--
-- Here we will create the root account and the necessary groups
--
insert into experimenter (id,permissions,version,omename,firstname,lastname)
        values (0,0,0,'root','root','root');
insert into experimenter (id,permissions,version,omename,firstname,lastname)
        values (nextval('seq_experimenter'),0,0,'guest','Guest','Account');
insert into session
        (id,permissions,timetoidle,timetolive,started,closed,defaultpermissions,defaulteventtype,uuid)
        select 0,-35,0,0,now(),now(),'rw----','BOOTSTRAP',0000;
insert into session
        (id,permissions,timetoidle,timetolive,started,closed,defaultpermissions,defaulteventtype,uuid)
        select nextval('seq_session'),-35, 0,0,now(),now(),'rw----','PREVIOUSITEMS','1111';
insert into event (id,permissions,time,status,experimenter,session) values (0,0,now(),'BOOTSTRAP',0,0);
insert into experimentergroup (id,permissions,version,owner_id,group_id,creation_id,update_id,name)
        values (0,-35,0,0,0,0,0,'system');
insert into experimentergroup (id,permissions,version,owner_id,group_id,creation_id,update_id,name)
        values (nextval('seq_experimentergroup'),-35,0,0,0,0,0,'user');
insert into experimentergroup (id,permissions,version,owner_id,group_id,creation_id,update_id,name)
        values (nextval('seq_experimentergroup'),-35,0,0,0,0,0,'default');
insert into experimentergroup (id,permissions,version,owner_id,group_id,creation_id,update_id,name)
        values (nextval('seq_experimentergroup'),-35,0,0,0,0,0,'guest');
insert into eventtype (id,permissions,owner_id,group_id,creation_id,value) values
        (0,-35,0,0,0,'Bootstrap');
insert into groupexperimentermap
        (id,permissions,version,owner_id,group_id,creation_id,update_id, parent, child, child_index)
        values
        (0,-35,0,0,0,0,0,0,0,0);
insert into groupexperimentermap
        (id,permissions,version,owner_id,group_id,creation_id,update_id, parent, child, child_index)
        select nextval('seq_groupexperimentermap'),-35,0,0,0,0,0,1,0,1;
insert into groupexperimentermap
        (id,permissions,version,owner_id,group_id,creation_id,update_id, parent, child, child_index)
        select nextval('seq_groupexperimentermap'),-35,0,0,0,0,0,3,1,0;

update event set type = 0;
update event set experimentergroup = 0;

alter table event alter column type set not null;
alter table event alter column experimentergroup set not null;

##insert into event (id,version,name,e) select next, 0 as version, 'Bootstrap'
##  as name, next from (select nextval('seq_event') as next) as nv;

## ENUMS
#foreach($enum in $types)
#foreach($prop in $enum.properties)
#set($table = ${enum.typeToColumn($enum.id)} )
#if($prop.class.name == "ome.dsl.EntryField")
insert into $table (id,permissions,owner_id,group_id,creation_id,value)
    select nextval('seq_$table'),-35,0,0,0,'$prop.name';
#end
#end
#end

create table configuration ( name varchar(255) primary key, value text );

alter table pixels add column url varchar(2048);
alter table originalfile add column url varchar(2048);
alter table thumbnail add column url varchar(2048);

create table password ( experimenter_id bigint unique not null REFERENCES experimenter (id) , hash char(24), dn text );
insert into password values (0,'@ROOTPASS@'); 
insert into password values (1,'');
-- root can now login with omero.rootpass property value
-- and guest can login with any value

-- Here we have finished initializing this database. 
update dbpatch set message = 'Database ready.', finished = now() 
  where currentVersion = '@DBVERSION@' and 
        currentPatch = @DBPATCH@ and 
        previousVersion = '@DBVERSION@' and 
        previousPatch = 0;

commit;