I have a couple of questions about the audit log. There are a couple of fields in the Servoy built in audit table I would like to know the meaning of.
- pk_data field: Usually, it’ll be formatted as “4.23432;” or “6.23423;” . What does the number “4” or sometimes “5” or “6” mean?
I understand the “23423” is the primary key of the record in the corresponding table, but if someone can please tell me what the prefix means?
2)also, there’s another field called log_action. it would sometimes be “2” or “3” or “4”, etc. does each number corresponding to something?
Thanks!
sammy
The prefix is the length of the key. So a key like 123 will be 3.123. The reason is that the pk column has to be capable of taking several PKs and it has to be a text column, because not every PK is an integer. If you then want to search for a PK, you would be doing something like
SELECT * FROM log WHERE pk_data LIKE '%123%'
Now you have the problem that a pk like 1234 also meets the query above. But the query
SELECT * FROM log WHERE pk_data LIKE '%3.123%'
will not find 1234, because that is saved as 4.1234.
The log_action refers to INSERT, UPDATE and DELETE actions. I don’t know which is whcih out of my head, though…
Hope this helps.
Thanks for the info patrick. The reason why i ask is that i currently use the sql code with the LIKE statement. but because i did an indexing to speed up the search. I have to use a "where pk_data = ‘6.12345;’ for the indexing to take effect and speed up the search. Using a like statement doesn’t help. somehow I need to change my query to an equal statement to take advantage of the indexing.
patrick:
The prefix is the length of the key. So a key like 123 will be 3.123. The reason is that the pk column has to be capable of taking several PKs and it has to be a text column, because not every PK is an integer. If you then want to search for a PK, you would be doing something like
SELECT * FROM log WHERE pk_data LIKE '%123%'
Now you have the problem that a pk like 1234 also meets the query above. But the query
SELECT * FROM log WHERE pk_data LIKE '%3.123%'
will not find 1234, because that is saved as 4.1234.
The log_action refers to INSERT, UPDATE and DELETE actions. I don’t know which is whcih out of my head, though…
Hope this helps.
If you want to use an equal statement, just use
... WHERE pk_data = '4.1234;'
Note that you need to include the semicolon…
but in my log table, the first number changes from 3 to 4 to 5 to 6 (as my other tables keep growing)
I guess can just pick out the length of the pk first, put it in a var, then just include it in my WHERE statement. Thanks for your help and your idea! Now that I know exactly what the prefix means, i can work with it!
If someone from servoy can please answer what the Log_action codes mean, tha’tll be great!
patrick:
If you want to use an equal statement, just use
... WHERE pk_data = '4.1234;'
Note that you need to include the semicolon…
If someone from servoy can please answer what the Log_action codes mean, tha’tll be great!
This can be solved “by observation”. Just add a record and see what its written, change that record and see what its written and delete one and check the log…
In case someone else stumbles on this post and wants a quick answer to log_action numbering. Sure it’s semi easy to do the “by observation” but it would be nice if it was listed somewhere. It’s not in the pdf manuals.
1 - deletes
2 - inserts
3 - updates