Support for SQLite 3 added to version 4.38 of MUSHclient

Posted by Nick Gammon on Sat 14 Feb 2009 01:15 AM — 10 posts, 43,423 views.

Nick Gammon Australia Forum Administrator Sat 14 Feb 2009 01:15 AM #0

Version 4.38 adds inbuilt support for SQLite. See their homepage for more information about SQLite:

http://www.sqlite.org/

From their homepage: "SQLite is a software library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine. SQLite is the most widely deployed SQL database engine in the world. The source code for SQLite is in the public domain."

The interface to scripting is only available under Lua scripting. It uses the LuaSQLite 3 library, described as "a Lua 5.1 wrapper for the SQLite3".

Documentation for the Lua SQLite3 interface is here:

http://luasqlite.luaforge.net/lsqlite3.html

The reason for doing this is to provide an easy-to-use, inbuilt mechanism for database access in MUSHclient. As described it is zero-configuration which means "SQLite does not need to be "installed" before it is used. There is no "setup" procedure. There is no server process that needs to be started, stopped, or configured.".

It is serverless, which means you do not need to install, start or configure a server before using it.

It is transactional, which means "All changes within a single transaction in SQLite either occur completely or not at all, even if the act of writing the change out to the disk is interrupted by a program crash, an operating system crash, or a power failure."

As the support for SQLite is built into MUSHclient's code itself, script writers (for version 4.38 onwards) can be assured that they can use the database features, regardless of the settings in the Lua "sandbox", or whether loading of DLLs is permitted.

SQLite databases are ordinary disk files, and thus could be stored in any convenient directory, such as the "worlds" folder, or some other suitable place. Alternatively you can use SQLite "memory" databases, for making use of database queries, without actually opening any files at all.

Advantages and disadvantages of a database

The main competitor to using a database in a MUD client situation, is to store "persistent" information either in the world file itself, as MUSHclient variables, or serialized to a plugin "state" file.

World variables

Advantage: Easy to use - MUSHclient variables are saved when the world file is saved
Disadvantages: Cannot store complex things, like tables or lists, without mucking around. Need to remember to save the world file or the variables are not saved.

Plugin state files

Advantage: Easy to use - plugin variables are saved when the plugin is closed.
Disadvantages: Potential for loss of data if the power fails at the exact moment the plugin state file is being saved. Possible loss of data if the plugin state file is not saved for a long time. Plugin state files are per-world, so you potentially duplicate information (like, lists of spells) between multiple worlds. Also can be slow to load when they get large.

SQLite database

