luasql - database interface (LuaSQL)
LuaSQL is a simple interface from Lua to a DBMS. It enables a Lua program to:
LuaSQL is a simple interface from Lua to a number of database management systems. It includes a set of drivers to some popular databases (currently PostgreSQL, ODBC, JDBC, MySQL, SQLite, Oracle, and ADO; Interbase and Sybase are in our plans). LuaSQL defines a simple object-oriented API. All drivers should implement this common API, but each one is free to offer extensions.
LuaSQL defines one single global variable, a table called luasql.
This table is used to store the initialization methods of the loaded
drivers. These methods are used to create an environment object
which is used to create a connection object.
A connection object can execute SQL statements and eventually create a
cursor object which is used to retrieve data.
Here is an example of the basic use of the library. After that, another example shows how to create an iterator over the result of a SELECT query.
-- load driver
require "luasql.postgres"
-- create environment object
env = assert (luasql.postgres())
-- connect to data source
con = assert (env:connect("luasql-test"))
-- reset our table
res = con:execute"DROP TABLE people"
res = assert (con:execute[[
CREATE TABLE people(
name varchar(50),
email varchar(50)
)
]])
-- add a few elements
list = {
{ name="Jose das Couves", email="jose@couves.com", },
{ name="Manoel Joaquim", email="manoel.joaquim@cafundo.com", },
{ name="Maria das Dores", email="maria@dores.com", },
}
for i, p in pairs (list) do
res = assert (con:execute(string.format([[
INSERT INTO people
VALUES ('%s', '%s')]], p.name, p.email)
))
end
-- retrieve a cursor
cur = assert (con:execute"SELECT name, email from people")
-- print all rows, the rows will be indexed by field names
row = cur:fetch ({}, "a")
while row do
print(string.format("Name: %s, E-mail: %s", row.name, row.email))
-- reusing the table of results
row = cur:fetch (row, "a")
end
-- close everything
cur:close()
con:close()
env:close()
And the output of this script should be:
Name: Jose das Couves, E-mail: jose@couves.com Name: Manoel Joaquim, E-mail: manoel.joaquim@cafundo.com Name: Maria das Dores, E-mail: maria@dores.com
It may be useful to offer an iterator for the resulting rows:
function rows (connection, sql_statement)
local cursor = assert (connection:execute (sql_statement))
return function ()
return cursor:fetch()
end
end
Here is how the iterator is used:
require "luasql.mysql"
env = assert (luasql.mysql())
con = assert (env:connect"my_db")
for id, name, address in rows (con, "select * from contacts") do
print (string.format ("%s: %s", name, address))
end
The above implementation relies on the garbage collector to close the cursor. It could be improved to give better error messages (including the SQL statement) or to explicitly close the cursor (by checking whether there are no more rows).
Source code for LuaSQL can be downloaded from the LuaForge (http://luaforge.net/project/showfiles.php) page. If you are using LuaBinaries (http://luaforge.net/projects/luabinaries) Release 2, a Windows executable version of LuaSQL can be found at the same LuaForge page.
LuaSQL is distributed as a set of C source files: a pair of common
source and header files (luasql.h and luasql.c); and one source
file for each driver. Each driver should be compiled with the
luasql.c file to generate a library. This library can be linked to
the application or dynamically loaded. The initialization function is
luaopen_luasqldrivername and it is a Lua C open-library
(http://www.lua.org/manual/5.1/manual.html#pdf-require) compatible
function.
All LuaSQL drivers follow the package model
(http://www.keplerproject.org/compat) for Lua 5.1 and therefore
should be ``installed''. Refer to the Compat-5.1 configuration
(http://www.keplerproject.org/compat/manual.html#configuration)
section to learn how to install the compiled binary. The compiled
binary should be copied to a directory named luasql in your
LUA_CPATH.
Windows users can use the compiled versions of LuaSQL available at LuaForge (http://luaforge.net/projects/luasql/files)
In order to use LuaSQL with JDBC, make sure that:
In order to use LuaSQL with ADO, make sure that Lua is running with LuaCOM 1.3 (http://www.tecgraf.puc-rio.br/~rcerq/luacom).
LuaSQL is just an abstraction layer that communicates between Lua and a database system. Therefore errors can occur on both levels, that is, inside the database client or inside LuaSQL driver.
Errors such as malformed SQL statements, unknown table names etc. are
called database errors and will be reported by the function/method
returning nil followed by the error message provided by the
database system. Errors such as wrong parameters, absent connection,
invalid objects etc., called API errors, are usually program errors
and so will raise a Lua error.
This behavior will be followed by all functions/methods described in this document unless otherwise stated.
A LuaSQL driver allows the use of the LuaSQL API with a database
management system that corresponds to the driver. To use a driver you
have to load it in the luasql table. The example below
require "luasql.odbc"
loads the ODBC driver in the luasql table. Note that you can have
more than one driver loaded at the same time doing something like:
require "luasql.odbc" require "luasql.oci8"
This example also shows that the driver name not always correspond to
the Database name, but to the driver name in the file system. Since it
refers to the OCI8 API, the Oracle driver has the name oci8 instead
of oracle.
Some drivers, such as the MySQL, have libraries for a number of
database versions that use the same file name (mysql). In this case
it is not possible to load more than one version of the MySQL driver
in the luasql table.
An environment object is created by calling the driver's
initialization function that is stored in the luasql table, indexed
with the same name as the driver (odbc, postgres etc). For example,
env = luasql.odbc()
will try to create an environment object using the ODBC driver. The
only exception is the JDBC driver, which needs to know which internal
driver to use. Therefore, when creating an environment, the driver
class name must be passed as the first parameter to the luasql.jdbc
function. For example:
env = luasql.jdbc ("com.mysql.jdbc.Driver")
env:closeenv:close()
Closes the environment env. Only successful if all connections
pertaining to it were closed first.
Returns: true in case of success; false when the object is
already closed.
env:connectenv:connect(sourcename[,username[,password]])
Connects to a data source specified in sourcename using username
and password if they are supplied.
The sourcename may vary according to each driver. Some use a
simple database name, like PostgreSQL, MySQL and SQLite; the ODBC
driver expects the name of the DSN; the Oracle driver expects the
service name; the JDBC driver expects a string like
"jdbc:<database system>://<database name>", which is
specific for each driver.
See also: PostgreSQL Extensions, and MySQL Extensions extensions.
Returns: a connection object.
A connection object contains specific attributes and parameters of a
single data source connection. A connection object is created by
calling the environment:connect (/env_connect) method.
conn:closeconn:close()
Closes the connection conn. Only successful if all cursors
pertaining to it have been closed and the connection is still open.
Returns: true in case of success and false in case of
failure.
conn:commitconn:commit()
Commits the current transaction. This feature might not work on database systems that do not implement transactions.
Returns: true in case of success and false when
the operation could not be performed or when it is not implemented.
conn:executeconn:execute(statement)
Executes the given SQL statement.
Returns: a cursor object if there are results, or the number of rows affected by the command otherwise.
conn:rollbackconn:rollback()
Rolls back the current transaction. This feature might not work on database systems that do not implement transactions.
Returns: true in case of success and false when
the operation could not be performed or when it is not implemented.
conn:setautocommitconn:setautocommit(boolean)
Turns on or off the ``auto commit'' mode. This feature might not work on database systems that do not implement transactions. On database systems that do not have the concept of ``auto commit mode'', but do implement transactions, this mechanism is implemented by the driver.
Returns: true in case of success and false when
the operation could not be performed or when it is not implemented.
A cursor object contains methods to retrieve data resulting from an
executed statement. A cursor object is created by using the
connection:execute function. See also PostgreSQL Extensions
and Oracle Extensions.
cur:closecur:close()
Closes this cursor.
Returns: true in case of success and false when
the object is already closed.
cur:fetchcur:fetch([table[,modestring]])
Retrieves the next row of results.
If fetch is called without parameters,
the results will be returned directly to the caller.
If fetch is called with a table, the results will be copied
into the table and the changed table will be returned.
In this case, an optional modestring parameter can be used.
It is just a string indicating how the resulting table should be constructed.
The mode string can contain:
"n""a"The numerical indices are the positions of the fields in the SELECT statement; the alphanumerical indices are the names of the fields.
The optional table parameter is a table that should be used to
store the next row. This allows the use of a unique table for many
fetches, which can improve the overall performance.
There is no guarantee about the types of the results: they may or may not be converted to adequate Lua types by the driver. In the current implementation, the PostgreSQL and MySQL drivers return all values as strings while the ODBC and Oracle drivers convert them to Lua types.
Returns: data, as above, or nil if there are no more rows. Note
that this method could return nil as a valid result.
cur:getcolnamescur:getcolnames()
Returns: a list (table) of column names.
cur:getcoltypescur:getcoltypes()
Returns: a list (table) of column types.
Besides the basic functionality provided by all drivers, the Postgres driver also offers these extra features:
env:connectenv:connect(sourcename[,username[,password[,hostname[,port]]]])
In the PostgreSQL driver, this method adds two optional parameters
that indicate the hostname and port to connect.
Also, the first parameter can contain all connection information,
as stated in the documentation for PQconnectdb function
in the PostgreSQL manual
(e.g. environment:connect("dbname=<name> user=<username>"))
See also: environment object
Returns: a connection object
cur:numrowscur:numrows()
See also: cursor object
Returns: the number of rows in the query result.
Besides the basic functionality provided by all drivers, the MySQL driver also offers these extra features:
env:connectenv:connect(sourcename[,username[,password[,hostname[,port]]]])
In the MySQL driver, this method adds two optional parameters that indicate the hostname and port to connect. See also: environment object
Returns: a connection object
cur:numrowscur:numrows()
See also: cursor object
Returns: the number of rows in the query result.
This driver is compatible with versions 4.0, 4.1 and 5.0 of the
MySQL API. Only from version 4.1 MySQL provides support for transactions by using
BDB or INNODB tables.
Therefore, with version 4.0 or without one of these types of tables, the
methods commit, rollback and
setautocommit will not work.
If you are using LuaSQL 2.0, cur:numrows()
is available only in version 2.0.2 or later.
Besides the basic functionality provided by all drivers, the Oracle driver also offers this extra feature:
cur:numrowscur:numrows()
See also: cursor object
Returns: the number of rows in the query result.
LuaSQL version 2.0.2 (for Lua 5.0) is now available for download.
The PostgreSQL driver has been tested on Windows, Linux and MacOS X and is compatible with PostgreSQL 7.x and 8.x.
The ODBC driver has been tested on Windows (SQLServer and Microsoft Access drivers).
The MySQL driver has been tested on Windows and Linux and is compatible with versions 4.0, 4.1 and 5.0.
The Oracle driver has been tested on Windows and is compatible with OCI 8 API.
The SQLite driver has been tested on Windows and Linux and is compatible with versions 2.x.
The JDBC driver has been tested on Windows with LuaJava 1.0 and JDK 1.4 (MySQL driver).
The ADO driver has been tested on Windows with LuaCOM 1.3 (Microsoft Access driver).
Version 2.0 was redesigned by Roberto Ierusalimschy, André Carregal and Tomás Guisasola as part of the Kepler Project (http://www.keplerproject.org). The implementation is compatible with Lua 5.0 and was coded by Tomás Guisasola, Eduardo Quintão, Thiago Ponte, Fabio Mascarenhas and Danilo Tuler, with many priceless contributions from Michael Roth, Tiago Dionizio, Pedro Maia and Leonardo Godinho.
LuaSQL was originally designed by Pedro Miller Rabinovitch and Roberto Ierusalimschy. The first implementation was compatible with Lua 4.0a. Many modifications were made but not distributed by Diego Nehab (ODBC),
LuaSQL development was sponsored by Fábrica Digital (http://www.fabricadigital.com.br), FINEP and CNPq.
For more information please contact us (info-NO-SPAM-THANKS@keplerproject.org) Comments are welcome!
You can also reach other Kepler developers and users on the Kepler Project mailing list (http://luaforge.net/mail/).
LuaSQL is free software and uses the same license as Lua 5.0.
LuaSQL is free software: it can be used for both academic and commercial purposes at absolutely no cost. There are no royalties or GNU-like ``copyleft'' restrictions. LuaSQL qualifies as Open Source (http://www.opensource.org/docs/definition.html) software. Its licenses are compatible with GPL (http://www.gnu.org/licenses/gpl.html). LuaSQL is not in the public domain and the Kepler Project (http://www.keplerproject.org) keep its copyright. The legal details are below.</p>
The spirit of the license is that you are free to use LuaSQL for any purpose at no cost without having to ask us. The only requirement is that if you do use LuaSQL, then you should give us credit by including the appropriate copyright notice somewhere in your product or its documentation.
The LuaSQL library is designed and implemented by the Kepler Project (http://www.keplerproject.org) team. The implementation is not derived from licensed software.
~~~~~
Copyright © 2003-2006 The Kepler Project.
Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the ``Software''), to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, subject to the following conditions:
The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software.
THE SOFTWARE IS PROVIDED ``AS IS'', WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.
~~~~~