Brun

How to Hive ACCOUNT and CLAN

Started by Brun,

15 posts in this topic

Brun    88

Brun
Posted (edited)

Purpose: SYNC  Account and Clan Tables so that Poptabs and respect follow a player through your hive of exile servers.
Assumptions: You Currently use one database per exile server on the same MySQL instance

Step 1. Backup your database
Step 2. Restore Backup to your test environments to test this change
If you can not do the above you might need help.
Step 3. Review the below SQL and adjust to suit your exile hive
Step 4. Execute SQL

Spoiler 

create database exile_hive;
use exile_hive;

-- --------------------------------------------------------

--
-- Table structure for table `account`
--
CREATE TABLE `account` (
  `uid` varchar(32) NOT NULL,
  `clan_id` int(11) UNSIGNED DEFAULT NULL,
  `name` varchar(64) NOT NULL,
  `money` double NOT NULL DEFAULT '0',
  `score` int(11) NOT NULL DEFAULT '0',
  `kills` int(11) UNSIGNED NOT NULL DEFAULT '0',
  `deaths` int(11) UNSIGNED NOT NULL DEFAULT '0',
  `first_connect_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `last_connect_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `last_disconnect_at` datetime DEFAULT NULL,
  `total_connections` int(11) UNSIGNED NOT NULL DEFAULT '1'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table `clan`
--
CREATE TABLE `clan` (
  `id` int(11) UNSIGNED NOT NULL,
  `name` varchar(64) NOT NULL,
  `leader_uid` varchar(32) NOT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `insignia_texture` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `account`
--
ALTER TABLE `account`
  ADD PRIMARY KEY (`uid`),
  ADD KEY `clan_id` (`clan_id`);

--
-- Indexes for table `clan`
--
ALTER TABLE `clan`
  ADD PRIMARY KEY (`id`),
  ADD KEY `leader_uid` (`leader_uid`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `clan`
--
ALTER TABLE `clan`
  MODIFY `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT;
  
  
--
-- Constraints for dumped tables
--

--
-- Constraints for table `account`
--
ALTER TABLE `account`
  ADD CONSTRAINT `account_ibfk_1` FOREIGN KEY (`clan_id`) REFERENCES `clan` (`id`) ON DELETE SET NULL;

--
-- Constraints for table `clan`
--
ALTER TABLE `clan`
  ADD CONSTRAINT `clan_ibfk_1` FOREIGN KEY (`leader_uid`) REFERENCES `account` (`uid`) ON DELETE CASCADE;

  
-- Merge latest record  
 insert into exile_hive.account
select  uid, null clan_id, max(name), max(money), max(score), sum(kills), sum(deaths), min(first_connect_at), max(last_connect_at), max(last_disconnect_at), sum(total_connections)
from
(select * from exile_namalsk.account union all
select * from exile_esseker.account union all
select * from exile_altis.account) a
group by uid;

commit;

-- Decomission legacy tables
alter table  exile_namalsk.clan rename to exile_namalsk.clan_old;
alter table  exile_namalsk.account rename to exile_namalsk.account_old;

alter table  exile_altis.clan rename to exile_altis.clan_old;
alter table  exile_altis.account rename to exile_altis.account_old;

alter table  exile_esseker.clan rename to exile_esseker.clan_old;
alter table  exile_esseker.account rename to exile_esseker.account_old;

-- Grant Permissions 
grant select, insert, update, delete on exile_hive.account to exile_namalsk;
grant select, insert, update, delete on exile_hive.clan to exile_namalsk;

grant select, insert, update, delete on exile_hive.account to exile_esseker;
grant select, insert, update, delete on exile_hive.clan to exile_esseker;

grant select, insert, update, delete on exile_hive.account to exile_altis;
grant select, insert, update, delete on exile_hive.clan to exile_altis;

 

 

Step 5. Update Foreign Keys to point to new exile_hive schema.

Spoiler 

-----------------------------------------
-----------------------------------------
-- EXILE SERVER 1
-----------------------------------------
-----------------------------------------

ALTER TABLE `exile_namalsk`.`construction` 
DROP FOREIGN KEY `construction_ibfk_1`;

ALTER TABLE `exile_namalsk`.`container` 
DROP FOREIGN KEY `container_ibfk_1`;

ALTER TABLE `exile_namalsk`.`player` 
DROP FOREIGN KEY `player_ibfk_1`;

ALTER TABLE `exile_namalsk`.`territory` 
DROP FOREIGN KEY `territory_ibfk_1`;

ALTER TABLE `exile_namalsk`.`territory` 
DROP FOREIGN KEY `territory_ibfk_2`;
  
ALTER TABLE `exile_namalsk`.`vehicle` 
DROP FOREIGN KEY `vehicle_ibfk_1`;


--
-- Constraints for table `construction`
--
ALTER TABLE `exile_namalsk`.`construction`
  ADD CONSTRAINT `construction_ibfk_1` FOREIGN KEY (`account_uid`) REFERENCES `exile_hive`.`account` (`uid`) ON DELETE CASCADE;

--
-- Constraints for table `container`
--
ALTER TABLE `exile_namalsk`.`container`
  ADD CONSTRAINT `container_ibfk_1` FOREIGN KEY (`account_uid`) REFERENCES `exile_hive`.`account` (`uid`) ON DELETE CASCADE;

--
-- Constraints for table `player`
--
ALTER TABLE `exile_namalsk`.`player`
  ADD CONSTRAINT `player_ibfk_1` FOREIGN KEY (`account_uid`) REFERENCES `exile_hive`.`account` (`uid`) ON DELETE CASCADE;

--
-- Constraints for table `territory`
--
ALTER TABLE `exile_namalsk`.`territory`
  ADD CONSTRAINT `territory_ibfk_1` FOREIGN KEY (`owner_uid`) REFERENCES `exile_hive`.`account` (`uid`) ON DELETE CASCADE,
  ADD CONSTRAINT `territory_ibfk_2` FOREIGN KEY (`flag_stolen_by_uid`) REFERENCES `exile_hive`.`account` (`uid`) ON DELETE SET NULL;

--
-- Constraints for table `vehicle`
--
ALTER TABLE `exile_namalsk`.`vehicle`
  ADD CONSTRAINT `vehicle_ibfk_1` FOREIGN KEY (`account_uid`) REFERENCES `exile_hive`.`account` (`uid`) ON DELETE CASCADE;
  
-----------------------------------------
-----------------------------------------
-- EXILE SERVER 2
-----------------------------------------
-----------------------------------------  
ALTER TABLE `exile_esseker`.`construction` 
DROP FOREIGN KEY `construction_ibfk_1`;

ALTER TABLE `exile_esseker`.`container` 
DROP FOREIGN KEY `container_ibfk_1`;

ALTER TABLE `exile_esseker`.`player` 
DROP FOREIGN KEY `player_ibfk_1`;

ALTER TABLE `exile_esseker`.`territory` 
DROP FOREIGN KEY `territory_ibfk_1`;

ALTER TABLE `exile_esseker`.`territory` 
DROP FOREIGN KEY `territory_ibfk_2`;
  
ALTER TABLE `exile_esseker`.`vehicle` 
DROP FOREIGN KEY `vehicle_ibfk_1`;


--
-- Constraints for table `construction`
--
ALTER TABLE `exile_esseker`.`construction`
  ADD CONSTRAINT `construction_ibfk_1` FOREIGN KEY (`account_uid`) REFERENCES `exile_hive`.`account` (`uid`) ON DELETE CASCADE;

--
-- Constraints for table `container`
--
ALTER TABLE `exile_esseker`.`container`
  ADD CONSTRAINT `container_ibfk_1` FOREIGN KEY (`account_uid`) REFERENCES `exile_hive`.`account` (`uid`) ON DELETE CASCADE;

--
-- Constraints for table `player`
--
ALTER TABLE `exile_esseker`.`player`
  ADD CONSTRAINT `player_ibfk_1` FOREIGN KEY (`account_uid`) REFERENCES `exile_hive`.`account` (`uid`) ON DELETE CASCADE;

--
-- Constraints for table `territory`
--
ALTER TABLE `exile_esseker`.`territory`
  ADD CONSTRAINT `territory_ibfk_1` FOREIGN KEY (`owner_uid`) REFERENCES `exile_hive`.`account` (`uid`) ON DELETE CASCADE,
  ADD CONSTRAINT `territory_ibfk_2` FOREIGN KEY (`flag_stolen_by_uid`) REFERENCES `exile_hive`.`account` (`uid`) ON DELETE SET NULL;

--
-- Constraints for table `vehicle`
--
ALTER TABLE `exile_esseker`.`vehicle`
  ADD CONSTRAINT `vehicle_ibfk_1` FOREIGN KEY (`account_uid`) REFERENCES `exile_hive`.`account` (`uid`) ON DELETE CASCADE;
  
-----------------------------------------
-----------------------------------------
-- EXILE SERVER 3
-----------------------------------------
-----------------------------------------  
ALTER TABLE `exile_altis`.`construction` 
DROP FOREIGN KEY `construction_ibfk_1`;

ALTER TABLE `exile_altis`.`container` 
DROP FOREIGN KEY `container_ibfk_1`;

ALTER TABLE `exile_altis`.`player` 
DROP FOREIGN KEY `player_ibfk_1`;

ALTER TABLE `exile_altis`.`territory` 
DROP FOREIGN KEY `territory_ibfk_1`;

ALTER TABLE `exile_altis`.`territory` 
DROP FOREIGN KEY `territory_ibfk_2`;
  
ALTER TABLE `exile_altis`.`vehicle` 
DROP FOREIGN KEY `vehicle_ibfk_1`;


--
-- Constraints for table `construction`
--
ALTER TABLE `exile_altis`.`construction`
  ADD CONSTRAINT `construction_ibfk_1` FOREIGN KEY (`account_uid`) REFERENCES `exile_hive`.`account` (`uid`) ON DELETE CASCADE;

--
-- Constraints for table `container`
--
ALTER TABLE `exile_altis`.`container`
  ADD CONSTRAINT `container_ibfk_1` FOREIGN KEY (`account_uid`) REFERENCES `exile_hive`.`account` (`uid`) ON DELETE CASCADE;

--
-- Constraints for table `player`
--
ALTER TABLE `exile_altis`.`player`
  ADD CONSTRAINT `player_ibfk_1` FOREIGN KEY (`account_uid`) REFERENCES `exile_hive`.`account` (`uid`) ON DELETE CASCADE;

--
-- Constraints for table `territory`
--
ALTER TABLE `exile_altis`.`territory`
  ADD CONSTRAINT `territory_ibfk_1` FOREIGN KEY (`owner_uid`) REFERENCES `exile_hive`.`account` (`uid`) ON DELETE CASCADE,
  ADD CONSTRAINT `territory_ibfk_2` FOREIGN KEY (`flag_stolen_by_uid`) REFERENCES `exile_hive`.`account` (`uid`) ON DELETE SET NULL;

--
-- Constraints for table `vehicle`
--
ALTER TABLE `exile_altis`.`vehicle`
  ADD CONSTRAINT `vehicle_ibfk_1` FOREIGN KEY (`account_uid`) REFERENCES `exile_hive`.`account` (`uid`) ON DELETE CASCADE;

 


Step 6. Modify @ExileServer\extDB\sql_custom_v2\exile.ini
This step tells the exile database client to access the table from a different schema.
You can either manually prefix account and clan table with exile_hive or copy and paste the below

Spoiler 

[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 exile_hive.account WHERE uid = ?) THEN 'true' ELSE 'false' END
Number of Inputs = 1
SQL1_INPUTS = 1 
OUTPUT = 1

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

[startAccountSession]
SQL1_1 = UPDATE exile_hive.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 exile_hive.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 exile_hive.account a LEFT JOIN exile_hive.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 exile_hive.account SET kills = kills + 1 WHERE uid = ?
Number Of Inputs = 1
SQL1_INPUTS = 1

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

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

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

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

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

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

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

[modifyAccountScore]
SQL1_1 = UPDATE exile_hive.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 = ?) 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 = DELETE FROM player 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 exile_hive.account a
SQL1_46 = ON a.uid = p.account_uid 
SQL1_47 = LEFT JOIN exile_hive.clan c 
SQL1_48 = ON c.id = a.clan_id
SQL1_49 = WHERE p.account_uid = ?
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-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 = DELETE FROM vehicle WHERE id = ?
Number Of Inputs = 1
SQL1_INPUTS = 1

[loadVehicleIdPage]
SQL1_1 = SELECT id FROM vehicle 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 = DELETE FROM construction WHERE id = ?
Number Of Inputs = 1
SQL1_INPUTS = 1 

[loadConstructionIdPage]
SQL1_1 = SELECT id FROM construction 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
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 = DELETE FROM container WHERE id = ?
Number Of Inputs = 1
SQL1_INPUTS = 1

[loadContainerIdPage]
SQL1_1 = SELECT id FROM container 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

;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
;; exile_hive.clan related queries
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;

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

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

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

[getClanMembers]
SQL_1_1 = SELECT uid, name FROM exile_hive.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 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 = DELETE FROM territory 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 last_updated_at < 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 last_updated_at < 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 vehicle WHERE last_updated_at < 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 last_paid_at < DATE_SUB(NOW(), INTERVAL ? DAY)
Number Of Inputs = 1
SQL1_INPUTS = 1

 

 

Edited by Brun
  • Like 7

Share this post

Link to post
Share on other sites
Advertisement

wasti    96

wasti
Posted

I knew using the db was the correct way to do this, just didn't have the servers (and time) to test it out with. Thanks for posting, great work :)

Share this post

Link to post
Share on other sites

wasti    96

wasti
Posted

sorry for double post, but bumping for .96
here is the updated ini file: 

[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 exile_hive.account WHERE uid = ?) THEN 'true' ELSE 'false' END
Number of Inputs = 1
SQL1_INPUTS = 1 
OUTPUT = 1

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

[startAccountSession]
SQL1_1 = UPDATE exile_hive.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 exile_hive.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 exile_hive.account a LEFT JOIN exile_hive.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 exile_hive.account SET kills = kills + 1 WHERE uid = ?
Number Of Inputs = 1
SQL1_INPUTS = 1

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

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

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

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

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

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

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

[modifyAccountScore]
SQL1_1 = UPDATE exile_hive.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 damage < 1) 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 = DELETE FROM player 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.id,
SQL1_44 = c.name,
SQL1_45 = p.temperature,
SQL1_46 = p.wetness
SQL1_47 = FROM player p
SQL1_48 = INNER JOIN exile_hive.account a
SQL1_49 = ON a.uid = p.account_uid 
SQL1_50 = LEFT JOIN exile_hive.clan c 
SQL1_51 = ON c.id = a.clan_id
SQL1_52 = WHERE p.account_uid = ?
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-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,44-STRING,45,46

[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 = temperature = ?,
SQL1_39 = wetness = ?
SQL1_40 = WHERE id = ?
Number Of Inputs = 39
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,38,39

;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
;; 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 = DELETE FROM vehicle WHERE id = ?
Number Of Inputs = 1
SQL1_INPUTS = 1

[loadVehicleIdPage]
SQL1_1 = SELECT id FROM vehicle 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,vehicle_texture 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,22

[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

[updateVehicleSkin]
SQL1_1 = UPDATE vehicle SET vehicle_texture = ? 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 = DELETE FROM construction WHERE id = ?
Number Of Inputs = 1
SQL1_INPUTS = 1 

[loadConstructionIdPage]
SQL1_1 = SELECT id FROM construction 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,territory_id 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,16

[countConstruction]
SQL1_1 = SELECT COUNT(*) FROM construction
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 = DELETE FROM container WHERE id = ?
Number Of Inputs = 1
SQL1_INPUTS = 1

[loadContainerIdPage]
SQL1_1 = SELECT id FROM container 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,abandoned 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,19-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 exile_hive.clan SET leader_uid = ?, name = ?
Number Of Inputs = 2
SQL1_INPUTS = 1,2
Return InsertID = true

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

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

[getClanMembers]
SQL_1_1 = SELECT uid, name FROM exile_hive.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 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 = DELETE FROM territory 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 last_updated_at < DATE_SUB(NOW(), INTERVAL ? DAY) AND territory_id IS NULL
Number Of Inputs = 1
SQL1_INPUTS = 1

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

; Removes vehicles that were not used within ? days
[deleteOldVehicles]
SQL1_1 = DELETE FROM vehicle WHERE last_updated_at < 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 last_paid_at < DATE_SUB(NOW(), INTERVAL ? DAY)
Number Of Inputs = 1
SQL1_INPUTS = 1

[addAbandonedSafes]
SQL1_1 = UPDATE container SET abandoned = NOW(), pin_code = '0000' WHERE last_updated_at < DATE_SUB(NOW(), INTERVAL ? DAY) AND territory_id IS NULL
Number Of Inputs = 1
SQL1_INPUTS = 1

 

Share this post

Link to post
Share on other sites

TPGDagger    51

TPGDagger
Posted (edited)

Is there an easier way to do this if I'm running two servers and just want the second to share from my exile DB in regard to the accounts/clan area versus having to split that DB into two, with that new server running a third?

Edited by TPGDagger

Share this post

Link to post
Share on other sites

wasti    96

wasti
Posted

yes, there are a couple options for running it on one or two db's.

option a) for example, I will use Exile_altis and Exile_chern as the 2 databases. You can set the foreign keys for your tables in exile_chern to the accounts table in Exile altis, and configure the exile_chern\@Exile_server\Extdb2\extdb2\sql_custom\exile.ini to use the Exile_altis database for accounts. Just use the ini I posted above and cchange exile_hive to Exile_altis or whatever your primary database is named.

option b (and this is the one I think you want based on your post) take the single Exile database, create a copy of all tables except for account and clan, and name them (original table name)_secondary. Then configure your secondary servers Exile.ini to use the _secondary tables except for account and clan.

 

option c) contact me on steam and we can work something out :) http://steamcommunity.com/id/wasti/

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
Go To Topic Listing MySQL

  • Recently Browsing   0 members

    No registered users viewing this page.