Module sqltable.env
Methods for the Environment object: the top level user API to SqlTable.
Functions
_sqltable_env.connections (this) | Returns how many connections SqlTable has open to the database. |
_sqltable_env.close (this) | Shuts down this environment. |
_sqltable_env.reset (this) | Reset the environment: leave the environment open, but close all existing connections and open new ones for future calls. |
_sqltable_env.setup_hook (this, fcn) | Provide a setup hook for new database connections that are opened, to configure any variables/PRAGMA options/etc that may be needed. |
_sqltable_env.exec (this, query[, values[, callback]]) | Manually execute a database query. |
_sqltable_env.select () | Manually perform a select statement. |
_sqltable_env.insert () | Manually perform an insert statement. |
_sqltable_env.update () | Manually perform an update statement. |
_sqltable_env.delete () | Manually perform a delete statement. |
_sqltable_env.count (tbl[, clause[, ...]]) | Execute a select that counts rows. |
_sqltable_env.all_rows (tbl) | Execute a select all rows, returning an iterator. |
_sqltable_env.where (tbl[, clause[, ...]]) | Execute a select, as limited by a where clause, returning an iterator. |
_sqltable_env.last_insert_id (tbl) | Retrieve the primary key of the last row that was inserted. |
_sqltable_env.placeholder (this[, nth]) | Writes a placeholder value for use in where statements. |
_sqltable_env.debugging (this, fcn) | Apply a debugging hook to this environment. |
_sqltable_env.clone (tbl, where, ...) | Slurps the result set of an entire query into memory so that normal pairs() can work. |
_sqltable_env.iclone (tbl, where, ...) | Slurps the result set of an entire query into memory so that normal ipairs() can work. |
_sqltable_env.begin_transaction (this, tbl) | Start a consistant transaction on a table. |
_sqltable_env.commit (this, tbl) | Commit a table's transaction. |
_sqltable_env.rollback (this, tbl) | Rollback a table's transaction
All changes will be reverted back to when begin_transaction was run. |
_sqltable_env.open_table (this, params) | Open a proxy table to a database table. |
Tables
TableParameters | Arguments for opening a table. |
Fields
_sqltable_env.next | A 'magic' value: when inserting new rows with auto incrementing primary keys, we don't have a key yet. |
Functions
- _sqltable_env.connections (this)
-
Returns how many connections SqlTable has open
to the database.
Parameters:
- this Environment object
Returns:
-
Number of existant connections
- _sqltable_env.close (this)
-
Shuts down this environment.
Parameters:
- this Environment object being closed
Returns:
-
Nothing.
- _sqltable_env.reset (this)
-
Reset the environment: leave the environment open, but close
all existing connections and open new ones for future calls.
This method comes in handy should your Lua script fork into a second process.
Parameters:
- this Environment object being reset
Returns:
-
Nothing.
- _sqltable_env.setup_hook (this, fcn)
-
Provide a setup hook for new database connections that are opened,
to configure any variables/PRAGMA options/etc that may be needed.
The pool will be reset once a hook is set, thus closing all open connections and reconnecting.
Parameters:
- this Environment object being reset
- fcn Function to implement connection setup
Returns:
-
Nothing.
- _sqltable_env.exec (this, query[, values[, callback]])
-
Manually execute a database query.
The arguments returned to the callback function are raw LuaDBI Userdata. See documentation for LuaDBI at http://code.google.com/p/luadbi/w/list for API details.
Parameters:
- this Environment object
- query Text of SQL query to execute
- values Table of values to bind to the query (if needed) (optional)
- callback Function that is called to handle returned data (optional)
Usage:
t:exec( "select name from t_employees where where title = $1", { 'Programmer' }, function( connection, statement ) local row = true while row do row = statement:fetch(true) do_stuff(row) end end )
- _sqltable_env.select ()
- Manually perform a select statement.
- _sqltable_env.insert ()
- Manually perform an insert statement.
- _sqltable_env.update ()
- Manually perform an update statement.
- _sqltable_env.delete ()
- Manually perform a delete statement.
- _sqltable_env.count (tbl[, clause[, ...]])
-
Execute a select that counts rows.
Parameters:
- tbl Table being row counted
- clause Optional where clause (optional)
- ... Optional bound parameters for where clause (optional)
Returns:
-
Integer number of rows
Usage:
total_employees = env.count( t_employees ) programmers = env.count( t_employees, "where title = $1", 'Programmer' )
- _sqltable_env.all_rows (tbl)
-
Execute a select all rows, returning an iterator.
Parameters:
- tbl Table to select from
Returns:
-
Iterator function similar in usage to pairs().
Usage:
for id, employee in env.all_rows( t_employees ) do print(id, employee.name) end
- _sqltable_env.where (tbl[, clause[, ...]])
-
Execute a select, as limited by a where clause, returning an
iterator. Identical to all_rows() except permits a where clause.
Parameters:
- tbl Table to select from
- clause Optional where clause (optional)
- ... Optional bound parameters for where clause (optional)
Returns:
-
Iterator function similar in usage to pairs().
Usage:
for id, employee in env.where( t_employees, "title = $1", 'Programmer' ) do print(id, employee.name) end
- _sqltable_env.last_insert_id (tbl)
-
Retrieve the primary key of the last row that was inserted.
Parameters:
- tbl Table object that last query was performed on
Returns:
-
Primary key of last insert.
See also:
Usage:
t_employees[ sqltable.next ] = { name = 'Alice', title = 'Programmer' } alice_id = env.last_insert_id( t_employees )
- _sqltable_env.placeholder (this[, nth])
-
Writes a placeholder value for use in where statements. Helps make
manually crafted queries more portable across databases.
Parameters:
- this Environment object
- nth Specify that this is the n-th placeholder in this query (needed by some database engines) (optional)
Usage:
sql = "where column = " .. env:placeholder(1) -- "where column = $1" for PostgreSQL -- "where column = ?" for MySQL
- _sqltable_env.debugging (this, fcn)
-
Apply a debugging hook to this environment. Any SQL call made will
be passed to the function, along with a table of any bound
parameters placed as part of the call.
The hook is called before the code is compiled, so the given callback function will be called just before an error is raised.
The hook can be disabled by calling this method again with no arguments.
Parameters:
- this Environment object
- fcn Debugging function
Usage:
function sql_debug( q, args ) print(q) for k, v in pairs(args) do print(k, '"'..tostring(v)..'"') end end db.env:debugging( sql_debug )
- _sqltable_env.clone (tbl, where, ...)
-
Slurps the result set of an entire query into memory so that normal
pairs() can work. The key is chosen from the table's specified key
column.
THIS IS NOT VERY PERFORMANT WHEN USED WITH LARGE TABLES. Use caution.
Parameters:
- tbl Table object to clone
- where Optional where clause
- ... Parameters to where clause
Returns:
-
Table of data as returned by select
Usage:
programmers = env.clone( t_employees, "where title = $1", 'Programmer' ) all_employees = env.clone( t_employees ) for name, title in pairs(all_employees) do print(name, title) end
- _sqltable_env.iclone (tbl, where, ...)
-
Slurps the result set of an entire query into memory so that normal
ipairs() can work. The key starts at one and increments per row,
forming a standard Lua array.
THIS IS NOT VERY PERFORMANT WHEN USED WITH LARGE TABLES. Use caution.
Parameters:
- tbl Table object to clone
- where Optional where clause
- ... Parameters to where clause
Returns:
-
Table of data as returned by select
Usage:
programmers = env.iclone( t_employees, "where title = $1", 'Programmer' ) all_employees = env.iclone( t_employees ) for employee_id, name in ipairs(all_employees) do print(employee_id, name) end
- _sqltable_env.begin_transaction (this, tbl)
-
Start a consistant transaction on a table.
The table will be locked to a single SQL connection with autocommit mode disabled and a new transaction started. You will need to call 'commit' or 'rollback' before the table is garbage collected, or a SQL connection will be leaked.
Parameters:
- this SqlTable environment
- tbl Table transaction is starting on
- _sqltable_env.commit (this, tbl)
-
Commit a table's transaction.
All changes to the table will be saved. The SQL connection will be returned to the pool and set back to autocommit mode.
Parameters:
- this SqlTable environment
- tbl Table to commit to transaction on
- _sqltable_env.rollback (this, tbl)
-
Rollback a table's transaction
All changes will be reverted back to when begin_transaction was run. The SQL connection will be returned to the pool and set back to autocommit mode.
Parameters:
- this SqlTable environment
- tbl Table to rollback transaction on
- _sqltable_env.open_table (this, params)
-
Open a proxy table to a database table.
Parameters:
- this Environment Object
- params TableParameters
Returns:
-
Proxy table object
Usage:
t_employees = assert(env:open_table{ name = 'employees', key = 'employee_id' })
Tables
- TableParameters
-
Arguments for opening a table.
Fields:
- name Name of table, in database
- key Primary key of table
- vendor Table of database-specific parameters
- readonly If set to true, updates, inserts, and deletes will be disabled.