SQL Databases from before 2.3.16

[John Ralls]

On Dec 8, 2010, at 12:42 PM, Derek Atkins wrote:

> John Ralls <jralls at ceridwen.us> writes:
> 
>> On Dec 8, 2010, at 6:36 AM, Phil Longstaff wrote:
>> 
>>> Support for this already exists.  There's a "versions" table which has 
>>> table-name/table-version pairs.  This is loaded automatically when the file is 
>>> opened.  The backend code for each object type contains a "create tables" 
>>> routine which is called to create and/or update tables for that object type.  At 
>>> this time, if the table version is not current, it can be updated.  There is 
>>> already some code for various object types which have handled some upgrades 
>>> through the 2.3.X series.
>>> 
>> 
>> That addresses changes to the schema (that is, how many columns are in a table, what their names and types are, which ones are keys, etc.) but not changes to how the rows are saved and retrieved in the backend. The changes I made in r19729 and r19911 were in the latter category.
> 
> Maybe this is a silly question, but why couldn't we use the versions
> table to notice this, too?  It's still a bug in the SQL, and we should
> get used to fixing SQL bugs internally.
> 
> I understand it might take a few tries to get right, but that's what
> "mysqldump" is for.  :)

The versions table has only one row for each table, having the table name and a version number. As long as the schema of the table doesn't change, different versions of Gnucash whose table description array matches the schema can write to the table... but they might understand the data differently. That's a row-by-row difference and the version table isn't fine-grained enough to capture it. That's the way it works now.

But we could add a row to the versions table with the last Gnucash version to touch the database. When we write a change to the backend that changes the way data are stored, we can invoke a routine that reads everything in the old way and writes it back out the new way, then updates the gnucash entry in the versions table. Older versions, not knowing what newer versions have changed, would have to punt with an error dialog ("Sorry, the database has been written by a newer version of Gnucash. This version can't safely read it.") It would get unwieldy in pretty short order (like the scrub routines in engine), but we could do it.

All of which is entirely skew to cleaning out the extra rows in Elwood's slots table. 

Oh, and there isn't enough sql yet to have "bugs in the sql". The sql is all very simple (select * from foo where guid="bar" is about as complex as it gets right now) with all of the interesting work done by qof. Rather a waste of an RDB, frankly. We'll have to change that in order to support multiple access, and the redesign will probably reach through qof into the engine. That will be another discussion, for after we get 2.4 released.

Regards,
John Ralls