0
|
1
|
|
2 package nabble.model;
|
|
3
|
|
4 import fschmidt.db.DbDatabase;
|
|
5 import jdbcpgbackup.ZipBackup;
|
|
6 import nabble.utils.Log4j;
|
|
7
|
|
8 import java.io.File;
|
|
9 import java.sql.SQLException;
|
|
10 import java.util.Collections;
|
|
11
|
|
12
|
|
13 public final class DbGlobalUpdater {
|
|
14
|
|
15 private static final int version = 15;
|
|
16
|
|
17 private static final boolean skipSlowSteps = Init.get("skipSlowSteps",false);
|
|
18 private static final File globalSchemaFile = new File("src/nabble/data/global.schema");
|
|
19
|
|
20 private static final String GLOBAL = "global";
|
|
21
|
|
22 private final DbUpdater up;
|
|
23
|
|
24 private DbGlobalUpdater(DbDatabase dbGlobal) {
|
|
25 this.up = new DbUpdater(dbGlobal,GLOBAL);
|
|
26 }
|
|
27
|
|
28 private void go() throws SQLException {
|
|
29 try {
|
|
30 update();
|
|
31 if( up.databaseVersion() != version )
|
|
32 throw new RuntimeException();
|
|
33 } finally {
|
|
34 up.close();
|
|
35 }
|
|
36 }
|
|
37
|
|
38 public static boolean isValid() {
|
|
39 ZipBackup backup = new ZipBackup( globalSchemaFile, Db.completeUrl );
|
|
40 return backup.compareTo(GLOBAL);
|
|
41 }
|
|
42
|
|
43 public static void backupSchema(String filename) {
|
|
44 ZipBackup backup = new ZipBackup( new File(filename), Db.completeUrl );
|
|
45 backup.dump( Collections.singleton(GLOBAL), SiteImpl.SCHEMA_DATA );
|
|
46 }
|
|
47 // in beanshell, I do:
|
|
48 // DbGlobalUpdater.backupSchema("/Users/Franklin/hg/nabble/src/nabble/data/global.schema")
|
|
49
|
|
50 public static void updateGlobal() throws SQLException {
|
|
51 DbDatabase dbGlobal;
|
|
52 try {
|
|
53 dbGlobal = Db.dbGlobal();
|
|
54 } catch(Db.NoSchema e) {
|
|
55 if( !e.getMessage().equals(GLOBAL) )
|
|
56 throw e;
|
|
57 DbDatabase db = Db.dbPostgres();
|
|
58 db.beginTransaction();
|
|
59 try {
|
|
60 DbSiteCreator.restoreSchema( globalSchemaFile, GLOBAL );
|
|
61 db.commitTransaction();
|
|
62 } finally {
|
|
63 db.endTransaction();
|
|
64 }
|
|
65 dbGlobal = Db.dbGlobal();
|
|
66 }
|
|
67 new DbGlobalUpdater(dbGlobal).go();
|
|
68 }
|
|
69
|
|
70 public static void main(String[] args) throws Exception {
|
|
71 Log4j.initForConsole();
|
|
72 updateGlobal();
|
|
73 }
|
|
74
|
|
75 void execStepAsPostgres(String sql)
|
|
76 throws SQLException
|
|
77 {
|
|
78 up.begin();
|
|
79 up.exec(
|
|
80 "\r\n set role "
|
|
81 +(Db.user)
|
|
82 +"\r\n "
|
|
83 );
|
|
84 up.exec(sql);
|
|
85 up.exec(
|
|
86 "\r\n set role global\r\n "
|
|
87 );
|
|
88 up.commit();
|
|
89 }
|
|
90
|
|
91 void update() throws SQLException {
|
|
92 switch( up.dbVersion() ) {
|
|
93 case 0:
|
|
94 up.execStep(
|
|
95 "\r\n insert into site_global (site_id)\r\n select site_id from site\r\n where not exists (select * from site_global where site_global.site_id=site.site_id)\r\n "
|
|
96 );
|
|
97 case 1: // again
|
|
98 up.execStep(
|
|
99 "\r\n insert into site_global (site_id)\r\n select site_id from site\r\n where not exists (select * from site_global where site_global.site_id=site.site_id)\r\n "
|
|
100 );
|
|
101 case 2:
|
|
102 up.execStep(
|
|
103 "\r\n alter table mailing_list_lookup\r\n drop constraint mailing_list_lookup_site_id_fkey\r\n "
|
|
104 );
|
|
105 case 3:
|
|
106 up.execStep(
|
|
107 "\r\n alter table mailing_list_lookup\r\n ADD FOREIGN KEY (site_id) REFERENCES site_global(site_id) ON DELETE CASCADE\r\n "
|
|
108 );
|
|
109 case 4:
|
|
110 up.execStep(
|
|
111 "\r\n alter table task\r\n drop constraint task_site_id_fkey\r\n "
|
|
112 );
|
|
113 case 5:
|
|
114 up.execStep(
|
|
115 "\r\n alter table task\r\n ADD FOREIGN KEY (site_id) REFERENCES site_global(site_id) ON DELETE CASCADE\r\n "
|
|
116 );
|
|
117 case 6:
|
|
118 up.execStep(null);
|
|
119 case 7:
|
|
120 up.execStep(null);
|
|
121 case 8:
|
|
122 execStepAsPostgres(
|
|
123 "\r\n alter schema public rename to pulic_old\r\n "
|
|
124 );
|
|
125 case 9:
|
|
126 up.execStep(
|
|
127 "\r\n alter table global.site_global\r\n add column remote_addr character varying;\r\n "
|
|
128 );
|
|
129 case 10:
|
|
130 up.execStep(
|
|
131 "\r\n create table safety (\r\n url character varying primary key,\r\n is_safe boolean not null,\r\n content text not null\r\n )\r\n "
|
|
132 );
|
|
133 case 11:
|
|
134 up.execStep(
|
|
135 "\r\n alter table site_global drop column owner_email\r\n "
|
|
136 );
|
|
137 case 12:
|
|
138 up.execStep(
|
|
139 "\r\n delete from safety\r\n "
|
|
140 );
|
|
141 case 13:
|
|
142 up.execStep(
|
|
143 "\r\n alter table site_global\r\n add column user_views integer NOT NULL DEFAULT 0\r\n "
|
|
144 );
|
|
145 case 14:
|
|
146 up.execStep(
|
|
147 "\r\n drop table safety\r\n "
|
|
148 );
|
|
149 }
|
|
150 }
|
|
151
|
|
152 }
|
|
153
|