[Planner Dev] Database setup tool.

[minus <minus toneby com>]

Hello!

during the last year or soo I've been sloooowly working on a small
program to do setup and upgrade of the planner database. 

The hardcoded values in main should be arguments to the program.

minus, who should not write at this time of the year.

-- 

/* -*- Mode: C; tab-width: 8; indent-tabs-mode: t; c-basic-offset: 8 -*- */
/*
 * Copyright (C) 2005 Imendio AB
 * Copyright (C) 2005 minus (Magnus Toneby) <minus toneby com>
 *
 * This program is free software; you can redistribute it and/or
 * modify it under the terms of the GNU General Public License as
 * published by the Free Software Foundation; either version 2 of the
 * License, or (at your option) any later version.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
 * General Public License for more details.
 *
 * You should have received a copy of the GNU General Public
 * License along with this program; if not, write to the
 * Free Software Foundation, Inc., 59 Temple Place - Suite 330,
 * Boston, MA 02111-1307, USA.
 */

#include <math.h>
#include <string.h>
#include <stdlib.h>
#include <glib.h>
#include <glib/gprintf.h>
#include <glib/gi18n.h>
#include <libgda/libgda.h>

#define STOP_ON_ERR GDA_COMMAND_OPTION_STOP_ON_ERRORS


static const gchar *
sql_get_last_error (GdaConnection *connection)
{
	GList       *list;
	GdaError    *error;
	const gchar *error_txt;

	list = (GList *) gda_connection_get_errors (connection);

	if (list == NULL) {
		return _("No errors reported.");
	}

	error = (GdaError *) g_list_last (list)->data;
      
	/* FIXME: Poor user, she won't get localized messages */
	error_txt = gda_error_get_description (error);

	return error_txt;
}


/* sort the largest first */
static gint
compare_version (gconstpointer newer, 
		 gconstpointer older)
{
	guint   subversion_old, subversion_new;
	guint   version_old, version_new;
	gchar **versionv_new, **versionv_old;

	const gchar* version_new_txt = *(const gchar**)newer;
	const gchar* version_old_txt = *(const gchar**)older;

	g_return_val_if_fail (version_new_txt != NULL, -1);
	g_return_val_if_fail (version_old_txt != NULL, 1);
	
	version_old = g_ascii_strtod (version_old_txt, NULL);
	version_new = g_ascii_strtod (version_new_txt, NULL);

	if (version_new > version_old) {
		return -1;
	}
	else if (version_old > version_new) {
		return 1;
	}
	
	/* Need to check subversion */
	versionv_old = g_strsplit (version_old_txt,".",-1);
	versionv_new = g_strsplit (version_new_txt,".",-1);

	subversion_old = g_ascii_strtod (versionv_old[1], NULL);
	subversion_new = g_ascii_strtod (versionv_new[1], NULL);

	g_strfreev (versionv_new);
	g_strfreev (versionv_old);

	if (subversion_new > subversion_old) {
		return -1;
	} 
	else if (subversion_new < subversion_old) {
		return 1;
	}
	return 0;
}


void
createTables(GdaConnection* connection,
	     const gchar* fileVersion)
{
	gchar *contents=NULL;
	gchar *database_file=NULL;
	GdaCommand* command=NULL;
	GdaDataModel* result=NULL;



	/* open transaction */

	database_file = g_strdup_printf("database-%s.sql",fileVersion);
	g_printf("filename: %s\n",database_file);
	

	g_file_get_contents (database_file, &contents, NULL, NULL);
	printf("q\n");
	command=gda_command_new(contents,GDA_COMMAND_TYPE_SQL,
				GDA_COMMAND_OPTION_STOP_ON_ERRORS);
	printf("p %d\n",command);
	result = gda_connection_execute_single_command(connection,
						       command,
						       NULL);

	printf("s\n");
	if (result) {
		/* update version tables  */	
		printf("database created\n");
		g_object_unref(result);
	} else {
		/* break */
		printf("failed creating database\n");
	}
       
	gda_command_free(command);
	g_free (contents);
	
	/*
	  if res==broken
	  rollback
	  else
	  commit
	*/
}





