#
# shell script to create Bacula Ingres tables
#
-bindir=@SQL_BINDIR@
+bindir=@INGRES_BINDIR@
PATH="$bindir:$PATH"
db_name=${db_name:-@db_name@}
db_user=${db_user:-@db_user@}
sql -u${db_user} $* ${db_name} <<END-OF-DATA
+--
+-- When using batch insert make sure you adhere to the following
+-- minimum Ingres version:
+--
+-- Ingres 9.2 or higher with the fix for bug 123652 and bug 117256
+-- For Solaris x86 this is Ingres 9.2. + patch 13785 or higher
+-- For Linux x86_64 this is Ingres 9.2 + patch 13790 or higher
+--
+
+--
+-- As it seems the upper limit for a VARCHAR/VARBYTE column is 32000 bytes
+-- We could have used a BLOB as storage type which can go up to 2 Gb but
+-- you cannot create indexes based on a BLOB (and we don't seem to be able
+-- to limit the part of the field which should be used for the index to lets
+-- say 255 chars like we can in MySQL) and the storage for a BLOB is also far
+-- from optimal so for now we decided that the upper limit for File and Path
+-- fields is the 32000 bytes.
+--
+-- To use the big VARBYTE colums we need the bigger page sizes enabled in
+-- ingres (which at this time supports 2K, 4K, 8K, 16K, 32K, 64K)
+-- Make sure you have the following settings in your
+-- ${II_SYSTEM}/ingres/files/config.dat
+--
+-- ii.<nodename>.dbms.private.*.cache.p8k_status: ON
+-- ii.<nodename>.dbms.private.*.cache.p16k_status: ON
+-- ii.<nodename>.dbms.private.*.cache.p32k_status: ON
+-- ii.<nodename>.dbms.private.*.cache.p64k_status: ON
+-- ii.<nodename>.rcp.dmf_cache_size8k: 200
+-- ii.<nodename>.rcp.dmf_cache_size16k: 200
+-- ii.<nodename>.rcp.dmf_cache_size32k: 200
+-- ii.<nodename>.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,
+ DeltaSeq INTEGER NOT NULL DEFAULT 0,
+ 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 SEQUENCE RestoreObject_Seq;
+CREATE TABLE RestoreObject (
+ RestoreObjectId INTEGER NOT NULL DEFAULT RestoreObject_Seq.nextval,
+ ObjectName VARBYTE(128) NOT NULL,
+ RestoreObject BLOB NOT NULL,
+ PluginName VARBYTE(128) NOT NULL,
+ ObjectLength INTEGER DEFAULT 0,
+ ObjectFullLength INTEGER DEFAULT 0,
+ ObjectIndex INTEGER DEFAULT 0,
+ ObjectType INTEGER DEFAULT 0,
+ FileIndex INTEGER DEFAULT 0,
+ JobId INTEGER,
+ ObjectCompression INTEGER DEFAULT 0,
+ PRIMARY KEY (RestoreObjectId)
+);
+CREATE INDEX restore_jobid_idx on RestoreObject(JobId);
-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,
- PRIMARY KEY(MediaTypeId)
- );
+ 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,
- PRIMARY KEY(StorageId)
- );
+ 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,
- PRIMARY KEY(DeviceId)
- );
-
-CREATE SEQUENCE pool_seq;
-CREATE TABLE pool
+ 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
(
- 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,
- PRIMARY KEY(LocLogId)
+ 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
INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
('i', 'Doing batch insert file records',15);
-INSERT INTO Version (VersionId) VALUES (12);
+INSERT INTO Version (VersionId) VALUES (@BDB_VERSION@);
-- Make sure we have appropriate permissions
\g
projects / bacula / bacula / blobdiff