In each HTTP query (e.g. /tool/checkStatusChange.php for a simple one) you have this repeating pattern
3 SELECT
select * from audit where audit.sessionId='q0c8b5p572psb0a3aucbvrjd09'
select * from audit where id=321094
select * from audit where id=321094
1 UPDATE
update audit set lastAccessDateTime='2020-12-30 09:58:52' , duration='00:08:50' , durationSeconds='530' , durationDisplay='00:08:50' where id=321094
Due to the fact that the PHP Audit object is not considered as a technical record, but as any other business record from the DB.
You might save one SELECT query by calling parent::simplesave instead of parent::save but I'm not sure of the side effects.
I suggest the following changes:
- Drop the column ID and replace the PK by the session id. Using an ID is not safe in a race condition as we have found duplicates in our DB.
- Make an exception, and do not use model/persistence function, but plain PDO prepare/execute calls for this class only.
- Use this simplified algorithm:
1 - Try to update the session by relying more on SQL DATE functions (compatible with MySQL and PostGres).
2 - If no row count is returned (PDOStatement::rowCount) by the update statement, insert a new record.
So most of the time you'd end up with only one update query per HTTP request.
Change audit PK patch
We have some duplicates as the previous code is not safe.
delete a1 from audit a1 INNER JOIN audit a2 WHERE a1.id < a2.id AND a1.sessionId = a2.sessionId;
alter table audit drop column id;
alter table audit
drop key auditSessionId,
add primary key(sessionId);
Rely on SQL DATE functions
Original query:
update audit set lastAccessDateTime='2020-12-30 09:58:52' , duration='00:08:50' , durationSeconds='530' , durationDisplay='00:08:50' where id=321094
New query (I don't know what is the dif between durationDisplay and duration):
update audit set
durationSeconds=TIMESTAMPDIFF(SECOND,lastAccessDateTime,NOW()),
durationDisplay=CONCAT(
Floor((TIMESTAMPDIFF(SECOND,lastAccessDateTime,NOW()) / (60*60))), ":",
Floor(MOD(TIMESTAMPDIFF(SECOND,lastAccessDateTime,NOW()), (60*60)) / 60), ":",
Floor(MOD(TIMESTAMPDIFF(SECOND,lastAccessDateTime,NOW()), (60)))
),
duration=CONCAT(
Floor((TIMESTAMPDIFF(SECOND,lastAccessDateTime,NOW()) / (60*60))), ":",
Floor(MOD(TIMESTAMPDIFF(SECOND,lastAccessDateTime,NOW()), (60*60)) / 60), ":",
Floor(MOD(TIMESTAMPDIFF(SECOND,lastAccessDateTime,NOW()), (60)))
),
lastAccessDateTime= NOW()
where sessionId = 'dl779d091jga85bcp0et76d9pr_20201231143108';
Update the code of Audit.php
There are some unused parameters and stuff that should be done elsewhere, such as:
if ($tz) date_default_timezone_set($tz); else date_default_timezone_set('Europe/Paris');