/************************************************************
*
* ElevateDB Reverse-Engineered script for the
* TEST database
*
* Generated on 28/01/2011 08:50:38
* By the user Administrator
*
************************************************************/

SCRIPT
BEGIN

/************************************************************
* Tables
************************************************************/

EXECUTE IMMEDIATE 'CREATE TABLE "users"
(
"RecRef" INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 0, INCREMENT BY 1) NOT NULL,
"Name" VARCHAR(24) COLLATE "UNI_CI" NOT NULL
)
VERSION 1
READWRITE
UNENCRYPTED
INDEX PAGE SIZE 4096
BLOB BLOCK SIZE 1024
PUBLISH BLOCK SIZE 1024
PUBLISH COMPRESSION 0
MAX ROW BUFFER SIZE 32768
MAX INDEX BUFFER SIZE 65536
MAX BLOB BUFFER SIZE 32768
MAX PUBLISH BUFFER SIZE 32768';

EXECUTE IMMEDIATE 'CREATE TABLE "projects"
(
"RecRef" INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 0, INCREMENT BY 1) NOT NULL,
"ParentRef" INTEGER DEFAULT 0  NOT NULL,
"Name" VARCHAR(48) COLLATE "UNI_CI" NOT NULL
)
VERSION 1
READWRITE
UNENCRYPTED
INDEX PAGE SIZE 4096
BLOB BLOCK SIZE 1024
PUBLISH BLOCK SIZE 1024
PUBLISH COMPRESSION 0
MAX ROW BUFFER SIZE 32768
MAX INDEX BUFFER SIZE 65536
MAX BLOB BUFFER SIZE 32768
MAX PUBLISH BUFFER SIZE 32768';

EXECUTE IMMEDIATE 'CREATE TABLE "workfolders"
(
"RecRef" INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 0, INCREMENT BY 1) NOT NULL,
"ProjectRef" INTEGER NOT NULL,
"UserRef" INTEGER NOT NULL,
"ComputerName" VARCHAR(15) COLLATE "UNI_CI" NOT NULL,
"WorkingFolder" VARCHAR(260) COLLATE "UNI_CI"
)
VERSION 1
READWRITE
UNENCRYPTED
INDEX PAGE SIZE 4096
BLOB BLOCK SIZE 1024
PUBLISH BLOCK SIZE 1024
PUBLISH COMPRESSION 0
MAX ROW BUFFER SIZE 32768
MAX INDEX BUFFER SIZE 65536
MAX BLOB BUFFER SIZE 32768
MAX PUBLISH BUFFER SIZE 32768';


/************************************************************
* Table Rows
************************************************************/

EXECUTE IMMEDIATE 'INSERT INTO "users" VALUES (1,
                 ''ADMIN'')';

EXECUTE IMMEDIATE 'INSERT INTO "users" VALUES (2,
                 ''LUCIAN'')';

EXECUTE IMMEDIATE 'INSERT INTO "projects" VALUES (1,
                 0,
                 ''EZDELPHI'')';

EXECUTE IMMEDIATE 'INSERT INTO "projects" VALUES (2,
                 1,
                 ''PROJECTS'')';

EXECUTE IMMEDIATE 'INSERT INTO "projects" VALUES (3,
                 2,
                 ''EZDELPHI'')';

EXECUTE IMMEDIATE 'INSERT INTO "projects" VALUES (4,
                 3,
                 ''DAN'')';

EXECUTE IMMEDIATE 'INSERT INTO "workfolders" VALUES (1,
                 2,
                 2,
                 ''ATLANT'',
                 ''P:\PROJECTS'')';


/************************************************************
* Views
************************************************************/


/************************************************************
* Functions
************************************************************/

EXECUTE IMMEDIATE 'CREATE FUNCTION "AddToken" (IN "InputStr" VARCHAR COLLATE "UNI_CI", IN "DelimStr" VARCHAR COLLATE "UNI_CI", IN "TokenStr" VARCHAR COLLATE "UNI_CI")
RETURNS VARCHAR(510) COLLATE "UNI_CI"
BEGIN
 DECLARE Result VARCHAR DEFAULT '''';

 IF InputStr = '''' THEN
   SET Result = TokenStr;
 ELSE
   IF TokenStr = '''' THEN
     SET Result = InputStr;
   ELSE
     IF DelimStr = '''' THEN
       SET Result = InputStr + TokenStr;
     ELSE
       SET Result = InputStr + DelimStr + TokenStr;
     END IF;
   END IF;
 END IF;

 RETURN Result;
