(Pending) Database: Replace "Mission" Tables with "Campaign/Operation" tables #59

New Issue

EagleTrooper · 2025-12-07T02:03:47-06:00

EagleTrooper commented

2025-12-07 02:03:47 -06:00

Replace Missions table with "Campaign Tables"

Drop Mission Tables

DROP TABLE `mission_event_attendees`;
DROP TABLE `mission_attendee_roles`;
DROP TABLE `mission_events`;

Create ingame_roles

CREATE TABLE `unit_ingame_roles` (
	`id` INT(11) NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
	`short_name` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
	`description` TEXT NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
	`sort_order` INT(11) NULL DEFAULT NULL,
	`active` TINYINT(4) NOT NULL DEFAULT '1',
	PRIMARY KEY (`id`) USING BTREE,
	UNIQUE INDEX `role_name` (`name`) USING BTREE,
	UNIQUE INDEX `short_name` (`short_name`) USING BTREE
)
COMMENT='Contains a list of in game roles that can be run.'
COLLATE='utf8mb4_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=25
;

Replace Missions table with "Create Campaign Tables"

CREATE TABLE `campaigns` (
	`id` INT(11) NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(100) NOT NULL COLLATE 'utf8mb4_general_ci',
	`author_id` INT(11) NULL DEFAULT NULL,
	`description` TEXT NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
	`createdAt` DATETIME NULL DEFAULT current_timestamp(),
	`updatedAt` DATETIME NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
	PRIMARY KEY (`id`) USING BTREE,
	INDEX `FK_campaigns_members` (`author_id`) USING BTREE,
	CONSTRAINT `FK_campaigns_members` FOREIGN KEY (`author_id`) REFERENCES `members` (`id`) ON UPDATE CASCADE ON DELETE RESTRICT
)
COMMENT='Contains a list of Campagins'
COLLATE='utf8mb4_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=1
;

Create Operation Type Table

CREATE TABLE `operation_type` (
	`id` INT(11) NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(100) NOT NULL COLLATE 'utf8mb4_general_ci',
	`active` TINYINT(4) NOT NULL DEFAULT '1',
	PRIMARY KEY (`id`) USING BTREE,
	UNIQUE INDEX `name` (`name`) USING BTREE
)
COLLATE='utf8mb4_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=1
;

Create Operation Table

CREATE TABLE `operations` (
	`id` INT(11) NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(250) NOT NULL COLLATE 'utf8mb4_general_ci',
	`description` TEXT NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
	`author_id` INT(11) NULL DEFAULT NULL,
	`campaign_id` INT(11) NULL DEFAULT NULL,
	`map_id` INT(11) NULL DEFAULT NULL,
	`type_id` INT(11) NULL DEFAULT NULL,
	`calendar_event_id` INT(11) NULL DEFAULT NULL,
	`startTime` DATETIME NULL DEFAULT NULL,
	`aarURL` VARCHAR(512) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
	`createdAt` DATETIME NOT NULL DEFAULT current_timestamp(),
	`updatedAt` DATETIME NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
	`deleted` TINYINT(4) NOT NULL DEFAULT '0',
	PRIMARY KEY (`id`) USING BTREE,
	UNIQUE INDEX `name_campaign_id` (`name`, `campaign_id`) USING BTREE,
	INDEX `FK_operations_members` (`author_id`) USING BTREE,
	INDEX `FK_operations_campaigns` (`campaign_id`) USING BTREE,
	INDEX `FK_operations_arma_maps` (`map_id`) USING BTREE,
	INDEX `FK_operations_calendar_events` (`calendar_event_id`) USING BTREE,
	INDEX `FK_operations_operation_type` (`type_id`) USING BTREE,
	CONSTRAINT `FK_operations_arma_maps` FOREIGN KEY (`map_id`) REFERENCES `arma_maps` (`id`) ON UPDATE CASCADE ON DELETE RESTRICT,
	CONSTRAINT `FK_operations_calendar_events` FOREIGN KEY (`calendar_event_id`) REFERENCES `calendar_events` (`id`) ON UPDATE CASCADE ON DELETE RESTRICT,
	CONSTRAINT `FK_operations_campaigns` FOREIGN KEY (`campaign_id`) REFERENCES `campaigns` (`id`) ON UPDATE CASCADE ON DELETE RESTRICT,
	CONSTRAINT `FK_operations_members` FOREIGN KEY (`author_id`) REFERENCES `members` (`id`) ON UPDATE CASCADE ON DELETE RESTRICT,
	CONSTRAINT `FK_operations_operation_type` FOREIGN KEY (`type_id`) REFERENCES `operation_type` (`id`) ON UPDATE CASCADE ON DELETE RESTRICT
)
COMMENT='Missions or Operations '
COLLATE='utf8mb4_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=1
;

