diff options
Diffstat (limited to 'dbmodelfunctions.lua')
| -rw-r--r-- | dbmodelfunctions.lua | 288 |
1 files changed, 190 insertions, 98 deletions
diff --git a/dbmodelfunctions.lua b/dbmodelfunctions.lua index cf92252..f5bb6c7 100644 --- a/dbmodelfunctions.lua +++ b/dbmodelfunctions.lua @@ -1,138 +1,186 @@ 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 retval = {} - local errtxt + 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() - local db = dbase - if type(dbase) == "function" then - db = dbase() - end - retval = db.listdatabases() + retval.value.databases.value = db.listdatabases() end) if not res and err then - errtxt = err + retval.value.connection.errtxt = err end - - return cfe({ type="list", value=retval, label="List of Databases", errtxt=errtxt }) + + return retval end function mymodule.list_tables(dbase, self, clientdata) - local retval = {} - local errtxt + 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 db = dbase - if type(dbase) == "function" then - db = dbase() - end local connected = db.databaseconnect() - retval = db.listtables() + retval.value.tables.value = db.listtables() if connected then db.databasedisconnect() end end) if not res and err then - errtxt = err + retval.value.connection.errtxt = err end - return cfe({ type="list", value=retval, label="List of Database Tables", errtxt=errtxt }) + return retval end function mymodule.list_table_entries(dbase, 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 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.entries = cfe({ type="structure", value={}, label="List of Database Entries" }) local res, err = pcall(function() - local db = dbase - if type(dbase) == "function" then - db = dbase() - end local connected = db.databaseconnect() local tables = db.listtables() - retval.table.errtxt = "Table does not exist" - errtxt = "Table does not exist" + retval.value.table.errtxt = "Table does not exist" + retval.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 {} + if t == retval.value.table.value then + retval.value.table.errtxt = nil + retval.errtxt = nil + retval.value.entries.value = db.getselectresponse("SELECT * FROM "..db.escape(t).." ORDER BY id ASC") or {} + retval.value.fields.value = db.listcolumns(t) or {} end end if connected then db.databasedisconnect() end end) if not res and err then - errtxt = err + retval.value.connection.errtxt = err end - - return cfe({ type="group", value=retval, label="Database Table Entries", errtxt=errtxt }) + return retval end function mymodule.get_new_table_entry(dbase, 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 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 db = dbase - if type(dbase) == "function" then - db = dbase() - end 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 + 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 fields = db.listcolumns(clientdata.table) + 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[f] = cfe({ label=f, seq=i }) - end - if retval.id then - retval.id.type = "hidden" + 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 - errtxt = err + retval.value.connection.errtxt = err end end - return cfe({ type="group", value=retval, label="Database Table Entry", errtxt=errtxt }) + return retval end function mymodule.get_table_entry(dbase, self, clientdata) - local retval = mymodule.get_new_table_entry(dbase, self, clientdata) - if not retval.value.table.errtxt and retval.value.id then - retval.value.id.type = "text" + 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 and retval.value.fields.value.id then + retval.value.fields.value.id.type = "text" local res, err = pcall(function() - local db = dbase - if type(dbase) == "function" then - db = dbase() - end 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 + retval.value.fields.value.id.errtxt = "Entry does not exist" -- FIXME - need to determine key + if retval.value.fields.value.id.value ~= "" then + local entry = db.getselectresponse("SELECT * FROM "..db.escape(retval.value.table.value).." WHERE id='"..db.escape(retval.value.fields.value.id.value).."'") + if entry and #entry > 0 then + for n,v in pairs(entry[1]) do + if retval.value.fields.value[n] then retval.value.fields.value[n].value = v end + if n == "id" then + retval.value.fields.value.id.readonly = true + retval.value.fields.value.id.errtxt = nil + retval.errtxt = nil end end end @@ -152,6 +200,12 @@ function mymodule.create_table_entry(dbase, self, entry, action) 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 @@ -161,19 +215,15 @@ function mymodule.update_table_entry(dbase, self, entry, action, create) entry.value.table.errtxt = "Table does not exist" end if not create then - if not entry.value.id then + if not entry.value.fields or not entry.value.fields.value.id then success = false - elseif not entry.value.id.value or entry.value.id.value == "" then + elseif not entry.value.fields.value.id.value or entry.value.fields.value.id.value == "" then success = false entry.value.id.errtxt = "Invalid id" end end if success then local res, err = pcall(function() - local db = dbase - if type(dbase) == "function" then - db = dbase() - end local connected = db.databaseconnect() local tables = db.listtables() success = false @@ -186,19 +236,20 @@ function mymodule.update_table_entry(dbase, self, entry, action, create) 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 sql = "SELECT * FROM "..db.escape(entry.value.table.value).." WHERE id='"..db.escape(entry.value.fields.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 + -- FIXME need to check if already exists for create end if success then local names = {} local values = {} - for n,v in pairs(entry.value) do - if n ~= "table" and n ~= "id" then + for n,v in pairs(entry.value.fields.value) do + if n ~= "id" then names[#names+1] = db.escape(n) values[#values+1] = db.escape(v.value) end @@ -206,7 +257,7 @@ function mymodule.update_table_entry(dbase, self, entry, action, create) 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).."'" + sql = "UPDATE "..db.escape(entry.value.table.value).." SET ("..table.concat(names, ", ")..") = ('"..table.concat(values, "', '").."') WHERE id='"..db.escape(entry.value.fields.value.id.value).."'" end db.runsqlcommand(sql) end @@ -228,24 +279,65 @@ function mymodule.update_table_entry(dbase, self, entry, action, create) end function mymodule.get_delete_table_entry(dbase, 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" }) + 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 + retval.value.fields = cfe({ type="group", value={}, label="Table Fields", seq=2 }) + retval.value.fields.value.id = cfe({ label="ID", seq=1 }) -- FIXME + --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.delete_table_entry(dbase, self, entry) + local db = dbase + if type(dbase) == "function" then + db = dbase() + end + fill_connection(db, self, entry) + + -- FIXME - need connection and fields 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" + elseif entry.value.fields.value.id.value == "" then + entry.value.fields.value.id.errtxt = "Invalid entry" else local res, err = pcall(function() - local db = dbase - if type(dbase) == "function" then - db = dbase() - end local connected = db.databaseconnect() entry.value.table.errtxt = "Invalid table" local tables = db.listtables() @@ -256,7 +348,7 @@ function mymodule.delete_table_entry(dbase, self, entry) 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).."'" + local sql = "DELETE FROM "..db.escape(entry.value.table.value).." WHERE id='"..db.escape(entry.value.fields.value.id.value).."'" db.runsqlcommand(sql) entry.errtxt = nil end |