> > 4) run a migrate script (eg 'tg-admin migrate'). This script would
> > drop the site's database and reinitialize it (sql drop && sql create).
>
> Why dropping? It would be more efficient to issue several ALTER TABLE,
> etc. than dropping information. If you have user passwords, important data,
> etc. you can't drop it.
>From what i understand, sqlite doesn't support ALTER TABLE (I could be
wrong here), and the underlying objective of what i'm thinking is to
abstract the migration process away from the database.
> > The migrate script would then populate the new (empty) database using
> > the backup copy and the migration file created in step 1
>
> How? What would it do about new mandatory columns? Or columns that doesn't
> exist anymore? Or if data was moved from one table to another (e.g. when you
> had a 1:1 relationship and now you have a N:M relationship)?
I'm assuming here that by 'mandantory column' you mean properties with
no default value specified. I was thinking that the process would be
something like:
---------
for i in old.Class1.select():
parameters = dict()
for key in old.i.__dict__():
parameters[key] = old.i.getattr(key)
new.Class1(parameters)
---------
The actual code would need to be more complex to incorporate property
name changes, property conversions, and class name changes. The point,
in any case, is that class instances would only be created in the new
database if they already exist in the old database, so mandantory
columns would be given values according to the existing database and
the migration file. For mandantory columns which referred to data not
already stored in the old database. If there is no way to convert the
existing data to the new format, there's not much point in worrying
about migration...
as for moving data from one table to another, that shouldn't be a
problem, you would retrieve the data from the old table by
instantiating the object, and then put it in the new table when you
inserted the new instance.
converting from 1:1 to N:M would rely on conversion functions. for
example, if you wanted to convert the following:
Old Model------------
class Person(SQLObject):
address = StringCol()
New Model----------
class Person(SQLObject):
address = MultipleJoin('Address')
class Address(SQLObject):
number = IntCol()
street = StringCol()
------------------------
You could define a conversion function in the migrate file which parsed
the address string, inserted the new Address instance, and added it to
the Person instance. As I mentioned, I think it would be helpful to
provide some helper functions for converting from one to another, so
maybe one of the parameters passed to the mapping function
('ValueToMultipleJoin' for example) would be the function which did
this conversion.
> > 5) "tg-admin migrate" would delete the old site copy and drop the
> > transfer database if the migration completed without errors
>
> Why not keeping it there? Dropping a database manually is a one command only
> thing. And safety is never enough...
That's true
> > The migrate script would move data between the old and new sites by
> > essentially starting both simultaneously. It would scan the new site's
> > model for all the defined classes and populate them based on the
> > migration file.
>
> What about columns that changed places? Or if I had a
> StringCol/UnicodeStringCol and now I have a ForeignKey or MultipleJoin in
> place?
Again, this shouldn't be an issue - the point of the migration file is
to tell the migration script how to make these changes. If it can be
done by hand, it shouldn't be too hard to code it, and for me it would
be easier to code in the 'turbogears environment' than by editing SQL
dumps in a text editor.
> > Migration files would specify differences between the two models.
> > Unless you specify otherwise in the migrate file, "tg-admin migrate"
> > would assume that any class or property with the same name in both the
> > new and old model has not changed, and assign the value from the old
> > version to the new version.
> >
> > Anything that has been changed could be specified in the migrate file,
> > so for example if Class.property1 had been renamed to Class.property2,
> > you could specify something like this in the migrate file:
> >
> > new.Class.property2 = old.Class.property1
>
> Could it be new.Class1.property_text1 = old.Class5.property_text3 ?
sure
> > Ideally, "tg-admin migrate" would provide a set of conversion functions
...
> > new.FavoriteLink = old.Link
> >
> > --------------
>
>
> All your examples are for changes inside one table only. What if I'm
> migrating data from one table to another?
this would depend on the semantics of the migration file, but i don't
see any reason it wouldn't be possible.
> Dropping the database is bad. We'd loose views, functions, stored procedures,
> extra indices, rules, etc. from it because those things can't be described
> with the ORM and are *HUGE* performance improvers, besides providing nice
> abstractions.
Are you creating these all at the command line each time you create a
database? It seems like this is something which would be in an
initialization script. If not, how do you distribute the app? What
happens when the database server melts down - is all that work lost?
If you have some easy way to set these up, why couldn't you use that
script to initialize the new database? It would make me very nervous
to rely on things which couldn't be easily moved from one database (or
server) to another.
> Why would one need to take the site down? It would be more useful to make a
> copy of the running database, perform all commands needed to convert it from
> one state to the other (one might want to go back...) and then prevente data
> insertion and updating on the old database while inserting records onto the
> new one. This would minimize downtime (all records would be queriable, but
> they won't be modifiable) and would preserve all structures one has created in
> parallel to the ORM.
Good point - it does make more sense for the 'new' version to be put
aside somewhere until you can verify the migration went as planned
(this is a good argument for re-creating the database, by the way). I
was thinking of just svn update-ing the install in place - didn't occur
to me to install the new version somewhere else. Doing it this way
would have the added benefit of not requiring a 'pre-migrate' script -
you could just set up the new version, then tell the migrate script
which (existing) TG install to pull data from.
> > new.Class.property2 = old.Class.property1
>
> Why not just assume the LHS is old and the RHS is the new?
It seems like it would be easier to retain namespaces, also I think I
prefer to be more explicit.
> > new.Class.property1 = MultipleJoinToRelatedJoin( old.Class.property1 )
> > new.Class.property2 = FloatColToIntCol( old.Class.property2,
> > round='down' )
>
> Couldn't you just introspect this info from the old/new class and
> convert automatically?
probably. In some cases it might be nice to be able to specify how the
conversion should take place. For example, if you were converting a
N:M MultipleJoin to a RelatedJoin, it would be nice to be able to
decide if a given M should be inserted, or if an existing M with the
same (or similar) values should be used.
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"TurboGears" 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/turbogears?hl=en
-~----------~----~----~----~------~----~------~--~---
