--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/src/nabble/model/DbGlobalUpdater.jmp	Thu Mar 21 19:15:52 2019 -0600
@@ -0,0 +1,165 @@
+<%
+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( %>
+			set role <%=Db.user%>
+		<% );
+		up.exec(sql);
+		up.exec( %>
+			set role global
+		<% );
+		up.commit();
+	}
+
+	void update() throws SQLException {
+		switch( up.dbVersion() ) {
+		case 0:
+			up.execStep( %>
+				insert into site_global (site_id)
+					select site_id from site
+						where not exists (select * from site_global where site_global.site_id=site.site_id)
+			<% );
+		case 1:  // again
+			up.execStep( %>
+				insert into site_global (site_id)
+					select site_id from site
+						where not exists (select * from site_global where site_global.site_id=site.site_id)
+			<% );
+		case 2:
+			up.execStep( %>
+				alter table mailing_list_lookup
+					drop constraint mailing_list_lookup_site_id_fkey
+			<% );
+		case 3:
+			up.execStep( %>
+				alter table mailing_list_lookup
+					ADD FOREIGN KEY (site_id) REFERENCES site_global(site_id) ON DELETE CASCADE
+			<% );
+		case 4:
+			up.execStep( %>
+				alter table task
+					drop constraint task_site_id_fkey
+			<% );
+		case 5:
+			up.execStep( %>
+				alter table task
+					ADD FOREIGN KEY (site_id) REFERENCES site_global(site_id) ON DELETE CASCADE
+			<% );
+		case 6:
+			up.execStep(null);
+		case 7:
+			up.execStep(null);
+		case 8:
+			execStepAsPostgres( %>
+				alter schema public rename to pulic_old
+			<% );
+		case 9:
+			up.execStep( %>
+				alter table global.site_global
+					add column remote_addr character varying;
+			<% );
+		case 10:
+			up.execStep( %>
+				create table safety (
+					url character varying primary key,
+					is_safe boolean not null,
+					content text not null
+				)
+			<% );
+		case 11:
+			up.execStep( %>
+				alter table site_global drop column owner_email
+			<% );
+		case 12:
+			up.execStep( %>
+				delete from safety
+			<% );
+		case 13:
+			up.execStep( %>
+				alter table site_global
+					add column user_views integer NOT NULL DEFAULT 0
+			<% );
+		case 14:
+			up.execStep( %>
+				drop table safety
+			<% );
+		}
+	}
+
+}
+%>