1-- update db schema from 2004 to 2171
2
3-- table UnsavedFiles is not used, therefore dropped.
4DROP TABLE IF EXISTS UnsavedFiles;
5
6--
7-- merge Filenames (from Filename.Name) info File table
8--
9
10-- To use lesser disk space,
11-- drops indices, that needs to regenerated anyway.
12DROP INDEX IF EXISTS file_jobid_idx;
13DROP INDEX IF EXISTS file_jpfid_idx;
14
15-- this temporary table, used for the migration,  should not exist, but drop it just in case.
16DROP TABLE IF EXISTS TmpMergeFilenameIntoFileTable;
17
18
19
20-- start transaction
21BEGIN;
22
23-- create temporary table to be used as File table later
24CREATE TABLE TmpMergeFilenameIntoFileTable (
25   FileId           BIGSERIAL   NOT NULL,
26   FileIndex        INTEGER     NOT NULL  DEFAULT 0,
27   JobId            INTEGER     NOT NULL,
28   PathId           INTEGER     NOT NULL,
29   DeltaSeq         SMALLINT    NOT NULL  DEFAULT 0,
30   MarkId           INTEGER     NOT NULL  DEFAULT 0,
31   Fhinfo           NUMERIC(20) NOT NULL  DEFAULT 0,
32   Fhnode           NUMERIC(20) NOT NULL  DEFAULT 0,
33   LStat            TEXT        NOT NULL,
34   Md5              TEXT        NOT NULL,
35   Name             TEXT        NOT NULL,
36   PRIMARY KEY (FileId)
37);
38
39-- merge Filename in File table
40INSERT INTO TmpMergeFilenameIntoFileTable
41  (FileId, FileIndex, JobId, PathId, Name, DeltaSeq, MarkId, LStat, Md5)
42  SELECT File.FileId, File.FileIndex, File.JobId, File. PathId, Filename.Name, File.DeltaSeq,
43         File.MarkId, File.LStat, File.Md5 FROM File, Filename
44  WHERE  File.FilenameId = Filename.FilenameId;
45
46DROP TABLE Filename;
47DROP TABLE File;
48ALTER TABLE TmpMergeFilenameIntoFileTable RENAME TO File;
49ALTER INDEX TmpMergeFilenameIntoFileTable_pkey RENAME TO file_pkey;
50
51-- adapt index
52CREATE INDEX file_jobid_idx ON File (JobId);
53ALTER SEQUENCE TmpMergeFilenameIntoFileTable_fileid_seq RENAME TO file_fileid_seq;
54SELECT setval('file_fileid_seq', (SELECT max(fileid) from file));
55CREATE INDEX file_jpfid_idx ON File (JobId, PathId, Name);
56CREATE INDEX file_pjidpart_idx ON File(PathId,JobId) WHERE FileIndex = 0 AND Name = '';
57
58-- Add JobMedia JobBytes info, also used by NDMP DAR
59ALTER TABLE JobMedia ADD COLUMN JobBytes NUMERIC(20) DEFAULT 0;
60
61UPDATE Version SET VersionId = 2171;
62
63COMMIT;
64
65set client_min_messages = fatal;
66
67ANALYSE;
68