SQL Update Script for 0.9.4

[CEN 449]

Run this on your current 0.9.35 database as part of the update to 0.9.4 and you won't have to wipe your database!

Spoiler

ALTER TABLE account CHANGE `first_connect_at` `first_connect_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP;
ALTER TABLE account CHANGE `last_connect_at` `last_connect_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP;

ALTER TABLE construction CHANGE `spawned_at` `spawned_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP;
ALTER TABLE construction CHANGE `position_x` `position_x` double NOT NULL DEFAULT '0';
ALTER TABLE construction CHANGE `position_y` `position_y` double NOT NULL DEFAULT '0';
ALTER TABLE construction CHANGE `position_z` `position_z` double NOT NULL DEFAULT '0';
ALTER TABLE construction CHANGE `direction_x` `direction_x` double NOT NULL DEFAULT '0';
ALTER TABLE construction CHANGE `direction_y` `direction_y` double NOT NULL DEFAULT '0';
ALTER TABLE construction CHANGE `direction_z` `direction_z` double NOT NULL DEFAULT '0';
ALTER TABLE construction CHANGE `up_x` `up_x` double NOT NULL DEFAULT '0';
ALTER TABLE construction CHANGE `up_y` `up_y` double NOT NULL DEFAULT '0';
ALTER TABLE construction CHANGE `up_z` `up_z` double NOT NULL DEFAULT '0';
ALTER TABLE construction CHANGE `is_locked` `is_locked` tinyint(1) NOT NULL DEFAULT '0';
ALTER TABLE construction ADD `territory_id` int(11) UNSIGNED DEFAULT NULL;
ALTER TABLE construction ADD `last_updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;
ALTER TABLE construction DROP COLUMN maintained_at;

ALTER TABLE container DROP COLUMN `last_accessed`;
ALTER TABLE container CHANGE `spawned_at` `spawned_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP;
ALTER TABLE container ADD `last_updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;
ALTER TABLE container ADD `territory_id` int(11) UNSIGNED DEFAULT NULL;

ALTER TABLE player DROP COLUMN is_alive;
ALTER TABLE player DROP COLUMN fatigue;
ALTER TABLE player DROP COLUMN hitpoint_head;
ALTER TABLE player DROP COLUMN hitpoint_body;
ALTER TABLE player DROP COLUMN hitpoint_hands;
ALTER TABLE player DROP COLUMN hitpoint_legs;
ALTER TABLE player DROP COLUMN died_at;
ALTER TABLE player ADD `hitpoints` varchar(255) NOT NULL DEFAULT '[]';
ALTER TABLE player CHANGE `spawned_at` `spawned_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP;

CREATE TABLE `player_history` (
  `id` int(11) UNSIGNED NOT NULL,
  `account_uid` varchar(32) NOT NULL,
  `name` varchar(64) NOT NULL,
  `died_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `position_x` double NOT NULL,
  `position_y` double NOT NULL,
  `position_z` double NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE territory CHANGE `id` `id` int(11) UNSIGNED NOT NULL;
ALTER TABLE territory CHANGE `created_at` `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP;
ALTER TABLE territory DROP COLUMN last_payed_at;
ALTER TABLE territory ADD `last_paid_at` datetime DEFAULT CURRENT_TIMESTAMP;

ALTER TABLE vehicle CHANGE `id` `id` int(11) UNSIGNED NOT NULL;
ALTER TABLE vehicle CHANGE `spawned_at` `spawned_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP;
ALTER TABLE vehicle ADD `last_updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

ALTER TABLE `construction`
  ADD KEY `account_uid` (`account_uid`),
  ADD KEY `territory_id` (`territory_id`);

ALTER TABLE `container`
  ADD KEY `account_uid` (`account_uid`),
  ADD KEY `territory_id` (`territory_id`);

ALTER TABLE `player_history`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `territory`
  ADD KEY `owner_uid` (`owner_uid`),
  ADD KEY `flag_stolen_by_uid` (`flag_stolen_by_uid`);

ALTER TABLE `vehicle`
  ADD KEY `account_uid` (`account_uid`);

ALTER TABLE `clan`
  MODIFY `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE `construction`
  MODIFY `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE `container`
  MODIFY `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE `player`
  MODIFY `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=14;
ALTER TABLE `player_history`
  MODIFY `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;
