On Wed, 13 Apr 2016 19:41:34 +0200 R Smith <rsmith at rsweb.co.za> wrote:
> Or did you mean you publish an interface of sorts in your application > that allows users to run their own queries, but it /has/ to be an "fts5" > query in some way, and now sometimes they want to list all rows? It is > quite baffling - but I might simply not understand the situation, > perhaps some more information on the setup would help us see the light > and help us to offer better suggestions than this :) > Well, OK, but the explanation is not very short. :) I am using SQLite as a storage back-end for my web forum engine (in assembly language): http://board.asm32.info I want to use the fts5 in order to provide the search feature for the forum. It is actually implemented and you can test it on the above link. The searching SQL is the following: select U.nick as UserName, U.id as UserID, U.avatar as avatar, T.slug, strftime('%d.%m.%Y %H:%M:%S', P.postTime, 'unixepoch') as PostTime, P.ReadCount, PostFTS.rowid, snippet(PostFTS, 0, '', '', '...', 16) as Content, T.Caption, (select count() from UnreadPosts UP where UP.UserID = ?4 and UP.PostID = PostFTS.rowid) as Unread from PostFTS left join Posts P on P.id = PostFTS.rowid left join Threads T on T.id = P.threadID left join ThreadTags TT on TT.ThreadID = T.id left join Users U on P.userID = U.id where PostFTS match ?1 and ( ?4 is null or T.slug = ?4) and (?5 is null or TT.tag = ?5) order by rank limit ?2 offset ?3 As you can see, besides the MATCH condition, in the WHERE clause, there are two more conditions - the first is for searching in particular thread and the second is for searching the posts in particular sub-forum (tag). So, I think it is very natural if the user submit empty query or query containing for example "*" (parameter ?1) the search engine to return all posts, belonging to the current thread (or tag). And vice versa, I think it is very unnatural, submitting empty query to get nothing as a result. So, I am searching for the way to make it without changing the whole SQL query. P.S. BTW another issue that I can't solve with fts5 is returning posts that does not contain some word. "NOT keyword" does not work at all, neither "* NOT keyword"; -- http://fresh.flatassembler.net http://asm32.info John Found <johnfound at asm32.info>
