Removed workaround for bug in Ingres with insert into tables with sequences from...

[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 34d5d612547fd3d515be285b14348ecc0f43dd06..fd831d377440b197866a9bdc620718c26eff5a3c 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,24 +69,27 @@ 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);
 
 CREATE TABLE RestoreObject (
    RestoreObjectId SERIAL NOT NULL,
-   Fname TEXT NOT NULL,
-   Path TEXT NOT NULL,
-   RestoreObject TEXT NOT NULL,
+   ObjectName 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,
-   PRIMARY KEY(RestoreObjectId),
+   FileIndex INTEGER DEFAULT 0,
+   JobId INTEGER,
+   ObjectCompression INTEGER DEFAULT 0,
+   PRIMARY KEY(RestoreObjectId)
    );
-CREATE INDEX restore_jobid_idx on file(JobId);
+CREATE INDEX restore_jobid_idx on RestoreObject(JobId);
 
 
 CREATE TABLE Job