[XASECO1/2] Plugin: DBTools v1.0.7

[jimpower]

DBTools-Plugin v1.0.7 for XAseco1/2
The backup & cleaning lady for your XAseco1/2 Database...



What it does:
DBTools can perform many tasks on your database like

• Backup your database locally or to FTP
• Restore a database backup from local file or from FTP
• Manage your backups (Automatically delete old backups after a given time) locally or on FTP
• Automatically perform optimizations/backups at a customizable time
• Optimize the database
• Repair the database
• Remove old tracks (including records, votes,...) from the database that are no more in the challengelist
• Remove all records behind position XX
• Remove all records/votes of a specific login
• Remove all records/votes of a specific track
• Remove records of cheaters that are on the Dedimania-Blacklist and optionally add them to your local blacklist. This feature is currently available for TMF only because we don't have a global TM2-Blacklist yet.
• Reset all user styles
• Wipe the database
• Clear the TimePlayed of all or of a specific login
• Clear the wins of all or of a specific login
• Clear Karma-Votes for a track / of a player / of all tracks
• ...

How to use it
Just use the command /dbtools for more information (The command is available for MasterAdmins only!).

Compatibility
It works with XAseco 1.14 (TMF) and XAseco2 v1.0 (TM2).

Attention:

• Always make a backup of your database first, DBTools comes with absolutely no warranty
• It's strongly recommended to restart XAseco1/2 after executing one of the commands
• It's strongly recommended to create an own directory for backups

Installation

• Copy dbtools.xml to your XAseco-Root-Directory
• Copy plugin.dbtools.php to your plugins directory
• Add <plugin>plugin.dbtools.php</plugin> to your plugins.xml
• Edit the dbtools.xml and make your settings
• Restart XAseco1/2

Scheduling
Since version 1.0.3, you can schedule all tasks for the plugin. It is important, that you enter the command without /dbtools. For example if you want to schedule a backup, just enter "backup" as command.

Code: Select all

		<!-- 
		This example creates a backup each Monday 04:18h -->
		<item>
			<command>backup</command>
			<week>ALL</week>
			<day>1</day>
			<time>04:18</time>
		</item>
		
		<!-- This example optimizes the database each Thursday 15:00h -->
		<item>
			<command>optimize</command>
			<week>ALL</week>
			<day>4</day>
			<time>15:00</time>
		</item>

FTP-Configuration
You can enable FTP-Upload to manage all your backups on an external FTP-Server (Set <backup_location> to FTP)

Code: Select all

	<ftp_hostname>www.myserver.com</ftp_hostname>
	<ftp_username>mylogin</ftp_username>
	<ftp_password>mypassword</ftp_password>
	<ftp_directory>directory/subdirectory</ftp_directory>
	<ftp_port>21</ftp_port> <!-- Default FTP-Port is 21-->
	<ftp_timeout>90</ftp_timeout> <!-- Connection Timeout in seconds-->

Note: If a file already exists on the FTP-Server, it will be overwritten. It is recommended to create a seperate directory for the backups

Backup & Restore Requirements
If you want to use the backup and restore features, the safe mode of your PHP-Installation must be turned off, you must use a Linux-OS, the xaseco directory must be writable (min 555) and the database must be located on the same machine as XAseco.

Explainations
/dbtools optimize
This should be used if you have deleted a large part of a table or if you have made many changes to a table with variable-length rows. You can use /dbtools optimize to reclaim the unused space and to defragment the data file. This statement may improve the performance sometimes significantly.

/dbtools backup
This command creates a backup of your database.
If you just use "/dbtools backup", the plugin creates an SQL-Dump with the filename you defined at the path you defined, or on the FTP-Server you definded.

/dbtools restore <ID>
This restores the last backup. You can find the <ID> by typing /dbtools listbackups. Normally /dbtools restore 1 should always restore the last backup. After the restore, you must restart Xaseco.

/dbtools repair
This command repairs a possibly corrupted table. Normally, you should never have to run /dbtools repair. However, if disaster strikes, this statement is very likely to get back all your data from a MyISAM table (important, your DB must be of type MyISAM).

/dbtools wipedb
This clears the complete database (like on a fresh install). Using this can make you cry...

Performance boosting
First of all, DBTools is not magic and it can not do any magic but in some cases it can increase the speed of your database significantly by just cleaning up. On big databases the following commands, in the correct order, can be used to reduce the ressources consumption:

