distal-attribute
distal-attribute
distal-attribute
distal-attribute

UUID support in Multi Column Filter

bherila posted 1 year ago in Feature discussion

When querying a postgresql table that has a UUID column, the multi-column filter feature generates a filter that uses LIKE params. This of course doesn't work because UUID in PGSQL does not support the LIKE operator, so the column(s) in error must be removed manually. Can we exclude UUID columns from the multi-column filter?

It would be a little bit harder, but perhaps we could detect if the pasted string is a valid UUID and use the "=" operator for the UUID columns in that case :D

(Also adding my plus-1 on the Git thing -- I'd send a patch but I don't even have SVN installed on my system!)

Thanks,

Ben

ansgar posted 1 year ago

So, just for a conclusion:

  • In PostgreSQL mode, exclude UUID columns from a multi-column filter
  • ... except for when the value is detected as UUID, in which case the col LIKE '%foo%' should be col='foo' right?

Have a regular expression for detecting a valid PostgreSQL UUID string?

bherila posted 1 year ago

Sure, hope this helps

based on: http://www.postgresql.org/docs/8.3/static/datatype-uuid.html

I wrote this regex:

[a-f0-9]{8}(?:-?[a-f0-9]{4}){3}-?[a-f0-9]{12}|{[a-f0-9]{8}(?:-[a-f0-9]{4}){3}-[a-f0-9]{12}}

and then auto-generated this code, which I hope helps:

var
    Regex: TPerlRegEx;
    FoundMatch: Boolean;

Regex := TPerlRegEx.Create(nil);
Regex.RegEx := '\A(?:[a-f0-9]{8}(?:-?[a-f0-9]{4}){3}-?[a-f0-9]{12}|{[a-f0-9]{8}(?:-[a-f0-9]{4}){3}-[a-f0-9]{12}})\z';
Regex.Options := [preCaseless];
Regex.Subject := SubjectString;
FoundMatch := Regex.Match;
bherila posted 1 year ago

And posted too soon -- to answer your questions

In PostgreSQL mode, exclude UUID columns from a multi-column filter

Yes

... except for when the value is detected as UUID, in which case the col LIKE '%foo%' should be col='foo' right?

Yes

ansgar posted 1 year ago

r5057 introduces a regular expression per data type. I added expressions for UUID and some simple ones for INT, SMALLINT and BIGINT. The condition is left away if the value does not match the expression of the relevant data types, just like discussed above.

I can now easily extend this feature for other data types which throw SQL errors when the user enters a non-matching value. I'm not sure on which data types that makes also sense.

bherila posted 1 year ago

Awesome, thanks so much for helping with this!

bherila posted 1 year ago

P.S. if it's not too hard to add BOOL/BOOLEAN support for true|false that would help too (or exclude BOOL/BOOLEAN) columns.

[Window Title]
UC Postgres: Error

[Content]
ERROR:  syntax error at or near ""user_is21""
LINE 2: "user_is21" LIKE '%4bde2d2f%' OR "user_is_testaccount" LIKE ...
        ^

[OK]
ansgar posted 1 year ago

r5061 now accepts only true and false for a BOOLEAN column in PostgreSQL.

Please login to leave a reply, or register at first.