· 5 years ago · Oct 09, 2020, 05:10 PM
1local Host = "localhost"
2local Username = "root"
3local Password = ""
4local DatabaseName = "garrysmod"
5local DatabasePort = 3306
6
7--[[
8
9 DONT TOUCH ANYTHING BELOW THIS LINE!
10
11]]--
12
13require( "mysqloo" )
14
15local function ConnectToDatabase()
16 bricks_gang_db = mysqloo.connect( Host, Username, Password, DatabaseName, DatabasePort )
17 bricks_gang_db.onConnected = function() print( "[BricksGang SQL] BricksGang database has connected!" ) end
18 bricks_gang_db.onConnectionFailed = function( err ) print( "[BricksGang SQL] Connection to BricksGang Database failed! Error: " ) PrintTable( err ) end
19 bricks_gang_db:connect()
20
21 local gangTableQuery = bricks_gang_db:query( [[ CREATE TABLE IF NOT EXISTS bricks_server_gangs (
22 gangID int NOT NULL UNIQUE,
23 gangName varchar(30),
24 gangIcon varchar(200),
25 owner varchar(20),
26 members TEXT,
27 money int,
28 level int,
29 experience bigint,
30 storage TEXT,
31 roles TEXT,
32 upgrades TEXT,
33 achievements TEXT
34 ); ]] )
35 function gangTableQuery:onSuccess(data) print( "[BricksGang SQL] bricks_server_gangs table validated!" ) end
36 function gangTableQuery:onError(err) print("[BricksGang SQL] An error occured while executing the query: " .. err) end
37 gangTableQuery:start()
38
39 local inboxTableQuery = bricks_gang_db:query( [[ CREATE TABLE IF NOT EXISTS bricks_server_inboxes (
40 receiverSteamID varchar(20),
41 receiverGangID int,
42 inboxKey int NOT NULL,
43 time int,
44 type TEXT,
45 reqInfo TEXT
46 ); ]] )
47 function inboxTableQuery:onSuccess(data) print( "[BricksGang SQL] bricks_server_inboxes table validated!" ) end
48 function inboxTableQuery:onError(err) print("[BricksGang SQL] An error occured while executing the query: " .. err) end
49 inboxTableQuery:start()
50
51 local associationTableQuery = bricks_gang_db:query( [[ CREATE TABLE IF NOT EXISTS bricks_server_associations (
52 gang1ID int NOT NULL,
53 gang2ID int NOT NULL,
54 associationType TEXT
55 ); ]] )
56 function associationTableQuery:onSuccess(data) print( "[BricksGang SQL] bricks_server_associations table validated!" ) end
57 function associationTableQuery:onError(err) print("[BricksGang SQL] An error occured while executing the query: " .. err) end
58 associationTableQuery:start()
59
60 local printerTableQuery = bricks_gang_db:query( [[ CREATE TABLE IF NOT EXISTS bricks_server_gangprinters (
61 gangID int NOT NULL,
62 printerID int NOT NULL,
63 servers TEXT,
64 upgrades TEXT
65 ); ]] )
66 function printerTableQuery:onSuccess(data) print( "[BricksGang SQL] bricks_server_gangprinters table validated!" ) end
67 function printerTableQuery:onError(err) print("[BricksGang SQL] An error occured while executing the query: " .. err) end
68 printerTableQuery:start()
69end
70ConnectToDatabase()
71
72-- GANGS --
73function BRICKS_SERVER.Func.InsertGangDB( gangID, gangName, gangIcon, owner, members, roles, func )
74 local query = bricks_gang_db:query( [[ INSERT INTO bricks_server_gangs (gangID, gangName, gangIcon, owner, members, roles)
75 VALUES( ']] .. gangID .. [[', ']] .. bricks_gang_db:escape( gangName ) .. [[', ']] .. bricks_gang_db:escape( gangIcon ) .. [[', ']] .. bricks_gang_db:escape( owner ) .. [[', ']] .. bricks_gang_db:escape( util.TableToJSON( members ) ) .. [[', ']] .. bricks_gang_db:escape( util.TableToJSON( roles ) ) .. [['
76 ); ]] )
77 if( func ) then
78 function query:onSuccess( err )
79 func()
80 end
81 end
82 function query:onError( err ) print( "[BricksGang SQL] An error occured while executing the query: " .. err ) end
83 query:start()
84end
85
86function BRICKS_SERVER.Func.UpdateGangDB( gangID, key, value )
87 local query = bricks_gang_db:query( "UPDATE bricks_server_gangs SET " .. key .. " = '" .. ((isstring( value ) and bricks_gang_db:escape( value )) or value) .. "' WHERE gangID = '" .. gangID .. "';" )
88 function query:onError( err ) print( "[BricksGang SQL] An error occured while executing the query: " .. err ) end
89 query:start()
90end
91
92function BRICKS_SERVER.Func.FetchGangsDB( func )
93 local query = bricks_gang_db:query( "SELECT * FROM bricks_server_gangs" )
94 function query:onSuccess( data ) func( data or {} ) end
95 function query:onError( err ) print( "[BricksGang SQL] An error occured while executing the query: " .. err ) end
96 query:start()
97end
98
99-- INBOX --
100function BRICKS_SERVER.Func.InsertInboxDB( receiverSteamID, receiverGangID, inboxKey, time, type, reqInfo )
101 local query = bricks_gang_db:query( [[ INSERT INTO bricks_server_inboxes (]] .. ((receiverSteamID and "receiverSteamID") or "receiverGangID") .. [[, inboxKey, time, type, reqInfo)
102 VALUES( ']] .. ((receiverSteamID and bricks_gang_db:escape( receiverSteamID )) or receiverGangID) .. [[', ']] .. inboxKey .. [[', ']] .. time .. [[', ']] .. bricks_gang_db:escape( type ) .. [[', ']] .. bricks_gang_db:escape( util.TableToJSON( reqInfo ) ) .. [['
103 ); ]] )
104 function query:onError( err ) print( "[BricksGang SQL] An error occured while executing the query: " .. err ) end
105 query:start()
106end
107
108function BRICKS_SERVER.Func.DeleteInboxDB( receiverSteamID, receiverGangID, inboxKey )
109 local query = bricks_gang_db:query( "DELETE FROM bricks_server_inboxes WHERE " .. ((receiverSteamID and "receiverSteamID") or "receiverGangID") .. " = '" .. ((receiverSteamID and bricks_gang_db:escape( receiverSteamID )) or receiverGangID) .. "' AND inboxKey = '" .. inboxKey .. "';" )
110 function query:onError( err ) print( "[BricksGang SQL] An error occured while executing the query: " .. err ) end
111 query:start()
112end
113
114function BRICKS_SERVER.Func.FetchInboxDB( func )
115 local query = bricks_gang_db:query( "SELECT * FROM bricks_server_inboxes" )
116 function query:onSuccess( data ) func( data or {} ) end
117 function query:onError( err ) print( "[BricksGang SQL] An error occured while executing the query: " .. err ) end
118 query:start()
119end
120
121-- ASSOCIATIONS --
122function BRICKS_SERVER.Func.InsertAssociationDB( gang1ID, gang2ID, associationType )
123 local query = bricks_gang_db:query( [[ INSERT INTO bricks_server_associations (gang1ID, gang2ID, associationType)
124 VALUES( ']] .. gang1ID .. [[', ']] .. gang2ID .. [[', ']] .. bricks_gang_db:escape( associationType ) .. [['
125 ); ]] )
126 function query:onError( err ) print( "[BricksGang SQL] An error occured while executing the query: " .. err ) end
127 query:start()
128end
129
130function BRICKS_SERVER.Func.UpdateAssociationDB( gang1ID, gang2ID, associationType )
131 local query = bricks_gang_db:query( "UPDATE bricks_server_associations SET associationType = '" .. bricks_gang_db:escape( associationType ) .. "' WHERE gang1ID = '" .. gang1ID .. "' AND gang2ID = '" .. gang2ID .. "';" )
132 function query:onError( err ) print( "[BricksGang SQL] An error occured while executing the query: " .. err ) end
133 query:start()
134end
135
136function BRICKS_SERVER.Func.DeleteAssociationDB( gang1ID, gang2ID )
137 local query = bricks_gang_db:query( "DELETE FROM bricks_server_associations WHERE gang1ID = '" .. gang1ID .. "' AND gang2ID = '" .. gang2ID .. "';" )
138 function query:onError( err ) print( "[BricksGang SQL] An error occured while executing the query: " .. err ) end
139 query:start()
140end
141
142function BRICKS_SERVER.Func.FetchAssociationsDB( func )
143 local query = bricks_gang_db:query( "SELECT * FROM bricks_server_associations" )
144 function query:onSuccess( data ) func( data or {} ) end
145 function query:onError( err ) print( "[BricksGang SQL] An error occured while executing the query: " .. err ) end
146 query:start()
147end
148
149-- PRINTERS --
150function BRICKS_SERVER.Func.InsertGangPrinterDB( gangID, printerID, servers, upgrades )
151 local query = bricks_gang_db:query( [[ INSERT INTO bricks_server_gangprinters (gangID, printerID, servers, upgrades)
152 VALUES( ]] .. gangID .. [[, ]] .. printerID .. [[, ]] .. sql.SQLStr( util.TableToJSON( servers or {} ) ) .. [[, ]] .. sql.SQLStr( util.TableToJSON( upgrades or {} ) ) .. [[
153 ); ]])
154 function query:onError( err ) print( "[BricksGang SQL] An error occured while executing the query: " .. err ) end
155 query:start()
156end
157
158function BRICKS_SERVER.Func.UpdateGangPrinterDB( gangID, printerID, servers, upgrades )
159 local query = bricks_gang_db:query( "UPDATE bricks_server_gangprinters SET " .. ((servers and "servers") or "upgrades") .. " = " .. sql.SQLStr( util.TableToJSON( servers or upgrades ) ) .. " WHERE gangID = '" .. gangID .. "' AND printerID = '" .. printerID .. "';" )
160 function query:onError( err ) print( "[BricksGang SQL] An error occured while executing the query: " .. err ) end
161 query:start()
162end
163
164function BRICKS_SERVER.Func.DeleteGangPrinterDB( gangID, printerID )
165 local query = bricks_gang_db:query( "DELETE FROM bricks_server_gangprinters WHERE gangID = '" .. gangID .. "' AND printerID = '" .. printerID .. "';" )
166 function query:onError( err ) print( "[BricksGang SQL] An error occured while executing the query: " .. err ) end
167 query:start()
168end
169
170function BRICKS_SERVER.Func.FetchGangPrinterDB( func )
171 local query = bricks_gang_db:query( "SELECT * FROM bricks_server_gangprinters" )
172 function query:onSuccess( data ) func( data or {} ) end
173 function query:onError( err ) print( "[BricksGang SQL] An error occured while executing the query: " .. err ) end
174 query:start()
175end
176
177
178-- CLEAR GANG FROM DATABASE --
179function BRICKS_SERVER.Func.ClearGangFromDB( gangID )
180 local queryGang = bricks_gang_db:query( "DELETE FROM bricks_server_gangs WHERE gangID = '" .. gangID .. "';" )
181 function queryGang:onError( err ) print( "[BricksGang SQL] An error occured while executing the query: " .. err ) end
182 queryGang:start()
183
184 local queryInbox = bricks_gang_db:query( "DELETE FROM bricks_server_inboxes WHERE receiverGangID = '" .. gangID .. "';" )
185 function queryInbox:onError( err ) print( "[BricksGang SQL] An error occured while executing the query: " .. err ) end
186 queryInbox:start()
187
188 local queryAssociation = bricks_gang_db:query( "DELETE FROM bricks_server_associations WHERE gang1ID = '" .. gangID .. "' OR gang2ID = '" .. gangID .. "';" )
189 function queryAssociation:onError( err ) print( "[BricksGang SQL] An error occured while executing the query: " .. err ) end
190 queryAssociation:start()
191
192 local queryPrinters = bricks_gang_db:query( "DELETE FROM bricks_server_gangprinters WHERE gangID = '" .. gangID .. "';" )
193 function queryPrinters:onError( err ) print( "[BricksGang SQL] An error occured while executing the query: " .. err ) end
194 queryPrinters:start()
195end