maca134

Database Tweaks - Delayed Deleting [NEEDS TESTING]

4 posts in this topic

 

Here is a exile.ini for extdb that "should" add delayed deleting of stuff. When something is deleted, it gets a deleted_at timestamp. These are then cleaned up after x days based on what your clean times are set too. (not the best at explaining shit)

Mean you should be able to undelete/unkill base stuff and players.

The deleted player cleanup happens at the same time as the vehicle cleanup.

I have not tested this properly yet so please be aware!

You also need to add a 5 columns to various tables:

ALTER TABLE `player`
	ADD COLUMN `deleted_at` DATETIME NULL DEFAULT NULL AFTER `last_updated_at`;

ALTER TABLE `construction`
	ADD COLUMN `deleted_at` DATETIME NULL DEFAULT NULL AFTER `last_updated_at`;

ALTER TABLE `container`
	ADD COLUMN `deleted_at` DATETIME NULL DEFAULT NULL AFTER `territory_id`;

ALTER TABLE `vehicle`
	ADD COLUMN `deleted_at` DATETIME NULL DEFAULT NULL AFTER `pin_code`;

ALTER TABLE `territory`
	ADD COLUMN `deleted_at` DATETIME NULL DEFAULT NULL AFTER `moderators`;

exile.ini

Spoiler

; SQL queries to add delete at columns to needed tables
;	ALTER TABLE `player`
;		ADD COLUMN `deleted_at` DATETIME NULL DEFAULT NULL AFTER `last_updated_at`;
;	
;	ALTER TABLE `construction`
;		ADD COLUMN `deleted_at` DATETIME NULL DEFAULT NULL AFTER `last_updated_at`;
;	
;	ALTER TABLE `container`
;		ADD COLUMN `deleted_at` DATETIME NULL DEFAULT NULL AFTER `territory_id`;
;	
;	ALTER TABLE `vehicle`
;		ADD COLUMN `deleted_at` DATETIME NULL DEFAULT NULL AFTER `pin_code`;
;	
;	ALTER TABLE `territory`
;		ADD COLUMN `deleted_at` DATETIME NULL DEFAULT NULL AFTER `moderators`;

[Default]
Version = 8
Number of Inputs = 0
Sanitize Input Value Check = false
Sanitize Output Value Check = false
Prepared Statement Cache = true
Return InsertID = false
Strip = true
Strip Chars Action = STRIP
Strip Chars = \/\|;{}<>\'
Strip Custom Chars = \/\|;{}<>\'

;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
;; Account related queries
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;

[isKnownAccount]
SQL1_1 = SELECT CASE WHEN EXISTS(SELECT uid FROM account WHERE uid = ?) THEN 'true' ELSE 'false' END
Number of Inputs = 1
SQL1_INPUTS = 1 
OUTPUT = 1

[createAccount]
SQL1_1 = INSERT INTO account SET uid = ?, name = ?
Number Of Inputs = 2
SQL1_INPUTS = 1,2

[startAccountSession]
SQL1_1 = UPDATE account SET name = ?, last_connect_at = NOW(), total_connections = total_connections + 1 WHERE uid = ?
Number Of Inputs = 2
SQL1_INPUTS = 2,1

[endAccountSession]
SQL1_1 = UPDATE account SET last_disconnect_at = NOW() WHERE uid = ?
Number Of Inputs = 1
SQL1_INPUTS = 1

[getAccountStats]
SQL1_1 = SELECT a.money, a.score, a.kills, a.deaths, a.clan_id, c.name FROM account a LEFT JOIN clan c ON c.id = a.clan_id WHERE a.uid = ?
Number of Inputs = 1
SQL1_INPUTS = 1 
OUTPUT = 1,2,3,4,5,6-STRING

[addAccountKill]
SQL1_1 = UPDATE account SET kills = kills + 1 WHERE uid = ?
Number Of Inputs = 1
SQL1_INPUTS = 1

[addAccountDeath]
SQL1_1 = UPDATE account SET deaths = deaths + 1 WHERE uid = ?
Number Of Inputs = 1
SQL1_INPUTS = 1

[getAccountMoney]
SQL1_1 = SELECT money FROM account WHERE uid = ?
Number of Inputs = 1
SQL1_INPUTS = 1 
OUTPUT = 1

[setAccountMoney]
SQL1_1 = UPDATE account SET money = ? WHERE uid = ?
Number of Inputs = 2
SQL1_INPUTS = 1,2 

[setAccountMoneyAndRespect]
SQL1_1 = UPDATE account SET money = ?, score = ? WHERE uid = ?
Number of Inputs = 3
SQL1_INPUTS = 1,2,3 

[modifyAccountMoney]
SQL1_1 = UPDATE account SET money = money + ? WHERE uid = ?
Number of Inputs = 2
SQL1_INPUTS = 1,2 

[getAccountScore]
SQL1_1 = SELECT score FROM account WHERE uid = ?
Number of Inputs = 1
SQL1_INPUTS = 1 
OUTPUT = 1

[setAccountScore]
SQL1_1 = UPDATE account SET score = ? WHERE uid = ?
Number of Inputs = 2
SQL1_INPUTS = 1,2 

[modifyAccountScore]
SQL1_1 = UPDATE account SET score = score + ? WHERE uid = ?
Number of Inputs = 2
SQL1_INPUTS = 1,2 

;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
;; Player related queries
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;

[hasAlivePlayer]
SQL1_1 = SELECT CASE WHEN EXISTS(SELECT account_uid FROM player WHERE account_uid = ? AND delete_at = null) THEN 'true' ELSE 'false' END
Number of Inputs = 1
SQL1_INPUTS = 1

[createPlayer]
SQL1_1 = INSERT INTO player SET account_uid = ?, name = ?
Number Of Inputs = 2
SQL1_INPUTS = 1,2
Return InsertID = true

[insertPlayerHistory]
SQL1_1 = INSERT INTO player_history SET account_uid = ?, name = ?, position_x = ?, position_y = ?, position_z = ?
Number Of Inputs = 5
SQL1_INPUTS = 1,2,3,4,5

[deletePlayer]
SQL1_1 = UPDATE player SET deleted_at = NOW() WHERE id = ?
Number Of Inputs = 1
SQL1_INPUTS = 1

