j.foucher/PS_Launcher
1
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity
Files
main
PS_Launcher/version/proserve-1.5.3/_migrations/0001_Init.sql
j.foucher 1acb735641 PROSERVE v1.5.3 migrations : idempotent SQLs (FK syntax fix) 
Schema source-of-truth for the proserveapi DB, versioned outside the
release ZIPs (the actual binaries live in version/ but are gitignored).

0001_Init.sql — full bootstrap of proserveapi (12 tables + view +
reference data + FKs). Reworked from a phpMyAdmin dump to be safely
re-runnable on:
  - fresh install (everything created)
  - existing customer DB pre-populated manually before launcher rollout
  - re-import via phpMyAdmin or mysql CLI
Idempotency tools used:
  - CREATE TABLE IF NOT EXISTS
  - INSERT IGNORE INTO ...  for reference rows
  - ADD PRIMARY KEY / KEY / UNIQUE KEY IF NOT EXISTS
  - ADD FOREIGN KEY IF NOT EXISTS `name` (...)  REFERENCES ...
    (note: MariaDB does NOT accept `ADD CONSTRAINT IF NOT EXISTS name
     FOREIGN KEY ...` — the IF NOT EXISTS goes after FOREIGN KEY, not
     after CONSTRAINT)
  - CREATE OR REPLACE VIEW
  - Removed the dump's `START TRANSACTION` / `COMMIT` (the launcher
    wraps each script in its own tx; embedded BEGIN/COMMIT would break
    the rollback semantics)
  - Removed `DEFINER=root@localhost` (uses CURRENT_USER, more portable)

0002_SessionType_ps_1.4.5.sql — adds id=7 'LongRange' + renames id=6
to 'Rescue'. Now uses INSERT IGNORE for the new row (skips if 0001
already inserted it on a fresh install) and the UPDATE is naturally
idempotent.

0003_UserSize_ps_1.5.0.sql — adds users.size column. Now uses
ADD COLUMN IF NOT EXISTS (the column is also in the recent 0001
baseline, so on a fresh install this will skip; on an upgrade from a
pre-0003 DB it actually adds it).

Validated locally on MariaDB 10.4 (XAMPP) with two consecutive runs of
each file: exit code 0 on every run, no errors.

