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