MadMongers

I don't understand the question. The database doesn't know anything about the app's data so it wouldn't be possible to version the apps data unless the app itself versioned the data.  If you just want regular backups of your database, then set up a slave and run backups every 10 to 30 minutes.  Or if you're looking for continuous backup (every time anything changes it gets backed up), then you probably want to get CDP (http://www.r1soft.com/linux-cdp/cdp-enterprise-edition/mysql/).

No, I'm not talking about versioning the apps data so much as the
database schema, permissions, etc. It's a project where the backend
database is still undergoing development by more than one developer.
One thing I found in a google search was something called liqibase,
which is the kind of thing I was thinking of.

On Wed, Aug 11, 2010 at 3:58 PM,   wrote:
> jt wrote:
>
> I don't understand the question. The database doesn't know anything about
> the app's data so it wouldn't be possible to version the apps data unless
> the app itself versioned the data.  If you just want regular backups of your
> database, then set up a slave and run backups every 10 to 30 minutes.  Or if
> you're looking for continuous backup (every time anything changes it gets
> backed up), then you probably want to get CDP
> (http://www.r1soft.com/linux-cdp/cdp-enterprise-edition/mysql/).
>
> View Online
>
> Madison Area Perl Mongers - MadMongers
> http://www.madmongers.org
>



--
Mary D Hoerr
mary.hoerr@gmail.com
www.mdhoerr.com

Ah. Ok. For WebGUI we have upgrade files which manage that (yeah I know it's not versioning per say, but it manages the process of migrating from one schema to another). For my super secret project I generate the schema via DBIx::Class. I don't use a versioning system other than git in either case.

There are DBIx::Class::Schema::Versioned or DBIx::Class::DeploymentHandler, but those are both Perl-centric and may not work if you're not also using DBIx::Class as your ORM.

We don't use MySQL (Oracle, baybee!), but we just dump the DB Objects (PL/SQL Functions/Procedures/Packaces, Views, Tables, etc and version them with SVN.

I believe there is an arguement to mysql_dump that will dump just the objects and not their data.  We generally write the table/view spec and check it in before running it to create the DB objects, but we have another job that checks that the versions in the DB aren't different and report on it (it's very hard to do -- and will probably get in the way -- if you stop people from being able to log in as the schema owner and change things as needed.)

The permissions are just a table (IIRC, but I don't remember the table name), so you can just have a job that dumps that table periodically and checks it in or diffs/alerts (or whatever is appropriate).

You might be able to get fancy and create a trigger that you could attach to the permissions table that would automatically dump it and check it in or something..  But I don't know much (read: anything) about MySQL triggers.

jon

On 8/11/10 5:06 PM, mary.hoerr@gmail.com wrote:

mdhoerr wrote:

No, I'm not talking about versioning the apps data so much as the
database schema, permissions, etc. It's a project where the backend
database is still undergoing development by more than one developer.
One thing I found in a google search was something called liqibase,
which is the kind of thing I was thinking of.

On Wed, Aug 11, 2010 at 3:58 PM,   wrote:
> jt wrote:
>
> I don't understand the question. The database doesn't know anything about
> the app's data so it wouldn't be possible to version the apps data unless
> the app itself versioned the data.  If you just want regular backups of your
> database, then set up a slave and run backups every 10 to 30 minutes.  Or if
> you're looking for continuous backup (every time anything changes it gets
> backed up), then you probably want to get CDP
> (http://www.r1soft.com/linux-cdp/cdp-enterprise-edition/mysql/).
>
> View Online
>
> Madison Area Perl Mongers - MadMongers
> http://www.madmongers.org
>



--
Mary D Hoerr
mary.hoerr@gmail.com
www.mdhoerr.com

View Online

Madison Area Perl Mongers - MadMongers
http://www.madmongers.org

-- 
.Jonathan J. Miner----------------------------------------------------.
|  jon@jjminer.org  |      photos - http://photos.jjminer.org/        |
|                   | R.A.W. #1629 - http://www.reggaeambassadors.org |
|                   | LOCS Webmaster - http://www.locs-buffett.org    |
|  jabber/gchat: camrycurbhopper@gmail.com      AIM: camrycurbhopper  |
`---------------------------------------------------------------------'

"We don't have a town drunk...   We all take turns!"
    -- James Slater, "Key West Address"

My solution is to keep the database schema as a plain text file that contains all of the "create table" commands. Then I periodically run a short program on my dev and production boxes that computes an SQL diff of the text file against the MySQL database and I manually apply that diff.  The most important part of my code (which I could extract if people are interested) is this function:

I find myself regularly messing with my schemas in dev, and I have
found it's best in the long run to drop databases and recreate them
from scratch with the new .sql files because small things, like
auto_increment values, get changed which end up creating unnecessary
bugs.  So I guess my question would be whether you're expecting to be
able to change the database schema during development without having
to reload the data.  If so, I would urge you to consider creating test
rows to populate the database with to standardize the development
testing, so that reloading the data is trivial.  I think that will get
you the most bang-for-the-buck in the long run.

--Martin

On Wed, Aug 11, 2010 at 9:59 PM,   wrote:
> chrisdolan wrote:
>
> My solution is to keep the database schema as a plain text file that
> contains all of the "create table" commands. Then I periodically run a short
> program on my dev and production boxes that computes an SQL diff of the text
> file against the MySQL database and I manually apply that diff.  The most
> important part of my code (which I could extract if people are interested)
> is this function:
>    SQL::Translator::Diff::schema_diff()
>
> Chris
>
> On Aug 11, 2010, at 3:47 PM,  
> wrote:
>
> mdhoerr wrote:
>
> Hi all,
>
> Does anyone have any recommendations for version control systems for
> MySQL databases? I'm working with a team that uses SVN for code
> version control, but they don't have any version control for their
> MySQL databases. It's not a perl app (it's php), but I was just
> wondering what you folks use / recommend in your own work.
>
> Thanks!
>
> --
> Mary D Hoerr
> mary.hoerr@gmail.com
> www.mdhoerr.com
>
> View Online
>
> Madison Area Perl Mongers - MadMongers
> http://www.madmongers.org
>
> View Online
>
> Madison Area Perl Mongers - MadMongers
> http://www.madmongers.org
>