Commits

Tuukka Norri committed bab6424

BaseTen schema and Assistant
- Changed baseten_lock_seq into a global sequence to avoid various problems caused by it not being owned by a table. Also made it cycle.
- While at it, changed modification's primary key to be set using DEFAULT instead of a trigger.
- Changed the upgrade schema item's title to "Install or Upgrade BaseTen schema."
- Made the upgrade IBAction also able to install the schema.
- Also made it handle both new- and old-style table names.
- Database metadata is now updated also after removing the schema.

Comments (0)

Files changed (3)

BaseTenAssistant/English.lproj/MainMenu.xib

 <archive type="com.apple.InterfaceBuilder3.Cocoa.XIB" version="7.02">
 	<data>
 		<int key="IBDocument.SystemTarget">1050</int>
-		<string key="IBDocument.SystemVersion">9F33</string>
+		<string key="IBDocument.SystemVersion">9G55</string>
 		<string key="IBDocument.InterfaceBuilderVersion">670</string>
-		<string key="IBDocument.AppKitVersion">949.34</string>
-		<string key="IBDocument.HIToolboxVersion">352.00</string>
+		<string key="IBDocument.AppKitVersion">949.43</string>
+		<string key="IBDocument.HIToolboxVersion">353.00</string>
 		<object class="NSMutableArray" key="IBDocument.EditedObjectIDs">
 			<bool key="EncodedWithXMLCoder">YES</bool>
 			<integer value="849"/>
+			<integer value="512"/>
+			<integer value="582"/>
 			<integer value="449"/>
-			<integer value="512"/>
 		</object>
 		<object class="NSArray" key="IBDocument.PluginDependencies">
 			<bool key="EncodedWithXMLCoder">YES</bool>
 									</object>
 									<string key="NSFrame">{{1, 1}, {368, 126}}</string>
 									<reference key="NSSuperview" ref="392771615"/>
-									<reference key="NSNextKeyView" ref="71290370"/>
 									<reference key="NSDocView" ref="71290370"/>
 									<object class="NSColor" key="NSBGColor">
 										<int key="NSColorSpace">1</int>
 							</object>
 							<string key="NSFrame">{{20, 60}, {385, 128}}</string>
 							<reference key="NSSuperview" ref="957137145"/>
-							<reference key="NSNextKeyView" ref="45882566"/>
 							<int key="NSsFlags">18</int>
 							<reference key="NSVScroller" ref="744026035"/>
 							<reference key="NSHScroller" ref="1061755770"/>
 								</object>
 								<object class="NSMenuItem" id="765264690">
 									<reference key="NSMenu" ref="452600950"/>
-									<string key="NSTitle">Upgrade BaseTen Schema</string>
+									<string key="NSTitle">Install or Upgrade BaseTen Schema</string>
 									<string key="NSKeyEquiv"/>
 									<int key="NSMnemonicLoc">2147483647</int>
 									<reference key="NSOnImage" ref="622407158"/>
 					<string>com.apple.InterfaceBuilder.CocoaPlugin</string>
 					<string>com.apple.InterfaceBuilder.CocoaPlugin</string>
 					<string>com.apple.InterfaceBuilder.CocoaPlugin</string>
-					<string>{{589, 634}, {113, 23}}</string>
+					<string>{{561, 568}, {89, 23}}</string>
 					<string>com.apple.InterfaceBuilder.CocoaPlugin</string>
 					<string>com.apple.InterfaceBuilder.CocoaPlugin</string>
 					<string>{{193, 484}, {616, 0}}</string>
 					<string>com.apple.InterfaceBuilder.CocoaPlugin</string>
 					<string>com.apple.InterfaceBuilder.CocoaPlugin</string>
 					<string>com.apple.InterfaceBuilder.CocoaPlugin</string>
-					<string>{{270, 149}, {506, 387}}</string>
+					<string>{{184, 149}, {506, 387}}</string>
 					<string>com.apple.InterfaceBuilder.CocoaPlugin</string>
-					<string>{{270, 149}, {506, 387}}</string>
+					<string>{{184, 149}, {506, 387}}</string>
 					<reference ref="8"/>
 					<string>{{441, 464}, {506, 387}}</string>
 					<reference ref="9"/>
 					<string>{{923, 941}, {232, 23}}</string>
 					<string>com.apple.InterfaceBuilder.CocoaPlugin</string>
 					<reference ref="9"/>
