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 ➜ Support for SQLite 3 added to normal scripting in version 4.40

Support for SQLite 3 added to normal scripting in version 4.40

This subject is now closed.     Refresh page


Posted by Nick Gammon   Australia  (23,120 posts)  Bio   Forum Administrator
Date Sun 22 Feb 2009 10:38 PM (UTC)

Amended on Mon 03 Jun 2019 06:06 AM (UTC) by Nick Gammon

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


Also see the SQLite Primer forum post about the fundamentals of SQL: http://mushclient.com/sql




Version 4.40 of MUSHclient introduces an SQLite API (application programming interface) for all supported script languages. This is in addition to the sqlite3 functions provided only to Lua scripts.

Preliminary discussion about SQLite is in this thread:

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

A full write-up on SQLite is at their web site:

http://www.sqlite.org


This post will discuss the way you can access databases from MUSHclient scripting. The examples will be in Lua, the recommended script language, but will work with minor syntactical modification in all the script languages.

Open and close the database


MUSHclient supports any number of SQLite databases open simultaneously. To distinguish between them, you supply a "database id" which is just a string you choose to identify each one.

Once opened, you supply this database ID to each subsequent database call.

All database related functions start with the word "Database", eg. DatabaseOpen, DatabaseClose etc.

The first action you need to take is to create or open the database. Databases can be held in memory or on disk. You need read or write permissions to access the database, as it is stored on disk as a normal disk file.

Function prototypes:


long DatabaseOpen(BSTR Name, BSTR Filename, long Flags);
long DatabaseClose(BSTR Name);


You can use various flags to control the opening behaviour:


Open_ReadOnly       = 0x00000001
Open_ReadWrite      = 0x00000002
Open_Create         = 0x00000004
Open_DeleteOnClose  = 0x00000008
Open_Exclusive      = 0x00000010
Open_Main_db        = 0x00000100
Open_Temp_db        = 0x00000200
Open_Transient_db   = 0x00000400
Open_Main_Journal   = 0x00000800
Open_Temp_Journal   = 0x00001000
Open_SubJournal     = 0x00002000
Open_Master_Journal = 0x00004000
Open_NoMutex        = 0x00008000
Open_FullMutex      = 0x00010000


Warning - the SQLite documentation suggests that you choose one of:


  • Open_ReadOnly (1)
  • Open_ReadWrite (2) or
  • Open_ReadWrite + Open_Create (6)


If not, the behaviour of the open may be undefined.


Example


DatabaseOpen ("db",   -- database ID
              GetInfo (66) .. "mytestdb.sqlite",   -- file name
              6)      -- flags

-- do stuff with the database here

DatabaseClose ("db")  -- close it


The above example opens "mytestdb.sqlite" in the same directory as the MUSHclient executable (obtained from GetInfo (66)), and uses flags Open_ReadWrite and Open_Create, to open for reading and writing, and create if it doesn't exist.

If successful, the call to DatabaseOpen will return zero, and the database ID is added to the valid table of databases. If not, a non-zero code will be returned, and this database ID cannot be used.

The special filename ":memory:" opens an in-memory database. This can be used to manipulate data (or play with SQL) without actually writing to disk.

Use DatabaseClose to close the database when you are finished with it.

If the database is still open when the world file is closed, the database is automatically closed.

Execute some SQL statements


The simplest way of executing SQL is to use DatabaseExec. This gives a simplified interface to compiling and executing SQL. A more complex method is described below.

Function prototype:


long DatabaseExec(BSTR Name, BSTR Sql);


You need to supply the database id of an existing opened database, and the SQL statements to be executed. More than one statement may be supplied, separated by semicolons. If one statement causes an error, execution will be terminated. The most recent error message string (if any) can be obtained by calling DatabaseError.

After successful execution, DatabaseExec will return 0. For more explanation if zero is not returned, call DatabaseError.

You cannot call DatabaseExec if you have called DatabasePrepare but not DatabaseFinalize, as a statement is currently being processed.

Example


DatabaseOpen ("db", GetInfo (66) .. "mytestdb.sqlite", 6)

DatabaseExec ("db", [[
DROP TABLE IF EXISTS weapons;
CREATE TABLE weapons(
        weapon_id INTEGER NOT NULL PRIMARY KEY autoincrement,
        name  TEXT NOT NULL,
        damage INT default 10,
        weight REAL
      );
      ]])
      
