Xenos a écrit :Et si j'interdis de construire un vaisseau dont le plan utilise le module interdit, alors des joueurs seront hyper-désavantagés: ils ne pourront pas modifier leur plan de vaisseau avec le module interdit tant que des vaisseaux de ce type existent dans l'espace, et ils ne pourront pas construire de nouveaux vaisseaux de ce type.

CREATE TABLE `fleet` (

	`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,

	`id_player` INT(10) UNSIGNED NOT NULL,

	`id_orbit` INT UNSIGNED NOT NULL,

	`label` VARCHAR(100) NOT NULL COLLATE 'utf8mb4_bin',

	-- @todo Remove this "status" as it seems totally useless now

	`status` ENUM('ATTACK','DEFEND') NOT NULL COLLATE 'utf8mb4_bin'

		COMMENT 'An aggressive fleet will automatically and instantly attack every hostile fleet on the same OC,

				while a defensive fleet will leave the other fleet alone',

	`chronotons_quantity` INT(10) UNSIGNED DEFAULT 0 NOT NULL

		COMMENT 'Quantity of chronotons ships in this fleet holds at the specified date',

	`chronotons_date` DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,

	`extraction_mass_per_chronoton` INT UNSIGNED NOT NULL DEFAULT 0

		COMMENT 'Mass of atoms fleet can extract per chronoton',

	`atom_storage_capacity` INT UNSIGNED NOT NULL DEFAULT 0

		COMMENT 'Quantity of atoms you can store in all the ships',

	`construction_power` INT UNSIGNED NOT NULL DEFAULT 0

		COMMENT 'Multiply the chronotons quantity by this to get the constructions points of the fleet',

	PRIMARY KEY (`id`),

	UNIQUE INDEX `fleet_orbit` (`id_orbit`),

	-- A player cannot be deleted: you might only insert "fake credentials" in their account if needed

	UNIQUE INDEX `id_player_label` (`id_player`, `label`),

	CONSTRAINT `FK_fleet_orbit` FOREIGN KEY (`id_orbit`) REFERENCES `orbit` (`id`)

		ON UPDATE CASCADE,

	CONSTRAINT `FK_fleet_player` FOREIGN KEY (`id_player`) REFERENCES `player` (`id`)

		ON UPDATE CASCADE ON DELETE CASCADE

	-- Delete the fleet on deleting the player since fleet has no meaning (it is fleet_blueprint that must not be deleted)

	-- So if such FK cascades a DELETE, then it means the fleet was empty (no spaceship in it at all)

)

COLLATE='utf8mb4_bin'

ENGINE=InnoDB

COMMENT 'The fleets the players control'



CREATE TABLE `fleet_blueprint` (

	`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,

	`id_fleet` INT(10) UNSIGNED NOT NULL,

	`id_blueprint` INT(10) UNSIGNED NOT NULL,

	`number` INT(10) UNSIGNED NOT NULL,

	PRIMARY KEY (`id`),

	-- A same blueprint cannot appear twice in a fleet (easier interface later)

	UNIQUE INDEX `id_blueprint_id_fleet` (`id_blueprint`, `id_fleet`),

	CONSTRAINT `FK_fleet_blueprint_blueprint` FOREIGN KEY (`id_blueprint`) REFERENCES `blueprint` (`id`)

		ON UPDATE CASCADE,

	CONSTRAINT `FK_fleet_blueprint_fleet` FOREIGN KEY (`id_fleet`) REFERENCES `fleet` (`id`)

		ON UPDATE CASCADE

)

COLLATE='utf8mb4_bin'

ENGINE=InnoDB

COMMENT 'Spaceships (conceptions) in every fleet

Quantities are always "for all ships of this type in that fleet"'



