Moved pagination into model and greatly sped up DID listing.

3 files changed, 97 insertions, 25 deletions

diff --git a/did-controller.lua b/did-controller.lua
index 1d243e4..79d9a6d 100644
--- a/did-controller.lua
+++ b/did-controller.lua
@@ -7,17 +7,17 @@ default_action = "viewrecords"
 
 function listuseddefinitions(self)
 	return self.model.getuseddefinitionlist(self.clientdata.did, self.clientdata.extension,
-		self.clientdata.identification, self.clientdata.description, self.clientdata.department)
+		self.clientdata.identification, self.clientdata.description, self.clientdata.department, self.clientdata.page)
 end
 
 function listunuseddefinitions(self)
 	return self.model.getunuseddefinitionlist(self.clientdata.did, self.clientdata.identification,
-		self.clientdata.description, self.clientdata.department)
+		self.clientdata.description, self.clientdata.department, self.clientdata.page)
 end
 
 function listdefinitions(self)
 	return self.model.getdefinitionlist(self.clientdata.did, self.clientdata.extension,
-		self.clientdata.identification, self.clientdata.description, self.clientdata.department)
+		self.clientdata.identification, self.clientdata.description, self.clientdata.department, self.clientdata.page)
 end
 
 function searchdefinitionlist(self)
diff --git a/did-html.lsp b/did-html.lsp
index 31f8155..c94d023 100644
--- a/did-html.lsp
+++ b/did-html.lsp
@@ -2,7 +2,9 @@
 require("viewfunctions")
 %>
 
