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