CREATE TABLE `blueprint` (

	`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,

	`id_player` INT(10) UNSIGNED NOT NULL,

	`id_material` INT(10) UNSIGNED NOT NULL

		COMMENT 'The blueprint must be made of a single and already defined material

				Otherwise, it explodes too quickly: 5 different materials for 3 different conceptions = 15 combinations',

	`label` VARCHAR(100) NOT NULL COLLATE 'utf8mb4_bin',

	`id_grid` TINYINT(3) UNSIGNED NOT NULL

		COMMENT 'Each spaceship is an assembly of modules aligned on a specific 2D grid so this is the grid',

	`id_appearence` SMALLINT(5) UNSIGNED NOT NULL

		COMMENT 'It will be computed by some dynamic code, and the file number (with no extension) is saved.

				So if I change the dynamic code, then the conceptions already made will not change their appearence',

	`atom_storage_capacity` INT UNSIGNED DEFAULT 0 NOT NULL

		COMMENT 'Total amount of atoms you can store in that ship',

	`extraction_mass_per_chronoton` INT UNSIGNED DEFAULT 0 NOT NULL

		COMMENT 'The mass of atoms this ship can extract per chronoton; 0 if no extraction possible',

	`construction_power` INT UNSIGNED DEFAULT 0 NOT NULL

		COMMENT 'The capacity of construction this ship has',

	`construction_power_for_building` INT UNSIGNED DEFAULT 0 NOT NULL

		COMMENT 'The construction capacity required by a fleet to make this ship',

	`atom_quantity_factor_for_building` INT UNSIGNED NOT NULL DEFAULT 1000

		COMMENT 'Multiply the number of atoms of the material by this factor

				to get the real quantity of atoms you need to make this ship',

	`is_editable` BIT(1) NOT NULL DEFAULT 0,

	PRIMARY KEY (`id`),

	-- 'A label must be unique for a specific player, to identify the blueprint uniquely'

	UNIQUE INDEX `id_player_label` (`id_player`, `label`),

	-- 'One same material can be used for several different conceptions'

	INDEX `FK_blueprint_material` (`id_material`),

	INDEX `FK_blueprint_grid` (`id_grid`),

	-- 'The appearence is stored so if I change the algorithm, then existing ships are NOT altered'

	INDEX `FK_blueprint_appearence` (`id_appearence`),

	CONSTRAINT `FK_blueprint_appearence` FOREIGN KEY (`id_appearence`) REFERENCES `appearence` (`id`)

		ON UPDATE CASCADE,

	CONSTRAINT `FK_blueprint_grid` FOREIGN KEY (`id_grid`) REFERENCES `grid` (`id`)

		ON UPDATE CASCADE,

	CONSTRAINT `FK_blueprint_material` FOREIGN KEY (`id_material`) REFERENCES `material` (`id`)

		ON UPDATE CASCADE,

	CONSTRAINT `FK_blueprint_player` FOREIGN KEY (`id_player`) REFERENCES `player` (`id`)

		ON UPDATE CASCADE ON DELETE CASCADE

)

COLLATE='utf8mb4_bin'

ENGINE=InnoDB

COMMENT 'Players'' spaceship conceptions

They are made in a specific single material, so their characteristics are determined by the blueprint

and not in real time by blueprint+material'



CREATE TABLE `blueprint_module` (

	`id_blueprint` INT(10) UNSIGNED NOT NULL,

	`id_module` TINYINT(3) UNSIGNED NOT NULL,

	`id_grid_case` INT(10) UNSIGNED NOT NULL,

	-- A blueprint cannot have two modules on the same grid case (a grid case cannot appear twice for a blueprint)

	UNIQUE INDEX `id_blueprint_id_grid_case` (`id_blueprint`, `id_grid_case`),

	INDEX `FK_blueprint_module_module` (`id_module`),

	INDEX `FK_blueprint_module_grid_case` (`id_grid_case`),

	CONSTRAINT `FK_blueprint_module_blueprint` FOREIGN KEY (`id_blueprint`) REFERENCES `blueprint` (`id`)

		ON UPDATE CASCADE ON DELETE CASCADE,

	CONSTRAINT `FK_blueprint_module_grid_case` FOREIGN KEY (`id_grid_case`) REFERENCES `grid_case` (`id`)

		ON UPDATE CASCADE,

	CONSTRAINT `FK_blueprint_module_module` FOREIGN KEY (`id_module`) REFERENCES `module` (`id`)

		ON UPDATE CASCADE

)

COLLATE='utf8mb4_bin'

ENGINE=InnoDB

COMMENT 'The modules in the blueprint, placed on spaceship grid cases'



CREATE TABLE `module` (

	`id` TINYINT(3) UNSIGNED NOT NULL AUTO_INCREMENT,

	`name` VARCHAR(50) NOT NULL COLLATE 'utf8mb4_bin'

		COMMENT 'Code readable identifier in dash-lower-case,

				and avoid any special-* name since it is used for other purposes',

	`is_registering_required` TINYINT(1) UNSIGNED NOT NULL DEFAULT 1

		COMMENT 'Indicates which modules must be used when registering',

	`label` VARCHAR(50) NOT NULL COLLATE 'utf8mb4_bin',

	`image` VARCHAR(50) NOT NULL COLLATE 'utf8mb4_bin'

		COMMENT 'Name of the image resource file URL, with extension but without path',

	PRIMARY KEY (`id`),

	-- Code readable identifier (unique tho)

	UNIQUE INDEX `name` (`name`),

	-- Human readable identifier (so unique to make it non ambiguous)

	UNIQUE INDEX `label` (`label`),

	-- Two modules cannot share the same image, or it's gonna be confusing

	UNIQUE INDEX `image` (`image`)

)

COLLATE='utf8mb4_bin'

ENGINE=InnoDB

COMMENT 'The modules of the ship'