-- =====================================================================================
-- Firebird SQL generated from XMI eported by Enterprise Architect/2.5
-- DO NOT EDIT! THIS IS A GENERATED FILE!
-- =====================================================================================

-- ======================================
-- Package: Data Model
-- ======================================
DROP TABLE "SEC_USER_PROPS";
DROP TABLE "SEC_USER_PROPF";
DROP TABLE "SEC_USER_PERMISSION";
DROP TABLE "SEC_MEMBERSHIP";
DROP TABLE "SEC_GROUP_PROPS";
DROP TABLE "SEC_GROUP_PROPF";
DROP TABLE "SEC_GROUP_PERMISSION";
DROP TABLE "SEC_USER";
DROP TABLE "SEC_PROP_TYPES";
DROP TABLE "SEC_PERMISSION_TYPE";
DROP TABLE "SEC_GROUP";

-- --------------------------------------
-- Table: SEC_GROUP
-- --------------------------------------

CREATE TABLE "SEC_GROUP" (
    "GROUP_ID" INTEGER NOT NULL PRIMARY KEY,
    "GROUPNAME" VARCHAR(64) NOT NULL
);

CREATE UNIQUE ASCENDING INDEX "SEC_GROUP$I1" ON "SEC_GROUP" ("GROUPNAME");

-- --------------------------------------
-- Table: SEC_PERMISSION_TYPE
-- --------------------------------------

CREATE TABLE "SEC_PERMISSION_TYPE" (
    "PERMISSION_ID" INTEGER NOT NULL PRIMARY KEY,
    "PERMISSION" VARCHAR(64) CHARACTER SET NONE NOT NULL COLLATE NONE
);

CREATE INDEX "SEC_PERMISSION_TYPE$PERMISSION" ON "SEC_PERMISSION_TYPE" ("PERMISSION");

-- --------------------------------------
-- Table: SEC_PROP_TYPES
-- --------------------------------------

CREATE TABLE "SEC_PROP_TYPES" (
    "PROP_ID" INTEGER NOT NULL PRIMARY KEY,
    "PROPNAME" VARCHAR(64) NOT NULL
);

CREATE INDEX "SEC_PROP_TYPES$I1" ON "SEC_PROP_TYPES" ("PROPNAME");

-- --------------------------------------
-- Table: SEC_USER
-- --------------------------------------

CREATE TABLE "SEC_USER" (
    "USER_ID" INTEGER NOT NULL PRIMARY KEY,
    "USERNAME" VARCHAR(64),
    "ACTIVE" CHAR(1) NOT NULL,
    "PASSWORD" VARCHAR(64)
);

CREATE INDEX "SEC_USER$I1" ON "SEC_USER" ("USERNAME");

-- --------------------------------------
-- Table: SEC_GROUP_PERMISSION
-- --------------------------------------

CREATE TABLE "SEC_GROUP_PERMISSION" (
    "GROUP_ID" INTEGER NOT NULL REFERENCES "SEC_GROUP" ("GROUP_ID"),
    "PERMISSION_ID" INTEGER NOT NULL REFERENCES "SEC_PERMISSION_TYPE" ("PERMISSION_ID"),
    PRIMARY KEY ("GROUP_ID","PERMISSION_ID")
);


-- --------------------------------------
-- Table: SEC_GROUP_PROPF
-- --------------------------------------

CREATE TABLE "SEC_GROUP_PROPF" (
    "ID" INTEGER NOT NULL PRIMARY KEY,
    "GROUP_ID" INTEGER NOT NULL REFERENCES "SEC_GROUP" ("GROUP_ID"),
    "PROP_ID" INTEGER NOT NULL REFERENCES "SEC_PROP_TYPES" ("PROP_ID"),
    "INDEX" INTEGER NOT NULL,
    "VALUE" BLOB SEGMENT SIZE 512
);

CREATE INDEX "SEC_GROUP_PROPF$I1" ON "SEC_GROUP_PROPF" ("GROUP_ID","PROP_ID","INDEX");

-- --------------------------------------
-- Table: SEC_GROUP_PROPS
-- --------------------------------------

CREATE TABLE "SEC_GROUP_PROPS" (
    "ID" INTEGER NOT NULL PRIMARY KEY,
    "GROUP_ID" INTEGER NOT NULL REFERENCES "SEC_GROUP" ("GROUP_ID"),
    "PROP_ID" INTEGER NOT NULL REFERENCES "SEC_PROP_TYPES" ("PROP_ID"),
    "INDEX" INTEGER NOT NULL,
    "VALUE" VARCHAR(64)
);

CREATE INDEX "SEC_GROUP_PROPS$I1" ON "SEC_GROUP_PROPS" ("GROUP_ID","PROP_ID","INDEX");

-- --------------------------------------
-- Table: SEC_MEMBERSHIP
-- --------------------------------------

CREATE TABLE "SEC_MEMBERSHIP" (
    "GROUP_ID" INTEGER NOT NULL REFERENCES "SEC_GROUP" ("GROUP_ID"),
    "USER_ID" INTEGER NOT NULL REFERENCES "SEC_USER" ("USER_ID"),
    PRIMARY KEY ("GROUP_ID","USER_ID")
);


-- --------------------------------------
-- Table: SEC_USER_PERMISSION
-- --------------------------------------

CREATE TABLE "SEC_USER_PERMISSION" (
    "USER_ID" INTEGER NOT NULL REFERENCES "SEC_USER" ("USER_ID"),
    "PERMISSION_ID" INTEGER NOT NULL REFERENCES "SEC_PERMISSION_TYPE" ("PERMISSION_ID"),
    PRIMARY KEY ("USER_ID","PERMISSION_ID")
);

CREATE INDEX "SEC_USER_PERMISSION$I1" ON "SEC_USER_PERMISSION" ("PERMISSION_ID");
CREATE INDEX "SEC_USER_PERMISSION$I2" ON "SEC_USER_PERMISSION" ("USER_ID");

-- --------------------------------------
-- Table: SEC_USER_PROPF
-- --------------------------------------

CREATE TABLE "SEC_USER_PROPF" (
    "ID" INTEGER NOT NULL PRIMARY KEY,
    "USER_ID" INTEGER NOT NULL REFERENCES "SEC_USER" ("USER_ID"),
    "PROP_ID" INTEGER NOT NULL REFERENCES "SEC_PROP_TYPES" ("PROP_ID"),
    "INDEX" INTEGER NOT NULL,
    "VALUE" BLOB SEGMENT SIZE 512
);

CREATE INDEX "SEC_USER_PROPF$I1" ON "SEC_USER_PROPF" ("USER_ID","PROP_ID","INDEX");

-- --------------------------------------
-- Table: SEC_USER_PROPS
-- --------------------------------------

CREATE TABLE "SEC_USER_PROPS" (
    "ID" INTEGER NOT NULL PRIMARY KEY,
    "USER_ID" INTEGER NOT NULL REFERENCES "SEC_USER" ("USER_ID"),
    "PROP_ID" INTEGER NOT NULL REFERENCES "SEC_PROP_TYPES" ("PROP_ID"),
    "INDEX" INTEGER NOT NULL,
    "VALUE" VARCHAR(64)
);

CREATE INDEX "SEC_USER_PROPS$I1" ON "SEC_USER_PROPS" ("USER_ID","PROP_ID","INDEX");
-- EOF