Re: [Planner Dev] MySQL database access support

[Jon Wilkins <jon wilkins winskil co uk>]

On Wed, 2005-12-21 at 09:24 -0500, Kurt Maute wrote:
> On Tue, 2005-12-20 at 01:08 +0000, Jon Wilkins wrote:
> > Hi All,
> > 
> > I've spent some time over the past couple of days getting to grips with
> > the gda/postgresql bits of libplanner.
> > 
> > I've got planner opening a small project from mysql whilst retaining
> > support for postgresql (including the original CURSOR-based queries).
> 
> cool!
> 
> > The planner gui current builds a URI of
> >  sql://user:password(at)host#database&project_id
> > as a result of entry in the Open From Database dialog.  
> > 
> > This uri is passed through to libplanner which uses the "sql" prefix to
> > call code to open via the database storage module instead of file.  
> > 
> > By getting the planner gui to generate "mysql://" or "pgsql://" uri's
> > instead, I've enabled libplanner to select the correct gda database
> > provider.  The same uri prefix is used in mrp-sql.c (the source that
> > actually does the data access) to select between two different sets of
> > sql statements - one for postgresql and one for mysql.
> 
> I'm interested to know how you're implementing the mysql backend.  I'm
> hoping you're not creating a completely separate backend for mysql.
> Really, we should have a single, generic libgda backend which should
> work for any of the libgda supported databases - no?
> 

The current pgsql access uses libgda - and so using the same libgda code
to connect to a mysql database instead is should "simply" be a case of
setting the provider sting in mrp-sql.c to "MySQL" instead of
"PostgreSQL".  There's no point creating a module to use native mysql
access when the product already depends on libgda which can do most of
the work for us.

Once connected to postgres, the current code makes extensive use of
cursors to access the database - something that mysql does not support.
There has been discussion on this thead a while back about waiting until
MySQL v5 was out because that would support cursors, however from what I
can see in the mysql docs, cursors are only supported in stored
procedures -- so we can't just use a different provider and connect
seamlessly to different dbms servers.

There was also a suggestion in the list that we should standardise all
database access to SQL95 -- essentially using the "lowest common
denominator" - but the postgresql code using cursors works so why change
it?

It's not just the use of cursors that requires specific implementation
-- the postgresql queries use a postgresql-specific method for getting
Unix Epoch time stamps from data strings -- the mysql method is slightly
different.

Because of the use of cursors, the typical sequence of each query for
postgres is :

  - execute query to set/define cursor
  - check query worked (cursor created)
  - execute fetchall query to get data into cursor
  - check query worked worked (fetchall worked)
  ... then use the data
.. Notice that there are two sql queries being executed - one to create
the cursor and one to fetch the data.  Without using cursors, the
seqeunce is simpler and uses a single sql query:
  - execute query to get data
  - check query worked
  ... then use the data

So each function in mrp-sql that executes a query needs to have a
mechanism to check which dbms is being accessed and select the query &
number of calls appropriately.  At the moment I've used a switch rather
than an if() because it implies easier support for more than two options
(ie gives the structure to add access to XYZ DBMS in the future.

This leaves the code very readable but not, to my mind, 'elegant'.  The
attachment is an example showing "before" and "after" - this structure
is needed for every function that includes sql read and similar for
every sql write... 

I have been wondering if a better method would be to pull all the query
strings out, put them in a 2-d array then have the code select the query
from the array based on the operation required & the dbms. ...

 ie    SOME_ARRAY[mysql]     [get project table data]
    of SOME_ARRAY[postgresql][get project table data]  
    yielding the specific queries to get data from the projects table

But that still gives the problem of needing two sql executes for
postgres and one for mysql.  However, as the sequence of calls it pretty
much identical for the 12 of so functions that retrieve data from
database, I have also thought of creating a generic function to execute
the query - it would "magically" prepend the query with the appropriate
cursor commands for mysql - that way making the calling functions more
generic and allowing the queries to be selected from an array.  



> > And a question to Kurt/Richard....  Do you have any problems with using
> > the prefix/protocol on the uri to denote the database type?  In my code
> > I've left the current "sql" as a pseudonym for "pgsql" so that the
> > planner/libplanner interface is maintained.
> 
> I have no problem with this - my concern is that we ensure the code is
> as simple and maintainable as possible, so I'd like to hear more about
> your backend plan.
I'm all for simple maintenance!  

Hope this all makes sense - let me know if you're happy with this
approach.


jon


// Was...............
query = g_strdup_printf ("DECLARE mycursor CURSOR FOR SELECT * FROM resource WHERE proj_id=%d",
			data->project_id);
res = sql_execute_query (data->con, query);
g_free (query);
		
if (res == NULL) {
	g_warning ("DECLARE CURSOR command failed (resource) %s.",
			sql_get_last_error (data->con));
	goto out;
}
g_object_unref (res);
		
res = sql_execute_query (data->con, "FETCH ALL in mycursor");
if (res == NULL) {
	g_warning ("FETCH ALL failed for resource %s.",
			sql_get_last_error (data->con));
	goto out;
}

=========================================================================================
//
// Is now................
//

switch(sql_type){
	case MRP_SQL_POSTGRESQL:{
		query = g_strdup_printf ("DECLARE mycursor CURSOR FOR SELECT * FROM resource WHERE proj_id=%d",
					data->project_id);
		res = sql_execute_query (data->con, query);
		g_free (query);
	
		if (res == NULL) {
			g_warning ("DECLARE CURSOR command failed (resource) %s.",
					sql_get_last_error (data->con));
			goto out;
		}
		g_object_unref (res);
		
		res = sql_execute_query (data->con, "FETCH ALL in mycursor");
		if (res == NULL) {
			g_warning ("FETCH ALL failed for resource %s.",
					sql_get_last_error (data->con));
			goto out;
		}
		break;
	}
	case MRP_SQL_MYSQL:{
		query = g_strdup_printf ("SELECT * FROM resource WHERE proj_id=%d",
					data->project_id);
		res = sql_execute_query (data->con, query);
		g_free (query);
		
		if (res == NULL) {
			g_warning ("DECLARE CURSOR command failed (resource) %s.",
					sql_get_last_error (data->con));
			goto out;
		}
		break;
	}
	default:{
		res=NULL;
		break;
	}
}