Project

General

Profile

« Previous | Next » 

Revision 2531

Added by sgarg over 19 years ago

Bugfixes to the previous commit and adding comments etc.

View differences:

src/upgrade-db-to-1.6-postgres.sql
51 51 
CREATE INDEX xml_path_index_idx3 ON xml_path_index (nodedatanumerical);
52 52 

  		




  53
  53
  
    

  		




  
  54
  
    

  		




  
  55
  
    

  		




  54
  56
  
    
/*


  		




  55
  57
  
    
 * Table for storing the nodes for the old revisions of the document and the deleted documents


  		




  56
  58
  
    
 */


  		




  ......




  85
  87
  
    
CREATE INDEX xml_nodes_revisions_idx3 ON xml_nodes_revisions (nodename);


  		




  86
  88
  
    

  		




  87
  89
  
    

  		




  
  90
  
    

  		




  88
  91
  
    
/**


  		




  89
  92
  
    
 * Drop the constraint from xml_revisions which points to xml_nodes


  		




  90
  93
  
    
 */


  		




  91
  94
  
    
ALTER TABLE xml_revisions DROP CONSTRAINT xml_revisions_root_fk;


  		




  92
  95
  
    

  		




  93
  96
  
    

  		




  
  97
  
    

  		




  94
  98
  
    
/**


  		




  95
  99
  
    
 * Copy the nodes from xml_nodes to xml_nodes_revisions for old revisions


  		




  96
  100
  
    
 * of the documents and deleted documents


  		




  97
  101
  
    
 */


  		




  
  102
  
    
INSERT INTO xml_nodes_revisions (nodeid, nodeindex, nodetype, 


  		




  
  103
  
    
nodename, nodeprefix, nodedata, parentnodeid, rootnodeid, docid, 


  		




  
  104
  
    
date_created, date_updated, nodedatanumerical) SELECT n.nodeid, 


  		




  
  105
  
    
n.nodeindex, n.nodetype, n.nodename, n.nodeprefix, n.nodedata, 


  		




  
  106
  
    
n.parentnodeid, n.rootnodeid, n.docid, n.date_created, n.date_updated, 


  		




  
  107
  
    
n.nodedatanumerical FROM xml_nodes n LEFT JOIN xml_documents 


  		




  
  108
  
    
ON n.rootnodeid = xml_documents.rootnodeid WHERE 


  		




  
  109
  
    
xml_documents.rootnodeid IS NULL;


  		




  98
  110
  
    

  		




  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;


  		




  100
  111
  
    

  		




  
  112
  
    

  		




  101
  113
  
    
/**


  		




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


  		




  103
  115
  
    
 * xml_nodes_revisions


  		




  104
  116
  
    
 */


  		




  105
  117
  
    
ALTER TABLE xml_revisions ADD CONSTRAINT xml_revisions_root_fk


  		




  106
  118
  
    
 FOREIGN KEY (rootnodeid) REFERENCES xml_nodes_revisions(nodeid);


  		




  
  119
  
    

  		




  
  120
  
    

  		




  107
  121
  
    
 


  		




  108
  
  
    

  		




  109
  122
  
    
/**


  		




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


  		




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


  		




  ......




  113
  126
  
    
DELETE FROM xml_index WHERE nodeid = xml_nodes_revisions.nodeid;


  		




  114
  127
  
    

  		




  115
  128
  
    

  		




  
  129
  
    

  		




  116
  130
  
    
/**


  		




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


  		




  118
  132
  
    
 */


  		




  
  133
  
    
ALTER TABLE xml_nodes DROP CONSTRAINT xml_nodes_pk CASCADE;


  		




  119
  134
  
    

  		




  120
  
  
    
// rename xml_nodes to xml_nodes_2


  		




  121
  
  
    
alter table xml_nodes rename to xml_nodes_2;


  		




  122
  135
  
    

  		




  123
  136
  
    

  		




  124
  
  
    
// Create a new xml_nodes table 


  		




  
  137
  
    
/** rename xml_nodes to xml_nodes_2 */


  		




  
  138
  
    
ALTER TABLE xml_nodes RENAME TO xml_nodes_2;


  		




  
  139
  
    

  		




  
  140
  
    

  		




  
  141
  
    
/** Create a new xml_nodes table */


  		




  125
  142
  
    
CREATE TABLE xml_nodes (


  		




  126
  143
  
    
        nodeid INT8 default nextval('xml_nodes_id_seq'),


  		




  127
  144
  
    
                                        -- the unique node id (pk)


  		




  ......




  147
  164
  
    
);


  		




  148
  165
  
    

  		




  149
  166
  
    

  		




  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;


  		




  
  167
  
    
/** copy data from xml_nodes_2 to xml_nodes */


  		




  
  168
  
    
INSERT INTO xml_nodes (nodeid, nodeindex, nodetype, nodename, 


  		




  
  169
  
    
nodeprefix, nodedata, parentnodeid, rootnodeid, docid, 


  		




  
  170
  
    
date_created, date_updated, nodedatanumerical) SELECT n.nodeid, 


  		




  
  171
  
    
n.nodeindex, n.nodetype, n.nodename, n.nodeprefix, n.nodedata, 


  		




  
  172
  
    
n.parentnodeid, n.rootnodeid, n.docid, n.date_created, 


  		




  
  173
  
    
n.date_updated, n.nodedatanumerical FROM xml_nodes_2 n 


  		




  
  174
  
    
LEFT JOIN xml_nodes_revisions r ON n.rootnodeid = r.rootnodeid 


  		




  
  175
  
    
WHERE r.rootnodeid is NULL;


  		




  152
  176
  
    

  		




  153
  
  
    
// create indexes


  		




  
  177
  
    
/** Drop old indexes **/


  		




  
  178
  
    
DROP INDEX xml_nodes_idx1;


  		




  
  179
  
    
DROP INDEX xml_nodes_idx2;


  		




  
  180
  
    
DROP INDEX xml_nodes_idx3;


  		




  
  181
  
    

  		




  
  182
  
    
/** Create new indexes **/


  		




  154
  183
  
    
CREATE INDEX xml_nodes_idx1 ON xml_nodes (rootnodeid);


  		




  155
  184
  
    
CREATE INDEX xml_nodes_idx2 ON xml_nodes (parentnodeid);


  		




  156
  185
  
    
CREATE INDEX xml_nodes_idx3 ON xml_nodes (nodename);


  		




  157
  186
  
    

  		




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


  		




  
  187
  
    
/** Add constaints which were deleted before moving xml_nodes to xml_nodes_2 */


  		




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


  		




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


  		




  161
  190
  
    

  		




  
  191
  
    
/** Drop xml_nodes_2 table */


  		




  
  192
  
    
DROP TABLE xml_nodes_2;


  		




  162
  193
  
    

  		




  
  194
  
    
/** done */


  		












Also available in:  Unified diff