ÿþ/************************************************************

*

* ElevateDB Reverse-Engineered script for the

* TESTEXP2012 database

*

* Generated on 01/03/2012 08:37:54

* By the user LUCIAN

*

************************************************************/



SCRIPT

BEGIN



/************************************************************

* Tables

************************************************************/



EXECUTE IMMEDIATE 'CREATE TABLE "categories"

(

"Name" VARCHAR(20) COLLATE "UNI_CI" NOT NULL

)

VERSION 1.00

READWRITE

UNENCRYPTED

INDEX PAGE SIZE 8192

BLOB BLOCK SIZE 1024

PUBLISH BLOCK SIZE 1024

PUBLISH COMPRESSION 6

MAX ROW BUFFER SIZE 32768

MAX INDEX BUFFER SIZE 65536

MAX BLOB BUFFER SIZE 32768

MAX PUBLISH BUFFER SIZE 32768';



EXECUTE IMMEDIATE 'CREATE TABLE "subcat"

(

"Category" VARCHAR(40) COLLATE "UNI_CI" NOT NULL,

"Name" VARCHAR(40) COLLATE "UNI_CI" NOT NULL

)

VERSION 1.00

READWRITE

UNENCRYPTED

INDEX PAGE SIZE 8192

BLOB BLOCK SIZE 1024

PUBLISH BLOCK SIZE 1024

PUBLISH COMPRESSION 6

MAX ROW BUFFER SIZE 32768

MAX INDEX BUFFER SIZE 65536

MAX BLOB BUFFER SIZE 32768

MAX PUBLISH BUFFER SIZE 32768';



EXECUTE IMMEDIATE 'CREATE TABLE "stuff"

(

"IDCol" INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 0, INCREMENT BY 1) NOT NULL,

"Category" VARCHAR(20) COLLATE "UNI_CI" NOT NULL,

"SubCategory" VARCHAR(40) COLLATE "UNI_CI" NOT NULL,

"DateWhen" DATE DEFAULT CURRENT_DATE

)

VERSION 1.00

READWRITE

UNENCRYPTED

INDEX PAGE SIZE 8192

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 "categories" VALUES (''ALFA'')';



EXECUTE IMMEDIATE 'INSERT INTO "categories" VALUES (''BETA'')';



EXECUTE IMMEDIATE 'INSERT INTO "categories" VALUES (''GAMA'')';



EXECUTE IMMEDIATE 'INSERT INTO "subcat" VALUES (''ALFA'',

                  ''ONE'')';



EXECUTE IMMEDIATE 'INSERT INTO "subcat" VALUES (''ALFA'',

                  ''THREE'')';



EXECUTE IMMEDIATE 'INSERT INTO "subcat" VALUES (''ALFA'',

                  ''TWO'')';



EXECUTE IMMEDIATE 'INSERT INTO "subcat" VALUES (''BETA'',

                  ''ONE'')';



EXECUTE IMMEDIATE 'INSERT INTO "subcat" VALUES (''BETA'',

                  ''THREE'')';



EXECUTE IMMEDIATE 'INSERT INTO "subcat" VALUES (''BETA'',

                  ''TWO'')';



EXECUTE IMMEDIATE 'INSERT INTO "subcat" VALUES (''GAMA'',

                  ''FOUR'')';



EXECUTE IMMEDIATE 'INSERT INTO "subcat" VALUES (''GAMA'',

                  ''ONE'')';



EXECUTE IMMEDIATE 'INSERT INTO "subcat" VALUES (''GAMA'',

                  ''THREE'')';



EXECUTE IMMEDIATE 'INSERT INTO "subcat" VALUES (''GAMA'',

                  ''TWO'')';



EXECUTE IMMEDIATE 'INSERT INTO "stuff" VALUES (1,

                  ''ALFA'',

                  ''ONE'',

                  DATE ''2012-03-01'')';



EXECUTE IMMEDIATE 'INSERT INTO "stuff" VALUES (2,

                  ''BETA'',

                  ''TWO'',

                  DATE ''2012-03-01'')';



EXECUTE IMMEDIATE 'INSERT INTO "stuff" VALUES (3,

                  ''GAMA'',

                  ''THREE'',

                  DATE ''2012-03-01'')';





/************************************************************

* Views

************************************************************/





/************************************************************

* Functions

************************************************************/





/************************************************************

* Procedures

************************************************************/





/************************************************************

* Table triggers, indexes, and constraints

************************************************************/



-- Creating non-foreign key constraints for categories table



EXECUTE IMMEDIATE 'ALTER TABLE "categories"

ADD CONSTRAINT "PKey" PRIMARY KEY ("Name")';



-- Creating non-foreign key constraints for subcat table



EXECUTE IMMEDIATE 'ALTER TABLE "subcat"

ADD CONSTRAINT "PKey" PRIMARY KEY ("Category", "Name"),

ADD CONSTRAINT "uqSub" UNIQUE ("Name", "Category")';



-- Creating non-foreign key constraints for stuff table



EXECUTE IMMEDIATE 'ALTER TABLE "stuff"

ADD CONSTRAINT "PKey" PRIMARY KEY ("IDCol")';



-- Creating foreign key constraints for subcat table



EXECUTE IMMEDIATE 'ALTER TABLE "subcat"

ADD CONSTRAINT "fkCat" FOREIGN KEY ("Category")

REFERENCES "categories" ("Name")

ON UPDATE RESTRICT

ON DELETE RESTRICT';



-- Creating foreign key constraints for stuff table



EXECUTE IMMEDIATE 'ALTER TABLE "stuff"

ADD CONSTRAINT "fkCat" FOREIGN KEY ("Category")

REFERENCES "categories" ("Name")

ON UPDATE NO ACTION

ON DELETE NO ACTION,

ADD CONSTRAINT "fkSub" FOREIGN KEY ("SubCategory")

REFERENCES "subcat" ("Name", "Category")

ON UPDATE NO ACTION

ON DELETE NO ACTION';



END



/************************************************************

* End of generated SQL

************************************************************/