Advantages: Guaranteed data safety. Database is saved in a "complete" state. SQL is an industry standard language. Complex queries can be written to get at data in different ways. Large amounts of data (eg. thousands of mobs' history) can be stored, but quickly accessed as the database only has to be queried in part, not completely read into memory.
Disadvantages: Can be slower if writing a lot of data, however if done in a transaction, this slowdown is minimized. SQL takes a bit of work to learn. More programming needed to write to, or read from, the database compared to the other methods.

Example


-- open database in memory
db = assert (sqlite3.open_memory())

-- create a table
assert (db:execute[[
  CREATE TABLE players(
    name  varchar(50),
    class varchar(50)
  )
]])


-- add a few elements
list = {
  { name="Nick Gammon", class="mage", },
  { name="David Haley", class="warrior", },
  { name="Shadowfyr", class="priest", },
}

for i, p in pairs (list) do
  assert (db:execute(string.format([[
    INSERT INTO players
    VALUES ('%s', '%s')]], p.name, p.class)
  ))
end

-- print all rows, the rows will be indexed by field names

require "tprint"

for row in db:nrows("SELECT * FROM players") do
  print ("\n------ new row ---------\n")
  tprint (row)
end

-- close database
db:close()

Output

------ new row ---------

"name"="Nick Gammon"
"class"="mage"

------ new row ---------

"name"="David Haley"
"class"="warrior"

------ new row ---------

"name"="Shadowfyr"
"class"="priest"

Nick Gammon Australia Forum Administrator Sat 14 Feb 2009 01:53 AM #1

The documentation for the Lua interface is in the file docs/lsqlite3.html which is part of the MUSHclient install.

The examples which are part of the lsqlite3 download all work inside the Immediate window, providing you omit the first line:


require("lsqlite3")

This is not required as the module sqlite3 is already loaded. Alternatively, change it to read:


require("sqlite3")

One such example is:


local db = sqlite3.open_memory()

db:exec[[
  CREATE TABLE test (id INTEGER PRIMARY KEY, content);

  INSERT INTO test VALUES (NULL, 'Hello World');
  INSERT INTO test VALUES (NULL, 'Hello Lua');
  INSERT INTO test VALUES (NULL, 'Hello Sqlite3')
]]

for row in db:nrows("SELECT * FROM test") do
  print(row.id, row.content)
end

Nick Gammon Australia Forum Administrator Sat 14 Feb 2009 03:58 AM #2

The installer is about 170 Kb larger because of the addition of SQLite. I think this is an acceptable overhead. It is still pretty small considering the power of the client.

Nick Gammon Australia Forum Administrator Sat 14 Feb 2009 05:21 AM #3

You can easily play with disk databases by using the sqlite3.exe tool available from their web site:

http://www.sqlite.org/sqlite-3_6_10.zip

For example, to slightly modify my earlier test:


-- open database on disk
db = assert (sqlite3.open( GetInfo (66) .. "test.sqlite"))

-- create a table
assert (db:execute[[
  CREATE TABLE players(
    name  varchar(50),
    class varchar(50)
  )
]])


-- add a few elements
list = {
  { name="Nick Gammon", class="mage", },
  { name="David Haley", class="warrior", },
  { name="Shadowfyr", class="priest", },
}

for i, p in pairs (list) do
  assert (db:execute(string.format([[
    INSERT INTO players
    VALUES ('%s', '%s')]], p.name, p.class)
  ))
end

-- close database
db:close()

This now writes to a file "test.sqlite" in the same directory as the MUSHclient executable.

Once that is done we can fire up sqlite3.exe like this:


sqlite test.sqlite

SQLite version 3.6.10
Enter ".help" for instructions
Enter SQL statements terminated with a ";"

sqlite> .mode column
sqlite> select * from players;
Nick Gammon  mage
David Haley  warrior
Shadowfyr    priest

This confirms that the data is indeed on our disk file. Now if we want to export it into standard SQL (perhaps to email to someone) we can use the ".dump" command. First we will tell it to use a file for output, and then dump the table:


sqlite> .output players.sql
sqlite> .dump players

Now if we edit the players.sql file, we see this:


BEGIN TRANSACTION;
CREATE TABLE players(
    name  varchar(50),
    class varchar(50)
  );
INSERT INTO "players" VALUES('Nick Gammon','mage');
INSERT INTO "players" VALUES('David Haley','warrior');
INSERT INTO "players" VALUES('Shadowfyr','priest');
COMMIT;

This SQL code could be used to recreate the database later on.

Nick Gammon Australia Forum Administrator Sat 14 Feb 2009 07:46 PM #4

As an experiment, I tried to import the MUSHclient documentation database into SQLite. The documentation itself is here:

http://www.gammon.com.au/files/mushclient/src/documentation.sql.bz2

Under Windows I needed to be able to unzip it, so I needed bzip2:

http://www.bzip.org/1.0.5/bzip2-105-x86-win32.exe

This line unzips the file:


bzip2-105-x86-win32.exe -d documentation.sql.bz2

This gives the file: documentation.sql

The general technique for importing into SQLite from an SQL file is this:


sqlite3.exe docs.sqlite < documentation.sql

This assumes docs.sqlite is the (newly created) database, and documentation.sql are the SQL commands to be imported.

An alternative way to import SQL is to do it via the sqlite3 command-line interface. That is:


sqlite3.exe docs.sqlite

sqlite> .read documentation.sql
sqlite> .exit

However I hit a few snags in that the syntax used by MySQL and SQLite is different. Thus the file documentation.sql had to be edited (I used UltraEdit) and the following changes made:

A "create table" statement looks like the following:


DROP TABLE IF EXISTS commands;
CREATE TABLE commands (
  command_name varchar(30) NOT NULL default '',
  command_id int(11) NOT NULL default '0',
  when_active tinyint(4) NOT NULL default '0',
  menu varchar(60) default NULL,
  accelerator varchar(25) default NULL,
  short_description varchar(80) default NULL,
  description text,
  productid int(11) NOT NULL default '0',
  label varchar(60) default NULL,
  doc_name varchar(30) default NULL,
  dialog_name varchar(30) default NULL,
  PRIMARY KEY  (command_name),
  UNIQUE KEY command_when (command_name,when_active)
) TYPE=MyISAM;

Using a find-and-replace you need to:

Omit: TYPE=MyISAM
Change: UNIQUE KEY <something> to UNIQUE

Thus it (and similarly for all the other CREATE TABLE statements) should look like this:


DROP TABLE IF EXISTS commands;
CREATE TABLE commands (
  command_name varchar(30) NOT NULL default '',
  command_id int(11) NOT NULL default '0',
  when_active tinyint(4) NOT NULL default '0',
  menu varchar(60) default NULL,
  accelerator varchar(25) default NULL,
  short_description varchar(80) default NULL,
  description text,
  productid int(11) NOT NULL default '0',
  label varchar(60) default NULL,
  doc_name varchar(30) default NULL,
  dialog_name varchar(30) default NULL,
  PRIMARY KEY  (command_name),
  UNIQUE (command_name,when_active)
) ;

MySQL uses \' inside quoted strings to put in a quote (eg. 'Nick\'s cat'). This is apparently not standard SQL, so you need to change \' to '' (two single quotes). Thus:

