|
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 set role <%=Db.user%>
|
|
|
81 <% );
|
|
|
82 up.exec(sql);
|
|
|
83 up.exec( %>
|
|
|
84 set role global
|
|
|
85 <% );
|
|
|
86 up.commit();
|
|
|
87 }
|
|
|
88
|
|
|
89 void update() throws SQLException {
|
|
|
90 switch( up.dbVersion() ) {
|
|
|
91 case 0:
|
|
|
92 up.execStep( %>
|
|
|
93 insert into site_global (site_id)
|
|
|
94 select site_id from site
|
|
|
95 where not exists (select * from site_global where site_global.site_id=site.site_id)
|
|
|
96 <% );
|
|
|
97 case 1: // again
|
|
|
98 up.execStep( %>
|
|
|
99 insert into site_global (site_id)
|
|
|
100 select site_id from site
|
|
|
101 where not exists (select * from site_global where site_global.site_id=site.site_id)
|
|
|
102 <% );
|
|
|
103 case 2:
|
|
|
104 up.execStep( %>
|
|
|
105 alter table mailing_list_lookup
|
|
|
106 drop constraint mailing_list_lookup_site_id_fkey
|
|
|
107 <% );
|
|
|
108 case 3:
|
|
|
109 up.execStep( %>
|
|
|
110 alter table mailing_list_lookup
|
|
|
111 ADD FOREIGN KEY (site_id) REFERENCES site_global(site_id) ON DELETE CASCADE
|
|
|
112 <% );
|
|
|
113 case 4:
|
|
|
114 up.execStep( %>
|
|
|
115 alter table task
|
|
|
116 drop constraint task_site_id_fkey
|
|
|
117 <% );
|
|
|
118 case 5:
|
|
|
119 up.execStep( %>
|
|
|
120 alter table task
|
|
|
121 ADD FOREIGN KEY (site_id) REFERENCES site_global(site_id) ON DELETE CASCADE
|
|
|
122 <% );
|
|
|
123 case 6:
|
|
|
124 up.execStep(null);
|
|
|
125 case 7:
|
|
|
126 up.execStep(null);
|
|
|
127 case 8:
|
|
|
128 execStepAsPostgres( %>
|
|
|
129 alter schema public rename to pulic_old
|
|
|
130 <% );
|
|
|
131 case 9:
|
|
|
132 up.execStep( %>
|
|
|
133 alter table global.site_global
|
|
|
134 add column remote_addr character varying;
|
|
|
135 <% );
|
|
|
136 case 10:
|
|
|
137 up.execStep( %>
|
|
|
138 create table safety (
|
|
|
139 url character varying primary key,
|
|
|
140 is_safe boolean not null,
|
|
|
141 content text not null
|
|
|
142 )
|
|
|
143 <% );
|
|
|
144 case 11:
|
|
|
145 up.execStep( %>
|
|
|
146 alter table site_global drop column owner_email
|
|
|
147 <% );
|
|
|
148 case 12:
|
|
|
149 up.execStep( %>
|
|
|
150 delete from safety
|
|
|
151 <% );
|
|
|
152 case 13:
|
|
|
153 up.execStep( %>
|
|
|
154 alter table site_global
|
|
|
155 add column user_views integer NOT NULL DEFAULT 0
|
|
|
156 <% );
|
|
|
157 case 14:
|
|
|
158 up.execStep( %>
|
|
|
159 drop table safety
|
|
|
160 <% );
|
|
|
161 }
|
|
|
162 }
|
|
|
163
|
|
|
164 }
|
|
|
165 %>
|
