Project

General

Profile

« Previous | Next » 

Revision 2530

Added by sgarg over 19 years ago

Changes to the upgrade script so that 'IN' and 'NOT IN' are not used in the queries and hence the upgrade script is much faster.

View differences:

src/upgrade-db-to-1.6-postgres.sql
29 29
30 30 
CREATE SEQUENCE xml_path_index_id_seq;
31 31 
CREATE TABLE xml_path_index (
32 
    nodeid INT8  default nextval('xml_path_index_id_seq'),
32 
        nodeid INT8  default nextval('xml_path_index_id_seq'),
33 33 
        docid VARCHAR(250),     -- the document id
34 34 
        path VARCHAR(1000),     -- precomputed path through tree
35 35 
        nodedata VARCHAR(4000), -- the data for this node (e.g.,
......
38 38 
                                  -- if it is a number
39 39 
        parentnodeid INT8,      -- id of the parent of the node represented
40 40 
                                -- by this row
41 
   CONSTRAINT xml_path_index_pk PRIMARY KEY (nodeid),
42 
   CONSTRAINT xml_path_index_docid_fk
41 
        CONSTRAINT xml_path_index_pk PRIMARY KEY (nodeid),
42 
        CONSTRAINT xml_path_index_docid_fk
43 43 
                FOREIGN KEY (docid) REFERENCES xml_documents
44 44 
);
45 45
......
70 70 
        date_updated DATE,
71 71 
        nodedatanumerical FLOAT8, -- the data for this node if
72 72 
                                  -- if it is a number
73 
   CONSTRAINT xml_nodes_revisions_pk PRIMARY KEY (nodeid),
74 
   CONSTRAINT xml_nodes_revisions_root_fk
73 
        CONSTRAINT xml_nodes_revisions_pk PRIMARY KEY (nodeid),
74 
        CONSTRAINT xml_nodes_revisions_root_fk
75 75 
                FOREIGN KEY (rootnodeid) REFERENCES xml_nodes_revisions,
76 
   CONSTRAINT xml_nodes_revisions_parent_fk
76 
        CONSTRAINT xml_nodes_revisions_parent_fk
77 77 
                FOREIGN KEY (parentnodeid) REFERENCES xml_nodes_revisions
78 78 
);
79 79
......
95 95 
 * Copy the nodes from xml_nodes to xml_nodes_revisions for old revisions
96 96 
 * of the documents and deleted documents
97 97 
 */
98 
INSERT INTO xml_nodes_revisions (nodeid, nodeindex, nodetype,
99 
nodename, nodeprefix, nodedata, parentnodeid, rootnodeid,
100 
docid, date_created, date_updated, nodedatanumerical)
101 
SELECT * FROM xml_nodes WHERE rootnodeid NOT IN
102 
(SELECT rootnodeid from xml_documents where rootnodeid is not NULL);
103 98 

  		




  
  99
  
    
INSERT INTO xml_nodes_revisions (nodeid, nodeindex, nodetype, nodename, nodeprefix, nodedata, parentnodeid, rootnodeid, docid, date_created, date_updated, nodedatanumerical) SELECT n.nodeid, n.nodeindex, n.nodetype, n.nodename, n.nodeprefix, n.nodedata, n.parentnodeid, n.rootnodeid, n.docid, n.date_created, n.date_updated, n.nodedatanumerical FROM xml_nodes n LEFT JOIN xml_documents ON n.rootnodeid = xml_documents.rootnodeid WHERE xml_documents.rootnodeid IS NULL;


  		




  104
  100
  
    

  		




  105
  101
  
    
/**


  		




  106
  102
  
    
 * Create the key constraint in xml_revisions which points to 


  		




  ......




  114
  110
  
    
 * Delete the records from xml_index table which point to old revisions in xml_index


  		




  115
  111
  
    
 * This is possible for documents for which the indexing thread failed during UPDATE


  		




  116
  112
  
    
 */


  		




  
  113
  
    
DELETE FROM xml_index WHERE nodeid = xml_nodes_revisions.nodeid;


  		




  117
  114
  
    

  		




  118
  
  
    
DELETE FROM xml_index WHERE nodeid IN (SELECT nodeid FROM xml_nodes WHERE


  		




  119
  
  
    
rootnodeid NOT IN (SELECT rootnodeid FROM xml_documents WHERE rootnodeid IS NOT NULL)); 


  		




  120
  115
  
    

  		




  121
  116
  
    
