[Tutorial] Storing player chat log in MySQL, automatic deletion and periodic backups

[Tutorial] Storing player chat log in MySQL, automatic deletion and periodic backups - 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: Scripting Help (https://sampforum.blast.hk/forumdisplay.php?fid=12)
+---- Forum: Tutorials (https://sampforum.blast.hk/forumdisplay.php?fid=70)
+---- Thread: [Tutorial] Storing player chat log in MySQL, automatic deletion and periodic backups (/showthread.php?tid=511737)

Storing player chat log in MySQL, automatic deletion and periodic backups - RajatPawar - 07.05.2014

List of contents -

__________________________________________________ _

Creating table for storing chat
Storing the chat from OnPlayerText
Emptying the table every X days
Automatically backing up the database
Showing the chat on a webpage

__________________________________________________ _

1) Creating table for storing chat

We need about three fields for storing the total information about each chat line.
The information we need is - (along with the datatype and max length)

Username [ VARCHAR(24) ]
Text [VARCHAR(144)] or [TEXT]
Time [DATETIME]

Additionally/optionally, you can add an "ID" column as a primary, auto-incrementing field.
The table looks like -

2) Storing the chat from OnPlayerText

There are two ways to handle this situation.

Storing about 50 - 60 chats in a variable, then sending a query to add them ALL.
Send a query to update with EACH text.

It's easy to do the second one. You could do the first one too, depends on what you prefer.
You need to now use the callback to send a query EACH time. We use a static variable..

pawn Код:

public OnPlayerText(playerid, text[])
{
     static pQuery[168 + 50], pName[MAX_PLAYERNAME];
     GetPlayerName(playerid, pName, MAX_PLAYER_NAME);

     format(pQuery, sizeof(pQuery), "INSERT INTO "#TABLE_NAME"(Username, Time, Text) VALUES('%s', NOW(), '%s')", pName, text);
    
     // The NOW() function inserts the current datetime into the table.

     mysql_function_query(connectionHandle, pQuery, false, "", "");
     // No need to store any cache data, nor any need for processing under a callback.

     return 1;
}

In the above query using NOW() and wherever further it is used, please consider this -

Quote:

Originally Posted by Vince

Use of the datetime field is up for discussion. I've always preferred regular Unix timestamps. In any case you should use UTC_TIMESTAMP() rather than NOW(). The latter one gets the local server time, which can lead to inconsistencies if migrating the server to another country (e.g. from Europe to the US) or even if trying to display data in the user's local time.

That's it!
Your chat should successfully log in the table.

3) Emptying the table every X days.

Even though MySQL handles large sites like ********, sometimes, I like to keep the amount of rows limited with a periodic deletion of the log every 15 - 30 days. This can be done using a feature of MySQL called 'events'.

Events are run every X time as specified and do something that we want it to. Usually, the event_scheduler -which needs to be ON for events to run, is not ON, so you need to enable it. Here are some topics that will help you to turn on the event scheduler to ON, on your server, whatever it is.

(http://forum.logzilla.net/?topic=71.0) (-- Make sure your event scheduler is on by running the tests given there)

You can run the query needed to start the event through PHPMYADMIN or through your gamemode, as you wish. (I'd rather run it ONCE on the phpMyAdmin)

pawn Код:

CREATE EVENT `DeleteOldLogs` ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 3 DAY DO
DELETE LOW_PRIORITY FROM my_database.table_name WHERE `Time` < DATE_SUB(NOW(), INTERVAL 5 DAY)

That will start an event named 'DeleteOldLogs' which will run EVERY 3 days, and delete all chats which are more than 5 days old. It checks the 5 days thing using the `Time` column we made in the table. The LOW_PRIORITY clause is there to make sure we don't delete while someone is reading from the table at that time. Cool, right?

4) Automatically backing up the database

We have all been there. Some update ruins the database, and we need to rollback on the data. You need not worry. You can set an automatic backup mechanism to backup a table.

You can use various methods to do that. You can use CRON plus mysqldump, or use a PHP script (which is usually not recommended due to security issues) or there are some actual total scripts which do that for you, waiting to be installed.

You can also use the EVENT feature to backup the table. The only drawback/change is that the exported file is in the .tsv format which may make it difficult for you.

Here's a small snippet you might like to use for this method - View topic

For using CRON or other methods, view -

5) Showing the chat on a webpage

You can use a combination of PHP, jQuery (AJAX) and MySQL to show a kind of LIVE chat between a webpage and the server. We can pull the data from the table and show it.

