SQL to show only vehicles?

[mandy561]

Anyone know a SQL query that would show only vehicles? :D

Thanks

[MuhKuh]

The database isn't designed to query vehicles correctly.

 

A correct way would be to create a vehicle white list table and check against the classname. There are some useful dayz database tools (e.g. Private Hive tools with 'tool_vehicle_pool' table).

 

If you only want to clean up vehicles:

 

DELETE FROM Object_DATA WHERE (CharacterID = 0 AND (Inventory = '[]' OR Inventory = '[[[],[]],[[],[]],[[],[]]]')) OR Damage > 0.5

 

-> Deletes all vehicles without owner and empty inventory or vehicles which are to heavy damaged

[LoadingSA]

The database isn't designed to query vehicles correctly.

 

A correct way would be to create a vehicle white list table and check against the classname. There are some useful dayz database tools (e.g. Private Hive tools with 'tool_vehicle_pool' table).

 

If you only want to clean up vehicles:

 

DELETE FROM Object_DATA WHERE (CharacterID = 0 AND (Inventory = '[]' OR Inventory = '[[[],[]],[[],[]],[[],[]]]')) OR Damage > 0.5

 

-> Deletes all vehicles without owner and empty inventory or vehicles which are to heavy damaged

 

This will also remove player built objects.

[Meowzors]

add a 'Hitpoints' NOT LIKE '[]' and that code will ignore the player built items

[hambeast]

I posted a query on another thread that should return only vehicles.  I have not had a chance to verify the results but the code is good.

 

edit: had a chance to run the query, here is a better version

SELECT * FROM
dayz_epoch.object_data OD
WHERE 1 = 1
  AND OD.inventory IS NOT NULL
  AND OD.inventory NOT LIKE '[]'
  AND OD.classname NOT LIKE '%storage%'
  AND OD.Classname NOT LIKE '%shed%'
  AND OD.Classname NOT LIKE '%shack%'
  AND OD.Classname NOT LIKE '%rack%'
  AND OD.Classname NOT LIKE '%crate%'
  AND OD.Classname NOT LIKE '%house%'

[nightmare]

SELECT * FROM 
`Object_DATA` 
WHERE `Classname` 
NOT REGEXP 'land|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';

[hambeast]

SELECT * FROM 
`Object_DATA` 
WHERE `Classname` 
NOT REGEXP 'land|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';

learn something new every day.  Not ashamed to admit I had no idea mysql had regex support.

[Sandbird]

Easiest and most secure way to bring out vehicles....vendor or map based:

 

This way was used looooong time ago before epoch...why they removed the allowedvehicles table is a mystery. Like this you can never go wrong.. Not to mention, all vehicles sqls are so easy to do after.

 

I mean i am so sorry @nightmare, yours looks perfectly fine....but why go from London -> Paris via Peru :D

[hambeast]

Easiest and most secure way to bring out vehicles....vendor or map based:

 

This way was used looooong time ago before epoch...why they removed the allowedvehicles table is a mystery. Like this you can never go wrong.. Not to mention, all vehicles sqls are so easy to do after.

 

I mean i am so sorry @nightmare, yours looks perfectly fine....but why go from London -> Paris via Peru :D

What engine?  I don't remember bliss ever having an allowedvehicles table.   There was a vehicle, instance_vehicle, and a world_vehicle table though.  Sometimes I long for the bliss schema.

[nightmare]

learn something new every day.  Not ashamed to admit I had no idea mysql had regex support.

 

i too..

 

 

 

I mean i am so sorry @nightmare, yours looks perfectly fine....but why go from London -> Paris via Peru :D

 

??

[Sandbird]

The so many "or" values i meant :)

 

Well i dont remember exactly which engine...but it was before that leaked Origins 1.7.1 files.

[Pro_Speedy]

SELECT * FROM 
`Object_DATA` 
WHERE `Classname` 
NOT REGEXP 'land|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';

 

Ok what would i add to this to delete old vehicles?

[Sandbird]

Ok what would i add to this to delete old vehicles?

 

replace SELECT * FROM  with DELETE FROM

[Pro_Speedy]

replace SELECT * FROM  with DELETE FROM

 

And i suppose i can just add like " And 'last modified' >5 days" 

 

?

 

Thanks

[Sandbird]

And i suppose i can just add like " And 'last modified' >5 days" 

 

?

 

Thanks

 

If adding that to the Select * from, yields the results you want, then yes.

Always do the select * from, before going to the delete method.

And always backup before :)

[hambeast]

This is the way I like to write my queries so I make sure I get the exact data I want.

 

tip: you can use "--" at the start of a line to comment in mysql.  Also, the WHERE 1 = 1 allows us to add/switch conditions by commenting lines without breaking your query.

 

Step 1: Write query to return rows you wish to delete like so:

SELECT * FROM
dayz_epoch.object_data OD
WHERE 1 = 1
AND OD.characterID = 1234

Step 2: Run Query and verify results

 

Step 3: Comment SELECT * line and add in DELETE FROM

DELETE FROM
-- SELECT * FROM
dayz_epoch.object_data OD
WHERE 1 = 1
AND OD.characterID = 1234

For more info on MySQL, I started with this tutorial quite a few years ago.  It covers the command line and uses PHP as well but it is a good primer.  http://www.tizag.com/mysqlTutorial/