[FilterScript] [MySQL R41-4] IP Logger (dialogs)

[FilterScript] [MySQL R41-4] IP Logger (dialogs) - Printable Version

+- SA-MP Forums Archive (https://sampforum.blast.hk)
+-- Forum: SA-MP Scripting and Plugins (https://sampforum.blast.hk/forumdisplay.php?fid=8)
+--- Forum: Filterscripts (https://sampforum.blast.hk/forumdisplay.php?fid=17)
+--- Thread: [FilterScript] [MySQL R41-4] IP Logger (dialogs) (/showthread.php?tid=660401)

Pages: 1 2

[MySQL R41-4] IP Logger (dialogs) - TheToretto - 02.11.2018

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 :

Source code:

Github

Re: [MySQL R41-4] IP Logger (dialogs) - Franovitch - 02.11.2018

brilliant !

Re: [MySQL R41-4] IP Logger (dialogs) - Zeus666 - 02.11.2018

Nice

Re: [MySQL R41-4] IP Logger (dialogs) - Heress - 02.11.2018

It's AMAZING!!

Re: [MySQL R41-4] IP Logger (dialogs) - Calisthenics - 02.11.2018

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.

Re: [MySQL R41-4] IP Logger (dialogs) - TheToretto - 02.11.2018

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.

Re: [MySQL R41-4] IP Logger (dialogs) - CaptainBoi - 02.11.2018

Well really good job!

Re: [MySQL R41-4] IP Logger (dialogs) - zakariacaspeer - 02.11.2018

wow nice bro

Re: [MySQL R41-4] IP Logger (dialogs) - KinderClans - 02.11.2018

Quote:

Originally Posted by TheToretto

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);

Re: [MySQL R41-4] IP Logger (dialogs) - TheToretto - 02.11.2018

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.

Re: [MySQL R41-4] IP Logger (dialogs) - solstice_ - 02.11.2018

Nice one man!

Re: [MySQL R41-4] IP Logger (dialogs) - TheToretto - 02.11.2018

Thanks, appreciate it

Re: [MySQL R41-4] IP Logger (dialogs) - Calisthenics - 03.11.2018

Quote:

Originally Posted by TheToretto

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)

Re: [MySQL R41-4] IP Logger (dialogs) - Mellnik - 03.11.2018

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

Re: [MySQL R41-4] IP Logger (dialogs) - GameOvr - 03.11.2018

Awesome but can you release a sqlite version too
Plz

Re: [MySQL R41-4] IP Logger (dialogs) - TheToretto - 03.11.2018

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?

Re: [MySQL R41-4] IP Logger (dialogs) - GameOvr - 03.11.2018

Tried but I faild xD

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

Re: [MySQL R41-4] IP Logger (dialogs) - Calisthenics - 04.11.2018

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.

Re: [MySQL R41-4] IP Logger (dialogs) - TheToretto - 04.11.2018

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

Re: [MySQL R41-4] IP Logger (dialogs) - Jefff - 07.11.2018

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