UUIDs - Advice needed

[PleiadesAustralia]

Hello,

I am tearing my hair out with UUIDs. In creating append queries to the database it will usually work to simply add a unique integer value in these feels which then automatically convert to a UUID. However, when trying to debug and manually entering a record this approach doesn't work and I am unable to enter record manually. Why are UUIDs there? They seem to add little value while making things difficult. How should I work with these fields?

Regards,
Sam

[horsburgh]

Sam - we used UUIDs for a small number of things in ODM2 because we wanted those identifiers to be unique regardless of where they went. For example, if we had several ODM2 database instances from which metadata were being harvested for cataloging, we wanted the identifiers for things like SamplingFeatures to be the same in the database instance and in the catalog. This would help in disambiguating where multiple users (e.g., multiple database instances) may have use the same codes or names for different objects, which can happen.

Which RDBMS are you using, and how are you trying to insert data? Generally speaking - I've used a function in Python to generate UUIDs to insert them into ODM2 instances in SQLite, and I've used similar functions available in MySQL and SQL Server. Let us know a little more about what you've tried and we may be able to help more.

[emiliom]

FYI, in PostgreSQL (9.3, I think) I've used built-in uuid functionality, with this scheme:

md5(random()::text || clock_timestamp()::text)::uuid

[PleiadesAustralia]

I fixed the problem- versions of Access before 2014 cannot handle the bigint data type and thus can’t work on ODM2 without changing the type of the primary key. Upgrading to Access 2016 fixed the problem.

…

Sent from my iPhone On 6 Jul 2018, at 11:57 pm, Jeff Horsburgh <notifications@github.com<mailto:notifications@github.com>> wrote: Sam - we used UUIDs for a small number of things in ODM2 because we wanted those identifiers to be unique regardless of where they went. For example, if we had several ODM2 database instances from which metadata were being harvested for cataloging, we wanted the identifiers for things like SamplingFeatures to be the same in the database instance and in the catalog. This would help in disambiguating where multiple users (e.g., multiple database instances) may have use the same codes or names for different objects, which can happen. Which RDBMS are you using, and how are you trying to insert data? Generally speaking - I've used a function in Python to generate UUIDs to insert them into ODM2 instances in SQLite, and I've used similar functions available in MySQL and SQL Server. Let us know a little more about what you've tried and we may be able to help more. — You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub<#147 (comment)>, or mute the thread<https://github.com/notifications/unsubscribe-auth/AlE2g6q1Hd-UMywQ4Fc2cgAWjAGU8ic-ks5uD4jhgaJpZM4U-oie>.

[emiliom]

@PleiadesAustralia are you deploying ODM2 on MS Access? Note that none of us has tried that. We've committed to supporting 4 RDBMS platforms (SQL Server, PostgreSQL, MySQL and SQLite), and the software ecosystem we've developed is generally tested on those 4 (eg, see https://github.com/ODM2/odm2-software-ecosystem) -- though it's not always possible to test all 4 for all software. However, with MS Access, you're pretty much on your own.

[PleiadesAustralia]

SQL Server - feeding data in from an access data capture module.

…

Sent from my iPhone On 9 Jul 2018, at 3:38 pm, Emilio Mayorga <notifications@github.com<mailto:notifications@github.com>> wrote: @PleiadesAustralia<https://github.com/PleiadesAustralia> are you deploying ODM2 on MS Access? Note that none of us has tried that. We've committed to supporting 4 RDBMS platforms (SQL Server, PostgreSQL, MySQL and SQLite), and the software ecosystem we've developed is generally tested on those 4 (eg, see https://github.com/ODM2/odm2-software-ecosystem) -- though it's not always possible to test all 4 for all software. However, with MS Access, you're pretty much on your own. — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub<#147 (comment)>, or mute the thread<https://github.com/notifications/unsubscribe-auth/AlE2g2T2oGaVs40GR-7wtectF9NcMlBoks5uEwh0gaJpZM4U-oie>.

[emiliom]

SQL Server - feeding data in from an access data capture module.

👍