DatabaseClose ("db")  -- close it


Note that in Lua the sequence [[ ... ]] is a multiple-line string, so you can easily imbed SQL statements into your code without worrying about a lot of quotes symbols.

DatabaseExec is not suitable for executing SQL code that queries the database for data (like a SELECT statement), as there is no provision for getting the results back. In that situation use DatabasePrepare / DatabaseStep / DatabaseFinalize. Alternatively, use DatabaseGetField to get back the first column from the first row (like a count of things).


Prepare / Step / Finalize SQL statements


The more general way of executing SQL statements is to prepare/step/finalize. You can think of these steps like this:


  • DatabasePrepare - "compile" some SQL, ready for executing it
  • DatabaseStep - "execute" the SQL, getting a result of some sort
  • DatabaseFinalize - discard the prepared statement, ready to do something else.


Note: If you only want to get a single thing (like a count of spells) then DatabaseGetField will do a DatabasePrepare / DatabaseStep / DatabaseFinalize and return column 1 of row 1 of the result.

Function prototypes:


long DatabasePrepare(BSTR Name, BSTR Sql);
long DatabaseStep(BSTR Name);
long DatabaseFinalize(BSTR Name);
long DatabaseReset(BSTR Name);

long DatabaseGetField(BSTR Name, BSTR Sql);


For DatabasePrepare, you need to supply the database id of an existing opened database, and the SQL statement to be executed. Only one statement should be supplied. If multiple statements are supplied, only the first will be used. (eg. "drop table a; drop table b" - in this case only the first "drop table" would be executed).

If there is a syntax error in your SQL, that will be reported by a non-zero return code from DatabasePrepare. The most recent error message string (if any) can be obtained by calling DatabaseError.

Note that to include single quotes inside a string literal, in your SQL statement they must be doubled. For example:


INSERT INTO weapons (name, damage) VALUES ('Nick''s sword', 42)



Only one statement can be active at once, so after doing a DatabasePrepare you must do DatabaseFinalize before doing another DatabasePrepare. The exception is that if DatabasePrepare returns an error, then you do not need to do DatabaseFinalize because there is no active statement.

After successfully doing DatabasePrepare, you then need to call DatabaseStep for each row in the query. This is the execution phase. For SQL statements that return multiple rows of data, you need to do DatabaseStep once for each row. For SQL statements that just do one thing (like INSERT INTO) then you only need to call DatabaseStep once.

DatabaseStep will return SQLITE_ROW (100) if it has returned a row of data, otherwise it will return SQLITE_DONE (101) if it has reached the end of the query. You could make a loop that continues to call DatabaseStep until it no longer returns 100, in order to process every row in the query.

If you want to reprocess the SQL query from the start for some reason, you can call DatabaseReset and the query restarts at the beginning (you would then need to call DatabaseStep again to re-obtain the first row).

Example


DatabaseOpen ("db", GetInfo (66) .. "mytestdb.sqlite", 6)

DatabaseExec ("db", [[
DROP TABLE IF EXISTS weapons;
CREATE TABLE weapons(
        weapon_id INTEGER NOT NULL PRIMARY KEY autoincrement,
        name  TEXT NOT NULL,
        damage INT default 10,
        weight REAL
      );
      ]])
      
-- insert a record
DatabasePrepare ("db", "INSERT INTO weapons (name, damage) VALUES ('sword', 42)")  --> returns 0 (SQLITE_OK)
DatabaseStep ("db")   -- returns 101 (SQLITE_DONE)
DatabaseFinalize ("db")  -- returns 0 (SQLITE_OK)
      
DatabaseClose ("db")  -- close it


The example above shows how the three phases (prepare/step/finalize) are used to execute an SQL statement. This is a bit contrived because you can use DatabaseExec to do the same thing. The real power of prepare/step/finalize is to get data back from the database, so let's add a few records, and get them back ...


require "tprint"

DatabaseOpen ("db", GetInfo (66) .. "mytestdb.sqlite", 6)

rc = DatabaseExec ("db", [[
DROP TABLE IF EXISTS weapons;
CREATE TABLE weapons(
        weapon_id INTEGER NOT NULL PRIMARY KEY autoincrement,
        name  TEXT NOT NULL,
        damage INT default 10,
        weight REAL
      );
      ]])
      

