Mike's Mirth Utils

ChannelUtils.js

/**
 * ChannelUtils
 * @param config: {name, user, password, url, dbClass, cacheConnection, cacheName}
 * @param config.name: a db connection name must be unique or will cause issues
 * @param config.user: user name to use for db connection
 * @param config.password: db user password
 * @param config.url: db url
 * @param config.dbClass: db class - Default: org.postgresql.Driver
 * @param config.cacheConnection: Should cache the connection? true/false
 * @param config.cacheName: default - cachedConnection:ChannelUtils:channelName:config.name
 * @constructor
 */
function ChannelUtils(config) {
  config = config || $cfg('mirthDB')
  DBConnection.call(this, config)
}

ChannelUtils.prototype = Object.create(DBConnection.prototype)

// $tryElse inherited from DBConnection

ChannelUtils.prototype.getDBID = function (cid) {
  cid = cid || channelId
  const resultSet = this.executeDBStatement('SELECT local_channel_id from d_channels where channel_id = ?;', true, [cid])
  if (resultSet.next()) return Number.parseInt(JSON.parse(resultSet.getString(1)))
  throw new Error(channelId + ":" + channelId + ": " + "Failed to get DB ID for channelId: " + cid)
}

ChannelUtils.prototype.createMetaDataIndex = function (metadata) {
  const columnName = metadata.toUpperCase()
  const tableName = 'd_mcm' + this.getDBID()
  const sqlStmt = 'CREATE INDEX CONCURRENTLY IF NOT EXISTS ' + 'idx_' + tableName + '_' + columnName + ' ON ' + tableName + ' ("' + columnName + '");'
  this.executeDBStatement(sqlStmt, false)
}

