UUID (universally unique identifier) is used for unique identifiers in and across systems. It is widely supported with algorithm(s) implemented and available as library/utility in programming languages. Also, databases have it implemented and made available in queries via DB function(s). There are 4 different version of uuid.
I recently attempted to migrate a Spring Boot application from MySQL database to PostgreSQL database. The application was written in Plain-Old-JDBC-DAO (Data Access Object interface/implementation pattern) style with hand-coded SQLs mixed and tightly coupled with Java code. Luckily there were fairly decent number of Integration Test-cases in place already. Otherwise, it would have been very challenging and nasty to identify all the SQLs mixed with code that needed migration.
In my attempt, I learned the fact that both MySQL and PostgreSQL databases have functions to generate random UUID. However, Postgres versions prior to 13 require little more effort to get access to those functions. This post is to share some details on that. I am not a database guy and would only love touching the surface ;)
Environment: Java 13, Spring Boot 2.2.4.RELEASE, PostgreSQL 11.8, MySQL 5.7.31 on macOS Catalina 10.15.6
After migrating schema using AWS Schema Conversion Tool (AWS SCT) and non-transactional data as SQL exports into baseline Flyway scripts, the next step was to identify code changes.
Most of integration test cases failed with org.springframework.jdbc.BadSqlGrammarException revealing all inline SQLs that needed migration. One such SQL was using MySQL uuid() function which failed with the following error, indicating that uunid() was MySQL specific:
org.postgresql.util.PSQLException: ERROR: function uuid() does not exist
MySQL - uuid support
MySql has uuid() function generates uuid of Version 1 algorithm which involves the MAC address of the computer and timestamp.
-- generates a random uuid of version 1
SELECT uuid();
635ef48c-1498-11eb-a38e-4bb3a0084954
PostgreSQL
PostgreSQL distribution comes with additional supplied modules but are not installed by default. Any user account with CREATE privilege can install module(s). There is a uuid generation function (gen_random_uuid()) available in pgcrypto module which generates uuid of Version 4 algorithm which is derived entirely from random hexadecimal numbers.
Without having pgcrypto module installed, the SQL:
SELECT gen_random_uuid(); -- genertes random UUID
would result with the following error:
SQL Error [42883]: ERROR: function gen_random_uuid() does not exist
Hint: No function matches the given name and argument types. You might need to add explicit type casts.
The following SQLs are handy to find out all available extensions and see the list of extensions installed.
SELECT * FROM pg_avilable_extensions ORDER BY name; -- list available extensions
SELECT * FROM pg_extension; -- list installed extensions
To install pgcrypto extension, use the following command.
This can go into application's Flyway script to get the extension installed into the database as needed.
CREATE EXTENSION IF NOT EXISTS "pgcrypto"; -- creates extension
Once, pgcrypto extension is installed, the following returns a random UUID generated:
SELECT gen_random_uuid(); -- generate a random uuid of version 4
842d3fae-7788-4ecb-b441-7c7e8130b8bf
NOTE
In PostgreSQL 13 this function is made available in the core. There is no need for installing pgcrypto module in this case.
TIP
Finding version number of a given uuid string is no big deal.
The M in uuid format: xxxxxxxx-xxxx-Mxxx-xxxx-xxxxxxxxxxxx tells the version number.
Groovy script to find out the version number of a given uuid string:
println "uuid version: ${UUID.fromString('842d3fae-7788-4ecb-b441-7c7e8130b8bf').version()}" // 4, PostgreSQL gen_random_uuid() generated
println "uuid version: ${UUID.fromString('635ef48c-1498-11eb-a38e-4bb3a0084954').version()}" // 1, MySQL uuid() generated
Posts
No comments:
Post a Comment