· 7 years ago · Nov 09, 2018, 07:22 PM
1--[[
2 MySQLite - Abstraction mechanism for SQLite and MySQL
3 Why use this?
4 - Easy to use interface for MySQL
5 - No need to modify code when switching between SQLite and MySQL
6 - Queued queries: execute a bunch of queries in order an run the callback when all queries are done
7 License: LGPL V2.1 (read here: https://www.gnu.org/licenses/lgpl-2.1.html)
8 Supported MySQL modules:
9 - MySQLOO
10 - tmysql4
11 Note: When both MySQLOO and tmysql4 modules are installed, MySQLOO is used by default.
12 /*---------------------------------------------------------------------------
13 Documentation
14 ---------------------------------------------------------------------------*/
15 MySQLite.initialize([config :: table]) :: No value
16 Initialize MySQLite. Loads the config from either the config parameter OR the MySQLite_config global.
17 This loads the module (if necessary) and connects to the MySQL database (if set up).
18 The config must have this layout:
19 {
20 EnableMySQL :: Bool - set to true to use MySQL, false for SQLite
21 Host :: String - database hostname
22 Username :: String - database username
23 Password :: String - database password (keep away from clients!)
24 Database_name :: String - name of the database
25 Database_port :: Number - connection port (3306 by default)
26 Preferred_module :: String - Preferred module, case sensitive, must be either "mysqloo" or "tmysql4"
27 MultiStatements :: Bool - Only available in tmysql4: allow multiple SQL statements per query
28 }
29 ----------------------------- Utility functions -----------------------------
30 MySQLite.isMySQL() :: Bool
31 Returns whether MySQLite is set up to use MySQL. True for MySQL, false for SQLite.
32 Use this when the query syntax between SQLite and MySQL differs (example: AUTOINCREMENT vs AUTO_INCREMENT)
33 MySQLite.SQLStr(str :: String) :: String
34 Escapes the string and puts it in quotes.
35 It uses the escaping method of the module that is currently being used.
36 MySQLite.tableExists(tbl :: String, callback :: function, errorCallback :: function)
37 Checks whether table tbl exists.
38 callback format: function(res :: Bool)
39 res is a boolean indicating whether the table exists.
40 The errorCallback format is the same as in MySQLite.query.
41 ----------------------------- Running queries -----------------------------
42 MySQLite.query(sqlText :: String, callback :: function, errorCallback :: function) :: No value
43 Runs a query. Calls the callback parameter when finished, calls errorCallback when an error occurs.
44 callback format:
45 function(result :: table, lastInsert :: number)
46 Result is the table with results (nil when there are no results or when the result list is empty)
47 lastInsert is the row number of the last inserted value (use with AUTOINCREMENT)
48 Note: lastInsert is NOT supported when using SQLite.
49 errorCallback format:
50 function(error :: String, query :: String) :: Bool
51 error is the error given by the database module.
52 query is the query that triggered the error.
53 Return true to suppress the error!
54 MySQLite.queryValue(sqlText :: String, callback :: function, errorCallback :: function) :: No value
55 Runs a query and returns the first value it comes across.
56 callback format:
57 function(result :: any)
58 where the result is either a string or a number, depending on the requested database field.
59 The errorCallback format is the same as in MySQLite.query.
60 ----------------------------- Transactions -----------------------------
61 MySQLite.begin() :: No value
62 Starts a transaction. Use in combination with MySQLite.queueQuery and MySQLite.commit.
63 MySQLite.queueQuery(sqlText :: String, callback :: function, errorCallback :: function) :: No value
64 Queues a query in the transaction. Note: a transaction must be started with MySQLite.begin() for this to work.
65 The callback will be called when this specific query has been executed successfully.
66 The errorCallback function will be called when an error occurs in this specific query.
67 See MySQLite.query for the callback and errorCallback format.
68 MySQLite.commit(onFinished)
69 Commits a transaction and calls onFinished when EVERY queued query has finished.
70 onFinished is NOT called when an error occurs in one of the queued queries.
71 onFinished is called without arguments.
72 ----------------------------- Hooks -----------------------------
73 DatabaseInitialized
74 Called when a successful connection to the database has been made.
75]]
76
77local bit = bit
78local debug = debug
79local error = error
80local ErrorNoHalt = ErrorNoHalt
81local hook = hook
82local include = include
83local pairs = pairs
84local require = require
85local sql = sql
86local string = string
87local table = table
88local timer = timer
89local tostring = tostring
90local GAMEMODE = GM or GAMEMODE
91local mysqlOO
92local TMySQL
93local _G = _G
94
95local multistatements
96
97local MySQLite_config = MySQLite_config or RP_MySQLConfig or FPP_MySQLConfig
98local moduleLoaded
99
100local function loadMySQLModule()
101 if moduleLoaded or not MySQLite_config or not MySQLite_config.EnableMySQL then return end
102
103 local moo, tmsql = file.Exists("bin/gmsv_mysqloo_*.dll", "LUA"), file.Exists("bin/gmsv_tmysql4_*.dll", "LUA")
104
105 if not moo and not tmsql then
106 error("Could not find a suitable MySQL module. Supported modules are MySQLOO and tmysql4.")
107 end
108 moduleLoaded = true
109
110 require(moo and tmsql and MySQLite_config.Preferred_module or
111 moo and "mysqloo" or
112 "tmysql4")
113
114 multistatements = CLIENT_MULTI_STATEMENTS
115
116 mysqlOO = mysqloo
117 TMySQL = tmysql
118end
119loadMySQLModule()
120
121module("MySQLite")
122
123
124function initialize(config)
125 MySQLite_config = config or MySQLite_config
126
127 if not MySQLite_config then
128 ErrorNoHalt("Warning: No MySQL config!")
129 end
130
131 loadMySQLModule()
132
133 if MySQLite_config.EnableMySQL then
134 connectToMySQL(MySQLite_config.Host, MySQLite_config.Username, MySQLite_config.Password, MySQLite_config.Database_name, MySQLite_config.Database_port)
135 else
136 timer.Simple(0, function()
137 GAMEMODE.DatabaseInitialized = GAMEMODE.DatabaseInitialized or function() end
138 hook.Call("DatabaseInitialized", GAMEMODE)
139 end)
140 end
141end
142
143local CONNECTED_TO_MYSQL = false
144local msOOConnect
145databaseObject = nil
146
147local queuedQueries
148local cachedQueries
149
150function isMySQL()
151 return CONNECTED_TO_MYSQL
152end
153
154function begin()
155 if not CONNECTED_TO_MYSQL then
156 sql.Begin()
157 else
158 if queuedQueries then
159 debug.Trace()
160 error("Transaction ongoing!")
161 end
162 queuedQueries = {}
163 end
164end
165
166function commit(onFinished)
167 if not CONNECTED_TO_MYSQL then
168 sql.Commit()
169 if onFinished then onFinished() end
170 return
171 end
172
173 if not queuedQueries then
174 error("No queued queries! Call begin() first!")
175 end
176
177 if #queuedQueries == 0 then
178 queuedQueries = nil
179 if onFinished then onFinished() end
180 return
181 end
182
183 -- Copy the table so other scripts can create their own queue
184 local queue = table.Copy(queuedQueries)
185 queuedQueries = nil
186
187 -- Handle queued queries in order
188 local queuePos = 0
189 local call
190
191 -- Recursion invariant: queuePos > 0 and queue[queuePos] <= #queue
192 call = function(...)
193 queuePos = queuePos + 1
194
195 if queue[queuePos].callback then
196 queue[queuePos].callback(...)
197 end
198
199 -- Base case, end of the queue
200 if queuePos + 1 > #queue then
201 if onFinished then onFinished() end -- All queries have finished
202 return
203 end
204
205 -- Recursion
206 local nextQuery = queue[queuePos + 1]
207 query(nextQuery.query, call, nextQuery.onError)
208 end
209
210 query(queue[1].query, call, queue[1].onError)
211end
212
213function queueQuery(sqlText, callback, errorCallback)
214 if CONNECTED_TO_MYSQL then
215 table.insert(queuedQueries, {query = sqlText, callback = callback, onError = errorCallback})
216 return
217 end
218 -- SQLite is instantaneous, simply running the query is equal to queueing it
219 query(sqlText, callback, errorCallback)
220end
221
222local function msOOQuery(sqlText, callback, errorCallback, queryValue)
223 local query = databaseObject:query(sqlText)
224 local data
225 query.onData = function(Q, D)
226 data = data or {}
227 data[#data + 1] = D
228 end
229
230 query.onError = function(Q, E)
231 if databaseObject:status() == mysqlOO.DATABASE_NOT_CONNECTED then
232 table.insert(cachedQueries, {sqlText, callback, queryValue})
233
234 -- Immediately try reconnecting
235 msOOConnect(MySQLite_config.Host, MySQLite_config.Username, MySQLite_config.Password, MySQLite_config.Database_name, MySQLite_config.Database_port)
236 return
237 end
238
239 local supp = errorCallback and errorCallback(E, sqlText)
240 if not supp then error(E .. " (" .. sqlText .. ")") end
241 end
242
243 query.onSuccess = function()
244 local res = queryValue and data and data[1] and table.GetFirstValue(data[1]) or not queryValue and data or nil
245 if callback then callback(res, query:lastInsert()) end
246 end
247 query:start()
248end
249
250local function tmsqlQuery(sqlText, callback, errorCallback, queryValue)
251 local call = function(res)
252 res = res[1] -- For now only support one result set
253 if not res.status then
254 local supp = errorCallback and errorCallback(res.error, sqlText)
255 if not supp then error(res.error .. " (" .. sqlText .. ")") end
256 return
257 end
258
259 if not res.data or #res.data == 0 then res.data = nil end -- compatibility with other backends
260 if queryValue and callback then return callback(res.data and res.data[1] and table.GetFirstValue(res.data[1]) or nil) end
261 if callback then callback(res.data, res.lastid) end
262 end
263
264 databaseObject:Query(sqlText, call)
265end
266
267local function SQLiteQuery(sqlText, callback, errorCallback, queryValue)
268 sql.m_strError = "" -- reset last error
269
270 local lastError = sql.LastError()
271 local Result = queryValue and sql.QueryValue(sqlText) or sql.Query(sqlText)
272
273 if sql.LastError() and sql.LastError() ~= lastError then
274 local err = sql.LastError()
275 local supp = errorCallback and errorCallback(err, sqlText)
276 if supp == false then error(err .. " (" .. sqlText .. ")", 2) end
277 return
278 end
279
280 if callback then callback(Result) end
281 return Result
282end
283
284function query(sqlText, callback, errorCallback)
285 local qFunc = (CONNECTED_TO_MYSQL and ((mysqlOO and msOOQuery) or (TMySQL and tmsqlQuery))) or SQLiteQuery
286 return qFunc(sqlText, callback, errorCallback, false)
287end
288
289function queryValue(sqlText, callback, errorCallback)
290 local qFunc = (CONNECTED_TO_MYSQL and ((mysqlOO and msOOQuery) or (TMySQL and tmsqlQuery))) or SQLiteQuery
291 return qFunc(sqlText, callback, errorCallback, true)
292end
293
294local function onConnected()
295 CONNECTED_TO_MYSQL = true
296
297 -- Run the queries that were called before the connection was made
298 for k, v in pairs(cachedQueries or {}) do
299 cachedQueries[k] = nil
300 if v[3] then
301 queryValue(v[1], v[2])
302 else
303 query(v[1], v[2])
304 end
305 end
306 cachedQueries = {}
307
308 hook.Call("DatabaseInitialized", GAMEMODE.DatabaseInitialized and GAMEMODE or nil)
309end
310
311msOOConnect = function(host, username, password, database_name, database_port)
312 databaseObject = mysqlOO.connect(host, username, password, database_name, database_port)
313
314 if timer.Exists("darkrp_check_mysql_status") then timer.Remove("darkrp_check_mysql_status") end
315
316 databaseObject.onConnectionFailed = function(_, msg)
317 timer.Simple(5, function()
318 msOOConnect(MySQLite_config.Host, MySQLite_config.Username, MySQLite_config.Password, MySQLite_config.Database_name, MySQLite_config.Database_port)
319 end)
320 error("Connection failed! " .. tostring(msg) .. "\nTrying again in 5 seconds.")
321 end
322
323 databaseObject.onConnected = onConnected
324
325 databaseObject:connect()
326end
327
328local function tmsqlConnect(host, username, password, database_name, database_port)
329 local db, err = TMySQL.initialize(host, username, password, database_name, database_port, nil, MySQLite_config.MultiStatements and multistatements or nil)
330 if err then error("Connection failed! " .. err .. "\n") end
331
332 databaseObject = db
333 onConnected()
334end
335
336function connectToMySQL(host, username, password, database_name, database_port)
337 database_port = database_port or 3306
338 local func = mysqlOO and msOOConnect or TMySQL and tmsqlConnect or function() end
339 func(host, username, password, database_name, database_port)
340end
341
342function SQLStr(str)
343 local escape =
344 not CONNECTED_TO_MYSQL and sql.SQLStr or
345 mysqlOO and function(str) return "\"" .. databaseObject:escape(tostring(str)) .. "\"" end or
346 TMySQL and function(str) return "\"" .. databaseObject:Escape(tostring(str)) .. "\"" end
347
348 return escape(str)
349end
350
351function tableExists(tbl, callback, errorCallback)
352 if not CONNECTED_TO_MYSQL then
353 local exists = sql.TableExists(tbl)
354 callback(exists)
355
356 return exists
357 end
358
359 queryValue(string.format("SHOW TABLES LIKE %s", SQLStr(tbl)), function(v)
360 callback(v ~= nil)
361 end, errorCallback)
362end