/*ALIAS>prop_db*/

/*SQLStart>1705*/
CREATE GENERATOR ORG_ID_GEN;
/*SQLStop*/

/*SQLStart>1706*/
CREATE DOMAIN BOOLEAN AS 
SMALLINT
DEFAULT 0 
CHECK (VALUE = 0 OR VALUE = 1) 
/*SQLStop*/


/*SQLStart>1707*/
CREATE TABLE ORIGINALS (
    ORIGINALS_ORIGINALID     INTEGER NOT NULL,
    ORIGINALS_DOCID          INTEGER,
    ORIGINALS_RESID          INTEGER,
    ORIGINALS_FILENAME       VARCHAR(128),
    ORIGINALS_REDACTIONTYPE  INTEGER,
    ORIGINALS_LOCKED         BOOLEAN DEFAULT 0 NOT NULL,
    ORIGINALS_HIDDEN         BOOLEAN DEFAULT 0 NOT NULL,
    ORIGINALS_EDITORID       INTEGER,
    ORIGINALS_COMMENT        VARCHAR(128)
);
/*SQLStop*/


/*SQLStart>1708*/
ALTER TABLE ORIGINALS ADD PRIMARY KEY (ORIGINALS_ORIGINALID);
/*SQLStop*/


/*SQLStart>1709*/
CREATE OR ALTER TRIGGER ORIGINAL_UID FOR ORIGINALS
ACTIVE BEFORE INSERT POSITION 0
as
begin
   originals.originals_originalid= gen_id(org_id_gen, 1);
end
/*SQLStop*/

/*SQLStart>1710*/
GRANT ALL ON ORIGINALS TO PUBLIC;
/*SQLStop*/


/*MANYStart>1711*/
CREATE GENERATOR VER_NUM_GEN;
CREATE GENERATOR VER_DIRNUM_GEN;
/*MANYStop*/

/*SQLStart>1712*/
CREATE TABLE VERSIONS (
    VERSIONS_ORIGINALID     INTEGER NOT NULL,
    VERSIONS_VERSIONNUMBER  INTEGER NOT NULL,
    VERSIONS_DIRNUM         INTEGER NOT NULL,
    VERSIONS_VERSIONDATE    DOUBLE PRECISION,
    VERSIONS_EDITORID       INTEGER,
    VERSIONS_COMMENT        VARCHAR(128)
);
/*SQLStop*/

/*SQLStart>1713*/
ALTER TABLE VERSIONS ADD PRIMARY KEY (VERSIONS_VERSIONNUMBER, VERSIONS_DIRNUM);
/*SQLStop*/


/*SQLStart>1714*/
CREATE OR ALTER TRIGGER VERSION_CNT_EVENT FOR VERSIONS
ACTIVE BEFORE INSERT POSITION 0
as
   declare variable nnum integer;
   declare variable dirnum integer;
begin
   nnum= gen_id(ver_num_gen, 1);

   if(nnum > 1000) then
   begin
      nnum= -nnum;
      dirnum= 1;
   end
   else
   begin
      nnum= 0;
      dirnum= 0;
   end

   versions.versions_versionnumber= gen_id(ver_num_gen, nnum);
   versions.versions_dirnum= gen_id(ver_dirnum_gen, dirnum);
end
/*SQLStop*/

/*SQLStart>1715*/
GRANT ALL ON VERSIONS TO PUBLIC;
/*SQLStop*/