Re: Managing database change: improvements needed

> > > I'd try to write a script with good error catching.
> > It's not impossible, but a *lot* more work than tracking which changes have
> > 1) Number/name the scripts and keep a catalog of the names/numbers, which
> > can be queried by the update program
> > 2) Query PostgreSQL's own schema catalog for specific characteristics and
> > conditionally execute the DDL change commands
>
> > (2) would AFAIK require coding a specific stored procedure for each change,
> > while (1) is exactly the direction I'm proposing (by using Sqitch to do it
> > for us, that is).
> This ignores the fact that the current scripts work. The need is just to
> silence the noise!
While that's true, it's not all that is needed. The other item that I was thinking of addressing is that with Sqitch, we - devs - don't have to maintain the same schema twice anymore: once in Pg-database.sql and once in Fixes.sql. With Sqitch, I could deal with the two in one blow.

> The disadvantages of sqitching are: (1) splitting up the code clients or
> developers may want to review,
If the solution to my point above is to freeze Pg-database.sql and depend on Fixes.sql to be applied on top, that's probably worse than this point: Fixes.sql grows organically with new items continually appended. With Sqitch, changes to a namespace can be grouped into similarly named files, sorting them closely together in the same directory; providing a nice overview on a modular level.

> (2) fixing an unbroken process (time poorly spent),
Except that the process isn't completely unbroken, since both devs and users have their complaints (see my two improvements above). Additionally, writing scripts in such a way that loading them repeatedly doesn't throw errors hasn't been addressed in our codebase before yet. So, time probably needs to be spent one way or another.

There's another thing that's broken in the current process: error checking. There's no way to report failure to load Fixes.sql -- the way it's implemented today, that is. This is a major problem, because LedgerSMB assumes success after loading Fixes.sql (or rather: Fixes.sql *reports* success, but it doesn't know if it succeeded or not). Without error checking, there's no way to report problems to users, which makes up for a pretty uncontrolled process and environment. I think this needs to be addressed in order to be able to present a better user experience.

> (3) verification of verification scripts (logic errors
> and collisions)
Point taken. Maybe we don't want to use the verification scripts: the original should simply fail to load when there's a problem. The loader should detect that.

> (4) another dependency,
True.

> (5) reliability, and
> (6) dealing with user changes made by those who ignored sqitch
> as "only another dependency".
>From my understanding there is no problem to editing the schema without Sqitch until a Sqitch script tries to make a conflicting change.

> Sqitch reliability sounds good where there is one deployer; where
> deployers duplicate deployments it is not so well valued.
Do you have a reference to that? I haven't found anything on it, but if those experiences are around, then we should definitely consider those.

> > changes and (y') is the schema for the new version, including your changes
> > (as long as there are no conflicts, of course).
> Which is the modded users' unique concern.
> A namespace reservation could ease that issue.
Absolutely. I did find the advice you were looking for in the archives the other day: it was to create your own schema with its own tables and add the extra fields there, using the same pkey as in the original table.

> > This way, you get the benefit of re-using the efforts from the project and
> > a full re-analysis of the schema wouldn't be necessary.
> This seems like a red herring. A full re-analysis never came
> into consideration.

Ok. Nevertheless, with Sqitch you could simply add your changes to the deployment plan and all our additional changes would come in adding on top of that; if you and we make conflicting changes to stored procedures, Git will point that out when you pull in our changes.

> Are you overly enthused by what you believe sqitch will accomplish
> for lsmb?
Heh. I hope not. I'd like these things to happen rationally and based on the available facts and to address real issues. As a developer, I *think* I'm missing a tool like Sqitch to manage the multi-branch development process *and* a deployment tool to all the databases out there.

> I see the conversations about 1.5 and big changes that Chris and you
> would like to make. Those tell me that this is not a time for
> new toyism and feature creep, but a time for formalizing code and design,
> for making the code freezable, for finding code creating tools that will
> improve production, and maybe having the "big" rewrite.
Exactly. Which is why I took the time to build the Perl::Critic tests over the weekend: we want to have tools to help us support our development efforts; be it as development tools or as testing tools. Perl::Critic is the latter, I was thinking Sqitch being the former.

Regarding the big changes that we want to make: These are all changes to make the LedgerSMB product more robust in various ways including, but not limited to, the code dimension. These are the areas where we want to improve:

* Quality assurance
This is a development/release tool which helps assert functional stability
* Code base simplification
This is a developer tool to stirr flexibility and thereby developer productivity
To this extent 1.5 no longer does specific hoop-jumping to maintain the half-hearted Dojo integration that 1.4 has (by going full-pull on the Dojo integration now)
* User friendliness
This is a tool to help market adopition together with quality assurance
If the tool does what users expect it to do on the first run, that's greatly going to help market adoption. If people have to wade through lots of instructions and then still don't get it to work, even though the tool reports success,... then LedgerSMB is not going to end up on the short-list.
* (More) modern user experience
* And maybe others I forgot to name explicitly

In my mind, these are not feature creep. Nor is Sqitch. In my mind, the discussion about Sqitch *is* about formalizing design, as in: "we currently don't have a design regarding database upgrades, let's make one and implement it".

> In this train, sqitch is just an ill timed distraction.
Well, all the effort in the world isn't going to help market adoption if people can get a more stable and pleasurable experience elsewhere. To me, 1.5 is about having an all-over better user experience than 1.3 and 1.4. That means much stabler, much more responsive and definitely much more informative errors.

[snip]

> > No :-) But Sqitch does a number of things we currently don't do

> > 1) Check deployment consistency before deployment of new changes
> What happens if my 1.4.9 is not consistent, how do I move forward?
> Learn sqitch, I suppose.

