[TheToretto]

IP Logger

(MySQL R41-4)

Introduction:

What it does is basically, every time someone connects to the server, when he disconnects, even if he doesn't register, it saves: The username, the IP, the date and exact time he connected, and disconnected. So four fields. How can this be useful? Well, an example is: You can detect multi-accounts. (manually ofc, I can release an automated one if requested.)

Screenshots:









Dependencies :

• zcmd - Zeex
• sscanf v2.8.3 - Y_Less
• MySQL R41-41 - BlueG

Source code:

• Github

[Franovitch]

brilliant !

[Zeus666]

Nice

[Heress]

It's AMAZING!!

[Calisthenics]

Provide a .sql file about the table structure please.

Few tips to consider:

• Save dates as TIMESTAMP OR DATETIME datatype and not as string. There is mysql function to format a readable form and an integrated system can even allow you retrieving total online time and many more benefits.
• Avoid non-threaded queries as it can lead to memory leaks (you forgot to delete the cache instance).
• Do not copy strings directly as this:
  
  pawn Code:

  g_ConnectionDate[playerid] = ReturnDate();
  g_pIP[playerid] = ReturnIP(playerid);
  
  g_DisconnectionDate[playerid] = ReturnDate();

  There is `strcpy` (macro of `strcat`) or `memcpy` function. Avoid `format` function.
• Select columns you want to retrieve and not everything. `Name` or `Disconnected` columns are never retrieved.
• Set appropriate keys to table structure. Searching by a string is slower than an integer. Searching by a non-indexed string is even slower.

[TheToretto]

Quote:

Originally Posted by Calisthenics Provide a .sql file about the table structure please. Few tips to consider: Save dates as TIMESTAMP OR DATETIME datatype and not as string. There is mysql function to format a readable form and an integrated system can even allow you retrieving total online time and many more benefits. Avoid non-threaded queries as it can lead to memory leaks (you forgot to delete the cache instance). Do not copy strings directly as this: pawn Code: g_ConnectionDate[playerid] = ReturnDate(); g_pIP[playerid] = ReturnIP(playerid); g_DisconnectionDate[playerid] = ReturnDate(); There is `strcpy` (macro of `strcat`) or `memcpy` function. Avoid `format` function. Select columns you want to retrieve and not everything. `Name` or `Disconnected` columns are never retrieved. Set appropriate keys to table structure. Searching by a string is slower than an integer. Searching by a non-indexed string is even slower.

Hey, thanks a lot for the tips, I really appreciate it, I've done everything you mentioned, (Commit) except the datetime, do you have an example or something? And I want to keep the same actual form displayed in the dialogs, if possible.

[CaptainBoi]

Well really good job!

[zakariacaspeer]

wow nice bro

[KinderClans]

Quote:

Originally Posted by TheToretto Hey, thanks a lot for the tips, I really appreciate it, I've done everything you mentioned, (Commit) except the datetime, do you have an example or something? And I want to keep the same actual form displayed in the dialogs, if possible.

pawn Code:

INSERT INTO yourtable (time_column) VALUES (NOW())

To show it:

pawn Code:

new date[36], string[40];
cache_get_value_name(i,"time_column", date, 36);
format(string, sizeof(string), "Date: %s", date);

[TheToretto]

Quote:

Originally Posted by KinderClans pawn Code: INSERT INTO yourtable (time_column) VALUES (NOW()) To show it: pawn Code: new date[36], string[40]; cache_get_value_name(i,"time_column", date, 36); format(string, sizeof(string), "Date: %s", date);

Thanks, but what if I want to just store the time? Because, I send only one query, to save both the connection date & disconnection. I don't want to use multiple queries.

[solstice_]

Nice one man!

[TheToretto]

Thanks, appreciate it

[Calisthenics]

Quote:

Originally Posted by TheToretto Hey, thanks a lot for the tips, I really appreciate it, I've done everything you mentioned, (Commit) except the datetime, do you have an example or something? And I want to keep the same actual form displayed in the dialogs, if possible.

Yes, it is possible: https://dev.mysql.com/doc/refman/8.0...on_date-format

pawn Code:

SELECT IP, 
       DATE_FORMAT(Connected, '%%Y %%M %%e - %%T') AS Connected
  FROM iplogger
 WHERE Name = '%e'

Thank you for taking into consideration my suggestions. Re-consider about the use of threaded SELECT queries.

When your table does not have a primary key and searches by name, it filters about 10 percent -- it scans the whole table for looking any matches. A name cannot be unique as a player can connect more than once but a pair can be. A player joins a specific time, this pair is unique and we can also take advantage of it.

pawn Code:

CREATE TABLE `iplogger` (
  `Name` varchar(24) NOT NULL,
  `IP` varchar(15) NOT NULL,
  `Connected` datetime NOT NULL,
  `Disconnected` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`Name`,`Connected`)
)

Now it filters 100 percent.

Store unix timestamp on connect for a player and use FROM_UNIXTIME when a player disconnects.

pawn Code:

INSERT INTO iplogger (Name, IP, Connected) 
VALUES ('%s', '%s', FROM_UNIXTIME(%d))

There is one last suggestion: IPv4 can be converted to integer and take less space. If you are interested:
https://dev.mysql.com/doc/refman/8.0...tion_inet-aton
https://dev.mysql.com/doc/refman/8.0...tion_inet-ntoa

Store IP as unsigned int and the use is very simply INET_ATON('127.0.0.1') and INET_NTOA(IP)

[Mellnik]

You may not save personal data from EU citizens according to GDPR.

[GameOvr]

Awesome but can you release a sqlite version too
Plz

[TheToretto]

Thanks Calisthenics, I understood what you meant, but even with examples I didn't really succeed employing them. Could you please apply a pull request with the changes? Would be very nice from you.

Quote:

Originally Posted by Mellnik You may not save personal data from EU citizens according to GDPR.

Ew, idgaf lmao

Quote:

Originally Posted by GameOvr Awesome but can you release a sqlite version too Plz

I've never used those "lite" versions of MySQL, so I can't, but it seems like you're onto SQLite, why don't you do?

[GameOvr]

Tried but I faild xD

Idk how to check that the player connected ip is already in the db and more such things

[Calisthenics]

Quote:

Originally Posted by TheToretto Thanks Calisthenics, I understood what you meant, but even with examples I didn't really succeed employing them. Could you please apply a pull request with the changes? Would be very nice from you.

I noticed a few more issues so I addressed them all in the pull request I sent. Already tested to confirm everything works as expected.

I like how the pagination was done, well done.

[TheToretto]

Quote:

Originally Posted by Calisthenics I noticed a few more issues so I addressed them all in the pull request I sent. Already tested to confirm everything works as expected. I like how the pagination was done, well done.

Thanks a lot dude I really appreciate your big contribution, added your name in the top of the script, didn't knew what else I can do to thank you

[Jefff]

You should also remove both includes zcmd and sscanf because its only used one time and not necessary, you can use classic samp OnPlayerCommandText