[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
Re: Managing database change: improvements needed
- Subject: Re: Managing database change: improvements needed
- From: "R. Ransbottom" <..hidden..>
- Date: Sat, 12 Sep 2015 10:27:15 -0400
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
> > 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
> 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.
Ledger-smb-devel mailing list