j.foucher
68cea1eb4d
- BDD (migrations 1.6.0 + 1.7.0, idempotentes) :
* 4 nouveaux index composites sur reactevents, triggerevents, sessions
* vue sessiondebriefs reecrite (sous-requetes correlees -> JOINs)
puis materialisee dans table session_debriefs_cache via procedure
stockee rebuild_debrief_cache, recalculee uniquement aux changements
* fix bug double JOIN participates : TargetRole = "IA" pour les hits
NPC/objets (au lieu d'un role aleatoire de participant)
- API PHP :
* connexion PDO en singleton + ATTR_PERSISTENT
* cache disque par session (cache/sess_<id>/) pour debrief, get, userhistory
avec invalidation ciblee sur events et lifecycle
* pagination opt-in (limit/offset) sur lists/sessions_for_user
* helpers ensure_/rebuild_/invalidate_session_debrief_cache_db
* debug/benchmark.php pour mesurer la latence des 6 endpoints cles
- Endpoints HTTP : signatures inchangees, compat Unreal preservee
- Gain mesure : -58% a -83% sur les 5 endpoints cles
Co-Authored-By: Claude Opus 4.7 (1M context) <noreply@anthropic.com>
237 lines
10 KiB
SQL
237 lines
10 KiB
SQL
-- ============================================================
|
|
-- Migration 1.7.0 - Materialisation de la vue sessiondebriefs
|
|
-- ============================================================
|
|
-- Cible : MariaDB 10.4+.
|
|
-- Idempotent : peut etre rejoue sans erreur. Le cache est entierement
|
|
-- reconstruit a chaque execution (TRUNCATE + backfill).
|
|
--
|
|
-- Changement principal : la vue sessiondebriefs etait recalculee a chaque
|
|
-- requete (12 LEFT JOIN + GROUP BY + sous-requetes correlees). Elle est
|
|
-- desormais materialisee dans une vraie table session_debriefs_cache,
|
|
-- peuplee :
|
|
-- - en bulk au moment de cette migration (backfill toutes sessions),
|
|
-- - puis par une procedure stockee rebuild_debrief_cache(p_session_id)
|
|
-- appelee depuis le code PHP a chaque modification de session
|
|
-- (session/stop, session/userleave, session/updateobjectives,
|
|
-- events/storetriggerevent, events/storereactevent).
|
|
--
|
|
-- La vue sessiondebriefs garde son nom et ses colonnes : le code consommateur
|
|
-- (stats_object.php : getResultsForSession, getUserHistory) ne change pas.
|
|
-- Elle devient un simple passe-plat sur la table cache.
|
|
--
|
|
-- Action 2 (fix partiel du double JOIN participates) : la jointure PH
|
|
-- (qui sert a determiner TargetRoleId/TargetRole) est desormais contrainte
|
|
-- sur PH.userId = R.hitUserId au lieu de matcher tous les participants.
|
|
-- Effet :
|
|
-- * Pour les hits user-on-user : meme resultat (correct par construction).
|
|
-- * Pour les hits NPC/objets : TargetRoleId vaut 3 (IA) au lieu d'un
|
|
-- role aleatoire d'un autre participant. C'est un fix de bug.
|
|
-- La jointure PS reste inchangee (preserve le fallback ShooterId quand T est NULL).
|
|
-- ============================================================
|
|
|
|
USE proserveapi;
|
|
|
|
-- ------------------------------------------------------------
|
|
-- 1) Table de cache (creation idempotente)
|
|
-- ------------------------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS `session_debriefs_cache` (
|
|
`SessionId` int(11) NOT NULL,
|
|
`SessionTypeId` int(11) DEFAULT NULL,
|
|
`SessionType` varchar(255) DEFAULT NULL,
|
|
`SessionName` varchar(255) DEFAULT NULL,
|
|
`SessionDate` datetime DEFAULT NULL,
|
|
`MapName` varchar(255) DEFAULT NULL,
|
|
`ScenarioName` varchar(255) DEFAULT NULL,
|
|
`SessionSuccessful` tinyint(1) DEFAULT NULL,
|
|
`SessionDuration` float DEFAULT NULL,
|
|
`TriggerTypeId` int(11) DEFAULT NULL,
|
|
`TriggerType` varchar(255) DEFAULT NULL,
|
|
`ShooterId` int(11) DEFAULT NULL,
|
|
`ShooterName` varchar(255) DEFAULT NULL,
|
|
`ShooterRoleId` int(11) DEFAULT NULL,
|
|
`ShooterRole` varchar(255) DEFAULT NULL,
|
|
`ShotIndex` int(11) DEFAULT NULL,
|
|
`ReactId` int(11) DEFAULT NULL,
|
|
`ReactModeId` int(11) DEFAULT NULL,
|
|
`ReactMode` varchar(255) DEFAULT NULL,
|
|
`ReactTypeId` int(11) DEFAULT NULL,
|
|
`ReactType` varchar(255) DEFAULT NULL,
|
|
`TargetUserId` int(11) DEFAULT NULL,
|
|
`TargetUserName` varchar(255) DEFAULT NULL,
|
|
`TargetRoleId` int(11) DEFAULT NULL,
|
|
`TargetRole` varchar(255) DEFAULT NULL,
|
|
`TargetName` varchar(255) DEFAULT NULL,
|
|
`TargetBoneName` varchar(255) DEFAULT NULL,
|
|
`TargetKilled` int(4) DEFAULT NULL,
|
|
`HitLocationX` double DEFAULT NULL,
|
|
`HitLocationY` double DEFAULT NULL,
|
|
`HitLocationTag` varchar(255) DEFAULT NULL,
|
|
`HitPrecision` double DEFAULT NULL,
|
|
`HitTargetDistance` double DEFAULT NULL,
|
|
`ReactionTime` double DEFAULT NULL,
|
|
`TimeStamp` double DEFAULT NULL,
|
|
`NbHit` bigint(21) DEFAULT NULL,
|
|
`NbKilled` bigint(21) DEFAULT NULL,
|
|
KEY `idx_sdc_session` (`SessionId`),
|
|
KEY `idx_sdc_session_shooter` (`SessionId`, `ShooterId`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
|
|
|
|
|
|
-- ------------------------------------------------------------
|
|
-- 2) Procedure stockee pour reconstruire le cache d'une session
|
|
-- ------------------------------------------------------------
|
|
DROP PROCEDURE IF EXISTS rebuild_debrief_cache;
|
|
|
|
DELIMITER $$
|
|
CREATE PROCEDURE rebuild_debrief_cache(IN p_session_id INT)
|
|
BEGIN
|
|
DELETE FROM session_debriefs_cache WHERE SessionId = p_session_id;
|
|
|
|
INSERT INTO session_debriefs_cache (
|
|
SessionId, SessionTypeId, SessionType, SessionName, SessionDate,
|
|
MapName, ScenarioName, SessionSuccessful, SessionDuration,
|
|
TriggerTypeId, TriggerType, ShooterId, ShooterName,
|
|
ShooterRoleId, ShooterRole, ShotIndex, ReactId, ReactModeId,
|
|
ReactMode, ReactTypeId, ReactType, TargetUserId, TargetUserName,
|
|
TargetRoleId, TargetRole, TargetName, TargetBoneName, TargetKilled,
|
|
HitLocationX, HitLocationY, HitLocationTag, HitPrecision,
|
|
HitTargetDistance, ReactionTime, TimeStamp, NbHit, NbKilled
|
|
)
|
|
SELECT
|
|
S.id,
|
|
S.sessionType,
|
|
ST.displayName,
|
|
S.sessionName,
|
|
S.sessionDate,
|
|
S.mapName,
|
|
S.scenarioName,
|
|
S.success,
|
|
S.timeToFinish,
|
|
COALESCE(T.type, -1),
|
|
COALESCE(TT.displayName, ''),
|
|
IFNULL(T.srcUserId, PS.userId),
|
|
US.username,
|
|
IFNULL(PS.role, 3),
|
|
URS.displayName,
|
|
COALESCE(T.indexCount, -1),
|
|
COALESCE(R.id, -1),
|
|
COALESCE(R.reactMode, 2),
|
|
COALESCE(RM.displayName, ''),
|
|
COALESCE(R.reactType, -1),
|
|
COALESCE(RT.displayName, ''),
|
|
COALESCE(R.hitUserId, -1),
|
|
COALESCE(UH.username, ''),
|
|
IFNULL(PH.role, 3),
|
|
URT.displayName,
|
|
COALESCE(R.hitTargetName, ''),
|
|
COALESCE(R.hitBoneName, ''),
|
|
COALESCE(R.targetKilled, 0),
|
|
COALESCE(R.objectHitLocationX, 0),
|
|
COALESCE(R.objectHitLocationY, 0),
|
|
COALESCE(R.objectHitTagLocation, ''),
|
|
COALESCE(R.hitPrecision, 0),
|
|
COALESCE(R.distance, 0),
|
|
COALESCE(R.reactTime, 0),
|
|
COALESCE(R.timeStamp, 0),
|
|
COUNT(DISTINCT R.id),
|
|
COUNT(DISTINCT RK.srcEventIndex, RK.hitTargetName)
|
|
FROM sessions S
|
|
LEFT JOIN participates PS ON (S.id = PS.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)
|
|
-- PH : Action 2 du fix - PH represente la participation de l'utilisateur cible
|
|
-- (au lieu de "n'importe quel participant") pour que TargetRole soit coherent.
|
|
LEFT JOIN participates PH ON (S.id = PH.sessionId AND PH.userId = R.hitUserId)
|
|
LEFT JOIN userroles URS ON (URS.id = IFNULL(PS.role, 3))
|
|
LEFT JOIN userroles URT ON (URT.id = IFNULL(PH.role, 3))
|
|
WHERE S.id = p_session_id
|
|
GROUP BY S.id, IFNULL(T.srcUserId, PS.userId), COALESCE(T.indexCount, -1), COALESCE(R.hitTargetName, '')
|
|
ORDER BY S.id, IFNULL(T.srcUserId, PS.userId), COALESCE(T.indexCount, -1), COALESCE(R.id, -1);
|
|
END$$
|
|
DELIMITER ;
|
|
|
|
|
|
-- ------------------------------------------------------------
|
|
-- 3) Backfill complet : recalcule le cache pour toutes les sessions existantes
|
|
-- ------------------------------------------------------------
|
|
TRUNCATE TABLE session_debriefs_cache;
|
|
|
|
INSERT INTO session_debriefs_cache (
|
|
SessionId, SessionTypeId, SessionType, SessionName, SessionDate,
|
|
MapName, ScenarioName, SessionSuccessful, SessionDuration,
|
|
TriggerTypeId, TriggerType, ShooterId, ShooterName,
|
|
ShooterRoleId, ShooterRole, ShotIndex, ReactId, ReactModeId,
|
|
ReactMode, ReactTypeId, ReactType, TargetUserId, TargetUserName,
|
|
TargetRoleId, TargetRole, TargetName, TargetBoneName, TargetKilled,
|
|
HitLocationX, HitLocationY, HitLocationTag, HitPrecision,
|
|
HitTargetDistance, ReactionTime, TimeStamp, NbHit, NbKilled
|
|
)
|
|
SELECT
|
|
S.id,
|
|
S.sessionType,
|
|
ST.displayName,
|
|
S.sessionName,
|
|
S.sessionDate,
|
|
S.mapName,
|
|
S.scenarioName,
|
|
S.success,
|
|
S.timeToFinish,
|
|
COALESCE(T.type, -1),
|
|
COALESCE(TT.displayName, ''),
|
|
IFNULL(T.srcUserId, PS.userId),
|
|
US.username,
|
|
IFNULL(PS.role, 3),
|
|
URS.displayName,
|
|
COALESCE(T.indexCount, -1),
|
|
COALESCE(R.id, -1),
|
|
COALESCE(R.reactMode, 2),
|
|
COALESCE(RM.displayName, ''),
|
|
COALESCE(R.reactType, -1),
|
|
COALESCE(RT.displayName, ''),
|
|
COALESCE(R.hitUserId, -1),
|
|
COALESCE(UH.username, ''),
|
|
IFNULL(PH.role, 3),
|
|
URT.displayName,
|
|
COALESCE(R.hitTargetName, ''),
|
|
COALESCE(R.hitBoneName, ''),
|
|
COALESCE(R.targetKilled, 0),
|
|
COALESCE(R.objectHitLocationX, 0),
|
|
COALESCE(R.objectHitLocationY, 0),
|
|
COALESCE(R.objectHitTagLocation, ''),
|
|
COALESCE(R.hitPrecision, 0),
|
|
COALESCE(R.distance, 0),
|
|
COALESCE(R.reactTime, 0),
|
|
COALESCE(R.timeStamp, 0),
|
|
COUNT(DISTINCT R.id),
|
|
COUNT(DISTINCT RK.srcEventIndex, RK.hitTargetName)
|
|
FROM sessions S
|
|
LEFT JOIN participates PS ON (S.id = PS.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)
|
|
LEFT JOIN participates PH ON (S.id = PH.sessionId AND PH.userId = R.hitUserId)
|
|
LEFT JOIN userroles URS ON (URS.id = IFNULL(PS.role, 3))
|
|
LEFT JOIN userroles URT ON (URT.id = IFNULL(PH.role, 3))
|
|
GROUP BY S.id, IFNULL(T.srcUserId, PS.userId), COALESCE(T.indexCount, -1), COALESCE(R.hitTargetName, '')
|
|
ORDER BY S.id, IFNULL(T.srcUserId, PS.userId), COALESCE(T.indexCount, -1), COALESCE(R.id, -1);
|
|
|
|
|
|
-- ------------------------------------------------------------
|
|
-- 4) La vue devient un passe-plat sur la table cache
|
|
-- ------------------------------------------------------------
|
|
CREATE OR REPLACE VIEW sessiondebriefs AS
|
|
SELECT * FROM session_debriefs_cache;
|