[loadPlayer]
SQL1_1 = SELECT p.id,
SQL1_2 = p.name,
SQL1_3 = p.account_uid,
SQL1_4 = p.damage,
SQL1_5 = p.hunger,
SQL1_6 = p.thirst,
SQL1_7 = p.alcohol,
SQL1_8 = p.oxygen_remaining,
SQL1_9 = p.bleeding_remaining,
SQL1_10 = p.hitpoints,
SQL1_11 = p.direction,
SQL1_12 = p.position_x,
SQL1_13 = p.position_y,
SQL1_14 = p.position_z,
SQL1_15 = p.assigned_items,
SQL1_16 = p.backpack,
SQL1_17 = p.backpack_items,
SQL1_18 = p.backpack_magazines,
SQL1_19 = p.backpack_weapons,
SQL1_20 = p.current_weapon,
SQL1_21 = p.goggles,
SQL1_22 = p.handgun_items,
SQL1_23 = p.handgun_weapon,
SQL1_24 = p.headgear,
SQL1_25 = p.binocular,
SQL1_26 = p.loaded_magazines,
SQL1_27 = p.primary_weapon,
SQL1_28 = p.primary_weapon_items,
SQL1_29 = p.secondary_weapon,
SQL1_30 = p.secondary_weapon_items,
SQL1_31 = p.uniform,
SQL1_32 = p.uniform_items,
SQL1_33 = p.uniform_magazines,
SQL1_34 = p.uniform_weapons,
SQL1_35 = p.vest,
SQL1_36 = p.vest_items,
SQL1_37 = p.vest_magazines,
SQL1_38 = p.vest_weapons,
SQL1_39 = a.money,
SQL1_40 = a.score,
SQL1_41 = a.kills,
SQL1_42 = a.deaths,
SQL1_43 = c.name
SQL1_44 = FROM player p
SQL1_45 = INNER JOIN account a
SQL1_46 = ON a.uid = p.account_uid
SQL1_47 = LEFT JOIN clan c
SQL1_48 = ON c.id = a.clan_id
SQL1_49 = WHERE p.account_uid = ? AND p.delete_at = null
Number of Inputs = 1
SQL1_INPUTS = 1
SQL2_INPUTS = 1
OUTPUT = 1,2-STRING,3-STRING,4,5,6,7,8,9,10,11,12,13,14,15,16-STRING,17,18,19,20-STRING,21-STRING,22,23-STRING,24-STRING,25-STRING,26,27-STRING,28,29-STRING,30,31-STRING,32,33,34,35-STRING,36,37,38,39,40,41,42,43-STRING

[updatePlayer]
SQL1_1 = UPDATE player SET
SQL1_2 = name = ?,
SQL1_3 = damage  = ?,
SQL1_4 = hunger = ?,
SQL1_5 = thirst = ?,
SQL1_6 = alcohol = ?,
SQL1_7 = oxygen_remaining  = ?,
SQL1_8 = bleeding_remaining = ?,
SQL1_9 = hitpoints = ?,
SQL1_10 = direction = ?,
SQL1_11 = position_x = ?,
SQL1_12 = position_y = ?,
SQL1_13 = position_z = ?,
SQL1_14 = assigned_items = ?,
SQL1_15 = backpack = ?,
SQL1_16 = backpack_items = ?,
SQL1_17 = backpack_magazines = ?,
SQL1_18 = backpack_weapons = ?,
SQL1_19 = current_weapon = ?,
SQL1_20 = goggles = ?,
SQL1_21 = handgun_items = ?,
SQL1_22 = handgun_weapon = ?,
SQL1_23 = headgear = ?,
SQL1_24 = binocular = ?,
SQL1_25 = loaded_magazines = ?,
SQL1_26 = primary_weapon = ?,
SQL1_27 = primary_weapon_items = ?,
SQL1_28 = secondary_weapon = ?,
SQL1_29 = secondary_weapon_items = ?,
SQL1_30 = uniform = ?,
SQL1_31 = uniform_items = ?,
SQL1_32 = uniform_magazines = ?,
SQL1_33 = uniform_weapons = ?,
SQL1_34 = vest = ?,
SQL1_35 = vest_items = ?,
SQL1_36 = vest_magazines = ?,
SQL1_37 = vest_weapons = ?
SQL1_38 = WHERE id = ?
Number Of Inputs = 37
SQL1_INPUTS = 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37

;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
;; Vehicle related queries
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;

[insertVehicle]
SQL1_1 = INSERT INTO vehicle SET 
SQL1_2 = class = ?, 
SQL1_3 = account_uid = ?,
SQL1_4 = is_locked = ?,
SQL1_5 = position_x = ?,
SQL1_6 = position_y = ?,
SQL1_7 = position_z = ?,
SQL1_8 = direction_x = ?,
SQL1_9 = direction_y = ?,
SQL1_10 = direction_z = ?,
SQL1_11 = up_x = ?,
SQL1_12 = up_y = ?,
SQL1_13 = up_z = ?,
SQL1_14 = pin_code = ?
Number of Inputs = 13
SQL1_INPUTS = 1,2,3,4,5,6,7,8,9,10,11,12,13
Return InsertID = true

[deleteVehicle]
SQL1_1 = UPDATE vehicle SET deleted_at = NOW() WHERE id = ?
Number Of Inputs = 1
SQL1_INPUTS = 1

[loadVehicleIdPage]
SQL1_1 = SELECT id FROM vehicle WHERE delete_at = null LIMIT ?,?
Number Of Inputs = 2
SQL1_INPUTS = 1,2
OUTPUT = 1

[loadVehicle]
SQL1_1 = SELECT id,class,spawned_at,account_uid,is_locked,fuel,damage,hitpoints,position_x,position_y,position_z,direction_x,direction_y,direction_z,up_x,up_y,up_z,cargo_items,cargo_magazines,cargo_weapons,pin_code FROM vehicle WHERE id = ?
Number Of Inputs = 1
SQL1_INPUTS = 1
OUTPUT = 1,2-STRING,3-STRING,4-STRING,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21-STRING

