conf/evolutions/default/1.sql

# -- !Ups
CREATE TABLE "StorageEntry" (
  id INTEGER NOT NULL PRIMARY KEY,
  S_ROOT_PATH CHARACTER VARYING,
  S_CLIENT_PATH CHARACTER VARYING,
  S_STORAGE_TYPE CHARACTER VARYING NOT NULL,
  S_USER CHARACTER VARYING,
  S_PASSWORD CHARACTER VARYING,
  S_HOST CHARACTER VARYING,
  I_PORT SMALLINT
);

CREATE SEQUENCE "StorageEntry_id_seq"
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;
ALTER SEQUENCE "StorageEntry_id_seq" OWNED BY "StorageEntry".id;

ALTER TABLE public."StorageEntry_id_seq" OWNER TO projectlocker;

ALTER TABLE "StorageEntry" OWNER TO "projectlocker";

--------------------------------------------------

CREATE TABLE "ProjectType" (
  id INTEGER NOT NULL PRIMARY KEY,
  S_NAME CHARACTER VARYING NOT NULL,
  S_OPENS_WITH CHARACTER VARYING NOT NULL,
  S_TARGET_VERSION CHARACTER VARYING NOT NULL
);

CREATE SEQUENCE "ProjectType_id_seq"
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;

ALTER SEQUENCE "ProjectType_id_seq" OWNED BY "ProjectType".id;
ALTER TABLE public."ProjectType_id_seq" OWNER TO projectlocker;
ALTER TABLE "ProjectType" OWNER TO "projectlocker";

-------------------------------------------------

CREATE TABLE "FileEntry" (
  id INTEGER NOT NULL PRIMARY KEY,
  S_FILEPATH CHARACTER VARYING NOT NULL,
  K_STORAGE_ID INTEGER NOT NULL,
  I_VERSION INTEGER NOT NULL,
  S_USER CHARACTER VARYING NOT NULL,
  T_CTIME TIMESTAMP WITH TIME ZONE NOT NULL,
  T_MTIME TIMESTAMP WITH TIME ZONE NOT NULL,
  T_ATIME TIMESTAMP WITH TIME ZONE NOT NULL,
  B_HAS_CONTENT BOOLEAN NOT NULL DEFAULT FALSE,
  B_HAS_LINK BOOLEAN NOT NULL DEFAULT FALSE
);

CREATE SEQUENCE "FileEntry_id_seq"
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;

ALTER SEQUENCE "FileEntry_id_seq" OWNED BY "FileEntry".id;

ALTER TABLE public."FileEntry_id_seq" OWNER TO projectlocker;

ALTER TABLE "FileEntry" OWNER TO "projectlocker";
CREATE UNIQUE INDEX IX_PATH_STORAGE ON "FileEntry" (S_FILEPATH, K_STORAGE_ID);
ALTER TABLE "FileEntry" ADD CONSTRAINT "fk_storage" FOREIGN KEY (K_STORAGE_ID) REFERENCES "StorageEntry"(id) DEFERRABLE INITIALLY DEFERRED;

------------------------------------------------------

CREATE TABLE "ProjectTemplate" (
  id INTEGER NOT NULL PRIMARY KEY,
  S_NAME CHARACTER VARYING NOT NULL,
  K_PROJECT_TYPE INTEGER NOT NULL,
  K_FILE_REF INTEGER NOT NULL
);
CREATE SEQUENCE "ProjectTemplate_id_seq"
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;

ALTER SEQUENCE "ProjectTemplate_id_seq" OWNED BY "ProjectTemplate".id;
ALTER TABLE public."ProjectTemplate_id_seq" OWNER TO projectlocker;