ChannelUtils.prototype.getMessageByMetadata = function (key, value, cid) {
  const dbID = this.getDBID(cid)
  if (!dbID) {
    this.throwError('getMessageByMetadata()', 'No dbID found for channel ID: ' + cid)
  }
  const sql = [
    'select * from d_mc## right join d_mcm## on d_mcm##.message_id = d_mc##.message_id and d_mcm##.metadata_id = d_mc##.metadata_id where "',
    key.toUpperCase(),
    '" = ?::varchar and content_type = 1'
  ].join('').replace(/##/g, dbID)
  const sqlStmnt = this.sqlRowsAsJSON(sql)
  // value is explicitly converted to a string for mirth 3.7.0 to fix:
  // org.postgresql.util.PSQLException: Can't infer the SQL type to use for an instance of org.mozilla.javascript.NativeString
  const resultSet = this.executeDBStatement(sqlStmnt || sql, true, [String(value)])
  if (resultSet.next()) {
    return sqlStmnt ? JSON.parse(resultSet.getString(1)) : resultSet
  }
  return sqlStmnt ? [] : resultSet
}

ChannelUtils._updateIndex = function (name, cid) {
  const globalIndex = globalMap.get('ChannelUtilsIndex') || {}
  globalIndex[name] = cid
  globalMap.put('ChannelUtilsIndex', globalIndex)
}

ChannelUtils.setMessageIndex = function (key, value, name, dbConfig) {
  const channelUtils = new ChannelUtils(String($cfg(dbConfig)))
  channelUtils.createMetaDataIndex(key)
  channelMap.put(key, value)
  ChannelUtils._updateIndex(name, channelId)
}

ChannelUtils.getMessageByIndex = function (key, value, name, dbConfig, options) {
  options = options || {sort: true}
  const channelUtils = new ChannelUtils(String($cfg(dbConfig)))
  const globalIndex = globalMap.get('ChannelUtilsIndex')
  const cid = globalIndex[name]
  var result = channelUtils.getMessageByMetadata(key, value, cid) || []
  if (options.sort) {
    result = result.sort((a, b) => a.message_id > b.message_id)
  }
  if (options.parseXml) {
    result = result.map(order => new XML(SerializerFactory.getSerializer('HL7V2').toXML(order.content)))
    if (options.filter) {
      result = result.filter(order => options.filter.indexOf(order['ORC']['ORC.1']['ORC.1.1'].toString()) > -1)
    }
  }
  return result
}

/**
 * Gets messages from channel with {channelID} by metadata column {key} with value of {value}
 * @param {string} key metadata column
 * @param {string} value metadata value
 * @param {string} channelID
 * @param {string} dbConfig $cfg map key for db config
 * @param {boolean} [parseXml=false] should parse to XML?
 * @param {boolean} [sort=true] should sort by message id?
 * @param {[string]} [filter] should filter on ORC.1.1 example ['XO', 'NW', 'SC']
 * @return {[*]}
 */
ChannelUtils.getMessageByIndexV2 = function ({key, value, channelID, dbConfig, parseXml, sort, filter}) {
  const channelUtils = new ChannelUtils(String($cfg(dbConfig)))
  var result = channelUtils.getMessageByMetadata(key, value, channelID) || []
  if (sort) {
    result = result.sort((a, b) => a.message_id > b.message_id)
  }
  if (parseXml) {
    result = result.map(order => new XML(SerializerFactory.getSerializer('HL7V2').toXML(order.content)))
    if (Array.isArray(filter)) {
      result = result.filter(order => filter.indexOf(order['ORC']['ORC.1']['ORC.1.1'].toString()) > -1)
    }
  }
  return result
}

/* global check, $cfg, SerializerFactory, XML, globalMap, channelMap, channelId, DBConnection*/

DBConnection.js

/**
 * DBConnection is a base class designed to be extended and provide basic DB functionality in a reusable fashion.
 *
 * Author: Michael Lee Hobbs
 * LICENSE: GNU GENERAL PUBLIC LICENSE - https://www.gnu.org/licenses/gpl-3.0.html
 * History:
 * - 20201226 - Initial Release
 * - 20210406 - PACMANO Release, the Less is MORE edition! Line count cut by %40! Simplification of code.
 *
 * example config:
 * {"name": "mirthdb", "user": "mirthdb", "password": "mirthdb", "url": "jdbc:postgresql://postgres:5432/mirthdb", "dbClass": "org.postgresql.Driver", "cacheConnection": true}
 *
 * @param config: {name, user, password, url, dbClass, cacheConnection, cacheName}
 * @param config.name: a db connection name must be unique or will cause issues
 * @param config.user: user name to use for db connection
 * @param config.password: db user password
 * @param config.url: db url
 * @param config.dbClass: db class - Default: org.postgresql.Driver
 * @param config.cacheConnection: Should cache the connection? true/false
 * @param config.cacheName: optional for cross channel caching - defaults to cachedConnection:className:channelName:config.name
 * @constructor
 */
function DBConnection(config) {
    if (!config) this.throwError(null, 'config is undefined!')
    this._config = (typeof config === 'string') ? JSON.parse(config) : JSON.parse(JSON.stringify(config));
    ['name', 'url', 'user', 'password'].forEach(key => !this._config[key] && this.throwError(null, 'config.' + key + ' is undefined!'))
    this._config.cacheName = this._config.cacheName || ['cachedConnection', channelName, this._config.name].join(':')
}

// utility functions
const $tryElse = (cb, elseCB) => {
    try {
        return cb()
    } catch (e) {
        return elseCB(e)
    }
}
const $sleep = (ms) => java.lang.Thread.sleep(ms)

/**
 * Closes the DB connection
 */
DBConnection.prototype.closeConnection = () => $tryElse(() => globalMap.get(this._config.cacheName).close(), () => '')

/**
 * Executes a SQL statement
 * @param statement
 * @param isQuery
 * @param paramList - Java ArrayList or JS Array
 * @return {*|undefined} - results or undefined
 */
DBConnection.prototype.executeDBStatement = function (statement, isQuery, paramList, _retry) {
    statement = String(statement)
    _retry = (_retry || 0) + 1   // recursive call
    const dbConnection = this._getConnection()
    const arrList = Array.isArray(paramList) ? paramList : []
    if (paramList instanceof Packages.java.util.ArrayList) {
        const paramListIterator = paramList.iterator()
        while (paramListIterator.hasNext()) arrList.push('' + paramListIterator.next())
    }

    try {
        return (isQuery) ? dbConnection.executeCachedQuery(statement, paramList || arrList) : dbConnection.executeUpdate(statement, paramList || arrList)
    } catch (e) {
        const errorCheck = ['I/O error', 'This connection has been closed.', 'FATAL: sorry, too many clients already', 'FATAL: terminating connection due to administrator command'] // temporary issues // 20211005 pcoyne ADD AWS timeout
        if (_retry < 10 && errorCheck.some(check => e.message.indexOf(check) > -1)) {
            $sleep(_retry * 100)
            return this.executeDBStatement(statement, isQuery, paramList, _retry)
        }
        const debugStatement = arrList.reduce((acc, cur) => acc.replace('?', "'" + cur + "'"), statement)
        throw this.errorPrefix('executeDBStatement', 'statement: ' + debugStatement + ', isQuery: ' + isQuery + ' on "' + this._config.name + '"', e)
    } finally {
        if (!this._config.cacheConnection) this.closeConnection()
    }
}

/**
 * Executes multiple request in order
 * @param paramsArr [statement, isQuery, paramList] - See executeDBStatement
 * @return [{*|undefined}]
 */
DBConnection.prototype.executeDBStatements = (paramsArr) => paramsArr.map(([statement, isQuery, paramList]) => this.executeDBStatement(statement, isQuery, paramList))

DBConnection.prototype.errorPrefix = function (func, msg, error) {
    (error = error || new Error()).message = channelName + ': ' + this.constructor.name + (func && '.' + func || '') + (msg && ' - ' + msg || '') + '\n' + error.message //+ '\n' + error.stack
    return error
}

/**
 * Throw or log and error adding additional information
 * @param func - optional - Name of function that is throwing
 * @param msg - optional - Additional message, example could be additional error information
 * @param error - original error object
 */
DBConnection.prototype.throwError = function (func, msg, error) {
    (error = error || new Error()).message = channelName + ': ' + this.constructor.name + (func && '.' + func || '') + (msg && ' - ' + msg || '') + '\n' + error.message + '\n' + error.stack
    throw error
}

DBConnection.prototype._getConnection = function (_retry) {
    _retry = (_retry || 0) + 1
    const dbConnection = globalMap.get(this._config.cacheName)
    const result = $tryElse(() => !Boolean(globalMap.get(this._config.cacheName).getConnection().isClosed()), (e) => e)
    if (result === true) return dbConnection
    if (result instanceof Error && _retry > 5) this.throwError('_getConnection()', 'Failed to open a connection!', result)
    $sleep(_retry * 100)
    const {dbClass, url, user, password} = this._config
    try {
        globalMap.put(this._config.cacheName, DatabaseConnectionFactory.createDatabaseConnection(dbClass, url, user, password))
    } catch (e) {
        this.throwError('_getConnection', '', e)
    }
    return this._getConnection(_retry)
}

/**
 * Wraps sql statement in array to json and array agg if wrapper for a given DB is found.
 * @param sql
 * @return {String} sql statement
 */
DBConnection.prototype.sqlRowsAsJSON = function (sql) {
    const wrappers = {
        'org.postgresql.Driver': (sql) => "select array_to_json(array_agg(t)) from (" + sql + ") as t;"
    }
    if (wrappers[this._config.dbClass]) return wrappers[this._config.dbClass](sql)
}

/**
 * Wraps sql statement in array agg if wrapper for a given DB is found.
 * @param sql
 * @return {String} sql statement
 */
DBConnection.prototype.sqlAsJSON = function (sql) {
    const wrappers = {
        'org.postgresql.Driver': (sql) => "select array_agg(t) from (" + sql + ") as t;"
    }
    if (wrappers[this._config.dbClass]) return wrappers[this._config.dbClass](sql)
}

mirthUtils.js

/**
 * Author: Michael Lee Hobbs
 * LICENSE: GNU GENERAL PUBLIC LICENSE - https://www.gnu.org/licenses/gpl-3.0.html
 * History:
 * - 20201221 - Initial Release
 * - 20201226 - Added expect.component, preprocessor's, encoding's
 * - 20201226 - Added response.setResponseStatus, limitMsgObx5Length
 */
const utils = {
    string: {
        removeNonAscii: (str) => String(str || '').replace(/[^\x20-\x7E]/g, ''),
        splitOnSpace: (str, length, noTrim) => {
            var regex = new RegExp("(.{1," + length + "}(\\s|$))\\s*", 'g')
            var matches = String(str).match(regex) || ['']
            return matches.map((ele) => (noTrim) ? ele : ele.trim())
        },
        clean: (str, defaultValue) => String(str || defaultValue || '').replace(/undefine|null/g, ''),
        cleanAllObjectKeys: (obj) => Object.keys(obj).forEach(k => obj[k] = utils.string.clean(obj[k])),
    },
    date: {
        toHL7DT: (date, includeTZ) => {
            const _date = (date instanceof Date) ? date.toISOString() : String(date)
            var tz = ''
            if (includeTZ) tz = '-0000'
            return _date.split('.')[0].replace(/[\s-T:]/g, '') + tz
        },
        toHL7Date: (date) => {
            const _date = (date instanceof Date) ? date.toISOString() : String(date)
            return _date.split('T')[0].replace(/[\s-T:]/g, '')
        },
        getAge: (date) => {
            date = (typeof date === 'string') ? new Date(date) : date
            const year = (new Date()).getFullYear()
            return year - date.getFullYear()
        },
        parseHL7DT: (dateString) => {
            const year = dateString.substring(0, 4)
            const month = dateString.substring(4, 6)
            const day = dateString.substring(6, 8)
            const hour = dateString.substring(8, 10) || '00'
            const min = dateString.substring(10, 12) || '00'
            const sec = dateString.substring(12, 14) || '00'
            var tz = '-0000'
            if (dateString.indexOf(/[-+]/)) tz = dateString.slice(-5) // has tz
            var dtStr = year + '-' + month + '-' + day + 'T' + hour + ':' + min + ':' + sec + tz
            return new Date(dtStr)
        },
    },
    handlers: {
        runIt: (fn, args) => (typeof fn === 'function') ? fn(args) : fn,
        tryIt: (fn, defaultValue) => {
            var result = undefined
            try {
                if (Array.isArray(fn)) fn.some(func => (result = utils.handlers.tryIt(func)) !== undefined)
                else result = utils.handlers.runIt(fn)
                return result !== undefined ? result : utils.handlers.runIt(defaultValue)
            } catch (e) {
                return utils.handlers.runIt(defaultValue, e)
            }
        }
    },
    array: {
        limitElementLength: (arr, length) => {
            var out = []
            arr.forEach((line) => {
                var _line = line.trim()
                if (_line.length > length) utils.string.splitOnSpace(_line, length).forEach(l => out.push(l))
                else out.push(_line)
            })
            return out
        }
    },
    xml: {
        encoding: {
            field: {value: '|', escaped: '\\F\\'},
            component: {value: '^', escaped: '\\S\\'},
            fieldRepetition: {value: '~', escaped: '\\R\\'},
            escape: {value: '\\', escaped: '\\E\\'},
            subcomponent: {value: '&', escaped: '\\T\\'},
            // segment: {value: '\r' escaped: '\\Cxxyy\\'},
        },
        unescapeEncodingChars: (str, encoding) => {
            const {field, component, fieldRepetition, escape, subcomponent} = encoding || utils.xml.encoding
            const getRegex = (enc) => new RegExp('\\' + enc.escaped + '\\', 'g')
            const decode = (str, code) => str.replace(getRegex(code), code.value)
            var out = decode(str, field)
            out = decode(out, field)
            out = decode(out, component)
            out = decode(out, fieldRepetition)
            out = decode(out, subcomponent)
            return decode(out, escape)
        },
        escapeEncodingChars: (str, encoding) => {
            const {field, component, fieldRepetition, escape, subcomponent} = encoding || utils.xml.encoding
            var out = utils.xml.unescapeEncodingChars(str)
            return out.split('').map(char => {
                if (char === field.value) return field.escaped
                if (char === component.value) return component.escaped
                if (char === fieldRepetition.value) return fieldRepetition.escaped
                if (char === escape.value) return escape.escaped
                if (char === subcomponent.value) return subcomponent.escaped
                return char
            }).join('')
        },
        writePerson: (seg, data, hasId) => {
            if (!seg) throw new Error('seg cannot be undefined!')
            const {id, family, given, middle, suffix, prefix, degree} = data
            const base = seg.name().toString() + '.'
            var index = 1
            if (hasId) seg[base + index++] = id || ''
            seg[base + index++] = family || ''
            seg[base + index++] = given || ''
            seg[base + index++] = middle || ''
            seg[base + index++] = suffix || ''
            seg[base + index++] = prefix || ''
            seg[base + index++] = degree || ''
        },
        expect: {
            component: (xmlObj, options) => {
                options.encoding = options.encoding || utils.xml.encoding
                const {encoding, suppressError, shouldEscape} = options
                try {
                    if (xmlObj.hasSimpleContent()) return xmlObj.toString()
                    // this means we have subcomponents
                    const segName = xmlObj.name().toString()
                    const json = JSON.parse(XmlUtil.toJson(xmlObj.toString()))[segName]
                    const out = Object.keys(json).reduce((acc, key) => acc.push(json[key]) && acc, []).join(encoding.subcomponent.value)
                    if (!shouldEscape) return out
                    return utils.xml.escapeEncodingChars(out, encoding)
                } catch (e) {
                    if (!suppressError) throw e
                    logger.error(channelName + ': Error! ' + e)
                }
            }
        },
        preprocessor: {
            /**
             * Executes each transformer on each seg of a raw HL7 message
             * @param message - raw HL7 message
             * @param transformers - array of functions that will be executed in order with each being passed the result of the previous function with the first being passed the original seg
             * @param segmentDeliminator - optional default \r
             * @return {string}
             */
            process: (message, transformers, segmentDeliminator) => {
                segmentDeliminator = segmentDeliminator || '\r'
                return String(message)
                    .split(segmentDeliminator)
                    .map(seg => transformers.reduce((acc, transformer) => transformer(acc) || acc, seg) || seg)
                    .join(segmentDeliminator)
            },
            transformers: {
                nonRepeatingField: (filter, fields, fieldDeliminator, fieldRepetition) => {
                    fieldDeliminator = fieldDeliminator || '|'
                    fieldRepetition = fieldRepetition || '~'
                    return (seg) => {
                        if (seg.startsWith(filter)) {
                            return seg.split(fieldDeliminator)
                                .map((ele, i) => fields.includes(i) ? ele.replace(new RegExp(fieldRepetition, 'g'), '\\R\\') : ele)
                                .join(fieldDeliminator)
                        }
                    }
                }
            }
        },
        postprocessor: {
            limitMsgObx5Length: (msg, length) => {
                var newMsg = <HL7Message/>;
                for each(seg in msg.children()) {
                    var segName = String(seg.name())
                    if (segName === 'MSH') newMsg.MSH = msg.MSH.copy()
                    else if (segName === 'OBX') {
                        utils.string.splitOnSpace(seg['OBX.5']['OBX.5.1'].toString(), length).forEach(line => {
                            var obx = seg.copy()
                            obx['OBX.5']['OBX.5.1'] = line
                            newMsg.appendChild(obx)
                        })
                    } else {
                        newMsg.appendChild(seg.copy())
                    }
                }
                for (var i = 1; i <= newMsg.elements('OBX').length(); i++) {
                    newMsg.OBX[i - 1]['OBX.1']['OBX.1.1'] = i
                }
                return newMsg
            }
        }
    },
    response: {
        setResponseStatus: (msg, statusMap, maxAttempts) => {
            // called outside a response transformer
            if (!responseStatus) {
                logger.warn(channelName + ': called utils.response.setResponseStatus outside of a response transformer!')
                return
            }
            const status = String(msg['ERR']['ERR.4']['ERR.4.2'])
            const attempt = Number.parseInt(connectorMessage.getSendAttempts()) || 0
            if (maxAttempts && maxAttempts > attempt) return
            statusMap.some((map) => {
                if (map.match === status) {
                    responseStatus = map.status
                    return true
                }
            })
        }
    }
}


if (typeof module !== 'undefined') module.exports = utils

PersistentMap.js

/**
 * PersistentMap extends DBConnection and is a DB backed persistent map and can be accessed from anywhere/anytime unlike most Mirth Connect maps.
 *
 * Author: Michael Lee Hobbs
 * LICENSE: GNU GENERAL PUBLIC LICENSE - https://www.gnu.org/licenses/gpl-3.0.html
 * History:
 * - 20201226 - Initial Release
 *
 * example config:
 * {
 *   "mapName": "john_doe_memorial_persistent_map",
 *   "name": "mirthdb",
 *   "user": "mirthdb",
 *   "password": "mirthdb",
 *   "url": "jdbc:postgresql://postgres:5432/mirthdb",
 *   "dbClass": "org.postgresql.Driver",
 *   "cacheConnection": true,
 *   "expires": 15
 * }
 *
 * Usage Example:
 * ### Start of Example ###
 * # Deploy Script
 * var persistentMap = new PersistentMap(JSON.parse($cfg('joe_doe_memorial_persistent_map')))
 * persistentMap.initialize()
 * return;
 *
 * # Transformer Script Source:
 * var $p = new PersistentMap(JSON.parse($cfg('john_doe_memorial_persistent_map')))
 * var type = msg['V_ALLDATA']['V_OBSERVATIONREPORT']['DOCUMENT_FILEKIND']
 * var id = msg['V_ALLDATA']['V_CUSTOM']['RECORDID']
 * $p.set(id  + ':' + type, msg['V_ALLDATA']['V_OBSERVATIONREPORT']['BUFFERCONTENT'])
 *
 * const txt = $p.get(id  + ':TXT').value
 * const pdf = $p.get(id  + ':PDF').value
 *
 * if (!txt || !pdf) return destinationSet.removeAll()
 * ### End of Example ###
 *
 * @param config: {mapName, name, user, password, url, dbClass, cacheConnection, expires, initialize}
 * @param config.mapName: mapName name
 * @param config.name: a db connection name must be unique or will cause issues
 * @param config.user: user name to use for db connection
 * @param config.password: db user password
 * @param config.url: db url
 * @param config.dbClass: db class
 * @param config.cacheConnection: Should cache the connection? true/false
 * @param config.expires: optional integer, defaults to 30, number of days entry will expire in and be deleted after last update - this can be set per channel
 * @param config.initialize: optional boolean, if true will call PersistentMap.initialize()
 * @constructor
 */
function PersistentMap(config) {
    DBConnection.call(this, config)
    this._expires = Number.parseInt(config.expires) || 30
    if (!config.mapName) throw new Error(this.getErrorPrefix('constructor(config)') + ' - config.mapName is undefined!')
    this._mapName = config.mapName
    if (config.initialize) this.initialize()
    this.prune()
}

PersistentMap.prototype = Object.create(DBConnection.prototype)

/**
 * Initializes the DB with table for a PersistentMap. This is called by the constructor if config.initialize === true
 */
PersistentMap.prototype.initialize = function () {
    this.executeDBStatement([
            "CREATE TABLE IF NOT EXISTS " + this._mapName,
            "(",
            "  id SERIAL PRIMARY KEY,",
            "  key varchar(64) not null,",
            "  value text,",
            "  created_at timestamp with time zone DEFAULT now(),",
            "  updated_at timestamp with time zone DEFAULT now(),",
            "  expires_at timestamp with time zone,",
            "  CONSTRAINT " + this._mapName + "_key_unique UNIQUE(key)",
            ");",
        ].join('\n'),
        false,
        null
    )
}

/**
 * Drops table/indexes for PersistentMap. To execute a drop you must call the drop function like so: PersistentMap.drop().areYouSure('YES')
 * @returns {{areYouSure: areYouSure}}
 */
PersistentMap.prototype.drop = function () {
    return {
        areYouSure: function (answer) {
            if (answer !== 'YES') throw new Error(this.getErrorPrefix('drop().areYouSure(answer)') + ' - expected answer === "YES" found "' + answer + '"')
            this.executeDBStatement("DROP TABLE IF EXISTS " + this._mapName + ";", false)
        }.bind(this)
    }
}

/**
 * Returns a key, value pair
 * @param key - the key/value pair to return
 * @returns {object|undefined} {key, value} or undefined if key does not exist
 */
PersistentMap.prototype.get = function (key) {
    const sqlStmnt = 'select row_to_json(t) from (select key, value from ' + this._mapName + ' where key = ?) as t;'
    const resultSet = this.executeDBStatement(sqlStmnt, true, [key])
    return (resultSet.next()) ? JSON.parse(resultSet.getString(1)) : {}
}

/**
 * Returns an array of key, value pairs where value ilike %query%
 * Can be slow once map grows to a large size or does not have a reasonable expires
 * @param query - text to search for in values
 * @returns {Array}
 */
PersistentMap.prototype.search = function (query) {
    const sqlStmnt = "select json_agg(t) from (select key, value from " + this._mapName + " where value ilike '%" + query + "%') as t;"
    const resultSet = this.executeDBStatement(sqlStmnt, true)
    return (resultSet.next()) ? JSON.parse(resultSet.getString(1)) : []
}

/**
 * Sets a key to value and will overwrite an existing key/value pair
 * @param key - the key to set
 * @param value - the value to set the key to
 * @param expires_at - optional ISO timestamp, defaults to now + PersistentMap.expires days
 * @returns {number} the id of the inserted value or -1 if there insert/update failure
 */
PersistentMap.prototype.set = function (key, value, expires_at) {
    const updated_at = (new Date()).toISOString()
    if (!expires_at) {
        expires_at = new Date()
        expires_at = (new Date(expires_at.setDate(expires_at.getDate() + this._expires))).toISOString()
    }

    const sqlStmnt = [
        "INSERT INTO " + this._mapName + " (key, value, updated_at, expires_at)",
        "VALUES (?, ?, ?::timestamp, ?::timestamp)",
        "ON CONFLICT ON CONSTRAINT " + this._mapName + "_key_unique DO UPDATE",
        "SET value = EXCLUDED.value, updated_at = EXCLUDED.updated_at, expires_at = EXCLUDED.expires_at",
        "returning id;",
    ].join('\n')
    const resultSet = this.executeDBStatement(sqlStmnt, true, [key, value, updated_at, expires_at])
    return (resultSet.next()) ? Number.parseInt(resultSet.getString(1)) : -1
}

/**
 * Deletes a key/value pair
 * @param key - the key to delete
 */
PersistentMap.prototype.delete = function (key) {
    const sqlStmnt = "DELETE FROM " + this._mapName + " WHERE key = ?;"
    this.executeDBStatement(sqlStmnt, true, [key])
}

/**
 * Prunes any key/value pair where the expires_at is less than NOW()
 */
PersistentMap.prototype.prune = function () {
    const sqlStmnt = "DELETE FROM " + this._mapName + " WHERE expires_at is not null and expires_at < NOW();"
    try {
        this.executeDBStatement(sqlStmnt, false)
    } catch (e) {
        // it's possible prune could fail when called from the constructor and initialize has not yet been called
    }
}