Useful queries for managing your extDB MySQL Database

I have not worked a lot with sql so I don't know if this is even possible.

Could I run something that would delete all player accounts and anything owned/placed by those players if they were last seen 20(or any number) days +

EDIT:
I have been experimenting but am not having much luck. To break it down it would be something like this:

If PlayerSave LastModified is older then "x days" then y = PlayerUID

Find/Delete all lines in ServerObjects and ServerVehicles  containing "Y"

Further to the above, is there a simple function that will find every line in a database containing the player ID to be deleted?

If I don't have to search each table it would probably be easier.

Or if anyone has a good resource for sql query explanations/building I'd be very keen to learn more now that I've started.

Code: [Select]

delete from ServerObjects where Variables like '%76561198056097019%'   

This works on my test database and deleted 200+ objects. I am having trouble with determining the PlayerSave - "LastModified" column contains a Datetime over 20 days old and then if true pass the UID to the delete line.

Maybe I'm going about this wrong. Instead of deleting things owned by the player maybe I can just check the date and then delete every row containing that player ID in the database. This will clear the player saves and all objects/vehicles. Although it might be good to keep the stats.

Bit of thinking out loud. I'll keep working on it

Not sure if there was an easier way and I'm still learning but I got this to delete all old stuff players have left around the map. Players that are no longer actively playing. This does not delete Player gear/spawn/bank accounts)

I had to run this in 3 separate queries, Not sure if this was due to the control panel access I have or just haven't learnt how to make it all run together.

This may not apply to most people but I do not have a limit on base object cleanup and do not want players to have to re-lock stuff all the time.

(Change delete to select if you just want to see the list of what would be deleted)

-Run first to remove all quotation marks so the JOIN will work

Code: [Select]

UPDATE ServerObjects SET OwnerUID = REPLACE(OwnerUID, '"', '');

-Run this second to delete all player placed server objects based on the players last log in/ save date (Modified date)

Adjust the date as required to delete all records prior to the selected date.

Code: [Select]

DELETE ServerObjects
   FROM
      ServerObjects
         LEFT JOIN PlayerSave
            on ServerObjects.OwnerUID = PlayerSave.PlayerUID
           and PlayerSave.LastModified <= '2015-07-13'
Where
           PlayerSave.LastModified <= '2015-07-13'

-Run this after deletion to reapply the "" double quotation marks to the OwnerUID column

Code: [Select]

update ServerObjects set OwnerUID=concat('"',OwnerUID,'"')


Or you can do

Code: [Select]

on ServerObjects.OwnerUID = CONCAT('"',PlayerSave.PlayerUID,'"')
But anyway, the usage of an object does not necessary correlate with the owner's last login - if that player placed a wall or crate in a base, and the base is still in use by others, you're effectively wiping out part of it.

Probably should note as per AgentRev note above that this might not apply to people using vanilla. If players are using base-lockers everything in the radius of the base will be owned by the individual and then this can apply without that issue.

I want to run 2 php cleaning on my server, but I do not know how to do, can someone help? I will put in crontab

ty

Delete all beacons after 7 days

Code: [Select]

<?php

$dbaddress = "localhost";
$dbport = "3306";
$dbusername = "root";
$dbpassword = "";
$dbname = "wasteland";
$con=mysqli_connect("$dbaddress","$dbusername","$dbpassword","$dbname","$dbport");
if (mysqli_connect_errno())
{
echo "Failed to connect to MySQL: " . mysqli_connect_error();
}


$query = mysqli_query($con,"delete from serverobjects where .......


?>



delete all objects after 30 days except "Land_Device_assembled_F, Land_InfoStand_V2_F,Land_Canal_Wall_Stairs_F, Box_NATO_AmmoVeh_F"

Code: [Select]

<?php

$dbaddress = "localhost";
$dbport = "3306";
$dbusername = "root";
$dbpassword = "";
$dbname = "wasteland";
$con=mysqli_connect("$dbaddress","$dbusername","$dbpassword","$dbname","$dbport");
if (mysqli_connect_errno())
{
echo "Failed to connect to MySQL: " . mysqli_connect_error();
}


$query = mysqli_query($con,"delete from serverobjects where ........


?>


You are overcomplicating it, use the MySQL event scheduler: http://dev.mysql.com/doc/refman/en/event-scheduler.html

Here is an example of how to create a recurring event:

Code: [Select]

CREATE EVENT `Dumping_event`
    ON SCHEDULE EVERY 1 DAY
    ON COMPLETION PRESERVE
DO
BEGIN
    INSERT INTO stock_dumps(itemcode, quantity, avgcost, ttlval,dump_date)
      SELECT itmcode, quantity, avgcost, (avgcost * quantity)as ttlval, NOW()
      FROM table_1 JOIN table_2 ON table_1.itmcode = table_2.itmcode;
END;
If you really wish to use cron, you can call a script directly:

Code: [Select]

mysql --username=root --password=1234 --database=wasteland < "/path/to/file.sql"

ty AgentRev.

I need your help.
I have a server and a mission based on Wasteland. The server uses a mysql database (Heidi SQL). The data transfer method is extDB.
And I have a variable (parameter) that I want to save in this database. How can I do this? Can someone explain in detail or provide a link to the article?