Thomas Lohrum wrote:Please explain, why it is hard to implement. If i can understand the problem, i might be able to help to come up with a solution
Well, it will require some basic understanding of SQL, but you asked for it;)
Now CintaNotes is able to do search using a single SELECT database query using the LIKE operator to find the desired text.
However, this operator just does exact-phrase search. In order to do a more "loose" search, CN will have to do some preparation.
Say we are searching for [word1 word3] and want the note with text 'word0 word1 word2 word3 word4' to be displayed. CN will do the following:
1) split the search query into separate words: "word1", "word3"
2) construct a complex query with multiple LIKE operators: SELECT * FROM Notes WHERE text LIKE '%word1%' and text LIKE '%word3%'.
Seems simple, but.. we have to watch out for those quotes! what if we are searching for ["word0 word1" word2]? so we have to modify step 1:
1) split the search query into separate search phrases, respecting quotations: "word0 word1", "word2".
But this is still not all! If we do an overhaul of the search system, we need to fix a problem that was there before: supporting the "not" and "or" operators. Now they have the form of "-" for NOT and "|" for OR, and are only supported when the full-text index is used (namely, when the "exact search" checkbox is off).
So, we need to modify the algorithm once again:
1) analyse the query string and build a tree corresponding to the search operators used, where operators become tree nodes, and words or quoted phrases are leafs. This will require building an expression parser. So the query like "word1 word2|word3 -word4" becomes:
Code: Select all
__ AND _
/ | \
word1 OR NOT
/ \ \
word2 word3 word4
2) Transform the tree into SQL:
Code: Select all
SELECT * FROM Notes
WHERE (text LIKE '%word1%') AND ((text LIKE '%word2%') OR (text LIKE '%word3%')) AND NOT (text LIKE '%word4%')
3) Execute SQL and get the results.
Thomas Lohrum wrote:Well, CN is fast, really fast. Part of it might be SQLite, of course. I have created 800 records in the last ten months since i use CN and my machine uses a SSD. Speed is no concern to me. Speed sure will be different on different hardware and database size. My guess is, that it is still fast on those machines.
You see, since speed is CN's competitive advantage, I really hate to lose it;)
Thomas Lohrum wrote:I am confused about the term "full-text search facility". You mentioned a full-text index, which allows to search words at the beginning of a word only. Whatever it is called, i want to be able to do a search for words in any position of the note text. If this is slower, i'll accept. More important it is to me, to actually find the desired note!
Yes, I did mean full-text index, as implemented by the FTS3 SQLite extension which CN now uses.
What I meant: if you select "search inside words", full-text index won't be used and the search will be slower, but will bring you more results. This is the same as now. But unlike current behavior, the search will split the search string into separate words.
Thomas Lohrum wrote:I don't understand. What means "same way"? Searching for words separated by spaces in any part of the text is what i am looking for. However, i want this feature to always work.
Yes, "same way" meant "same as full-text index based search now does". And the latter does search for words separated into spaces, is just limited to search from word beginning because of the way full-text index is built.
Thomas Lohrum wrote:If we actually mean the same, as mentioned in my comments above, then Yes. Please also consider Chris' thread on searching tags, as this has (big) influence on the search result also.
Yes, I also agree with Chris' arguments.