Automate the generation of natural keys

Question

I'm studying a way to serialize part of the data in database A and deserialize it in database B (a sort of save/restore between different installations) and I've had a look to Django natural keys to avoid problems due to duplicated IDs.

The only issue is that I should add a custom manager and a new method to all my models. Is there a way to make Django automatically generate natural keys by looking at unique=True or unique_togheter fields?

Answer 1

Please note this answer has nothing to do with Django, but hopefully give you another alternative to think about.

You didn't mention your database, however, in SQL Server there is a BINARY_CHECKSUM() keyword you can use to give you a unique value for the data held in the row. Think of it as a hash against all the fields in the row. This checksum method can be used to update a database from another by checking if local row checksum <> remote row checksum.

This SQL below will update a local database from a remote database. It won't insert new rows, for that you use insert ... where id > @MaxLocalID

SELECT  delivery_item_id, BINARY_CHECKSUM(*) AS bc
INTO    #DI
FROM    [REMOTE.NETWORK.LOCAL].YourDatabase.dbo.delivery_item di


SELECT  delivery_item_id, BINARY_CHECKSUM(*) AS bc
INTO    #DI_local
FROM    delivery_item di

-- Get rid of items that already match
DELETE  FROM #DI_local
WHERE   delivery_item_id IN (SELECT l.delivery_item_id
                             FROM   #DI x, #DI_local l
                             WHERE  l.delivery_item_id = x.delivery_item_id
                             AND l.bc = x.bc)

DROP TABLE #DI

UPDATE  DI
SET     engineer_id = X.engineer_id,
        ... -- Set other fields here
FROM    delivery_item DI,
        [REMOTE.NETWORK.LOCAL].YourDatabase.dbo.delivery_item x,
        #DI_local L
WHERE   x.delivery_item_id = L.delivery_item_id
        AND DI.delivery_item_id = L.delivery_item_id

DROP TABLE #DI_local

For the above to work, you will need a linked server between your local database and the remote database:

-- Create linked server if you don't have one already 
IF NOT EXISTS ( SELECT  srv.name
                FROM    sys.servers srv
                WHERE   srv.server_id != 0
                        AND srv.name = N'REMOTE.NETWORK.LOCAL' ) 
    BEGIN
        EXEC master.dbo.sp_addlinkedserver @server = N'REMOTE.NETWORK.LOCAL',
        @srvproduct = N'SQL Server'

        EXEC master.dbo.sp_addlinkedsrvlogin
        @rmtsrvname = N'REMOTE.NETWORK.LOCAL',
        @useself = N'False', @locallogin = NULL,
        @rmtuser = N'your user name',
        @rmtpassword = 'your password'
    END
GO