From: Ashley Sheridan on
On Thu, 2010-07-15 at 09:07 +0100, Pete Ford wrote:

> On 15/07/10 06:03, Paul M Foster wrote:
> > On Wed, Jul 14, 2010 at 09:28:53PM -0700, Slith One wrote:
> >
> >> I'm developing an app using Zend Framwork using Git for version control.
> >>
> >> What is the best approach for updating the schema and the database
> >> when one of us makes an update to the db structure?
> >>
> >> currently, we have to blow out the tables and recreate them manually
> >> to reflect the new updates.
> >
> > I'm probably being naive, but don't you have an ALTER TABLE sql
> > statement available to you?
> >
> > Also, for what it's worth, I don't build tables manually (at the command
> > line or whatever). I always create a script which will build the tables
> > I need. If, for some crazy reason, I do have to restart from scratch,
> > it's a simple matter to alter that script and re-run it.
> >
> > Paul
> >
>
> Scripting is the way to go for database changes: every time I have to make a
> schema change I write an SQL script to do the job, including any manipulation of
> data required. Then I make a copy of the real data and test the hell out of the
> change script before going live with it.
> You can commit the database script to your source control at the time you commit
> the code changes, and then when you update the live system you run any new
> scripts at the same time.
>
>
>
> --
> Peter Ford, Developer phone: 01580 893333 fax: 01580 893399
> Justcroft International Ltd. www.justcroft.com
> Justcroft House, High Street, Staplehurst, Kent TN12 0AH United Kingdom
> Registered in England and Wales: 2297906
> Registered office: Stag Gates House, 63/64 The Avenue, Southampton SO17 1XS
>


ALTER TABLE is the way to go. If in doubt, look at the SQL phpMyAdmin
produces when you make the changes in there.

Thanks,
Ash
http://www.ashleysheridan.co.uk


From: "Bob McConnell" on
From: Pete Ford

> On 15/07/10 09:14, Ashley Sheridan wrote:
>> ALTER TABLE is the way to go. If in doubt, look at the SQL phpMyAdmin
>> produces when you make the changes in there.
>>
>
> Yeah, scripting "ALTER TABLE" commands ... :)

We maintain two files for every schema, site_schema.pgsql and
site_delta.pgsql. Every time we modify the schema, we add the change
commands to the delta file. We also have markers in it for each build
number, so the update scripts can determine which changes need to be run
when a site is updated.

We use a similar technique on other systems that use Oracle or Sybase
ASA on the back end, but those are stored as a shell database and sets
of patch files for each build.

Bob McConnell
From: Richard Quadling on
On 15 July 2010 13:29, Bob McConnell <rvm(a)cbord.com> wrote:
> From: Pete Ford
>
>> On 15/07/10 09:14, Ashley Sheridan wrote:
>>> ALTER TABLE is the way to go. If in doubt, look at the SQL phpMyAdmin
>>> produces when you make the changes in there.
>>>
>>
>> Yeah, scripting "ALTER TABLE" commands ... :)
>
> We maintain two files for every schema, site_schema.pgsql and
> site_delta.pgsql. Every time we modify the schema, we add the change
> commands to the delta file. We also have markers in it for each build
> number, so the update scripts can determine which changes need to be run
> when a site is updated.
>
> We use a similar technique on other systems that use Oracle or Sybase
> ASA on the back end, but those are stored as a shell database and sets
> of patch files for each build.
>
> Bob McConnell

I use a tool called SQL Compare and SQL Data Compare - by a company
called RedGate. These allow me and others to work on our in-house DBs
and when we are ready to roll out the release, create a changeset of
all the differences between the last release and this one.

It only works on MS SQL servers, but there are others [1]

Regards,

Richard Quadling.

[1] http://stackoverflow.com/questions/1265962/is-there-an-equivalent-of-redgate-sql-compare-for-mysql-databases