1.\" $NetBSD: sqlite.3lua,v 1.1 2013/10/27 12:47:54 mbalmer Exp $ 2.\" 3.\" Copyright (c) 2013 Marc Balmer <mbalmer@NetBSD.org>. All rights reserved. 4.\" 5.\" Redistribution and use in source and binary forms, with or without 6.\" modification, are permitted provided that the following conditions 7.\" are met: 8.\" 1. Redistributions of source code must retain the above copyright 9.\" notice, this list of conditions and the following disclaimer. 10.\" 2. Redistributions in binary form must reproduce the above copyright 11.\" notice, this list of conditions and the following disclaimer in the 12.\" documentation and/or other materials provided with the distribution. 13.\" 3. Neither the name of the University nor the names of its contributors 14.\" may be used to endorse or promote products derived from this software 15.\" without specific prior written permission. 16.\" 17.\" THIS SOFTWARE IS PROVIDED BY THE REGENTS AND CONTRIBUTORS ``AS IS'' AND 18.\" ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE 19.\" IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE 20.\" ARE DISCLAIMED. IN NO EVENT SHALL THE REGENTS OR CONTRIBUTORS BE LIABLE 21.\" FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL 22.\" DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS 23.\" OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) 24.\" HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT 25.\" LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY 26.\" OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF 27.\" SUCH DAMAGE. 28.\" 29.\" 30.Dd October 28, 2013 31.Dt SQLITE 3lua 32.Os 33.Sh NAME 34.Nm sqlite 35.Nd access 36SQLite3 files from Lua 37.Sh SYNOPSIS 38.Cd "local sqlite = require 'sqlite'" 39.Pp 40.Bl -tag -width XXXX -compact 41.\" 42.\" GENERAL FUNCTIONS 43.\" 44.It Dv err = sqlite.initialize() 45.It Dv sqlite.shutdown() 46.It Dv db, err = sqlite.open(file [, flags]) 47.It Dv version = sqlite.libversion() 48.It Dv version = sqlite.libversion_number() 49.It Dv id = sqlite.sourceid() 50.\" 51.\" DATABASE FUNCTIONS 52.\" 53.Pp 54.It Dv err = sqlite.close(db) 55.It Dv stmt, err = sqlite.prepare(db, sql) 56.It Dv err = sqlite.exec(db, sql) 57.It Dv err = sqlite.errcode(db) 58.It Dv msg = sqlite.errmsg(db) 59.It Dv res = sqlite.get_autocommit(db) 60.It Dv res = sqlite.changes(db) 61.\" 62.\" STATEMENT FUNCTIONS 63.\" 64.Pp 65.It Dv err = sqlite.bind(stmt, pidx, value) 66.It Dv count = sqlite.bind_parameter_count(stmt) 67.It Dv pidx = sqlite.bind_parameter_index(stmt, name) 68.It Dv name = sqlite.bind_parameter_name(stmt, pidx) 69.It Dv err = sqlite.step(stmt) 70.It Dv value = sqlite.column(stmt, cidx) 71.It Dv sqlite.reset(stmt) 72.It Dv sqlite.clear_bindings(stmt) 73.It Dv sqlite.finalize(stmt) 74.It Dv name = sqlite.column_name(stmt, cidx) 75.It Dv count = sqlite.column_count(stmt) 76.El 77.Sh DESCRIPTION 78The 79.Nm 80Lua binding provides access to SQLite3 files. 81.Pp 82.Sh GENERAL FUNCTIONS 83.Bl -tag -width XXXX -compact 84.It Dv err = sqlite.initialize() 85Initialize the SQLite3 library. 86Workstation applications using SQLite normally do not need to invoke this 87function. 88.Pp 89.It Dv sqlite.shutdown() 90Deallocate any resources that were allocated by sqlite.initialize(). 91Workstation applications using SQLite normally do not need to invoke this 92function. 93.Pp 94.It Dv db, err = sqlite.open(file [, flags]) 95Open a database, optionally passing flags. 96When called without flags, the database will be opened for reading and 97writing and it will be created if it does not yet exist. 98The following flags are defined: 99.Pp 100.Bl -tag -width XXXX -compact 101.It Dv sqlite.OPEN_READONLY 102The database is opened in read-only mode. 103If the database does not already exist, an error is returned. 104.Pp 105.It Dv sqlite.OPEN_READWRITE 106The database is opened for reading and writing if possible, or reading only if 107the file is write protected by the operating system. 108In either case the database must already exist, otherwise an error is returned. 109.Pp 110.It Dv sqlite.OPEN_CREATE 111The database is opened for reading and writing, and is created if it does not 112already exist. 113.El 114.Pp 115.It Dv version = sqlite.libversion() 116Return the SQLite3 library version number as a string. 117.Pp 118.It Dv version = sqlite.libversion_number() 119Return the SQLite3 library version number as a number. 120.Pp 121.It Dv id = sqlite.sourceid() 122Return teh SQLite3 library source id as a string. 123.EL 124.Sh DATABASE FUNCTIONS 125Database functions operate on database objects returned by 126.Em sqlite.open() . 127.Pp 128.Bl -tag -width XXXX -compact 129.It Dv err = sqlite.close(db) 130Close an open database. 131Like with all remaining database functions, this function can also be called 132using the Lua "colon" syntactic sugar as 133.Em db:close() . 134.Pp 135.It Dv stmt, err = sqlite.prepare(db, sql) 136Return a prepared statement. 137.Pp 138.It Dv err = sqlite.exec(db, sql) 139Directly execute an SQL statement. 140Be careful when creating SQL on the fly (SQL injection attacks). 141.Pp 142.It Dv err = sqlite.errcode(db) 143Return the numeric error code. 144.Pp 145.It Dv msg = sqlite.errmsg(db) 146Return the error message as a string. 147.Pp 148.It Dv res = sqlite.get_autocommit(db) 149Return the autocommit flag. 150.Pp 151.It Dv res = sqlite.changes(db) 152This function returns the number of database rows that were changed or inserted 153or deleted by the most recently completed SQL statement on the database. 154.El 155.Sh STATEMENT FUNCTIONS 156.Bl -tag -width XXXX -compact 157.It Dv err = sqlite.bind(stmt, pidx, value) 158Bind 159.Ar value 160to the paramter 161.Ar pidx 162in the prepared statment 163.Ar stmt . 164.Pp 165.It Dv count = sqlite.bind_parameter_count(stmt) 166Return the number of paramaters in the prepared statement 167.Ar stmt . 168.Pp 169.It Dv pidx = sqlite.bind_parameter_index(stmt, name) 170Return the paramter index for 171.Ar name 172in the prepared statement 173.Ar stmt . 174.Pp 175.It Dv name = sqlite.bind_parameter_name(stmt, pidx) 176Return the parameter name for the paramater index 177.Ar pidx 178in the prepared statement 179.Ar stmt . 180.Pp 181.It Dv err = sqlite.step(stmt) 182Execute prepared statement 183.Ar stmt . 184.Pp 185.It Dv value = sqlite.column(stmt, cidx) 186Return the value at column 187.Ar cidx 188in the prepared statement 189.Ar stmt . 190.Pp 191.It Dv sqlite.reset(stmt) 192The sqlite.reset() function is called to reset a prepared statement object back 193to its initial state, ready to be re-executed. 194.Pp 195.It Dv sqlite.clear_bindings(stmt) 196Contrary to the intuition of many, sqlite.reset() does not reset the bindings on 197a prepared statement. 198Use this routine to reset all host parameters to NULL. 199.Pp 200.It Dv sqlite.finalize(stmt) 201The sqlite.finalize() function is called to delete a prepared statement. 202.Pp 203.It Dv name = sqlite.column_name(stmt, cidx) 204Return the name assigned to a particular column in the result set of a SELECT 205statement. 206.Pp 207.It Dv count = sqlite.column_count(stmt) 208Return the number of columns in the result set returned by the prepared 209statement 210.Ar stmt . 211This routine returns 0 if 212.Ar stmt 213is an SQL statement that does not return data (for example an UPDATE). 214.El 215.Sh EROROR CODES 216Most functions return an error code, the following error codes 217are defined: 218.Pp 219.Bl -tag -widht XXXX -compact 220.It Dv sqlite.OK 221Successful result. 222.Pp 223.It Dv sqlite.ERROR 224SQL error or missing database. 225.Pp 226.It Dv sqlite.INTERNAL 227Internal logic error in SQLite. 228.Pp 229.It Dv sqlite.PERM 230Access permission denied. 231.Pp 232.It Dv sqlite.ABORT 233Callback routine requested an abort. 234.Pp 235.It Dv sqlite.BUSY 236The database file is locked. 237.Pp 238.It Dv sqlite.LOCKED 239A table in the database is locked. 240.Pp 241.It Dv sqlite.NOMEM 242Out of memory. 243.Pp 244.It Dv sqlite.READONLY 245Attempt to write a readonly database. 246.Pp 247.It Dv sqlite.INTERRUPT 248Operation terminated by sqlite3_interrupt(). 249.Pp 250.It Dv sqlite.IOERR 251Some kind of disk I/O error occurred. 252.Pp 253.It Dv sqlite.CORRUPT 254The database disk image is malformed. 255.Pp 256.It Dv sqlite.NOTFOUND 257Unknown opcode in sqlite3_file_control(). 258.Pp 259.It Dv sqlite.FULL 260Insertion failed because database is full. 261.Pp 262.It Dv sqlite.CANTOPEN 263Unable to open the database file. 264.Pp 265.It Dv sqlite.PROTOCOL 266Database lock protocol error. 267.Pp 268.It Dv sqlite.EMPTY 269Database is empty. 270.Pp 271.It Dv sqlite.SCHEMA 272The database schema changed. 273.Pp 274.It Dv sqlite.TOOBIG 275String or BLOB exceeds size limit. 276.Pp 277.It Dv sqlite.CONSTRAINT 278Abort due to constraint violation. 279.Pp 280.It Dv sqlite.MISMATCH 281Data type mismatch. 282.Pp 283.It Dv sqlite.MISUSE 284Library used incorrectly. 285.Pp 286.It Dv sqlite.NOLFS 287Uses OS features not supported on host. 288.Pp 289.It Dv sqlite.AUTH 290Authorization denied. 291.Pp 292.It Dv sqlite.FORMAT 293Auxiliary database format error. 294.Pp 295.It Dv sqlite.RANGE 2962nd parameter to sqlite.bind() out of range. 297.Pp 298.It Dv sqlite.NOTADB 299File opened that is not a database file. 300.Pp 301.It Dv sqlite.ROW 302sqlite.step() has another row ready. 303.Pp 304.It Dv sqlite.DONE 305sqlite.step() has finished executing. 306.El 307.Pp 308.Sh SEE ALSO 309.Xr lua 1 , 310.Xr luac 1 , 311.Xr sqlite3 1, 312.Xr intro 3lua 313.Sh HISTORY 314An 315.Nm 316manual appeared in 317.Nx 7.0 . 318.Sh AUTHORS 319.An -nosplit 320The 321.Nm 322Lua binding was written by 323.An Marc Balmer Aq Mt mbalmer@NetBSD.org . 324