upgrade.sql
author Dan
Thu, 17 Jan 2008 02:03:33 -0500
changeset 349 fdaf9070566c
parent 328 dc838fd61a06
permissions -rw-r--r--
More progress on the installer. At this point it can install and import the language, but does not rename config files. Still much work to be done, most notably localization and creation of MySQL users and databases.
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
0
902822492a68 Initial population
dan@scribus.fuhry.local.fuhry.local
parents:
diff changeset
     1
-- Enano CMS - upgrade SQL
902822492a68 Initial population
dan@scribus.fuhry.local.fuhry.local
parents:
diff changeset
     2
-- Variables are in the format {{VAR_NAME}}
902822492a68 Initial population
dan@scribus.fuhry.local.fuhry.local
parents:
diff changeset
     3
-- ALL NON-SQL LINES, even otherwise blank lines, must start with "--" or they will get sent to MySQL!
902822492a68 Initial population
dan@scribus.fuhry.local.fuhry.local
parents:
diff changeset
     4
-- Common tasks (version numbers)
902822492a68 Initial population
dan@scribus.fuhry.local.fuhry.local
parents:
diff changeset
     5
DELETE FROM {{TABLE_PREFIX}}config WHERE config_name='enano_version' OR config_name='enano_beta_version' OR config_name='enano_alpha_version' OR config_name='enano_rc_version';
177
492510dddc34 Upgrade should work (untested)
Dan
parents: 156
diff changeset
     6
INSERT INTO {{TABLE_PREFIX}}config (config_name, config_value) VALUES( 'enano_version', '1.1.1' );
492510dddc34 Upgrade should work (untested)
Dan
parents: 156
diff changeset
     7
---BEGIN Stable1.0ToUnstable1.1---
179
36b287f1d85c [F] Added support for account lockouts. User is locked out or required to complete a CAPTCHA after specified threshold for specified period.
Dan
parents: 177
diff changeset
     8
-- UPDATE {{TABLE_PREFIX}}groups SET group_id=9998 WHERE group_id=4;
36b287f1d85c [F] Added support for account lockouts. User is locked out or required to complete a CAPTCHA after specified threshold for specified period.
Dan
parents: 177
diff changeset
     9
-- UPDATE {{TABLE_PREFIX}}group_members SET group_id=9998 WHERE group_id=4;
36b287f1d85c [F] Added support for account lockouts. User is locked out or required to complete a CAPTCHA after specified threshold for specified period.
Dan
parents: 177
diff changeset
    10
-- INSERT INTO {{TABLE_PREFIX}}groups(group_id,group_name,group_type,system_group) VALUES(4, 'Regular members', 3, 1);
36b287f1d85c [F] Added support for account lockouts. User is locked out or required to complete a CAPTCHA after specified threshold for specified period.
Dan
parents: 177
diff changeset
    11
CREATE TABLE {{TABLE_PREFIX}}lockout( id int(12) NOT NULL auto_increment, ipaddr varchar(40) NOT NULL, action ENUM('credential', 'level') NOT NULL DEFAULT 'credential', timestamp int(12) NOT NULL DEFAULT 0, PRIMARY KEY ( id ) ) CHARACTER SET `utf8`;
210
2b283402e4e4 Added language export to JSON page and localization for Javascript using $lang.get(). Localized AJAX login interface.
Dan
parents: 209
diff changeset
    12
CREATE TABLE {{TABLE_PREFIX}}language( lang_id smallint(5) NOT NULL auto_increment, lang_code varchar(16) NOT NULL, lang_name_default varchar(64) NOT NULL, lang_name_native varchar(64) NOT NULL, last_changed int(12) NOT NULL DEFAULT 0, PRIMARY KEY ( lang_id ) ) CHARACTER SET `utf8`;
209
8a00247d1dee Login page mostly localized
Dan
parents: 180
diff changeset
    13
CREATE TABLE {{TABLE_PREFIX}}language_strings( string_id bigint(15) NOT NULL auto_increment, lang_id smallint(5) NOT NULL, string_category varchar(32) NOT NULL, string_name varchar(64) NOT NULL, string_content longtext NOT NULL, PRIMARY KEY ( string_id ) );
8a00247d1dee Login page mostly localized
Dan
parents: 180
diff changeset
    14
ALTER TABLE {{TABLE_PREFIX}}users ADD COLUMN user_lang smallint(5) NOT NULL;
328
dc838fd61a06 Added initial avatar support. Currently rather feature complete except for admin controls for avatar.
Dan
parents: 326
diff changeset
    15
ALTER TABLE {{TABLE_PREFIX}}users ADD COLUMN user_has_avatar tinyint(1) NOT NULL;
dc838fd61a06 Added initial avatar support. Currently rather feature complete except for admin controls for avatar.
Dan
parents: 326
diff changeset
    16
ALTER TABLE {{TABLE_PREFIX}}users ADD COLUMN avatar_type ENUM('jpg', 'png', 'gif') NOT NULL;
177
492510dddc34 Upgrade should work (untested)
Dan
parents: 156
diff changeset
    17
---END Stable1.0ToUnstable1.1---
317
f8356d9c3481 Rebrand as 1.0.3 (Dyrad)
Dan
parents: 301
diff changeset
    18
---BEGIN 1.0.2---
f8356d9c3481 Rebrand as 1.0.3 (Dyrad)
Dan
parents: 301
diff changeset
    19
-- No DB changes in this release
f8356d9c3481 Rebrand as 1.0.3 (Dyrad)
Dan
parents: 301
diff changeset
    20
---END 1.0.2---
149
42c6c83b8a00 Rebrand for 1.0.2 (again, lol)
Dan
parents: 142
diff changeset
    21
---BEGIN 1.0.2b1---
156
edbff85d43e8 Feature add: new page group type: regular expression match (PCRE)
Dan
parents: 153
diff changeset
    22
-- This is really optional, but could reduce confusion if regex page groups get truncated for no apparent reason.
edbff85d43e8 Feature add: new page group type: regular expression match (PCRE)
Dan
parents: 153
diff changeset
    23
