You need a change management system for your database schema. You are thinking in terms of development ease but truly the difficulty lies in preserving production data; there's no way just copying the schema will ever suffice. The moment you need to do an alter table your sunk. You can't be dropping tables just so you can create the schema you need as you can during development. As development marches on you will be faced with situations that need to modify data that won't need to modify schema. You need to manage those changes as well.
Rails has the best scheme I've seen and I would guess many frameworks have copied it; what are you writing your app in? A simpler method but requires you to be very deliberate in the queries you write is a pattern that coppermine gallery uses; and probably many other php apps. Consists of two files. An initial SQL file that is designed to create a new schema (seed). The second is for updating an existing one (update). (you may only need update if you aren't creating new databases all the time) When you make a change to you schema you make it to both files being mindful of the context they are run. The layout of the files is the same; a series of properly terminated queries; the trick is this file is read as a string; split on those terminations and run one statement at a time; if it succeeds print "ok"; if it doesn't print "already done"; That's where the deliberate part comes in; since you need to write the queries to be idempotent; ie you should write the query such that you can run it any number of times but it won't damage anything after it's initial run. Since failure is expected; obviously you need to test what you add to the files really well. Check the SQL directory in coppermine to see what I mean. Lastly RUNNING your db schema mgmt system IS in the capistrano perview. But the actual details and implementation of that system is in the perview of your application or application framework. So a forum dedicated to whatever framework you are building your app in would be a better place to get advice for this issue. On Apr 2, 2011, at 1:05 PM, mem <[email protected]> wrote: > On 02-04-2011 19:25, Lee Hambley wrote: >> >> Is this correct? >> >> Correct! >> >> (Will reply in depth when I'm home) > > Thanks. > > Well, one question is prevailing. > > I've created a git hook that executes a mysqldump to the schema and saves it > somewhere on a place where the git can reach. > > The git hook seems to be a nice option, because since Capistrano will deploy > committed files, I can rest assured that it will deploy (my last local schema > to a file). > > After reading a bit more, I now understand that this seems to not suffice. > > I mean: "Great I have a sql file with my last schema on the remote server". > Now what? > I still need a way to apply that schema to the remote database. > > If I'm thinking right, could Capistrano help me archiving this last step ? > -- > * You received this message because you are subscribed to the Google Groups > "Capistrano" group. > * To post to this group, send email to [email protected] > * To unsubscribe from this group, send email to > [email protected] For more options, visit this group at > http://groups.google.com/group/capistrano?hl=en -- * You received this message because you are subscribed to the Google Groups "Capistrano" group. * To post to this group, send email to [email protected] * To unsubscribe from this group, send email to [email protected] For more options, visit this group at http://groups.google.com/group/capistrano?hl=en
