/* test environment for view filesdefaultscan.this script will create a database (you need an empty db first), add sample data and run the viewjb Sept 2010*//****************** GENERATORS ********************/CREATE GENERATOR SEQ_CATALOGID;CREATE GENERATOR SEQ_COMPUTERID;CREATE GENERATOR SEQ_DATEID;CREATE GENERATOR SEQ_EXEID;CREATE GENERATOR SEQ_EXIFID;CREATE GENERATOR SEQ_EXPOSUREPROGRAMID;CREATE GENERATOR SEQ_FILEID;CREATE GENERATOR SEQ_FILENAMEID;CREATE GENERATOR SEQ_FLASHID;CREATE GENERATOR SEQ_LIGHTSOURCEID;CREATE GENERATOR SEQ_LOGSEQUENCE;CREATE GENERATOR SEQ_MD5ID;CREATE GENERATOR SEQ_MP3GENREID;CREATE GENERATOR SEQ_MP3ID;CREATE GENERATOR SEQ_ORIENTATIONID;CREATE GENERATOR SEQ_PATHID;CREATE GENERATOR SEQ_SCANID;CREATE GENERATOR SEQ_USERCOMMENTID;/******************** DOMAINS *********************/CREATE DOMAIN MEMO AS BLOB SUB_TYPE 1 DEFAULT NULL COLLATE UTF8;/******************** TABLES **********************/CREATE TABLE LOGS( LOGSEQUENCE INTEGER NOT NULL, LOGDATE TIMESTAMP NOT NULL, LOGMESSAGE VARCHAR(2048), PRIMARY KEY (LOGSEQUENCE));CREATE TABLE TBLCATALOGS( CATALOGID INTEGER NOT NULL, DESCRIPTION VARCHAR(255) NOT NULL, NOTES VARCHAR(5000), WHENCREATED TIMESTAMP, PRIMARY KEY (CATALOGID));CREATE TABLE TBLCOMPUTERS( COMPUTERID INTEGER NOT NULL, COMPUTERNAME VARCHAR(255) NOT NULL, PRIMARY KEY (COMPUTERID));CREATE TABLE TBLEXES( EXEID INTEGER NOT NULL, EXECOMPANY VARCHAR(255), EXECOPYRIGHT VARCHAR(255), EXEDESCRIPTION VARCHAR(255), EXEFILEVERSION VARCHAR(255), EXEINTERNALNAME VARCHAR(255), EXEORIGINALFILENAME VARCHAR(255), EXEPRODUCTNAME VARCHAR(255), EXEPRODUCTVERSION VARCHAR(255), PRIMARY KEY (EXEID));CREATE TABLE TBLEXIFS( EXIFID INTEGER NOT NULL, EXIFAPERTURE VARCHAR(255), EXIFARTIST VARCHAR(255), EXIFCOMPRESSEDBPP VARCHAR(255), EXIFCOPYRIGHT VARCHAR(255), EXIFDATETIME TIMESTAMP, EXIFDATETIMEDIGITIZED TIMESTAMP, EXIFDATETIMEORIGINAL TIMESTAMP, EXIFEXPOSURE VARCHAR(255), EXIFEXPOSUREPROGRAM INTEGER, EXIFFLASH INTEGER, EXIFFSTOPS VARCHAR(255), EXIFIMAGEDESCRIPTION VARCHAR(255), EXIFISO INTEGER, EXIFLIGHTSOURCE INTEGER, EXIFMAKE VARCHAR(255), EXIFMAXAPERTURE VARCHAR(255), EXIFMETERINGMETHOD VARCHAR(255), EXIFMETERINGMODE VARCHAR(255), EXIFMODEL VARCHAR(255), EXIFORIENTATION INTEGER, EXIFPIXELXDIMENSION INTEGER, EXIFPIXELYDIMENSION INTEGER, EXIFSHUTTERSPEED VARCHAR(255), EXIFSOFTWARE VARCHAR(255), EXIFUSERCOMMENTS INTEGER, EXIFXRESOLUTION INTEGER, EXIFYRESOLUTION INTEGER, PRIMARY KEY (EXIFID), CONSTRAINT UI_EXIF UNIQUE (EXIFDATETIME,EXIFDATETIMEDIGITIZED,EXIFDATETIMEORIGINAL,EXIFMODEL,EXIFUSERCOMMENTS));CREATE TABLE TBLEXPOSUREPROGRAMS( EXPOSUREPROGRAMID INTEGER NOT NULL, EXPOSUREPROGRAM VARCHAR(255), PRIMARY KEY (EXPOSUREPROGRAMID));CREATE TABLE TBLFILENAMES( FILENAMEID INTEGER NOT NULL, FILENAME VARCHAR(255) NOT NULL, PRIMARY KEY (FILENAMEID));CREATE TABLE TBLFILES( FILEID INTEGER NOT NULL, SCANID INTEGER, COMPUTERID INTEGER, EXEID INTEGER, EXIFID INTEGER, FILENAMEID INTEGER NOT NULL, MD5ID INTEGER, MP3ID INTEGER, FILESIZE INTEGER, PATHID INTEGER NOT NULL, DATEMODIFIED TIMESTAMP, DATECREATED TIMESTAMP, DATEACCESSED TIMESTAMP, FILEDESCRIPTION VARCHAR(255), PRIMARY KEY (FILEID));CREATE TABLE TBLFLASHES( FLASHID INTEGER NOT NULL, FLASH VARCHAR(255), PRIMARY KEY (FLASHID));CREATE TABLE TBLLIGHTSOURCES( LIGHTSOURCEID INTEGER NOT NULL, LIGHTSOURCE VARCHAR(255), PRIMARY KEY (LIGHTSOURCEID));CREATE TABLE TBLMD5S( MD5ID INTEGER NOT NULL, MD5HASH CHAR(32), PRIMARY KEY (MD5ID));CREATE TABLE TBLMP3GENRES( MP3GENREID INTEGER NOT NULL, GENRE VARCHAR(255), PRIMARY KEY (MP3GENREID));CREATE TABLE TBLMP3S( MP3ID INTEGER NOT NULL, MP3ALBUM VARCHAR(255), MP3ARTIST VARCHAR(255), MP3COMMENT VARCHAR(255), MP3TITLE VARCHAR(255), MP3TRACK INTEGER, MP3YEAR INTEGER, MP3GENREID INTEGER, PRIMARY KEY (MP3ID));CREATE TABLE TBLORIENTATIONS( ORIENTATIONID INTEGER NOT NULL, ORIENTATION VARCHAR(255), PRIMARY KEY (ORIENTATIONID));CREATE TABLE TBLPATHS( PATHID INTEGER NOT NULL, FILEPATH VARCHAR(255) NOT NULL, PRIMARY KEY (PATHID));CREATE TABLE TBLSCANS( SCANID INTEGER NOT NULL, CATALOGID INTEGER, ROOT VARCHAR(255) NOT NULL, VOLUME VARCHAR(255), SERIAL INTEGER, FILTERSTRING VARCHAR(50), DESCRIPTION VARCHAR(255) NOT NULL, WHENCREATED TIMESTAMP, PRIMARY KEY (SCANID));CREATE TABLE TBLUSERCOMMENTS( USERCOMMENTID INTEGER NOT NULL, "COMMENT" VARCHAR(255), PRIMARY KEY (USERCOMMENTID));/********************* VIEWS **********************/CREATE VIEW CATALOGSCANS (CATALOGID, SCANID, CATALOGDESCRIPTION, CATALOGNOTES, CATALOGWHENCREATED, SCANROOT, SCANVOLUME, SCANSERIAL, SCANDESCRIPTION, SCANFILTERSTRING, SCANWHENCREATED)ASSELECTC.CATALOGID,S.SCANID,C.DESCRIPTION,C.NOTES,C.WHENCREATED,S.ROOT,S.VOLUME,S.SERIAL,S.DESCRIPTION,S.FILTERSTRING,S.WHENCREATEDFROMTBLCATALOGS C INNER JOIN TBLSCANS S ON C.CATALOGID=S.CATALOGIDORDER BY C.DESCRIPTION, S.ROOT;CREATE VIEW FILESDEFAULTSCAN (COMPUTERNAME, FILEPATH, FILENAME, FILESIZE, FILEDESCRIPTION, DATEMODIFIED, DATEACCESSED, DATECREATED, MD5HASH, EXECOMPANY, EXECOPYRIGHT, EXEDESCRIPTION, EXEFILEVERSION, EXEINTERNALNAME, EXEORIGINALFILENAME, EXEPRODUCTNAME, EXEPRODUCTVERSION, EXIFID, EXIFAPERTURE, EXIFARTIST, EXIFCOMPRESSEDBPP, EXIFCOPYRIGHT, EXIFDATETIME, EXIFDATETIMEDIGITIZED, EXIFDATETIMEORIGINAL, EXIFEXPOSUREPROGRAM, EXIFFLASH, EXIFFSTOPS, EXIFIMAGEDESCRIPTION, EXIFISO, EXIFLIGHTSOURCE, EXIFMAKE, EXIFMAXAPERTURE, EXIFMETERINGMETHOD, EXIFMETERINGMODE, EXIFMODEL, EXIFORIENTATION, EXIFPIXELXDIMENSION, EXIFPIXELYDIMENSION, EXIFSHUTTERSPEED, EXIFSOFTWARE, EXIFUSERCOMMENTS, EXIFXRESOLUTION, EXIFYRESOLUTION, MP3ALBUM, MP3ARTIST, MP3COMMENT, MP3TITLE, MP3TRACK, MP3YEAR, MP3GENREID)ASSELECTcomputer.COMPUTERNAME, path.FILEPATH,filename.FILENAME,f.FILESIZE, f.FILEDESCRIPTION,f.DATEMODIFIED,f.DATEACCESSED,f.DATECREATED,hash.MD5HASH, exe.EXECOMPANY, exe.EXECOPYRIGHT, exe.EXEDESCRIPTION, exe.EXEFILEVERSION, exe.EXEINTERNALNAME, exe.EXEORIGINALFILENAME, exe.EXEPRODUCTNAME, exe.EXEPRODUCTVERSION, exif.EXIFID, exif.EXIFAPERTURE, exif.EXIFARTIST, exif.EXIFCOMPRESSEDBPP, exif.EXIFCOPYRIGHT, exif.EXIFDATETIME, exif.EXIFDATETIMEDIGITIZED, exif.EXIFDATETIMEORIGINAL, exposure.EXPOSUREPROGRAM AS EXIFEXPOSUREPROGRAM, flash.FLASH AS EXIFFLASH, exif.EXIFFSTOPS, exif.EXIFIMAGEDESCRIPTION, exif.EXIFISO, light.LIGHTSOURCE AS EXIFLIGHTSOURCE, exif.EXIFMAKE, exif.EXIFMAXAPERTURE, exif.EXIFMETERINGMETHOD, exif.EXIFMETERINGMODE, exif.EXIFMODEL, exiforientation.ORIENTATION AS EXIFORIENTATION, exif.EXIFPIXELXDIMENSION, exif.EXIFPIXELYDIMENSION, exif.EXIFSHUTTERSPEED, exif.EXIFSOFTWARE, exifusercomments."COMMENT" AS EXIFUSERCOMMENTS, exif.EXIFXRESOLUTION, exif.EXIFYRESOLUTION,mp3.MP3ALBUM, mp3.MP3ARTIST, mp3.MP3COMMENT, mp3.MP3TITLE, mp3.MP3TRACK, mp3.MP3YEAR, mp3genre.GENRE AS MP3GENREIDFROM(((((((((TBLFILES f INNER JOIN TBLCOMPUTERS computer ON f.computerid=computer.COMPUTERID) INNER JOIN TBLSCANS scan ON f.SCANID=scan.SCANID)INNER JOIN TBLCATALOGS cat ON scan.CATALOGID=cat.CATALOGID)INNER JOIN TBLFILENAMES filename ON f.FILENAMEID=filename.FILENAMEID)INNER JOIN TBLPATHS path ON f.PATHID=path.PATHID)LEFT OUTER JOIN ( TBLMP3S mp3 LEFT OUTER JOIN TBLMP3GENRES mp3genre ON mp3.MP3GENREID=mp3genre.MP3GENREID ) ON f.MP3ID=mp3.MP3ID)LEFT OUTER JOIN TBLMD5S hash ON f.MD5ID=hash.MD5ID)LEFT OUTER JOIN TBLEXES exe ON f.EXEID=exe.EXEID)LEFT OUTER JOIN ( TBLEXIFS exif LEFT OUTER JOIN TBLEXPOSUREPROGRAMS exposure ON exif.EXIFEXPOSUREPROGRAM=exposure.EXPOSUREPROGRAM LEFT OUTER JOIN TBLFLASHES flash ON exif.EXIFFLASH=flash.FLASHID LEFT OUTER JOIN TBLLIGHTSOURCES light ON exif.EXIFLIGHTSOURCE=light.LIGHTSOURCEID LEFT OUTER JOIN TBLORIENTATIONS exiforientation ON exif.EXIFORIENTATION=exiforientation.ORIENTATIONID LEFT OUTER JOIN TBLUSERCOMMENTS exifusercomments ON exif.EXIFUSERCOMMENTS=exifusercomments.USERCOMMENTID ) ON f.EXIFID=exif.EXIFID)WHERE scan.DESCRIPTION='Default scan' AND cat.DESCRIPTION='Default catalog'ORDER BY computer.COMPUTERNAME, path.FILEPATH, filename.FILENAME;CREATE VIEW SCANFILES (SCANID, CATALOGID, ROOT, VOLUME, SERIAL, FILTERSTRING, DESCRIPTION, WHENCREATED, FILEID, COMPUTERID, EXEID, EXIFID, FILENAMEID, MD5ID, MP3ID, FILESIZE, PATHID, DATEMODIFIED, DATECREATED, DATEACCESSED, FILEDESCRIPTION)ASSELECTs.SCANID, s.CATALOGID, s.ROOT, s.VOLUME, s.SERIAL, s.FILTERSTRING, s.DESCRIPTION, s.WHENCREATED,a.FILEID, a.COMPUTERID, a.EXEID, a.EXIFID, a.FILENAMEID, a.MD5ID, a.MP3ID, a.FILESIZE, a.PATHID, a.DATEMODIFIED, a.DATECREATED, a.DATEACCESSED, a.FILEDESCRIPTION FROM TBLFILES a INNER JOIN TBLSCANS s ON a.SCANID=s.SCANID;CREATE VIEW SCANSPATHS (SCANID, PATHID)AS/* write select statement here */SELECT DISTINCTSCANID, PATHIDFROMTBLFILES;CREATE VIEW SUBDIRECTORIES (SCANID, PATHID, FILEPATH, SUBDIR, SUBPATHID)ASSELECTDISTINCTS.SCANID,P.PATHID, P.FILEPATH, C.FILEPATH AS SUBDIR, C.PATHID AS SUBPATHIDFROM(SCANSPATHS S INNER JOINTBLPATHS P ON S.PATHID=P.PATHID),TBLPATHS CWHERE C.FILEPATH LIKE P.FILEPATH || '%'ORDER BY P.FILEPATH, C.FILEPATH;CREATE VIEW DIRECTORYSIZE (CATALOGID, SCANID, PATHID, FILEPATH, NONRECURSIVESIZE)AS/* We can't use WITH CHECK OPTION because the aggregate will never allow this to be an updatable view */SELECT SCANFILES.CATALOGID, SCANFILES.SCANID, TBLPATHS.PATHID,TBLPATHS.FILEPATH, Sum(SCANFILES.FILESIZE) AS NONRECURSIVESIZEFROM SCANFILESINNER JOIN TBLPATHS ON SCANFILES.PATHID = TBLPATHS.PATHIDGROUP BY SCANFILES.CATALOGID, SCANFILES.SCANID, TBLPATHS.PATHID, TBLPATHS.FILEPATH;CREATE VIEW CUMULATIVEDIRECTORYSIZE (CATALOG_ID, SCAN_ID, FILEPATH, TOTALSIZE)ASSELECTD.CATALOGID,D.SCANID,D.FILEPATH,D.NONRECURSIVESIZEFROM DIRECTORYSIZE D INNER JOIN SUBDIRECTORIES SON D.PATHID=S.SUBPATHID ORDER BY D.CATALOGID, D.SCANID, D.FILEPATH;/******************* EXCEPTIONS *******************/CREATE EXCEPTION SPCOULDNOTFINDCATALOGINFO'Could not add records: unable to add catalog information to database';CREATE EXCEPTION SPCOULDNOTFINDCOMPUTERINFO'Could not add records: unable to add computer name to database';CREATE EXCEPTION SPCOULDNOTFINDDATEINFO'Could not add records: unable to add date/time value to database';CREATE EXCEPTION SPCOULDNOTFINDEXIFINFO'Could not add records: unable to add EXIF information to database';CREATE EXCEPTION SPCOULDNOTFINDEXPOSUREINFO'Could not add records: unable to add EXIF exposure information to database';CREATE EXCEPTION SPCOULDNOTFINDFILENAMEINFO'Could not add records: unable to add filename information to database';CREATE EXCEPTION SPCOULDNOTFINDFLASHINFO'Could not add records: unable to add EXIF flash information to database';CREATE EXCEPTION SPCOULDNOTFINDLIGHTSOURCEINFO'Could not add records: unable to add EXIF light source information to database';CREATE EXCEPTION SPCOULDNOTFINDMD5INFO'Could not add records: unable to add MD5 hash information to database';CREATE EXCEPTION SPCOULDNOTFINDMP3GENREINFO'Could not add records: unable to add MP3 genre information to database';CREATE EXCEPTION SPCOULDNOTFINDMP3INFO'Could not add records: unable to add MP3 information to database';CREATE EXCEPTION SPCOULDNOTFINDORIENTATIONINFO'Could not add records: unable to add EXIF orientation information to database';CREATE EXCEPTION SPCOULDNOTFINDPATHINFO'Could not add records: unable to add path information to database.';CREATE EXCEPTION SPCOULDNOTFINDSCANINFO'Could not add records: unable to add scan information to database';CREATE EXCEPTION SPCOULDNOTFINDUSERCOMMINFO'Could not add records: unable to add user comments value to database';CREATE EXCEPTION SPCOULDNOTFINDVERSIONINFO'Could not add records: unable to add version information to database';CREATE EXCEPTION SPNOCATALOGSPECIFIED'Could not add records: no catalog ID specified.';CREATE EXCEPTION SPNOSCANSPECIFIED'Could not add records: no scan ID specified.';/******************** TRIGGERS ********************/SET TERM ^ ;CREATE TRIGGER SEQ_LOGS FOR LOGS ACTIVEBEFORE INSERT POSITION 0ASBEGIN -- New generated primary key if not specified. IF ((NEW.LOGSEQUENCE IS NULL) OR (NEW.LOGSEQUENCE=0)) THEN BEGIN NEW.LOGSEQUENCE = NEXT VALUE FOR SEQ_LOGSEQUENCE; END -- New log date if not specified IF (NEW.LOGDATE IS NULL) THEN BEGIN NEW.LOGDATE = CURRENT_TIMESTAMP; ENDEND^SET TERM ; ^SET TERM ^ ;CREATE TRIGGER SEQ_TBLCATALOGS FOR TBLCATALOGS ACTIVEBEFORE INSERT POSITION 0ASdeclare variable localWHENCREATED TIMESTAMP; /* The value for WHENCREATED */BEGIN localWHENCREATED = CURRENT_TIMESTAMP; /* Get generated primary key unless it is specified */ IF ((NEW.CatalogID IS NULL) OR (NEW.CatalogID=0)) THEN BEGIN NEW.CatalogID = NEXT VALUE FOR SEQ_CATALOGID; END --new catalogid/* Insert current date/time unless it is specified */ IF (NEW.WHENCREATED IS NULL) THEN BEGIN NEW.WHENCREATED = localWHENCREATED; ENDEND^SET TERM ; ^SET TERM ^ ;CREATE TRIGGER SEQ_TBLCOMPUTERS FOR TBLCOMPUTERS ACTIVEBEFORE INSERT POSITION 0ASBEGIN IF ((NEW.ComputerID IS NULL) OR (NEW.ComputerID=0)) THEN BEGIN NEW.ComputerID = NEXT VALUE FOR SEQ_COMPUTERID; END --new primary keyEND^SET TERM ; ^SET TERM ^ ;CREATE TRIGGER SEQ_TBLEXES FOR TBLEXES ACTIVEBEFORE INSERT POSITION 0ASBEGIN -- New generated primary key if not specified. IF ((NEW.EXEID IS NULL) OR (NEW.EXEID=0)) THEN BEGIN NEW.EXEID = NEXT VALUE FOR SEQ_EXEID; END --new primary key END^SET TERM ; ^SET TERM ^ ;CREATE TRIGGER SEQ_TBLEXIFS FOR TBLEXIFS ACTIVEBEFORE INSERT POSITION 0ASBEGIN -- New generated primary key if not specified. IF ((NEW.EXIFID IS NULL) OR (NEW.EXIFID=0)) THEN BEGIN NEW.EXIFID = NEXT VALUE FOR SEQ_EXIFID; END --new primary keyEND^SET TERM ; ^SET TERM ^ ;CREATE TRIGGER SEQ_TBLEXPOSUREPROGRAMS FOR TBLEXPOSUREPROGRAMS ACTIVEBEFORE INSERT POSITION 0ASBEGIN -- New generated primary key if not specified. IF ((NEW.EXPOSUREPROGRAMID IS NULL) OR (NEW.EXPOSUREPROGRAMID=0)) THEN BEGIN NEW.EXPOSUREPROGRAMID = NEXT VALUE FOR SEQ_EXPOSUREPROGRAMID; END --new primary key END^SET TERM ; ^SET TERM ^ ;CREATE TRIGGER SEQ_TBLFILENAMES FOR TBLFILENAMES ACTIVEBEFORE INSERT POSITION 0ASBEGIN -- New generated primary key if not specified. IF ((NEW.FileNameID IS NULL) OR (NEW.FileNameID=0)) THEN BEGIN NEW.FileNameID = NEXT VALUE FOR SEQ_FILENAMEID; END --new primary keyEND^SET TERM ; ^SET TERM ^ ;CREATE TRIGGER SEQ_TBLFILES FOR TBLFILES ACTIVEBEFORE INSERT POSITION 0ASBEGIN -- New generated primary key if not specified. IF ((NEW.FileID IS NULL) OR (NEW.FileID=0)) THEN BEGIN NEW.FileID = NEXT VALUE FOR SEQ_FILEID; END --new primary key END^SET TERM ; ^SET TERM ^ ;CREATE TRIGGER SEQ_TBLFLASHES FOR TBLFLASHES ACTIVEBEFORE INSERT POSITION 0ASBEGIN -- New generated primary key if not specified. IF ((NEW.FLASHID IS NULL) OR (NEW.FLASHID=0)) THEN BEGIN NEW.FLASHID = NEXT VALUE FOR SEQ_FLASHID; END --new primary key END^SET TERM ; ^SET TERM ^ ;CREATE TRIGGER SEQ_TBLLIGHTSOURCES FOR TBLLIGHTSOURCES ACTIVEBEFORE INSERT POSITION 0ASBEGIN -- New generated primary key if not specified. IF ((NEW.LIGHTSOURCEID IS NULL) OR (NEW.LIGHTSOURCEID=0)) THEN BEGIN NEW.LIGHTSOURCEID = NEXT VALUE FOR SEQ_LIGHTSOURCEID; END --new primary key END^SET TERM ; ^SET TERM ^ ;CREATE TRIGGER SEQ_TBLMD5S FOR TBLMD5S ACTIVEBEFORE INSERT POSITION 0ASBEGIN -- New generated primary key if not specified. IF ((NEW.MD5ID IS NULL) OR (NEW.MD5ID=0)) THEN BEGIN NEW.MD5ID = NEXT VALUE FOR SEQ_MD5ID; END --new primary key END^SET TERM ; ^SET TERM ^ ;CREATE TRIGGER SEQ_TBLMP3GENRES FOR TBLMP3GENRES ACTIVEBEFORE INSERT POSITION 0ASBEGIN -- New generated primary key if not specified. IF ((NEW.MP3GENREID IS NULL) OR (NEW.MP3GENREID=0)) THEN BEGIN NEW.MP3GENREID = NEXT VALUE FOR SEQ_MP3GENREID; END --new primary key END^SET TERM ; ^SET TERM ^ ;CREATE TRIGGER SEQ_TBLMP3S FOR TBLMP3S ACTIVEBEFORE INSERT POSITION 0ASBEGIN -- New generated primary key if not specified. IF ((NEW.MP3ID IS NULL) OR (NEW.MP3ID=0)) THEN BEGIN NEW.MP3ID = NEXT VALUE FOR SEQ_MP3ID; END --new primary keyEND^SET TERM ; ^SET TERM ^ ;CREATE TRIGGER SEQ_TBLORIENTATIONS FOR TBLORIENTATIONS ACTIVEBEFORE INSERT POSITION 0ASBEGIN -- New generated primary key if not specified. IF ((NEW.ORIENTATIONID IS NULL) OR (NEW.ORIENTATIONID=0)) THEN BEGIN NEW.ORIENTATIONID = NEXT VALUE FOR SEQ_ORIENTATIONID; END --new primary key END^SET TERM ; ^SET TERM ^ ;CREATE TRIGGER SEQ_TBLPATHS FOR TBLPATHS ACTIVEBEFORE INSERT POSITION 0ASBEGIN -- New generated primary key if not specified. IF ((NEW.PathID IS NULL) OR (NEW.PathID=0)) THEN BEGIN NEW.PathID = NEXT VALUE FOR SEQ_PATHID; END --new primary keyEND^SET TERM ; ^SET TERM ^ ;CREATE TRIGGER SEQ_TBLSCANS FOR TBLSCANS ACTIVEBEFORE INSERT POSITION 0ASdeclare variable localWHENCREATED TIMESTAMP; /* The value for WHENCREATED */BEGIN localWHENCREATED = CURRENT_TIMESTAMP; -- New generated primary key if not specified. IF ((NEW.ScanID IS NULL) OR (NEW.ScanID=0)) THEN BEGIN NEW.ScanID = NEXT VALUE FOR SEQ_SCANID; END --new primary key /* Insert current date/time unless it is specified */ IF (NEW.WHENCREATED IS NULL) THEN BEGIN NEW.WHENCREATED = localWHENCREATED; END -- Determine new.whencreated. END^SET TERM ; ^SET TERM ^ ;CREATE TRIGGER SEQ_TBLUSERCOMMENTS FOR TBLUSERCOMMENTS ACTIVEBEFORE INSERT POSITION 0ASBEGIN -- New generated primary key if not specified. IF ((NEW.USERCOMMENTID IS NULL) OR (NEW.USERCOMMENTID=0)) THEN BEGIN NEW.USERCOMMENTID = NEXT VALUE FOR SEQ_USERCOMMENTID; END --new primary key END^SET TERM ; ^UPDATE RDB$RELATION_FIELDS set RDB$DESCRIPTION = 'User notes' where RDB$FIELD_NAME = 'NOTES' and RDB$RELATION_NAME = 'TBLCATALOGS';CREATE UNIQUE INDEX UICOMPUTERNAME ON TBLCOMPUTERS (COMPUTERNAME);ALTER TABLE TBLEXIFS ADD CONSTRAINT FK_EXIFEXPOSUREPROGRAM FOREIGN KEY (EXIFEXPOSUREPROGRAM) REFERENCES TBLEXPOSUREPROGRAMS (EXPOSUREPROGRAMID) ON UPDATE CASCADE ON DELETE CASCADE;ALTER TABLE TBLEXIFS ADD CONSTRAINT FK_EXIFFLASH FOREIGN KEY (EXIFFLASH) REFERENCES TBLFLASHES (FLASHID) ON UPDATE CASCADE ON DELETE CASCADE;ALTER TABLE TBLEXIFS ADD CONSTRAINT FK_EXIFLIGHTSOURCE FOREIGN KEY (EXIFLIGHTSOURCE) REFERENCES TBLLIGHTSOURCES (LIGHTSOURCEID) ON UPDATE CASCADE ON DELETE CASCADE;ALTER TABLE TBLEXIFS ADD CONSTRAINT FK_EXIFORIENTATION FOREIGN KEY (EXIFORIENTATION) REFERENCES TBLORIENTATIONS (ORIENTATIONID) ON UPDATE CASCADE ON DELETE CASCADE;ALTER TABLE TBLEXIFS ADD CONSTRAINT FK_EXIFUSERCOMMENTS FOREIGN KEY (EXIFUSERCOMMENTS) REFERENCES TBLUSERCOMMENTS (USERCOMMENTID) ON UPDATE CASCADE ON DELETE CASCADE;CREATE UNIQUE INDEX UIFILENAME ON TBLFILENAMES (FILENAME);UPDATE RDB$RELATION_FIELDS set RDB$DESCRIPTION = 'Date when file was last modified.' where RDB$FIELD_NAME = 'DATEMODIFIED' and RDB$RELATION_NAME = 'TBLFILES';UPDATE RDB$RELATION_FIELDS set RDB$DESCRIPTION = 'Date when file was initially created.' where RDB$FIELD_NAME = 'DATECREATED' and RDB$RELATION_NAME = 'TBLFILES';UPDATE RDB$RELATION_FIELDS set RDB$DESCRIPTION = 'Date when file was last accessed/read/written.' where RDB$FIELD_NAME = 'DATEACCESSED' and RDB$RELATION_NAME = 'TBLFILES';UPDATE RDB$RELATION_FIELDS set RDB$DESCRIPTION = 'User-supplied description.' where RDB$FIELD_NAME = 'FILEDESCRIPTION' and RDB$RELATION_NAME = 'TBLFILES';ALTER TABLE TBLFILES ADD CONSTRAINT FK_COMPUTERID FOREIGN KEY (COMPUTERID) REFERENCES TBLCOMPUTERS (COMPUTERID) ON UPDATE CASCADE ON DELETE CASCADE;ALTER TABLE TBLFILES ADD CONSTRAINT FK_EXEID FOREIGN KEY (EXEID) REFERENCES TBLEXES (EXEID) ON UPDATE CASCADE ON DELETE CASCADE;ALTER TABLE TBLFILES ADD CONSTRAINT FK_FILENAMEID FOREIGN KEY (FILENAMEID) REFERENCES TBLFILENAMES (FILENAMEID) ON UPDATE CASCADE ON DELETE CASCADE;ALTER TABLE TBLFILES ADD CONSTRAINT FK_PATHID FOREIGN KEY (PATHID) REFERENCES TBLPATHS (PATHID) ON UPDATE CASCADE ON DELETE CASCADE;ALTER TABLE TBLFILES ADD CONSTRAINT FK_SCANID FOREIGN KEY (SCANID) REFERENCES TBLSCANS (SCANID) ON UPDATE CASCADE ON DELETE CASCADE;CREATE INDEX ICOMPUTERID ON TBLFILES (COMPUTERID);CREATE INDEX IEXEID ON TBLFILES (EXEID);CREATE INDEX IFILEDESCRIPTION ON TBLFILES (FILEDESCRIPTION);CREATE INDEX IFILENAMEID ON TBLFILES (FILENAMEID);CREATE INDEX IPATHID ON TBLFILES (PATHID);UPDATE RDB$RELATION_FIELDS set RDB$DESCRIPTION = 'The MD5 hash of the file contents.' where RDB$FIELD_NAME = 'MD5HASH' and RDB$RELATION_NAME = 'TBLMD5S';CREATE INDEX IMD5HASH ON TBLMD5S (MD5HASH);ALTER TABLE TBLMP3S ADD CONSTRAINT FK_MP3GENREID FOREIGN KEY (MP3GENREID) REFERENCES TBLMP3GENRES (MP3GENREID) ON UPDATE CASCADE ON DELETE CASCADE;CREATE UNIQUE INDEX UIFILEPATH ON TBLPATHS (FILEPATH);UPDATE RDB$RELATION_FIELDS set RDB$DESCRIPTION = 'Unique identifier/primary key for scans.' where RDB$FIELD_NAME = 'SCANID' and RDB$RELATION_NAME = 'TBLSCANS';UPDATE RDB$RELATION_FIELDS set RDB$DESCRIPTION = 'Link to catalog of which this scan is a part. A catalog may contain one or more scans.' where RDB$FIELD_NAME = 'CATALOGID' and RDB$RELATION_NAME = 'TBLSCANS';UPDATE RDB$RELATION_FIELDS set RDB$DESCRIPTION = 'Path where the scan begins. Examples: C:\ on Windows, /usr on Unix/Linux/OSX' where RDB$FIELD_NAME = 'ROOT' and RDB$RELATION_NAME = 'TBLSCANS';UPDATE RDB$RELATION_FIELDS set RDB$DESCRIPTION = 'Volume serial description for CD, DVD, Hard disk root. Handy for recognizing DVDs etc' where RDB$FIELD_NAME = 'VOLUME' and RDB$RELATION_NAME = 'TBLSCANS';UPDATE RDB$RELATION_FIELDS set RDB$DESCRIPTION = 'Volume serial number (hopefully a unique id)' where RDB$FIELD_NAME = 'SERIAL' and RDB$RELATION_NAME = 'TBLSCANS';UPDATE RDB$RELATION_FIELDS set RDB$DESCRIPTION = 'Filter used when scanning files. If not present, all files are scanned.' where RDB$FIELD_NAME = 'FILTERSTRING' and RDB$RELATION_NAME = 'TBLSCANS';UPDATE RDB$RELATION_FIELDS set RDB$DESCRIPTION = 'Optional description of the scan: e.g. filesystem details.' where RDB$FIELD_NAME = 'DESCRIPTION' and RDB$RELATION_NAME = 'TBLSCANS';UPDATE RDB$RELATION_FIELDS set RDB$DESCRIPTION = 'System-generated: date when the scan record was created.' where RDB$FIELD_NAME = 'WHENCREATED' and RDB$RELATION_NAME = 'TBLSCANS';ALTER TABLE TBLSCANS ADD CONSTRAINT FK_CATALOGID FOREIGN KEY (CATALOGID) REFERENCES TBLCATALOGS (CATALOGID) ON UPDATE CASCADE ON DELETE CASCADE;UPDATE RDB$RELATIONS set RDB$DESCRIPTION = 'todo: work in progress; show directory and subdirectory size. should show size for entire c: or / drive....' where RDB$RELATION_NAME = 'CUMULATIVEDIRECTORYSIZE';UPDATE RDB$RELATIONS set RDB$DESCRIPTION = 'Join of TBLSCANS and TBLFILES. Useful for operations such as getting all subdirectories and sizes in a given scan/filesystem.Bit strange why we have to give distinct. TODO: fix SQL; this must be easier to do.' where RDB$RELATION_NAME = 'SCANFILES';UPDATE RDB$RELATIONS set RDB$DESCRIPTION = 'All paths contained in all scans. Unique excerpt from TBLFILES. Useful for subdirectory calculation, etc.' where RDB$RELATION_NAME = 'SCANSPATHS';UPDATE RDB$TRIGGERS set RDB$DESCRIPTION = 'Table for database/application level logging, e.g. for errors in stored procedures.' where RDB$TRIGGER_NAME = 'SEQ_LOGS';GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE ON LOGS TO SYSDBA WITH GRANT OPTION;GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE ON TBLCATALOGS TO SYSDBA WITH GRANT OPTION;GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE ON TBLCOMPUTERS TO SYSDBA WITH GRANT OPTION;GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE ON TBLEXES TO SYSDBA WITH GRANT OPTION;GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE ON TBLEXIFS TO SYSDBA WITH GRANT OPTION;GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE ON TBLEXPOSUREPROGRAMS TO SYSDBA WITH GRANT OPTION;GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE ON TBLFILENAMES TO SYSDBA WITH GRANT OPTION;GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE ON TBLFILES TO SYSDBA WITH GRANT OPTION;GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE ON TBLFLASHES TO SYSDBA WITH GRANT OPTION;GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE ON TBLLIGHTSOURCES TO SYSDBA WITH GRANT OPTION;GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE ON TBLMD5S TO SYSDBA WITH GRANT OPTION;GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE ON TBLMP3GENRES TO SYSDBA WITH GRANT OPTION;GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE ON TBLMP3S TO SYSDBA WITH GRANT OPTION;GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE ON TBLORIENTATIONS TO SYSDBA WITH GRANT OPTION;GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE ON TBLPATHS TO SYSDBA WITH GRANT OPTION;GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE ON TBLSCANS TO SYSDBA WITH GRANT OPTION;GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE ON TBLUSERCOMMENTS TO SYSDBA WITH GRANT OPTION;GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE ON CATALOGSCANS TO SYSDBA WITH GRANT OPTION;GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE ON CUMULATIVEDIRECTORYSIZE TO SYSDBA WITH GRANT OPTION;GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE ON DIRECTORYSIZE TO SYSDBA WITH GRANT OPTION;GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE ON FILESDEFAULTSCAN TO SYSDBA WITH GRANT OPTION;GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE ON SCANFILES TO SYSDBA WITH GRANT OPTION;GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE ON SCANSPATHS TO SYSDBA WITH GRANT OPTION;GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE ON SUBDIRECTORIES TO SYSDBA WITH GRANT OPTION;-- Commit all DDL statementsCOMMIT;-- Add some sample dataINSERT INTO TBLCATALOGS (CATALOGID, DESCRIPTION, NOTES, WHENCREATED) VALUES ('11', 'Default catalog', 'System-generated catalog used for all scans that have no explicit catalog specified.', '23.09.2010, 08:49:52.943');INSERT INTO TBLSCANS (SCANID, CATALOGID, ROOT, VOLUME, SERIAL, FILTERSTRING, DESCRIPTION, WHENCREATED) VALUES ('11', '11', '', NULL, NULL, NULL, 'Default scan', '23.09.2010, 08:49:52.943');INSERT INTO TBLCOMPUTERS (COMPUTERID, COMPUTERNAME) VALUES ('11', 'JEKYLL');INSERT INTO TBLEXES (EXEID, EXECOMPANY, EXECOPYRIGHT, EXEDESCRIPTION, EXEFILEVERSION, EXEINTERNALNAME, EXEORIGINALFILENAME, EXEPRODUCTNAME, EXEPRODUCTVERSION) VALUES ('11', 'Microsoft', 'Copyright 1989', 'MSDOS', '6.20', 'MS DOS 6.20', 'AUTOEXEC.BAT', 'MS DOS', '6.20');INSERT INTO TBLUSERCOMMENTS (USERCOMMENTID, "COMMENT") VALUES ('11', 'A picture of dos or something. Can be unicode');INSERT INTO TBLPATHS (PATHID, FILEPATH) VALUES ('11', 'C:\');INSERT INTO TBLORIENTATIONS (ORIENTATIONID, ORIENTATION) VALUES ('11', 'Straight. or possibly bisexual');INSERT INTO TBLMP3GENRES (MP3GENREID, GENRE) VALUES ('11', 'Antique');INSERT INTO TBLMD5S (MD5ID, MD5HASH) VALUES ('11', 'e4d909c290d0fb1ca068ffaddf22cbd0');INSERT INTO TBLLIGHTSOURCES (LIGHTSOURCEID, LIGHTSOURCE) VALUES ('11', 'Light from above');INSERT INTO TBLFLASHES (FLASHID, FLASH) VALUES ('11', 'Flash Gordon');INSERT INTO TBLFILENAMES (FILENAMEID, FILENAME) VALUES ('11', 'AUTOEXEC.BAT');INSERT INTO TBLEXPOSUREPROGRAMS (EXPOSUREPROGRAMID, EXPOSUREPROGRAM) VALUES ('11', 'Systematic exposure program');INSERT INTO TBLMP3S (MP3ID, MP3ALBUM, MP3ARTIST, MP3COMMENT, MP3TITLE, MP3TRACK, MP3YEAR, MP3GENREID) VALUES ('11', 'Bill''s greatest hits', 'B. Gates', 'Short names only', 'Get this party started!', '6', '1989', '11');INSERT INTO TBLEXIFS (EXIFID, EXIFAPERTURE, EXIFARTIST, EXIFCOMPRESSEDBPP, EXIFCOPYRIGHT, EXIFDATETIME, EXIFDATETIMEDIGITIZED, EXIFDATETIMEORIGINAL, EXIFEXPOSURE, EXIFEXPOSUREPROGRAM, EXIFFLASH, EXIFFSTOPS, EXIFIMAGEDESCRIPTION, EXIFISO, EXIFLIGHTSOURCE, EXIFMAKE, EXIFMAXAPERTURE, EXIFMETERINGMETHOD, EXIFMETERINGMODE, EXIFMODEL, EXIFORIENTATION, EXIFPIXELXDIMENSION, EXIFPIXELYDIMENSION, EXIFSHUTTERSPEED, EXIFSOFTWARE, EXIFUSERCOMMENTS, EXIFXRESOLUTION, EXIFYRESOLUTION) VALUES ('11', 'EXIFAPERTURE', 'Bill Gates', '400bpp', 'Microsoft', '31.07.2009, 14:04:22.000', '31.07.2009, 14:04:22.000', '03.02.2005, 09:22:12.000', 'Exposure is good!', '11', '11', 'Pull out the stops', 'A picture of DOS in ASCII', '100', '11', 'Canon', 'maxaperture', 'metmethod', 'meteringmode', 'Ixus 300', '11', '640', '480', 'fast', 'Definitely software', '11', '640', '480');INSERT INTO TBLFILES (FILEID, SCANID, COMPUTERID, EXEID, EXIFID, FILENAMEID, MD5ID, MP3ID, FILESIZE, PATHID, DATEMODIFIED, DATECREATED, DATEACCESSED, FILEDESCRIPTION) VALUES ('5', '11', '11', '11', '11', '11', '11', '11', '188', '11', '03.02.2005, 09:22:12.000', '31.12.1989, 14:04:22.000', '31.07.2009, 14:04:22.000', 'A description');COMMIT;-- Now run the problematic view:SELECT r.COMPUTERNAME, r.FILEPATH, r.FILENAME, r.FILESIZE, r.FILEDESCRIPTION, r.DATEMODIFIED, r.DATEACCESSED, r.DATECREATED, r.MD5HASH, r.EXECOMPANY, r.EXECOPYRIGHT, r.EXEDESCRIPTION, r.EXEFILEVERSION, r.EXEINTERNALNAME, r.EXEORIGINALFILENAME, r.EXEPRODUCTNAME, r.EXEPRODUCTVERSION, r.EXIFID, r.EXIFAPERTURE, r.EXIFARTIST, r.EXIFCOMPRESSEDBPP, r.EXIFCOPYRIGHT, r.EXIFDATETIME, r.EXIFDATETIMEDIGITIZED, r.EXIFDATETIMEORIGINAL, r.EXIFEXPOSUREPROGRAM, r.EXIFFLASH, r.EXIFFSTOPS, r.EXIFIMAGEDESCRIPTION, r.EXIFISO, r.EXIFLIGHTSOURCE, r.EXIFMAKE, r.EXIFMAXAPERTURE, r.EXIFMETERINGMETHOD, r.EXIFMETERINGMODE, r.EXIFMODEL, r.EXIFORIENTATION, r.EXIFPIXELXDIMENSION, r.EXIFPIXELYDIMENSION, r.EXIFSHUTTERSPEED, r.EXIFSOFTWARE, r.EXIFUSERCOMMENTS, r.EXIFXRESOLUTION, r.EXIFYRESOLUTION, r.MP3ALBUM, r.MP3ARTIST, r.MP3COMMENT, r.MP3TITLE, r.MP3TRACK, r.MP3YEAR, r.MP3GENREIDFROM FILESDEFAULTSCAN r