diff options
Diffstat (limited to 'dbmodelfunctions.lua')
| -rw-r--r-- | dbmodelfunctions.lua | 103 |
1 files changed, 52 insertions, 51 deletions
diff --git a/dbmodelfunctions.lua b/dbmodelfunctions.lua index f5bb6c7..d9c8689 100644 --- a/dbmodelfunctions.lua +++ b/dbmodelfunctions.lua @@ -92,6 +92,7 @@ function mymodule.list_table_entries(dbase, self, clientdata) 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() @@ -102,8 +103,9 @@ function mymodule.list_table_entries(dbase, self, clientdata) 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 {} + 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 @@ -166,22 +168,27 @@ function mymodule.get_table_entry(dbase, self, clientdata) 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" + 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" - 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 + 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 @@ -210,46 +217,39 @@ function mymodule.update_table_entry(dbase, self, entry, action, create) 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 + 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 not create then - if not entry.value.fields or not entry.value.fields.value.id then - success = false - 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 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 + 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 - if success and not create then - 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" + 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 - -- FIXME need to check if already exists for create end if success then local names = {} local values = {} for n,v in pairs(entry.value.fields.value) do - if n ~= "id" then + if create or not reversekeyfields[n] then names[#names+1] = db.escape(n) values[#values+1] = db.escape(v.value) end @@ -257,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.fields.value.id.value).."'" + 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 @@ -306,12 +306,11 @@ function mymodule.get_delete_table_entry(dbase, self, clientdata) 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 }) - 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 + 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) @@ -330,12 +329,9 @@ function mymodule.delete_table_entry(dbase, self, entry) 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.fields.value.id.value == "" then - entry.value.fields.value.id.errtxt = "Invalid entry" else local res, err = pcall(function() local connected = db.databaseconnect() @@ -348,7 +344,12 @@ 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.fields.value.id.value).."'" + 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 |