summaryrefslogtreecommitdiffstatshomepage
path: root/3rdparty/lsqlite3/examples/order.lua
blob: c17b05f0df6ec552b683b0d1b5979d7706dd9c5e (plain) (blame)
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
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