|Starting with versioning mysql schemata without overkill. Good solutions?||#1|
I(ve arrived at the point where I realise that I must start versioning my database schemata and changes. I consequently read the existing posts on SO about that topic but I(m not sure how to proceed.
I(m basically a one man company and not long ago I didn(t even use version control for my code. I(m on a windows environment, using Aptana (IDE) and SVN (with Tortoise). I work on PHP/mysql projects.
What(s a efficient and sufficient (no overkill) way to version my database schemata?
I do have a freelancer or two in some projects but I don(t expect a lot of branching and merging going on. So basically I would like to keep track of concurrent schemata to my code revisions.
 Momentary solution: for the moment I decided I will just make a schema dump plus one with the necessary initial data whenever I(m going to commit a tag (stable version). That seems to be just enough for me at the current stage.[/edit]
[edit2]plus I(m now also using a third file called increments.sql where I put all the changes with dates, etc. to make it easy to trace the change history in one file. from time to time I integrate the changes into the two other files and empty the increments.sql[/edit]
posted date: 2009-04-16 03:28:00
|Re: Starting with versioning mysql schemata without overkill. Good solutions?||#3|
Simple way for a small company: dump your database to SQL and add it to your repository. Then every time you change something, add the changes in the dump file. You can then use diff to see changes between versions, not to mention have comments explaining your changes. This will also make you virtually immune to MySQL upgrades. The one downside I(ve seen to this is that you have to remember to manually add the SQL to your dumpfile. You can train yourself to always remember, but be careful if you work with others. Missing an update could be a pain later on. This could be mitigated by creating some elaborate script to do it for you when submitting to subversion but it(s a bit much for a one man show.Edit: In the year that(s gone by since this answer, I(ve had to implement a versioning scheme for MySQL for a small team. Manually adding each change was seen as a cumbersome solution, much like it was mentioned in the comments, so we went with dumping the database and adding that file to version control. What we found was that test data was ending up in the dump and was making it quite difficult to figure out what had changed. This could be solved by dumping the schema only, but this was impossible for our projects since our applications depended on certain data in the database to function. Eventually we returned to manually adding changes to the database dump. Not only was this the simplest solution, but it also solved certain issues that some versions of MySQL have with exporting/importing. Normally we would have to dump the development database, remove any test data, log entries, etc, remove/change certain names where applicable and only then be able to create the production database. By manually adding changes we could control exactly what would end up in production, a little at a time, so that in the end everything was ready and moving to the production environment was as painless as possible.
posted date: 2009-04-16 03:32:00
|Re: Starting with versioning mysql schemata without overkill. Good solutions?||#4|
How about versioning file generated by doing this:
mysqldump --no-data database > database.sql
posted date: 2009-04-16 03:34:00
|Re: Starting with versioning mysql schemata without overkill. Good solutions?||#5|
Where I work we have an install script for each new version of the app which has the sql we need to run for the upgrade. This works well enough for 6 devs with some branching for maintenance releases. We(re considering moving to Auto Patch http://autopatch.sourceforge.net/ which handles working out what patches to apply to any database you are upgrading. It looks like there may be some small complication handling branching with auto Patch, but it doesn(t sound like that(ll be an issue for you.
posted date: 2009-04-16 03:40:00
|Re: Starting with versioning mysql schemata without overkill. Good solutions?||#6|
why not just dump every time after a change? maybe a batch file that dumps the schema and auto-commits (just this file)? the manual step makes it just harder, imho
posted date: 2009-04-16 04:09:00
|Re: Starting with versioning mysql schemata without overkill. Good solutions?||#7|
i(d guess, a batch file like this should do the job (didn(t try tough) ...
mysqldump --no-data -ufoo -pbar dbname > path/to/app/schema.sql svn commit path/to/app/schema.sql
just run the batch file after changing the schema, or let a cron/scheduler do it (but i don(t know ... i think, commits work if just the timestamps changed, even if the contents is the same. don(t know if that would be a problem.)
posted date: 2009-04-16 04:15:00
|Re: Starting with versioning mysql schemata without overkill. Good solutions?||#8|
I've done exactly that with a small team of developers and it worked quite well. We used phpmyadmin for the export and a small custom script to import/replace everything once a new dump was available. Just remember to tell everyone that a new version is in the repo so other's changes won't conflict.
posted date: 2009-04-16 04:23:00
|Re: Starting with versioning mysql schemata without overkill. Good solutions?||#9|
The main ideea is to have a folder with this structure in your project base path
Now who the whole thing works is that you have 3 folders:TablesHolds the table create query. I recommend using the naming “table_name.sql”.DataHolds the table insert data query. I recommend using the same naming “table_name.sql”.Note: Not all tables need a data file, you would only add the ones that need this initial data on project install.ChangesetsThis is the main folder you will work with.This holds the change sets made to the initial structure. This holds actually folders with changesets.For example i added a folder 1123 wich will contain the modifications made in revision 1123 ( the number is from your code source control ) and may contain one or more sql files.I like to add them grouped into tables with the naming xx_tablename.sql - the xx is a number that tells the order they need to be runned, since sometimes you need the modification runned in a certain order.Note:When you modify a table, you also add those modifications to table and data files … since those are the file s that will be used to do a fresh install.This is the main ideea.for more details you could check this blog post
posted date: 2009-04-16 04:36:00
|Re: Starting with versioning mysql schemata without overkill. Good solutions?||#10|
I appreciate your well explained solution but I think this would already qualify for overkill in my situation.
posted date: 2009-04-16 05:10:00
|Re: Starting with versioning mysql schemata without overkill. Good solutions?||#11|
but it still helps me because it gave me an idea of how to handle initial data!
posted date: 2009-04-16 05:12:00
|Re: Starting with versioning mysql schemata without overkill. Good solutions?||#12|
sounds good to me, couldn't this batch file be a svn hook script, have never used hooks so far...
posted date: 2009-04-16 05:13:00
|select page: « 1 2 »|