-- put some data into the database
DatabaseExec ("db", 
  [[
  INSERT INTO weapons (name, damage) VALUES ('sword', 42);
  INSERT INTO weapons (name, damage) VALUES ('mace', 55);
  INSERT INTO weapons (name, damage) VALUES ('staff', 35);
  ]])

-- prepare a query
DatabasePrepare ("db", "SELECT * from weapons ORDER BY name")

-- find the column names
names = DatabaseColumnNames ("db")
tprint (names)

-- execute to get the first row
rc = DatabaseStep ("db")  -- read first row

-- now loop, displaying each row, and getting the next one
while rc == 100 do
  
  print ("")
  values = DatabaseColumnValues ("db")
  tprint (values)

  rc = DatabaseStep ("db")  -- read next row

end -- while loop

-- finished with the statement
DatabaseFinalize ("db")

DatabaseClose ("db")  -- close it


This more elaborate example shows looping over the result set, getting a row at a time (and using DatabaseColumnValues to get the column values), and displaying them. The output looks like this:


1="weapon_id"
2="name"
3="damage"
4="weight"

1=2
2="mace"
3=55

1=3
2="staff"
3=35

1=1
2="sword"
3=42


The first thing we can do after DatabasePrepare is to find out the column names by using DatabaseColumnNames. This returns a table containing the name of each column, indexed by the column number (starting at 1).

We can do this before doing a DatabaseStep, because the column names (and number of columns) is a property of the SQL statement, whether or not any rows are found that actually match the query.


Getting information about prepared statements


Once you have a prepared SQL statement there are various things you can do to find out more about it.


  • DatabaseColumns - find how many columns the query will return (per row) which might be zero
  • DatabaseColumnName - find the name of a particular column
  • DatabaseColumnNames - get a table of all the column names


Each SQL statement will potentially return zero or more columns of data, each time you do DatabaseStep. An SQL statement that does not return data (eg. INSERT INTO, or DROP TABLE) will have zero columns in the result set.

Otherwise, you will get one or more columns. You may not know in advance how many columns you are expecting, for example if you do "SELECT * from <table>".

After successfully doing a DatabasePrepare you can use DatabaseColumns to get the count of columns.

You can also use DatabaseColumnName to find a particular column name (eg. the name of column 5). Columns are indexed starting at 1, to be consistent with the way much of the rest of MUSHclient, and Lua, works. This differs from the SQLite documentation, where columns are indexed from zero.

You can also use DatabaseColumnNames to get a table of all the column names.

Function prototypes:


long DatabaseColumns(BSTR Name);
BSTR DatabaseColumnName(BSTR Name, long Column);
VARIANT DatabaseColumnNames(BSTR Name);


The column name is the name given in the "AS" clause in the query, if any. Otherwise SQLite assigns a column name, which is not guaranteed to be consistent between versions. Often the column name will simply be the name of the variable being queried, however calculations may return generated names.

For example, using this SQL statement:


DatabasePrepare ("db", "SELECT name AS 'weapon name', damage * 5 from weapons")


The returned column names were 'weapon name' and 'damage * 5'.

Getting information about a row


Once you have a row returned by calling DatabaseStep and getting the result SQLITE_ROW (100) that means you have a valid row to work with. You can find out various things about this row ...


  • DatabaseColumnType - for each column, what type of data we have in that row
  • DatabaseColumnText - convert that column's data into text
  • DatabaseColumnValue - return that column's data as a string, integer, float, or NULL
  • DatabaseColumnValues - return a table containing all the data for that row as string, integer, float, or NULL (depending on the column type)


The column number must be from 1 to the maximum number of columns (the first column is column 1). You can find the maximum number of columns by calling DatabaseColumns.

Column types are:


  • Integer: 1
  • Float: 2
  • Text: 3
  • Blob: 4
  • Null: 5



Function prototypes:


long DatabaseColumnType(BSTR Name, long Column);
BSTR DatabaseColumnText(BSTR Name, long Column);
VARIANT DatabaseColumnValue(BSTR Name, long Column);
VARIANT DatabaseColumnValues(BSTR Name);


Example of using these:


DatabaseOpen ("db", GetInfo (66) .. "mytestdb.sqlite", 6)

