LUA_PATH = "?;?.lua;../?;../?.lua"


require "sqlite3"


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 = ?")
  return stmt:bind(id):first_cols()
end


local function all_invoices()
  return db:rows("SELECT id, customer, title FROM invoice")
end


local function all_articles(invoice)
  local wrap = coroutine.wrap
  local yield = coroutine.yield
  
  iterator = function()
    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 = ?")
    row = stmt:bind(invoice):first_row() 
    
    -- Every Invoice has at least one article.
    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.
      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")
      
      for row in stmt:bind(invoice):rows() do
        yield(row.article, row.price)
      end
    end
  end
  
  return wrap(iterator)
end



for invoice in all_invoices() do
  local id    = tostring(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(invoice.id) do
    print( string.format("%20s  %8.2f", article, price) )
  end
  
  print()
end




