1mg.write("HTTP/1.0 200 OK\r\nContent-Type: text/html\r\n\r\n")
2
3mg.write([[<html>
4<head>
5<title>Lua SQLite database test</title>
6<style>
7table, th, td {
8    border: 1px solid black;
9    border-collapse: collapse;
10    border-spacing: 5px;
11}
12th, td {
13    padding: 5px;
14    text-align: left;
15}
16</style>
17</head>
18<body>
19<p>This is Lua script example 1, served by the
20<a href="https://github.com/civetweb/civetweb">CivetWeb web server</a>,
21version ]] .. mg.version .. [[.
22</p><p>
23The following features are available:
24<ul>
25]])
26
27  mg.write('<li><a href="http://www.lua.org/docs.html">' .. _VERSION .. "</a> server pages</li>")
28  if sqlite3 then
29    mg.write('<li><a href="http://lua.sqlite.org/index.cgi/doc/tip/doc/lsqlite3.wiki">sqlite3</a> binding</li>')
30  end
31  if lfs then
32    mg.write('<li><a href="https://keplerproject.github.io/luafilesystem/manual.html">lua file system</a></li>')
33  end
34
35
36mg.write("</ul></p>\r\n")
37mg.write("<p> Today is " .. os.date("%A") .. "</p>\r\n")
38mg.write("<p> URI is " .. mg.request_info.uri .. "</p>\r\n")
39
40mg.write("<p>\r\n<pre>\r\n")
41
42-- Open database
43local db, errcode, errmsg = sqlite3.open('requests.db')
44
45if db then
46
47  -- Note that the data base is located in the current working directory
48  -- of the process if no other path is given here.
49
50  -- Setup a trace callback, to show SQL statements we'll be executing.
51  -- db:trace(function(data, sql) mg.write('Executing: ', sql: '\n') end, nil)
52
53  -- Create a table if it is not created already
54  db:exec([[
55    CREATE TABLE IF NOT EXISTS requests (
56      id INTEGER PRIMARY KEY AUTOINCREMENT,
57      timestamp NOT NULL,
58      method NOT NULL,
59      uri NOT NULL,
60      addr,
61      civetwebversion,
62      luaversion,
63      aux
64    );
65  ]])
66
67  -- Add colums to table created with older version
68  db:exec("ALTER TABLE requests ADD COLUMN civetwebversion;")
69  db:exec("ALTER TABLE requests ADD COLUMN luaversion;")
70  db:exec("ALTER TABLE requests ADD COLUMN aux;")
71
72  -- Add entry about this request
73  local stmt = db:prepare(
74    'INSERT INTO requests VALUES(NULL, datetime("now"), ?, ?, ?, ?, ?, ?);');
75  stmt:bind_values(mg.request_info.request_method,
76                   mg.request_info.uri,
77                   mg.request_info.remote_port,
78                   mg.version,
79                   _VERSION,
80                   ""
81                   )
82  stmt:step()
83  stmt:finalize()
84
85  -- Show all previous records
86  mg.write('<table>\n')
87  mg.write("<tr>\n")
88  mg.write("<th>id</th>\n")
89  mg.write("<th>timestamp</th>\n")
90  mg.write("<th>method</th>\n")
91  mg.write("<th>uri</th>\n")
92  mg.write("<th>addr</th>\n")
93  mg.write("<th>civetweb</th>\n")
94  mg.write("<th>lua</th>\n")
95  mg.write("<th>aux</th>\n")
96  mg.write("</tr>\n")
97
98  stmt = db:prepare('SELECT * FROM requests ORDER BY id DESC;')
99  while stmt:step() == sqlite3.ROW do
100    local v = stmt:get_values()
101    mg.write("<tr>\n")
102    local i = 1
103    while (v[i]) do
104      mg.write("<td>" .. v[i] .. "</td>\n")
105      i = i+1
106    end
107    mg.write("</tr>\n")
108  end
109
110  mg.write("</table>\n")
111
112  -- Close database
113  db:close()
114
115else
116
117  mg.write("DB error:\n")
118  mg.write("code = " .. tostring(errcode) .. "\n")
119  mg.write("msg = " .. tostring(msg) .. "\n")
120
121end
122
123mg.write([[
124</pre>
125</p>
126</body>
127</html>
128]])
129
130