ALTER TABLE {{TABLE_PREFIX}}page_groups MODIFY COLUMN pg_target text DEFAULT NULL;
272
e0ec986c0af3 Searching sucks, and Enano's search algorithm was complete bullcrap. So I rewrote it. No, it does not use Google search technology. Like they have a patent for using the Arial font on search result pages anyway.
Dan
parents: 264
diff changeset
    24
-- I have no idea how or why, but the f'ing index didn't get created for who-knows-how-many releases.
e0ec986c0af3 Searching sucks, and Enano's search algorithm was complete bullcrap. So I rewrote it. No, it does not use Google search technology. Like they have a patent for using the Arial font on search result pages anyway.
Dan
parents: 264
diff changeset
    25
-- We'll attempt to create it here, but don't die if it fails
291
a1d0846c4504 A couple of minor tweaks to the upgrade schema; tests pending
Dan
parents: 285
diff changeset
    26
@ALTER TABLE {{TABLE_PREFIX}}page_text ENGINE = MYISAM, COLLATE = utf8_bin, CHARSET = utf8;
a1d0846c4504 A couple of minor tweaks to the upgrade schema; tests pending
Dan
parents: 285
diff changeset
    27
ALTER TABLE {{TABLE_PREFIX}}search_index CHARSET = utf8, COLLATE = utf8_bin, MODIFY COLUMN word varchar(64) NOT NULL;
301
7e094a04d84e Updated artwork with finalized new/revised logo; finalized upgrade schema
Dan
parents: 297
diff changeset
    28
-- The search cache is no longer needed because of the new unified search engine (it's too f'ing fast to need a cache :-D)
272
e0ec986c0af3 Searching sucks, and Enano's search algorithm was complete bullcrap. So I rewrote it. No, it does not use Google search technology. Like they have a patent for using the Arial font on search result pages anyway.
Dan
parents: 264
diff changeset
    29
@DROP TABLE {{TABLE_PREFIX}}search_cache;
285
7846d45bd250 Changed all urlname/page_id columns to varchar(255) because 63 characters just isn't long enough
Dan
parents: 272
diff changeset
    30
-- Yes, it appears we need pages with names this long after all
7846d45bd250 Changed all urlname/page_id columns to varchar(255) because 63 characters just isn't long enough
Dan
parents: 272
diff changeset
    31
ALTER TABLE {{TABLE_PREFIX}}pages MODIFY COLUMN urlname varchar(255), MODIFY COLUMN name varchar(255);
292
b3cfaf0a505c Fixed highlighting in search results; changed search algorithm to give more score for terms found in page title; hopefully (hackishly) fixed login_key_cache getting too long
Dan
parents: 291
diff changeset
    32
ALTER TABLE {{TABLE_PREFIX}}page_text MODIFY COLUMN page_id varchar(255), MODIFY COLUMN namespace varchar(63), MODIFY COLUMN page_text longtext;
b3cfaf0a505c Fixed highlighting in search results; changed search algorithm to give more score for terms found in page title; hopefully (hackishly) fixed login_key_cache getting too long
Dan
parents: 291
diff changeset
    33
@CREATE FULLTEXT INDEX {{TABLE_PREFIX}}page_search_idx ON {{TABLE_PREFIX}}page_text(page_id, namespace, page_text);
297
ee1fc84f12a8 SECURITY: Tighten default allowed file types; make sure search index rebuild is performed on upgrade
Dan
parents: 292
diff changeset
    34
UPDATE {{TABLE_PREFIX}}config SET config_value='cbf:len=185;crc=55fb6f14;data=0[1],1[4],0[3],1[1],0[22],1[1],0[16],1[3],0[16],1[1],0[1],1[2],0[6],1[1],0[1],1[1],0[4],1[2],0[3],1[1],0[48],1[2],0[2],1[1],0[4],1[1],0[37]|end' WHERE config_name = 'allowed_mime_types' AND config_value='cbf:len=168;crc=c3dcad3f;data=0[1],1[4],0[3],1[1],0[2],1[1],0[11],1[1],0[7],1[1],0[9],1[1],0[6],1[3],0[10],1[1],0[2],1[2],0[1],1[1],0[1],1[2],0[6],1[3],0[1],1[1],0[2],1[4],0[1],1[2],0[3],1[1],0[4],1[2],0[26],1[5],0[6],1[2],0[2],1[1],0[4],1[1],0[10],1[2],0[1],1[1],0[6]|end';
301
7e094a04d84e Updated artwork with finalized new/revised logo; finalized upgrade schema
Dan
parents: 297
diff changeset
    35
@UPDATE {{TABLE_PREFIX}}users SET theme='oxygen',style='bleu' WHERE user_id = 2;
149
42c6c83b8a00 Rebrand for 1.0.2 (again, lol)
Dan
parents: 142
diff changeset
    36
---END 1.0.2b1---
142
ca9118d9c0f2 Rebrand as 1.0.2 (Coblynau); internal links are now parsed by RenderMan::parse_internal_links()
Dan
parents: 129
diff changeset
    37
---BEGIN 1.0.1.1---
ca9118d9c0f2 Rebrand as 1.0.2 (Coblynau); internal links are now parsed by RenderMan::parse_internal_links()
Dan
parents: 129
diff changeset
    38
---END 1.0.1.1---
129
0b5244001799 Rebranded as 1.0.1.1; fixed category page drawing bug; updated link to GPL in the about page to the GPLv2
Dan
parents: 123
diff changeset
    39
---BEGIN 1.0.1---
0b5244001799 Rebranded as 1.0.1.1; fixed category page drawing bug; updated link to GPL in the about page to the GPLv2
Dan
parents: 123
diff changeset
    40
---END 1.0.1---
73
0a74676a2f2f Made the move to Loch Ness, and got some basic page grouping functionality working. TODO: fix some UI issues in Javascript ACL editor and change non-JS ACL editor to work with page groups too
Dan
parents: 64
diff changeset
    41
