From 2827d5bfe8e22ab03ab7c89ef244ff3903193b33 Mon Sep 17 00:00:00 2001 From: Marco van Wieringen Date: Sat, 17 Apr 2010 10:12:13 +0200 Subject: [PATCH] First stab at rewriting the ingres database schema to allow bigger path and filenames up to the current limit of 32000 bytes. Also reindented the database definitions to the Bacula standards which make it somewhat easier to read. Implemented the retrieval of some additional datatypes in the ingres backend now we switched some types from VARCHAR to VARBYTE. --- bacula/src/cats/drop_ingres_tables.in | 62 +- bacula/src/cats/grant_ingres_privileges.in | 60 +- bacula/src/cats/make_ingres_tables.in | 636 +++++++++++---------- 3 files changed, 382 insertions(+), 376 deletions(-) diff --git a/bacula/src/cats/drop_ingres_tables.in b/bacula/src/cats/drop_ingres_tables.in index da68087c7d..33ad322f8c 100755 --- a/bacula/src/cats/drop_ingres_tables.in +++ b/bacula/src/cats/drop_ingres_tables.in @@ -8,49 +8,49 @@ db_name=${db_name:-@db_name@} db_user=${db_user:-@db_user@} sql -u${db_user} ${db_name} $* <.dbms.private.*.cache.p8k_status: ON +-- ii..dbms.private.*.cache.p16k_status: ON +-- ii..dbms.private.*.cache.p32k_status: ON +-- ii..dbms.private.*.cache.p64k_status: ON +-- ii..rcp.dmf_cache_size8k: 200 +-- ii..rcp.dmf_cache_size16k: 200 +-- ii..rcp.dmf_cache_size32k: 200 +-- ii..rcp.dmf_cache_size64k: 200 +-- + SET AUTOCOMMIT ON\g -CREATE SEQUENCE filename_seq; -CREATE TABLE filename +CREATE SEQUENCE Filename_Seq; +CREATE TABLE Filename ( - filenameid integer not null default filename_seq.nextval, - name varchar(256) not null, - primary key (filenameid) + FilenameId INTEGER NOT NULL DEFAULT Filename_Seq.nextval, + Name VARBYTE(32000) NOT NULL, + PRIMARY KEY (FilenameId) ); --- ALTER TABLE filename ALTER COLUMN name SET STATISTICS 1000; -CREATE UNIQUE INDEX filename_name_idx on filename (name); +CREATE UNIQUE INDEX (filename_name_idx ON filename (Name) WITH STRUCTURE=HASH,PAGE_SIZE=32768); -CREATE SEQUENCE path_seq; +CREATE SEQUENCE Path_Seq; CREATE TABLE path ( - pathid integer not null default path_seq.nextval, - path varchar(256) not null, - primary key (pathid) + Pathid INTEGER NOT NULL DEFAULT Path_Seq.nextval, + Path VARBYTE(32000) NOT NULL, + PRIMARY KEY (Pathid) ); --- ALTER TABLE path ALTER COLUMN path SET STATISTICS 1000; -CREATE UNIQUE INDEX path_name_idx on path (path); +CREATE UNIQUE INDEX (path_name_idx ON path (Path) WITH STRUCTURE=HASH,PAGE_SIZE=32768); -CREATE SEQUENCE file_seq; +CREATE SEQUENCE File_Seq; CREATE TABLE file ( - fileid integer8 not null default file_seq.nextval, - fileindex integer not null default 0, - jobid integer not null, - pathid integer not null, - filenameid integer not null, - markid integer not null default 0, - lstat varchar(256) not null, - md5 varchar(256) not null, - primary key (fileid) + FileId BIGINT NOT NULL DEFAULT File_Seq.nextval, + FileIndex INTEGER NOT NULL DEFAULT 0, + JobId INTEGER NOT NULL, + PathId INTEGER NOT NULL, + FilenameId INTEGER NOT NULL, + MarkId INTEGER NOT NULL DEFAULT 0, + Lstat VARBYTE(255) NOT NULL, + Md5 VARBYTE(255) NOT NULL, + PRIMARY KEY (fileid) ); -CREATE INDEX file_jpfid_idx on file (jobid, pathid, filenameid); +CREATE INDEX file_jpfid_idx ON File (jobid, pathid, filenameid); -- If you need performances, you can remove this index -- the database engine is able to use the composite index -- to find all records with a given JobId -CREATE INDEX file_jobid_idx on file(jobid); - --- --- Add this if you have a good number of job --- that run at the same time --- ALTER SEQUENCE file_fileid_seq CACHE 1000; - --- --- Possibly add one or more of the following indexes --- if your Verifies are too slow. --- --- CREATE INDEX file_pathid_idx on file(pathid); --- CREATE INDEX file_filenameid_idx on file(filenameid); - --- --- ***FIXME*** this needs to be corrected and turned on ---CREATE TABLE RestoreObject ( --- RestoreObjectId INTEGER NOT NULL DEFAULT RestoreObject_seq.nextval, --- ObjectName TEXT NOT NULL, --- RestoreObject TEXT NOT NULL, --- PluginName TEXT NOT NULL, --- ObjectIndex INTEGER DEFAULT 0, --- ObjectType INTEGER DEFAULT 0, --- FileIndex INTEGER UNSIGNED DEFAULT 0, --- JobId INTEGER UNSIGNED, --- ObjectCompression INTEGER DEFAULT 0, --- PRIMARY KEY(RestoreObjectId), --- ); ---CREATE INDEX restore_jobid_idx on file(JobId); --- - +CREATE INDEX file_jobid_idx ON File(jobid); + +CREATE INDEX restore_jobid_idx ON File(JobId); + +CREATE SEQUENCE RestoreObject_Seq; +CREATE TABLE RestoreObject ( + RestoreObjectId INTEGER NOT NULL DEFAULT RestoreObject_Seq.nextval, + Fname VARBYTE(32000) NOT NULL, + Path VARBYTE(32000) NOT NULL, + RestoreObject VARBYTE(32000) NOT NULL, + PluginName VARBYTE(128) NOT NULL, + ObjectIndex INTEGER DEFAULT 0, + ObjectType INTEGER DEFAULT 0, + FileIndex INTEGER DEFAULT 0, + JobId INTEGER, + PRIMARY KEY(RestoreObjectId) +); -CREATE SEQUENCE Job_seq; +CREATE SEQUENCE Job_Seq; CREATE TABLE Job ( - JobId integer not null default Job_seq.nextval, - Job varchar(256) not null, - Name varchar(256) not null, - Type char(1) not null, - Level char(1) not null, - ClientId integer default 0, - JobStatus char(1) not null, - SchedTime timestamp without time zone, - StartTime timestamp without time zone, - EndTime timestamp without time zone, - RealEndTime timestamp without time zone, - JobTDate bigint default 0, - VolSessionId integer default 0, - volSessionTime integer default 0, - JobFiles integer default 0, - JobBytes bigint default 0, - ReadBytes bigint default 0, - JobErrors integer default 0, - JobMissingFiles integer default 0, - PoolId integer default 0, - FilesetId integer default 0, - PriorJobid integer default 0, - PurgedFiles smallint default 0, - HasBase smallint default 0, - HasCache smallint default 0, - Reviewed smallint default 0, - Comment varchar(256), - primary key (jobid) + JobId INTEGER NOT NULL DEFAULT Job_Seq.nextval, + Job VARBYTE(128) NOT NULL, + Name VARBYTE(128) NOT NULL, + Type CHAR(1) NOT NULL, + Level CHAR(1) NOT NULL, + ClientId INTEGER DEFAULT 0, + JobStatus CHAR(1) NOT NULL, + SchedTime TIMESTAMP WITHOUT TIME ZONE, + StartTime TIMESTAMP WITHOUT TIME ZONE, + EndTime TIMESTAMP WITHOUT TIME ZONE, + RealEndTime TIMESTAMP WITHOUT TIME ZONE, + JobTDate BIGINT DEFAULT 0, + VolSessionId INTEGER DEFAULT 0, + volSessionTime INTEGER DEFAULT 0, + JobFiles INTEGER DEFAULT 0, + JobBytes BIGINT DEFAULT 0, + ReadBytes BIGINT DEFAULT 0, + JobErrors INTEGER DEFAULT 0, + JobMissingFiles INTEGER DEFAULT 0, + PoolId INTEGER DEFAULT 0, + FilesetId INTEGER DEFAULT 0, + PriorJobid INTEGER DEFAULT 0, + PurgedFiles SMALLINT DEFAULT 0, + HasBase SMALLINT DEFAULT 0, + HasCache SMALLINT DEFAULT 0, + Reviewed SMALLINT DEFAULT 0, + Comment VARBYTE(4096), + PRIMARY KEY (JobId) ); -CREATE INDEX job_name_idx on job (name); +CREATE INDEX job_name_idx ON Job (Name); -- Create a table like Job for long term statistics -CREATE SEQUENCE JobHisto_seq; +CREATE SEQUENCE JobHisto_Seq; CREATE TABLE JobHisto ( - JobId integer not null default JobHisto_seq.nextval, - Job varchar(256) not null, - Name varchar(256) not null, - Type char(1) not null, - Level char(1) not null, - ClientId integer default 0, - JobStatus char(1) not null, - SchedTime timestamp without time zone, - StartTime timestamp without time zone, - EndTime timestamp without time zone, - RealEndTime timestamp without time zone, - JobTDate bigint default 0, - VolSessionId integer default 0, - volSessionTime integer default 0, - JobFiles integer default 0, - JobBytes bigint default 0, - ReadBytes bigint default 0, - JobErrors integer default 0, - JobMissingFiles integer default 0, - PoolId integer default 0, - FilesetId integer default 0, - PriorJobid integer default 0, - PurgedFiles smallint default 0, - HasBase smallint default 0, - HasCache smallint default 0, - Reviewed smallint default 0, - Comment varchar(256), - primary key (jobid) + JobId INTEGER NOT NULL DEFAULT JobHisto_Seq.nextval, + Job VARBYTE(128) NOT NULL, + Name VARBYTE(128) NOT NULL, + Type CHAR(1) NOT NULL, + Level CHAR(1) NOT NULL, + ClientId INTEGER DEFAULT 0, + JobStatus CHAR(1) NOT NULL, + SchedTime TIMESTAMP WITHOUT TIME ZONE, + StartTime TIMESTAMP WITHOUT TIME ZONE, + EndTime TIMESTAMP WITHOUT TIME ZONE, + RealEndTime TIMESTAMP WITHOUT TIME ZONE, + JobTDate BIGINT DEFAULT 0, + VolSessionId INTEGER DEFAULT 0, + volSessionTime INTEGER DEFAULT 0, + JobFiles INTEGER DEFAULT 0, + JobBytes BIGINT DEFAULT 0, + ReadBytes BIGINT DEFAULT 0, + JobErrors INTEGER DEFAULT 0, + JobMissingFiles INTEGER DEFAULT 0, + PoolId INTEGER DEFAULT 0, + FilesetId INTEGER DEFAULT 0, + PriorJobid INTEGER DEFAULT 0, + PurgedFiles SMALLINT DEFAULT 0, + HasBase SMALLINT DEFAULT 0, + HasCache SMALLINT DEFAULT 0, + Reviewed SMALLINT DEFAULT 0, + Comment VARBYTE(4096), + PRIMARY KEY (JobId) ); -CREATE INDEX jobhisto_idx on JobHisto ( StartTime ); - +CREATE INDEX jobhisto_idx ON JobHisto (StartTime); -CREATE SEQUENCE Location_seq; +CREATE SEQUENCE Location_Seq; CREATE TABLE Location ( - LocationId integer not null default Location_seq.nextval, - Location varchar(256) not null, - Cost integer default 0, - Enabled smallint, - primary key (LocationId) + LocationId INTEGER NOT NULL DEFAULT Location_Seq.nextval, + Location VARBYTE(128) NOT NULL, + Cost INTEGER DEFAULT 0, + Enabled SMALLINT, + PRIMARY KEY (LocationId) ); -CREATE SEQUENCE fileset_seq; -CREATE TABLE fileset +CREATE SEQUENCE Fileset_Seq; +CREATE TABLE Fileset ( - filesetid integer not null default fileset_seq.nextval, - fileset varchar(256) not null, - md5 varchar(256) not null, - createtime timestamp without time zone not null, - primary key (filesetid) + FilesetId INTEGER NOT NULL DEFAULT Fileset_Seq.nextval, + Fileset VARBYTE(128) NOT NULL, + Md5 VARBYTE(128) NOT NULL, + Createtime TIMESTAMP WITHOUT TIME ZONE NOT NULL, + PRIMARY KEY (filesetid) ); -CREATE INDEX fileset_name_idx on fileset (fileset); +CREATE INDEX fileset_name_idx ON Fileset (fileset); -CREATE SEQUENCE jobmedia_seq; -CREATE TABLE jobmedia +CREATE SEQUENCE JobMedia_Seq; +CREATE TABLE JobMedia ( - jobmediaid integer not null default jobmedia_seq.nextval, - jobid integer not null, - mediaid integer not null, - firstindex integer default 0, - lastindex integer default 0, - startfile integer default 0, - endfile integer default 0, - startblock bigint default 0, - endblock bigint default 0, - volindex integer default 0, - primary key (jobmediaid) + JobMediaId INTEGER NOT NULL DEFAULT JobMedia_Seq.nextval, + JobId INTEGER NOT NULL, + MediaId INTEGER NOT NULL, + FirstIndex INTEGER DEFAULT 0, + LastIndex INTEGER DEFAULT 0, + StartFile INTEGER DEFAULT 0, + EndFile INTEGER DEFAULT 0, + StartBlock BIGINT DEFAULT 0, + EndBlock BIGINT DEFAULT 0, + VolIndex INTEGER DEFAULT 0, + PRIMARY KEY (jobmediaid) ); -CREATE INDEX job_media_job_id_media_id_idx on jobmedia (jobid, mediaid); +CREATE INDEX job_media_job_id_media_id_idx ON JobMedia (jobid, mediaid); -CREATE SEQUENCE media_seq; -CREATE TABLE media +CREATE SEQUENCE Media_Seq; +CREATE TABLE Media ( - mediaid integer not null default media_seq.nextval, - volumename varchar(256) not null, - slot integer default 0, - poolid integer default 0, - mediatype varchar(256) not null, - mediatypeid integer default 0, - labeltype integer default 0, - firstwritten timestamp without time zone, - lastwritten timestamp without time zone, - labeldate timestamp without time zone, - voljobs integer default 0, - volfiles integer default 0, - volblocks integer default 0, - volmounts integer default 0, - volbytes bigint default 0, - volparts integer default 0, - volerrors integer default 0, - volwrites integer default 0, - volcapacitybytes bigint default 0, - volstatus varchar(256) not null - check (volstatus in ('Full','Archive','Append', + MediaId INTEGER NOT NULL DEFAULT Media_Seq.nextval, + VolumeName VARBYTE(128) NOT NULL, + Slot INTEGER DEFAULT 0, + PoolId INTEGER DEFAULT 0, + MediaType VARBYTE(128) NOT NULL, + MediaTypeId INTEGER DEFAULT 0, + LabelType INTEGER DEFAULT 0, + FirstWritten TIMESTAMP WITHOUT TIME ZONE, + LastWritten TIMESTAMP WITHOUT TIME ZONE, + LabelDate TIMESTAMP WITHOUT TIME ZONE, + VolJobs INTEGER DEFAULT 0, + VolFiles INTEGER DEFAULT 0, + VolBlocks INTEGER DEFAULT 0, + VolMounts INTEGER DEFAULT 0, + VolBytes BIGINT DEFAULT 0, + VolParts INTEGER DEFAULT 0, + VolErrors INTEGER DEFAULT 0, + VolWrites INTEGER DEFAULT 0, + VolCapacitybytes BIGINT DEFAULT 0, + VolStatus VARBYTE(128) NOT NULL + CHECK (volstatus in ('Full','Archive','Append', 'Recycle','Purged','Read-Only','Disabled', 'Error','Busy','Used','Cleaning','Scratch')), - enabled smallint default 1, - recycle smallint default 0, - ActionOnPurge smallint default 0, - volretention bigint default 0, - voluseduration bigint default 0, - maxvoljobs integer default 0, - maxvolfiles integer default 0, - maxvolbytes bigint default 0, - inchanger smallint default 0, - StorageId bigint default 0, - DeviceId integer default 0, - mediaaddressing smallint default 0, - volreadtime bigint default 0, - volwritetime bigint default 0, - endfile integer default 0, - endblock bigint default 0, - LocationId integer default 0, - recyclecount integer default 0, - initialwrite timestamp without time zone, - scratchpoolid integer default 0, - recyclepoolid integer default 0, - comment varchar(256), - primary key (mediaid) + Enabled SMALLINT DEFAULT 1, + Recycle SMALLINT DEFAULT 0, + ActionOnPurge SMALLINT DEFAULT 0, + VolRetention BIGINT DEFAULT 0, + VolUseDuration BIGINT DEFAULT 0, + MaxVolJobs INTEGER DEFAULT 0, + MaxVolFiles INTEGER DEFAULT 0, + MaxVolBytes BIGINT DEFAULT 0, + InChanger SMALLINT DEFAULT 0, + StorageId BIGINT DEFAULT 0, + DeviceId INTEGER DEFAULT 0, + MediaAddressing SMALLINT DEFAULT 0, + VolReadTime BIGINT DEFAULT 0, + VolWriteTime BIGINT DEFAULT 0, + EndFile INTEGER DEFAULT 0, + EndBlock BIGINT DEFAULT 0, + LocationId INTEGER DEFAULT 0, + RecycleCount INTEGER DEFAULT 0, + InitialWrite TIMESTAMP WITHOUT TIME ZONE, + ScratchPoolId INTEGER DEFAULT 0, + RecyclePoolId INTEGER DEFAULT 0, + Comment VARBYTE(4096), + PRIMARY KEY (mediaid) ); -create unique index media_volumename_id on media (volumename); +CREATE UNIQUE INDEX media_volumename_id ON Media (VolumeName); -CREATE SEQUENCE MediaType_seq; +CREATE SEQUENCE MediaType_Seq; CREATE TABLE MediaType ( - MediaTypeId INTEGER NOT NULL DEFAULT MediaType_seq.nextval, - MediaType varchar(256) NOT NULL, - ReadOnly INTEGER NOT NULL DEFAULT 0, + MediaTypeId INTEGER NOT NULL DEFAULT MediaType_Seq.nextval, + MediaType VARBYTE(128) NOT NULL, + ReadOnly INTEGER NOT NULL DEFAULT 0, PRIMARY KEY(MediaTypeId) - ); +); -CREATE SEQUENCE Storage_seq; +CREATE SEQUENCE Storage_Seq; CREATE TABLE Storage ( - StorageId INTEGER NOT NULL DEFAULT Storage_seq.nextval, - Name varchar(256) NOT NULL, - AutoChanger INTEGER NOT NULL DEFAULT 0, + StorageId INTEGER NOT NULL DEFAULT Storage_Seq.nextval, + Name VARBYTE(128) NOT NULL, + AutoChanger INTEGER NOT NULL DEFAULT 0, PRIMARY KEY(StorageId) - ); +); -CREATE SEQUENCE Device_seq; +CREATE SEQUENCE Device_Seq; CREATE TABLE Device ( - DeviceId INTEGER NOT NULL DEFAULT Device_seq.nextval, - Name varchar(256) NOT NULL, - MediaTypeId INTEGER NOT NULL, - StorageId INTEGER NOT NULL, - DevMounts INTEGER NOT NULL DEFAULT 0, - DevReadBytes BIGINT NOT NULL DEFAULT 0, - DevWriteBytes BIGINT NOT NULL DEFAULT 0, - DevReadBytesSinceCleaning BIGINT NOT NULL DEFAULT 0, - DevWriteBytesSinceCleaning BIGINT NOT NULL DEFAULT 0, - DevReadTime BIGINT NOT NULL DEFAULT 0, - DevWriteTime BIGINT NOT NULL DEFAULT 0, - DevReadTimeSinceCleaning BIGINT NOT NULL DEFAULT 0, - DevWriteTimeSinceCleaning BIGINT NOT NULL DEFAULT 0, - CleaningDate timestamp without time zone, - CleaningPeriod BIGINT NOT NULL DEFAULT 0, + DeviceId INTEGER NOT NULL DEFAULT Device_Seq.nextval, + Name VARBYTE(128) NOT NULL, + MediaTypeId INTEGER NOT NULL, + StorageId INTEGER NOT NULL, + DevMounts INTEGER NOT NULL DEFAULT 0, + DevReadBytes BIGINT NOT NULL DEFAULT 0, + DevWriteBytes BIGINT NOT NULL DEFAULT 0, + DevReadBytesSinceCleaning BIGINT NOT NULL DEFAULT 0, + DevWriteBytesSinceCleaning BIGINT NOT NULL DEFAULT 0, + DevReadTime BIGINT NOT NULL DEFAULT 0, + DevWriteTime BIGINT NOT NULL DEFAULT 0, + DevReadTimeSinceCleaning BIGINT NOT NULL DEFAULT 0, + DevWriteTimeSinceCleaning BIGINT NOT NULL DEFAULT 0, + CleaningDate TIMESTAMP WITHOUT TIME ZONE, + CleaningPeriod BIGINT NOT NULL DEFAULT 0, PRIMARY KEY(DeviceId) - ); +); -CREATE SEQUENCE pool_seq; -CREATE TABLE pool +CREATE SEQUENCE Pool_Seq; +CREATE TABLE Pool ( - poolid integer not null default pool_seq.nextval, - name varchar(256) not null, - numvols integer default 0, - maxvols integer default 0, - useonce smallint default 0, - usecatalog smallint default 0, - acceptanyvolume smallint default 0, - volretention bigint default 0, - voluseduration bigint default 0, - maxvoljobs integer default 0, - maxvolfiles integer default 0, - maxvolbytes bigint default 0, - autoprune smallint default 0, - recycle smallint default 0, - ActionOnPurge smallint default 0, - pooltype varchar(256) - check (pooltype in ('Backup','Copy','Cloned','Archive','Migration','Scratch')), - labeltype integer default 0, - labelformat varchar(256) not null, - enabled smallint default 1, - scratchpoolid integer default 0, - recyclepoolid integer default 0, - NextPoolId integer default 0, - MigrationHighBytes BIGINT DEFAULT 0, - MigrationLowBytes BIGINT DEFAULT 0, - MigrationTime BIGINT DEFAULT 0, - primary key (poolid) + PoolId INTEGER NOT NULL DEFAULT pool_Seq.nextval, + Name VARBYTE(128) NOT NULL, + NumVols INTEGER DEFAULT 0, + MaxVols INTEGER DEFAULT 0, + UseOnce SMALLINT DEFAULT 0, + UseCatalog SMALLINT DEFAULT 0, + AcceptAnyVolume SMALLINT DEFAULT 0, + VolRetention BIGINT DEFAULT 0, + VolUseDuration BIGINT DEFAULT 0, + MaxVolJobs INTEGER DEFAULT 0, + MaxVolFiles INTEGER DEFAULT 0, + MaxVolBytes BIGINT DEFAULT 0, + AutoPrune SMALLINT DEFAULT 0, + Recycle SMALLINT DEFAULT 0, + ActionOnPurge SMALLINT DEFAULT 0, + PoolType VARBYTE(32) + CHECK (pooltype in ('Backup','Copy','Cloned','Archive','Migration','Scratch')), + LabelType INTEGER DEFAULT 0, + LabelFormat VARBYTE(128) NOT NULL, + Enabled SMALLINT DEFAULT 1, + ScratchPoolId INTEGER DEFAULT 0, + RecyclePoolId INTEGER DEFAULT 0, + NextPoolId INTEGER DEFAULT 0, + MigrationHighBytes BIGINT DEFAULT 0, + MigrationLowBytes BIGINT DEFAULT 0, + MigrationTime BIGINT DEFAULT 0, + PRIMARY KEY (poolid) ); -CREATE INDEX pool_name_idx on pool (name); +CREATE INDEX pool_name_idx ON Pool (name); -CREATE SEQUENCE client_seq; -CREATE TABLE client +CREATE SEQUENCE Client_Seq; +CREATE TABLE Client ( - clientid integer not null default client_seq.nextval, - name varchar(256) not null, - uname varchar(256) not null, - autoprune smallint default 0, - fileretention bigint default 0, - jobretention bigint default 0, - primary key (clientid) + ClientId INTEGER NOT NULL DEFAULT Client_Seq.nextval, + Name VARBYTE(128) NOT NULL, + Uname VARBYTE(256) NOT NULL, + AutoPrune SMALLINT DEFAULT 0, + FileRetention BIGINT DEFAULT 0, + JobRetention BIGINT DEFAULT 0, + PRIMARY KEY (clientid) ); -create unique index client_name_idx on client (name); +CREATE UNIQUE INDEX client_name_idx ON Client (Name); -CREATE SEQUENCE Log_seq; +CREATE SEQUENCE Log_Seq; CREATE TABLE Log ( - LogId integer not null default Log_seq.nextval, - JobId integer not null, - Time timestamp without time zone, - LogText varchar(256) not null, - primary key (LogId) + LogId INTEGER NOT NULL DEFAULT Log_Seq.nextval, + JobId INTEGER NOT NULL, + Time TIMESTAMP WITHOUT TIME ZONE, + LogText VARBYTE(4096) NOT NULL, + PRIMARY KEY (LogId) ); -create index log_name_idx on Log (JobId); +CREATE INDEX log_name_idx ON Log (JobId); -CREATE SEQUENCE LocationLog_seq; +CREATE SEQUENCE LocationLog_Seq; CREATE TABLE LocationLog ( - LocLogId INTEGER NOT NULL DEFAULT LocationLog_seq.nextval, - Date timestamp without time zone, - Comment varchar(256) NOT NULL, - MediaId INTEGER DEFAULT 0, - LocationId INTEGER DEFAULT 0, - newvolstatus varchar(256) not null - check (newvolstatus in ('Full','Archive','Append', + LocLogId INTEGER NOT NULL DEFAULT LocationLog_Seq.nextval, + Date TIMESTAMP WITHOUT TIME ZONE, + Comment VARBYTE(4096) NOT NULL, + MediaId INTEGER DEFAULT 0, + LocationId INTEGER DEFAULT 0, + NewVolStatus VARBYTE(32) NOT NULL + CHECK (newvolstatus in ('Full','Archive','Append', 'Recycle','Purged','Read-Only','Disabled', 'Error','Busy','Used','Cleaning','Scratch')), - newenabled smallint, + NewEnabled SMALLINT, PRIMARY KEY(LocLogId) ); -CREATE TABLE counters +CREATE TABLE Counters ( - counter varchar(256) not null, - minvalue integer default 0, - maxvalue integer default 0, - currentvalue integer default 0, - wrapcounter varchar(256) not null, - primary key (counter) + Counter VARBYTE(128) NOT NULL, + MinValue INTEGER DEFAULT 0, + MaxValue INTEGER DEFAULT 0, + CurrentValue INTEGER DEFAULT 0, + WrapCounter VARBYTE(128) NOT NULL, + PRIMARY KEY (counter) ); -CREATE SEQUENCE basefiles_seq; -CREATE TABLE basefiles +CREATE SEQUENCE BaseFiles_Seq; +CREATE TABLE BaseFiles ( - baseid integer not null default basefiles_seq.nextval, - jobid integer not null, - fileid bigint not null, - fileindex integer , - basejobid integer , - primary key (baseid) + BaseId INTEGER NOT NULL DEFAULT BaseFiles_Seq.nextval, + JobId INTEGER NOT NULL, + FileId BIGINT NOT NULL, + FileIndex INTEGER, + BaseJobid INTEGER, + PRIMARY KEY (BaseId) ); -CREATE INDEX basefiles_jobid_idx ON BaseFiles ( JobId ); +CREATE INDEX basefiles_jobid_idx ON BaseFiles (JobId); CREATE TABLE unsavedfiles ( - UnsavedId integer not null, - jobid integer not null, - pathid integer not null, - filenameid integer not null, - primary key (UnsavedId) + UnsavedId INTEGER NOT NULL, + JobId INTEGER NOT NULL, + PathId INTEGER NOT NULL, + FilenameId INTEGER NOT NULL, + PRIMARY KEY (UnsavedId) ); CREATE TABLE CDImages ( - MediaId integer not null, - LastBurn timestamp without time zone not null, - primary key (MediaId) + MediaId INTEGER NOT NULL, + LastBurn TIMESTAMP WITHOUT TIME ZONE NOT NULL, + PRIMARY KEY (MediaId) ); - CREATE TABLE PathHierarchy ( - PathId integer NOT NULL, - PPathId integer NOT NULL, - CONSTRAINT pathhierarchy_pkey PRIMARY KEY (PathId) + PathId INTEGER NOT NULL, + PPathId INTEGER NOT NULL, + CONSTRAINT pathhierarchy_pkey PRIMARY KEY (PathId) ); -CREATE INDEX pathhierarchy_ppathid - ON PathHierarchy (PPathId); +CREATE INDEX pathhierarchy_ppathid ON PathHierarchy (PPathId); CREATE TABLE PathVisibility ( - PathId integer NOT NULL, - JobId integer NOT NULL, - Size int8 DEFAULT 0, - Files int4 DEFAULT 0, - CONSTRAINT pathvisibility_pkey PRIMARY KEY (JobId, PathId) + PathId INTEGER NOT NULL, + JobId INTEGER NOT NULL, + Size BIGINT DEFAULT 0, + Files INTEGER DEFAULT 0, + CONSTRAINT pathvisibility_pkey PRIMARY KEY (JobId, PathId) ); -CREATE INDEX pathvisibility_jobid - ON PathVisibility (JobId); +CREATE INDEX pathvisibility_jobid ON PathVisibility (JobId); CREATE TABLE version ( - versionid integer not null + versionid INTEGER NOT NULL ); CREATE TABLE Status ( - JobStatus CHAR(1) NOT NULL, - JobStatusLong varchar(256), - Severity int, + JobStatus CHAR(1) NOT NULL, + JobStatusLong VARBYTE(128), + Severity INTEGER, PRIMARY KEY (JobStatus) - ); +); \g -- 2.39.5