-					<string>{{336, 684}, {255, 183}}</string>
+					<string>{{336, 684}, {319, 183}}</string>
 					<string>com.apple.InterfaceBuilder.CocoaPlugin</string>
 					<reference ref="9"/>
 					<string>{{766, 811}, {235, 153}}</string>
 					<string>com.apple.InterfaceBuilder.CocoaPlugin</string>
 					<string>fi.karppinen.BaseTen.InterfaceBuilderPlugin</string>
 					<string>com.apple.InterfaceBuilder.CocoaPlugin</string>
-					<string>{{568, 296}, {425, 233}}</string>
+					<string>{{265, 296}, {425, 233}}</string>
 					<string>com.apple.InterfaceBuilder.CocoaPlugin</string>
-					<string>{{568, 296}, {425, 233}}</string>
+					<string>{{265, 296}, {425, 233}}</string>
 					<reference ref="8"/>
 					<reference ref="8"/>
 					<reference ref="9"/>
 					<reference ref="9"/>
 					<string>com.apple.InterfaceBuilder.CocoaPlugin</string>
 					<string>com.apple.InterfaceBuilder.CocoaPlugin</string>
-					<string>{{429, 332}, {389, 320}}</string>
+					<string>{{301, 332}, {389, 320}}</string>
 					<string>com.apple.InterfaceBuilder.CocoaPlugin</string>
-					<string>{{429, 332}, {389, 320}}</string>
+					<string>{{301, 332}, {389, 320}}</string>
 					<reference ref="9"/>
 					<string>{{906, 834}, {308, 201}}</string>
 					<reference ref="9"/>

BaseTenAssistant/Sources/BXAController.m

 	return retval;
 }
 
-
 - (BOOL) canUpgradeSchema
 {
 	BOOL retval = NO;
 	}
 	
 	//Clean up.
-	[connection executeQuery: @"DROP TABLE baseten_view_pkey"];
-	[connection executeQuery: @"DROP TABLE baseten_enabled_oids"];
+	[connection executeQuery: @"DROP TABLE IF EXISTS baseten_view_pkey"];
+	[connection executeQuery: @"DROP TABLE IF EXISTS baseten_enabled_oids"];
 }
 
 - (void) upgradeBaseTenSchema
 {
-	PGTSConnection* connection = [[(BXPGInterface *) [mContext databaseInterface] transactionHandler] connection];
-	[connection executeQuery: @"CREATE TEMPORARY TABLE baseten_view_pkey AS SELECT * FROM baseten.view_pkey"];
-	NSError* error; //Patch by Tim Bedford 2008-08-11
-	NSString* query = 
-	@"CREATE TEMPORARY TABLE baseten_enabled_oids AS "
-	@" SELECT relid AS oid FROM baseten.enabled_relation";
-	[connection executeQuery: query];
+	NSInvocation* callback = nil;
+	if ([self hasBaseTenSchema])
+	{
+		callback = MakeInvocation (self, @selector (finishUpgrading:));
+		
+		//We should have multiple migrator classes instead of the switch statement below,
+		//but since there are only two different cases, we probably can manage with it.
+		BXPGTransactionHandler* handler = [(BXPGInterface *) [mContext databaseInterface] transactionHandler];
+		BXPGDatabaseDescription* desc = [handler databaseDescription];
+		PGTSConnection* connection = [handler connection];
+
+		//If version is less than 0.922, we have old-style table and function names.
+		NSNumber* version = [desc schemaVersion];
+		NSNumber* threshold = [NSDecimalNumber decimalNumberWithString: @"0.922"];
+		NSComparisonResult res = [threshold compare: version];
+		switch (res)
+		{
+			case NSOrderedDescending:
+			{
+				NSString* query = nil;
+				query = @"CREATE TEMPORARY TABLE baseten_view_pkey AS SELECT * FROM baseten.viewprimarykey";
+				[connection executeQuery: query];
+				query = 
+				@"CREATE TEMPORARY TABLE baseten_enabled_oids AS "
+				@" SELECT oid FROM pg_class WHERE baseten.isobservingcompatible (oid) = true";
+				[connection executeQuery: query];
+				break;
+			}
+				
+			case NSOrderedSame:
+			case NSOrderedAscending:
+			{
+				NSString* query = nil;
+				query = @"CREATE TEMPORARY TABLE baseten_view_pkey AS SELECT * FROM baseten.view_pkey";
+				[connection executeQuery: query];
+				query = 
+				@"CREATE TEMPORARY TABLE baseten_enabled_oids AS "
+				@" SELECT relid AS oid FROM baseten.enabled_relation";
+				[connection executeQuery: query];
+				break;
+			}
+				
+			default:
+				break;
+		}
+	}
 	
+	NSError* error = nil;
 	//Patch by Tim Bedford 2008-08-11
-	[self installBaseTenSchema: MakeInvocation (self, @selector (finishUpgrading:)) error:&error];
+	[self installBaseTenSchema: callback error: &error];
 	//FIXME: handle the error.
 	//End patch
 }
 	NSDictionary* entities = [mContext entitiesBySchemaAndName: YES error: NULL];
 	[mEntitiesBySchema setContent: entities];
 }
