local mq = require('mq')
local sqlite3 = require('lsqlite3complete')
local db
local sqlquery
local debug_toggle = true
local _SQL = {}
_SQL.InScopeTbls = { 'AppData', 'Logs', 'NPCs', 'Items' }
_SQL.tblCreate = {
AppData = {
sql = 'CREATE TABLE AppData ( npc_id NUMERIC , npc_distance NUMERIC );',
tblCreated = false,
contents = {
a = '11', --place holders
b = '21', --place holders
c = '31' --place holders
}
},
Logs = {
sql = 'CREATE TABLE Logs ( npc_id NUMERIC , npc_distance NUMERIC );',
tblCreated = false,
contents = {
a = '12', --place holders
b = '22', --place holders
c = '32' --place holders
}
},
NPCs ={
sql = 'CREATE TABLE NPCs ( npc_id NUMERIC , npc_distance NUMERIC );',
tblCreated = false,
contents = {
a = '13', --place holders
b = '23', --place holders
c = '33' --place holders
}
},
Items ={
sql = 'CREATE TABLE Items ( id INTEGER PRIMARY KEY AUTOINCREMENT, min_lvl NUMERIC , max_level NUMERIC, item_name TEXT, item_id max_level, item_price NUMERIC );',
tblCreated = false,
contents = {
item1 = 'INSERT INTO Items (min_lvl, max_level, item_name, item_id, item_price) VALUES (1, 20, \'Minor Soulstone\', 76013, 11)',
item2 = 'INSERT INTO Items (min_lvl, max_level, item_name, item_id, item_price) VALUES (21, 30, \'Lesser Soulstone\', 76014, 25)',
item3 = 'INSERT INTO Items (min_lvl, max_level, item_name, item_id, item_price) VALUES (31, 40, \'Soulstone\', 76015, 53)',
item4 = 'INSERT INTO Items (min_lvl, max_level, item_name, item_id, item_price) VALUES (41, 50, \'Greater Soulstone\', 76016, 84)',
item5 = 'INSERT INTO Items (min_lvl, max_level, item_name, item_id, item_price) VALUES (51, 60, \'Faceted Soulstone\', 76017, 116)',
item6 = 'INSERT INTO Items (min_lvl, max_level, item_name, item_id, item_price) VALUES (61, 70, \'Pristine Soulstone\', 76018, 158)',
item7 = 'INSERT INTO Items (min_lvl, max_level, item_name, item_id, item_price) VALUES (71, 75, \'Glowing Soulstone\', 76019, 263)',
item8 = 'INSERT INTO Items (min_lvl, max_level, item_name, item_id, item_price) VALUES (76, 80, \'Prismatic Soulstone\', 76048, 420)',
item9 = 'INSERT INTO Items (min_lvl, max_level, item_name, item_id, item_price) VALUES (81, 85, \'Iridescent Soulstone\', 76065, 525)',
item10 = 'INSERT INTO Items (min_lvl, max_level, item_name, item_id, item_price) VALUES (86, 90, \'Phantasmal Soulstone\', 76274, 630)',
item11 = 'INSERT INTO Items (min_lvl, max_level, item_name, item_id, item_price) VALUES (91, 95, \'Luminous Soulstone\', 76275, 735)',
item12 = 'INSERT INTO Items (min_lvl, max_level, item_name, item_id, item_price) VALUES (96, 100, \'Coalescent Soulstone\', 94076, 840)',
item13 = 'INSERT INTO Items (min_lvl, max_level, item_name, item_id, item_price) VALUES (101, 105, \'Torrential Soulstone\', 94091, 1050)',
item14 = 'INSERT INTO Items (min_lvl, max_level, item_name, item_id, item_price) VALUES (106, 110, \'Draconic Soulstone\', 94092, 2100)',
item15 = 'INSERT INTO Items (min_lvl, max_level, item_name, item_id, item_price) VALUES (111,115, \'Velium Soulstone\', 94093, 3150)'
}
}
}
-- #### helpers ####
-- source for this snazzy little function an be found on various Lua scripts on the Redguides website.
--TODO: make this more "mine". Right now it works but need to do something else with this.
local Output = function(msg) print('\aw\at['..mq.TLO.Time()..'] [EZ Wipe Rez\aw] \a-t' .. msg) end
--this simple function accepts a lua table and value and loops through in an efforts to find match and returns trueif one is found.
local function FindTblVal (tab, val)
for index, value in ipairs(tab) do
if value == val then
return true
end
end
return false
end
-- #### end helpers ####
--setup our SQLLite DB - this will create a new DB if one does not exist or open if it does
local function SetupShop()
local mq_install_path = mq.TLO.MacroQuest.Path():gsub('\\', '/')
local config_path = '/config/'
local db_name = 'WipeEZRezInfo.db'
db = sqlite3.open(mq_install_path .. config_path .. db_name, sqlite3.OPEN_READWRITE + sqlite3.OPEN_CREATE)
end
--[[
Since this is a simple script, the DB will need to be built upon first run.
This checks the DB for the required in scope tables, and creates if they don't exist. Within the lua table above, the specific row inserts are listed as well.
Since my row inserts are small, this solution will work for this simple script. However, should I need to deploy a bigger dataset, a DB will either
need to be distributed or I will need some other solution.
--]]
local function ConfirmDBIntegeregity()
if db:isopen() then --is our DB open? sure hope so!
sqlquery = "SELECT tbl_name FROM sqlite_master WHERE type='table'" -- query all tables within the DB
for x in db:urows(sqlquery) do
if FindTblVal(_SQL.InScopeTbls, x) then -- sometimes SQLLite creates system tables within the DB (sqllite_sequence for example). I need to ignore any table wihtin the DB that is not in scope.
_SQL.tblCreate[x]['tblCreated'] = true -- update the created flag to true if found.
end
end
for i,tbl in pairs(_SQL.tblCreate) do
if tbl['tblCreated'] == false then
print('executing this sql coding',tbl['sql'] ) -- debug/change this. This is ugly!
db:exec(tbl['sql']) -- execute the table create TSQL
for x,tblz in pairs(_SQL.tblCreate[i].contents) do
--print(tblz) -- debug
db:exec(tblz) --loop through the contents of the table to insert the required records.
end
end
end
--TODO: implement a full debug system
if debug_toggle then
end
else
-- TODO: need some sort of graceful exit, i think...TBD
GracefulExit('Somethin ain\'t right yo!')
end
end
SetupShop()
ConfirmDBIntegeregity()
