-- 11.5.4 COPYRIGHT (C) UNIFY CORPORATION 1984-2009
remark

this statement creates a new user id which can be used by DBAs to access
MAIN database

;


grant connect to guest identified by guest;

remark

these statements create the tables in the main database needed to manage the
allocation of space for partitioned databases

;

create table defaults  (stogroup char(36));

create table databases (name char(16),
			stogroup char(36),
			logstogroup char(36));

create table stogroups (name char(36));

create table areas     (name char(36),
			areaid number,
			pathname char(128),
			areasize number);

create table stoareas  (stogroup char(36),
			areaname char(36));

create table extents   (areaid number,
			offset number,
			extsize number,
			dbname char(16),
			filename char(128),
			filepos number);

create table freeexts  (areaid number,
			offset number,
			extsize number);

remark

this statements create the views/synonyms, which can be accessed by the
public, in the MAIN database.

;

create view syssql.defaults as
	    select stogroup from sysadm.defaults;

grant select on syssql.defaults to public;

create public synonym defaults for syssql.defaults;

create view syssql.databases  as
	    select name, stogroup, logstogroup from sysadm.databases;

grant select on syssql.databases to public;

create public synonym databases for syssql.databases;

create view syssql.stogroups as
	    select name from sysadm.stogroups;

grant select on syssql.stogroups to public;

create public synonym stogroups for syssql.stogroups;

create view syssql.areas(name, areasize, pathname) as
	    select name, areasize / 1048576, pathname from sysadm.areas;

grant select on syssql.areas to public;

create public synonym areas for syssql.areas;

create view syssql.stoareas as
	    select stogroup, areaname from sysadm.stoareas;

grant select on syssql.stoareas to public;

create public synonym stoareas for syssql.stoareas;

create view syssql.extents (name, dbname, extsize, offset) as
	    select areas.name,
		   extents.dbname,
		   extents.extsize / 1048576,
		   extents.offset /1048576
		   from sysadm.extents, sysadm.areas where
			extents.areaid = areas.areaid;

grant select on syssql.extents to public;

create public synonym extents for syssql.extents;

create view syssql.freeexts(name, offset, extsize) as
	    select areas.name,
		   freeexts.offset / 1048576,
		   freeexts.extsize / 1048576
		   from sysadm.areas, sysadm.freeexts where
			areas.areaid = freeexts.areaid;

grant select on syssql.freeexts to public;

create public synonym freeexts for syssql.freeexts;

remark

  unique indexes
;

create unique index idxarea  on areas(name);

create unique index idxdbs   on databases(name);

create unique index idxexts  on extents(areaid, offset);

create unique index idxfex   on freeexts(areaid, offset);

create unique index idxsta   on stoareas(stogroup, areaname);

create unique index idxstg   on stogroups(name);


remark

these statements store the commands used to manipulate the main database
tables.

;
remark

 commands involving DATABASES table

;
store selname select   name
	      from     databases
	      where    name = :dbname;

store seldb   select   stogroup
	      from     databases
	      where    name = :dbname;

store sellog  select   logstogroup
	      from     databases
	      where    name = :dbname;

remark

 commands involving EXTENTS table

;
store selsize select   sum(extsize)
	      from     extents
	      where    dbname = :dbname
	      and      filename = :filename;

store selexts select   areaid, offset, extsize
	      from     extents
	      where    dbname = :dbname
	      and      filename = :filename
	      order by filepos;

store newselexts select   areaid, offset, extsize, filepos
	      from  extents
	      where    dbname = :dbname
	      and      filename = :filename
	      order by filepos;

store selexta select   areaid, offset, extsize
	      from     extents
	      where    dbname = :dbname
	      and      filename = :filename
	      order by areaid, offset;

store delfile delete
	      from     extents
	      where    dbname = :dbname
	      and      filename = :filename;

store insext  insert
	      into     extents
	      values  (:areaid,
		       :offset,
		       :extsize,
		       :dbname,
		       :filename,
		       :filepos);

remark

 commands involving AREAS table

;
store selarea select   pathname, areasize
	      from     areas
	      where    areaid = :areaid;



remark

 commands involving FREEEXTS table

;
store selgt   select   f.areaid, f.offset, f.extsize
	      from     freeexts f, areas a, stoareas s
	      where    f.areaid > :areaid
	      and      f.extsize >= :extsize
	      and      s.stogroup = :stogroup
	      and      s.areaname = a.name
	      and      a.areaid = f.areaid
	      order by f.areaid, f.extsize;

store selleq  select   f.areaid, f.offset, f.extsize
	      from     freeexts f, areas a, stoareas s
	      where    f.areaid <= :areaid
	      and      f.extsize >= :extsize
	      and      s.stogroup = :stogroup
	      and      s.areaname = a.name
	      and      a.areaid = f.areaid
	      order by f.areaid, f.extsize;

store selsto  select   f.areaid, f.offset, f.extsize
	      from     freeexts f, areas a, stoareas s
	      where    f.extsize >= :extsize
	      and      s.stogroup = :stogroup
	      and      s.areaname = a.name
	      and      a.areaid = f.areaid
	      order by f.extsize;

store selsame select   offset, extsize
	      from     freeexts
	      where    areaid = :areaid
	      and      extsize >= :extsize
	      order by extsize;

store delfree delete
	      from     freeexts
	      where    areaid = :areaid
	      and      offset = :offset;

