Re: [Planner Dev] mysql database support

[Miles <miles jamkit com>]

Hi,

I've made a start on this, getting the sql adjusted to parse in mysql is 
not proving too tricky.

What I wanted to know/check though was how planner works when it saves 
to the database: is the process of using the database essentially

 - i choose a project from the list of projects in the database
 - planner loads all the data i need in from the project i chose
 - i do some work, and make changes to my project
 - i press save: planner saves all the data, even the data which is 
unchanged, to the database

i.e. it is not intended that if user A updates the resource list, by 
adding a new resource, at the same time as user B is adding tasks, then 
user B sees the new resource when they go to the resource list.  They 
are both effectively working on their own separate sandbox.

So, my thought was that perhaps a lot of the fiddly-ness of the database 
compatibility can be completely removed by using xml.

 - i choose save to database
 - planner generates a data file in its xml format, and gives it to the 
database plugin
 - the database plugin applies an XSL transform is applied which turns 
the xml into sql compatible with the particular database it is to be 
stored in
 - the database plugin runs the output sql against the database
 - the database plugin tells planner whether it worked or not

Similarly, if the planner xml data format has a schema, then xsl could 
be used to generate the build scripts for each database version.  This 
would be an advantage because it should (in theory!) be easier to 
maintain one schema and some xsl rather than several data plugins, 
several build scripts, etc, etc...  Plus it could also make debugging 
easier, as then a copy of planner's xml output should be enough to work 
out the cause of any problem in using the database.

Is this a profitable route to begin to pursue?  It suddenly struck me 
that the combination of the 'experimental' schema plus fractionally 
different mysql and postgres versions of build scripts and data plugins 
could be making for a maintenance disaster ... !

Any thoughts, suggestions, pros, cons?!  The only bit of this I don't 
know much about at the moment is the XML schema: the XSL part would be 
very simple though.

Miles


Richard Hult wrote:
> Miles wrote:
>
> > Hi,
>
>
> Hello,
>
> Support for MySQL (and other databases) is definitely a goal for us, and 
> mostly the reason for switching from using libpg directly to using gda.
>
> > Having looked at the planner source a bit, it seems that the main 
> > obstacle is that planner uses cursors for its queries.  The queries 
> > look simple enough though that they could be converted to SELECTs 
> > without problem.  Is this correct, or is there a particular reason for 
> > using cursors?
>
>
> I don't think there was any particular reason, just that it was an easy 
> and quick way to get things working with postgres at the time we did it.
>
> It would be great if you would look into this, especially since the 
> database support still is a bit experimental. This means that we still 
> have the freedom to change it around quite a bit, since we don't expect 
> people to use it in production yet (even if some might already do so).
>
> /Richard