[web2py] Re: How to migrate to UUID references in db without messing up reference fields
Thanks very much Massimo and Philip. I had missed the fact that the id_map
parameter triggered smart updating of the reference fields. That's great
and for the moment is solves my problem.
I'll keep thinking about a longer-term solution for syncing. Here's what
I'm thinking would be desirable:
1. 2-way intelligent sync (look at whatever update-time info is
available in the table), maybe using websockets
2. automated backup of the original data for rollback if necessary
3. can be performed on one table
4. reference field ids are updated on both ends as necessary
5. no extra fields need be added to db model
6. can easily be scheduled or triggered by user activity on one end
In the long run this is something I'll need, so if I make any progress I'll
let you know.
Ian
On Monday, May 26, 2014 11:49:20 AM UTC-4, Massimo Di Pierro wrote:
I now understand your problem better.
Have you looked into?
db.export_to_csv_file(file)
db.import_from_csv_file(file,id_map={})
https://groups.google.com/forum/#!msg/web2py/P_lBv8JKiiQ/LESBbSGikw8J
When importing from the file the id_map will recognize records from the
uuid (assuming they have it) and fix the references accordingly. So the
references in the imported records will be different but will point to the
right record. This may fail if there are circular references because it
would be unable to built the references. Moreover it requires comparing the
entire db and cannot be used on one single table.
I agree a better solution would be desirable.
Massimo
On Saturday, 24 May 2014 10:58:39 UTC-5, Ian W. Scott wrote:
Thanks Massimo. I explained my problem a bit better (I hope) in my reply
to Philip below. There are a couple of reasons why the approach you suggest
isn't ideal, from my point of view:
1. I'm working with existing database instances (sqlite). So if I
change all of the reference fields to use uuid connections as you
suggest,
I will have to perform a large-scale migration with a custom script. I'm
nervous about the potential for error here.
2. This approach would prevent my use of some great web2py dal
features (reference fields, list-reference fields, recursive selects,
cascading deletes, etc.) I'd really rather not have to sacrifice so much
web2py functionality just to synchronize two db instances.
I wonder whether this doesn't point to a potential area for improvement
in web2py. It seems like a framework like this would benefit from having an
easier, built-in solution for synchronizing db instances without breaking
dal features. So if you can think of a way to facilitate synchronization
that's more elegant, I'd be happy to contribute back any code I write to
implement it.
Thanks again,
Ian
On Saturday, May 24, 2014 3:08:58 AM UTC-4, Massimo Di Pierro wrote:
Hello Ian, Sorry we overlooked your email.
You can easily add a UUID field
from gluon.util import web2py_uuid
db.define_table('person',Field('name'),Field('uuid',compute=lambda:web2py_uuid()))
You can also create table that reference that field:
db.define_table('thing',Field('name'),Field('owner',requires=IS_IN_DB(db,'person.uuid','name'))
Except the reference will not be enforced at the DB level, only at the
web2py level.
Massimo
On Friday, 23 May 2014 23:04:10 UTC-5, Ian W. Scott wrote:
After 10 days I've received no help on this. Is there something about
the question that is inappropriate? Is my question unclear?
On Wednesday, May 14, 2014 11:44:43 AM UTC-4, Ian W. Scott wrote:
I need to take an existing db and implement a UUID referencing system
so that I can sync local db's with a central remote version. But I'm
concerned that this will break reference fields that refer to the newly
synced rows.
My understanding is that the UUID field is necessary because a csv
import will assign different row ids to the new entries in the target db
than the ones they had in the source db (especially if new records have
been added to the target db in the meantime). The UUID is supposed to
overcome this, by allowing the db to recognize that rows are equivalent
even if they have different ids. But in that case, won't reference fields
in other tables often be pointing to the wrong ID number in the target
db?
(i.e., they'll keep the row ID of the db version in which they were
created, and if this ID changes in the target db they will then be
referencing a different record.)
Sorry if this explanation is overly complicated. I'm just trying to
get things clear in my own mind.
Thanks
--
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
---
You received this message because you are subscribed to the Google Groups
web2py-users group.
To unsubscribe from this group and stop receiving emails from it, send an
[web2py] Re: How to migrate to UUID references in db without messing up reference fields
I now understand your problem better.
Have you looked into?
db.export_to_csv_file(file)
db.import_from_csv_file(file,id_map={})
https://groups.google.com/forum/#!msg/web2py/P_lBv8JKiiQ/LESBbSGikw8J
When importing from the file the id_map will recognize records from the
uuid (assuming they have it) and fix the references accordingly. So the
references in the imported records will be different but will point to the
right record. This may fail if there are circular references because it
would be unable to built the references. Moreover it requires comparing the
entire db and cannot be used on one single table.
I agree a better solution would be desirable.
Massimo
On Saturday, 24 May 2014 10:58:39 UTC-5, Ian W. Scott wrote:
Thanks Massimo. I explained my problem a bit better (I hope) in my reply
to Philip below. There are a couple of reasons why the approach you suggest
isn't ideal, from my point of view:
1. I'm working with existing database instances (sqlite). So if I
change all of the reference fields to use uuid connections as you suggest,
I will have to perform a large-scale migration with a custom script. I'm
nervous about the potential for error here.
2. This approach would prevent my use of some great web2py dal
features (reference fields, list-reference fields, recursive selects,
cascading deletes, etc.) I'd really rather not have to sacrifice so much
web2py functionality just to synchronize two db instances.
I wonder whether this doesn't point to a potential area for improvement in
web2py. It seems like a framework like this would benefit from having an
easier, built-in solution for synchronizing db instances without breaking
dal features. So if you can think of a way to facilitate synchronization
that's more elegant, I'd be happy to contribute back any code I write to
implement it.
Thanks again,
Ian
On Saturday, May 24, 2014 3:08:58 AM UTC-4, Massimo Di Pierro wrote:
Hello Ian, Sorry we overlooked your email.
You can easily add a UUID field
from gluon.util import web2py_uuid
db.define_table('person',Field('name'),Field('uuid',compute=lambda:web2py_uuid()))
You can also create table that reference that field:
db.define_table('thing',Field('name'),Field('owner',requires=IS_IN_DB(db,'person.uuid','name'))
Except the reference will not be enforced at the DB level, only at the
web2py level.
Massimo
On Friday, 23 May 2014 23:04:10 UTC-5, Ian W. Scott wrote:
After 10 days I've received no help on this. Is there something about
the question that is inappropriate? Is my question unclear?
On Wednesday, May 14, 2014 11:44:43 AM UTC-4, Ian W. Scott wrote:
I need to take an existing db and implement a UUID referencing system
so that I can sync local db's with a central remote version. But I'm
concerned that this will break reference fields that refer to the newly
synced rows.
My understanding is that the UUID field is necessary because a csv
import will assign different row ids to the new entries in the target db
than the ones they had in the source db (especially if new records have
been added to the target db in the meantime). The UUID is supposed to
overcome this, by allowing the db to recognize that rows are equivalent
even if they have different ids. But in that case, won't reference fields
in other tables often be pointing to the wrong ID number in the target db?
(i.e., they'll keep the row ID of the db version in which they were
created, and if this ID changes in the target db they will then be
referencing a different record.)
Sorry if this explanation is overly complicated. I'm just trying to get
things clear in my own mind.
Thanks
--
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
---
You received this message because you are subscribed to the Google Groups
web2py-users group.
To unsubscribe from this group and stop receiving emails from it, send an email
to web2py+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
[web2py] Re: How to migrate to UUID references in db without messing up reference fields
Hello Ian, Sorry we overlooked your email.
You can easily add a UUID field
from gluon.util import web2py_uuid
db.define_table('person',Field('name'),Field('uuid',compute=lambda:web2py_uuid()))
You can also create table that reference that field:
db.define_table('thing',Field('name'),Field('owner',requires=IS_IN_DB(db,'person.uuid','name'))
Except the reference will not be enforced at the DB level, only at the
web2py level.
Massimo
On Friday, 23 May 2014 23:04:10 UTC-5, Ian W. Scott wrote:
After 10 days I've received no help on this. Is there something about the
question that is inappropriate? Is my question unclear?
On Wednesday, May 14, 2014 11:44:43 AM UTC-4, Ian W. Scott wrote:
I need to take an existing db and implement a UUID referencing system so
that I can sync local db's with a central remote version. But I'm concerned
that this will break reference fields that refer to the newly synced rows.
My understanding is that the UUID field is necessary because a csv import
will assign different row ids to the new entries in the target db than the
ones they had in the source db (especially if new records have been added
to the target db in the meantime). The UUID is supposed to overcome this,
by allowing the db to recognize that rows are equivalent even if they have
different ids. But in that case, won't reference fields in other tables
often be pointing to the wrong ID number in the target db? (i.e., they'll
keep the row ID of the db version in which they were created, and if this
ID changes in the target db they will then be referencing a different
record.)
Sorry if this explanation is overly complicated. I'm just trying to get
things clear in my own mind.
Thanks
--
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
---
You received this message because you are subscribed to the Google Groups
web2py-users group.
To unsubscribe from this group and stop receiving emails from it, send an email
to web2py+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
[web2py] Re: How to migrate to UUID references in db without messing up reference fields
Thanks Massimo. I explained my problem a bit better (I hope) in my reply to
Philip below. There are a couple of reasons why the approach you suggest
isn't ideal, from my point of view:
1. I'm working with existing database instances (sqlite). So if I change
all of the reference fields to use uuid connections as you suggest, I will
have to perform a large-scale migration with a custom script. I'm nervous
about the potential for error here.
2. This approach would prevent my use of some great web2py dal features
(reference fields, list-reference fields, recursive selects, cascading
deletes, etc.) I'd really rather not have to sacrifice so much web2py
functionality just to synchronize two db instances.
I wonder whether this doesn't point to a potential area for improvement in
web2py. It seems like a framework like this would benefit from having an
easier, built-in solution for synchronizing db instances without breaking
dal features. So if you can think of a way to facilitate synchronization
that's more elegant, I'd be happy to contribute back any code I write to
implement it.
Thanks again,
Ian
On Saturday, May 24, 2014 3:08:58 AM UTC-4, Massimo Di Pierro wrote:
Hello Ian, Sorry we overlooked your email.
You can easily add a UUID field
from gluon.util import web2py_uuid
db.define_table('person',Field('name'),Field('uuid',compute=lambda:web2py_uuid()))
You can also create table that reference that field:
db.define_table('thing',Field('name'),Field('owner',requires=IS_IN_DB(db,'person.uuid','name'))
Except the reference will not be enforced at the DB level, only at the
web2py level.
Massimo
On Friday, 23 May 2014 23:04:10 UTC-5, Ian W. Scott wrote:
After 10 days I've received no help on this. Is there something about the
question that is inappropriate? Is my question unclear?
On Wednesday, May 14, 2014 11:44:43 AM UTC-4, Ian W. Scott wrote:
I need to take an existing db and implement a UUID referencing system so
that I can sync local db's with a central remote version. But I'm concerned
that this will break reference fields that refer to the newly synced rows.
My understanding is that the UUID field is necessary because a csv
import will assign different row ids to the new entries in the target db
than the ones they had in the source db (especially if new records have
been added to the target db in the meantime). The UUID is supposed to
overcome this, by allowing the db to recognize that rows are equivalent
even if they have different ids. But in that case, won't reference fields
in other tables often be pointing to the wrong ID number in the target db?
(i.e., they'll keep the row ID of the db version in which they were
created, and if this ID changes in the target db they will then be
referencing a different record.)
Sorry if this explanation is overly complicated. I'm just trying to get
things clear in my own mind.
Thanks
--
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
---
You received this message because you are subscribed to the Google Groups
web2py-users group.
To unsubscribe from this group and stop receiving emails from it, send an email
to web2py+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
[web2py] Re: How to migrate to UUID references in db without messing up reference fields
After 10 days I've received no help on this. Is there something about the question that is inappropriate? On Wednesday, May 14, 2014 11:44:43 AM UTC-4, Ian W. Scott wrote: I need to take an existing db and implement a UUID referencing system so that I can sync local db's with a central remote version. But I'm concerned that this will break reference fields that refer to the newly synced rows. My understanding is that the UUID field is necessary because a csv import will assign different row ids to the new entries in the target db than the ones they had in the source db (especially if new records have been added to the target db in the meantime). The UUID is supposed to overcome this, by allowing the db to recognize that rows are equivalent even if they have different ids. But in that case, won't reference fields in other tables often be pointing to the wrong ID number in the target db? (i.e., they'll keep the row ID of the db version in which they were created, and if this ID changes in the target db they will then be referencing a different record.) Sorry if this explanation is overly complicated. I'm just trying to get things clear in my own mind. Thanks -- Resources: - http://web2py.com - http://web2py.com/book (Documentation) - http://github.com/web2py/web2py (Source code) - https://code.google.com/p/web2py/issues/list (Report Issues) --- You received this message because you are subscribed to the Google Groups web2py-users group. To unsubscribe from this group and stop receiving emails from it, send an email to web2py+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout.
