Welcome Guest, Not a member yet? Register   Sign In
logging db actions via model events and id problem
#1

(This post was last modified: 01-12-2022, 12:56 PM by dgvirtual.)

I am implementing a database writes log for my app through the Codeigniter 4 Model events ($afterInsert, $afterUpdate and $afterDelete). Each log entry (the log resides in a separate database table) should include info on (1) the database table,  (2) the row inserted/updated/deleted, (3) action type and  (4) the serialized content of update/insert.
So, for example, after each db update a callback function log_db_update($data) is invoked, which is supplied by the $data array, that normally should include, to quote the manual:

Quote:id = the array of primary keys of the rows being updated.
data = the key/value pairs being updated.
result
= the results of the update() method used through the Query Builder.
Now, the problem is, I do not always get the id for the hook when I update (or when I delete).
If the update code finds the data to update by id, let's say:
PHP Code:
$watchesModel->update($id$data); 
then I do  get the id of the updated row for the callback function. However, in many cases my update statements look like this: 
PHP Code:
$watchesModel->where('date'$date)->set($data)->update(); 
And then instead of an id of the updated row I get null value in the $data array that is passed to the callback function. Needless to say, it is not useful for my purposes.
I see I can only do one thing: rewrite the update statements into find & update statements, like this:
PHP Code:
$entry $watchesModel->where('date'$date)->first();
$watchesModel->update($entry->id$data); 
But that would be a painful refactoring of my code, not to mention the probable performance deterioration (in the case of my app it would not be noticeable at all, but that should be a consideration).
So, I have two questions:
1. Is there a better way to get what I want?
2. Is this behaviour of hooks something that I should report as a bug or "needs improvement" thing for the team developing Codeigniter? Or would changing this behaviour be too much to ask for (and if so, why)?
Donatas
==

Donatas G.
Reply
#2

(01-12-2022, 12:55 PM)dgvirtual Wrote: So, I have two questions:
1. Is there a better way to get what I want?
2. Is this behaviour of hooks something that I should report as a bug or "needs improvement" thing for the team developing Codeigniter? Or would changing this behaviour be too much to ask for (and if so, why)?

There is no ID value in your code. It seems it is impossible to know the ID value without querying to the DB.

It seems it is a bit better to extend CodeIgniter\Model event to add a query to lookup the ID value and pass it to the callback.
Reply
#3

(01-12-2022, 07:41 PM)kenjis Wrote:
(01-12-2022, 12:55 PM)dgvirtual Wrote: So, I have two questions:
1. Is there a better way to get what I want?
2. Is this behaviour of hooks something that I should report as a bug or "needs improvement" thing for the team developing Codeigniter? Or would changing this behaviour be too much to ask for (and if so, why)?

There is no ID value in your code. It seems it is impossible to know the ID value without querying to the DB.

It seems it is a bit better to extend CodeIgniter\Model event to add a query to lookup the ID value and pass it to the callback.
Ha, that sounds reasonable, but I have never done such an extension before. I looked up the BaseModel class of the Codeigniter and see no obvious way to do it...

If someone were to give me directions, that would perhaps ultimately benefit the whole community, so, I would be glad to do it with some help and write a tutorial later... Anyone?
==

Donatas G.
Reply
#4

First you need to extend the Model or use a trait by implementing the where() method.
1. The method must save all conditions.
2. The method will have to call the parent __call method in order for it to call the builder.

In the desired model event, you get the previously saved conditions and make a selection from the database with an id.

Perhaps another way will work for you. These are the triggers of the DBMS you are using.
Reply
#5

I’m with iRedds on this: you should either look into database triggers or limit your queries to ID-only updates. Trying to recreate ID-based events for queries involving other fields is going to add a ton of overhead because you will be doing mass reads and writes for every single call.
I have a library that does mostly the same thing you are doing, but it ignores non-ID queries. Feel free to check it out (due for a large update in the next week or so): https://github.com/tattersoftware/codeigniter4-audits
Reply
#6

Thank you @MGatner  and @iRedds. After thinking through this I decided that fixing my code is a cheaper option. First, I have found a lot of stupid code like
Code:
$watchesModel->where('id', $id)->set($data)->update();
That was easy to fix. Then, in some places the id of the entry was available in the code, just I happened not to use it in the query. I easily fixed those lines.
True, in some places I found I had to do a query first to get the id, but well, that was not so difficult after all.
And in some cases, where a query would update multiple rows at once, I decided logging those was not that critical after all. ) Angel
If anyone were interested, I could paste here the complete solution I am using now.
Thanks guys for help!
==

Donatas G.
Reply




Theme © iAndrew 2016 - Forum software by © MyBB