Jump to content
Donnovan

SQL - Clean Old Bases

Recommended Posts

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`);

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...

  • Advertisement
  • Supporters
  • Discord

×
×
  • Create New...