Thanks, Phil. I think you've understood my problem fairly well. If I'm unclear it's partly because I'm new to db mechanics, but I'll try to lay things out more clearly:
My existing setup: - I have two existing instances of the same web2py application - Currently the models just use web2py's native ID for referencing. - I have a lot of *existing* records with reference fields that use those ID numbers. - my back-end is sqlite My goal: - I need to set up a way to synchronize the database information between the two instances (not just replicate an identical db instance). - It is quite likely that different data may be created in *both* instances between synchronizations. My partial solution: - I wanted to allow either instance to draw in new (or updated) data from the other via a csv file (generated by the other instance). - I was focusing on this approach because it's the only one suggested in the web2py manual for syncing db instances. - Adding a UUID field to the existing model would allow me to recognize duplicate records accurately. My problem with this solution: - I have a whole lot of reference fields in multiple tables that use the current ID numbers. When I start syncing based on UUID, these ID references will be broken on one or the other app instance. Possible workarounds? 1. I could follow the approach in the web2py manual (UUID fields, csv as the vehicle for getting new data) and migrate all of my reference fields to use the UUID field instead of ID. I'm a bit nervous about the potential for data corruption and errors in executing such a large-scale migration with my own migration script. This would also mean my reference fields are (as you say) only enforced at the web2py level, not at the db (sqlite) level. I think it would also limit my use of web2py's model features (e.g., list-reference fields would have to become simple list-string fields?). 2. I could move the whole db to use UUIDs for the PK of each table. I much prefer this idea. It sounds more elegant and would allow me to continue using my current model structure (with regular reference fields, list-reference fields, etc.). But I have no idea how to go about setting this up, either on the sqlite end (can sqlite use UUIDs as PKs?) or on the web2py end. Any help would be greatly appreciated! Ian On Saturday, May 24, 2014 4:02:37 AM UTC-4, Philip Kilner wrote: > > Hi Ian, > > I see that Massimo has answered you, but on re-reading your question I > wasn't clear quite what you were asking. I have an application that > needs to use UUIDs myself, so thought it worth jumping in. > > On 14/05/14 16:44, 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. > > > > Is this an existing web2py-generated db, or a legacy db? Am assuming the > former, but tell me if I'm wrong... > > Do you have existing ID fields which you wish to /convert/ to UUIDs, so > that you are using UUIDs *instead* of IDs? > > Or are the IDs exposed in the application, such that you wish to > /supplement/ them with an UUID field to support replication? > > > > 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.) > > > > It's the mention of CSV imports that I'm stuck on - is the intention to > use this as your replication mechanism, or are you saying that you are > importing data into each instance of the db, and need to preserve > imported IDs? > > I may be misreading you, but I would very strongly recommend the use of > the db's existing native replication mechanism if at all possible. You > don't specify your back-end database, but we've had good results from > Postgres replication, FWIW. > > > > Sorry if this explanation is overly complicated. I'm just trying to get > > things clear in my own mind. > > > > It does sound complicated, but I'm not 100% certain that I'm clearly > distinguishing between the outcome you want and your thoughts as to how > to achieve it. > > FWIW, I have a similar issue, and what I would like to do is use UUID > PKs throughout in web2py, and use the db's *native* replication > mechanism, which should work OOTB with UUID PKs. > > Am I right in thinking that this approach would solve your > ID/replication problem, but that your situation is complicated by the > fact that your starting point is an existing app with IDs? > > Apologies for bombarding you with questions! > > > -- > > Regards, > > PhilK > > > 'a bell is a cup...until it is struck' > > -- 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.
