/
tests.js
389 lines (341 loc) · 18 KB
/
tests.js
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
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
const { Client } = require('..');
const assert = require('assert');
async function testProtocolState(client) {
let result = await client.query('SELECT * FROM users LIMIT 10');
assert(result.rows.length === 10, `SELECT 1 got ${result.rows.length} users but wanted 10`);
assert(result.rowCount === 10, 'SELECT 1 has wrong rowCount' + ` ${JSON.stringify(result)}`);
assert(result.cmd === 'SELECT', 'SELECT 1 has wrong cmd' + ` ${JSON.stringify(result)}`)
result = await client.query('SELECT 1234 as toObject, 5678 as toArray');
assert(result.rows[0].toobject === '1234', 'toObject not working as column name');
assert(result.rows[0].toarray === '5678', 'toArray not working as column name');
assert(result.rows[0].toArray().join(",") === '1234,5678', 'toArray not working');
assert(JSON.stringify(result.rows[0].toObject()) === JSON.stringify({toobject:'1234',toarray:'5678'}), 'toObject not working');
result = await client.query('CREATE TABLE IF NOT EXISTS users_test (name text, email text, password text)');
assert(result.rows.length === 0, 'CREATE TABLE got wrong number of rows' + ` ${JSON.stringify(result.rows)}`);
assert(result.cmd === 'CREATE TABLE', 'CREATE TABLE has wrong cmd' + ` ${JSON.stringify(result)}`)
result = await client.query('DELETE FROM users_test WHERE password = $1', ['baz']);
assert(result.rows.length === 0, 'DELETE got wrong number of rows' + ` ${JSON.stringify(result.rows)}`);
assert(result.cmd === 'DELETE', 'DELETE has wrong cmd' + ` ${JSON.stringify(result)}`)
result = await client.query('INSERT INTO users_test (name, email, password) VALUES ($1, $2, $3) RETURNING password', ['foo', 'bar', 'baz']);
assert(result.rows.length === 1, 'INSERT got wrong number of rows' + ` ${JSON.stringify(result.rows)}`);
assert(result.rows[0].password === 'baz', 'INSERT did not return password' + ` ${JSON.stringify(result.rows)}`);
assert(result.rowCount === 1, 'INSERT has wrong rowCount' + ` ${JSON.stringify(result)}`);
assert(result.cmd === 'INSERT', 'INSERT has wrong cmd' + ` ${JSON.stringify(result)}`)
result = await client.query('SELECT name, email, password FROM users_test WHERE password = $1', ['baz']);
assert(result.rows.length === 1, 'SELECT 2 got wrong number of rows') + ` ${JSON.stringify(result.rows)}`;
assert(result.rows[0].name === 'foo', 'SELECT 2 did not get right name' + ` ${JSON.stringify(result.rows)}`);
assert(result.rows[0].email === 'bar', 'SELECT 2 did not get right email' + ` ${JSON.stringify(result.rows)}`);
assert(result.rowCount === 1, 'SELECT 2 has wrong rowCount' + ` ${JSON.stringify(result)}`);
assert(result.cmd === 'SELECT', 'SELECT 2 has wrong cmd' + ` ${JSON.stringify(result)}`)
result = await client.query('INSERT INTO users_test (name, email, password) VALUES ($1, $2, $3)', ['fox', 'bax', 'baz']);
assert(result.rows.length === 0, 'INSERT 2 got wrong number of rows' + ` ${JSON.stringify(result.rows)}`);
assert(result.rowCount === 1, 'INSERT 2 has wrong rowCount' + ` ${JSON.stringify(result)}`);
assert(result.cmd === 'INSERT', 'INSERT 2 has wrong cmd' + ` ${JSON.stringify(result)}`)
result = await client.query('UPDATE users_test SET name = $1 WHERE password = $2', ['qux', 'baz']);
assert(result.rows.length === 0, 'UPDATE got wrong number of rows' + ` ${JSON.stringify(result.rows)}`);
assert(result.rowCount === 2, 'UPDATE has wrong rowCount' + ` ${JSON.stringify(result)}`);
assert(result.cmd === 'UPDATE', 'UPDATE has wrong cmd' + ` ${JSON.stringify(result)}`)
result = await client.query('SELECT name, email, password FROM users_test WHERE password = $1', ['baz']);
assert(result.rows.length === 2, 'SELECT 3 got wrong number of rows' + ` ${JSON.stringify(result.rows)}`);
assert(result.rows.every(r => r.password === 'baz' && r.name === 'qux'), 'SELECT 3 rows have wrong password or name' + ` ${JSON.stringify(result.rows)}`)
assert(result.rowCount === 2, 'SELECT 3 has wrong rowCount' + ` ${JSON.stringify(result)}`);
assert(result.cmd === 'SELECT', 'SELECT 3 has wrong cmd' + ` ${JSON.stringify(result)}`)
result = await client.query('DELETE FROM users_test WHERE password = $1', ['baz']);
assert(result.rows.length === 0, 'DELETE 2 got wrong number of rows' + ` ${JSON.stringify(result.rows)}`);
assert(result.rowCount === 2, 'DELETE 2 has wrong rowCount' + ` ${JSON.stringify(result)}`);
assert(result.cmd === 'DELETE', 'DELETE 2 has wrong cmd' + ` ${JSON.stringify(result)}`)
}
function randomBytes() {
var length = Math.round(Math.pow(Math.random(), 8) * 10000);
var buf = Buffer.alloc(length);
for (let i = 0; i < buf.byteLength; i++) {
buf[i] = (Math.random() * 256) | 0;
}
return buf;
}
function randomString() {
return (Math.random() * 1e12).toString(36);
}
module.exports = async function runTest(client) {
let t0, result, copyResult, ws;
let promises = [], count = 0;
promises.splice(0);
await testProtocolState(client);
t0 = Date.now();
var id = Buffer.from(Math.floor(Math.random() * 1000000).toString());
var params = [id];
for (var i = 0; i < 30000; i++) {
promises.push(client.query('SELECT * FROM users WHERE email = $1', params, Client.BINARY));
}
result = await Promise.all(promises);
console.error(1000 * result.length / (Date.now() - t0), 'single-row-hitting queries per second');
promises.splice(0);
result = null;
{ // README examples
assert(client.serverParameters.server_encoding, "Server parameters didn't receive an encoding");
// Access row fields as object properties.
let { rows, rowCount } = await client.query(
'SELECT name, email FROM users WHERE id = $1', ['adb42e46-d1bc-4b64-88f4-3e754ab52e81']);
console.error(rows[0].name, rows[0].email, rowCount);
console.error(rows[0][0], rows[0][1], rowCount);
// You can also convert the row into an object or an array.
assert(rows[0].toObject().name === rows[0].toArray()[0]);
ws = require('fs').createWriteStream('/dev/null');
// Stream raw query results protocol to stdout (why waste cycles on parsing data...)
await client.query(
'SELECT name, email FROM users WHERE id = $1',
['adb42e46-d1bc-4b64-88f4-3e754ab52e81'],
Client.STRING, // Or Client.BINARY. Controls the format of data that PostgreSQL sends you.
true, // Cache the parsed query (default is true. If you use the query text only once, set this to false.)
ws // The result stream. Client calls stream.write(buffer) on this. See ObjectReader for details.
);
ws.end();
// Binary data
const buf = Buffer.from([0,1,2,3,4,5,255,254,253,252,251,0]);
const result = await client.query('SELECT $1::bytea', [buf], Client.BINARY, false);
assert(buf.toString('hex') === result.rows[0][0].toString('hex'), "bytea roundtrip failed");
// Query execution happens in a pipelined fashion, so when you do a million
// random SELECTs, they get written to the server right away, and the server
// replies are streamed back to you.
promises = [];
for (let i = 0; i < 100; i++) {
const id = Math.floor(Math.random()*1000000).toString();
promises.push(client.query('SELECT * FROM users WHERE email = $1', [id]));
}
await Promise.all(promises);
// Partial query results
client.startQuery('SELECT * FROM users', []);
while (client.inQuery) {
const resultChunk = await client.getResults(100);
// To stop receiving chunks, send a sync.
if (resultChunk.rows.length > 1) {
await client.sync();
break;
}
}
console.error('\nREADME tests done\n')
}
for (var i = 0; i < 100; i++) {
const randos = randomBytes();
result = await client.query('SELECT $1::bytea, octet_length($1::bytea)', [randos], Client.BINARY);
assert(result.rows[0][1].readInt32BE(0) === randos.byteLength, "Bytea wrong length " + randos.byteLength + " !== " + result.rows[0][1].readInt32BE(0));
assert(result.rows[0][0].toString('hex') === randos.toString('hex'), "Bytea roundtrip failed " + randos.toString('hex') + " !== " + result.rows[0][0].toString('hex'));
}
const bytes = Buffer.alloc(256);
for (var i = 0; i < 256; i++) bytes[i] = i;
await client.query('CREATE TABLE IF NOT EXISTS large_object_test (name text, file oid)');
await client.query('INSERT INTO large_object_test (name, file) VALUES ($1, lo_from_bytea(0, $2))', ['my_object', bytes]);
result = await client.query('SELECT lo_get(file), octet_length(lo_get(file)) FROM large_object_test WHERE name = $1', ['my_object'], Client.BINARY);
await client.query('SELECT lo_unlink(file) FROM large_object_test WHERE name = $1', ['my_object']);
await client.query('DROP TABLE large_object_test');
assert(result.rows[0][1].readInt32BE(0) === 256, "Large object wrong length");
assert(result.rows[0][0].toString('hex') === bytes.toString('hex'), "Large object roundtrip failed");
// What happens if we sync several times?
client.sync();
client.sync();
client.sync();
client.sync();
await client.sync();
// Partial queries
await testProtocolState(client);
t0 = Date.now();
result = 0;
client.startQuery('SELECT * FROM users', []);
while (client.inQuery) {
result += (await client.getResults(100)).rows.length;
}
console.error(`received ${result} rows`);
console.error(1000 * result / (Date.now() - t0), 'partial query (100 rows per execute) rows per second');
result = null;
await testProtocolState(client);
t0 = Date.now();
result = 0;
client.startQuery('SELECT * FROM users', []);
while (client.inQuery) {
result += (await client.getResults(100)).rows.length;
if (result >= 10000) {
await client.sync();
break;
}
}
console.error(`received ${result} rows`);
console.error(1000 * result / (Date.now() - t0), 'partial query (early exit) rows per second');
result = null;
await testProtocolState(client);
for (var i = 0; i < 30000; i++) {
if (i % 1000 === 0) {
await Promise.all(promises);
process.stderr.write(`\rwarming up ${i} / 30000 `);
}
const id = Math.random() < 0.9 ? Math.floor(Math.random() * 1000000).toString() : randomString();
promises.push(client.query('SELECT * FROM users WHERE email = $1', [id]));
}
process.stderr.write(`\rwarming up ${i} / 30000\n`);
result = await Promise.all(promises);
promises.splice(0);
await testProtocolState(client);
t0 = Date.now();
for (var i = 0; i < 30000; i++) {
const id = Math.floor(Math.random() * 1000000).toString();
promises.push(client.query('SELECT * FROM users WHERE email = $1', [id]));
}
result = await Promise.all(promises);
console.error(1000 * result.length / (Date.now() - t0), 'random queries per second');
promises.splice(0);
result = null;
promises.splice(0);
await testProtocolState(client);
t0 = Date.now();
var params = [];
for (var i = 0; i < 10000; i++) {
promises.push(client.query('SELECT * FROM users LIMIT 100', params, Client.BINARY));
}
result = await Promise.all(promises);
console.error(100000 * result.length / (Date.now() - t0), '100-row query rows per second');
promises.splice(0);
result = null;
promises.splice(0);
await testProtocolState(client);
t0 = Date.now();
var params = [];
const nullStream = {
pipe: true,
writes: 0,
write: function(buf) { this.writes++; }
};
for (var i = 0; i < 10000; i++) {
promises.push(client.query('SELECT * FROM users LIMIT 100', params, Client.BINARY, true, nullStream));
}
result = await Promise.all(promises);
console.error(100000 * result.length / (Date.now() - t0), 'streamed 100-row query rows per second');
console.error(nullStream.writes / promises.length, 'stream writes per query');
promises.splice(0);
result = null;
await testProtocolState(client);
ws = require('fs').createWriteStream('test.dat');
t0 = Date.now();
result = await client.query('SELECT * FROM users', [], Client.BINARY, true, ws);
await ws.end();
console.error(1000 * 1000011 / (Date.now() - t0), 'binary query rows per second piped to test.dat');
result = null;
await testProtocolState(client);
ws = require('fs').createWriteStream('test_str.dat');
t0 = Date.now();
result = await client.query('SELECT * FROM users', [], Client.STRING, true, ws);
await ws.end();
console.error(1000 * 1000011 / (Date.now() - t0), 'string query rows per second piped to test_str.dat');
result = null;
require('fs').unlinkSync('test_str.dat');
await testProtocolState(client);
t0 = Date.now();
result = await client.query('SELECT * FROM users', []);
console.error(1000 * result.rows.length / (Date.now() - t0), 'query rows per second');
result = null;
await testProtocolState(client);
t0 = Date.now();
result = await client.query('SELECT * FROM users', []);
count = 0;
for (let i = 0, rows = result.rows; i < rows.length; i++) count += rows[i].toArray().length;
console.error(1000 * result.rows.length / (Date.now() - t0), 'query rows as arrays per second', count);
result = null;
await testProtocolState(client);
t0 = Date.now();
result = await client.query('SELECT * FROM users', []);
count = 0;
for (let i = 0, rows = result.rows; i < rows.length; i++) count += rows[i].toObject() ? 1 : 0;
console.error(1000 * result.rows.length / (Date.now() - t0), 'query rows as objects per second', count);
result = null;
await testProtocolState(client);
t0 = Date.now();
result = await client.query('SELECT * FROM users', [], Client.BINARY);
console.error(1000 * result.rows.length / (Date.now() - t0), 'binary query rows per second');
result = Buffer.concat(result.rows.map(r => r.rowBuffer));
let readBack = require('fs').readFileSync('test.dat');
for (let i = 0; i < result.byteLength; i++) if (result[i] !== readBack[i]) throw(Error("inequality " + i));
readBack = null;
result = null;
require('fs').unlinkSync('test.dat');
await testProtocolState(client);
t0 = Date.now();
result = await client.query('SELECT * FROM users', [], Client.BINARY);
count = 0;
for (let i = 0, rows = result.rows; i < rows.length; i++) count += rows[i].toArray().length;
console.error(1000 * result.rows.length / (Date.now() - t0), 'binary query rows as arrays per second', count);
result = null;
await testProtocolState(client);
t0 = Date.now();
result = await client.query('SELECT * FROM users', [], Client.BINARY);
count = 0;
for (let i = 0, rows = result.rows; i < rows.length; i++) count += rows[i].toObject() ? 1 : 0;
console.error(1000 * result.rows.length / (Date.now() - t0), 'binary query rows as objects per second', count);
copyResult = result;
result = null;
await testProtocolState(client);
t0 = Date.now();
result = client.query('SELECT * FROM users', []);
await client.cancel();
try {
await result;
} catch(err) {
console.error('Cancel test: ' + err.message);
}
console.error('Elapsed: ' + (Date.now() - t0) + ' ms');
result = null;
await testProtocolState(client);
result = await client.query('DELETE FROM users_copy');
console.error(`Deleted ${result.rowCount} rows from users_copy`);
await testProtocolState(client);
t0 = Date.now();
const userFields = copyResult.rowParser.columns.map(f => f.name);
const insertQueryString = `
INSERT INTO users_copy
(${userFields.join(",")})
VALUES
(${userFields.map((_,i) => `$${i+1}`).join(",")})
`;
// console.error(insertQueryString);
// console.error(copyResult.rows[0]);
client.query('BEGIN');
result = 0;
copyResult.rows.splice(30000); // Leave only 30k rows
promises.push(client.query(insertQueryString, copyResult.rows[0].toArray()));
for (let i = 1; i < copyResult.rows.length; i++) {
promises.push(client.query(insertQueryString, copyResult.rows[i]));
}
result += (await Promise.all(promises)).length;
await client.query('COMMIT');
console.error(1000 * result / (Date.now() - t0), 'binary inserts per second');
promises.splice(0);
copyResult = null;
result = null;
await testProtocolState(client);
t0 = Date.now();
result = await client.query('COPY users TO STDOUT (FORMAT text)');
console.error(1000 * result.rows.length / (Date.now() - t0), 'text copyTo rows per second');
// console.error(result.rows[0].toString());
await testProtocolState(client);
t0 = Date.now();
result = await client.query('COPY users TO STDOUT (FORMAT csv)');
console.error(1000 * result.rows.length / (Date.now() - t0), 'csv copyTo rows per second');
// console.error(result.rows[0].toString());
await testProtocolState(client);
t0 = Date.now();
result = await client.query('COPY users TO STDOUT (FORMAT binary)', [], Client.BINARY);
console.error(1000 * result.rows.length / (Date.now() - t0), 'binary copyTo rows per second');
// console.error(result.rows[0]);
copyResult = result;
await testProtocolState(client);
result = await client.query('DELETE FROM users_copy');
console.error(`Deleted ${result.rowCount} rows from users_copy`);
await testProtocolState(client);
t0 = Date.now();
let copyIn = await client.query('COPY users_copy FROM STDIN (FORMAT binary)', [], Client.BINARY);
for (let i = 0; i < copyResult.rows.length; i += 1000) {
const chunk = Buffer.concat(copyResult.rows.slice(i, i + 1000));
client.copyData(chunk);
}
await client.copyDone(copyIn);
console.error(1000 * copyResult.rows.length / (Date.now() - t0), 'binary copyFrom rows per second');
copyResult = null;
await testProtocolState(client);
}