• Kick all players, set a password (This makes most fun and saves the most ressources ...joke.).
• /dbtools backup Always(!) create a backup of your DB first. If this fails, do not continue.
• /dbtools cleanupdb This removes tracks and records from the database that are no more in your tracklist
• /dbtools offsetdelete XX This will kick out all records behind position XX (replace XX by your number - in Xaseco's default configuration 50 is a good choice...)
• /dbtools optimize We deleted tons of rows, so we should optimize the tables now
• Restart.

Side-Effects: These commands (especially the deletion of records) can effect the player stats (Most Records-Widget, Serverrank,...). Speed vs. Features is always a compromise.

Download
DBTools v1.0.7 for XAseco1/2

Preview of the upcoming version
At the dev page

Changelog
v1.0.7
* Added general Mode-Selector: FTP or LOCAL for backups.
* Added backup restore directly from FTP
* Added check if FTP-Server is reachable on startup (in FTP-Mode only)
* Added <week> to scheduling (These are the calendar weeks from 1 to max 53).
* Scheduling <day> and <week> can now be set to ALL
* Added automatic deletion of old backups on the FTP-Server (configurable via <ftp_prune_after>)
* Added automatic deletion of old local backups (configurable via <backup_local_prune_after>)
* Added <blocked_commands> to the xml to disable selected features
* Added /dbtools listbackups command
* Added /dbtools deletebackup <ID> command
* Added the variable #DATE# to include the current timestamp into the backup filename
* Changed the /dbtools restore command – You must now restore the backups via the ID..Example: /dbtools restore 1
* /dbtools restore no more accepts custom filenames! Filename must be set in dbtools.xml!
* Disabled <allow_customfilenames> by default – it makes no sense
* Better structure for the /dbtools help command
* All backups must have the .sql ending
* Added checks if the backup directory is writable
v1.0.6 (sorry Xymph from now 1 update/week)
* Added the command offsetdelete to delete records behind a defined position
* Added the command "/dbtools help <COMMAND>" for more details on the usage
v1.0.5
* Added the Console-Window
* Added sync with Dedi-Blacklist (cheaters command)
* Added karmaclear, karmaclearthis, karmaclearlogin
v1.0.4
* Added support for undef.de's "Third-party Plugins UpToDate"
* Fixed an issue with the command cleanupdb
* Added misconfiguration checks
* Added <ftp_port> and <ftp_timeout> to the dbtools.xml
* Added the possibility to delete the local backup after uploading
* Added <scheduling_enabled> to the dbtools.xml
v1.0.3
* Added uploading backups to an external FTP-Server
* Added scheduling
v1.0.2
* Added Backup/Restore
v1.0.1
* Bugfixed command clearstyles
v1.0.0
* Initial release

[Pajo]

Hi,

Great plugin you have there I must say.Thanks for sharing it with the community. If I may, I would like to give a suggestion: When you type /dbtools help , you get a list of all commands in chat. Is it possible to include the list of all commands in new window in the middle of screen on next update?

Regards, Harry.

[jimpower]

Well I am not a fan of those popups, but I thought it would be a good idea to move the dbtools-output to a console window. if the console window is enabled at the config (v1.0.5+), it pops out automatically when typing a command.

So, 1.0.5 New features:

• Sync your database with the Dedimania Blacklist (optionally add them to the blacklist). This is avail for TMF only at the moment because I didn't find the URL of the TM2-Datafile...Maybe someone can help?!
• Console-Window
• Clear local karma votes

[TheBigG]

may you do it like xaseco /dbtools help in chat /dbtools helpall in window

[Xymph]

Sync your database with the Dedimania Blacklist (optionally add them to the blacklist). This is avail for TMF only at the moment because I didn't find the URL of the TM2-Datafile...Maybe someone can help?!

There is no Dedimania TM2 blacklist yet, no cheaters have emerged so far (last time I checked anyway).

[jimpower]

Ok. I hope it'll go on like this. Less work, more fun for everyone. Except for the hack-kiddies - they don't deserve fun.

[Xymph]

(sorry Xymph from now 1 update/week)

That would be welcome... phew.

[TheBigG]

looks like that the plugin has problems with spaces in the db name

[jimpower]

uhh spaces in dbnames.

for your (edited) folder question:
create a folder like xaseco/backups and make it writable, then use /dbtools backup backups/mybackupname.sql

[TheBigG]

but a other question can you add weeks to your scheduler too?
thanks in advance

[jimpower]

is on the list for the upcoming version.

[jimpower]

but a other question can you add weeks to your scheduler too?
thanks in advance

You may check the preview version for the 1.0.7 here: http://punkr.de/dbtools-plugin

Weeks are already included there (see the changelog)

[TheBigG]

but a other question can you add weeks to your scheduler too?
thanks in advance

You may check the preview version for the 1.0.7 here: http://punkr.de/dbtools-plugin

Weeks are already included there (see the changelog)

yeah thank you, great plugin so far

[jimpower]

Yes I just uploaded v1.0.7 with some nice additions...

small excerpt:
* Restore backups directly from your FTP-Server
* Add timestamp to the filename
* Auto-Delete old backups after a given number of days
Full changelog at the 1st post.

That's it for the moment. I should eat sthg..

[weasel]

you are that guy who makes plugins for trackmaniaserver.org.

Nice work !

Can we make request for plugins ?
I don't like the cp widget on top of screen , too small.
( bestcps )

Will try this one on my server, looks great.