Create Operation Attendees

CREATE TABLE `operations_attendees` (
	`id` INT(11) NOT NULL AUTO_INCREMENT,
	`operation_id` INT(11) NOT NULL,
	`member_id` INT(11) NOT NULL,
	`group_id` INT(11) NULL DEFAULT NULL,
	`role_id` INT(11) NULL DEFAULT NULL,
	`join_time` DATETIME NULL DEFAULT NULL,
	`leave_time` DATETIME NULL DEFAULT NULL,
	`created_at` DATETIME NOT NULL DEFAULT current_timestamp(),
	`updated_at` DATETIME NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
	`notes` DATETIME NULL DEFAULT NULL,
	`finalized` TINYINT(4) NOT NULL DEFAULT '0',
	PRIMARY KEY (`id`) USING BTREE,
	INDEX `FK_operations_attendees_operations` (`operation_id`) USING BTREE,
	INDEX `FK_operations_attendees_members` (`member_id`) USING BTREE,
	INDEX `FK_operations_attendees_structure_groups` (`group_id`) USING BTREE,
	INDEX `FK_operations_attendees_ingame_roles` (`role_id`) USING BTREE,
	CONSTRAINT `FK_operations_attendees_members` FOREIGN KEY (`member_id`) REFERENCES `members` (`id`) ON UPDATE CASCADE ON DELETE RESTRICT,
	CONSTRAINT `FK_operations_attendees_ingame_roles` FOREIGN KEY (`role_id`) REFERENCES `ingame_roles` (`id`) ON UPDATE CASCADE ON DELETE RESTRICT,
	CONSTRAINT `FK_operations_attendees_operations` FOREIGN KEY (`operation_id`) REFERENCES `operations` (`id`) ON UPDATE CASCADE ON DELETE RESTRICT,
	CONSTRAINT `FK_operations_attendees_structure_groups` FOREIGN KEY (`group_id`) REFERENCES `structure_group` (`id`) ON UPDATE CASCADE ON DELETE RESTRICT
)
COLLATE='utf8mb4_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=6
;

Create ingame_assets (OPTIONAL)

CREATE TABLE `ingame_assets` (
	`id` INT(11) NOT NULL AUTO_INCREMENT,
	`class_name` VARCHAR(100) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
	`category` ENUM('APC','Rotary','Truck','Boats','MRAP','TiltRotor','Cars','Fixed-Wing','Artillery','Armor','Primary weapons','Optics','Secondary weapons','Launchers') NOT NULL COLLATE 'utf8mb4_general_ci',
	`friendly_name` VARCHAR(100) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
	`description` TINYTEXT NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
	`image_name` VARCHAR(250) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
	`currently_approved` TINYINT(1) NULL DEFAULT '1',
	PRIMARY KEY (`id`) USING BTREE
)
COLLATE='utf8mb4_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=252
;

