custom_merge with foreign keys

Happy new year to you all!

So I got around to implementing the idea I mentioned in the other thread. Maybe this is of interest to anybody? Since that thread is closed, I’m opening a new one.

This version of custom_merge is able to handle foreign keys in insert statements. Say, like the example in the other thread, there are two tables “foo” and “bar”, both have an “id” column, and bar has a “foo_id” referencing the foo table. Now it is possible to add records to both tables in one merge statement, like this:


local T = db.tables({vmd='mydatabase.vmd', name='Message'})
local foo = T.foo[1]
foo:remove('id')
foo.foovalue = 'some data'
local bar = T.bar[1]
bar:remove('id')
bar.barvalue = 'some more data'
bar.foo_id = 1

local myForeignKeys = {{
Table = 'bar',
Column = 'foo_id',
ReferencesTable = 'foo'
}}

dbConnection:customMerge{data = T, foreignKeys = myForeignKeys}


The foreignKeys parameter takes a list of records, telling which column of which table references the primary key of which other table. Here, I tell customMerge that the value in bar.foo_id is not a literal value to be written into the database, but that it references the record added to foo. The id value that the database has generated for the record T.foo[1] will be inserted into bar.foo_id here.

One caveat is that the ordering of the tables in mydatabase.vmd is important. Be sure to add first the referenced table (foo), then the tables referencing it (bar). If you have circular references, phew, tough luck. Oh, surely there are other limitations, too. E.g. we do not need “update” statements, so they probably won’t work. But this version was good enough for us.

Attachments:

You must be logged in to view attached files.