Re: Preserving data through db updates/changes
Luke Skibinski Holt wrote:
Absolutely - I had never thought of anything like that before. It's
pretty daunting compared to the easy way (automating the
dumping/reloading and manual editing of sql files). I think the
implementation of such a beast would be very difficult though - there
are too many gotchas that have broad impact on the rest of the db. It's
easy enough to prune away at a model but the real difficulties come
when you want to add new fields, especially required fields.
I was just hoping for some django magic to do it all for me ;)
I've done some thinking about this with past (php) frameworks, and it
does strike me as odd that after all the work we put into automating
version control of our source code and our desktop installers... but
yet we don't have much of use for data schemas beyond hand writing SQL,
which can often be imprecise (maybe you forgot a few changes) and in the
case of using a decent ORM is not very DRY.
Obviously the full implicit system is out of bounds for some of the
reasons the parent mentions. We worry about our data if a fully
implicit system is doing the work. On the other hand, the fully
explicit techniques mentioned further up the message family aren't DRY
and can get annoying/irritating.
The key to any endeavor into such a field would be to step gradually in
the direction of a true modern version control. Here's some of the
steps that I see:
* How about an ``sqldiff`` command to sit beside the others like
``sqlclear`` and ``sqlreset``? You have the model, you have the current
db schema... show me what the differences are and I'll have a quicker
time in writing SQL to fix that. Even cooler: I'm using version
control, if I give the tool two model files, could you give me the
difference between the two in terms of the SQL? Neither of those should
be that tough, should they? But both should be that much more useful in
moving to a good model versioning system.
* How about a useful Schema Introspection API? Things like
``MyModel.objects.db.has_field('my_field_name')``... Not only would
this be useful for migration scripts (``if not
MyModel.objects.db.has_field('my_field_name'): # Add Field SQL
Script``), and fun things like automatic migration on the first save(),
but could be useful for creating "polymorphic" models that adapt to the
current db schema (for instance, I was thinking about very modular
systems that have "installable" options, and the current easiest way to
do this would be to have seperate "glue apps" with relational models,
but it would be neat to have models react to whether or not other
models, or certain fields of their own model, are "installed" in the
database).
* Then, how about a useful "Schema API" to add the fields and update the
fields based on the Model information? Once you have
``MyModel.objects.db.has_field('my_field_name')``, then you can think
about things like ``add_field()`` or ``update_field()``. With those
even cooler migration scripts could be built, and it could make it real
quick to update a database from the Python console.
Those are my thoughts at the moment, feel free to see if they are useful
or necessary. ("Do the simplest thing, but no simpler", right?)
--
--Max Battcher--
http://www.worldmaker.net/
Re: Preserving data through db updates/changes
On 1/18/06, Jeffrey E. Forcier <[EMAIL PROTECTED]> wrote: > > I do something similar to both Eric and Maniac, e.g. I create SQL > language files which contain ALTER TABLE and similar statements, and > apply those to a 'base' database to transform it into the most recent > version without losing data. I haven't automated it yet like Eric > has, but it won't be too difficult to accomplish when I want to. What we're all doing is creating our own flavors of version control with migration scripts. ;-) I've often wondered why databases are so special; app deployment requires migration of existing data, too. We just don't try to build an installer every time we make an app. It seems to me that a DB Version could be a django sys table attribute, and that the model could have that version number. If out of sync, django could give an informative error: "Err: poll model version 3 running against DB version 1". And providing some "convention over configuration", there could be a model-migration directory with file names like migrate_1_to_2.sql I haven't suggested this before because I wasn't prepared to implement it, but the discussion made the opportunity too ripe. ;-)
Re: Preserving data through db updates/changes
I do something similar to both Eric and Maniac, e.g. I create SQL language files which contain ALTER TABLE and similar statements, and apply those to a 'base' database to transform it into the most recent version without losing data. I haven't automated it yet like Eric has, but it won't be too difficult to accomplish when I want to. Regards, Jeff On Jan 18, 2006, at 4:04 PM, Eric Walstad wrote: Luke Skibinski Holt wrote: ... I'm just looking for a quick way to update my models in the development stage while keeping test data. Hey Luke, I keep sample/test data in sql files and have a script that reloads that data into my database. The script: - recreate the app's database (dropdb, createdb) - initialize the django tables (django-admin.py init) - install the admin and app projects (django-admin.py install admin, etc) - load all the sql files found in some/path/to/sql/files/ in alpha order I keep the sql files in a directory and I name them in such a way that they will be loaded back into the database in the order I need: $ ls -1 100_users.sql 150_addresses.sql ... 550_customers.sql 600_applications.sql ... 900_notes.sql 950_state_histories.sql I wrote a simple bash script to handle the drudgery. The heart of the script, the part that loads the sql, looks like this: for SQL_FILE in $APP_SQL/*.sql do echo echo "Loading $SQL_FILE" su - $USER_NAME -c "psql $APP_NAME < $SQL_FILE" done which loads all my data. The rest of the script collects info from the user regarding paths to django-admin.py, repository trunk and database user, etc., then runs the database and django-admin.py commands to drop/create the db and init and install django apps. When our schema changes, I edit the appropriate sql file to match the new schema. Then I run my 'complete_rebuild' script. Each member of the development team has a copy of the script so that if any of us changes the sample/test data, the rest can quickly and easily synchronize our local database with the new changes. We occasionally make changes to our schema, but frequently change the sample data to exercise testing edge cases. This approach has worked well for us during development. It allows us to have a common starting point of data and a fast and easy way to rebuild the application during development. I hope that helps. Eric. -- Jeffrey E. Forcier Research and Development Stroz Friedberg, LLC 15 Maiden Lane, 12th Floor New York, NY 10038 [main]212-981-6540 [direct]212-981-6546 http://www.strozllc.com This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No right to confidential or privileged treatment of this message is waived or lost by any error in transmission. If you have received this message in error, please immediately notify the sender by e-mail or by telephone at 212.981.6540, delete the message and all copies from your system and destroy any hard copies. You must not, directly or indirectly, use, disclose, distribute, print or copy any part of this message if you are not the intended recipient.
Re: Preserving data through db updates/changes
Luke Skibinski Holt wrote: ... > I'm just looking for a quick way to update my models in the development > stage while keeping test data. Hey Luke, I keep sample/test data in sql files and have a script that reloads that data into my database. The script: - recreate the app's database (dropdb, createdb) - initialize the django tables (django-admin.py init) - install the admin and app projects (django-admin.py install admin, etc) - load all the sql files found in some/path/to/sql/files/ in alpha order I keep the sql files in a directory and I name them in such a way that they will be loaded back into the database in the order I need: $ ls -1 100_users.sql 150_addresses.sql ... 550_customers.sql 600_applications.sql ... 900_notes.sql 950_state_histories.sql I wrote a simple bash script to handle the drudgery. The heart of the script, the part that loads the sql, looks like this: for SQL_FILE in $APP_SQL/*.sql do echo echo "Loading $SQL_FILE" su - $USER_NAME -c "psql $APP_NAME < $SQL_FILE" done which loads all my data. The rest of the script collects info from the user regarding paths to django-admin.py, repository trunk and database user, etc., then runs the database and django-admin.py commands to drop/create the db and init and install django apps. When our schema changes, I edit the appropriate sql file to match the new schema. Then I run my 'complete_rebuild' script. Each member of the development team has a copy of the script so that if any of us changes the sample/test data, the rest can quickly and easily synchronize our local database with the new changes. We occasionally make changes to our schema, but frequently change the sample data to exercise testing edge cases. This approach has worked well for us during development. It allows us to have a common starting point of data and a fast and easy way to rebuild the application during development. I hope that helps. Eric.
Re: Preserving data through db updates/changes
Luke Skibinski Holt wrote: I'm just looking for a quick way to update my models in the development stage while keeping test data. I once wrote how I do it: http://groups.google.com/group/django-users/browse_frm/thread/251a42339efc79df/94ab85140ec35c97?lnk=st=3#94ab85140ec35c97
Re: Preserving data through db updates/changes
Just dump the data into a file and reload it after re-creating the tables. At most, you will have to edit a few SQL fields/values to comply with the new column/table names. Doing it automated is *really* hard and I don't think it will be better (or more accurate) than just re-inserting the data via some db command. On 1/18/06, Luke Skibinski Holt <[EMAIL PROTECTED]> wrote: > > How probable would it be to implement a means of updating a database > schema when only minor changes occur? > > 'minor' has to be defined, but the removal of columns or entire models > could also be considered depending on their impact to the rest of the > app/project. With no regard for the actual data, models with no > relations could easily be dropped or have fields added/removed without > a sqlreset and then a clumsy find/replace on the data dump to reinsert > it back. > > I'm just looking for a quick way to update my models in the development > stage while keeping test data. Django is great for prototyping, and > despite my best laid plans (and usecases), things change and grow as I > discover new ways of doing them (as they rightfully should in a > prototype). > > Are there any such plans in the works? > > > Luke Skibinski Holt > >
Preserving data through db updates/changes
How probable would it be to implement a means of updating a database schema when only minor changes occur? 'minor' has to be defined, but the removal of columns or entire models could also be considered depending on their impact to the rest of the app/project. With no regard for the actual data, models with no relations could easily be dropped or have fields added/removed without a sqlreset and then a clumsy find/replace on the data dump to reinsert it back. I'm just looking for a quick way to update my models in the development stage while keeping test data. Django is great for prototyping, and despite my best laid plans (and usecases), things change and grow as I discover new ways of doing them (as they rightfully should in a prototype). Are there any such plans in the works? Luke Skibinski Holt
