Push detection system

[IntinteDAO]

I think it's possible to create a push detection system with relative ease. It wouldn't be a perfect system, but an automated one. It would be very cool if there was a possible way to disable it from constants.php

Push means a situation where a user with less points transfers resources (or ships) to a user with more points, thus building him.

I think it is possible to introduce a restriction to some ship missions where the number of points is verified.

Additionally (if I think correctly) it will allow correct operation of the invitation system, which because of sending each other's ships didn't make sense and served for violations.

[koburk]

We need to keep in mind stuff like lending ships. This game allows players to get bigger because they're not alone in the universe ;).

My point is: I need to attack someone, but i need 2 more ships, so i ask for them to a player with less points than me, he transfers those 2 ships so i can attack, once the attack is done, i will send those ships back to the right owner.
That idea replaces groupal attacks, but allows even bigger attacks. Just saying, whilst developing this that's something that needs to be in mind or make a rule against it directly.

[koburk]

Just need to be tested in order to check possible errors/fixes. It includes pushing detection and excludes payments (normal trades), selling ships and lending ships.
Pending to set resource costs based on SteemNova currency, I've used 4:2:1 for development...

Cheers!

CREATE DEFINER=`root`@`localhost` PROCEDURE `fleets_to_hist`()
BEGIN
	DECLARE v_now DATETIME;
    DECLARE v_days,v_id,v_mission,v_owner,v_target_owner,v_elementID INT;
    DECLARE v_fleet_array TEXT;
    DECLARE METAL,CRYSTAL,DEUTERIUM,C_METAL,C_CRYSTAL,C_DEUTERIUM,TOTAL,C_TOTAL,V_TOTAL INT DEFAULT 0;
    DECLARE finished INTEGER DEFAULT 0;
	DECLARE CURSOR_JUDGE CURSOR FOR 
		SELECT `id`,`fleet_mission`,`fleet_owner`,`fleet_target_owner`,`fleet_array`,`fleet_resource_metal`,`fleet_resource_crystal`,`fleet_resource_deuterium` 
			FROM `uni1_fleets_hist` 
				WHERE `rank_fleet_owner` < `rank_fleet_target_owner` 
                AND `date` < NOW() - INTERVAL v_days DAY
                AND `checked` = 0;
	DECLARE CURSOR_PRICES CURSOR FOR
		SELECT elementID FROM uni1_vars where class = 200;
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;
    set v_now = now();/* Insert date */
	SET v_days = 7;/* Days allowed to return ships/resources */
    
    /* MINIMUM RATES */
    SET METAL = 1;
    SET CRYSTAL = 2;
    SET DEUTERIUM = 4;
    
	CREATE TABLE IF NOT EXISTS `uni1_fleets_hist` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `date` datetime default null,
  `rank_fleet_owner` int(11) unsigned NOT NULL,
  `rank_fleet_target_owner` int(11) unsigned NOT NULL,
  `fleet_id` bigint(11) unsigned NOT NULL DEFAULT 0,
  `fleet_owner` int(11) unsigned NOT NULL DEFAULT '0',
  `fleet_mission` tinyint(3) unsigned NOT NULL DEFAULT '3',
  `fleet_amount` bigint(20) unsigned NOT NULL DEFAULT '0',
  `fleet_array` text,
  `fleet_universe` tinyint(3) unsigned NOT NULL,
  `fleet_start_time` int(11) NOT NULL DEFAULT '0',
  `fleet_start_id` int(11) unsigned NOT NULL,
  `fleet_start_galaxy` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `fleet_start_system` smallint(5) unsigned NOT NULL DEFAULT '0',
  `fleet_start_planet` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `fleet_start_type` tinyint(3) unsigned NOT NULL DEFAULT '1',
  `fleet_end_time` int(11) NOT NULL DEFAULT '0',
  `fleet_end_stay` int(11) NOT NULL DEFAULT '0',
  `fleet_end_id` int(11) unsigned NOT NULL,
  `fleet_end_galaxy` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `fleet_end_system` smallint(5) unsigned NOT NULL DEFAULT '0',
  `fleet_end_planet` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `fleet_end_type` tinyint(3) unsigned NOT NULL DEFAULT '1',
  `fleet_target_obj` smallint(3) unsigned NOT NULL DEFAULT '0',
  `fleet_resource_metal` double(50,0) unsigned NOT NULL DEFAULT '0',
  `fleet_resource_crystal` double(50,0) unsigned NOT NULL DEFAULT '0',
  `fleet_resource_deuterium` double(50,0) unsigned NOT NULL DEFAULT '0',
  `fleet_resource_darkmatter` double(50,0) unsigned NOT NULL DEFAULT '0',
  `fleet_wanted_resource` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `fleet_wanted_resource_amount` double(50,0) unsigned NOT NULL DEFAULT '0',
  `fleet_no_m_return` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `fleet_target_owner` int(11) unsigned NOT NULL DEFAULT '0',
  `fleet_group` int(10) unsigned NOT NULL DEFAULT '0',
  `fleet_mess` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `start_time` int(11) DEFAULT NULL,
  `fleet_busy` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `hasCanceled` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `checked` tinyint(1) unsigned NOT NULL DEFAULT '0', 
  /*
	checked = 0 default
    checked = 1 everything ok
    checked = 2 this mission might be against the rules :(
  */
  PRIMARY KEY (`id`),
  KEY `fleet_target_owner` (`fleet_target_owner`,`fleet_mission`),
  KEY `fleet_owner` (`fleet_owner`,`fleet_mission`),
  KEY `fleet_mission` (`fleet_mission`)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

	/* Insert Suspicious FLEETS */
	-- mission 3  = transport
    -- mission 17 = transfer
	INSERT INTO `uni1_fleets_hist`
			(`date`,`fleet_id`,`fleet_owner`,`fleet_mission`,`fleet_amount`,`fleet_array`,`fleet_universe`,`fleet_start_time`,`fleet_start_id`,`fleet_start_galaxy`,`fleet_start_system`,`fleet_start_planet`,`fleet_start_type`,`fleet_end_time`,`fleet_end_stay`,`fleet_end_id`,`fleet_end_galaxy`,`fleet_end_system`,`fleet_end_planet`,`fleet_end_type`,`fleet_target_obj`,`fleet_resource_metal`,`fleet_resource_crystal`,`fleet_resource_deuterium`,`fleet_resource_darkmatter`,`fleet_wanted_resource`,`fleet_wanted_resource_amount`,`fleet_no_m_return`,`fleet_target_owner`,`fleet_group`,`fleet_mess`,`start_time`,`fleet_busy`,`hasCanceled`,`rank_fleet_owner`,`rank_fleet_target_owner`)
	SELECT 	`date`,`fleet_id`,`fleet_owner`,`fleet_mission`,`fleet_amount`,`fleet_array`,`fleet_universe`,`fleet_start_time`,`fleet_start_id`,`fleet_start_galaxy`,`fleet_start_system`,`fleet_start_planet`,`fleet_start_type`,`fleet_end_time`,`fleet_end_stay`,`fleet_end_id`,`fleet_end_galaxy`,`fleet_end_system`,`fleet_end_planet`,`fleet_end_type`,`fleet_target_obj`,`fleet_resource_metal`,`fleet_resource_crystal`,`fleet_resource_deuterium`,`fleet_resource_darkmatter`,`fleet_wanted_resource`,`fleet_wanted_resource_amount`,`fleet_no_m_return`,`fleet_target_owner`,`fleet_group`,`fleet_mess`,`start_time`,`fleet_busy`,`hasCanceled`,`CORE`.`total_rank`,`usu`.`total_rank`
    FROM (
	  SELECT v_now as `date`,`uf`.`fleet_id` as `fleet_id`,`uf`.`fleet_owner` as `fleet_owner`,`uf`.`fleet_mission` as `fleet_mission`,`uf`.`fleet_amount` as `fleet_amount`,`uf`.`fleet_array` as `fleet_array`,`uf`.`fleet_universe` as `fleet_universe`,`uf`.`fleet_start_time` as `fleet_start_time`,`uf`.`fleet_start_id` as `fleet_start_id`,`uf`.`fleet_start_galaxy` as `fleet_start_galaxy`,`uf`.`fleet_start_system` as `fleet_start_system`,`uf`.`fleet_start_planet` as `fleet_start_planet`,`uf`.`fleet_start_type` as `fleet_start_type`,`uf`.`fleet_end_time` as `fleet_end_time`,`uf`.`fleet_end_stay` as `fleet_end_stay`,`uf`.`fleet_end_id` as `fleet_end_id`,`uf`.`fleet_end_galaxy` as `fleet_end_galaxy`,`uf`.`fleet_end_system` as `fleet_end_system`,`uf`.`fleet_end_planet` as `fleet_end_planet`,`uf`.`fleet_end_type` as `fleet_end_type`,`uf`.`fleet_target_obj` as `fleet_target_obj`,`uf`.`fleet_resource_metal` as `fleet_resource_metal`,`uf`.`fleet_resource_crystal` as `fleet_resource_crystal`,`uf`.`fleet_resource_deuterium` as `fleet_resource_deuterium`,`uf`.`fleet_resource_darkmatter`,`uf`.`fleet_wanted_resource` as `fleet_wanted_resource`,`uf`.`fleet_wanted_resource_amount` as `fleet_wanted_resource_amount`,`uf`.`fleet_no_m_return` as `fleet_no_m_return`,`uf`.`fleet_target_owner` as `fleet_target_owner`,`uf`.`fleet_group` as `fleet_group`,`uf`.`fleet_mess` as `fleet_mess`,`uf`.`start_time` as `start_time`,`uf`.`fleet_busy` as `fleet_busy`,`uf`.`hasCanceled` as `hasCanceled`,`us`.`total_rank` as `total_rank`
		FROM `uni1_fleets` `uf` 
			INNER JOIN `uni1_statpoints` `us`
				ON `us`.`id_owner` = `uf`.`fleet_owner`
                AND `us`.`stat_type` = 1
			LEFT JOIN `uni1_fleets_hist` `ufh`
				ON `uf`.`fleet_id` = `ufh`.`fleet_id`
		WHERE `ufh`.`fleet_id` IS NULL AND (`uf`.`fleet_mission` = 3 OR `uf`.`fleet_mission` = 17)
        )`CORE`
        INNER JOIN `uni1_statpoints` `usu`
        ON `CORE`.`fleet_target_owner` = `usu`.`id_owner`
        AND `usu`.`stat_type` = 1;
	
    /* CHECK WHO'S NOT PAYING ^^ */
	OPEN CURSOR_JUDGE;
     DISCERN: LOOP
						
        FETCH CURSOR_JUDGE INTO v_id,v_mission,v_owner,v_target_owner,v_fleet_array,C_METAL,C_CRYSTAL,C_DEUTERIUM;
        IF finished = 1 THEN 
            LEAVE DISCERN;
        END IF;
        -- GET INFO
        if (SELECT count(*) FROM `uni1_fleets_hist` 
			WHERE `checked` = 0 
			AND `fleet_owner` = v_target_owner 
			AND `fleet_target_owner` = v_owner 
			AND `fleet_mission` = 17
			AND `fleet_array` = v_fleet_array) > 0 then
            
			-- Returning ships
            UPDATE `uni1_fleets_hist` SET `checked` = 1 
				WHERE `id` = (SELECT `id`
								FROM `uni1_fleets_hist` 
									WHERE `checked` = 0 
										AND `fleet_owner` = v_target_owner 
										AND `fleet_target_owner` = v_owner 
										AND `fleet_mission` = 17
										AND `fleet_array` = v_fleet_array
									LIMIT 1)
				OR `id` = v_id;
		ELSEIF (SELECT count(*) FROM `uni1_fleets_hist` 
				WHERE `checked` = 0 
				AND `fleet_owner` = v_target_owner 
				AND `fleet_target_owner` = v_owner 
				AND `fleet_mission` = 3 ) > 0 then
			-- Selling ships
			OPEN CURSOR_PRICES;
			 ASK_PRICE: LOOP
								
				FETCH CURSOR_PRICES INTO v_elementID;
				IF finished = 1 THEN 
					LEAVE ASK_PRICE;
				END IF;
				
				-- GET FLEET SENT COST
				SELECT substring(v_fleet_array,LOCATE(v_elementID,v_fleet_array)+4,LENGTH(v_fleet_array)-LOCATE(v_elementID,v_fleet_array)) INTO @B;
				IF (LOCATE(':',@B)-1) > 0 THEN
					SET @AMOUNT = (SELECT SUBSTRING(@B,1,LOCATE(':',@B)-1));
				ELSE
					SET @AMOUNT = (SELECT SUBSTRING(@B,1,LENGTH(@B)));
				END IF;
				
				SET V_TOTAL = V_TOTAL + (SELECT @AMOUNT * `cost901` * METAL + @AMOUNT * `cost902` * CRYSTAL + @AMOUNT * `cost903` * DEUTERIUM FROM uni1_vars WHERE `elementID` = v_elementID);
				
			 END LOOP ASK_PRICE;
			CLOSE CURSOR_PRICES;
			END IF;
            
		SET finished = 0;
    
		-- CHECK FOR PAYMENTS >= THAN SENT FLEET COST
		IF (SELECT count(*) FROM `uni1_fleets_hist` 
				WHERE `checked` = 0 
				AND `fleet_owner` = v_target_owner 
				AND `fleet_target_owner` = v_owner 
				AND `fleet_mission` = 3
                AND (`fleet_resource_metal` * METAL + `fleet_resource_crystal` * CRYSTAL + `fleet_resource_deuterium` * DEUTERIUM) >= V_TOTAL) THEN
                
			UPDATE `uni1_fleets_hist` SET `checked` = 1 WHERE `id` = v_id;
            
            UPDATE `uni1_fleets_hist` SET `checked` = 1 WHERE `id` = (SELECT `id` FROM `uni1_fleets_hist` 
				WHERE `checked` = 0 
				AND `fleet_owner` = v_target_owner 
				AND `fleet_target_owner` = v_owner 
				AND `fleet_mission` = 3
                AND (`fleet_resource_metal` * METAL + `fleet_resource_crystal` * CRYSTAL + `fleet_resource_deuterium` * DEUTERIUM) >= V_TOTAL
                ORDER BY (`fleet_resource_metal` * METAL + `fleet_resource_crystal` * CRYSTAL + `fleet_resource_deuterium` * DEUTERIUM) ASC
                LIMIT 1);
        ELSE
			UPDATE `uni1_fleets_hist` SET `checked` = 2 WHERE `id` = v_id;
        END IF;
        
  
        if (SELECT count(*) FROM `uni1_fleets_hist` 
			WHERE `date` < NOW() - INTERVAL v_days DAY 
			AND `checked` = 0 
			AND `fleet_owner` = v_target_owner 
			AND `fleet_target_owner` = v_owner 
			AND `fleet_mission` = 3
			AND (`fleet_resource_metal` * METAL + `fleet_resource_crystal` * CRYSTAL + `fleet_resource_deuterium` * DEUTERIUM) >= (C_METAL * METAL + C_CRYSTAL * CRYSTAL + C_DEUTERIUM * DEUTERIUM)) > 0 then
			-- trade
            UPDATE `uni1_fleets_hist` SET `checked` = 1 
				WHERE `id` = (SELECT `id` FROM `uni1_fleets_hist` 
								WHERE `date` < NOW() - INTERVAL v_days DAY 
								AND `checked` = 0 
								AND `fleet_owner` = v_target_owner 
								AND `fleet_target_owner` = v_owner 
								AND `fleet_mission` = 3
								AND (`fleet_resource_metal` * METAL + `fleet_resource_crystal` * CRYSTAL + `fleet_resource_deuterium` * DEUTERIUM) >= (C_METAL * METAL + C_CRYSTAL * CRYSTAL + C_DEUTERIUM * DEUTERIUM));
		ELSE
            UPDATE `uni1_fleets_hist` SET `checked` = 2 WHERE `id` = v_id;
        END IF;
        
     END LOOP DISCERN;
    CLOSE CURSOR_JUDGE;

END

[koburk]

@IntinteDAO any plans to implement this ?

[IntinteDAO]

Well, you have to check if the code is being executed on hostings. In my opinion, this kind of simple things can be done in PHP.

But I'll see. I think the code should be as universal as possible.

[koburk]

haha, up to you.
I much rather have everything on sql side