• Introduction
• Task
• Implementation
• Example code
• Create a VMD with spaces
• Create a DBS file to match a DB
• How it works
• More information

Introduction

The original custom_merge.lua module does not support spaces in table or field names. One of our customers modified the code to support spaces when using Microsoft SQL Server. This change will only work with SQL Server and SQLite.

This page explains how this changes works and how you can modify the code to work with other databases.

If you have any questions please contact us at support@interfaceware.com.

Task [top]

Modify the custom_merge.lua module to work for table and field names that include spaces.

Implementation [top]

Follow these steps to use the module:

1. Create a module for the code:
   1. Use a local module custom_merge if you are using the code in a single channel
      Note: The local custom_merge module takes precedence over a shared custom_merge module.
   2. If you are using the module in multiple channels use a shared module name like custom_merge_sql.

      We recommend naming the customized module after the chosen database. This means you can safely use multiple versions, like the original custom_merge, custom_merge_sql, custom_merge_oracle, etc.

2. Copy the code into the module.
3. Uncomment the correct delimiter at the top of the module:
   1. The delimiters are initially set to '' (blank) so the module behaves identically to the original custom_merge module.
   2. You will need to choose the desired delimiter for your chosen database
   3. For example here we have chosen the delimiter for SQL Server:
      
4. Add require 'custom_merge' at the top of your script.
5. Call the customMerge() method anywhere you would call the regular merge, either on database connection objects as conn:customMerge() or by itself as db.customMerge().
   • You will need to create a VMD file with spaces in table and field names that corresponds to your database structure.

This is the code for the modified module — the delimiters are initially set to '' (blank).

This is the github code for the main module used for testing:

Example code [top]

You can use a VMD file or a DBS file to specify your database structure — we show code examples for both. If you use a VMD file you will need to modify it in a text editor (to add spaces). However you can easily generate a DBS file that includes spaces — so we strongly recommend using a DBS file rather than a VMD.

1. Import the HL7 to Database repository code.
2. Modify the MapPatient() function to match your database structure.

   In the example we created a MapPatientSpace() function so we could keep the original function for testing. In a real system you would probably just modify the original function.

3. This is how the code looks when using a DBS file:
   
4. This is how the code looks when using a VMD file:
   

Create a VMD with spaces [top]

You cannot create a VMD with spaces in table or field names Chameleon as it “helpfully” converts spaces to underscores — this means you will need to modify the VMD in a text editor to include spaces.

Follow these to steps to create a VMD with spaces in table or field names:

1. Use Chameleon to create your VMD:
   • Enter your field and table names with spaces — they will be changed underscores.
2. Make a copy of the VMD file you just created.
3. Open the copy of the VMD in a text editor and replace the underscores with spaces:
   • We recommend to a search on the names and/or underscores first as a sanity check.
   • If the modified file does not work initially you can make another copy and try again.
4. If you are still having problems please contact us at support@interfaceware.com.

Create a DBS file to match a DB [top]

Use the Import Database Schema repository code to create a DBS file to match your database structure.

If you have problems with this please contact us at support@interfaceware.com.

How it works [top]

The code simply wraps table and field names in the quoted identifiers delimiters that are correspond to the database being used.

The quoted identifier delimiters for the four database we tested against are:

1. SQL Server: Uses square brackets, like this:  [my table] and [my field]
2. Oracle: Uses double quotes, like this:  “my table” and “my field”
3. MySQL: Uses back-quotes/back-ticks, like this:  `my table` and `my field`
4. SQLite: Works with all of the above delimiters

   Note: You can also work with other databases by using the specified quoted identifier delimiters.

   But be aware that we did not test the code against other databases — so you will need to test thoroughly before using it in a live system.

To choose your desired database simply uncomment the correct delimiter at the top of the module, for example here we have chosen the delimiter for SQL Server:

The changes are actually quite simple and only affect three functions:

1. Table names needed to be delimited in the the genericMergeSQL() function:
   
2. Field names needed to be delimited in the the addRowInsert() and addRowInsert() functions:
   

More information [top]

• The original custom_merge.lua module
• A customizable Lua database merge method
• Forum thread with foreign key support added by a customer