From 7737dbd5def8fe3f38553e6e5c91bfc389fcae64 Mon Sep 17 00:00:00 2001 From: Ted Trask Date: Sat, 8 Jan 2011 19:54:34 +0000 Subject: Added actions for listing and editing database tables. Relys on kamctlrc. --- kamailio-controller.lua | 21 +++ kamailio-createtableentry-html.lsp | 14 ++ kamailio-listtables-html.lsp | 14 ++ kamailio-model.lua | 338 ++++++++++++++++++++++++++++++++++--- kamailio-updatetableentry-html.lsp | 1 + kamailio-viewtable-html.lsp | 48 ++++++ kamailio.menu | 1 + kamailio.roles | 4 +- 8 files changed, 418 insertions(+), 23 deletions(-) create mode 100644 kamailio-createtableentry-html.lsp create mode 100644 kamailio-listtables-html.lsp create mode 120000 kamailio-updatetableentry-html.lsp create mode 100644 kamailio-viewtable-html.lsp diff --git a/kamailio-controller.lua b/kamailio-controller.lua index a4bff67..69d3d1d 100644 --- a/kamailio-controller.lua +++ b/kamailio-controller.lua @@ -36,3 +36,24 @@ end function updateuser(self) return controllerfunctions.handle_form(self, function() return self.model.get_user(self.clientdata.username) end, self.model.update_user, self.clientdata, "Update", "Update User") end + +function listtables(self) + return self.model.list_tables() +end + +function viewtable(self) + return self.model.list_table_entries(self.clientdata.table) +end + +function deletetableentry(self) + return self:redirect_to_referrer(self.model.delete_table_entry(self.clientdata.table, self.clientdata.id)) +end + +function updatetableentry(self) + return controllerfunctions.handle_form(self, function() return self.model.get_table_entry(self.clientdata.table, self.clientdata.id) end, self.model.update_table_entry, self.clientdata, "Update", "Update Table Entry", "Entry updated") +end + +function createtableentry(self) + return controllerfunctions.handle_form(self, function() return self.model.get_table_entry(self.clientdata.table) end, self.model.create_table_entry, self.clientdata, "Create", "Create New Table Entry", "Entry created") +end + diff --git a/kamailio-createtableentry-html.lsp b/kamailio-createtableentry-html.lsp new file mode 100644 index 0000000..b0dabfa --- /dev/null +++ b/kamailio-createtableentry-html.lsp @@ -0,0 +1,14 @@ +<% local form, viewlibrary, page_info = ... +require("viewfunctions") +%> + +

<%= html.html_escape(form.label) %>

+<% + form.value.table.type = "hidden" + if page_info.action == "updatetableentry" and form.value.id then + form.value.id.readonly = true + elseif form.value.id then + form.value.id.type = "hidden" + end + displayform(form, nil, nil, page_info, 2) +%> diff --git a/kamailio-listtables-html.lsp b/kamailio-listtables-html.lsp new file mode 100644 index 0000000..6a117e4 --- /dev/null +++ b/kamailio-listtables-html.lsp @@ -0,0 +1,14 @@ +<% local form, viewlibrary, page_info, session = ... %> +<% require("viewfunctions") %> + +

<%= html.html_escape(form.label) %>

