X-Git-Url: https://git.sur5r.net/?a=blobdiff_plain;f=bacula%2Fsrc%2Fcats%2Fmake_ingres_tables.in;h=a3bc72506decf22c0b3d3300ecd3dec2b91e439d;hb=ab75c2027cd5cbbee384761dc315f480868d3f25;hp=132629ae5973b96eaca1bec897ab37bc1fdfcbc3;hpb=031b9e95ccb4fdbbdf6aa948233647e31080f693;p=bacula%2Fbacula

diff --git a/bacula/src/cats/make_ingres_tables.in b/bacula/src/cats/make_ingres_tables.in
index 132629ae59..a3bc72506d 100755
--- a/bacula/src/cats/make_ingres_tables.in
+++ b/bacula/src/cats/make_ingres_tables.in
@@ -9,427 +9,442 @@ 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,
+   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 RestoreObject(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
 
@@ -476,7 +491,7 @@ INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
 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