-<% local subdata, pagedata = paginate(data.value.definitions.value, page_info.clientdata, 15) %>
+<% -- local subdata, pagedata = paginate(data.value.definitions.value, page_info.clientdata, 15) %>
+<% local subdata = data.value.definitions.value %>
+<% local pagedata = data.value.pagedata.value %>
 
 <script type="text/javascript">
 function newPopup(url) {
diff --git a/did-model.lua b/did-model.lua
index c18e376..121fbf5 100644
--- a/did-model.lua
+++ b/did-model.lua
@@ -10,6 +10,8 @@ require("luasql.postgres")
 -- "SELECT extension FROM pubdid WHERE did='$1' AND 'now'>=starttime AND 'now'<endtime ORDER BY stale LIMIT 1"
 -- To update pubdid each night at midnight, use cron and the didpublish script
 
+local pagesize = 15
+
 local DatabaseName = "did"
 local DatabaseOwner = "didowner"
 local DatabaseUser = "diduser"
@@ -204,7 +206,14 @@ local generatewhereclause = function(did, extension, identification, description
 	if #where > 0 then
 		sql = " " .. (clause or "WHERE") .. " " .. table.concat(where, " AND ")
 	end
-APP.logevent(sql)
+	return sql
+end
+
+local generatelimitclause = function(page)
+	local sql = ""
+	if page and tonumber(page) and tonumber(page) > 0 then
+		sql = " LIMIT "..pagesize.." OFFSET "..((page-1)*pagesize)
+	end
 	return sql
 end
 
@@ -221,28 +230,70 @@ local getdefinitionentries = function(sql)
 	return entries
 end
 
-local listunuseddefinitions = function(did, identification, description, department)
+local listunuseddefinitions = function(did, identification, description, department, page)
 	local where = generatewhereclause(did, nil, identification, description, department, "AND")
-	local sql = "SELECT * FROM definition WHERE did NOT IN (SELECT did FROM rule)"..where.." ORDER BY did"
+	local sql = "SELECT * FROM definition WHERE did NOT IN (SELECT did FROM rule)"..where.." ORDER BY did"..generatelimitclause(page)
 	return getdefinitionentries(sql)
 end
 
+local countunuseddefinitions = function(did, identification, description, department)
+	local where = generatewhereclause(did, nil, identification, description, department, "AND")
+	local sql = "SELECT count(*) FROM definition WHERE did NOT IN (SELECT did FROM rule)"..where
+	cur = assert (con:execute(sql))
+	local count = cur:fetch()
+	cur:close()
+	return count
+end
+
 -- Lists only the definitions that have rules, this also allows us to select based upon extension
-local listuseddefinitions = function(did, extension, identification, description, department)
+local listuseddefinitions = function(did, extension, identification, description, department, page)
 	local where = string.gsub(generatewhereclause(did, extension, identification, description, department, "HAVING"), "extension", "array_to_string(array_accum(rule.extension), ', ')")
 	-- Combine with rules to get extensions, this will drop all dids that don't have rules
 	-- Relies on this custom aggregate function being defined
 	-- local sql = "CREATE AGGREGATE array_accum(anyelement)(sfunc = array_append, stype = anyarray, initcond = '{}')"
 	local sql = "SELECT definition.did, identification, department, description, array_to_string(array_accum(rule.extension), ', ') AS extension FROM definition,rule WHERE definition.did=rule.did"
-	sql = sql.." GROUP BY definition.did, identification, department, description"..where.." ORDER BY definition.did"
+	sql = sql.." GROUP BY definition.did, identification, department, description"..where.." ORDER BY definition.did"..generatelimitclause(page)
+	return getdefinitionentries(sql)
+end
+
+-- Counts only the definitions that have rules, this also allows us to select based upon extension
+local countuseddefinitions = function(did, extension, identification, description, department)
+	local where = string.gsub(generatewhereclause(did, extension, identification, description, department, "HAVING"), "extension", "array_to_string(array_accum(rule.extension), ', ')")
+	-- Combine with rules to get extensions, this will drop all dids that don't have rules
+	-- Relies on this custom aggregate function being defined
+	-- local sql = "CREATE AGGREGATE array_accum(anyelement)(sfunc = array_append, stype = anyarray, initcond = '{}')"
+	local sql = "SELECT definition.did FROM definition,rule WHERE definition.did=rule.did"
+	sql = sql.." GROUP BY definition.did"..where
+	sql = "SELECT count(*) from ("..sql..") AS temp"
+	cur = assert (con:execute(sql))
+	local count = cur:fetch()
+	cur:close()
+	return count
+end
+
+local listdefinitionsandextensions = function(did, identification, description, department, page)
+	local where = generatewhereclause(did, nil, identification, description, department, "HAVING")
+	-- Combine with rules to get extensions, use LEFT JOIN to not drop all dids that don't have rules
+	-- Relies on this custom aggregate function being defined
+	-- local sql = "CREATE AGGREGATE array_accum(anyelement)(sfunc = array_append, stype = anyarray, initcond = '{}')"
+	local sql = "SELECT definition.did, identification, department, description, array_to_string(array_accum(rule.extension), ', ') AS extension FROM definition LEFT JOIN rule ON definition.did=rule.did"
+	sql = sql.." GROUP BY definition.did, identification, department, description"..where.." ORDER BY definition.did"..generatelimitclause(page)
 	return getdefinitionentries(sql)
 end
 
-local listdefinitions = function(did, identification, description, department)
-	local sql = "SELECT * FROM definition"..generatewhereclause(did, nil, identification, description, department).." ORDER BY did"
+local listdefinitions = function(did, identification, description, department, page)
+	local sql = "SELECT * FROM definition"..generatewhereclause(did, nil, identification, description, department).." ORDER BY did"..generatelimitclause(page)
 	return getdefinitionentries(sql)
 end
 
+local countdefinitions = function(did, identification, description, department)
+	local sql = "SELECT count(*) FROM definition"..generatewhereclause(did, nil, identification, description, department)
+	cur = assert (con:execute(sql))
+	local count = cur:fetch()
+	cur:close()
+	return count
+end
+
 local listdefs = function(did, identification, description, department)
 	local sql = "SELECT did FROM definition"..generatewhereclause(did, nil, identification, description, department).." ORDER BY did"
 	local entries = {}
@@ -632,6 +683,7 @@ local function createdefinitionlist(did, extension, identification, description,
 	retval.identification = cfe({ value=identification or "", label="Identification search string" })
 	retval.description = cfe({ value=description or "", label="Description search string" })
 	retval.department = cfe({ value=department or "", label="Department search string" })
+	retval.pagedata = cfe({ type="table", value={numpages=1, page=1, pagesize=pagesize, num=0}, label="Pagination Data"})
 	return cfe({ type="group", value=retval, label="DID Number List" })
 end
 
@@ -690,15 +742,34 @@ local describeruleschange = function(oldrules, newrules)
 	end
 end
 
+local determinepagedata = function(count, page)
+	count = tonumber(count) or 0
+	local page_data = { numpages=1, page=1, pagesize=pagesize, num=count }
+	if count > pagesize then
+		page_data.numpages = math.floor((count + pagesize -1)/pagesize)
+		if page and tonumber(page) then
+			page_data.page = tonumber(page)
+		end
+		if page_data.page > page_data.numpages then
+			page_data.page = page_data.numpages
+		elseif page_data.page < 0 then
+			page_data.page = 0
+		end
+	end
+	return page_data
+end
+
 -- ################################################################################
 -- PUBLIC FUNCTIONS
 
-function getuseddefinitionlist(did, extension, identification, description, department)
+function getuseddefinitionlist(did, extension, identification, description, department, page)
 	local def = createdefinitionlist(stripdash(did), extension, identification, description, department)
 	def.label = "Used "..def.label
 	local res, err = pcall(function()
 		local connected = databaseconnect(DatabaseUser)
-		def.value.definitions.value = listuseddefinitions(stripdash(did), extension, identification, description, department)
+		local count = countuseddefinitions(stripdash(did), extension, identification, description, department)
+		def.value.pagedata.value = determinepagedata(count, page)
+		def.value.definitions.value = listuseddefinitions(stripdash(did), extension, identification, description, department, def.value.pagedata.value.page)
 		if connected then databasedisconnect() end
 	end)
 	if not res then
@@ -710,13 +781,15 @@ function getuseddefinitionlist(did, extension, identification, description, depa
 	return def
 end
 
-function getunuseddefinitionlist(did, identification, description, department)
+function getunuseddefinitionlist(did, identification, description, department, page)
 	local def = createdefinitionlist(stripdash(did), nil, identification, description, department)
 	def.value.extension = nil
 	def.label = "Unused "..def.label
 	local res, err = pcall(function()
 		local connected = databaseconnect(DatabaseUser)
-		def.value.definitions.value = listunuseddefinitions(stripdash(did), identification, description, department)
+		local count = countunuseddefinitions(stripdash(did), identification, description, department)
+		def.value.pagedata.value = determinepagedata(count, page)
+		def.value.definitions.value = listunuseddefinitions(stripdash(did), identification, description, department, def.value.pagedata.value.page)
 		if connected then databasedisconnect() end
 	end)
 	if not res then
@@ -728,22 +801,19 @@ function getunuseddefinitionlist(did, identification, description, department)
 	return def
 end
 
-function getdefinitionlist(did, extension, identification, description, department)
+function getdefinitionlist(did, extension, identification, description, department, page)
 	local def = createdefinitionlist(stripdash(did), extension, identification, description, department)
 	--def.value.extension = nil
 	local res, err = pcall(function()
 		local connected = databaseconnect(DatabaseUser)
 		if def.value.extension.value == "" then
-			def.value.definitions.value = listdefinitions(stripdash(did), identification, description, department)
+			local count = countdefinitions(stripdash(did), identification, description, department)
+			def.value.pagedata.value = determinepagedata(count, page)
+			def.value.definitions.value = listdefinitionsandextensions(stripdash(did), identification, description, department, def.value.pagedata.value.page)
 		else
-			def.value.definitions.value = listuseddefinitions(stripdash(did), extension, identification, description, department)
---[[			local tmp = listunuseddefinitions(stripdash(did), identification, description, department)
-			for i,val in ipairs(tmp) do
-				val.extension = ""
-				table.insert(def.value.definitions.value, val)
-			end
-			table.sort(def.value.definitions.value, function (a,b) return (a.did < b.did) end )
---]]	
+			local count = countuseddefinitions(stripdash(did), extension, identification, description, department)
+			def.value.pagedata.value = determinepagedata(count, page)
+			def.value.definitions.value = listuseddefinitions(stripdash(did), extension, identification, description, department, def.value.pagedata.value.page)
 		end
 		if connected then databasedisconnect() end
 	end)