diff options
Diffstat (limited to '3rdparty/lsqlite3/examples/order.lua')
-rw-r--r-- | 3rdparty/lsqlite3/examples/order.lua | 122 |
1 files changed, 122 insertions, 0 deletions
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 |