From 81b3b0d20ea4d1cd526a1d673d98bd5ea10e8ea4 Mon Sep 17 00:00:00 2001 From: Kern Sibbald Date: Tue, 28 Apr 2009 06:58:27 +0000 Subject: [PATCH] Update Win32/64 table creation to have new DB version 11 format git-svn-id: https://bacula.svn.sourceforge.net/svnroot/bacula/trunk@8757 91ce42f0-d328-0410-95d8-f526ca767f89 --- bacula/src/win32/cats/make_mysql_tables.sql | 684 ++++++++-------- .../src/win32/cats/make_postgresql_tables.sql | 740 +++++++++--------- bacula/src/win32/cats/make_sqlite3_tables.sql | 59 +- bacula/technotes | 1 + 4 files changed, 786 insertions(+), 698 deletions(-) diff --git a/bacula/src/win32/cats/make_mysql_tables.sql b/bacula/src/win32/cats/make_mysql_tables.sql index 4f6833f1fe..86a3c8b484 100644 --- a/bacula/src/win32/cats/make_mysql_tables.sql +++ b/bacula/src/win32/cats/make_mysql_tables.sql @@ -1,327 +1,357 @@ -USE bacula; --- --- Note, we use BLOB rather than TEXT because in MySQL, --- BLOBs are identical to TEXT except that BLOB is case --- sensitive in sorts, which is what we want, and TEXT --- is case insensitive. --- -CREATE TABLE Filename ( - FilenameId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, - Name BLOB NOT NULL, - PRIMARY KEY(FilenameId), - INDEX (Name(255)) - ); - -CREATE TABLE Path ( - PathId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, - Path BLOB NOT NULL, - PRIMARY KEY(PathId), - INDEX (Path(255)) - ); - - -CREATE TABLE File ( - FileId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, - FileIndex INTEGER UNSIGNED DEFAULT 0, - JobId INTEGER UNSIGNED NOT NULL REFERENCES Job, - PathId INTEGER UNSIGNED NOT NULL REFERENCES Path, - FilenameId INTEGER UNSIGNED NOT NULL REFERENCES Filename, - MarkId INTEGER UNSIGNED DEFAULT 0, - LStat TINYBLOB NOT NULL, - MD5 TINYBLOB, - PRIMARY KEY(FileId), - INDEX (JobId), - INDEX (JobId, PathId, FilenameId) - ); - -# -# Possibly add one or more of the following indexes -# to the above File table if your Verifies are -# too slow. -# -# INDEX (PathId), -# INDEX (FilenameId), -# INDEX (FilenameId, PathId) -# INDEX (JobId), -# - -CREATE TABLE MediaType ( - MediaTypeId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, - MediaType TINYBLOB NOT NULL, - ReadOnly TINYINT DEFAULT 0, - PRIMARY KEY(MediaTypeId) - ); - -CREATE TABLE Storage ( - StorageId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, - Name TINYBLOB NOT NULL, - AutoChanger TINYINT DEFAULT 0, - PRIMARY KEY(StorageId) - ); - -CREATE TABLE Device ( - DeviceId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, - Name TINYBLOB NOT NULL, - MediaTypeId INTEGER UNSIGNED DEFAULT 0 REFERENCES MediaType, - StorageId INTEGER UNSIGNED DEFAULT 0 REFERENCES Storage, - DevMounts INTEGER UNSIGNED DEFAULT 0, - DevReadBytes BIGINT UNSIGNED DEFAULT 0, - DevWriteBytes BIGINT UNSIGNED DEFAULT 0, - DevReadBytesSinceCleaning BIGINT UNSIGNED DEFAULT 0, - DevWriteBytesSinceCleaning BIGINT UNSIGNED DEFAULT 0, - DevReadTime BIGINT UNSIGNED DEFAULT 0, - DevWriteTime BIGINT UNSIGNED DEFAULT 0, - DevReadTimeSinceCleaning BIGINT UNSIGNED DEFAULT 0, - DevWriteTimeSinceCleaning BIGINT UNSIGNED DEFAULT 0, - CleaningDate DATETIME DEFAULT 0, - CleaningPeriod BIGINT UNSIGNED DEFAULT 0, - PRIMARY KEY(DeviceId) - ); - - -CREATE TABLE Job ( - JobId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, - Job TINYBLOB NOT NULL, - Name TINYBLOB NOT NULL, - Type BINARY(1) NOT NULL, - Level BINARY(1) NOT NULL, - ClientId INTEGER DEFAULT 0 REFERENCES Client, - JobStatus BINARY(1) NOT NULL, - SchedTime DATETIME DEFAULT 0, - StartTime DATETIME DEFAULT 0, - EndTime DATETIME DEFAULT 0, - RealEndTime DATETIME DEFAULT 0, - JobTDate BIGINT UNSIGNED DEFAULT 0, - VolSessionId INTEGER UNSIGNED DEFAULT 0, - VolSessionTime INTEGER UNSIGNED DEFAULT 0, - JobFiles INTEGER UNSIGNED DEFAULT 0, - JobBytes BIGINT UNSIGNED DEFAULT 0, - JobErrors INTEGER UNSIGNED DEFAULT 0, - JobMissingFiles INTEGER UNSIGNED DEFAULT 0, - PoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool, - FileSetId INTEGER UNSIGNED DEFAULT 0 REFERENCES FileSet, - PriorJobId INTEGER UNSIGNED DEFAULT 0 REFERENCES Job, - PurgedFiles TINYINT DEFAULT 0, - HasBase TINYINT DEFAULT 0, - PRIMARY KEY(JobId), - INDEX (Name(128)) - ); - - -CREATE TABLE Location ( - LocationId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, - Location TINYBLOB NOT NULL, - Cost INTEGER DEFAULT 0, - Enabled TINYINT, - PRIMARY KEY(LocationId) - ); - -CREATE TABLE LocationLog ( - LocLogId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, - Date DATETIME DEFAULT 0, - Comment BLOB NOT NULL, - MediaId INTEGER UNSIGNED DEFAULT 0 REFERENCES Media, - LocationId INTEGER UNSIGNED DEFAULT 0 REFERENCES Location, - NewVolStatus ENUM('Full', 'Archive', 'Append', 'Recycle', 'Purged', - 'Read-Only', 'Disabled', 'Error', 'Busy', 'Used', 'Cleaning') NOT NULL, - NewEnabled TINYINT, - PRIMARY KEY(LocLogId) -); - - -# -CREATE TABLE FileSet ( - FileSetId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, - FileSet TINYBLOB NOT NULL, - MD5 TINYBLOB, - CreateTime DATETIME DEFAULT 0, - PRIMARY KEY(FileSetId) - ); - -CREATE TABLE JobMedia ( - JobMediaId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, - JobId INTEGER UNSIGNED NOT NULL REFERENCES Job, - MediaId INTEGER UNSIGNED NOT NULL REFERENCES Media, - FirstIndex INTEGER UNSIGNED DEFAULT 0, - LastIndex INTEGER UNSIGNED DEFAULT 0, - StartFile INTEGER UNSIGNED DEFAULT 0, - EndFile INTEGER UNSIGNED DEFAULT 0, - StartBlock INTEGER UNSIGNED DEFAULT 0, - EndBlock INTEGER UNSIGNED DEFAULT 0, - VolIndex INTEGER UNSIGNED DEFAULT 0, - Copy INTEGER UNSIGNED DEFAULT 0, - Stripe INTEGER UNSIGNED DEFAULT 0, - PRIMARY KEY(JobMediaId), - INDEX (JobId, MediaId) - ); - - -CREATE TABLE Media ( - MediaId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, - VolumeName TINYBLOB NOT NULL, - Slot INTEGER DEFAULT 0, - PoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool, - MediaType TINYBLOB NOT NULL, - MediaTypeId INTEGER UNSIGNED DEFAULT 0 REFERENCES MediaType, - LabelType TINYINT DEFAULT 0, - FirstWritten DATETIME DEFAULT 0, - LastWritten DATETIME DEFAULT 0, - LabelDate DATETIME DEFAULT 0, - VolJobs INTEGER UNSIGNED DEFAULT 0, - VolFiles INTEGER UNSIGNED DEFAULT 0, - VolBlocks INTEGER UNSIGNED DEFAULT 0, - VolMounts INTEGER UNSIGNED DEFAULT 0, - VolBytes BIGINT UNSIGNED DEFAULT 0, - VolParts INTEGER UNSIGNED DEFAULT 0, - VolErrors INTEGER UNSIGNED DEFAULT 0, - VolWrites INTEGER UNSIGNED DEFAULT 0, - VolCapacityBytes BIGINT UNSIGNED DEFAULT 0, - VolStatus ENUM('Full', 'Archive', 'Append', 'Recycle', 'Purged', - 'Read-Only', 'Disabled', 'Error', 'Busy', 'Used', 'Cleaning') NOT NULL, - Enabled TINYINT DEFAULT 1, - Recycle TINYINT DEFAULT 0, - VolRetention BIGINT UNSIGNED DEFAULT 0, - VolUseDuration BIGINT UNSIGNED DEFAULT 0, - MaxVolJobs INTEGER UNSIGNED DEFAULT 0, - MaxVolFiles INTEGER UNSIGNED DEFAULT 0, - MaxVolBytes BIGINT UNSIGNED DEFAULT 0, - InChanger TINYINT DEFAULT 0, - StorageId INTEGER UNSIGNED DEFAULT 0 REFERENCES Storage, - DeviceId INTEGER UNSIGNED DEFAULT 0 REFERENCES Device, - MediaAddressing TINYINT DEFAULT 0, - VolReadTime BIGINT UNSIGNED DEFAULT 0, - VolWriteTime BIGINT UNSIGNED DEFAULT 0, - EndFile INTEGER UNSIGNED DEFAULT 0, - EndBlock INTEGER UNSIGNED DEFAULT 0, - LocationId INTEGER UNSIGNED DEFAULT 0 REFERENCES Location, - RecycleCount INTEGER UNSIGNED DEFAULT 0, - InitialWrite DATETIME DEFAULT 0, - ScratchPoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool, - RecyclePoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool, - Comment BLOB, - PRIMARY KEY(MediaId), - INDEX (PoolId) - ); - -CREATE INDEX inx8 ON Media (PoolId); - - - -CREATE TABLE Pool ( - PoolId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, - Name TINYBLOB NOT NULL, - NumVols INTEGER UNSIGNED DEFAULT 0, - MaxVols INTEGER UNSIGNED DEFAULT 0, - UseOnce TINYINT DEFAULT 0, - UseCatalog TINYINT DEFAULT 0, - AcceptAnyVolume TINYINT DEFAULT 0, - VolRetention BIGINT UNSIGNED DEFAULT 0, - VolUseDuration BIGINT UNSIGNED DEFAULT 0, - MaxVolJobs INTEGER UNSIGNED DEFAULT 0, - MaxVolFiles INTEGER UNSIGNED DEFAULT 0, - MaxVolBytes BIGINT UNSIGNED DEFAULT 0, - AutoPrune TINYINT DEFAULT 0, - Recycle TINYINT DEFAULT 0, - PoolType ENUM('Backup', 'Copy', 'Cloned', 'Archive', 'Migration', 'Scratch') NOT NULL, - LabelType TINYINT DEFAULT 0, - LabelFormat TINYBLOB, - Enabled TINYINT DEFAULT 1, - ScratchPoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool, - RecyclePoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool, - NextPoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool, - MigrationHighBytes BIGINT UNSIGNED DEFAULT 0, - MigrationLowBytes BIGINT UNSIGNED DEFAULT 0, - MigrationTime BIGINT UNSIGNED DEFAULT 0, - UNIQUE (Name(128)), - PRIMARY KEY (PoolId) - ); - - -CREATE TABLE Client ( - ClientId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, - Name TINYBLOB NOT NULL, - Uname TINYBLOB NOT NULL, /* full uname -a of client */ - AutoPrune TINYINT DEFAULT 0, - FileRetention BIGINT UNSIGNED DEFAULT 0, - JobRetention BIGINT UNSIGNED DEFAULT 0, - UNIQUE (Name(128)), - PRIMARY KEY(ClientId) - ); - -CREATE TABLE Log ( - LogId INTEGER UNSIGNED AUTO_INCREMENT, - JobId INTEGER UNSIGNED DEFAULT 0 REFERENCES Job, - Time DATETIME DEFAULT 0, - LogText BLOB NOT NULL, - PRIMARY KEY(LogId), - INDEX (JobId) - ); - - -CREATE TABLE BaseFiles ( - BaseId INTEGER UNSIGNED AUTO_INCREMENT, - BaseJobId INTEGER UNSIGNED NOT NULL REFERENCES Job, - JobId INTEGER UNSIGNED NOT NULL REFERENCES Job, - FileId INTEGER UNSIGNED NOT NULL REFERENCES File, - FileIndex INTEGER UNSIGNED, - PRIMARY KEY(BaseId) - ); - -CREATE TABLE UnsavedFiles ( - UnsavedId INTEGER UNSIGNED AUTO_INCREMENT, - JobId INTEGER UNSIGNED NOT NULL REFERENCES Job, - PathId INTEGER UNSIGNED NOT NULL REFERENCES Path, - FilenameId INTEGER UNSIGNED NOT NULL REFERENCES Filename, - PRIMARY KEY (UnsavedId) - ); - - - -CREATE TABLE Counters ( - Counter TINYBLOB NOT NULL, - MinValue INTEGER DEFAULT 0, - MaxValue INTEGER DEFAULT 0, - CurrentValue INTEGER DEFAULT 0, - WrapCounter TINYBLOB NOT NULL, - PRIMARY KEY (Counter(128)) - ); - -CREATE TABLE CDImages ( - MediaId INTEGER UNSIGNED NOT NULL, - LastBurn DATETIME NOT NULL, - PRIMARY KEY (MediaId) - ); - -CREATE TABLE Status ( - JobStatus CHAR(1) BINARY NOT NULL, - JobStatusLong BLOB, - PRIMARY KEY (JobStatus) - ); - -INSERT INTO Status (JobStatus,JobStatusLong) VALUES - ('C', 'Created, not yet running'), - ('R', 'Running'), - ('B', 'Blocked'), - ('T', 'Completed successfully'), - ('E', 'Terminated with errors'), - ('e', 'Non-fatal error'), - ('f', 'Fatal error'), - ('D', 'Verify found differences'), - ('A', 'Canceled by user'), - ('F', 'Waiting for Client'), - ('S', 'Waiting for Storage daemon'), - ('m', 'Waiting for new media'), - ('M', 'Waiting for media mount'), - ('s', 'Waiting for storage resource'), - ('j', 'Waiting for job resource'), - ('c', 'Waiting for client resource'), - ('d', 'Waiting on maximum jobs'), - ('t', 'Waiting on start time'), - ('p', 'Waiting on higher priority jobs'); - -CREATE TABLE Version ( - VersionId INTEGER UNSIGNED NOT NULL - ); - --- Initialize Version -INSERT INTO Version (VersionId) VALUES (10); +USE ${db_name}; +-- +-- Note, we use BLOB rather than TEXT because in MySQL, +-- BLOBs are identical to TEXT except that BLOB is case +-- sensitive in sorts, which is what we want, and TEXT +-- is case insensitive. +-- +CREATE TABLE Filename ( + FilenameId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, + Name BLOB NOT NULL, + PRIMARY KEY(FilenameId), + INDEX (Name(255)) + ); + +CREATE TABLE Path ( + PathId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, + Path BLOB NOT NULL, + PRIMARY KEY(PathId), + INDEX (Path(255)) + ); + + +CREATE TABLE File ( + FileId BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, + FileIndex INTEGER UNSIGNED DEFAULT 0, + JobId INTEGER UNSIGNED NOT NULL REFERENCES Job, + PathId INTEGER UNSIGNED NOT NULL REFERENCES Path, + FilenameId INTEGER UNSIGNED NOT NULL REFERENCES Filename, + MarkId INTEGER UNSIGNED DEFAULT 0, + LStat TINYBLOB NOT NULL, + MD5 TINYBLOB, + PRIMARY KEY(FileId), + INDEX (JobId), + INDEX (JobId, PathId, FilenameId) + ); + +-- +-- Possibly add one or more of the following indexes +-- to the above File table if your Verifies are +-- too slow. +-- +-- INDEX (PathId), +-- INDEX (FilenameId), +-- INDEX (FilenameId, PathId) +-- INDEX (JobId), +-- + +CREATE TABLE MediaType ( + MediaTypeId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, + MediaType TINYBLOB NOT NULL, + ReadOnly TINYINT DEFAULT 0, + PRIMARY KEY(MediaTypeId) + ); + +CREATE TABLE Storage ( + StorageId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, + Name TINYBLOB NOT NULL, + AutoChanger TINYINT DEFAULT 0, + PRIMARY KEY(StorageId) + ); + +CREATE TABLE Device ( + DeviceId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, + Name TINYBLOB NOT NULL, + MediaTypeId INTEGER UNSIGNED DEFAULT 0 REFERENCES MediaType, + StorageId INTEGER UNSIGNED DEFAULT 0 REFERENCES Storage, + DevMounts INTEGER UNSIGNED DEFAULT 0, + DevReadBytes BIGINT UNSIGNED DEFAULT 0, + DevWriteBytes BIGINT UNSIGNED DEFAULT 0, + DevReadBytesSinceCleaning BIGINT UNSIGNED DEFAULT 0, + DevWriteBytesSinceCleaning BIGINT UNSIGNED DEFAULT 0, + DevReadTime BIGINT UNSIGNED DEFAULT 0, + DevWriteTime BIGINT UNSIGNED DEFAULT 0, + DevReadTimeSinceCleaning BIGINT UNSIGNED DEFAULT 0, + DevWriteTimeSinceCleaning BIGINT UNSIGNED DEFAULT 0, + CleaningDate DATETIME DEFAULT 0, + CleaningPeriod BIGINT UNSIGNED DEFAULT 0, + PRIMARY KEY(DeviceId) + ); + + +CREATE TABLE Job ( + JobId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, + Job TINYBLOB NOT NULL, + Name TINYBLOB NOT NULL, + Type BINARY(1) NOT NULL, + Level BINARY(1) NOT NULL, + ClientId INTEGER DEFAULT 0 REFERENCES Client, + JobStatus BINARY(1) NOT NULL, + SchedTime DATETIME DEFAULT 0, + StartTime DATETIME DEFAULT 0, + EndTime DATETIME DEFAULT 0, + RealEndTime DATETIME DEFAULT 0, + JobTDate BIGINT UNSIGNED DEFAULT 0, + VolSessionId INTEGER UNSIGNED DEFAULT 0, + VolSessionTime INTEGER UNSIGNED DEFAULT 0, + JobFiles INTEGER UNSIGNED DEFAULT 0, + JobBytes BIGINT UNSIGNED DEFAULT 0, + ReadBytes BIGINT UNSIGNED DEFAULT 0, + JobErrors INTEGER UNSIGNED DEFAULT 0, + JobMissingFiles INTEGER UNSIGNED DEFAULT 0, + PoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool, + FileSetId INTEGER UNSIGNED DEFAULT 0 REFERENCES FileSet, + PriorJobId INTEGER UNSIGNED DEFAULT 0 REFERENCES Job, + PurgedFiles TINYINT DEFAULT 0, + HasBase TINYINT DEFAULT 0, + PRIMARY KEY(JobId), + INDEX (Name(128)) + ); + +-- Create a table like Job for long term statistics +CREATE TABLE JobHisto ( + JobId INTEGER UNSIGNED NOT NULL, + Job TINYBLOB NOT NULL, + Name TINYBLOB NOT NULL, + Type BINARY(1) NOT NULL, + Level BINARY(1) NOT NULL, + ClientId INTEGER DEFAULT 0, + JobStatus BINARY(1) NOT NULL, + SchedTime DATETIME DEFAULT 0, + StartTime DATETIME DEFAULT 0, + EndTime DATETIME DEFAULT 0, + RealEndTime DATETIME DEFAULT 0, + JobTDate BIGINT UNSIGNED DEFAULT 0, + VolSessionId INTEGER UNSIGNED DEFAULT 0, + VolSessionTime INTEGER UNSIGNED DEFAULT 0, + JobFiles INTEGER UNSIGNED DEFAULT 0, + JobBytes BIGINT UNSIGNED DEFAULT 0, + ReadBytes BIGINT UNSIGNED DEFAULT 0, + JobErrors INTEGER UNSIGNED DEFAULT 0, + JobMissingFiles INTEGER UNSIGNED DEFAULT 0, + PoolId INTEGER UNSIGNED DEFAULT 0, + FileSetId INTEGER UNSIGNED DEFAULT 0, + PriorJobId INTEGER UNSIGNED DEFAULT 0, + PurgedFiles TINYINT DEFAULT 0, + HasBase TINYINT DEFAULT 0, + INDEX (StartTime) + ); + +CREATE TABLE Location ( + LocationId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, + Location TINYBLOB NOT NULL, + Cost INTEGER DEFAULT 0, + Enabled TINYINT, + PRIMARY KEY(LocationId) + ); + +CREATE TABLE LocationLog ( + LocLogId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, + Date DATETIME DEFAULT 0, + Comment BLOB NOT NULL, + MediaId INTEGER UNSIGNED DEFAULT 0 REFERENCES Media, + LocationId INTEGER UNSIGNED DEFAULT 0 REFERENCES Location, + NewVolStatus ENUM('Full', 'Archive', 'Append', 'Recycle', 'Purged', + 'Read-Only', 'Disabled', 'Error', 'Busy', 'Used', 'Cleaning') NOT NULL, + NewEnabled TINYINT, + PRIMARY KEY(LocLogId) +); + + +CREATE TABLE FileSet ( + FileSetId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, + FileSet TINYBLOB NOT NULL, + MD5 TINYBLOB, + CreateTime DATETIME DEFAULT 0, + PRIMARY KEY(FileSetId) + ); + +CREATE TABLE JobMedia ( + JobMediaId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, + JobId INTEGER UNSIGNED NOT NULL REFERENCES Job, + MediaId INTEGER UNSIGNED NOT NULL REFERENCES Media, + FirstIndex INTEGER UNSIGNED DEFAULT 0, + LastIndex INTEGER UNSIGNED DEFAULT 0, + StartFile INTEGER UNSIGNED DEFAULT 0, + EndFile INTEGER UNSIGNED DEFAULT 0, + StartBlock INTEGER UNSIGNED DEFAULT 0, + EndBlock INTEGER UNSIGNED DEFAULT 0, + VolIndex INTEGER UNSIGNED DEFAULT 0, + Copy INTEGER UNSIGNED DEFAULT 0, + Stripe INTEGER UNSIGNED DEFAULT 0, + PRIMARY KEY(JobMediaId), + INDEX (JobId, MediaId) + ); + + +CREATE TABLE Media ( + MediaId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, + VolumeName TINYBLOB NOT NULL, + Slot INTEGER DEFAULT 0, + PoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool, + MediaType TINYBLOB NOT NULL, + MediaTypeId INTEGER UNSIGNED DEFAULT 0 REFERENCES MediaType, + LabelType TINYINT DEFAULT 0, + FirstWritten DATETIME DEFAULT 0, + LastWritten DATETIME DEFAULT 0, + LabelDate DATETIME DEFAULT 0, + VolJobs INTEGER UNSIGNED DEFAULT 0, + VolFiles INTEGER UNSIGNED DEFAULT 0, + VolBlocks INTEGER UNSIGNED DEFAULT 0, + VolMounts INTEGER UNSIGNED DEFAULT 0, + VolBytes BIGINT UNSIGNED DEFAULT 0, + VolParts INTEGER UNSIGNED DEFAULT 0, + VolErrors INTEGER UNSIGNED DEFAULT 0, + VolWrites INTEGER UNSIGNED DEFAULT 0, + VolCapacityBytes BIGINT UNSIGNED DEFAULT 0, + VolStatus ENUM('Full', 'Archive', 'Append', 'Recycle', 'Purged', + 'Read-Only', 'Disabled', 'Error', 'Busy', 'Used', 'Cleaning') NOT NULL, + Enabled TINYINT DEFAULT 1, + Recycle TINYINT DEFAULT 0, + ActionOnPurge TINYINT DEFAULT 0, + VolRetention BIGINT UNSIGNED DEFAULT 0, + VolUseDuration BIGINT UNSIGNED DEFAULT 0, + MaxVolJobs INTEGER UNSIGNED DEFAULT 0, + MaxVolFiles INTEGER UNSIGNED DEFAULT 0, + MaxVolBytes BIGINT UNSIGNED DEFAULT 0, + InChanger TINYINT DEFAULT 0, + StorageId INTEGER UNSIGNED DEFAULT 0 REFERENCES Storage, + DeviceId INTEGER UNSIGNED DEFAULT 0 REFERENCES Device, + MediaAddressing TINYINT DEFAULT 0, + VolReadTime BIGINT UNSIGNED DEFAULT 0, + VolWriteTime BIGINT UNSIGNED DEFAULT 0, + EndFile INTEGER UNSIGNED DEFAULT 0, + EndBlock INTEGER UNSIGNED DEFAULT 0, + LocationId INTEGER UNSIGNED DEFAULT 0 REFERENCES Location, + RecycleCount INTEGER UNSIGNED DEFAULT 0, + InitialWrite DATETIME DEFAULT 0, + ScratchPoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool, + RecyclePoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool, + Comment BLOB, + PRIMARY KEY(MediaId), + UNIQUE (VolumeName(128)), + INDEX (PoolId) + ); + +CREATE TABLE Pool ( + PoolId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, + Name TINYBLOB NOT NULL, + NumVols INTEGER UNSIGNED DEFAULT 0, + MaxVols INTEGER UNSIGNED DEFAULT 0, + UseOnce TINYINT DEFAULT 0, + UseCatalog TINYINT DEFAULT 0, + AcceptAnyVolume TINYINT DEFAULT 0, + VolRetention BIGINT UNSIGNED DEFAULT 0, + VolUseDuration BIGINT UNSIGNED DEFAULT 0, + MaxVolJobs INTEGER UNSIGNED DEFAULT 0, + MaxVolFiles INTEGER UNSIGNED DEFAULT 0, + MaxVolBytes BIGINT UNSIGNED DEFAULT 0, + AutoPrune TINYINT DEFAULT 0, + Recycle TINYINT DEFAULT 0, + ActionOnPurge TINYINT DEFAULT 0, + PoolType ENUM('Backup', 'Copy', 'Cloned', 'Archive', 'Migration', 'Scratch') NOT NULL, + LabelType TINYINT DEFAULT 0, + LabelFormat TINYBLOB, + Enabled TINYINT DEFAULT 1, + ScratchPoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool, + RecyclePoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool, + NextPoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool, + MigrationHighBytes BIGINT UNSIGNED DEFAULT 0, + MigrationLowBytes BIGINT UNSIGNED DEFAULT 0, + MigrationTime BIGINT UNSIGNED DEFAULT 0, + UNIQUE (Name(128)), + PRIMARY KEY (PoolId) + ); + + +CREATE TABLE Client ( + ClientId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, + Name TINYBLOB NOT NULL, + Uname TINYBLOB NOT NULL, /* full uname -a of client */ + AutoPrune TINYINT DEFAULT 0, + FileRetention BIGINT UNSIGNED DEFAULT 0, + JobRetention BIGINT UNSIGNED DEFAULT 0, + UNIQUE (Name(128)), + PRIMARY KEY(ClientId) + ); + +CREATE TABLE Log ( + LogId INTEGER UNSIGNED AUTO_INCREMENT, + JobId INTEGER UNSIGNED DEFAULT 0 REFERENCES Job, + Time DATETIME DEFAULT 0, + LogText BLOB NOT NULL, + PRIMARY KEY(LogId), + INDEX (JobId) + ); + + +CREATE TABLE BaseFiles ( + BaseId INTEGER UNSIGNED AUTO_INCREMENT, + BaseJobId INTEGER UNSIGNED NOT NULL REFERENCES Job, + JobId INTEGER UNSIGNED NOT NULL REFERENCES Job, + FileId BIGINT UNSIGNED NOT NULL REFERENCES File, + FileIndex INTEGER UNSIGNED, + PRIMARY KEY(BaseId) + ); + +CREATE TABLE UnsavedFiles ( + UnsavedId INTEGER UNSIGNED AUTO_INCREMENT, + JobId INTEGER UNSIGNED NOT NULL REFERENCES Job, + PathId INTEGER UNSIGNED NOT NULL REFERENCES Path, + FilenameId INTEGER UNSIGNED NOT NULL REFERENCES Filename, + PRIMARY KEY (UnsavedId) + ); + + + +CREATE TABLE Counters ( + Counter TINYBLOB NOT NULL, + MinValue INTEGER DEFAULT 0, + MaxValue INTEGER DEFAULT 0, + CurrentValue INTEGER DEFAULT 0, + WrapCounter TINYBLOB NOT NULL, + PRIMARY KEY (Counter(128)) + ); + +CREATE TABLE CDImages ( + MediaId INTEGER UNSIGNED NOT NULL, + LastBurn DATETIME NOT NULL, + PRIMARY KEY (MediaId) + ); + +CREATE TABLE Status ( + JobStatus CHAR(1) BINARY NOT NULL, + JobStatusLong BLOB, + PRIMARY KEY (JobStatus) + ); + +INSERT INTO Status (JobStatus,JobStatusLong) VALUES + ('C', 'Created, not yet running'), + ('R', 'Running'), + ('B', 'Blocked'), + ('T', 'Completed successfully'), + ('E', 'Terminated with errors'), + ('e', 'Non-fatal error'), + ('f', 'Fatal error'), + ('D', 'Verify found differences'), + ('A', 'Canceled by user'), + ('F', 'Waiting for Client'), + ('S', 'Waiting for Storage daemon'), + ('m', 'Waiting for new media'), + ('M', 'Waiting for media mount'), + ('s', 'Waiting for storage resource'), + ('j', 'Waiting for job resource'), + ('c', 'Waiting for client resource'), + ('d', 'Waiting on maximum jobs'), + ('t', 'Waiting on start time'), + ('p', 'Waiting on higher priority jobs'), + ('i', 'Doing batch insert file records'), + ('a', 'SD despooling attributes'); + +CREATE TABLE Version ( + VersionId INTEGER UNSIGNED NOT NULL + ); + +-- Initialize Version +INSERT INTO Version (VersionId) VALUES (11); + diff --git a/bacula/src/win32/cats/make_postgresql_tables.sql b/bacula/src/win32/cats/make_postgresql_tables.sql index 3991926fe6..e7d0d86a0a 100644 --- a/bacula/src/win32/cats/make_postgresql_tables.sql +++ b/bacula/src/win32/cats/make_postgresql_tables.sql @@ -1,360 +1,380 @@ -CREATE TABLE filename -( - filenameid serial not null, - name text not null, - primary key (filenameid) -); - -CREATE INDEX filename_name_idx on filename (name); - -CREATE TABLE path -( - pathid serial not null, - path text not null, - primary key (pathid) -); - -CREATE INDEX path_name_idx on path (path); - -CREATE TABLE file -( - fileid serial not null, - 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 text not null, - md5 text not null, - primary key (fileid) -); - -CREATE INDEX file_jobid_idx on file (jobid); -CREATE INDEX file_fp_idx on file (filenameid, pathid); - --- --- 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); --- CREATE INDEX file_jpfid_idx on file (jobid, pathid, filenameid); - -CREATE TABLE job -( - jobid serial not null, - job text not null, - name text 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, - joberrors integer default 0, - jobmissingfiles integer default 0, - poolid integer default 0, - filesetid integer default 0, - purgedfiles smallint default 0, - hasbase smallint default 0, - priorjobid integer default 0, - primary key (jobid) -); - -CREATE INDEX job_name_idx on job (name); - -CREATE TABLE Location ( - LocationId serial not null, - Location text not null, - Cost integer default 0, - Enabled smallint, - primary key (LocationId) -); - - -CREATE TABLE fileset -( - filesetid serial not null, - fileset text not null, - md5 text not null, - createtime timestamp without time zone not null, - primary key (filesetid) -); - -CREATE INDEX fileset_name_idx on fileset (fileset); - -CREATE TABLE jobmedia -( - jobmediaid serial not null, - 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, - copy integer default 0, - primary key (jobmediaid) -); - -CREATE INDEX job_media_job_id_media_id_idx on jobmedia (jobid, mediaid); - -CREATE TABLE media -( - mediaid serial not null, - volumename text not null, - slot integer default 0, - poolid integer default 0, - mediatype text 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 text 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, - 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 integer 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 text, - primary key (mediaid) -); - -create unique index media_volumename_id on media (volumename); - - -CREATE TABLE MediaType ( - MediaTypeId SERIAL, - MediaType TEXT NOT NULL, - ReadOnly INTEGER DEFAULT 0, - PRIMARY KEY(MediaTypeId) - ); - -CREATE TABLE Storage ( - StorageId SERIAL, - Name TEXT NOT NULL, - AutoChanger INTEGER DEFAULT 0, - PRIMARY KEY(StorageId) - ); - -CREATE TABLE Device ( - DeviceId SERIAL, - Name TEXT 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 TABLE pool -( - poolid serial not null, - name text 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, - pooltype text - check (pooltype in ('Backup','Copy','Cloned','Archive','Migration','Scratch')), - labeltype integer default 0, - labelformat text 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 TABLE client -( - clientid serial not null, - name text not null, - uname text 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 TABLE Log -( - LogId serial not null, - JobId integer not null, - Time timestamp without time zone, - LogText text not null, - primary key (LogId) -); -create index log_name_idx on Log (JobId); - -CREATE TABLE LocationLog ( - LocLogId SERIAL NOT NULL, - Date timestamp without time zone, - Comment TEXT NOT NULL, - MediaId INTEGER DEFAULT 0, - LocationId INTEGER DEFAULT 0, - newvolstatus text not null - check (newvolstatus in ('Full','Archive','Append', - 'Recycle','Purged','Read-Only','Disabled', - 'Error','Busy','Used','Cleaning','Scratch')), - newenabled smallint, - PRIMARY KEY(LocLogId) -); - - - -CREATE TABLE counters -( - counter text not null, - minvalue integer default 0, - maxvalue integer default 0, - currentvalue integer default 0, - wrapcounter text not null, - primary key (counter) -); - - - -CREATE TABLE basefiles -( - baseid serial not null, - jobid integer not null, - fileid integer not null, - fileindex integer , - basejobid integer , - primary key (baseid) -); - -CREATE TABLE unsavedfiles -( - 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) -); - - -CREATE TABLE version -( - versionid integer not null -); - -CREATE TABLE Status ( - JobStatus CHAR(1) NOT NULL, - JobStatusLong TEXT, - PRIMARY KEY (JobStatus) - ); - -INSERT INTO Status (JobStatus,JobStatusLong) VALUES - ('C', 'Created, not yet running'); -INSERT INTO Status (JobStatus,JobStatusLong) VALUES - ('R', 'Running'); -INSERT INTO Status (JobStatus,JobStatusLong) VALUES - ('B', 'Blocked'); -INSERT INTO Status (JobStatus,JobStatusLong) VALUES - ('T', 'Completed successfully'); -INSERT INTO Status (JobStatus,JobStatusLong) VALUES - ('E', 'Terminated with errors'); -INSERT INTO Status (JobStatus,JobStatusLong) VALUES - ('e', 'Non-fatal error'); -INSERT INTO Status (JobStatus,JobStatusLong) VALUES - ('f', 'Fatal error'); -INSERT INTO Status (JobStatus,JobStatusLong) VALUES - ('D', 'Verify found differences'); -INSERT INTO Status (JobStatus,JobStatusLong) VALUES - ('A', 'Canceled by user'); -INSERT INTO Status (JobStatus,JobStatusLong) VALUES - ('F', 'Waiting for Client'); -INSERT INTO Status (JobStatus,JobStatusLong) VALUES - ('S', 'Waiting for Storage daemon'); -INSERT INTO Status (JobStatus,JobStatusLong) VALUES - ('m', 'Waiting for new media'); -INSERT INTO Status (JobStatus,JobStatusLong) VALUES - ('M', 'Waiting for media mount'); -INSERT INTO Status (JobStatus,JobStatusLong) VALUES - ('s', 'Waiting for storage resource'); -INSERT INTO Status (JobStatus,JobStatusLong) VALUES - ('j', 'Waiting for job resource'); -INSERT INTO Status (JobStatus,JobStatusLong) VALUES - ('c', 'Waiting for client resource'); -INSERT INTO Status (JobStatus,JobStatusLong) VALUES - ('d', 'Waiting on maximum jobs'); -INSERT INTO Status (JobStatus,JobStatusLong) VALUES - ('t', 'Waiting on start time'); -INSERT INTO Status (JobStatus,JobStatusLong) VALUES - ('p', 'Waiting on higher priority jobs'); - - -INSERT INTO Version (VersionId) VALUES (10); - --- Make sure we have appropriate permissions + +CREATE TABLE filename +( + filenameid serial not null, + name text not null, + primary key (filenameid) +); + +ALTER TABLE filename ALTER COLUMN name SET STATISTICS 1000; +CREATE UNIQUE INDEX filename_name_idx on filename (name); + +CREATE TABLE path +( + pathid serial not null, + path text not null, + primary key (pathid) +); + +ALTER TABLE path ALTER COLUMN path SET STATISTICS 1000; +CREATE UNIQUE INDEX path_name_idx on path (path); + +CREATE TABLE file +( + fileid bigserial not null, + 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 text not null, + md5 text not null, + primary key (fileid) +); + +CREATE INDEX file_jobid_idx on file (jobid); +CREATE INDEX file_fp_idx on file (filenameid, pathid); + +-- +-- 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); +-- CREATE INDEX file_jpfid_idx on file (jobid, pathid, filenameid); + +CREATE TABLE job +( + jobid serial not null, + job text not null, + name text 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, + purgedfiles smallint default 0, + hasbase smallint default 0, + priorjobid integer default 0, + primary key (jobid) +); + +CREATE INDEX job_name_idx on job (name); + +-- Create a table like Job for long term statistics +CREATE TABLE JobHisto (LIKE Job); +CREATE INDEX jobhisto_idx ON jobhisto ( starttime ); + + +CREATE TABLE Location ( + LocationId serial not null, + Location text not null, + Cost integer default 0, + Enabled smallint, + primary key (LocationId) +); + + +CREATE TABLE fileset +( + filesetid serial not null, + fileset text not null, + md5 text not null, + createtime timestamp without time zone not null, + primary key (filesetid) +); + +CREATE INDEX fileset_name_idx on fileset (fileset); + +CREATE TABLE jobmedia +( + jobmediaid serial not null, + 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, + copy integer default 0, + primary key (jobmediaid) +); + +CREATE INDEX job_media_job_id_media_id_idx on jobmedia (jobid, mediaid); + +CREATE TABLE media +( + mediaid serial not null, + volumename text not null, + slot integer default 0, + poolid integer default 0, + mediatype text 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 text 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 integer 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 text, + primary key (mediaid) +); + +create unique index media_volumename_id on media (volumename); + + +CREATE TABLE MediaType ( + MediaTypeId SERIAL, + MediaType TEXT NOT NULL, + ReadOnly INTEGER DEFAULT 0, + PRIMARY KEY(MediaTypeId) + ); + +CREATE TABLE Storage ( + StorageId SERIAL, + Name TEXT NOT NULL, + AutoChanger INTEGER DEFAULT 0, + PRIMARY KEY(StorageId) + ); + +CREATE TABLE Device ( + DeviceId SERIAL, + Name TEXT 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 TABLE pool +( + poolid serial not null, + name text 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 text + check (pooltype in ('Backup','Copy','Cloned','Archive','Migration','Scratch')), + labeltype integer default 0, + labelformat text 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 TABLE client +( + clientid serial not null, + name text not null, + uname text 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 TABLE Log +( + LogId serial not null, + JobId integer not null, + Time timestamp without time zone, + LogText text not null, + primary key (LogId) +); +create index log_name_idx on Log (JobId); + +CREATE TABLE LocationLog ( + LocLogId SERIAL NOT NULL, + Date timestamp without time zone, + Comment TEXT NOT NULL, + MediaId INTEGER DEFAULT 0, + LocationId INTEGER DEFAULT 0, + newvolstatus text not null + check (newvolstatus in ('Full','Archive','Append', + 'Recycle','Purged','Read-Only','Disabled', + 'Error','Busy','Used','Cleaning','Scratch')), + newenabled smallint, + PRIMARY KEY(LocLogId) +); + + + +CREATE TABLE counters +( + counter text not null, + minvalue integer default 0, + maxvalue integer default 0, + currentvalue integer default 0, + wrapcounter text not null, + primary key (counter) +); + + + +CREATE TABLE basefiles +( + baseid serial not null, + jobid integer not null, + fileid bigint not null, + fileindex integer , + basejobid integer , + primary key (baseid) +); + +CREATE TABLE unsavedfiles +( + 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) +); + + +CREATE TABLE version +( + versionid integer not null +); + +CREATE TABLE Status ( + JobStatus CHAR(1) NOT NULL, + JobStatusLong TEXT, + PRIMARY KEY (JobStatus) + ); + +INSERT INTO Status (JobStatus,JobStatusLong) VALUES + ('C', 'Created, not yet running'); +INSERT INTO Status (JobStatus,JobStatusLong) VALUES + ('R', 'Running'); +INSERT INTO Status (JobStatus,JobStatusLong) VALUES + ('B', 'Blocked'); +INSERT INTO Status (JobStatus,JobStatusLong) VALUES + ('T', 'Completed successfully'); +INSERT INTO Status (JobStatus,JobStatusLong) VALUES + ('E', 'Terminated with errors'); +INSERT INTO Status (JobStatus,JobStatusLong) VALUES + ('e', 'Non-fatal error'); +INSERT INTO Status (JobStatus,JobStatusLong) VALUES + ('f', 'Fatal error'); +INSERT INTO Status (JobStatus,JobStatusLong) VALUES + ('D', 'Verify found differences'); +INSERT INTO Status (JobStatus,JobStatusLong) VALUES + ('A', 'Canceled by user'); +INSERT INTO Status (JobStatus,JobStatusLong) VALUES + ('F', 'Waiting for Client'); +INSERT INTO Status (JobStatus,JobStatusLong) VALUES + ('S', 'Waiting for Storage daemon'); +INSERT INTO Status (JobStatus,JobStatusLong) VALUES + ('m', 'Waiting for new media'); +INSERT INTO Status (JobStatus,JobStatusLong) VALUES + ('M', 'Waiting for media mount'); +INSERT INTO Status (JobStatus,JobStatusLong) VALUES + ('s', 'Waiting for storage resource'); +INSERT INTO Status (JobStatus,JobStatusLong) VALUES + ('j', 'Waiting for job resource'); +INSERT INTO Status (JobStatus,JobStatusLong) VALUES + ('c', 'Waiting for client resource'); +INSERT INTO Status (JobStatus,JobStatusLong) VALUES + ('d', 'Waiting on maximum jobs'); +INSERT INTO Status (JobStatus,JobStatusLong) VALUES + ('t', 'Waiting on start time'); +INSERT INTO Status (JobStatus,JobStatusLong) VALUES + ('p', 'Waiting on higher priority jobs'); +INSERT INTO Status (JobStatus,JobStatusLong) VALUES + ('a', 'SD despooling attributes'); +INSERT INTO Status (JobStatus,JobStatusLong) VALUES + ('i', 'Doing batch insert file records'); + +INSERT INTO Version (VersionId) VALUES (11); + +-- Make sure we have appropriate permissions + diff --git a/bacula/src/win32/cats/make_sqlite3_tables.sql b/bacula/src/win32/cats/make_sqlite3_tables.sql index 89f6f8959a..83a4e4ca2f 100644 --- a/bacula/src/win32/cats/make_sqlite3_tables.sql +++ b/bacula/src/win32/cats/make_sqlite3_tables.sql @@ -1,6 +1,7 @@ + CREATE TABLE Filename ( FilenameId INTEGER, - Name TEXT DEFAULT "", + Name TEXT DEFAULT '', PRIMARY KEY(FilenameId) ); @@ -8,7 +9,7 @@ CREATE INDEX inx1 ON Filename (Name); CREATE TABLE Path ( PathId INTEGER, - Path TEXT DEFAULT "", + Path TEXT DEFAULT '', PRIMARY KEY(PathId) ); @@ -41,10 +42,10 @@ CREATE TABLE Job ( JobId INTEGER, Job VARCHAR(128) NOT NULL, Name VARCHAR(128) NOT NULL, - Type CHAR NOT NULL, - Level CHAR NOT NULL, + Type CHAR(1) NOT NULL, + Level CHAR(1) NOT NULL, ClientId INTEGER REFERENCES Client DEFAULT 0, - JobStatus CHAR NOT NULL, + JobStatus CHAR(1) NOT NULL, SchedTime DATETIME NOT NULL, StartTime DATETIME DEFAULT 0, EndTime DATETIME DEFAULT 0, @@ -54,6 +55,7 @@ CREATE TABLE Job ( VolSessionTime INTEGER UNSIGNED DEFAULT 0, JobFiles INTEGER UNSIGNED DEFAULT 0, JobBytes BIGINT UNSIGNED DEFAULT 0, + ReadBytes BIGINT UNSIGNED DEFAULT 0, JobErrors INTEGER UNSIGNED DEFAULT 0, JobMissingFiles INTEGER UNSIGNED DEFAULT 0, PoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0, @@ -65,6 +67,35 @@ CREATE TABLE Job ( ); CREATE INDEX inx6 ON Job (Name); +-- Create a table like Job for long term statistics +CREATE TABLE JobHisto ( + JobId INTEGER, + Job VARCHAR(128) NOT NULL, + Name VARCHAR(128) NOT NULL, + Type CHAR(1) NOT NULL, + Level CHAR(1) NOT NULL, + ClientId INTEGER DEFAULT 0, + JobStatus CHAR(1) NOT NULL, + SchedTime DATETIME NOT NULL, + StartTime DATETIME DEFAULT 0, + EndTime DATETIME DEFAULT 0, + RealEndTime DATETIME DEFAULT 0, + JobTDate BIGINT UNSIGNED DEFAULT 0, + VolSessionId INTEGER UNSIGNED DEFAULT 0, + VolSessionTime INTEGER UNSIGNED DEFAULT 0, + JobFiles INTEGER UNSIGNED DEFAULT 0, + JobBytes BIGINT UNSIGNED DEFAULT 0, + ReadBytes BIGINT UNSIGNED DEFAULT 0, + JobErrors INTEGER UNSIGNED DEFAULT 0, + JobMissingFiles INTEGER UNSIGNED DEFAULT 0, + PoolId INTEGER UNSIGNED DEFAULT 0, + FileSetId INTEGER UNSIGNED DEFAULT 0, + PriorJobId INTEGER UNSIGNED DEFAULT 0, + PurgedFiles TINYINT DEFAULT 0, + HasBase TINYINT DEFAULT 0 + ); +CREATE INDEX inx61 ON JobHisto (StartTime); + CREATE TABLE Location ( LocationId INTEGER, Location TEXT NOT NULL, @@ -92,7 +123,7 @@ CREATE TABLE Log ( LogText TEXT NOT NULL, PRIMARY KEY(LogId) ); -CREATE INDEX LogInx1 ON File (JobId); +CREATE INDEX LogInx1 ON Log (JobId); CREATE TABLE FileSet ( @@ -144,6 +175,7 @@ CREATE TABLE Media ( VolStatus VARCHAR(20) NOT NULL, Enabled TINYINT DEFAULT 1, Recycle TINYINT DEFAULT 0, + ActionOnPurge TINYINT DEFAULT 0, VolRetention BIGINT UNSIGNED DEFAULT 0, VolUseDuration BIGINT UNSIGNED DEFAULT 0, MaxVolJobs INTEGER UNSIGNED DEFAULT 0, @@ -217,6 +249,7 @@ CREATE TABLE Pool ( MaxVolBytes BIGINT UNSIGNED DEFAULT 0, AutoPrune TINYINT DEFAULT 0, Recycle TINYINT DEFAULT 0, + ActionOnPurge TINYINT DEFAULT 0, PoolType VARCHAR(20) NOT NULL, LabelType TINYINT DEFAULT 0, LabelFormat VARCHAR(128) NOT NULL, @@ -270,7 +303,7 @@ CREATE TABLE NextId ( -- Initialize JobId to start at 1 -INSERT INTO NextId (id, TableName) VALUES (1, "Job"); +INSERT INTO NextId (id, TableName) VALUES (1, 'Job'); CREATE TABLE Version ( VersionId INTEGER UNSIGNED NOT NULL @@ -337,11 +370,15 @@ INSERT INTO Status (JobStatus,JobStatusLong) VALUES ('t', 'Waiting on start time'); INSERT INTO Status (JobStatus,JobStatusLong) VALUES ('p', 'Waiting on higher priority jobs'); +INSERT INTO Status (JobStatus,JobStatusLong) VALUES + ('a', 'SD despooling attributes'); +INSERT INTO Status (JobStatus,JobStatusLong) VALUES + ('i', 'Doing batch insert file records'); --- Initialize Version -INSERT INTO Version (VersionId) VALUES (10); +-- Initialize Version +INSERT INTO Version (VersionId) VALUES (11); -PRAGMA default_synchronous = OFF; -PRAGMA default_cache_size = 10000; +PRAGMA default_cache_size = 100000; +PRAGMA synchronous = NORMAL; diff --git a/bacula/technotes b/bacula/technotes index afe4b99101..de97904d98 100644 --- a/bacula/technotes +++ b/bacula/technotes @@ -3,6 +3,7 @@ General: 28Apr09 +kes Update Win32/64 table creation to have new DB version 11 format kes Remove illegal Options in Exclude of default Win32/64 bacula-dir.conf 27Apr09 ebl Fix bug #1274 where a migration job can be canceled like the -- 2.39.5