Guest User

Untitled

a guest
Feb 13th, 2017
112
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. --[[
  2. Name: sv_mysql.lua
  3.  
  4.  
  5. ]]--
  6.  
  7.  
  8. local RemoveErrors = " DEBUG Error "
  9.  
  10. ReDeemSQL = {};
  11. ReDeemSQL["FixErrors"] = ErrorNoHalt("SQL - Remove" ..RemoveErrors)
  12.  
  13. require "tmysql4"
  14.  
  15. GM.SQL = (GAMEMODE or GM).SQL or {}
  16. GM.SQL.m_intConnectRetryInterval = GM.Config.SQLReconnectInterval
  17. GM.SQL.m_bVerbose = false
  18.  
  19. function GM.SQL:LogMsg( str )
  20. ErrorNoHalt( "[SQL] ".. str, "\n" )
  21. end
  22.  
  23. function GM.SQL:LogDev( str, ... )
  24. if not self.m_bVerbose then return end
  25. ErrorNoHalt( "[SQL-DEBUG] ".. str, ..., "\n" )
  26. end
  27.  
  28. function GM.SQL:Connect( strHostName, strUserName, strPassword, strDatabaseName )
  29. if self.m_bConnected then return end
  30.  
  31. self:ConnectReadOnly( strHostName, strUserName, strPassword, strDatabaseName )
  32. self:ConnectWritePool( strHostName, strUserName, strPassword, strDatabaseName )
  33. self:InitWriteQueue()
  34. self.m_bConnected = true
  35. self:InitGamemodeTables()
  36. end
  37.  
  38. function GM.SQL:IsConnected()
  39. return self.m_bConnected and true
  40. end
  41.  
  42. function GM.SQL:Tick()
  43. self:TickPlayerIntervals()
  44. end
  45.  
  46. function GM.SQL:PlayerInitialSpawn( pPlayer )
  47. self:AssignPlayerPoolID( pPlayer )
  48. end
  49.  
  50. function GM.SQL:PlayerDisconnected( strSID64 )
  51. if not self:GetPlayerPoolID( strSID64 ) then return end
  52. self:CommitPlayerDiffs( strSID64 )
  53. self:UnrefPlayerPoolID( strSID64 )
  54. end
  55.  
  56. function GM.SQL:ShutDown()
  57. for k, v in pairs( player.GetAll() ) do
  58. if not self:GetPlayerPoolID( v:SteamID64() ) then continue end
  59. self:CommitPlayerDiffs( v:SteamID64() )
  60. self:UnrefPlayerPoolID( v:SteamID64() )
  61. end
  62. end
  63.  
  64. -- ----------------------------------------------------------------
  65. -- Read Only
  66.  
  67. GM.SQL.m_tblReadQueue = (GAMEMODE or GM).SQL.m_tblReadQueue or {}
  68.  
  69. function GM.SQL:ConnectReadOnly( strHostName, strUserName, strPassword, strDatabaseName )
  70. local db, err = tmysql.initialize( strHostName, strUserName, strPassword, strDatabaseName )
  71. if not db then return error( "Unable to initialize a database connection! Error: ".. err ) end
  72. self:LogMsg( "Read-only database connected." )
  73.  
  74. self.m_pDBReadOnly = db
  75. self.m_pDBReadOnly:Query( "SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;", function( tblData )
  76. self:LogMsg( "Read database set to read-only mode." )
  77.  
  78. timer.Create( "KeepAliveReadDB", 10, 0, function()
  79. if not self.m_pDBReadOnly then return end
  80. self.m_pDBReadOnly:Query( "SELECT 5+5;" )
  81. end )
  82. end )
  83. end
  84.  
  85. function GM.SQL:QueryReadOnly( strQuery, funcCallback )
  86. if not self.m_pDBReadOnly then return end
  87. if not self.m_bWaitingReconnect and self.m_pDBReadOnly:IsConnected() then --We can run this now
  88. self.m_pDBReadOnly:Query( strQuery, function( tblData )
  89. tblData = tblData[1]
  90.  
  91. if tblData.status then --Query ran OK
  92. funcCallback( tblData.data )
  93. else
  94. self:LogMsg( "Read Query Error! Error: ".. tblData.error )
  95. --We lost connection, put this in the queue and try to start the reconnect loop
  96. if tblData.error and tblData.error:lower() == "mysql server has gone away" then
  97. table.insert( self.m_tblReadQueue, { strQuery, funcCallback } )
  98. self:ReconnectReadWorker()
  99. end
  100. end
  101. end )
  102. else
  103. --We lost connection, put this in the queue and try to start the reconnect loop
  104. table.insert( self.m_tblReadQueue, { strQuery, funcCallback } )
  105. self:ReconnectReadWorker()
  106. end
  107. end
  108.  
  109. function FixErrors()
  110.  
  111. local AttemptReconnect
  112.  
  113. if AttemptReconnect == true then
  114. for k,v in pairs(ReDeemSQL) do
  115. print(RemoveErrors)
  116. end
  117. end
  118. end
  119.  
  120. function GM.SQL:ReconnectReadWorker()
  121. if self.m_bWaitingReconnect then return end --We are already trying to reconnect right now
  122.  
  123. self:LogMsg( "Attempting to reconnect the read-only database." )
  124. self.m_bWaitingReconnect = true
  125.  
  126. self.m_pDBReadOnly:Query( "SELECT 5+5;", function( tblData )
  127. if tblData.error and tblData.error:lower() == "mysql server has gone away" then
  128. self:LogMsg( "Connection to read-only database failed, retrying in ".. self.m_intConnectRetryInterval.. " seconds..." )
  129.  
  130. timer.Simple( self.m_intConnectRetryInterval +1, function()
  131. if not self.m_pDBReadOnly or not self.m_bWaitingReconnect then return end
  132. self.m_bWaitingReconnect = nil
  133. self:ReconnectReadWorker()
  134. end )
  135. else
  136. self:LogMsg( "Read-only database reconnected." )
  137. self.m_pDBReadOnly:Query( "SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;", function( tblData )
  138. if tblData.error and tblData.error:lower() == "mysql server has gone away" then
  139. self:LogMsg( "Connection to read-only database failed, retrying in ".. self.m_intConnectRetryInterval.. " seconds..." )
  140.  
  141. timer.Simple( self.m_intConnectRetryInterval +1, function()
  142. if not self.m_pDBReadOnly or not self.m_bWaitingReconnect then return end
  143. self.m_bWaitingReconnect = nil
  144. self:ReconnectReadWorker()
  145. end )
  146. else
  147. self:LogMsg( "Read database set to read-only mode." )
  148. self.m_bWaitingReconnect = nil
  149.  
  150. local runQueue = table.Copy( self.m_tblReadQueue )
  151. self.m_tblReadQueue = {}
  152.  
  153. for k, v in ipairs( runQueue ) do
  154. self:QueryReadOnly( v[1], v[2] )
  155. end
  156. end
  157. end )
  158. end
  159. end )
  160. end
  161.  
  162. -- ----------------------------------------------------------------
  163. -- Write Pool
  164.  
  165. GM.SQL.m_intWritePoolSize = GM.Config.SQLNumWriteWorkers
  166. GM.SQL.m_tblWorkerStatus = (GAMEMODE or GM).SQL.m_tblWorkerStatus or {}
  167. GM.SQL.m_tblWritePool = (GAMEMODE or GM).SQL.m_tblWritePool or {}
  168. GM.SQL.m_tblWriteQueue = (GAMEMODE or GM).SQL.m_tblWriteQueue or {}
  169. GM.SQL.m_tblPlayerPoolIDs = (GAMEMODE or GM).SQL.m_tblPlayerPoolIDs or {}
  170.  
  171. GM.SQL.WORKER_RUNNING_QUERY = 0
  172. GM.SQL.WORKER_CONNECTING = 1
  173. GM.SQL.WORKER_IDLE = 2
  174. GM.SQL.WORKER_LOCKED = 3
  175.  
  176. function GM.SQL:InitWriteQueue()
  177. for i = 1, self.m_intWritePoolSize do
  178. self.m_tblWriteQueue[i] = {}
  179. end
  180. end
  181.  
  182. function GM.SQL:GetWriteQueue()
  183. return self.m_tblWriteQueue
  184. end
  185.  
  186. function GM.SQL:ConnectWritePool( strHostName, strUserName, strPassword, strDatabaseName )
  187. for i = 1, self.m_intWritePoolSize do
  188. local db, err = tmysql.initialize( strHostName, strUserName, strPassword, strDatabaseName )
  189. if not db then return error( "Unable to initialize a database connection! Error: ".. err ) end
  190. self.m_tblWritePool[i] = db
  191. self:SetWorkerStatus( i, self.WORKER_IDLE )
  192. self:LogMsg( "Write worker #".. i.. " connected." )
  193. end
  194.  
  195. timer.Create( "KeepAliveWriteDB", 10, 0, function()
  196. for k, v in pairs( self.m_tblWritePool ) do
  197. if v then v:Query( "SELECT 5+5;" ) end
  198. end
  199. end )
  200. end
  201.  
  202. function GM.SQL:IsWorkerConnecting( intWorkerID )
  203. return self:GetWorkerStatus( intWorkerID ) == self.WORKER_CONNECTING
  204. end
  205.  
  206. function GM.SQL:GetWorkerStatus( intWorkerID )
  207. return self.m_tblWorkerStatus[intWorkerID]
  208. end
  209.  
  210. function GM.SQL:SetWorkerStatus( intWorkerID, intStatus )
  211. self.m_tblWorkerStatus[intWorkerID] = intStatus
  212. end
  213.  
  214. function GM.SQL:ReconnectWorker( intWorkerID )
  215. if self:GetWorkerStatus( intWorkerID ) == self.WORKER_CONNECTING then return end
  216. if not self:GetWriteWorker( intWorkerID ) then return end
  217.  
  218. self:SetWorkerStatus( intWorkerID, self.WORKER_CONNECTING )
  219.  
  220. self:GetWriteWorker( intWorkerID ):Query( "SELECT 5+5;", function( tblData )
  221. if tblData.error and tblData.error:lower() == "mysql server has gone away" then
  222. self:LogMsg( "Connection to worker #".. intWorkerID.. " database failed, retrying in ".. self.m_intConnectRetryInterval.. " seconds..." )
  223.  
  224. timer.Simple( self.m_intConnectRetryInterval +1, function()
  225. if not self:GetWriteWorker( intWorkerID ) or self:GetWorkerStatus( intWorkerID ) ~= self.WORKER_CONNECTING then return end
  226. self:SetWorkerStatus( intWorkerID, self.WORKER_LOCKED )
  227. self:ReconnectWorker( intWorkerID )
  228. end )
  229. else
  230. self:LogMsg( "Write worker #".. intWorkerID.. " reconnected." )
  231. self:SetWorkerStatus( intWorkerID, self.WORKER_IDLE )
  232.  
  233. --Check for stuff in the queue
  234. if #self.m_tblWriteQueue[intWorkerID] > 0 then --We have queued queries, run the next one
  235. self:PooledQueryWrite( intWorkerID, self.m_tblWriteQueue[intWorkerID][1][1], self.m_tblWriteQueue[intWorkerID][1][2], 1 )
  236. end
  237. end
  238. end )
  239. end
  240.  
  241. function GM.SQL:GetWriteWorker( intWorkerID )
  242. return self.m_tblWritePool[intWorkerID]
  243. end
  244.  
  245. function GM.SQL:AssignPlayerPoolID( pPlayer )
  246. local counts = {}
  247. for i = 1, self.m_intWritePoolSize do
  248. counts[i] = 0
  249. end
  250.  
  251. for k, v in pairs( self.m_tblPlayerPoolIDs ) do
  252. counts[v] = counts[v] +1
  253. end
  254.  
  255. local idealID, curCount = -1, math.huge
  256. for id, v in pairs( counts ) do
  257. if curCount > v then
  258. curCount = v
  259. idealID = id
  260. end
  261. end
  262.  
  263. if idealID == -1 then
  264. idealID = math.random( 1, self.m_intWritePoolSize )
  265. end
  266.  
  267. self:LogDev( pPlayer:Nick().. "'s worker id is now ".. idealID )
  268. self.m_tblPlayerPoolIDs[pPlayer:SteamID64()] = idealID
  269. end
  270.  
  271. function GM.SQL:GetPlayerPoolID( strSID64 )
  272. return self.m_tblPlayerPoolIDs[strSID64]
  273. end
  274.  
  275. function GM.SQL:UnrefPlayerPoolID( strSID64 )
  276. self.m_tblPlayerPoolIDs[strSID64] = nil
  277. end
  278.  
  279. function GM.SQL:PooledQueryWrite( intWorkerID, strQuery, funcCallback, intQueueIDX )
  280. local db = self:GetWriteWorker( intWorkerID )
  281. if not db then return end
  282.  
  283. self:LogDev( "GM.SQL:PooledQueryWrite", "worker = ".. intWorkerID, "query = ".. strQuery )
  284.  
  285. if db:IsConnected() then
  286. if self:GetWorkerStatus( intWorkerID ) == self.WORKER_IDLE then --We can run this now
  287. intQueueIDX = intQueueIDX or table.insert( self.m_tblWriteQueue[intWorkerID], { strQuery, funcCallback } )
  288. self:SetWorkerStatus( intWorkerID, self.WORKER_RUNNING_QUERY )
  289.  
  290. db:Query( strQuery, function( tblData )
  291. tblData = tblData[1]
  292.  
  293. if tblData.status then --Query ran OK
  294. table.remove( self.m_tblWriteQueue[intWorkerID], intQueueIDX ) --Pop this query off the queue
  295. self:SetWorkerStatus( intWorkerID, self.WORKER_IDLE )
  296.  
  297. if #self.m_tblWriteQueue[intWorkerID] > 0 then --We have queued queries, run the next one
  298. self:PooledQueryWrite( intWorkerID, self.m_tblWriteQueue[intWorkerID][1][1], self.m_tblWriteQueue[intWorkerID][1][2], 1 )
  299. end
  300.  
  301. if funcCallback then
  302. funcCallback( tblData.data, tblData )
  303. end
  304. else
  305. --We lost connection, keep this in the queue and try to start the reconnect loop
  306. if tblData.error and tblData.error:lower() == "mysql server has gone away" then
  307. self:ReconnectWorker( intWorkerID )
  308. else --We made some other kind of error, just keep running
  309. table.remove( self.m_tblWriteQueue[intWorkerID], intQueueIDX ) --Pop this query off the queue
  310. self:SetWorkerStatus( self.WORKER_IDLE )
  311.  
  312. if tblData.error then
  313. self:LogMsg( "[WARNING][ERROR!] ".. tblData.error )
  314. end
  315.  
  316. if #self.m_tblWriteQueue[intWorkerID] > 0 then --We have queued queries, run the next one
  317. self:PooledQueryWrite( intWorkerID, self.m_tblWriteQueue[intWorkerID][1][1], self.m_tblWriteQueue[intWorkerID][1][2], 1 )
  318. end
  319. end
  320. end
  321. end )
  322. else
  323. --We are already running a query, put this in the queue
  324. table.insert( self.m_tblWriteQueue[intWorkerID], { strQuery, funcCallback } )
  325. end
  326. else --We lost connection, put this in the queue and try to start the reconnect loop
  327. table.insert( self.m_tblWriteQueue[intWorkerID], { strQuery, funcCallback } )
  328. self:ReconnectWorker( intWorkerID )
  329. end
  330. end
RAW Paste Data