This topic contains 5 replies, has 3 voices, and was last updated by  Julian Muir 4 years, 5 months ago.

custom_merge.lua – customizable Lua module for merging rows into a database

  • If you’re using Iguana 5.5 or above and ever need to update rows in a database table, you should take a look at our new customMerge() Lua method. It replicates the functionality of the built-in db.merge() method, but with some extra options and with reasonably transparent operation that allows modification to suit your needs.

    More info can be found on our wiki page for customMerge. The method is implemented in custom_merge.lua, which is available from our code server.

    Let us know your feedback or if you have any questions!

    Hi, I thought I’d share an improvement I did for custom_merge.lua (SQL Server only). We had the problem that it was awkward to find out the IDs of new database records created by customMerge.
    This new version will return a table of all created ID values, along with the names of the tables in which the new records were inserted. It is done by calling “SCOPE_IDENTITY()” after each insert statement and collecting the results in a variable.

    I use this to get the IDs and fill foreign key fields of dependent tables in the next merge actions.

    It would be really neat to be able to somehow transfer the IDs to the foreign key fields within one merge, so we wouldn’t need several merge actions. Maybe I’ll think of something here.

    But in the meantime, in case anybody is interested, here is my patched version of custom_merge.lua. If you have comments, proposals or bugs, be sure to let me know!

    [Edited to move the code into a file attachment]

    Attachments:
    You must be logged in to view attached files.

    Oookay, I did something, but I’m not quite happy with it. Still, I figure I’ll share it here, in case somebody has an idea to make it actually useful.

    Let’s recap the problem: Say I have two tables in my database:

    CREATE TABLE foo( id INTEGER IDENTITY(1,1),
    foovalue VARCHAR(255),
    PRIMARY KEY(id) );
    CREATE TABLE bar( id INTEGER IDENTITY(1,1),
    barvalue VARCHAR(255),
    foo_id INTEGER,
    PRIMARY KEY(id),
    FOREIGN KEY (foo_id) REFERENCES foo(id));

    I’d like to use customMerge to add one record in foo and one record in bar, referencing the foo record.

    In Lua, I’d write something like:

    local T = db.tables({vmd='mydatabase.vmd', name='Message'})
    local fooRec = T.foo[1]
    fooRec:remove('id') -- is set automatically by the database
    fooRec.foovalue = 'some foo value'
    local barRec = T.bar[1]
    barRec:remove('id') -- is set automatically by the database
    barRec.barvalue = 'some bar value'
    barRec.foo_id = ???????
    dbConnection:customMerge({data=T})

    OK, how can we reference in barRec the id of the fooRec we are creating?

    With the version of custom_merge I have posted above, I can first merge the foo record, get the id from the result and create a new db.tables() for the bar. Like this, we have two merges and no atomic transaction. (OK, we could begin a transaction around the whole thing etc., but we don’t really want to litter this code with database internals, do we?)

    So, here is what I have tried. I have changed batchGetNewIds in custom_merge.lua like this:


    local function batchGetNewIds(Statements)
    local numberOfInsert = 1
    local i = 1
    while i <= #Statements do -- no "for" loop, because #Statements may change in the loop.
    local insertmatch = Statements[i]:match('^INSERT INTO ([^%(]+)%(')
    if insertmatch then
    table.insert(Statements, i+1, 'DECLARE @id'..numberOfInsert..' INT; SELECT @id'..numberOfInsert..' = SCOPE_IDENTITY(); INSERT @listOfIDs(id, tablename) values(SCOPE_IDENTITY(), ' .. insertmatch:qt() .. ')')
    i = i + 1
    numberOfInsert = numberOfInsert + 1
    end
    i = i + 1
    end

    return 'BEGIN\ndeclare @listOfIDs table (id int, tablename varchar(100));\n' .. table.concat(Statements, ';\n') .. ';\nSELECT * from @listOfIDs; END;'
    end

    Now, for each INSERT statement in the merge, the generated id is saved into a local variable @id1, @id2, etc. We could reference these local variables in the following statements. But how do we transport these references from Lua to SQL? We’d like to write:

    barRec.foo_id = '@id1'

    So, foo_id is filled with the id generated in the first INSERT statement.
    But this does not work. foo_id is an integer and does not accept string values.

    The only solution I’ve come up with is to use negative numbers for foreign key references. Thus, I write:

    barRec.foo_id = -1

    In custom_merge, this negative value must be translated into the variable. In the function addSQLValue, I’ve added these lines:

    elseif curType == 'integer' and string.find(Node:nodeName(), '_id') and tonumber(tostring(Node)) < 0 then
    local requestedId = -tonumber(tostring(Node))
    Buffer:write('@id' .. tostring(requestedId))

    So, if the column is an integer, whose name contains ‘_id’ (so it is a foreign key column), and the value is negative, write the requested id variable to the buffer.

    Sounds great? But: What if the ordering of the insert statements os wrong? Could it be possible that the ‘bar’ record is written before the ‘foo’ record? Because ‘bar’ is alphabetically before ‘foo’? And what if there are even more tables dependent on each other, and it is really difficult to find out the correct ordering? And if the ordering of the insert statements is changed automatically, how can we know which number the foo insert will have?

    The problem is mostly that we can only transport integer values in the table record. If we could transport strings, we could code the table name or something.

    But like this, I really feel that I’m teasing the bear (looking for trouble) with this fiddly solution.

    Does anybody have an idea how to solve this reliably? I can only think of adding more parameters to customMerge, to tell which column references which on another table, and to give the ordering of the tables to be inserted, but this might open a can of worms.

    Perhaps the Lua tonumber() function will help?

     barRec.foo_id = '@id1'
     barRec.foo_id = tonumber(@id) -- change to this
    

    Hi Julian,
    if only it were that simple! ‘@id1’ is not available in the Lua code, it is only in the SQL code generated deep within the bowels of customMerge.
    I’ve pondered this this night, and I think I must really add one or two new parameters to customMerge in order to achieve what I want.
    If I achieve something, maybe I’ll post it here if there is interest.

    I suspected as much, but it was worth mentioning just in case.

    I think it is worth posting as inserting records into multiple tables and using the Id (i.e., add a new patient then diagnosis, procedure, kin etc…) is a fairly common requirement. Cascading keys through several tables perhaps a bit less so.

    However there is also a case for just making a new function that uses your updated custom_merge and wraps two (or more) merges in that function. Basically using the logic you suggest:

    With the version of custom_merge I have posted above, I can first merge the foo record, get the id from the result and create a new db.tables() for the bar. Like this, we have two merges and no atomic transaction. (OK, we could begin a transaction around the whole thing etc., but we don’t really want to litter this code with database internals, do we?)

    Using a wrapper function means you don’t have to make fiddly changes to custom_merge. The downside is that you have will have two do two merge calls which means more database overhead, and performance will suffer a little.

    As far as keeping the atomic transaction, hmmm. If you wrap two merge call in a transaction then you could be using nested transactions and there are potential issues: MySQL apparently doesn’t support them, MSSQL does (but treats it as a single transaction), Oracle however allows you to commit or rollback a nested transaction independently of the parent (so three different behaviours). So you should probably prevent the custom_merge from making a transaction in this case.

    NOTE: IMHO Transactions are often used when they are not needed, for example adding Patient + stuff probably does not need a transaction (you always want to add the patient). But if you are doing a SalesInvoice + InvoiceLine then a transaction is usually appropriate (though an empty invoice might be valid depending on workflow/business rules). So maybe you don’t need a transaction?

    Perhaps it is just easier to write custom code using conn:execute{} and keep the merge for simple stuff that doesn’t need to find/use keys. I have a feeling that most customers end up doing this sort of stuff with conn:execute{}.

The topic ‘custom_merge.lua – customizable Lua module for merging rows into a database’ is closed to new replies.