ALTER TABLE "ProjectTemplate" OWNER TO "projectlocker";
CREATE UNIQUE INDEX IX_TEMPLATE_NAME on "ProjectTemplate" (S_NAME);
ALTER TABLE "ProjectTemplate" ADD CONSTRAINT FK_PROJECT_TYPE FOREIGN KEY (K_PROJECT_TYPE) REFERENCES "ProjectType"(id) DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE "ProjectTemplate" ADD CONSTRAINT FK_FILE_REF FOREIGN KEY (K_FILE_REF) REFERENCES "FileEntry"(id) DEFERRABLE INITIALLY DEFERRED;

---------------------------------------------------

CREATE TABLE "ProjectEntry" (
  id INTEGER NOT NULL PRIMARY KEY,
  K_PROJECT_TYPE INTEGER NOT NULL,
  S_VIDISPINE_ID CHARACTER VARYING NULL,
  S_TITLE CHARACTER VARYING NOT NULL,
  T_CREATED TIMESTAMP WITH TIME ZONE NOT NULL,
  S_USER CHARACTER VARYING NOT NULL
);

CREATE SEQUENCE "ProjectEntry_id_seq"
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;
ALTER TABLE public."ProjectEntry_id_seq" OWNER TO projectlocker;
ALTER SEQUENCE "ProjectEntry_id_seq" OWNED BY "ProjectEntry".id;

ALTER TABLE "ProjectEntry" OWNER TO "projectlocker";
ALTER TABLE "ProjectEntry" ADD CONSTRAINT FK_PROJECT_TYPE FOREIGN KEY (K_PROJECT_TYPE) REFERENCES "ProjectType"(id) DEFERRABLE INITIALLY DEFERRED;

----------------------------------------------------

CREATE TABLE "ProjectFileAssociation" (
  id INTEGER NOT NULL  PRIMARY KEY,
  K_PROJECT_ENTRY INTEGER NOT NULL,
  K_FILE_ENTRY INTEGER NOT NULL
);
CREATE SEQUENCE "ProjectFileAssociation_id_seq"
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;
ALTER SEQUENCE "ProjectFileAssociation_id_seq" OWNED BY "ProjectFileAssociation".id;

ALTER TABLE public."ProjectFileAssociation_id_seq" OWNER TO projectlocker;

ALTER TABLE "ProjectFileAssociation" OWNER TO "projectlocker";
ALTER TABLE "ProjectFileAssociation" ADD CONSTRAINT FK_PROJECT_ENTRY FOREIGN KEY (K_PROJECT_ENTRY) REFERENCES "ProjectEntry"(id) DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE "ProjectFileAssociation" ADD CONSTRAINT FK_FILE_ENTRY FOREIGN KEY (K_FILE_ENTRY) REFERENCES "FileEntry"(id) DEFERRABLE INITIALLY DEFERRED;

--------------------------------------------------

ALTER TABLE ONLY "FileEntry" ALTER COLUMN id SET DEFAULT nextval('"FileEntry_id_seq"'::regclass);

ALTER TABLE ONLY "ProjectEntry" ALTER COLUMN id SET DEFAULT nextval('"ProjectEntry_id_seq"'::regclass);

ALTER TABLE ONLY "ProjectFileAssociation" ALTER COLUMN id SET DEFAULT nextval('"ProjectFileAssociation_id_seq"'::regclass);

ALTER TABLE ONLY "ProjectTemplate" ALTER COLUMN id SET DEFAULT nextval('"ProjectTemplate_id_seq"'::regclass);

ALTER TABLE ONLY "ProjectType" ALTER COLUMN id SET DEFAULT nextval('"ProjectType_id_seq"'::regclass);

ALTER TABLE ONLY "StorageEntry" ALTER COLUMN id SET DEFAULT nextval('"StorageEntry_id_seq"'::regclass);

---------------------------------------------------

# -- !Downs
DROP TABLE "ProjectFileAssociation" CASCADE;
DROP TABLE "ProjectEntry" CASCADE;
DROP TABLE "ProjectTemplate" CASCADE;
DROP TABLE "FileEntry" CASCADE;
DROP TABLE "ProjectType" CASCADE;
DROP TABLE "StorageEntry" CASCADE;