store updfree update   freeexts
	      set      offset = :newoffset,
		       extsize = :newextsize
	      where    areaid = :areaid
	      and      offset = :offset;

store selcomb select   offset, extsize
	      from     freeexts
	      where    areaid = :areaid
	      and     (offset+extsize = :offset or
		       offset = :endoffset)
	      order by offset;

store insfree insert
	      into     freeexts
	      values  (:areaid,
		       :offset,
		       :extsize);



remark

  commands for front end

;
store selaid  select areaid from areas where areaid = :1;

store insare  insert into areas values (:1, :2, :3, :4);

store insfex  insert into freeexts values(:1, :2, :3);

store selasf  select areasize, pathname, areaid from areas where name = :1;

store seleps  select areas.areaid from areas, extents
		     where extents.offset + extents.extsize > :1
			   and areas.name = :2
			   and areas.areaid = extents.areaid;

store selasz  select areasize from areas where name = :1;

store updasz  update areas set areasize = :1 where name = :2;

store selsta  select areaname from stoareas where areaname = :1;

store selexa  select extents.areaid from extents, areas
		     where areas.areaid = extents.areaid
			   and areas.name = :1;

store delfex  delete from freeexts where areaid =
		     (select areaid from areas where name = :1);

store delare  delete from areas where name = :1;

store insstg  insert into stogroups values (:1);

store inssta  insert into stoareas values (:1, :2);

store delsta  delete from stoareas where stogroup = :1 and areaname = :2;

store selsgd  select stogroup from databases where stogroup = :1 or logstogroup = :1;

store seldfs  select stogroup from defaults;

store seldfl  select stogroup from defaults where stogroup = :1;

store delstg  delete from stogroups where name = :1;

store delasa  delete from stoareas where stogroup = :1;

store deldfl  delete from defaults;

store insdfl  insert into defaults values (:1);

store selsgp  select name from stogroups where name = :1;

store insdbs  insert into databases values (:1, :2, :3);

store upddbsd update databases set stogroup = :1 where name = :2;

store upddbsl update databases set logstogroup = :1 where name =:2;

store deldbs  delete from databases where name = :1;

store seldbs  select name from databases where name = :1;

store selexf  select filename from extents where dbname = :1;

store seladb  select name from databases;

store selare  select areaid from areas where name = :1;

store updfxs  update freeexts set extsize = extsize - :1
		     where areaid = :2 and offset + extsize > :3;

store delfxs  delete from freeexts where extsize = 0;


remark
*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=* NEWLY ADDED *=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=
;

remark
  The following command selects the number of log files that use a particular
  area.
;

store SelNumLogs
      select count(*)
      from   areas, stoareas, databases
      where
	     areas.areaid = :areaid		and
	     stoareas.areaname = areas.name	and
	     databases.logstogroup = stoareas.stogroup
;
remark
------------------------------------------------------------------------------
 The following are used to maintain 2-phase commit information
------------------------------------------------------------------------------
;

create table CSVTRANS (id1		varchar(128),
		       id2		varchar(128),
		       state		char(20),
		       protocol 	char(20),
		       numparts 	number,
		       committime	datetime,
		       lastmodtime	datetime
		      );


create index CSVTXIX on CSVTRANS(id1);

store INSCSVTX	insert into CSVTRANS values (:1, :2, :3, :4, :5,
					      :6, SYSDATETIME);

store DELCSVTX	delete from CSVTRANS where id1 = :1 and id2 = :2;

store UPDCSVTX	update CSVTRANS set numparts = numparts - :1
		where  id1=:2 and id2=:3;

store SELCSVTX	select id1, id2, state, protocol, numparts, committime
		from CSVTRANS;

store SELCSVT1	select id1, id2, state, protocol, numparts, committime
		from CSVTRANS
		where id1 = :1 and id2=:2;

create view CSVTRANSV (id, state, protocol, numparts, committime,
		      lastmodtime)  as
	    select @substring(id1 || id2, 0, 40), state, protocol,
		   numparts, committime, lastmodtime
		   from SYSADM.CSVTRANS;

create view SYSSQL.CSVTRANS (id, state, protocol, numparts, committime,
			     lastmodtime)  as
	    select id1 || id2, state, protocol,
		   numparts, committime, lastmodtime
		   from SYSADM.CSVTRANS;

grant select on SYSSQL.CSVTRANS to public;


create table CSVPARTS (id1		varchar(128),
		       id2		varchar(128),
		       db		char(16),
		       username 	char(8),
		       "password"	char(16)
		      );

create index CSVPTIX on CSVPARTS(id1);

store INSCSVPT	insert into CSVPARTS values (:1, :2, :3, :4, :5);

store DELCSVPT	delete from CSVPARTS where id1 = :1 and id2=:2;

store DELCSVPA	delete from  CSVPARTS
		       where id1=:1 and id2=:2 and db=:3 and
			     username=:4 and "password"=:5;

store SELCSVPT	select id1, id2, db, username, "password" from	CSVPARTS
							    where id1= :1 and
								  id2= :2;

store CNTCSVPT	select count(*) from CSVPARTS where id1 = :1 and id2 = :2;


create view CSVPARTSV (id, db, username, "password") as
	    select @substring(id1 || id2, 0, 40), db, username, "password"
	    from   SYSADM.CSVPARTS;

create view SYSSQL.CSVPARTS (id, db, username) as
	    select id1 || id2, db, username from SYSADM.CSVPARTS;

grant select on SYSSQL.CSVPARTS to public;
