SQL - Clean Old Bases

[Donnovan]

This configurable SQL code do that:

 

1 - Find all the plots

2 - Check all objects in the radius of each plot

3 - Count if there is any up to date object in the plot radius (a vault opened recently, a vehicle used recently, a wall/floor constructed recently, etc...)

4 - If there is no objects in the plot that have been used recently, the plot and its objects are considered dead

5 - Delete the dead plots and all its ojects.

 

OBS 1: To make the del happens, you need to uncomment the last line.

OBS 2: A backup of the deleted objects is made in each run.

 

TABLES CREATED ON THE PROCESS:

PLOT_FUNCTIONAL

PLOT_NOT_FUNCTIONAL

PLOT_NOT_FUNCTIONAL_OBJECTS

PLOT_NOT_FUNCTIONAL_BACKUP

 

FUNCTIONS CREATED ON THE PROCESS:

TwoPointsDistance

 

NICE CONSIDERATIONS:

This SQL in mainly for the ones who want to do base cleanup manually and have the automatic clean up and base mantain turned off.

-- ----------------------------------
-- KONFIG: PLOT RADIUS IN METERS ----
-- ----------------------------------
SET @plotRad = 60;

-- ------------------------------------------
-- KONFIG: MAXIMUM OLD ALLOWED IN DAYS ------
-- ------------------------------------------
SET @maxOld = 20;

-- -------------------------------------------------------------------------------
-- KONFIG: MINIMUM AMOUNT OF UP TO DATE OBJECTS TO CONSIDER THE PLOT ACTIVE ------
-- -------------------------------------------------------------------------------
SET @minObj = 1;

-- -------------------------------------
-- FUNCTION: DISTANCE OBJECT X PLOT ----
-- -------------------------------------
DROP FUNCTION IF EXISTS `TwoPointsDistance`;
DELIMITER ;;
CREATE FUNCTION `TwoPointsDistance`(`x1` DOUBLE,`y1` DOUBLE,`x2` DOUBLE,`y2` DOUBLE) RETURNS DOUBLE
BEGIN
    DECLARE plotDistance DOUBLE;
    SET plotDistance = POWER(POWER(`x2`-`x1`,2)+POWER(`y2`-`y1`,2),1/2);
    RETURN plotDistance;
END
;;
DELIMITER ;

-- ------------------
-- SQL: QUERYES -----
-- ------------------
DROP TABLE IF EXISTS `PLOT_FUNCTIONAL`;

CREATE TABLE `PLOT_FUNCTIONAL` AS
	SELECT `PlotID`, SUM(1) AS `Qtd`
	FROM (SELECT `ObjectID` AS `PlotID`, `Worldspace` AS `PlotWS` FROM `Object_DATA` WHERE `Classname` = 'Plastic_Pole_EP1_DZ') AS `Plots`, `Object_DATA`
	WHERE
		TwoPointsDistance(
			SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(`Worldspace`,'[',-1),',',2),',',+1)*1,
            SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(`Worldspace`,'[',-1),',',2),',',-1)*1,
            SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(`PlotWS`,'[',-1),',',2),',',+1)*1,
			SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(`PlotWS`,'[',-1),',',2),',',-1)*1
		) < @plotRad
		AND
		`LastUpdated` > DATE_SUB(CURRENT_TIMESTAMP,INTERVAL @maxOld DAY)
	GROUP BY `PlotID`
;

DROP TABLE IF EXISTS `PLOT_NOT_FUNCTIONAL`;

CREATE TABLE `PLOT_NOT_FUNCTIONAL` AS
	SELECT `ObjectID` AS `PlotID`, `Worldspace` AS `PlotWS` FROM `Object_DATA` WHERE `Classname` = 'Plastic_Pole_EP1_DZ' AND `ObjectID` NOT IN (SELECT `PlotID` FROM `PLOT_FUNCTIONAL` WHERE `Qtd` >= @minObj);
;

DROP TABLE IF EXISTS `PLOT_NOT_FUNCTIONAL_OBJECTS`;

CREATE TABLE `PLOT_NOT_FUNCTIONAL_OBJECTS` AS
	SELECT `Object_DATA`.*
	FROM `PLOT_NOT_FUNCTIONAL`, `Object_DATA`
	WHERE
		TwoPointsDistance(
			SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(`Worldspace`,'[',-1),',',2),',',+1)*1,
            SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(`Worldspace`,'[',-1),',',2),',',-1)*1,
            SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(`PlotWS`,'[',-1),',',2),',',+1)*1,
			SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(`PlotWS`,'[',-1),',',2),',',-1)*1
		) < @plotRad
	ORDER BY `LastUpdated` DESC
;

-- ------------------
-- SHOW RESULTS -----
-- ------------------
SELECT * FROM `PLOT_NOT_FUNCTIONAL_OBJECTS`;

-- ----------------------------
-- MAKE BACKUP BEFORE DEL -----
-- ----------------------------
DROP TABLE IF EXISTS `PLOT_NOT_FUNCTIONAL_BACKUP`;
CREATE TABLE `PLOT_NOT_FUNCTIONAL_BACKUP` AS SELECT * FROM `PLOT_NOT_FUNCTIONAL_OBJECTS`;

-- --------------------------------------------
-- DELETE OBJECTS ON NON FUNCTIONAL PLOTS -----
-- --------------------------------------------
-- DELETE FROM `Object_DATA` WHERE `ObjectID`IN (SELECT `ObjectID` FROM `PLOT_NOT_FUNCTIONAL_OBJECTS`);

[KingRaymond795]

awesome!! thanks for this