Right. But what happens if our current procedure fails? How do you even find out?

> > 2) Allow verification of the deployment afterwards
> Somehow this has been done.
I don't understand this point. Currently we don't verify the install, that's for sure.

> > 3) Prevent duplicate application of changes,
> > --> making the deployment logs clean of errors
> > --> no longer frightening users who take a peek in this log
> Suppress the noise--one task.
Ah. But what about these:
* Controlled upgrades from 1.5.0 to 1.5.1 to ...
* Providing error feedback to users based on schema load failures
* Only report *real* errors in the logs, not errors

> > 4) Provide a downgrade path
> You already are renaming the schema thereby providing a down path
> with less complexity.

Copying the schema only happens when upgrading from 1.2 -> 1.3 -> 1.4. But not when upgrading 1.4.12->1.4.16. When you want to go back, all you can do is run the 1.4.12 schema files again and hope that that works. However, if 1.4.16 added a constraint, there's nothing in this procedure which will remove that exact constraint. So, there's no way to get back to 1.4.12 in a controlled way.

> > I'm looking at it from the perspective of
> > the software that the LedgerSMB project distributes. This means thousands
> > of databases across the world in way more than 2 data centers (sometimes
>
> But it is not your task to redeploy them. 97% of those probably have
> one company running and would likely prefer not to be bothered.
Bothered with what? I'm not asking them to learn Sqitch. The process for running upgrades will be the same for them as it will be now: just go to 'setup.pl', log in with a superuser and let setup.pl do the rest. Under the hood, things will be different, but that won't be visible.

> Those
> that have multiple companies would probably like to syncronise (modulo
> a test case) upgrades; again a simpler case. No one wants to downgrade
> past where they were.
Thanks for the vote of confidence here :-) I am pretty sure people want to do so though: there have been versions released which were generally good, but with a specific broken workflow which simply paralized one of the companies using it. They wanted to back out, waiting for the next release to fix the broken workflow.

> The beauty of the sqitch idea, is to organize the code, so moving amongst
> many different versions is easy. In the lsmb case, that supports a
> dis-incentive for users to upgrade.
Accounting users are naturally hesitant to upgrade a working system (and rightfully so). If that system documents the "steps back", that actually might be a great way to overcome upgrade-resistence.

> > just at home), completely out of the control of the project, where

> > deployments should "simply work", upgrading from anywhere from 1.2.<latest>
> > or SQL Ledger 2.6/2.8 to the version being upgraded to.
>
> I been lurking around for over two years and I've seen few
> how-do-I-get-from-A-to-B questions.
Right. However, in the same timeframe, there have been even more people declaring LedgerSMB is simply too hard to get installed. Part of that is because we're unable to provide people with feedback to diagnose the problems they're experiencing. One issue there is that loading the schema and Fixes.sql simply doesn't allow us to do that well enough.

> > I'd like the switch to Sqitch to help people with local schema
> > modifications to maintain their modified database and enjoy the regular
>
> As a customizing user: sqitch doesn't address my issue at all; if I install
> a VCS, sqitch doesn't do much for me.
Actually, it doesn't do anything for you in the sense that you probably have to learn an extra tool and that costs you extra energy (although, if you were a customizing user without VC knowledge, that too would be extra effort). But it *does* do something for the interaction between the project's ongoing work and your own modifications: it's my understanding that if we use Sqitch correctly, the stored procedures will always be stored in the same files. This means that if we change stored procedures that you had to change too, conflicts will arise. With the Fixes.sql approach, a new definition of the stored procedure will simply be lumped at the end of Fixes.sql, causing no conflict or warning of any kind (and hence a need for review).

> > Please note that I'm very much valueing your feedback,
> I am American, we are reknown for rudeness and insensitivity; I try
> to break that mold. I don't have a problem with you thinking I'm wrong;
> I hope to be at least as gentle as you.

Heh. Thanks. I hope I explained my line of reasoning well enough, even though you do not agree. We have seen Chris's opinion on the matter. I'll consult some of the other people that have some regular involvement in LedgerSMB and come up with a decision on how to proceed.

Thanks for your feedback!

Regards,

Erik.