From e4129fdebf270ab4a586af93947278817ed89349 Mon Sep 17 00:00:00 2001 From: Ted Trask Date: Mon, 3 Nov 2014 15:04:17 +0000 Subject: Add support for NULL and DEFAULT values in viewtable, create/update/delete tableentry --- lib/db-viewtable-html.lsp | 8 ++++- lib/dbmodelfunctions.lua | 75 ++++++++++++++++++++++++++++++++++------------- 2 files changed, 62 insertions(+), 21 deletions(-) diff --git a/lib/db-viewtable-html.lsp b/lib/db-viewtable-html.lsp index bd4f051..9b60720 100644 --- a/lib/db-viewtable-html.lsp +++ b/lib/db-viewtable-html.lsp @@ -106,7 +106,13 @@ allowupdate = allowupdate and viewlibrary.check_permission("updatetableentry") <% end %> <% for i,f in ipairs(form.value.fields.value) do %> - <%= html.html_escape(tableentry[f]) %> + + <% if not tableentry[f] then %> + NULL + <% else %> + <%= html.html_escape(tableentry[f]) %> + <% end %> + <% end %> <% end %> diff --git a/lib/dbmodelfunctions.lua b/lib/dbmodelfunctions.lua index 79bc134..c1da6ed 100644 --- a/lib/dbmodelfunctions.lua +++ b/lib/dbmodelfunctions.lua @@ -151,9 +151,13 @@ function mymodule.get_new_table_entry(dbase, self, clientdata) end if not errtxt then retval.value.fields = cfe({ type="group", value={}, label="Table Fields", seq=2 }) + retval.value.nulls = cfe({ type="group", value={}, label="Null Value Table Fields", seq=3 }) + retval.value.defaults = cfe({ type="group", value={}, label="Default Value Table Fields", seq=4 }) 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 }) + retval.value.nulls.value[f] = cfe({ type="boolean", value=false, label=f, seq=i }) + retval.value.defaults.value[f] = cfe({ type="boolean", value=false, label=f, seq=i }) end end if connected then db.databasedisconnect() end @@ -166,6 +170,18 @@ function mymodule.get_new_table_entry(dbase, self, clientdata) return retval end +local function get_field_value(dbase, self, entry, field) + local retval + if entry.value.defaults and entry.value.defaults.value[field] and entry.value.defaults.value[field].value then + retval = "DEFAULT" + elseif entry.value.nulls and entry.value.nulls.value[field] and entry.value.nulls.value[field].value then + retval = "NULL" + elseif entry.value.fields and entry.value.fields.value[field] then + retval = "'"..dbase.escape(entry.value.fields.value[field].value).."'" + end + return retval +end + function mymodule.get_table_entry(dbase, self, clientdata) local db = dbase if type(dbase) == "function" then @@ -183,15 +199,20 @@ function mymodule.get_table_entry(dbase, self, clientdata) 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).."'" + fieldchecks[#fieldchecks+1] = f.."="..get_field_value(db, self, retval, f) 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 + --for n,v in pairs(entry[1]) do + for n,v in pairs(retval.value.fields.value) do + if not entry[1][n] then + retval.value.nulls.value[n].value = true + else + retval.value.fields.value[n].value = entry[1][n] + end if reversekeyfields[n] then retval.value.fields.value[n].readonly = true retval.value.fields.value[n].errtxt = nil @@ -235,35 +256,47 @@ function mymodule.update_table_entry(dbase, self, entry, action, create) 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).."'" + fieldchecks[#fieldchecks+1] = f.."="..get_field_value(db, self, entry, f) 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" + -- We want to catch syntax errors, because they just indicate the entry does not exist + local res2, err2 = pcall(function() + 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 - 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) + if not res2 and err2 then + if not create then + success = false + errtxt = err + for i,n in ipairs(keyfields) do + entry.value.fields.value[n].errtxt = "Entry does not exist" + end end end if success then + local sql 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) + names[#names+1] = n + values[#values+1] = get_field_value(db, self, entry, n) end end if create then - sql = "INSERT INTO "..db.escape(entry.value.table.value).." ("..table.concat(names, ", ")..") VALUES('"..table.concat(values, "', '").."')" + 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 ") + 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 @@ -314,8 +347,10 @@ function mymodule.get_delete_table_entry(dbase, self, clientdata) 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.nulls = cfe({ type="group", value={}, label="Null Value Table Fields", seq=3 }) for i,f in ipairs(keyfields) do retval.value.fields.value[f] = cfe({ label=f, seq=i }) + retval.value.nulls.value[f] = cfe({ type="boolean", value=false, label=f, seq=i }) end end if connected then db.databasedisconnect() end @@ -353,7 +388,7 @@ function mymodule.delete_table_entry(dbase, self, entry) 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).."'" + fieldchecks[#fieldchecks+1] = f.."="..get_field_value(db, self, entry, f) end local sql = "DELETE FROM "..db.escape(entry.value.table.value).." WHERE "..table.concat(fieldchecks, " AND ") db.runsqlcommand(sql) -- cgit v1.2.3