0
|
1 <%
|
|
2 package nabble.model;
|
|
3
|
|
4 import fschmidt.db.DbDatabase;
|
|
5 import org.slf4j.Logger;
|
|
6 import org.slf4j.LoggerFactory;
|
|
7
|
|
8 import java.sql.SQLException;
|
|
9 import java.util.HashMap;
|
|
10 import java.util.Map;
|
|
11
|
|
12
|
|
13 final class DbSiteUpdater {
|
|
14 private static final Logger logger = LoggerFactory.getLogger(DbSiteUpdater.class);
|
|
15
|
|
16 // don't forget to update site.schema
|
|
17 static final int version = 167;
|
|
18
|
|
19 private static class Result {
|
|
20 private static final long wait = 2000L; // 2 seconds
|
|
21 private final long waitUntil = System.currentTimeMillis() + wait;
|
|
22 private DbSiteUpdater updater;
|
|
23 private SQLException sqlException = null;
|
|
24 private RuntimeException runtimeException = null;
|
|
25 private Error error = null;
|
|
26 private final String user;
|
|
27
|
|
28 private Result(DbDatabase db,String user) {
|
|
29 updater = new DbSiteUpdater(db,this,user);
|
|
30 this.user = user;
|
|
31 updater.start();
|
|
32 }
|
|
33
|
|
34 private synchronized void check() throws SQLException {
|
|
35 if( !isDone() ) {
|
|
36 long now = System.currentTimeMillis();
|
|
37 if( now < waitUntil ) {
|
|
38 try {
|
|
39 wait( waitUntil - now );
|
|
40 } catch(InterruptedException e) {
|
|
41 throw new RuntimeException(e);
|
|
42 }
|
|
43 }
|
|
44 if( !isDone() )
|
|
45 throw new UpdatingException(user);
|
|
46 }
|
|
47 if( sqlException != null )
|
|
48 throw sqlException;
|
|
49 if( runtimeException != null )
|
|
50 throw runtimeException;
|
|
51 if( error != null )
|
|
52 throw error;
|
|
53 }
|
|
54
|
|
55 synchronized boolean isDone() {
|
|
56 return updater==null;
|
|
57 }
|
|
58
|
|
59 synchronized void done(Throwable e) {
|
|
60 updater = null;
|
|
61 if( e != null ) {
|
|
62 if( e instanceof SQLException ) {
|
|
63 sqlException = (SQLException)e;
|
|
64 } else if( e instanceof RuntimeException ) {
|
|
65 runtimeException = (RuntimeException)e;
|
|
66 } else if( e instanceof Error ) {
|
|
67 error = (Error)e;
|
|
68 } else {
|
|
69 runtimeException = new RuntimeException("invalid exception type",e);
|
|
70 }
|
|
71 }
|
|
72 notifyAll();
|
|
73 }
|
|
74 }
|
|
75
|
|
76 private static final Map<String,Result> userResults = new HashMap<String,Result>();
|
|
77 private static final Object lock = new Object();
|
|
78
|
|
79 static void update(String user,DbDatabase db) throws SQLException {
|
|
80 if( user.equals("global") )
|
|
81 return; // done from command line
|
|
82 getResult(user,db).check();
|
|
83 }
|
|
84
|
|
85 private static Result getResult(String user,DbDatabase db) throws SQLException {
|
|
86 synchronized(lock) {
|
|
87 Result result = userResults.get(user);
|
|
88 if( result == null ) {
|
|
89 result = new Result(db,user);
|
|
90 userResults.put(user,result);
|
|
91 }
|
|
92 return result;
|
|
93 }
|
|
94 }
|
|
95
|
|
96 // for shell
|
|
97 public static void clearResult(long siteId) {
|
|
98 String user = SiteKey.getInstance(siteId).schema();
|
|
99 synchronized(lock) {
|
|
100 userResults.remove(user);
|
|
101 }
|
|
102 }
|
|
103
|
|
104
|
|
105
|
|
106
|
|
107 private final Result result;
|
|
108 private final DbUpdater up;
|
|
109
|
|
110 private DbSiteUpdater(DbDatabase db,Result result,String schema) {
|
|
111 this.up = new DbUpdater(db,schema);
|
|
112 this.result = result;
|
|
113 }
|
|
114
|
|
115 private void start() {
|
|
116 try { // needed for site creation because version is only up to date in current transaction
|
|
117 if( up.dbVersion() == version ) {
|
|
118 result.done(null);
|
|
119 return;
|
|
120 }
|
|
121 } catch(RuntimeException e) {
|
|
122 result.done(e);
|
|
123 return;
|
|
124 } catch(Error e) {
|
|
125 result.done(e);
|
|
126 return;
|
|
127 }
|
|
128 Executors.executeNow(new Runnable(){public void run(){
|
|
129 try {
|
|
130 go();
|
|
131 result.done(null);
|
|
132 } catch(SQLException e) {
|
|
133 result.done(e);
|
|
134 } catch(RuntimeException e) {
|
|
135 result.done(e);
|
|
136 } catch(Error e) {
|
|
137 result.done(e);
|
|
138 } finally {
|
|
139 if( !result.isDone() )
|
|
140 logger.error("result isn't done");
|
|
141 }
|
|
142 }});
|
|
143 }
|
|
144
|
|
145 void go() throws SQLException {
|
|
146 try {
|
|
147 update();
|
|
148 if( up.databaseVersion() != version )
|
|
149 throw new RuntimeException();
|
|
150 } finally {
|
|
151 up.close();
|
|
152 }
|
|
153 }
|
|
154
|
|
155 @Override public String toString() {
|
|
156 return "DbSiteUpdater-" + up.schema;
|
|
157 }
|
|
158
|
|
159 private void execCatching(String sql,String exStr) throws SQLException {
|
|
160 up.begin();
|
|
161 try {
|
|
162 up.exec(sql);
|
|
163 } catch(SQLException e) {
|
|
164 if( !e.getMessage().contains(exStr) )
|
|
165 throw e;
|
|
166 logger.info(e.toString());
|
|
167 up.exec("rollback");
|
|
168 }
|
|
169 up.commit();
|
|
170 }
|
|
171
|
|
172 void update() throws SQLException {
|
|
173 switch( up.dbVersion() ) {
|
|
174 case 0:
|
|
175 up.execStep( %>
|
|
176 create index embed_key_site_idx on embed_key(site_id)
|
|
177 <% );
|
|
178 case 1:
|
|
179 up.execStep( %>
|
|
180 create index file_avatar_site_idx on file_avatar(site_id)
|
|
181 <% );
|
|
182 case 2:
|
|
183 up.execStep( %>
|
|
184 create index file_node_site_idx on file_node(site_id)
|
|
185 <% );
|
|
186 case 3:
|
|
187 up.execStep( %>
|
|
188 create index file_temp_site_idx on file_temp(site_id)
|
|
189 <% );
|
|
190 case 4:
|
|
191 up.execStep( %>
|
|
192 create index mailing_list_site_idx on mailing_list(site_id)
|
|
193 <% );
|
|
194 case 5:
|
|
195 up.execStep( %>
|
|
196 create index node_msg_site_idx on node_msg(site_id)
|
|
197 <% );
|
|
198 case 6:
|
|
199 up.execStep( %>
|
|
200 create index node_property_site_idx on node_property(site_id)
|
|
201 <% );
|
|
202 case 7:
|
|
203 up.execStep( %>
|
|
204 create index paid_site_idx on paid(site_id)
|
|
205 <% );
|
|
206 case 8:
|
|
207 up.execStep( %>
|
|
208 create index registration_site_idx on registration(site_id)
|
|
209 <% );
|
|
210 case 9:
|
|
211 up.execStep( %>
|
|
212 create index subscription_site_idx on subscription(site_id)
|
|
213 <% );
|
|
214 case 10:
|
|
215 up.execStep( %>
|
|
216 create index user_property_site_idx on user_property(site_id)
|
|
217 <% );
|
|
218 case 11:
|
|
219 up.execStep( %>
|
|
220 create index view_count_site_idx on view_count(site_id)
|
|
221 <% );
|
|
222 case 12:
|
|
223 up.execStep( %>
|
|
224 create index visited_site_idx on visited(site_id)
|
|
225 <% );
|
|
226
|
|
227 case 13:
|
|
228 up.execStep( %>
|
|
229 create index tag_node_idx on tag(node_id)
|
|
230 <% );
|
|
231 case 14:
|
|
232 up.execStep( %>
|
|
233 create index tag_user_idx on tag(user_id)
|
|
234 <% );
|
|
235 case 15:
|
|
236 up.execStep( %>
|
|
237 create index embed_key_node_idx on embed_key(node_id)
|
|
238 <% );
|
|
239 case 16:
|
|
240 up.execStep( %>
|
|
241 create index visited_node_idx on visited(node_id)
|
|
242 <% );
|
|
243 case 17:
|
|
244 up.execStep( %>
|
|
245 create index visited_last_node_idx on visited(last_node_id)
|
|
246 <% );
|
|
247 case 18:
|
|
248 up.execStep( %>
|
|
249 alter table node add column poll_option_count integer default null
|
|
250 <% );
|
|
251 case 19:
|
|
252 up.execStep( %>
|
|
253 alter table configuration drop column site_id
|
|
254 <% );
|
|
255 case 20:
|
|
256 up.execStep( %>
|
|
257 alter table configuration ADD PRIMARY KEY (name)
|
|
258 <% );
|
|
259 case 21:
|
|
260 up.execStep( %>
|
|
261 alter table embed_key drop column site_id
|
|
262 <% );
|
|
263 case 22:
|
|
264 up.execStep( %>
|
|
265 alter table file_avatar drop column site_id
|
|
266 <% );
|
|
267 case 23:
|
|
268 up.execStep( %>
|
|
269 alter table file_node drop column site_id
|
|
270 <% );
|
|
271 case 24:
|
|
272 up.execStep( %>
|
|
273 alter table file_temp drop column site_id
|
|
274 <% );
|
|
275 case 25:
|
|
276 up.execStep( %>
|
|
277 alter table mailing_list drop column site_id
|
|
278 <% );
|
|
279 case 26:
|
|
280 up.execStep( %>
|
|
281 alter table node_msg drop column site_id
|
|
282 <% );
|
|
283 case 27:
|
|
284 up.execStep( %>
|
|
285 alter table node_property drop column site_id
|
|
286 <% );
|
|
287 case 28:
|
|
288 up.execStep( %>
|
|
289 alter table paid drop column site_id
|
|
290 <% );
|
|
291 case 29:
|
|
292 up.execStep( %>
|
|
293 alter table registration drop column site_id
|
|
294 <% );
|
|
295 case 30:
|
|
296 up.execStep( %>
|
|
297 alter table subscription drop column site_id
|
|
298 <% );
|
|
299 case 31:
|
|
300 up.execStep( %>
|
|
301 alter table user_property drop column site_id
|
|
302 <% );
|
|
303 case 32:
|
|
304 up.execStep( %>
|
|
305 alter table view_count drop column site_id
|
|
306 <% );
|
|
307 case 33:
|
|
308 up.execStep( %>
|
|
309 alter table visited drop column site_id
|
|
310 <% );
|
|
311 case 34:
|
|
312 up.execStep( %>
|
|
313 alter table file_site drop column site_id
|
|
314 <% );
|
|
315 case 35:
|
|
316 up.execStep( %>
|
|
317 alter table file_site ADD PRIMARY KEY (name)
|
|
318 <% );
|
|
319 case 36:
|
|
320 up.execStep( %>
|
|
321 alter table module drop column site_id
|
|
322 <% );
|
|
323 case 37:
|
|
324 up.execStep( %>
|
|
325 alter table module ADD PRIMARY KEY (module_name)
|
|
326 <% );
|
|
327 case 38:
|
|
328 up.execStep( %>
|
|
329 alter table site_property drop column site_id
|
|
330 <% );
|
|
331 case 39:
|
|
332 up.execStep( %>
|
|
333 alter table site_property ADD PRIMARY KEY (key)
|
|
334 <% );
|
|
335 case 40:
|
|
336 up.execStep( %>
|
|
337 alter table tweak drop column site_id
|
|
338 <% );
|
|
339 case 41:
|
|
340 up.execStep( %>
|
|
341 alter table tweak ADD PRIMARY KEY (tweak_name)
|
|
342 <% );
|
|
343 case 42:
|
|
344 up.execStep( %>
|
|
345 alter table tag drop column site_id
|
|
346 <% );
|
|
347 case 43:
|
|
348 up.execStep( %>
|
|
349 CREATE UNIQUE INDEX tag_idx
|
|
350 ON tag (node_id, user_id, label);
|
|
351 <% );
|
|
352 case 44:
|
|
353 up.execStep( %>
|
|
354 drop index tag_node_idx
|
|
355 <% );
|
|
356 case 45:
|
|
357 up.execStep( %>
|
|
358 alter table user_ drop column site_id
|
|
359 <% );
|
|
360 case 46:
|
|
361 up.execStep( %>
|
|
362 CREATE UNIQUE INDEX user_email_idx
|
|
363 ON user_ (lower(email));
|
|
364 <% );
|
|
365 case 47:
|
|
366 up.execStep( %>
|
|
367 CREATE UNIQUE INDEX user_name_idx
|
|
368 ON user_ (lower(name));
|
|
369 <% );
|
|
370 case 48:
|
|
371 up.execStep( %>
|
|
372 alter table node drop column site_id
|
|
373 <% );
|
|
374 case 49:
|
|
375 up.execStep( %>
|
|
376 CREATE INDEX node_cookie_idx
|
|
377 ON node (cookie)
|
|
378 WHERE cookie IS NOT NULL;
|
|
379 <% );
|
|
380 case 50:
|
|
381 up.execStep( %>
|
|
382 CREATE INDEX node_message_id_idx
|
|
383 ON node (lower(message_id))
|
|
384 WHERE message_id IS NOT NULL;
|
|
385 <% );
|
|
386 case 51:
|
|
387 up.execStep( %>
|
|
388 drop table embed_key
|
|
389 <% );
|
|
390 case 52:
|
|
391 up.execStep( %>
|
|
392 delete from site_property where key = '__embedding_warning'
|
|
393 <% );
|
|
394 case 53:
|
|
395 up.execStep( %>
|
|
396 alter table site drop column site_id
|
|
397 <% );
|
|
398 case 54:
|
|
399 up.execStep( %>
|
|
400 alter table site add column one integer default 1 not null
|
|
401 <% );
|
|
402 case 55:
|
|
403 up.execStep( %>
|
|
404 alter table site ADD PRIMARY KEY (one)
|
|
405 <% );
|
|
406 case 56:
|
|
407 up.execStep( %>
|
|
408 alter table site add column content_type character varying;
|
|
409 <% );
|
|
410 case 57:
|
|
411 up.execStep( %>
|
|
412 update configuration set naml=E'<override_macro name="site_style">\n\t<n.overridden/>\n\t#search-box { text-align:left; }\n</override_macro>' where name='searchBoxAlignment';
|
|
413 <% );
|
|
414 case 58:
|
|
415 up.execStep( %>
|
|
416 update site set ad_type=null where ad_type='ADBRITE' or ad_type='ADSENSE'
|
|
417 <% );
|
|
418
|
|
419 case 59:
|
|
420 up.execStep( %>
|
|
421 alter table node
|
|
422 add column unsafe character varying default 'new',
|
|
423 add column safe_version integer
|
|
424 <% );
|
|
425 case 60:
|
|
426 up.execStep( %>
|
|
427 alter table user_
|
|
428 add column unsafe character varying default 'new',
|
|
429 add column safe_version integer
|
|
430 <% );
|
|
431
|
|
432 case 61:
|
|
433 up.execStep( %>
|
|
434 update site set ad_type='HOPELESS' where ad_type='COPYRIGHTED'
|
|
435 <% );
|
|
436 case 62:
|
|
437 up.execStep( %>
|
|
438 update site set ad_type='HOPELESS' where ad_type='OURS'
|
|
439 <% );
|
|
440 case 63:
|
|
441 up.execStep( %>
|
|
442 update site set ad_type=null, ad_free=<%=Integer.MAX_VALUE%> where ad_type='NONE'
|
|
443 <% );
|
|
444
|
|
445 case 64:
|
|
446 up.execStep( %>
|
|
447 alter table node
|
|
448 add column is_safe boolean
|
|
449 <% );
|
|
450 case 65:
|
|
451 up.execStep( %>
|
|
452 alter table user_
|
|
453 add column is_safe boolean
|
|
454 <% );
|
|
455 case 66:
|
|
456 up.execStep( null );
|
|
457 case 67:
|
|
458 up.execStep( null );
|
|
459 case 68:
|
|
460 up.execStep( %>
|
|
461 delete from tag where label='hide_ads'
|
|
462 <% );
|
|
463 case 69:
|
|
464 up.execStep( %>
|
|
465 update site set ad_type=null where ad_type='SOME_ADSENSE'
|
|
466 <% );
|
|
467 case 70:
|
|
468 up.execStep( %>
|
|
469 update site set ad_type='ADULT' where ad_type='HACK'
|
|
470 <% );
|
|
471 case 71:
|
|
472 up.execStep( %>
|
|
473 update site set content_type=null where content_type='FAMILY'
|
|
474 <% );
|
|
475 case 72:
|
|
476 up.execStep( %>
|
|
477 drop index if exists node_is_safe_idx
|
|
478 <% );
|
|
479 case 73:
|
|
480 up.execStep( %>
|
|
481 drop index if exists user_is_safe_idx
|
|
482 <% );
|
|
483 case 74:
|
|
484 up.execStep( %>
|
|
485 create index node_unsafe_idx on node(unsafe) where unsafe is not null and unsafe!='new'
|
|
486 <% );
|
|
487 case 75:
|
|
488 up.execStep( %>
|
|
489 create index user_unsafe_idx on user_(unsafe) where unsafe is not null and unsafe!='new'
|
|
490 <% );
|
|
491 case 76:
|
|
492 up.execStep( %>
|
|
493 update site set content_type=ad_type where content_type is null and ad_type is not null and ad_type!='HOPELESS'
|
|
494 <% );
|
|
495 case 77:
|
|
496 up.execStep( %>
|
|
497 update site set ad_type=content_type where content_type is not null and ad_type is null
|
|
498 <% );
|
|
499 case 78:
|
|
500 up.execStep( %>
|
|
501 update site set ad_type='ADULT' where content_type='ADULT' and ad_type='PORN'
|
|
502 <% );
|
|
503
|
|
504 case 79:
|
|
505 up.execStep( %>
|
|
506 alter table user_
|
|
507 drop column unsafe,
|
|
508 drop column safe_version,
|
|
509 drop column is_safe
|
|
510 <% );
|
|
511 case 80:
|
|
512 up.execStep( null );
|
|
513 case 81:
|
|
514 up.execStep( null );
|
|
515 case 82:
|
|
516 up.begin();
|
|
517 up.exec( %>
|
|
518 alter table node
|
|
519 drop column unsafe,
|
|
520 drop column safe_version,
|
|
521 drop column is_safe
|
|
522 <% );
|
|
523 up.exec( %>
|
|
524 alter table node
|
|
525 add column unsafe character varying,
|
|
526 add column safe_version integer,
|
|
527 add column is_safe boolean
|
|
528 <% );
|
|
529 up.commit();
|
|
530 case 83:
|
|
531 up.execStep( %>
|
|
532 create index node_unsafe_idx on node(unsafe) where safe_version is not null
|
|
533 <% );
|
|
534 case 84:
|
|
535 up.execStep( %>
|
|
536 delete from module where module_name = 'topic_ads';
|
|
537 <% );
|
|
538 case 85:
|
|
539 up.execStep( %>
|
|
540 alter table node
|
|
541 drop column unsafe,
|
|
542 drop column safe_version,
|
|
543 drop column is_safe
|
|
544 <% );
|
|
545 case 86:
|
|
546 up.execStep( %>
|
|
547 alter table site
|
|
548 add column when_created timestamp with time zone NOT NULL DEFAULT now()
|
|
549 <% );
|
|
550 case 87:
|
|
551 up.execStep( %>
|
|
552 update site
|
|
553 set when_created = node.when_created
|
|
554 from node
|
|
555 where node.node_id = site.root_node_id
|
|
556 <% );
|
|
557
|
|
558 case 88:
|
|
559 up.execStep( %>
|
|
560 alter table site
|
|
561 add column is_safe boolean NOT NULL DEFAULT true
|
|
562 <% );
|
|
563 case 89:
|
|
564 up.execStep( %>
|
|
565 update site set is_safe = false where ad_type is not null
|
|
566 <% );
|
|
567 case 90:
|
|
568 up.execStep( %>
|
|
569 update site set when_created = now() where not is_safe
|
|
570 <% );
|
|
571 case 91:
|
|
572 up.execStep( %>
|
|
573 alter table site rename column ad_type to old_ad_type
|
|
574 <% );
|
|
575 case 92:
|
|
576 up.execStep( %>
|
|
577 alter table site rename column content_type to old_content_type
|
|
578 <% );
|
|
579 case 93:
|
|
580 up.execStep( %>
|
|
581 alter table site
|
|
582 add column ad_credits_for_users boolean NOT NULL DEFAULT false
|
|
583 <% );
|
|
584 case 94:
|
|
585 up.execStep( %>
|
|
586 alter table site
|
|
587 add column monthly_views integer NOT NULL DEFAULT 0
|
|
588 <% );
|
|
589 case 95:
|
|
590 up.execStep( %>
|
|
591 delete from tag where label='group:Administrators' and node_id is null and user_id = (select user_id from user_ where email = 'pedro@nabble.com')
|
|
592 <% );
|
|
593
|
|
594 case 96:
|
|
595 execCatching( %>
|
|
596 alter table tweak ADD PRIMARY KEY (tweak_name)
|
|
597 <%, "multiple primary keys for table" );
|
|
598 case 97:
|
|
599 execCatching( %>
|
|
600 alter table mailing_list ADD PRIMARY KEY (node_id)
|
|
601 <%, "multiple primary keys for table" );
|
|
602 case 98:
|
|
603 /*
|
|
604 execCatching( %>
|
|
605 alter table mailing_list ADD CONSTRAINT mailing_list_node_id_fkey FOREIGN KEY (node_id) REFERENCES node (node_id) ON DELETE CASCADE
|
|
606 <%, "already exists" );
|
|
607 */
|
|
608 up.execStep( null );
|
|
609 case 99:
|
|
610 execCatching( %>
|
|
611 CREATE INDEX forum_ml_idx ON mailing_list (lower(list_address))
|
|
612 <%, "already exists" );
|
|
613 case 100:
|
|
614 execCatching( %>
|
|
615 alter table node ADD PRIMARY KEY (node_id)
|
|
616 <%, "multiple primary keys for table" );
|
|
617 case 101:
|
|
618 execCatching( %>
|
|
619 alter table configuration ADD PRIMARY KEY (name)
|
|
620 <%, "multiple primary keys for table" );
|
|
621 case 102:
|
|
622 execCatching( %>
|
|
623 alter table file_avatar ADD PRIMARY KEY (user_id, name)
|
|
624 <%, "multiple primary keys for table" );
|
|
625 case 103:
|
|
626 execCatching( %>
|
|
627 alter table file_node ADD PRIMARY KEY (node_id, name)
|
|
628 <%, "multiple primary keys for table" );
|
|
629 case 104:
|
|
630 execCatching( %>
|
|
631 alter table file_site ADD PRIMARY KEY (name)
|
|
632 <%, "multiple primary keys for table" );
|
|
633 case 105:
|
|
634 execCatching( %>
|
|
635 alter table file_temp ADD PRIMARY KEY (user_id, name)
|
|
636 <%, "multiple primary keys for table" );
|
|
637 case 106:
|
|
638 execCatching( %>
|
|
639 CREATE INDEX file_temp_date_idx ON file_temp (date_)
|
|
640 <%, "already exists" );
|
|
641 case 107:
|
|
642 execCatching( %>
|
|
643 alter table module ADD PRIMARY KEY (module_name)
|
|
644 <%, "multiple primary keys for table" );
|
|
645 case 108:
|
|
646 execCatching( %>
|
|
647 CREATE INDEX node_cookie_idx ON node (cookie) WHERE cookie IS NOT NULL
|
|
648 <%, "already exists" );
|
|
649 case 109:
|
|
650 execCatching( %>
|
|
651 CREATE INDEX node_export ON node (export_permalink) WHERE export_permalink IS NOT NULL
|
|
652 <%, "already exists" );
|
|
653 case 110:
|
|
654 execCatching( %>
|
|
655 CREATE INDEX node_message_id_idx ON node (lower(message_id)) WHERE message_id IS NOT NULL
|
|
656 <%, "already exists" );
|
|
657 case 111:
|
|
658 execCatching( %>
|
|
659 CREATE INDEX node_ml2_idx ON node (lower(parent_message_id)) WHERE lower(parent_message_id) IS NOT NULL
|
|
660 <%, "already exists" );
|
|
661 case 112:
|
|
662 execCatching( %>
|
|
663 CREATE INDEX node_owner_idx ON node (owner_id, when_created)
|
|
664 <%, "already exists" );
|
|
665 case 113:
|
|
666 execCatching( %>
|
|
667 CREATE UNIQUE INDEX node_parent2_idx ON node (parent_id, when_created, node_id)
|
|
668 <%, "already exists" );
|
|
669 case 114:
|
|
670 execCatching( %>
|
|
671 CREATE UNIQUE INDEX node_parent_idx ON node (parent_id, last_node_date, node_id)
|
|
672 <%, "already exists" );
|
|
673 case 115:
|
|
674 execCatching( %>
|
|
675 CREATE INDEX node_sent_idx ON node (when_sent) WHERE when_sent IS NOT NULL
|
|
676 <%, "already exists" );
|
|
677 case 116:
|
|
678 execCatching( %>
|
|
679 CREATE UNIQUE INDEX pinned_node_idx ON node (parent_id, pin) WHERE pin IS NOT NULL
|
|
680 <%, "already exists" );
|
|
681 case 117:
|
|
682 execCatching( %>
|
|
683 CREATE INDEX post_date ON node (when_created)
|
|
684 <%, "already exists" );
|
|
685 case 118:
|
|
686 execCatching( %>
|
|
687 alter table node_msg ADD PRIMARY KEY (node_id)
|
|
688 <%, "multiple primary keys for table" );
|
|
689 case 119:
|
|
690 execCatching( %>
|
|
691 alter table node_property ADD PRIMARY KEY (node_id, key)
|
|
692 <%, "multiple primary keys for table" );
|
|
693 case 120:
|
|
694 execCatching( %>
|
|
695 alter table paid ADD PRIMARY KEY (user_id)
|
|
696 <%, "multiple primary keys for table" );
|
|
697 case 121:
|
|
698 execCatching( %>
|
|
699 alter table registration ADD PRIMARY KEY (key_)
|
|
700 <%, "multiple primary keys for table" );
|
|
701 case 122:
|
|
702 execCatching( %>
|
|
703 CREATE INDEX registration_date_idx ON registration (date_)
|
|
704 <%, "already exists" );
|
|
705 case 123:
|
|
706 execCatching( %>
|
|
707 alter table site ADD PRIMARY KEY (one)
|
|
708 <%, "multiple primary keys for table" );
|
|
709 case 124:
|
|
710 execCatching( %>
|
|
711 alter table site_property ADD PRIMARY KEY (key)
|
|
712 <%, "multiple primary keys for table" );
|
|
713 case 125:
|
|
714 execCatching( %>
|
|
715 alter table subscription ADD PRIMARY KEY (user_id, node_id)
|
|
716 <%, "multiple primary keys for table" );
|
|
717 case 126:
|
|
718 execCatching( %>
|
|
719 CREATE INDEX subscription_node_idx ON subscription (node_id)
|
|
720 <%, "already exists" );
|
|
721 case 127:
|
|
722 execCatching( %>
|
|
723 CREATE UNIQUE INDEX tag_idx ON tag (node_id, user_id, label)
|
|
724 <%, "already exists" );
|
|
725 case 128:
|
|
726 execCatching( %>
|
|
727 CREATE INDEX tag_user_idx ON tag (user_id)
|
|
728 <%, "already exists" );
|
|
729 case 129:
|
|
730 execCatching( %>
|
|
731 alter table user_ ADD PRIMARY KEY (user_id)
|
|
732 <%, "multiple primary keys for table" );
|
|
733 case 130:
|
|
734 execCatching( %>
|
|
735 CREATE UNIQUE INDEX user_email_idx ON user_ (lower(email))
|
|
736 <%, "already exists" );
|
|
737 case 131:
|
|
738 execCatching( %>
|
|
739 CREATE UNIQUE INDEX user_name_idx ON user_ (lower(name))
|
|
740 <%, "already exists" );
|
|
741 case 132:
|
|
742 execCatching( %>
|
|
743 alter table user_property ADD PRIMARY KEY (user_id, key)
|
|
744 <%, "multiple primary keys for table" );
|
|
745 case 133:
|
|
746 execCatching( %>
|
|
747 alter table view_count ADD PRIMARY KEY (node_id)
|
|
748 <%, "multiple primary keys for table" );
|
|
749 case 134:
|
|
750 execCatching( %>
|
|
751 alter table visited ADD PRIMARY KEY (user_id, node_id)
|
|
752 <%, "multiple primary keys for table" );
|
|
753 case 135:
|
|
754 execCatching( %>
|
|
755 CREATE INDEX visited_last_node_idx ON visited (last_node_id)
|
|
756 <%, "already exists" );
|
|
757 case 136:
|
|
758 execCatching( %>
|
|
759 CREATE INDEX visited_node_idx ON visited (node_id)
|
|
760 <%, "already exists" );
|
|
761
|
|
762 case 137:
|
|
763 execCatching( %>
|
|
764 ALTER TABLE ONLY file_avatar
|
|
765 ADD CONSTRAINT file_avatar_user_id_fkey FOREIGN KEY (user_id) REFERENCES user_(user_id) ON DELETE CASCADE
|
|
766 <%, "already exists" );
|
|
767 case 138:
|
|
768 up.execStep( null );
|
|
769 case 139:
|
|
770 execCatching( %>
|
|
771 ALTER TABLE ONLY file_temp
|
|
772 ADD CONSTRAINT file_temp_user_id_fkey FOREIGN KEY (user_id) REFERENCES user_(user_id) ON DELETE CASCADE
|
|
773 <%, "already exists" );
|
|
774 case 140:
|
|
775 execCatching( %>
|
|
776 ALTER TABLE ONLY mailing_list
|
|
777 ADD CONSTRAINT mailing_list_node_id_fkey FOREIGN KEY (node_id) REFERENCES node(node_id) ON DELETE CASCADE
|
|
778 <%, "already exists" );
|
|
779 case 141:
|
|
780 up.execStep( null );
|
|
781 case 142:
|
|
782 execCatching( %>
|
|
783 ALTER TABLE ONLY node
|
|
784 ADD CONSTRAINT node_new_owner_id_fkey FOREIGN KEY (owner_id) REFERENCES user_(user_id)
|
|
785 <%, "already exists" );
|
|
786 case 143:
|
|
787 execCatching( %>
|
|
788 ALTER TABLE ONLY node
|
|
789 ADD CONSTRAINT node_node_id_fkey FOREIGN KEY (node_id) REFERENCES node_msg(node_id) DEFERRABLE INITIALLY DEFERRED
|
|
790 <%, "already exists" );
|
|
791 case 144:
|
|
792 up.execStep( null );
|
|
793 case 145:
|
|
794 up.execStep( null );
|
|
795 case 146:
|
|
796 execCatching( %>
|
|
797 ALTER TABLE ONLY paid
|
|
798 ADD CONSTRAINT paid_user_id_fkey FOREIGN KEY (user_id) REFERENCES user_(user_id) ON DELETE CASCADE
|
|
799 <%, "already exists" );
|
|
800 case 147:
|
|
801 execCatching( %>
|
|
802 ALTER TABLE ONLY site
|
|
803 ADD CONSTRAINT site_root_node_id_fkey FOREIGN KEY (root_node_id) REFERENCES node(node_id) DEFERRABLE INITIALLY DEFERRED
|
|
804 <%, "already exists" );
|
|
805 case 148:
|
|
806 up.execStep( null );
|
|
807 case 149:
|
|
808 execCatching( %>
|
|
809 ALTER TABLE ONLY subscription
|
|
810 ADD CONSTRAINT subscription_user_id_fkey FOREIGN KEY (user_id) REFERENCES user_(user_id) ON DELETE CASCADE
|
|
811 <%, "already exists" );
|
|
812 case 150:
|
|
813 up.execStep( null );
|
|
814 case 151:
|
|
815 execCatching( %>
|
|
816 ALTER TABLE ONLY tag
|
|
817 ADD CONSTRAINT tag_user_id_fkey FOREIGN KEY (user_id) REFERENCES user_(user_id) ON DELETE CASCADE
|
|
818 <%, "already exists" );
|
|
819 case 152:
|
|
820 execCatching( %>
|
|
821 ALTER TABLE ONLY user_property
|
|
822 ADD CONSTRAINT user_property_user_id_fkey FOREIGN KEY (user_id) REFERENCES user_(user_id) ON DELETE CASCADE
|
|
823 <%, "already exists" );
|
|
824 case 153:
|
|
825 up.execStep( null );
|
|
826 case 154:
|
|
827 up.execStep( null );
|
|
828 case 155:
|
|
829 up.execStep( null );
|
|
830 case 156:
|
|
831 execCatching( %>
|
|
832 ALTER TABLE ONLY visited
|
|
833 ADD CONSTRAINT visited_user_id_fkey FOREIGN KEY (user_id) REFERENCES user_(user_id) ON DELETE CASCADE
|
|
834 <%, "already exists" );
|
|
835 case 157:
|
|
836 up.execStep( null );
|
|
837
|
|
838 // problematic constraints
|
|
839 case 158:
|
|
840 execCatching( %>
|
|
841 ALTER TABLE ONLY file_node
|
|
842 ADD CONSTRAINT file_node_node_id_fkey FOREIGN KEY (node_id) REFERENCES node(node_id) ON DELETE CASCADE
|
|
843 <%, "already exists" );
|
|
844 case 159:
|
|
845 execCatching( %>
|
|
846 ALTER TABLE ONLY node_msg
|
|
847 ADD CONSTRAINT node_msg_node_id_fkey FOREIGN KEY (node_id) REFERENCES node(node_id) ON DELETE CASCADE
|
|
848 <%, "already exists" );
|
|
849 case 160:
|
|
850 execCatching( %>
|
|
851 ALTER TABLE ONLY node
|
|
852 ADD CONSTRAINT node_parent_id_fkey FOREIGN KEY (parent_id) REFERENCES node(node_id) ON DELETE CASCADE
|
|
853 <%, "already exists" );
|
|
854 case 161:
|
|
855 execCatching( %>
|
|
856 ALTER TABLE ONLY node_property
|
|
857 ADD CONSTRAINT node_property_node_id_fkey FOREIGN KEY (node_id) REFERENCES node(node_id) ON DELETE CASCADE
|
|
858 <%, "already exists" );
|
|
859 case 162:
|
|
860 execCatching( %>
|
|
861 ALTER TABLE ONLY subscription
|
|
862 ADD CONSTRAINT subscription_node_id_fkey FOREIGN KEY (node_id) REFERENCES node(node_id) ON DELETE CASCADE
|
|
863 <%, "already exists" );
|
|
864 case 163:
|
|
865 execCatching( %>
|
|
866 ALTER TABLE ONLY tag
|
|
867 ADD CONSTRAINT tag_node_id_fkey FOREIGN KEY (node_id) REFERENCES node(node_id) ON DELETE CASCADE
|
|
868 <%, "already exists" );
|
|
869 case 164:
|
|
870 execCatching( %>
|
|
871 ALTER TABLE ONLY view_count
|
|
872 ADD CONSTRAINT view_count_node_id_fkey FOREIGN KEY (node_id) REFERENCES node(node_id) ON DELETE CASCADE
|
|
873 <%, "already exists" );
|
|
874 case 165:
|
|
875 execCatching( %>
|
|
876 ALTER TABLE ONLY visited
|
|
877 ADD CONSTRAINT visited_last_node_id_fkey FOREIGN KEY (last_node_id) REFERENCES node(node_id) ON DELETE CASCADE
|
|
878 <%, "already exists" );
|
|
879 case 166:
|
|
880 execCatching( %>
|
|
881 ALTER TABLE ONLY visited
|
|
882 ADD CONSTRAINT visited_node_id_fkey FOREIGN KEY (node_id) REFERENCES node(node_id) ON DELETE CASCADE
|
|
883 <%, "already exists" );
|
|
884 }
|
|
885 }
|
|
886 }
|
|
887 %>
|