http://tutorialzine.com/2010/10/ajax...hat-php-mysql/

This is a tutorial on a web chat room. If you understand enough, you can easily modify it to get the data from the table (making the job easy, you don't even have to store anything!) and displaying it without refreshing, somewhat like an IRC. Since it's given there, it would be a waste of time for me to explain this anymore!

6) Notes

NOTE: Use the escape function provided to avoid SQL injection by smart asses.
NOTE: You can find the my.ini file under 'MySQL' for WAMP.
NOTE: Thanks to Calgon - you should add an anti-spam to avoid rows of the same text being stored in the table.

Thank you, I hope you enjoyed this or it helped you.
Please point out ANY mistakes.

Re: Storing player chat log in MySQL, automatic deletion and periodic backups - Vince - 07.05.2014

Use of the datetime field is up for discussion. I've always preferred regular Unix timestamps. In any case you should use UTC_TIMESTAMP() rather than NOW(). The latter one gets the local server time, which can lead to inconsistencies if migrating the server to another country (e.g. from Europe to the US) or even if trying to display data in the user's local time.

Re: Storing player chat log in MySQL, automatic deletion and periodic backups - RajatPawar - 07.05.2014

Quote:

Originally Posted by Vince

That's, yes, true, but if the server and the SQLDB are on the same host, then it's no problem (for interaction b/w them, but still timezones would pose a problem). I've actually just added your quote, I'll look into the function and THEN include it, I don't want to blankly include it! But, thank you!

Re: Storing player chat log in MySQL, automatic deletion and periodic backups - Johnson_boy - 09.05.2014

Quote:

Originally Posted by Vince

There doesn't appear to any specific performance or flexibility benefit of using unix timestamp, so I personally prefer MySQL datetime, and would recommend you to use it as well. You can also use MySQL's native date and time functions, and in addition it's human readable without any conversion. The latter point is not that important, but at least I find the date and time functions very powerful and easy to use.

What I do agree on is using UTC_TIMESTAMP() or UTC_DATE() rather than NOW(), as, like you said, it guarantees the there will be no issues with the timezones.

Further reading: http://stackoverflow.com/questions/4...e-vs-timestamp

Edit: On topic:
It might be worth mentioning that the input should be escaped.

Код:

format(pQuery, sizeof(pQuery), "INSERT INTO "#TABLE_NAME"(Username, Time, Text) VALUES('%s', NOW(), '%s')", pName, text);

If someone were to say

Код:

'); TRUNCATE TABLE `chat`; --

you'd likely to lose your data, assuming they could guess the name of the table. Or even worse, if the mysql user has access to more tables/database, they can as well be potentionally deleted/modified by trying some of common table/database names.

Another thing: You should probably use datetime field instead of date, as it would most likely be useful to store the time of the message as well, not just the date.

Re: Storing player chat log in MySQL, automatic deletion and periodic backups - RajatPawar - 09.05.2014

Quote:

Originally Posted by Johnson_boy

Код:

format(pQuery, sizeof(pQuery), "INSERT INTO "#TABLE_NAME"(Username, Time, Text) VALUES('%s', NOW(), '%s')", pName, text);

Of course, I forgot that! I'll add a note.

Quote:

Originally Posted by Johnson_boy

Another thing: You should probably use datetime field instead of date, as it would most likely be useful to store the time of the message as well, not just the date.

I didn't feel any need to do so (IMHO), but if anyone'd like to that particular.. why not, I'll again add a side note.

Cheers

Re: Storing player chat log in MySQL, automatic deletion and periodic backups - Patrick - 09.05.2014

Anyways nice & useful tutorial but have you ever read using '#' to embed texts? Anyways here's an information from Southclaw I used to use '# to embed text but he just told me off and learned something

Source: http://southclawjk.wordpress.com/201...ext-embedding/

Re: Storing player chat log in MySQL, automatic deletion and periodic backups - RajatPawar - 09.05.2014

Quote:

Originally Posted by Patrick_

Anyways nice & useful tutorial but have you ever read using '#' to embed texts? Anyways here's an information from Southclaw I used to use '# to embed text but he just told me off and learned something

Source: http://southclawjk.wordpress.com/201...ext-embedding/

I actually know about this already from one of ******'s post months ago, but I haven't got around to changing my habit. I don't know, it just seems much more fitting, however weird that sounds! I'll try changing it

Thanks!