Griffon26's Blog

Planner’s support for storing projects in databases has been rather broken for a while. I recently fixed a bug that caused Planner to crash when you tried to save over an existing project in the database (#397132), so it was once again possible to use a database for storage. There was also a problem with newer postgresql versions (#318440), but that turned out to be a bug in postgresql that has subsequently been solved in postgresql-8.2.5.

Just last week I got a request to add support for libgda-3, because some distributions were dropping support for older libgda versions. I expected this to be more or less a configure fix, but it turns out it was quite a bit more work than that.

Planner accesses the database from two files: libplanner/mrp-sql.c and src/planner-sql-plugin.c. Both used a convenience function called sql_execute_query() which was just a wrapper around gda’s gda_connection_execute_single_command() and is used to do both queries (SELECTs) and other commands (BEGIN, INSERT, …). It was even used to execute a whole bunch of commands in one go when setting up the planner database. In the mean time libgda has become more strict in checking what is passed to its functions (and rightly so), forcing me to properly fix this code.

The three functions available are gda_connection_execute_select_command(), gda_connection_execute_non_select_command() and gda_connection_execute_command(). The first two are just wrappers around the second. Since planner is only interested in query results for single select queries and does sometimes need to execute multiple commands at once, I decided to split sql_execute_query() into two functions:

• sql_execute_query():
  uses gda_connection_execute_select_command() and returns a GdaDataModel * that you need to pass to g_object_unref() when it’s done with it, and
• sql_execute_command()
  uses gda_connection_execute_command() and returns a gboolean indicating success or failure. This function cleans up whatever results it receives on its own.

I then went through the sources modifying all calls to sql_execute_query(). The result of calls to sql_execute_query() will now always be stored in a local variable named model, while the result of sql_execute_command() will be stored in the gboolean success. I think it’s important to have such a clear distinction between cases where you have to catch a return value and clean up an object and cases where you don’t.

After modifying configure.in to detect the right GDA version and making the above changes as well as a few other fixes, it was time to run some tests.
*create a few task*, *save*, *load*.. hmm.. did I just see that task duration change?…
*modify task length*, *save*, *load*.. no, wait.. the project start date is shifting.. it’s 31 January 2008 now
*modify something*, *save*, *load*.. now it’s 30 January 2008!?

I could hardly believe my changes were causing this problem and sure enough, the same happened with svn HEAD. Some debugging and quite a bit of reading of the postgresql docs made it clear that the cause of this was a different interpretation of dates between Planner and postgresql. On one hand we were doing the calculation from time to seconds-since-epoch ourselves in Planner, but on the other we were letting postgresql do the reverse calculation when a project was loaded. The time zone used in these calculations was UTC for Planner, but local time for postgresql.
An example:

A seconds-since-epoch value of 86400 (which is the number of seconds in a single day) corresponds to the timestamp 02 Jan 1970 00:00:00 UTC.

1. When planner saves a project with a start date equal to 86400, it will store 02 Jan 1970 in the database.
2. When planner loads the project, it asks postgresql to do the conversion to seconds-since-epoch. Postgresql interprets the date to be local time, which at this time is UTC + 1 in my case. My local time of 02 Jan 1970 00:00:00 corresponds to 01 Jan 1970 23:00:00 UTC, so postgresql returns 86400 – 3600 = 82800 seconds-since-epoch.
3. When planner saves this project again, it will store 01 Jan 1970 in the database as a date without time, shifting the project start date not just by an hour but by an entire day.

This bug has now been fixed, but I’m still working on the libgda-3 changes. I broke compatibility with older gda versions, so I’ll have to restore that. I’ve put the latest version of the libgda-3 patch online for anyone to take a look at it and maybe contribute. I’m only working on it during CET evenings, so there’s at least an 18-hour window for a second person to work on it without having to solve merge conflicts all the time. Just send me your changes before I start working on it the next day.

Posted by Griffon26 on Sunday, February 3rd, 2008 at 18:04.