Re: Managing database change: improvements needed

Hi Rob,

On Thu, Sep 10, 2015 at 9:53 PM, R. Ransbottom <..hidden..> wrote:

My question about user database changes was sparked by a vague
remembrance of a text saying to use inheritance. Apparently,
I misremembered.

For myself, I am leaning toward setting up a local repository.
I would have liked to avoid adding a larger version control
requirement for my eventual replacement.

Ok. With the situation as it is, that seems logical to me, but just to be sure, can you describe how you think you will track the main repository and how you will manage the database schema changes?

For minor local changes, managing the sql model is relatively easy,
it is the user view that tends to grow into the odd cracks.

Could you elaborate what you mean by this statement? If the sql model of your repository starts to diverge from the sql model in the main repository, how is that relatively easy to manage?

> >>> We have a main schema file for the table definitions and a number of
> >>> modules with stored procedures grouped by "subject".

> >>> Next to that, there's a "Fixes" file, which contains all incremental
> >>> updates to the schema since some version.

> >>> When I want to change the schema as part of our development, I need to
> >>> change the schema definition files *and* I need to add the schema changes
> >>> to the Fixes.sql file.

Sqitch would seem to just help break this down into lots of little scripts
that can be written over time. I don't see the benefit.

Yes, the little scripts *will* be written over time, because we have small changes to the database schema every now and then. Currently these small scripts are all "lumped" into a single large file called Fixes.sql. The problem with this approach is that changes which don't apply to the database being upgraded are applied regardless. As a result the transaction that they're in will fail, leading to humongous numbers of errors in the database creation/upgrade logs. As a result, it's very hard for people to report problems to our mailing list and even worse, users may have doubts about the quality of the software, with these huge numbers of errors.

It would be conceptually simpler to have side-by-side installations
and migrating. Having differing versions up could be of value.

Wondering what you mean here. Are you planning to copy over all data from one version to another every time you upgrade say from 1.4.x -> 1.4.y (x<y)? If so, are you planning to develop scripts every time?

Currently, we have scripts to do that for copying of data from LedgerSMB 1.2 and SQL Ledger 2.8 to 1.3/1.4. However, those scripts don't take customizations (user-adaptations) into account.

One reason why I like the way Sqitch works is that you'll be able to put your schema changes in sqitch files and deploy those on your LedgerSMB version. Then, when a new release comes out, you simply add those to the end of the list of sqitch files and run the standard LedgerSMB upgrade procedure. LedgerSMB will then simply upgrade the database with your modifications in it.

Bye,

Erik.

http://efficito.com -- Hosted accounting and ERP.

Robust and Flexible. No vendor lock-in.