| Message
|
Also see the database functions forum post: http://mushclient.com/db
Now that SQLite database access is incorporated in MUSHclient, it is handy to have a condensed reference to the common things you do in SQL in one place. SQL stands for Structured Query Language.
Data types
For more details, see: http://www.sqlite.org/datatype3.html
There are five data types in SQLite:
- NULL - The value is a NULL value. This is effectively used to store the fact "we have no data here".
- INTEGER - The value is a signed integer (whole number) up to 8 bytes long.
This would store numbers in the range -9,223,372,036,854,775,807 to 9,223,372,036,854,775,807.
- REAL - The value is a floating point value which is 8 bytes long. The real type contains 64 bits: 1 for sign, 11 for the exponent, and 52 for the mantissa.
Its range is +/–1.7E308 with at least 15 digits of precision. (E308 is 1 followed by 308 zeroes).
- TEXT - The value is a text string of any length. Caution: use TEXT rather than STRING, or a string like '42E3' might be turned into 42000.
- BLOB - The value is a blob of data, stored exactly as it was input (intended for binary data such as images or sounds).
Any column in the database, except an "INTEGER PRIMARY KEY" can be used to store any type. That is, you can store text, for example, in columns declared as REAL.
An example of creating a table with the four (non-NULL) types is:
CREATE TABLE t1 (
t TEXT,
r REAL,
i INTEGER,
b BLOB
);
However you can also create untyped columns, like this:
CREATE TABLE t1 (
mobname,
hitpoints,
class,
description
);
One of the points of putting in a column type is that it helps SQLite to know how to compare values. For example, comparing a number to be greater than 5 will give a different result to comparing a string to be greater than '5', depending on the values involved. In other words, whether it does a string compare (where the left-most character is compared first) or a numeric compare (where the total value is compared).
You do not need to specify a size (eg. for a text field, or a number field) as SQLite automatically allocates enough room to hold whatever value you want to put into it.
String literals
Strings in SQL are quoted using single quotes. The only character that needs special treatment is the single quote itself, which must be doubled. For example, to insert "Nick's cat" you would need to do this:
INSERT INTO t1 (mobname) VALUES ('Nick''s cat');
You might do that with this code:
fixed_name = string.gsub (name, "'", "''") -- replace single quotes with two lots of single quotes (Lua)
fixed_name = Replace (name, "'", "''"", true) -- standard script interface
... and then use fixed_name as the variable to be inserted into the SQL statement.
Tests appear to indicate that you cannot directly incorporate the "null" byte (hex 0x00) into a SQL statement. You can indirectly incorporate any 8-bit values by using the bind_names function. In normal SQL statements, bytes with the values 1 through to 255 can be used, bearing in mind the need to double-up single-quotes (bytes with the value 39 (hex 0x27)).
Example of a function that fixes up strings for SQL:
-- Quote the argument, replacing single quotes with two lots of single quotes.
-- If nil supplied, return NULL (not quoted).
function fixsql (s)
if s then
return "'" .. (string.gsub (s, "'", "''")) .. "'"
else
return "NULL"
end -- if
end -- fixsql
Basic SQL
You can use comments in SQL by using two dashes. This makes the rest of that line a comment.
eg.
DROP TABLE t1; -- delete table t1
You can make multi-line comments by using /* .... */.
eg.
/*
Written by Nick Gammon.
Date: 7th August 2009
*/
DROP TABLE
For more details, see: http://www.sqlite.org/lang_droptable.html
To remove an existing table from a database, use DROP TABLE. eg
You will get an error if the table doesn't exist. To suppress that use the IF EXISTS clause:
CREATE TABLE
For more details, see: http://www.sqlite.org/lang_createtable.html
This is used to create a table initially. Tables are given a name, and then a list of column names, in brackets. For example:
CREATE TABLE mobs (
mobname TEXT,
dps REAL,
hitpoints INTEGER,
image BLOB
);
If you only want to create the table the first time around (eg. when a script first runs on the database) you can add IF NOT EXISTS, like this:
CREATE TABLE IF NOT EXISTS mobs (
mobname TEXT,
dps REAL,
hitpoints INTEGER,
image BLOB
);
Each column may have various "constraints" placed on it, which modify the column behaviour. These can be:
- PRIMARY KEY [ AUTOINCREMENT ] - this field is the primary key - if AUTOINCREMENT is specified, then the value is automatically incremented if it is not supplied. The primary key should also be specified as NOT NULL, as it doesn't make a huge amount of sense to have a primary key that can be NULL.
- NOT NULL - this field cannot hold the value NULL, and thus must be specified
- UNIQUE - this value must be unique in this table
- CHECK (expression) - checks an expression, eg. CHECK (hitpoints > 1)
- DEFAULT - specify a default value if the value is not supplied
For example:
CREATE TABLE mobs (
mobid INTEGER PRIMARY KEY AUTOINCREMENT,
mobname TEXT UNIQUE,
dps REAL NOT NULL,
hitpoints INTEGER CHECK (hitpoints > 0),
class TEXT DEFAULT "warrior"
);
In this example the mobid column is the primary key, and does not need to be explicitly supplied, as it is given a unique incrementing number each time a new row is added.
The mobname column is indexed (for fast lookup) and must be unique. The dps column must be supplied (NOT NULL) which means that if you do an INSERT INTO that column would need to be explicitly inserted.
The hitpoints column is checked to be greater than zero. The class column will default to "warrior" if not explicitly inserted.
You can also add "table constraints" which apply to the whole table. Examples of this might be to make a unique key, or primary key, that spans more than one column.
For example:
CREATE TABLE mobs (
mobname TEXT NOT NULL,
dps REAL,
hitpoints INTEGER,
class TEXT,
PRIMARY KEY (mobname),
UNIQUE (mobname, class),
CHECK (dps > hitpoints)
);
In this example, we are using mobname as the primary key, and the combination of mobname/class must be unique to the table. Also we are checking that the dps is greater than the hitpoints for each row.
Note that each table automatically has a unique 64-bit key. This can be referred to in SELECT statements as "ROWID", "OID", or "_ROWID_". This is the key that the row is actually stored under in the underlying database file. If you use exactly the clause "INTEGER PRIMARY KEY" when creating your table, the field with that clause is aliased to the "ROWID" field. Because of this, only integer values may be stored into the INTEGER PRIMARY KEY field. The INTEGER PRIMARY KEY can never be NULL. Even if you do not specify AUTOINCREMENT you will get a unique key for every record. However AUTOINCREMENT makes sure that each key is larger than the previous one, until you run out of keys. Without AUTOINCREMENT specified, if you use the maximum key, then SQLite will attempt to randomly find an unused lower one. Since the highest key possible is 9,223,372,036,854,775,807 you will not run out of keys in a big hurry.
For speed purposes you can also add indexes. These allow faster lookup of the indexed columns. Columns which are indexed are looked-up via their index. Columns which are not indexed can only be found by a sequential scan of all rows. If the table is large this could be much slower.
For example:
CREATE TABLE mobs (
mobname TEXT,
dps REAL,
hitpoints INTEGER,
image BLOB
);
CREATE UNIQUE INDEX mobname_index ON mobs (mobname);
CREATE INDEX IF NOT EXISTS hitpoints_index ON mobs (hitpoints);
The expression "UNIQUE INDEX" prohibits duplicates of the index. The "IF NOT EXISTS" clause can be used to add an index later on, provided it does not already exist for that table.
There is also an ALTER TABLE statement which allows limited changes to an existing table, like adding new columns.
INSERT INTO
For more details, see: http://www.sqlite.org/lang_insert.html
INSERT INTO is used for adding data to the database table.
You can specify a list of columns and then what to put into them, like this:
INSERT INTO mobs (mobname, dps, hitpoints, class) VALUES ('kobold', 22, 33, 'mage');
You don't have to name every field in the table, however unused fields will be given either the default value, or NULL.
So, for example, to make the class take the default (which might be NULL) you could say:
INSERT INTO mobs (mobname, dps, hitpoints) VALUES ('kobold', 22, 33);
Alternatively, if you don't specify the column names then you simply supply the values in the order they are defined for the table, like this:
INSERT INTO mobs VALUES ('kobold', 22, 33, 'mage');
You can also insert by selecting from another table, like this:
INSERT INTO mobs (mobname, dps, hitpoints, class) SELECT mobname, dps, hitpoints, class FROM other_mobs;
Or, more simply, if the other table has the same number of columns, in the same order, as this table:
INSERT INTO mobs SELECT * FROM other_mobs;
You can also simply insert all default values:
INSERT INTO mobs DEFAULT VALUES;
You can also explicitly insert NULL:
INSERT INTO mobs VALUES (NULL, 22, 33, 'mage');
You can always find the ROWID (primary key) of the most recently-inserted table. For example, you can do this:
rowid = db:last_insert_rowid() -- Lua interface
rowid = DatabaseLastInsertRowid ("db") -- standard script interface
DELETE FROM
For more details, see: http://www.sqlite.org/lang_delete.html
This deletes one or more rows that meet a condition. If no condition is specified all rows are removed (this is very fast).
DELETE FROM mobs; -- delete every row
To specify a condition (the more usual case):
DELETE FROM mobs WHERE mobid = 22; -- delete mobid 22
DELETE FROM mobs WHERE hitpoints < 33; -- delete all mobs whose hitpoints are less than 33
DELETE FROM mobs WHERE mobname IS NULL; -- delete all mobs which don't have any name
For more details about the WHERE clause see the SELECT statement.
You can find the number of rows changed by the most recent statement. For example, in Lua you can do this:
changes = db:changes() -- how many rows got deleted? (Lua interface)
changes = DatabaseChanges ("db") -- standard script interface
Note that DELETE FROM deletes rows from the table. However DROP TABLE deletes the entire table.
SELECT
For more details, see: http://www.sqlite.org/lang_select.html
The SELECT statement is the workhorse for getting data from your database.
Basically you specify which fields you want (or * for all fields), from which table or tables, and the conditions for finding those fields.
The SELECT statement will return zero or more rows. In the standard script interface you would use DatabaseStep to obtain each row. DatabaseStep returns the value 100 to indicate it has returned a row for processing, and 101 to indicate the end of the set of rows.
Examples:
SELECT * FROM mobs WHERE mobid = 22; -- get a particular row
SELECT mobname, dps FROM mobs WHERE hitpoints < 33; -- get particular fields
SELECT * FROM mobs WHERE mobname IS NULL; -- find rows with no mobname
SELECT * FROM mobs; -- get every row
If you do not specify a condition you will get every row as part of your "set" of returned results. Normally you iterate through the rows one at a time in your script.
The things you select can be expressions, like this:
SELECT dps * 20 FROM mobs WHERE hitpoints < 33; -- get dps multiplied by 20
You can even select without using a table:
SELECT 2 + 2; -- returns field named "2 + 2" with value 4.
For more details about expressions, see: http://www.sqlite.org/lang_expr.html
You can specify a name for the returned field, which is particularly useful for calculations:
SELECT dps * 20 AS extra_dps FROM mobs; -- get dps multiplied by 20, named "extra_dps"
SELECT 2 + 2 AS my_sum; -- returns field "my_sum" with value 4.
There are quite a few functions you can use both for the selected fields, or for the WHERE clause.
For more details, see:
- http://www.sqlite.org/lang_corefunc.html --> core functions
- http://www.sqlite.org/lang_datefunc.html --> date functions
- http://www.sqlite.org/lang_aggfunc.html --> aggregation functions
Core functions are things like upper or lower case transformations, string manipulation, rounding, trimming and so on.
Date functions manipulate dates and times, like finding the date today, or 5 days in the future.
Aggregation functions operate over the selected data, so for example MAX(hitpoints) would return the maximum of the number of hitpoints of the selected data set. AVG(hitpoints) would return the average, and so on.
Examples:
SELECT sqlite_version(); -- returns 3.6.10
SELECT date('now'); -- returns something like: "2009-08-07"
SELECT upper(mobname) FROM mobs; -- mob names returned in upper case
SELECT * FROM mobs WHERE upper(class) = "MAGE"; -- look for mages even if stored in lower case
SELECT * FROM mobs WHERE date_added = date('now'); -- look for records we added today
SELECT AVG(hitpoints) FROM mobs; -- get average hit points
SELECT COUNT(*) FROM mobs; -- count number of rows in mobs table
A few useful WHERE clauses are:
- WHERE <column> IS NOT NULL -- check for non-null columns
- WHERE <column> IS NULL -- check for null columns
- WHERE <column> LIKE '%something%' -- check that 'something' is somewhere in the column
- WHERE <column> NOT LIKE '%something%' -- check that 'something' is not somewhere in the column
You can select from multiple tables by "joining" them. For example, if one table had class names indexed by the class id (key), and another table had mobs in it, with their class being indicated by a class id, you could find the mobs, and their class names, like this:
SELECT * FROM mobs, classes WHERE mobs.classid = classes.classid;
In this case, each row from mobs would have an associated row from classes, where the classid matches.
An alternative way of expressing the join would be like this:
SELECT * FROM mobs JOIN classes USING (classid); -- the classid field is the one to match on
Or, if the tables have different names for the fields you want to join you can do this:
SELECT * FROM mobs JOIN classes ON mobs.classid = classes.id; -- these two fields have to match
You can sort the results by using the ORDER clause:
SELECT * FROM mobs ORDER BY mobname; -- sort into mobname order
SELECT * FROM mobs ORDER BY mobname DESC; -- sort into reverse mobname order
And you can limit the number of items returned (eg. to a screenfull):
SELECT * FROM mobs ORDER BY mobname LIMIT 24; -- only return first 24 items
There is more complexity than this to the SELECT statement - see the documentation for other things you can do.
UPDATE
For more details, see: http://www.sqlite.org/lang_update.html
The UPDATE statement is used to change an existing row. For example:
UPDATE players SET date_logged_in = date('now') WHERE player_name = 'Nick';
Basically you SET one or more fields, limiting the action to rows identified by the WHERE clause.
For example, if you wanted to give all mobs another 10% hitpoints, and add another 20 mana:
UPDATE mobs SET hitpoints = hitpoints * 1.10, mana = mana + 20;
You can find the number of rows changed by the most recent statement. For example, you can do this:
changes = db:changes() -- how many rows got updated? (Lua interface)
changes = DatabaseChanges ("db") -- standard script interface
CREATE VIEW
For more details, see: http://www.sqlite.org/lang_createview.html
Views let you "package up" a SELECT statement as a "view" so that you can use the view later on in other SELECT statements.
For example:
CREATE VIEW mobs_and_classes AS
SELECT mobname, classname FROM mobs JOIN classes ON mobs.classid = classes.classid; -- make the view
SELECT * from mobs_and_classes ORDER BY classname; -- use the view to select some rows
Views can be dropped if you need to re-create them:
DROP VIEW mobs_and_classes;
BEGIN TRANSACTION / COMMIT / ROLLBACK
For more details, see: http://www.sqlite.org/lang_transaction.html
Everything in SQLite is done inside a transaction. If you do not have a transaction in effect already then anything that updates the database will automatically be wrapped inside a transaction.
You can save a considerable amount of time by starting transactions yourself, especially if you need to update a lot of things. For example, if you are adding hundreds of items to the database, put a BEGIN TRANSACTION at the start and a COMMIT at the end, to save time, as the software only needs to "protect" the database from an unexpected power failure or other problem once, rather than hundreds of times.
The other advantage of transactions is that they are "atomic". That is, either the whole transaction gets done, or none of it.
For example:
BEGIN TRANSACTION;
INSERT INTO mobs VALUES ('kobold', 22, 33, 'mage');
INSERT INTO mobs VALUES ('naga', 22, 33, 'mage');
INSERT INTO mobs VALUES ('wolf', 22, 33, 'warrior');
INSERT INTO mobs VALUES ('sheep', 22, 33, 'creature');
COMMIT;
Making transactions atomic is particularly important if rows are related. For example, if you add a new player to the database, and then immediately after add some inventory items into an inventory table, you don't want to have the player half-added, with no inventory, if the power fails or the program crashes. By wrapping everything in a transaction, either everything relevant gets added, or none of it.
If you find half-way through a transaction that you cannot complete it (perhaps, a SELECT statement fails?) then you can issue a ROLLBACK instead of a COMMIT. The rollback terminates the transaction, and puts things back to how they were.
For example:
BEGIN TRANSACTION;
INSERT INTO mobs VALUES ('kobold', 22, 33, 'mage');
INSERT INTO mobs VALUES ('naga', 22, 33, 'mage');
-- problem detected here
ROLLBACK; -- database is unchanged
|
- Nick Gammon
www.gammon.com.au, www.mushclient.com | | Top |
|
![[Go to top]](/images/gototop.gif){kind=small}
top