[loadVehicleContainer]
SQL1_1 = SELECT cargo_container FROM vehicle WHERE id = ?
Number Of Inputs = 1
SQL1_INPUTS = 1
OUTPUT = 1

[updateVehicle]
SQL1_1 = UPDATE vehicle SET 
SQL1_2 = is_locked = ?, 
SQL1_3 = fuel = ?, 
SQL1_4 = damage = ?, 
SQL1_5 = hitpoints = ?,
SQL1_6 = position_x = ?,
SQL1_7 = position_y = ?,
SQL1_8 = position_z = ?,
SQL1_9 = direction_x = ?,
SQL1_10 = direction_y = ?,
SQL1_11 = direction_z = ?,
SQL1_12 = up_x = ?,
SQL1_13 = up_y = ?,
SQL1_14 = up_z = ?,
SQL1_15 = cargo_items = ?,
SQL1_16 = cargo_magazines = ?,
SQL1_17 = cargo_weapons = ?,
SQL1_18 = cargo_container = ?
SQL1_19 = WHERE id = ?
Number of Inputs = 18
SQL1_INPUTS = 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18

[updateVehicleClass]
SQL1_1 = UPDATE vehicle SET class = ? WHERE id = ?
Number of Inputs = 2
SQL1_INPUTS = 1,2

[vehicleSetPinCode]
SQL1_1 = UPDATE vehicle SET pin_code = ? WHERE id = ?
Number of Inputs = 2
SQL1_INPUTS = 1,2


;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
;; Construction related queries
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;

[insertConstruction]
SQL1_1 = INSERT INTO construction SET 
SQL1_2 = class = ?, 
SQL1_3 = account_uid = ?, 
SQL1_4 = position_x = ?,
SQL1_5 = position_y = ?,
SQL1_6 = position_z = ?,
SQL1_7 = direction_x = ?,
SQL1_8 = direction_y = ?,
SQL1_9 = direction_z = ?,
SQL1_10 = up_x = ?,
SQL1_11 = up_y = ?,
SQL1_12 = up_z = ?,
SQL1_13 = territory_id = $CUSTOM_1$
Number of Inputs = 11
Number of Custom Inputs = 1
SQL1_INPUTS = 1,2,3,4,5,6,7,8,9,10,11
Return InsertID = true

[deleteConstruction]
SQL1_1 = UPDATE construction SET deleted_at = NOW() WHERE id = ?
Number Of Inputs = 1
SQL1_INPUTS = 1 

[loadConstructionIdPage]
SQL1_1 = SELECT id FROM construction WHERE delete_at = null LIMIT ?,?
Number Of Inputs = 2
SQL1_INPUTS = 1,2
OUTPUT = 1

[loadConstruction]
SQL1_1 = SELECT id,class,account_uid,spawned_at,position_x,position_y,position_z,direction_x,direction_y,direction_z,up_x,up_y,up_z,is_locked,pin_code FROM construction WHERE id = ?
Number Of Inputs = 1
SQL1_INPUTS = 1 
OUTPUT = 1,2-STRING,3-STRING,4-STRING,5,6,7,8,9,10,11,12,13,14,15-STRING

[countConstruction]
SQL1_1 = SELECT COUNT(*) FROM construction WHERE delete_at = null
Number Of Inputs = 0
OUTPUT = 1

[constructionSetPinCode]
SQL1_1 = UPDATE construction SET pin_code= ? WHERE id = ?
Number of Inputs = 2
SQL1_INPUTS = 1,2

[upgradeObject]
SQL1_1 = UPDATE construction SET class=? WHERE id = ?
Number of Inputs = 2
SQL1_INPUTS = 1,2

[addDoorLock]
SQL1_1 = UPDATE construction SET pin_code = ?,is_locked = -1 WHERE id = ?
Number of Inputs = 2
SQL1_INPUTS = 1,2

[updateLock]
SQL1_1 = UPDATE construction SET is_locked = ? WHERE id = ?
Number of Inputs = 2
SQL1_INPUTS = 1,2

[updateConstructionTerritoryIDs]
SQL1_1 = UPDATE construction SET territory_id = ? WHERE id IN(?)
Number of Inputs = 2
SQL1_INPUTS = 1,2

;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
;; Containers related queries
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;

[insertContainer]
SQL1_1 = INSERT INTO container SET 
SQL1_2 = class = ?, 
SQL1_3 = account_uid = ?, 
SQL1_4 = position_x = ?,
SQL1_5 = position_y = ?,
SQL1_6 = position_z = ?,
SQL1_7 = direction_x = ?,
SQL1_8 = direction_y = ?,
SQL1_9 = direction_z = ?,
SQL1_10 = up_x = ?,
SQL1_11 = up_y = ?,
SQL1_12 = up_z = ?,
SQL1_13 = cargo_items = ?,
SQL1_14 = cargo_magazines = ?,
SQL1_15 = cargo_weapons = ?,
SQL1_16 = cargo_container = ?,
SQL1_17 = pin_code = ?,
SQL1_18 = territory_id = $CUSTOM_1$
Number of Inputs = 16
Number of Custom Inputs = 1
SQL1_INPUTS = 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16
Return InsertID = true

[deleteContainer]
SQL1_1 = UPDATE container SET deleted_at = NOW() WHERE id = ?
Number Of Inputs = 1
SQL1_INPUTS = 1

[loadContainerIdPage]
SQL1_1 = SELECT id FROM container WHERE delete_at = null LIMIT ?,?
Number Of Inputs = 2
SQL1_INPUTS = 1,2
OUTPUT = 1

[loadContainer]
SQL1_1 = SELECT id,class,account_uid,is_locked,position_x,position_y,position_z,direction_x,direction_y,direction_z,up_x,up_y,up_z,cargo_items,cargo_magazines,cargo_weapons,pin_code,territory_id FROM container WHERE id = ?
Number Of Inputs = 1
SQL1_INPUTS = 1
OUTPUT = 1,2-STRING,3-STRING,4,5,6,7,8,9,10,11,12,13,14,15,16,17-STRING

[loadContainerCargo]
SQL1_1 = SELECT cargo_container FROM container WHERE id = ?
Number Of Inputs = 1
SQL1_INPUTS = 1
OUTPUT = 1