rc = DatabaseExec ("db", [[
DROP TABLE IF EXISTS weapons;
CREATE TABLE weapons(
        weapon_id INTEGER NOT NULL PRIMARY KEY autoincrement,
        name  TEXT NOT NULL,
        damage INT default 10,
        weight REAL
      );
      ]])
      
-- put a record in
DatabaseExec ("db", "INSERT INTO weapons (name, damage) VALUES ('sword', 42);")

-- get it back
DatabasePrepare ("db", "SELECT * from weapons")

-- first row
DatabaseStep ("db")

-- find column count
column_count = DatabaseColumns ("db")
print ("columns =", column_count)

-- for each column, display data
for i = 1, column_count do
  print ("column", i, "type", DatabaseColumnType ("db", i),
         "text", DatabaseColumnValue ("db", i))
end -- for 

-- done with statement
DatabaseFinalize ("db")

-- done with database
DatabaseClose ("db")  -- close it


Output


columns = 4
column 1 type 1 text 1
column 2 type 3 text sword
column 3 type 1 text 42
column 4 type 5 text nil


Note that column 1 (the primary key) had 1 automatically inserted into it by the autoincrement clause. Column 4 (weight) had nothing inserted, and had no default, so it remained as NULL, which was returned to Lua as nil.


Keeping track of changes


After inserting data into the database we often want to know whether it actually got inserted, and if so, using what primary key (if the keys are automatically allocated). A few extra functions help us with that.


  • DatabaseTotalChanges - This function returns the number of row changes caused by INSERT, UPDATE or DELETE statements since the database connection was opened. The count includes all changes from all trigger contexts. However, the count does not include changes used to implement REPLACE constraints, do rollbacks or ABORT processing, or DROP table processing. The changes are counted as soon as the statement that makes them is completed (when the statement handle is passed to DatabaseReset or DatabaseFinalize).

  • DatabaseChanges - This function returns the number of database rows that were changed or inserted or deleted by the most recently completed SQL statement on the database connection specified by the first parameter. Only changes that are directly specified by the INSERT, UPDATE, or DELETE statement are counted. Auxiliary changes caused by triggers are not counted. Use the DatabaseTotalChanges function to find the total number of changes including changes caused by triggers.

    A "row change" is a change to a single row of a single table caused by an INSERT, DELETE, or UPDATE statement. Rows that are changed as side effects of REPLACE constraint resolution, rollback, ABORT processing, DROP TABLE, or by any other mechanisms do not count as direct row changes.

  • DatabaseLastInsertRowid - this returns a string which is the most recent automatically allocated database key (for example, if you are using autoincrement on the primary key). Internally it is a 64-bit number, but since there is no ready mechanism for returning 64-bit numbers via the scripting interface, MUSHclient converts it to a string, and returns that.



Function prototypes:


long DatabaseTotalChanges(BSTR Name);
long DatabaseChanges(BSTR Name);
BSTR DatabaseLastInsertRowid(BSTR Name);



Keeping track of your databases


These two functions help you know which databases are open and what state they are in:


  • DatabaseList - returns a table of all active (open) database IDs.

  • DatabaseInfo - returns information about a particular database, as follows:


    • 1 - Name of database on disk
    • 2 - Has a DatabasePrepare been validly done recently (and is still active)?
    • 3 - Has a valid row been returned by DatabaseStep?
    • 4 - Count of columns returned from the last DatabasePrepare.




Function prototypes:


VARIANT DatabaseList();
VARIANT DatabaseInfo(BSTR Name, long InfoType);


Constants


Column types:


  • Integer : 1
  • Float : 2
  • Text : 3
  • Blob : 4
  • Null : 5


