Re: [Planner Dev] Database schemas, versions and upgrading paths

["lincoln phipps openmutual net" <lincoln phipps openmutual net>]

Alvaro del Castillo wrote:

> Hi guys!
>
> I am working in coding the database schema from a file. We will do it
> when the SQL module storage is loaded and I imagine the steps like:
>
> 1. Check if we have any planner SQL schemas:
>
> 	1.1 If we don't have any schemas we try to load the schemas from 	the
> file database-VERSION.sql. If we can't find the file, we 	have to report
> an error to the user. If we find the file, we'll 	load it in the
> database and continue execution. 
>
> 	1.2 If we have them, then go 2.
>
>
> 2. If 1.2 then check if we have the correct tables in the database:


To do this we really need that "planner" table which I was thinking
about which contains....

	tables_dtd  (text field)

as well as the other things (tables_prefix etc). This would
contain the "0.11" text. Though we know what we mean I want to
avoid the word "schema" as its a postgres database term.

Then the mrp-storage-sql would read that table and retrieve the
tables_dtd and see if it was legal for it. It would also read
the tables_prefix to know what to prefix further table reads with ;)
e.g. "hostedplanner_" thus whereas today it just does a get on
"project" it would actually be a get on "hostedplanner_project".
As mentioned this was to avoid clashing or table name where you
are on a hosted environment and have just one database.

The initial table of "planner" to bootstrap this could be
anything - maybe we make it part of the initial database
connect e.g.

 host:   (as today)
 database:  (as today)
 basetable:  (this is the table mentioned above default to "planner")
 username:  (as today)
 password:  (as today)

> We need to check that the actual schemas in the database are the good
> ones for our planner version. For example, when you upgrade from 0.13 to
> 0.14, maybe we have created a upgrade SQL file with the needed commands
> to upgrade the SQL schemas stuff.
>
> If we detect that the database schemas are from and older planner
> version (we need to put the planner version in someplace in schemas), we
> try to find the file "upgrade-OLD_VERSION-NEW_VERSION.sql". If we don't
> find any, then no upgrades are needed.
>
> If we find the file, we'll load it in the database.

Yes - us developers will have to craft the permitted upgrades
and create the appropriate "upgrade" .sql files. This is what I'd
started with in the 0.6 -> 0.11. The real problems will come
if its not just adding an attribute or table but moving
and processing existing data. We haven't looked at that yet.

But that'll come later - right now if we can simply create or
delete a database and upgrade an existing database via Planner
then that would be a great step forward for people.

> If we follow this way, we need to call the SQL files in a very strict
> way in order we don't break the code in storage sql module.
>
> I attach a patch with some ideas about the code.
>
> Cheers
>
> -- Alvaro
>
>
> ------------------------------------------------------------------------
>
> Index: libplanner/mrp-storage-sql.c
> ===================================================================
> RCS file: /cvs/gnome/planner/libplanner/mrp-storage-sql.c,v
> retrieving revision 1.2
> diff -u -b -B -p -r1.2 mrp-storage-sql.c
> --- libplanner/mrp-storage-sql.c	15 Jan 2004 19:51:15 -0000	1.2
> +++ libplanner/mrp-storage-sql.c	1 Jul 2004 05:03:01 -0000
> @@ -102,6 +102,26 @@ module_init (GTypeModule *module)
>  G_MODULE_EXPORT MrpStorageModule *
>  module_new (void *project)
>  {
> +	GDir*           dir;
> +	const gchar    *name;
> +	const gchar    *version_old_test="0.11";
> +
> +	/* Check for tables */
> +	dir = g_dir_open (SQL_DIR, 0, NULL);
> +	g_message ("Loading scheme data for tables from "SQL_DIR);
> +	while ((name = g_dir_read_name (dir)) != NULL) {
> +		if (strncmp (name + strlen (name) - 4, ".sql", 4) == 0) {
> +			gchar *schema = g_build_path (G_DIR_SEPARATOR_S,
> +						      SQL_DIR,
> +						      name,
> +						      NULL);
> +			g_message ("Schemas found in %s", schema);
> +			g_message ("Looking for schema: database-%s.sql", VERSION);
> +			g_message ("Looking from upgrade: upgrade-%s-%s.sql", version_old_test, VERSION);
> +			g_free (schema);				
> +		}
> +	}
> +	
>  	return MRP_STORAGE_MODULE (g_object_new (MRP_TYPE_STORAGE_SQL, NULL));
>  }
>  
>
>
> ------------------------------------------------------------------------
>
> _______________________________________________
> Planner-dev mailing list
> Planner-dev lists imendio com
> http://lists.imendio.com/mailman/listinfo/planner-dev