[sqlite] SQLite4 release date and how to compile on windows platform

[Zarian Waheed]

Does anyone know if any release date has been announced for SQLite4?
Secondly where can I find the instructions to compile SQLite4 on windows 
platform.

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

[sqlite] How to run sqlite just in memory - pragma cache_size issue

[Kf Lee]

Hi,

I use sqlite (v3.7.9) in  a Linuxmint environment to act as gateway for,
on one side, a dozen of users give control request via TCPIP, Bluetooth
HTTP and Linux Pipe. On the other side, via RS232 and Zigbee to control some
30 devices according to the request. All activities, user requests and device
feedback are written to the database which is about 2.5MB and the
entry is at about
one database read/write in one/two seconds. The size of database will
not increase
as a fixed amount of entries are stayed in the database, all other written to
text log file.

I have tried to use: pragma cache_size=3000 and executed the statement after
database has been opened. My understanding is that this statement
would give 3MB cache
size to keep all data in the memory and reduced hard disk access.
However the hard
disk access seem still there with disk hamming rise up and down when
write to database.

Perhaps I misunderstood what pragma cache does, please advice.

I have also explored the possbility of open database in :Memory: but
could not find
how this would be linked to the hard disk database. Are there
facilities to open a hard disk
database and keep all in memory, operate the memory db using normal
sqlite statements,
and later save it back to hard disk? That sound like a perfect solution.

kfl. (Hong Kong).

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

Re: [sqlite] Major performance difference when joining on FTS4 table's docid column versus custom id column

[Clemens Ladisch]

Per Vognsen wrote:
> Am I wrong to think that joining on docid should be as fast as joining on
> indexed integer columns in other tables?

Looking up a record by docid is faster than non-FTS lookups on other
columns, but the virtual table implementation still has to go through
a separate query to look up the data in the actual table.

> what's the recommended way to speed things up?

If you want to do anything but FTS searches, you should store a copy of
the data in a 'real' table.  (If you're concerned about space, make the
FTS table an external content table: 
.)


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Revisiting Collation Advice

[Jan Slodicka]

For those that were interested in the  discussion about iOS NOCASE collation
   here
are some news.

First of all we made the original algorithm more safe. The most important
step was to limit the character set where ascii comparison was applied. This
was done by modifying SAFE_CHAR macro:

#define SAFE_CHAR(x)( (0x61<=(x) && (x)<=0x7A)  ||  (0x30<=(x) &&
(x)<=0x39)  ||  (x)==32 )

After running a huge number of tests the code was released to production.
However, from time to time we got reports about sqlite crashes. Closer
analysis showed integrity problems in indexes.

At that point we decided to run collation tests (testing
reflexivity/symmetry/transitivity) on randomly generated strings. And we
found things like this:

'qsnB;QQ' < 'qsS1'  &&  'qsS1' < 'qß:0,hg', but 'qsnB;QQ' > 'qß:0,hg'
!!!

Hard to believe that a system compare function can behave this way, but this
is how the iOS call
CFStringCompare(s1, s2,
kCFCompareCaseInsensitive|kCFCompareLocalized|kCFCompareDiacriticInsensitive|kCFCompareNonliteral);
works.

This particular problem is caused by the flag kCFCompareLocalized. Needless
to say that there is no documentation that would warn you.

I thought this is worth publishing as it may save quite a few headaches for
iOS programmers that need to cook sorting algorithm.




--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/Revisiting-Collation-Advice-tp71546.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Regarding adding a new clause to the SELECT staement

[Simon Slavin]

On 2 Oct 2013, at 5:54pm, vaibh...@iitk.ac.in wrote:

> I am a final year undergraduate student from IIT Kanpur, India. I am doing
> my B.Tech project under the guidance of Prof. Arnab Bhattacharya in which
> I have to add Skyline clause to the Select statement.
> 
> But seeing the large code repository in 'sqlite3.c' I am not sure from
> where should I begin, so it would be very helpful if someone would direct
> me to the right person or help me out. I would be grateful.

You should not start by looking the amalgamation source code at all.

Instead download from the last section of the SQLite download page the Legacy 
Source Code Distribution Format complete source tree.

This has the source for SQLite split out into the individual c and h files 
you'd use if you were writing SQLite yourself.

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

[sqlite] Major performance difference when joining on FTS4 table's docid column versus custom id column

[Per Vognsen]

In our application, we have an FTS4 table 'tmexpandedtext' that contains
all our text strings. Other tables have columns like fullname_id, path_id
and filename_id that point to strings in this table by their docid. The
issue we're seeing is that joining on the docid column seems really slow
compared to doing the join on an otherwise equivalent non-FTS4 table.

My question is twofold:

Am I wrong to think that joining on docid should be as fast as joining on
indexed integer columns in other tables? And if I'm indeed wrong about
that, what's the recommended way to speed things up?

Here's a transcript to show what's going on:

sqlite> .timer on
sqlite> .output foo.txt
sqlite> SELECT msg.tsc, msg.flags FROM tmmessages msg LEFT JOIN
tmexpandedtext etext ON etext.docid=msg.fullname_id LEFT JOIN
tmexpandedtext ptext
ON ptext.docid=msg.path_id LEFT JOIN tmexpandedtext ftext ON
ftext.docid=msg.filename_id;
CPU Time: user 31.106599 sys 0.062400
sqlite> .output stdout
sqlite> explain query plan SELECT msg.tsc, msg.flags FROM tmmessages msg
LEFT JOIN tmexpandedtext etext ON etext.docid=msg.fullname_id LEFT JOIN
tmexpandedtext ptext ON ptext.docid=msg.path_id LEFT JOIN tmexpandedtext
ftext ON ftext.docid=msg.filename_id;
0|0|0|SCAN TABLE tmmessages AS msg (~100 rows)
0|1|1|SCAN TABLE tmexpandedtext AS etext VIRTUAL TABLE INDEX 1: (~0 rows)
0|2|2|SCAN TABLE tmexpandedtext AS ptext VIRTUAL TABLE INDEX 1: (~0 rows)
0|3|3|SCAN TABLE tmexpandedtext AS ftext VIRTUAL TABLE INDEX 1: (~0 rows)
CPU Time: user 0.00 sys 0.00

The first scan is okay since in this example we're grabbing everything in
the tmmessages and resolving the three id columns to the corresponding
strings in tmexpandedtext. Normally I would say the three other scans are
suspicious, but from my reading I've been let to believe that a SCAN TABLE
with VIRTUAL TABLE INDEX is the moral equivalent of a SEARCH TABLE on a
non-FTS4 table. However, the slowness of the query seems to say otherwise.

For reference, here are the sizes of the two relevant tables:

sqlite> select count() from tmexpandedtext;
70154
CPU Time: user 0.031200 sys 0.00
sqlite> select count() from tmmessages;
116
CPU Time: user 0.00 sys 0.031200

Now let's create a non-FTS4 equivalent of the tmmessages table and re-do
the query with it:

sqlite> create table strings (content VARCHAR, docid INTEGER);
CPU Time: user 0.00 sys 0.00
sqlite> create index strings_docid ON strings(docid);
CPU Time: user 0.00 sys 0.00
sqlite> INSERT INTO strings SELECT content, docid FROM tmexpandedtext;
CPU Time: user 0.187201 sys 0.00
sqlite> SELECT msg.tsc, msg.flags FROM tmmessages msg LEFT JOIN strings
etext ON etext.docid=msg.fullname_id LEFT JOIN strings ptext ON
ptext.docid=msg.path_id LEFT JOIN strings ftext ON
ftext.docid=msg.filename_id;
CPU Time: user 3.572423 sys 0.031200

As you can see, it is much faster and more in line with what you'd expect.
Here is the query plan:

sqlite> explain query plan SELECT msg.tsc, msg.flags FROM tmmessages msg
LEFT JOIN strings etext ON etext.docid=msg.fullname_id LEFT JOIN strings
ptext ON ptext.docid=msg.path_id LEFT JOIN strings ftext ON
ftext.docid=msg.filename_id;
0|0|0|SCAN TABLE tmmessages AS msg (~100 rows)
0|1|1|SEARCH TABLE strings AS etext USING COVERING INDEX strings_docid
(docid=?) (~10 rows)
0|2|2|SEARCH TABLE strings AS ptext USING COVERING INDEX strings_docid
(docid=?) (~10 rows)
0|3|3|SEARCH TABLE strings AS ftext USING COVERING INDEX strings_docid
(docid=?) (~10 rows)
CPU Time: user 0.00 sys 0.00

Any advice or insight would be greatly appreciated!

Sincerely,
Per Vognsen
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Regarding adding a new clause to the SELECT staement

[vaibhavv]

Hello,

I am a final year undergraduate student from IIT Kanpur, India. I am doing
my B.Tech project under the guidance of Prof. Arnab Bhattacharya in which
I have to add Skyline clause to the Select statement.

But seeing the large code repository in 'sqlite3.c' I am not sure from
where should I begin, so it would be very helpful if someone would direct
me to the right person or help me out. I would be grateful.

Regards,
Vaibhav
Final Year Undergraduate
IIT Kanpur
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Version 3.8.1 beta

[Warren Young]

On 10/2/2013 03:34, Jan Nijtmans wrote:


This patch appears to work


Confirmed.  Thanks!


(but maybe -no-undefined
should come in through @LDFLAGS@


No.  The flag is for libtool, not for gcc, which is the program libtool 
calls on to do the linking on Cygwin.  It will cause the link step to 
fail if you put it in LDFLAGS because gcc doesn't know that flag.


(And in case you're wondering, LDFLAGS=-Wl,-no-undefined is also wrong.)
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users