Return codes:


  • SQLITE_OK 0 : Successful result
  • SQLITE_ERROR 1 : SQL error or missing database
  • SQLITE_INTERNAL 2 : Internal logic error in SQLite
  • SQLITE_PERM 3 : Access permission denied
  • SQLITE_ABORT 4 : Callback routine requested an abort
  • SQLITE_BUSY 5 : The database file is locked
  • SQLITE_LOCKED 6 : A table in the database is locked
  • SQLITE_NOMEM 7 : A malloc() failed
  • SQLITE_READONLY 8 : Attempt to write a readonly database
  • SQLITE_INTERRUPT 9 : Operation terminated by sqlite3_interrupt()
  • SQLITE_IOERR 10 : Some kind of disk I/O error occurred
  • SQLITE_CORRUPT 11 : The database disk image is malformed
  • SQLITE_NOTFOUND 12 : NOT USED. Table or record not found
  • SQLITE_FULL 13 : Insertion failed because database is full
  • SQLITE_CANTOPEN 14 : Unable to open the database file
  • SQLITE_PROTOCOL 15 : NOT USED. Database lock protocol error
  • SQLITE_EMPTY 16 : Database is empty
  • SQLITE_SCHEMA 17 : The database schema changed
  • SQLITE_TOOBIG 18 : String or BLOB exceeds size limit
  • SQLITE_CONSTRAINT 19 : Abort due to constraint violation
  • SQLITE_MISMATCH 20 : Data type mismatch
  • SQLITE_MISUSE 21 : Library used incorrectly
  • SQLITE_NOLFS 22 : Uses OS features not supported on host
  • SQLITE_AUTH 23 : Authorization denied
  • SQLITE_FORMAT 24 : Auxiliary database format error
  • SQLITE_RANGE 25 : 2nd parameter to sqlite3_bind out of range
  • SQLITE_NOTADB 26 : File opened that is not a database file
  • SQLITE_ROW 100 : DatabaseStep has another row ready
  • SQLITE_DONE 101 : DatabaseStep has finished executing

    MUSHclient also returns the following codes where applicable:

  • -1 : Database id not found
  • -2 : Database not open
  • -3 : Already have prepared statement
  • -4 : Do not have prepared statement
  • -5 : Do not have a valid row
  • -6 : Database already exists under a different disk name
  • -7 : Column number out of range

- Nick Gammon

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

Posted by Nick Gammon   Australia  (23,120 posts)  Bio   Forum Administrator
Date Reply #1 on Mon 23 Feb 2009 01:57 AM (UTC)

Amended on Sat 08 Aug 2009 06:57 AM (UTC) by Nick Gammon

Message
Transactions

Transactions are good for various reasons:


  • They speed up multiple inserts or updates

  • A related group of actions becomes "atomic" - either the whole lot completes, or the whole lot fails. This stops the database getting into an inconsistent state.


Transactions speed up multiple actions because SQLite tries hard to make sure each individual transaction completes, or fails. Thus each transaction involves opening and closing disk files, waiting for disk I/O to complete and so on. Thus if a transaction does a lot at once, that time is spread over many actions.

An example of making a transaction is to take this earlier example:


-- put some data into the database
DatabaseExec ("db", 
  [[
  INSERT INTO weapons (name, damage) VALUES ('sword', 42);
  INSERT INTO weapons (name, damage) VALUES ('mace', 55);
  INSERT INTO weapons (name, damage) VALUES ('staff', 35);
  ]])


... and put BEGIN TRANSACTION / COMMIT around it, like this:


-- put some data into the database
DatabaseExec ("db", 
  [[
  BEGIN TRANSACTION;
  INSERT INTO weapons (name, damage) VALUES ('sword', 42);
  INSERT INTO weapons (name, damage) VALUES ('mace', 55);
  INSERT INTO weapons (name, damage) VALUES ('staff', 35);
  COMMIT;
  ]])


Now the transaction is only committed if it finishes. However we have a problem here. The database is still locked, because the BEGIN TRANSACTION succeeded, but the COMMIT did not.

So we really need to test for failure, and roll back the database if that happens, like this:


-- put some data into the database
rc = DatabaseExec ("db", 
  [[
  BEGIN TRANSACTION;
  INSERT INTO weapons (name, damage) VALUES ('sword', 42);
  INSERT INTO weapons (name, damage) VALUES ('mace', 55);
  INSERT INTO weapons (name, damage) VALUES ('staff', 35);
  amfrunct the baterpomp;
  COMMIT;
  ]])

if rc ~= 0 then
  DatabaseExec ("db", "ROLLBACK")
end -- if not successful


The nonsense line "amfrunct the baterpomp;" causes an SQL error to be reported, however the BEGIN TRANSACTION and the three INSERT INTOs have all been done.

By testing the return code from the DatabaseExec we can decide to roll back the entire transaction, thus unlocking the database and making it useable again.

- Nick Gammon

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

