changeset 1407:1979cff9aad2

add sql/Web_query
author Franklin Schmidt <fschmidt@gmail.com>
date Fri, 20 Sep 2019 17:00:30 -0600
parents 8187ddb0e827
children 5b8f76e26ab7
files conv.txt examples/blog/src/lib/Db.luan src/luan/host/Util.luan src/luan/host/init.luan src/luan/modules/lucene/Web_search.luan src/luan/modules/sql/Sql.luan src/luan/modules/sql/Web_query.luan
diffstat 7 files changed, 161 insertions(+), 8 deletions(-) [+]
line wrap: on
line diff
--- a/conv.txt	Wed Sep 18 09:19:58 2019 -0600
+++ b/conv.txt	Fri Sep 20 17:00:30 2019 -0600
@@ -1,3 +1,5 @@
+Sql.luan query
+
 luan.lib
 Thread.schedule
 
--- a/examples/blog/src/lib/Db.luan	Wed Sep 18 09:19:58 2019 -0600
+++ b/examples/blog/src/lib/Db.luan	Fri Sep 20 17:00:30 2019 -0600
@@ -38,6 +38,6 @@
 Db.db = Db.new("site:/private/local/lucene")
 
 Db.db.restore_from_postgres()
-Thread.schedule( Db.db.check, { delay=0, repeating_delay=Time.period{minutes=1}, id="blog-db-check" } )
+Thread.schedule( Db.db.check, { delay=0, repeating_delay=Time.period{hours=1}, id="blog-db-check" } )
 
 return Db
--- a/src/luan/host/Util.luan	Wed Sep 18 09:19:58 2019 -0600
+++ b/src/luan/host/Util.luan	Fri Sep 20 17:00:30 2019 -0600
@@ -64,7 +64,7 @@
 		return
 	end
 	local db = database(pg_admin)
-	local exists = db.query("select rolname from pg_roles where rolname=?",domain)() ~= nil;
+	local exists = db.query("select rolname from pg_roles where rolname=?",domain).results() ~= nil;
 	--logger.info("exists "..exists)
 	if exists then
 		db.update( [[alter role "]]..domain..[[" with encrypted password ']]..password..[[']] )
--- a/src/luan/host/init.luan	Wed Sep 18 09:19:58 2019 -0600
+++ b/src/luan/host/init.luan	Fri Sep 20 17:00:30 2019 -0600
@@ -88,7 +88,7 @@
 		password = Io.password
 	}
 	local db = database(pg)
-	local exists = db.query("select datname from pg_database where datname=?",domain)() ~= nil;
+	local exists = db.query("select datname from pg_database where datname=?",domain).results() ~= nil;
 	--logger.info("exists "..exists)
 	if not exists then
 		db.update( [[create user "]]..spec.user..[[" with encrypted password ']]..spec.password..[[']] )
--- a/src/luan/modules/lucene/Web_search.luan	Wed Sep 18 09:19:58 2019 -0600
+++ b/src/luan/modules/lucene/Web_search.luan	Fri Sep 20 17:00:30 2019 -0600
@@ -3,7 +3,6 @@
 local pairs = Luan.pairs or error()
 local ipairs = Luan.ipairs or error()
 local range = Luan.range or error()
-local to_string = Luan.to_string or error()
 local stringify = Luan.stringify or error()
 local eval = Luan.eval or error()
 local Io = require "luan:Io.luan"
@@ -140,8 +139,8 @@
 	</head>
 	<body>
 		<h2>Lucene Results</h2>
-		<p><label>Query:</label> <b><%=html_encode(to_string(query))%></b></p>
-		<p><label>Sort:</label> <b><%=html_encode(to_string(sort))%></b></p>
+		<p><label>Query:</label> <b><%=html_encode(query)%></b></p>
+		<p><label>Sort:</label> <b><%=html_encode(sort)%></b></p>
 		<table>
 			<tr>
 				<th></th>
--- a/src/luan/modules/sql/Sql.luan	Wed Sep 18 09:19:58 2019 -0600
+++ b/src/luan/modules/sql/Sql.luan	Fri Sep 20 17:00:30 2019 -0600
@@ -3,6 +3,7 @@
 local error = Luan.error
 local new_error = Luan.new_error or error()
 local set_metatable = Luan.set_metatable or error()
