[sqlite] FTS5 query that to match all rows.

[Dan Kennedy]

On 04/13/2016 11:24 PM, John Found wrote:
> What FTS5 query should I use in order to match all rows in the table?
>

Can you use "SELECT * FROM fts_table;"?

Dan.

[sqlite] FTS5 query that to match all rows.

[John Found]

On Wed, 13 Apr 2016 19:41:34 +0200
R Smith  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 

[sqlite] FTS5 query that to match all rows.

[John Found]

On Wed, 13 Apr 2016 23:38:04 +0700
Dan Kennedy  wrote:

> On 04/13/2016 11:24 PM, John Found wrote:
> > What FTS5 query should I use in order to match all rows in the table?
> >
> 
> Can you use "SELECT * FROM fts_table;"?
> 

*I* can. But the users of the program where I use SQLite can't simply run 
arbitrary SQL statements.

> Dan.
> 
> 
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


-- 
http://fresh.flatassembler.net
http://asm32.info
John Found 

[sqlite] FTS5 query that to match all rows.

[R Smith]

On 2016/04/13 6:44 PM, John Found wrote:
> On Wed, 13 Apr 2016 23:38:04 +0700
> Dan Kennedy  wrote:
>
>> On 04/13/2016 11:24 PM, John Found wrote:
>>> What FTS5 query should I use in order to match all rows in the table?
>>>
>> Can you use "SELECT * FROM fts_table;"?
>>
> *I* can. But the users of the program where I use SQLite can't simply run 
> arbitrary SQL statements.

Ok... but then what good would knowing a "query that will return all 
rows" be to you if they cannot run arbitrary queries? The query Dan 
listed is the one that will do it. Either you program it in your 
application, or you teach the users to do it, what other way would there 
be to run any query of any kind?

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 :)

Cheers!
Ryan