Forum

Welcome to ProjeQtOr new Forum. We migrated old forum to the new website.
You will find all your posts here, with your usual account.
Just one point : you’ll have to reinitialize your password. Use “Lost password” feature.

[PROPOSAL] Change d...
 
Notifications
Clear all

[PROPOSAL] Change design of audit feature

2 Posts
2 Users
0 Reactions
1,792 Views
(@bbalet)
Posts: 10
Active Member
Topic starter
 
[#7150]

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');
 


 
Posted : 03 Jan 2021 22H13
babynus
(@babynus)
Posts: 14953
Main Contributor Admin
 

Hi,

Proposale recorded as Ticket #5153

Thanks for your analysis


 
Posted : 04 Jan 2021 16H57
Share:

Scroll to Top