SQL function to delete vehicles without a key #1007

Skaronator · 2014-01-23T15:53:14Z

No description provided.

vbawol · 2014-01-23T16:20:01Z

I think it would be better to just unlock vehicles if left unattended for xx number of days?

Skaronator · 2014-01-23T16:59:23Z

Would be also fine

maca134 · 2014-01-23T17:03:49Z

Here is a query to find how many keys for each vehicle are on the map, the only thing that needs fleshing out is the object exclusion list (wall, doors, etc). Also, not the fastest for queries but works.

SELECT
object_data.Classname,
(CASE
    WHEN object_data.CharacterID < 2501 THEN CONCAT('ItemKeyGreen', object_data.CharacterID)
    WHEN object_data.CharacterID < 5001 THEN CONCAT('ItemKeyRed', object_data.CharacterID - 2500)
    WHEN object_data.CharacterID < 7501 THEN CONCAT('ItemKeyBlue', object_data.CharacterID - 5000)
    WHEN object_data.CharacterID < 10001 THEN CONCAT('ItemKeyYellow', object_data.CharacterID - 7500)
    WHEN object_data.CharacterID < 12501 THEN CONCAT('ItemKeyBlack', object_data.CharacterID - 10000)
    ELSE 'ERROR'
END) as `KeyColour`,
(SELECT COUNT(*) FROM `character_data` WHERE `Alive` = '1' AND (`Inventory` LIKE CONCAT('%', KeyColour, '%') OR `Backpack` LIKE CONCAT('%', KeyColour, '%'))) AS InChar,
(SELECT COUNT(*) FROM `object_data` WHERE `Inventory` LIKE CONCAT('%', KeyColour, '%')) AS InVeh
FROM `object_data`
WHERE `CharacterID` <> '0' 
AND `CharacterID` <= 12500
AND `Classname` NOT LIKE '%Land%' 
AND `Classname` NOT LIKE '%VaultStorageLocked%' 
AND `Classname` NOT LIKE '%Cinder%' 
AND `Classname` NOT IN ('TentStorage', 'StorageShed_DZ', 'TentStorageDomed', 'LockboxStorageLocked', 'WoodShack_DZ')

Took 80 seconds to check 1000 vehicles ish

maca134 · 2014-01-23T17:22:40Z

SELECT
object_data.Classname,
(CASE
    WHEN object_data.CharacterID < 2501 THEN CONCAT('ItemKeyGreen', object_data.CharacterID)
    WHEN object_data.CharacterID < 5001 THEN CONCAT('ItemKeyRed', object_data.CharacterID - 2500)
    WHEN object_data.CharacterID < 7501 THEN CONCAT('ItemKeyBlue', object_data.CharacterID - 5000)
    WHEN object_data.CharacterID < 10001 THEN CONCAT('ItemKeyYellow', object_data.CharacterID - 7500)
    WHEN object_data.CharacterID < 12501 THEN CONCAT('ItemKeyBlack', object_data.CharacterID - 10000)
    ELSE 'ERROR'
END) as `KeyColour`,
(SELECT COUNT(*) FROM `character_data` WHERE `Alive` = '1' AND (`Inventory` LIKE CONCAT('%', KeyColour, '%') OR `Backpack` LIKE CONCAT('%', KeyColour, '%'))) AS InChar,
(SELECT COUNT(*) FROM `object_data` WHERE `Inventory` LIKE CONCAT('%', KeyColour, '%')) AS InVeh
FROM `object_data`
WHERE `CharacterID` <> '0'
AND `CharacterID` <= 12500
AND `Classname` NOT LIKE '%Land%'
AND `Classname` NOT LIKE '%VaultStorageLocked%'
AND `Classname` NOT LIKE '%Cinder%'
AND `Classname` NOT IN ('TentStorage', 'StorageShed_DZ', 'TentStorageDomed', 'TentStorageDomed2', 'WoodFloor_DZ', 'WoodLargeWallWin_DZ' 'LockboxStorageLocked', 'WoodShack_DZ', 'MetalFloor_DZ', 'WoodStairsSans_DZ', 'WoodFloorQuarter_DZ', 'WoodLargeWall_DZ', 'LockboxStorageLocked', 'WoodLargeWallWin_DZ','WoodFloorHalf_DZ','WoodSmallWallThird_DZ','WoodStairs_DZ')

Exclusion update thanks to Cen