-
-- (BOOL) canRemoveSchema
-{
-	return [[[(BXPGInterface *) [mContext databaseInterface] transactionHandler] 
-			 databaseDescription] hasBaseTenSchema];
-}
 @end
 
 
 	{
 		retval = YES; // YES by default
 		if(action == @selector(upgradeSchema:))
-			retval = [self canUpgradeSchema];
+			retval = ([self canUpgradeSchema] || ![self hasBaseTenSchema]);
 		else if(action == @selector(removeSchema:))
-			retval = [self canRemoveSchema];
+			retval = [self hasBaseTenSchema];
 		else if(action == @selector(exportLog:))
 			retval = [self canExportLog];
 		else if(action == @selector(importDataModel:))
 	{
 		for (id pair in [mEntities arrangedObjects])
 			[[pair value] setEnabled: NO];
+		[[mContext databaseInterface] reloadDatabaseMetadata];
+		[self checkBaseTenSchema: NULL];
 	}
 }
 

Resources/BaseTenModifications.sql.m4

 
 changequote(`{{', `}}')
 -- ' -- Fix for syntax coloring in SQL mode.
-define({{_bx_version_}}, {{0.923}})dnl
+define({{_bx_version_}}, {{0.924}})dnl
 define({{_bx_compat_version_}}, {{0.18}})dnl
 
 
 REVOKE ALL PRIVILEGES ON SCHEMA "baseten" FROM PUBLIC;
 GRANT USAGE ON SCHEMA "baseten" TO basetenread;
 
-CREATE TEMPORARY SEQUENCE "baseten_lock_seq";
-REVOKE ALL PRIVILEGES ON SEQUENCE "baseten_lock_seq" FROM PUBLIC;
-
 
 -- Helper functions
 
 GRANT EXECUTE ON FUNCTION "baseten".running_backend_pids () TO basetenread;
 
 
