| Subject | Overflow error during data type conversion |
|---|---|
| Author | realjimbeam |
| Post date | 2010-09-23T08:17:30Z |
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
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