MSSQL Server UniqueIdentifier

[hyppopotamus]

pgloader --version
pgloader version "3.6.1"
compiled with SBCL 1.4.0-1.el7

config

load database
     from mssql://mssql2psql:blablabla@tsql/test
     into postgresql://test:test@psql/test

with create no indexes
including only table names like 'entity' in schema 'dbo'
set work_mem to '16MB', maintenance_work_mem to '512 MB'
alter schema 'dbo' rename to 'public'

before load do $$ drop schema if exists dbo cascade; $$;

The first half of the uuid translated "backward" for each section.

Additionals:
https://dba.stackexchange.com/questions/121869/sql-server-uniqueidentifier-guid-internal-representation
https://stackoverflow.com/questions/17679/why-does-guid-tostring-reverse-the-byte-order

[dimitri]

Hi @hyppopotamus ; can you give more information about the issue you have? Such as for example a list of identifiers in SQL Server and their migrated version in PostgreSQL?

[hyppopotamus]

Hi @dimitri , sure

MSSQL

id
15FB045F-DB6A-269D-6473-023F3C936FCF
0C033FB4-C629-B867-3C98-11F924DFEBBB
07926FF3-9319-B93E-80FF-1897825FDEAD
59F13A63-D259-355E-F81B-18DC52F3ACA5
...

Postgres

                  id
--------------------------------------
 5f04fb15-6adb-9d26-6473-023f3c936fcf
 b43f030c-29c6-67b8-3c98-11f924dfebbb
 f36f9207-1993-3eb9-80ff-1897825fdead
 633af159-59d2-5e35-f81b-18dc52f3aca5
...

[dimitri]

That's quite strange indeed. The whole code that handles the transformation of the unique identifiers to UUID is pasted below. We receive the UUID as a vector of bytes and use a standard UUID library to transform the bytes into a string representation. This library doesn't seem to know about the way MS does its GUID, I'm going to try something:

(defun sql-server-uniqueidentifier-to-uuid (id)
  (declare (type (or null (array (unsigned-byte 8) (16))) id))
  (when id
    (format nil "~a" (uuid:byte-array-to-uuid id))))

[dimitri]

Please compile from sources and try again, it might work better now with the adjusted transformation function. As I've coded that mostly “blind” (I tested with a single value from the stack overflow page you've quoted), more testing is necessary on this one.

CL-USER> (pgloader.transforms::sql-server-uniqueidentifier-to-uuid
          (uuid:uuid-to-byte-array
           (uuid:make-uuid-from-string
            "EBA4DE58-D35F-46D9-9049-7939381F1A3B")))
"58DEA4EB-5FD3-D946-9049-7939381F1A3B"

[hyppopotamus]

Please fix, or compilation error happens =)

hyppo@d9:~/GITS/pgloader$ git diff
diff --git a/src/utils/transforms.lisp b/src/utils/transforms.lisp
index 750add8..0c6961d 100644
--- a/src/utils/transforms.lisp
+++ b/src/utils/transforms.lisp
@@ -383,7 +383,7 @@
                           :clock-seq-var (aref id 8)
                           :clock-seq-low (aref id 9)
                           :node (uuid::arr-to-bytes 10 15 id))))
-      (princ-to-string uuid)))))
+      (princ-to-string uuid))))

 (defun unix-timestamp-to-timestamptz (unixtime-string)
   "Takes a unix timestamp (seconds since beginning of 1970) and converts it

[dimitri]

Oops. Sorry about that. Emacs/SLIME interactive environment is good at ignoring that problem, compiling from files is not, and I didn't recompile the whole project before sending the previous fix...

[hyppopotamus]

pgloader version "3.6.fbdc95e"
compiled with SBCL 1.4.16.104-843cc622f

looks good,
thank you