Advertisement
Guest User

axel

a guest
Mar 2nd, 2018
104
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 12.68 KB | None | 0 0
  1. -- connection settings
  2. hostname = "localhost"
  3. username = "root"
  4. password = "QbY57OIE4Vz5JnVolUxA5t3boHa+Wjc1qZCv1dqIvO8="
  5. database = "mtajmk"
  6. port = 3306
  7. -- external mysql connection settings
  8. externalhostname = get( "forums_hostname" )
  9. externalusername = get( "forums_username" )
  10. externalpassword = get( "forums_password" )
  11. externaldatabase = get( "forums_database" )
  12. externalport = tonumber( get( "forums_port" ) )
  13.  
  14. -- global things
  15. local MySQLConnection = nil
  16. local forumConnection = nil
  17. local resultPool = { }
  18. local queryPool = { }
  19. local sqllog = false
  20. local countqueries = 0
  21.  
  22.  
  23. function getMySQLUsername()
  24. return username
  25. end
  26.  
  27. function getMySQLPassword()
  28. return password
  29. end
  30.  
  31. function getMySQLDBName()
  32. return db
  33. end
  34.  
  35. function getMySQLHost()
  36. return host
  37. end
  38.  
  39. function getMySQLPort()
  40. return port
  41. end
  42.  
  43. --Debug
  44. function tellMeMySQLStates(thePlayer)
  45. if exports.integration:isPlayerScripter(thePlayer) then
  46. if forumConnection then
  47. if mysql_ping(forumConnection) then
  48. outputChatBox("Forum Connection is online.", thePlayer, 0, 255, 0)
  49. else
  50. outputChatBox("Main DB is offline.", thePlayer, 255, 0, 0)
  51. end
  52. else
  53. outputChatBox("Forum Connection is offline.", thePlayer, 255, 0, 0)
  54. end
  55. if MySQLConnection then
  56. if mysql_ping(MySQLConnection) then
  57. outputChatBox("Main DB is online.", thePlayer, 0, 255, 0)
  58. else
  59. outputChatBox("Main DB is offline.", thePlayer, 255, 0, 0)
  60. end
  61. else
  62. outputChatBox("Main DB is offline.", thePlayer, 255, 0, 0)
  63. end
  64. end
  65. end
  66. addCommandHandler("testdb", tellMeMySQLStates, false, false)
  67.  
  68. --Forum settings
  69. function openForumDatabase(res)
  70. outputDebugString("--FORUMS DATABASE CREDENTIALS--")
  71. outputDebugString("--HOST: "..(externalhostname or "Error"))
  72. outputDebugString("--DB: "..(externaldatabase or "Error"))
  73. outputDebugString("--USER: "..(externalusername or "Error"))
  74. outputDebugString("--PW: "..(externalpassword and "***********" or "Error"))
  75. outputDebugString("--PORT: "..(externalport or "Error"))
  76. outputDebugString("--RESULT: ")
  77. forumConnection = mysql_connect(externalhostname, externalusername, externalpassword, externaldatabase, externalport)
  78. if (not forumConnection) then
  79. if (res == getThisResource()) then
  80. outputDebugString("Cannot connect to the forum database.")
  81. end
  82. return nil
  83. else
  84. outputDebugString("OK")
  85. end
  86. return nil
  87. end
  88. addEventHandler("onResourceStart", getResourceRootElement(getThisResource()), openForumDatabase, false)
  89.  
  90. function destroyForumConnection()
  91. if (not forumConnection) then
  92. return nil
  93. end
  94. mysql_close(forumConnection)
  95. return nil
  96. end
  97. addEventHandler("onResourceStop", getResourceRootElement(getThisResource()), destroyForumConnection, false)
  98.  
  99. function forumPing()
  100. if (mysql_ping(forumConnection) == false) then
  101. -- FUU, NO MOAR CONNECTION
  102. destroyForumConnection()
  103. openForumDatabase(nil)
  104. if (mysql_ping(forumConnection) == false) then
  105. logForumSQLError()
  106. return false
  107. end
  108. return true
  109. end
  110.  
  111. return true
  112. end
  113.  
  114. function forumQuery(str)
  115. if sqllog then
  116. exports['logs']:logMessage(str, 24)
  117. end
  118. countqueries = countqueries + 1
  119.  
  120. if (forumPing()) then
  121. local result = mysql_query(forumConnection, str)
  122. if (not result) then
  123. logForumSQLError(str)
  124. return false
  125. end
  126.  
  127. local resultid = getFreeResultPoolID()
  128. resultPool[resultid] = result
  129. queryPool[resultid] = str
  130. return resultid
  131. end
  132. return false
  133. end
  134.  
  135. function forum_query_insert_free(str)
  136. local queryresult = forumQuery(str)
  137. if not (queryresult == false) then
  138. local result = forum_insert_id()
  139. free_result(queryresult)
  140. return result
  141. end
  142. return false
  143. end
  144.  
  145. function forum_insert_id()
  146. return mysql_insert_id(forumConnection) or false
  147. end
  148.  
  149. function forum_query_free(str)
  150. local queryresult = forumQuery(str)
  151. if not (queryresult == false) then
  152. free_result(queryresult)
  153. return true
  154. end
  155. return false
  156. end
  157.  
  158. function forum_query_fetch_assoc(str)
  159. local queryresult = forumQuery(str)
  160. if not (queryresult == false) then
  161. local result = fetch_assoc(queryresult)
  162. free_result(queryresult)
  163. return result
  164. end
  165. return false
  166. end
  167. --End Forum
  168.  
  169. -- connectToDatabase - Internal function, to spawn a DB connection
  170. function connectToDatabase(res)
  171. outputDebugString("--FORUMS DATABASE CREDENTIALS--")
  172. outputDebugString("--HOST: "..(hostname or "Error"))
  173. outputDebugString("--DB: "..(database or "Error"))
  174. outputDebugString("--USER: "..(username or "Error"))
  175. outputDebugString("--PW: "..(password and "***********" or "Error"))
  176. outputDebugString("--PORT: "..(port or "Error"))
  177. outputDebugString("--RESULT: ")
  178.  
  179. MySQLConnection = mysql_connect(hostname, username, password, database, port)
  180.  
  181. if (not MySQLConnection) then
  182. if (res == getThisResource()) then
  183. outputDebugString("Cannot connect to the MTA database.")
  184. cancelEvent(true, "Cannot connect to the database.")
  185. end
  186. return nil
  187. else
  188. outputDebugString("OK")
  189. end
  190.  
  191. return nil
  192. end
  193. addEventHandler("onResourceStart", getResourceRootElement(getThisResource()), connectToDatabase, false)
  194.  
  195. -- destroyDatabaseConnection - Internal function, kill the connection if theres one.
  196. function destroyDatabaseConnection()
  197. if (not MySQLConnection) then
  198. return nil
  199. end
  200. mysql_close(MySQLConnection)
  201. return nil
  202. end
  203. addEventHandler("onResourceStop", getResourceRootElement(getThisResource()), destroyDatabaseConnection, false)
  204.  
  205. -- do something usefull here
  206. function logSQLError(str)
  207. local message = str or 'N/A'
  208. outputDebugString("MYSQL ERROR "..mysql_errno(MySQLConnection) .. ": " .. mysql_error(MySQLConnection))
  209. exports['logs']:logMessage("MYSQL ERROR [QUERY] " .. message .. " [ERROR] " .. mysql_errno(MySQLConnection) .. ": " .. mysql_error(MySQLConnection), 24)
  210. end
  211.  
  212. function logForumSQLError(str)
  213. local message = str or 'N/A'
  214. outputDebugString("MYSQL ERROR "..mysql_errno(forumConnection) .. ": " .. mysql_error(forumConnection))
  215. exports['logs']:logMessage("MYSQL ERROR [QUERY] " .. message .. " [ERROR] " .. mysql_errno(forumConnection) .. ": " .. mysql_error(forumConnection), 24)
  216. end
  217.  
  218. function getFreeResultPoolID()
  219. local size = #resultPool
  220. if (size == 0) then
  221. return 1
  222. end
  223. for index, query in ipairs(resultPool) do
  224. if (query == nil) then
  225. return index
  226. end
  227. end
  228. return (size + 1)
  229. end
  230.  
  231. ------------ EXPORTED FUNCTIONS ---------------
  232.  
  233. function ping()
  234. if (mysql_ping(MySQLConnection) == false) then
  235. -- FUU, NO MOAR CONNECTION
  236. destroyDatabaseConnection()
  237. connectToDatabase(nil)
  238. if (mysql_ping(MySQLConnection) == false) then
  239. logSQLError()
  240. return false
  241. end
  242. return true
  243. end
  244.  
  245. return true
  246. end
  247.  
  248. function escape_string(str)
  249. if (ping()) then
  250. return mysql_escape_string(MySQLConnection, str)
  251. end
  252. return false
  253. end
  254.  
  255. function query(str)
  256. --outputDebugString("[mySQL]...")
  257. if sqllog then
  258. exports['logs']:logMessage(str, 24)
  259. end
  260. countqueries = countqueries + 1
  261.  
  262. if (ping()) then
  263. local result = mysql_query(MySQLConnection, str)
  264. if (not result) then
  265. logSQLError(str)
  266. return false
  267. end
  268.  
  269. local resultid = getFreeResultPoolID()
  270. resultPool[resultid] = result
  271. queryPool[resultid] = str
  272. return resultid
  273. end
  274. return false
  275. end
  276.  
  277. function unbuffered_query(str)
  278. if sqllog then
  279. exports['logs']:logMessage(str, 24)
  280. end
  281. countqueries = countqueries + 1
  282.  
  283. if (ping()) then
  284. local result = mysql_unbuffered_query(MySQLConnection, str)
  285. if (not result) then
  286. logSQLError(str)
  287. return false
  288. end
  289.  
  290. local resultid = getFreeResultPoolID()
  291. resultPool[resultid] = result
  292. queryPool[resultid] = str
  293. return resultid
  294. end
  295. return false
  296. end
  297.  
  298. function query_free(str)
  299. local queryresult = query(str)
  300. if not (queryresult == false) then
  301. free_result(queryresult)
  302. return true
  303. end
  304. return false
  305. end
  306.  
  307. function rows_assoc(resultid)
  308. if (not resultPool[resultid]) then
  309. return false
  310. end
  311. return mysql_rows_assoc(resultPool[resultid])
  312. end
  313.  
  314. function fetch_assoc(resultid)
  315. if (not resultPool[resultid]) then
  316. return false
  317. end
  318. return mysql_fetch_assoc(resultPool[resultid])
  319. end
  320.  
  321. function free_result(resultid)
  322. if (not resultPool[resultid]) then
  323. return false
  324. end
  325. mysql_free_result(resultPool[resultid])
  326. table.remove(resultPool, resultid)
  327. table.remove(queryPool, resultid)
  328. return nil
  329. end
  330.  
  331. -- incase a nub wants to use it, FINE
  332. function result(resultid, row_offset, field_offset)
  333. if (not resultPool[resultid]) then
  334. return false
  335. end
  336. return mysql_result(resultPool[resultid], row_offset, field_offset)
  337. end
  338.  
  339. function num_rows(resultid)
  340. if (not resultPool[resultid]) then
  341. return false
  342. end
  343. return mysql_num_rows(resultPool[resultid])
  344.  
  345. end
  346.  
  347. function insert_id()
  348. return mysql_insert_id(MySQLConnection) or false
  349. end
  350.  
  351. function query_fetch_assoc(str)
  352. local queryresult = query(str)
  353. if not (queryresult == false) then
  354. local result = fetch_assoc(queryresult)
  355. free_result(queryresult)
  356. return result
  357. end
  358. return false
  359. end
  360.  
  361. function query_rows_assoc(str)
  362. local queryresult = query(str)
  363. if not (queryresult == false) then
  364. local result = rows_assoc(queryresult)
  365. free_result(queryresult)
  366. return result
  367. end
  368. return false
  369. end
  370.  
  371. function query_insert_free(str)
  372. local queryresult = query(str)
  373. if not (queryresult == false) then
  374. local result = insert_id()
  375. free_result(queryresult)
  376. return result
  377. end
  378. return false
  379. end
  380.  
  381. function escape_string(str)
  382. if not (str) then return false end
  383. return mysql_escape_string(MySQLConnection, str)
  384. end
  385.  
  386. function debugMode()
  387. if (sqllog) then
  388. sqllog = false
  389. else
  390. sqllog = true
  391. end
  392. return sqllog
  393. end
  394.  
  395. function returnQueryStats()
  396. return countqueries
  397. -- maybe later more
  398. end
  399.  
  400. function getOpenQueryStr( resultid )
  401. if (not queryPool[resultid]) then
  402. return false
  403. end
  404.  
  405. return queryPool[resultid]
  406. end
  407.  
  408. local resources = {
  409. ["mysql"] = getResourceFromName("mysql"),
  410. ["shop-system"] = getResourceFromName("shop-system"),
  411. ["vehicle-manager"] = getResourceFromName("vehicle-manager"),
  412. }
  413.  
  414. addCommandHandler( 'mysqlleaky',
  415. function(thePlayer)
  416. if exports.integration:isPlayerScripter(thePlayer) then
  417. outputDebugString("queryPool="..tostring(#queryPool))
  418. outputChatBox("#queryPool="..tostring(#queryPool), thePlayer)
  419. --[[
  420. local count = 0
  421. local res = {}
  422. local pretty = {}
  423. for k, v in pairs( resources ) do
  424. if not res[v] then
  425. res[v] = {}
  426. end
  427. table.insert(res[v], queryPool[k])
  428. count = count + 1
  429. end
  430.  
  431. if count == 0 then
  432. outputDebugString('Not leaking anything.')
  433. return
  434. end
  435.  
  436. outputDebugString('Only leaking a mere ' .. count .. ' result handles.')
  437.  
  438. for k, v in pairs( res ) do
  439. outputDebugString(tostring(k) .. ': ' .. tostring(#v) .. "\n\t" .. table.concat(v, "\n\t"))
  440. end
  441. --]]
  442. end
  443. end
  444. )
  445.  
  446. --Custom functions
  447. local function createWhereClause( array, required )
  448. if not array then
  449. -- will cause an error if it's required and we wanna concat it.
  450. return not required and '' or nil
  451. end
  452. local strings = { }
  453. for i, k in pairs( array ) do
  454. table.insert( strings, "`" .. i .. "` = '" .. ( tonumber( k ) or escape_string( k ) ) .. "'" )
  455. end
  456. return ' WHERE ' .. table.concat(strings, ' AND ')
  457. end
  458.  
  459. function select( tableName, clause )
  460. local array = {}
  461. local result = query( "SELECT * FROM " .. tableName .. createWhereClause( clause ) )
  462. if result then
  463. while true do
  464. local a = fetch_assoc( result )
  465. if not a then break end
  466. table.insert(array, a)
  467. end
  468. free_result( result )
  469. return array
  470. end
  471. return false
  472. end
  473.  
  474. function select_one( tableName, clause )
  475. local a
  476. local result = query( "SELECT * FROM " .. tableName .. createWhereClause( clause ) .. ' LIMIT 1' )
  477. if result then
  478. a = fetch_assoc( result )
  479. free_result( result )
  480. return a
  481. end
  482. return false
  483. end
  484.  
  485. function insert( tableName, array )
  486. local keyNames = { }
  487. local values = { }
  488. for i, k in pairs( array ) do
  489. table.insert( keyNames, i )
  490. table.insert( values, tonumber( k ) or escape_string( k ) )
  491. end
  492.  
  493. local q = "INSERT INTO `"..tableName.."` (`" .. table.concat( keyNames, "`, `" ) .. "`) VALUES ('" .. table.concat( values, "', '" ) .. "')"
  494.  
  495. return query_insert_free( q )
  496. end
  497.  
  498. function update( tableName, array, clause )
  499.  
  500. local strings = { }
  501. for i, k in pairs( array ) do
  502. table.insert( strings, "`" .. i .. "` = " .. ( k == mysql_null() and "NULL" or ( "'" .. ( tonumber( k ) or escape_string( k ) ) .. "'" ) ) )
  503. end
  504. local q = "UPDATE `" .. tableName .. "` SET " .. table.concat( strings, ", " ) .. createWhereClause( clause, true )
  505.  
  506. return query_free( q )
  507. end
  508.  
  509. function delete( tableName, clause )
  510. return query_free( "DELETE FROM " .. tableName .. createWhereClause( clause, true ) )
  511. end
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement