diff options
Diffstat (limited to 'dbmodelfunctions.lua')
| -rw-r--r-- | dbmodelfunctions.lua | 230 |
1 files changed, 230 insertions, 0 deletions
diff --git a/dbmodelfunctions.lua b/dbmodelfunctions.lua new file mode 100644 index 0000000..3bc2382 --- /dev/null +++ b/dbmodelfunctions.lua @@ -0,0 +1,230 @@ +module(..., package.seeall) +-- 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) + +function list_tables(db) + local retval = {} + local errtxt + local res, err = pcall(function() + local connected = db.databaseconnect() + retval = db.listtables() + if connected then db.databasedisconnect() end + end) + if not res and err then + errtxt = err + end + + return cfe({ type="list", value=retval, label="List of Database Tables", errtxt=errtxt }) +end + +function list_table_entries(db, self, clientdata) + local table = clientdata.table + local retval = {} + retval.table = cfe({ value=table or "", label="Table" }) + retval.fields = cfe({ type="list", value={}, label="List of Table Fields" }) + retval.entries = cfe({ type="structure", value={}, label="List of Database Entries" }) + local errtxt + local res, err = pcall(function() + local connected = db.databaseconnect() + local tables = db.listtables() + retval.table.errtxt = "Table does not exist" + errtxt = "Table does not exist" + for i,t in ipairs(tables) do + if t == table then + retval.table.errtxt = nil + errtxt = nil + retval.entries.value = db.getselectresponse("SELECT * FROM "..db.escape(table).." ORDER BY id ASC") or {} + retval.fields.value = db.listcolumns(table) or {} + end + end + if connected then db.databasedisconnect() end + end) + if not res and err then + errtxt = err + end + + return cfe({ type="group", value=retval, label="Database Table Entries", errtxt=errtxt }) +end + +function get_new_table_entry(db, self, clientdata) + local retval = {} + retval.table = cfe({ value=clientdata.table or "", label="Table", errtxt="Table does not exist", readonly=true, seq=0 }) + local errtxt = "Table does not exist" + if clientdata.table and clientdata.table ~= "" then + local res, err = pcall(function() + local connected = db.databaseconnect() + local tables = db.listtables() + for i,t in ipairs(tables) do + if t == clientdata.table then + retval.table.errtxt = nil + errtxt = nil + break + end + end + if not errtxt then + local fields = db.listcolumns(clientdata.table) + for i,f in ipairs(fields) do + retval[f] = cfe({ label=f, seq=i }) + end + if retval.id then + retval.id.type = "hidden" + end + end + if connected then db.databasedisconnect() end + end) + if not res and err then + errtxt = err + end + end + + return cfe({ type="group", value=retval, label="Database Table Entry", errtxt=errtxt }) +end + +function get_table_entry(db, self, clientdata) + local retval = get_new_table_entry(db, self, clientdata) + if not retval.value.table.errtxt and retval.value.id then + retval.value.id.type = "text" + local res, err = pcall(function() + local connected = db.databaseconnect() + retval.errtxt = "Entry does not exist" + if retval.value.id then + retval.value.id.value = clientdata.id or "" + retval.value.id.errtxt = "Entry does not exist" + if clientdata.id and clientdata.id ~= "" then + local entry = db.getselectresponse("SELECT * FROM "..db.escape(clientdata.table).." WHERE id='"..db.escape(clientdata.id).."'") + if entry and #entry > 0 then + for n,v in pairs(entry[1]) do + if retval.value[n] then retval.value[n].value = v end + if n == "id" then + retval.value.id.readonly = true + retval.value.id.errtxt = nil + retval.errtxt = nil + end + end + 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 create_table_entry(db, self, entry, action) + return update_table_entry(db, self, entry, action, true) +end + +function update_table_entry(db, self, entry, action, create) + 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 then + success = false + entry.value.table.errtxt = "Table does not exist" + end + if not create then + if not entry.value.id then + success = false + elseif not entry.value.id.value or entry.value.id.value == "" then + success = false + entry.value.id.errtxt = "Invalid id" + end + end + if success then + local res, err = pcall(function() + local connected = db.databaseconnect() + local tables = db.listtables() + success = false + entry.value.table.errtxt = "Table does not exist" + for i,t in ipairs(tables) do + if t == entry.value.table.value then + success = true + entry.value.table.errtxt = nil + break + end + end + if success and not create then + local sql = "SELECT * FROM "..db.escape(entry.value.table.value).." WHERE id='"..db.escape(entry.value.id.value).."'" + local tmp = db.getselectresponse(sql) + if not tmp or #tmp == 0 then + success = false + entry.value.id.errtxt = "Entry does not exist" + errtxt = "Entry does not exist" + end + end + if success then + local names = {} + local values = {} + for n,v in pairs(entry.value) do + if n ~= "table" and n ~= "id" 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 id='"..db.escape(entry.value.id.value).."'" + 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 get_delete_table_entry(db, self, clientdata) + local retval = {} + retval.table = cfe({ value=clientdata.table or "", label="Table", seq=0 }) + retval.id = cfe({ value=clientdata.id or "", label="ID", seq=1 }) + return cfe({ type="group", value=retval, label="Delete Database Table Entry" }) +end + +function delete_table_entry(db, self, entry) + entry.errtxt = "Failed to delete table entry" + if entry.value.table.value == "" then + entry.value.table.errtxt = "Invalid table" + elseif entry.value.id.value == "" then + entry.value.id.errtxt = "Invalid entry" + 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 sql = "DELETE FROM "..db.escape(entry.value.table.value).." WHERE id='"..db.escape(entry.value.id.value).."'" + 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 |