neyuki 0.1.1

A small Lua-like scripting language implemented in Rust
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

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) ------------
--
-- `os.getenv` only exposes an allowlist of variables, so 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