Beginning Lua Programming (2007)
Chapter 14. Managing Information with Databases
As a programmer, you'll sooner or later need to store information in between invocations of a program. You can store this information in a text file and decode it with patterns, or you can store it in a Lua file and reloade it with loadfile. But sometimes these methods aren't sufficiently fast, powerful, flexible, or scalable, in which case, you should store the information in a database. This chapter is a very brief introduction to databases and database systems. It will cover the following:
· How data is organized in and retrieved from a database
· SQL, the special-purpose language used to interact with databases
· LuaSQL, a set of Lua bindings for a number of popular database systems
Some Basic Relational Database Concepts
This section demonstrates some basic database concepts by having you implement a simple database and a system for retrieving data from it entirely in Lua.
Almost all databases in use today are what are known as relational databases because they are designed to work with relationships between things (the previous generation of databases was not good at this). In the following example, the things in question are customers, products, and orders of an imaginary business. The relational aspect is the fact that the orders are defined in terms of the products ordered and the customers who ordered them.
Try It Out
Creating a Simple Database Entirely in Lua
1. Save the following as join.lua:
-- A demonstration of a simple relational database with join
-- functionality entirely in Lua.
-- Example database tables:
Cust = {
{Id = "C001", NameLast = "Bumppo", NameFirst = "Natty"},
{Id = "C002", NameLast = "Finn", NameFirst = "Huckleberry"},
{Id = "C003", NameLast = "Darcy", NameFirst = "Fitzwilliam"},
{Id = "C004", NameLast = "Bennet", NameFirst = "Elizabeth"},
{Id = "C005", NameLast = "Marner", NameFirst = "Silas"},
}
Product = {
{Id = "P001", DescStr = "whatchamacallit"},
{Id = "P002", DescStr = "gizmo"},
{Id = "P003", DescStr = "gewgaw"},
{Id = "P004", DescStr = "thingamajig"},
{Id = "P005", DescStr = "widget"},
{Id = "P006", DescStr = "doodad"},
{Id = "P007", DescStr = "whatsit"},
}
Order = {
{Id = "O001", CustId = "C003", ProductId = "P002", Count = 52},
{Id = "O002", CustId = "C003", ProductId = "P004", Count = 87},
{Id = "O003", CustId = "C004", ProductId = "P001", Count = 12},
{Id = "O004", CustId = "C004", ProductId = "P003", Count = 8},
{Id = "O005", CustId = "C004", ProductId = "P005", Count = 20},
{Id = "O006", CustId = "C002", ProductId = "P004", Count = 2},
}
-- Returns a new database table composed of the selected columns of
-- Tbl1 and Tbl2 where WhereFnc is true:
function Join(Tbl1, Tbl1Select, Tbl2, Tbl2Select, WhereFnc)
local Ret = {}
-- For each pairing of rows:
for _, Tbl1Row in ipairs(Tbl1) do
for _, Tbl2Row in ipairs(Tbl2) do
if WhereFnc(Tbl1Row, Tbl2Row) then
-- WhereFnc is true, so include the selected fields of this
-- pairing in the result:
local RetRow = {}
for _, Tbl1Col in ipairs(Tbl1Select) do
RetRow[Tbl1Col] = Tbl1Row[Tbl1Col]
end
for _, Tbl2Col in ipairs(Tbl2Select) do
RetRow[Tbl2Col] = Tbl2Row[Tbl2Col]
end
Ret[#Ret + 1] = RetRow
end
end
end
return Ret
end
-- Space-padding helper function for ShowDbTbl:
local function ShowField(Str, Width, Numeric)
local Pad = string.rep(" ", Width - string.len(Str))
if Numeric then
io.write(Pad, Str, " ")
else
io.write(Str, Pad, " ")
end
end
-- Displays a database table (an array of associative rows):
function ShowDbTbl(Tbl)
local ColList = {}
-- Get the width of each column name and type of each column:
for ColStr, Val in pairs(Tbl[1]) do
ColList[#ColList + 1] = {Name = ColStr,
Width = string.len(ColStr), Numeric = type(Val) == "number"}
end
-- Get the maximum width of each column:
for _, Row in ipairs(Tbl) do
for _, Col in ipairs(ColList) do
Col.Width = math.max(string.len(Row[Col.Name]), Col.Width)
end
end
-- Display a column header:
for _, Col in ipairs(ColList) do
ShowField(Col.Name, Col.Width, Col.Numeric)
end
io.write("\n")
for _, Col in ipairs(ColList) do
ShowField(string.rep("-", Col.Width), Col.Width, false)
end
io.write("\n")
-- Display the rows:
for _, Row in ipairs(Tbl) do
for _, Col in ipairs(ColList) do
ShowField(Row[Col.Name], Col.Width, Col.Numeric)
end
io.write("\n")
end
end
-- Demonstration:
CustOrder = Join(Cust, {"NameLast"},
Order, {"ProductId", "Count"},
function(Cust, Order) return Cust.Id == Order.CustId end)
print("*** Cust joined to Order ***")
ShowDbTbl(CustOrder)
print()
CustOrderProduct = Join(CustOrder, {"NameLast", "Count"},
Product, {"DescStr"},
function(CustOrder, Product)
return CustOrder.ProductId == Product.Id
end)
print("*** Cust joined to Order joined to Product ***")
ShowDbTbl(CustOrderProduct)
print()
Bennet = Join(CustOrder, {"Count"},
Product, {"DescStr"},
function(CustOrder, Product)
return CustOrder.ProductId == Product.Id
and CustOrder.NameLast == "Bennet"
end)
print("*** All orders by customer 'Bennet' ***")
ShowDbTbl(Bennet)
2. Run it with this:
lua join.lua
The output will be:
*** Cust joined to Order ***
Count NameLast ProductId
----- -------- ---------
2 Finn P004
52 Darcy P002
87 Darcy P004
12 Bennet P001
8 Bennet P003
20 Bennet P005
*** Cust joined to Order joined to Product ***
DescStr Count NameLast
--------------- ----- --------
thingamajig 2 Finn
gizmo 52 Darcy
thingamajig 87 Darcy
whatchamacallit 12 Bennet
gewgaw 8 Bennet
widget 20 Bennet
*** All orders by customer 'Bennet' ***
Count DescStr
----- ---------------
12 whatchamacallit
8 gewgaw
20 widget
How It Works
The word “table” has two meanings in this chapter. The Lua Cust, Product, and Order tables are also tables in a database-specific sense. They are called tables because they can be visualized as arrangements of rows and columns. The columns have names, but the rows don't. For instance, take a look at this table of customers:
Cust = {
{Id = "C002", NameLast = "Finn", NameFirst = "Huckleberry"},
{Id = "C003", NameLast = "Darcy", NameFirst = "Fitzwilliam"},
{Id = "C004", NameLast = "Bennet", NameFirst = "Elizabeth"},
{Id = "C005", NameLast = "Marner", NameFirst = "Silas"},
}
The following table shows how you can visualize this.
An array of associative tables is just one of several ways to conveniently represent a database table as a Lua table.
In another intersection with Lua terminology, database column names (such as NameLast) can also be called field names, and particular columns in particular rows can be called fields. For example, the NameLast field of the second row in the previous table is “Finn.”
The following call does what is known as a join between the Cust table and the Order table:
CustOrder = Join(Cust, {"NameLast"},
Order, {"ProductId", "Count"},
function(Cust, Order) return Cust.Id == Order.CustId end)
The Join function takes a database table (a table in the previously described row/column format), a (not necessarily exhaustive) list of field names from that table, another table, a list of field names from that table, and a two-argument function. Using nested for loops, it goes through every possible pairing of one row from the first table and one from the second (in this case, five times six = thirty pairings) as follows:
function Join(Tbl1, Tbl1Select, Tbl2, Tbl2Select, WhereFnc)
local Ret = {}
for _, Tbl1Row in ipairs(Tbl1) do
for _, Tbl2Row in ipairs(Tbl2) do
if WhereFnc(Tbl1Row, Tbl2Row) then
If a pairing makes WhereFnc return true, then the selected fields of that pairing are added to the table to be returned (which itself is a row/column-formatted database table) like this:
if WhereFnc(Tbl1Row, Tbl2Row) then
local RetRow = {}
for _, Tbl1Col in ipairs(Tbl1Select) do
RetRow[Tbl1Col] = Tbl1Row[Tbl1Col]
end
for _, Tbl2Col in ipairs(Tbl2Select) do
RetRow[Tbl2Col] = Tbl2Row[Tbl2Col]
end
Ret[#Ret + 1] = RetRow
end
So joining two tables means getting the selected fields for all pairings of rows where WhereFnc is true (which is why it's called WhereFnc). In the join between Cust and Order, the WhereFnc returns true only if Cust's Id is equal to Order's CustId. Because each row of Order has a unique Id, and each ofOrder's CustId fields has a corresponding Id field in Cust, this join returns one row for every row in Order. Specifically it returns the following for every order:
· The last name of the customer who made the order
· The product ID
· The quantity of the product ordered
For example, this shows that Huckleberry Finn ordered two of product P004:
*** Cust joined to Order ***
Count NameLast ProductId
----- -------- ---------
2 Finn P004
52 Darcy P002
87 Darcy P004
12 Bennet P001
8 Bennet P003
20 Bennet P005
This Join function operates on only two database tables at a time, but because the result is itself a database table, it can be joined with yet another table. That's how the product descriptions for each order are retrieved — the join between CustOrder and Product gets every order's customer name, count, and product description as follows:
CustOrderProduct = Join(CustOrder, {"NameLast", "Count"},
Product, {"DescStr"},
function(CustOrder, Product)
return CustOrder.ProductId == Product.Id
end)
The result shows that the product (P004) that Huckleberry Finn ordered two of is a thingamajig:
*** Cust joined to Order joined to Product ***
DescStr Count NameLast
--------------- ----- --------
thingamajig 2 Finn
gizmo 52 Darcy
thingamajig 87 Darcy
whatchamacallit 12 Bennet
gewgaw 8 Bennet
widget 20 Bennet
The two joins used their WhereFncs to determine which rows of one table go with which rows of the other. The third join's WhereFnc does this, but then it disqualifies some of those rows for not having the right NameLast field, as follows:
Bennet = Join(CustOrder, {"Count"},
Product, {"DescStr"},
function(CustOrder, Product)
return CustOrder.ProductId == Product.Id
and CustOrder.NameLast == "Bennet"
end)
The result shows only Elizabeth Bennet's three orders and none of the other customers' orders:
*** All orders by customer 'Bennet' ***
Count DescStr
----- ---------------
12 whatchamacallit
8 gewgaw
20 widget
If a WhereFnc always returns true, it pairs every row of the first table with every row of the second table, even if those rows have nothing to do with each other. To demonstrate this, run the Lua interpreter (in the same directory as join.lua) and type:
> dofile("join.lua")
The join.lua functions and database tables are global, so after it's done printing, you can do the following:
> ShowDbTbl(Join(Cust, {"NameLast"},
>> Order, {"ProductId", "Count"},
>> function() return true end))
This will print out 30 rows. Here are the first 10:
Count NameLast ProductId
----- -------- ---------
52 Bumppo P002
87 Bumppo P004
12 Bumppo P001
8 Bumppo P003
20 Bumppo P005
2 Bumppo P004
52 Finn P002
87 Finn P004
12 Finn P001
8 Finn P003
This (joining every row of one table with every row of the other) is called a cross join or Cartesian product. It is important for two reasons:
· It is the theoretical basis for all other joins. (A cross join will have as many rows as the Join function's nested loops have iterations.)
· You may sometimes do it by mistake.
An example of doing it by mistake would be leaving out the CustOrder.ProductId-==-Product.Id condition in the WhereFnc to get all of Elizabeth Bennet's orders:
Bennet = Join(CustOrder, {"Count"},
Product, {"DescStr"},
function(CustOrder, Product)
return CustOrder.NameLast == "Bennet" -- UH-OH, CROSS JOIN!
end)
ShowDbTbl(Bennet)
This will print Elizabeth's three orders, mixed in with 18 orders that don't even exist (they're junk generated by the cross join). Another way of putting that is that it prints each of Elizabeth's three orders seven times, with only one of the seven having the right DescStr, as shown here:
Count DescStr
----- ---------------
12 whatchamacallit
12 gizmo
12 gewgaw
12 thingamajig
12 widget
12 doodad
12 whatsit
8 whatchamacallit
8 gizmo
8 gewgaw
8 thingamajig
8 widget
8 doodad
8 whatsit
20 whatchamacallit
20 gizmo
20 gewgaw
20 thingamajig
20 widget
20 doodad
20 whatsit
In addition to showing what tables, rows, columns, and joins are, join.lua demonstrates a few other things.
One is a principle of database design: don't duplicate information. The Order table could have looked like this:
Order = {
{Id = "O001", NameLast = "Darcy", NameFirst = "Fitzwilliam",
DescStr = "gizmo", Count = 52},
{Id = "O002", NameLast = "Darcy", NameFirst = "Fitzwilliam",
DescStr = "thingamajig", Count = 87},
{Id = "O003", NameLast = "Bennet", NameFirst = "Elizabeth",
DescStr = "whatchamacallit", Count = 12},
{Id = "O004", NameLast = "Bennet", NameFirst = "Elizabeth",
DescStr = "gewgaw", Count = 8},
{Id = "O005", NameLast = "Bennet", NameFirst = "Elizabeth",
DescStr = "widget", Count = 20},
{Id = "O006", NameLast = "Finn", NameFirst = "Huckleberry",
DescStr = "thingamajig", Count = 2},
}
This would have made Cust and Product unnecessary, but if Elizabeth Bennet got married to Fitzwilliam Darcy and needed to change her last name, it would need to be changed in three places rather than one, which is a hassle and an opportunity for error. Also, real-world database systems generally don't do the string interning discussed in Chapter 10, which means that three occurrences of "Bennet" take up three times as much space as one.
If you read up on databases, you'll find out about “normal forms,” which for the most part are applications of the “don't duplicate information” principle. The principle is sometimes disobeyed for reasons of efficiency and simplicity.
Another lesson of join.lua is that a real database system needs to be more optimized. Imagine if there were a thousand customers and one hundred thousand orders. Finding all orders with the last name “Bennet” would make the nested loop in Join do one hundred million iterations.
This is solved in real database systems by using indexes. This is yet another meaning for a word already well-laden with meanings. In this case, you should think of an index in the back of a book, which lets you go right to the page or pages where a topic is mentioned. An index for Cust'sNameLast field might look like this:
{Bumppo = {1}, Finn = {2}, Darcy = {3}, Bennet = {4},
Marner = {5}}
If Join were able to look inside the WhereFnc and see that the last name “Bennet” was being sought, the table would tell it that the fourth element of Cust had that last name. (The reason the numbers are wrapped in arrays is in case multiple customers share a last name.)
The previous table wouldn't speed up a search for “all customers whose last names start with B,” but an index that would speed it up might look like this:
{{NameLast = "Bennet", 4},
{NameLast = "Bumppo", 1},
{NameLast = "Darcy", 3},
{NameLast = "Finn", 2},
{NameLast = "Marner", 5}}
This array is sorted by last name, so a technique called binary search can be used to do the following:
1. Jump into the middle of the array.
2. If the array element in the middle's NameLast starts with "B", then the B's have been found.
3. If not, repeat these steps with the first half or the second half of the array, depending on whether the just-tested element's NameLast starts with a letter after or before "B".
If there are a lot of customers, this method is much faster than searching through every single customer, because with every iteration, the distance to the thing being searched for is halved or more than halved.
Use of database indexes is outside the scope of this chapter, but all serious database systems offer some way to control what indexes are created.
SQL, LuaSQL, and MySQL
Most database systems use a language called SQL to express interaction with a database. (It's pronounced “ESS-CUE-ELL,” or sometimes even “SEE-kwuhll,” and it stands for Structured Query Language, but nobody actually uses the full name.) SQL was designed specifically for this purpose, and is not really usable as a general-purpose language, so it is generally combined with a language (like Lua) that is good for general-purpose programming. A program that needs to interact with a database is written in the general-purpose language, except for those parts of program that deal with the database, which are written in SQL.
There are many database systems, all with their own pluses and minuses (and all with their own slightly different dialects of SQL). One is SQLite, which you met in Chapter 12. Another popular one is MySQL (pronounced “MY-ESS-CUE-ELL”).
LuaSQL is a collection of Lua bindings to these and other database systems (PostgreSQL, ODBC, JDBC, Oracle, and ADO). In the remainder of this chapter, you'll be using the LuaSQL binding to MySQL.
LuaSQL currently only exists for Lua 5.0. (That's why the examples here use string.len instead of #.)
MySQL and LuaSQL are available respectively on dev.mysql.com and www.keplerproject.org/luasql/. You will need to download and install both (according to the instructions on their websites) for the following example to work.
LuaSQL is part of the Kepler project (www.keplerproject.org), so if you have Kepler installed, then you already have LuaSQL. The following example assumes that a Lua 5.0 interpreter called lua50 is in your search path. If this is not the case, give the full path to the interpreter, changing the following line:
lua50 mysql.lua Create
to this (for example):
"C:\ProgramFiles\Kepler\lua50" mysql.lua Create
The script init.lua in the Kepler directory makes sure that require knows where to look for the LuaSQL module. The Kepler installer sets your shell's LUA_INIT environment variable to @C:\Program Files\Kepler\init.lua (or something similar, depending on where Kepler is installed). This value for LUA_INITcauses Lua to run init.lua before running mysql.lua. (init.lua has no effect on Lua 5.1.)
Try It Out
Creating a Customers, Products, and Orders Table in MySQL
In this exercise, you programmatically interact with the MySQL database server by means of a Lua script.
1. At your shell, use the following mysqladmin function to create a new MySQL database called Lua":
mysqladmin -u root -p create Lua
You'll be prompted for a password. If you set up a password for the root (administrator) account when you installed MySQL, type that one. If no password has been set, press Enter.
2. Invoke the MySQL client application as follows:
mysql -u root -p
After submitting the MySQL root password, you'll see a mysql> prompt.
3. At the mysql> prompt, create a new MySQL user account with the username Lua by typing the following:
'SmPlPaSs' is the new account's password in this example.
GRANT ALL PRIVILEGES ON Lua.* TO 'Lua'@'localhost'
IDENTIFIED BY 'SmPlPaSs';
4. Type exit, quit, or \q to leave the MySQL client.
5. With your text editor, create a file with the following contents and save it as mysql.lua:
require("luasql.mysql")
-- Executes all of the the SQL statements in List (stopping if one
-- fails):
function ExecSqlList(Conn, List)
local Succ, ErrStr
for _, SqlStr in ipairs(List) do
Succ, ErrStr = Conn:execute(SqlStr)
if not Succ then break end -- BREAK ON ERROR.
end
return Succ, ErrStr
end
-- Space-padding helper function for ShowCursor:
local function ShowField(Str, Width, Numeric)
local Pad = string.rep(" ", Width - string.len(Str))
if Numeric then
io.write(Pad, Str, " ")
else
io.write(Str, Pad, " ")
end
end
-- Displays all the rows in a database cursor, then closes the
-- cursor:
function ShowCursor(Cursor)
---- Get the name of each column and that name's width:
local ColNames = Cursor:getcolnames()
local ColWidths = {}
for I, Name in ipairs(ColNames) do
ColWidths[I] = string.len(Name)
end
-- Find out which columns hold numbers:
local ColTypes = Cursor:getcoltypes()
local ColIsNums = {}
for I, Type in ipairs(ColTypes) do
if string.sub(Type, 1, 6) == "number" then
ColIsNums[I] = true
else
ColIsNums[I] = false
end
end
-- A wrapper for Cursor:fetch that lets it return a table without
-- being given one:
local function RowsIter()
local Row = {}
return Cursor:fetch(Row)
end
-- Store all rows and the maximum widths of all columns:
local Rows = {}
for Row in RowsIter do
table.insert(Rows, Row)
for I, Field in ipairs(Row) do
ColWidths[I] = math.max(ColWidths[I], string.len(Field))
end
end
-- Display a column header:
for I, ColName in ipairs(ColNames) do
ShowField(ColName, ColWidths[I], ColIsNums[I])
end
io.write("\n")
for I, ColWidth in ipairs(ColWidths) do
ShowField(string.rep("-", ColWidth), ColWidth, false)
end
io.write("\n")
-- Display the rows:
for _, Row in ipairs(Rows) do
for I, Field in ipairs(Row) do
ShowField(Field, ColWidths[I], ColIsNums[I])
end
io.write("\n")
end
Cursor:close()
end
-- Creates the Cust, Product, and Ord tables:
function Create(Conn)
return ExecSqlList(Conn, {[[
CREATE TABLE Cust (
Id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
NameLast CHAR(50),
NameFirst CHAR(50))]], [[
CREATE TABLE Product (
Id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
DescStr CHAR(50))]], [[
CREATE TABLE Ord (
Id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
CustId INT UNSIGNED,
ProductId INT UNSIGNED,
Count INT UNSIGNED)]]})
end
-- Puts rows into Cust, Product, and Ord:
function Populate(Conn)
return ExecSqlList(Conn, {[[
TRUNCATE TABLE Cust]], [[
INSERT INTO Cust (NameLast, NameFirst) VALUES
('Bumppo', 'Natty'),
('Finn', 'Huckleberry'),
('Darcy', 'Fitzwilliam'),
('Bennet', 'Elizabeth'),
('Marner', 'Silas')]], [[
TRUNCATE TABLE Product]], [[
INSERT INTO Product (DescStr) VALUES
('whatchamacallit'), ('gizmo'), ('gewgaw'), ('thingamajig'),
('widget'), ('doodad'), ('whatsit')]], [[
TRUNCATE TABLE Ord]], [[
-- These CustIds and ProductIds are hardcoded, which means this
-- INSERT would break if the previous INSERTs' auto-increment IDs
-- didn't start at 1, but the TRUNCATEs make sure they do:
INSERT INTO Ord (CustId, ProductId, Count) VALUES
(3, 2, 52),
(3, 4, 87),
(4, 1, 12),
(4, 3, 8),
(4, 5, 20),
(2, 4, 2)]]})
end
-- Does some sample SELECTs (joins) and displays the results:
function Test(Conn)
local Cursor, ErrStr
Cursor, ErrStr = Conn:execute([[
SELECT NameLast, ProductId, Count
FROM Cust, Ord
WHERE Cust.Id = Ord.CustId]])
if Cursor then
print("*** Cust joined to Order ***")
ShowCursor(Cursor)
print()
Cursor, ErrStr = Conn:execute([[
SELECT NameLast, Count, DescStr
FROM Cust, Ord, Product
WHERE Cust.Id = Ord.CustId
AND Ord.ProductId = Product.Id]])
if Cursor then
print("*** Cust joined to Order joined to Product ***")
ShowCursor(Cursor)
print()
Cursor, ErrStr = Conn:execute([[
SELECT Count, DescStr
FROM Cust, Ord, Product
WHERE Cust.Id = Ord.CustId
AND Ord.ProductId = Product.Id
AND Cust.NameLast = 'Bennet']])
if Cursor then
print("*** All orders by customer 'Bennet' ***")
ShowCursor(Cursor)
end
end
end
return not ErrStr, ErrStr
end
-- Drops the Cust, Product, and Ord tables:
function Drop(Conn)
return Conn:execute("DROP TABLE IF EXISTS Cust, Product, Ord")
end
-- Get what the LuaSQL documentation calls an "environment":
local MysqlEnv, ErrStr = luasql.mysql()
if MysqlEnv then
-- Get a connection to the "Lua" database as the user "Lua":
local Conn
Conn, ErrStr = MysqlEnv:connect("Lua", "Lua", "SmPlPaSs")
if Conn then
-- Obey the command given as a command-line argument:
local Cmd = arg[1]
local Succ
if Cmd == "Create" then
Succ, ErrStr = Create(Conn)
if Succ then
print("Successfully created Cust, Product, and Ord tables")
end
elseif Cmd == "Populate" then
Succ, ErrStr = Populate(Conn)
if Succ then
print("Successfully populated Cust, Product, and Ord tables")
end
elseif Cmd == "Test" then
Succ, ErrStr = Test(Conn)
elseif Cmd == "Drop" then
Succ, ErrStr = Drop(Conn)
if Succ then
print("Successfully dropped Cust, Product, and Ord tables")
end
else
ErrStr = "A command-line argument of 'Create', 'Populate',"
.. " 'Test', or 'Drop' is required"
end
Conn:close()
end
MysqlEnv:close()
end
if ErrStr then
io.stderr:write(ErrStr, "\n")
end
6. At the shell, run it with the Create command-line argument as follows:
lua50 mysql.lua Create
It will print this:
Successfully created Cust, Product, and Ord tables
7. Run it again with the Populate command-line argument as follows:
lua50 mysql.lua Populate
It will print this:
Successfully populated Cust, Product, and Ord tables
8. Run it one more time with the Test command-line argument as follows:
lua50 mysql.lua Test
It will print this:
*** Cust joined to Order ***
NameLast ProductId Count
-------- --------- -----
Finn 4 2
Darcy 2 52
Darcy 4 87
Bennet 1 12
Bennet 3 8
Bennet 5 20
*** Cust joined to Order joined to Product ***
NameLast Count DescStr
-------- ----- ---------------
Finn 2 thingamajig
Darcy 52 gizmo
Darcy 87 thingamajig
Bennet 12 whatchamacallit
Bennet 8 gewgaw
Bennet 20 widget
*** All orders by customer 'Bennet' ***
Count DescStr
----- ---------------
12 whatchamacallit
8 gewgaw
20 widget
How It Works
require("luasql.mysql") creates the luasql namespace table and puts a function in it called mysql. With this function, an object (called an environment in the LuaSQL documentation) is created. This object has a connect method, with which a connection is opened to the database Lua as the userLua with the password SmPlPaSs:
Conn, ErrStr = MysqlEnv:connect("Lua", "Lua", "SmPlPaSs")
After this connection object is obtained, the command-line argument decides which function to run. The first one run is Create, which calls the function ExecSqlList with the connection object and a three-element array as arguments. The array's first element is this string:
CREATE TABLE Cust (
Id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
NameLast CHAR(50),
NameFirst CHAR(50))
This is what SQL looks like. SQL keywords, function names, and so on are case-insensitive, but they are often typed in all-uppercase as shown. This is partly to make SQL's rather baroque syntax a bit easier to process visually, and partly just out of tradition. In MySQL, column names are case-sensitive, but database names and table names are only case-sensitive if the filesystem where the database is stored is case-sensitive.
The preceding CREATE-TABLE statement creates a table called Cust with columns Id, NameLast, and NameFirst. In MySQL, as in most database systems (SQLite being a notable exception), a given column can hold only one datatype. The CHAR(50) after NameLast and NameFirst means that those columns can
each hold a string of up to 50 characters. The INT-UNSIGNED-NOT-NULL after Id means that Id can hold an unsigned integer, but cannot hold the special value NULL. PRIMARY-KEY has to do with the indexing described at the end of the previous section. (AUTO_INCREMENT will be explained later.)
This and the two other CREATE-TABLE statements are passed to ExecSqlList, which uses the connection object's execute method to execute them, creating three tables.
The Order table from the example earlier in this chapter was renamed Ord here because Order is a reserved word in MySQL.
Next, the Populate function is called, which fills the three tables with data. The TRUNCATE statements clean out anything that's already in the tables (so that things will still work if you run Populate twice), and the INSERT-INTO statements do the actual insertion of data.
Notice that values are inserted into all the columns except for the Id columns. This is because the Id columns of all three tables are auto-increment columns, which means that the first row inserted into a given table will get an Id of 1, the next will get an Id of 2, and so on. In this example, where all the data is being loaded at once, it would be just as easy to hard-code the Id fields, but the auto-increment functionality shows its usefulness in more realistic programs, where non-hardcoded rows are added one at a time.
Finally, Test is called. It executes three SELECT statements and uses ShowCursor to display the results. Each of these SELECTS consists of three parts:
· A SELECT clause, which names the columns that will be in the result. This is equivalent to the second and fourth arguments of the Join function from earlier in the chapter.
· A FROM clause, which names the tables that the previously selected columns are in. This is equivalent to the first and third arguments of Join.
· A WHERE clause, which constrains which rows will be in the result. This is equivalent to Join's fifth (WhereFnc) argument. If no WHERE clause is given, but multiple tables are given in the FROM clause, then a cross join is done.
In both the SELECT clause and the WHERE clause, a column can be specified with or without its table name: TblName.ColName or ColName. The first form is mandatory if the same column name is used in two tables named in the FROM clause. This WHERE clause:
SELECT NameLast, ProductId, Count
FROM Cust, Ord
WHERE Cust.Id = Ord.CustId
could have been written WHERE Cust.Id-=-CustId, because Cust doesn't have a CustId column, but not WHERE-Id-=-CustId, because Ord does have an Id column. (The = in SQL means the same thing as == in Lua.)
More than two tables can be joined by one SELECT statement, by including them all in the FROM clause and the appropriate join conditions in the WHERE clause. If the FROM clause only names one table, then just rows from that table are selected, and no join is done.
These SELECTs are executed by the connection object's execute method:
local Cursor, ErrStr
Cursor, ErrStr = Conn:execute([[
SELECT NameLast, ProductId, Count
When execute is given a statement that doesn't return any data, like CREATE or INSERT, it returns a success Boolean. When it is given a SELECT, it returns a cursor object that is used to read the SELECT's results. The cursor has the following methods:
· getcolnames: Returns an array of the result's column names.
· getcoltypes: Returns an array of the datatypes of the result's columns.
· fetch: Returns the first row the first time it's called, the second row the second time it's called, and so on. It returns nil when there are no more rows.
· close: Closes the cursor.
The fetch method takes two optional arguments. If given no arguments, it returns as many values as there are columns. If given a Lua table as a first argument, it puts the columns of the row being returned into that table and returns it. If the second argument is not given or if it is "n" (for “numeric”), then the returned Lua table is an array indexed by column number such as the following:
("Finn", "4", "2"}
If the second argument is "a" (alphanumeric), then the returned table is associative by column name, like this:
(NameLast = "Finn", ProductId = "4", Count = "2"}
Notice also that LuaSQL's MySQL driver (as of this writing) returns even numeric values as strings.
The ShowCursor function uses these methods to display all the rows in the cursor given to it. Most of the work it does is formatting: getting the maximum width of each column and making sure that strings are left-justified and numbers are right-justified.
If you want to delete the Cust, Product, and Ord tables and start over, use Drop as a command-line argument to mysql.lua.
Summary
This has been a very brief introduction to databases. You haven't even learned about UPDATE and DELETE, the SQL statements that change and remove rows, nor have you learned about the nonintuitive behavior of the SQL value NULL. That information is available in the many books and websites about SQL.
Here are the highlights of what this chapter did cover:
· Relational databases store data in tables, which are organized in rows and columns.
· Combining the data from multiple tables is called joining those tables.
· Combining every single row of one table with every single row of another table is called a cross join.
· Duplication of data in a database should be avoided when possible.
· Database systems use indexes for faster searching.
· SQL is a special-purpose language for interacting with databases.
· The SQL CREATE statement creates a table. The INSERTINTO statement puts rows into a table. The SELECT statement reads rows from a table.
· LuaSQL is a set of Lua bindings for a number of popular database systems, including MySQL.
· LuaSQL returns the results of a SELECT as a cursor object with getcolnames, getcoltypes, fetch, and close methods.
Before you go on to the next chapter, which is about web programming, check out these exercises. (Answers are in the appendix.)
Exercises
1. Write a function that takes a LuaSQL cursor and returns a table in the format used by Join (an array of associative tables).
2. The following SELECT doesn't work — it returns an error message. Why?
SELECT Id, CustId, NameLast, ProductId, DescStr, Count
FROM Cust, Ord, Product
WHERE Cust.Id = Ord.CustId
AND Ord.ProductId = Product.Id