Re: [web2py] To uuid or not?
Not necessarily.
If you export and reimport them with the option
db.import_from_csv_file(...,id_map={}) and if the db is empty when
reimporting, everything works fine.
If you want to be able to import data in a database already populated, you
must have the UUID field. This is because web2py cannot tell if two records
are the same my simply looking at the id. In fact the database could have
been exported on a different instance with a different numbering system.
On Saturday, 14 July 2012 11:33:50 UTC-5, Alexei Vinidiktov wrote:
>
>
>
> On Monday, May 14, 2012 7:58:57 PM UTC+7, Ross Peoples wrote:
>>
>> Some databases allow you to temporarily turn off auto-generating IDs so
>> that you can import a table keeping its original IDs intact. I wrote a
>> database abstraction layer a few years back for another project that
>> specifically allowed you to make an identical copy of a database from a
>> MySQL database to a PostgreSQL database on a different machine and
>> preserving the IDs was something I needed to figure out. Here is how you do
>> it with MSSQL, MySQL, and PostgreSQL. Maybe Massimo can add this as an
>> option to the DAL's import method:
>>
>> First, import your records, then run these on your database engine:
>>
>> MSSQL:
>> db.executesql('SET IDENTITY_INSERT dbo.mytable OFF');
>> db.executesql('DBCC CHECKIDENT (mytable)');
>>
>> MySQL:
>> count = db.executesql('SELECT MAX(id) FROM mytable;')
>> count += 1
>> db.executesql('ALTER TABLE mytable AUTO_INCREMENT=%s;' % count)
>>
>> PostgreSQL:
>> count = db.executesql('SELECT MAX(id) FROM mytable;')
>> count += 1
>> db.executesql('ALTER SEQUENCE mytable_sequence RESTART WITH %s;' % count)
>>
>> For all 3 database engines, this sets the auto-increment counter to the
>> max ID it finds in the table (AFTER the import) +1 so that new rows will
>> have the proper IDs.
>>
>
> Do I understand correctly that without this addition it is impossible to
> maintain referential integrity of a database when exporting its tables as
> csv files and then importing them back?
>
Re: [web2py] To uuid or not?
On Monday, May 14, 2012 7:58:57 PM UTC+7, Ross Peoples wrote:
>
> Some databases allow you to temporarily turn off auto-generating IDs so
> that you can import a table keeping its original IDs intact. I wrote a
> database abstraction layer a few years back for another project that
> specifically allowed you to make an identical copy of a database from a
> MySQL database to a PostgreSQL database on a different machine and
> preserving the IDs was something I needed to figure out. Here is how you do
> it with MSSQL, MySQL, and PostgreSQL. Maybe Massimo can add this as an
> option to the DAL's import method:
>
> First, import your records, then run these on your database engine:
>
> MSSQL:
> db.executesql('SET IDENTITY_INSERT dbo.mytable OFF');
> db.executesql('DBCC CHECKIDENT (mytable)');
>
> MySQL:
> count = db.executesql('SELECT MAX(id) FROM mytable;')
> count += 1
> db.executesql('ALTER TABLE mytable AUTO_INCREMENT=%s;' % count)
>
> PostgreSQL:
> count = db.executesql('SELECT MAX(id) FROM mytable;')
> count += 1
> db.executesql('ALTER SEQUENCE mytable_sequence RESTART WITH %s;' % count)
>
> For all 3 database engines, this sets the auto-increment counter to the
> max ID it finds in the table (AFTER the import) +1 so that new rows will
> have the proper IDs.
>
Do I understand correctly that without this addition it is impossible to
maintain referential integrity of a database when exporting its tables as
csv files and then importing them back?
Re: [web2py] To uuid or not?
On 14 May 2012 15:36, Ross Peoples wrote: > I have created issue 796: > http://code.google.com/p/web2py/issues/detail?id=796 > > > Thanks Massimo, Ross. This will be of great help. Regards Johann -- Because experiencing your loyal love is better than life itself, my lips will praise you. (Psalm 63:3)
Re: [web2py] To uuid or not?
I have created issue 796: http://code.google.com/p/web2py/issues/detail?id=796 While creating the issue, I noticed that I had made an error with the MSSQL part. This was corrected in the issue.
Re: [web2py] To uuid or not?
Definitively. Please open a ticket about this. Thanks Ross.
On Monday, 14 May 2012 07:58:57 UTC-5, Ross Peoples wrote:
>
> Some databases allow you to temporarily turn off auto-generating IDs so
> that you can import a table keeping its original IDs intact. I wrote a
> database abstraction layer a few years back for another project that
> specifically allowed you to make an identical copy of a database from a
> MySQL database to a PostgreSQL database on a different machine and
> preserving the IDs was something I needed to figure out. Here is how you do
> it with MSSQL, MySQL, and PostgreSQL. Maybe Massimo can add this as an
> option to the DAL's import method:
>
> First, import your records, then run these on your database engine:
>
> MSSQL:
> db.executesql('SET IDENTITY_INSERT dbo.mytable OFF');
> db.executesql('DBCC CHECKIDENT (mytable)');
>
> MySQL:
> count = db.executesql('SELECT MAX(id) FROM mytable;')
> count += 1
> db.executesql('ALTER TABLE mytable AUTO_INCREMENT=%s;' % count)
>
> PostgreSQL:
> count = db.executesql('SELECT MAX(id) FROM mytable;')
> count += 1
> db.executesql('ALTER SEQUENCE mytable_sequence RESTART WITH %s;' % count)
>
> For all 3 database engines, this sets the auto-increment counter to the
> max ID it finds in the table (AFTER the import) +1 so that new rows will
> have the proper IDs.
>
Re: [web2py] To uuid or not?
Some databases allow you to temporarily turn off auto-generating IDs so
that you can import a table keeping its original IDs intact. I wrote a
database abstraction layer a few years back for another project that
specifically allowed you to make an identical copy of a database from a
MySQL database to a PostgreSQL database on a different machine and
preserving the IDs was something I needed to figure out. Here is how you do
it with MSSQL, MySQL, and PostgreSQL. Maybe Massimo can add this as an
option to the DAL's import method:
First, import your records, then run these on your database engine:
MSSQL:
db.executesql('SET IDENTITY_INSERT dbo.mytable OFF');
db.executesql('DBCC CHECKIDENT (mytable)');
MySQL:
count = db.executesql('SELECT MAX(id) FROM mytable;')
count += 1
db.executesql('ALTER TABLE mytable AUTO_INCREMENT=%s;' % count)
PostgreSQL:
count = db.executesql('SELECT MAX(id) FROM mytable;')
count += 1
db.executesql('ALTER SEQUENCE mytable_sequence RESTART WITH %s;' % count)
For all 3 database engines, this sets the auto-increment counter to the max
ID it finds in the table (AFTER the import) +1 so that new rows will have
the proper IDs.
Re: [web2py] To uuid or not?
> > > > As I understand it the id's get rebuilt if you export a table as csv-file > and import it on another computer. When the whole database gets exported > and imported the relationships stays consistent. > > Yes, I've learned this the hard way. Moving the database between computers is OK if you use the native database backup method, but you definitely lose database portability via DAL if you expose IDs in URLs. We don't use UUIDs for URLs, but rather hashes that can be truncated (and possibly base64-encoded (URL-safe)) to keep the values brief.
Re: [web2py] To uuid or not?
On 12 May 2012 21:04, Sebastian E. Ovide wrote: > > what problems have you got moving the DB to another computer ? > As I understand it the id's get rebuilt if you export a table as csv-file and import it on another computer. When the whole database gets exported and imported the relationships stays consistent. Regards Johann -- Because experiencing your loyal love is better than life itself, my lips will praise you. (Psalm 63:3)
Re: [web2py] To uuid or not?
I find that using the ID works perfectly well... I'm using UUID only on those situations where I need to pass it as vars/args (URLS for example) so that hackers cannot guess the IDs what problems have you got moving the DB to another computer ? On Fri, May 11, 2012 at 2:07 PM, Johann Spies wrote: > I developed my original app to use uuid-crossreferences in stead of the > normal 'id'-field. The reason was that I wanted it to be consistent when I > move the database to another computer. > > Now I am rewriting the app to make it more efficient and to make the code > cleaner - applying some new features that came into web2py recently - e.g. > archiving. > > I am considering it to drop the dependence on uuid-references and move > back to id-based references and to keep the present references in the > database valid I am thinking (as an experiment) to do it in the following > way: > > * add a computed field to each table that use uuid-based references. This > computation will then lookup the id in the other table based on the > uuid-reference. > * after confirming that the references work well, I plan to drop the > uuid-fields and uuid-based reference fields from each table using them. and > change the computed field to normal reference-field. > * remove archiving methods based on uuid which I have developed and use > the new archiving in the trunk. > > Am I overlooking something? > > I would like the opinion of some experts in this list on what I have in > mind please. Is there still a place for uuid-based references? When? > > Regards > Johann > -- > Because experiencing your loyal love is better than life itself, > my lips will praise you. (Psalm 63:3) > > -- Sebastian E. Ovide
[web2py] To uuid or not?
I developed my original app to use uuid-crossreferences in stead of the normal 'id'-field. The reason was that I wanted it to be consistent when I move the database to another computer. Now I am rewriting the app to make it more efficient and to make the code cleaner - applying some new features that came into web2py recently - e.g. archiving. I am considering it to drop the dependence on uuid-references and move back to id-based references and to keep the present references in the database valid I am thinking (as an experiment) to do it in the following way: * add a computed field to each table that use uuid-based references. This computation will then lookup the id in the other table based on the uuid-reference. * after confirming that the references work well, I plan to drop the uuid-fields and uuid-based reference fields from each table using them. and change the computed field to normal reference-field. * remove archiving methods based on uuid which I have developed and use the new archiving in the trunk. Am I overlooking something? I would like the opinion of some experts in this list on what I have in mind please. Is there still a place for uuid-based references? When? Regards Johann -- Because experiencing your loyal love is better than life itself, my lips will praise you. (Psalm 63:3)
