· 8 years ago · Aug 16, 2018, 01:16 AM
1
2-- libmysql goes in: "\Steam\steamapps\common\GarrysMod"
3-- mysqloo goes in: "\Steam\steamapps\common\GarrysMod\garrysmod\lua\bin"
4-- if the bin folder does not exist create it yourself.
5--Refer to here for more help: https://support.serenityservers.net/article/36/how-to-install-mysqloo-or-tmysql
6
7local DATABASE_HOST = "144.217.11.134"
8local DATABASE_PORT = 3306
9local DATABASE_NAME = "s2774_db_2"
10local DATABASE_USERNAME = "s2774_Ce3hVQQRAJ"
11local DATABASE_PASSWORD = "KWTLfAjWGMNVyLhQ3aOr"
12
13require("mysqloo")
14
15local db = mysqloo.connect(DATABASE_HOST, DATABASE_USERNAME, DATABASE_PASSWORD, DATABASE_NAME, DATABASE_PORT)
16
17local function dbprint(message, color)
18 color = color or Color(0,255,255)
19 MsgC( color, "[DINVENTORY SQL] " .. message .. "\n")
20
21end
22
23local function DInventoryQuery(query, callback, showPrint)
24 showPrint = showPrint or true
25 local newQuery = db:query(query)
26 newQuery.onSuccess = function(q,d)
27 if showPrint then
28 dbprint("SUCESSFUL QUERY: " .. query)
29 end
30 if callback then
31 callback(q,d)
32 end
33 end
34 if showPrint then
35 newQuery.onError = function(db, error)
36 dbprint("Failed QUERY: " .. query .. " | " .. error, Color(255,10,10))
37 end
38 end
39 return newQuery
40
41end
42
43
44
45local function ConnectToDatabase()
46 dbprint("Attemping to connect to the database.")
47 db.onConnected = function()
48 dbprint("Connected to the database successfully.")
49 end
50 db.onConnectionFailed = function(db, msg)
51 dbprint("Failed to connect to the database.")
52 dbprint(msg)
53 end
54 db:connect()
55
56 local playerQuery = DInventoryQuery("CREATE TABLE IF NOT EXISTS dinventory_items ( itemid INT NOT NULL AUTO_INCREMENT PRIMARY KEY, classname VARCHAR(45) UNIQUE);")
57
58 local itemsQuery = DInventoryQuery("CREATE TABLE IF NOT EXISTS dinventory_player ( playerid INT NOT NULL AUTO_INCREMENT PRIMARY KEY, steamid VARCHAR(22) UNIQUE);")
59
60 local inventoryQuery = DInventoryQuery("CREATE TABLE IF NOT EXISTS dinventory_player_inventory ( playerid INT NOT NULL AUTO_INCREMENT, slot INT NOT NULL , itemid INT, amount INT, PRIMARY KEY (playerid, slot));")
61
62 local hotbarQuery = DInventoryQuery("CREATE TABLE IF NOT EXISTS dinventory_player_hotbar ( playerid INT NOT NULL AUTO_INCREMENT, slot INT, itemid INT, PRIMARY KEY (playerid, slot));")
63 playerQuery:start()
64 itemsQuery:start()
65 inventoryQuery:start()
66 hotbarQuery:start()
67
68
69end
70
71ConnectToDatabase()
72
73local function CheckConnection()
74
75 if db:status() != mysqloo.DATABASE_CONNECTED then
76 dbprint("Database connection invalid reconnecting.", true)
77 ConnectToDatabase()
78 end
79
80end
81
82function DINVENTORY.InitPlayer(ply)
83 if not ply:IsPlayer() then return end
84 local initQuery = DInventoryQuery("SELECT * FROM dinventory_player WHERE steamid = '" .. ply:SteamID() .. "';", function(q,d)
85 if #d == 0 then
86 local insertQuery = DInventoryQuery("INSERT INTO dinventory_player (steamid) VALUES('" .. ply:SteamID() .. "');", function(q,d)
87 ply.DINVENTORY_DB_ID = q:lastInsert()
88 end)
89 insertQuery:start()
90 else
91 ply.DINVENTORY_DB_ID = d[1].playerid
92 end
93 end)
94 initQuery:start()
95
96end
97
98function DINVENTORY.InitItemData()
99
100 for k,v in pairs(DINVENTORY.RegisteredItems) do
101 local initQuery = DInventoryQuery("SELECT * FROM dinventory_items WHERE classname = '" .. k .. "';", function(q,d)
102 if #d == 0 then
103 local insertQuery = DInventoryQuery("INSERT INTO dinventory_items (classname) VALUES('" .. k .. "');", function(q,d)
104 local id = q:lastInsert()
105 DINVENTORY.RegisteredItems[k].DINVENTORY_DB_ID = id
106 DINVENTORY.RegisteredItemsID[id] = DINVENTORY.RegisteredItems[k]
107 end)
108 insertQuery:start()
109 else
110 DINVENTORY.RegisteredItems[k].DINVENTORY_DB_ID = d[1].itemid
111 DINVENTORY.RegisteredItemsID[d[1].itemid] = DINVENTORY.RegisteredItems[k]
112 end
113
114 end)
115 initQuery:start()
116 end
117end
118
119function DINVENTORY.SaveData(ply)
120 if not ply.DINVENTORY_DB_ID then return end
121 timer.Create("DINVENTORY_ANTISPAM", .5, 1, function()
122 local REG = DINVENTORY.RegisteredItems
123 local getInventoryQuery = DInventoryQuery("SELECT * FROM dinventory_player_inventory WHERE playerid = " .. ply.DINVENTORY_DB_ID .. ";", function(q,d)
124 for _, data in pairs(d) do
125 for k,v in pairs(ply.inventory.backpack) do
126 if data.slot == k then
127 if data.amount != v.amount then
128 local deleteQuery = DInventoryQuery("DELETE FROM dinventory_player_inventory WHERE slot = " .. k .. " AND playerid = " .. ply.DINVENTORY_DB_ID .. ";")
129 deleteQuery:start()
130 end
131 end
132 end
133 end
134 end)
135 getInventoryQuery:start()
136 local getHotbarQuery = DInventoryQuery("SELECT * FROM dinventory_player_hotbar WHERE playerid = " .. ply.DINVENTORY_DB_ID .. ";", function(q,d)
137
138 for _, data in pairs(d) do
139 for k,v in pairs(ply.inventory.hotbar) do
140 if not REG[v] or REG[v] and data.slot == k and data.itemid != REG[v].DINVENTORY_DB_ID then
141 local deleteQuery = DInventoryQuery("DELETE FROM dinventory_player_hotbar WHERE slot = " .. k .. " AND playerid = " .. ply.DINVENTORY_DB_ID .. ";")
142 deleteQuery:start()
143 end
144 end
145 end
146
147 end)
148 getHotbarQuery:start()
149 for k,v in pairs(ply.inventory.backpack) do
150 if v.amount and v.amount > 0 then
151 local updateQuery = DInventoryQuery("REPLACE INTO dinventory_player_inventory (playerid, slot, itemid, amount) VALUES(" .. ply.DINVENTORY_DB_ID .. ", " .. k .. ", '" .. REG[v.classname].DINVENTORY_DB_ID .. "', " .. v.amount .. ");")
152 updateQuery:start()
153 end
154 end
155 for k,v in pairs(ply.inventory.hotbar) do
156 if v != "" then
157 local updateQuery = DInventoryQuery("REPLACE INTO dinventory_player_hotbar (playerid, slot, itemid) VALUES(" .. ply.DINVENTORY_DB_ID .. ", " .. k .. ", '" .. REG[v].DINVENTORY_DB_ID .. "');")
158 updateQuery:start()
159 end
160 end
161 end)
162
163
164end
165
166function DINVENTORY.GiveItems(ply)
167 if not ply.DINVENTORY_DB_ID then return end
168 local REG = DINVENTORY.RegisteredItemsID
169 local inventoryQuery = DInventoryQuery("SELECT * FROM dinventory_player_inventory WHERE playerid = '" .. ply.DINVENTORY_DB_ID .. "';")
170 inventoryQuery.onSuccess = function(q, d)
171 for k,v in pairs(d) do
172 if REG[v.itemid] then
173 ply.inventory.backpack[v.slot] = {classname = REG[v.itemid].classname, amount = v.amount}
174 else
175 local deleteQuery = DInventoryQuery("DELETE FROM dinventory_player_inventory WHERE slot = " .. v.slot .. " AND playerid = '" .. ply.DINVENTORY_DB_ID .. "';")
176 deleteQuery:start()
177 end
178 end
179 ply:SyncInventory()
180 end
181 inventoryQuery:start()
182 local hotbarQuery = DInventoryQuery("SELECT * FROM dinventory_player_hotbar WHERE playerid = '" .. ply.DINVENTORY_DB_ID .. "';")
183 hotbarQuery.onSuccess = function(q,d)
184 for k,v in pairs(d) do
185 if REG[v.itemid] then
186 ply.inventory.hotbar[v.slot or #ply.inventory.hotbar + 1 or 1] = REG[v.itemid].classname
187 DINVENTORY.Give(ply, REG[v.itemid].classname)
188 else
189 local deleteQuery = DInventoryQuery("DELETE FROM dinventory_player_hotbar WHERE slot = " .. v.slot .. " AND playerid = '" .. ply.DINVENTORY_DB_ID .. "';")
190 deleteQuery:start()
191 end
192 end
193 ply:SyncInventory()
194 end
195 hotbarQuery:start()
196
197
198end
199
200
201net.Receive("inventory_create", function(len,ply)
202 DINVENTORY.InitPlayer(ply)
203 ply:CreateInventory()
204 DINVENTORY.GiveInventory(ply)
205end)
206
207timer.Create("DINVENTORY_CHECKCONNECTION", 5, 0, function()
208
209 CheckConnection()
210
211end)