---BEGIN 1.0---
115
261f367623af Fixed the obnoxious issue with forms using GET and index.php?title=Foo URL scheme (this works a whole lot better than MediaWiki now
Dan
parents: 106
diff changeset
    42
-- Fix for obnoxious $_GET issue
123
8df3abef6647 Fixed the two known issues in the release notes; tarballs will be updated accordingly
Dan
parents: 119
diff changeset
    43
UPDATE {{TABLE_PREFIX}}sidebar SET block_type=1,block_content='<div class="slideblock2" style="padding: 0px;"><form action="$CONTENTPATH$$NS_SPECIAL$Search" method="get" style="padding: 0; margin: 0;"><p><input type="hidden" name="title" value="$NS_SPECIAL$Search" />$INPUT_AUTH$<input name="q" alt="Search box" type="text" size="10" style="width: 70%" /> <input type="submit" value="Go" style="width: 20%" /></p></form></div>' WHERE block_name='Search' AND item_id=4;
104
9c17aacd5515 Added ACL rule preventing guests from viewing the memberlist (by request from Neal Gompa <ngompa13 at gmail>)
Dan
parents: 96
diff changeset
    44
-- Added on advice from Neal
272
e0ec986c0af3 Searching sucks, and Enano's search algorithm was complete bullcrap. So I rewrote it. No, it does not use Google search technology. Like they have a patent for using the Arial font on search result pages anyway.
Dan
parents: 264
diff changeset
    45
-- Remember that 1 = AUTH_DENY.
104
9c17aacd5515 Added ACL rule preventing guests from viewing the memberlist (by request from Neal Gompa <ngompa13 at gmail>)
Dan
parents: 96
diff changeset
    46
INSERT INTO {{TABLE_PREFIX}}acl(target_type,target_id,page_id,namespace,rules) VALUES(2,1,'Memberlist','Special','read=1;mod_misc=1;upload_files=1;upload_new_version=1;create_page=1;edit_acl=1;');
94
8fc958168261 Fixed delvote_ips bug with MySQL 5.0.45 - see http://forum.enanocms.org/viewtopic.php?f=5&t=8
Dan
parents: 91
diff changeset
    47
-- Bugfix for MySQL 5.0.45, see http://forum.enanocms.org/viewtopic.php?f=5&t=8
106
329738fefe7f Second attempt at making delvote_ips work under MySQL 5.0.45 and 5.0.41
Dan
parents: 104
diff changeset
    48
ALTER TABLE {{TABLE_PREFIX}}pages MODIFY COLUMN delvote_ips text DEFAULT NULL;
73
0a74676a2f2f Made the move to Loch Ness, and got some basic page grouping functionality working. TODO: fix some UI issues in Javascript ACL editor and change non-JS ACL editor to work with page groups too
Dan
parents: 64
diff changeset
    49
CREATE TABLE {{TABLE_PREFIX}}page_groups( pg_id mediumint(8) NOT NULL auto_increment, pg_type tinyint(2) NOT NULL DEFAULT 1, pg_name varchar(255) NOT NULL DEFAULT '', pg_target varchar(255) DEFAULT NULL, PRIMARY KEY ( pg_id ) ) CHARACTER SET `utf8` COLLATE `utf8_bin`;
0a74676a2f2f Made the move to Loch Ness, and got some basic page grouping functionality working. TODO: fix some UI issues in Javascript ACL editor and change non-JS ACL editor to work with page groups too
Dan
parents: 64
diff changeset
    50
CREATE TABLE {{TABLE_PREFIX}}page_group_members( pg_member_id int(12) NOT NULL auto_increment, pg_id mediumint(8) NOT NULL, page_id varchar(63) NOT NULL, namespace varchar(63) NOT NULL DEFAULT 'Article', PRIMARY KEY ( pg_member_id ) ) CHARACTER SET `utf8` COLLATE `utf8_bin`;
76
608dee512bf0 Work started on page tags, still aways to go, but syncing to Nighthawk
Dan
parents: 73
diff changeset
    51
CREATE TABLE {{TABLE_PREFIX}}tags( tag_id int(12) NOT NULL auto_increment, tag_name varchar(63) NOT NULL DEFAULT 'bla', page_id varchar(255) NOT NULL, namespace varchar(255) NOT NULL, user mediumint(8) NOT NULL DEFAULT 1, PRIMARY KEY ( tag_id ) ) CHARACTER SET `utf8` COLLATE `utf8_bin`;
608dee512bf0 Work started on page tags, still aways to go, but syncing to Nighthawk
Dan
parents: 73
diff changeset
    52
UPDATE {{TABLE_PREFIX}}acl SET rules=CONCAT(rules,'tag_create=4;tag_delete_own=4;tag_delete_other=4;') WHERE target_type=1 AND target_id=2;
90
9d29f7e101d6 Fixed yet another minor XSS hole, this time in search results
Dan
parents: 87
diff changeset
    53
DELETE FROM {{TABLE_PREFIX}}search_cache;
73
0a74676a2f2f Made the move to Loch Ness, and got some basic page grouping functionality working. TODO: fix some UI issues in Javascript ACL editor and change non-JS ACL editor to work with page groups too
Dan
parents: 64
diff changeset
    54
---END 1.0---
21
663fcf528726 Updated all version numbers back to Banshee; a few preliminary steps towards full UTF-8 support in page URLs
Dan
parents: 18
diff changeset
    55
---BEGIN 1.0RC3---
49
ef92ded52f79 Fixed typo in upgrade schema (1.0RC3 -> 1.0)
Dan
parents: 33
diff changeset
    56
ALTER TABLE {{TABLE_PREFIX}}users ADD COLUMN user_coppa tinyint(1) NOT NULL DEFAULT 0;
64
60e450d2e8c3 Fixed typo in RC3->1.0 upgrade schema
Dan
parents: 60
diff changeset
    57
UPDATE {{TABLE_PREFIX}}sidebar SET block_content='[[$NS_SPECIAL$CreatePage|Create a page]]\n[[$NS_SPECIAL$UploadFile|Upload file]]\n[[$NS_SPECIAL$SpecialPages|Special pages]]\n{if auth_admin}\n[[$NS_SPECIAL$EditSidebar|Edit the sidebar]]\n$ADMIN_LINK$\n{/if}' WHERE item_id=2;
50
5e9bb8b7bbdf Fixed several more bugs in the upgrade schema
Dan
parents: 49
diff changeset
    58
UPDATE {{TABLE_PREFIX}}sidebar SET block_content='[[User:$USERNAME$|User page]]\n[[Special:Contributions/$USERNAME$|My Contributions]]\n{if user_logged_in}\n[[$NS_SPECIAL$Preferences|Preferences]]\n[[Special:PrivateMessages|Private messages ($UNREAD_PMS$)]]\n[[Special:Usergroups|Group control panel]]\n$THEME_LINK$\n{/if}\n{if user_logged_in}\n$LOGOUT_LINK$\n{else}\n[[Special:Register|Create an account]]\n$LOGIN_LINK$\n[[Special:Login/Special:PrivateMessages|Private messages]]\n{/if}' WHERE item_id=3;
51
436e7a3a4013 Fixed even more bugs in the upgrade schema (there may be a number of commits like this, as we are testing the upgrade script on a temp server)
Dan
parents: 50
diff changeset
    59
-- Updated PHP-ized search box
436e7a3a4013 Fixed even more bugs in the upgrade schema (there may be a number of commits like this, as we are testing the upgrade script on a temp server)
Dan
parents: 50
diff changeset
    60
-- block_type=3: 3 = BLOCK_PHP
436e7a3a4013 Fixed even more bugs in the upgrade schema (there may be a number of commits like this, as we are testing the upgrade script on a temp server)
Dan
parents: 50
diff changeset
    61
UPDATE {{TABLE_PREFIX}}sidebar SET block_content='?><div class=\"slideblock2\" style=\"padding: 0px;\"><form action=\"<?php echo makeUrlNS(\'Special\', \'Search\'); ?>\" method=\"get\"><input name=\"q\" alt=\"Search box\" type=\"text\" size=\"10\" style=\"width: 70%\" /> <input type=\"submit\" value=\"Go\" style=\"width: 20%\" /></form></div>',block_type=3 WHERE block_name='Search';
21
663fcf528726 Updated all version numbers back to Banshee; a few preliminary steps towards full UTF-8 support in page URLs
Dan
parents: 18
diff changeset
    62
---END 1.0RC3---
0
902822492a68 Initial population
dan@scribus.fuhry.local.fuhry.local
parents:
diff changeset
    63
---BEGIN 1.0RC2---
902822492a68 Initial population
dan@scribus.fuhry.local.fuhry.local
parents:
diff changeset
    64
-- Add the "Moderators" group
902822492a68 Initial population
dan@scribus.fuhry.local.fuhry.local
parents:
diff changeset
    65
UPDATE {{TABLE_PREFIX}}groups SET group_id=9999 WHERE group_id=3;
902822492a68 Initial population
dan@scribus.fuhry.local.fuhry.local
parents:
diff changeset
    66
UPDATE {{TABLE_PREFIX}}group_members SET group_id=9999 WHERE group_id=3;
902822492a68 Initial population
dan@scribus.fuhry.local.fuhry.local
parents:
diff changeset
    67
ALTER TABLE {{TABLE_PREFIX}}groups ADD COLUMN system_group tinyint(1) NOT NULL DEFAULT 0;
902822492a68 Initial population
dan@scribus.fuhry.local.fuhry.local
parents:
diff changeset
    68
UPDATE {{TABLE_PREFIX}}groups SET system_group=1 WHERE group_id=1 OR group_id=2;
902822492a68 Initial population
dan@scribus.fuhry.local.fuhry.local
parents:
diff changeset
    69
INSERT INTO {{TABLE_PREFIX}}groups(group_id,group_name,group_type,system_group) VALUES(3, 'Moderators', 3, 1);
76
608dee512bf0 Work started on page tags, still aways to go, but syncing to Nighthawk
Dan
parents: 73
diff changeset
    70
-- ...and add the associated ACL rule
608dee512bf0 Work started on page tags, still aways to go, but syncing to Nighthawk
Dan
parents: 73
diff changeset
    71
INSERT INTO {{TABLE_PREFIX}}acl(target_type,target_id,page_id,namespace,rules) VALUES(1,3,NULL,NULL,'read=4;post_comments=4;edit_comments=4;edit_page=4;view_source=4;mod_comments=4;history_view=4;history_rollback=4;history_rollback_extra=4;protect=4;rename=3;clear_logs=2;vote_delete=4;vote_reset=4;delete_page=4;set_wiki_mode=2;password_set=2;password_reset=2;mod_misc=2;edit_cat=4;even_when_protected=4;upload_files=2;upload_new_version=3;create_page=3;php_in_pages=2;edit_acl=2;');
60
71b50f8c8f85 Changed administration login request to use the AJAX login form; made high-level authentication more apparent in the AJAX box; recompiled Oxygen Mint
Dan
parents: 51
diff changeset
    72
-- Reset default user's theme to Oxygen, to emphasize stable release
71b50f8c8f85 Changed administration login request to use the AJAX login form; made high-level authentication more apparent in the AJAX box; recompiled Oxygen Mint
Dan
parents: 51
diff changeset
    73
UPDATE {{TABLE_PREFIX}}users SET theme='oxygen',style='bleu' WHERE user_id=1 OR user_id=2;
0
902822492a68 Initial population
dan@scribus.fuhry.local.fuhry.local
parents:
diff changeset
    74
-- Create table with extra user information
87
570f68c3fe36 Redid stupid fading button code and fixed several RC2 bugs in the upgrade schema; 1.0.1 release candidate
Dan
parents: 76
diff changeset
    75
CREATE TABLE {{TABLE_PREFIX}}users_extra( user_id mediumint(8) NOT NULL, user_aim varchar(63) default NULL, user_yahoo varchar(63) default NULL, user_msn varchar(255) default NULL, user_xmpp varchar(255) default NULL, user_homepage text, user_location text, user_job text, user_hobbies text, email_public tinyint(1) NOT NULL default '0', userpage_comments smallint(5) NOT NULL default '0', PRIMARY KEY ( user_id ) );
51
436e7a3a4013 Fixed even more bugs in the upgrade schema (there may be a number of commits like this, as we are testing the upgrade script on a temp server)
Dan
parents: 50
diff changeset
    76
-- Turn on the Enano button on the sidebar
436e7a3a4013 Fixed even more bugs in the upgrade schema (there may be a number of commits like this, as we are testing the upgrade script on a temp server)
Dan
parents: 50
diff changeset
    77
INSERT INTO {{TABLE_PREFIX}}config(config_name,config_value) VALUES('powered_btn', '1');
0
902822492a68 Initial population
dan@scribus.fuhry.local.fuhry.local
parents:
diff changeset
    78
---END 1.0RC2---
902822492a68 Initial population
dan@scribus.fuhry.local.fuhry.local
parents:
diff changeset
    79
---BEGIN 1.0RC1---
902822492a68 Initial population
dan@scribus.fuhry.local.fuhry.local
parents:
diff changeset
    80
-- Not too many DB changes in this release - that's a good sign ;-)
902822492a68 Initial population
dan@scribus.fuhry.local.fuhry.local
parents:
diff changeset
    81
ALTER TABLE {{TABLE_PREFIX}}search_index MODIFY COLUMN word varbinary(64) NOT NULL;
264
0fe1f610698d Well I'm an idiot - the fulltext index on page_text was missing from a default installation. It didn't break searches but probably slowed them down tremendously. Also set engine to MyISAM on page_text to avoid cryptic error messages from MySQL.
Dan
parents: 256
diff changeset
    82
-- This is to correct an issue encountered upgrading a stock Win2k3 + IIS setup
0fe1f610698d Well I'm an idiot - the fulltext index on page_text was missing from a default installation. It didn't break searches but probably slowed them down tremendously. Also set engine to MyISAM on page_text to avoid cryptic error messages from MySQL.
Dan
parents: 256
diff changeset
    83
ALTER TABLE {{TABLE_PREFIX}}page_text ENGINE = MYISAM;
0
902822492a68 Initial population
dan@scribus.fuhry.local.fuhry.local
parents:
diff changeset
    84
CREATE FULLTEXT INDEX {{TABLE_PREFIX}}page_search_idx ON {{TABLE_PREFIX}}page_text(page_id,namespace,page_text);
902822492a68 Initial population
dan@scribus.fuhry.local.fuhry.local
parents:
diff changeset
    85
UPDATE {{TABLE_PREFIX}}users SET user_level=3 WHERE user_level=2;
50
5e9bb8b7bbdf Fixed several more bugs in the upgrade schema
Dan
parents: 49
diff changeset
    86
UPDATE {{TABLE_PREFIX}}sidebar SET block_content='[[$NS_USER$$USERNAME$|User page]]\n[[$NS_SPECIAL$Contributions/$USERNAME$|My Contributions]]\n{if user_logged_in}\n[[$NS_SPECIAL$Preferences|Preferences]]\n[[$NS_SPECIAL$PrivateMessages|Private messages]]\n[[$NS_SPECIAL$Usergroups|Group control panel]]\n$THEME_LINK$\n{/if}\n{if user_logged_in}\n$LOGOUT_LINK$\n{else}\n[[$NS_SPECIAL$Register|Create an account]]\n$LOGIN_LINK$\n[[$NS_SPECIAL$Login/$NS_SPECIAL$PrivateMessages|Private messages]]\n{/if}',block_name='$USERNAME$' WHERE ( block_name='$USERNAME' OR block_name='$USERNAME$' ) AND item_id=3;
0
902822492a68 Initial population
dan@scribus.fuhry.local.fuhry.local
parents:
diff changeset
    87
---END 1.0RC1---
902822492a68 Initial population
dan@scribus.fuhry.local.fuhry.local
parents:
diff changeset
    88
---BEGIN 1.0b4---
902822492a68 Initial population
dan@scribus.fuhry.local.fuhry.local
parents:
diff changeset
    89
CREATE TABLE {{TABLE_PREFIX}}hits( hit_id bigint(20) NOT NULL auto_increment, username varchar(63) NOT NULL, time int(12) NOT NULL DEFAULT 0, page_id varchar(63), namespace varchar(63), PRIMARY KEY ( hit_id ) );
902822492a68 Initial population
dan@scribus.fuhry.local.fuhry.local
parents:
diff changeset
    90
CREATE TABLE {{TABLE_PREFIX}}search_index( word binary(32) NOT NULL, page_names text, PRIMARY KEY ( word ) );
902822492a68 Initial population
dan@scribus.fuhry.local.fuhry.local
parents:
diff changeset
    91
CREATE TABLE {{TABLE_PREFIX}}search_cache( search_id int(15) NOT NULL auto_increment, search_time int(11) NOT NULL, query text, results longblob, PRIMARY KEY ( search_id ));
902822492a68 Initial population
dan@scribus.fuhry.local.fuhry.local
parents:
diff changeset
    92
CREATE TABLE {{TABLE_PREFIX}}acl( rule_id int(12) UNSIGNED NOT NULL auto_increment, target_type tinyint(1) UNSIGNED NOT NULL, target_id int(12) UNSIGNED NOT NULL, page_id varchar(255), namespace varchar(24), rules text, PRIMARY KEY ( rule_id ) );
902822492a68 Initial population
dan@scribus.fuhry.local.fuhry.local
parents:
diff changeset
    93
ALTER TABLE  {{TABLE_PREFIX}}users ADD COLUMN old_encryption tinyint(1) NOT NULL DEFAULT 0;
902822492a68 Initial population
dan@scribus.fuhry.local.fuhry.local
parents:
diff changeset
    94
ALTER TABLE  {{TABLE_PREFIX}}users MODIFY COLUMN password text;
902822492a68 Initial population
dan@scribus.fuhry.local.fuhry.local
parents:
diff changeset
    95
ALTER TABLE  {{TABLE_PREFIX}}users ADD COLUMN temp_password text, ADD COLUMN temp_password_time int(12) NOT NULL DEFAULT 0;
902822492a68 Initial population
dan@scribus.fuhry.local.fuhry.local
parents:
diff changeset
    96
UPDATE {{TABLE_PREFIX}}users SET old_encryption=1;
902822492a68 Initial population
dan@scribus.fuhry.local.fuhry.local
parents:
diff changeset
    97
UPDATE {{TABLE_PREFIX}}users SET user_level=9 WHERE user_level=2;
902822492a68 Initial population
dan@scribus.fuhry.local.fuhry.local
parents:
diff changeset
    98
UPDATE {{TABLE_PREFIX}}users SET user_level=5 WHERE user_level=1;
902822492a68 Initial population
dan@scribus.fuhry.local.fuhry.local
parents:
diff changeset
    99
UPDATE {{TABLE_PREFIX}}users SET user_level=2 WHERE user_level=0;
902822492a68 Initial population
dan@scribus.fuhry.local.fuhry.local
parents:
diff changeset
   100
UPDATE {{TABLE_PREFIX}}users SET user_level=1 WHERE user_level=-1;
96
0b53c89a7d11 Fixed: when going beta 4 or earlier -> RC1 or later, default ACL rule for admins was not created, causing possibly confusing lockouts
Dan
parents: 94
diff changeset
   101
INSERT INTO {{TABLE_PREFIX}}acl(target_type,target_id,page_id,namespace,rules) VALUES(1,2,NULL,NULL,'read=4;post_comments=4;edit_comments=4;edit_page=4;view_source=4;mod_comments=4;history_view=4;history_rollback=4;history_rollback_extra=4;protect=4;rename=4;clear_logs=4;vote_delete=4;vote_reset=4;delete_page=4;tag_create=4;tag_delete_own=4;tag_delete_other=4;set_wiki_mode=4;password_set=4;password_reset=4;mod_misc=4;edit_cat=4;even_when_protected=4;upload_files=4;upload_new_version=4;create_page=4;php_in_pages={{ADMIN_EMBED_PHP}};edit_acl=4;');
0
902822492a68 Initial population
dan@scribus.fuhry.local.fuhry.local
parents:
diff changeset
   102
-- Group system
902822492a68 Initial population
dan@scribus.fuhry.local.fuhry.local
parents:
diff changeset
   103
CREATE TABLE {{TABLE_PREFIX}}groups( group_id mediumint(5) UNSIGNED NOT NULL auto_increment, group_name varchar(64), group_type tinyint(1) NOT NULL DEFAULT 1, PRIMARY KEY ( group_id ) );
902822492a68 Initial population
dan@scribus.fuhry.local.fuhry.local
parents:
diff changeset
   104
CREATE TABLE {{TABLE_PREFIX}}group_members( member_id int(12) UNSIGNED NOT NULL auto_increment, group_id mediumint(5) UNSIGNED NOT NULL, user_id int(12) NOT NULL, is_mod tinyint(1) NOT NULL DEFAULT 0, pending tinyint(1) NOT NULL DEFAULT 0, PRIMARY KEY ( member_id ) );
902822492a68 Initial population
dan@scribus.fuhry.local.fuhry.local
parents:
diff changeset
   105
INSERT INTO {{TABLE_PREFIX}}groups(group_id,group_name,group_type) VALUES(1, 'Everyone', 3),(2,'Administrators',3);
902822492a68 Initial population
dan@scribus.fuhry.local.fuhry.local
parents:
diff changeset
   106
-- Sidebar updates
902822492a68 Initial population
dan@scribus.fuhry.local.fuhry.local
parents:
diff changeset
   107
DELETE FROM {{TABLE_PREFIX}}sidebar WHERE item_id=5 AND block_name='Links';
902822492a68 Initial population
dan@scribus.fuhry.local.fuhry.local
parents:
diff changeset
   108
INSERT INTO {{TABLE_PREFIX}}sidebar(item_order, sidebar_id, block_name, block_type, block_content) VALUES(2, 2, 'Links', 4, 'Links');
902822492a68 Initial population
dan@scribus.fuhry.local.fuhry.local
parents:
diff changeset
   109
UPDATE {{TABLE_PREFIX}}sidebar SET block_content='[[$NS_USER$$USERNAME$|User page]]\n[[$NS_SPECIAL$Contributions/$USERNAME$|My Contributions]]\n{if user_logged_in}\n[[$NS_SPECIAL$Preferences|Preferences]]\n[[$NS_SPECIAL$PrivateMessages|Private messages]]\n[[$NS_SPECIAL$Usergroups|Group control panel]]\n$THEME_LINK$\n{/if}\n{if user_logged_in}\n$LOGOUT_LINK$\n{else}\n[[$NS_SPECIAL$Register|Create an account]]\n[[$NS_SPECIAL$Login/$PAGE_URLNAME$|Log in]]\n[[$NS_SPECIAL$Login/$NS_SPECIAL$PrivateMessages|Private messages]]\n{/if}' WHERE block_name='$USERNAME$' AND item_id=3;
902822492a68 Initial population
dan@scribus.fuhry.local.fuhry.local
parents:
diff changeset
   110
UPDATE {{TABLE_PREFIX}}sidebar SET block_name='$USERNAME$' WHERE block_name='$USERNAME';
902822492a68 Initial population
dan@scribus.fuhry.local.fuhry.local
parents:
diff changeset
   111
-- Set the default theme
902822492a68 Initial population
dan@scribus.fuhry.local.fuhry.local
parents:
diff changeset
   112
INSERT INTO {{TABLE_PREFIX}}themes(theme_id,theme_name,theme_order,default_style,enabled) VALUES('stpatty', 'St. Patty', 1, 'shamrock.css', 1);
902822492a68 Initial population
dan@scribus.fuhry.local.fuhry.local
parents:
diff changeset
   113
UPDATE {{TABLE_PREFIX}}themes SET theme_order=2 WHERE theme_id='oxygen';
902822492a68 Initial population
dan@scribus.fuhry.local.fuhry.local
parents:
diff changeset
   114
UPDATE {{TABLE_PREFIX}}users SET theme='stpatty',style='shamrock';
902822492a68 Initial population
dan@scribus.fuhry.local.fuhry.local
parents:
diff changeset
   115
---END 1.0b4---
902822492a68 Initial population
dan@scribus.fuhry.local.fuhry.local
parents:
diff changeset
   116
---BEGIN 1.0b3---
902822492a68 Initial population
dan@scribus.fuhry.local.fuhry.local
parents:
diff changeset
   117
INSERT INTO {{TABLE_PREFIX}}config(config_name, config_value) VALUES( 'allowed_mime_types', 'cbf:len=168;crc=c3dcad3f;data=0[1],1[4],0[3],1[1],0[2],1[1],0[11],1[1],0[7],1[1],0[9],1[1],0[6],1[3],0[10],1[1],0[2],1[2],0[1],1[1],0[1],1[2],0[6],1[3],0[1],1[1],0[2],1[4],0[1],1[2],0[3],1[1],0[4],1[2],0[26],1[5],0[6],1[2],0[2],1[1],0[4],1[1],0[10],1[2],0[1],1[1],0[6]|end' );
223
8877bbfccc38 F'ing private message message_read column...
Dan
parents: 156
diff changeset
   118
ALTER TABLE {{TABLE_PREFIX}}privmsgs ADD COLUMN message_read tinyint(1) NOT NULL DEFAULT 0;
0
902822492a68 Initial population
dan@scribus.fuhry.local.fuhry.local
parents:
diff changeset
   119
---END 1.0b3---
902822492a68 Initial population
dan@scribus.fuhry.local.fuhry.local
parents:
diff changeset
   120
---BEGIN 1.0b2---
902822492a68 Initial population
dan@scribus.fuhry.local.fuhry.local
parents:
diff changeset
   121
-- 10/1: Removed alterations to users table, moved to upgrade.php, to allow the session manager to work
902822492a68 Initial population
dan@scribus.fuhry.local.fuhry.local
parents:
diff changeset
   122
CREATE TABLE {{TABLE_PREFIX}}privmsgs( message_id int(15) NOT NULL auto_increment, message_from varchar(63), message_to varchar(255), date int(12), subject varchar(63), message_text text, folder_name varchar(63), PRIMARY KEY (message_id) );
902822492a68 Initial population
dan@scribus.fuhry.local.fuhry.local
parents:
diff changeset
   123
CREATE TABLE {{TABLE_PREFIX}}buddies( buddy_id int(15) NOT NULL auto_increment, user_id mediumint(8), buddy_user_id mediumint(8), is_friend tinyint(1) NOT NULL DEFAULT 1, PRIMARY KEY (buddy_id) );
902822492a68 Initial population
dan@scribus.fuhry.local.fuhry.local
parents:
diff changeset
   124
-- Fill 'em up with a basic sidebar - sometime there will be a migration script that will convert the old sidebar format to the new
902822492a68 Initial population
dan@scribus.fuhry.local.fuhry.local
parents:
diff changeset
   125
CREATE TABLE {{TABLE_PREFIX}}sidebar( item_id smallint(3) NOT NULL auto_increment, item_order smallint(3) NOT NULL DEFAULT 0, sidebar_id smallint(3) NOT NULL DEFAULT 1, block_name varchar(63) NOT NULL, block_type tinyint(1) NOT NULL DEFAULT 0, item_enabled tinyint(1) NOT NULL DEFAULT 1, block_content text, PRIMARY KEY ( item_id ));
902822492a68 Initial population
dan@scribus.fuhry.local.fuhry.local
parents:
diff changeset
   126
INSERT INTO {{TABLE_PREFIX}}sidebar(item_id, item_order, sidebar_id, block_name, block_type, block_content) VALUES (1, 1, 1, 'Navigation', 1, '[[Main Page|Home]]'),(2, 2, 1, 'Tools', 1, '[[Special:CreatePage|Create a page]]\n[[Special:UploadFile|Upload file]]\n[[Special:SpecialPages|Special pages]]\n{if auth_admin}\n[[Special:EditSidebar|Edit the sidebar]]\n[[Special:Administration|Administration]]\n{/if}'),(3, 3, 1, '$USERNAME$', 1, '[[User:$USERNAME$|User page]]\n[[Special:Contributions/$USERNAME$|My Contributions]]\n{if user_logged_in}\n[[Special:Preferences|Preferences]]\n[[Special:PrivateMessages|Private messages]]\n$THEME_LINK$\n{/if}\n{if user_logged_in}\n$LOGOUT_LINK$\n{else}\n[[Special:Register|Create an account]]\n[[Special:Login/$PAGE_URLNAME$|Log in]]\n[[Special:Login/Special:PrivateMessages|Private messages]]\n{/if}'),(4, 4, 1, 'Search', 1, '<div class="slideblock2" style="padding: 3px;"><form action="$SCRIPTPATH$/Special:Search" method="get" style="padding: 0; margin: 0;"><p><input name="q" alt="Search box" type="text" size="10" style="width: 70%" /> <input type="submit" value="Go" style="width: 20%" /></p></form></div>'),(5, 2, 2, 'Links', 3, '$ob = Array();\nif(getConfig(''sflogo_enabled'')==''1'')\n{\n  $ob[] = ''<a style="text-align: center;" href="http://sourceforge.net/" onclick="window.open(this.href);return false;"><img border="0" alt="SourceForge.net Logo" src="http://sflogo.sourceforge.net/sflogo.php?group_id=''.getConfig(''sflogo_groupid'').''&type=''.getConfig(''sflogo_type'').''" /></a>'';\n}\nif(getConfig(''w3c_v32'')     ==''1'') $ob[] = ''<a style="text-align: center;" href="http://validator.w3.org/check?uri=referer" onclick="window.open(this.href);return false;"><img style="border: 0px solid #FFFFFF;" alt="Valid HTML 3.2" src="http://validator.w3.org/images/v32" /></a>'';\nif(getConfig(''w3c_v40'')     ==''1'') $ob[] = ''<a style="text-align: center;" href="http://validator.w3.org/check?uri=referer" onclick="window.open(this.href);return false;"><img style="border: 0px solid #FFFFFF;" alt="Valid HTML 4.0" src="http://validator.w3.org/images/v40" /></a>'';\nif(getConfig(''w3c_v401'')    ==''1'') $ob[] = ''<a style="text-align: center;" href="http://validator.w3.org/check?uri=referer" onclick="window.open(this.href);return false;"><img style="border: 0px solid #FFFFFF;" alt="Valid HTML 4.01" src="http://validator.w3.org/images/v401" /></a>'';\nif(getConfig(''w3c_vxhtml10'')==''1'') $ob[] = ''<a style="text-align: center;" href="http://validator.w3.org/check?uri=referer" onclick="window.open(this.href);return false;"><img style="border: 0px solid #FFFFFF;" alt="Valid XHTML 1.0" src="http://validator.w3.org/images/vxhtml10" /></a>'';\nif(getConfig(''w3c_vxhtml11'')==''1'') $ob[] = ''<a style="text-align: center;" href="http://validator.w3.org/check?uri=referer" onclick="window.open(this.href);return false;"><img style="border: 0px solid #FFFFFF;" alt="Valid XHTML 1.1" src="http://validator.w3.org/images/vxhtml11" /></a>'';\nif(getConfig(''w3c_vcss'')    ==''1'') $ob[] = ''<a style="text-align: center;" href="http://validator.w3.org/check?uri=referer" onclick="window.open(this.href);return false;"><img style="border: 0px solid #FFFFFF;" alt="Valid CSS" src="http://validator.w3.org/images/vcss" /></a>'';\nif(getConfig(''dbd_button'')  ==''1'') $ob[] = ''<a style="text-align: center;" href="http://www.defectivebydesign.org/join/button" onclick="window.open(this.href);return false;"><img style="border: 0px solid #FFFFFF;" alt="DRM technology restricts what you can do with your computer" src="http://defectivebydesign.org/sites/nodrm.civicactions.net/files/images/dbd_sm_btn.gif" /><br /><small>Protect your freedom &gt;&gt;</small></a>'';\nif(count($ob) > 0) echo ''<div style="text-align: center; padding: 5px;">''.implode(''<br />'', $ob).''</div>'';');
902822492a68 Initial population
dan@scribus.fuhry.local.fuhry.local
parents:
diff changeset
   127
ALTER TABLE {{TABLE_PREFIX}}banlist ADD COLUMN reason text;
902822492a68 Initial population
dan@scribus.fuhry.local.fuhry.local
parents:
diff changeset
   128
-- Here's a tricky one for ya :-/ what we're trying to do is add an auto-increment primary key to a table, this was a first for me but it seemed to work, tested on MySQL 4.1.20
902822492a68 Initial population
dan@scribus.fuhry.local.fuhry.local
parents:
diff changeset
   129
ALTER TABLE {{TABLE_PREFIX}}comments ADD COLUMN comment_id int(12) NOT NULL auto_increment FIRST, ADD PRIMARY KEY ( comment_id );
902822492a68 Initial population
dan@scribus.fuhry.local.fuhry.local
parents:
diff changeset
   130
-- Session manager stuff
902822492a68 Initial population
dan@scribus.fuhry.local.fuhry.local
parents:
diff changeset
   131
ALTER TABLE {{TABLE_PREFIX}}themes ADD COLUMN default_style varchar(63) NOT NULL DEFAULT '';
902822492a68 Initial population
dan@scribus.fuhry.local.fuhry.local
parents:
diff changeset
   132
ALTER TABLE {{TABLE_PREFIX}}users ADD COLUMN signature text;
902822492a68 Initial population
dan@scribus.fuhry.local.fuhry.local
parents:
diff changeset
   133
ALTER TABLE {{TABLE_PREFIX}}users ADD COLUMN reg_time int(11) NOT NULL DEFAULT 0;
902822492a68 Initial population
dan@scribus.fuhry.local.fuhry.local
parents:
diff changeset
   134
ALTER TABLE {{TABLE_PREFIX}}users ADD COLUMN account_active tinyint(1) NOT NULL DEFAULT 0;
902822492a68 Initial population
dan@scribus.fuhry.local.fuhry.local
parents:
diff changeset
   135
ALTER TABLE {{TABLE_PREFIX}}users ADD COLUMN activation_key varchar(40) NOT NULL DEFAULT 0;
902822492a68 Initial population
dan@scribus.fuhry.local.fuhry.local
parents:
diff changeset
   136
UPDATE {{TABLE_PREFIX}}users SET account_active=1;
902822492a68 Initial population
dan@scribus.fuhry.local.fuhry.local
parents:
diff changeset
   137
UPDATE {{TABLE_PREFIX}}themes SET default_style='bleu.css' WHERE theme_id='oxygen';
902822492a68 Initial population
dan@scribus.fuhry.local.fuhry.local
parents:
diff changeset
   138
---END 1.0b2---
902822492a68 Initial population
dan@scribus.fuhry.local.fuhry.local
parents:
diff changeset
   139
---BEGIN 1.0b1---
902822492a68 Initial population
dan@scribus.fuhry.local.fuhry.local
parents:
diff changeset
   140
CREATE TABLE {{TABLE_PREFIX}}files( time_id int(12) NOT NULL, page_id varchar(63) NOT NULL, filename varchar(127), size bigint(15) NOT NULL, mimetype varchar(63), file_extension varchar(8), data longblob, PRIMARY KEY (time_id) );
902822492a68 Initial population
dan@scribus.fuhry.local.fuhry.local
parents:
diff changeset
   141
ALTER TABLE {{TABLE_PREFIX}}pages MODIFY COLUMN protected tinyint(1) NOT NULL DEFAULT 0;
902822492a68 Initial population
dan@scribus.fuhry.local.fuhry.local
parents:
diff changeset
   142
ALTER TABLE {{TABLE_PREFIX}}pages ADD COLUMN wiki_mode tinyint(1) NOT NULL DEFAULT 2 AFTER protected;
902822492a68 Initial population
dan@scribus.fuhry.local.fuhry.local
parents:
diff changeset
   143
ALTER TABLE {{TABLE_PREFIX}}pages ADD COLUMN password varchar(40) NOT NULL DEFAULT '' AFTER wiki_mode;
902822492a68 Initial population
dan@scribus.fuhry.local.fuhry.local
parents:
diff changeset
   144
ALTER TABLE {{TABLE_PREFIX}}comments ADD COLUMN user_id mediumint(8) NOT NULL DEFAULT -1;
902822492a68 Initial population
dan@scribus.fuhry.local.fuhry.local
parents:
diff changeset
   145
ALTER TABLE {{TABLE_PREFIX}}comments ADD COLUMN time int(12) NOT NULL default 0;
902822492a68 Initial population
dan@scribus.fuhry.local.fuhry.local
parents:
diff changeset
   146
UPDATE {{TABLE_PREFIX}}pages SET wiki_mode=2;
902822492a68 Initial population
dan@scribus.fuhry.local.fuhry.local
parents:
diff changeset
   147
UPDATE {{TABLE_PREFIX}}comments SET user_id=-1;
902822492a68 Initial population
dan@scribus.fuhry.local.fuhry.local
parents:
diff changeset
   148
---END 1.0b1---