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?

Trusteeship

django-natural-keys package now does exactly this. – Telstar

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

Heirdom

This is not exactly what I want to achieve: not a sync between databases but a user-driven exchange of data between separate installation of the same database. That is: user A inserts his data into his installation, then exports the data in a file and gives it to user B which loads the data into his own installation (which already has its data). I want to avoid any ID conflict (and manage all the other issues!) – Trusteeship

Hi Don. In that case you should use a GUID as your key. The database can automatically generate these for you. See uniqueidentifier. We have 50+ warehouses all inserting data remotely and send their data up to our primary database using SQL Server replication. They all use a GUID as the primary key as this is guaranteed to be unique. It works very well. – Heirdom

Answer 2

In that case you should use a GUID as your key. The database can automatically generate these for you. Google uniqueidentifier. We have 50+ warehouses all inserting data remotely and send their data up to our primary database using SQL Server replication. They all use a GUID as the primary key as this is guaranteed to be unique. It works very well.

Heirdom

Answer 3

my solution has nothing to do with natural keys but uses picke/unpickle. It's not the most efficient way, but it's simple and easy to adapt to your code. I don't know if it works with a complex db structure, but if this is not your case give it a try!

when connected to db A:

import pickle
records_a = your_model.objects.filter(...) 
f = open("pickled.records_a.txt", 'wb')
pickle.dump(records_a, f) 
f.close()

then move the file and when connected to db B run:

import pickle 
records_a = pickle.load(open('pickled.records_a.txt'))
for r in records_a:
    r.id = None
    r.save()

Hope this helps

Fiedling

Thanks, this answer goes in the right direction but it still doesn't cover my needs: I need to reconstruct relations which, in Django, rely on the record id; so I cannot set it to None and let Django assign it. – Trusteeship

Answer 4

make a custom base model by extending models.Model class, and write your generic manager inside it, and acustom .save() method then edit your models to extend the custome base model. this will have no side effect on your db tables structure nor old saved data, except when you update some old rows. and if you had old data try to make a fake update to all your recoreds.

Soubrette

This is the right answer, but needs fleshing out... I'm just working on this at the moment, but the trick is working out how to pass the fields in for the NK lookup. Another possibility would be to create factory function which returns a Manager class of the right type. – Monoclinous