/**


  		




  122
  117
  
    
 * Delete the records from xml_nodes which were transfered to xml_nodes_revisions 


  		




  123
  118
  
    
 */


  		




  124
  
  
    
DELETE FROM xml_nodes WHERE rootnodeid NOT IN


  		




  125
  
  
    
(SELECT rootnodeid from xml_documents where rootnodeid is not NULL);


  		




  
  119
  
    

  		




  
  120
  
    
// rename xml_nodes to xml_nodes_2


  		




  
  121
  
    
alter table xml_nodes rename to xml_nodes_2;


  		




  
  122
  
    

  		




  
  123
  
    

  		




  
  124
  
    
// Create a new xml_nodes table 


  		




  
  125
  
    
CREATE TABLE xml_nodes (


  		




  
  126
  
    
        nodeid INT8 default nextval('xml_nodes_id_seq'),


  		




  
  127
  
    
                                        -- the unique node id (pk)


  		




  
  128
  
    
        nodeindex INT8,         -- order of nodes within parent


  		




  
  129
  
    
        nodetype VARCHAR(20),   -- type (DOCUMENT, COMMENT, PI,


  		




  
  130
  
    
                                -- ELEMENT, ATTRIBUTE, TEXT)


  		




  
  131
  
    
        nodename VARCHAR(250),  -- the name of an element or attribute


  		




  
  132
  
    
        nodeprefix VARCHAR(50), -- the namespace prefix of the node


  		




  
  133
  
    
        nodedata VARCHAR(4000), -- the data for this node (e.g.,


  		




  
  134
  
    
                                -- for TEXT it is the content)


  		




  
  135
  
    
        parentnodeid INT8,      -- index of the parent of this node


  		




  
  136
  
    
        rootnodeid INT8,        -- index of the root node of this tree


  		




  
  137
  
    
        docid VARCHAR(250),     -- index to the document id


  		




  
  138
  
    
        date_created DATE,


  		




  
  139
  
    
        date_updated DATE,


  		




  
  140
  
    
        nodedatanumerical FLOAT8, -- the data for this node if


  		




  
  141
  
    
                                  -- if it is a number


  		




  
  142
  
    
   CONSTRAINT xml_nodes_pk PRIMARY KEY (nodeid),


  		




  
  143
  
    
   CONSTRAINT xml_nodes_root_fk


  		




  
  144
  
    
                FOREIGN KEY (rootnodeid) REFERENCES xml_nodes,


  		




  
  145
  
    
   CONSTRAINT xml_nodes_parent_fk


  		




  
  146
  
    
                FOREIGN KEY (parentnodeid) REFERENCES xml_nodes


  		




  
  147
  
    
);


  		




  
  148
  
    

  		




  
  149
  
    

  		




  
  150
  
    
// copy data from xml_nodes_2 to xml_nodes


  		




  
  151
  
    
INSERT INTO xml_nodes (nodeid, nodeindex, nodetype, nodename, nodeprefix, nodedata, parentnodeid, rootnodeid, docid, date_created, date_updated, nodedatanumerical) select n.nodeid, n.nodeindex, n.nodetype, n.nodename, n.nodeprefix, n.nodedata, n.parentnodeid, n.rootnodeid, n.docid, n.date_created, n.date_updated, n.nodedatanumerical from xml_nodes n LEFT JOIN xml_nodes_revisions r ON n.rootnodeid = r.rootnodeid where r.rootnodeid is NULL;


  		




  
  152
  
    

  		




  
  153
  
    
// create indexes


  		




  
  154
  
    
CREATE INDEX xml_nodes_idx1 ON xml_nodes (rootnodeid);


  		




  
  155
  
    
CREATE INDEX xml_nodes_idx2 ON xml_nodes (parentnodeid);


  		




  
  156
  
    
CREATE INDEX xml_nodes_idx3 ON xml_nodes (nodename);


  		




  
  157
  
    

  		




  
  158
  
    
// add constaints which were deleted while moving xml_nodes to xml_nodes_2


  		




  
  159
  
    
ALTER TABLE xml_documents ADD CONSTRAINT xml_documents_root_fk FOREIGN KEY (rootnodeid) REFERENCES xml_nodes;


  		




  
  160
  
    
ALTER TABLE xml_index ADD CONSTRAINT xml_index_nodeid_fk FOREIGN KEY (nodeid) REFERENCES xml_nodes;


  		




  
  161
  
    

  		




  
  162
  
    

  		












Also available in:  Unified diff