summaryrefslogtreecommitdiffstatshomepage
path: root/plugins/data/database.lua
diff options
context:
space:
mode:
author Vas Crabb <vas@vastheman.com>2022-06-25 04:57:24 +1000
committer Vas Crabb <vas@vastheman.com>2022-06-25 04:57:24 +1000
commit923ef2c25db75ccb6a6d747304fc9c79ef454a46 (patch)
tree1a53bc7898adc2a7015a5a9e208c5df8cb024023 /plugins/data/database.lua
parent2f453da00cab66472bde7bc40ca22ce7f4b89e74 (diff)
-plugins.data: Reworked the code substantialy.
* Use the plugin data folder for storing the cache. The history folder may be read-only or shared with different configurations. * Don't create the cache database or surrounding folder if there's nothing to store in it. * Actually use prepared queries multiple times rather than always destroying them after a single use. * Added proper error checking for most database operations. * Improved query performance by avoiding outer joins and table scans. -bus/nubus: Made the Macintosh Display Cards map the blue channel to white with monochrome monitors. Also added logging for PLL configuration to help debug how CRTC and RAMDAC clocks work in the future.
Diffstat (limited to 'plugins/data/database.lua')
-rw-r--r--plugins/data/database.lua170
1 files changed, 144 insertions, 26 deletions
diff --git a/plugins/data/database.lua b/plugins/data/database.lua
index be52f23ee63..e0249182409 100644
--- a/plugins/data/database.lua
+++ b/plugins/data/database.lua
@@ -1,41 +1,159 @@
-local sql = require("lsqlite3")
-local datfile = {}
+local sql = require('lsqlite3')
local db
-local function check_db(msg)
+local function check_db_result(msg)
if db:errcode() > sql.OK then
- emu.print_error(string.format("Error: %s (%s - %s)", msg, db:errcode(), db:errmsg()))
+ emu.print_error(string.format('Error: %s (%s - %s)', msg, db:errcode(), db:errmsg()))
+ return false
end
+ return true
end
-do
- local dbpath = emu.subst_env(mame_manager.ui.options.entries.historypath:value():match("([^;]+)"))
- db = sql.open(dbpath .. "/history.db")
- if not db then
- lfs.mkdir(dbpath)
- db = sql.open(dbpath .. "/history.db")
+local function settings_path()
+ return emu.subst_env(manager.machine.options.entries.homepath:value():match('([^;]+)')) .. '/data'
+end
+
+local function check_version_table()
+ local found = false
+ db:exec(
+ [[SELECT COUNT(*) FROM sqlite_master WHERE name = 'version' AND type = 'table';]],
+ function (udata, cols, values, names)
+ found = tonumber(values[1]) > 0
+ return 0
+ end)
+ if check_db_result('checking for "version" table') and (not found) then
+ db:exec(
+ [[CREATE TABLE version (
+ datfile VARCHAR NOT NULL,
+ version VARCHAR NOT NULL,
+ PRIMARY KEY (datfile));]])
+ found = check_db_result('creating "version" table')
+ end
+ if not found then
+ db:close()
+ db = nil
+ end
+end
+
+local function open_existing()
+ db = sql.open(settings_path() .. '/history.db', sql.OPEN_READWRITE)
+ if db then
+ check_version_table()
+ end
+ return db
+end
+
+local function open_create()
+ -- first try to create settings directory
+ local dir = settings_path()
+ local attr = lfs.attributes(dir)
+ if (not attr) and (not lfs.mkdir(dir)) then
+ emu.print_error(string.format('Error creating data plugin settings directory "%s"', dir))
+ elseif attr and (attr.mode ~= 'directory') then
+ emu.print_error(string.format('Error opening data plugin database: "%s" is not a directory', dir))
+ else
+ -- now try to open the database
+ local dbpath = dir .. '/history.db'
+ db = sql.open(dbpath, sql.OPEN_READWRITE | sql.OPEN_CREATE)
if not db then
- emu.print_error("Unable to create history.db")
- return false
+ emu.print_error(string.format('Error opening data plugin database "%s"', dbpath))
+ else
+ check_version_table()
end
- check_db("opening database")
end
+ return db
end
-if db then
- local found = false
- db:exec("select * from sqlite_master where name = 'version'", function() found = true return 0 end)
- check_db("checking for 'version' table")
- if not found then
- db:exec([[
- CREATE TABLE version (
- version VARCHAR NOT NULL,
- datfile VARCHAR UNIQUE NOT NULL)]])
- check_db("creating 'version' table")
+
+local dbtable = {
+ ROW = sql.ROW,
+ BUSY = sql.BUSY,
+ DONE = sql.DONE,
+ check = check_db_result }
+
+function dbtable.sanitize_name(name)
+ return name:gsub('[^%w%."]', '_')
+end
+
+function dbtable.get_version(filename)
+ -- don't try to create the database here, just return nil if it doesn't exist
+ if (not db) and (not open_existing()) then
+ return nil
+ end
+ local query = db:prepare([[SELECT version FROM version WHERE datfile = ?;]])
+ query:bind_values(filename)
+ local result
+ while result == nil do
+ local status = query:step()
+ if status == sql.ROW then
+ result = query:get_value(0)
+ elseif status ~= sql.BUSY then
+ break
+ end
+ end
+ query:finalize()
+ return result
+end
+
+function dbtable.set_version(filename, version)
+ if (not db) and (not open_create()) then
+ return nil
+ end
+ local query
+ if version ~= nil then
+ query = db:prepare(
+ [[INSERT INTO version (datfile, version) VALUES (?1, ?2)
+ ON CONFLICT(datfile) DO UPDATE set version = ?2;]])
+ query:bind_values(filename, version)
+ else
+ query = db:prepare([[DELETE FROM version WHERE datfile = ?1;]])
+ query:bind_values(filename)
+ end
+ local result
+ while result == nil do
+ local status = query:step()
+ if status == sqlite3.DONE then
+ result = true
+ elseif result ~= sqlite3.ROW then
+ result = false
+ end
+ end
+ query:finalize()
+ return result
+end
+
+function dbtable.prepare(...)
+ if (not db) and open_create() then
+ dbtable.prepare = function (...) return db:prepare(...) end
+ end
+ if db then
+ return db:prepare(...)
+ else
+ return nil
end
end
-local dbtable = { prepare = function(...) return db:prepare(...) end,
- exec = function(...) return db:exec(...) end, ROW = sql.ROW, check = check_db }
+function dbtable.exec(...)
+ if (not db) and open_create() then
+ dbtable.exec = function (...) return db:exec(...) end
+ end
+ if db then
+ return db:exec(...)
+ else
+ return nil
+ end
+end
+
+function dbtable.open_data_file(file)
+ local fh, filepath
+ for path in mame_manager.ui.options.entries.historypath:value():gmatch('([^;]+)') do
+ filepath = emu.subst_env(path) .. '/' .. file
+ fh = io.open(filepath, 'r')
+ if fh then
+ break
+ end
+ end
+ return fh, filepath, dbtable.sanitize_name(file), dbtable.get_version(file)
+end
-return db and dbtable or false
+return dbtable