| Posted by
| Nick Gammon
Australia (23,057 posts) Bio
Forum Administrator |
| Message
| An alternative to serializing variables like that is to use the SQLite3 database interface.
Here is an example. First some helper functions:
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
-- 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
Open the database (creating it if necessary):
-- open database on disk
databasename = GetInfo (66) .. "tracker.db" -- in MUSHclient directory
db = "db" -- just an internal identifier
DatabaseOpen (db, databasename, 6) -- Open_ReadWrite + Open_Create (6)
Create a couple of tables if they don't exist:
-- create the table
dbcheck (DatabaseExec (db, [[
CREATE TABLE IF NOT EXISTS players(
PlayerID INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
Name TEXT UNIQUE,
Class TEXT
)
]]))
-- create the LastQuest table
dbcheck (DatabaseExec (db, [[
CREATE TABLE IF NOT EXISTS LastQuest(
PlayerID INTEGER NOT NULL PRIMARY KEY,
Pracs INTEGER,
Qp INTEGER,
Gold INTEGER,
Trivia INTEGER,
Trains INTEGER
)
]]))
Find the player in the database (Charneus in this case) and if not found, add him:
-- add a player (if necessary)
-- test data
playerName = "Charneus"
playerClass = "Mage"
-- see if player already exists
PlayerID = DatabaseGetField (db,
string.format ("SELECT PlayerID FROM players WHERE Name = %s", fixsql (playerName)))
-- not on database, add it
if PlayerID == nil then
dbcheck (DatabaseExec (db, string.format ("INSERT INTO Players (Name, Class) VALUES (%s, %s)",
fixsql (playerName),
fixsql (playerClass))))
PlayerID = tonumber (DatabaseLastInsertRowid (db))
-- add some quest stats
dbcheck (DatabaseExec (db, (string.format (
"INSERT INTO LastQuest (PlayerID, Pracs, Qp, Gold, Trivia, Trains) " ..
"VALUES (%i, 0, 0, 0, 0, 0)", PlayerID ))))
end -- if
print ("PlayerID =", PlayerID)
We also added a record into LastQuest with zero for each field.
Now update the LastQuest table with the current info:
-- test data
Pracs = 22
Qp = 33
Gold = 44
Trivia = 55
Trains = 666
-- update quest stats
dbcheck (DatabaseExec (db, (string.format (
"UPDATE LastQuest SET Pracs = %i, Qp = %i, Gold = %i, Trivia = %i, Trains = %i " ..
"WHERE PlayerID = %i",
Pracs, Qp, Gold, Trivia, Trains, -- new data
PlayerID )))) -- primary key
Find the current LastQuest information:
-- get data back
-- prepare a query
dbcheck (DatabasePrepare (db, string.format ("SELECT * from LastQuest WHERE PlayerID = %i", PlayerID)))
-- find the column names
names = DatabaseColumnNames (db)
-- execute to get the first row
rc = DatabaseStep (db) -- read first row
-- now loop, displaying each row, and getting the next one
while rc == sqlite3.ROW do
print ("")
values = DatabaseColumnValues (db)
for k, v in ipairs (names) do
print (v, "=", values [k])
end -- for
rc = DatabaseStep (db) -- read next row
end -- while loop
-- finished with the statement
DatabaseFinalize (db)
Output from above:
Pracs = 22
Qp = 33
Gold = 44
Trivia = 55
Trains = 666
When done, close the database:
DatabaseClose (db) -- close it
This is more complex than serializing tables, but more flexible perhaps in the long run. Also once you write to the database the data is on disk, even if the program crashes.
The Aardwolf mapper, for example, uses SQLite3 for its mapper database, and that is nice and fast. |
- Nick Gammon
www.gammon.com.au, www.mushclient.com | | Top |
|
![[Go to top]](/images/gototop.gif){kind=small}
top