CEN

SQL Update Script for 0.9.4

32 posts in this topic

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 by CEN
12 people like this

Share this post


Link to post
Share on other sites
Advertisement

Add this line:

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

Share this post


Link to post
Share on other sites
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.

1 person likes this

Share this post


Link to post
Share on other sites

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

Edited by TPGDagger

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.