Register forum user name Search FAQ

Gammon Forum

Notice: Any messages purporting to come from this site telling you that your password has expired, or that you need to verify your details, confirm your email, resolve issues, making threats, or asking for money, are spam. We do not email users with any such messages. If you have lost your password you can obtain a new one by using the password reset link.
 Entire forum ➜ MUSHclient ➜ General ➜ SQLite Primer

SQLite Primer

This subject is now closed.     Refresh page


Posted by Nick Gammon   Australia  (23,121 posts)  Bio   Forum Administrator
Date Fri 07 Aug 2009 08:00 AM (UTC)

Amended on Tue 03 Jun 2014 11:06 PM (UTC) by Nick Gammon

Message
This page can be quickly reached from the link: http://www.gammon.com.au/sql


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


DROP TABLE t1;


You will get an error if the table doesn't exist. To suppress that use the IF EXISTS clause:


DROP TABLE IF EXISTS t1;





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

Posted by Nick Gammon   Australia  (23,121 posts)  Bio   Forum Administrator
Date Reply #1 on Sat 08 Aug 2009 05:43 AM (UTC)

Amended on Fri 18 Nov 2016 02:36 AM (UTC) by Nick Gammon

Message
Working with a database from a script


The examples below show ways you can use the SQLite database engine from scripting in MUSHclient.

Check if database exists


The first thing a plugin might want to do is see if the database exists at all, and if not, create it.

We need two things here:


  • The name of the database on disk
  • The internal "database id" used to refer to the database during script calls



databasename = GetInfo (67) .. GetPluginID () .. "_weapons.db"


The above example makes a name in the MUSHclient world file directory (GetInfo (67)) and then uses the current Plugin ID to make the name unique, so that it won't clash with other plugins. We then append "_weapons.db" so the file makes sense when browsing the directory.

General scripting method


dbid = "db"  -- Database internal identifier string (used for subsequent calls to DatabaseXxxxxx functions)
status = DatabaseOpen (dbid, databasename, 1)

if status ~= 0 then
  -- create the database
end -- if


The code above opens the database for read-only, without the "create" flag set. This will return 0 if the database exists, and some other number (possibly 14: Unable to open the database file) if not.

Lua Sqlite3 interface method


databasename = GetInfo (67) .. GetPluginID () .. "_weapons.db"

