local sql = require("@neyuki/sql")
local fs = require("@neyuki/fs")
local string = require("@neyuki/string")
-- ARGUMENT CHECKS (no server needed) ---------------------------------------
local ok, err = try(sql.connect, 42)
assert(not ok and string.find(err, "URL string or an options table", 1, true) != nil, "connect should reject non-targets: " .. tostring(err))
ok, err = try(sql.connect, "sqlite://db.sqlite")
assert(not ok and string.find(err, "sqlite", 1, true) != nil, "connect should name an unknown driver: " .. tostring(err))
ok, err = try(sql.connect, "not a url")
assert(not ok and string.find(err, "scheme", 1, true) != nil, "connect should reject a URL without a scheme: " .. tostring(err))
ok, err = try(sql.connect, {})
assert(not ok and string.find(err, "driver must be provided", 1, true) != nil, "connect should demand a driver: " .. tostring(err))
ok, err = try(sql.connect, { driver = "postgres" })
assert(not ok and string.find(err, "user must be provided", 1, true) != nil, "postgres should demand a user: " .. tostring(err))
ok, err = try(sql.connect, { driver = "postgres", user = "u", port = 70000 })
assert(not ok and string.find(err, "out of range", 1, true) != nil, "connect should check the port: " .. tostring(err))
ok, err = try(sql.connect, { driver = "mysql", ssl = "yes" })
assert(not ok and string.find(err, "ssl must be a boolean", 1, true) != nil, "connect should check ssl: " .. tostring(err))
-- Nothing listens on port 1, so both drivers fail fast at the socket.
ok, err = try(sql.connect, "postgres://u:p@127.0.0.1:1/db")
assert(not ok, "connecting to a closed port should fail")
ok, err = try(sql.connect, { driver = "mariadb", host = "127.0.0.1", port = 1, user = "u", timeout = 5 })
assert(not ok, "connecting to a closed port should fail")
-- LIVE ROUND TRIPS (opt in with server URLs in tests/sql.local) ------------
--
-- The URLs come from a git-ignored file with lines such as
-- postgres=postgres://user:pass@127.0.0.1:5432/postgres
-- mysql=mysql://root:pass@127.0.0.1:3306/test
-- Without the file this part is skipped.
local liveUrls = {}
if (fs.exists("tests/sql.local")) then
local file = fs.open("tests/sql.local", "r")
for line in file:lines() do
local equals = string.find(line, "=", 1, true)
if (equals != nil) then
liveUrls[string.sub(line, 1, equals - 1)] = string.sub(line, equals + 1)
end
end
file:close()
end
-- Exercises the parts that behave the same on both servers.
function exercise(db: Connection, placeholder: (number) -> string)
assert(db:isOpen(), "a fresh connection should be open")
assert(db:ping(), "a fresh connection should answer a ping")
db:execute("DROP TABLE IF EXISTS neyuki_sql_test")
if (db.driver == "postgres") then
db:execute("CREATE TABLE neyuki_sql_test (id SERIAL PRIMARY KEY, name TEXT NOT NULL, score DOUBLE PRECISION, active BOOLEAN, note TEXT)")
else
db:execute("CREATE TABLE neyuki_sql_test (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(64) NOT NULL, score DOUBLE, active BOOLEAN, note TEXT)")
end
local insert = "INSERT INTO neyuki_sql_test (name, score, active, note) VALUES (" ..
placeholder(1) .. ", " .. placeholder(2) .. ", " .. placeholder(3) .. ", " .. placeholder(4) .. ")"
local affected, lastId = db:execute(insert, { "ada", 1.5, true, "first" })
assert(affected == 1, "insert should touch one row")
if (db.driver == "mysql") then assert(lastId == 1, "mysql should report the inserted id") end
affected = db:execute(insert, { "bob", 2, false, nil })
assert(affected == 1, "a nil parameter should bind as NULL")
-- a mismatched parameter count is an error, not a silent NULL
assert(not try(db.execute, db, insert, { "eve" }), "too few parameters should error")
local rows = db:query("SELECT id, name, score, active, note FROM neyuki_sql_test ORDER BY id")
assert(#rows == 2, "both rows should come back")
assert(rows.columns[1] == "id" and rows.columns[5] == "note", "columns should be listed in select order")
assert(rows[1].id == 1 and rows[1].name == "ada" and rows[1].score == 1.5, "row values should keep their types")
assert(rows[1].note == "first" and rows[2].note == nil, "NULL should read as nil")
if (db.driver == "postgres") then
assert(rows[1].active == true and rows[2].active == false, "postgres booleans should be booleans")
else
assert(rows[1].active == 1 and rows[2].active == 0, "mysql booleans are tinyints")
end
local row = db:queryOne("SELECT name FROM neyuki_sql_test WHERE id = " .. placeholder(1), { 2 })
assert(row != nil and row.name == "bob", "queryOne should return the matching row")
assert(db:queryOne("SELECT name FROM neyuki_sql_test WHERE id = " .. placeholder(1), { 99 }) == nil, "queryOne should return nil for no rows")
-- a failing body rolls the transaction back and re-raises
ok, err = try(db.transaction, db, function(tx: Connection)
tx:execute("DELETE FROM neyuki_sql_test")
error("abort")
end)
assert(not ok and string.find(err, "abort", 1, true) != nil, "transaction should re-raise the body's error")
assert(#db:query("SELECT id FROM neyuki_sql_test") == 2, "a failed transaction should be rolled back")
-- a successful body commits and returns its result
local count = db:transaction(function(tx: Connection)
tx:execute("DELETE FROM neyuki_sql_test WHERE id = " .. placeholder(1), { 1 })
return #tx:query("SELECT id FROM neyuki_sql_test")
end)
assert(count == 1, "transaction should return the body's result")
assert(#db:query("SELECT id FROM neyuki_sql_test") == 1, "a successful transaction should be committed")
-- server errors carry the message and SQLSTATE
ok, err = try(db.query, db, "SELECT * FROM neyuki_no_such_table")
assert(not ok and string.find(err, "SQLSTATE", 1, true) != nil, "server errors should carry a SQLSTATE: " .. tostring(err))
db:execute("DROP TABLE neyuki_sql_test")
db:close()
assert(not db:isOpen(), "close should mark the connection closed")
assert(not try(db.query, db, "SELECT 1"), "a closed connection should refuse queries")
db:close()
end
local postgresUrl = liveUrls.postgres
if (postgresUrl != nil) then
local db = sql.connect(postgresUrl)
assert(db.driver == "postgres")
exercise(db, function(n: number): string return "$" .. tostring(n) end)
-- type decoding that is specific to postgres
db = sql.connect(postgresUrl)
local row = db:queryOne("SELECT 1::int2 AS i2, 2::int8 AS i8, 1.5::float4 AS f4, 12345678901234567890.123::numeric AS n, " ..
"'2024-02-29'::date AS d, '12:34:56.5'::time AS t, '2024-02-29 12:34:56'::timestamp AS ts, " ..
"'2024-02-29 12:34:56+02'::timestamptz AS tz, 'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11'::uuid AS u, " ..
"'{\"a\": 1}'::jsonb AS j, ARRAY[1, 2, NULL]::int[] AS arr, 'x'::bytea AS b, $1::int + 1 AS p, $2::text AS s, $3::bool AS f",
{ 41, "hi", false })
assert(row.i2 == 1 and row.i8 == 2 and row.f4 == 1.5, "small numeric types should decode")
assert(row.n == "12345678901234567890.123", "numeric should keep every digit: " .. tostring(row.n))
assert(row.d == "2024-02-29" and row.t == "12:34:56.5" and row.ts == "2024-02-29 12:34:56", "dates and times should read like the server prints them")
assert(row.tz == "2024-02-29 10:34:56+00", "timestamptz should be reported in UTC: " .. tostring(row.tz))
assert(row.u == "a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11", "uuid should be hyphenated")
assert(row.j == "{\"a\": 1}", "jsonb should be its text")
assert(#row.arr == 3 and row.arr[1] == 1 and row.arr[2] == 2 and row.arr[3] == nil, "arrays should become arrays")
assert(row.b == "x" and row.p == 42 and row.s == "hi" and row.f == false, "parameters should convert to the inferred type")
local ok2, err2 = try(db.query, db, "SELECT '1 day'::interval AS i")
assert(not ok2 and string.find(err2, "cast it to text", 1, true) != nil, "unsupported types should explain the fix: " .. tostring(err2))
db:close()
end
local mysqlUrl = liveUrls.mysql
if (mysqlUrl != nil) then
local db = sql.connect(mysqlUrl)
assert(db.driver == "mysql")
exercise(db, function(n: number): string return "?" end)
db = sql.connect(mysqlUrl)
local row = db:queryOne("SELECT CAST(12345678901234567890.123 AS DECIMAL(30, 3)) AS n, DATE('2024-02-29') AS d, " ..
"TIMESTAMP('2024-02-29 12:34:56') AS ts, TIME('12:34:56.5') AS t, ? + 1 AS p, ? AS s, CAST(? AS UNSIGNED) AS u",
{ 41, "hi", 18446744073709551615 })
assert(row.n == "12345678901234567890.123", "decimal should keep every digit: " .. tostring(row.n))
assert(row.d == "2024-02-29" and row.ts == "2024-02-29 12:34:56" and row.t == "12:34:56.5", "dates and times should read like the server prints them")
assert(row.p == 42 and row.s == "hi", "parameters should bind")
assert(row.u == 18446744073709551615, "unsigned bigints should not overflow")
db:close()
end