[sqlite] do temporary on-disk databases get cleaned up on abnormal process termination?

[Sam Roberts]

The docs say you have to close the DB handle to clean them up. I'm
concerned that if a process is SIGKILLed or just exits abruptly that
the temporary DBs will accumulate on disk.

What mechanism is used to create the temporary files? If the file is
unlinked after open, then process exit is sufficient, but I haven't
been able to trigger data overflow onto disk in my quick testing.

Thanks,
Sam

Re: [sqlite] Is there a difference between DELETE and UPDATE/INSERT in terms of syncing to disk?

[Sam Roberts]

For the record, the problem was that django (by default) starts a
transaction before handing an http request, then commits it after the
request is handled.

Since we were in the middle of a transaction, all the python/django
code we wrote saw the state of the DB as it was partially through a
transaction, but external code didn't see the changes on disk yet.

We didn't realize that we were opertaing within an uncommitted
transaction, so had a the race condition where we kicked off an
external process to backup the DB after the DELETE was complete, and
it would sometimes see the changes, and sometimes not, depending on
what happened first - the request handler completing and transaction
getting committed, or the external process starting up and locking the
DB.

I think there is something specific about the relative speeds of the
handlers that do DELETEs vs. INSERTs that biased the race condition,
so who won depended on the action and led us down the wrong path.

Anyhow, thanks for your help.

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

[sqlite] Is there a difference between DELETE and UPDATE/INSERT in terms of syncing to disk?

[Sam Roberts]

I'm posting this for one of my coworkers whos email isn't showing up.

-- Forwarded message --
From: Kevin Yoo 

I am writing a Django app with sqlite. I am using Django’s database
hook framework to perform certain actions on sqlite’s db file: namely,
whenever any modification is made to the database, be it an
INSERT/UPDATE/DELETE, the db file is copied to a backup drive.

On INSERTs and UPDATEs, the copied db file contains the new or
modified records. On DELETEs, the deleted items are in the copied db
file (but shouldn't be).

It would appear that sqlite3 does not immediately write DELETEs to
disk. I have confirmed this behavior by pausing my Django app
immediately upon entrance into my database hook callback, running
sqlite in another process and examining the .db file on disk – the
deleted record is still present. Further, after the deletion but
before pausing the Django app I run a SELECT query within Django and
it does not see the deleted item.

Is there a way of forcing sqlite to write to disk after deletion? Is
my premise correct that deletions are treated differently?

The PRAGMA SYNCHRONOUS is set to 2 (FULL).

Doing a VACUUM also causes changes to be flushed to disk, but takes 5
seconds, so doesn't work for us.

Is there some other call we can make to cause DELETEs to be flushed to
disk? Any suggestions on whats happening?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] searching with like on FTS3

[Sam Roberts]

On Mon, Nov 29, 2010 at 9:08 AM, Pascal Schnurr  wrote:
> Hi recently I noticed that i can't search with the like '%searchword%'
> syntax on an FTS3 virtual table.

I'm no expert, but are you sure? This exact example, using LIKE,
appears in the FTS3 docs:

http://www.sqlite.org/fts3.html#section_1
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] accessing table column names other then via pragma, or...

[Sam Roberts]

On Tue, Nov 9, 2010 at 9:08 PM, Chris Wolf  wrote:
> ...can pragma result sets be accessed in other sql statements?
>
> I wish to use SQLite to perform some data re-formatting, as such, I need
> to output the results
> in CSV format.  Unfortunately, the ".output" command does not emit the
> column names
> in the first row.

Does the .headers command help?

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

Re: [sqlite] Full text search FTS3 of files

[Sam Roberts]

Take a look at the custom tokenizer API. I think tokens returned don't
necessarily have to be substrings of the text. So, maybe the text you
"tokenize" could be the file path, but the tokens could be things you
pull from the contents of the file.

Just a thought,
Cheers,
Sam
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] how do I get the FTS3 "malformed MATCH expression" error message?

[Sam Roberts]

>From the command line, I see:


