diff options
Diffstat (limited to 'dbmodelfunctions.lua')
| -rw-r--r-- | dbmodelfunctions.lua | 366 |
1 files changed, 0 insertions, 366 deletions
diff --git a/dbmodelfunctions.lua b/dbmodelfunctions.lua deleted file mode 100644 index d9c8689..0000000 --- a/dbmodelfunctions.lua +++ /dev/null @@ -1,366 +0,0 @@ -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 |