[updateContainer]
SQL1_1 = UPDATE container SET 
SQL1_2 = is_locked = ?, 
SQL1_3 = position_x = ?,
SQL1_4 = position_y = ?,
SQL1_5 = position_z = ?,
SQL1_6 = direction_x = ?,
SQL1_7 = direction_y = ?,
SQL1_8 = direction_z = ?,
SQL1_9 = up_x = ?,
SQL1_10 = up_y = ?,
SQL1_11 = up_z = ?,
SQL1_12 = cargo_items = ?,
SQL1_13 = cargo_magazines = ?,
SQL1_14 = cargo_weapons = ?,
SQL1_15 = cargo_container = ?,
SQL1_16 = territory_id = $CUSTOM_1$
SQL1_17 = WHERE id = ?
Number of Inputs = 15
Number of Custom Inputs = 1
SQL1_INPUTS = 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15

[containerSetPinCode]
SQL1_1 = UPDATE container SET pin_code= ? WHERE id = ?
Number of Inputs = 2
SQL1_INPUTS = 1,2

[updateContainerTerritoryIDs]
SQL1_1 = UPDATE container SET territory_id = ? WHERE id IN(?)
Number of Inputs = 2
SQL1_INPUTS = 1,2

;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
;; Clan related queries
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;

[createClan]
SQL1_1 = INSERT INTO clan SET leader_uid = ?, name = ?
Number Of Inputs = 2
SQL1_INPUTS = 1,2
Return InsertID = true

[setAccountClanLink]
SQL_1_1 = UPDATE account SET clan_id = ? WHERE uid = ?
Number Of Inputs = 2
SQL1_INPUTS = 1,2

[deleteClan]
SQL_1_1 = DELETE FROM clan WHERE id = ?
Number Of Inputs = 1
SQL1_INPUTS = 1

[getClanMembers]
SQL_1_1 = SELECT uid, name FROM account WHERE clan_id = ?
Number Of Inputs = 1
SQL1_INPUTS = 1
OUTPUT = 1-STRING,2-STRING

;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
;; Territory related queries
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;

[createTerritory]
SQL1_1 = INSERT INTO territory SET owner_uid = ?, name = ?, position_x = ? , position_y = ? , position_z = ?, radius = ? , level = ? , flag_texture = ? , flag_stolen = ? , flag_stolen_by_uid =$CUSTOM_1$ , flag_steal_message = ?, build_rights = ? , moderators = ? 
Number Of Inputs = 12
Number Of Custom Inputs = 1
SQL1_INPUTS = 1,2,3,4,5,6,7,8,9,10,11,12
Return InsertID = true

[loadTerriotryIdPage]
SQL1_1 = SELECT id FROM territory WHERE delete_at = null LIMIT ?,?
Number Of Inputs = 2
SQL1_INPUTS = 1,2
OUTPUT = 1

[loadTerritory]
SQL1_1 = SET @connector = ?;
SQL2_1 = SELECT id,owner_uid,name,position_x,position_y,position_z,radius, level,flag_texture,flag_stolen,flag_stolen_by_uid,flag_steal_message,last_paid_at,build_rights,moderators,(SELECT COUNT(*)FROM construction c WHERE c.territory_id = @connector) FROM territory WHERE id = @connector
Number Of Inputs = 1
SQL1_INPUTS = 1
OUTPUT = 1,2-STRING,3-STRING,4,5,6,7,8,9-STRING,10,11-STRING,12-STRING,13-DateTime_ISO8601,14,15,16

[setTerritoryLevel]
SQL1_1 = UPDATE territory SET level = ? WHERE id = ?
Number Of Inputs = 2
SQL1_INPUTS = 1,2

[setTerritorySize]
SQL1_1 = UPDATE territory SET radius = ? WHERE id = ?
Number Of Inputs = 2
SQL1_INPUTS = 1,2

[updateTerritoryBuildRights]
SQL1_1 = UPDATE territory SET build_rights = ? WHERE id = ?
Number Of Inputs = 2
SQL1_INPUTS = 1,2

[updateTerritoryModerators]
SQL1_1 = UPDATE territory SET moderators = ? WHERE id = ?
Number Of Inputs = 2
SQL1_INPUTS = 1,2

[maintainTerritory]
SQL1_1 = UPDATE territory SET last_paid_at = NOW() WHERE id = ?
Number Of Inputs = 1
SQL1_INPUTS = 1

[deleteTerritory]
SQL1_1 = UPDATE territory SET deleted_at = NOW() WHERE id = ?
Number Of Inputs = 1
SQL1_INPUTS = 1

;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
;; Garbage Collector
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;

; Removes containers outside territories that were not accessed within ? days
[deleteOldContainers]
SQL1_1 = DELETE FROM container WHERE deleted_at < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL ? DAY)
SQL2_1 = UPDATE container SET deleted_at = NOW() WHERE deleted_at = null AND last_updated_at < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL ? DAY)) AND territory_id = NULL
Number Of Inputs = 1
SQL1_INPUTS = 1

; Removes contructions outside territories after ? days
[deleteOldConstructions]
SQL1_1 = DELETE FROM construction WHERE deleted_at < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL ? DAY)
SQL2_1 = UPDATE construction SET deleted_at = NOW() WHERE deleted_at = null AND last_updated_at < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL ? DAY)) AND territory_id = NULL
Number Of Inputs = 1
SQL1_INPUTS = 1

; Removes vehicles that were not used within ? days
[deleteOldVehicles]
SQL1_1 = DELETE FROM player WHERE deleted_at < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL ? DAY)
SQL2_1 = DELETE FROM vehicle WHERE deleted_at < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL ? DAY)
SQL3_1 = UPDATE vehicle SET deleted_at = NOW() WHERE deleted_at = null AND last_updated_at < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL ? DAY))
Number Of Inputs = 1
SQL1_INPUTS = 1

; Removes territories (and all containers/constructions) that were not paid within ? days
[deleteUnpaidTerritories]
SQL1_1 = DELETE FROM territory WHERE deleted_at < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL ? DAY)
SQL2_1 = UPDATE territory SET deleted_at = NOW() WHERE deleted_at = null AND last_paid_at < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL ? DAY))
Number Of Inputs = 1
SQL1_INPUTS = 1

 