Skaronator · 2014-01-23T17:51:08Z

Thanks a lot!

maca134 · 2014-01-23T17:59:46Z

Me and few from 'the' Skype convo have been fleshing it out abit:

SELECT
object_data.ObjectID,
object_data.ObjectUID,
object_data.Worldspace,
object_data.Classname,
(CASE
        WHEN object_data.CharacterID < 2501 THEN CONCAT('ItemKeyGreen', object_data.CharacterID)
        WHEN object_data.CharacterID < 5001 THEN CONCAT('ItemKeyRed', object_data.CharacterID - 2500)
        WHEN object_data.CharacterID < 7501 THEN CONCAT('ItemKeyBlue', object_data.CharacterID - 5000)
        WHEN object_data.CharacterID < 10001 THEN CONCAT('ItemKeyYellow', object_data.CharacterID - 7500)
        WHEN object_data.CharacterID < 12501 THEN CONCAT('ItemKeyBlack', object_data.CharacterID - 10000)
        ELSE 'ERROR'
END) AS `KeyColour`,
(SELECT COUNT(*) FROM `character_data` WHERE `Alive` = '1' AND (`Inventory` LIKE CONCAT('%', KeyColour, '%') OR `Backpack` LIKE CONCAT('%', KeyColour, '%'))) AS InChar,
(SELECT COUNT(*) FROM `object_data` WHERE `Inventory` LIKE CONCAT('%', KeyColour, '%')) AS InVeh
FROM `object_data`
WHERE `CharacterID` <> '0'
AND `CharacterID` <= 12500
AND `Classname` NOT LIKE 'Land%'
AND `Classname` NOT LIKE 'Cinder%'
AND `Classname` NOT LIKE 'Wood%'  
AND `Classname` NOT LIKE 'Metal%'
AND `Classname` NOT LIKE '%Storage%'
AND `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')

This will show how many keys exists in the char inventory/backpack and in vehicle invents. If you add

HAVING
InChar = 0 AND InVeh = 0

It will filter out non-key vehicles

I wouldnt feel confident in doing a delete with it quite yet tho

Fank · 2014-01-23T21:25:18Z

Here my UnlockVehicleWithoutKey Procedure

CREATE PROCEDURE `Proc_UnlockVehicleWithoutKey`()
BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE VehicleID INT;
    DECLARE ItemKey VARCHAR(17);
    DECLARE vehicleCursor CURSOR FOR
    SELECT
        ObjectID
        ,(CASE
            WHEN CharacterID < 2501 THEN CONCAT('ItemKeyGreen', CharacterID)
            WHEN CharacterID < 5001 THEN CONCAT('ItemKeyRed', CharacterID - 2500)
            WHEN CharacterID < 7501 THEN CONCAT('ItemKeyBlue', CharacterID - 5000)
            WHEN CharacterID < 10001 THEN CONCAT('ItemKeyYellow', CharacterID - 7500)
            WHEN CharacterID < 12501 THEN CONCAT('ItemKeyBlack', CharacterID - 10000)
            ELSE 'ERROR'
        END) as ItemKey
    FROM
        Object_DATA
    WHERE
        CharacterID <> 0
        AND CharacterID <= 12500
        AND Inventory <> '[]'
        AND Classname NOT LIKE 'Tent%'
        AND Classname NOT LIKE '%Locked'
        AND Classname NOT LIKE 'Land%'
        AND Classname NOT LIKE 'Cinder%'
        AND Classname NOT LIKE 'Wood%'  
        AND Classname NOT LIKE 'Metal%'
        AND Classname NOT LIKE '%Storage%'
        AND 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');
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;

    OPEN vehicleCursor;

    REPEAT
        FETCH vehicleCursor INTO VehicleID, ItemKey;

        IF
            (SELECT COUNT(*) FROM Character_DATA WHERE Inventory LIKE CONCAT('%"', ItemKey, '"%') OR Backpack LIKE CONCAT('%"', ItemKey, '"%')) = 0
            && (SELECT COUNT(*) FROM Object_DATA WHERE Inventory LIKE CONCAT('%"', ItemKey, '"%')) = 0
        THEN
            UPDATE
                Object_DATA
            SET
                CharacterID = 0
            WHERE
                ObjectID = VehicleID;
        END IF;

    UNTIL done END REPEAT;

    CLOSE vehicleCursor;
END

Skaronator · 2014-01-23T21:25:23Z

@maca134 Could you add this as a MySQL event via pull request?
https://github.com/vbawol/DayZ-Epoch/blob/master/Server%20Files/SQL/add_recommended_mysql_events.sql

maca134 · 2014-01-23T22:29:43Z

The unlock version or delete version?

Fank · 2014-01-23T22:57:36Z

I prefer the unlock version

Skaronator · 2014-01-24T11:55:30Z

Add both, one for delete and one for unlock

ghost · 2014-01-24T13:46:46Z

http://gsngaming.com/uploads/monthly_10_2013/post-241-0-81297100-1382474775.jpg

Skaronator · 2014-01-24T14:55:25Z

haha^^

maca134 · 2014-01-25T17:33:04Z

Im having trouble making the SQL function define working :( but the event should be fine once the function is in...
Fank, can u help?

# Find Vehicle Key Fund
# SELECT FindVehicleKeys(CharID/KeyID);


DROP FUNCTION IF EXISTS `FindVehicleKeys`;

DELIMITER ;;
CREATE FUNCTION `FindVehicleKeys` (`keyId` int) RETURNS int
BEGIN
    DECLARE totalKeys INT DEFAULT 0;
    DECLARE keyName VARCHAR(32) DEFAULT "";
    DECLARE keysInChar INT DEFAULT 0;
    DECLARE keysInObj INT DEFAULT 0;

    SET keyName = (CASE
        WHEN `keyId` < 2501 THEN CONCAT('ItemKeyGreen', `keyId`)
        WHEN `keyId` < 5001 THEN CONCAT('ItemKeyRed', `keyId` - 2500)
        WHEN `keyId` < 7501 THEN CONCAT('ItemKeyBlue', `keyId` - 5000)
        WHEN `keyId` < 10001 THEN CONCAT('ItemKeyYellow', `keyId` - 7500)
        WHEN `keyId` < 12501 THEN CONCAT('ItemKeyBlack', `keyId` - 10000)
        ELSE 'ERROR'
    END);

    SET keysInChar = (SELECT COUNT(*) FROM `character_data` WHERE `Alive` = '1' AND (`Inventory` LIKE CONCAT('%', keyName, '%') OR `Backpack` LIKE CONCAT('%', keyName, '%')));
    SET keysInObj = (SELECT COUNT(*) FROM `object_data` WHERE `Inventory` LIKE CONCAT('%', keyName, '%'));

    RETURN (keysInChar + keysInObj);
END;;
DELIMITER ;

# Unlock
CREATE DEFINER=`root`@`localhost` 
    EVENT `UnlockNonKeyVehicles`
        ON SCHEDULE AT '2014-01-25 17:00:20'
        ON COMPLETION NOT PRESERVE
    ENABLE
    DO
        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 FindVehicleKeys(object_data.CharacterID) = 0;

# Delete
CREATE DEFINER=`root`@`localhost` 
    EVENT `DeleteNonKeyVehicles`
        ON SCHEDULE AT '2014-01-25 17:00:20'
        ON COMPLETION NOT PRESERVE
    ENABLE
    DO
        DELETE FROM
            object_data
        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 FindVehicleKeys(object_data.CharacterID) = 0;

Fank · 2014-01-26T12:23:40Z

-- Find Vehicle Key Fund
-- SELECT FindVehicleKeys(CharID/KeyID);
DELIMITER $$
DROP FUNCTION IF EXISTS `FindVehicleKeysCount`$$
CREATE FUNCTION `FindVehicleKeysCount` (`keyId` INT) RETURNS INT
BEGIN
    DECLARE totalKeys INT DEFAULT 0;
    DECLARE keyName VARCHAR(32) DEFAULT "";
    DECLARE keysInChar INT DEFAULT 0;
    DECLARE keysInObj INT DEFAULT 0;

    SET keyName = (CASE
        WHEN `keyId` < 2501 THEN CONCAT('ItemKeyGreen', `keyId`)
        WHEN `keyId` < 5001 THEN CONCAT('ItemKeyRed', `keyId` - 2500)
        WHEN `keyId` < 7501 THEN CONCAT('ItemKeyBlue', `keyId` - 5000)
        WHEN `keyId` < 10001 THEN CONCAT('ItemKeyYellow', `keyId` - 7500)
        WHEN `keyId` < 12501 THEN CONCAT('ItemKeyBlack', `keyId` - 10000)
        ELSE 'ERROR'
    END);

    SET keysInChar = (SELECT COUNT(*) FROM `Character_DATA` WHERE `Alive` = '1' AND (`Inventory` LIKE CONCAT('%', keyName, '%') OR `Backpack` LIKE CONCAT('%', keyName, '%')));
    SET keysInObj = (SELECT COUNT(*) FROM `Object_DATA` WHERE `Inventory` LIKE CONCAT('%', keyName, '%'));

    RETURN (keysInChar + keysInObj);
END$$
DELIMITER ;

-- Unlock
DROP EVENT `UnlockNonKeyVehicles`;
CREATE EVENT `UnlockNonKeyVehicles`
        ON SCHEDULE AT NOW()
        ON COMPLETION NOT PRESERVE
    ENABLE
    DO
        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 FindVehicleKeys(object_data.CharacterID) = 0;

-- Delete
DROP EVENT `DeleteNonKeyVehicles`;
CREATE EVENT `DeleteNonKeyVehicles`
        ON SCHEDULE AT NOW()
        ON COMPLETION NOT PRESERVE
    ENABLE
    DO
        DELETE FROM
            object_data
        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 FindVehicleKeys(object_data.CharacterID) = 0;

Fank · 2014-01-26T13:15:35Z

Tested and work fine

vbawol · 2014-01-31T15:24:05Z

Please make a pull request to add this to here https://github.com/vbawol/DayZ-Epoch/blob/master/Server%20Files/SQL/add_recommended_mysql_events.sql or make another sql file for it.

Fank · 2014-01-31T19:38:22Z

ill do it

Skaronator · 2014-01-31T19:46:32Z

Add Both, one for delete one for unlock

Fank · 2014-01-31T19:51:26Z

sure

vbawol · 2014-02-01T19:37:16Z

The unlock event should work fine on a running server but the delete event should actually be a function instead of an event.

This would be so that the function can be called before server startup via a bat file if needed. If done via an event we will be deleting vehicles that are still active in the server.

Because of this and because we should not be running both events at the same time I removed the delete event.
e2b66da

@Fank could you re add this delete event as a function?

vbawol · 2014-02-01T23:13:59Z

thanks

vbawol closed this as completed Jan 31, 2014

vbawol reopened this Feb 1, 2014

vbawol closed this as completed Feb 1, 2014

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

SQL function to delete vehicles without a key #1007

SQL function to delete vehicles without a key #1007

Skaronator commented Jan 23, 2014

vbawol commented Jan 23, 2014

Skaronator commented Jan 23, 2014

maca134 commented Jan 23, 2014

maca134 commented Jan 23, 2014

Skaronator commented Jan 23, 2014

maca134 commented Jan 23, 2014

Fank commented Jan 23, 2014

Skaronator commented Jan 23, 2014

maca134 commented Jan 23, 2014

Fank commented Jan 23, 2014

Skaronator commented Jan 24, 2014

ghost commented Jan 24, 2014

Skaronator commented Jan 24, 2014

maca134 commented Jan 25, 2014

Fank commented Jan 26, 2014

Fank commented Jan 26, 2014

vbawol commented Jan 31, 2014

Fank commented Jan 31, 2014

Skaronator commented Jan 31, 2014

Fank commented Jan 31, 2014

vbawol commented Feb 1, 2014

vbawol commented Feb 1, 2014

SQL function to delete vehicles without a key #1007

SQL function to delete vehicles without a key #1007

Comments

Skaronator commented Jan 23, 2014

vbawol commented Jan 23, 2014

Skaronator commented Jan 23, 2014

maca134 commented Jan 23, 2014

maca134 commented Jan 23, 2014

Skaronator commented Jan 23, 2014

maca134 commented Jan 23, 2014

Fank commented Jan 23, 2014

Skaronator commented Jan 23, 2014

maca134 commented Jan 23, 2014

Fank commented Jan 23, 2014

Skaronator commented Jan 24, 2014

ghost commented Jan 24, 2014

Skaronator commented Jan 24, 2014

maca134 commented Jan 25, 2014

Fank commented Jan 26, 2014

Fank commented Jan 26, 2014

vbawol commented Jan 31, 2014

Fank commented Jan 31, 2014

Skaronator commented Jan 31, 2014

Fank commented Jan 31, 2014

vbawol commented Feb 1, 2014

vbawol commented Feb 1, 2014