Dismal Posted March 15, 2014 Report Share Posted March 15, 2014 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. Link to comment Share on other sites More sharing options...
0 Kivuli Posted March 15, 2014 Report Share Posted March 15, 2014 DELETE FROM object_data WHERE `CharacterID` = 0 AND DATE (`Datestamp`) < CURDATE() - INTERVAL 7 DAY That should do what you want. Link to comment Share on other sites More sharing options...
0 Dismal Posted March 15, 2014 Author Report Share Posted March 15, 2014 dont buildables also have character id of 0? Link to comment Share on other sites More sharing options...
0 Dismal Posted March 15, 2014 Author Report Share Posted March 15, 2014 and what about vehicles that no longer have keys in the server Link to comment Share on other sites More sharing options...
0 Kivuli Posted March 15, 2014 Report Share Posted March 15, 2014 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 1 Link to comment Share on other sites More sharing options...
0 Dismal Posted March 15, 2014 Author Report Share Posted March 15, 2014 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. Link to comment Share on other sites More sharing options...
0 Dismal Posted March 15, 2014 Author Report Share Posted March 15, 2014 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. Link to comment Share on other sites More sharing options...
0 Kivuli Posted March 16, 2014 Report Share Posted March 16, 2014 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 Link to comment Share on other sites More sharing options...
0 Dismal Posted March 16, 2014 Author Report Share Posted March 16, 2014 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? Link to comment Share on other sites More sharing options...
0 Dismal Posted March 16, 2014 Author Report Share Posted March 16, 2014 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. Link to comment Share on other sites More sharing options...
0 Dismal Posted March 16, 2014 Author Report Share Posted March 16, 2014 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. Link to comment Share on other sites More sharing options...
0 Kerbo Posted March 16, 2014 Report Share Posted March 16, 2014 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; Dismal 1 Link to comment Share on other sites More sharing options...
0 Kivuli Posted March 16, 2014 Report Share Posted March 16, 2014 Yep, that should do it. I was thinking in a hybrid of Arma script and SQL and forgot about the buildings not being updated. Link to comment Share on other sites More sharing options...
0 Dismal Posted March 16, 2014 Author Report Share Posted March 16, 2014 WOW i love that select option, thank you so much, you guys rock! Link to comment Share on other sites More sharing options...
0 Kerbo Posted March 17, 2014 Report Share Posted March 17, 2014 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. :) Link to comment Share on other sites More sharing options...
0 justchil Posted March 28, 2014 Report Share Posted March 28, 2014 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') Link to comment Share on other sites More sharing options...
Question
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.
Link to comment
Share on other sites
15 answers to this question
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now