Re: [sqlite] Select WHERE IN List ordering

[joshuapinter]

You saved my bacon with this one. Just wanted to pop in and say a quick
thanks to you. :) 



--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] -Wsign-compare warning in lempar.c

[Nick Wellnhofer]

With the latest Lemon code, I get a warning under GCC with -Wsign-compare:

warning: comparison between signed and unsigned integer expressions 
[-Wsign-compare]

 assert( i>=0 && i+YYNTOKEN<=sizeof(yy_lookahead)/sizeof(yy_lookahead[0]) );
   ^

Nick
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Need some tips on using FTS5 with SQLite

[John Found]

I am using FTS5 for pretty complex search in my application, but recently, 
trying to make it even more complex I faced some problems that are more general 
than only FTS5.

I have a forum engine where are several tables for the threads, for the posts, 
for the users etc. At first I want to be able to search in the posts text, but 
moreover, this search have to be limiter to some subset of the posts, for 
example in the posts of a particular thread or posts of some user. Also, there 
are cases where free-text search is not actually necessary, for example when I 
am searching for all posts from a particular user.

At first, I tried to create a FTS5 table, containing only the text data that 
need to be searched and then to access it by queries of the type:

select 
  some, 
  fields 
from 
  fts 
  left join posts p on p.id = fts.rowid
  left join threads t on t.id = p.threadid
  left join users u on u.id = p.userid
where
  fts match ?1 and u.nick = ?2 and t.id = ?3
order by ORDER

Such queries are pretty fast when there is only fts match directive in the 
where clause. 
But any additional condition added ruins the performance, especially if the fts 
match returns big amount of matches.

Additional problem is the order by clause. If the ORDER BY term is "rank" 
everything works great, but changing it to
other field (for example the post time in order to get first most recent posts) 
causes huge slow down of the query.

My second attempt was to sacrifice space for speed and to put all searchable 
data in the fts table - post text, the thread titles and the usernames. This 
way, building complex fts queries kind of:

   (content: ?1 OR caption: ?2) AND thread: ?3 AND user: ?4

I can leave only the fts query in the WHERE clause. This way, the search is 
pretty fast, but the huge problem remains
the ORDER BY clause. Again everything works fine with "rank", but attempts to 
use any other field for sorting, causes
huge probems: slow downs up to tens of seconds (usual search time is few 
milliseconds) and out of memory errors. 

Such problems with this second approach are even more serious than on the first 
approach. i.e. with the second approach everything works fine and quick with 
"rank" order by, and very, very slow and with errors, on any other "order by" 
option.

So, he main question follows:

What is the right way to design such complex search systems, based on FTS? How 
to properly approach the sorting of the search results in order to not have so 
big slowdowns and out of memory errors.

Any tips are highly welcome!

Regards
-- 
http://fresh.flatassembler.net
http://asm32.info
John Found 
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Header corruption

[Jens Alfke]

> On Feb 8, 2018, at 12:43 AM, Eduardo  wrote:
> 
> Profiling shouldn't help, it isn't the rigth tool. Use Xcode analyzer or from
> command line scan-build and scan-view. If you can, use the last version of
> clang-analyzer.

The Clang address sanitizer would be the best tool for diagnosing memory 
corruption. It's a lifesaver.

In Xcode it's very easy to enable: go to the scheme editor, select Run (or 
Test) from the list on the left, click the Diagnostics tab, and click the 
"Address Sanitizer" checkbox. I also recommend checking "Detect use of stack 
after return" and "Malloc Scribble". Then press the Run (or Test) button. I 
tend to leave this on all the time while developing; it slows down the program, 
but not enough to get in the way.

It can also be used from the command-line, but I have no knowledge of how to do 
that. (I do know it requires recompiling with a special compiler flag, since it 
instruments the machine code.)

—Jens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Please remove my id from mailing list/subscription.

[Igor Korot]

Hi,

On Thu, Feb 8, 2018 at 10:00 AM, Vasanth  wrote:
> Please remove my id from mailing list/subscription.

Why not do it yourself?
Did you try to go to the link shown at the end of this email or any
other for that matter?

Thank you.

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

[sqlite] Please remove my id from mailing list/subscription.

[Vasanth]

Please remove my id from mailing list/subscription.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Header corruption

[Eduardo]

On Wed, 7 Feb 2018 17:29:54 +
Deon Brewis  escribió:

> Oh yeah, I don’t think this is a SQLITE bug or anything. 
> 
> I think something in our code is writing to memory after freed. I'm just 
> trying to track it down at the point that it happens. We've tried all 
> Profiling tools on both OSX and Windows without luck, so my next step is 
> trying to find the writing thread at the point of corruption.
> 

Profiling shouldn't help, it isn't the rigth tool. Use Xcode analyzer or from
command line scan-build and scan-view. If you can, use the last version of
clang-analyzer.

In your project directory type:

%mkdir review
%scan-build make -o review/ -V

in directory review is the scan-build html output. Use:
 
%scan-view review/ to see it again


> Dan Kennedy's suggestion seems like that would we that way to do that.
> 
> - Deon

-- 
Eduardo 
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users