[Home] [Downloads] [Search] [Help/forum]

Gammon Software Solutions forum

See www.mushclient.com/spam for dealing with forum spam. Please read the MUSHclient FAQ!

[Folder]  Entire forum
-> [Folder]  MUSHclient
. -> [Folder]  General
. . -> [Subject]  SQLite3 foreign key support now available in version 4.45 of MUSHclient
Home  |  Users  |  Search  |  FAQ
Username:
Register forum user name
Password:
Forgotten password?

SQLite3 foreign key support now available in version 4.45 of MUSHclient

[Reply to this subject]  Reply to this subject   [New subject]  Start a new subject   [Refresh] Refresh page


Posted by Nick Gammon   Australia  (19,173 posts)  [Biography] bio   Forum Administrator
Date Sat 12 Dec 2009 03:18 AM (UTC)  quote  ]

Amended on Sat 12 Dec 2009 03:54 AM (UTC) by Nick Gammon

Message
Version 4.45 of MUSHclient upgrades the SQLite3 database version from 3.6.16 to 3.6.21.

Amongst other things, this adds direct support of foreign key constraints. The importance of these constraints is that they ensure that the database has integrity, that is, you don't have rows in one table which are supposed to refer to rows in another table, which don't exist.

For more details, see:

http://www.sqlite.org/foreignkeys.html

An example of using foreign keys appears below, adapted from some code in this thread:

http://www.gammon.com.au/forum/?id=9650

The changes to support foreign keys are in bold. Note the pragma directive to turn foreign key support on.



print "commencing"

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


function dbcheck (code)

 if code ~= 0 and       -- no error
    code ~= 100 and     -- completed OK with another row of data
    code ~= 101 then    -- completed OK, no more rows
    local err = DatabaseError(db)  -- the rollback will change the error message
    DatabaseExec (db, "ROLLBACK")  -- rollback any transaction to unlock the database
    error (err, 2)                 -- show error in caller's context
  end -- if

end -- dbcheck 

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

dbcheck (DatabaseExec (db, [[


  PRAGMA foreign_keys = ON;

  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
          FOREIGN KEY(class_id) REFERENCES classes(class_id)

        );
    

  -- 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,
          FOREIGN KEY(player_id) REFERENCES players(player_id),
          FOREIGN KEY(type_id) REFERENCES item_types(type_id)
        );


  -- 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
          FOREIGN KEY(player_id) REFERENCES players (player_id),
          FOREIGN KEY(skill_id) REFERENCES skills (skill_id),
          
          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"


This code runs without errors (in the Immediate window with Lua as the scripting language). However if you force a foreign key violation, eg, changing one of lines near the end from:


INSERT INTO player_skills (player_id, skill_id) VALUES (2, 3);  -- David / freeze


to:


INSERT INTO player_skills (player_id, skill_id) VALUES (999, 3);  -- David / freeze


... then you get an error message: "foreign key constraint failed".


- Nick Gammon

www.gammon.com.au, www.mushclient.com
[Go to top] top

Posted by WillFa   USA  (517 posts)  [Biography] bio
Date Reply #1 on Sat 12 Dec 2009 05:12 AM (UTC)  quote  ]
Message
Awesome! :) Thanks for all the work, Nick!
[Go to top] 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.


3,068 views.

[Reply to this subject]  Reply to this subject   [New subject]  Start a new subject   [Refresh] Refresh page

Go to topic:           Search the forum


[Go to top] top

[Home]

Written by Nick Gammon - 5K

Comments to: Gammon Software support
[RH click to get RSS URL] Forum RSS feed ( http://www.gammon.com.au/rss/forum.xml )

[Best viewed with any browser - 2K]    [Internet Contents Rating Association (ICRA) - 2K]    [Web site powered by FutureQuest.Net]