5 people like this

Share this post


Link to post
Share on other sites

Updated version

Spoiler

; SQL queries to add delete at columns to needed tables
;	ALTER TABLE `player`
;		ADD COLUMN `deleted_at` DATETIME NULL DEFAULT NULL AFTER `last_updated_at`;
;	
;	ALTER TABLE `construction`
;		ADD COLUMN `deleted_at` DATETIME NULL DEFAULT NULL AFTER `last_updated_at`;
;	
;	ALTER TABLE `container`
;		ADD COLUMN `deleted_at` DATETIME NULL DEFAULT NULL AFTER `territory_id`;
;	
;	ALTER TABLE `vehicle`
;		ADD COLUMN `deleted_at` DATETIME NULL DEFAULT NULL AFTER `pin_code`;
;	
;	ALTER TABLE `territory`
;		ADD COLUMN `deleted_at` DATETIME NULL DEFAULT NULL AFTER `moderators`;

[Default]
Version = 12
Number of Inputs = 0
Sanitize Input Value Check = false
Sanitize Output Value Check = false
Prepared Statement Cache = true
Return InsertID = false
Strip = true
Strip Chars Action = STRIP
Strip Chars = \/\|;{}<>\'
Strip Custom Chars = \/\|;{}<>\'

;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
;; Account related queries
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;

[isKnownAccount]
SQL1_1 = SELECT CASE WHEN EXISTS(SELECT uid FROM account WHERE uid = ?) THEN 'true' ELSE 'false' END
Number of Inputs = 1
SQL1_INPUTS = 1 
OUTPUT = 1

[createAccount]
SQL1_1 = INSERT INTO account SET uid = ?, name = ?
Number Of Inputs = 2
SQL1_INPUTS = 1,2

[startAccountSession]
SQL1_1 = UPDATE account SET name = ?, last_connect_at = NOW(), total_connections = total_connections + 1 WHERE uid = ?
Number Of Inputs = 2
SQL1_INPUTS = 2,1

[endAccountSession]
SQL1_1 = UPDATE account SET last_disconnect_at = NOW() WHERE uid = ?
Number Of Inputs = 1
SQL1_INPUTS = 1

[getAccountStats]
SQL1_1 = SELECT a.money, a.score, a.kills, a.deaths, a.clan_id, c.name FROM account a LEFT JOIN clan c ON c.id = a.clan_id WHERE a.uid = ?
Number of Inputs = 1
SQL1_INPUTS = 1 
OUTPUT = 1,2,3,4,5,6-STRING

[addAccountKill]
SQL1_1 = UPDATE account SET kills = kills + 1 WHERE uid = ?
Number Of Inputs = 1
SQL1_INPUTS = 1

[addAccountDeath]
SQL1_1 = UPDATE account SET deaths = deaths + 1 WHERE uid = ?
Number Of Inputs = 1
SQL1_INPUTS = 1

[getAccountMoney]
SQL1_1 = SELECT money FROM account WHERE uid = ?
Number of Inputs = 1
SQL1_INPUTS = 1 
OUTPUT = 1

[setAccountMoney]
SQL1_1 = UPDATE account SET money = ? WHERE uid = ?
Number of Inputs = 2
SQL1_INPUTS = 1,2 

[setAccountMoneyAndRespect]
SQL1_1 = UPDATE account SET money = ?, score = ? WHERE uid = ?
Number of Inputs = 3
SQL1_INPUTS = 1,2,3 

[modifyAccountMoney]
SQL1_1 = UPDATE account SET money = money + ? WHERE uid = ?
Number of Inputs = 2
SQL1_INPUTS = 1,2 

[getAccountScore]
SQL1_1 = SELECT score FROM account WHERE uid = ?
Number of Inputs = 1
SQL1_INPUTS = 1 
OUTPUT = 1

[setAccountScore]
SQL1_1 = UPDATE account SET score = ? WHERE uid = ?
Number of Inputs = 2
SQL1_INPUTS = 1,2 

[modifyAccountScore]
SQL1_1 = UPDATE account SET score = score + ? WHERE uid = ?
Number of Inputs = 2
SQL1_INPUTS = 1,2 

;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
;; Player related queries
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;

[hasAlivePlayer]
SQL1_1 = SELECT CASE WHEN EXISTS(SELECT account_uid FROM player WHERE account_uid = ? AND deleted_at IS NULL) THEN 'true' ELSE 'false' END
Number of Inputs = 1
SQL1_INPUTS = 1
OUTPUT = 1

[createPlayer]
SQL1_1 = INSERT INTO player SET account_uid = ?, name = ?
Number Of Inputs = 2
SQL1_INPUTS = 1,2
Return InsertID = true

[insertPlayerHistory]
SQL1_1 = INSERT INTO player_history SET account_uid = ?, name = ?, position_x = ?, position_y = ?, position_z = ?
Number Of Inputs = 5
SQL1_INPUTS = 1,2,3,4,5

[deletePlayer]
SQL1_1 = UPDATE player SET deleted_at = NOW() WHERE id = ?
Number Of Inputs = 1
SQL1_INPUTS = 1

