Move the lib files into a subdirectory in preparation for creating a generic controller/model

1 files changed, 366 insertions, 0 deletions

diff --git a/lib/dbmodelfunctions.lua b/lib/dbmodelfunctions.lua
new file mode 100644
index 0000000..d9c8689
--- /dev/null
+++ b/lib/dbmodelfunctions.lua
@@ -0,0 +1,366 @@
+local mymodule = {}
+
+db = require("acf.db")
+
+-- Helper library for model functions for viewing and editing database entries
+-- Relies heavily on the acf-lib/db.lua library (db object passed into each function)
+
+local function get_connection(dbase, self, clientdata)
+	local retval = cfe({ type="group", value={} })
+	retval.value.connection = cfe({ type="group", value={}, label="Database Connection", seq=0 })
+	retval.value.connection.value.engine = cfe({ type="select", label="Database Engine", option={}, key=true, seq=1 })
+	for n,v in pairs(db.engine) do
+		retval.value.connection.value.engine.option[#retval.value.connection.value.engine.option+1] = {name=n, value=v}
+	end
+	retval.value.connection.value.database = cfe({ label="Database", key=true, seq=2 })
+	retval.value.connection.value.user = cfe({ label="User", key=true, seq=3 })
+	retval.value.connection.value.password = cfe({ type="password", label="Password", key=true, seq=4 })
+	retval.value.connection.value.host = cfe({ label="Host", key=true, seq=5 })
+	retval.value.connection.value.port = cfe({ label="Port", key=true, seq=6 })
+
+	self.handle_clientdata(retval, clientdata)
+
+	-- If dbase has a default, remove the cfe. Otherwise, fill in with the value from the cfe.
+	for n,v in pairs(retval.value.connection.value) do
+		if dbase[n] then
+			retval.value.connection.value[n] = nil
+		else
+			dbase[n] = v.value
+		end
+	end
+
+	return retval
+end
+
+local function fill_connection(dbase, self, retval)
+	-- Fill in dbase with the values from the cfe retval
+	for n,v in pairs(retval.value.connection.value) do
+		dbase[n] = v.value
+	end
+
+	return retval
+end
+
+function mymodule.list_databases(dbase, self, clientdata)
+	local db = dbase
+	if type(dbase) == "function" then
+		db = dbase()
+	end
+
+	local retval = get_connection(db, self, clientdata)
+	retval.label = "List of Databases"
+	retval.value.databases = cfe({ type="list", value={}, label="List of Databases" })
+	local res, err = pcall(function()
+		retval.value.databases.value = db.listdatabases()
+	end)
+	if not res and err then
+		retval.value.connection.errtxt = err
+	end
+
+	return retval
+end
+
+function mymodule.list_tables(dbase, self, clientdata)
+	local db = dbase
+	if type(dbase) == "function" then
+		db = dbase()
+	end
+
+	local retval = get_connection(db, self, clientdata)
+	retval.label = "List of Database Tables"
+	retval.value.tables = cfe({ type="list", value={}, label="List of Database Tables" })
+	local res, err = pcall(function()
+		local connected = db.databaseconnect()
+		retval.value.tables.value = db.listtables()
+		if connected then db.databasedisconnect() end
+	end)
+	if not res and err then
+		retval.value.connection.errtxt = err
+	end
+	
+	return retval
+end
+
+function mymodule.list_table_entries(dbase, self, clientdata)
+	local db = dbase
+	if type(dbase) == "function" then
+		db = dbase()
+	end
+
+	local retval = get_connection(db, self, clientdata)
+	retval.label = "Database Table Entries"
+	retval.value.table = cfe({ label="Table", key=true })
+	self.handle_clientdata(retval, clientdata)
+	retval.value.fields = cfe({ type="list", value={}, label="List of Table Fields" })
+	retval.value.keyfields = cfe({ type="list", value={}, label="List of Key Table Fields" })
+	retval.value.entries = cfe({ type="structure", value={}, label="List of Database Entries" })
+	local res, err = pcall(function()
+		local connected = db.databaseconnect()
+		local tables = db.listtables()
+		retval.value.table.errtxt = "Table does not exist"
+		retval.errtxt = "Table does not exist"
+		for i,t in ipairs(tables) do
+			if t == retval.value.table.value then
+				retval.value.table.errtxt = nil
+				retval.errtxt = nil
+				retval.value.fields.value = db.listcolumns(t) or {}
+				retval.value.keyfields.value = db.listkeycolumns(t) or {}
+				retval.value.entries.value = db.getselectresponse("SELECT * FROM "..db.escape(t)) or {}
+			end
+		end
+		if connected then db.databasedisconnect() end
+	end)
+	if not res and err then
+		retval.value.connection.errtxt = err
+	end
+	return retval
+end
+
+function mymodule.get_new_table_entry(dbase, self, clientdata)
+	local db = dbase
+	if type(dbase) == "function" then
+		db = dbase()
+	end
+
+	local retval = get_connection(db, self, clientdata)
+	retval.label = "Database Table Entry"
+
+	retval.value.table = cfe({ label="Table", errtxt="Table does not exist", key=true, seq=1 })
+	self.handle_clientdata(retval, clientdata)
+	retval.errtxt = "Table does not exist"
+	if retval.value.table.value ~= "" then
+		local res, err = pcall(function()
+			local connected = db.databaseconnect()
+			local tables = db.listtables()
+			for i,t in ipairs(tables) do
+				if t == retval.value.table.value then
+					retval.value.table.errtxt = nil
+					retval.value.table.readonly = true
+					retval.errtxt = nil
+					for n,v in pairs(retval.value.connection.value) do
+						v.readonly = true
+					end
+					break
+				end
+			end
+			if not errtxt then
+				retval.value.fields = cfe({ type="group", value={}, label="Table Fields", seq=2 })
+				local fields = db.listcolumns(retval.value.table.value)
+				for i,f in ipairs(fields) do
+					retval.value.fields.value[f] = cfe({ label=f, seq=i })
+				end
+			end
+			if connected then db.databasedisconnect() end
+		end)
+		if not res and err then
+			retval.value.connection.errtxt = err
+		end
+	end
+
+	return retval
+end
+
+function mymodule.get_table_entry(dbase, self, clientdata)
+	local db = dbase
+	if type(dbase) == "function" then
+		db = dbase()
+	end
+
+	local retval = mymodule.get_new_table_entry(db, self, clientdata)
+	self.handle_clientdata(retval, clientdata) -- Fill in the fields
+	if not retval.value.connection.errtxt and not retval.value.table.errtxt and retval.value.fields then
+		local res, err = pcall(function()
+			local connected = db.databaseconnect()
+			local keyfields = db.listkeycolumns(retval.value.table.value)
+			local reversekeyfields = {}
+			local fieldchecks = {}
+			for i,f in ipairs(keyfields) do
+				reversekeyfields[f] = i
+				retval.value.fields.value[f].errtxt = "Entry does not exist"
+				fieldchecks[#fieldchecks+1] = f.."='"..db.escape(retval.value.fields.value[f].value).."'"
+			end
+			retval.errtxt = "Entry does not exist"
+			local sql = "SELECT * FROM "..db.escape(retval.value.table.value).." WHERE "..table.concat(fieldchecks, " AND ")
+			local entry = db.getselectresponse(sql)
+			if entry and #entry > 0 then
+				retval.errtxt = nil
+				for n,v in pairs(entry[1]) do
+					if retval.value.fields.value[n] then retval.value.fields.value[n].value = v end
+					if reversekeyfields[n] then
+						retval.value.fields.value[n].readonly = true
+						retval.value.fields.value[n].errtxt = nil
+					end
+				end
+			end
+			if connected then db.databasedisconnect() end
+		end)
+		if not res and err then
+			retval.errtxt = err
+		end
+	end
+
+	return retval
+end
+
+function mymodule.create_table_entry(dbase, self, entry, action)
+        return mymodule.update_table_entry(dbase, self, entry, action, true)
+end
+
+function mymodule.update_table_entry(dbase, self, entry, action, create)
+	local db = dbase
+	if type(dbase) == "function" then
+		db = dbase()
+	end
+	fill_connection(db, self, entry)
+
+	local success = true
+	local errtxt
+	-- Validate the settings
+	-- relying on get_table_entry to do the validation of table
+	if entry.value.table.value == "" or entry.value.table.errtxt or nil == entry.value.fields then
+		success = false
+		entry.value.table.errtxt = "Table does not exist"
+	end
+	if success then
+        	local res, err = pcall(function()
+			local connected = db.databaseconnect()
+			local keyfields = db.listkeycolumns(entry.value.table.value)
+			local reversekeyfields = {}
+			local fieldchecks = {}
+			for i,f in ipairs(keyfields) do
+				reversekeyfields[f] = i
+				entry.value.fields.value[f].errtxt = "Entry does not exist"
+				fieldchecks[#fieldchecks+1] = f.."='"..db.escape(entry.value.fields.value[f].value).."'"
+			end
+			local sql = "SELECT * FROM "..db.escape(entry.value.table.value).." WHERE "..table.concat(fieldchecks, " AND ")
+			local tmp = db.getselectresponse(sql)
+			if not create and (not tmp or #tmp == 0) then
+				success = false
+				for i,n in ipairs(keyfields) do
+					entry.value.fields.value[n].errtxt = "Entry does not exist"
+				end
+			elseif create and tmp and #tmp>0 then
+				success = false
+				for i,n in ipairs(keyfields) do
+					entry.value.fields.value[n].errtxt = "Entry already exists"
+				end
+			end
+			if success then
+				local names = {}
+				local values = {}
+				for n,v in pairs(entry.value.fields.value) do
+					if create or not reversekeyfields[n] then
+						names[#names+1] = db.escape(n)
+						values[#values+1] = db.escape(v.value)
+					end
+				end
+				if create then
+					sql = "INSERT INTO "..db.escape(entry.value.table.value).." ("..table.concat(names, ", ")..") VALUES('"..table.concat(values, "', '").."')"
+				else
+					sql = "UPDATE "..db.escape(entry.value.table.value).." SET ("..table.concat(names, ", ")..") = ('"..table.concat(values, "', '").."') WHERE "..table.concat(fieldchecks, " AND ")
+				end
+				db.runsqlcommand(sql)
+			end
+			if connected then db.databasedisconnect() end
+		end)
+		if not res and err then
+			success = false
+			errtxt = err
+		end
+	end
+	if not success then
+		if create then
+			entry.errtxt = errtxt or "Failed to create entry"
+		else
+			entry.errtxt = errtxt or "Failed to save entry"
+		end
+	end
+	return entry
+end
+
+function mymodule.get_delete_table_entry(dbase, self, clientdata)
+	local db = dbase
+	if type(dbase) == "function" then
+		db = dbase()
+	end
+
+	local retval = get_connection(db, self, clientdata)
+	retval.label = "Delete Database Table Entry"
+
+	retval.value.table = cfe({ label="Table", errtxt="Table does not exist", key=true, seq=1 })
+	self.handle_clientdata(retval, clientdata)
+	retval.errtxt = "Table does not exist"
+	if retval.value.table.value ~= "" then
+		local res, err = pcall(function()
+			local connected = db.databaseconnect()
+			local tables = db.listtables()
+			for i,t in ipairs(tables) do
+				if t == retval.value.table.value then
+					retval.value.table.errtxt = nil
+					retval.value.table.readonly = true
+					retval.errtxt = nil
+					for n,v in pairs(retval.value.connection.value) do
+						v.readonly = true
+					end
+					break
+				end
+			end
+			if not errtxt then
+				local keyfields = db.listkeycolumns(retval.value.table.value)
+				retval.value.fields = cfe({ type="group", value={}, label="Table Fields", seq=2 })
+				for i,f in ipairs(keyfields) do
+					retval.value.fields.value[f] = cfe({ label=f, seq=i })
+				end
+			end
+			if connected then db.databasedisconnect() end
+		end)
+		if not res and err then
+			retval.value.connection.errtxt = err
+		end
+	end
+
+	return retval
+end
+
+function mymodule.delete_table_entry(dbase, self, entry)
+	local db = dbase
+	if type(dbase) == "function" then
+		db = dbase()
+	end
+	fill_connection(db, self, entry)
+
+	entry.errtxt = "Failed to delete table entry"
+	if entry.value.table.value == "" then
+		entry.value.table.errtxt = "Invalid table"
+	else
+	       	local res, err = pcall(function()
+			local connected = db.databaseconnect()
+			entry.value.table.errtxt = "Invalid table"
+			local tables = db.listtables()
+			for i,t in ipairs(tables) do
+				if t == entry.value.table.value then
+					entry.value.table.errtxt = nil
+					break
+				end
+			end
+			if not entry.value.table.errtxt then
+				local keyfields = db.listkeycolumns(entry.value.table.value)
+				local fieldchecks = {}
+				for i,f in ipairs(keyfields) do
+					fieldchecks[#fieldchecks+1] = f.."='"..db.escape(entry.value.fields.value[f].value).."'"
+				end
+				local sql = "DELETE FROM "..db.escape(entry.value.table.value).." WHERE "..table.concat(fieldchecks, " AND ")
+				db.runsqlcommand(sql)
+				entry.errtxt = nil
+			end
+			if connected then db.databasedisconnect() end
+		end)
+		if not res and err then
+			entry.errtxt = err
+		end
+	end
+
+	return entry
+end
+
+return mymodule