Before:
```
'Nick\'s cat'
```
After:
```
'Nick''s cat'
```
A single find-and-replace should do that for you.

SQLite does not support the "enum" row definition, so at around line 465 where it says:


CREATE TABLE functions (
  name varchar(30) NOT NULL default '',
  prototype text NOT NULL,
  summary varchar(255) NOT NULL default '',
  description text NOT NULL,
  vb_example text,
  java_example text,
  perl_example text,
  return_value text,
  type_of_object enum('Method','Property') NOT NULL default 'Method',
  see_also varchar(255) default NULL,
  version float default NULL,
  python_example text,
  doc_name varchar(30) default NULL,
  lua_example text,
  lua_notes text,
  PRIMARY KEY  (name)
) ;

Change the bold line to:


type_of_object varchar(10) NOT NULL default 'Method',

SQLite uses "autoincrement" differently so at around line 870, where it says:


CREATE TABLE general_doc (
  doc_name varchar(30) NOT NULL default '',
  title varchar(80) default NULL,
  description text,
  productid int(11) NOT NULL default '0',
  special int(11) default NULL,
  general_id int(11) NOT NULL auto_increment,
  wiki_name varchar(32) default NULL,
  PRIMARY KEY  (general_id),
  UNIQUE(doc_name)
) ;

Change the create table definition to:


DROP TABLE IF EXISTS general_doc;
CREATE TABLE general_doc (
  doc_name varchar(30) NOT NULL default '',
  title varchar(80) default NULL,
  description text,
  productid int(11) NOT NULL default '0',
  special int(11) default NULL,
  general_id INTEGER NOT NULL PRIMARY KEY  autoincrement,
  wiki_name varchar(32) default NULL,
  UNIQUE(doc_name)
) ;

Change the KEY definitions for general_doc_xref. At around line 1040, change:


CREATE TABLE general_doc_xref (
  doc_name varchar(30) NOT NULL default '',
  xref_name varchar(30) NOT NULL default '',
  PRIMARY KEY  (doc_name,xref_name),
  UNIQUE(doc_name,xref_name),
  KEY doc_name_2 (doc_name),
  KEY xref_name (xref_name)
) ;

to:


CREATE TABLE general_doc_xref (
  doc_name varchar(30) NOT NULL default '',
  xref_name varchar(30) NOT NULL default '',
  PRIMARY KEY  (doc_name,xref_name),
  UNIQUE(doc_name,xref_name)
) ;
CREATE INDEX doc_name_2 ON general_doc_xref (doc_name);
CREATE INDEX xref_name ON general_doc_xref (xref_name);

After making those changes, the file imported in about 11 seconds on my PC. That was about 1450 lines.

However to make things much faster wrap the whole thing into a transaction. At the very start of the file add:


BEGIN TRANSACTION;

And at the very end add:


COMMIT;

Now the file imports in about 1 second.