void
upgrade(GdaConnection* connection,
	const GPtrArray* upgradeVersions,
	int startUpgradeFrom)
{
	unsigned int i;
	gchar *contents=NULL;
	gchar *upgrade_file=NULL;
	gchar *upgrade_version=NULL;
	int result=0;


	/* open transaction */

	
	/* loop on files */
	for (i=0; i < upgradeVersions->len; i++) {
	{
		upgrade_version=g_ptr_array_index(upgradeVersions,i);
		upgrade_file = g_strdup_printf("upgrade-%s.sql",upgrade_version);
		g_printf("filename: %s\n",upgrade_file);

		g_file_get_contents (upgrade_file, &contents, NULL, NULL);
		
		
		/* result = sql_execute_query (connection, contents); */05
		g_free (contents);
		
		/*
		  if res==ok {
		  update version tables 
		  } else {
		  break
		  }
		*/
		
	}
	/*
	  if res==broken
	  rollback
	  else
	  commit
	*/
	}
	
}

int
findUpgradePath(const GPtrArray* upgradeVersions, 
		const gchar* databaseVersion, 
		const gchar* fileVersion)
{
	int i;
	int compareResult;
	gchar* q;

	g_print("dbv: %s\n",databaseVersion);
	g_print("fsv: %s\n",fileVersion);

	for (i=0; i < (int)upgradeVersions->len; i++) {
		g_print("u %d: %s\n",i,
			(gchar *)g_ptr_array_index(upgradeVersions,i));
	}

	for (i=(int)upgradeVersions->len-1; i>=0; i--) {
		q=g_ptr_array_index(upgradeVersions,i);
		compareResult=compare_version(&databaseVersion,&q);
		if (compareResult==0) break;
	}
	
	if (compareResult!=0) {
		return -1;
	}

	return i;
}



void 
upgradeDecision(GdaConnection* connection,
		const GPtrArray* databaseVersions,
		const GPtrArray* fileVersions,
		const GPtrArray* upgradeVersions)
{
	/* if databaseVersion is empty 
	     use first element of fileVersions
	   else:

	     if first element of databaseVersion < 
                first element in fileVersions:
	       try to find a upgrade path in upgradeVersions
	       if path exists
                 apply it (from lowest to highest)

	     else if first element of databaseVersion == 
                first element in fileVersions:
	       We are done! database is the latest known version.

	     else:
	       should not happend, something is messed up!
	*/
	int startUpgradeFrom=-1;
	gchar *dbv=NULL;
	gchar *fv=NULL;
	if (databaseVersions->len==0) {
		fv=g_ptr_array_index(fileVersions,0);
		createTables(connection,fv);
	} else {
		
		fv=g_ptr_array_index(fileVersions,0);
		dbv=g_ptr_array_index(databaseVersions,0);
		g_print("comp: %s %s\n",fv,dbv);
			
		switch (compare_version(&fv,&dbv))
		{
		case -1:
			/* version in db is lower than file */
			startUpgradeFrom=findUpgradePath(upgradeVersions,dbv,fv);
			if (startUpgradeFrom<0) {
				g_print("Could not find the version the " \
					"database claims to be at in the " \
					"database/update files\n");
			} else {
				upgrade(connection,upgradeVersions,startUpgradeFrom);
				g_print("-1\n");
			}
			break;
		case 0:
			/* version in db the same as the newest file */
			g_print("Nothin to do\n");
			break;
		case 1:
			/* version in db is higher than the newest file */
			g_print("I hope you are a developer...\n");
			g_print("Your database has gone into the future\n");
			break;
		}
	}
}
 