+local range = Luan.range or error()
 local Database = require "java:luan.modules.sql.Database"
 local Logging = require "luan:logging/Logging.luan"
 local logger = Logging.logger "Sql"
@@ -32,6 +33,9 @@
 
 	function database.query(sql,...)
 		local rs = java_database.query(sql,...)
+		local query = {}
+		query.java = rs
+
 		local mt = {}
 		function mt.__index(_,key)
 			local rtn = rs.getObject(key)
@@ -39,7 +43,7 @@
 		end
 		local result = {}
 		set_metatable(result,mt)
-		return function()
+		function query.results()
 			if rs.isClosed() then
 				return nil
 			end
@@ -48,7 +52,18 @@
 				return nil
 			end
 			return result
-		end, rs
+		end
+
+		function query.column_names()
+			local meta = rs.getMetaData()
+			local names = {}
+			for i in range(1,meta.getColumnCount()) do
+				names[i] = meta.getColumnName(i)
+			end
+			return names
+		end
+
+		return query
 	end
 
 	return database
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/src/luan/modules/sql/Web_query.luan	Fri Sep 20 17:00:30 2019 -0600
@@ -0,0 +1,137 @@
+local Luan = require "luan:Luan.luan"
+local error = Luan.error
+local ipairs = Luan.ipairs or error()
+local stringify = Luan.stringify or error()
+local to_string = Luan.to_string or error()
+local Io = require "luan:Io.luan"
+local Http = require "luan:http/Http.luan"
+local Html = require "luan:Html.luan"
+local html_encode = Html.encode or error()
+local Sql = require "luan:sql/Sql.luan"
+
+
+local Web_query = {}
+
+local function style()
+%>
+			body {
+				font-family: sans-serif;
+				margin: 2em 5%;
+			}
+			h2 {
+				margin-bottom: .5em;
+			}
+			input, textarea {
+				margin-top: 1em;
+				font: inherit;
+			}
+			input[type="submit"] {
+				cursor: pointer;
+				padding: .5em;
+				border-radius: 4px;
+			}
+<%
+end
+
+local function form()
+%>
+<!doctype html>
+<html>
+	<head>
+		<title>SQL</title>
+		<style>
+<%			style() %>
+		</style>
+	</head>
+	<body>
+		<h2>SQL Query</h2>
+		<form>
+			<div>
+				<textarea name="query" cols=80 rows=10 autofocus></textarea>
+			</div>
+			<div>
+				<input type="submit">
+			</div>
+		</form>
+	</body>
+</html>
+<%
+end
+
+local function result(db_spec)
+	local query_str = Http.request.parameters.query
+	local db = Sql.database(db_spec)
+	local query = db.query(query_str)
+	local cols = query.column_names()
+%>
+<!doctype html>
+<html>
+	<head>
+		<title>SQL</title>
+		<style>
+<%			style() %>
+			table {
+				border-collapse: collapse;
+				font-size: smaller;
+			}
+			th, td {
+				text-align: left;
+				padding: .5em;
+				border: solid 1px #ddd;
+			}
+			pre {
+				font: inherit;
+			}
+		</style>
+	</head>
+	<body>
+		<h2>SQL Results</h2>
+		<p><b><pre><%=html_encode(query_str)%></pre></b></p>
+		<table>
+			<tr>
+<%
+			for _, col in ipairs(cols) do
+%>
+				<th><%=col%></th>
+<%
+			end
+%>
+			</tr>
+<%
+		for result in query.results do
+%>
+			<tr>
+<%
+			for _, col in ipairs(cols) do
+%>
+				<td><%=html_encode(to_string(result[col]))%></td>
+<%
+			end
+%>
+			</tr>
+<%
+		end
+%>
+		</table>
+	</body>
+</html>
+<%
+	db.close()
+end
+
+function Web_query.of(db_spec)
+	db_spec or error "db_spec is nil"
+
+	return function()
+		Io.stdout = Http.response.text_writer()
+		local query = Http.request.parameters.query
+		if Http.request.parameters.query ~= nil then
+			result(db_spec)
+		else
+			form()
+		end
+	end
+
+end
+
+return Web_query