SQL Lite makes a great place to sort extra data for Family Historian Plugins, especially if you are already familiar with SQL.

Requires: luasql – see Code Snippet Module Require With Loadif you’re coding for ƒh V5 or 6.

if not(loadrequire('luasql','luasql.sqlite3')) then return end

Code

Taken from the FTP Manager, these functions show the basics for controlling a database.

function opendb(dbname)
    -- Check for Settings Database and create if needed
    local db = fhGetPluginDataFileName()
    local dbenv = assert (luasql.sqlite3())
    -- connect to data source, if the file does not exist it will be created
    dbcon = assert (dbenv:connect(db))
    -- check table for page list
    checkTable(dbcon,'pagelist',
    [[CREATE TABLE pagelist(filename varchar(500), md5hash varchar(32),UNIQUE (filename))
    ]])
    -- create table for settings
    checkTable(dbcon,'settings',
    [[CREATE TABLE settings(key varchar(20), directory varchar(500), 
               host varchar(500), folder varchar(50), userid varchar(50), password varchar(50), UNIQUE (key))
    ]])
    return dbenv,dbcon
end
function checkTable(dbcon,table,createString)
    local sql = string.format([[SELECT count(name) as count FROM sqlite_master WHERE type='table' AND name='%s']],table)
    local cur = assert(dbcon:execute(sql))
    local rowcount = cur:fetch (row, "a")
    cur:close()
    if tonumber(rowcount) == 0 then
        -- Table not found create it
        res,err = assert(dbcon:execute(createString))
    end
end
function closedb(dbenv,dbcon)
    dbcon:close()
    dbenv:close()
end
function loadSettings(dbcon)
    local sql = [[SELECT * FROM settings]]
    local cur,err = assert(dbcon:execute(sql))
    local row = cur:fetch({},'a')
    cur:close()
    if row then
        return row
    else
        -- return default values
        return {
            directory = fhGetContextInfo('CI_PROJECT_PUBLIC_FOLDER')..'\\FH Website',
            host = 'websitehost',
            folder = '/',
            userid = 'user',
            password = 'password',
            new = 'yes'
        }
    end
end
function saveSettings(dbcon,settings)
    -- Check for Settings
    if settings.new == 'yes' then
        -- Create
        sql = string.format([[insert into settings (directory, host, folder, userid, password) Values('%s','%s','%s','%s','%s')]],settings.directory,settings.host,settings.folder,settings.userid,settings.password)
    else
        -- Update
        sql = string.format([[update settings set directory = '%s', host = '%s',folder = '%s',userid = '%s', password = '%s']],settings.directory,settings.host,settings.folder,settings.userid,settings.password)
    end
    local res = assert(dbcon:execute(sql))
end