Schema version control
Hi all,
My company is having trouble managing how we upgrade schema changes across many versions of our software. I imagine this is a common problem and there're probably some neat solutions that we don't know about.
For the last 10 years we have been writing bash shell scripts essentially numbered in order db0001, db0002, db0003.... The number represents the schema version which is recorded in the database and updated by the shell scripts. We have a template that provides all the functionality we need, we just copy the script and fill in the blanks. The schema upgrade scripts are committed to svn along with the software changes, and we have a process when installing the software at a site that runs the scripts on the DB in order before starting up the new version of the software.
This has worked really well so far. But we've effectively only had one version of the software in development at any time. We're now in the habit of branching the software to form releases to promote stability when making large changes. The idea is that only really important changes are merged in to the releases. This introduces a bit of a problem when some change needs to be merged from one release to another. The typical problem is that we might have two versions of the software 10.0 at schema version 10057 and 11.0 at 11023 and we need to merge an important bug fix from schema 11023 in to 10.0. The issue is that 11023 might depend upon changes introduced in the schema versions before it. Or 11023 might introduce changes that cause later scripts to break (11000 - 11023) when upgrading from 10.0 to 11.0.
One potential solution is to require that schema changes are never merged in to a release, but of course sometimes business requires we do =(
I'm really interested to hear how you guys manage schema upgrades in the face of branches and upgrading from many different versions of the database.
I've been reading http://pyrseas.wordpress.com/2011/02/07/version-control-part-2-sql-databases/ but I have a feeling that this blog post won't address branches.
Cheers!
--Royce
On 02/10/2011 02:38 PM, Royce Ausburn wrote:
Hi all,
My company is having trouble managing how we upgrade schema changes across many versions of our software. I imagine this is a common problem and there're probably some neat solutions that we don't know about.
For the last 10 years we have been writing bash shell scripts essentially numbered in order db0001, db0002, db0003.... The number represents the schema version which is recorded in the database and updated by the shell scripts. We have a template that provides all the functionality we need, we just copy the script and fill in the blanks. The schema upgrade scripts are committed to svn along with the software changes, and we have a process when installing the software at a site that runs the scripts on the DB in order before starting up the new version of the software.
This has worked really well so far. But we've effectively only had one version of the software in development at any time. We're now in the habit of branching the software to form releases to promote stability when making large changes. The idea is that only really important changes are merged in to the releases. This introduces a bit of a problem when some change needs to be merged from one release to another. The typical problem is that we might have two versions of the software 10.0 at schema version 10057 and 11.0 at 11023 and we need to merge an important bug fix from schema 11023 in to 10.0. The issue is that 11023 might depend upon changes introduced in the schema versions before it. Or 11023 might introduce changes that cause later scripts to break (11000 - 11023) when upgrading from 10.0 to 11.0.
One potential solution is to require that schema changes are never merged in to a release, but of course sometimes business requires we do =(
I'm really interested to hear how you guys manage schema upgrades in the face of branches and upgrading from many different versions of the database.
I've been reading http://pyrseas.wordpress.com/2011/02/07/version-control-part-2-sql-databases/ but I have a feeling that this blog post won't address branches.
Cheers!
--Royce
Don't the bash scripts get checked in to .../perforce/cvs/svn/git/...?
Aren't they part of the resources of the project(s)?
On 2/10/2011 3:38 PM, Royce Ausburn wrote:
Hi all,
My company is having trouble managing how we upgrade schema changes across many versions of our software. I imagine this is a common problem and there're probably some neat solutions that we don't know about.
For the last 10 years we have been writing bash shell scripts essentially numbered in order db0001, db0002, db0003.... The number represents the schema version which is recorded in the database and updated by the shell scripts. We have a template that provides all the functionality we need, we just copy the script and fill in the blanks. The schema upgrade scripts are committed to svn along with the software changes, and we have a process when installing the software at a site that runs the scripts on the DB in order before starting up the new version of the software.
This has worked really well so far. But we've effectively only had one version of the software in development at any time. We're now in the habit of branching the software to form releases to promote stability when making large changes. The idea is that only really important changes are merged in to the releases. This introduces a bit of a problem when some change needs to be merged from one release to another. The typical problem is that we might have two versions of the software 10.0 at schema version 10057 and 11.0 at 11023 and we need to merge an important bug fix from schema 11023 in to 10.0. The issue is that 11023 might depend upon changes introduced in the schema versions before it. Or 11023 might introduce changes that cause later scripts to break (11000 - 11023) when upgrading from 10.0 to 11.0.
One potential solution is to require that schema changes are never merged in to a release, but of course sometimes business requires we do =(
I'm really interested to hear how you guys manage schema upgrades in the face of branches and upgrading from many different versions of the database.
I've been reading http://pyrseas.wordpress.com/2011/02/07/version-control-part-2-sql-databases/ but I have a feeling that this blog post won't address branches.
Cheers!
--Royce
So, 10.0 at 10057.
11.0 at 11023.
then 10.1 needs some fixes so db is bumped to 10058.
Then, later, you can upgrade 10057 to 11023, but you cant get 10058 to
11023.
Humm... maybe you need smarter upgrade scripts? Would having logic in
the script help? Something like:
if not fieldExists('xyz) then alter table ... add xyz ...
Or, maybe your schema numbering system is to broad? Maybe each table
could have a version number?
Or some kinda flags like:
create table dbver(key text);
then an update would be named: "add xyz to bob".
then the update code:
q = select key from dbver where key = 'add xyz to bob';
if q.eof then
alter table bob add xyz
-Andy
On 2/10/2011 4:14 PM, Andy Colson wrote:
On 2/10/2011 3:38 PM, Royce Ausburn wrote:
Hi all,
My company is having trouble managing how we upgrade schema changes
across many versions of our software. I imagine this is a common
problem and there're probably some neat solutions that we don't know
about.For the last 10 years we have been writing bash shell scripts
essentially numbered in order db0001, db0002, db0003.... The number
represents the schema version which is recorded in the database and
updated by the shell scripts. We have a template that provides all the
functionality we need, we just copy the script and fill in the blanks.
The schema upgrade scripts are committed to svn along with the
software changes, and we have a process when installing the software
at a site that runs the scripts on the DB in order before starting up
the new version of the software.This has worked really well so far. But we've effectively only had one
version of the software in development at any time. We're now in the
habit of branching the software to form releases to promote stability
when making large changes. The idea is that only really important
changes are merged in to the releases. This introduces a bit of a
problem when some change needs to be merged from one release to
another. The typical problem is that we might have two versions of the
software 10.0 at schema version 10057 and 11.0 at 11023 and we need to
merge an important bug fix from schema 11023 in to 10.0. The issue is
that 11023 might depend upon changes introduced in the schema versions
before it. Or 11023 might introduce changes that cause later scripts
to break (11000 - 11023) when upgrading from 10.0 to 11.0.One potential solution is to require that schema changes are never
merged in to a release, but of course sometimes business requires we
do =(I'm really interested to hear how you guys manage schema upgrades in
the face of branches and upgrading from many different versions of the
database.I've been reading
http://pyrseas.wordpress.com/2011/02/07/version-control-part-2-sql-databases/
but I have a feeling that this blog post won't address branches.Cheers!
--Royce
So, 10.0 at 10057.
11.0 at 11023.then 10.1 needs some fixes so db is bumped to 10058.
Then, later, you can upgrade 10057 to 11023, but you cant get 10058 to
11023.Humm... maybe you need smarter upgrade scripts? Would having logic in
the script help? Something like:if not fieldExists('xyz) then alter table ... add xyz ...
Or, maybe your schema numbering system is to broad? Maybe each table
could have a version number?Or some kinda flags like:
create table dbver(key text);then an update would be named: "add xyz to bob".
then the update code:
q = select key from dbver where key = 'add xyz to bob';
if q.eof then
alter table bob add xyz-Andy
D'oh! a bug in my update script:
q = select key from dbver where key = 'add xyz to bob';
if q.eof then
alter table bob add xyz
insert into dbver('add xyz to bob');
How embarrassing :-)
-Andy
We have this kickass solution we built at work called dbsteward that
just takes care of all of this for us, automatically. You just give
it the new version and the old version and it generates update statements
to feed into PG.
The reason I'm bringing this up is that we're working to release
dbsteward as open source for PGCon. So, if you can wait a bit, you
can jump on that train.
In response to Andy Colson <andy@squeakycode.net>:
On 2/10/2011 3:38 PM, Royce Ausburn wrote:
Hi all,
My company is having trouble managing how we upgrade schema changes across many versions of our software. I imagine this is a common problem and there're probably some neat solutions that we don't know about.
For the last 10 years we have been writing bash shell scripts essentially numbered in order db0001, db0002, db0003.... The number represents the schema version which is recorded in the database and updated by the shell scripts. We have a template that provides all the functionality we need, we just copy the script and fill in the blanks. The schema upgrade scripts are committed to svn along with the software changes, and we have a process when installing the software at a site that runs the scripts on the DB in order before starting up the new version of the software.
This has worked really well so far. But we've effectively only had one version of the software in development at any time. We're now in the habit of branching the software to form releases to promote stability when making large changes. The idea is that only really important changes are merged in to the releases. This introduces a bit of a problem when some change needs to be merged from one release to another. The typical problem is that we might have two versions of the software 10.0 at schema version 10057 and 11.0 at 11023 and we need to merge an important bug fix from schema 11023 in to 10.0. The issue is that 11023 might depend upon changes introduced in the schema versions before it. Or 11023 might introduce changes that cause later scripts to break (11000 - 11023) when upgrading from 10.0 to 11.0.
One potential solution is to require that schema changes are never merged in to a release, but of course sometimes business requires we do =(
I'm really interested to hear how you guys manage schema upgrades in the face of branches and upgrading from many different versions of the database.
I've been reading http://pyrseas.wordpress.com/2011/02/07/version-control-part-2-sql-databases/ but I have a feeling that this blog post won't address branches.
Cheers!
--Royce
So, 10.0 at 10057.
11.0 at 11023.then 10.1 needs some fixes so db is bumped to 10058.
Then, later, you can upgrade 10057 to 11023, but you cant get 10058 to
11023.Humm... maybe you need smarter upgrade scripts? Would having logic in
the script help? Something like:if not fieldExists('xyz) then alter table ... add xyz ...
Or, maybe your schema numbering system is to broad? Maybe each table
could have a version number?Or some kinda flags like:
create table dbver(key text);then an update would be named: "add xyz to bob".
then the update code:
q = select key from dbver where key = 'add xyz to bob';
if q.eof then
alter table bob add xyz-Andy
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
--
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/
On 2/10/2011 4:18 PM, Bill Moran wrote:
We have this kickass solution we built at work called dbsteward that
just takes care of all of this for us, automatically. You just give
it the new version and the old version and it generates update statements
to feed into PG.The reason I'm bringing this up is that we're working to release
dbsteward as open source for PGCon. So, if you can wait a bit, you
can jump on that train.In response to Andy Colson<andy@squeakycode.net>:
But... that assumes all updates are DDL only? What if I have a lookup
table, and want to add some new entries into it?
-Andy
Top-posting is frowned upon by some (not me), but since Bill started it...
I for one will be waiting to see your dbsteward. How does it compare
functionally or stylistically with Ruby's migration tools (which I found
to be pretty cool and frustrating all in one go).
Show quoted text
On 02/10/2011 03:18 PM, Bill Moran wrote:
We have this kickass solution we built at work called dbsteward that
just takes care of all of this for us, automatically. You just give
it the new version and the old version and it generates update statements
to feed into PG.The reason I'm bringing this up is that we're working to release
dbsteward as open source for PGCon. So, if you can wait a bit, you
can jump on that train.In response to Andy Colson <andy@squeakycode.net>:
On 2/10/2011 3:38 PM, Royce Ausburn wrote:
Hi all,
My company is having trouble managing how we upgrade schema changes across many versions of our software. I imagine this is a common problem and there're probably some neat solutions that we don't know about.
For the last 10 years we have been writing bash shell scripts essentially numbered in order db0001, db0002, db0003.... The number represents the schema version which is recorded in the database and updated by the shell scripts. We have a template that provides all the functionality we need, we just copy the script and fill in the blanks. The schema upgrade scripts are committed to svn along with the software changes, and we have a process when installing the software at a site that runs the scripts on the DB in order before starting up the new version of the software.
This has worked really well so far. But we've effectively only had one version of the software in development at any time. We're now in the habit of branching the software to form releases to promote stability when making large changes. The idea is that only really important changes are merged in to the releases. This introduces a bit of a problem when some change needs to be merged from one release to another. The typical problem is that we might have two versions of the software 10.0 at schema version 10057 and 11.0 at 11023 and we need to merge an important bug fix from schema 11023 in to 10.0. The issue is that 11023 might depend upon changes introduced in the schema versions before it. Or 11023 might introduce changes that cause later scripts to break (11000 - 11023) when upgrading from 10.0 to 11.0.
One potential solution is to require that schema changes are never merged in to a release, but of course sometimes business requires we do =(
I'm really interested to hear how you guys manage schema upgrades in the face of branches and upgrading from many different versions of the database.
I've been reading http://pyrseas.wordpress.com/2011/02/07/version-control-part-2-sql-databases/ but I have a feeling that this blog post won't address branches.
Cheers!
--Royce
So, 10.0 at 10057.
11.0 at 11023.then 10.1 needs some fixes so db is bumped to 10058.
Then, later, you can upgrade 10057 to 11023, but you cant get 10058 to
11023.Humm... maybe you need smarter upgrade scripts? Would having logic in
the script help? Something like:if not fieldExists('xyz) then alter table ... add xyz ...
Or, maybe your schema numbering system is to broad? Maybe each table
could have a version number?Or some kinda flags like:
create table dbver(key text);then an update would be named: "add xyz to bob".
then the update code:
q = select key from dbver where key = 'add xyz to bob';
if q.eof then
alter table bob add xyz-Andy
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
In response to Andy Colson <andy@squeakycode.net>:
On 2/10/2011 4:18 PM, Bill Moran wrote:
We have this kickass solution we built at work called dbsteward that
just takes care of all of this for us, automatically. You just give
it the new version and the old version and it generates update statements
to feed into PG.The reason I'm bringing this up is that we're working to release
dbsteward as open source for PGCon. So, if you can wait a bit, you
can jump on that train.In response to Andy Colson<andy@squeakycode.net>:
But... that assumes all updates are DDL only? What if I have a lookup
table, and want to add some new entries into it?
It has provisions for maintaining static data as well. We have a bunch
of lookup tables (too many, in my opinion) and it does an excellent job
of maintaining them. They just need to have a primary key, but that's
not usually a problem with lookup tables.
--
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/
Royce Ausburn wrote on 10.02.2011 22:38:
I'm really interested to hear how you guys manage schema upgrades in
the face of branches and upgrading from many different versions of
the database.
We are quite happy with Liquibase. You can simply run it against a database and tell it to migrate it to "Version x.y"
As it keeps track of all changes applied it automatically knows what to do.
I can handle static data as well as stored procedure and any custom SQL.
Regards
Thomas
In response to Rob Sargent <robjsargent@gmail.com>:
Top-posting is frowned upon by some (not me), but since Bill started it...
Oops ... the weird thing is that I'm usually really anal about not top-
posting ...
I for one will be waiting to see your dbsteward. How does it compare
functionally or stylistically with Ruby's migration tools (which I found
to be pretty cool and frustrating all in one go).
I'm not familiar with Ruby's migration tools, so I can't say much.
The overview:
You store your schema and data as XML (this is easy to migrate to, because
it includes a tool that makes the XML from a live database)
Keep your XML schema files in some RCS.
When it's time for a new deployment, you run the dbsteward tool against
the schema XML and it turns it into DDL and DML.
When it's time for an upgrade, you run the dbsteward tool against two
schema XML files, and it calculates what has changed and generates the
appropriate DDL and DML to upgrade.
So ... you know, however that compares with the Ruby stuff is how it
does.
--
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/
Bill Moran wrote on 10.02.2011 23:59:
The overview:
You store your schema and data as XML (this is easy to migrate to, because
it includes a tool that makes the XML from a live database)
Keep your XML schema files in some RCS.
When it's time for a new deployment, you run the dbsteward tool against
the schema XML and it turns it into DDL and DML.
When it's time for an upgrade, you run the dbsteward tool against two
schema XML files, and it calculates what has changed and generates the
appropriate DDL and DML to upgrade.
This very much sounds like Liquibase. Do you happen to know any differences?
Regards
Thomas
On Thu, 10 Feb 2011 17:59:30 -0500, Bill Moran <wmoran@potentialtech.com>
wrote:
The overview:
You store your schema and data as XML (this is easy to migrate to,
because
it includes a tool that makes the XML from a live database)
We're doing a similar thing here except we're a Lisp shop so our
schema is defined as a set of "defentities" and we can migrate from
one version to another using a corresponding set of "defmaps".
Keep your XML schema files in some RCS.
When it's time for a new deployment, you run the dbsteward tool against
the schema XML and it turns it into DDL and DML.
When it's time for an upgrade, you run the dbsteward tool against two
schema XML files, and it calculates what has changed and generates the
appropriate DDL and DML to upgrade.
This sounds pretty cool. Ours doesn't do that yet but that's next
on my TODO list.
--
Andy Chambers
On 02/10/2011 03:59 PM, Bill Moran wrote:
In response to Rob Sargent <robjsargent@gmail.com>:
Top-posting is frowned upon by some (not me), but since Bill started it...
Oops ... the weird thing is that I'm usually really anal about not top-
posting ...I for one will be waiting to see your dbsteward. How does it compare
functionally or stylistically with Ruby's migration tools (which I found
to be pretty cool and frustrating all in one go).I'm not familiar with Ruby's migration tools, so I can't say much.
The overview:
You store your schema and data as XML (this is easy to migrate to, because
it includes a tool that makes the XML from a live database)
Keep your XML schema files in some RCS.
When it's time for a new deployment, you run the dbsteward tool against
the schema XML and it turns it into DDL and DML.
When it's time for an upgrade, you run the dbsteward tool against two
schema XML files, and it calculates what has changed and generates the
appropriate DDL and DML to upgrade.So ... you know, however that compares with the Ruby stuff is how it
does.
Now at the bottom :)
It's been a couple years since I played with Ruby ActiveRecord but it's
(of course) radically than what you describe. The ddl is in the ruby
code and naturally the code is in RCS. So a revision is a new instance
of ActiveRecord (iirc) which does the change(s) (create table ttt, alter
table vvv etc). Maybe skip a rev. Rollback to a rev is definitely
there because one writes the undo for each new revision. This include
manipulating the data of course, so there are limitations.
I personally am leary of the 'make the prod match the dev db' approach.
Who knows what extras lurk in the depths. I think one should be able to
make the dev db from scratch and write the necessary scripts to change
to (and from if possible) each revision. Apply to prod when tested.
In response to Thomas Kellerer <spam_eater@gmx.net>:
Bill Moran wrote on 10.02.2011 23:59:
The overview:
You store your schema and data as XML (this is easy to migrate to, because
it includes a tool that makes the XML from a live database)
Keep your XML schema files in some RCS.
When it's time for a new deployment, you run the dbsteward tool against
the schema XML and it turns it into DDL and DML.
When it's time for an upgrade, you run the dbsteward tool against two
schema XML files, and it calculates what has changed and generates the
appropriate DDL and DML to upgrade.This very much sounds like Liquibase. Do you happen to know any differences?
Hrm ... before we started writing dbsteward, we looked around to see
if something already existed and didn't find Liquibase. This is the
first I've heard of it. I'm thinking it was some time in 2008, and
according to their changelog, Liquibase was around at that time. I
wonder how we missed it ...
Anyway ... based on nothing more than a quick scan of their quickstart
page, here are the differences I see:
* Liquibase is dependent on you creating "changesets". I'm sure this
works, but we took a different approach with dbsteward. dbsteward
expects you to maintain XML files that represent the entire database,
then dbsteward does the work of figuring out what changed. Our
opinion was that svn already does the work of tracking changes, why
reinvent the wheel.
* Looks like liquibase requires you to talk to the database to push
the changes? dbsteward outputs a DDL/DML file that you can push
in whatever way is best. This is important to us because we use
Slony, and DDL changes have to be submitted through EXECUTE SCRIPT()
* dbsteward has built-in Slony support (i.e. it will make slony configs
as well as slony upgrade scripts in addition to DDL/DML)
* Does liquibase support UDFs? dbsteward does.
* liquibase has a lot more supported platforms at this time. dbsteward
only supports PostgreSQL and MSSQL (because that's all that we needed)
but I expect that other support will come quickly once we release it.
* Does liquibase support things like multi-column indexes and multi-
column primary keys? dbsteward does.
I don't think I should go on and on, as I could ask a lot of questions
about what liquibase does, and I simply don't have the time right now
to research it, or ask all those question ;)
Anyway ... sorry for the teaser on this, but we're trying to get through
all the hoops the company is requiring us to do to release it, and we
think we're on track to be ready by PGCon, so there'll be a website up
as soon as we can get it.
--
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/
In response to Rob Sargent <robjsargent@gmail.com>:
On 02/10/2011 03:59 PM, Bill Moran wrote:
In response to Rob Sargent <robjsargent@gmail.com>:
I for one will be waiting to see your dbsteward. How does it compare
functionally or stylistically with Ruby's migration tools (which I found
to be pretty cool and frustrating all in one go).I'm not familiar with Ruby's migration tools, so I can't say much.
The overview:
You store your schema and data as XML (this is easy to migrate to, because
it includes a tool that makes the XML from a live database)
Keep your XML schema files in some RCS.
When it's time for a new deployment, you run the dbsteward tool against
the schema XML and it turns it into DDL and DML.
When it's time for an upgrade, you run the dbsteward tool against two
schema XML files, and it calculates what has changed and generates the
appropriate DDL and DML to upgrade.So ... you know, however that compares with the Ruby stuff is how it
does.Now at the bottom :)
It's been a couple years since I played with Ruby ActiveRecord but it's
(of course) radically than what you describe. The ddl is in the ruby
code and naturally the code is in RCS. So a revision is a new instance
of ActiveRecord (iirc) which does the change(s) (create table ttt, alter
table vvv etc). Maybe skip a rev. Rollback to a rev is definitely
there because one writes the undo for each new revision. This include
manipulating the data of course, so there are limitations.
dbsteward can do downgrades ... you just feed it the old schema and
the new schema in reverse of how you'd do an upgrade ;)
Oh, also, it allows us to do installation-specific overrides. We use
this ONLY for DML for lookup lists where some clients have slightly
different names for things than others. In theory, it could do DDL
overrides as well, but we decided on a policy of not utilizing that
because we wanted the schemas to be consistent on all our installs.
I personally am leary of the 'make the prod match the dev db' approach.
Who knows what extras lurk in the depths. I think one should be able to
make the dev db from scratch and write the necessary scripts to change
to (and from if possible) each revision. Apply to prod when tested.
dbsteward allows us to do all this. A developer can make a change,
rebuild a test database from their change to make sure it works, then
test the upgrade process as well, all before even checking the code in.
--
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/
Bill Moran wrote on 11.02.2011 00:37:
Anyway ... based on nothing more than a quick scan of their quickstart
page, here are the differences I see:
* Liquibase is dependent on you creating "changesets". I'm sure this
works, but we took a different approach with dbsteward. dbsteward
expects you to maintain XML files that represent the entire database,
then dbsteward does the work of figuring out what changed. Our
opinion was that svn already does the work of tracking changes, why
reinvent the wheel.
That sounds like a very nice feature.
* Looks like liquibase requires you to talk to the database to push
the changes? dbsteward outputs a DDL/DML file that you can push
in whatever way is best. This is important to us because we use
Slony, and DDL changes have to be submitted through EXECUTE SCRIPT()
No, Liquibase can also emit the SQL that it would execute.
* dbsteward has built-in Slony support (i.e. it will make slony configs
as well as slony upgrade scripts in addition to DDL/DML)
* liquibase has a lot more supported platforms at this time. dbsteward
only supports PostgreSQL and MSSQL (because that's all that we needed)
but I expect that other support will come quickly once we release it.
* Does liquibase support things like multi-column indexes and multi-
column primary keys? dbsteward does.
Yes without problems (including of course the necessary foreing keys)
Anyway ... sorry for the teaser on this, but we're trying to get through
all the hoops the company is requiring us to do to release it, and we
think we're on track to be ready by PGCon, so there'll be a website up
as soon as we can get it.
Thanks for the feedback, I would really like to see it.
The approach that you do not record the changes but simply let the software find them seems like a very nifty feature.
I wonder how you detect renaming a table or a column?
On which programming language is dbstewart based?
Regards
Thomas
On 02/10/2011 04:44 PM, Bill Moran wrote:
In response to Rob Sargent <robjsargent@gmail.com>:
On 02/10/2011 03:59 PM, Bill Moran wrote:
In response to Rob Sargent <robjsargent@gmail.com>:
I for one will be waiting to see your dbsteward. How does it compare
functionally or stylistically with Ruby's migration tools (which I found
to be pretty cool and frustrating all in one go).I'm not familiar with Ruby's migration tools, so I can't say much.
The overview:
You store your schema and data as XML (this is easy to migrate to, because
it includes a tool that makes the XML from a live database)
Keep your XML schema files in some RCS.
When it's time for a new deployment, you run the dbsteward tool against
the schema XML and it turns it into DDL and DML.
When it's time for an upgrade, you run the dbsteward tool against two
schema XML files, and it calculates what has changed and generates the
appropriate DDL and DML to upgrade.So ... you know, however that compares with the Ruby stuff is how it
does.Now at the bottom :)
It's been a couple years since I played with Ruby ActiveRecord but it's
(of course) radically than what you describe. The ddl is in the ruby
code and naturally the code is in RCS. So a revision is a new instance
of ActiveRecord (iirc) which does the change(s) (create table ttt, alter
table vvv etc). Maybe skip a rev. Rollback to a rev is definitely
there because one writes the undo for each new revision. This include
manipulating the data of course, so there are limitations.dbsteward can do downgrades ... you just feed it the old schema and
the new schema in reverse of how you'd do an upgrade ;)Oh, also, it allows us to do installation-specific overrides. We use
this ONLY for DML for lookup lists where some clients have slightly
different names for things than others. In theory, it could do DDL
overrides as well, but we decided on a policy of not utilizing that
because we wanted the schemas to be consistent on all our installs.I personally am leary of the 'make the prod match the dev db' approach.
Who knows what extras lurk in the depths. I think one should be able to
make the dev db from scratch and write the necessary scripts to change
to (and from if possible) each revision. Apply to prod when tested.dbsteward allows us to do all this. A developer can make a change,
rebuild a test database from their change to make sure it works, then
test the upgrade process as well, all before even checking the code in.
Good work. Will look forward to it.
On 11/02/2011, at 9:59 AM, Thomas Kellerer wrote:
Royce Ausburn wrote on 10.02.2011 22:38:
I'm really interested to hear how you guys manage schema upgrades in
the face of branches and upgrading from many different versions of
the database.We are quite happy with Liquibase. You can simply run it against a database and tell it to migrate it to "Version x.y"
As it keeps track of all changes applied it automatically knows what to do.
I can handle static data as well as stored procedure and any custom SQL.
Thanks! Liquibase looks pretty neat. We'll have to check it out.
--Royce
So, 10.0 at 10057.
11.0 at 11023.then 10.1 needs some fixes so db is bumped to 10058.
Then, later, you can upgrade 10057 to 11023, but you cant get 10058 to 11023.
Humm... maybe you need smarter upgrade scripts? Would having logic in the script help? Something like:
if not fieldExists('xyz) then alter table ... add xyz ...
Or, maybe your schema numbering system is to broad? Maybe each table could have a version number?
Or some kinda flags like:
create table dbver(key text);then an update would be named: "add xyz to bob".
then the update code:
q = select key from dbver where key = 'add xyz to bob';
if q.eof then
alter table bob add xyz
This is effectively the approach we've been working with so far, but it isn't great. The issue is that you need to be really aware of what changes might or might not have been in the previous databases... This can be hard.
There's also the problem of updating data, etc.
Don't the bash scripts get checked in to .../perforce/cvs/svn/git/...?
Aren't they part of the resources of the project(s)?
Yep - they absolutely are. The issue is that there're multiple branches *potentially* having new scripts committed. Fortunately it's rare as the release branches seldom require schema changes, but it's an important enough problem to need a better solution.
--Royce
