· 8 years ago · Dec 15, 2017, 03:18 PM
1/**
2 * Initialise the alias table
3 * @param db The database
4 */
5stock Alias_Init(DB:db)
6{
7 if(db) {
8 db_free_result(db_query(db, "CREATE TABLE IF NOT EXISTS `Aliases` ( `name` TEXT NOT NULL COLLATE NOCASE , `ip` TEXT NOT NULL , `datetime` TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP , `onlinetime` INTEGER NOT NULL DEFAULT 0 , PRIMARY KEY ( `name` , `ip` ) )"));
9 }
10}
11
12/**
13 * Add or update an alias
14 * @param db The database
15 * @param playername The player name
16 * @param playerip The player IP
17 */
18stock bool:Alias_Add(DB:db, const playername[], const playerip[])
19{
20 if(db && !isnull(playername) && !isnull(playerip)) {
21 new string[100 + MAX_PLAYER_NAME + MAX_IP];
22 format(string, sizeof(string), "SELECT ROWID FROM `Aliases` WHERE ( `name` = '%s' AND `ip` = '%s' )", playername, playerip);
23 new DBResult:result = db_query(db, string);
24 if(db_num_rows(result)) {
25 db_get_field(result, 0, string, sizeof(string));
26 db_free_result(result);
27 format(string, sizeof(string), "UPDATE `Aliases` SET `datetime` = CURRENT_TIMESTAMP WHERE ( ROWID = %s )", string);
28 db_free_result(db_query(db, string));
29 } else {
30 format(string, sizeof(string), "INSERT INTO `Aliases` ( `name` , `ip` ) VALUES ( '%s' , '%s' )", playername, playerip);
31 db_free_result(db_query(db, string));
32 }
33 return true;
34 }
35 return false;
36}
37
38/**
39 * Update an alias with the time online
40 * @param db The database
41 * @param playername The player name
42 * @param playerip The player IP
43 * @param seconds The time online in seconds
44 */
45stock bool:Alias_Update(DB:db, const playername[], const playerip[], seconds)
46{
47 if(db && !isnull(playername) && !isnull(playerip) && seconds > 0) {
48 new string[160 + MAX_PLAYER_NAME + MAX_IP];
49 format(string, sizeof(string), "UPDATE `Aliases` SET `datetime` = CURRENT_TIMESTAMP , `onlinetime` = `onlinetime` + %i WHERE ( `name` = '%s' AND `ip` = '%s' )", seconds, playername, playerip);
50 db_free_result(db_query(db, string));
51 return true;
52 }
53 return false;
54}
55
56/**
57 * Get the last IP used by a name
58 * @param db The database
59 * @param playername The player name
60 * @return The last IP
61 */
62stock Alias_GetLastIP(DB:db, const playername[])
63{
64 new pIP[MAX_IP];
65 if(db && !isnull(playername)) {
66 new string[90 + MAX_PLAYER_NAME];
67 format(string, sizeof(string), "SELECT `ip` FROM `Aliases` WHERE ( `name` = '%s' ) ORDER BY `datetime` DESC LIMIT 1", playername);
68 new DBResult:result = db_query(db, string);
69 if(db_num_rows(result)) {
70 db_get_field(result, 0, pIP, sizeof(pIP));
71 }
72 db_free_result(result);
73 }
74 return pIP;
75}
76
77/**
78 * Get a player's aliases from a name (find's the last IP used by the name)
79 * @param db The database
80 * @param playername The player name
81 * @param names The array to concatenate the aliases onto
82 * @param maxlength The size of the names array
83 * @return The number of aliases
84 */
85stock Alias_GetAliasFromName(DB:db, const playername[], names[], maxlength=sizeof(names))
86{
87 if(db && !isnull(playername)) {
88 new string[170 + MAX_PLAYER_NAME];
89 format(string, sizeof(string), "SELECT DISTINCT `name` FROM `Aliases` WHERE ( `ip` = ( SELECT `ip` FROM `Aliases` WHERE ( `name` = '%s' ) ORDER BY `datetime` DESC LIMIT 1 ) ) ORDER BY `datetime` DESC", playername);
90 new DBResult:result = db_query(db, string),
91 num_rows = db_num_rows(result);
92 if(num_rows) {
93 db_get_field(result, 0, string, sizeof(string));
94 strcat(names, string, maxlength);
95 for(new i = 1; i < num_rows; i++) {
96 db_next_row(result);
97 db_get_field(result, 0, string, sizeof(string));
98 if(maxlength - strlen(names) < strlen(string) + 2) break;
99 format(names, maxlength, "%s %s", names, string);
100 }
101 db_free_result(result);
102 return num_rows;
103 }
104 db_free_result(result);
105 }
106 return 0;
107}
108
109/**
110 * Get a player's aliases from an IP
111 * @param db The database
112 * @param playerip The player IP
113 * @param names The array to concatenate the aliases onto
114 * @param maxlength The size of the names array
115 * @return The number of aliases
116 */
117stock Alias_GetAliasFromIP(DB:db, const playerip[], names[], maxlength=sizeof(names))
118{
119 if(db && !isnull(playerip)) {
120 new string[110 + MAX_IP];
121 format(string, sizeof(string), "SELECT DISTINCT `name` FROM ( SELECT * FROM `Aliases` WHERE ( `ip` GLOB '%s' ) ORDER BY `datetime` DESC )", playerip);
122 new DBResult:result = db_query(db, string),
123 num_rows = db_num_rows(result);
124 if(num_rows) {
125 db_get_field(result, 0, string, sizeof(string));
126 strcat(names, string, maxlength);
127 for(new i = 1; i < num_rows; i++) {
128 db_next_row(result);
129 db_get_field(result, 0, string, sizeof(string));
130 if(maxlength - strlen(names) < strlen(string) + 2) break;
131 format(names, maxlength, "%s %s", names, string);
132 }
133 db_free_result(result);
134 return num_rows;
135 }
136 db_free_result(result);
137 }
138 return 0;
139}
140
141/**
142 * Get the aliases used by a player's subnet from their name (find's the last IP used by the name)
143 * @param db The database
144 * @param playername The player name
145 * @param names The array to concatenate the aliases onto
146 * @param maxlength The size of the names array
147 * @return The number of aliases
148 */
149stock Alias_GetAlias2FromName(DB:db, const playername[], names[], maxlength=sizeof(names))
150{
151 if(db && !isnull(playername)) {
152 new string[90 + MAX_IP + MAX_PLAYER_NAME];
153 format(string, sizeof(string), "SELECT `ip` FROM `Aliases` WHERE ( `name` = '%s' ) ORDER BY `datetime` DESC LIMIT 1", playername);
154 new DBResult:result = db_query(db, string);
155 if(db_num_rows(result)) {
156 db_get_field(result, 0, string, sizeof(string));
157 db_free_result(result);
158 format(string, sizeof(string), "SELECT DISTINCT `name` FROM `Aliases` WHERE ( `ip` GLOB '%s' ) ORDER BY `datetime` DESC", IPsubnet(string));
159 result = db_query(db, string);
160 new num_rows = db_num_rows(result);
161 if(num_rows) {
162 db_get_field(result, 0, string, sizeof(string));
163 strcat(names, string, maxlength);
164 for(new i = 1; i < num_rows; i++) {
165 db_next_row(result);
166 db_get_field(result, 0, string, sizeof(string));
167 if(maxlength - strlen(names) < strlen(string) + 2) break;
168 format(names, maxlength, "%s %s", names, string);
169 }
170 db_free_result(result);
171 return num_rows;
172 }
173 }
174 db_free_result(result);
175 return 0;
176 }
177 return 0;
178}
179
180/**
181 * Get the aliases used by a player's subnet
182 * @param db The database
183 * @param playerip The player IP
184 * @param names The array to concatenate the aliases onto
185 * @param maxlength The size of the names array
186 * @return The number of aliases
187 */
188stock Alias_GetAlias2FromIP(DB:db, const playerip[], names[], maxlength=sizeof(names))
189{
190 if(db && !isnull(playerip)) {
191 new string[90 + MAX_IP];
192 format(string, sizeof(string), "SELECT DISTINCT `name` FROM `Aliases` WHERE ( `ip` GLOB '%s' ) ORDER BY `datetime` DESC", IPsubnet(playerip));
193 new DBResult:result = db_query(db, string),
194 num_rows = db_num_rows(result);
195 if(num_rows) {
196 db_get_field(result, 0, string, sizeof(string));
197 strcat(names, string, maxlength);
198 for(new i = 1; i < num_rows; i++) {
199 db_next_row(result);
200 db_get_field(result, 0, string, sizeof(string));
201 if(maxlength - strlen(names) < strlen(string) + 2) break;
202 format(names, maxlength, "%s %s", names, string);
203 }
204 db_free_result(result);
205 return num_rows;
206 }
207 db_free_result(result);
208 return 0;
209 }
210 return 0;
211}
212
213/**
214 * Get the aliases of a player's previous IPs
215 * @param db The database
216 * @param playername The player name
217 * @param names The array to concatenate the aliases onto
218 * @param maxlength The size of the names array
219 * @return The number of aliases
220 */
221stock Alias_GetAlias3FromName(DB:db, const playername[], names[], maxlength=sizeof(names))
222{
223 if(db && !isnull(playername)) {
224 new string[140 + MAX_PLAYER_NAME];
225 format(string, sizeof(string), "SELECT DISTINCT `name` FROM `Aliases` WHERE ( `ip` IN ( SELECT `ip` FROM `Aliases` WHERE ( `name` = '%s' ) ) ) ORDER BY `datetime` DESC", playername);
226 new DBResult:result = db_query(db, string),
227 num_rows = db_num_rows(result);
228 if(num_rows) {
229 db_get_field(result, 0, string, sizeof(string));
230 strcat(names, string, maxlength);
231 for(new i = 1; i < num_rows; i++) {
232 db_next_row(result);
233 db_get_field(result, 0, string, sizeof(string));
234 if(maxlength - strlen(names) < strlen(string) + 2) break;
235 format(names, maxlength, "%s %s", names, string);
236 }
237 db_free_result(result);
238 return num_rows;
239 }
240 db_free_result(result);
241 }
242 return 0;
243}
244
245/**
246 * Get a player's previous IPs
247 * @param db The database
248 * @param playername The player name
249 * @param IPs The array to concatenate the IPs onto
250 * @param maxlength The size of the IPs array
251 * @return The number of IPs
252 */
253stock Alias_GetIPsFromName(DB:db, const playername[], IPs[], maxlength=sizeof(IPs))
254{
255 if(db && !isnull(playername)) {
256 new string[90 + MAX_PLAYER_NAME];
257 format(string, sizeof(string), "SELECT DISTINCT `ip` FROM `Aliases` WHERE ( `name` = '%s' ) ORDER BY `datetime` DESC", playername);
258 new DBResult:result = db_query(db, string),
259 num_rows = db_num_rows(result);
260 if(num_rows) {
261 db_get_field(result, 0, string, sizeof(string));
262 strcat(IPs, string, maxlength);
263 for(new i = 1; i < num_rows; i++) {
264 db_next_row(result);
265 db_get_field(result, 0, string, sizeof(string));
266 if(maxlength - strlen(IPs) < strlen(string) + 2) break;
267 format(IPs, maxlength, "%s %s", IPs, string);
268 }
269 db_free_result(result);
270 return num_rows;
271 }
272 db_free_result(result);
273 }
274 return 0;
275}