sql queries

[Dismal]

Hey guys.

 

I am terrible with the SQL database and have had issues with getting my cleanup queries to work.

 

I'm looking for a few cleanup queries to remove ONLY vehicles with no keys on the server and ONLY vehicles that have not been updated in 7 days.

 

Everything i have found so far has been for all objects which removes peoples walls and buildables and what not.

 

If anyone could perhaps give me a few SQL queries that would help me i would appreciate it.

 

 

[Kivuli]

DELETE FROM object_data WHERE `CharacterID` = 0 AND DATE (`Datestamp`) < CURDATE() - INTERVAL 7 DAY

That should do what you want.

[Dismal]

dont buildables also have character id of 0?

[Dismal]

and what about vehicles that no longer have keys in the server

[Kivuli]

Buildables are stamped with the character ID of the person who built them. There is a way to do it for vehicles without keys present, but I'd have to look that one up. It involves doing a search of both the object_data and character_data tables for the key in question.

[Dismal]

yeah that would be very useful if you figure it out i would appreciate you posting it here.and thank you for the post about the character id.

[Dismal]

I have another question, Server vehicles don't have character ID's.

 

What about player bought vehicles? i want to remove bought vehicles that haven't been updated in 7 days as well. regardless of inventory.

[Kivuli]

If you want to remove ANY vehicle that hasn't been touched in 7 days, just remove the CharacterID = 0 part. It'd look like this:

DELETE FROM object_data WHERE DATE (`Datestamp`) < CURDATE() - INTERVAL 7 DAY;

As for removing vehicles that no longer have a key, you'll have to create an SQL event as described in this thread:

 

http://epochmod.com/forum/index.php?/topic/7065-cleaning-up-owned-vehicles-where-no-keys-exist/?hl=%2Bkeyless+%2Bvehicles#entry46188

[Dismal]

oh nice, i ran one like that kivuli and it removed some walls and stuff from peoples base, is there a way to tell it not to do walls and stuff? like an exception string?

[Dismal]

i never thought i would be so bad at this, i can't make heads or tales of that post with the delete none key vehicles stuff.

[Dismal]

I guess the issue i'm having, is i have a hosted server. i have access to the database and can make events, but for some reason everything i can find, is either failing and gives me errors or removes peoples walls and floors and stuff.

 

I cant seem to find an example of a sql event that will leave peoples bases alone, but remove server spawned AND bought vehicles that have not been updated in 7 days, regardless if they are locked or not and regardless if they have inventory.

 

Last time i ran something i it deleted all the walls and floors that had been built prior to the 7 day mark because those don't ever update the time stamp.

[Kerbo]

This should do the trick. You can test first with the SELECT and then if it looks OK run the second with DELETE.

 

SELECT * FROM
`Object_DATA`
WHERE
`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 `Object_DATA`.`LastUpdated` < NOW() - INTERVAL 7 DAY;

DELETE FROM
`Object_DATA`
WHERE
`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 `Object_DATA`.`LastUpdated` < NOW() - INTERVAL 7 DAY;

[Kivuli]

Yep, that should do it. I was thinking in a hybrid of Arma script and SQL and forgot about the buildings not being updated.

[Dismal]

WOW i love that select option, thank you so much, you guys rock!

[Kerbo]

WOW i love that select option, thank you so much, you guys rock!

Yes, it's always a good idea to check your DELETE with a SELECT first. :)

[justchil]

Anyone notice a few things like m240nest, parkbench, bagfenceround having charid's of 0?

 

I updated my query:

 

AND `Object_DATA`.`Classname` NOT IN ('M240Nest_DZ', 'ParkBench_DZ', 'BagFenceRound_DZ', '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')