| Subject | Re: Overflow error during data type conversion |
|---|---|
| Author | realjimbeam |
| Post date | 2010-09-24T07:05:41Z |
I've stripped down the database in question and tried inserting the value 1 instead of Systematic exposure in the record in TBLEXPOSUREPROGRAMS (the offending record). If I use the number, it works, indicating this really is a character conversion issue.
If I insert Systematic exposure using isql.exe instead of Firebird, and run the view, I get the same error.
Am I somehow inserting invalid text? Systematic exposure doesn't strike me even as weird ASCII, so it must be perfectly acceptable Unicode?
Should I file a bug on the Firebird tracker?
Tightened database test script:
http://pastebin.ca/1948077
or
/********************* 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
SELECT
computer.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 MP3GENREID
FROM
(
(
(
(
(
(
(
(
(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 statements
COMMIT;
-- Add some sample data
UPDATE OR INSERT 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');
UPDATE OR 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');
UPDATE OR INSERT INTO TBLCOMPUTERS (COMPUTERID, COMPUTERNAME) VALUES ('11', 'JEKYLL');
UPDATE OR 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');
UPDATE OR INSERT INTO TBLUSERCOMMENTS (USERCOMMENTID, "COMMENT") VALUES ('11', 'A picture of dos or something. Can be unicode');
UPDATE OR INSERT INTO TBLPATHS (PATHID, FILEPATH) VALUES ('11', 'C:\');
UPDATE OR INSERT INTO TBLORIENTATIONS (ORIENTATIONID, ORIENTATION) VALUES ('11', 'Straight. or possibly bisexual');
UPDATE OR INSERT INTO TBLMP3GENRES (MP3GENREID, GENRE) VALUES ('11', 'Antique');
UPDATE OR INSERT INTO TBLMD5S (MD5ID, MD5HASH) VALUES ('11', 'e4d909c290d0fb1ca068ffaddf22cbd0');
UPDATE OR INSERT INTO TBLLIGHTSOURCES (LIGHTSOURCEID, LIGHTSOURCE) VALUES ('11', 'Light from above');
UPDATE OR INSERT INTO TBLFLASHES (FLASHID, FLASH) VALUES ('11', 'Flash Gordon');
UPDATE OR INSERT INTO TBLFILENAMES (FILENAMEID, FILENAME) VALUES ('11', 'AUTOEXEC.BAT');
UPDATE OR INSERT INTO TBLEXPOSUREPROGRAMS (EXPOSUREPROGRAMID, EXPOSUREPROGRAM) VALUES ('11', 'Systematic exposure program');
UPDATE OR 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');
UPDATE OR 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');
UPDATE OR 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
If I insert Systematic exposure using isql.exe instead of Firebird, and run the view, I get the same error.
Am I somehow inserting invalid text? Systematic exposure doesn't strike me even as weird ASCII, so it must be perfectly acceptable Unicode?
Should I file a bug on the Firebird tracker?
Tightened database test script:
http://pastebin.ca/1948077
or
/********************* 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
SELECT
computer.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 MP3GENREID
FROM
(
(
(
(
(
(
(
(
(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 statements
COMMIT;
-- Add some sample data
UPDATE OR INSERT 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');
UPDATE OR 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');
UPDATE OR INSERT INTO TBLCOMPUTERS (COMPUTERID, COMPUTERNAME) VALUES ('11', 'JEKYLL');
UPDATE OR 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');
UPDATE OR INSERT INTO TBLUSERCOMMENTS (USERCOMMENTID, "COMMENT") VALUES ('11', 'A picture of dos or something. Can be unicode');
UPDATE OR INSERT INTO TBLPATHS (PATHID, FILEPATH) VALUES ('11', 'C:\');
UPDATE OR INSERT INTO TBLORIENTATIONS (ORIENTATIONID, ORIENTATION) VALUES ('11', 'Straight. or possibly bisexual');
UPDATE OR INSERT INTO TBLMP3GENRES (MP3GENREID, GENRE) VALUES ('11', 'Antique');
UPDATE OR INSERT INTO TBLMD5S (MD5ID, MD5HASH) VALUES ('11', 'e4d909c290d0fb1ca068ffaddf22cbd0');
UPDATE OR INSERT INTO TBLLIGHTSOURCES (LIGHTSOURCEID, LIGHTSOURCE) VALUES ('11', 'Light from above');
UPDATE OR INSERT INTO TBLFLASHES (FLASHID, FLASH) VALUES ('11', 'Flash Gordon');
UPDATE OR INSERT INTO TBLFILENAMES (FILENAMEID, FILENAME) VALUES ('11', 'AUTOEXEC.BAT');
UPDATE OR INSERT INTO TBLEXPOSUREPROGRAMS (EXPOSUREPROGRAMID, EXPOSUREPROGRAM) VALUES ('11', 'Systematic exposure program');
UPDATE OR 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');
UPDATE OR 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');
UPDATE OR 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
--- In firebird-support@yahoogroups.com, "realjimbeam" <realjimbeam@...> wrote:
>
> Hi all,
> Flamerobin Firebird 2.1.3 x86/Linux, db UTF8, ODS 11.1, page size 16384, (but similar issue on Firebird 2.5RC3 Windows)
> If I run the view FILESDEFAULTSCAN (complex in that it joins a lot of table, but basically spits out a denormalized version), I get a SQL message -413 Overflow occurred during data type conversion. Engine code 33554434, Engine Message: conversion error from string "Systematic exposure program"
>
> I'm not trying to cast/convert anything; the column in question is a varchar(255) column.
>
> Is this my problem or a database bug?
>
> DDL/test data/view script:
> http://pastebin.ca/1947185
> or
> /* test environment for view filesdefaultscan.
> this script will create a database (you need an empty db first), add sample data and run the view
> jb 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)
> AS
> SELECT
> C.CATALOGID,
> S.SCANID,
> C.DESCRIPTION,
> C.NOTES,
> C.WHENCREATED,
> S.ROOT,
> S.VOLUME,
> S.SERIAL,
> S.DESCRIPTION,
> S.FILTERSTRING,
> S.WHENCREATED
> FROM
> TBLCATALOGS C INNER JOIN TBLSCANS S ON C.CATALOGID=S.CATALOGID
> ORDER 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)
> AS
> SELECT
> computer.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 MP3GENREID
> FROM
> (
> (
> (
> (
> (
> (
> (
> (
> (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)
> AS
> SELECT
> s.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 DISTINCT
> SCANID, PATHID
> FROM
> TBLFILES;
> CREATE VIEW SUBDIRECTORIES (SCANID, PATHID, FILEPATH, SUBDIR, SUBPATHID)
> AS
> SELECT
> DISTINCT
> S.SCANID,
> P.PATHID,
> P.FILEPATH,
> C.FILEPATH AS SUBDIR,
> C.PATHID AS SUBPATHID
> FROM
> (SCANSPATHS S INNER JOIN
> TBLPATHS P ON S.PATHID=P.PATHID),
> TBLPATHS C
> WHERE 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 NONRECURSIVESIZE
> FROM SCANFILES
> INNER JOIN TBLPATHS
> ON SCANFILES.PATHID = TBLPATHS.PATHID
> GROUP BY SCANFILES.CATALOGID, SCANFILES.SCANID, TBLPATHS.PATHID, TBLPATHS.FILEPATH;
> CREATE VIEW CUMULATIVEDIRECTORYSIZE (CATALOG_ID, SCAN_ID, FILEPATH, TOTALSIZE)
> AS
> SELECT
> D.CATALOGID,
> D.SCANID,
> D.FILEPATH,
> D.NONRECURSIVESIZE
> FROM DIRECTORYSIZE D
> INNER JOIN SUBDIRECTORIES S
> ON 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 ACTIVE
> BEFORE INSERT POSITION 0
> AS
> BEGIN
> -- 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;
> END
> END^
> SET TERM ; ^
> SET TERM ^ ;
> CREATE TRIGGER SEQ_TBLCATALOGS FOR TBLCATALOGS ACTIVE
> BEFORE INSERT POSITION 0
> AS
> declare 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;
> END
> END^
> SET TERM ; ^
> SET TERM ^ ;
> CREATE TRIGGER SEQ_TBLCOMPUTERS FOR TBLCOMPUTERS ACTIVE
> BEFORE INSERT POSITION 0
> AS
> BEGIN
> IF ((NEW.ComputerID IS NULL) OR (NEW.ComputerID=0)) THEN
> BEGIN
> NEW.ComputerID = NEXT VALUE FOR SEQ_COMPUTERID;
> END --new primary key
> END^
> SET TERM ; ^
> SET TERM ^ ;
> CREATE TRIGGER SEQ_TBLEXES FOR TBLEXES ACTIVE
> BEFORE INSERT POSITION 0
> AS
> BEGIN
> -- 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 ACTIVE
> BEFORE INSERT POSITION 0
> AS
> BEGIN
> -- 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 key
> END^
> SET TERM ; ^
> SET TERM ^ ;
> CREATE TRIGGER SEQ_TBLEXPOSUREPROGRAMS FOR TBLEXPOSUREPROGRAMS ACTIVE
> BEFORE INSERT POSITION 0
> AS
> BEGIN
> -- 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 ACTIVE
> BEFORE INSERT POSITION 0
> AS
> BEGIN
> -- 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 key
> END^
> SET TERM ; ^
> SET TERM ^ ;
> CREATE TRIGGER SEQ_TBLFILES FOR TBLFILES ACTIVE
> BEFORE INSERT POSITION 0
> AS
> BEGIN
> -- 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 ACTIVE
> BEFORE INSERT POSITION 0
> AS
> BEGIN
> -- 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 ACTIVE
> BEFORE INSERT POSITION 0
> AS
> BEGIN
> -- 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 ACTIVE
> BEFORE INSERT POSITION 0
> AS
> BEGIN
> -- 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 ACTIVE
> BEFORE INSERT POSITION 0
> AS
> BEGIN
> -- 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 ACTIVE
> BEFORE INSERT POSITION 0
> AS
> BEGIN
> -- 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 key
> END^
> SET TERM ; ^
> SET TERM ^ ;
> CREATE TRIGGER SEQ_TBLORIENTATIONS FOR TBLORIENTATIONS ACTIVE
> BEFORE INSERT POSITION 0
> AS
> BEGIN
> -- 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 ACTIVE
> BEFORE INSERT POSITION 0
> AS
> BEGIN
> -- 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 key
> END^
> SET TERM ; ^
> SET TERM ^ ;
> CREATE TRIGGER SEQ_TBLSCANS FOR TBLSCANS ACTIVE
> BEFORE INSERT POSITION 0
> AS
> declare 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 ACTIVE
> BEFORE INSERT POSITION 0
> AS
> BEGIN
> -- 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 statements
> COMMIT;
> -- Add some sample data
> INSERT 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.MP3GENREID
> FROM FILESDEFAULTSCAN r
>