SQL Cleanups - Someone mind sharing?

[cen]

@echo off

:Settings
set scriptpath=C:\Program Files\Steam\SteamApps\Common\Arma 2 Operation Arrowhead\scripts\
set mysqlpath=C:\Program Files\MySQL\MySQL Server 5.6\bin

:Run Cleanup SQL
cd /d "%mysqlpath%"
echo Deleting damaged objects...
mysql -h 127.0.0.1 -u USERNAME -pPASSWORD -D DATABASE< "%scriptpath%"\NAMEOFSQLFILE.sql
ping 127.0.0.1 -n 15 >NUL
echo Successful... I think.
ping 127.0.0.1 -n 10 >NUL

[Tricks]

  On 2/8/2015 at 8:52 PM, Gr8 said:

The List of script that cen posted is not correctly formatted, You cannot run all of it at the same time. Only one by one.

 

Here is the Correct Format for all the scripts plus some extra ones by me:

 

  Reveal hidden contents

/* =================== DELETES =================== */

/* Delete old crap */
DELETE FROM `object_data`
WHERE `LastUpdated` < DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 21 DAY);

/* Delete destroyed objects */
DELETE FROM `object_data`
WHERE Damage = 1;

/* Delete plots and doors not being cleaned */
DELETE FROM `object_data`
WHERE `LastUpdated` < DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 14 DAY)
AND `Classname` IN ('Plastic_Pole_EP1_DZ','CinderWallDoorSmallLocked_DZ','CinderWallDoorLocked_DZ','CinderWallSmallDoorway_DZ','CinderWallDoorway_DZ','Land_DZE_WoodDoorLocked','CinderWallDoor_DZ','CinderWallDoorSmall_DZ','Land_DZE_WoodDoor','Land_DZE_GarageWoodDoor','Land_DZE_GarageWoodDoorLocked','Land_DZE_LargeWoodDoorLocked','Land_DZE_LargeWoodDoor','WoodLargeWallDoor_DZ');

/* Delete old login data */
DELETE FROM `player_login`;

/* Delete Old Banks */
DELETE FROM banking_data
WHERE LastUpdated < NOW() - INTERVAL 20 DAY ;

/* Delete untouched server spawned vehicles */
DELETE FROM `object_data`
WHERE `LastUpdated` < DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 3 DAY)
AND `CharacterID` = '0'
AND `Classname`
NOT REGEXP 'barrier|storage|shed|bench|wall|floor|fence|pump|wood|hrescue|stick|pole|generator|panel|house|rack|bag|stand|barrel|canvas|wire|hedgehog|net|trap|ramp|fort|sand|scaffold|nest';

/* Delete all vehicles not touched in 7 days */
DELETE FROM `object_data`
WHERE `LastUpdated` < DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 7 DAY)
AND `Classname`
NOT REGEXP 'barrier|storage|shed|bench|wall|floor|fence|pump|wood|hrescue|stick|pole|generator|panel|house|rack|bag|stand|barrel|canvas|wire|hedgehog|net|trap|ramp|fort|sand|scaffold|nest';

/* Delete empty storage objects */
DELETE FROM `object_data`
WHERE `LastUpdated` < DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 3 DAY)
AND ( (`Inventory` IS NULL) OR (`Inventory` = '[]') OR (`Inventory` = '[[[],[]],[[],[]],[[],[]]]') )
AND `Classname` IN ('Wooden_shed_DZ','WoodShack_DZ', 'TentStorage', 'TentStorageDomed', 'TentStorageDomed2', 'WoodCrate_DZ', 'GunRack_DZ', 'OutHouse_DZ', 'StorageShed_DZ');

/* Delete unused storage objects */
DELETE FROM `object_data`
WHERE `LastUpdated` < DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 10 DAY)
AND `Classname` IN ('Wooden_shed_DZ','WoodShack_DZ', 'TentStorage', 'TentStorageDomed', 'TentStorageDomed2', 'WoodCrate_DZ', 'GunRack_DZ', 'OutHouse_DZ', 'StorageShed_DZ');

/* Delete all dead characters */
DELETE from Character_data
USING Character_data, Character_data as tempchartable
WHERE (Character_data.PlayerUID = tempchartable.PlayerUID)
AND (NOT Character_data.alive = tempchartable.alive)
AND (Character_data.alive = 0);

/* Delete Inactive Characters */
DELETE FROM Character_DATA
WHERE LastLogin < NOW() - INTERVAL 10 DAY;


/* =================== UPDATES =================== */

/* Unlock Purchased Untouched Vehicles */
UPDATE `object_data`
SET `CharacterID` = 0
WHERE `LastUpdated` < DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 5 DAY)
AND `CharacterID` > 0
AND `Classname`
NOT REGEXP 'barrier|storage|shed|bench|wall|floor|fence|pump|wood|hrescue|stick|pole|generator|panel|house|rack|bag|stand|barrel|canvas|wire|hedgehog|net|trap|ramp|fort|sand|scaffold|nest';

