adkats_users.sql

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";

DELIMITER $$
$$

$$

DELIMITER ;

CREATE TABLE `adkats_commands` (
  `command_id` int(11) UNSIGNED NOT NULL,
  `command_active` enum('Active','Disabled','Invisible') COLLATE utf8_unicode_ci NOT NULL DEFAULT 'Active',
  `command_key` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `command_logging` enum('Log','Mandatory','Ignore','Unable') COLLATE utf8_unicode_ci NOT NULL DEFAULT 'Log',
  `command_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `command_text` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `command_playerInteraction` tinyint(1) NOT NULL,
  `command_access` enum('Any','AnyHidden','AnyVisible','GlobalVisible','TeamVisible','SquadVisible') COLLATE utf8_unicode_ci NOT NULL DEFAULT 'Any'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='AdKats - Command List';

CREATE TABLE `adkats_rolecommands` (
  `role_id` int(11) UNSIGNED NOT NULL,
  `command_id` int(11) UNSIGNED NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='AdKats - Connection of commands to roles';

CREATE TABLE `adkats_rolegroups` (
  `role_id` int(11) UNSIGNED NOT NULL,
  `group_key` varchar(100) COLLATE utf8_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='AdKats - Connection of groups to roles';

CREATE TABLE `adkats_roles` (
  `role_id` int(11) UNSIGNED NOT NULL,
  `role_key` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `role_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='AdKats - Role List';

CREATE TABLE `adkats_users` (
  `user_id` int(11) UNSIGNED NOT NULL,
  `user_name` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `user_email` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `user_phone` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
  `user_role` int(11) UNSIGNED NOT NULL DEFAULT '1',
  `user_expiration` datetime NOT NULL,
  `user_notes` varchar(1000) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'No Notes'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='AdKats - User List';

CREATE TABLE `adkats_usersoldiers` (
  `user_id` int(11) UNSIGNED NOT NULL,
  `player_id` int(10) UNSIGNED NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='AdKats - Connection of users to soldiers';


ALTER TABLE `adkats_commands`
  ADD PRIMARY KEY (`command_id`),
  ADD UNIQUE KEY `command_key_UNIQUE` (`command_key`),
  ADD UNIQUE KEY `command_text_UNIQUE` (`command_text`);

ALTER TABLE `adkats_rolecommands`
  ADD PRIMARY KEY (`role_id`,`command_id`),
  ADD KEY `adkats_rolecommands_fk_role` (`role_id`),
  ADD KEY `adkats_rolecommands_fk_command` (`command_id`);

ALTER TABLE `adkats_rolegroups`
  ADD PRIMARY KEY (`role_id`,`group_key`),
  ADD KEY `adkats_rolegroups_fk_role` (`role_id`),
  ADD KEY `adkats_rolegroups_fk_command` (`group_key`);

ALTER TABLE `adkats_roles`
  ADD PRIMARY KEY (`role_id`),
  ADD UNIQUE KEY `role_key_UNIQUE` (`role_key`);

ALTER TABLE `adkats_users`
  ADD PRIMARY KEY (`user_id`),
  ADD KEY `adkats_users_fk_role` (`user_role`);

ALTER TABLE `adkats_usersoldiers`
  ADD PRIMARY KEY (`user_id`,`player_id`),
  ADD KEY `adkats_usersoldiers_fk_user` (`user_id`),
  ADD KEY `adkats_usersoldiers_fk_player` (`player_id`);


ALTER TABLE `adkats_roles`
  MODIFY `role_id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=154;
ALTER TABLE `adkats_users`
  MODIFY `user_id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=395;

ALTER TABLE `adkats_rolecommands`
  ADD CONSTRAINT `adkats_rolecommands_fk_command` FOREIGN KEY (`command_id`) REFERENCES `adkats_commands` (`command_id`) ON UPDATE CASCADE,
  ADD CONSTRAINT `adkats_rolecommands_fk_role` FOREIGN KEY (`role_id`) REFERENCES `adkats_roles` (`role_id`) ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE `adkats_rolegroups`
  ADD CONSTRAINT `adkats_rolegroups_fk_role` FOREIGN KEY (`role_id`) REFERENCES `adkats_roles` (`role_id`) ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE `adkats_users`
  ADD CONSTRAINT `adkats_users_fk_role` FOREIGN KEY (`user_role`) REFERENCES `adkats_roles` (`role_id`) ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE `adkats_usersoldiers`
  ADD CONSTRAINT `adkats_usersoldiers_fk_player` FOREIGN KEY (`player_id`) REFERENCES `tbl_playerdata` (`PlayerID`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `adkats_usersoldiers_fk_user` FOREIGN KEY (`user_id`) REFERENCES `adkats_users` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE;