diff options
Diffstat (limited to '3rdparty/lsqlite3/examples')
-rw-r--r-- | 3rdparty/lsqlite3/examples/aggregate.lua | 35 | ||||
-rw-r--r-- | 3rdparty/lsqlite3/examples/function.lua | 21 | ||||
-rw-r--r-- | 3rdparty/lsqlite3/examples/hooks_advanced.lua | 349 | ||||
-rw-r--r-- | 3rdparty/lsqlite3/examples/order.lua | 122 | ||||
-rw-r--r-- | 3rdparty/lsqlite3/examples/simple.lua | 16 | ||||
-rw-r--r-- | 3rdparty/lsqlite3/examples/smart.lua | 22 | ||||
-rw-r--r-- | 3rdparty/lsqlite3/examples/statement.lua | 39 | ||||
-rw-r--r-- | 3rdparty/lsqlite3/examples/tracing.lua | 20 | ||||
-rw-r--r-- | 3rdparty/lsqlite3/examples/update_hook.lua | 31 |
9 files changed, 655 insertions, 0 deletions
diff --git a/3rdparty/lsqlite3/examples/aggregate.lua b/3rdparty/lsqlite3/examples/aggregate.lua new file mode 100644 index 00000000000..6087b0c65cc --- /dev/null +++ b/3rdparty/lsqlite3/examples/aggregate.lua @@ -0,0 +1,35 @@ + +local sqlite3 = require("lsqlite3") + +local db = sqlite3.open_memory() + +assert( db:exec "CREATE TABLE test (col1, col2)" ) +assert( db:exec "INSERT INTO test VALUES (1, 2)" ) +assert( db:exec "INSERT INTO test VALUES (2, 4)" ) +assert( db:exec "INSERT INTO test VALUES (3, 6)" ) +assert( db:exec "INSERT INTO test VALUES (4, 8)" ) +assert( db:exec "INSERT INTO test VALUES (5, 10)" ) + +do + + local square_error_sum = 0 + + local function step(ctx, a, b) + local error = a - b + local square_error = error * error + square_error_sum = square_error_sum + square_error + end + + local function final(ctx) + ctx:result_number( square_error_sum / ctx:aggregate_count() ) + end + + assert( db:create_aggregate("my_stats", 2, step, final) ) + +end + +--for a,b in db:urows("SELECT col1, col2 FROM test") +--do print("a b: ", a, b) end + +for my_stats in db:urows("SELECT my_stats(col1, col2) FROM test") +do print("my_stats:", my_stats) end diff --git a/3rdparty/lsqlite3/examples/function.lua b/3rdparty/lsqlite3/examples/function.lua new file mode 100644 index 00000000000..34ba4f70092 --- /dev/null +++ b/3rdparty/lsqlite3/examples/function.lua @@ -0,0 +1,21 @@ + +local sqlite3 = require("lsqlite3") + + +local db = sqlite3.open_memory() + +assert( db:exec "CREATE TABLE test (col1, col2)" ) +assert( db:exec "INSERT INTO test VALUES (1, 2)" ) +assert( db:exec "INSERT INTO test VALUES (2, 4)" ) +assert( db:exec "INSERT INTO test VALUES (3, 6)" ) +assert( db:exec "INSERT INTO test VALUES (4, 8)" ) +assert( db:exec "INSERT INTO test VALUES (5, 10)" ) + +assert( db:create_function("my_sum", 2, function(ctx, a, b) + ctx:result_number( a + b ) +end)) + + +for col1, col2, sum in db:urows("SELECT *, my_sum(col1, col2) FROM test") do + print(col1, col2, sum) +end diff --git a/3rdparty/lsqlite3/examples/hooks_advanced.lua b/3rdparty/lsqlite3/examples/hooks_advanced.lua new file mode 100644 index 00000000000..6dd105a9336 --- /dev/null +++ b/3rdparty/lsqlite3/examples/hooks_advanced.lua @@ -0,0 +1,349 @@ + +local sqlite3 = require("lsqlite3") +local db + +optbl = { [sqlite3.UPDATE] = "UPDATE"; + [sqlite3.INSERT] = "INSERT"; + [sqlite3.DELETE] = "DELETE" + } +setmetatable(optbl, + {__index=function(t,n) return string.format("Unknown op %d",n) end}) + + +function update_hook(ud, op, dname, tname, rowid) + print("Sqlite Update Hook:", optbl[op], dname, tname, rowid) +end + +function commit_hook(ud) + print("<Sqlite Commit Hook>") +end + +function rollback_hook(ud) + print("<Sqlite Rollback Hook>") +end + +-- db:exec wrapper with result assertion +function db_exec(stmt) + if db:exec(stmt) ~= sqlite3.OK then + print("Sqlite ERROR: ", db:errmsg()) + end +end + +-- debug output for database table +function db_print_tables(...) + for i = 1,select('#',...) do + name = select(i,...) + print(string.format("\n%s contents:", name)) + for row in db:nrows("SELECT * FROM " .. name) do + print(row.id, row.content) + end + end +end + +local tests = {} + +-- runs specified test with all necessary setup +-- @param string name +function run_test(name) + -- banner + print(string.format([[ + + + ========================== + %s + ========================== + ]], name)) + + -- setup + db = sqlite3.open_memory() + local udtbl = {0, 0, 0} + db:update_hook(update_hook, udtbl) + db:commit_hook(commit_hook, udtbl) + db:rollback_hook(rollback_hook, udtbl) + + -- run test + tests[name]() + + -- destroy + db:close() +end + + +function tests.insert_select() + db_exec[[ + CREATE TABLE T1 ( id INTEGER PRIMARY KEY, content VARCHAR ); + CREATE TABLE T2 ( id INTEGER PRIMARY KEY, content VARCHAR ); + + INSERT INTO T1 VALUES (NULL, 'Hello World'); + INSERT INTO T1 VALUES (NULL, 'Hello Lua'); + INSERT INTO T1 VALUES (NULL, 'Hello Sqlite3'); + + INSERT INTO T2 SELECT * FROM T1; + ]] + + db_print_tables('T1', 'T2') +end + +function tests.trigger_insert() + db_exec[[ + CREATE TABLE T1 ( id INTEGER PRIMARY KEY, content VARCHAR ); + CREATE TABLE T2 ( id INTEGER PRIMARY KEY, content VARCHAR ); + + CREATE TRIGGER after_insert_T1 + AFTER INSERT ON T1 + FOR EACH ROW + BEGIN + INSERT INTO T2 VALUES(NEW.id, NEW.content); + END; + + INSERT INTO T1 VALUES (NULL, 'Hello World'); + INSERT INTO T1 VALUES (NULL, 'Hello Lua'); + INSERT INTO T1 VALUES (NULL, 'Hello Sqlite3'); + ]] + + db_print_tables('T1', 'T2') +end + +function tests.cascade_delete() + db_exec[[ + PRAGMA foreign_keys = ON; + + CREATE TABLE T1 ( id INTEGER PRIMARY KEY, content VARCHAR ); + CREATE TABLE T2 ( id INTEGER PRIMARY KEY REFERENCES T1 ON DELETE CASCADE, content VARCHAR ); + CREATE TABLE T3 ( id INTEGER PRIMARY KEY REFERENCES T2 ON DELETE CASCADE, content VARCHAR ); + + INSERT INTO T1 VALUES (NULL, 'a'); + INSERT INTO T1 VALUES (NULL, 'b'); + INSERT INTO T1 VALUES (NULL, 'c'); + INSERT INTO T1 VALUES (NULL, 'd'); + + INSERT INTO T2 SELECT * FROM T1; + INSERT INTO T3 SELECT * FROM T2; + + DELETE FROM T1 WHERE id < 3; + ]] + + db_print_tables('T1', 'T2', 'T3') +end + +function tests.cascade_update() + db_exec[[ + PRAGMA foreign_keys = ON; + + CREATE TABLE T1 ( id INTEGER PRIMARY KEY, content VARCHAR ); + CREATE TABLE T2 ( id INTEGER PRIMARY KEY REFERENCES T1 ON UPDATE CASCADE, content VARCHAR ); + CREATE TABLE T3 ( id INTEGER PRIMARY KEY REFERENCES T2 ON UPDATE CASCADE, content VARCHAR ); + + INSERT INTO T1 VALUES (NULL, 'a'); + INSERT INTO T1 VALUES (NULL, 'b'); + INSERT INTO T1 VALUES (NULL, 'c'); + INSERT INTO T1 VALUES (NULL, 'd'); + + INSERT INTO T2 SELECT * FROM T1; + INSERT INTO T3 SELECT * FROM T2; + + UPDATE T1 SET id = id + 10 WHERE id < 3; + ]] + + db_print_tables('T1', 'T2', 'T3') +end + +-- hook "anomaly" +-- implicit one-statement transaction rollback demonstration +function tests.cascade_update_restrict() + db_exec[[ + PRAGMA foreign_keys = ON; + + CREATE TABLE T1 ( id INTEGER PRIMARY KEY, content VARCHAR ); + CREATE TABLE T2 ( id INTEGER PRIMARY KEY REFERENCES T1 ON UPDATE RESTRICT, content VARCHAR ); + CREATE TABLE T3 ( id INTEGER PRIMARY KEY REFERENCES T2 ON UPDATE RESTRICT, content VARCHAR ); + + INSERT INTO T1 VALUES (NULL, 'a'); + INSERT INTO T1 VALUES (NULL, 'b'); + INSERT INTO T1 VALUES (NULL, 'c'); + INSERT INTO T1 VALUES (NULL, 'd'); + + INSERT INTO T2 SELECT * FROM T1; + INSERT INTO T3 SELECT * FROM T2; + + -- this update gets reverted but the update_hook with rowid=11 *DOES* get triggered + UPDATE T1 SET id = id + 10 WHERE id < 3; + ]] + + db_print_tables('T1', 'T2', 'T3') +end + +-- hook "anomaly" +-- case is analogous to cascade_update_restrict +function tests.cascade_delete_restrict() + db_exec[[ + PRAGMA foreign_keys = ON; + + CREATE TABLE T1 ( id INTEGER PRIMARY KEY, content VARCHAR ); + CREATE TABLE T2 ( id INTEGER PRIMARY KEY REFERENCES T1 ON DELETE RESTRICT, content VARCHAR ); + CREATE TABLE T3 ( id INTEGER PRIMARY KEY REFERENCES T2 ON DELETE RESTRICT, content VARCHAR ); + + INSERT INTO T1 VALUES (NULL, 'a'); + INSERT INTO T1 VALUES (NULL, 'b'); + INSERT INTO T1 VALUES (NULL, 'c'); + INSERT INTO T1 VALUES (NULL, 'd'); + + INSERT INTO T2 SELECT * FROM T1; + INSERT INTO T3 SELECT * FROM T2; + + DELETE FROM T1 WHERE id < 3; + ]] + + db_print_tables('T1', 'T2', 'T3') +end + +-- no anomalies here +function tests.fk_violate_insert() + db_exec[[ + PRAGMA foreign_keys = ON; + + CREATE TABLE T1 ( id INTEGER PRIMARY KEY, content VARCHAR ); + CREATE TABLE T2 ( id INTEGER PRIMARY KEY REFERENCES T1, content VARCHAR); + + INSERT INTO T1 VALUES (NULL, 'a'); + INSERT INTO T1 VALUES (NULL, 'b'); + + INSERT INTO T2 VALUES(99, 'xxx'); + ]] + + db_print_tables('T1', 'T2') +end + +-- hook "anomaly" +function tests.fk_violate_update() + db_exec[[ + PRAGMA foreign_keys = ON; + + CREATE TABLE T1 ( id INTEGER PRIMARY KEY, content VARCHAR ); + CREATE TABLE T2 ( id INTEGER PRIMARY KEY REFERENCES T1, content VARCHAR); + + INSERT INTO T1 VALUES (NULL, 'a'); + INSERT INTO T1 VALUES (NULL, 'b'); + INSERT INTO T2 VALUES(1, 'a'); + + -- update doesn't succeed but we get a hook for tuple with rowid=99 in T2 + UPDATE T2 SET id = 99 WHERE id = 1; + ]] + + db_print_tables('T1', 'T2') +end + +-- like fk_violate_update but wrapped inside an explicit transaction +function tests.transaction_fk_violate_update() + db_exec[[ + PRAGMA foreign_keys = ON; + + CREATE TABLE T1 ( id INTEGER PRIMARY KEY, content VARCHAR ); + CREATE TABLE T2 ( id INTEGER PRIMARY KEY REFERENCES T1, content VARCHAR); + + BEGIN TRANSACTION; + INSERT INTO T1 VALUES (NULL, 'a'); + INSERT INTO T1 VALUES (NULL, 'b'); + INSERT INTO T2 VALUES(1, 'a'); + + -- Doesn't trigger rollback hook because the implicit update statement transaction + -- is nested inside our explicit transaction. However we *do* get an error. + UPDATE T2 SET id = 99 WHERE id = 1; + COMMIT; + ]] + + db_print_tables('T1', 'T2') +end + +function tests.cascade_update_setnull() + db_exec[[ + PRAGMA foreign_keys = ON; + + CREATE TABLE T1 ( id INTEGER PRIMARY KEY, content VARCHAR ); + CREATE TABLE T2 ( id INTEGER PRIMARY KEY, content INTEGER REFERENCES T1(id) ON UPDATE SET NULL); + + INSERT INTO T1 VALUES (NULL, 'a'); + INSERT INTO T1 VALUES (NULL, 'b'); + INSERT INTO T1 VALUES (NULL, 'c'); + INSERT INTO T1 VALUES (NULL, 'd'); + + INSERT INTO T2 SELECT NULL, id FROM T1; + + UPDATE T1 SET id = id + 10 WHERE id < 3; + ]] + + db_print_tables('T1', 'T2') +end + +function tests.transaction_commit() + db_exec[[ + CREATE TABLE T1 ( id INTEGER PRIMARY KEY, content VARCHAR ); + + BEGIN TRANSACTION; + INSERT INTO T1 VALUES (NULL, 'Hello World'); + INSERT INTO T1 VALUES (NULL, 'Hello Lua'); + COMMIT; + ]] + + db_print_tables('T1') +end + +function tests.transaction_rollback() + db_exec[[ + CREATE TABLE T1 ( id INTEGER PRIMARY KEY, content VARCHAR ); + + BEGIN TRANSACTION; + INSERT INTO T1 VALUES (NULL, 'Hello World'); + INSERT INTO T1 VALUES (NULL, 'Hello Lua'); + ROLLBACK; + ]] + + db_print_tables('T1') +end + +function tests.savepoint_nested_commit() + db_exec[[ + CREATE TABLE T1 ( id INTEGER PRIMARY KEY, content VARCHAR ); + + SAVEPOINT S1; + INSERT INTO T1 VALUES (NULL, 'Hello World'); + INSERT INTO T1 VALUES (NULL, 'Hello Lua'); + + SAVEPOINT S2; + INSERT INTO T1 VALUES (NULL, 'Hello Sqlite3'); + -- nested commit doesn't trigger commit_hook + RELEASE S2; + + INSERT INTO T1 VALUES (NULL, 'Hello transactions'); + RELEASE S1; + ]] + + db_print_tables('T1') +end + +function tests.savepoint_nested_rollback() + db_exec[[ + CREATE TABLE T1 ( id INTEGER PRIMARY KEY, content VARCHAR ); + + SAVEPOINT S1; + INSERT INTO T1 VALUES (NULL, 'Hello World'); + INSERT INTO T1 VALUES (NULL, 'Hello Lua'); + + SAVEPOINT S2; + INSERT INTO T1 VALUES (NULL, 'Hello Sqlite3'); + -- nested rollback doesn't trigger rollback_hook + ROLLBACK TO S2; + + INSERT INTO T1 VALUES (NULL, 'Hello transactions'); + RELEASE S1; + ]] + + db_print_tables('T1') +end + + +-- run_test('fk_violate_insert') + +for k,v in pairs(tests) do + run_test(k) +end diff --git a/3rdparty/lsqlite3/examples/order.lua b/3rdparty/lsqlite3/examples/order.lua new file mode 100644 index 00000000000..c17b05f0df6 --- /dev/null +++ b/3rdparty/lsqlite3/examples/order.lua @@ -0,0 +1,122 @@ + +local sqlite3 = require("lsqlite3") + +local db = assert( sqlite3:open_memory() ) + +assert( db:exec[[ + + CREATE TABLE customer ( + id INTEGER PRIMARY KEY, + name VARCHAR(40) + ); + + CREATE TABLE invoice ( + id INTEGER PRIMARY KEY, + customer INTEGER NOT NULL, + title VARCHAR(80) NOT NULL, + article1 VARCHAR(40) NOT NULL, + price1 REAL NOT NULL, + article2 VARCHAR(40), + price2 REAL + ); + + CREATE TABLE invoice_overflow ( + id INTEGER PRIMARY KEY, + invoice INTEGER NOT NULL, + article VARCHAR(40) NOT NULL, + price REAL NOT NULL + ); + + INSERT INTO customer VALUES( + 1, "Michael" ); + + INSERT INTO invoice VALUES( + 1, 1, "Computer parts", "harddisc", 89.90, "floppy", 9.99 ); + + INSERT INTO customer VALUES( + 2, "John" ); + + INSERT INTO invoice VALUES( + 2, 2, "Somme food", "apples", 2.79, "pears", 5.99 ); + + INSERT INTO invoice_overflow VALUES( + NULL, 2, "grapes", 6.34 ); + + INSERT INTO invoice_overflow VALUES( + NULL, 2, "strawberries", 4.12 ); + + INSERT INTO invoice_overflow VALUES( + NULL, 2, "tomatoes", 6.17 ); + + INSERT INTO invoice VALUES( + 3, 2, "A new car", "Cybercar XL-1000", 65000.00, NULL, NULL ); + +]] ) + + +local function customer_name(id) + local stmt = db:prepare("SELECT name FROM customer WHERE id = ?") + stmt:bind_values(id) + stmt:step() + local r = stmt:get_uvalues() + stmt:finalize() + return r +end + + +local function all_invoices() + return db:nrows("SELECT id, customer, title FROM invoice") +end + + +local function all_articles(invoice) + + local function iterator() + local stmt, row + + -- Get the articles that are contained in the invoice table itself. + stmt = db:prepare("SELECT article1, price1, article2, price2 FROM invoice WHERE id = ?") + stmt:bind_values(invoice) + stmt:step() + row = stmt:get_named_values() + + -- Every Invoice has at least one article. + coroutine.yield(row.article1, row.price1) + + -- Maybe the Invoice has a second article? + if row.article2 then + + -- Yes, there is a second article, so return it. + coroutine.yield(row.article2, row.price2) + + -- When there was an second article, maybe there are even + -- more articles in the overflow table? We will see... + + stmt = db:prepare("SELECT article, price FROM invoice_overflow WHERE invoice = ? ORDER BY id") + stmt:bind_values(invoice) + + for row in stmt:nrows() do + coroutine.yield(row.article, row.price) + end + end + end + + return coroutine.wrap(iterator) +end + + +for invoice in all_invoices() do + local id = invoice.id + local name = customer_name(invoice.customer) + local title = invoice.title + + print() + print("Invoice #"..id..", "..name..": '"..title.."'") + print("----------------------------------------") + + for article, price in all_articles(id) do + print( string.format("%20s %8.2f", article, price) ) + end + + print() +end diff --git a/3rdparty/lsqlite3/examples/simple.lua b/3rdparty/lsqlite3/examples/simple.lua new file mode 100644 index 00000000000..adc403cfdfa --- /dev/null +++ b/3rdparty/lsqlite3/examples/simple.lua @@ -0,0 +1,16 @@ + +local sqlite3 = require("lsqlite3") + +local db = sqlite3.open_memory() + +db:exec[[ + CREATE TABLE test (id INTEGER PRIMARY KEY, content); + + INSERT INTO test VALUES (NULL, 'Hello World'); + INSERT INTO test VALUES (NULL, 'Hello Lua'); + INSERT INTO test VALUES (NULL, 'Hello Sqlite3') +]] + +for row in db:nrows("SELECT * FROM test") do + print(row.id, row.content) +end diff --git a/3rdparty/lsqlite3/examples/smart.lua b/3rdparty/lsqlite3/examples/smart.lua new file mode 100644 index 00000000000..db56c1c1b5c --- /dev/null +++ b/3rdparty/lsqlite3/examples/smart.lua @@ -0,0 +1,22 @@ + +local sqlite3 = require("lsqlite3") + +local db = sqlite3.open_memory() + +db:exec[[ CREATE TABLE test (id INTEGER PRIMARY KEY, content) ]] + +local stmt = db:prepare[[ INSERT INTO test VALUES (:key, :value) ]] + +stmt:bind_names{ key = 1, value = "Hello World" } +stmt:step() +stmt:reset() +stmt:bind_names{ key = 2, value = "Hello Lua" } +stmt:step() +stmt:reset() +stmt:bind_names{ key = 3, value = "Hello Sqlite3" } +stmt:step() +stmt:finalize() + +for row in db:nrows("SELECT * FROM test") do + print(row.id, row.content) +end diff --git a/3rdparty/lsqlite3/examples/statement.lua b/3rdparty/lsqlite3/examples/statement.lua new file mode 100644 index 00000000000..acf9cb4ade6 --- /dev/null +++ b/3rdparty/lsqlite3/examples/statement.lua @@ -0,0 +1,39 @@ + +local sqlite3 = require("lsqlite3") + +local db = sqlite3.open_memory() + +db:exec[[ + CREATE TABLE test ( + id INTEGER PRIMARY KEY, + content VARCHAR + ); +]] + +local insert_stmt = assert( db:prepare("INSERT INTO test VALUES (NULL, ?)") ) + +local function insert(data) + insert_stmt:bind_values(data) + insert_stmt:step() + insert_stmt:reset() +end + +local select_stmt = assert( db:prepare("SELECT * FROM test") ) + +local function select() + for row in select_stmt:nrows() do + print(row.id, row.content) + end +end + +insert("Hello World") +print("First:") +select() + +insert("Hello Lua") +print("Second:") +select() + +insert("Hello Sqlite3") +print("Third:") +select() diff --git a/3rdparty/lsqlite3/examples/tracing.lua b/3rdparty/lsqlite3/examples/tracing.lua new file mode 100644 index 00000000000..e72e3907b38 --- /dev/null +++ b/3rdparty/lsqlite3/examples/tracing.lua @@ -0,0 +1,20 @@ + +local sqlite3 = require("lsqlite3") + +local db = sqlite3.open_memory() + +db:trace( function(ud, sql) + print("Sqlite Trace:", sql) +end ) + +db:exec[[ + CREATE TABLE test ( id INTEGER PRIMARY KEY, content VARCHAR ); + + INSERT INTO test VALUES (NULL, 'Hello World'); + INSERT INTO test VALUES (NULL, 'Hello Lua'); + INSERT INTO test VALUES (NULL, 'Hello Sqlite3'); +]] + +for row in db:rows("SELECT * FROM test") do + -- NOP +end diff --git a/3rdparty/lsqlite3/examples/update_hook.lua b/3rdparty/lsqlite3/examples/update_hook.lua new file mode 100644 index 00000000000..3bce0a2795c --- /dev/null +++ b/3rdparty/lsqlite3/examples/update_hook.lua @@ -0,0 +1,31 @@ + +local sqlite3 = require("lsqlite3") + +local db = sqlite3.open_memory() + +optbl = { [sqlite3.UPDATE] = "UPDATE"; + [sqlite3.INSERT] = "INSERT"; + [sqlite3.DELETE] = "DELETE" + } +setmetatable(optbl, + {__index=function(t,n) return string.format("Unknown op %d",n) end}) + +udtbl = {0, 0, 0} + +db:update_hook( function(ud, op, dname, tname, rowid) + print("Sqlite Update Hook:", optbl[op], dname, tname, rowid) +end, udtbl) + +db:exec[[ + CREATE TABLE test ( id INTEGER PRIMARY KEY, content VARCHAR ); + + INSERT INTO test VALUES (NULL, 'Hello World'); + INSERT INTO test VALUES (NULL, 'Hello Lua'); + INSERT INTO test VALUES (NULL, 'Hello Sqlite3'); + UPDATE test SET content = 'Hello Again World' WHERE id = 1; + DELETE FROM test WHERE id = 2; +]] + +for row in db:nrows("SELECT * FROM test") do + print(row.id, row.content) +end |