[loadPlayer]
SQL1_1 = SELECT p.id,
SQL1_2 = p.name,
SQL1_3 = p.account_uid,
SQL1_4 = p.damage,
SQL1_5 = p.hunger,
SQL1_6 = p.thirst,
SQL1_7 = p.alcohol,
SQL1_8 = p.oxygen_remaining,
SQL1_9 = p.bleeding_remaining,
SQL1_10 = p.hitpoints,
SQL1_11 = p.direction,
SQL1_12 = p.position_x,
SQL1_13 = p.position_y,
SQL1_14 = p.position_z,
SQL1_15 = p.assigned_items,
SQL1_16 = p.backpack,
SQL1_17 = p.backpack_items,
SQL1_18 = p.backpack_magazines,
SQL1_19 = p.backpack_weapons,
SQL1_20 = p.current_weapon,
SQL1_21 = p.goggles,
SQL1_22 = p.handgun_items,
SQL1_23 = p.handgun_weapon,
SQL1_24 = p.headgear,
SQL1_25 = p.binocular,
SQL1_26 = p.loaded_magazines,
SQL1_27 = p.primary_weapon,
SQL1_28 = p.primary_weapon_items,
SQL1_29 = p.secondary_weapon,
SQL1_30 = p.secondary_weapon_items,
SQL1_31 = p.uniform,
SQL1_32 = p.uniform_items,
SQL1_33 = p.uniform_magazines,
SQL1_34 = p.uniform_weapons,
SQL1_35 = p.vest,
SQL1_36 = p.vest_items,
SQL1_37 = p.vest_magazines,
SQL1_38 = p.vest_weapons,
SQL1_39 = a.money,
SQL1_40 = a.score,
SQL1_41 = a.kills,
SQL1_42 = a.deaths,
SQL1_43 = c.name
SQL1_44 = FROM player p
SQL1_45 = INNER JOIN account a
SQL1_46 = ON a.uid = p.account_uid
SQL1_47 = LEFT JOIN clan c
SQL1_48 = ON c.id = a.clan_id
SQL1_49 = WHERE p.account_uid = ? AND p.deleted_at IS NULL
Number of Inputs = 1
SQL1_INPUTS = 1
SQL2_INPUTS = 1
OUTPUT = 1,2-STRING,3-STRING,4,5,6,7,8,9,10,11,12,13,14,15,16-STRING,17,18,19,20-STRING,21-STRING,22,23-STRING,24-STRING,25-STRING,26,27-STRING,28,29-STRING,30,31-STRING,32,33,34,35-STRING,36,37,38,39,40,41,42,43-STRING

[updatePlayer]
SQL1_1 = UPDATE player SET
SQL1_2 = name = ?,
SQL1_3 = damage  = ?,
SQL1_4 = hunger = ?,
SQL1_5 = thirst = ?,
SQL1_6 = alcohol = ?,
SQL1_7 = oxygen_remaining  = ?,
SQL1_8 = bleeding_remaining = ?,
SQL1_9 = hitpoints = ?,
SQL1_10 = direction = ?,
SQL1_11 = position_x = ?,
SQL1_12 = position_y = ?,
SQL1_13 = position_z = ?,
SQL1_14 = assigned_items = ?,
SQL1_15 = backpack = ?,
SQL1_16 = backpack_items = ?,
SQL1_17 = backpack_magazines = ?,
SQL1_18 = backpack_weapons = ?,
SQL1_19 = current_weapon = ?,
SQL1_20 = goggles = ?,
SQL1_21 = handgun_items = ?,
SQL1_22 = handgun_weapon = ?,
SQL1_23 = headgear = ?,
SQL1_24 = binocular = ?,
SQL1_25 = loaded_magazines = ?,
SQL1_26 = primary_weapon = ?,
SQL1_27 = primary_weapon_items = ?,
SQL1_28 = secondary_weapon = ?,
SQL1_29 = secondary_weapon_items = ?,
SQL1_30 = uniform = ?,
SQL1_31 = uniform_items = ?,
SQL1_32 = uniform_magazines = ?,
SQL1_33 = uniform_weapons = ?,
SQL1_34 = vest = ?,
SQL1_35 = vest_items = ?,
SQL1_36 = vest_magazines = ?,
SQL1_37 = vest_weapons = ?
SQL1_38 = WHERE id = ?
Number Of Inputs = 37
SQL1_INPUTS = 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37

;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
;; Vehicle related queries
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;

[insertVehicle]
SQL1_1 = INSERT INTO vehicle SET 
SQL1_2 = class = ?, 
SQL1_3 = account_uid = ?,
SQL1_4 = is_locked = ?,
SQL1_5 = position_x = ?,
SQL1_6 = position_y = ?,
SQL1_7 = position_z = ?,
SQL1_8 = direction_x = ?,
SQL1_9 = direction_y = ?,
SQL1_10 = direction_z = ?,
SQL1_11 = up_x = ?,
SQL1_12 = up_y = ?,
SQL1_13 = up_z = ?,
SQL1_14 = pin_code = ?
Number of Inputs = 13
SQL1_INPUTS = 1,2,3,4,5,6,7,8,9,10,11,12,13
Return InsertID = true

[deleteVehicle]
SQL1_1 = UPDATE vehicle SET deleted_at = NOW() WHERE id = ?
Number Of Inputs = 1
SQL1_INPUTS = 1

[loadVehicleIdPage]
SQL1_1 = SELECT id FROM vehicle WHERE deleted_at IS NULL LIMIT ?,?
Number Of Inputs = 2
SQL1_INPUTS = 1,2
OUTPUT = 1

[loadVehicle]
SQL1_1 = SELECT id,class,spawned_at,account_uid,is_locked,fuel,damage,hitpoints,position_x,position_y,position_z,direction_x,direction_y,direction_z,up_x,up_y,up_z,cargo_items,cargo_magazines,cargo_weapons,pin_code FROM vehicle WHERE id = ?
Number Of Inputs = 1
SQL1_INPUTS = 1
OUTPUT = 1,2-STRING,3-STRING,4-STRING,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21-STRING

[loadVehicleContainer]
SQL1_1 = SELECT cargo_container FROM vehicle WHERE id = ?
Number Of Inputs = 1
SQL1_INPUTS = 1
OUTPUT = 1

[updateVehicle]
SQL1_1 = UPDATE vehicle SET 
SQL1_2 = is_locked = ?, 
SQL1_3 = fuel = ?, 
SQL1_4 = damage = ?, 
SQL1_5 = hitpoints = ?,
SQL1_6 = position_x = ?,
SQL1_7 = position_y = ?,
SQL1_8 = position_z = ?,
SQL1_9 = direction_x = ?,
SQL1_10 = direction_y = ?,
SQL1_11 = direction_z = ?,
SQL1_12 = up_x = ?,
SQL1_13 = up_y = ?,
SQL1_14 = up_z = ?,
SQL1_15 = cargo_items = ?,
SQL1_16 = cargo_magazines = ?,
SQL1_17 = cargo_weapons = ?,
SQL1_18 = cargo_container = ?
SQL1_19 = WHERE id = ?
Number of Inputs = 18
SQL1_INPUTS = 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18