Posted by Nick Gammon   Australia  (23,120 posts)  Bio   Forum Administrator
Date Reply #2 on Mon 23 Feb 2009 03:38 AM (UTC)
Message
If you want to check that your databases are setup correctly offline, download their command-line tool and use that (241 Kb):

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

Some examples of using it are on the page:

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

- Nick Gammon

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

Posted by Nick Gammon   Australia  (23,120 posts)  Bio   Forum Administrator
Date Reply #3 on Sat 28 Feb 2009 12:41 AM (UTC)
Message
Alternatively, there is a GUI SQLite browser available for OS/X, Windows and Linux:

http://sourceforge.net/projects/sqlitebrowser/



- Nick Gammon

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

Posted by Nick Gammon   Australia  (23,120 posts)  Bio   Forum Administrator
Date Reply #4 on Sat 28 Feb 2009 12:42 AM (UTC)
Message

Example of it in operation:


- Nick Gammon

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

Posted by Nick Gammon   Australia  (23,120 posts)  Bio   Forum Administrator
Date Reply #5 on Fri 27 Mar 2009 03:46 AM (UTC)

Amended on Fri 27 Mar 2009 03:57 AM (UTC) by Nick Gammon

Message
Analyzing your database

You can also download an SQLite "analyzer" program from:

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

Look for a file named something like: sqlite3_analyzer-3.6.1.zip

Running that program on my MUSHclient preferences file gave me quite a bit of output, an example is:


*** Table PREFS and all its indices **********************************

Percentage of total database..........  40.6%
Number of entries..................... 144
Bytes of storage consumed............. 13312
Bytes of payload...................... 8364        62.8%
Average payload per entry............. 58.08
Average unused bytes per entry........ 30.01
Average fanout........................ 4.00
Fragmentation.........................  66.7%
Maximum payload per entry............. 5089
Entries that use overflow............. 1            0.69%
Index pages used...................... 1
Primary pages used.................... 7
Overflow pages used................... 5
Total pages used...................... 13
Unused bytes on index pages........... 991         96.8%
Unused bytes on primary pages......... 3217        44.9%
Unused bytes on overflow pages........ 114          2.2%
Unused bytes on all pages............. 4322        32.5%


- Nick Gammon

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

Posted by Nick Gammon   Australia  (23,120 posts)  Bio   Forum Administrator
Date Reply #6 on Fri 27 Mar 2009 03:57 AM (UTC)
Message
Cleaning up your database

Another handy thing to do to your database is "vacuum" it, which cleans out unused space by rebuilding the database file. Since this is an SQL command you can do it programmatically, or using the command-line sqlite3.exe program, like this:


sqlite3 mushclient_prefs.sqlite

SQLite version 3.6.10
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> VACUUM;
sqlite> .exit



Or, in a single line:


sqlite3 mushclient_prefs.sqlite vacuum



- Nick Gammon

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

Posted by Nick Gammon   Australia  (23,120 posts)  Bio   Forum Administrator
Date Reply #7 on Fri 27 Mar 2009 03:59 AM (UTC)
Message
Backing up your database

A database file can be backed up into SQL statements like this:


sqlite3 mushclient_prefs.sqlite .dump > prefs.sql


This creates a file prefs.sql with the necessary SQL statements in it for recreating the database.

- Nick Gammon

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

Posted by Nick Gammon   Australia  (23,120 posts)  Bio   Forum Administrator
Date Reply #8 on Fri 27 Mar 2009 04:02 AM (UTC)

Amended on Fri 27 Mar 2009 05:32 AM (UTC) by Nick Gammon

Message
Restoring from a backup

Later on, you can restore from a backed-up SQL file like this:


sqlite3 mushclient_prefs.sqlite < prefs.sql


You would generally delete or rename the old copy first, otherwise you are likely to get a lot of "table already exists" error messages.

- Nick Gammon

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

Posted by Maxhrk   USA  (76 posts)  Bio
Date Reply #9 on Sun 16 Aug 2009 05:31 PM (UTC)
Message
Hi Nick, I think this codes below probably need to be appended to constant.lua in the mushclient plugins folder.


