From 43c8ac69ccc43f25df84dc9f117ae8db06aac3b1 Mon Sep 17 00:00:00 2001 From: =?utf8?q?V=C3=A1s=C3=A1ry=20D=C3=A1niel?= Date: Wed, 2 Oct 2019 14:38:20 +0000 Subject: [PATCH] git-tfs-id: [http://tfs.userrendszerhaz.hu:8080/tfs/DefaultCollection]$/MediaCube;C31556 --- .../environments/development.properties | 83 +++ .../scripts/000_create_changelog.sql | 27 + .../scripts/001_create_structure.sql | 642 ++++++++++++++++++ .../002_init.sql} | 0 .../003_metadata.sql} | 0 .../004_views.sql} | 0 .../005_indexer_view.sql} | 0 7 files changed, 752 insertions(+) create mode 100644 server/user.jobengine.osgi.db/migrations/environments/development.properties create mode 100644 server/user.jobengine.osgi.db/migrations/scripts/000_create_changelog.sql create mode 100644 server/user.jobengine.osgi.db/migrations/scripts/001_create_structure.sql rename server/user.jobengine.osgi.db/migrations/{20190909125326_init.sql => scripts/002_init.sql} (100%) rename server/user.jobengine.osgi.db/migrations/{20190915120000_metadata.sql => scripts/003_metadata.sql} (100%) rename server/user.jobengine.osgi.db/migrations/{20190916120000_views.sql => scripts/004_views.sql} (100%) rename server/user.jobengine.osgi.db/migrations/{20190917120000_indexer_view.sql => scripts/005_indexer_view.sql} (100%) diff --git a/server/user.jobengine.osgi.db/migrations/environments/development.properties b/server/user.jobengine.osgi.db/migrations/environments/development.properties new file mode 100644 index 00000000..0113161c --- /dev/null +++ b/server/user.jobengine.osgi.db/migrations/environments/development.properties @@ -0,0 +1,83 @@ +# +# Copyright 2010-2017 the original author or authors. +# +# Licensed under the Apache License, Version 2.0 (the "License"); +# you may not use this file except in compliance with the License. +# You may obtain a copy of the License at +# +# http://www.apache.org/licenses/LICENSE-2.0 +# +# Unless required by applicable law or agreed to in writing, software +# distributed under the License is distributed on an "AS IS" BASIS, +# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. +# See the License for the specific language governing permissions and +# limitations under the License. +# + +## Base time zone to ensure times are consistent across machines +time_zone=GMT+0:00 + +## The character set that scripts are encoded with +# script_char_set=UTF-8 + +## JDBC connection properties. +driver=com.ibm.db2.jcc.DB2Driver +url=jdbc:db2://localhost:50000/mc:retrieveMessagesFromServerOnGetMessage=true; +username=db2admin +password=password + +# +# A NOTE ON STORED PROCEDURES AND DELIMITERS +# +# Stored procedures and functions commonly have nested delimiters +# that conflict with the schema migration parsing. If you tend +# to use procs, functions, triggers or anything that could create +# this situation, then you may want to experiment with +# send_full_script=true (preferred), or if you can't use +# send_full_script, then you may have to resort to a full +# line delimiter such as "GO" or "/" or "!RUN!". +# +# Also play with the autocommit settings, as some drivers +# or databases don't support creating procs, functions or +# even tables in a transaction, and others require it. +# + +# This ignores the line delimiters and +# simply sends the entire script at once. +# Use with JDBC drivers that can accept large +# blocks of delimited text at once. +send_full_script=false + +# This controls how statements are delimited. +# By default statements are delimited by an +# end of line semicolon. Some databases may +# (e.g. MS SQL Server) may require a full line +# delimiter such as GO. +# These are ignored if send_full_script is true. +delimiter=@ +full_line_delimiter=false + +# If set to true, each statement is isolated +# in its own transaction. Otherwise the entire +# script is executed in one transaction. +# Few databases should need this set to true, +# but some do. +auto_commit=false + +# If set to false, warnings from the database will interrupt migrations. +ignore_warnings=false + +# Custom driver path to allow you to centralize your driver files +# Default requires the drivers to be in the drivers directory of your +# initialized migration directory (created with "migrate init") +# driver_path= + +# Name of the table that tracks changes to the database +changelog=CHANGELOG + +# Migrations support variable substitutions in the form of ${variable} +# in the migration scripts. All of the above properties will be ignored though, +# with the exception of changelog. +# Example: The following would be referenced in a migration file as ${ip_address} +# ip_address=192.168.0.1 + diff --git a/server/user.jobengine.osgi.db/migrations/scripts/000_create_changelog.sql b/server/user.jobengine.osgi.db/migrations/scripts/000_create_changelog.sql new file mode 100644 index 00000000..3195c63c --- /dev/null +++ b/server/user.jobengine.osgi.db/migrations/scripts/000_create_changelog.sql @@ -0,0 +1,27 @@ +-- // Create Changelog + +-- Default DDL for changelog table that will keep +-- a record of the migrations that have been run. + +-- You can modify this to suit your database before +-- running your first migration. + +-- Be sure that ID and DESCRIPTION fields exist in +-- BigInteger and String compatible fields respectively. + +CREATE TABLE ${changelog} ( +ID NUMERIC(20,0) NOT NULL, +APPLIED_AT VARCHAR(25) NOT NULL, +DESCRIPTION VARCHAR(255) NOT NULL +) +@ + +ALTER TABLE ${changelog} +ADD CONSTRAINT PK_${changelog} +PRIMARY KEY (id) +@ + +-- //@UNDO + +DROP TABLE ${changelog} +@ diff --git a/server/user.jobengine.osgi.db/migrations/scripts/001_create_structure.sql b/server/user.jobengine.osgi.db/migrations/scripts/001_create_structure.sql new file mode 100644 index 00000000..b0b4ac35 --- /dev/null +++ b/server/user.jobengine.osgi.db/migrations/scripts/001_create_structure.sql @@ -0,0 +1,642 @@ +-- // Create structure. +-- Migration SQL that makes the change goes here. + +CREATE PROCEDURE SET_CONSTRAINTS(ENFORCE VARCHAR(3)) +LANGUAGE SQL +BEGIN + DECLARE v_tabname VARCHAR(128); + DECLARE v_constname VARCHAR(128); + DECLARE v_rows INTEGER; + DECLARE v_alter_table_sql VARCHAR(256); + DECLARE tmp_cursor CURSOR FOR SELECT tabname, constname FROM syscat.references WHERE TABSCHEMA = CURRENT SCHEMA ORDER BY CONSTNAME,TABNAME, REFTABNAME; + SELECT count(*) INTO v_rows FROM syscat.references WHERE TABSCHEMA = CURRENT SCHEMA; + OPEN tmp_cursor; + WHILE (v_rows > 0) DO + FETCH tmp_cursor INTO v_tabname, v_constname; + SET v_alter_table_sql = 'alter table ' || '"' || current SCHEMA || '"' || '.' || v_tabname || ' alter foreign key ' || v_constname; + IF (ENFORCE = 'NO') THEN + SET v_alter_table_sql = v_alter_table_sql || ' NOT ENFORCED'; + ELSE + SET v_alter_table_sql = v_alter_table_sql || ' ENFORCED'; + END IF; + EXECUTE IMMEDIATE v_alter_table_sql; + SET v_rows = v_rows - 1; + END while; + CLOSE tmp_cursor; +END @ + +CREATE PROCEDURE DROP_DYNAMIC_DATA() +LANGUAGE SQL +BEGIN + DECLARE v_tabname VARCHAR(128); + DECLARE v_rows INTEGER; + DECLARE v_drop_table_sql VARCHAR(256); + DECLARE tmp_cursor CURSOR FOR SELECT tabname FROM syscat.tables WHERE tabname LIKE 'METADATA\_%' ESCAPE '\' AND TABSCHEMA = CURRENT SCHEMA; + SELECT count(*) INTO v_rows FROM syscat.tables WHERE tabname LIKE 'METADATA\_%' ESCAPE '\' AND TABSCHEMA = CURRENT SCHEMA; + OPEN tmp_cursor; + WHILE (v_rows > 0) DO + FETCH tmp_cursor INTO v_tabname; + SET v_drop_table_sql = 'drop table ' || '"' || current SCHEMA || '"' || '.' || v_tabname; + EXECUTE IMMEDIATE v_drop_table_sql; + SET v_rows = v_rows - 1; + END while; + CLOSE tmp_cursor; +END @ + +CREATE TABLE MASTERID +( + ID BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1) PRIMARY KEY, + CREATED TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL +)@ + +CREATE TABLE DOMAINCATEGORY +( + ID BIGINT GENERATED ALWAYS AS IDENTITY (START WITH 1) PRIMARY KEY, + NAME VARCHAR(40) NOT NULL +)@ + +CREATE UNIQUE INDEX UDX_DOMAINCATEGORY_NAME ON DOMAINCATEGORY ("NAME")@ + +CREATE TABLE DOMAIN +( + ID BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1) PRIMARY KEY, + DOMAINCATEGORYID BIGINT NOT NULL, + DISPLAY VARCHAR(200) NOT NULL, + VALUE BIGINT, + CONSTRAINT FK_DOMAIN_DOMAINCATEGORYID FOREIGN KEY (DOMAINCATEGORYID) REFERENCES DOMAINCATEGORY (ID) +)@ + +CREATE INDEX UDX_DOMAIN_DOMAINCATEGORYID_DISPLAY ON DOMAIN ("DOMAINCATEGORYID","DISPLAY")@ + +CREATE TABLE ITEMTYPE +( + ID BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1) PRIMARY KEY, + NAME VARCHAR (40) NOT NULL, + DESCRIPTION VARCHAR (255) NOT NULL, + ISSTATIC CHARACTER (1) NOT NULL, + CONSTRAINT CHK_ITEMTYPE_ISSTATIC CHECK (ISSTATIC in ('Y', 'N')) +)@ + +CREATE UNIQUE INDEX UDX_ITEMTYPE_NAME ON ITEMTYPE ("NAME")@ + +CREATE TABLE METADATATYPE +( + ID BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1) PRIMARY KEY, + NAME VARCHAR(40) NOT NULL, + DATATYPE VARCHAR(10) NOT NULL, + LENGTH INTEGER NOT NULL, + BASETYPE VARCHAR(10) NOT NULL DEFAULT '', + JAVATYPE VARCHAR(255) NOT NULL DEFAULT '' +)@ + +CREATE UNIQUE INDEX UDX_METADATATYPE_NAME ON METADATATYPE ("NAME")@ + +CREATE TABLE METADATAELEMENT +( + ID BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1) PRIMARY KEY, + METADATATYPEID BIGINT NOT NULL, + NAME VARCHAR(40) NOT NULL, + DESCRIPTION VARCHAR(255), + DOMAINCATEGORYID BIGINT , + CONSTRAINT FK_METADATAELEMENT_METADATATYPEID FOREIGN KEY (METADATATYPEID) REFERENCES METADATATYPE (ID), + CONSTRAINT FK_METADATAELEMENT_DOMAINCATEGORYID FOREIGN KEY (DOMAINCATEGORYID) REFERENCES DOMAINCATEGORY (ID) +)@ + +CREATE UNIQUE INDEX UDX_METADATAELEMENT_NAME ON METADATAELEMENT ("NAME")@ + +CREATE TABLE METADATA +( + ID BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1) PRIMARY KEY, + ITEMTYPEID BIGINT NOT NULL, + METADATAELEMENTID BIGINT NOT NULL, + ISDBINDEX CHARACTER(1) NOT NULL, + ISDBUNIQUE CHARACTER(1) NOT NULL, + ISDBNULLABLE CHARACTER(1) NOT NULL, + ISLISTABLE CHARACTER(1) NOT NULL, + ISEDITABLE CHARACTER(1) NOT NULL, + ISGENERATED CHARACTER(1) NOT NULL, + ISSEARCHABLE CHARACTER(1) NOT NULL, + GUITYPE VARCHAR(20) NOT NULL, + LISTTYPE VARCHAR(20) NOT NULL, + STATICTABLE VARCHAR(20) , + POJOFIELD VARCHAR(20) , + SQLFIELD VARCHAR(20) , + PARAMETER VARCHAR(20) , + DOMAINCATEGORYID BIGINT, + CONSTRAINT CHK_METADATA_ISDBINDEX CHECK (ISDBINDEX in ('Y', 'N')), + CONSTRAINT CHK_METADATA_ISDBUNIQUE CHECK (ISDBUNIQUE in ('Y', 'N')), + CONSTRAINT CHK_METADATA_ISDBNULLABLE CHECK (ISDBNULLABLE in ('Y', 'N')), + CONSTRAINT CHK_METADATA_ISEDITABLE CHECK (ISEDITABLE in ('Y', 'N')), + CONSTRAINT CHK_METADATA_ISLISTABLE CHECK (ISLISTABLE in ('Y', 'N')), + CONSTRAINT CHK_METADATA_ISGENERATED CHECK (ISGENERATED in ('Y', 'N')), + CONSTRAINT CHK_METADATA_ISSEARCHABLE CHECK (ISGENERATED in ('Y', 'N')), + CONSTRAINT FK_METADATA_ITEMTYPEID FOREIGN KEY (ITEMTYPEID) REFERENCES ITEMTYPE (ID), + CONSTRAINT FK_METADATA_METADATAELEMENTID FOREIGN KEY (METADATAELEMENTID) REFERENCES METADATAELEMENT (ID) +)@ + +CREATE TABLE DOMAININDEX +( + ID BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1) PRIMARY KEY, + RECORDID BIGINT NOT NULL, + METADATAID BIGINT NOT NULL, + DOMAINID BIGINT NOT NULL, + CONSTRAINT FK_DOMAININDEX_RECORDID FOREIGN KEY (RECORDID) REFERENCES MASTERID (ID), + CONSTRAINT FK_DOMAININDEX_METADATAID FOREIGN KEY (METADATAID) REFERENCES METADATA (ID), + CONSTRAINT FK_DOMAININDEX_DOMAINID FOREIGN KEY (DOMAINID) REFERENCES DOMAIN (ID) +)@ + +CREATE TABLE ITEM +( + ID BIGINT NOT NULL PRIMARY KEY, + ITEMTYPEID BIGINT NOT NULL, + HOUSEID VARCHAR(1000), + TITLE VARCHAR(1000) NOT NULL, + DESCRIPTION VARCHAR(32000), + ISFOLDER CHARACTER(1) NOT NULL DEFAULT 'N', + CREATED TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL, + MODIFIED TIMESTAMP NOT NULL GENERATED BY DEFAULT FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP, + CONSTRAINT CHK_ITEM_ISFOLDER CHECK (ISFOLDER in ('Y', 'N')), + CONSTRAINT FK_ITEM_ID FOREIGN KEY (ID) REFERENCES MASTERID (ID), + CONSTRAINT FK_ITEM_ITEMTYPEID FOREIGN KEY (ITEMTYPEID) REFERENCES ITEMTYPE (ID) +)@ + + +CREATE INDEX IDX_ITEM_TITLE ON ITEM ("TITLE")@ +--CREATE INDEX IDX_ITEM_DESCRIPTION ON ITEM ("DESCRIPTION")@ +CREATE INDEX IDX_ITEM_HOUSEID ON ITEM ("HOUSEID")@ +CREATE INDEX IDX_ITEM_CREATED ON ITEM ("CREATED")@ +CREATE INDEX IDX_ITEM_ISFOLDER ON ITEM ("ISFOLDER")@ + +CREATE TABLE FOLDER +( + ID BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1) PRIMARY KEY, + PARENTID BIGINT NOT NULL, + CHILDID BIGINT NOT NULL, + CONSTRAINT CHK_FOLDER_SELFCHILD CHECK (PARENTID != CHILDID), + CONSTRAINT FK_FOLDER_PARENTID FOREIGN KEY (PARENTID) REFERENCES ITEM (ID), + CONSTRAINT FK_FOLDER_CHILDID FOREIGN KEY (CHILDID) REFERENCES ITEM (ID) +)@ + +CREATE UNIQUE INDEX UDX_FOLDER_NODUPLICATE ON FOLDER ("PARENTID", "CHILDID")@ + +CREATE TRIGGER TRG_INSERT_FOLDER AFTER INSERT ON FOLDER +REFERENCING NEW AS N + FOR EACH ROW UPDATE ITEM SET ISFOLDER = 'Y' WHERE ID = N.PARENTID AND ISFOLDER = 'N'@ + + +CREATE TRIGGER TRG_DELETE_FOLDER AFTER DELETE ON FOLDER +REFERENCING OLD AS O + FOR EACH ROW UPDATE ITEM SET ISFOLDER = 'N' WHERE ID = O.PARENTID AND NOT EXISTS (SELECT * FROM FOLDER WHERE PARENTID = O.PARENTID)@ + + +CREATE TABLE MEDIA +( + ID BIGINT NOT NULL PRIMARY KEY, + ITEMID BIGINT NOT NULL, + ITEMTYPEID BIGINT NOT NULL, + HOUSEID VARCHAR(1000), + TITLE VARCHAR(1000) NOT NULL, + DESCRIPTION VARCHAR(32000), + LENGTH BIGINT NOT NULL, + CREATED TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL, + MODIFIED TIMESTAMP NOT NULL GENERATED BY DEFAULT FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP, + ARCHIVED TIMESTAMP, + POSTER BLOB, + CONSTRAINT FK_MEDIA_ID FOREIGN KEY (ID) REFERENCES MASTERID (ID), + CONSTRAINT FK_MEDIA_ITEMID FOREIGN KEY (ITEMID) REFERENCES ITEM (ID), + CONSTRAINT FK_MEDIA_ITEMTYPEID FOREIGN KEY (ITEMTYPEID) REFERENCES ITEMTYPE (ID) +)@ + +CREATE INDEX IDX_MEDIA_TITLE ON MEDIA ("TITLE")@ +CREATE INDEX IDX_MEDIA_ITEMID ON MEDIA ("ITEMID")@ +--CREATE INDEX IDX_MEDIA_DESCRIPTION ON MEDIA ("DESCRIPTION")@ +CREATE INDEX IDX_MEDIA_HOUSEID ON MEDIA ("HOUSEID")@ +CREATE INDEX IDX_MEDIA_CREATED ON MEDIA ("CREATED")@ +CREATE INDEX IDX_MEDIA_ARCHIVED ON MEDIA ("ARCHIVED")@ +CREATE INDEX IDX_MEDIA_LENGTH ON MEDIA ("LENGTH")@ + +CREATE TABLE FILETYPE +( + ID BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1) PRIMARY KEY, + NAME VARCHAR(40) NOT NULL, + DESCRIPTION VARCHAR(255), + VIDEOCODEC VARCHAR(10) NOT NULL, + AUDIOCODEC VARCHAR(10) NOT NULL, + VIDEOTRACKS INT NOT NULL, + AUDIOTRACKS INT NOT NULL, + FRAMERATE FLOAT NOT NULL +)@ + +CREATE UNIQUE INDEX UDX_FILETYPE_NAME ON FILETYPE ("NAME")@ + +CREATE TABLE STORE +( + ID BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1) PRIMARY KEY, + NAME VARCHAR(80) NOT NULL, + ISSYSTEM CHARACTER(1) NOT NULL DEFAULT 'N', + ISLOWRES CHARACTER(1) NOT NULL DEFAULT 'N', + CONSTRAINT CHK_STORE_ISSYSTEM CHECK (ISSYSTEM in ('Y', 'N')), + CONSTRAINT CHK_STORE_ISLOWRES CHECK (ISLOWRES in ('Y', 'N')) +)@ + +CREATE UNIQUE INDEX UDX_STORE_NAME ON STORE ("NAME")@ + +CREATE TABLE MEDIAFILE +( + ID BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1) PRIMARY KEY, + HOUSEID VARCHAR(1000), + MEDIAID BIGINT NOT NULL, + STOREID BIGINT NOT NULL, + FILETYPEID BIGINT NOT NULL, + RELATIVEPATH VARCHAR(1000) NOT NULL, + FILESTRUCTINFO VARCHAR(255), + CONSTRAINT FK_MEDIAFILE_MEDIAID FOREIGN KEY (MEDIAID) REFERENCES MEDIA (ID), + CONSTRAINT FK_MEDIAFILE_STOREID FOREIGN KEY (STOREID) REFERENCES STORE (ID), + CONSTRAINT FK_MEDIAFILE_FILETYPEID FOREIGN KEY (FILETYPEID) REFERENCES FILETYPE (ID) +)@ + +CREATE TABLE STOREURI +( + ID BIGINT GENERATED BY DEFAULT AS IDENTITY(START WITH 1) PRIMARY KEY, + STOREID BIGINT NOT NULL, + PROTOCOL VARCHAR(20) , + DELIVERY VARCHAR(20) , + URI VARCHAR(255) NOT NULL, + ISSTREAM CHARACTER(1) NOT NULL DEFAULT 'N', + ISSOURCE CHARACTER(1) NOT NULL DEFAULT 'N', + ISTARGET CHARACTER(1) NOT NULL DEFAULT 'N', + USERNAME VARCHAR(20), + PASSWORD VARCHAR(20), + ROOTPATH VARCHAR(255), + PORTNUMBER INT, + CONSTRAINT CHK_STOREURI_ISSTREAM CHECK (ISSTREAM in ('Y', 'N')), + CONSTRAINT CHK_STOREURI_ISSOURCE CHECK (ISSOURCE in ('Y', 'N')), + CONSTRAINT CHK_STOREURI_ISTARGET CHECK (ISTARGET in ('Y', 'N')), + CONSTRAINT FK_STOREURI_STOREID FOREIGN KEY (STOREID) REFERENCES STORE (ID) +)@ + +CREATE TABLE SHOT +( + ID BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1) PRIMARY KEY, + MEDIAID BIGINT NOT NULL, + THUMBNAIL BLOB, + INPOINT BIGINT NOT NULL, + OUTPOINT BIGINT NOT NULL, + DESCRIPTION VARCHAR(255), + CREATED TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL, + CONSTRAINT FK_SHOT_MEDIAID FOREIGN KEY (MEDIAID) REFERENCES MEDIA (ID) +)@ + +CREATE INDEX IDX_SHOT_DESCRIPTION ON SHOT ("DESCRIPTION")@ + +CREATE TABLE BREAK +( + ID BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1) PRIMARY KEY, + MEDIAID BIGINT NOT NULL, + THUMBNAIL BLOB NOT NULL, + INPOINT BIGINT NOT NULL, + BREAKTYPE VARCHAR(10) NOT NULL, + CREATED TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL, + CONSTRAINT FK_BREAK_MEDIAID FOREIGN KEY (MEDIAID) REFERENCES MEDIA (ID) +)@ + +CREATE TABLE SCENE +( + ID BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1) PRIMARY KEY, + NAME VARCHAR(80) NOT NULL, + CREATED TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL, + MODIFIED TIMESTAMP NOT NULL GENERATED BY DEFAULT FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP +)@ + +CREATE TABLE SCENECONTENT +( + ID BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1) PRIMARY KEY, + SCENEID BIGINT NOT NULL, + SHOTID BIGINT NOT NULL, + POSITION BIGINT NOT NULL, + CONSTRAINT FK_SCENECONTENT_SCENEID FOREIGN KEY (SCENEID) REFERENCES SCENE (ID), + CONSTRAINT FK_SCENECONTENT_SHOTID FOREIGN KEY (SHOTID) REFERENCES SHOT (ID) +)@ + +CREATE TABLE SEARCHDEFINITION +( + ID BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1) PRIMARY KEY, + NAME VARCHAR(40) NOT NULL, + CREATED TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL, + MODIFIED TIMESTAMP NOT NULL GENERATED BY DEFAULT FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP, + DATA BLOB +)@ +CREATE UNIQUE INDEX UDX_SEARCHDEFINITION_NAME ON SEARCHDEFINITION ("NAME")@ + +CREATE TABLE USERINFO +( + ID BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1) PRIMARY KEY, + FULLNAME VARCHAR(80) NOT NULL, + USERNAME VARCHAR(20) NOT NULL, + PASSWORD VARCHAR(20) NOT NULL FOR BIT DATA, + CREATED TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL, + LASTLOGIN TIMESTAMP, + RIGHTS INT NOT NULL DEFAULT 0 +)@ + +CREATE UNIQUE INDEX UDX_USERINFO_USERNAME ON USERINFO ("USERNAME")@ +CREATE INDEX IDX_ITEM_USERNAMEPASSWORD ON USERINFO ("USERNAME", "PASSWORD")@ + +CREATE TABLE JOB +( + ID BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1) PRIMARY KEY, + NAME VARCHAR(80) NOT NULL, + OWNER VARCHAR(80), + PRIORITY INT NOT NULL DEFAULT 0, + PROGRESS INT NOT NULL DEFAULT 0, + STATUS VARCHAR(80) NOT NULL, + DESCRIPTION VARCHAR(200), + SUBMITTED TIMESTAMP NOT NULL, + FINISHED TIMESTAMP, + TEMPLATE VARCHAR(80) NOT NULL, + SCHEDULEDTIME TIMESTAMP +)@ + +CREATE TABLE JOBPARAMETERS +( + ID BIGINT NOT NULL PRIMARY KEY, + DATA BLOB +)@ + +CREATE TABLE WORKFLOWACTION +( + ID BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1) PRIMARY KEY, + TOUCHED TIMESTAMP NOT NULL, + STARTED TIMESTAMP NOT NULL, + FINISHED TIMESTAMP NOT NULL, + SUCCESSFUL CHARACTER(1) NOT NULL DEFAULT 'N', + HOUSEID VARCHAR(40), + DESCRIPTION VARCHAR(255) NOT NULL, + SOURCE VARCHAR(255) NOT NULL, + DESTINATION VARCHAR(255) NOT NULL, + TAG VARCHAR(40) NOT NULL, + USERNAME VARCHAR(50) NOT NULL, + SIZE BIGINT NOT NULL, + CONSTRAINT CHK_WORKFLOWACTION_SUCCESSFUL CHECK (SUCCESSFUL in ('Y', 'N')) +)@ + +CREATE INDEX IDX_WORKFLOWACTION_FINISHED ON WORKFLOWACTION ("FINISHED")@ +CREATE INDEX IDX_WORKFLOWACTION_HOUSEID ON WORKFLOWACTION ("HOUSEID")@ + + +CREATE TABLE MEDIADESCRIPTION ( + ID BIGINT GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1, CACHE 20) NOT NULL, + ITEMID BIGINT NOT NULL, + MEDIAID BIGINT NOT NULL, + MEDIAFILEID BIGINT NOT NULL, + DESCRIPTION CLOB(400000) NOT LOGGED COMPACT, + CONSTRAINT PK_MEDIADESCRIPTION PRIMARY KEY(ID) +) +@ + +ALTER TABLE DB2ADMIN.MEDIADESCRIPTION + ADD CONSTRAINT FK_MEDIADESCRIPTION_MEDIAID + FOREIGN KEY(MEDIAID) + REFERENCES DB2ADMIN.MEDIA(ID) + ON DELETE CASCADE + ON UPDATE NO ACTION +@ +ALTER TABLE DB2ADMIN.MEDIADESCRIPTION + ADD CONSTRAINT FK_MEDIADESCRIPTION_MEDIAFILEID + FOREIGN KEY(MEDIAFILEID) + REFERENCES DB2ADMIN.MEDIAFILE(ID) + ON DELETE CASCADE + ON UPDATE NO ACTION +@ +ALTER TABLE DB2ADMIN.MEDIADESCRIPTION + ADD CONSTRAINT FK_MEDIADESCRIPTION_ITEMID + FOREIGN KEY(ITEMID) + REFERENCES DB2ADMIN.ITEM(ID) + ON DELETE CASCADE + ON UPDATE NO ACTION +@ + +CREATE OR REPLACE FUNCTION DEFDATE (TS timestamp) +returns varchar(10) +return +with tmp (dd,mm,yyyy,hh,mi,ss,nnnnnn) as +( + select + substr( digits (day(TS)),9), + substr( digits (month(TS)),9) , + rtrim(char(year(TS))) , + substr( digits (hour(TS)),9), + substr( digits (minute(TS)),9), + substr( digits (second(TS)),9), + rtrim(char(microsecond(TS))) + from sysibm.sysdummy1 + ) +select yyyy || mm || dd from tmp +@ + + +CREATE OR REPLACE FUNCTION HUNDATE (TS timestamp) +returns varchar(10) +return +with tmp (dd,mm,yyyy,hh,mi,ss,nnnnnn) as +( + select + substr( digits (day(TS)),9), + substr( digits (month(TS)),9) , + rtrim(char(year(TS))) , + substr( digits (hour(TS)),9), + substr( digits (minute(TS)),9), + substr( digits (second(TS)),9), + rtrim(char(microsecond(TS))) + from sysibm.sysdummy1 + ) +select yyyy || '.' || mm || '.' || dd from tmp +@ + +CREATE OR REPLACE FUNCTION HIGHLIGHT (content CLOB(400000), starttag VARCHAR(20), endtag VARCHAR(20), pattern VARCHAR(1000)) + RETURNS VARCHAR(32000) + LANGUAGE SQL + NO EXTERNAL ACTION + READS SQL DATA + DETERMINISTIC +BEGIN + DECLARE RESULT VARCHAR(32000) DEFAULT ''; + DECLARE CHUNKSIZE INT DEFAULT 10000; + DECLARE CHUNK VARCHAR(10000); + DECLARE CHUNKSTART INT DEFAULT 1; + DECLARE HITSTART INT DEFAULT 1; + WHILE LENGTH(RESULT) = 0 AND CHUNKSTART < LENGTH(content) DO + SET CHUNK = DBMS_LOB.SUBSTR(content, CHUNKSIZE, CHUNKSTART); + SET HITSTART = REGEXP_INSTR(CHUNK, pattern, 1, 1, 0, 'i'); + IF HITSTART > 0 THEN + IF CHUNKSTART + HITSTART > 100 THEN + SET CHUNK = DBMS_LOB.SUBSTR(content, 200, CHUNKSTART + HITSTART - 100); + ELSE + SET CHUNK = DBMS_LOB.SUBSTR(content, 200, CHUNKSTART); + END IF; + SET CHUNK = REPLACE(CHUNK, '<', ''); + SET CHUNK = REPLACE(CHUNK, '>', ''); + SET RESULT = '...' || REGEXP_REPLACE(CHUNK, pattern, starttag||'$0'||endtag,1,0,'i') || '...'; + END IF; + SET CHUNKSTART = CHUNKSTART + CHUNKSIZE; + END WHILE; + RETURN RESULT; +END +@ + +CREATE OR REPLACE VIEW VW_ITEMS as +select +i.id as itemid, +i.created as itemcreated, +i.houseid as itemhouseid, +i.title as itemtitle, +m.id as mediaid, +m.created as mediacreated, +m.archived, +m.length, +it.name as mediatype, +m.houseid as mediahouseid, +m.title as mediatitle, +mf.id as mediafileid, +mf.houseid as mediafilehouseid +from mediafile mf +left outer join media m on (m.id = mf.mediaid) +left outer join item i on (i.id = m.itemid) +left outer join itemtype it on (it.id = m.itemtypeid) +where mf.storeid = 1 +@ + +CREATE OR REPLACE VIEW VW_ITEMS_DESCRIPTION as +select +i.id as itemid, +i.created as itemcreated, +i.houseid as itemhouseid, +i.title as itemtitle, +i.description as itemdescription, +m.id as mediaid, +m.created as mediacreated, +m.archived, +m.length, +m.description as mediadescription, +it.name as mediatype, +m.houseid as mediahouseid, +m.title as mediatitle, +mf.id as mediafileid, +mf.houseid as mediafilehouseid +from mediafile mf +left outer join media m on (m.id = mf.mediaid) +left outer join item i on (i.id = m.itemid) +left outer join itemtype it on (it.id = m.itemtypeid) +where mf.storeid = 1 +@ + +CREATE OR REPLACE FUNCTION FULLDESC (IN initemid BIGINT, IN inmediaid BIGINT, IN inmediafileid BIGINT) +returns CLOB(400000) +return +SELECT + COALESCE(hundate(archived), '') || ' ' || + COALESCE(defdate(archived), '') || ' ' || + COALESCE(itemhouseid, '') || ' ' || + COALESCE(itemtitle, '') || ' ' || + COALESCE(itemdescription,'') || ' ' || + COALESCE(mediahouseid, '') || ' ' || + COALESCE(mediatitle, '') || ' ' || + COALESCE(mediadescription, '') || ' ' || + COALESCE(mediafilehouseid, '') +from vw_items_description WHERE itemid = initemid AND mediaid = inmediaid AND mediafileid = inmediafileid +@ + +CREATE OR REPLACE TRIGGER TRG_MEDIAFILE_AFTER_INSERT after insert +on mediafile +referencing new as n +for each row +when (n.storeid = 1) +begin atomic + insert into mediadescription (itemid, mediaid, mediafileid, description) + select + itemid, + mediaid, + mediafileid, + fulldesc(itemid, mediaid, mediafileid) + from vw_items + where mediafileid = n.id; +end +@ + +CREATE OR REPLACE TRIGGER TRG_MEDIAFILE_AFTER_DELETE after delete +on mediafile +referencing old as o +for each row +when (o.storeid = 1) +begin atomic + delete from mediadescription where mediafileid = o.id; +end +@ + +CREATE OR REPLACE TRIGGER TRG_MEDIA_AFTER_UPDATE after update on media +referencing new as n +for each row +begin atomic + update mediadescription SET description = fulldesc(itemid, mediaid, mediafileid) where mediaid = n.id; +end +@ + +CREATE OR REPLACE TRIGGER TRG_ITEM_AFTER_UPDATE after update on item +referencing new as n +for each row +begin atomic + update mediadescription SET description = fulldesc(itemid, mediaid, mediafileid) where itemid = n.id; +end +@ + + +-- //@UNDO +-- SQL to undo the change goes here. + +CALL SET_CONSTRAINTS('NO')@ +CALL DROP_DYNAMIC_DATA()@ + +DROP PROCEDURE SET_CONSTRAINTS@ +DROP PROCEDURE DROP_DYNAMIC_DATA@ + +DROP TABLE MEDIAFILE@ +DROP TABLE SCENECONTENT@ +DROP TABLE SHOT@ +DROP TABLE MEDIA@ +DROP TABLE ITEM@ +DROP TABLE MASTERID@ +DROP TABLE METADATA@ +DROP TABLE ITEMTYPE@ +DROP TABLE METADATAELEMENT@ +DROP TABLE METADATATYPE@ +DROP TABLE SCENE@ +DROP TABLE STOREURI@ +DROP TABLE FILETYPE@ +DROP TABLE STORE@ +DROP TABLE BREAK@ +DROP TABLE DOMAIN@ +DROP TABLE DOMAINCATEGORY@ +DROP TABLE DOMAININDEX@ +DROP TABLE WORKFLOWACTION@ +DROP TABLE SEARCHDEFINITION@ +DROP TABLE FOLDER@ +DROP TABLE USERINFO@ +DROP TABLE JOB@ +DROP TABLE JOBPARAMETERS@ +DROP TABLE MEDIADESCRIPTION@ + +DROP TRIGGER TRG_INSERT_FOLDER@ +DROP TRIGGER TRG_DELETE_FOLDER@ +DROP TRIGGER TRG_ITEM_AFTER_UPDATE@ +DROP TRIGGER TRG_MEDIA_AFTER_UPDATE@ +DROP TRIGGER TRG_MEDIAFILE_AFTER_DELETE@ +DROP TRIGGER TRG_MEDIAFILE_AFTER_INSERT@ + +DROP FUNCTION FULLDESC@ + +DROP FUNCTION DEFDATE@ +DROP FUNCTION HUNDATE@ +DROP FUNCTION HIGHLIGHT@ + +DROP VIEW VW_ITEMS@ +DROP VIEW VW_ITEMS_DESCRIPTION@ diff --git a/server/user.jobengine.osgi.db/migrations/20190909125326_init.sql b/server/user.jobengine.osgi.db/migrations/scripts/002_init.sql similarity index 100% rename from server/user.jobengine.osgi.db/migrations/20190909125326_init.sql rename to server/user.jobengine.osgi.db/migrations/scripts/002_init.sql diff --git a/server/user.jobengine.osgi.db/migrations/20190915120000_metadata.sql b/server/user.jobengine.osgi.db/migrations/scripts/003_metadata.sql similarity index 100% rename from server/user.jobengine.osgi.db/migrations/20190915120000_metadata.sql rename to server/user.jobengine.osgi.db/migrations/scripts/003_metadata.sql diff --git a/server/user.jobengine.osgi.db/migrations/20190916120000_views.sql b/server/user.jobengine.osgi.db/migrations/scripts/004_views.sql similarity index 100% rename from server/user.jobengine.osgi.db/migrations/20190916120000_views.sql rename to server/user.jobengine.osgi.db/migrations/scripts/004_views.sql diff --git a/server/user.jobengine.osgi.db/migrations/20190917120000_indexer_view.sql b/server/user.jobengine.osgi.db/migrations/scripts/005_indexer_view.sql similarity index 100% rename from server/user.jobengine.osgi.db/migrations/20190917120000_indexer_view.sql rename to server/user.jobengine.osgi.db/migrations/scripts/005_indexer_view.sql -- 2.54.0