.gitignore tightened so version/ only tracks _migrations/*.sql — the
~14 GB UE5 binaries and the _report/ build artifacts stay out of git.

Co-Authored-By: Claude Opus 4.7 (1M context) <noreply@anthropic.com>
2026-05-03 09:35:14 +02:00

356 lines
19 KiB
SQL
Raw Permalink Blame History

-- =============================================================================
-- 0001_Init.sql — schéma initial PROSERVE (proserveapi).
--
-- Idempotent : tout est en IF NOT EXISTS / INSERT IGNORE / ADD CONSTRAINT IF
-- NOT EXISTS, donc rejouable sur une DB déjà peuplée sans générer d'erreur.
-- Cible MariaDB 10.0.2+ (XAMPP standard depuis longtemps).
--
-- Note : pas de START TRANSACTION / COMMIT à l'intérieur du fichier — le
-- launcher (PSLauncher.Core.Migrations.DatabaseMigrationService) wrappe déjà
-- chaque .sql dans sa propre transaction pour pouvoir rollback proprement.
--
-- Source d'origine : dump phpMyAdmin 5.2.1 / MariaDB 10.4.32 (5 fév 2026).
-- =============================================================================
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";
SET NAMES utf8mb4;
CREATE DATABASE IF NOT EXISTS `proserveapi`
DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
USE `proserveapi`;
-- =============================================================================
-- TABLES
-- =============================================================================
CREATE TABLE IF NOT EXISTS `participates` (
`userId` int(11) NOT NULL COMMENT 'user id',
`sessionId` int(11) NOT NULL COMMENT 'session id',
`score` int(11) NOT NULL DEFAULT 0 COMMENT 'user score in the session',
`firePrecision` float NOT NULL DEFAULT 0 COMMENT 'user precision during the session',
`reactionTime` float NOT NULL DEFAULT 0 COMMENT 'average reaction time of the user in this session',
`nbEnemyHit` int(11) NOT NULL DEFAULT 0 COMMENT 'nb of enemies hit',
`nbCivilsHit` int(11) NOT NULL DEFAULT 0 COMMENT 'nb of civils hit',
`damageTaken` int(11) NOT NULL DEFAULT 0 COMMENT 'nb hits received',
`endStatus` int(1) NOT NULL DEFAULT 1 COMMENT 'user status at the end of the session (0=>dead, 1=>alive, add 2 for injured ?)',
`avatar` varchar(255) NOT NULL COMMENT 'avatar of user for this session',
`weapon` varchar(255) NOT NULL COMMENT 'weapon of user for this session',
`role` int(11) NOT NULL DEFAULT 0 COMMENT 'user role in the session',
`results` text NOT NULL DEFAULT '\'\'' COMMENT 'user''s results (objectives) in session (JSON format)'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `reacteventmodes` (
`id` int(11) NOT NULL COMMENT 'id',
`displayName` varchar(255) NOT NULL COMMENT 'name',
`description` varchar(255) NOT NULL COMMENT 'description'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `reactevents` (
`id` int(11) NOT NULL COMMENT 'event id',
`srcEventSessionId` int(11) NOT NULL COMMENT 'session id',
`srcEventIndex` int(11) NOT NULL COMMENT 'id of the triggering event that initiated this reaction event',
`reactType` int(11) NOT NULL COMMENT 'react event type',
`reactMode` int(11) NOT NULL DEFAULT 0 COMMENT 'react event mode (penetration, ricochet, termination)',
`hitUserId` int(11) NOT NULL DEFAULT 0 COMMENT 'id of the user that has been hit (0 if enemy/civil/env/game)',
`hitTargetName` varchar(255) NOT NULL COMMENT 'target hit by the event',
`hitBoneName` varchar(255) NOT NULL COMMENT 'bone hit on the target',
`damage` float NOT NULL DEFAULT 0 COMMENT 'damage points for the event',
`targetKilled` tinyint(1) NOT NULL DEFAULT 0 COMMENT 'did target got killed by the event ?',
`objectHitLocationX` float NOT NULL DEFAULT 0 COMMENT 'when hitting a target (object) record X position on texture',
`objectHitLocationY` float NOT NULL DEFAULT 0 COMMENT 'when hitting a target (object) record Y position on texture',
`objectHitTagLocation` varchar(255) NOT NULL COMMENT 'tag on the hit object',
`hitPrecision` float NOT NULL DEFAULT 1 COMMENT 'precision of the hit',
`distance` float NOT NULL DEFAULT 0 COMMENT 'distance from source to target hit',
`reactTime` float NOT NULL DEFAULT 0 COMMENT 'reaction time (for challenges)',
`timeStamp` float NOT NULL DEFAULT 0 COMMENT 'timeStamp react shot was registered'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `reacteventtypes` (
`id` int(11) NOT NULL COMMENT 'type id',
`displayName` varchar(255) NOT NULL COMMENT 'name',
`description` varchar(255) NOT NULL COMMENT 'description'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `sessions` (
`id` int(11) NOT NULL COMMENT 'session id',
`sessionType` int(11) NOT NULL COMMENT 'session type',
`sessionName` varchar(255) NOT NULL COMMENT 'session name',
`sessionDate` datetime NOT NULL DEFAULT current_timestamp() COMMENT 'date/time of the session',
`mapName` varchar(255) NOT NULL COMMENT 'map of the session',
`scenarioName` varchar(255) NOT NULL COMMENT 'scenario name that was played',
`success` tinyint(1) NOT NULL DEFAULT 0 COMMENT 'was the session successful (scenario objective reached) ?',
`timeToFinish` float NOT NULL DEFAULT 0 COMMENT 'session duration',
`score` int(11) NOT NULL DEFAULT 0 COMMENT 'user score',
`nbEnemyHit` int(11) NOT NULL DEFAULT 0 COMMENT 'nb of enemies hit',
`nbCivilsHit` int(11) NOT NULL DEFAULT 0 COMMENT 'nb of civils saved',
`damageTaken` float NOT NULL DEFAULT 0 COMMENT 'total damage taken',
`replayFileName` varchar(255) NOT NULL COMMENT 'filename of the session replay'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `sessiontypes` (
`id` int(11) NOT NULL COMMENT 'session type id',
`displayName` varchar(255) NOT NULL COMMENT 'displayed name of the session type',
`description` varchar(255) NOT NULL COMMENT 'description of the session type'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `triggerevents` (
`sessionId` int(11) NOT NULL COMMENT 'id of the user session during which the event was triggered',
`indexCount` int(11) NOT NULL COMMENT 'index of event in session',
`srcUserId` int(11) NOT NULL COMMENT 'id of the user that triggered the shot',
`type` int(11) NOT NULL COMMENT 'type of event',
`successful` tinyint(1) NOT NULL COMMENT 'was the shot (or triggered event) successful (hit a target) ?',
`timeStamp` float NOT NULL COMMENT 'time the event occurred at'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `triggereventtypes` (
`id` int(11) NOT NULL COMMENT 'event type id',
`displayName` varchar(255) NOT NULL COMMENT 'displayed name of the event type',
`description` varchar(255) NOT NULL COMMENT 'description of the event type'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `userroles` (
`id` int(11) NOT NULL COMMENT 'session type id',
`displayName` varchar(255) NOT NULL COMMENT 'displayed name of the session type',
`description` varchar(255) NOT NULL COMMENT 'description of the session type'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `users` (
`id` int(11) NOT NULL COMMENT 'user id',
`username` varchar(255) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL COMMENT 'username',
`password` varchar(255) NOT NULL COMMENT 'password',
`firstName` varchar(255) NOT NULL COMMENT 'first name',
`lastName` varchar(255) NOT NULL COMMENT 'last name',
`created` timestamp NOT NULL DEFAULT current_timestamp() COMMENT 'date/time of the creation of the user account',
`leftHanded` tinyint(1) NOT NULL DEFAULT 0 COMMENT 'is user left handed?',
`maleGender` tinyint(1) NOT NULL DEFAULT 1 COMMENT 'is user a man (true) or a woman?',
`charSkinAssetName` varchar(255) NOT NULL COMMENT 'name of the asset for the character skin',
`weaponAssetName` varchar(255) NOT NULL COMMENT 'name of the asset for the weapon',
`lastConnection` datetime NOT NULL DEFAULT current_timestamp() COMMENT 'date/time of the last connection',
`avgPrecision` float NOT NULL COMMENT 'average precision when firing',
`avgReaction` float NOT NULL COMMENT 'average reaction time',
`avgFault` float NOT NULL COMMENT 'average error rate',
`avgRapidity` float NOT NULL COMMENT 'average rapidity time',
`size` int(6) NOT NULL DEFAULT 175
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;
CREATE TABLE IF NOT EXISTS `userstatus` (
`id` int(11) NOT NULL COMMENT 'status id',
`displayName` varchar(255) NOT NULL COMMENT 'display name for status (alive, dead)',
`description` varchar(255) NOT NULL COMMENT 'description'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
-- =============================================================================
-- DONNÉES DE RÉFÉRENCE (INSERT IGNORE = skip silencieux si PK déjà présente)
-- =============================================================================
INSERT IGNORE INTO `reacteventmodes` (`id`, `displayName`, `description`) VALUES
(0, 'Penetration', ''),
(1, 'Ricochet', ''),
(2, 'Termination', '');
INSERT IGNORE INTO `reacteventtypes` (`id`, `displayName`, `description`) VALUES
(0, 'EnemyHit', 'An enemy has been hit'),
(1, 'CivilianHit', 'A civilian has been hit'),
(2, 'PoliceHit', 'Another player has been hit by himself'),
(3, 'ObjectHit', 'A target (object) has been hit'),
(4, 'PaperTargetHit', ''),
(5, 'TargetHit', ''),
(6, 'DeadBodyHit', 'An already-dead body has been hit');
INSERT IGNORE INTO `sessiontypes` (`id`, `displayName`, `description`) VALUES
(0, 'FireRange', ''),
(1, 'Challenge', ''),
(2, 'Protect', ''),
(3, 'De-Escalation', ''),
(4, 'Terrorism', ''),
(5, 'FireExtinction', ''),
(6, 'Rescue', ''),
(7, 'LongRange', '');
INSERT IGNORE INTO `triggereventtypes` (`id`, `displayName`, `description`) VALUES
(0, 'Fire', 'Player has fired');
INSERT IGNORE INTO `userroles` (`id`, `displayName`, `description`) VALUES
(0, 'Police', 'Player is Police/SWAT'),
(1, 'Enemy', 'Player is an Enemy'),
(2, 'Civil', 'Player is a Civilian'),
(3, 'IA', 'Role for IA and NPC');
INSERT IGNORE INTO `userstatus` (`id`, `displayName`, `description`) VALUES
(0, 'Alive', 'avatar is alive'),
(1, 'Lightly Injured', 'avatar has been slightly injured'),
(2, 'Seriously Injured', 'avatar has been seriously injured'),
(3, 'Dead', 'avatar is dead');
-- Comptes utilisateurs initiaux (system + 2 démos). INSERT IGNORE évite
-- d'écraser ce que le client aurait déjà créé si la table users contient
-- des id=0/1/2 différents.
INSERT IGNORE INTO `users` (`id`, `username`, `password`, `firstName`, `lastName`, `created`, `leftHanded`, `maleGender`, `charSkinAssetName`, `weaponAssetName`, `lastConnection`, `avgPrecision`, `avgReaction`, `avgFault`, `avgRapidity`, `size`) VALUES
(0, 'game', '', '', '', '2023-01-24 10:52:35', 0, 0, '', '', '2023-01-24 11:52:35', 0, 0, 0, 0, 0),
(1, 'Jerome', 'Q29udHJvbDM1', 'Jerome', '', '2026-02-05 18:06:06', 0, 1, 'PS_PAWN_VR_V2_POLICE_ARABIC_C', 'Weapon_G17_C', '2026-02-05 12:31:59', 0, 0, 0, 0, 171),
(2, 'Jerome2', 'Q29udHJvbDM1', 'Jerome2', '', '2026-02-05 18:06:24', 0, 1, 'PS_PAWN_VR_V2_POLICE_BLACK_C', 'Weapon_G17_C', '2026-02-05 12:32:23', 0, 0, 0, 0, 175);
-- =============================================================================
-- INDEX & CONTRAINTES (toutes en IF NOT EXISTS pour idempotence)
-- =============================================================================
-- participates : PK composite (userId, sessionId) + FKs
ALTER TABLE `participates`
ADD PRIMARY KEY IF NOT EXISTS (`userId`,`sessionId`),
ADD KEY IF NOT EXISTS `FK_Participates_SessionId` (`sessionId`),
ADD KEY IF NOT EXISTS `FK_Participates_UserStatus` (`endStatus`),
ADD KEY IF NOT EXISTS `FK_Participates_UserRole` (`role`);
ALTER TABLE `reacteventmodes`
ADD PRIMARY KEY IF NOT EXISTS (`id`);
ALTER TABLE `reactevents`
ADD PRIMARY KEY IF NOT EXISTS (`id`),
ADD KEY IF NOT EXISTS `FK_React_TypeId` (`reactType`),
ADD KEY IF NOT EXISTS `FK_React_HitUserId` (`hitUserId`),
ADD KEY IF NOT EXISTS `FK_React_EventIndex` (`srcEventIndex`),
ADD KEY IF NOT EXISTS `FK_React_SessionId` (`srcEventSessionId`),
ADD KEY IF NOT EXISTS `FK_React_Mode` (`reactMode`);
ALTER TABLE `reacteventtypes`
ADD PRIMARY KEY IF NOT EXISTS (`id`);
ALTER TABLE `sessions`
ADD PRIMARY KEY IF NOT EXISTS (`id`),
ADD KEY IF NOT EXISTS `FK_sessionType` (`sessionType`) USING BTREE;
ALTER TABLE `sessiontypes`
ADD PRIMARY KEY IF NOT EXISTS (`id`);
ALTER TABLE `triggerevents`
ADD PRIMARY KEY IF NOT EXISTS (`indexCount`,`sessionId`),
ADD KEY IF NOT EXISTS `FK_TriggerEvent_SrcUserId` (`srcUserId`),
ADD KEY IF NOT EXISTS `FK_TriggerEvent_Type` (`type`),
ADD KEY IF NOT EXISTS `FK_TriggerEvent_UserId` (`sessionId`);
ALTER TABLE `triggereventtypes`
ADD PRIMARY KEY IF NOT EXISTS (`id`);
ALTER TABLE `userroles`
ADD PRIMARY KEY IF NOT EXISTS (`id`);
ALTER TABLE `users`
ADD PRIMARY KEY IF NOT EXISTS (`id`),
ADD UNIQUE KEY IF NOT EXISTS `username` (`username`);
ALTER TABLE `userstatus`
ADD PRIMARY KEY IF NOT EXISTS (`id`);
-- =============================================================================
-- AUTO_INCREMENT (idempotent : MariaDB ignore la valeur si MAX(id) la dépasse)
-- =============================================================================
ALTER TABLE `reactevents`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'event id';
ALTER TABLE `sessions`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'session id';
ALTER TABLE `users`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'user id';
-- =============================================================================
-- FOREIGN KEYS
-- Syntaxe : ADD FOREIGN KEY IF NOT EXISTS `name` (...) REFERENCES ...
-- (NOTE : en MariaDB, le `CONSTRAINT name FOREIGN KEY` n'accepte PAS de clause
-- `IF NOT EXISTS` ; en revanche `FOREIGN KEY IF NOT EXISTS name` est supporté
-- depuis 10.0.2 et est strictement équivalent côté résultat.)
-- =============================================================================
ALTER TABLE `participates`
ADD FOREIGN KEY IF NOT EXISTS `FK_Participates_SessionId` (`sessionId`) REFERENCES `sessions` (`id`) ON DELETE CASCADE,
ADD FOREIGN KEY IF NOT EXISTS `FK_Participates_UserId` (`userId`) REFERENCES `users` (`id`) ON DELETE CASCADE,
ADD FOREIGN KEY IF NOT EXISTS `FK_Participates_UserRole` (`role`) REFERENCES `userroles` (`id`),
ADD FOREIGN KEY IF NOT EXISTS `FK_Participates_UserStatus` (`endStatus`) REFERENCES `userstatus` (`id`);
ALTER TABLE `reactevents`
ADD FOREIGN KEY IF NOT EXISTS `FK_React_HitUserId` (`hitUserId`) REFERENCES `users` (`id`),
ADD FOREIGN KEY IF NOT EXISTS `FK_React_Mode` (`reactMode`) REFERENCES `reacteventmodes` (`id`),
ADD FOREIGN KEY IF NOT EXISTS `FK_React_SessionId` (`srcEventSessionId`) REFERENCES `sessions` (`id`) ON DELETE CASCADE,
ADD FOREIGN KEY IF NOT EXISTS `FK_React_TypeId` (`reactType`) REFERENCES `reacteventtypes` (`id`);
ALTER TABLE `sessions`
ADD FOREIGN KEY IF NOT EXISTS `FK_SessionType` (`sessionType`) REFERENCES `sessiontypes` (`id`);
ALTER TABLE `triggerevents`
ADD FOREIGN KEY IF NOT EXISTS `FK_TriggerEvent_SrcUserId` (`srcUserId`) REFERENCES `users` (`id`) ON DELETE CASCADE,
ADD FOREIGN KEY IF NOT EXISTS `FK_TriggerEvent_Type` (`type`) REFERENCES `triggereventtypes` (`id`),
ADD FOREIGN KEY IF NOT EXISTS `FK_TriggerEvent_UserId` (`sessionId`) REFERENCES `sessions` (`id`) ON DELETE CASCADE;
-- =============================================================================
-- VUE sessiondebriefs (CREATE OR REPLACE = idempotent par définition)
-- =============================================================================
CREATE OR REPLACE
ALGORITHM=UNDEFINED
SQL SECURITY DEFINER
VIEW `sessiondebriefs` AS
SELECT
`s`.`id` AS `SessionId`,
`s`.`sessionType` AS `SessionTypeId`,
`st`.`displayName` AS `SessionType`,
`s`.`sessionName` AS `SessionName`,
`s`.`sessionDate` AS `SessionDate`,
`s`.`mapName` AS `MapName`,
`s`.`scenarioName` AS `ScenarioName`,
`s`.`success` AS `SessionSuccessful`,
`s`.`timeToFinish` AS `SessionDuration`,
coalesce(`t`.`type`, -1) AS `TriggerTypeId`,
coalesce(`tt`.`displayName`, '') AS `TriggerType`,
(SELECT ifnull(`t`.`srcUserId`, `ps`.`userId`)) AS `ShooterId`,
`us`.`username` AS `ShooterName`,
(SELECT ifnull(`ps`.`role`, 3)) AS `ShooterRoleId`,
(SELECT `r`.`displayName` FROM `userroles` `r` WHERE `r`.`id` = `ShooterRoleId`) AS `ShooterRole`,
coalesce(`t`.`indexCount`, -1) AS `ShotIndex`,
coalesce(`r`.`id`, -1) AS `ReactId`,
coalesce(`r`.`reactMode`, 2) AS `ReactModeId`,
coalesce(`rm`.`displayName`, '') AS `ReactMode`,
coalesce(`r`.`reactType`, -1) AS `ReactTypeId`,
coalesce(`rt`.`displayName`, '') AS `ReactType`,
coalesce(`r`.`hitUserId`, -1) AS `TargetUserId`,
coalesce(`uh`.`username`, '') AS `TargetUserName`,
(SELECT ifnull(`ph`.`role`, 3)) AS `TargetRoleId`,
(SELECT `r`.`displayName` FROM `userroles` `r` WHERE `r`.`id` = `TargetRoleId`) AS `TargetRole`,
coalesce(`r`.`hitTargetName`, '') AS `TargetName`,
coalesce(`r`.`hitBoneName`, '') AS `TargetBoneName`,
coalesce(`r`.`targetKilled`, 0) AS `TargetKilled`,
coalesce(`r`.`objectHitLocationX`, 0) AS `HitLocationX`,
coalesce(`r`.`objectHitLocationY`, 0) AS `HitLocationY`,
coalesce(`r`.`objectHitTagLocation`, '') AS `HitLocationTag`,
coalesce(`r`.`hitPrecision`, 0) AS `HitPrecision`,
coalesce(`r`.`distance`, 0) AS `HitTargetDistance`,
coalesce(`r`.`reactTime`, 0) AS `ReactionTime`,
coalesce(`r`.`timeStamp`, 0) AS `TimeStamp`,
count(distinct `r`.`id`) AS `NbHit`,
count(distinct `rk`.`srcEventIndex`, `rk`.`hitTargetName`) AS `NbKilled`
FROM `sessions` `s`
LEFT JOIN `participates` `ps` ON `s`.`id` = `ps`.`sessionId`
LEFT JOIN `participates` `ph` ON `s`.`id` = `ph`.`sessionId`
LEFT JOIN `triggerevents` `t` ON `s`.`id` = `t`.`sessionId`
LEFT JOIN `triggereventtypes` `tt` ON `tt`.`id` = `t`.`type`
LEFT JOIN `sessiontypes` `st` ON `st`.`id` = `s`.`sessionType`
LEFT JOIN `reactevents` `r` ON `t`.`indexCount` = `r`.`srcEventIndex`
AND `t`.`sessionId` = `r`.`srcEventSessionId`
LEFT JOIN `users` `uh` ON `uh`.`id` = `r`.`hitUserId`
LEFT JOIN `users` `us` ON `us`.`id` = `t`.`srcUserId`
OR `us`.`id` = `ps`.`userId`
LEFT JOIN `reacteventtypes` `rt` ON `rt`.`id` = `r`.`reactType`
LEFT JOIN `reacteventmodes` `rm` ON `rm`.`id` = coalesce(`r`.`reactMode`, 2)
LEFT JOIN `reactevents` `rk` ON `r`.`id` = `rk`.`id` AND `rk`.`targetKilled` = 1
GROUP BY
`s`.`id`,
(SELECT ifnull(`t`.`srcUserId`, `ps`.`userId`)),
`t`.`indexCount`,
`r`.`hitTargetName`
ORDER BY
`s`.`id` ASC,
(SELECT ifnull(`t`.`srcUserId`, `ps`.`userId`)) ASC,
`t`.`indexCount` ASC,
`r`.`id` ASC;
Reference in New Issue View Git Blame Copy Permalink