END

VERSION 1';


/************************************************************
* Procedures
************************************************************/

EXECUTE IMMEDIATE 'CREATE PROCEDURE "GetWorkingFolder" (IN "ProjectRef" INTEGER, IN "UserRef" INTEGER, IN "ComputerName" VARCHAR(15) COLLATE "UNI_CI", INOUT "TargetFolder" VARCHAR(260) COLLATE "UNI_CI")
BEGIN
 DECLARE ParentRef INTEGER DEFAULT 0;
 DECLARE ProjectName VARCHAR DEFAULT '''';
 DECLARE WorkingFolder VARCHAR DEFAULT '''';

 DECLARE ProjectCursor CURSOR FOR ProjectStatement;
 DECLARE WFCursor CURSOR FOR WFStatement;

 PREPARE WFStatement FROM ''SELECT WorkingFolder FROM workfolders WHERE ProjectRef=? AND UserRef=? AND UPPER(ComputerName)=UPPER(''''?'''')'';
 OPEN WFCursor USING ProjectRef, UserRef, ComputerName;
 FETCH FIRST FROM WFCursor(''WorkingFolder'') INTO WorkingFolder;
 CLOSE WFCursor;
 UNPREPARE WFStatement;

 SET WorkingFolder=TRIM(BOTH '' '' FROM COALESCE(WorkingFolder, ''''));
 IF WorkingFolder<>'''' THEN
   SET TargetFolder=WorkingFolder;
 ELSE
   PREPARE ProjectStatement FROM ''SELECT ParentRef, Name FROM projects WHERE RecRef=?'';
   OPEN ProjectCursor USING ProjectRef;
   FETCH FIRST FROM ProjectCursor(''ParentRef'', ''Name'') INTO ParentRef, ProjectName;
   CLOSE ProjectCursor;
   UNPREPARE ProjectStatement;
   IF ParentRef>0 THEN
     CALL GetWorkingFolder(ParentRef, UserRef, ComputerName, TargetFolder);
     SET TargetFolder=AddToken(TargetFolder, ''\'', ProjectName);
   END IF;
 END IF;
END

VERSION 1';


/************************************************************
* Table triggers, indexes, and constraints
************************************************************/

-- Creating indexes for projects table

EXECUTE IMMEDIATE 'CREATE INDEX "ixJustParent" ON "projects" ("ParentRef")';

-- Creating indexes for workfolders table

EXECUTE IMMEDIATE 'CREATE INDEX "ixComp" ON "workfolders" ("ComputerName" COLLATE "UNI_CI")';

-- Creating non-foreign key constraints for users table

EXECUTE IMMEDIATE 'ALTER TABLE "users"
ADD CONSTRAINT "PKey" PRIMARY KEY ("RecRef"),
ADD CONSTRAINT "ixName" UNIQUE ("Name", "RecRef")';

-- Creating non-foreign key constraints for projects table

EXECUTE IMMEDIATE 'ALTER TABLE "projects"
ADD CONSTRAINT "PKey" PRIMARY KEY ("RecRef"),
ADD CONSTRAINT "ixParent" UNIQUE ("ParentRef", "Name")';

-- Creating non-foreign key constraints for workfolders table

EXECUTE IMMEDIATE 'ALTER TABLE "workfolders"
ADD CONSTRAINT "PKey" PRIMARY KEY ("RecRef"),
ADD CONSTRAINT "uqKey" UNIQUE ("UserRef", "ProjectRef", "ComputerName")';

-- Creating foreign key constraints for workfolders table

EXECUTE IMMEDIATE 'ALTER TABLE "workfolders"
ADD CONSTRAINT "fkProject" FOREIGN KEY ("ProjectRef")
REFERENCES "projects" ("RecRef")
ON UPDATE NO ACTION
ON DELETE NO ACTION,
ADD CONSTRAINT "fkUser" FOREIGN KEY ("UserRef")
REFERENCES "users" ("RecRef")
ON UPDATE NO ACTION
ON DELETE NO ACTION';

END

/************************************************************
* End of generated SQL
************************************************************/