summaryrefslogtreecommitdiffstats
diff options
context:
space:
mode:
authorTed Trask <ttrask01@yahoo.com>2011-01-08 19:54:34 +0000
committerTed Trask <ttrask01@yahoo.com>2011-01-08 19:54:34 +0000
commit7737dbd5def8fe3f38553e6e5c91bfc389fcae64 (patch)
tree6b8f4a5d052e80a113a3700a57f18b5fae34d9c9
parent0b38e3b87b7dad7b6f76ac8857b85120ae256a7a (diff)
downloadacf-kamailio-7737dbd5def8fe3f38553e6e5c91bfc389fcae64.tar.bz2
acf-kamailio-7737dbd5def8fe3f38553e6e5c91bfc389fcae64.tar.xz
Added actions for listing and editing database tables. Relys on kamctlrc.
-rw-r--r--kamailio-controller.lua21
-rw-r--r--kamailio-createtableentry-html.lsp14
-rw-r--r--kamailio-listtables-html.lsp14
-rw-r--r--kamailio-model.lua338
l---------kamailio-updatetableentry-html.lsp1
-rw-r--r--kamailio-viewtable-html.lsp48
-rw-r--r--kamailio.menu1
-rw-r--r--kamailio.roles4
8 files changed, 418 insertions, 23 deletions
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")
+%>
+
+<H1><%= html.html_escape(form.label) %></H1>
+<%
+ 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") %>
+
+<H1><%= html.html_escape(form.label) %></H1>
+<DL>
+<% for i,table in ipairs(form.value) do %>
+ <li>
+ <% if viewlibrary.check_permission("viewtable") then %>
+ <%= html.link{value = "viewtable?table=" .. table, label=table} %>
+ <% else %>
+ <%= html.html_escape(table) %>
+ <% end %>
+<% end %>
+</DL>
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) %>
+
+<H1><%= html.html_escape(form.label) %> - <%= html.html_escape(form.value.table.value) %></H1>
+<DL>
+<TABLE>
+ <TR style="background:#eee;font-weight:bold;">
+ <% if viewlibrary.check_permission("deletetableentry") or viewlibrary.check_permission("updatetableentry") then %>
+ <TD style="padding-right:20px;white-space:nowrap;" class="header">Action</TD>
+ <% end %>
+ <% for i,f in ipairs(form.value.fields.value) do %>
+ <TD style="padding-right:20px;white-space:nowrap;" class="header"><%= html.html_escape(f) %></TD>
+ <% end %>
+ </TR>
+
+<% for i,tableentry in ipairs(form.value.entries.value) do %>
+ <TR>
+ <% if viewlibrary.check_permission("deletetableentry") or viewlibrary.check_permission("updatetableentry") then %>
+ <TD style="padding-right:20px;white-space:nowrap;">
+ <% if viewlibrary.check_permission("updatetableentry") then %>
+ <form action="updatetableentry" method="POST">
+ <input class="hidden" type="hidden" name="table" value="<%= html.html_escape(form.value.table.value) %>">
+ <input class="hidden" type="hidden" name="id" value="<%= html.html_escape(tableentry.id) %>">
+ <input class="hidden" type="hidden" name="redir" value="<%= html.html_escape(page_info.orig_action.."?table="..form.value.table.value) %>">
+ <input class="submit" type="submit" value="Update"></form>
+ <% end %>
+ <% if viewlibrary.check_permission("deletetableentry") then %>
+ <form action="deletetableentry" method="POST">
+ <input class="hidden" type="hidden" name="table" value="<%= html.html_escape(form.value.table.value) %>">
+ <input class="hidden" type="hidden" name="id" value="<%= html.html_escape(tableentry.id) %>">
+ <input class="submit" type="submit" value="Delete"></form>
+ <% end %>
+ </TD>
+ <% end %>
+ <% for i,f in ipairs(form.value.fields.value) do %>
+ <TD><%= html.html_escape(tableentry[f]) %></TD>
+ <% end %>
+ </TR>
+<% end %>
+</TABLE>
+</DL>
+
+<% 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