-- ----------------------------------------------------------
-- Codes returned by Sqlite.
-- ----------------------------------------------------------
SQLITE_OK = 0 		-- Successful result 
SQLITE_ERROR = 1  	--SQL error or missing database 
SQLITE_INTERNAL = 2 --Internal logic error in SQLite 
SQLITE_PERM = 3 	--Access permission denied 
SQLITE_ABORT = 4 	--Callback routine requested an abort 
SQLITE_BUSY = 5 	--The database file is locked 
SQLITE_LOCKED = 6   --A table in the database is locked 
SQLITE_NOMEM = 7 	--A malloc() failed 
SQLITE_READONLY = 8 --Attempt to write a readonly database 
SQLITE_INTERRUPT = 9 --Operation terminated by sqlite3_interrupt()
SQLITE_IOERR = 10 	 -- Some kind of disk I/O error occurred 
SQLITE_CORRUPT = 11  -- The database disk image is malformed 
SQLITE_NOTFOUND = 12 -- NOT USED. Table or record not found 
SQLITE_FULL = 13 	 -- Insertion failed because database is full 
SQLITE_CANTOPEN = 14 -- Unable to open the database file 
SQLITE_PROTOCOL = 15 -- NOT USED. Database lock protocol error 
SQLITE_EMPTY = 16    -- Database is empty 
SQLITE_SCHEMA = 17 	 -- The database schema changed 
SQLITE_TOOBIG = 18   -- String or BLOB exceeds size limit 
SQLITE_CONSTRAINT = 19 -- Abort due to constraint violation 
SQLITE_MISMATCH = 20   -- Data type mismatch 
SQLITE_MISUSE = 21     -- Library used incorrectly 
SQLITE_NOLFS = 22 	-- Uses OS features not supported on host 
SQLITE_AUTH = 23 	-- Authorization denied 
SQLITE_FORMAT = 24 	-- Auxiliary database format error 
SQLITE_RANGE = 25 	-- 2nd parameter to sqlite3_bind out of range 
SQLITE_NOTADB = 26 	-- File opened that is not a database file 
SQLITE_ROW = 100  	--DatabaseStep has another row ready 
SQLITE_DONE = 101 	-- DatabaseStep has finished executing 
Top

Posted by Nick Gammon   Australia  (23,120 posts)  Bio   Forum Administrator
Date Reply #10 on Sun 16 Aug 2009 09:15 PM (UTC)

Amended on Sun 16 Aug 2009 09:16 PM (UTC) by Nick Gammon

Message
Those constants are already in the sqlite3 table, which is automatically available in Lua. The exact names are:


sqlite3.OK = 0
sqlite3.INTEGER = 1
sqlite3.INTERNAL = 2
sqlite3.PERM = 3
sqlite3.ABORT = 4
sqlite3.BUSY = 5
sqlite3.LOCKED = 6
sqlite3.NOMEM = 7
sqlite3.READONLY = 8
sqlite3.INTERRUPT = 9
sqlite3.IOERR = 10
sqlite3.CORRUPT = 11
sqlite3.NOTFOUND = 12
sqlite3.FULL = 13
sqlite3.CANTOPEN = 14
sqlite3.PROTOCOL = 15
sqlite3.EMPTY = 16
sqlite3.SCHEMA = 17
sqlite3.TOOBIG = 18
sqlite3.CONSTRAINT = 19
sqlite3.MISMATCH = 20
sqlite3.MISUSE = 21
sqlite3.NOLFS = 22
sqlite3.FORMAT = 24
sqlite3.RANGE = 25
sqlite3.NOTADB = 26
sqlite3.ROW = 100
sqlite3.DONE = 101



I generated the above listing by running this script in the Immediate window:


t = {}
for k, v in pairs (sqlite3) do
  if type (v) == "number" then
   t [v] = k
  end -- if
end -- for

require "pairsbykeys"

for k, v in pairsByKeys(t) do
  print ("sqlite3." .. v .. " = " .. k)
end -- for



You could just do:


require "tprint"
tprint (sqlite3)


But the output looks slightly more messy.

- Nick Gammon

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

Posted by Nick Gammon   Australia  (23,120 posts)  Bio   Forum Administrator
Date Reply #11 on Sun 16 Aug 2009 09:19 PM (UTC)
Message
That was just for reference. The constants are there already, so you could change:


-- now loop, displaying each row, and getting the next one
while rc == 100 do
  
  print ("")
  values = DatabaseColumnValues ("db")
  tprint (values)

  rc = DatabaseStep ("db")  -- read next row

