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