Add BDB_VERSION for scripts + prepare update 11 to 12 + automate BDB_VERSION checking

[bacula/bacula] / bacula / src / cats / make_postgresql_tables.in

diff --git a/bacula/src/cats/make_postgresql_tables.in b/bacula/src/cats/make_postgresql_tables.in
index dae28e4c8afc3c3b6b1f91820e42b4abca2f3fe8..aa28815a54d0acaaeb02e54a8f01f5d49b7e1313 100644 (file)
--- a/bacula/src/cats/make_postgresql_tables.in
+++ b/bacula/src/cats/make_postgresql_tables.in
@@ -2,51 +2,65 @@
 #
 # shell script to create Bacula PostgreSQL tables
 #
+# Important note: 
+#   You won't get any support for performance issue if you changed the default
+#   schema.
+#
 bindir=@SQL_BINDIR@
 PATH="$bindir:$PATH"
 db_name=${db_name:-@db_name@}
 
 psql -f - -d ${db_name} $* <<END-OF-DATA
 
-CREATE TABLE filename
+CREATE TABLE Filename
 (
-    filenameid       serial      not null,
-    name             text        not null,
-    primary key (filenameid)
+    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);
+ALTER TABLE Filename ALTER COLUMN Name SET STATISTICS 1000;
+CREATE UNIQUE INDEX filename_name_idx on Filename (Name);
 
-CREATE TABLE path
+CREATE TABLE Path
 (
-    pathid           serial      not null,
-    path             text        not null,
-    primary key (pathid)
+    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
+ALTER TABLE Path ALTER COLUMN Path SET STATISTICS 1000;
+CREATE UNIQUE INDEX path_name_idx on Path (Path);
+
+-- We strongly recommend to avoid the temptation to add new indexes.
+-- In general, these will cause very significant performance
+-- problems in other areas.  A better approch is to carefully check
+-- that all your memory configuation parameters are
+-- suitable for the size of your installation. If you backup
+-- millions of files, you need to adapt the database memory
+-- configuration parameters concerning sorting, joining and global
+-- memory.  By default, sort and join parameters are very small
+-- (sometimes 8Kb), and having sufficient memory specified by those
+-- parameters is extremely important to run fast.  
+
+-- In File table
+-- FileIndex can be 0 for FT_DELETED files
+-- FileNameId can link to Filename.Name='' for directories
+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)
+    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_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);
+CREATE INDEX file_jobid_idx on File (JobId);
 
 --
 -- Add this if you have a good number of job
@@ -55,7 +69,8 @@ CREATE INDEX file_jobid_idx on file(jobid);
 
 --
 -- Possibly add one or more of the following indexes
---  if your Verifies are too slow.
+--  if your Verifies are too slow, but they can slow down
+--  backups.
 --
 -- CREATE INDEX file_pathid_idx on file(pathid);
 -- CREATE INDEX file_filenameid_idx on file(filenameid);
@@ -63,17 +78,18 @@ CREATE INDEX file_jobid_idx on file(jobid);
 CREATE TABLE RestoreObject (
    RestoreObjectId SERIAL NOT NULL,
    ObjectName TEXT NOT NULL,
-   RestoreObject TEXT NOT NULL,
+   RestoreObject BYTEA NOT NULL,
    PluginName TEXT NOT NULL,
    ObjectLength INTEGER DEFAULT 0,
+   ObjectFullLength INTEGER DEFAULT 0,
    ObjectIndex INTEGER DEFAULT 0,
    ObjectType INTEGER DEFAULT 0,
-   FileIndex INTEGER UNSIGNED DEFAULT 0,
-   JobId INTEGER UNSIGNED,
+   FileIndex INTEGER DEFAULT 0,
+   JobId INTEGER,
    ObjectCompression INTEGER DEFAULT 0,
-   PRIMARY KEY(RestoreObjectId),
+   PRIMARY KEY(RestoreObjectId)
    );
-CREATE INDEX restore_jobid_idx on file(JobId);
+CREATE INDEX restore_jobid_idx on RestoreObject(JobId);
 
 
 CREATE TABLE Job
@@ -428,7 +444,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