sqlite> SELECT section, count(*) FROM 'index' WHERE name MATCH '"*'
GROUP BY section;
Error: malformed MATCH expression: ["*]


But when I do a prepare/step, I get a much less informative message:

int result = sqlite3_prepare_v2(routeDb, sql, -1, , NULL );
NSAssert2( SQLITE_OK == result, @"Error preparing statment <%s>: %s",
sql, sqlite3_errmsg( routeDb ) );
---> preparation works fine

while ( SQLITE_ROW == (result = sqlite3_step(compiledStatement)) ) {
// ...
}
NSAssert3( result == SQLITE_DONE, @"Unexpected result of stepping
<%s>. %d '%s'", sql, result, sqlite3_errmsg( routeDb ) );

---> This returns a very generic message:

  Unexpected result of stepping . 1 'SQL logic error
or missing database'

In this case, the user typed a double quote into an FTS3 search field,
which isn't valid, but I want to get the message that says that, not
'SQL logic error'!

Is there a better API I can use to get validation of the match syntax?

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

Re: [sqlite] Disambiguation of Latin accent characters for FTS3

[Sam Roberts]

On Thu, Sep 30, 2010 at 8:36 AM, Travis Orr  wrote:
> I know it is possible but can't figure out what needs to be done to be able 
> to make FTS3 see E as being equal to É. And other similar cases.

Despite the orthographic similarity, those sounds are as different to
French speakers as "a" and "e" are to English speakers.

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

Re: [sqlite] FTS3 MATCH syntax

[Sam Roberts]

I'm not an authority, but I've been using FTS3.

FTS3 tokenizes strings on whitespace (and other chars), so I think the
best you can do would be something
like given:john given:q.

It doesn't work really well out of the box for substring matching.


On Fri, Sep 24, 2010 at 4:16 PM, GHCS Software  wrote:
>  I'm just started with generating a table using FTS3 and understand
> most of the query formats, but am stuck on one minor point of syntax. My
> FTS3 query will make reference to a specific table, e.g.:
>
>     MATCH 'surname:smith'
>
> The problem is that I can't figure out how to specify it if the string
> to be searched for has a space in it. I've tried several varieties of
> quotes without any success, e.g.:
>
>     MATCH '"given:john q"'
>     MATCH 'given:"john q"'
>
> and so on. Is there a way to get this to work?
>
> --
> Doug Gordon
> *GHCS Software*
> http://www.ghcssoftware.com
> g...@ghcssoftware.com 
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Record corruption on Mac OS X 10.6 (Snow Leopard)

[Sam Roberts]

On Mon, Aug 16, 2010 at 9:33 AM, Dario Napolitano
 wrote:
> Hello everyone.
> I have developed a conversion tool to generate a SQLite database from a
> MySQL one. The tool is a simple C Cocoa application in which I have
> statically compiled the amalgamation source of SQLite. The tool has a
> reading thread that pulls records from MySQL and a writing thread that
> saves them into the SQLite database, via a locking queue. The SQLite
> connection is created on the main thread and then handed off to the
> writing thread which is the only one to use it.

OT: Is your tool public? My partner wrote this tool

http://github.com/senorprogrammer/mysql2sqlite.rb

but we're always interested in other approaches.

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

Re: [sqlite] How to import data from stdin?

[Sam Roberts]

On Sun, Aug 15, 2010 at 4:40 PM, Peng Yu  wrote:
> On Sun, Aug 15, 2010 at 5:58 PM, Simon Slavin  wrote:
>>
>> On 15 Aug 2010, at 11:31pm, Peng Yu wrote:
>>> sqlite3 main.db < file.txt <> Some of those lines are commands to your Unix shell and others are commands 
>> for the sqlite3 program.  You cannot mix a shell script and SQL commands 
>> like that.  Try these three files:
>
> This is called here document in bash. You can mix sqlite3 code with bash code.

Aren't you setting stdin for the sqlite3 utility to file.txt, AND to
the contents of the here document. How is sqlite3 supposed to
distinguish between the two types of data coming into it on stdin?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Substring (LIKE "%key%") searches, would FTS3 with suffix-tree tokenizer be the fast way?

[Sam Roberts]

On Fri, Aug 6, 2010 at 11:32 AM, Scott Hess <sh...@google.com> wrote:
> On Thu, Aug 5, 2010 at 12:42 PM, Sam Roberts <vieuxt...@gmail.com> wrote:
>> FTS3 only searches full terms/words by default, but I think if I built a 
>> custom
>> tokenizer that returned all the suffix trees for a name:
>
> FTS3 can do prefix searches, MATCH 'a*'.  Also, it aimed to support

Prefix searches don't allow matching in the middle of words. For
example, I want  "bert"
to match my name, "roberts".

So, I think I'd need to tokenize roberts as "s", "ts", ..., "berts",
"oberts", ... etc.

Then do a prefix match for "bert*" in order to see that "roberts" matches.

Lucky, I don't need or care about any of the snippeting stuff, because
I'm matching short strings (names).

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

Re: [sqlite] Substring (LIKE "%key%") searches, would FTS3 with suffix-tree tokenizer be the fast way?

[Sam Roberts]

On Fri, Aug 6, 2010 at 6:11 AM, Adam DeVita  wrote:
> A variant on Simon's plan.
> Are the 10,000 rows static, slowly changing, or frequently changing?

Never change, it's read-only.

>  Does
> it make sense to pre-calculate some counts at the time data is loaded?
>  Is
> this memory constrained so much that you can't afford 1 or 2 MB to let you
> look up based on ints? (I'm assuming that one letter is all you are after,
> either 'starts with' or 'contains' and not in order combinations.)

No, substrings, it's just that I then need a count of matching
substrings by first char.

Good idea, there are a number of other queries where pre-calculating
is linear in the space cost, but here the the usage is interactive
search, where as they type more of the name, it narrows down the
search results as people type in more.

Pre-calculating would be about 40 factorial in space, there are about
64000 3-character strings, and then once  they typed the 4th char in
it would be slow again. Of course, not all of those exist. Hm. Maybe
I'll try to precalculate the suffix tree, and see how many results
there really are, I don't need to store zero results.

The fastest I've found so far is using FTS3. Its a little slow, but
not unusably so. There are only 2500 rows now, I hope that it will
scale well as the DB increases in size. I'm still considering other
approaches, maybe a custom b-tree.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Substring (LIKE "%key%") searches, would FTS3 with suffix-tree tokenizer be the fast way?

[Sam Roberts]

On Thu, Aug 5, 2010 at 1:37 PM, Simon Slavin <slav...@bigfraud.org> wrote:
>
> On 5 Aug 2010, at 8:42pm, Sam Roberts wrote:
>
>> select substr(name,1,1), count(*) from my_table where name like '%e%'
>> group by substr(name,1,1);
>
> If you are constantly going to be using the first character of the name like 
> that, give it a column of its own with its own index.

That's a good idea. I think it would help a lot with row fetching if
section was it's own column:

  select * from my_table where section is "g" and name like "%e%"
order by name limit 1 offset 4;

But do you think the section would make the counting faster? I think
I'd have to get the row counts like this, which would still do the
slow full table scan:

  select section, count(*) from my_table where name like '%e%' group by section;

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

[sqlite] Substring (LIKE "%key%") searches, would FTS3 with suffix-tree tokenizer be the fast way?

[Sam Roberts]

I'd appreciate any suggestions on good ways to do this, I'm neither an SQL or
sqlite expert, so I might be thinking about it all wrong.

I have something like a (read-only) address book/rolodex, with interactive
searching. As users type into the search box, I need to first know for each
section how many rows match the substring typed so far.  I only display the
rows that are visible on screen.

I have two queries:

(A) I count the rows in a letter group.

If they typed "e":

select substr(name,1,1), count(*) from my_table where name like '%e%'
group by substr(name,1,1);
A|94
B|118
C|131
...

This is too slow, ~3sec, with 2500 rows, and we want to have 1 rows.

Worse, when they type "es", the search is as slow after they type "s" as when
they typed "e", even though the "es" rows are a sub-set of the rows that
matched "e".

FTS3 only searches full terms/words by default, but I think if I built a custom
tokenizer that returned all the suffix trees for a name:

"fu bar" => [ "r", "ar", "bar", " bar", "u bar", "fu bar"]

That I could do rewrite query (A) like this:

select substr(name,1,1), count(*) from my_table where name match 'e*'
group by substr(name,1,1);

Is this a reasonable approach? Is there a better way? Has somebody
else done this?



(B) I access specific rows within a letter group.

For visible rows, I fetch them by offset into a letter group, so row 4 in the
"g" section of names containing "e" would be:

select * from my_table where name like "g%" and name like "%e%" order
by name limit 1 offset 4;

The performance for this is OK, right now, I think it's because the first LIKE
can use the index, so the linear scan is over only a few hundred rows. Or it
could be that the on-screen display of each row is slower than the DB search. I
think it might become a problem, though.

I'm not sure how I would rewrite this to use FTS3 if it turns out to be to slow
for a larger DB, maybe a tokenizer that puts the first letter of  the name as
the first letter of every suffix?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users