· 9 years ago · Jul 02, 2017, 07:20 PM
1--[[ MySQL SAVING INFORMATION ]]--
2-- Requires mysqloo from here:
3-- https://facepunch.com/showthread.php?t=1357773&p=43754012&viewfull=1#post43754012
4-- Follow the instructions at the bottom of the post for installation.
5-- Download the correct 2 files for the server's OS and place where instructed.
6-- Once MySQL is enabled, use the following console command in the server's console:
7-- JukeBox_TransferSongs
8-- to transfer all currently saves songs in the txt file to the MySQL table.
9
10JukeBox.Settings.MySQL = {}
11
12-- This dictates whether to use MySQL for the All Songs list.
13-- If set to true, all the details below will need filling in.
14JukeBox.Settings.MySQL.UseMySQL = true
15
16-- Set this to true if you're using tmysql4 rather than mysqloo.
17-- Note that this requires the use of this file:
18-- https://raw.githubusercontent.com/bkacjios/gm_tmysql4/master/mysqloo.lua
19-- Save this as lua/includes/modules/mysqloo.lua
20-- THIS ISN'T FINISHED!!!
21JukeBox.Settings.MySQL.tmysql4Override = false
22
23-- The IP of the MySQL database
24local Host = "localhost"
25
26-- The username used to access the MySQL database
27local Username = "pointshopadmin"
28
29-- The password used with the username to access the MySQL database
30local Password = "ah62$ki(foo"
31
32-- The port of the MySQL database
33local Port = 3306
34
35-- The name of the MySQL database
36local Database = "zarppointshop"
37
38--[[ END OF SETTINGS, DO NOT EDIT ]]--
39--// BEGINNING OF MYSQL \\--
40JukeBox.MySQL = {}
41JukeBox.MySQL.Connection = nil
42JukeBox.MySQL.Available = false
43JukeBox.MySQL.Transferred = false
44
45if not JukeBox.Settings.MySQL.UseMySQL then return end
46JukeBox.MySQL.Available = true
47
48if !JukeBox.Settings.MySQL.tmysql4Override then
49 require( "mysqloo" )
50end
51
52local db = mysqloo.connect( Host, Username, Password, Database, Port )
53function db:onConnected()
54
55 print( "[JukeBox] Connection Status....OK" )
56
57 JukeBox.MySQL.Connection = self
58 JukeBox.MySQL:CheckTable()
59
60end
61function db:onConnectionFailed( err )
62 print( "[JukeBox] Connection Status....FAIL \n[JukeBox] Error: "..err )
63end
64print( "[JukeBox] Queueing MySQL Connection..." )
65
66function JukeBox.MySQL:CheckTable()
67 if not self.Connection then return end
68
69 local q = self.Connection:query( "CREATE TABLE IF NOT EXISTS JukeBox_AllSongs( id varchar(11), name varchar(100), artist varchar(100), length int, starttime int, endtime int );" )
70 function q:onSuccess( data )
71 print( "[JukeBox] Table Status.........OK" )
72 JukeBox.MySQL:GetSongsData()
73 end
74 function q:onError( err, sql )
75 print( "[JukeBox] Table Status.........FAIL \n[JukeBox] Error: "..err )
76 end
77 q:start()
78end
79
80db:connect()
81
82function JukeBox.MySQL:GetSongsData()
83 if not self.Connection then return end
84
85 local q = self.Connection:query( "SELECT * FROM JukeBox_AllSongs;" )
86 function q:onSuccess( data )
87 print( "[JukeBox] Data Status..........OK" )
88 print( "[JukeBox] Received "..#data.." results from All Songs table." )
89 for k, v in pairs( data ) do
90
91 if table.Count(JukeBox.SongList) < 1000 then
92
93 if v.starttime == 0 then
94 v.starttime = nil
95 end
96 if v.endtime == v.length then
97 v.endtime = nil
98 end
99 JukeBox.SongList[v.id] = v
100 end
101 end
102 print(table.Count(JukeBox.SongList))
103 JukeBox:SendAllSongs()
104 end
105 function q:onError( err, sql )
106 print( "[JukeBox] Data Status..........FAIL \n[JukeBox] Error: "..err )
107 end
108 q:start()
109end
110
111function JukeBox.MySQL:AddSong( data )
112 if not self.Connection then return end
113
114 self:DeleteSong( data.id )
115
116 data.id = self.Connection:escape( data.id )
117 data.name = self.Connection:escape( data.name )
118 data.artist = self.Connection:escape( data.artist )
119 if not data.starttime then data.starttime = 0 end
120 if not data.endtime then data.endtime = data.length end
121
122 local q = self.Connection:query( "INSERT INTO JukeBox_AllSongs VALUES( \""..data.id.."\", \""..data.name.."\", \""..data.artist.."\", "..data.length..", "..data.starttime..", "..data.endtime.." );" )
123 function q:onSuccess( data )
124
125 end
126 function q:onError( err, sql )
127
128 end
129 q:start()
130
131end
132
133function JukeBox.MySQL:DeleteSong( id )
134 if not self.Connection then return end
135
136 id = self.Connection:escape( id )
137
138 local q = self.Connection:query( "DELETE FROM JukeBox_AllSongs WHERE id=\""..id.."\";" )
139 function q:onSuccess( data )
140
141 end
142 function q:onError( err, sql )
143
144 end
145 q:start()
146end
147
148function JukeBox.MySQL:TransferSongs()
149 if not self.Connection then
150 print( "[JukeBox] Can't transfer songs as MySQL conection isn't established." )
151 return
152 end
153
154 print( "[JukeBox] Beginning song transfer." )
155 if file.Read( "JukeBox_AllSongs.txt", "DATA" ) != "" then
156 local TableFromJSON = util.JSONToTable( file.Read( "JukeBox_AllSongs.txt", "DATA" ) )
157 for k, v in pairs( TableFromJSON ) do
158 self:AddSong( v )
159 end
160 print( "[JukeBox] Transferred "..table.Count(TableFromJSON).." songs from text file.\n[JukeBox] Songs will be loaded next level change/restart." )
161 else
162 print( "[JukeBox] File is empty, transferred 0 songs." )
163 end
164end
165concommand.Add( "JukeBox_TransferSongs", function( ply )
166 if IsValid(ply) then return end
167 if JukeBox.MySQL.Transferred then
168 print( "[JukeBox] You have already transferred the songs list, please change level and try again." )
169 return
170 end
171 JukeBox.MySQL:TransferSongs()
172 JukeBox.MySQL.Transferred = true
173end )
174
175function JukeBox.MySQL:AddTestSong()
176 if not self.Connection then return end
177
178 local q = self.Connection:query( "INSERT INTO JukeBox_AllSongs VALUES( \"12345678911\", \"Song Name\", \"Song Artist\", 123, 0, 123 );" )
179 function q:onSuccess( data )
180 print( "[JukeBox] Added a Test Song..." )
181 end
182 function q:onError( err, sql )
183 print( "[JukeBox] Error: "..err )
184 end
185 q:start()
186end