Alternatively, simply do the BEGIN TRANSACTION inside the sqlite3 program, like this:


sqlite3.exe docs.sqlite

sqlite> BEGIN TRANSACTION;
sqlite> .read documentation.sql
sqlite> COMMIT;
sqlite> .exit

(Don't combine these methods or you will get an error message "cannot start a transaction within a transaction".

After importing, we can use SQLite to make queries:


sqlite3.exe docs.sqlite

sqlite> .tables
commands          errors            general_doc       lua_functions
dialogs           functions         general_doc_xref  tblUnixControl

sqlite> select name, doc_name from functions where doc_name = 'macros';
Accelerator|macros
AcceleratorList|macros
AcceleratorTo|macros

Nick Gammon Australia Forum Administrator Sat 14 Feb 2009 08:59 PM #5

I note that after importing, escaped sequences in strings are not translated (eg. \n is still just \n in the string, it is not translated into a newline.

However MUSHclient provides a function (FixupEscapeSequences) that will translate the common ones. So for example, with the documentation database loaded, this would show the documentation for utils.msgbox with the \n and \r sequences translated.


-- open database 
db = assert (sqlite3.open ("/downloads/docs.sqlite"))

for row in db:nrows("SELECT * FROM lua_functions where name = 'utils.msgbox'") do
  print (FixupEscapeSequences (row.description))
end

-- close database
db:close()

Nick Gammon Australia Forum Administrator Sun 15 Feb 2009 05:18 AM #6

An interesting application of the SQLite database is to improve the performance of the MUSHclient spellchecker.

I normally have it turned off, because loading the entire spellcheck dictionary into memory (which the current spellchecker does) is a bit slow for my taste. It isn't very slow, like it takes a second or two. But you know how impatient computer people get. ;)

A fairly simple rewrite converts the spell check dictionaries into a SQLite database. It does this the first time around, when it detects that the database does not exist. After that, spell checking is done by looking up candidate words in the database. This makes loading the spellchecker virtually instantaneous (except for the first time when the conversion is done), as it simply involves opening the database file.

After that, checking individual words is slightly slower, as each word is looked up with a "select" statement into the database. However it is still fast enough that you don't notice it. To test it, I wrote a test program that checked 42,400 words (using the SpellCheck script function). It ran in about 3 seconds. Obviously you are unlikely to be checking 40 thousand words every time you type a command into the MUD, so this is an extreme case.

The good thing about this approach is that, since it eliminates the loading delay, you could make your dictionary larger (and have more obscure words in it), without affecting the startup time.

A similar approach could be used to modify the thesaurus plugin, so that it would load instantly as well.

Nick Gammon Australia Forum Administrator Sun 15 Feb 2009 05:20 AM #7

The modified spellchecker.lua file is here:


--[[
  Spell checker for MUSHclient, written by Nick Gammon.
  Written:  9th October 2006
  Updated: 11th October 2006
  Updated:  6th March 2007 to make progress bar optional
  Updated: 13th April 2007 to added IGNORE_MIXED_CASE, IGNORE_IMBEDDED_NUMBERS
  Updated: 15th February 2009 to convert to using SQLite database instead of Lua table
  Updated: 21st February 2009 to fix problem where words with 2 metaphones were only stored once.
--]]

local SHOW_PROGRESS_BAR = true -- show progress bar? true or false

local METAPHONE_LENGTH = 4   -- how many characters of metaphone to get back
local EDIT_DISTANCE = 4      -- how close a word must be to appear in the list of suggestions
local CASE_SENSITIVE = false -- compare case? true or false
local IGNORE_CAPITALIZED = false -- ignore words starting with a capital? true or false
local IGNORE_MIXED_CASE = false  -- ignore words in MixedCase (like that one)? true or false
local IGNORE_IMBEDDED_NUMBERS = false -- ignore words with numbers in them? true or false

-- this is the pattern we use to find "words" in the text to be spell-checked
local pattern = "%a+'?[%a%d]+" -- regexp to give us a word with a possible single imbedded quote

-- path to the spell check dictionaries
local directory = utils.info ().app_directory .. "spell\\"
-- file name of the user dictionary, in the above path
local userdict = "userdict.txt"

-- stuff below used internally
local make_upper  -- this becomes the upper-case conversion function, see below
local db          -- SQLite3 dictionary database
local cancelmessage = "spell check cancelled"
local previousword  --> not used right now
local change, ignore  -- tables of change-all, ignore-all words

-- dictionaries - add new entries along similar lines to add more dictionary files
local files = {

-- lower-case words

  "english-words.10",
  "english-words.20",
  "english-words.35",
  "english-words.40",

-- upper case words

  "english-upper.10",
  "english-upper.35",
  "english-upper.40",

-- American words

  "american-words.10",
  "american-words.20",

-- contractions (eg. aren't, doesn't)

  "english-contractions.10",
  "english-contractions.35",
  
-- user dictionary
  userdict,
  }
  
 -- trim leading and trailing spaces from a string
local function trim (s)
  return (string.gsub (s, "^%s*(.-)%s*$", "%1"))
end -- trim

-- insert a word into our metaphone table - called by reading dictionaries
-- and also by adding a word during the spellcheck
local function insert_word (word, user)
  
  if word == "" then
    return
  end -- empty word
  
              
  -- get both metaphones
  local m1, m2 = utils.metaphone (word, METAPHONE_LENGTH)
  local fixed_word = string.gsub (word, "'", "''")  -- convert ' to ''
  
  assert (db:execute (string.format ("INSERT INTO words VALUES (NULL, '%s', '%s', %i)",
            fixed_word, m1, user)));
    
  -- do 2nd metaphone, if any
   if m2 then
    assert (db:execute (string.format ("INSERT INTO words VALUES (NULL, '%s', '%s', %i)",
              fixed_word, m2, user)));
   end -- having alternative      
  
end -- insert_word


-- sort function for sorting the suggestions into edit-distance order
local function suggestions_compare (word)
   return function (a, b)
     local diff = utils.edit_distance (make_upper (a), word) - 
                  utils.edit_distance (make_upper (b), word) 
     if diff == 0 then
       return make_upper (a) < make_upper (b)
     else
       return diff < 0
     end -- differences the same?
  end -- compareit
end -- function suggestions_compare

-- check for one word, called by spellcheck (invokes suggestion dialog)
local function checkword_and_suggest (word)

  if IGNORE_CAPITALIZED then
    -- ignore words starting in caps
    if string.find (word, "^[A-Z]") then
      return word, "ignore"
    end -- this round, ignore this word
  end -- if IGNORE_CAPITALIZED

  if IGNORE_MIXED_CASE  then
    -- ignore words in mixed case
    if string.find (word, "[A-Z]") and
       string.find (word, "[a-z]") then
      return word, "ignore"
    end -- this round, ignore this word
  end -- if IGNORE_MIXED_CASE

  if IGNORE_IMBEDDED_NUMBERS  then
    -- ignore words with numbers in them
    if string.find (word, "%d") then
      return word, "ignore"
    end -- this round, ignore this word
  end -- if IGNORE_IMBEDDED_NUMBERS

  uc_word = make_upper (word)  -- convert to upper-case if wanted

  -- if we already did "ignore all" on this particular word, ignore it again
  if ignore [word] then
    return word, "ignore"
  end -- this round, ignore this word
  
  -- if we said change A to B, change it again
  if change [word] then
    return change [word], "change"
  end -- change to this word
  
  -- table of suggestions, based on the metaphone
  local keyed_suggestions = {}
  
  -- get both metaphones
  local m1, m2 = utils.metaphone (word, METAPHONE_LENGTH)
  
  local function lookup_metaphone (m)
    local found = false
    for row in db:rows(string.format ("SELECT name FROM words WHERE metaphone = '%s'", m)) do
      local word = row [1]
      if make_upper (word) == uc_word then
        found = true -- found exact match
        break
      end  -- found
      if utils.edit_distance (make_upper (word), uc_word) < EDIT_DISTANCE then
        keyed_suggestions [word] = true
      end -- close enough
    end
    return found
  end -- lookup_metaphone
  
  -- look up first metaphone
  if lookup_metaphone (m1) then
    return word, "ok"
  end -- word found

  -- try 2nd metaphone
  if m2 then
    if lookup_metaphone (m2) then
      return word, "ok"
    end -- word found
  end -- have alternate metaphone

  -- pull into indexed table
  local suggestions = {}
  for k in pairs (keyed_suggestions) do
    table.insert (suggestions, k)
  end -- for 
     
  table.sort (suggestions, suggestions_compare (uc_word))
  
  -- not found? do spell check dialog
  local action, replacement = utils.spellcheckdialog (word, suggestions)
  
  -- they cancelled?
  if not action then
    error (cancelmessage)  --> forces us out of gsub loop
  end -- cancelled
  
  -- ignore this only - just return
  if action == "ignore" then
    return word, "ignore" -- use current word
  end -- ignore word

  -- ignore all of this word? add to list
  if action == "ignoreall" then
    ignore [word] = true
    return word, "ignore" -- use current word
  end -- ignore word

  -- add to user dictionary? 
  -- add to metaphone table, and rewrite dictionary
  if action == "add" then
    insert_word (word, 1)
    return word, "ok"
  end -- adding
  
  -- change word once? return replacement
  if action == "change" then
     return checkword_and_suggest (replacement)  -- however, re-check it
  end -- changing
  
  -- change all occurrences? add to table, return replacement
  if action == "changeall" then
    local newword, newaction = checkword_and_suggest (replacement) -- re-check it
    if newaction == "ok" then
       change [word] = newword
    end -- if approved
    return newword  -- return the new word
  end -- changing
  
  error "unexpected result from dialog"
end -- checkword_and_suggest

-- exported function to do the spellcheck
function spellcheck (line)
  change = {}  -- words to change
  ignore = {}  -- words to ignore
  
  -- we raise an error if they cancel the spell check dialog
  ok, result = xpcall (function () 
                        return string.gsub (line, pattern, checkword_and_suggest) 
                      end, debug.traceback)
 
  if ok then
    return result
  end -- not cancelled spell check

  -- whoops! syntax error?
  if not string.find (result, cancelmessage, 1, true) then
    error (result)
  end -- some syntax error
  
  return nil  --> shows they cancelled
end -- spellchecker

local notfound  -- table of not-found words, for spellcheck_string

-- check for one word, called by spellcheck_string
local function checkword (word)
  uc_word = make_upper (word)  -- convert to upper-case if wanted

  -- get first metaphone 
  local m = utils.metaphone (word, METAPHONE_LENGTH)
  
  local found = false
  for row in db:rows(string.format ("SELECT name FROM words WHERE metaphone = '%s'", m)) do
    local word = row [1]
    if make_upper (word) == uc_word then
      found = true -- found exact match
      break
    end  -- found
  end
  
  if found then return end  -- do nothing if word found

  -- otherwise insert our word   
  table.insert (notfound, word) 
end -- function checkword

-- exported function to spellcheck a string
function spellcheck_string (text)
  notfound = {}
  string.gsub (text, pattern, checkword)
  return notfound
end -- spellcheck_string

-- exported function to add a word to the user dictionary
function spellcheck_add_word (word, action, replacement)
  assert (action == "i", "Can only use action 'i' in user dictionary")  -- only "i" supported right now
  insert_word (word, 1)
end -- spellcheck_string

-- read one of the dictionaries
local function read_dict (dlg, name)
 if SHOW_PROGRESS_BAR then
   dlg:step ()
   dlg:status (directory .. name)
   if dlg:checkcancel () then
     error "Dictionary loading cancelled"
   end -- if cancelled
 end -- if SHOW_PROGRESS_BAR

  for line in io.lines (directory .. name) do
    insert_word (line, 0)
  end 
end -- read_dict
  
local function init ()

  -- make a suitable function depending on whether they want case-sensitive or not
  if CASE_SENSITIVE then
    make_upper = function (s) return s end -- return original
  else
    make_upper = function (s) return s:upper () end  -- make upper case
  end -- case-sensitivity test
  
  -- if no user dictionary, create it
  local f = io.open (directory .. userdict, "r")
  if not f then
    f = io.output (directory .. userdict)
    f:close ()
  else
    f:close ()
  end -- checking for user dictionary
  
  -- open database on disk
  db = assert (sqlite3.open( directory .. "spell.sqlite"))
  local words_table = false
  local count = 0
  
  -- if database just created, there won't be a words table
  for row in db:nrows("SELECT * FROM sqlite_master WHERE type = 'table' AND name = 'words'") do 
    if string.match (row.sql, "word_id") then  -- better be newer version
      words_table = true
    end -- if
  end
  
  -- if no words table, make one
  if not words_table then
      -- create a table to hold the words
    assert (db:execute[[
      DROP TABLE IF EXISTS words;
      CREATE TABLE words(
        word_id INTEGER NOT NULL PRIMARY KEY autoincrement,
        name  VARCHAR(10) NOT NULL,
        metaphone VARCHAR(10) NOT NULL,
        user INT(1)
      );
      CREATE INDEX metaphone_index ON words (metaphone);
      CREATE INDEX name_index ON words (name);
    ]])
  end -- if 
    
  -- check if table empty
  for row in db:rows('SELECT COUNT(*) FROM words') do 
    count = row [1] 
  end

  -- if empty, populate it  
  if count == 0 then
    
    local dlg
  
    if SHOW_PROGRESS_BAR then
      dlg = progress.new ("Loading dictionaries into SQLite database ...")
    
      dlg:range (0, #files)
      dlg:setstep (1)
    end -- if SHOW_PROGRESS_BAR
     
    assert (db:execute "BEGIN TRANSACTION");
    
    for k, v in ipairs (files) do
      ok, result = pcall (function () 
                            read_dict (dlg, v) 
                          end)
      if not ok then 
        if SHOW_PROGRESS_BAR then
          dlg:close ()
        end -- if SHOW_PROGRESS_BAR
        error (result)
      end -- not ok
    end -- reading each file
    
    if SHOW_PROGRESS_BAR then
      dlg:close ()
    end -- if SHOW_PROGRESS_BAR
    
    assert (db:execute "COMMIT");
    
  end -- if nothing in database
  
  
end -- init

-- when script is loaded, do initialization stuff

init ()

[EDIT] Fixed on 21st February 2009 to fix problem where words were only stored on their first metaphone (because the word was the primary key). This has been changed to have an autoincrement primary key, with the word name as just a non-unique index. Now you can have multiple words and multiple metaphones.

Nick Gammon Australia Forum Administrator Sun 15 Feb 2009 08:21 PM #8

If you ever need to find out what your custom-defined words are, copy and paste the following into your Immediate window (this is using Lua) and then click on the Run button.


local directory = utils.info ().app_directory .. "spell\\"
db = assert (sqlite3.open( directory .. "spell.sqlite"))
for row in db:rows ('SELECT name FROM words WHERE user = 1') do 
    print (row [1])
end
db:close ()

This will echo in the output window all your custom dictionary words. They could then be copied and pasted into your userdict.txt file.

How to add more dictionary files to the database

The spellchecker.lua file in the previous post only reads your dictionary files (like "english-words.10", "english-words.20" etc.) the first time it is run, and it does not find the database file spell.sqlite.

If you want to add extra files:

Save your custom words by running the script above, and copying and pasting them into the file userdict.txt file in the "spell" directory.
Close MUSHclient (to close the database)
In the "spell" directory, delete the file "spell.sqlite"
Add any extra dictionary files that you want into the "spell" directory.
Add the names of those extra files into the spellchecker.lua file (in the MUSHclient directory), near the top. It doesn't really matter exactly where the filenames go, but the suggested place below would be a good one. Of course, if you want to use a completely different language (like German), replace all the English file names with suitable ones containing German words. The format of the dictionary files is simply one word per line, at the start of the line.
```
local files = {

-- lower-case words

  "english-words.10",
  "english-words.20",
  "english-words.35",
  "english-words.40",

-- upper case words

  "english-upper.10",
  "english-upper.35",
  "english-upper.40",

-- American words

  "american-words.10",
  "american-words.20",

-- contractions (eg. aren't, doesn't)

  "english-contractions.10",
  "english-contractions.35",
  
--> put more file names here

-- user dictionary
  userdict,
  }
 
```
Restart MUSHclient. This will then notice that the spell.sqlite file is not there, and read all of the above files back into the database, along with your custom words from userdict.txt.

Nick Gammon Australia Forum Administrator Sun 22 Feb 2009 10:44 PM #9

Also see this thread for how to access the SQLite database from normal scripting (not necessarily Lua):

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