nitradoa_3_exile_1_0_4a_fix.sql

/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;


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

--
-- Table structure for table `account`
--

CREATE TABLE `account` (
  `uid` varchar(32) NOT NULL DEFAULT '[]',
  `clan_id` int(11) UNSIGNED DEFAULT NULL,
  `name` varchar(64) NOT NULL DEFAULT '[]',
  `score` int(11) NOT NULL DEFAULT 0,
  `kills` int(11) UNSIGNED NOT NULL DEFAULT 0,
  `deaths` int(11) UNSIGNED NOT NULL DEFAULT 0,
  `locker` int(11) 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 DEFAULT '[]',
  `leader_uid` varchar(32) NOT NULL DEFAULT '[]',
  `created_at` datetime NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

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

--
-- Table structure for table `clan_map_marker`
--

CREATE TABLE `clan_map_marker` (
  `id` int(11) UNSIGNED NOT NULL,
  `clan_id` int(11) UNSIGNED NOT NULL,
  `markerType` tinyint(4) NOT NULL DEFAULT -1,
  `positionArr` text NOT NULL DEFAULT '[]',
  `color` varchar(255) NOT NULL,
  `icon` varchar(255) NOT NULL,
  `iconSize` float UNSIGNED NOT NULL,
  `label` varchar(255) NOT NULL,
  `labelSize` float UNSIGNED NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

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

--
-- Table structure for table `construction`
--

CREATE TABLE `construction` (
  `id` int(11) UNSIGNED NOT NULL,
  `class` varchar(64) NOT NULL,
  `account_uid` varchar(32) NOT NULL,
  `spawned_at` datetime NOT NULL DEFAULT current_timestamp(),
  `position_x` double NOT NULL DEFAULT 0,
  `position_y` double NOT NULL DEFAULT 0,
  `position_z` double NOT NULL DEFAULT 0,
  `direction_x` double NOT NULL DEFAULT 0,
  `direction_y` double NOT NULL DEFAULT 0,
  `direction_z` double NOT NULL DEFAULT 0,
  `up_x` double NOT NULL DEFAULT 0,
  `up_y` double NOT NULL DEFAULT 0,
  `up_z` double NOT NULL DEFAULT 0,
  `is_locked` tinyint(1) NOT NULL DEFAULT 0,
  `pin_code` varchar(6) NOT NULL DEFAULT '000000',
  `damage` tinyint(1) UNSIGNED DEFAULT 0,
  `territory_id` int(11) UNSIGNED DEFAULT NULL,
  `last_updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

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

--
-- Table structure for table `container`
--

CREATE TABLE `container` (
  `id` int(11) UNSIGNED NOT NULL,
  `class` varchar(64) NOT NULL,
  `spawned_at` datetime NOT NULL DEFAULT current_timestamp(),
  `account_uid` varchar(32) DEFAULT NULL,
  `is_locked` tinyint(1) NOT NULL DEFAULT 0,
  `position_x` double NOT NULL DEFAULT 0,
  `position_y` double NOT NULL DEFAULT 0,
  `position_z` double NOT NULL DEFAULT 0,
  `direction_x` double NOT NULL DEFAULT 0,
  `direction_y` double NOT NULL DEFAULT 0,
  `direction_z` double NOT NULL DEFAULT 0,
  `up_x` double NOT NULL DEFAULT 0,
  `up_y` double NOT NULL DEFAULT 0,
  `up_z` double NOT NULL DEFAULT 1,
  `cargo_items` text NOT NULL DEFAULT '[]',
  `cargo_magazines` text NOT NULL DEFAULT '[]',
  `cargo_weapons` text NOT NULL DEFAULT '[]',
  `cargo_container` text NOT NULL DEFAULT '[]',
  `last_updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `pin_code` varchar(6) NOT NULL DEFAULT '000000',
  `territory_id` int(11) UNSIGNED DEFAULT NULL,
  `deleted_at` datetime DEFAULT NULL,
  `money` int(11) UNSIGNED NOT NULL DEFAULT 0,
  `abandoned` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT;

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

--
-- Table structure for table `player`
--

CREATE TABLE `player` (
  `id` int(11) UNSIGNED NOT NULL,
  `name` varchar(64) NOT NULL,
  `account_uid` varchar(32) NOT NULL,
  `money` int(11) UNSIGNED NOT NULL DEFAULT 0,
  `damage` double UNSIGNED NOT NULL DEFAULT 0,
  `hunger` double UNSIGNED NOT NULL DEFAULT 100,
  `thirst` double UNSIGNED NOT NULL DEFAULT 100,
  `alcohol` double UNSIGNED NOT NULL DEFAULT 0,
  `temperature` double NOT NULL DEFAULT 37,
  `wetness` double UNSIGNED NOT NULL DEFAULT 0,
  `oxygen_remaining` double UNSIGNED NOT NULL DEFAULT 1,
  `bleeding_remaining` double UNSIGNED NOT NULL DEFAULT 0,
  `hitpoints` varchar(1024) NOT NULL DEFAULT '[]',
  `direction` double NOT NULL DEFAULT 0,
  `position_x` double NOT NULL DEFAULT 0,
  `position_y` double NOT NULL DEFAULT 0,
  `position_z` double NOT NULL DEFAULT 0,
  `spawned_at` datetime NOT NULL DEFAULT current_timestamp(),
  `assigned_items` text NOT NULL DEFAULT '[]',
  `backpack` varchar(64) NOT NULL DEFAULT '[]',
  `backpack_items` text NOT NULL DEFAULT '[]',
  `backpack_magazines` text NOT NULL DEFAULT '[]',
  `backpack_weapons` text NOT NULL DEFAULT '[]',
  `current_weapon` varchar(64) NOT NULL DEFAULT '[]',
  `goggles` varchar(64) NOT NULL DEFAULT '[]',
  `handgun_items` text NOT NULL DEFAULT '[]',
  `handgun_weapon` varchar(64) NOT NULL DEFAULT '[]',
  `headgear` varchar(64) NOT NULL DEFAULT '[]',
  `binocular` varchar(64) NOT NULL DEFAULT '[]',
  `loaded_magazines` text NOT NULL DEFAULT '[]',
  `primary_weapon` varchar(64) NOT NULL DEFAULT '[]',
  `primary_weapon_items` text NOT NULL DEFAULT '[]',
  `secondary_weapon` varchar(64) NOT NULL DEFAULT '[]',
  `secondary_weapon_items` text NOT NULL DEFAULT '[]',
  `uniform` varchar(64) NOT NULL DEFAULT '[]',
  `uniform_items` text NOT NULL DEFAULT '[]',
  `uniform_magazines` text NOT NULL DEFAULT '[]',
  `uniform_weapons` text NOT NULL DEFAULT '[]',
  `vest` varchar(64) NOT NULL DEFAULT '[]',
  `vest_items` text NOT NULL DEFAULT '[]',
  `vest_magazines` text NOT NULL DEFAULT '[]',
  `vest_weapons` text NOT NULL DEFAULT '[]',
  `last_updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

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

--
-- Table structure for table `player_history`
--

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;

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

--
-- Table structure for table `territory`
--

CREATE TABLE `territory` (
  `id` int(11) UNSIGNED NOT NULL,
  `owner_uid` varchar(32) NOT NULL,
  `name` varchar(64) NOT NULL,
  `position_x` double NOT NULL,
  `position_y` double NOT NULL,
  `position_z` double NOT NULL,
  `radius` double NOT NULL,
  `level` int(11) NOT NULL,
  `flag_texture` varchar(255) NOT NULL,
  `flag_stolen` tinyint(1) NOT NULL DEFAULT 0,
  `flag_stolen_by_uid` varchar(32) DEFAULT NULL,
  `flag_stolen_at` datetime DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `last_paid_at` datetime DEFAULT current_timestamp(),
  `xm8_protectionmoney_notified` tinyint(1) NOT NULL DEFAULT 0,
  `build_rights` varchar(640) NOT NULL DEFAULT '0',
  `moderators` varchar(320) NOT NULL DEFAULT '0',
  `deleted_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

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

--
-- Table structure for table `vehicle`
--

CREATE TABLE `vehicle` (
  `id` int(11) UNSIGNED NOT NULL,
  `class` varchar(64) NOT NULL,
  `spawned_at` datetime NOT NULL DEFAULT current_timestamp(),
  `account_uid` varchar(32) DEFAULT NULL,
  `is_locked` tinyint(1) NOT NULL DEFAULT 0,
  `fuel` double UNSIGNED NOT NULL DEFAULT 0,
  `damage` double UNSIGNED NOT NULL DEFAULT 0,
  `hitpoints` text NOT NULL DEFAULT '[]',
  `position_x` double NOT NULL DEFAULT 0,
  `position_y` double NOT NULL DEFAULT 0,
  `position_z` double NOT NULL DEFAULT 0,
  `direction_x` double NOT NULL DEFAULT 0,
  `direction_y` double NOT NULL DEFAULT 0,
  `direction_z` double NOT NULL DEFAULT 0,
  `up_x` double NOT NULL DEFAULT 0,
  `up_y` double NOT NULL DEFAULT 0,
  `up_z` double NOT NULL DEFAULT 1,
  `cargo_items` text NOT NULL DEFAULT '[]',
  `cargo_magazines` text NOT NULL DEFAULT '[]',
  `cargo_weapons` text NOT NULL DEFAULT '[]',
  `cargo_container` text NOT NULL DEFAULT '[]',
  `last_updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `pin_code` varchar(6) NOT NULL DEFAULT '000000',
  `deleted_at` datetime DEFAULT NULL,
  `money` int(11) UNSIGNED NOT NULL DEFAULT 0,
  `vehicle_texture` text NOT NULL DEFAULT '[]',
  `territory_id` int(11) UNSIGNED DEFAULT NULL,
  `nickname` varchar(64) NOT NULL 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`);

--
-- Indexes for table `clan_map_marker`
--
ALTER TABLE `clan_map_marker`
  ADD PRIMARY KEY (`id`),
  ADD KEY `clan_id` (`clan_id`);

--
-- Indexes for table `construction`
--
ALTER TABLE `construction`
  ADD PRIMARY KEY (`id`),
  ADD KEY `account_uid` (`account_uid`),
  ADD KEY `territory_id` (`territory_id`);

--
-- Indexes for table `container`
--
ALTER TABLE `container`
  ADD PRIMARY KEY (`id`),
  ADD KEY `account_uid` (`account_uid`),
  ADD KEY `territory_id` (`territory_id`);

--
-- Indexes for table `player`
--
ALTER TABLE `player`
  ADD PRIMARY KEY (`id`),
  ADD KEY `player_uid` (`account_uid`);

--
-- Indexes for table `player_history`
--
ALTER TABLE `player_history`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `territory`
--
ALTER TABLE `territory`
  ADD PRIMARY KEY (`id`),
  ADD KEY `owner_uid` (`owner_uid`),
  ADD KEY `flag_stolen_by_uid` (`flag_stolen_by_uid`);

--
-- Indexes for table `vehicle`
--
ALTER TABLE `vehicle`
  ADD PRIMARY KEY (`id`),
  ADD KEY `account_uid` (`account_uid`),
  ADD KEY `vehicle_ibfk_2_idx` (`territory_id`);

--
-- AUTO_INCREMENT for dumped tables
--

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

--
-- AUTO_INCREMENT for table `clan_map_marker`
--
ALTER TABLE `clan_map_marker`
  MODIFY `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `construction`
--
ALTER TABLE `construction`
  MODIFY `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `container`
--
ALTER TABLE `container`
  MODIFY `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `player`
--
ALTER TABLE `player`
  MODIFY `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `player_history`
--
ALTER TABLE `player_history`
  MODIFY `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `territory`
--
ALTER TABLE `territory`
  MODIFY `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `vehicle`
--
ALTER TABLE `vehicle`
  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;

--
-- Constraints for table `clan_map_marker`
--
ALTER TABLE `clan_map_marker`
  ADD CONSTRAINT `clan_map_marker_ibfk_1` FOREIGN KEY (`clan_id`) REFERENCES `clan` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `construction`
--
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;

--
-- Constraints for table `container`
--
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;

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

--
-- Constraints for table `territory`
--
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;

--
-- Constraints for table `vehicle`
--
ALTER TABLE `vehicle`
  ADD CONSTRAINT `vehicle_ibfk_1` FOREIGN KEY (`account_uid`) REFERENCES `account` (`uid`) ON DELETE CASCADE,
  ADD CONSTRAINT `vehicle_ibfk_2` FOREIGN KEY (`territory_id`) REFERENCES `territory` (`id`) ON DELETE CASCADE;
COMMIT;