Add code to determine the primary key fields and use them to uniquely identify rows

Previously, this assumed 'id' was the primary key column as inherited from the kamailio code

2 files changed, 61 insertions, 54 deletions

diff --git a/db-viewtable-html.lsp b/db-viewtable-html.lsp
index 283d624..3bde35e 100644
--- a/db-viewtable-html.lsp
+++ b/db-viewtable-html.lsp
@@ -65,9 +65,9 @@ if form.value.connection then
 	formvalues.connection = form.value.connection
 	for n,v in pairs(form.value.connection.value) do v.type="hidden" end
 end
-if form.value.fields then
+if form.value.keyfields then
 	formvalues.fields = cfe({ type="group", value={} })
-	for i,f in ipairs(form.value.fields.value) do
+	for i,f in ipairs(form.value.keyfields.value) do
 		formvalues.fields.value[f] = cfe({ type="hidden" })
 	end
 end
@@ -78,7 +78,13 @@ formvalues.redir = redir
 <% for i,tableentry in ipairs(form.value.entries.value) do %>
 	<tr>
 	<% if viewlibrary.check_permission("deletetableentry") or viewlibrary.check_permission("updatetableentry") then %>
-		<% formvalues.fields.value.id.value = tableentry.id %>
+		<%
+		if formvalues.fields then
+			for i,f in ipairs(form.value.keyfields.value) do
+				formvalues.fields.value[f].value = tableentry[f]
+			end
+		end
+		%>
 		<td>
 	<% if viewlibrary.check_permission("updatetableentry") then %>
 		<% htmlviewfunctions.displayitem(cfe({type="link", value=formvalues, label="", option="Update", action="updatetableentry"}), page_info, -1) %>
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