$select in table

Wed Mar 22 14:02:54 UTC 2023

Very interesting.

I remember the Df1 concatenation fields, and the extended index length of two fields as well, where the index length of a field covered the next field as well, giving a joint index.  Omnis 3 suff I think.

But this in PG is very clever, as we just search using the % on each field.

Oh, and I know the who the real Andy Hilton is, no need to search :)

Thanks

Mike Matthews

Lineal Software Solutions
Commercial House, The Strand<x-apple-data-detectors://1/1> Barnstaple, Devon, EX31 1EU<x-apple-data-detectors://1/1>

omnis at lineal.co.uk<mailto:mike.matthews at lineal.co.uk>

www.lineal.co.uk<http://www.lineal.co.uk/>

www.sqlworks.co.uk<http://www.sqlworks.co/>

On 22 Mar 2023, at 13:45, TBS <andyh at totallybrilliant.com<mailto:andyh at totallybrilliant.com>> wrote:

Caution: This is a message which has originated from outside the organisation. Ensure the sender is trusted and the content is safe before opening links or attachments.

WOW…….

This blows my mind !! Never knew about it but it looks just…..fabulous !!!!

Thank you as always Doug !! This will keep me busy for a while…..:)

Andy
On Mar 22, 2023, 9:15 AM -0400, Doug Easterbrook <doug at artsman.com<mailto:doug at artsman.com>>, wrote:
hi Andy.

you are right ..   it is a good thread.      how to find things in a database.  There are so many tools, so, I’ll introduce text search as well

I understand the ‘Andy' and ‘Hilton’ example by parsing the name and doing two like searches.     we did something similar in that we created a hidden field that had concatenated:  first name || last name || company || last name || first name.

we did that was some 20 years ago in omnis DF1 world and discovered it was great for ilike searching in postgres.      its a case of bringing old ideas into new technology.

then we found ‘postgres text search’ which is amazing for what it can do.   (it does have some limitations)

This lets us use postgres text search on every record which does a bunch of things that are useful, like making word order independent and helping with pluralization of some words and other lexemes.  this link might help explain how it works

https://www.postgresql.org/docs/current/textsearch-intro.html#TEXTSEARCH-MATCHING

what we did was:

1) make a field in each database table called XX_SEARCH. (replace XX_ with the prefix of the table)
2) created a trigger on the table that maintans XX_SEARCH with specified fields in that table (usually all the character and primary keys in the row)

3) create a function that builds the text search (included below) that to show what it might look like

# Creates a to_tsquery compatible search string
#
# Parameters
# pText->the text we want to turn into a 'tect search'
# pConnector-the connector for text search . either & or |

# remove :* which are the begins with search .. along with other white space chars. replace single quote, double quote with a space
Do tStringFields.$replace(pText,'''',' ',pText)
Do tStringFields.$replace(pText,'"',' ',pText)
Do tStringFields.$removechars(pText,con(':*''',chr(9,10,12,13)),kTMRemove,pText)

# remove double spaces
Do tStringFields.$trim(pText)
While pos(' ',pText)
Do tStringFields.$replace(pText,' ',' ',pText)
End While

# escape any other chars - eventually might allow & or |
Do tStringFields.$replace(pText,')','\)',pText)
Do tStringFields.$replace(pText,'(','\(',pText)
Do tStringFields.$replace(pText,'&','\&',pText)
Do tStringFields.$replace(pText,'!','\!',pText)
Do tStringFields.$replace(pText,'|','\|',pText)

# notthing provided, so return empty string
If len(pText)=0
Quit method pText
End If

# add words in the text search vector
Do tStringFields.$replace(pText,' ',con(':* ',pConnector,' '),pText)

# finish the string
Calculate pText as con("'",pText,":*'")

Quit method pText

4) the general gist of the program flow is

a) figure out what the text search should be
— SearchBox is a string like ‘Andy Hilton’ and is what the user types in to find in your database record
— and textSearch is the result of formatting it as per the function above

Calculate textSearch as tDatabase.$sqlTextSearch(iSearchBox,'&')

b) add it to the postgres where clause for text searching as per the following abbreviated example

Begin statement
Sta: Select * from F_CLIENT where
Sta:to_tsquery([textSearch]) @@ C_SEARCH)
End statement

note: we may also add in  iLike searches since its important to find people using any method that that may match

bottom line, its one more tool in the ‘how do you find things in a database when you want to be a bit more google-ish on the results returned.

there are even functions in text search that give a match % indication so you could have your results like

50% confidence -> this is the record for andy hilton
25% confidence -> this other record could be andy hilton

hopefully that idea makes sense.

what we have found in using this
1) the database size increases about 20% because each record now has the XX_SEARCH field that is indexed.    Not a bad tradeoff

2) using text search is quite fast, comparable to the performance of ilike with ‘%’ at both ends of the search term.