Also see the SQLite Primer forum post about the fundamentals of SQL: http://mushclient.com/sql
Version 4.40 of MUSHclient introduces an SQLite API (application programming interface) for all supported script languages. This is in addition to the sqlite3 functions provided only to Lua scripts.
Preliminary discussion about SQLite is in this thread:
http://www.gammon.com.au/forum/?id=9241
A full write-up on SQLite is at their web site:
http://www.sqlite.org
This post will discuss the way you can access databases from MUSHclient scripting. The examples will be in Lua, the recommended script language, but will work with minor syntactical modification in all the script languages.
Open and close the database
MUSHclient supports any number of SQLite databases open simultaneously. To distinguish between them, you supply a "database id" which is just a string you choose to identify each one.
Once opened, you supply this database ID to each subsequent database call.
All database related functions start with the word "Database", eg. DatabaseOpen, DatabaseClose etc.
The first action you need to take is to create or open the database. Databases can be held in memory or on disk. You need read or write permissions to access the database, as it is stored on disk as a normal disk file.
Function prototypes:
long DatabaseOpen(BSTR Name, BSTR Filename, long Flags);
long DatabaseClose(BSTR Name);
You can use various flags to control the opening behaviour:
Open_ReadOnly = 0x00000001
Open_ReadWrite = 0x00000002
Open_Create = 0x00000004
Open_DeleteOnClose = 0x00000008
Open_Exclusive = 0x00000010
Open_Main_db = 0x00000100
Open_Temp_db = 0x00000200
Open_Transient_db = 0x00000400
Open_Main_Journal = 0x00000800
Open_Temp_Journal = 0x00001000
Open_SubJournal = 0x00002000
Open_Master_Journal = 0x00004000
Open_NoMutex = 0x00008000
Open_FullMutex = 0x00010000
Warning - the SQLite documentation suggests that you choose one of:
- Open_ReadOnly (1)
- Open_ReadWrite (2) or
- Open_ReadWrite + Open_Create (6)
If not, the behaviour of the open may be undefined.
Example
DatabaseOpen ("db", -- database ID
GetInfo (66) .. "mytestdb.sqlite", -- file name
6) -- flags
-- do stuff with the database here
DatabaseClose ("db") -- close it
The above example opens "mytestdb.sqlite" in the same directory as the MUSHclient executable (obtained from GetInfo (66)), and uses flags Open_ReadWrite and Open_Create, to open for reading and writing, and create if it doesn't exist.
If successful, the call to DatabaseOpen will return zero, and the database ID is added to the valid table of databases. If not, a non-zero code will be returned, and this database ID cannot be used.
The special filename ":memory:" opens an in-memory database. This can be used to manipulate data (or play with SQL) without actually writing to disk.
Use DatabaseClose to close the database when you are finished with it.
If the database is still open when the world file is closed, the database is automatically closed.
Execute some SQL statements
The simplest way of executing SQL is to use DatabaseExec. This gives a simplified interface to compiling and executing SQL. A more complex method is described below.
Function prototype:
long DatabaseExec(BSTR Name, BSTR Sql);
You need to supply the database id of an existing opened database, and the SQL statements to be executed. More than one statement may be supplied, separated by semicolons. If one statement causes an error, execution will be terminated. The most recent error message string (if any) can be obtained by calling DatabaseError.
After successful execution, DatabaseExec will return 0. For more explanation if zero is not returned, call DatabaseError.
You cannot call DatabaseExec if you have called DatabasePrepare but not DatabaseFinalize, as a statement is currently being processed.
Example
DatabaseOpen ("db", GetInfo (66) .. "mytestdb.sqlite", 6)
DatabaseExec ("db", [[
DROP TABLE IF EXISTS weapons;
CREATE TABLE weapons(
weapon_id INTEGER NOT NULL PRIMARY KEY autoincrement,
name TEXT NOT NULL,
damage INT default 10,
weight REAL
);
]])
DatabaseClose ("db") -- close it
Note that in Lua the sequence [[ ... ]] is a multiple-line string, so you can easily imbed SQL statements into your code without worrying about a lot of quotes symbols.
DatabaseExec is not suitable for executing SQL code that queries the database for data (like a SELECT statement), as there is no provision for getting the results back. In that situation use DatabasePrepare / DatabaseStep / DatabaseFinalize. Alternatively, use DatabaseGetField to get back the first column from the first row (like a count of things).
Prepare / Step / Finalize SQL statements
The more general way of executing SQL statements is to prepare/step/finalize. You can think of these steps like this:
- DatabasePrepare - "compile" some SQL, ready for executing it
- DatabaseStep - "execute" the SQL, getting a result of some sort
- DatabaseFinalize - discard the prepared statement, ready to do something else.
Note: If you only want to get a single thing (like a count of spells) then DatabaseGetField will do a DatabasePrepare / DatabaseStep / DatabaseFinalize and return column 1 of row 1 of the result.
Function prototypes:
long DatabasePrepare(BSTR Name, BSTR Sql);
long DatabaseStep(BSTR Name);
long DatabaseFinalize(BSTR Name);
long DatabaseReset(BSTR Name);
long DatabaseGetField(BSTR Name, BSTR Sql);
For DatabasePrepare, you need to supply the database id of an existing opened database, and the SQL statement to be executed. Only one statement should be supplied. If multiple statements are supplied, only the first will be used. (eg. "drop table a; drop table b" - in this case only the first "drop table" would be executed).
If there is a syntax error in your SQL, that will be reported by a non-zero return code from DatabasePrepare. The most recent error message string (if any) can be obtained by calling DatabaseError.
Note that to include single quotes inside a string literal, in your SQL statement they must be doubled. For example:
INSERT INTO weapons (name, damage) VALUES ('Nick''s sword', 42)
Only one statement can be active at once, so after doing a DatabasePrepare you must do DatabaseFinalize before doing another DatabasePrepare. The exception is that if DatabasePrepare returns an error, then you do not need to do DatabaseFinalize because there is no active statement.
After successfully doing DatabasePrepare, you then need to call DatabaseStep for each row in the query. This is the execution phase. For SQL statements that return multiple rows of data, you need to do DatabaseStep once for each row. For SQL statements that just do one thing (like INSERT INTO) then you only need to call DatabaseStep once.
DatabaseStep will return SQLITE_ROW (100) if it has returned a row of data, otherwise it will return SQLITE_DONE (101) if it has reached the end of the query. You could make a loop that continues to call DatabaseStep until it no longer returns 100, in order to process every row in the query.
If you want to reprocess the SQL query from the start for some reason, you can call DatabaseReset and the query restarts at the beginning (you would then need to call DatabaseStep again to re-obtain the first row).
Example
DatabaseOpen ("db", GetInfo (66) .. "mytestdb.sqlite", 6)
DatabaseExec ("db", [[
DROP TABLE IF EXISTS weapons;
CREATE TABLE weapons(
weapon_id INTEGER NOT NULL PRIMARY KEY autoincrement,
name TEXT NOT NULL,
damage INT default 10,
weight REAL
);
]])
-- insert a record
DatabasePrepare ("db", "INSERT INTO weapons (name, damage) VALUES ('sword', 42)") --> returns 0 (SQLITE_OK)
DatabaseStep ("db") -- returns 101 (SQLITE_DONE)
DatabaseFinalize ("db") -- returns 0 (SQLITE_OK)
DatabaseClose ("db") -- close it
The example above shows how the three phases (prepare/step/finalize) are used to execute an SQL statement. This is a bit contrived because you can use DatabaseExec to do the same thing. The real power of prepare/step/finalize is to get data back from the database, so let's add a few records, and get them back ...
require "tprint"
DatabaseOpen ("db", GetInfo (66) .. "mytestdb.sqlite", 6)
rc = DatabaseExec ("db", [[
DROP TABLE IF EXISTS weapons;
CREATE TABLE weapons(
weapon_id INTEGER NOT NULL PRIMARY KEY autoincrement,
name TEXT NOT NULL,
damage INT default 10,
weight REAL
);
]])
-- put some data into the database
DatabaseExec ("db",
[[
INSERT INTO weapons (name, damage) VALUES ('sword', 42);
INSERT INTO weapons (name, damage) VALUES ('mace', 55);
INSERT INTO weapons (name, damage) VALUES ('staff', 35);
]])
-- prepare a query
DatabasePrepare ("db", "SELECT * from weapons ORDER BY name")
-- find the column names
names = DatabaseColumnNames ("db")
tprint (names)
-- execute to get the first row
rc = DatabaseStep ("db") -- read first row
-- now loop, displaying each row, and getting the next one
while rc == 100 do
print ("")
values = DatabaseColumnValues ("db")
tprint (values)
rc = DatabaseStep ("db") -- read next row
end -- while loop
-- finished with the statement
DatabaseFinalize ("db")
DatabaseClose ("db") -- close it
This more elaborate example shows looping over the result set, getting a row at a time (and using DatabaseColumnValues to get the column values), and displaying them. The output looks like this:
1="weapon_id"
2="name"
3="damage"
4="weight"
1=2
2="mace"
3=55
1=3
2="staff"
3=35
1=1
2="sword"
3=42
The first thing we can do after DatabasePrepare is to find out the column names by using DatabaseColumnNames. This returns a table containing the name of each column, indexed by the column number (starting at 1).
We can do this before doing a DatabaseStep, because the column names (and number of columns) is a property of the SQL statement, whether or not any rows are found that actually match the query.
Getting information about prepared statements
Once you have a prepared SQL statement there are various things you can do to find out more about it.
- DatabaseColumns - find how many columns the query will return (per row) which might be zero
- DatabaseColumnName - find the name of a particular column
- DatabaseColumnNames - get a table of all the column names
Each SQL statement will potentially return zero or more columns of data, each time you do DatabaseStep. An SQL statement that does not return data (eg. INSERT INTO, or DROP TABLE) will have zero columns in the result set.
Otherwise, you will get one or more columns. You may not know in advance how many columns you are expecting, for example if you do "SELECT * from <table>".
After successfully doing a DatabasePrepare you can use DatabaseColumns to get the count of columns.
You can also use DatabaseColumnName to find a particular column name (eg. the name of column 5). Columns are indexed starting at 1, to be consistent with the way much of the rest of MUSHclient, and Lua, works. This differs from the SQLite documentation, where columns are indexed from zero.
You can also use DatabaseColumnNames to get a table of all the column names.
Function prototypes:
long DatabaseColumns(BSTR Name);
BSTR DatabaseColumnName(BSTR Name, long Column);
VARIANT DatabaseColumnNames(BSTR Name);
The column name is the name given in the "AS" clause in the query, if any. Otherwise SQLite assigns a column name, which is not guaranteed to be consistent between versions. Often the column name will simply be the name of the variable being queried, however calculations may return generated names.
For example, using this SQL statement:
DatabasePrepare ("db", "SELECT name AS 'weapon name', damage * 5 from weapons")
The returned column names were 'weapon name' and 'damage * 5'.
Getting information about a row
Once you have a row returned by calling DatabaseStep and getting the result SQLITE_ROW (100) that means you have a valid row to work with. You can find out various things about this row ...
- DatabaseColumnType - for each column, what type of data we have in that row
- DatabaseColumnText - convert that column's data into text
- DatabaseColumnValue - return that column's data as a string, integer, float, or NULL
- DatabaseColumnValues - return a table containing all the data for that row as string, integer, float, or NULL (depending on the column type)
The column number must be from 1 to the maximum number of columns (the first column is column 1). You can find the maximum number of columns by calling DatabaseColumns.
Column types are:
- Integer: 1
- Float: 2
- Text: 3
- Blob: 4
- Null: 5
Function prototypes:
long DatabaseColumnType(BSTR Name, long Column);
BSTR DatabaseColumnText(BSTR Name, long Column);
VARIANT DatabaseColumnValue(BSTR Name, long Column);
VARIANT DatabaseColumnValues(BSTR Name);
Example of using these:
DatabaseOpen ("db", GetInfo (66) .. "mytestdb.sqlite", 6)
rc = DatabaseExec ("db", [[
DROP TABLE IF EXISTS weapons;
CREATE TABLE weapons(
weapon_id INTEGER NOT NULL PRIMARY KEY autoincrement,
name TEXT NOT NULL,
damage INT default 10,
weight REAL
);
]])
-- put a record in
DatabaseExec ("db", "INSERT INTO weapons (name, damage) VALUES ('sword', 42);")
-- get it back
DatabasePrepare ("db", "SELECT * from weapons")
-- first row
DatabaseStep ("db")
-- find column count
column_count = DatabaseColumns ("db")
print ("columns =", column_count)
-- for each column, display data
for i = 1, column_count do
print ("column", i, "type", DatabaseColumnType ("db", i),
"text", DatabaseColumnValue ("db", i))
end -- for
-- done with statement
DatabaseFinalize ("db")
-- done with database
DatabaseClose ("db") -- close it
Output
columns = 4
column 1 type 1 text 1
column 2 type 3 text sword
column 3 type 1 text 42
column 4 type 5 text nil
Note that column 1 (the primary key) had 1 automatically inserted into it by the autoincrement clause. Column 4 (weight) had nothing inserted, and had no default, so it remained as NULL, which was returned to Lua as nil.
Keeping track of changes
After inserting data into the database we often want to know whether it actually got inserted, and if so, using what primary key (if the keys are automatically allocated). A few extra functions help us with that.
- DatabaseTotalChanges - This function returns the number of row changes caused by INSERT, UPDATE or DELETE statements since the database connection was opened. The count includes all changes from all trigger contexts. However, the count does not include changes used to implement REPLACE constraints, do rollbacks or ABORT processing, or DROP table processing. The changes are counted as soon as the statement that makes them is completed (when the statement handle is passed to DatabaseReset or DatabaseFinalize).
- DatabaseChanges - This function returns the number of database rows that were changed or inserted or deleted by the most recently completed SQL statement on the database connection specified by the first parameter. Only changes that are directly specified by the INSERT, UPDATE, or DELETE statement are counted. Auxiliary changes caused by triggers are not counted. Use the DatabaseTotalChanges function to find the total number of changes including changes caused by triggers.
A "row change" is a change to a single row of a single table caused by an INSERT, DELETE, or UPDATE statement. Rows that are changed as side effects of REPLACE constraint resolution, rollback, ABORT processing, DROP TABLE, or by any other mechanisms do not count as direct row changes.
- DatabaseLastInsertRowid - this returns a string which is the most recent automatically allocated database key (for example, if you are using autoincrement on the primary key). Internally it is a 64-bit number, but since there is no ready mechanism for returning 64-bit numbers via the scripting interface, MUSHclient converts it to a string, and returns that.
Function prototypes:
long DatabaseTotalChanges(BSTR Name);
long DatabaseChanges(BSTR Name);
BSTR DatabaseLastInsertRowid(BSTR Name);
Keeping track of your databases
These two functions help you know which databases are open and what state they are in:
- DatabaseList - returns a table of all active (open) database IDs.
- DatabaseInfo - returns information about a particular database, as follows:
- 1 - Name of database on disk
- 2 - Has a DatabasePrepare been validly done recently (and is still active)?
- 3 - Has a valid row been returned by DatabaseStep?
- 4 - Count of columns returned from the last DatabasePrepare.
Function prototypes:
VARIANT DatabaseList();
VARIANT DatabaseInfo(BSTR Name, long InfoType);
Constants
Column types:
- Integer : 1
- Float : 2
- Text : 3
- Blob : 4
- Null : 5
Return codes:
- SQLITE_OK 0 : Successful result
- SQLITE_ERROR 1 : SQL error or missing database
- SQLITE_INTERNAL 2 : Internal logic error in SQLite
- SQLITE_PERM 3 : Access permission denied
- SQLITE_ABORT 4 : Callback routine requested an abort
- SQLITE_BUSY 5 : The database file is locked
- SQLITE_LOCKED 6 : A table in the database is locked
- SQLITE_NOMEM 7 : A malloc() failed
- SQLITE_READONLY 8 : Attempt to write a readonly database
- SQLITE_INTERRUPT 9 : Operation terminated by sqlite3_interrupt()
- SQLITE_IOERR 10 : Some kind of disk I/O error occurred
- SQLITE_CORRUPT 11 : The database disk image is malformed
- SQLITE_NOTFOUND 12 : NOT USED. Table or record not found
- SQLITE_FULL 13 : Insertion failed because database is full
- SQLITE_CANTOPEN 14 : Unable to open the database file
- SQLITE_PROTOCOL 15 : NOT USED. Database lock protocol error
- SQLITE_EMPTY 16 : Database is empty
- SQLITE_SCHEMA 17 : The database schema changed
- SQLITE_TOOBIG 18 : String or BLOB exceeds size limit
- SQLITE_CONSTRAINT 19 : Abort due to constraint violation
- SQLITE_MISMATCH 20 : Data type mismatch
- SQLITE_MISUSE 21 : Library used incorrectly
- SQLITE_NOLFS 22 : Uses OS features not supported on host
- SQLITE_AUTH 23 : Authorization denied
- SQLITE_FORMAT 24 : Auxiliary database format error
- SQLITE_RANGE 25 : 2nd parameter to sqlite3_bind out of range
- SQLITE_NOTADB 26 : File opened that is not a database file
- SQLITE_ROW 100 : DatabaseStep has another row ready
- SQLITE_DONE 101 : DatabaseStep has finished executing
MUSHclient also returns the following codes where applicable:
- -1 : Database id not found
- -2 : Database not open
- -3 : Already have prepared statement
- -4 : Do not have prepared statement
- -5 : Do not have a valid row
- -6 : Database already exists under a different disk name
- -7 : Column number out of range
|
![[Go to top]](/images/gototop.gif){kind=small}
top