GPtrArray* 
readVersionsFromDatabase(GdaConnection* connection)
{
	GdaCommand*   command=NULL;
	GdaDataModel* data=NULL;
	GdaRow*       row=NULL;
	GdaValue*     value=NULL;
	int           rowcounter=0;
	GPtrArray*    result = NULL;
	int           rows=0;
	gchar*        valueData;

	result=g_ptr_array_new ();

	command=gda_command_new("SELECT value FROM property_global " \
				"WHERE prop_name='database_version'",
				GDA_COMMAND_TYPE_SQL,GDA_COMMAND_OPTION_STOP_ON_ERRORS);
	if (command==NULL) {
 		printf("command is null\n");
	}
	data=gda_connection_execute_single_command(connection,command,NULL);
	if (data==NULL) {
		printf("database exist, but no tables\n");
	} else {
		rows=gda_data_model_get_n_rows(data);
		
		for (rowcounter=0; rowcounter<rows; rowcounter++)
		{
			if ((row=(GdaRow*)gda_data_model_get_row(data,0))==NULL) {
				/*  table exists but is empty */
				/* FIX: how should this be handled */
			} else {
				value=gda_row_get_value(row,0);
				if (value==NULL) {
					printf("value is null");
				} else {
					valueData=g_strdup(gda_value_get_string(value));
					g_ptr_array_add(result,valueData);
				}
			}
		}
		g_object_unref(data);
	}
	gda_command_free(command);
	
	return result;
}


 GPtrArray *
 readDatabaseFileVersions(const gchar* directoryname)
 {
	 GPtrArray*     result=NULL;
	 GDir*          dir=NULL;
	const gchar*   name=NULL;
        gchar*         filenameUTF8=NULL;
	gchar*         prefixremoved=NULL;
	gchar*         reversed=NULL;
	gchar*         postfixremoved=NULL;
	gchar*         rereversed=NULL;

	result=g_ptr_array_new ();

	dir = g_dir_open (directoryname, 0, NULL);
	while ((name = g_dir_read_name (dir)) != NULL) {
		if (!g_str_has_suffix (name, ".sql")) {
			continue;
		}
		if (!g_str_has_prefix (name, "database-")) {
			continue;
		}

		filenameUTF8 = g_filename_to_utf8 (name,-1,NULL,NULL,NULL);
		if (!g_utf8_validate(filenameUTF8,-1,NULL)) {
			g_print("filename not valid utf8");
		}

		prefixremoved=g_strdup (g_utf8_offset_to_pointer(filenameUTF8,9));
		reversed=g_utf8_strreverse (prefixremoved,
					    g_utf8_strlen(prefixremoved,-1));
		postfixremoved=g_strdup (g_utf8_offset_to_pointer(reversed,4));
		rereversed=g_utf8_strreverse (postfixremoved,
					      g_utf8_strlen(postfixremoved,-1));
		
		g_free(prefixremoved);
		g_free(reversed);
		g_free(postfixremoved);
		
		g_ptr_array_add (result,rereversed);
	}
	g_dir_close (dir);
	
	return(result);
 }
 
 GPtrArray *
	 readDatabaseUpgradeVersions(const gchar* directoryname)
 {
	GPtrArray*   result=NULL;
	GDir*        dir=NULL;
	const gchar* name=NULL;
        gchar*       filenameUTF8=NULL;
	gchar*       prefixremoved=NULL;
	gchar*       reversed=NULL;
	gchar*       postfixremoved=NULL;
	gchar*       rereversed=NULL;

	result=g_ptr_array_new ();

	dir = g_dir_open (directoryname, 0, NULL);
	while ((name = g_dir_read_name (dir)) != NULL) {
		if (!g_str_has_suffix (name, ".sql")) {
			continue;
		}

		if (!g_str_has_prefix (name, "upgrade-")) {
			continue;
		}

		filenameUTF8 = g_filename_to_utf8 (name,-1,NULL,NULL,NULL);
		if (!g_utf8_validate(filenameUTF8,-1,NULL)) {
			g_print("filename not valid utf8");
		}

		prefixremoved=g_strdup (g_utf8_offset_to_pointer(filenameUTF8,8));
		reversed=g_utf8_strreverse (prefixremoved,
					    g_utf8_strlen(prefixremoved,-1));
		postfixremoved=g_strdup (g_utf8_offset_to_pointer(reversed,4));
		rereversed=g_utf8_strreverse (postfixremoved,
					     g_utf8_strlen(postfixremoved,-1));

		g_free(filenameUTF8);
		g_free(prefixremoved);
		g_free(reversed);
		g_free(postfixremoved);

		g_ptr_array_add (result,rereversed);
	}
	g_dir_close (dir);

	return(result);
}

 void findAndOrderDatabaseCommands(GdaConnection* connection,
				  const gchar* directoryName)
{
	GPtrArray* fileVersions=NULL;
	GPtrArray* upgradeVersions=NULL;
	GPtrArray* databaseVersions=NULL;
	int i;

	
	fileVersions=readDatabaseFileVersions(directoryName);
	upgradeVersions=readDatabaseUpgradeVersions(directoryName);
	databaseVersions=readVersionsFromDatabase(connection);
	
	g_ptr_array_sort(fileVersions,compare_version);
 	g_ptr_array_sort(upgradeVersions,compare_version);
	g_ptr_array_sort(databaseVersions,compare_version);
	
	upgradeDecision(connection,
			databaseVersions,fileVersions,upgradeVersions);

	/* print info */
	/*
	g_print("info:\n");

	for (i=0; i < fileVersions->len; i++) {
		g_print("f %d: %s\n",i,g_ptr_array_index(fileVersions,i));
	}
	for (i=0; i < upgradeVersions->len; i++) {
		g_print("u %d: %s\n",i,g_ptr_array_index(upgradeVersions,i));
	}
       	for (i=0; i < databaseVersions->len; i++) {
		g_print("d %d: %s\n",i,g_ptr_array_index(databaseVersions,i));
	}
	*/
	
	g_ptr_array_free(fileVersions,TRUE);
	g_ptr_array_free(upgradeVersions,TRUE);
	g_ptr_array_free(databaseVersions,TRUE); 
}
 
 int 
	 main (int argc,
	       char *argv[])
{
	GdaClient *client=NULL;
	GdaConnection *connection=NULL;
	
	/* parse options */
	const gchar *datasource="planner";
	
	const gchar *dbprovider="PostgreSQL";
	const gchar *dbhost="localhost";
	/* port */
	const gchar *dbname="plannerdb";
	const gchar *dbusername="planner";
	const gchar *dbpassword="plannerpassword";
	const gchar *DirectoryWithDBFiles="../data/sql";
	/* end of options */
	
	const gchar *source_dbstring;

	gda_init ("plannerGDA", "0.0", argc, argv);


	/* try to connect to database */
	/* no connection: database down? wrong host*/
        /* connection: */
	/* find databasedefinition and upgrade files */
	client = gda_client_new();
	connection = gda_client_open_connection(client,
						datasource, 
						dbusername, 
						dbpassword,
						GDA_CONNECTION_OPTIONS_READ_ONLY);
	/* no connection, try another way */
	if (connection == NULL) {
		g_print("No connection, yes\n");
		source_dbstring = g_strdup_printf ("HOST=%s;DATABASE=%s",dbhost,dbname);

		gda_config_save_data_source(datasource,dbprovider,
					    source_dbstring,"A planner database instance",
					    NULL,NULL);

		connection = gda_client_open_connection(client,
							datasource, 
							dbusername, 
							dbpassword,
							GDA_CONNECTION_OPTIONS_READ_ONLY);
	}
	
	if (connection != NULL) {
		g_print("Got connection\n");
		findAndOrderDatabaseCommands(connection,DirectoryWithDBFiles);
		gda_connection_close(connection);
	} else {
		g_print("No connection\n");
		return -1;
	}

	return 0;
}