· 9 years ago · Apr 14, 2017, 08:06 PM
1#> __ ____ __ ____ ____ _ _ __ ____ _ _ ____ __
2#> /__\ ( ___( ) __( _ (_ _( \/ /__\ ( _ ( \/ ( ___( )
3#> /(__)\ )__) )(_(___)(_) _)(_ \ /(__)\ ) /\ / )__) )(__
4#> (__)(__(____(____) (____(____) \(__)(__(_)\_) \/ (____(____)
5# ______ _ _______ _
6# | ___ \ | | (_______) _ (_)
7# | | _ | |_ _ ___ ____| | _____ ___ ____ ____| |_ _ ___ ____ ___
8# | || || | | | |/___)/ _ | | | ___/ _ \| _ \ / ___| _)| |/ _ \| _ \ /___)
9# | || || | |_| |___ | | | | | | | | |_| | | | ( (___| |__| | |_| | | | |___ |
10# |_||_||_|\__ (___/ \_|| |_| |_| \___/|_| |_|\____)\___|_|\___/|_| |_(___/
11# (____/ |_|
12# %
13#Script de MYSQL system pour les BDD skript
14#Création AelDivarvel le 04/03/2016
15#Copyright: Eternal
16#Modificaton: Proutassiers
17#Pre-Requis: skript2.2,Skquery
18#A faire:
19#Update: Englobement de toutes les BDD pour skript le 17/04/2016
20# Aide: Le 'playername' peut être définit sur 0, le 'type' doit être le type de la variable pour être parsé ensuite (array, text ou location)
21
22#~ #-------------------------------------------------------------------------------------------------------------------
23#~ # ######################################
24#~ # # '[EXEMPLES/DOC]' #
25#~ # ######################################
26#~ #-------------------------------------------------------------------------------------------------------------------
27#~ set {_table} to "variables"
28#~ set {_base} to "Hub_Economy"
29#~ set {_varname} to "teste"
30#~ set {_playername} to "0"
31#~ set {_type} to "array"
32#~ set {_name} to name of player
33#~ '/!\ ATTENTION: Le type peut soit être "array" ou bien "location" ou "text".'
34#~ 'Le reste DEVRA être enregistré sous forme texte brut. (ex: les items)'
35#~ '---------------------------------------------------------------------------------------------------------'
36#~ ---------------------------------------------
37#~ [ Pour envoyer un array ]
38#~ ---------------------------------------------
39#~ add "test1" to {_array::*}
40#~ add "test2" to {_array::*}
41#~ add "test3" to {_array::*}
42#~ add "test4" to {_array::*}
43#~ EnvoiSQL("variables", "Hub_Economy", "teste", "0", "text", {_array::*}) <- TEXT comme type pour (re)redéfinir un array
44#~ ---------------------------------------------
45#~ [ Pour rajouter une variable dans un array ]
46#~ ---------------------------------------------
47#~ set {_variable_simple} to "blah"
48#~ EnvoiSQL("variables", "Hub_Economy", "teste", "0", "array", {_variable_simple}) <- ARRAY comme type pour rajouter à un array
49#~ ---------------------------------------------
50#~ [ Pour envoyer une variable simple ]
51#~ ---------------------------------------------
52#~ set {_variable_simple} to "blah"
53#~ EnvoiSQL("variables", "Hub_Economy", "teste", "0", "text", {_variable_simple})
54#~ ---------------------------------------------
55#~ [ Pour retirer un texte d'un array ]
56#~ ---------------------------------------------
57#~ RemoveSQLarray("variables","Hub_Shoplist","Nom_Variable","Texte à virer du array",0) <- 1 si tu veut virer tout, 0 si tu veut en virer qu'un
58#~ ---------------------------------------------
59#~ [ Pour envoyer une location ]
60#~ ---------------------------------------------
61#~ set {_location} to "%location of player%"
62#~ EnvoiSQL("variables", "Hub_Economy", "teste", "0", "location", {_location})
63#~ ---------------------------------------------
64#~ [ Pour DELETE une variable ]
65#~ ---------------------------------------------
66#~ RemoveSQL({_table}, {_base}, {_varname})
67#~ ---------------------------------------------
68#~ [ Pour récupérer une variable ]
69#~ ---------------------------------------------
70#~ '/!\ ATTENTION: Le GetSQL va toujours mettre tout dans un array, que ça soit une location ou un texte simple'
71#~ 'A toi de le parser comme tu le désire ensuite, le X Y Z de la location sera dans les index 1 2 et 3 par exemple.'
72#~ 'Ne pas hésiter à enregistrer un item sous la forme "1xGold_Block" par exemple, puis le "split" ou faire des "replace all ".." with "" in {_var}" '
73#~ '---------------------------------------------------------------------------------------------------------'
74#~ set {_getResult::*} to GetSQL({_table},{_base},{_varname})
75#~ broadcast "%{_getResult::*}%"
76
77
78#~ #-------------------------------------------------------------------------------------------------------------------
79#~ # ######################################
80#~ # # '[DEBUT Options]' #
81#~ # ######################################
82#~ #-------------------------------------------------------------------------------------------------------------------
83
84script options:
85 $ init com.mysql.jdbc.Driver
86 $ db url jdbc:mysql://localhost:3306/?autoReconnect=true
87 $ db username ******
88 $ db password **************
89
90#~ #-------------------------------------------------------------------------------------------------------------------
91#~ # ######################################
92#~ # # '[Création tables]' #
93#~ # ######################################
94#~ #-------------------------------------------------------------------------------------------------------------------
95
96on load:
97 update "CREATE TABLE IF NOT EXISTS Game_rewards.variables (`Id` int not null auto_increment primary key,`varname` varchar(255),`Playername` varchar(255),`type` varchar(255),`value` varchar(1000))"
98 update "CREATE TABLE IF NOT EXISTS Hub_chat.variables (`Id` int not null auto_increment primary key,`varname` varchar(255),`Playername` varchar(255),`type` varchar(255),`value` varchar(1000))"
99 update "CREATE TABLE IF NOT EXISTS Hub_stats_players.variables (`Id` int not null auto_increment primary key,`varname` varchar(255),`Playername` varchar(255),`type` varchar(255),`value` varchar(1000))"
100 update "CREATE TABLE IF NOT EXISTS Hub_Player.variables (`Id` int not null auto_increment primary key,`varname` varchar(255),`Playername` varchar(255),`type` varchar(255),`value` varchar(1000))"
101 update "CREATE TABLE IF NOT EXISTS Hub_Economy.variables (`Id` int not null auto_increment primary key,`varname` varchar(255),`Playername` varchar(255),`type` varchar(255),`value` varchar(1000))"
102 update "CREATE TABLE IF NOT EXISTS Hub_Shoplist.variables (`Id` int not null auto_increment primary key,`varname` varchar(255),`Playername` varchar(255),`type` varchar(255),`value` varchar(1000))"
103
104
105#~ #-------------------------------------------------------------------------------------------------------------------
106#~ # #############################################
107#~ # # '[Fonction EnvoiSQL (INSERT/UPDATE)]' #-> EnvoiSQL({_table}, {_base}, {_varname}, {_playername}, {_type}, {_value})
108#~ # #############################################
109#~ #-------------------------------------------------------------------------------------------------------------------
110
111function EnvoiSQL(table:text, base:text, varname:text, playername:text, type:text, value:texts):
112 set {_type2} to "%{_type}%"
113
114 loop {_value::*}:
115 if {_valueParsed} is set:
116 set {_valueParsed} to "%{_valueParsed}%].::.[%loop-value%"
117 set {_type2} to "array"
118 else:
119 set {_valueParsed} to "%loop-value%"
120 replace all "'" with "''" in {_valueParsed}
121 if {_type} is "location":
122 replace all "x: " with "" in {_valueParsed}
123 replace all ", y: " with "/" in {_valueParsed}
124 replace all ", z: " with "/" in {_valueParsed}
125 set {_SetResult::*} to objects in column "value" from result of query "SELECT * FROM %{_base}%.%{_table}% WHERE varname = '%{_varname}%'"
126 set {_SetResult2::*} to objects in column "varname" from result of query "SELECT * FROM %{_base}%.%{_table}% WHERE varname = '%{_varname}%'"
127
128 if {_type} is "array":
129 if {_SetResult::*} is set:
130 set {_Old_Value} to {_SetResult::1}
131 set {_New_Value} to "%{_Old_Value}%].::.[%{_valueParsed}%"
132 set {_valueParsed} to {_New_Value}
133 else:
134 set {_New_Value} to "%{_valueParsed}%"
135 set {_valueParsed} to {_New_Value}
136 set {_GetResult} to false
137 loop {_SetResult2::*}:
138 If loop-value is {_varname}:
139 set {_GetResult} to true
140 If {_GetResult} is false:
141 update "INSERT INTO %{_base}%.%{_table}% (`varname`,`Playername`,`type`,`value`) VALUES ('%{_varname}%', '%{_playername}%', '%{_type2}%', '%{_valueParsed}%')"
142 else:
143 update "UPDATE %{_base}%.%{_table}% SET `value` = '%{_valueParsed}%' WHERE `%{_table}%`.`varname` ='%{_varname}%'"
144
145#~ #-------------------------------------------------------------------------------------------------------------------
146#~ # #############################################
147#~ # # '[Fonction RemoveSQL (DELETE)]' #-> RemoveSQL({_table}, {_base}, {_varname})
148#~ # #############################################
149#~ #-------------------------------------------------------------------------------------------------------------------
150
151function RemoveSQL(table:text, base:text, varname:text):
152 set {_SetResult::*} to objects in column "varname" from result of query "SELECT * FROM %{_base}%.%{_table}% WHERE varname = '%{_varname}%'"
153 set {_GetResult} to false
154 loop {_SetResult::*}:
155 If loop-value is {_varname}:
156 set {_GetResult} to true
157 If {_GetResult} is true:
158 update "DELETE FROM %{_base}%.%{_table}% WHERE varname = '%{_varname}%'"
159 else:
160 loop all players:
161 if loop-player has permission "MYSQL.ADMIN":
162 send "Impossible, table/base/donnée introuvable. (Table: %{_table}%, BDD: %{_base}%, Varname: %{_varname}%" to loop-player
163
164#~ #-------------------------------------------------------------------------------------------------------------------
165#~ # #############################################
166#~ # # '[Fonction RemoveSQLarray (valeur array)]'#-> RemoveSQL({_table}, {_base}, {_varname}, {_Num_0_ou_1)
167#~ # #############################################
168#~ #-------------------------------------------------------------------------------------------------------------------
169
170function RemoveSQLarray(table:text, base:text, varname:text, value:text, all:number):
171 set {_SetResult::*} to objects in column "value" from result of query "SELECT * FROM %{_base}%.%{_table}% WHERE varname = '%{_varname}%'"
172 set {_SetResult2::*} to objects in column "varname" from result of query "SELECT * FROM %{_base}%.%{_table}% WHERE varname = '%{_varname}%'"
173 set {_GetResult} to false
174 loop {_SetResult2::*}:
175 If loop-value is {_varname}:
176 set {_GetResult} to true
177 set {_Value_Old} to {_SetResult::1}
178 If {_GetResult} is true:
179 if {_all} is equal to 1:
180 replace all "%{_value}%].::.[" with "" in {_Value_Old}
181 replace all "%{_value}%" with "" in {_Value_Old}
182
183 update "UPDATE %{_base}%.%{_table}% SET `value` = '%{_Value_Old}%' WHERE `%{_table}%`.`varname` ='%{_varname}%'"
184 else:
185 set {_GetResultParsed::*} to {_Value_Old} split at "].::.["
186 remove {_value} from {_GetResultParsed::*}
187 loop {_GetResultParsed::*}:
188 if {_New_Value} is not set:
189 set {_New_Value} to loop-value
190 else:
191 set {_New_Value} to "%{_New_Value}%].::.[%loop-value%"
192 update "UPDATE %{_base}%.%{_table}% SET `value` = '%{_New_Value}%' WHERE `%{_table}%`.`varname` ='%{_varname}%'"
193 else:
194 loop all players:
195 if loop-player has permission "MYSQL.ADMIN":
196 send "Impossible, table/base/donnée introuvable. (Table: %{_table}%, BDD: %{_base}%, Varname: %{_varname}%" to loop-player
197#~ #-------------------------------------------------------------------------------------------------------------------
198#~ # #############################################
199#~ # # '[Fonction GetSQL (SELECT)]' #-> set {_getResult::*} to GetSQL({_table},{_base},{_varname})
200#~ # #############################################
201#~ #-------------------------------------------------------------------------------------------------------------------
202
203function GetSQL(table:text, base:text, varname:text) :: texts:
204 set {_TypeResult::*} to objects in column "type" from result of query "SELECT * FROM %{_base}%.%{_table}% WHERE varname = '%{_varname}%'"
205 set {_SetResult::*} to objects in column "value" from result of query "SELECT * FROM %{_base}%.%{_table}% WHERE varname = '%{_varname}%'"
206 delete {_TypeResultGet}
207 loop {_TypeResult::*}:
208 If loop-value is set:
209 set {_TypeResultGet} to loop-value
210
211 If {_TypeResultGet} is set:
212 if {_TypeResultGet} is "text":
213 delete {_GetResult}
214 loop {_SetResult::*}:
215 If loop-value is set:
216 set {_GetResult} to loop-value
217 set {_GetResult} to {_GetResult} parsed as text
218 if {_GetResult} is set:
219 return {_GetResult}
220 stop trigger
221 else:
222 return {_GetResult}
223 stop trigger
224
225 else if {_TypeResultGet} is "array":
226 delete {_GetResult}
227 loop {_SetResult::*}:
228 If loop-value is set:
229 set {_GetResult} to loop-value
230 if {_GetResult} is not set:
231 return {_GetResult}
232 stop trigger
233 set {_GetResultParsed::*} to {_GetResult} split at "].::.["
234 if {_GetResultParsed::*} is not set:
235 add {_GetResult} to {_GetResultParsed::*}
236 return {_GetResultParsed::*}
237 stop trigger
238
239 else if {_TypeResultGet} is "location":
240 delete {_GetResult}
241 loop {_SetResult::*}:
242 If loop-value is set:
243 set {_GetResult} to loop-value
244 if {_GetResult} is not set:
245 return {_GetResult}
246 stop trigger
247 set {_GetResultParsed::*} to {_GetResult} split at "/"
248 if {_GetResultParsed::*} is not set:
249 add {_GetResult} to {_GetResultParsed::*}
250 return {_GetResultParsed::*}
251 stop trigger
252
253 delete {_GetResult}
254 loop {_SetResult::*}:
255 If loop-value is set:
256 set {_GetResult} to loop-value
257 return {_GetResult}