[updateVehicleClass]
SQL1_1 = UPDATE vehicle SET class = ? WHERE id = ?
Number of Inputs = 2
SQL1_INPUTS = 1,2

[vehicleSetPinCode]
SQL1_1 = UPDATE vehicle SET pin_code = ? WHERE id = ?
Number of Inputs = 2
SQL1_INPUTS = 1,2


;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
;; Construction related queries
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;

[insertConstruction]
SQL1_1 = INSERT INTO construction SET 
SQL1_2 = class = ?, 
SQL1_3 = account_uid = ?, 
SQL1_4 = position_x = ?,
SQL1_5 = position_y = ?,
SQL1_6 = position_z = ?,
SQL1_7 = direction_x = ?,
SQL1_8 = direction_y = ?,
SQL1_9 = direction_z = ?,
SQL1_10 = up_x = ?,
SQL1_11 = up_y = ?,
SQL1_12 = up_z = ?,
SQL1_13 = territory_id = $CUSTOM_1$
Number of Inputs = 11
Number of Custom Inputs = 1
SQL1_INPUTS = 1,2,3,4,5,6,7,8,9,10,11
Return InsertID = true

[deleteConstruction]
SQL1_1 = UPDATE construction SET deleted_at = NOW() WHERE id = ?
Number Of Inputs = 1
SQL1_INPUTS = 1 

[loadConstructionIdPage]
SQL1_1 = SELECT id FROM construction WHERE deleted_at IS NULL LIMIT ?,?
Number Of Inputs = 2
SQL1_INPUTS = 1,2
OUTPUT = 1

[loadConstruction]
SQL1_1 = SELECT id,class,account_uid,spawned_at,position_x,position_y,position_z,direction_x,direction_y,direction_z,up_x,up_y,up_z,is_locked,pin_code FROM construction WHERE id = ?
Number Of Inputs = 1
SQL1_INPUTS = 1 
OUTPUT = 1,2-STRING,3-STRING,4-STRING,5,6,7,8,9,10,11,12,13,14,15-STRING

[countConstruction]
SQL1_1 = SELECT COUNT(*) FROM construction WHERE deleted_at IS NULL
Number Of Inputs = 0
OUTPUT = 1

[constructionSetPinCode]
SQL1_1 = UPDATE construction SET pin_code= ? WHERE id = ?
Number of Inputs = 2
SQL1_INPUTS = 1,2

[upgradeObject]
SQL1_1 = UPDATE construction SET class=? WHERE id = ?
Number of Inputs = 2
SQL1_INPUTS = 1,2

[addDoorLock]
SQL1_1 = UPDATE construction SET pin_code = ?,is_locked = -1 WHERE id = ?
Number of Inputs = 2
SQL1_INPUTS = 1,2

[updateLock]
SQL1_1 = UPDATE construction SET is_locked = ? WHERE id = ?
Number of Inputs = 2
SQL1_INPUTS = 1,2

[updateConstructionTerritoryIDs]
SQL1_1 = UPDATE construction SET territory_id = ? WHERE id IN(?)
Number of Inputs = 2
SQL1_INPUTS = 1,2

;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
;; Containers related queries
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;

[insertContainer]
SQL1_1 = INSERT INTO container SET 
SQL1_2 = class = ?, 
SQL1_3 = account_uid = ?, 
SQL1_4 = position_x = ?,
SQL1_5 = position_y = ?,
SQL1_6 = position_z = ?,
SQL1_7 = direction_x = ?,
SQL1_8 = direction_y = ?,
SQL1_9 = direction_z = ?,
SQL1_10 = up_x = ?,
SQL1_11 = up_y = ?,
SQL1_12 = up_z = ?,
SQL1_13 = cargo_items = ?,
SQL1_14 = cargo_magazines = ?,
SQL1_15 = cargo_weapons = ?,
SQL1_16 = cargo_container = ?,
SQL1_17 = pin_code = ?,
SQL1_18 = territory_id = $CUSTOM_1$
Number of Inputs = 16
Number of Custom Inputs = 1
SQL1_INPUTS = 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16
Return InsertID = true

[deleteContainer]
SQL1_1 = UPDATE container SET deleted_at = NOW() WHERE id = ?
Number Of Inputs = 1
SQL1_INPUTS = 1

[loadContainerIdPage]
SQL1_1 = SELECT id FROM container WHERE deleted_at IS NULL LIMIT ?,?
Number Of Inputs = 2
SQL1_INPUTS = 1,2
OUTPUT = 1

[loadContainer]
SQL1_1 = SELECT id,class,account_uid,is_locked,position_x,position_y,position_z,direction_x,direction_y,direction_z,up_x,up_y,up_z,cargo_items,cargo_magazines,cargo_weapons,pin_code,territory_id FROM container WHERE id = ?
Number Of Inputs = 1
SQL1_INPUTS = 1
OUTPUT = 1,2-STRING,3-STRING,4,5,6,7,8,9,10,11,12,13,14,15,16,17-STRING,18

[loadContainerCargo]
SQL1_1 = SELECT cargo_container FROM container WHERE id = ?
Number Of Inputs = 1
SQL1_INPUTS = 1
OUTPUT = 1

[updateContainer]
SQL1_1 = UPDATE container SET 
SQL1_2 = is_locked = ?, 
SQL1_3 = position_x = ?,
SQL1_4 = position_y = ?,
SQL1_5 = position_z = ?,
SQL1_6 = direction_x = ?,
SQL1_7 = direction_y = ?,
SQL1_8 = direction_z = ?,
SQL1_9 = up_x = ?,
SQL1_10 = up_y = ?,
SQL1_11 = up_z = ?,
SQL1_12 = cargo_items = ?,
SQL1_13 = cargo_magazines = ?,
SQL1_14 = cargo_weapons = ?,
SQL1_15 = cargo_container = ?,
SQL1_16 = territory_id = $CUSTOM_1$
SQL1_17 = WHERE id = ?
Number of Inputs = 15
Number of Custom Inputs = 1
SQL1_INPUTS = 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15

