/
upgrades
224 lines (181 loc) · 9.59 KB
/
upgrades
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
# This is the upgrade file for SQL. What does this mean to you?
# If you are a developer you should leave notes here as to what
# was done and when. Tihs include inserts, alter tables and deletions.
# The Format is that you add your changes to the end with notes
# and who you are. If your changes occur after a certain release tag
# then you need to place your changes there.
# LCR 2/7, 10PM T_2_3_0_9
# To fix the data in users to add the + sign to positive values -Brian
UPDATE users_param SET value = CONCAT("+",value) WHERE (name LIKE "reason%" OR name LIKE "people_bonus%") AND value LIKE "_";
# Upgrade Zoo to latest schema, once you do this run the rebuildPeople script
# in the plugin. -Brian
CREATE TABLE temp_zoo SELECT * FROM people;
DROP TABLE IF EXISTS people;
CREATE TABLE people (
id MEDIUMINT UNSIGNED NOT NULL auto_increment,
uid MEDIUMINT UNSIGNED NOT NULL,
person MEDIUMINT UNSIGNED NOT NULL,
type enum("friend","foe"),
perceive enum("fan","freak"),
fof MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,
eof MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,
UNIQUE degree_of_separation (uid,person),
PRIMARY KEY (id)
);
INSERT INTO people (uid,person,type) SELECT uid, person, type from temp_zoo;
# This was removed from the bonus system a while ago. -Brian
ALTER TABLE users_comments DROP anon_comments;
# Changes for sections
# Make sure you run install-plugin for sections and delete the old sections.pl from your
# slashcode theme. -Brian
ALTER TABLE sections ADD url char(32) DEFAULT '' NOT NULL;
ALTER TABLE sections ADD hostname char(32)DEFAULT '' NOT NULL;
# Well we didn't need either of these two indexes. We got the same data from two
# other rows. -Brian
ALTER TABLE comments DROP INDEX uid;
ALTER TABLE comments DROP INDEX sid;
# More key stuff. We had a number of duplicate keys in
# users. I should really spend time and go through the
# entire schema. -Brian
ALTER TABLE users DROP INDEX login;
ALTER TABLE users ADD INDEX login (nickname,uid,passwd);
ALTER TABLE users DROP INDEX chk4user;
ALTER TABLE users ADD INDEX chk4user (realemail,nickname);
ALTER TABLE users DROP INDEX nickname_lookup;
ALTER TABLE users DROP INDEX chk4email;
# Few more.
ALTER TABLE moderatorlog DROP INDEX sid_2;
ALTER TABLE moderatorlog ADD INDEX sid_2 (cid,uid,sid);
ALTER TABLE moderatorlog DROP INDEX cid;
# new multi topics stuff
DROP TABLE IF EXISTS story_topics;
CREATE TABLE story_topics (
id int(5) NOT NULL auto_increment,
sid varchar(16) NOT NULL default '',
tid smallint(5) unsigned default NULL,
FOREIGN KEY (sid) REFERENCES stories(sid),
FOREIGN KEY (tid) REFERENCES topics(tid),
PRIMARY KEY (id),
INDEX sid (sid),
INDEX tid (tid)
) TYPE=MyISAM;
# New var to regulate comment display - Jamie
INSERT INTO vars (name, value, description) VALUES ('comments_max_email_len','40','Max num of chars of fakeemail to display in comment header');
#______________________________________________________________________
# End of T_2_3_0_10
#______________________________________________________________________
# New attribute for stories
ALTER TABLE stories ADD day_published date DEFAULT '0000-00-00' NOT NULL;
ALTER TABLE stories ADD INDEX published (day_published);
UPDATE stories SET day_published=time;
INSERT INTO vars (name, value, description) VALUES ('comments_moddable_archived','0','Are comments in discussions that have been archived moderatable?');
INSERT INTO vars (name, value, description) VALUES ('comments_moddable_hours','336','Num hours after being posted that a comment may be moderated');
#
# Start of R_2_3_0_13
#
# This was added to sql/mysql/slashschema_create.sql ... but is NOT yet live.
# Brian says "list" is being yanked and "type" is part of code that PatG
# hasn't yet committed, so I'm ignoring it for now... this is here in
# upgrades as a reminder.
# added new column to section_extras to accomodate pulldows
# (key/values stored in string_param)
ALTER TABLE section_extras add column type enum("text","list") DEFAULT 'text' NOT NULL;
ALTER TABLE string_param modify column code varchar(32) NOT NULL;
# End of R_2_3_0_13
# Start of T_2_3_0_14
ALTER TABLE users_hits ADD COLUMN hits_bought_today SMALLINT UNSIGNED DEFAULT 0 NOT NULL AFTER hits_bought;
ALTER TABLE sessions add column last_subid varchar(15);
ALTER TABLE sessions add column last_sid varchar(16);
# End of R_2_3_0_14
# Start of T_2_3_0_15
# add ECODE tag ...
UPDATE vars SET value=CONCAT(value,'|ECODE') WHERE name='approvedtags';
# Message threshold
INSERT INTO vars (name, value, description) VALUES ('message_threshold','1','Default threshold for a comment to trigger a message');
# End of R_2_3_0_15
# Start of T_2_3_0_16
# Browser bug workaround
INSERT INTO vars (name, value, description) VALUES ('comment_nonstartwordchars','.,;:/','Chars which cannot start a word (will be forcibly separated from the rest of the word by a space) - this works around a Windows/MSIE "widening" bug - set blank for no action')
;
# Size changes and new column -Brian
ALTER TABLE sections CHANGE url url varchar(128) DEFAULT '' NOT NULL;
ALTER TABLE sections CHANGE hostname hostname varchar(128) DEFAULT '' NOT NULL;
ALTER TABLE sections ADD cookiedomain varchar(128) DEFAULT '' NOT NULL;
# freshenup.pl chunks article .shtml files
INSERT INTO vars (name, value, description) VALUES ('freshenup_max_stories','100','Maximum number of article.shtml files to write at a time in freshenup.pl');
# Start of T_2_3_0_20
# New hooks table
DROP TABLE IF EXISTS hooks;
CREATE TABLE hooks (
id mediumint(5) UNSIGNED NOT NULL auto_increment,
param varchar(50) DEFAULT '' NOT NULL,
class varchar(100) DEFAULT '' NOT NULL,
subroutine varchar(100) DEFAULT '' NOT NULL,
PRIMARY KEY (id),
UNIQUE hook_param (param,class,subroutine)
) TYPE = myisam;
ALTER TABLE stories ADD COLUMN qid MEDIUMINT UNSIGNED DEFAULT NULL;
# Same as below
#perl -MSlash::Test=virtusername -le '$qs = $slashdb->sqlSelectAllHashrefArray("pollquestions.qid AS qid, pollquestions.sid AS sid", "pollquestions, stories", "pollquestions.sid=stories.sid"); for my $hr (@$qs) { my($q,$s) = ($hr->{qid}, $hr->{sid}); my $sq=$slashdb->sqlQuote($s); print "$q -> $s"; $slashdb->sqlUpdate("stories", { qid => $q }, "sid=$sq") or die "\tFAILED" }'
# I have never tried to do a "source" from a file but I suspect it will work -Brian
select CONCAT('UPDATE stories SET qid=',qid,' WHERE sid="',sid,'";') into outfile "/tmp/pollquestions.sql" from pollquestions;
source /tmp/pollquestions.sql
# (and, once we're convinced everything works)
ALTER TABLE pollquestions DROP COLUMN sid;
# New submission_param table
DROP TABLE IF EXISTS submission_param;
CREATE TABLE submission_param (
param_id mediumint UNSIGNED NOT NULL auto_increment,
subid varchar(15) NOT NULL,
name varchar(32) DEFAULT '' NOT NULL,
value text DEFAULT '' NOT NULL,
UNIQUE submission_key (subid,name),
PRIMARY KEY (param_id)
) TYPE = myisam;
# This is the multi section topic stuff for Foundries
INSERT INTO code_param (type, code, name) VALUES ('section_topic_types',1,'default');
ALTER TABLE section_topics ADD COLUMN type smallint UNSIGNED NOT NULL;
UPDATE section_topics SET type=1;
ALTER TABLE section_extras MODIFY COLUMN type ENUM("text","list","topics") DEFAULT 'text' NOT NULL;
# Autopoll update
ALTER TABLE pollquestions ADD COLUMN autopoll enum("no","yes") DEFAULT 'no' NOT NULL;
# Submissions
ALTER TABLE submissions ADD COLUMN weight float DEFAULT '0' NOT NULL;
#New index_handler bits
INSERT INTO vars (name, value, description) VALUES ('index_handler','index.pl','The perl servlet to call fo conections to the root of the server.');
ALTER TABLE sections ADD COLUMN index_handler varchar(30) DEFAULT "index.pl" NOT NULL;
ALTER TABLE sections ADD COLUMN writestatus ENUM("ok","dirty") DEFAULT 'ok' NOT NULL,
#slashd_log table
INSERT INTO menus (menu, label, value, seclev, menuorder) VALUES ('admin','Slashd','[% constants.rootdir %]/admin.pl?op=slashd',500,17);
# Note this is different than what Brian committed Wednesday morning;
# were any sites updated with that?
DROP TABLE IF EXISTS slashd_status;
CREATE TABLE slashd_status (
task varchar(50) NOT NULL default '',
next_begin DATETIME,
in_progress TINYINT NOT NULL DEFAULT '0',
last_completed DATETIME,
summary VARCHAR(255) NOT NULL DEFAULT '',
duration float(6,2) DEFAULT '0.00' NOT NULL,
PRIMARY KEY (task)
) TYPE=MyISAM;
# Remember pollBooth is now a plugin!
# Unique changes for Pat
ALTER TABLE section_topics DROP PRIMARY KEY;
ALTER TABLE section_topics ADD PRIMARY KEY (section,type,tid);
INSERT INTO string_param (type, code, name) VALUES ('yes_no','yes','yes');
INSERT INTO string_param (type, code, name) VALUES ('yes_no','no','no');
INSERT INTO vars (name, value, description) VALUES ('label_ui','0','Whether to label some things in the admin ui');
INSERT INTO vars (name, value, description) VALUES ('enable_index_topic','','set this to the value in string param for index topic \(something like "topic_4"\)');
INSERT INTO vars (name, value, description) VALUES ('get_titles','0','get the story titles');
INSERT INTO vars (name, value, description) VALUES ('organise_stories','','organise story blocks');
INSERT INTO string_param (Type, code, name) VALUES ('section_topic_type','topic_1','Default');
# another small change for topic types
ALTER TABLE section_topics MODIFY COLUMN type varchar(16) NOT NULL DEFAULT 'topic_1';
UPDATE section_topics SET type = 'topic_1' WHERE topic = '1';
# Change for plugins/Subscribe
ALTER TABLE subscribe_payments ADD COLUMN method VARCHAR(6) AFTER transaction_id;
ALTER TABLE comments DROP index countreplies;
ALTER TABLE comments ADD KEY countreplies (pid,sid);
# Not read just yet but I am leaving it here as a note -Brian
# ALTER TABLE blocks ADD autosubmit enum("no","yes") DEFAULT 'no' NOT NULL;