rendered paste body/********************* ROLES **********************//********************* UDFS ***********************//****************** GENERATORS ********************//******************** DOMAINS *********************/CREATE DOMAIN MEMO AS BLOB SUB_TYPE 1 DEFAULT NULL COLLATE UTF8;/******************* PROCEDURES ******************//******************** TABLES **********************/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 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)AS SELECTcomputer.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;/******************** TRIGGERS ********************/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);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);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);ALTER TABLE TBLSCANS ADD CONSTRAINT FK_CATALOGID FOREIGN KEY (CATALOGID) REFERENCES TBLCATALOGS (CATALOGID) ON UPDATE CASCADE ON DELETE CASCADE;-- 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 *FROM FILESDEFAULTSCAN r