SEO

Hello!

I'm just getting in to using CVS to track changes to my source code for PHP
projects that I'm working on, and it's exactly what my organization needed.

However, there does not appear to be a way to track changes to mySQL
databases in the same way. Basically, as the structure of tables are
changed to meet the requirements of new features, I'd like a way to be able
to record those changes (both structural table changes and also "default
table data" such as table of states or zip codes or whatever) in a CVS-type
system (preferably integrated with CVS directly) so that when a customer
uses CVS to get the newest version of the code for their project, they can
also get (and automatically apply) all changes to their database for the new
version.

Does such a system exist? How do other people cope with these types of
updates?

Thanks for any guidance!

Tim Gustafson
(831) 425-4522 x 100
(831) 621-6299 Fax

We keep all of the schema (one file per table) in SVN (subversion)
with a directory to represent each database. As the schema evolves,
we have had no trouble tracking changes through resource history and
are able to extract diffs on every commited change. It works like a
charm and would proably work equally as well with CVS.

- michael


On 3/30/07, Tim Gustafson <tim@santacruzcomputers.com> wrote:
> Hello!
>
> I'm just getting in to using CVS to track changes to my source code for PHP
> projects that I'm working on, and it's exactly what my organization needed.
>
> However, there does not appear to be a way to track changes to mySQL
> databases in the same way. Basically, as the structure of tables are
> changed to meet the requirements of new features, I'd like a way to be able
> to record those changes (both structural table changes and also "default
> table data" such as table of states or zip codes or whatever) in a CVS-type
> system (preferably integrated with CVS directly) so that when a customer
> uses CVS to get the newest version of the code for their project, they can
> also get (and automatically apply) all changes to their database for the new
> version.
>
> Does such a system exist? How do other people cope with these types of
> updates?
>
> Thanks for any guidance!
>
> Tim Gustafson
> (831) 425-4522 x 100
> (831) 621-6299 Fax
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=mdykman@gmail.com
>
>


--
- michael dykman
- mdykman@gmail.com

- All models are wrong. Some models are useful.

Michael,

We're about to head down the same road, using Subversion, and my thought was to
initially capture a series of CREATE TABLE statements and store them all in
one file.

Then as schema was modified on the development server, update those
statements using SVN.

Your idea looks a lot better, may I presume to outline how I think you use it?
I'm assuming you capture, for each table, an initial CREATE TABLE, and
save it in a file. Then as the schema changes you update the file with the
ALTER TABLE statements, commiting the changes.

If you have to recreate the database, you execute the file up to the last
change point.

I suppose you could do the same thing to maintain the data stored in lookup
tables.

We're using Joomla! and have extended it quite a bit, and are now running
three databases - dev, beta and since last week, live.

Later this week I'll be moving myself and one other developer to an SVN
environment; we will see how it goes.


Cheers - Miles Thompson


At 07:04 PM 3/30/2007, Michael Dykman wrote:

>We keep all of the schema (one file per table) in SVN (subversion)
>with a directory to represent each database. As the schema evolves,
>we have had no trouble tracking changes through resource history and
>are able to extract diffs on every commited change. It works like a
>charm and would proably work equally as well with CVS.
>
>- michael
>
>
>On 3/30/07, Tim Gustafson <tim@santacruzcomputers.com> wrote:
>>Hello!
>>
>>I'm just getting in to using CVS to track changes to my source code for PHP
>>projects that I'm working on, and it's exactly what my organization needed.
>>
>>However, there does not appear to be a way to track changes to mySQL
>>databases in the same way. Basically, as the structure of tables are
>>changed to meet the requirements of new features, I'd like a way to be able
>>to record those changes (both structural table changes and also "default
>>table data" such as table of states or zip codes or whatever) in a CVS-type
>>system (preferably integrated with CVS directly) so that when a customer
>>uses CVS to get the newest version of the code for their project, they can
>>also get (and automatically apply) all changes to their database for the new
>>version.
>>
>>Does such a system exist? How do other people cope with these types of
>>updates?
>>
>>Thanks for any guidance!
>>
>>Tim Gustafson
>>(831) 425-4522 x 100
>>(831) 621-6299 Fax
>>
>>
>>--
>>MySQL General Mailing List
>>For list archives: http://lists.mysql.com/mysql
>>To unsubscribe: http://lists.mysql.com/mysql?unsub=mdykman@gmail.com
>>
>
>
>--
>- michael dykman
>- mdykman@gmail.com


--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.5.446 / Virus Database: 268.18.24/741 - Release Date: 3/31/2007 8:54 PM

Using ALTER statements would make it tough to get a complete view. I
would stick with your original idea. This would enable diffs to work
nicely, and the latest revision would contain everything you need to
know to create the database.

-Micah

On 04/01/2007 07:11 AM, Miles Thompson wrote:
> Michael,
>
> We're about to head down the same road, using Subversion, and my
> thought was to
> initially capture a series of CREATE TABLE statements and store them
> all in one file.
>
> Then as schema was modified on the development server, update those
> statements using SVN.
>
> Your idea looks a lot better, may I presume to outline how I think you
> use it?
> I'm assuming you capture, for each table, an initial CREATE TABLE,
> and save it in a file. Then as the schema changes you update the file
> with the ALTER TABLE statements, commiting the changes.
>
> If you have to recreate the database, you execute the file up to the
> last change point.
>
> I suppose you could do the same thing to maintain the data stored in
> lookup tables.
>
> We're using Joomla! and have extended it quite a bit, and are now
> running three databases - dev, beta and since last week, live.
>
> Later this week I'll be moving myself and one other developer to an
> SVN environment; we will see how it goes.
>
>
> Cheers - Miles Thompson
>
>
> At 07:04 PM 3/30/2007, Michael Dykman wrote:
>
>> We keep all of the schema (one file per table) in SVN (subversion)
>> with a directory to represent each database. As the schema evolves,
>> we have had no trouble tracking changes through resource history and
>> are able to extract diffs on every commited change. It works like a
>> charm and would proably work equally as well with CVS.
>>
>> - michael
>>
>>
>> On 3/30/07, Tim Gustafson <tim@santacruzcomputers.com> wrote:
>>> Hello!
>>>
>>> I'm just getting in to using CVS to track changes to my source code
>>> for PHP
>>> projects that I'm working on, and it's exactly what my organization
>>> needed.
>>>
>>> However, there does not appear to be a way to track changes to mySQL
>>> databases in the same way. Basically, as the structure of tables are
>>> changed to meet the requirements of new features, I'd like a way to
>>> be able
>>> to record those changes (both structural table changes and also
>>> "default
>>> table data" such as table of states or zip codes or whatever) in a
>>> CVS-type
>>> system (preferably integrated with CVS directly) so that when a
>>> customer
>>> uses CVS to get the newest version of the code for their project,
>>> they can
>>> also get (and automatically apply) all changes to their database for
>>> the new
>>> version.
>>>
>>> Does such a system exist? How do other people cope with these types of
>>> updates?
>>>
>>> Thanks for any guidance!
>>>
>>> Tim Gustafson
>>> (831) 425-4522 x 100
>>> (831) 621-6299 Fax
>>>
>>>
>>> --
>>> MySQL General Mailing List
>>> For list archives: http://lists.mysql.com/mysql
>>> To unsubscribe: http://lists.mysql.com/mysql?unsub=mdykman@gmail.com
>>>
>>
>>
>> --
>> - michael dykman
>> - mdykman@gmail.com
>
>

DDLUTILS is what you need:

check this out:
http://db.apache.org/ddlutils/

and better still (if you are using Ant as a build tool):
http://db.apache.org/ddlutils/ant/

Then you can store these ant scripts in your VCS (version control system).
To create or destroy the schema with data just run an ant target and you
would be done.

Anoop


On 4/1/07, Micah Stevens <micah@raincross-tech.com> wrote:
>
> Using ALTER statements would make it tough to get a complete view. I
> would stick with your original idea. This would enable diffs to work
> nicely, and the latest revision would contain everything you need to
> know to create the database.
>
> -Micah
>
> On 04/01/2007 07:11 AM, Miles Thompson wrote:
> > Michael,
> >
> > We're about to head down the same road, using Subversion, and my
> > thought was to
> > initially capture a series of CREATE TABLE statements and store them
> > all in one file.
> >
> > Then as schema was modified on the development server, update those
> > statements using SVN.
> >
> > Your idea looks a lot better, may I presume to outline how I think you
> > use it?
> > I'm assuming you capture, for each table, an initial CREATE TABLE,
> > and save it in a file. Then as the schema changes you update the file
> > with the ALTER TABLE statements, commiting the changes.
> >
> > If you have to recreate the database, you execute the file up to the
> > last change point.
> >
> > I suppose you could do the same thing to maintain the data stored in
> > lookup tables.
> >
> > We're using Joomla! and have extended it quite a bit, and are now
> > running three databases - dev, beta and since last week, live.
> >
> > Later this week I'll be moving myself and one other developer to an
> > SVN environment; we will see how it goes.
> >
> >
> > Cheers - Miles Thompson
> >
> >
> > At 07:04 PM 3/30/2007, Michael Dykman wrote:
> >
> >> We keep all of the schema (one file per table) in SVN (subversion)
> >> with a directory to represent each database. As the schema evolves,
> >> we have had no trouble tracking changes through resource history and
> >> are able to extract diffs on every commited change. It works like a
> >> charm and would proably work equally as well with CVS.
> >>
> >> - michael
> >>
> >>
> >> On 3/30/07, Tim Gustafson <tim@santacruzcomputers.com> wrote:
> >>> Hello!
> >>>
> >>> I'm just getting in to using CVS to track changes to my source code
> >>> for PHP
> >>> projects that I'm working on, and it's exactly what my organization
> >>> needed.
> >>>
> >>> However, there does not appear to be a way to track changes to mySQL
> >>> databases in the same way. Basically, as the structure of tables are
> >>> changed to meet the requirements of new features, I'd like a way to
> >>> be able
> >>> to record those changes (both structural table changes and also
> >>> "default
> >>> table data" such as table of states or zip codes or whatever) in a
> >>> CVS-type
> >>> system (preferably integrated with CVS directly) so that when a
> >>> customer
> >>> uses CVS to get the newest version of the code for their project,
> >>> they can
> >>> also get (and automatically apply) all changes to their database for
> >>> the new
> >>> version.
> >>>
> >>> Does such a system exist? How do other people cope with these types
> of
> >>> updates?
> >>>
> >>> Thanks for any guidance!
> >>>
> >>> Tim Gustafson
> >>> (831) 425-4522 x 100
> >>> (831) 621-6299 Fax
> >>>
> >>>
> >>> --
> >>> MySQL General Mailing List
> >>> For list archives: http://lists.mysql.com/mysql
> >>> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=mdykman@gmail.com
> >>>
> >>
> >>
> >> --
> >> - michael dykman
> >> - mdykman@gmail.com
> >
> >
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=a...marv@gmail.com
>
>


--
Thanks and best regards,
Anoop

Sounds like perhaps an unnecessary complication, why would this be
better than the root SQL CREATE statements?

-Micah

On 04/01/2007 12:41 PM, Anoop kumar V wrote:
> DDLUTILS is what you need:
>
> check this out:
> http://db.apache.org/ddlutils/
>
> and better still (if you are using Ant as a build tool):
> http://db.apache.org/ddlutils/ant/
>
> Then you can store these ant scripts in your VCS (version control
> system).
> To create or destroy the schema with data just run an ant target and you
> would be done.
>
> Anoop
>
>
> On 4/1/07, Micah Stevens <micah@raincross-tech.com> wrote:
>>
>> Using ALTER statements would make it tough to get a complete view. I
>> would stick with your original idea. This would enable diffs to work
>> nicely, and the latest revision would contain everything you need to
>> know to create the database.
>>
>> -Micah
>>
>> On 04/01/2007 07:11 AM, Miles Thompson wrote:
>> > Michael,
>> >
>> > We're about to head down the same road, using Subversion, and my
>> > thought was to
>> > initially capture a series of CREATE TABLE statements and store them
>> > all in one file.
>> >
>> > Then as schema was modified on the development server, update those
>> > statements using SVN.
>> >
>> > Your idea looks a lot better, may I presume to outline how I think you
>> > use it?
>> > I'm assuming you capture, for each table, an initial CREATE TABLE,
>> > and save it in a file. Then as the schema changes you update the file
>> > with the ALTER TABLE statements, commiting the changes.
>> >
>> > If you have to recreate the database, you execute the file up to the
>> > last change point.
>> >
>> > I suppose you could do the same thing to maintain the data stored in
>> > lookup tables.
>> >
>> > We're using Joomla! and have extended it quite a bit, and are now
>> > running three databases - dev, beta and since last week, live.
>> >
>> > Later this week I'll be moving myself and one other developer to an
>> > SVN environment; we will see how it goes.
>> >
>> >
>> > Cheers - Miles Thompson
>> >
>> >
>> > At 07:04 PM 3/30/2007, Michael Dykman wrote:
>> >
>> >> We keep all of the schema (one file per table) in SVN (subversion)
>> >> with a directory to represent each database. As the schema evolves,
>> >> we have had no trouble tracking changes through resource history and
>> >> are able to extract diffs on every commited change. It works like a
>> >> charm and would proably work equally as well with CVS.
>> >>
>> >> - michael
>> >>
>> >>
>> >> On 3/30/07, Tim Gustafson <tim@santacruzcomputers.com> wrote:
>> >>> Hello!
>> >>>
>> >>> I'm just getting in to using CVS to track changes to my source code
>> >>> for PHP
>> >>> projects that I'm working on, and it's exactly what my organization
>> >>> needed.
>> >>>
>> >>> However, there does not appear to be a way to track changes to mySQL
>> >>> databases in the same way. Basically, as the structure of tables
>> are
>> >>> changed to meet the requirements of new features, I'd like a way to
>> >>> be able
>> >>> to record those changes (both structural table changes and also
>> >>> "default
>> >>> table data" such as table of states or zip codes or whatever) in a
>> >>> CVS-type
>> >>> system (preferably integrated with CVS directly) so that when a
>> >>> customer
>> >>> uses CVS to get the newest version of the code for their project,
>> >>> they can
>> >>> also get (and automatically apply) all changes to their database for
>> >>> the new
>> >>> version.
>> >>>
>> >>> Does such a system exist? How do other people cope with these types
>> of
>> >>> updates?
>> >>>
>> >>> Thanks for any guidance!
>> >>>
>> >>> Tim Gustafson
>> >>> (831) 425-4522 x 100
>> >>> (831) 621-6299 Fax
>> >>>
>> >>>
>> >>> --
>> >>> MySQL General Mailing List
>> >>> For list archives: http://lists.mysql.com/mysql
>> >>> To unsubscribe:
>> http://lists.mysql.com/mysql?unsub=mdykman@gmail.com
>> >>>
>> >>
>> >>
>> >> --
>> >> - michael dykman
>> >> - mdykman@gmail.com
>> >
>> >
>>
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe:
>> http://lists.mysql.com/mysql?unsub=a...marv@gmail.com
>>
>>
>
>

Sql create statements need to be run using a compatible client. sqlplus for
oracle, mysqlclient for mysql etc.. Here you just have a target as part of
your routine build that also takes care of building / renewing your database
with (or w/o) data.

Plus a layer of abstraction such as a ant for everything development related
allows you to integrate into system integration tools like cruise control /
continuum etc.. So you automate most of the stuff: building your database,
testing against code etc... The investment is marginal and only during the
setup of these tools, but the gains are phenomenal. (just like the benefits
realized with setting up cvs and all)
http://www.martinfowler.com/articles...tegration.html
http://www.zorched.net/2006/08/19/re...ld-automation/ (scroll down
to the database part)

Platform independence is another benefit that comes to mind...(ant / java)

Not to digress - but I would advise (strongly) the author to consider svn
instead of cvs (svn: subversion is the new cvs built fresh from bottom
keeping in mind the deficiencies of cvs)
http://subversion.tigris.org/


Anoop

On 4/1/07, Micah Stevens <micah@raincross-tech.com> wrote:
>
> Sounds like perhaps an unnecessary complication, why would this be
> better than the root SQL CREATE statements?
>
> -Micah
>
> On 04/01/2007 12:41 PM, Anoop kumar V wrote:
> > DDLUTILS is what you need:
> >
> > check this out:
> > http://db.apache.org/ddlutils/
> >
> > and better still (if you are using Ant as a build tool):
> > http://db.apache.org/ddlutils/ant/
> >
> > Then you can store these ant scripts in your VCS (version control
> > system).
> > To create or destroy the schema with data just run an ant target and you
> > would be done.
> >
> > Anoop
> >
> >
> > On 4/1/07, Micah Stevens <micah@raincross-tech.com> wrote:
> >>
> >> Using ALTER statements would make it tough to get a complete view. I
> >> would stick with your original idea. This would enable diffs to work
> >> nicely, and the latest revision would contain everything you need to
> >> know to create the database.
> >>
> >> -Micah
> >>
> >> On 04/01/2007 07:11 AM, Miles Thompson wrote:
> >> > Michael,
> >> >
> >> > We're about to head down the same road, using Subversion, and my
> >> > thought was to
> >> > initially capture a series of CREATE TABLE statements and store them
> >> > all in one file.
> >> >
> >> > Then as schema was modified on the development server, update those
> >> > statements using SVN.
> >> >
> >> > Your idea looks a lot better, may I presume to outline how I think
> you
> >> > use it?
> >> > I'm assuming you capture, for each table, an initial CREATE TABLE,
> >> > and save it in a file. Then as the schema changes you update the file
> >> > with the ALTER TABLE statements, commiting the changes.
> >> >
> >> > If you have to recreate the database, you execute the file up to the
> >> > last change point.
> >> >
> >> > I suppose you could do the same thing to maintain the data stored in
> >> > lookup tables.
> >> >
> >> > We're using Joomla! and have extended it quite a bit, and are now
> >> > running three databases - dev, beta and since last week, live.
> >> >
> >> > Later this week I'll be moving myself and one other developer to an
> >> > SVN environment; we will see how it goes.
> >> >
> >> >
> >> > Cheers - Miles Thompson
> >> >
> >> >
> >> > At 07:04 PM 3/30/2007, Michael Dykman wrote:
> >> >
> >> >> We keep all of the schema (one file per table) in SVN (subversion)
> >> >> with a directory to represent each database. As the schema evolves,
> >> >> we have had no trouble tracking changes through resource history and
> >> >> are able to extract diffs on every commited change. It works like a
> >> >> charm and would proably work equally as well with CVS.
> >> >>
> >> >> - michael
> >> >>
> >> >>
> >> >> On 3/30/07, Tim Gustafson <tim@santacruzcomputers.com> wrote:
> >> >>> Hello!
> >> >>>
> >> >>> I'm just getting in to using CVS to track changes to my source code
> >> >>> for PHP
> >> >>> projects that I'm working on, and it's exactly what my organization
> >> >>> needed.
> >> >>>
> >> >>> However, there does not appear to be a way to track changes to
> mySQL
> >> >>> databases in the same way. Basically, as the structure of tables
> >> are
> >> >>> changed to meet the requirements of new features, I'd like a way to
> >> >>> be able
> >> >>> to record those changes (both structural table changes and also
> >> >>> "default
> >> >>> table data" such as table of states or zip codes or whatever) in a
> >> >>> CVS-type
> >> >>> system (preferably integrated with CVS directly) so that when a
> >> >>> customer
> >> >>> uses CVS to get the newest version of the code for their project,
> >> >>> they can
> >> >>> also get (and automatically apply) all changes to their database
> for
> >> >>> the new
> >> >>> version.
> >> >>>
> >> >>> Does such a system exist? How do other people cope with these
> types
> >> of
> >> >>> updates?
> >> >>>
> >> >>> Thanks for any guidance!
> >> >>>
> >> >>> Tim Gustafson
> >> >>> (831) 425-4522 x 100
> >> >>> (831) 621-6299 Fax
> >> >>>
> >> >>>
> >> >>> --
> >> >>> MySQL General Mailing List
> >> >>> For list archives: http://lists.mysql.com/mysql
> >> >>> To unsubscribe:
> >> http://lists.mysql.com/mysql?unsub=mdykman@gmail.com
> >> >>>
> >> >>
> >> >>
> >> >> --
> >> >> - michael dykman
> >> >> - mdykman@gmail.com
> >> >
> >> >
> >>
> >> --
> >> MySQL General Mailing List
> >> For list archives: http://lists.mysql.com/mysql
> >> To unsubscribe:
> >> http://lists.mysql.com/mysql?unsub=a...marv@gmail.com
> >>
> >>
> >
> >
>

On 04/01/2007 03:28 PM, Anoop kumar V wrote:
> Sql create statements need to be run using a compatible client.
> sqlplus for
> oracle, mysqlclient for mysql etc.. Here you just have a target as
> part of
> your routine build that also takes care of building / renewing your
> database
> with (or w/o) data.
>
This wouldn't change anyhow, you'd still need a client during the build
process. You're just automating the control of the client, which IMHO is
only a good thing in certain circumstances. I agree it can be useful,
but not in all cases.
> Plus a layer of abstraction such as a ant for everything development
> related
> allows you to integrate into system integration tools like cruise
> control /
> continuum etc.. So you automate most of the stuff: building your
> database,
> testing against code etc... The investment is marginal and only during
> the
> setup of these tools, but the gains are phenomenal. (just like the
> benefits
> realized with setting up cvs and all)
> http://www.martinfowler.com/articles...tegration.html
> http://www.zorched.net/2006/08/19/re...ld-automation/ (scroll
> down
> to the database part)
>
I'll take a look at these articles, thank you.

>
> Not to digress - but I would advise (strongly) the author to consider
> svn
> instead of cvs (svn: subversion is the new cvs built fresh from bottom
> keeping in mind the deficiencies of cvs)
> http://subversion.tigris.org/
>
Agreed, my experience with Subversion has been a pleasurable one.

-Micah