1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
|
#!/usr/bin/env lem
--
-- This file is part of lem-postgres.
-- Copyright 2011 Emil Renner Berthing
-- Copyright 2013 Asbjørn Sloth Tønnesen
--
-- lem-postgres is free software: you can redistribute it and/or
-- modify it under the terms of the GNU General Public License as
-- published by the Free Software Foundation, either version 3 of
-- the License, or (at your option) any later version.
--
-- lem-postgres is distributed in the hope that it will be useful,
-- but WITHOUT ANY WARRANTY; without even the implied warranty of
-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
-- GNU General Public License for more details.
--
-- You should have received a copy of the GNU General Public License
-- along with lem-postgres. If not, see <http://www.gnu.org/licenses/>.
--
package.path = '?.lua;'..package.path
package.cpath = '?.so;'..package.cpath
print("Entering " .. arg[0])
local prettyprint
do
local write, format, tostring = io.write, string.format, tostring
function prettyprint(t)
local widths, columns = {}, #t[0]
for i = 1, columns do
widths[i] = 0
end
for i = 0, #t do
local row = t[i]
for j = 1, columns do
local value = row[j]
if value and #value > widths[j] then
widths[j] = #value
end
end
end
for i = 1, #widths do
widths[i] = '%-' .. tostring(widths[i] + 1) .. 's';
end
for i = 0, #t do
local row = t[i]
for j = 1, columns do
write(format(widths[j], row[j] or 'NULL'))
end
write('\n')
end
end
end
local utils = require 'lem.utils'
local postgres = require 'lem.postgres'
local dbconnstr = [[
host=localhost
user=myuser
password=mypasswd
dbname=mydb
]]
local db = assert(postgres.connect(dbconnstr))
assert(db:exec(
'CREATE TEMPORARY TABLE mytable (id serial PRIMARY KEY, name TEXT, foo integer)'))
assert(db:exec("COPY mytable (name, foo) FROM STDIN (delimiter ',')"))
assert(db:put('alpha,1\n'))
assert(db:put('beta,2\n'))
assert(db:put('gamma,4\n'))
assert(db:put('delta,8\n'))
assert(db:put('epsilon,\\N\n'))
assert(db:done())
local res = assert(db:exec('SELECT * FROM mytable WHERE id = $1', '1'))
prettyprint(res)
local res1, res2 = assert(db:exec([[
SELECT count(id) FROM mytable;
SELECT * FROM mytable WHERE id = 2;
]]));
prettyprint(res2)
assert(db:prepare('mystmt', 'SELECT * FROM mytable WHERE id = $1'))
local res = assert(db:run('mystmt', '3'))
prettyprint(res)
assert(db:prepare('insert', 'INSERT INTO mytable (name, foo) VALUES ($1, $2)'))
assert(db:run('insert', 'zeta', 32))
assert(db:run('insert', 'eta', nil))
assert(db:exec("COPY mytable TO STDIN (format csv, null '\\N')"))
print("\nDumping CSV:")
while true do
local row = assert(db:get())
if type(row) ~= 'string' then break end
io.write(row)
end
assert(db:exec('DROP TABLE mytable'))
assert(db:exec('LISTEN mytest;'))
assert(db:exec('NOTIFY mytest;'))
assert(db:exec("NOTIFY mytest, 'foo';"))
db:set_notifier(function(ev, payload, pid)
print('notification', ev, payload, pid)
end)
assert(db:exec('SELECT pg_notify($1, $2);', 'mytest', 'bar'))
assert(db:exec('SELECT pg_notify($1, $2), pg_notify($1, $3);', 'mytest', 'foo', 'bar'))
db:set_notifier(function(ev, payload, pid)
print('\nnotification', ev, payload, pid)
end)
utils.spawn(function()
local sleeper = utils.newsleeper()
for i=1,19 do
sleeper:sleep(0.20)
io.write('.')
end
print('')
end)
utils.spawn(function()
local sleeper = utils.newsleeper()
local db2 = assert(postgres.connect(dbconnstr))
for i=1,3 do
sleeper:sleep(1)
assert(db2:exec("NOTIFY mytest, 'foo from secondary connection';"))
end
end)
local sleeper = utils.newsleeper()
print('\ngoing to sleep')
sleeper:sleep(4)
print('awake again\n')
print("Exiting " .. arg[0])
-- vim: syntax=lua ts=2 sw=2 noet:
|
