SQL function to delete vehicles without a key

[vbawol]

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

[Skaronator]

Would be also fine

[maca134]

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]

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]

Thanks a lot!

[maca134]

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]

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]

@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]

The unlock version or delete version?

[Fank]

I prefer the unlock version

[Skaronator]

Add both, one for delete and one for unlock

[ghost]

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

[Skaronator]

haha^^

[maca134]

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]

-- 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]

Tested and work fine

[vbawol]

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]

ill do it

[Skaronator]

Add Both, one for delete one for unlock

[Fank]

sure

[vbawol]

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.
e2b66dad28c57b8a2ceed22fc4167c883bc8ac18

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

[vbawol]

thanks