Re: Managing database change: improvements needed

["R. Ransbottom" <..hidden..>]

On Fri, Sep 11, 2015 at 02:35:58PM +0200, Erik Huelsmann wrote:

> > 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.

> sure, can you describe how you think you will track the main repository and
> how you will manage the database schema changes?

As a user, I'd have the trunk, branches for local production and local 
development.  I would look to have infrequent upgrades--roughly annually.

Beyond that: a branch to give back. 

Most local changes are simplistic, storing and displaying extra data fields.  
This was the territory I thought lsmb might have some protocols or 
conventions to help manage or just prevent name collisions.  Annually, 
checking them and rebuilding those would not be too onerous; that without
a VCS.

> > 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?

I meant that _small_ changes to the SQL, like above, are _relatively_ 
easy to find/track without a VCS.  That is about the nature of data 
definition languages.  If the SQL code is spread around, it is still 
findable (\dS tablename, pg_dump).  From 1.4.0 to 1.4.15, there have not 
been that many changes to the database.  

(So far the dojo code is pretty obscure to me, but I expect it to 
have other concerns when it is creating tables--anyway I will do Dojo 101.)

I don't have any special wisdom in dealing with database change.
But with a core business transaction application, I do have caution.

> > > >>> 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.

I would use make to generate Pg-database.sql from Fixes.sql.  
Pg-database.sql being refactored and Fixes.sql destroyed at appropriate 
milestones--stopping stones for user upgrades.

> Yes, the little scripts *will* be written over time, because we have small

Point taken.

> 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

I feel there is a qualitative difference to adding to a script versus
repeatedly starting on a blank page--context, style, less naming.

> 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.

I'd try to write a script with good error catching.  

> 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

Yes.  What else?

> (x<y)? If so, are you planning to develop scripts every time?

Yes, though I expect much reuse in the typical cases.

> 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.

This sounds very much like how it is done now: q/[Ss]qitch//g.
Is writing three scripts making things less difficult?

http://stories.iovation.com/why-we-sqitch seems like an example where
sqitch is worth the effort.  

Rob

------------------------------------------------------------------------------
_______________________________________________
Ledger-smb-devel mailing list
..hidden..
https://lists.sourceforge.net/lists/listinfo/ledger-smb-devel