/* Fix inventory on maintain objects */
UPDATE `object_data`
SET `inventory` = '[]',`LastUpdated` = `LastUpdated`
WHERE `inventory` = '[[[],[]],[[],[]],[[],[]]]'
AND
`Classname` IN (
'BagFenceRound_DZ','CinderWall_DZ','CinderWallDoorLocked_DZ','CinderWallDoorSmall_DZ','CinderWallDoorSmallLocked_DZ',
'CinderWallHalf_DZ','FireBarrel_DZ','GunRack_DZ','LightPole_DZ','MetalFloor_DZ','MetalPanel_DZ','OutHouse_DZ','TentStorage',
'TentStorageDomed','TentStorageDomed2','Sandbag1_DZ','Scaffolding_DZ','StickFence_DZ','StorageShed_DZ','WorkBench_DZ',
'WoodCrate_DZ','WoodFloor_DZ','WoodFloorQuarter_DZ','WoodLargeWall_DZ','WoodLargeWallWin_DZ','WoodShack_DZ','WoodSmallWall_DZ',
'WoodSmallWallThird_DZ','WoodSmallWallWin_DZ','Land_DZE_GarageWoodDoor','WoodFloorHalf_DZ','Fort_RazorWire','Land_DZE_LargeWoodDoorLocked',
'WoodStairsRails_DZ','WoodLadder_DZ','WoodStairsSans_DZ','M240Nest_DZ','Land_DZE_WoodDoor'
);

/* Set Damage on all other objects */
UPDATE `object_data`
SET `Damage`=0.2
WHERE `ObjectUID` <> 0
AND `CharacterID` <> 0
AND `Datestamp` < DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 3 DAY)
AND ( (`Inventory` IS NULL) OR (`Inventory` = '[]') );

/* Set Damage On Doors/Plot Poles using Door Management via Age */
UPDATE `object_data`
SET `Damage`=0.2
WHERE `Classname` IN ('Plastic_Pole_EP1_DZ','CinderWallDoorSmallLocked_DZ','CinderWallDoorLocked_DZ','CinderWallSmallDoorway_DZ','CinderWallDoorway_DZ','Land_DZE_WoodDoorLocked','CinderWallDoor_DZ','CinderWallDoorSmall_DZ','Land_DZE_WoodDoor','Land_DZE_GarageWoodDoor','Land_DZE_GarageWoodDoorLocked','Land_DZE_LargeWoodDoorLocked','Land_DZE_LargeWoodDoor','WoodLargeWallDoor_DZ')
AND `Datestamp` < DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 3 DAY)
AND `Inventory` != '[]';

/* Set Safe Combo to 0000 */
UPDATE `object_data`
SET `CharacterID` = 0
WHERE `LastUpdated` < DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 14 DAY)
AND `CharacterID` > 0
AND `Classname` = "VaultStorageLocked"
AND `Inventory` <> '[]'
AND `Inventory` IS NOT NULL;

/* Set Lock Box to RED 00 */
UPDATE `object_data`
SET `CharacterID` = 10000
WHERE `LastUpdated` < DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 14 DAY)
AND `CharacterID` > 0
AND `Classname` = "LockboxStorageLocked"
AND `Inventory` <> '[]'
AND `Inventory` IS NOT NULL;

/* Unlock vehicles with lost keys */
UPDATE `object_data`
SET `object_data`.`CharacterID` = 0
WHERE `object_data`.`CharacterID` <> 0
AND`object_data`.`CharacterID` <= 12500
AND`object_data`.`Classname` NOT LIKE 'Tent%'
AND`object_data`.`Classname` NOT LIKE '%Locked'
AND`object_data`.`Classname` NOT LIKE 'Land%'
AND`object_data`.`Classname` NOT LIKE 'Cinder%'
AND`object_data`.`Classname` NOT LIKE 'Wood%'
AND`object_data`.`Classname` NOT LIKE 'Metal%'
AND`object_data`.`Classname` NOT LIKE '%Storage%'
AND`object_data`.`Classname` NOT IN ('OutHouse_DZ', 'GunRack_DZ', 'WorkBench_DZ', 'Sandbag1_DZ', 'FireBarrel_DZ', 'DesertCamoNet_DZ', 'StickFence_DZ', 'LightPole_DZ', 'DeerStand_DZ', 'ForestLargeCamoNet_DZ', 'Plastic_Pole_EP1_DZ', 'Hedgehog_DZ', 'FuelPump_DZ', 'Fort_RazorWire', 'SandNest_DZ', 'ForestCamoNet_DZ', 'Fence_corrugated_DZ', 'CanvasHut_DZ', 'Generator_DZ')
AND FindVehicleKeysCount1(object_data.CharacterID) = 0;

 

I don't see the differences between yours and Cen's.....?

[Gr8]

  On 3/19/2015 at 2:33 PM, Tricks said:

I don't see the differences between yours and Cen's.....?

 

Try Executing Cen's SQL without errors. 

[Tricks]

  On 3/19/2015 at 4:53 PM, Gr8 said:

Try Executing Cen's SQL without errors. 

 

They all loaded in fine and seem to be working. But I will double check. Can you show me an example?

[Gr8]

  On 3/19/2015 at 10:43 PM, Tricks said:

They all loaded in fine and seem to be working. But I will double check. Can you show me an example?

 

Looks like cen added back the msiing semi colons. Well I just checked and our both files are same. :P

[cen]

Yea I fixed it :D

[Gr8]

  On 3/20/2015 at 12:30 PM, cen said:

Yea I fixed it :D

 

:ph34r:

[Le0Heart]

In forums, there should be an option against each profile / person to donate to his paypal etc based on his contribution. E.g. I feel like I should thank and appreciate what Cen did by donating 5-10 usd to him just as a token of appreciation for the good contribution he did for the general masses. I will pick up a few of these as well :)

 

Respect!!!

[cen]

I don't want/need any donations my man, just spreading the knowledge of running servers for the better part of two years now :)

[Richie]

All hail Saint Cen :p