--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/src/nabble/model/DbGlobalUpdater.java	Thu Mar 21 19:15:52 2019 -0600
@@ -0,0 +1,153 @@
+
+package nabble.model;
+
+import fschmidt.db.DbDatabase;
+import jdbcpgbackup.ZipBackup;
+import nabble.utils.Log4j;
+
+import java.io.File;
+import java.sql.SQLException;
+import java.util.Collections;
+
+
+public final class DbGlobalUpdater {
+
+	private static final int version = 15;
+
+	private static final boolean skipSlowSteps = Init.get("skipSlowSteps",false);
+	private static final File globalSchemaFile = new File("src/nabble/data/global.schema");
+
+	private static final String GLOBAL = "global";
+
+	private final DbUpdater up;
+
+	private DbGlobalUpdater(DbDatabase dbGlobal) {
+		this.up = new DbUpdater(dbGlobal,GLOBAL);
+	}
+
+	private void go() throws SQLException {
+		try {
+			update();
+			if( up.databaseVersion() != version )
+				throw new RuntimeException();
+		} finally {
+			up.close();
+		}
+	}
+
+	public static boolean isValid() {
+		ZipBackup backup = new ZipBackup( globalSchemaFile, Db.completeUrl );
+		return backup.compareTo(GLOBAL);
+	}
+
+	public static void backupSchema(String filename) {
+		ZipBackup backup = new ZipBackup( new File(filename), Db.completeUrl );
+		backup.dump( Collections.singleton(GLOBAL), SiteImpl.SCHEMA_DATA );
+	}
+//	in beanshell, I do:
+//	DbGlobalUpdater.backupSchema("/Users/Franklin/hg/nabble/src/nabble/data/global.schema")
+
+	public static void updateGlobal() throws SQLException {
+		DbDatabase dbGlobal;
+		try {
+			dbGlobal = Db.dbGlobal();
+		} catch(Db.NoSchema e) {
+			if( !e.getMessage().equals(GLOBAL) )
+				throw e;
+			DbDatabase db = Db.dbPostgres();
+			db.beginTransaction();
+			try {
+				DbSiteCreator.restoreSchema( globalSchemaFile, GLOBAL );
+				db.commitTransaction();
+			} finally {
+				db.endTransaction();
+			}
+			dbGlobal = Db.dbGlobal();
+		}
+		new DbGlobalUpdater(dbGlobal).go();
+	}
+
+	public static void main(String[] args) throws Exception {
+		Log4j.initForConsole();
+		updateGlobal();
+	}
+
+	void execStepAsPostgres(String sql)
+		throws SQLException
+	{
+		up.begin();
+		up.exec( 
+		"\r\n			set role "
+		+(Db.user)
+		+"\r\n		"
+ );
+		up.exec(sql);
+		up.exec( 
+		"\r\n			set role global\r\n		"
+ );
+		up.commit();
+	}
+
+	void update() throws SQLException {
+		switch( up.dbVersion() ) {
+		case 0:
+			up.execStep( 
+		"\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			"
+ );
+		case 1:  // again
+			up.execStep( 
+		"\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			"
+ );
+		case 2:
+			up.execStep( 
+		"\r\n				alter table mailing_list_lookup\r\n					drop constraint mailing_list_lookup_site_id_fkey\r\n			"
+ );
+		case 3:
+			up.execStep( 
+		"\r\n				alter table mailing_list_lookup\r\n					ADD FOREIGN KEY (site_id) REFERENCES site_global(site_id) ON DELETE CASCADE\r\n			"
+ );
+		case 4:
+			up.execStep( 
+		"\r\n				alter table task\r\n					drop constraint task_site_id_fkey\r\n			"
+ );
+		case 5:
+			up.execStep( 
+		"\r\n				alter table task\r\n					ADD FOREIGN KEY (site_id) REFERENCES site_global(site_id) ON DELETE CASCADE\r\n			"
+ );
+		case 6:
+			up.execStep(null);
+		case 7:
+			up.execStep(null);
+		case 8:
+			execStepAsPostgres( 
+		"\r\n				alter schema public rename to pulic_old\r\n			"
+ );
+		case 9:
+			up.execStep( 
+		"\r\n				alter table global.site_global\r\n					add column remote_addr character varying;\r\n			"
+ );
+		case 10:
+			up.execStep( 
+		"\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			"
+ );
+		case 11:
+			up.execStep( 
+		"\r\n				alter table site_global drop column owner_email\r\n			"
+ );
+		case 12:
+			up.execStep( 
+		"\r\n				delete from safety\r\n			"
+ );
+		case 13:
+			up.execStep( 
+		"\r\n				alter table site_global\r\n					add column user_views integer NOT NULL DEFAULT 0\r\n			"
+ );
+		case 14:
+			up.execStep( 
+		"\r\n				drop table safety\r\n			"
+ );
+		}
+	}
+
+}
+