[WWdevel] Re: problem sets

[Sam H. <sh...@ma...>]

On Sep 1, 2005, at 11:00, Michael Gage wrote:

> On Sep 1, 2005, at 10:50 AM, Sam Hathaway wrote:
>
>> I agree. And we should fix the database import/export  
>> functionality for 2.1.4 so that people can transfer their courses  
>> from gdbm/sql to sql_single easily.
>
> Yes.  That would help a lot.  In addition we need facility that  
> upgrades the structure of a sql database when it changes.  The update
> facilities in moodle work very well in this regard.  When you  
> update to a new version it triggers a module that updates the  
> database.

Hi,

I took a look at Moodle's database upgrade functionality in more  
depth. Moodle stores the version of each module in a database table.  
If the current version of a module is newer, it allows the module to  
run a funtion that gets the old version number. This function usually  
just adds/modifies/removes database tables, but it can do other  
things, including modifying data in tables, etc.

I've apparently done most of the work necessary for this already, in  
the course of working on WWDBv3.

The first thing we'll have to do is add a `setting` table and define  
a `db_version` record in that table. This can actually be done  
automatically, by checking for that table and record, and adding them  
if they don't exist.

Every time the database gets loaded, it will check the value of  
`db_version`, and call a function for each version upgrade between  
the value stored in `db_version` and the current version.

Convenient things to do with this mechanism:

* Add fields to tables, add new tables,
* Normalize existing statuses in the database, once we figure out  
what we're going to do about that.
* Fix the NULL/empty string/zero situation.
* This might be too ambitious, but we could probably use this  
mechanism to split off the setversion information that's currently  
encoded in the set ID (with ",v") into separate tables.

It occurs to me that the system should automatically make backups of  
the current database before modifying it.
-sam