[Sommaire] [Imprimer]

   1  UPDATE ezsite_data SET value='3.9.0' WHERE name='ezpublish-version';
   2  UPDATE ezsite_data SET value='5' WHERE name='ezpublish-release';
   3  
   4  
   5  -- START: from 3.8.1
   6  CREATE INDEX ezkeyword_keyword_id ON ezkeyword USING btree ( keyword, id );
   7  CREATE INDEX ezkeyword_attr_link_kid_oaid ON ezkeyword_attribute_link USING btree ( keyword_id, objectattribute_id );
   8  
   9  CREATE INDEX ezurlalias_is_wildcard ON ezurlalias USING btree ( is_wildcard );
  10  
  11  CREATE INDEX eznode_assignment_coid_cov ON eznode_assignment USING btree ( contentobject_id,contentobject_version );
  12  CREATE INDEX eznode_assignment_is_main ON eznode_assignment USING btree ( is_main );
  13  CREATE INDEX eznode_assignment_parent_node ON eznode_assignment USING btree ( parent_node );
  14  -- END: from 3.8.1
  15  
  16  ALTER TABLE ezuservisit ADD COLUMN failed_login_attempts int;
  17  ALTER TABLE ezuservisit ALTER COLUMN failed_login_attempts SET DEFAULT 0;
  18  ALTER TABLE ezuservisit ALTER COLUMN failed_login_attempts SET NOT NULL;
  19  
  20  ALTER TABLE ezcontentobject_link ADD COLUMN relation_type int;
  21  ALTER TABLE ezcontentobject_link ALTER COLUMN relation_type SET DEFAULT 1;
  22  ALTER TABLE ezcontentobject_link ALTER COLUMN relation_type SET NOT NULL;
  23  UPDATE ezcontentobject_link SET relation_type=8 WHERE contentclassattribute_id<>0;
  24  
  25  
  26  -- START: 'default sorting' attribute for ezcontentclass
  27  
  28  ALTER TABLE ezcontentclass ADD COLUMN sort_field int;
  29  ALTER TABLE ezcontentclass ALTER COLUMN sort_field SET DEFAULT 1;
  30  ALTER TABLE ezcontentclass ALTER COLUMN sort_field SET NOT NULL;
  31  
  32  ALTER TABLE ezcontentclass ADD COLUMN sort_order int;
  33  ALTER TABLE ezcontentclass ALTER COLUMN sort_order SET DEFAULT 1;
  34  ALTER TABLE ezcontentclass ALTER COLUMN sort_order SET NOT NULL;
  35  
  36  -- END: 'default sorting' attribute for ezcontentclass
  37  
  38  -- START: new table for trash
  39  CREATE TABLE ezcontentobject_trash (
  40      contentobject_id integer,
  41      contentobject_version integer,
  42      depth integer DEFAULT 0 NOT NULL,
  43      is_hidden integer DEFAULT 0 NOT NULL,
  44      is_invisible integer DEFAULT 0 NOT NULL,
  45      main_node_id integer,
  46      modified_subnode integer DEFAULT 0,
  47      node_id integer DEFAULT 0 NOT NULL,
  48      parent_node_id integer DEFAULT 0 NOT NULL,
  49      path_identification_string text,
  50      path_string character varying(255) DEFAULT ''::character varying NOT NULL,
  51      priority integer DEFAULT 0 NOT NULL,
  52      remote_id character varying(100) DEFAULT ''::character varying NOT NULL,
  53      sort_field integer DEFAULT 1,
  54      sort_order integer DEFAULT 1
  55  );
  56  
  57  
  58  CREATE INDEX ezcobj_trash_co_id ON ezcontentobject_trash USING btree (contentobject_id);
  59  CREATE INDEX ezcobj_trash_depth ON ezcontentobject_trash USING btree (depth);
  60  CREATE INDEX ezcobj_trash_p_node_id ON ezcontentobject_trash USING btree (parent_node_id);
  61  CREATE INDEX ezcobj_trash_path ON ezcontentobject_trash USING btree (path_string);
  62  CREATE INDEX ezcobj_trash_path_ident ON ezcontentobject_trash USING btree (path_identification_string);
  63  CREATE INDEX ezcobj_trash_modified_subnode ON ezcontentobject_trash USING btree (modified_subnode);
  64  ALTER TABLE ONLY ezcontentobject_trash ADD CONSTRAINT ezcontentobject_trash_pkey PRIMARY KEY (node_id);
  65  -- END: new table for trash
  66  
  67  -- START: ezcontentclass/ezcontentclass_attribute translations
  68  ALTER TABLE ezcontentclass RENAME COLUMN name TO serialized_name_list;
  69  ALTER TABLE ezcontentclass ADD COLUMN language_mask integer;
  70  ALTER TABLE ezcontentclass ALTER language_mask SET NOT NULL;
  71  ALTER TABLE ezcontentclass ALTER language_mask SET DEFAULT 0;
  72  ALTER TABLE ezcontentclass ADD COLUMN initial_language_id integer;
  73  ALTER TABLE ezcontentclass ALTER initial_language_id SET NOT NULL;
  74  ALTER TABLE ezcontentclass ALTER initial_language_id SET DEFAULT 0;
  75  ALTER TABLE ezcontentclass_attribute RENAME COLUMN name TO serialized_name_list;
  76  
  77  CREATE TABLE ezcontentclass_name
  78  (
  79      contentclass_id integer NOT NULL default 0,
  80      contentclass_version integer NOT NULL default 0,
  81      language_locale varchar(20) NOT NULL default '',
  82      language_id integer NOT NULL default 0,
  83      name varchar(255) NOT NULL default ''
  84  );
  85  
  86  ALTER TABLE ONLY ezcontentclass_name
  87      ADD CONSTRAINT ezcontentclass_name_pkey PRIMARY KEY (contentclass_id, contentclass_version, language_id);
  88  -- END: ezcontentclass/ezcontentclass_attribute translations
  89  
  90  -- START: eztipafriend_counter, new column and primary key (new fetch function for tipafriend_top_list)
  91  ALTER TABLE eztipafriend_counter ADD COLUMN requested integer;
  92  ALTER TABLE eztipafriend_counter ALTER requested SET NOT NULL;
  93  ALTER TABLE eztipafriend_counter ALTER requested SET DEFAULT 0;
  94  
  95  ALTER TABLE eztipafriend_counter DROP CONSTRAINT eztipafriend_counter_pkey;
  96  ALTER TABLE ONLY eztipafriend_counter ADD CONSTRAINT eztipafriend_counter_pkey PRIMARY KEY( node_id, requested );
  97  -- END: eztipafriend_counter, new column and primary key (new fetch function for tipafriend_top_list)
  98  
  99  -- START: improvements in shop(better vat handling of order items, like shipping)
 100  ALTER TABLE ezorder_item ADD COLUMN is_vat_inc integer;
 101  ALTER TABLE ezorder_item ALTER is_vat_inc SET NOT NULL;
 102  ALTER TABLE ezorder_item ALTER is_vat_inc SET default 0;
 103  -- END: improvements in shop(better vat handling of order items, like shipping)
 104  
 105  
 106  
 107  -- START: from 3.8.5
 108  -- ezcontentobject
 109  CREATE INDEX ezcontentobject_pub ON ezcontentobject USING btree ( published );
 110  CREATE INDEX ezcontentobject_status ON ezcontentobject USING btree ( status );
 111  CREATE INDEX ezcontentobject_classid ON ezcontentobject USING btree ( contentclass_id );
 112  CREATE INDEX ezcontentobject_currentversion ON ezcontentobject USING btree ( current_version );
 113  
 114  -- ezcontentobject_name
 115  CREATE INDEX ezcontentobject_name_lang_id ON ezcontentobject_name USING btree ( language_id );
 116  CREATE INDEX ezcontentobject_name_name ON ezcontentobject_name USING btree ( name );
 117  CREATE INDEX ezcontentobject_name_co_id ON ezcontentobject_name USING btree ( contentobject_id );
 118  CREATE INDEX ezcontentobject_name_cov_id ON ezcontentobject_name USING btree ( content_version );
 119  
 120  -- ezcontentobject_version
 121  CREATE INDEX ezcobj_version_creator_id ON ezcontentobject_version USING btree ( creator_id );
 122  CREATE INDEX ezcobj_version_status ON ezcontentobject_version USING btree ( status );
 123  
 124  -- ezpolicy_limitation_value
 125  CREATE INDEX ezpolicy_limitation_value_val ON ezpolicy_limitation_value USING btree ( value );
 126  
 127  -- ezinfocollection_attribute
 128  CREATE INDEX ezinfocollection_attr_co_id ON ezinfocollection_attribute USING btree ( contentobject_id );
 129  
 130  -- ezurlalias
 131  CREATE INDEX ezurlalias_forward_to_id ON ezurlalias USING btree ( forward_to_id );
 132  
 133  -- ezkeyword
 134  CREATE INDEX ezkeyword_keyword ON ezkeyword USING btree ( keyword );
 135  
 136  -- ezurl
 137  CREATE INDEX ezurl_url ON ezurl USING btree ( url );
 138  
 139  -- ezcontentobject_attribute
 140  CREATE INDEX ezcontentobject_attr_id ON ezcontentobject_attribute USING btree ( id );
 141  
 142  -- ezcontentoclass_attribute
 143  CREATE INDEX ezcontentclass_attr_ccid ON ezcontentclass_attribute USING btree ( contentclass_id );
 144  
 145  -- eznode_assignment
 146  CREATE INDEX eznode_assignment_co_id ON eznode_assignment USING btree ( contentobject_id );
 147  CREATE INDEX eznode_assignment_co_version ON eznode_assignment USING btree ( contentobject_version );
 148  
 149  -- ezkeyword_attribute_link
 150  CREATE INDEX ezkeyword_attr_link_keyword_id ON ezkeyword_attribute_link USING btree ( keyword_id );
 151  -- END: from 3.8.5
 152  
 153  
 154  CREATE INDEX  ezsearch_return_cnt_ph_id_count  ON   ezsearch_return_count ( phrase_id, count );
 155  -- alter table ezsearch_return_count add key ( phrase_id, count );
 156  CREATE INDEX ezsearch_search_phrase_phr ON ezsearch_search_phrase ( phrase );
 157  -- alter table ezsearch_search_phrase add key ( phrase );
 158  CREATE SEQUENCE ezsearch_search_phrase_new_s
 159      START 1
 160      INCREMENT 1
 161      MAXVALUE 9223372036854775807
 162      MINVALUE 1
 163      CACHE 1;
 164  
 165  CREATE TABLE ezsearch_search_phrase_new (
 166    id int DEFAULT nextval('ezsearch_search_phrase_new_s'::text) PRIMARY KEY,
 167    phrase varchar(250) default NULL,
 168    phrase_count int default 0,
 169    result_count int default 0
 170  );
 171  CREATE UNIQUE INDEX ezsearch_search_phrase_phrase ON ezsearch_search_phrase_new ( phrase );
 172  CREATE INDEX ezsearch_search_phrase_count ON ezsearch_search_phrase_new ( phrase_count );
 173  
 174  
 175  INSERT INTO ezsearch_search_phrase_new ( phrase, phrase_count, result_count )
 176  SELECT   lower( phrase ), count(*), sum( ezsearch_return_count.count )
 177  FROM     ezsearch_search_phrase,
 178           ezsearch_return_count
 179  WHERE    ezsearch_search_phrase.id = ezsearch_return_count.phrase_id
 180  GROUP BY lower( ezsearch_search_phrase.phrase );
 181  
 182  -- ezsearch_return_count is of no (additional) use in a normal eZ publish installation
 183  -- but perhaps someone built something for himself, then it is not BC
 184  -- to not break BC apply the CREATE and INSERT statements
 185  CREATE SEQUENCE ezsearch_return_count_new_s
 186      START 1
 187      INCREMENT 1
 188      MAXVALUE 9223372036854775807
 189      MINVALUE 1
 190      CACHE 1;
 191  
 192  CREATE TABLE ezsearch_return_count_new (
 193    id int NOT NULL DEFAULT nextval('ezsearch_search_phrase_new_s'::text) PRIMARY KEY,
 194    phrase_id int NOT NULL default 0,
 195    time int NOT NULL default 0,
 196    count int NOT NULL default 0
 197  );
 198  CREATE INDEX  ezsearch_return_cnt_new_ph_id_cnt  ON  ezsearch_return_count_new ( phrase_id, count );
 199  
 200  INSERT INTO ezsearch_return_count_new ( phrase_id, time, count )
 201  SELECT    ezsearch_search_phrase_new.id, time, count
 202  FROM      ezsearch_search_phrase,
 203            ezsearch_search_phrase_new,
 204            ezsearch_return_count
 205  WHERE     ezsearch_search_phrase_new.phrase = LOWER( ezsearch_search_phrase.phrase ) AND
 206            ezsearch_search_phrase.id = ezsearch_return_count.phrase_id;
 207  
 208  -- final tasks with and without BC
 209  DROP TABLE ezsearch_search_phrase;
 210  --ALTER TABLE ezsearch_search_phrase RENAME TO ezsearch_search_phrase_old;
 211  ALTER TABLE ezsearch_search_phrase_new RENAME TO ezsearch_search_phrase;
 212  
 213  DROP TABLE ezsearch_return_count;
 214  -- ALTER TABLE ezsearch_return_count RENAME TO ezsearch_return_count_old;
 215  -- of course the next statement is only valid if you created `ezsearch_return_count_new`
 216  ALTER TABLE ezsearch_return_count_new RENAME TO ezsearch_return_count;
 217  ALTER TABLE  ezsearch_return_count drop  constraint ezsearch_return_count_new_pkey;
 218  ALTER TABLE ezsearch_return_count ADD PRIMARY KEY(id);
 219  ALTER TABLE ezsearch_search_phrase  drop  constraint ezsearch_search_phrase_new_pkey;
 220  ALTER TABLE ezsearch_search_phrase  ADD PRIMARY KEY(id);
 221  DROP  INDEX ezsearch_return_cnt_new_ph_id_cnt;
 222  CREATE INDEX  ezsearch_return_cnt_ph_id_cnt  ON   ezsearch_return_count ( phrase_id, count );
 223  
 224