end -- while loop


to:


-- now loop, displaying each row, and getting the next one
while rc == sqlite3.ROW do
  
  print ("")
  values = DatabaseColumnValues ("db")
  tprint (values)

  rc = DatabaseStep ("db")  -- read next row

end -- while loop


That makes it more readable.

- Nick Gammon

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

Posted by Nick Gammon   Australia  (23,120 posts)  Bio   Forum Administrator
Date Reply #12 on Mon 20 Sep 2010 10:18 PM (UTC)

Amended on Wed 22 Sep 2010 03:54 AM (UTC) by Nick Gammon

Message
Database maintenance

Various "pragma" commands can be issued to SQLite to do special actions to the database. One way of doing that is via the external sqlite3.exe program which you can download from:

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

Look for "A command-line program for accessing and modifying SQLite databases.".

In my examples below I have installed that program into the "path" (for example, into the same place as where the database is) and am typing the commands into the Windows "command window". (Use the Start menu -> Run and type "cmd").

Any response from the command is shown underneath.

Check free list


Return the number of unused pages in the database file:


sqlite3 mapper.db 'pragma freelist_count'
155


This shows there are 155 free "pages" (that is, disk used) on this database file. The default for a page is 1024 bytes.

Vacuum free list


This reorganizes the database, reducing its size by reclaiming the free pages. (Note that this is not a pragma command as such, but straight SQL).


sqlite3 mapper.db 'vacuum'


Afterwards:


sqlite3 mapper.db 'pragma freelist_count'
0


That confirms the free pages are "vacuumed up".

Integrity check


This pragma does an integrity check of the entire database. It looks for out-of-order records, missing pages, malformed records, and corrupt indices. If any problems are found, then strings are returned (as multiple rows with a single column per row) which describe the problems.

If no errors are found, a single row with the value "ok" is returned.


sqlite3 mapper.db 'pragma integrity_check'
ok


Journal mode


This lets you control the journal mode, which is how SQLite saves transactions before comitting them.

Effectively, the Write-Ahead Logging (WAL) inverts the way transactions are applied.

Previously (with DELETE journal mode) the original records were saved in the rollback journal file, and then the changes were applied to the database. To rollback, the journal was replayed to recreate the original database.

However with Write-Ahead Logging, the database is unchanged, and the commits are written to a separate file, which is then applied to the database from time to time. To rollback a partial change, the changes in the separate file are simply discarded. This has the advantage that reads can be done from the database during a transaction, because the original database is not in a half-completed transaction state.

For more details read:

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

To convert a database to Write-Ahead Logging:


sqlite3 mapper.db 'pragma journal_mode = wal'
wal


To convert a database to Delete (normal) mode:


sqlite3 mapper.db 'pragma journal_mode = delete'
delete


To see what mode is in use:


sqlite3 mapper.db 'pragma journal_mode'
delete



Page count


This shows the count of pages in the database. A quick check shows that (the default anyway) for Windows is a page being 1024 bytes.


sqlite3 mapper.db 'pragma page_count'
4014


In this case the database is 4110336 bytes (4014 * 1024).

Quick check


The pragma is like integrity_check except that it does not verify that index content matches table content. By skipping the verification of index content, quick_check is able to run much faster than integrity_check.


sqlite3 mapper.db 'pragma quick_check'
ok


Table Info


This pragma returns one row for each column in the named table. Columns in the result set include the column name, data type, whether or not the column can be NULL, and the default value for the column.


sqlite3 mapper.db 'pragma table_info(rooms)'
0|roomid|INTEGER|0||1
1|uid|TEXT|1||0
2|name|TEXT|0||0
3|area|TEXT|0||0
4|building|TEXT|0||0
5|terrain|TEXT|0||0
6|info|TEXT|0||0
7|notes|TEXT|0||0
8|x|INTEGER|0||0
9|y|INTEGER|0||0
10|z|INTEGER|0||0
11|date_added|DATE|0||0


Analyze


The ANALYZE command gathers statistics about indices and stores them in a special tables in the database where the query optimizer can use them to help make better index choices. This is not a pragma as such, but straight SQL.

This could be used to help queries run faster.


sqlite3 mapper.db 'analyze'


Others


There are other pragmas as described here:

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

- 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.


59,203 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.