-CREATE FUNCTION "baseten".lock_next_id () RETURNS BIGINT AS $$
-	SELECT nextval ('baseten_lock_seq');
-$$ VOLATILE LANGUAGE SQL EXTERNAL SECURITY INVOKER;
-REVOKE ALL PRIVILEGES ON  FUNCTION "baseten".lock_next_id () FROM PUBLIC;
-GRANT EXECUTE ON FUNCTION "baseten".lock_next_id () TO basetenuser;
-
-
 define({{between_operator}}, {{
 CREATE FUNCTION "baseten".between ($1 [2], $1) RETURNS BOOLEAN AS $$
 	SELECT ${{}}1[1] <= ${{}}2 AND ${{}}2 <= ${{}}1[2];
 
 
 -- For modification tracking
+CREATE SEQUENCE "baseten".modification_id_seq MAXVALUE 2147483647 CYCLE;
 CREATE TABLE "baseten".modification (
-	"baseten_modification_id" INTEGER PRIMARY KEY,
+	"baseten_modification_id" INTEGER PRIMARY KEY DEFAULT nextval ('"baseten"."modification_id_seq"'),
 	"baseten_modification_relid" OID NOT NULL,
 	"baseten_modification_timestamp" TIMESTAMP (6) WITHOUT TIME ZONE NULL DEFAULT NULL,
 	"baseten_modification_insert_timestamp" TIMESTAMP (6) WITHOUT TIME ZONE NOT NULL DEFAULT clock_timestamp (),
 	"baseten_modification_type" CHAR NOT NULL,
 	"baseten_modification_backend_pid" INT4 NOT NULL DEFAULT pg_backend_pid ()
 );
-CREATE SEQUENCE "baseten".modification_id_seq CYCLE OWNED BY "baseten".modification."baseten_modification_id";
-CREATE FUNCTION "baseten".set_mod_id () RETURNS TRIGGER AS $$
-BEGIN
-	NEW."baseten_modification_id" = nextval ('"baseten"."modification_id_seq"');
-	RETURN NEW;
-END;
-$$ VOLATILE LANGUAGE PLPGSQL EXTERNAL SECURITY DEFINER;
-CREATE TRIGGER "set_mod_id" BEFORE INSERT ON "baseten".modification 
-	FOR EACH ROW EXECUTE PROCEDURE "baseten".set_mod_id ();
-REVOKE ALL PRIVILEGES ON FUNCTION "baseten".set_mod_id () FROM PUBLIC;
+ALTER SEQUENCE "baseten".modification_id_seq OWNED BY "baseten".modification."baseten_modification_id";
 REVOKE ALL PRIVILEGES ON SEQUENCE "baseten".modification_id_seq FROM PUBLIC;
 REVOKE ALL PRIVILEGES ON "baseten".modification FROM PUBLIC;
 GRANT SELECT ON "baseten".modification TO basetenread;
+GRANT USAGE ON SEQUENCE "baseten".modification_id_seq TO basetenuser;
 
 
+CREATE SEQUENCE "baseten".lock_id_seq MAXVALUE 2147483647 CYCLE;
 CREATE TABLE "baseten".lock (
-	"baseten_lock_backend_pid"	 INTEGER NOT NULL DEFAULT pg_backend_pid (),
-	"baseten_lock_id"			 BIGINT NOT NULL DEFAULT "baseten".lock_next_id (),
+	"baseten_lock_id"			 INTEGER PRIMARY KEY DEFAULT nextval ('"baseten"."lock_id_seq"'),
 	"baseten_lock_relid"		 OID NOT NULL,
 	"baseten_lock_timestamp"	 TIMESTAMP (6) WITHOUT TIME ZONE NOT NULL DEFAULT clock_timestamp (),
 	"baseten_lock_query_type"	 CHAR (1) NOT NULL DEFAULT 'U',	 -- U == UPDATE, D == DELETE
 	"baseten_lock_cleared"		 BOOLEAN NOT NULL DEFAULT FALSE,
 	"baseten_lock_savepoint_idx" BIGINT NOT NULL,
-	PRIMARY KEY ("baseten_lock_backend_pid", "baseten_lock_id")
+	"baseten_lock_backend_pid"	 INTEGER NOT NULL DEFAULT pg_backend_pid ()
 );
+ALTER SEQUENCE "baseten".lock_id_seq OWNED BY "baseten".lock."baseten_lock_id";
+REVOKE ALL PRIVILEGES ON SEQUENCE "baseten".lock_id_seq FROM PUBLIC;
 REVOKE ALL PRIVILEGES ON "baseten".lock FROM PUBLIC;
 GRANT SELECT ON "baseten".lock TO basetenread;
+GRANT USAGE ON SEQUENCE "baseten".lock_id_seq TO basetenuser;
 
 
 -- Functions
 BEGIN
 	PERFORM "baseten".observing_compatible_ex (relid);
 
-	-- Don't create if exists.
-	-- Using PERFORM & checking FOUND might cause a race condition.
-	BEGIN
-		CREATE TEMPORARY SEQUENCE "baseten_lock_seq";
-	EXCEPTION WHEN OTHERS THEN
-	END;
-
 	nname := "baseten".lock_notification (relid);
 	--RAISE NOTICE 'Observing: %', nname;
 	EXECUTE 'LISTEN ' || quote_ident (nname);
 		'AFTER INSERT ON "baseten".' || quote_ident (mod_table) || ' ' ||
 		'FOR EACH STATEMENT EXECUTE PROCEDURE "baseten".mod_notify (''' || "baseten".mod_notification (relid_) || ''')';
 	EXECUTE query;
-	query :=
-		'CREATE TRIGGER "set_mod_id" ' ||
-		'BEFORE INSERT ON "baseten".' || quote_ident (mod_table) || ' ' ||
-		'FOR EACH ROW EXECUTE PROCEDURE "baseten".set_mod_id ()';
-	EXECUTE query;
 	
 	-- Triggers for the enabled relation.
 	IF is_view THEN
Tip: Filter by directory path e.g. /media app.js to search for public/media/app.js.
Tip: Use camelCasing e.g. ProjME to search for ProjectModifiedEvent.java.
Tip: Filter by extension type e.g. /repo .js to search for all .js files in the /repo directory.
Tip: Separate your search with spaces e.g. /ssh pom.xml to search for src/ssh/pom.xml.
Tip: Use ↑ and ↓ arrow keys to navigate and return to view the file.
Tip: You can also navigate files with Ctrl+j (next) and Ctrl+k (previous) and view the file with Ctrl+o.
Tip: You can also navigate files with Alt+j (next) and Alt+k (previous) and view the file with Alt+o.