+
+<% for i,table in ipairs(form.value) do %> +
  • + <% if viewlibrary.check_permission("viewtable") then %> + <%= html.link{value = "viewtable?table=" .. table, label=table} %> + <% else %> + <%= html.html_escape(table) %> + <% end %> +<% end %> +
    • diff --git a/kamailio-model.lua b/kamailio-model.lua index 8d4ec97..63be94a 100644 --- a/kamailio-model.lua +++ b/kamailio-model.lua @@ -11,8 +11,120 @@ require("validator") local processname = "kamailio" local packagename = "kamailio" local baseurl = "/etc/kamailio" +local kamctlrc_file = "/etc/kamailio/kamctlrc" local path = "PATH=/usr/local/bin:/usr/bin:/bin:/usr/local/sbin:/usr/sbin:/sbin " +local env +local con +local DBENGINE + +-- ################################################################################ +-- DATABASE FUNCTIONS + +local function assert (v, m) + if not v then + m = m or "Assertion failed!" + error(m, 0) + end + return v, m +end + +-- Escape special characters in sql statements +local escape = function(sql) + sql = sql or "" + sql = string.gsub(sql, "'", "''") + return string.gsub(sql, "\\", "\\\\") +end + +local databaseconnect = function() + if not con then + -- parse the kamctlrc file + local config = format.parse_ini_file(fs.read_file(kamctlrc_file), "") or {} + if not config.DBENGINE then + error("Database engine not specified, please setup one in the config script "..kamctlrc_file) + end + + -- create environment object + if config.DBENGINE == "MYSQL" or config.DBENGINE == "mysql" or config.DBENGINE == "MySQL" then + error("MYSQL database not supported") + elseif config.DBENGINE == "PGSQL" or config.DBENGINE == "pgsql" or config.DBENGINE == "postgres" or config.DBENGINE == "postgresql" or config.DBENGINE == "POSTGRESQL" then + require("luasql.postgres") + env = assert (luasql.postgres()) + DBENGINE = "PGSQL" + elseif config.DBENGINE == "ORACLE" or config.DBENGINE == "oracle" or config.DBENGINE == "Oracle" then + error("ORACLE database not supported") + elseif config.DBENGINE == "DBTEXT" or config.DBENGINE == "dbtext" or config.DBENGINE == "textdb" then + error("DBTEXT database not supported") + elseif config.DBENGINE == "DB_BERKELEY" or config.DBENGINE == "db_berkeley" or config.DBENGINE == "BERKELEY" or config.DBENGINE == "berkeley" then + error("BERKELEY database not supported") + else + error("Unknown database engine "..config.DBENGINE) + end + + -- connect to data source + con = assert(env:connect(config.DBNAME or "", config.DBRWUSER or "", config.DBRWPW or "")) + return true + end + return false +end + +local databasedisconnect = function() + if env then + env:close() + env = nil + end + if con then + con:close() + con = nil + end +end + +local runsqlcommand = function(sql) +logevent(sql) + assert(con:execute(sql)) +end + +local getselectresponse = function(sql) + local retval = {} +logevent(sql) + local cur = assert (con:execute(sql)) + local row = cur:fetch ({}, "a") + while row do + local tmp = {} + for name,val in pairs(row) do + tmp[name] = val + end + retval[#retval + 1] = tmp + row = cur:fetch (row, "a") + end + cur:close() + return retval +end + +local listtables = function() + local result = {} + if DBENGINE == "PGSQL" then + local tab = getselectresponse("SELECT tablename FROM pg_tables WHERE tablename !~* 'pg_*' ORDER BY tablename ASC") + for i,t in ipairs(tab) do + result[#result+1] = t.tablename + end + else + -- untested + result = con:tables() + end + return result +end + +local listcolumns = function(table) + local result = {} + if DBENGINE == "PGSQL" then + local col = getselectresponse("SELECT a.attname AS field FROM pg_class c, pg_attribute a, pg_type t WHERE c.relname = '"..table.."' AND a.attnum > 0 AND a.attrelid = c.oid AND a.atttypid = t.oid ORDER BY a.attnum") + for i,c in ipairs(col) do + result[#result+1] = c.field + end + end + return result +end -- ################################################################################ -- LOCAL FUNCTIONS @@ -71,36 +183,47 @@ function list_files() return cfe({ type="structure", value=retval, label="List of Kamailio files" }) end -function list_users() - -- Database format: id | username | domain | password | email_address | ha1 | ha1b | rpid - local cmd = path .. "kamctl db show subscriber" +local function parse_db_show(table) + local cmd = path .. "kamctl db show "..(table or "") local f = io.popen(cmd) - -- These settings work for Postgres database - local skiplines = 2 - local delimiter = "%s*|%s*" - local dbengine = format.parse_ini_file(fs.read_file("/etc/kamailio/kamctlrc") or "", "", "DBENGINE") - if dbengine == "DBTEXT" then - skiplines = 0 - delimiter = "\'?%s*,%s*\'?" - end - + -- These settings work for Postgres and DBTEXT database + local delimiter = "\'?%s*[,|]%s*\'?" local results = {} + local errtxt for line in f:lines() do - if skiplines > 0 then - skiplines = skiplines-1 + if #results == 0 and string.match(line, "^ERROR:") then + errtxt = line + results = nil + break + end + if string.match(line, "^[+-]+$") then + results = {} else local words = format.string_to_table(line, delimiter) - if #words > 1 then - local temp = {username = words[2], - --domain = words[3], - password = words[4], - --email_address = words[5] - } - results[#results+1] = temp + if words and #words > 0 then + results[#results+1] = words end end end f:close() + return results, errtxt +end + +function list_users() + -- Database format: id | username | domain | password | email_address | ha1 | ha1b | rpid + local results = {} + local r, errtxt + r, errtxt = parse_db_show("subscriber") + for i,words in ipairs(r or {}) do + if #words > 1 then + local temp = {username = words[2], + --domain = words[3], + password = words[4], + --email_address = words[5] + } + results[#results+1] = temp + end + end table.sort(results, function(a,b) return a.username < b.username end) return cfe({type="list", value=results, label="Kamailio Users"}) end @@ -166,3 +289,176 @@ function update_user(user) return user end + +function list_tables() + local retval = {} + local errtxt + -- Get the devices from the DB + local res, err = pcall(function() + local connected = databaseconnect() + retval = listtables() + if connected then databasedisconnect() end + end) + if not res and err then + errtxt = err + end + + return cfe({ type="list", value=retval, label="List of Database Tables", errtxt=errtxt }) +end + +function list_table_entries(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 + -- Get the devices from the DB + local res, err = pcall(function() + local connected = databaseconnect() + retval.entries.value = getselectresponse("SELECT * FROM "..table.." ORDER BY id ASC") or {} + retval.fields.value = listcolumns(table) or {} + if connected then databasedisconnect() end + end) + if not res and err then + errtxt = err + end + + return cfe({ type="group", value=retval, label="Database Table Entries", errtxt=errtxt }) +end + +function get_table_entry(table, id) + local retval = {} + retval.table = cfe({ value=table or "", label="Table", errtxt="Table does not exist" }) + local errtxt = "Table does not exist" + if table and table ~= "" then + local res, err = pcall(function() + local connected = databaseconnect() + local tables = listtables() + for i,t in ipairs(tables) do + if t == table then + retval.table.errtxt = nil + errtxt = nil + break + end + end + if not errtxt then + local fields = listcolumns(table) + for i,f in ipairs(fields) do + retval[f] = cfe({ label=f, seq=i }) + end + if id and id ~= "" then + local entry = getselectresponse("SELECT * FROM "..table.." WHERE id='"..escape(id).."'") + if entry and #entry > 0 then + for n,v in pairs(entry[1]) do + if retval[n] then retval[n].value = v end + end + end + end + end + if connected then databasedisconnect() end + end) + if not res and err then + errtxt = err + end + end + + return cfe({ type="group", value=retval, label="Database Table Entry", errtxt=errtxt }) +end + +function create_table_entry(entry) + return update_table_entry(entry, true) +end + +function update_table_entry(entry, create) + 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 then + success = false + entry.value.table.errtxt = "Table does not exist" + end + if not create then + if not entry.value.id then + success = false + elseif not entry.value.id.value or entry.value.id.value == "" then + success = false + entry.value.id.errtxt = "Invalid id" + end + end + if success then + local res, err = pcall(function() + local connected = databaseconnect() + if success and not create then + local sql = "SELECT * FROM "..entry.value.table.value.." WHERE id='"..escape(entry.value.id.value).."'" + local tmp = getselectresponse(sql) + if not tmp or #tmp == 0 then + success = false + errtxt = "Entry does not exist" + end + end + if success then + local names = {} + local values = {} + for n,v in pairs(entry.value) do + if n ~= "table" and n ~= "id" then + names[#names+1] = n + values[#values+1] = escape(v.value) + end + end + if create then + sql = "INSERT INTO "..entry.value.table.value.." ("..table.concat(names, ", ")..") VALUES('"..table.concat(values, "', '").."')" + else + sql = "UPDATE "..entry.value.table.value.." SET ("..table.concat(names, ", ")..") = ('"..table.concat(values, "', '").."') WHERE id='"..escape(entry.value.id.value).."'" + end + runsqlcommand(sql) + end + if connected then 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 delete_table_entry(table, id) + local result = "" + local errtxt + if not table or table == "" then + errtxt = "Invalid table" + elseif not id or id == "" then + errtxt = "Invalid entry" + else + local res, err = pcall(function() + local connected = databaseconnect() + errtxt = "Invalid table" + local tables = listtables() + for i,t in ipairs(tables) do + if t == table then + errtxt = nil + break + end + end + if not errtxt then + local sql = "DELETE FROM "..table.." WHERE id='"..escape(id).."'" + runsqlcommand(sql) + result = "Entry Deleted" + end + if connected then databasedisconnect() end + end) + if not res and err then + errtxt = err + end + end + + return cfe({ value=result, errtxt=errtxt, label="Delete Entry Result" }) +end diff --git a/kamailio-updatetableentry-html.lsp b/kamailio-updatetableentry-html.lsp new file mode 120000 index 0000000..ee06465 --- /dev/null +++ b/kamailio-updatetableentry-html.lsp @@ -0,0 +1 @@ +kamailio-createtableentry-html.lsp \ No newline at end of file diff --git a/kamailio-viewtable-html.lsp b/kamailio-viewtable-html.lsp new file mode 100644 index 0000000..7d0aa41 --- /dev/null +++ b/kamailio-viewtable-html.lsp @@ -0,0 +1,48 @@ +<% local form, viewlibrary, page_info, session = ... %> +<% require("viewfunctions") %> + +<% displaycommandresults({"deletetableentry", "updatetableentry"}, session) %> +<% displaycommandresults({"createtableentry"}, session, true) %> + +

    <%= html.html_escape(form.label) %> - <%= html.html_escape(form.value.table.value) %>

    +
    + + + <% if viewlibrary.check_permission("deletetableentry") or viewlibrary.check_permission("updatetableentry") then %> + + <% end %> + <% for i,f in ipairs(form.value.fields.value) do %> + + <% end %> + + +<% for i,tableentry in ipairs(form.value.entries.value) do %> + + <% if viewlibrary.check_permission("deletetableentry") or viewlibrary.check_permission("updatetableentry") then %> + + <% end %> + <% for i,f in ipairs(form.value.fields.value) do %> + + <% end %> + +<% end %> +
    Action<%= html.html_escape(f) %>
    + <% if viewlibrary.check_permission("updatetableentry") then %> +
    + + + +
    + <% end %> + <% if viewlibrary.check_permission("deletetableentry") then %> +
    + + +
    + <% end %> +     		<%= html.html_escape(tableentry[f]) %>
    +
    + +<% if viewlibrary and viewlibrary.dispatch_component and viewlibrary.check_permission("createtableentry") then + viewlibrary.dispatch_component("createtableentry", {table=form.value.table.value}) +end %> diff --git a/kamailio.menu b/kamailio.menu index 607387f..4a9a3e5 100644 --- a/kamailio.menu +++ b/kamailio.menu @@ -2,4 +2,5 @@ # Cat Group Tab Action Applications 86Kamailio Status status Applications 86Kamailio Users listusers +Applications 86Kamailio Database listtables Applications 86Kamailio Expert listfiles diff --git a/kamailio.roles b/kamailio.roles index 111828b..15add79 100644 --- a/kamailio.roles +++ b/kamailio.roles @@ -1,4 +1,4 @@ USER=kamailio:status,kamailio:startstop,kamailio:listusers -EDITOR=kamailio:createuser,kamailio:updateuser,kamailio:deleteuser +EDITOR=kamailio:createuser,kamailio:updateuser,kamailio:deleteuser,kamailio:listtables,kamailio:viewtable,kamailio:deletetableentry,kamailio:updatetableentry,kamailio:createtableentry EXPERT=kamailio:listfiles,kamailio:edit -ADMIN=kamailio:status,kamailio:startstop,kamailio:listusers,kamailio:createuser,kamailio:updateuser,kamailio:deleteuser,kamailio:listfiles,kamailio:edit +ADMIN=kamailio:status,kamailio:startstop,kamailio:listusers,kamailio:createuser,kamailio:updateuser,kamailio:deleteuser,kamailio:listfiles,kamailio:edit,kamailio:listtables,kamailio:viewtable,kamailio:deletetableentry,kamailio:updatetableentry,kamailio:createtableentry -- cgit v1.2.3