[containerSetPinCode]
SQL1_1 = UPDATE container SET pin_code= ? WHERE id = ?
Number of Inputs = 2
SQL1_INPUTS = 1,2

[updateContainerTerritoryIDs]
SQL1_1 = UPDATE container SET territory_id = ? WHERE id IN(?)
Number of Inputs = 2
SQL1_INPUTS = 1,2

;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
;; Clan related queries
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;

[createClan]
SQL1_1 = INSERT INTO clan SET leader_uid = ?, name = ?
Number Of Inputs = 2
SQL1_INPUTS = 1,2
Return InsertID = true

[setAccountClanLink]
SQL_1_1 = UPDATE account SET clan_id = ? WHERE uid = ?
Number Of Inputs = 2
SQL1_INPUTS = 1,2

[deleteClan]
SQL_1_1 = DELETE FROM clan WHERE id = ?
Number Of Inputs = 1
SQL1_INPUTS = 1

[getClanMembers]
SQL_1_1 = SELECT uid, name FROM account WHERE clan_id = ?
Number Of Inputs = 1
SQL1_INPUTS = 1
OUTPUT = 1-STRING,2-STRING

;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
;; Territory related queries
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;

[createTerritory]
SQL1_1 = INSERT INTO territory SET owner_uid = ?, name = ?, position_x = ? , position_y = ? , position_z = ?, radius = ? , level = ? , flag_texture = ? , flag_stolen = ? , flag_stolen_by_uid =$CUSTOM_1$ , flag_steal_message = ?, build_rights = ? , moderators = ? 
Number Of Inputs = 12
Number Of Custom Inputs = 1
SQL1_INPUTS = 1,2,3,4,5,6,7,8,9,10,11,12
Return InsertID = true

[loadTerriotryIdPage]
SQL1_1 = SELECT id FROM territory WHERE deleted_at IS NULL LIMIT ?,?
Number Of Inputs = 2
SQL1_INPUTS = 1,2
OUTPUT = 1

[loadTerritory]
SQL1_1 = SET @connector = ?;
SQL2_1 = SELECT id,owner_uid,name,position_x,position_y,position_z,radius, level,flag_texture,flag_stolen,flag_stolen_by_uid,flag_steal_message,last_paid_at,build_rights,moderators,(SELECT COUNT(*)FROM construction c WHERE c.territory_id = @connector) FROM territory WHERE id = @connector
Number Of Inputs = 1
SQL1_INPUTS = 1
OUTPUT = 1,2-STRING,3-STRING,4,5,6,7,8,9-STRING,10,11-STRING,12-STRING,13-DateTime_ISO8601,14,15,16

[setTerritoryLevel]
SQL1_1 = UPDATE territory SET level = ? WHERE id = ?
Number Of Inputs = 2
SQL1_INPUTS = 1,2

[setTerritorySize]
SQL1_1 = UPDATE territory SET radius = ? WHERE id = ?
Number Of Inputs = 2
SQL1_INPUTS = 1,2

[updateTerritoryBuildRights]
SQL1_1 = UPDATE territory SET build_rights = ? WHERE id = ?
Number Of Inputs = 2
SQL1_INPUTS = 1,2

[updateTerritoryModerators]
SQL1_1 = UPDATE territory SET moderators = ? WHERE id = ?
Number Of Inputs = 2
SQL1_INPUTS = 1,2

[maintainTerritory]
SQL1_1 = UPDATE territory SET last_paid_at = NOW() WHERE id = ?
Number Of Inputs = 1
SQL1_INPUTS = 1

[deleteTerritory]
SQL1_1 = UPDATE territory SET deleted_at = NOW() WHERE id = ?
Number Of Inputs = 1
SQL1_INPUTS = 1

;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
;; Garbage Collector
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;

; Removes containers outside territories that were not accessed within ? days
[deleteOldContainers]
SQL1_1 = DELETE FROM container WHERE deleted_at < DATE_SUB(NOW(), INTERVAL ? DAY)
SQL2_1 = UPDATE container SET deleted_at = NOW() WHERE deleted_at IS NULL AND last_updated_at < DATE_SUB(NOW(), INTERVAL ? DAY) AND territory_id = NULL
Number Of Inputs = 1
SQL1_INPUTS = 1
SQL2_INPUTS = 1

; Removes contructions outside territories after ? days
[deleteOldConstructions]
SQL1_1 = DELETE FROM construction WHERE deleted_at < DATE_SUB(NOW(), INTERVAL ? DAY)
SQL2_1 = UPDATE construction SET deleted_at = NOW() WHERE deleted_at IS NULL AND last_updated_at < DATE_SUB(NOW(), INTERVAL ? DAY) AND territory_id = NULL
Number Of Inputs = 1
SQL1_INPUTS = 1
SQL2_INPUTS = 1

; Removes vehicles that were not used within ? days
[deleteOldVehicles]
SQL1_1 = DELETE FROM player WHERE deleted_at < DATE_SUB(NOW(), INTERVAL ? DAY)
SQL2_1 = DELETE FROM vehicle WHERE deleted_at < DATE_SUB(NOW(), INTERVAL ? DAY)
SQL3_1 = UPDATE vehicle SET deleted_at = NOW() WHERE deleted_at IS NULL AND last_updated_at < DATE_SUB(NOW(), INTERVAL ? DAY)
Number Of Inputs = 1
SQL1_INPUTS = 1
SQL2_INPUTS = 1
SQL3_INPUTS = 1

; Removes territories (and all containers/constructions) that were not paid within ? days
[deleteUnpaidTerritories]
SQL1_1 = DELETE FROM territory WHERE deleted_at < DATE_SUB(NOW(), INTERVAL ? DAY)
SQL2_1 = UPDATE territory SET deleted_at = NOW() WHERE deleted_at IS NULL AND last_paid_at < DATE_SUB(NOW(), INTERVAL ? DAY)
Number Of Inputs = 1
SQL1_INPUTS = 1
SQL2_INPUTS = 1

 

 

Share this post


Link to post
Share on other sites
Advertisement

Please confirm/report back. I did test it but had some odd result and not had chance to go thru it properly.

Share this post


Link to post
Share on other sites
Advertisement

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now

  • Recently Browsing   0 members

    No registered users viewing this page.