## Replace Missions table with "Campaign Tables" ### Drop Mission Tables ```sql DROP TABLE `mission_event_attendees`; DROP TABLE `mission_attendee_roles`; DROP TABLE `mission_events`; ``` ### Create ingame_roles ```sql CREATE TABLE `unit_ingame_roles` ( `id` INT(11) NOT NULL AUTO_INCREMENT, `name` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci', `short_name` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci', `description` TEXT NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci', `sort_order` INT(11) NULL DEFAULT NULL, `active` TINYINT(4) NOT NULL DEFAULT '1', PRIMARY KEY (`id`) USING BTREE, UNIQUE INDEX `role_name` (`name`) USING BTREE, UNIQUE INDEX `short_name` (`short_name`) USING BTREE ) COMMENT='Contains a list of in game roles that can be run.' COLLATE='utf8mb4_general_ci' ENGINE=InnoDB AUTO_INCREMENT=25 ; ``` ### Replace Missions table with "Create Campaign Tables" ```sql CREATE TABLE `campaigns` ( `id` INT(11) NOT NULL AUTO_INCREMENT, `name` VARCHAR(100) NOT NULL COLLATE 'utf8mb4_general_ci', `author_id` INT(11) NULL DEFAULT NULL, `description` TEXT NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci', `createdAt` DATETIME NULL DEFAULT current_timestamp(), `updatedAt` DATETIME NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), PRIMARY KEY (`id`) USING BTREE, INDEX `FK_campaigns_members` (`author_id`) USING BTREE, CONSTRAINT `FK_campaigns_members` FOREIGN KEY (`author_id`) REFERENCES `members` (`id`) ON UPDATE CASCADE ON DELETE RESTRICT ) COMMENT='Contains a list of Campagins' COLLATE='utf8mb4_general_ci' ENGINE=InnoDB AUTO_INCREMENT=1 ; ``` ### Create Operation Type Table ```sql CREATE TABLE `operation_type` ( `id` INT(11) NOT NULL AUTO_INCREMENT, `name` VARCHAR(100) NOT NULL COLLATE 'utf8mb4_general_ci', `active` TINYINT(4) NOT NULL DEFAULT '1', PRIMARY KEY (`id`) USING BTREE, UNIQUE INDEX `name` (`name`) USING BTREE ) COLLATE='utf8mb4_general_ci' ENGINE=InnoDB AUTO_INCREMENT=1 ; ``` ### Create Operation Table ```sql CREATE TABLE `operations` ( `id` INT(11) NOT NULL AUTO_INCREMENT, `name` VARCHAR(250) NOT NULL COLLATE 'utf8mb4_general_ci', `description` TEXT NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci', `author_id` INT(11) NULL DEFAULT NULL, `campaign_id` INT(11) NULL DEFAULT NULL, `map_id` INT(11) NULL DEFAULT NULL, `type_id` INT(11) NULL DEFAULT NULL, `calendar_event_id` INT(11) NULL DEFAULT NULL, `startTime` DATETIME NULL DEFAULT NULL, `aarURL` VARCHAR(512) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci', `createdAt` DATETIME NOT NULL DEFAULT current_timestamp(), `updatedAt` DATETIME NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), `deleted` TINYINT(4) NOT NULL DEFAULT '0', PRIMARY KEY (`id`) USING BTREE, UNIQUE INDEX `name_campaign_id` (`name`, `campaign_id`) USING BTREE, INDEX `FK_operations_members` (`author_id`) USING BTREE, INDEX `FK_operations_campaigns` (`campaign_id`) USING BTREE, INDEX `FK_operations_arma_maps` (`map_id`) USING BTREE, INDEX `FK_operations_calendar_events` (`calendar_event_id`) USING BTREE, INDEX `FK_operations_operation_type` (`type_id`) USING BTREE, CONSTRAINT `FK_operations_arma_maps` FOREIGN KEY (`map_id`) REFERENCES `arma_maps` (`id`) ON UPDATE CASCADE ON DELETE RESTRICT, CONSTRAINT `FK_operations_calendar_events` FOREIGN KEY (`calendar_event_id`) REFERENCES `calendar_events` (`id`) ON UPDATE CASCADE ON DELETE RESTRICT, CONSTRAINT `FK_operations_campaigns` FOREIGN KEY (`campaign_id`) REFERENCES `campaigns` (`id`) ON UPDATE CASCADE ON DELETE RESTRICT, CONSTRAINT `FK_operations_members` FOREIGN KEY (`author_id`) REFERENCES `members` (`id`) ON UPDATE CASCADE ON DELETE RESTRICT, CONSTRAINT `FK_operations_operation_type` FOREIGN KEY (`type_id`) REFERENCES `operation_type` (`id`) ON UPDATE CASCADE ON DELETE RESTRICT ) COMMENT='Missions or Operations ' COLLATE='utf8mb4_general_ci' ENGINE=InnoDB AUTO_INCREMENT=1 ; ``` ### Create Operation Attendees ```sql CREATE TABLE `operations_attendees` ( `id` INT(11) NOT NULL AUTO_INCREMENT, `operation_id` INT(11) NOT NULL, `member_id` INT(11) NOT NULL, `group_id` INT(11) NULL DEFAULT NULL, `role_id` INT(11) NULL DEFAULT NULL, `join_time` DATETIME NULL DEFAULT NULL, `leave_time` DATETIME NULL DEFAULT NULL, `created_at` DATETIME NOT NULL DEFAULT current_timestamp(), `updated_at` DATETIME NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), `notes` DATETIME NULL DEFAULT NULL, `finalized` TINYINT(4) NOT NULL DEFAULT '0', PRIMARY KEY (`id`) USING BTREE, INDEX `FK_operations_attendees_operations` (`operation_id`) USING BTREE, INDEX `FK_operations_attendees_members` (`member_id`) USING BTREE, INDEX `FK_operations_attendees_structure_groups` (`group_id`) USING BTREE, INDEX `FK_operations_attendees_ingame_roles` (`role_id`) USING BTREE, CONSTRAINT `FK_operations_attendees_members` FOREIGN KEY (`member_id`) REFERENCES `members` (`id`) ON UPDATE CASCADE ON DELETE RESTRICT, CONSTRAINT `FK_operations_attendees_ingame_roles` FOREIGN KEY (`role_id`) REFERENCES `ingame_roles` (`id`) ON UPDATE CASCADE ON DELETE RESTRICT, CONSTRAINT `FK_operations_attendees_operations` FOREIGN KEY (`operation_id`) REFERENCES `operations` (`id`) ON UPDATE CASCADE ON DELETE RESTRICT, CONSTRAINT `FK_operations_attendees_structure_groups` FOREIGN KEY (`group_id`) REFERENCES `structure_group` (`id`) ON UPDATE CASCADE ON DELETE RESTRICT ) COLLATE='utf8mb4_general_ci' ENGINE=InnoDB AUTO_INCREMENT=6 ; ``` ### Create ingame_assets (OPTIONAL) ```sql CREATE TABLE `ingame_assets` ( `id` INT(11) NOT NULL AUTO_INCREMENT, `class_name` VARCHAR(100) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci', `category` ENUM('APC','Rotary','Truck','Boats','MRAP','TiltRotor','Cars','Fixed-Wing','Artillery','Armor','Primary weapons','Optics','Secondary weapons','Launchers') NOT NULL COLLATE 'utf8mb4_general_ci', `friendly_name` VARCHAR(100) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci', `description` TINYTEXT NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci', `image_name` VARCHAR(250) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci', `currently_approved` TINYINT(1) NULL DEFAULT '1', PRIMARY KEY (`id`) USING BTREE ) COLLATE='utf8mb4_general_ci' ENGINE=InnoDB AUTO_INCREMENT=252 ; ```

EagleTrooper changed title from ~~Database: Replace "Missoin" Tables with "Campaign/Operation" tables~~ to (Pending) Database: Replace "Mission" Tables with "Campaign/Operation" tables

2025-12-07 11:43:06 -06:00

Sign in to join this conversation.

1 Participants

Notifications

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: 17th-Ranger-Battalion-ORG/milsim-site-v4#59