-- open database - throws an error if it cannot (creates the database if it doesn't exist)
db = assert (sqlite3.open(databasename))


Check if table exists


We might go further and check that our table exists in the database by doing a SELECT on the sqlite_master table:

General scripting method


status = DatabaseOpen (dbid, databasename, 1)  -- open for reading

if status == 0 then  -- if we could open it

  DatabasePrepare (dbid,
                   "SELECT * FROM sqlite_master WHERE name = 'weapons' AND type = 'table'")

  status = DatabaseStep (dbid)   --> sqlite3.ROW (100) means we got a row, sqlite3.DONE (101) means we didn't
  print ("Got status", status)
  DatabaseFinalize (dbid)
  DatabaseClose (dbid)  -- close it

end -- if


After the above code we have these possibilities:


  • Status sqlite3.CANTOPEN (14) database does not exist
  • Status sqlite3.ROW (100) : database exists, and weapons table exists
  • Status sqlite3.DONE (101) : database exists, but no weapons table
  • Other statuses: some other problem (eg. database busy)



Lua Sqlite3 interface method



databasename = GetInfo (67) .. GetPluginID () .. "_weapons.db"

-- open database
db = assert (sqlite3.open(databasename))

stmt = db:prepare(
       "SELECT * FROM sqlite_master WHERE name = 'weapons' AND type = 'table'")
if not stmt then
  print ("SQL error on SELECT:", db:errmsg())
else
  status = stmt:step ()  -- read one row
  if status == sqlite3.ROW then
    print ("Table exists")
  elseif status == sqlite3.DONE then
    print ("Table does not exist")
  else
    print ("Database error:", status)
  end -- if
  stmt:finalize ()  -- done with the statement
end -- if stmt made OK
db:close ()



Create a table


Now that we can create some tables.

General scripting method


databasename = GetInfo (67) .. GetPluginID () .. "_weapons.db"

DatabaseOpen (dbid, databasename, 6)  -- open database for read/write (+ create if necessary)

DatabaseExec (dbid, [=[
CREATE TABLE IF NOT EXISTS weapons (
        weapon_id INTEGER PRIMARY KEY AUTOINCREMENT,
        name      TEXT NOT NULL,
        damage    INTEGER,
        weight    REAL
      );
      ]=])

-- work on database here

DatabaseClose (dbid)  -- close it


Lua Sqlite3 interface method


databasename = GetInfo (67) .. GetPluginID () .. "_weapons.db"

-- open database
db = assert (sqlite3.open(databasename))

assert (db:exec ([=[
CREATE TABLE IF NOT EXISTS weapons (
        weapon_id INTEGER PRIMARY KEY AUTOINCREMENT,
        name      TEXT NOT NULL,
        damage    INTEGER,
        weight    REAL
      );
      ]=]))

-- work on database here

db:close ()


Now we can get on with querying the database, adding more stuff and generally putting it to good use. Most of the techniques for doing that are covered in another forum post, so I'll refer to that rather than repeat it all here:

http://mushclient.com/db

Mixing Lua and general database functions


You cannot mix calls to the Lua functions (opened with sqlite3.open) and the general functions which are designed for all scripting languages, where you open a database with DatabaseOpen. One uses a Lua "userdata" and the other uses a simple string to refer to the database. You need to choose whether you want to use the Lua functions or the more general functions.

- Nick Gammon

www.gammon.com.au, www.mushclient.com
Top

Posted by Nick Gammon   Australia  (23,121 posts)  Bio   Forum Administrator
Date Reply #2 on Sun 09 Aug 2009 01:27 AM (UTC)

Amended on Tue 03 Jun 2014 11:05 PM (UTC) by Nick Gammon

Message
Examples of table relationships

The main point of SQL is that you can set up multiple tables with relationships between them. Getting this right makes using the database later on much easier.

Basically you should not repeat information. So, for example, if lots of players are mages you don't put the word "mage" into the player table. Maybe one day you want to change it to "magician", or maybe translate the word "mage" into German.

Instead, you make a table of classes (one of which would be a mage), and put in the player table an entry which is the "key" of the relevant class.

Primary key

The primary key uniquely identifies each row in a table. Perhaps, key 2 in the class table is the key of the "warrior" class.

Foreign key

The foreign key is the key which relates one table to another. For example, in our player table the primary key is the key of this player, however to find out his/her class we need to refer to the "foreign" key (class_id in my examples below). In the player table this is the foreign key, as it refers to another table. In the class table the class_id column is the primary key.

Example SQL

The script below (in Lua) can be copied and pasted into the MUSHclient immediate window to create the database in your "worlds" directory. After that I will do various examples in the sqlite3.exe command-line program to show various queries:


print "commencing"

databasename = GetInfo (67) .. GetPluginID () .. "_mud.db"
db = "db"


local MUSHclient_Database_Errors = {
  [-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",
  } -- end of MUSHclient_Database_Errors

-- check for errors on a DatabaseXXXXX call
function dbcheck (code)

 if code == sqlite3.OK or       -- no error
    code == sqlite3.ROW or      -- completed OK with another row of data
    code == sqlite3.DONE then   -- completed OK, no more rows
    return code
  end -- if code OK

  -- DatabaseError won't return the negative errors  
  local err = MUSHclient_Database_Errors [code] or DatabaseError(db)
  DatabaseExec (db, "ROLLBACK")  -- rollback any transaction to unlock the database
  error (err, 2)                 -- show error in caller's context

end -- dbcheck 

DatabaseOpen (db, databasename, 6)  -- open database for read/write (+ create if necessary)

dbcheck (DatabaseExec (db, [[

  BEGIN;
  
  -- player classes
  DROP TABLE IF EXISTS classes;
  CREATE TABLE classes (
          class_id    INTEGER PRIMARY KEY AUTOINCREMENT,
          name        TEXT NOT NULL,
          initial_hp  INTEGER NOT NULL
        );
 
 -- players
  DROP TABLE IF EXISTS players;
  CREATE TABLE players (
          player_id INTEGER PRIMARY KEY AUTOINCREMENT,
          name      TEXT NOT NULL,
          level     INTEGER,
          class_id  INTEGER  -- foreign key
        );

  -- inventory item types (eg. weapon, bag)
  DROP TABLE IF EXISTS item_types;
  CREATE TABLE item_types (
          type_id     INTEGER PRIMARY KEY AUTOINCREMENT,
          name        TEXT NOT NULL
        );

  -- one entry for each inventory item per player
  DROP TABLE IF EXISTS inventory;
  CREATE TABLE inventory (
          inv_id      INTEGER PRIMARY KEY AUTOINCREMENT,
          player_id   INTEGER NOT NULL,   -- foreign key
          type_id     INTEGER NOT NULL,   -- foreign key
          name        TEXT NOT NULL
        );

  -- improve efficiency of looking up inventory for particular players            
  CREATE INDEX IF NOT EXISTS player_id_index ON inventory (player_id);

  -- improve efficiency of looking up inventory items of a certain type
  CREATE INDEX IF NOT EXISTS type_id_index ON inventory (type_id);

  -- skill names
  DROP TABLE IF EXISTS skills;
  CREATE TABLE skills (
          skill_id    INTEGER PRIMARY KEY AUTOINCREMENT,
          name        TEXT NOT NULL
        );
            
  -- this table has an entry for each player who has each skill
  DROP TABLE IF EXISTS player_skills;
  CREATE TABLE player_skills (
          ps_id     INTEGER PRIMARY KEY AUTOINCREMENT,
          player_id INTEGER NOT NULL,   -- foreign key
          skill_id  INTEGER NOT NULL,   -- foreign key
          
          UNIQUE (player_id, skill_id)  -- make sure we don't duplicate them
        );

  -- improve efficiency of looking up skills for particular players            
  CREATE INDEX IF NOT EXISTS player_id_index ON player_skills (player_id);

  -- improve efficiency of looking up who has a particular skill            
  CREATE INDEX IF NOT EXISTS skill_id_index ON player_skills (skill_id);
        
  -- some classes
  INSERT INTO classes VALUES (1, 'mage', 100);
  INSERT INTO classes VALUES (2, 'warrior', 200);
  INSERT INTO classes VALUES (3, 'thief', 150);
  
  -- some players
  INSERT INTO players (name, level, class_id) VALUES ('Nick', 1, 1);
  INSERT INTO players (name, level, class_id) VALUES ('David', 1, 2);
  INSERT INTO players (name, level, class_id) VALUES ('Twisol', 5, 3);
  INSERT INTO players (name, level, class_id) VALUES ('Zeno', 10, 1);

  -- types of inventory
  INSERT INTO item_types (type_id, name) VALUES (1, 'weapon');
  INSERT INTO item_types (type_id, name) VALUES (2, 'armour');
  INSERT INTO item_types (type_id, name) VALUES (3, 'bag');
  INSERT INTO item_types (type_id, name) VALUES (4, 'shield');
  
  -- inventory - Nick
  INSERT INTO inventory (player_id, type_id, name) VALUES (1, 1, 'sword of misgiving');
  INSERT INTO inventory (player_id, type_id, name) VALUES (1, 2, 'cloak of hiding');
  INSERT INTO inventory (player_id, type_id, name) VALUES (1, 3, 'leather bag');
  INSERT INTO inventory (player_id, type_id, name) VALUES (1, 4, 'wooden shield');
  -- David
  INSERT INTO inventory (player_id, type_id, name) VALUES (2, 1, 'sword of attacking');
  INSERT INTO inventory (player_id, type_id, name) VALUES (2, 3, 'leather bag');
  INSERT INTO inventory (player_id, type_id, name) VALUES (2, 4, 'iron shield');
    
  -- skills
  INSERT INTO skills (skill_id, name) VALUES (1, 'fireball');
  INSERT INTO skills (skill_id, name) VALUES (2, 'frostshock');
  INSERT INTO skills (skill_id, name) VALUES (3, 'freeze');
  INSERT INTO skills (skill_id, name) VALUES (4, 'cure');
  
  -- player/skill combinations
  
  -- Nick
  INSERT INTO player_skills (player_id, skill_id) VALUES (1, 1);  -- Nick / fireball
  INSERT INTO player_skills (player_id, skill_id) VALUES (1, 2);  -- Nick / frostshock
  
  -- David
  INSERT INTO player_skills (player_id, skill_id) VALUES (2, 3);  -- David / freeze
  INSERT INTO player_skills (player_id, skill_id) VALUES (2, 4);  -- David / cure
  INSERT INTO player_skills (player_id, skill_id) VALUES (2, 2);  -- David / frostshock
  
    
  COMMIT;
  
          ]]))
        
DatabaseClose (db)  -- close it        
print "done"



I am running sqlite3.exe on the _mud.db file in the MUSHclient worlds directory. To make the output look more readable I typed a couple of extra commands:


.mode column
.headers on



Querying individual tables

Simple query:


SELECT * FROM players;
player_id   name        level       class_id
----------  ----------  ----------  ----------
1           Nick        1           1
2           David       1           2
3           Twisol      1           3
4           Zeno        10          1


To make that more readable:


SELECT name, level FROM players ORDER BY name;
name        level
----------  ----------
David       1
Nick        1
Twisol      1
Zeno        10


A list like the above one could be used in something like a "who" list.

Another example, showing all the available skills:


SELECT * FROM skills;
skill_id    name
----------  ----------
1           fireball
2           frostshock
3           freeze
4           cure





Relationships

There are three main relationships between table (apart from no relation):


  • One to many
  • Many to one
  • Many to many


These are all illustrated in the examples.




One to many

As an example, a single player may own many items in her inventory. However each inventory item is only owned by one player. This is one-to-many. This is easily implemented. In the code below you can see in the inventory table, each inventory item has a foreign key (player_id), which is the player that item belongs to. To find a particular player's inventory we merely search the inventory table for a match on that player's player_id.



SELECT players.name AS playername, 
    players.level AS level, 
    inventory.name AS inventory_item 
FROM players 
JOIN inventory ON players.player_id = inventory.player_id ;

playername  level       inventory_item
----------  ----------  ------------------
Nick        1           sword of misgiving
Nick        1           cloak of hiding
Nick        1           leather bag
Nick        1           wooden shield
David       1           sword of attacking
David       1           leather bag
David       1           iron shield


This example shows how you can relate tables by using JOIN. Also to avoid ambiguity on the field "name" I had to qualify it with the table name (ie. players.name and inventory.name).

That example showed the inventory for all players. To find a particular player we just narrow the search down:



SELECT players.name AS playername, 
    players.level AS level, 
    inventory.name AS inventory_item 
FROM players 
JOIN inventory ON players.player_id = inventory.player_id
WHERE players.player_id = 1    -- for this player only
ORDER BY inventory_item;       -- get in inventory name order
    

playername  level       inventory_item
----------  ----------  ---------------
Nick        1           cloak of hiding
Nick        1           leather bag
Nick        1           sword of misgiv
Nick        1           wooden shield


(The truncation of "sword of misgiving" is just an artifact of the column width in the sqlite3.exe program - you can change that).




Many to one

An example of many-to-one is player classes. Each player belongs to one class (thief, warrior, mage etc.). A particular player only ever belongs to one class. However each class has many players in it. This is many-to-one. To implement this store the key of the relevant class (class_id) in the player table as a foreign key. To find which class this player is in we just index directly into the class table using that key.


 
SELECT players.name AS playername, 
    level, 
    classes.name AS class 
    FROM players 
JOIN classes ON players.class_id = classes.class_id 
ORDER BY playername;
     
     
playername  level       class
----------  ----------  ----------
David       1           warrior
Nick        1           mage
Twisol      1           thief
Zeno        10          mage





Many to many

An example of many-to-many is player skills. Each player can have many skills, and each skill can be used by many players. Thus we cannot store the foreign key in either the players table or the skills table, as we would only have room for one foreign key there.

The solution is to have an intermediary table, which relates the two tables together. In my example this is the player_skills table. There is one entry in that table for each combination of player/skill.

In the code earlier up the page you see this, for example:


INSERT INTO player_skills (player_id, skill_id) VALUES (1, 2);  -- Nick / frostshock


This means that player 1 (Nick) knows skill 2 (frostshock).

A simple query on the player_skills table isn't very exciting:


select * from player_skills;
ps_id       player_id   skill_id
----------  ----------  ----------
1           1           1
2           1           2
3           2           3
4           2           4
5           2           2


However a more complicated JOIN relates each player to the skills they know:



SELECT players.name AS playername, 
       skills.name  AS skillname 
FROM players 
JOIN player_skills ON players.player_id = player_skills.player_id 
JOIN skills        ON skills.skill_id   = player_skills.skill_id;

playername  skillname
----------  ----------
Nick        fireball
Nick        frostshock
David       frostshock
David       freeze
David       cure


The two joins find the player table entry, and the skill table entry, for each known skill.




Another more complicated query finds each player, what class they are in, and what their inventory items are:



SELECT players.name     AS playername, 
       classes.name     AS class, 
       players.level    AS level, 
       inventory.name   AS inventory_item, 
       item_types.name  AS inventory_type 
FROM players 
JOIN classes    ON players.class_id   = classes.class_id  
JOIN inventory  ON players.player_id  = inventory.player_id 
JOIN item_types ON item_types.type_id = inventory.type_id;

playername  class       level       inventory_item      inventory_type
----------  ----------  ----------  ------------------  --------------
Nick        mage        1           sword of misgiving  weapon
Nick        mage        1           cloak of hiding     armour
Nick        mage        1           leather bag         bag
Nick        mage        1           wooden shield       shield
David       warrior     1           sword of attacking  weapon
David       warrior     1           leather bag         bag
David       warrior     1           iron shield         shield

- Nick Gammon

www.gammon.com.au, www.mushclient.com
Top

Posted by Nick Gammon   Australia  (23,121 posts)  Bio   Forum Administrator
Date Reply #3 on Sun 09 Aug 2009 01:54 AM (UTC)

Amended on Sun 09 Aug 2009 01:56 AM (UTC) by Nick Gammon

Message
Triggers

Another interesting thing you can do with SQL is set up "triggers". These are things that fire on specified database events. One use for this would be to automatically track events, like a player changing room, or getting something in his inventory. Let's start off by making an events table:


  -- this table is a list of events
  DROP TABLE IF EXISTS events;
  CREATE TABLE events (
          event_id  INTEGER PRIMARY KEY AUTOINCREMENT,
          type      TEXT NOT NULL,      -- event type
          player_id INTEGER NOT NULL,   -- foreign key
          what      TEXT NOT NULL       -- what they did
        );


Now to track when a player obtains an inventory item we need a trigger on the inventory table:


 --  notice player getting new items
  CREATE TRIGGER gained_inventory
  AFTER INSERT ON inventory
  FOR EACH ROW BEGIN 
    INSERT INTO events (type, player_id, what) 
    VALUES ('gained_inventory', NEW.player_id, NEW.inv_id);
  END;


Running my previous example of creating inventory items, with this trigger in place, gives these results:


select * from events;

event_id    type              player_id   what
----------  ----------------  ----------  ----------
1           gained_inventory  1           1
2           gained_inventory  1           2
3           gained_inventory  1           3
4           gained_inventory  1           4
5           gained_inventory  2           5
6           gained_inventory  2           6
7           gained_inventory  2           7


Now we might periodically query the events table to see what has happened recently.

Similarly we can find out if the player has lost an item:


  -- notice player losing items
  CREATE TRIGGER lost_inventory
  AFTER DELETE ON inventory
  FOR EACH ROW BEGIN 
    INSERT INTO events (type, player_id, what) 
    VALUES ('lost_inventory', OLD.player_id, OLD.name);
  END;


If I now delete an inventory item:


  DELETE FROM inventory WHERE inv_id = 7;


I see a new entry in the events table:


event_id    type              player_id   what
----------  ----------------  ----------  ----------
8           lost_inventory    2           iron shield





Another thing you can do with triggers is enforce "referential integrity". What this means is, you want to make it impossible to have "dangling" keys in the database. That is, foreign keys in one table, which point to non-existent items in another table.

We can do that like this. You need a trigger for each of the three events that might occur, INSERT, UPDATE and DELETE. In the case of INSERT or UPDATE we need to check that the item being inserted or updated has a foreign key that exists in the referred-to table.

In the case of delete, we are more concerned that we don't delete something in use. So in the example below, before we allow a class to be deleted, we make sure that no player is using that class.


  -- ensure player is added to valid class
  CREATE TRIGGER fk_insert_players_classes
  BEFORE INSERT ON players
  FOR EACH ROW BEGIN 
    SELECT RAISE(ABORT, 'INSERT for players for non-existent class') 
    WHERE  (SELECT class_id FROM classes WHERE class_id = NEW.class_id) IS NULL;
  END;

  -- ensure player is not changed to invalid class
  CREATE TRIGGER fk_update_players_classes
  BEFORE UPDATE ON players
  FOR EACH ROW BEGIN 
    SELECT RAISE(ABORT, 'UPDATE for players for non-existent class') 
    WHERE  (SELECT class_id FROM classes WHERE class_id = NEW.class_id) IS NULL;
  END;

  -- ensure class is not deleted when players are using it
  CREATE TRIGGER fk_delete_players_classes
  BEFORE DELETE ON classes
  FOR EACH ROW BEGIN 
    SELECT RAISE(ABORT, 'DELETE for classes - players are using this class') 
    WHERE  (SELECT class_id FROM players WHERE class_id = OLD.class_id) IS NOT NULL;
  END;


- Nick Gammon

www.gammon.com.au, www.mushclient.com
Top

Posted by Nick Gammon   Australia  (23,121 posts)  Bio   Forum Administrator
Date Reply #4 on Sun 09 Aug 2009 06:57 AM (UTC)

Amended on Tue 03 Jun 2014 11:51 PM (UTC) by Nick Gammon

Message
A bit of research shows there is an easy way of generating referential integrity with triggers ... *cough* Wikipedia *cough*.

[EDIT] However see the thread below, as foreign key support is now built into SQLite3:

Template:post=9922 Please see the forum thread: http://gammon.com.au/forum/?id=9922.





First, we add a REFERENCES clause to the table that uses a foreign key:


  -- player classes
  DROP TABLE IF EXISTS classes;
  CREATE TABLE classes (
          class_id    INTEGER PRIMARY KEY AUTOINCREMENT,
          name        TEXT NOT NULL,
          initial_hp  INTEGER NOT NULL
        );

  -- players
  DROP TABLE IF EXISTS players;
  CREATE TABLE players (
          player_id INTEGER PRIMARY KEY AUTOINCREMENT,
          name      TEXT NOT NULL,
          level     INTEGER,
          class_id  INTEGER  -- foreign key
            REFERENCES classes (class_id)
        );


This clause tells us that the class_id column in the players table refers to the class_id column in the classes table.

Unfortunately, SQLite doesn't implement the referential integrity checks directly (in other words, it ignores that clause). However the sqlite3.exe program will generate them for us.

Using sqlite3.exe we open the database, and then ask it to generate the appropriate triggers:


.genfkey

-- Triggers for foreign key mapping:
--
--     players(class_id) REFERENCES classes(class_id)
--     on delete RESTRICT
--     on update RESTRICT
--
CREATE TRIGGER genfkey1_insert_referencing BEFORE INSERT ON "players" 
  WHEN new."class_id" IS NOT NULL 
  AND NOT EXISTS (SELECT 1 FROM "classes" WHERE new."class_id" == "class_id")
BEGIN
  SELECT RAISE(ABORT, 'constraint failed');
END;

CREATE TRIGGER genfkey1_update_referencing BEFORE UPDATE OF class_id ON "players" 
    WHEN new."class_id" IS NOT NULL 
    AND NOT EXISTS (SELECT 1 FROM "classes" WHERE new."class_id" == "class_id")
BEGIN
  SELECT RAISE(ABORT, 'constraint failed');
END;

CREATE TRIGGER genfkey1_delete_referenced BEFORE DELETE ON "classes" 
    WHEN EXISTS (SELECT 1 FROM "players" WHERE old."class_id" == "class_id")
BEGIN
  SELECT RAISE(ABORT, 'constraint failed');
END;

CREATE TRIGGER genfkey1_update_referenced AFTER UPDATE OF class_id ON "classes" 
  WHEN EXISTS (SELECT 1 FROM "players" WHERE old."class_id" == "class_id")
BEGIN
  SELECT RAISE(ABORT, 'constraint failed');
END;


Now we can just copy and paste the above code into our SQL statement and we have our integrity checks.

We can do similar things with the other tables that had foreign keys by adding the REFERENCES clause, like this:


  -- one entry for each inventory item per player
  DROP TABLE IF EXISTS inventory;
  CREATE TABLE inventory (
          inv_id      INTEGER PRIMARY KEY AUTOINCREMENT,
          player_id   INTEGER NOT NULL   -- foreign key
            REFERENCES players (player_id),
          type_id     INTEGER NOT NULL   -- foreign key
            REFERENCES item_types (type_id),
          name        TEXT NOT NULL
        );
 
  -- this table has an entry for each player who has each skill
  DROP TABLE IF EXISTS player_skills;
  CREATE TABLE player_skills (
          ps_id     INTEGER PRIMARY KEY AUTOINCREMENT,
          player_id INTEGER NOT NULL   -- foreign key
            REFERENCES players (player_id),
          skill_id  INTEGER NOT NULL    -- foreign key
            REFERENCES skills (skill_id)
        );


Now, doing a ".genfkey" in sqlite3.exe generates a whole lot of extra triggers.

- Nick Gammon

www.gammon.com.au, www.mushclient.com
Top

Posted by Nick Gammon   Australia  (23,121 posts)  Bio   Forum Administrator
Date Reply #5 on Thu 20 Aug 2009 11:24 PM (UTC)

Amended on Thu 20 Aug 2009 11:32 PM (UTC) by Nick Gammon

Message
Using triggers to automatically track the date/time of changes

If you want to keep track of when a row was modified, you can make triggers to to do that. First, let's take our players table and add a couple of dates:


 -- players
  DROP TABLE IF EXISTS players;
  CREATE TABLE players (
          player_id INTEGER PRIMARY KEY AUTOINCREMENT,
          name      TEXT NOT NULL,
          level     INTEGER,
          class_id  INTEGER,  -- foreign key
          date_added DATE,
          date_modified DATE
        );


This example uses the DATE keyword (internally it is really an INTEGER) however by declaring it as DATE it helps the sqlite3 program know how to format the output.

Now with the date_added and date_modified columns in the table, we will make triggers to automatically apply those:


  CREATE TRIGGER insert_players AFTER INSERT ON players
     BEGIN
      UPDATE players SET date_added = DATETIME('NOW')  WHERE player_id = new.player_id;
     END;

  CREATE TRIGGER update_players AFTER UPDATE ON players
     BEGIN
      UPDATE players SET date_modified = DATETIME('NOW')  WHERE player_id = new.player_id;
     END;


We do the "insert" trigger *after* the insert, because that is when the autoincrement field new.player_id is valid.

Now if I add some players, and check the results, I see this:


select name, level, date_added, date_modified from players;

name        level       date_added           date_modified
----------  ----------  -------------------  -------------------
Nick        1           2009-08-20 23:20:28  2009-08-20 23:20:28
David       1           2009-08-20 23:20:28  2009-08-20 23:20:28
Twisol      5           2009-08-20 23:20:28  2009-08-20 23:20:28
Zeno        10          2009-08-20 23:20:28  2009-08-20 23:20:28


Interestingly, date_modified is set on the *insert* - I think this is because the insert trigger (which does an update) triggers the update trigger as well.

Now if I modify one record like this:


UPDATE players SET level = 5 WHERE name = 'Nick';


... and then check the table again, I see the modified date:


select name, level, date_added, date_modified from players;

name        level       date_added           date_modified
----------  ----------  -------------------  -------------------
Nick        5           2009-08-20 23:20:28  2009-08-20 23:22:43
David       1           2009-08-20 23:20:28  2009-08-20 23:20:28
Twisol      5           2009-08-20 23:20:28  2009-08-20 23:20:28
Zeno        10          2009-08-20 23:20:28  2009-08-20 23:20:28


Nick's level and date_modified have changed, the others have not.

You could also use triggers to write out an audit trail. For example at a certain date/time, Nick's level was changed from 5 to 80.

- Nick Gammon

www.gammon.com.au, www.mushclient.com
Top

Posted by Nick Gammon   Australia  (23,121 posts)  Bio   Forum Administrator
Date Reply #6 on Sat 12 Dec 2009 03:57 AM (UTC)
Message
Note that SQLite version 3.6.19 introduced direct support for foreign key constraints. This version (actually 3.6.21) is incorporated in MUSHclient version 4.45 upwards, so you can enforce referential integrity without having to generate triggers as decsribed above. For more details and an example:

Template:post=9922 Please see the forum thread: http://gammon.com.au/forum/?id=9922.


- Nick Gammon

www.gammon.com.au, www.mushclient.com
Top

The dates and times for posts above are shown in Universal Co-ordinated Time (UTC).

To show them in your local time you can join the forum, and then set the 'time correction' field in your profile to the number of hours difference between your location and UTC time.


34,006 views.

This subject is now closed.     Refresh page

Go to topic:           Search the forum


[Go to top] top

Information and images on this site are licensed under the Creative Commons Attribution 3.0 Australia License unless stated otherwise.