Yesterday I got filter and include properties to work, but the process was not that straight forward. It was my second attempt and it took over a hour with looking into the source code and debugging to get it working.
In our application we are using apache OJB as persistence layer so near all statements are generated and looks like the following one:
SELECT A0.FIRSTNAME,A0.LASTNAME,A0.BIRTHDATE,A0.PIDENT FROM MY_SCHEME.PERSON A0 WHERE (A0.PIDENT = 801) ORDER BY 2,3
And it is also how they are logged by p6spy. So the first thing I have tried was "include=PERSON" in spy.properties. It doesn't worked. Nothing get logged at all. "exclude=PERSON" hasn't worked too (all statements was logged as if filter=false was set).
Than I have tried to get sqlexpression to work. Here https://www.debuggex.com/r/blJkU7qV3cdcMxa4 I have created an expression matching above statement and inserted it into spy.properties with copy and paste as "sqlexpression = SELECT\s((\w+\.\w+,?)+)\sFROM\s\w+\.PERSON\s.+". It has not worked again :-). The simplified expression ".+\.PERSON\s.+" has not worked too.
Than it was time to look into the source code and to debug. While debugging sqlexpression I saw all backslashes was removed. I was not aware of it. So to get sqlexpression to work all backslashes should be escaped with one more backslash: "sqlexpression=.+\\.PERSON\\s.+". I think it is worth to be mentioned here http://p6spy.github.io/p6spy/2.0/configandusage.html and possibly directly in example spy.properties file.
After all I also get "include" and "exclude" to work (to some degree). The problem was my statements are upper case and I have written table name in upper case too. But current implementations only works if table names in "include" or "exclude" properties are lower case. It should be at least mentioned in the documentation and in example spy.properties file. Or even better it should be no difference between upper and lower case because the statement itself is converted to lower case before the pattern matching is done in P6LogQuery.isQueryOk().
Contrary to "include" and "exclude" properties if one works with "sqlexpression" than the table name should be written exactly as it appears in sql statement because this pattern is applied to original statement as it is (without first converting to lower case). So the behaviour is somehow inconsistent to how include/exclude-patterns are handled 12 row later in the same method.
The next thing there is following example "include = select" on the documentation page http://p6spy.github.io/p6spy/2.0/configandusage.html. It can't work either because values of "include"/"exclude" properties are converted to regex patterns "select.*from(.*((..." before they are matched against sql statements. So this example leads to regex pattern "select.*from(.*((select)).*)(where|;|$)" which would only match if the table name is "select".
And this is also the last point. Currently "include" and "exclude" works only for select statements. But it is not documented now and the documentation suggests one can filter for statement types using this properties.
Defect
13.2k Jan 5, 2023
2.6k Jan 7, 2023
547 Dec 30, 2022
37 Dec 12, 2022
1.6k Jan 6, 2023
43 Nov 30, 2022
1.3k Dec 29, 2022
6.4k Jan 6, 2023
3k Jan 4, 2023
12 Apr 19, 2021
10 Nov 29, 2022
7 Dec 2, 2022
16.6k Jan 4, 2023
3 Sep 2, 2021
111 Dec 14, 2021