开发者

Automate the generation of natural keys

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_toghete开发者_StackOverflow社区r fields?


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


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.


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


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.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