Mailing-List: contact cloudstack-dev-help@incubator.apache.org; run by ezmlm
Precedence: bulk
Reply-To: cloudstack-dev@incubator.apache.org
Received-SPF: pass (athena.apache.org: domain of will.chan@citrix.com
 designates 66.165.176.63 as permitted sender)
From: Will Chan <will.chan@citrix.com>
To: "cloudstack-dev@incubator.apache.org"
	<cloudstack-dev@incubator.apache.org>
Date: Mon, 13 Aug 2012 17:58:32 -0700
Subject: RE: schema maintenance
Thread-Topic: schema maintenance
Thread-Index: Ac15tOYTCjw1/WhOTXKQo6f3PcmakwAAfcPw
Message-ID: 
 <61AE1E2837A06D4A8E98B796183842D40129301A9217@SJCPMAILBOX01.citrite.net>
References: 
 <20120813170647.d3d18d9a633cb81ed61112bf108fc615.0f7b7f6fc3.wbe@email00.secureserver.net>
 <CA+96GG4Q-BUe2h0O85QiLCPgzDQ4kCNDwf-Azt6vMvqfj4dn+g@mail.gmail.com>
In-Reply-To: 
 <CA+96GG4Q-BUe2h0O85QiLCPgzDQ4kCNDwf-Azt6vMvqfj4dn+g@mail.gmail.com>
Accept-Language: en-US
Content-Language: en-US
acceptlanguage: en-US
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
MIME-Version: 1.0


> -----Original Message-----
> From: Chip Childers [mailto:chip.childers@sungard.com]
> Sent: Monday, August 13, 2012 5:36 PM
> To: cloudstack-dev@incubator.apache.org
> Subject: Re: schema maintenance
>=20
> On Mon, Aug 13, 2012 at 8:06 PM, Darren Shepherd
> <darren@godaddy.com> wrote:
> > Has there been any talk about not maintaining a monolithic
> > create_schema.sql and doing a more DB migrate style?  From a
> > supportability perspective its way easier if your SQL is just always
> > deltas from the last.  For example, you ship version 1 and create
> > schema1.sql.  Version 2 you add some stuff so you create a schema2.sql
> > that adds what you need to the database (just delta).  So if somebody
> > installs from scratch they run schema1.sql and schema2.sql.  If you
> > already have version 1, you just run schema2.sql.
> >
> > This is an extremely common approach and cuts down the need to test
> > the DB from scratch and upgrade.
> >
> > Darren
>=20
> +1 - I'm a huge fan of this, based on lots of time using the Django /
> Python world's South framework [1].  That approach supports schema and
> data migrations, rolling forwards and backwards.  It might not be possibl=
e
> to easily get to that level of sophistication, but it makes operation of =
an
> environment much easier.
>=20
> However, there does come a time when the total number of migrations gets
> out of hand.  I'm not sure we can get away without a "create db"
> script that essentially does 80% of the schema build...
>=20
> -chip
>=20
> [1] - http://south.aeracode.org/

CloudStack has effectively done that for upgrade scenarios.  You'll see thi=
ngs like schema-3.0.0to3.0.1.sql files.  It includes both the incremental D=
B changes from one version to the next but also includes migration scripts =
as well.  We also kept the monolithic schema file for convenience but I dou=
bt it will be that much work to simply call all the upgrade SQL rather than=
 that single file. =20

The only part is I agree with Chip that after some time, it could get big a=
nd slow to apply all those sql files if you were to start from scratch.  Fr=
om a development point of view, that single create-schema.sql file deploys =
much faster than 30 separate sql files and I assume it will just get slower=
 as time progresses.

Will