search

ddbdavis / Autoponics-Control-Software

This web application will eventually allow for remote control of an autoponics system.
http://www.solidstatedepot.com/wiki/index.php?title=Autoponics
Other
1 stars 0 forks source link

Decide on a protocol for database versioning #1

Open zukowski opened 13 years ago

zukowski commented 13 years ago

Have you given any thought to database versioning? I haven't done this in CI before, so I'm open to anything. There's this library that seems like it might do the trick: http://codeigniter.com/wiki/DBVersion/

zukowski commented 13 years ago

For now I've created a db folder, and I'm putting schema changes in that folder, named in this format: YYYYMMDDHHMMSS_descriptivename.sql.

So, for example, the sql file I added for creating the users table is db/20110522130300_users.sql.

If we wanted to add a column, say, to the users table, then we would make a migration file like 201105221305020_add_middle_name_to_users_table.sql

ddbdavis commented 13 years ago

I think that is a reasonable approach. At work here we've implemented a similar system. Whenever we make updates to the database we create 1) a copy of the database in its current state for reverting purposes and then 2) a descriptive SQL script that can be run against the database in order to make any modifications.

This allows the update SQL scripts to be distributed as part of the repo (in an SQL) file and we are storing the previous versions of the database in case we need to revert. The only hangup here is that if a user wanted to rewind to a point before several database changes had been made, the scripts would have to all be run again in order to step forward (time consuming). I'm still looking into methods that other people are using to version a MySQL db.