Created telephone DID application.

1 files changed, 925 insertions, 0 deletions

diff --git a/did-model.lua b/did-model.lua
new file mode 100644
index 0000000..319a5cc
--- /dev/null
+++ b/did-model.lua
@@ -0,0 +1,925 @@
+module(..., package.seeall)
+
+-- Load libraries
+require("modelfunctions")
+require("format")
+require("validator")
+require("luasql.postgres")
+
+-- NOTE -- This is the SQL statement that should be run by the VoIP server to figure out the current extension for a DID
+-- "SELECT extension FROM pubdid WHERE did='$1' AND 'now'>=starttime AND 'now'<endtime ORDER BY stale LIMIT 1"
+-- To update pubdid each night at midnight, use cron and the didpublish script
+
+local DatabaseName = "did"
+local DatabaseOwner = "didowner"
+local DatabaseUser = "diduser"
+
+local configfile = "/etc/did.conf"
+
+local path = "PATH=/usr/local/bin:/usr/bin:/bin:/usr/local/sbin:/usr/sbin:/sbin "
+local env
+local con
+
+local database_creation_script = {
+	"CREATE AGGREGATE array_accum(anyelement) (SFUNC = array_append, STYPE = anyarray, INITCOND = '{}')",
+	"CREATE TABLE dbhistlog (logdatetime timestamp(0) without time zone NOT NULL, msgtext text, userid text)",
+	"CREATE TABLE definition (did character varying(40) NOT NULL, identification character varying(7), department character varying(40), description character varying(255))",
+	"CREATE TABLE pubdid (did character varying(40), extension character varying(40), starttime time without time zone, endtime time without time zone, stale boolean)",
+	"CREATE TABLE rule (did character varying(40) NOT NULL, extension character varying(40) NOT NULL, starttime time without time zone, endtime time without time zone, startdate date, enddate date, dayofweek bit(7))",
+	"ALTER TABLE ONLY definition ADD CONSTRAINT definition_pkey PRIMARY KEY (did)",
+	"CREATE INDEX ruledididx ON rule USING btree (did)",
+	"CREATE INDEX ruledidextensionidx ON rule USING btree (did, extension)",
+	"CREATE INDEX ruledidtimesdowidx ON rule USING btree (did, starttime, endtime, dayofweek)",
+	"CREATE INDEX pubdiddidtimesidx ON pubdid USING btree (did, starttime, endtime)",
+	"GRANT SELECT, INSERT ON dbhistlog TO "..DatabaseUser,
+	"GRANT SELECT, UPDATE ON definition TO "..DatabaseUser,
+	"GRANT SELECT, UPDATE, INSERT, DELETE ON rule TO "..DatabaseUser,
+}
+
+-- ################################################################################
+-- DATABASE FUNCTIONS
+
+local function assert (v, m)
+	if not v then
+		m = m or "Assertion failed!"
+		error(m, 0)
+	end
+	return v, m
+end
+
+-- Escape special characters in sql statements
+local escape = function(sql)
+	sql = sql or ""
+	return string.gsub(sql, "'", "''")
+end
+
+-- List the postgres databases on this system
+local listdatabases = function()
+	local dbs = {}
+	local cmd = path.."psql -U postgres -tl 2>&1"
+	local f = io.popen(cmd)
+	local result = f:read("*a") or ""
+	f:close()
+	for line in string.gmatch(result, "[^\n]+") do
+		dbs[#dbs+1] = string.match(line, "^ (%S+)")
+	end
+	return dbs
+end
+
+-- Create the necessary database
+local createdatabase = function(password)
+	local result = {}
+
+	-- First, create the users
+	local cmd = path..'psql -U postgres -c "CREATE USER '..DatabaseOwner..' WITH PASSWORD \''..password..'\'" 2>&1'
+	local f = io.popen(cmd)
+	table.insert(result, f:read("*a"))
+	f:close()
+	cmd = path..'psql -U postgres -c "CREATE USER '..DatabaseUser..'" 2>&1'
+	f = io.popen(cmd)
+	table.insert(result, f:read("*a"))
+	f:close()
+	
+	-- Create the database
+	cmd = path..'psql -U postgres -c "CREATE DATABASE '..DatabaseName..' WITH OWNER '..DatabaseOwner..'" 2>&1'
+	f = io.popen(cmd)
+	table.insert(result, f:read("*a"))
+	f:close()
+	
+	return table.concat(result, "\n")
+end
+
+-- Delete the database and roles
+local deletedatabase = function()
+	local result = {}
+
+	local cmd = path..'psql -U postgres -c "DROP DATABASE '..DatabaseName..'" 2>&1'
+	local f = io.popen(cmd)
+	table.insert(result, f:read("*a"))
+	f:close()
+	cmd = path..'psql -U postgres -c "DROP ROLE '..DatabaseUser..'" 2>&1'
+	f = io.popen(cmd)
+	table.insert(result, f:read("*a"))
+	f:close()
+	cmd = path..'psql -U postgres -c "DROP ROLE '..DatabaseOwner..'" 2>&1'
+	f = io.popen(cmd)
+	table.insert(result, f:read("*a"))
+	f:close()
+	
+	return table.concat(result, "\n")
+end
+
+-- Run an SQL script
+local runSQLscript = function(filename)
+	-- Create the database
+	local cmd = path..'psql -U postgres -f "'..filename..'" '..DatabaseName..' 2>&1'
+	local f = io.popen(cmd)
+	local result = f:read("*a") or ""
+	f:close()
+	-- Create the tables
+	print (result)
+	return result
+end
+
+-- Create the database and tables
+-- pg_dump -U postgres -c did > did.postgres
+--runSQLscript("/path/did.postgres")
+
+local databaseconnect = function(username, password)
+	if not con then
+		-- create environment object
+		env = assert (luasql.postgres())
+		-- connect to data source
+		con = assert (env:connect(DatabaseName, username, password))
+		return true
+	end
+end
+
+local databasedisconnect = function()
+	if env then
+		env:close()
+		env = nil
+	end
+	if con then
+		con:close()
+		con = nil
+	end
+end
+
+local logme = function(message)
+	local userid = "-"
+	if APP and APP.sessiondata and APP.sessiondata.userinfo and APP.sessiondata.userinfo.userid then
+		userid = APP.sessiondata.userinfo.userid
+	end
+	local sql = string.format("INSERT INTO dbhistlog VALUES ('%s', '%s', '%s')",
+		os.date("%Y-%m-%d %H:%M:%S"), escape(message), userid)
+	local res = assert (con:execute(sql))
+end
+
+local listhistorylogentries = function()
+	local entries = {}
+	cur = assert (con:execute"SELECT logdatetime, msgtext, userid from dbhistlog")
+	row = cur:fetch ({}, "a")
+	while row do
+		entries[#entries+1] = {logdatetime = row.logdatetime, msgtext = row.msgtext, userid = row.userid}
+		row = cur:fetch (row, "a")
+	end
+	cur:close()
+	return entries
+end
+
+-- Delete history log information from more than a month ago
+local groomdbhistlog = function()
+	local res = assert (con:execute("delete from dbhistlog where " ..
+		"logdatetime < (now() - INTERVAL '1 month')"))
+	logme("removed " .. res .. " old dbhistlog lines")
+end
+
+local generatewhereclause = function(did, extension, identification, description, department, clause)
+	local sql = ""
+	local where = {}
+	-- We're going to use regular expressions so can search for substrings
+	if did and did ~= "" then
+		where[#where+1] = "definition.did ~ '.*"..escape(did)..".*'"
+	end
+	if extension and extension ~= "" then
+		where[#where+1] = "extension ~ '.*"..escape(extension)..".*'"
+	end
+	if identification and identification ~= "" then
+		where[#where+1] = "identification ~ '.*"..escape(identification)..".*'"
+	end
+	-- For these two, specify case insensitive
+	if description and description ~= "" then
+		where[#where+1] = "description ~* '.*"..escape(description)..".*'"
+	end
+	if department and department ~= "" then
+		where[#where+1] = "department ~* '.*"..escape(department)..".*'"
+	end
+	if #where > 0 then
+		sql = " " .. (clause or "WHERE") .. " " .. table.concat(where, " AND ")
+	end
+	return sql
+end
+
+local getdefinitionentries = function(sql)
+	local entries = {}
+	cur = assert (con:execute(sql))
+	row = cur:fetch ({}, "a")
+	while row do
+		entries[#entries+1] = {did=row.did, identification=row.identification, department=row.department, description=row.description, extension=row.extension}
+		row = cur:fetch (row, "a")
+	end
+	-- close everything
+	cur:close()
+	return entries
+end
+
+local listunuseddefinitions = function(did, identification, description, department)
+	local where = generatewhereclause(did, nil, identification, description, department, "AND")
+	local sql = "SELECT * FROM definition WHERE did NOT IN (SELECT did FROM rule)"..where.." ORDER BY did"
+	return getdefinitionentries(sql)
+end
+
+-- Lists only the definitions that have rules, this also allows us to select based upon extension
+local listuseddefinitions = function(did, extension, identification, description, department)
+	local where = string.gsub(generatewhereclause(did, extension, identification, description, department, "HAVING"), "extension", "array_to_string(array_accum(rule.extension), ', ')")
+	-- Combine with rules to get extensions, this will drop all dids that don't have rules
+	-- Relies on this custom aggregate function being defined
+	-- local sql = "CREATE AGGREGATE array_accum(anyelement)(sfunc = array_append, stype = anyarray, initcond = '{}')"
+	local sql = "SELECT definition.did, identification, department, description, array_to_string(array_accum(rule.extension), ', ') AS extension FROM definition,rule WHERE definition.did=rule.did"
+	sql = sql.." GROUP BY definition.did, identification, department, description"..where.." ORDER BY definition.did"
+	return getdefinitionentries(sql)
+end
+
+local listdefinitions = function(did, identification, description, department)
+	local sql = "SELECT * FROM definition"..generatewhereclause(did, nil, identification, description, department).." ORDER BY did"
+	return getdefinitionentries(sql)
+end
+
+local listexchanges = function()
+	local entries = {}
+	local sql = "SELECT substring(did from 1 for 6) AS exchange FROM definition GROUP BY exchange ORDER BY exchange"
+	cur = assert (con:execute(sql))
+	row = cur:fetch ({}, "a")
+	while row do
+		entries[#entries+1] = row.exchange
+		row = cur:fetch (row, "a")
+	end
+	-- close everything
+	cur:close()
+	return entries
+end
+
+local findunuseddefinition = function(exchange)
+	local entries = {}
+	local sql = "SELECT did FROM definition WHERE did NOT IN (SELECT did FROM rule) AND substring(did from 1 for 6)='"..exchange.."' AND identification='' AND department=''"
+	cur = assert (con:execute(sql))
+	row = cur:fetch ({}, "a")
+	while row do
+		entries[#entries+1] = row.did
+		row = cur:fetch (row, "a")
+	end
+	-- close everything
+	cur:close()
+	return entries
+end
+
+local updatedefinitionentry = function(definition)
+	local sql = string.format("UPDATE definition SET identification='%s', department='%s', description='%s' WHERE did='%s'",
+		escape(definition.identification), escape(definition.department),
+		escape(definition.description), escape(definition.did))
+	local res = assert (con:execute(sql))
+	logme("Updated DID "..definition.did)
+	return res
+end
+
+local insertdefinitionentry = function(definition)
+	local sql = string.format("INSERT INTO definition (did, identification, department, description) VALUES ('%s', '%s', '%s', '%s')",
+		escape(definition.did), escape(definition.identification),
+		escape(definition.department), escape(definition.description))
+	local res = assert (con:execute(sql))
+	logme("Inserted DID "..definition.did)
+	return res
+end
+
+local deletedefinitionentry = function(did)
+	local sql = string.format("DELETE FROM definition WHERE did='%s'", escape(did))
+	local res = assert (con:execute(sql))
+	logme("Deleted DID "..did)
+	return res
+end
+
+local listrules = function(did, date, dayofweek)
+	local entries = {}
+	-- retrieve a cursor
+	local sql = "SELECT * from rule"
+	local where = {}
+	if did then
+		where[#where+1] = "did='" .. did .. "'"
+	end
+	if date then
+		where[#where+1] = "(startdate IS NULL OR startdate <= '"..date.."') AND (enddate IS NULL OR enddate >= '"..date.."')"
+	end
+	if dayofweek then
+		where[#where+1] = "(dayofweek = '0000000' OR SUBSTRING(dayofweek FROM "..dayofweek.." FOR 1) = '1')"
+	end
+	if #where > 0 then
+		sql = sql .. " WHERE " .. table.concat(where, " AND ")
+	end
+	-- This ordering controls which rule overrides another, highest priority will be first
+	sql = sql .. " ORDER BY did, startdate ASC NULLS FIRST, enddate DESC NULLS FIRST, dayofweek ASC, starttime ASC NULLS FIRST, endtime DESC NULLS FIRST"
+	cur = assert (con:execute(sql))
+	row = cur:fetch ({}, "a")
+	while row do
+		entries[#entries+1] = {did=row.did, extension=row.extension, starttime=row.starttime, endtime=row.endtime, startdate=row.startdate, enddate=row.enddate, dayofweek=row.dayofweek}
+		row = cur:fetch (row, "a")
+	end
+	-- close everything
+	cur:close()
+	return entries
+end
+
+local updaterules = function(did, rules)
+	-- delete all rules for this did, and add in new ones
+	local sql = string.format("DELETE FROM rule WHERE did='%s'", escape(did))
+	local res = assert (con:execute(sql))
+	for i,rule in ipairs(rules) do
+		sql = {}
+		sql[1] = "INSERT INTO rule ("
+		sql[3] = ") VALUES ("
+		sql[5] = ")"
+		names = {}
+		vals = {}
+		for name,val in pairs(rule) do
+			if val and val ~= "" then
+				names[#names+1] = escape(name)
+				vals[#vals+1] = "'"..escape(val).."'"
+			end
+		end
+		sql[2] = table.concat(names, ", ")
+		sql[4] = table.concat(vals, ", ")
+		sql = table.concat(sql, "")
+		res = assert (con:execute(sql))
+	end
+	logme("Updated "..#rules.." rules for DID "..did)
+	return res
+end
+
+-- Put the given rules into pubdid
+local publishrules = function(did, rules)
+	-- mark all rules for this did as stale, add in new ones, and delete the stale ones
+	local sql = string.format("UPDATE pubdid SET stale=TRUE WHERE did='%s'", escape(did))
+	local res = assert (con:execute(sql))
+	for i,rule in ipairs(rules) do
+		sql = string.format("INSERT INTO pubdid VALUES ('%s', '%s', '%s', '%s', FALSE)", escape(rule.did),
+			escape(rule.extension), escape(rule.starttime), escape(rule.endtime))
+		res = assert (con:execute(sql))
+	end
+	sql = string.format("DELETE FROM pubdid WHERE did='%s' AND stale=TRUE", escape(did))
+	res = assert (con:execute(sql))
+	--logme("Published DID "..did)
+	return res
+end
+
+local testdatabaseentry = function(datatype, value)
+	local success = true
+	local errtxt
+	local sql = "CREATE TEMP TABLE testing ( test "..escape(datatype).." DEFAULT '"..escape(value).."' ) ON COMMIT DROP"
+	local res, err = pcall(function()
+		assert (con:execute(sql))
+	end)
+	if not res then
+		success = false
+		errtxt = string.gsub(err or "", "\n.*", "")
+	end
+	return success, errtxt
+end
+
+local convertdatabaseentry = function(datatype, value)
+	local success = true
+	local errtxt
+	local result = value
+	local res, err = pcall(function()
+		local sql = "CREATE TEMP TABLE testing ( test "..escape(datatype).." )"
+		assert (con:execute(sql))
+		sql = "INSERT INTO testing VALUES ('"..value.."')"
+		assert (con:execute(sql))
+		sql = "SELECT * FROM testing"
+		local cur = assert (con:execute(sql))
+		local row = cur:fetch ({}, "a")
+		if row then
+			result = row.test
+		end
+	end)
+	if not res then
+		success = false
+		errtxt = string.gsub(err or "", "\n.*", "")
+	end
+	local res, err = pcall(function()
+		local sql = "DROP TABLE testing"
+		assert (con:execute(sql))
+	end)
+	return success, errtxt, result
+end
+
+local printtableentries = function(tablename)
+	-- retrieve a cursor
+	local count = 0
+	cur = assert (con:execute("SELECT * from "..tablename))
+	-- print all rows, the rows will be indexed by field names
+	row = cur:fetch ({}, "a")
+	while row do
+		count = count + 1
+		for name,val in pairs(row) do
+			APP.logevent(name.." = "..val..", ")
+		end
+		row = cur:fetch (row, "a")
+	end
+	-- close everything
+	cur:close()
+	APP.logevent("Table "..tablename.." contains "..count.." rows")
+end
+
+local function insertdailyentry(rule, daily)
+	rule.starttime = rule.starttime or "00:00:00"
+	rule.endtime = rule.endtime or "24:00:00"
+	-- find the spot for this entry
+	local loc = #daily + 1 -- default to put at end
+	for i,ent in ipairs(daily) do
+		if ent.starttime >= rule.starttime then
+			loc = i
+			break
+		end
+	end
+	-- Adjust previous entry
+	if loc > 1 then
+		if daily[loc-1].endtime > rule.endtime then
+			-- split the previous entry
+			local temp = {}
+			for name,val in pairs(daily[loc-1]) do temp[name]=val end
+			table.insert(daily, loc, temp)
+		end
+		daily[loc-1].endtime = rule.starttime
+	end
+	-- Adjust the trailing entries
+	while #daily >= loc do
+		daily[loc].starttime = rule.endtime
+		if daily[loc].endtime <= daily[loc].starttime then
+			table.remove(daily, loc)
+		else
+			break
+		end
+	end
+	table.insert(daily, loc, rule)
+	return daily
+end
+
+-- time is a Lua time value (ie. result of os.time()
+local function getdailyentry(did, time)
+	time = time or os.time()
+	local date = os.date("%Y-%m-%d", time)
+	local dayofweek = os.date("%w", time)
+	if dayofweek == "0" then dayofweek = "7" end
+	-- get the rules for this did and date
+	local rules = listrules(did, date, dayofweek)
+	local daily = {}
+	for i,rule in ipairs(rules) do
+		insertdailyentry(rule, daily)
+	end
+	return daily
+end
+
+-- We're going to handle rules as a string, one rule per line, comma separated
+-- Convert rules table to string
+local function formatrules(rules)
+	value = {}
+	for i,rule in ipairs(rules) do
+		local rulearray = {rule.extension or "", rule.starttime or "", rule.endtime or "", rule.startdate or "", rule.enddate or "", rule.dayofweek or ""}
+		table.insert(value, table.concat(rulearray, ", "))
+	end
+	return table.concat(value, "\n")
+end
+
+-- Convert rules string to table
+local function parserules(did, rules)
+	local value = {}
+	for line in string.gmatch(rules, "([^\n]+)") do
+		local tabs = format.string_to_table(line, "%s*,%s*")
+		if #tabs > 0 then
+			value[#value+1] = {did=did, extension=tabs[1], starttime=tabs[2], endtime=tabs[3], startdate=tabs[4], enddate=tabs[5], dayofweek=tabs[6]}
+		end
+	end
+	return value
+end
+
+local function validaterules(rules)
+	-- Basically, we assume that any combination of rules is acceptable as long as each rule is valid
+	local success = true
+	local errtxt = {}
+	local res, err = pcall(function()
+		local connected = databaseconnect(DatabaseUser)
+		for i,rule in ipairs(rules) do
+			if not validator.is_integer(rule.did) then
+				errtxt[#errtxt+1] = "Rule #"..i..": DID is not a valid number"
+			end
+			if rule.extension == "" or string.find(rule.extension, "[^%d#%*]") then
+				errtxt[#errtxt+1] = "Rule #"..i..": Extension is not a valid number"
+			end
+			local res,err
+			if rule.starttime ~= "" then
+				res,err,rule.starttime = convertdatabaseentry("TIME", rule.starttime)
+				if not res then errtxt[#errtxt+1] = "Rule #"..i..": StartTime "..err end
+			end
+			if rule.endtime ~= "" then
+				res,err,rule.endtime = convertdatabaseentry("TIME", rule.endtime)
+				if not res then errtxt[#errtxt+1] = "Rule #"..i..": EndTime "..err end
+			end
+			if rule.startdate ~= "" then
+				res,err,rule.startdate = convertdatabaseentry("DATE", rule.startdate)
+				if not res then errtxt[#errtxt+1] = "Rule #"..i..": StartDate "..err end
+			end
+			if rule.enddate ~= "" then
+				res,err,rule.enddate = convertdatabaseentry("DATE", rule.enddate)
+				if not res then errtxt[#errtxt+1] = "Rule #"..i..": EndDate "..err end
+			end
+			if #rule.dayofweek ~= 7 or string.find(rule.dayofweek, "[^01]") then
+				errtxt[#errtxt+1] = "Rule #"..i..": DayOfWeek invalid entry"
+			end
+		end
+		if connected then databasedisconnect() end
+	end)
+	if not res and err then
+		errtxt[#errtxt+1] = err
+	end
+	if #errtxt > 0 then
+		success = false
+		errtxt = table.concat(errtxt, "\n")
+	else
+		errtxt = nil
+	end
+	return success, errtxt
+end
+
+local validatedefinition = function(defin)
+	local success = true
+	if not validator.is_integer(defin.value.did.value) then
+		defin.value.did.errtxt = "Must be a number"
+		success = false
+	end
+	if defin.value.identification.value ~= "" and not validator.is_integer(defin.value.identification.value) then
+		defin.value.identification.errtxt = "Invalid identification number"
+		success = false
+	end
+	-- defin.value.department
+	-- defin.value.description
+	
+	return success
+end
+
+local function createdefinitionlist(did, extension, identification, description, department)
+	local retval = {}
+	retval.definitions = cfe({ type="list", value={}, label="DID Definition List" })
+	retval.did = cfe({ value=did or "", label="DID search string" })
+	retval.extension = cfe({ value=extension or "", label="Extension search string" })
+	retval.identification = cfe({ value=identification or "", label="Identification search string" })
+	retval.description = cfe({ value=description or "", label="Description search string" })
+	retval.department = cfe({ value=department or "", label="Department search string" })
+	return cfe({ type="group", value=retval, label="DID Definition List" })
+end
+
+-- ################################################################################
+-- PUBLIC FUNCTIONS
+
+function getuseddefinitionlist(did, extension, identification, description, department)
+	local def = createdefinitionlist(did, extension, identification, description, department)
+	def.label = "Used "..def.label
+	local res, err = pcall(function()
+		local connected = databaseconnect(DatabaseUser)
+		def.value.definitions.value = listuseddefinitions(did, extension, identification, description, department)
+		if connected then databasedisconnect() end
+	end)
+	if not res then
+		def.errtxt = err
+	end
+	return def
+end
+
+function getunuseddefinitionlist(did, identification, description, department)
+	local def = createdefinitionlist(did, nil, identification, description, department)
+	def.value.extension = nil
+	def.label = "Unused "..def.label
+	local res, err = pcall(function()
+		local connected = databaseconnect(DatabaseUser)
+		def.value.definitions.value = listunuseddefinitions(did, identification, description, department)
+		if connected then databasedisconnect() end
+	end)
+	if not res then
+		def.errtxt = err
+	end
+	return def
+end
+
+function getdefinitionlist(did, extension, identification, description, department)
+	local def = createdefinitionlist(did, extension, identification, description, department)
+	--def.value.extension = nil
+	local res, err = pcall(function()
+		local connected = databaseconnect(DatabaseUser)
+		--def.value.definitions.value = listdefinitions(did, identification, description, department)
+		def.value.definitions.value = listuseddefinitions(did, extension, identification, description, department)
+		if def.value.extension.value == "" then
+			local tmp = listunuseddefinitions(did, identification, description, department)
+			for i,val in ipairs(tmp) do
+				val.extension = ""
+				table.insert(def.value.definitions.value, val)
+			end
+			table.sort(def.value.definitions.value, function (a,b) return (a.did < b.did) end )
+		end
+		if connected then databasedisconnect() end
+	end)
+	if not res then
+		def.errtxt = err
+	end
+	return def
+end
+
+function getdefinition(did)
+	local errtxt
+	local group = {}
+	group.did = cfe({ value=did or "", label="DID" })
+	group.identification = cfe({ label="Identification Number" })
+	group.department = cfe({ label="Department" })
+	group.description = cfe({ label="Description" })
+	group.rules = cfe({ type="longtext", label="Rules", descr="One entry (extension, starttime, endtime, startdate, enddate, dayofweek) per line"})
+	if did then
+		group.did.errtxt = "DID does not exist"
+		local res, err = pcall(function()
+			local connected = databaseconnect(DatabaseUser)
+			local definition = listdefinitions(did)
+			local rules = listrules(did)
+			if connected then databasedisconnect() end
+			if #definition == 1 then
+				group.did.errtxt = nil
+				for name,val in pairs(definition[1]) do
+					if group[name] then
+						group[name].value = val
+					end
+				end
+			end
+			if #rules > 0 then
+				group.rules.value = formatrules(rules)
+			end
+		end)
+		if not res then
+			errtxt = err
+		end
+	end
+
+	return cfe({ type="group", value=group, label="DID Description", errtxt=errtxt })
+end
+
+-- If exists true, then make sure exists first, if false or undefined, make sure doesn't exist
+function savedefinition(defin, exists)
+	-- remove blank entries, if present
+	defin.value.rules.value = string.gsub("\n"..format.dostounix(defin.value.rules.value), "\n%s*,%s*,%s*,%s*,%s*,%s*0000000", "")
+	defin.value.rules.value = string.gsub(defin.value.rules.value, "$\n", "")
+	
+	local rules = parserules(defin.value.did.value, defin.value.rules.value)
+	-- note that validaterules might change the rules to standard formatting
+	local success, errtxt = validaterules(rules)
+	defin.value.rules.value = formatrules(rules)
+	defin.value.rules.errtxt = errtxt
+	success = validatedefinition(defin) and success
+	defin.errtxt = "Failed to save definition"
+	if success then
+		local definition = {}
+		for name,val in pairs(defin.value) do
+			definition[name] = val.value
+		end
+
+		local res, err = pcall(function()
+			local connected
+			if not exists then
+				databasedisconnect()
+				local pw = format.parse_ini_file(fs.read_file(configfile) or "", "", "password") or ""
+				connected = databaseconnect(DatabaseOwner, pw)
+			else
+				connected = databaseconnect(DatabaseUser)
+			end
+			local def = listdefinitions(definition.did)
+			if #def > 0 and not exists then
+				defin.value.did.errtxt = "Definition already exists"
+			elseif #def == 0 and exists then
+				defin.value.did.errtxt = "Definition does not exist"
+			else
+				if exists then
+					updatedefinitionentry(definition)
+				else
+					insertdefinitionentry(definition)
+				end
+				updaterules(defin.value.did.value, rules)
+				defin.errtxt = nil
+			end
+			if connected then databasedisconnect() end
+		end)
+		if not res and err then
+			defin.errtxt = defin.errtxt .. "\n" .. err
+		end
+	end
+
+	return defin
+end
+
+function updatedefinition(defin)
+	return savedefinition(defin, true)
+end
+
+function deletedefinition(did)
+	local result = cfe({ label="Delete Definition Result", errtxt="Definition does not exist" })
+	local res, err = pcall(function()
+		databasedisconnect()
+		local pw = format.parse_ini_file(fs.read_file(configfile) or "", "", "password") or ""
+		databaseconnect(DatabaseOwner, pw)
+		local def = listdefinitions(did)
+		if #def == 1 then
+			deletedefinitionentry(did)
+			result.value = "Definition Deleted"
+			result.errtxt = nil
+		end
+		databasedisconnect()
+	end)
+	if not res then
+		result.errtxt = err
+	end
+	return result
+end
+
+function getunuseddefinition()
+	local errtxt
+	local retval = {}
+	retval.exchange = cfe({ type="select", label="Exchange", option={} })
+	local res, err = pcall(function()
+		local connected = databaseconnect(DatabaseUser)
+		local exchanges = listexchanges()
+		if #exchanges == 0 then
+			retval.exchange.errtxt = "No exchanges available"
+		else
+			for i,val in ipairs(exchanges) do
+				table.insert(retval.exchange.option, (string.gsub(val, "^(...)", "(%1) ")))
+			end
+			retval.exchange.value = retval.exchange.option[1]
+		end
+		if connected then databasedisconnect() end
+	end)
+	if not res and err then
+		errtxt = err
+	end
+	return cfe({ type="group", value=retval, label="Create New Definition" })
+end
+
+function setunuseddefinition(defin)
+	local success = modelfunctions.validateselect(defin.value.exchange)
+	if success then
+		local res, err = pcall(function()
+			local connected = databaseconnect(DatabaseUser)
+			local did = findunuseddefinition(string.gsub(defin.value.exchange.value, "[^%d]", ""))
+			if #did == 0 then
+				success = false
+				defin.value.exchange.errtxt = "There are no unused extensions for that exchange"
+			else
+				defin.value.did = cfe({ value=did[1], label="DID" })
+			end
+			if connected then databasedisconnect() end
+		end)
+		if not res and err then
+			defin.errtxt = err
+		end
+	end
+	if not success then
+		defin.errtxt = "Failed to create new definition"
+	end
+	return defin
+end
+
+function publishdefinition(did)
+	local result = ""
+	local errtxt = "Invalid DID"
+	local pw = format.parse_ini_file(fs.read_file(configfile) or "", "", "password") or ""
+	local res, err = pcall(function()
+		databasedisconnect()
+		databaseconnect(DatabaseOwner, pw)
+		local dids = listdefinitions()
+		for i,d in ipairs(dids) do
+			if d.did == did then
+				local rules = getdailyentry(did)
+				publishrules(did, rules)
+				errtxt = nil
+				result = "Published DID rules"
+				logme("Published DID "..did)
+				break
+			end
+		end
+		databasedisconnect()
+	end)
+	if not res and err then
+		errtxt = err
+	end
+	return cfe({ value=result, errtxt=errtxt, label="Result of Publish" })
+end
+
+function publishalldefinitions()
+	local result = ""
+	local errtxt
+	local pw = format.parse_ini_file(fs.read_file(configfile) or "", "", "password") or ""
+	local res, err = pcall(function()
+		databasedisconnect()
+		databaseconnect(DatabaseOwner, pw)
+		local dids = listdefinitions()
+		local time = os.time()
+		for i,d in ipairs(dids) do
+			local rules = getdailyentry(d.did, time)
+			publishrules(d.did, rules)
+		end
+		result = "Published "..#dids.." DID rules"
+		logme("Publishing "..#dids.." DIDs took "..os.time()-time.." seconds")
+		groomdbhistlog()
+		databasedisconnect()
+	end)
+	if not res and err then
+		errtxt = err
+	end
+	return cfe({ value=result or "", errtxt=errtxt, label="Result of Publish" })
+end
+
+function testdatabase()
+	local retval = cfe({ type="boolean", value=false, label="Database present" })
+	local dbs = listdatabases()
+	for i,db in ipairs(dbs) do
+		if db == DatabaseName then
+			retval.value = true
+			break
+		end
+	end
+	return retval
+end
+
+function getnewdatabase()
+	local database = {}
+	local errtxt
+	database.password = cfe({ label="Password" })
+	database.password_confirm = cfe({ label="Password (confirm)" })
+	local test = testdatabase()
+	if test.value then
+		errtxt = "Database already exists!"
+		success = false
+	end
+	return cfe({ type="group", value=database, label="Create Database", errtxt=errtxt })
+end
+
+function create_database(database)
+	local success = true
+	local errtxt
+
+	if database.value.password.value == "" or string.match(database.value.password.value, "'%s") then
+		database.value.password.errtxt = "Invalid password"
+		success = false
+	end
+	if database.value.password.value ~= database.value.password_confirm.value then
+		database.value.password_confirm.errtxt = "Password does not match"
+		success = false
+	end
+	local test = testdatabase()
+	if test.value then
+		errtxt = "Database already exists!"
+		success = false
+	end
+
+	if success then
+		errtxt = createdatabase(database.value.password.value)
+		test = testdatabase()
+		if not test.value then
+			success = false
+		else
+			local res, err = pcall(function()
+				databasedisconnect()
+				databaseconnect(DatabaseOwner, database.value.password.value)
+				for i,scr in ipairs(database_creation_script) do
+					assert (con:execute(scr))
+				end
+				databasedisconnect()
+				-- put the password in the config file for future use
+				local configcontent = format.update_ini_file(fs.read_file(configfile) or "", "", "password", database.value.password.value)
+				fs.write_file(configfile, configcontent)
+			end)
+			if not res then
+				errtxt = err
+				success = false
+			end
+		end
+		if not success then
+			deletedatabase()
+		end
+	end
+	
+	if not success then
+		database.errtxt = "Failed to create database"
+		if errtxt then
+			database.errtxt = database.errtxt.."\n"..errtxt
+		end
+	end
+
+	return database
+end
+
+function getactivitylog()
+	local retval = cfe({ type="list", value={}, label="Weblog Activity Log" })
+	local res, err = pcall(function()
+		local connected = databaseconnect(DatabaseUser)
+		retval.value = listhistorylogentries() or {}
+		if connected then databasedisconnect() end
+	end)
+	if not res then
+		retval.errtxt = err
+	end
+
+	return retval
+end