ALTER TABLE `territory`
  MODIFY `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE `vehicle`
  MODIFY `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT;

DELETE construction FROM construction WHERE construction.account_uid NOT IN (SELECT account.uid FROM account);
DELETE container FROM container WHERE container.account_uid NOT IN (SELECT account.uid FROM account);
DELETE player FROM player WHERE player.account_uid NOT IN (SELECT account.uid FROM account);
DELETE territory FROM territory WHERE territory.owner_uid NOT IN (SELECT account.uid FROM account);
DELETE vehicle FROM vehicle WHERE vehicle.account_uid NOT IN (SELECT account.uid FROM account);
UPDATE territory SET flag_stolen_by_uid = NULL;

ALTER TABLE `construction`
  ADD CONSTRAINT `construction_ibfk_1` FOREIGN KEY (`account_uid`) REFERENCES `account` (`uid`) ON DELETE CASCADE,
  ADD CONSTRAINT `construction_ibfk_2` FOREIGN KEY (`territory_id`) REFERENCES `territory` (`id`) ON DELETE CASCADE;

ALTER TABLE `container`
  ADD CONSTRAINT `container_ibfk_1` FOREIGN KEY (`account_uid`) REFERENCES `account` (`uid`) ON DELETE CASCADE,
  ADD CONSTRAINT `container_ibfk_2` FOREIGN KEY (`territory_id`) REFERENCES `territory` (`id`) ON DELETE CASCADE;

ALTER TABLE `player`
  ADD CONSTRAINT `player_ibfk_1` FOREIGN KEY (`account_uid`) REFERENCES `account` (`uid`) ON DELETE CASCADE;

ALTER TABLE `territory`
  ADD CONSTRAINT `territory_ibfk_1` FOREIGN KEY (`owner_uid`) REFERENCES `account` (`uid`) ON DELETE CASCADE,
  ADD CONSTRAINT `territory_ibfk_2` FOREIGN KEY (`flag_stolen_by_uid`) REFERENCES `account` (`uid`) ON DELETE SET NULL;

ALTER TABLE `vehicle`
  ADD CONSTRAINT `vehicle_ibfk_1` FOREIGN KEY (`account_uid`) REFERENCES `account` (`uid`) ON DELETE CASCADE;

UPDATE
    `construction` c
INNER JOIN  `territory` t ON ABS( t.position_x - c.position_x ) < t.radius
AND ABS( t.position_y - c.position_y ) < t.radius
SET
    c.territory_id = t.id;
    
UPDATE
    `container` c
INNER JOIN  `territory` t ON ABS( t.position_x - c.position_x ) < t.radius
AND ABS( t.position_y - c.position_y ) < t.radius
SET
    c.territory_id = t.id;

UPDATE territory SET flag_texture = REPLACE (flag_texture, 'exile_client', 'exile_assets') WHERE flag_texture LIKE '%exile_client%';
    
DELETE FROM construction WHERE territory_id is NULL;
DELETE FROM container WHERE territory_id is NULL;
DELETE FROM player WHERE damage = 1;

 

 

Edited December 9, 2015 by CEN

[Spartan 85]

You the man CEN.

[Eichi 4505]

Pinned

[Vishpala 516]

Add this line:

UPDATE territory SET flag_texture = REPLACE (flag_texture, 'exile_client', 'exile_assets') WHERE flag_texture LIKE '%exile_client%';

[CEN 449]

It was added yesterday :D

[odizzzzle 99]

run now or wait? should wait..

[BetterDeadThanZed 1006]

17 minutes ago, odizzzzle said:

run now or wait? should wait..

Don't run it until you have updated your server to the new version.

[Vishpala 516]

3 hours ago, CEN said:

It was added yesterday :D

Thanks :D

[TPGDagger 51]

@CEN you are the man! Thanks for saving me some time.

Edited December 10, 2015 by TPGDagger

[Fistt 0]

New update dont works, it get kicking me out all time. why?