[sqlite] Question on errors - IOERR and CANTOPEN

[Dennis Volodomanov]

I sometimes get either a SQLITE_IOERR or a SQLITE_CANTOPEN when issuing
BEGIN IMMEDIATE TRANSACTION or END TRANSACTION, however the database
file is there and is being used by another thread. I thought I'd get the
usual SQLITE_BUSY or SQLITE_LOCKED, but sometimes these file-related
errors come up.

 

Does anyone know why they come up and what should be the correct logic
to continue? Should (and can it) the operation in question be retried,
as if a BUSY/LOCKED was encountered?

 

Thanks in advance,

 

   Dennis

 

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

Re: [sqlite] read_uncommitted=on; question

[Dennis Volodomanov]

> You're probably better off ignoring all this stuff and just getting a
> working solution.  Only then is it worth running some sort of
> profiling system on your application to find out which bits are most
> worth optimising.  Since the sqlite3 library is pretty fast already
> you might find that fiddling with shared cache defaults could make
> only 5% the improvement that improving the rest of your code will.

I agree - if I ever did those timings, now would not be the best time :)
The solution that I have now works well (without shared cache), so I'll
move on to other areas for the time being - it's fast enough, as most
time is spent elsewhere (processing the actual files).

   Dennis


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

Re: [sqlite] read_uncommitted=on; question

[Simon Slavin]

On 26 May 2009, at 4:56am, Dennis Volodomanov wrote:

> Yes, good point... I might compare performance differences in using a
> shared connection (multiple threads each opening its own copy of the
> database and sharing the connection) vs using the same database from
> those threads, unless such timings have already been done?

You would not necessarily get the same results.  The way this works  
would be very sensitive to interactions between the thread scheduling  
algorithm and the sqlite3 functions, and to how fast the CPUs dealt  
with sqlite3 functions.  One set of timings on processors of a certain  
speed under a certain OS may not be useful for your particular setup.

But this whole thread suggests a little to me that you're engaging in  
premature optimization:

http://en.wikipedia.org/wiki/Optimization_(computer_science)#When_to_optimize

You're probably better off ignoring all this stuff and just getting a  
working solution.  Only then is it worth running some sort of  
profiling system on your application to find out which bits are most  
worth optimising.  Since the sqlite3 library is pretty fast already  
you might find that fiddling with shared cache defaults could make  
only 5% the improvement that improving the rest of your code will.

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

Re: [sqlite] read_uncommitted=on; question

[Dennis Volodomanov]

> > It's multiple threads all using the same sqlite3* handle. I call
> > sqlite3_enable_shared_cache(1); before opening the database and have
> > "PRAGMA read_uncommitted=1;" right after opening the database.
> 
> None of that has any effect as long as you only have one connection.
> For
> the cache to be shared, you need at least two connections to share it
> between. So you may as well drop those call.

Yes, good point... I might compare performance differences in using a
shared connection (multiple threads each opening its own copy of the
database and sharing the connection) vs using the same database from
those threads, unless such timings have already been done?

Thanks!

   Dennis


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

Re: [sqlite] [OT] How to implement paging

[Teg]

Hello Sam,

SC> I take it that there is no solution to my problem?

That's our job isn't it? Designing some solution that works?

- Might have a worker thread watching the DB and have it sending
notifications to the GUI as deletes and additions were made to the DB
so, the display would be updated in real time.

- Might design it so, updates to the DB are sent to a table of updates
and the worker thread both integrates the updates AND notifies the GUI
of the specific update (something like a task queue but, in the DB
itself).

- if the updates are external, might make the updater update an
"update table". So, the worker thread knows what was done without
having to scan the whole DB.

- If I couldn't do that, I might keep all the rowid's in memory and
refresh the rowid list from time to time to find the changes (added
and deleted rowids).

This is kind of basic to problems like this. It's not that there isn't
a solution, there's a 100 different ways to do it. You just have to
pick one. The "realtime-ness" of the display will determine the design.


In Windows for instance a "grid" never has to be filling in with data,
it can re-paint the items dynamically in real time as a result of
notifications. I'd probably use a pure virtual list control, worker
thread that monitors the DB and gated notifications to the GUI that
stalls the worker thread until the notification is processed (since in
windows, worker threads can't talk directly to the GUI elements).

In that way the GUI remains responsive, only a single thread talks to
the DB and the display can be updated in real time. It's how I handle
list controls with 500,000+ elements. Then you just pick whether you
want it to scroll or page.



Monday, May 25, 2009, 5:02:22 PM, you wrote:

SC> I understand and agree that things changing in the middle is not
SC> ideal.  In the situation I am dealing with, things MUST disappear in
SC> the middle of the dataset.  As far as adding things, it should happen
SC> at the end, but that is outside of my control, it all depends on how
SC> things are sorted.  Normally things will be added to the end of the
SC> dataset, though.

SC> I take it that there is no solution to my problem?

SC> Sam

SC> On Mon, May 25, 2009 at 4:53 PM, Teg  wrote:
>> Hello Sam,
>>
>> Are you planning on periodically updating the display as the user
>> interacts with it? Have items pop in and pop out again as they're
>> added or deleted? From your description, the data displayed in the GUI
>> will go stale very quickly. I actually have similar logic in my app
>> and I always append new things to the bottom so, it doesn't affect the
>> current displayed page. Nothing annoys me more than to have items
>> dynamically appearing and disappearing while I'm trying to interact
>> with a GUI.
>>
>>
>>
>> Monday, May 25, 2009, 4:32:56 PM, you wrote:
>>
>> SC> On Mon, May 25, 2009 at 4:05 PM, Teg  wrote:
 Hello Sam,

 Paging or scrolling is purely an abstraction you create with the GUI
 itself. If you load up 88 titles into memory and your screen display is
 40 lines then you have 3 "pages" in memory and you simply replace a
 "page" each time they scroll or page up/down.  You seem to be letting
 the back end dictate what the GUI does when in fact it's better to
 abstract the whole thing so, you can change the back end at will and
 not have to change the GUI.  For 88 items, I doubt I'd even use a DB.
 Nothing beats a flat text file when you have a tiny data set.
>>
>> SC> I hear you that paging should be frontend logic, normally.  The
>> SC> problem is that I have a *DYNAMIC* record set that is constantly
>> SC> changing:
>>
>> SC> The nature of the dataset is that it can grow very quickly early on
>> SC> and at any point after that rows can be deleted through out.  It is
>> SC> this last fact that I need the help of the backend to figure out the
>> SC> page.
>>
>> SC> Assume 5 items per page.  If there are 88 items in the record set on
>> SC> one call that returns 50 to 55, before the next call, items 3,12, 17,
>> SC> 32, and 42 are all deleted from the record set, Item #55 is now going
>> SC> to be #50.  If the front end is simply giving the backend an unique
>> SC> identifier of the item, not the PK, per the recommendations of the
>> SC> ScrollingCursor page, how does the front end learn that it now is
>> SC> getting #50 through #54 rather than #55 through #59?
>>
>> SC> Further, I am assuming this is a problem with paging on any dataset in
>> SC> any database, thus a backend issue :)
>>
>> SC> Sam
>>
>>
>>
>> --
>> Best regards,
>>  Teg                            mailto:t...@djii.com
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>



-- 
Best regards,
 Tegmailto:t...@djii.com

___
sqlite-users 

Re: [sqlite] read_uncommitted=on; question

[Igor Tandetnik]

"Dennis Volodomanov"
 wrote in
message
news:8501919721c3de4c81bca22846b08721a3f...@lazarus.conceiva.com
>> Wrong. Statements on the same connection certainly see changes made
>> on that connection, committed or otherwise.
>>
>> Are you talking about the same connection, or two different
>> connections in shared cache mode? You started describing the latter,
>> but now keep mentioning the former. Which way is it?
>
> It's multiple threads all using the same sqlite3* handle. I call
> sqlite3_enable_shared_cache(1); before opening the database and have
> "PRAGMA read_uncommitted=1;" right after opening the database.

None of that has any effect as long as you only have one connection. For 
the cache to be shared, you need at least two connections to share it 
between. So you may as well drop those call.

Igor Tandetnik 



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

Re: [sqlite] read_uncommitted=on; question

[Dennis Volodomanov]

> Because catching and dealing with the duplication is handled within
> the library function, using these things appropriately should mean
> that you don't have to do any fancy worrying about threads, processes
> or simultaneity at all: if anything funny goes on, only one of the
> INSERT operations will succeed.

Thank you for the info! Unfortunately things are a bit more complex, so
I can't rely on the INSERT to tell me whether the file is there or not,
because there's some heavy processing before that INSERT which of course
shouldn't be done if the file is already there. I'm changing the
program's logic in handling this, because SQLite is not at fault nor can
it solve this problem on its own. I'm basically going to do a quick
INSERT, then start the heavy processing and then do an UPDATE. This will
let me do SELECTs to check existence from other threads without locking
things up.

   Dennis


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

Re: [sqlite] read_uncommitted=on; question

[Simon Slavin]

On 26 May 2009, at 1:11am, Dennis Volodomanov wrote:

> Basically these are file names being inserted into a table, so, before
> each insert we check whether that file exists in the table already or
> not (by doing a SELECT on an indexed lowercase full file path). So, it
> really matters to me that the first insert completes and data is
> available for a select that follows immediately from another thread  
> (but
> same database connection).

Ah !  Okay, if that's what you want, you can take advantage of the  
variations on the INSERT call.  Define the filename column as UNIQUE,  
or if there's a combination, create a UNIQUE index for the table, then  
use one of

INSERT OR REPLACE
INSERT OR FAIL
INSERT OR IGNORE

depending on which logic best serves what your program does.  So if  
you don't care if the filename is already in the table, you can use  
INSERT OR IGNORE, so the INSERT will always work but never generate  
duplicate entries.  On the other hand, if you need to trap and handle  
the case where the filename is already there, use INSERT OR FAIL, and  
the error you get back will tell you whether the filename was already  
in the table.  See

http://www.sqlite.org/lang_conflict.html

for a detailed discussion of what happens for the various clauses.

Because catching and dealing with the duplication is handled within  
the library function, using these things appropriately should mean  
that you don't have to do any fancy worrying about threads, processes  
or simultaneity at all: if anything funny goes on, only one of the  
INSERT operations will succeed.

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

Re: [sqlite] read_uncommitted=on; question

[Dennis Volodomanov]

> Wrong. Statements on the same connection certainly see changes made on
> that connection, committed or otherwise.
> 
> Are you talking about the same connection, or two different
connections
> in shared cache mode? You started describing the latter, but now keep
> mentioning the former. Which way is it?

It's multiple threads all using the same sqlite3* handle. I call
sqlite3_enable_shared_cache(1); before opening the database and have
"PRAGMA read_uncommitted=1;" right after opening the database.

   Dennis


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

Re: [sqlite] Simple Outer Join question?

[Igor Tandetnik]

"Kees Nuyt"  wrote in
message news:8u3m151rqbbel40ilsvaatqmfhcnhsj...@dim53.demon.nl
> On Mon, 25 May 2009 23:14:50 +0200, Leo Freitag
>  wrote:
>> I have a table 'person' and a table 'group'. Every person can join
>> none, one or more groups.
>> No I want to select all persons except those who are member in group
>> 1. - Sounds simple, but not for me.
>
> This is an n:m relationship.
> If group has more attributes (columns) than just its number,
> you need a third table: person_group.
> Then join person with person_group where group_id != 1;

That would also pick people that are both in group 1 and group 2. You 
would need something like

select * from person
where person_id not in (
select person_id from person_group where group_id=1);

Igor Tandetnik 



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

Re: [sqlite] read_uncommitted=on; question

[Dennis Volodomanov]

> How do you know the read on connection B doesn't actually happen right
> _before_ the write on connection A? What kind of synchronization do
you
> employ between these two threads?

I'm using a critical section (with a CSingleLock) to synchronize
threads. However, just looking back at the code more closely now, I
think I see where the problem is. Both checks for existence of data can
finish up before any of the inserts are done and that is most probably
what I'm seeing here.

Thanks!

   Dennis


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

Re: [sqlite] read_uncommitted=on; question

[Igor Tandetnik]

"Dennis Volodomanov"
 wrote in
message
news:8501919721c3de4c81bca22846b08721a3f...@lazarus.conceiva.com
> If I turn off read_uncommitted, then data won't be "visible" by other
> threads (same database connection) until a commit is done, right?

Wrong. Statements on the same connection certainly see changes made on 
that connection, committed or otherwise.

Are you talking about the same connection, or two different connections 
in shared cache mode? You started describing the latter, but now keep 
mentioning the former. Which way is it?

Igor Tandetnik



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

Re: [sqlite] read_uncommitted=on; question

[Igor Tandetnik]

"Dennis Volodomanov"
 wrote in
message
news:8501919721c3de4c81bca22846b08721a3f...@lazarus.conceiva.com
> Ok, thank you for confirming that. It seems that connection B
> *sometimes* doesn't see data just inserted into a table by connection
> A.

How do you know the read on connection B doesn't actually happen right 
_before_ the write on connection A? What kind of synchronization do you 
employ between these two threads?

> Another question - is it possible for 2 threads sharing the same
> connection to do an insert at exactly the same time, thus potentially
> causing this problem?

read_uncommitted just turns off read locks. Write operations still 
acquire write locks on tables they touch, so you can't have simultaneous 
inserts into the same table.

> Maybe thread 1 did a "prepare", but not yet
> "step" and the same data is being "prepared" by thread 2

This makes no sense. You don't prepare data - you prepare statements. No 
data is touched by sqlite3_prepare call.

Igor Tandetnik



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

Re: [sqlite] read_uncommitted=on; question

[Dennis Volodomanov]

> > Ok, thank you for confirming that. It seems that connection B
> > *sometimes* doesn't see data just inserted into a table by
> > connection A.
> 
> How long a time is 'just' ?  You might want everything to be
> completely up-to-date but does that record really matter if it didn't
> exist a fraction of a second ago ?  If the difference matters to you
> then perhaps you shouldn't be using read_uncommitted.  That way your
> database will be completely consistent (assuming you're using
> transactions properly).

Basically these are file names being inserted into a table, so, before
each insert we check whether that file exists in the table already or
not (by doing a SELECT on an indexed lowercase full file path). So, it
really matters to me that the first insert completes and data is
available for a select that follows immediately from another thread (but
same database connection).

If I turn off read_uncommitted, then data won't be "visible" by other
threads (same database connection) until a commit is done, right? So,
that would be pretty much the same as what's happening now with
read_uncommitted turned on.

> > Another question - is it possible for 2 threads sharing the same
> > connection to do an insert at exactly the same time, thus
potentially
> > causing this problem?
> 
> Nope.  Even if you're using a multi-core processor, your motherboard
> can still only handle one memory access instruction at a time.
> Multiprocessing is an illusion carefully maintained by the operating
> system.

Yes, I understand that, but would the first "prepare" already lock the
database for writing, or does that occur only during a "step"?

Thank you,

   Dennis


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

Re: [sqlite] read_uncommitted=on; question

[Simon Slavin]

On 26 May 2009, at 12:36am, Dennis Volodomanov wrote:

> Ok, thank you for confirming that. It seems that connection B
> *sometimes* doesn't see data just inserted into a table by  
> connection A.

How long a time is 'just' ?  You might want everything to be  
completely up-to-date but does that record really matter if it didn't  
exist a fraction of a second ago ?  If the difference matters to you  
then perhaps you shouldn't be using read_uncommitted.  That way your  
database will be completely consistent (assuming you're using  
transactions properly).

> Another question - is it possible for 2 threads sharing the same
> connection to do an insert at exactly the same time, thus potentially
> causing this problem?

Nope.  Even if you're using a multi-core processor, your motherboard  
can still only handle one memory access instruction at a time.   
Multiprocessing is an illusion carefully maintained by the operating  
system.

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

Re: [sqlite] read_uncommitted=on; question

[Dennis Volodomanov]

> > I thought (from reading the docs) that with read_uncommitted=1 and
> > sqlite3_enable_shared_cache(1), if I INSERT something, it will be
> > picked
> > up if I do a SELECT on another thread's connection as being in the
> > database, even if a COMMIT has not been issued yet. Am I wrong in my
> > understanding?
> 
> That's correct.
> 
> Normally, when using shared-cache mode, if connection A writes to a
> table
> (within a transaction) then a second connection to the same shared-
> cache is
> unable to read from that table until connection A either COMMITs or
> ROLLBACKs
> its open transaction. Trying to do so returns SQLITE_LOCKED. However,
> when
> in read_uncommitted=1 mode, the second connection reads the modified
> contents
> of the table, even though that data has not yet been (and indeed may
> never be)
> committed.

Ok, thank you for confirming that. It seems that connection B
*sometimes* doesn't see data just inserted into a table by connection A.
I will try updating to 3.6.14.2, but most probably that won't change
anything.

Another question - is it possible for 2 threads sharing the same
connection to do an insert at exactly the same time, thus potentially
causing this problem? Maybe thread 1 did a "prepare", but not yet "step"
and the same data is being "prepared" by thread 2 - would that cause a
problem that I'm seeing then?

As a side note - I'm monitoring all error codes returned by
prepare/step, so any busy states will cause a small sleep and retry.

Thanks again,

   Dennis


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

Re: [sqlite] [OT] How to implement paging

[Simon Slavin]

On 25 May 2009, at 7:42pm, Sam Carleton wrote:

> So in the end, you are saying that it is completely and totally the
> responsibility of the frontend to keep track of the page number,
> correct?  The result set should simply return a total count so that
> the # of pages can be calculated.  Correct?

Yes.  That is one way of handling the problem.  Bear in mind that your  
database engine doesn't know anything about how many lines you want to  
fit on the screen at once: that is your application's problem and  
nothing to do with the data.

> The main reason is that my application
> is a kiosk system that can be run on a touch screen display.  Paging
> is much easier than scrolling on touch screens.

Ah, for a kiosk system, your original solution is better.  Quite right.


On 25 May 2009, at 9:32pm, Sam Carleton wrote:

> I hear you that paging should be frontend logic, normally.  The
> problem is that I have a *DYNAMIC* record set that is constantly
> changing:


On 25 May 2009, at 9:53pm, Teg wrote:

> Are you planning on periodically updating the display as the user
> interacts with it? Have items pop in and pop out again as they're
> added or deleted? From your description, the data displayed in the GUI
> will go stale very quickly.

The problem is even worse than that.  There are three common approaches:

A) Do the search when someone starts first asks for the list.  Ignore  
all changes to the data until they've exited the list and gone back in.

B) Each time the user moves from page to page, reflect changes to the  
data.

C) Constantly update the display to show changes in the data even if  
the user isn't hitting any keys.

They're all doable, but you're going to have to decide which of these  
you want to do.  I've done (C) in a PHP/AJAX solution and it wasn't  
too hard: you have to constantly check to see if any changes have been  
made to the table.  But it places a lot of load on the server and  
requires some extremely 'cheap' method of checking to see if any  
changes have been made.

Be aware that using just one 'SELECT' gives you solution (A): the  
command makes a table in memory which doesn't change even if the data  
changes before you've got all the rows from the response.

(B), on the other hand, has a number of problems if your users expect  
to see every record as they're paging through.  For instance, suppose  
you're showing 10 records per page.  Your user is on the first page  
when someone deletes the tenth record.  Does this shift record 11 to  
the first page ?  If so, then when the user hits 'next' do they miss  
the eleventh record ?

Another possibility: the user is viewing the second page when someone  
deletes the first five records in the list.  The user now hits  
'previous'.  Do you now show just five records on the display, or do  
you show five records again ?

Questions like the above are reasons I don't like paging solutions for  
live data.

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

Re: [sqlite] Simple Outer Join question?

[Kees Nuyt]

On Mon, 25 May 2009 23:14:50 +0200, Leo Freitag
 wrote:

>Hallo,
>
>I have a table 'person' and a table 'group'. Every person can join none, 
>one or more groups.
>No I want to select all persons except those who are member in group 1. 
>- Sounds simple, but not for me.

This is an n:m relationship.
If group has more attributes (columns) than just its number,
you need a third table: person_group.
Then join person with person_group where group_id != 1;

The person_group table could be called membership, if you
like.

>Thanks in advance
>Leo
-- 
  (  Kees Nuyt
  )
c[_]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Simple Outer Join question?

[Leo Freitag]

Hallo,

I have a table 'person' and a table 'group'. Every person can join none, 
one or more groups.
No I want to select all persons except those who are member in group 1. 
- Sounds simple, but not for me.

Thanks in advance
Leo



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

Re: [sqlite] [OT] How to implement paging

[Sam Carleton]

I understand and agree that things changing in the middle is not
ideal.  In the situation I am dealing with, things MUST disappear in
the middle of the dataset.  As far as adding things, it should happen
at the end, but that is outside of my control, it all depends on how
things are sorted.  Normally things will be added to the end of the
dataset, though.

I take it that there is no solution to my problem?

Sam

On Mon, May 25, 2009 at 4:53 PM, Teg  wrote:
> Hello Sam,
>
> Are you planning on periodically updating the display as the user
> interacts with it? Have items pop in and pop out again as they're
> added or deleted? From your description, the data displayed in the GUI
> will go stale very quickly. I actually have similar logic in my app
> and I always append new things to the bottom so, it doesn't affect the
> current displayed page. Nothing annoys me more than to have items
> dynamically appearing and disappearing while I'm trying to interact
> with a GUI.
>
>
>
> Monday, May 25, 2009, 4:32:56 PM, you wrote:
>
> SC> On Mon, May 25, 2009 at 4:05 PM, Teg  wrote:
>>> Hello Sam,
>>>
>>> Paging or scrolling is purely an abstraction you create with the GUI
>>> itself. If you load up 88 titles into memory and your screen display is
>>> 40 lines then you have 3 "pages" in memory and you simply replace a
>>> "page" each time they scroll or page up/down.  You seem to be letting
>>> the back end dictate what the GUI does when in fact it's better to
>>> abstract the whole thing so, you can change the back end at will and
>>> not have to change the GUI.  For 88 items, I doubt I'd even use a DB.
>>> Nothing beats a flat text file when you have a tiny data set.
>
> SC> I hear you that paging should be frontend logic, normally.  The
> SC> problem is that I have a *DYNAMIC* record set that is constantly
> SC> changing:
>
> SC> The nature of the dataset is that it can grow very quickly early on
> SC> and at any point after that rows can be deleted through out.  It is
> SC> this last fact that I need the help of the backend to figure out the
> SC> page.
>
> SC> Assume 5 items per page.  If there are 88 items in the record set on
> SC> one call that returns 50 to 55, before the next call, items 3,12, 17,
> SC> 32, and 42 are all deleted from the record set, Item #55 is now going
> SC> to be #50.  If the front end is simply giving the backend an unique
> SC> identifier of the item, not the PK, per the recommendations of the
> SC> ScrollingCursor page, how does the front end learn that it now is
> SC> getting #50 through #54 rather than #55 through #59?
>
> SC> Further, I am assuming this is a problem with paging on any dataset in
> SC> any database, thus a backend issue :)
>
> SC> Sam
>
>
>
> --
> Best regards,
>  Teg                            mailto:t...@djii.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] [OT] How to implement paging

[Teg]

Hello Sam,

Are you planning on periodically updating the display as the user
interacts with it? Have items pop in and pop out again as they're
added or deleted? From your description, the data displayed in the GUI
will go stale very quickly. I actually have similar logic in my app
and I always append new things to the bottom so, it doesn't affect the
current displayed page. Nothing annoys me more than to have items
dynamically appearing and disappearing while I'm trying to interact
with a GUI.



Monday, May 25, 2009, 4:32:56 PM, you wrote:

SC> On Mon, May 25, 2009 at 4:05 PM, Teg  wrote:
>> Hello Sam,
>>
>> Paging or scrolling is purely an abstraction you create with the GUI
>> itself. If you load up 88 titles into memory and your screen display is
>> 40 lines then you have 3 "pages" in memory and you simply replace a
>> "page" each time they scroll or page up/down.  You seem to be letting
>> the back end dictate what the GUI does when in fact it's better to
>> abstract the whole thing so, you can change the back end at will and
>> not have to change the GUI.  For 88 items, I doubt I'd even use a DB.
>> Nothing beats a flat text file when you have a tiny data set.

SC> I hear you that paging should be frontend logic, normally.  The
SC> problem is that I have a *DYNAMIC* record set that is constantly
SC> changing:

SC> The nature of the dataset is that it can grow very quickly early on
SC> and at any point after that rows can be deleted through out.  It is
SC> this last fact that I need the help of the backend to figure out the
SC> page.

SC> Assume 5 items per page.  If there are 88 items in the record set on
SC> one call that returns 50 to 55, before the next call, items 3,12, 17,
SC> 32, and 42 are all deleted from the record set, Item #55 is now going
SC> to be #50.  If the front end is simply giving the backend an unique
SC> identifier of the item, not the PK, per the recommendations of the
SC> ScrollingCursor page, how does the front end learn that it now is
SC> getting #50 through #54 rather than #55 through #59?

SC> Further, I am assuming this is a problem with paging on any dataset in
SC> any database, thus a backend issue :)

SC> Sam



-- 
Best regards,
 Tegmailto:t...@djii.com

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

Re: [sqlite] [OT] How to implement paging

[Sam Carleton]

On Mon, May 25, 2009 at 4:05 PM, Teg  wrote:
> Hello Sam,
>
> Paging or scrolling is purely an abstraction you create with the GUI
> itself. If you load up 88 titles into memory and your screen display is
> 40 lines then you have 3 "pages" in memory and you simply replace a
> "page" each time they scroll or page up/down.  You seem to be letting
> the back end dictate what the GUI does when in fact it's better to
> abstract the whole thing so, you can change the back end at will and
> not have to change the GUI.  For 88 items, I doubt I'd even use a DB.
> Nothing beats a flat text file when you have a tiny data set.

I hear you that paging should be frontend logic, normally.  The
problem is that I have a *DYNAMIC* record set that is constantly
changing:

The nature of the dataset is that it can grow very quickly early on
and at any point after that rows can be deleted through out.  It is
this last fact that I need the help of the backend to figure out the
page.

Assume 5 items per page.  If there are 88 items in the record set on
one call that returns 50 to 55, before the next call, items 3,12, 17,
32, and 42 are all deleted from the record set, Item #55 is now going
to be #50.  If the front end is simply giving the backend an unique
identifier of the item, not the PK, per the recommendations of the
ScrollingCursor page, how does the front end learn that it now is
getting #50 through #54 rather than #55 through #59?

Further, I am assuming this is a problem with paging on any dataset in
any database, thus a backend issue :)

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

Re: [sqlite] [OT] How to implement paging

[Teg]

Hello Sam,

Paging or scrolling is purely an abstraction you create with the GUI
itself. If you load up 88 titles into memory and your screen display is
40 lines then you have 3 "pages" in memory and you simply replace a
"page" each time they scroll or page up/down.  You seem to be letting
the back end dictate what the GUI does when in fact it's better to
abstract the whole thing so, you can change the back end at will and
not have to change the GUI.  For 88 items, I doubt I'd even use a DB.
Nothing beats a flat text file when you have a tiny data set.


SC> That is a very good question.  The main reason is that my application
SC> is a kiosk system that can be run on a touch screen display.  Paging
SC> is much easier than scrolling on touch screens.

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



-- 
Best regards,
 Tegmailto:t...@djii.com

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

Re: [sqlite] SQLite3 support for 64-bit unsigned integers

[Nuno Lucas]

Just a small clarification you probably forgot...

On Mon, May 25, 2009 at 6:56 PM, Jay A. Kreibich  wrote:
>  Since you haven't said what you're trying to do, it is difficult to
>  propose a workaround.  If all you need is a unsigned long long, you
>  can either use an 8-byte BLOB (and a lot of casting) or you can just
>  store the value as a signed int and cast back and forth.
>
>  BLOBs are likely the safer choice, since the sort order will be
>  correct.

This will be true if the BLOBs are stored as big-endian 64-bit
integers, not if they are stored as little-endian (the first byte
would then be the one less significant).


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

Re: [sqlite] [OT] How to implement paging

[Sam Carleton]

On Mon, May 25, 2009 at 2:43 PM, Tito Ciuro  wrote:
> Hi Sam,
>
> On May 25, 2009, at 10:58 AM, Sam Carleton wrote:
>
>> Example:  Following the logic of the ScrollingCursor page, lets assume
>> a total result set of 88 titles.  If the lasttitle happens to be the
>> 29th title, so the set that is returned is 30 through 34, how do I
>> determine that this is the 6th page of a total of 18 pages?
>
>
> Let's assume 88 titles. For the sake of the argument, say you decide
> to display 12 per page. That would be 88 / 12 = 7.3 = 8 pages. If you
> need to know in which page a specific title lies (say 63), you can do
> something like ceil (63 % 12) = ceil (5.25) = 6th page. Following the
> same logic, ceil (88 / 12) = 8 pages total. Does that answer your
> question?

Actually, no it doesn't.  The math is simple enough, along with
finding the total count.  The question is how do I find out the number
of the title passed in...

According to the ScrollingCursor page, I should be passing in a title,
aka a string.  Since my record set is very dynamic, the title might be
the 63rd one time, but the next time it could be the 71st or 55th.

How do I find out the "index" of the start of the page as to apply
some basic math to find the page number?

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

Re: [sqlite] [OT] How to implement paging

[Tito Ciuro]

Hi Sam,

On May 25, 2009, at 10:58 AM, Sam Carleton wrote:

> Example:  Following the logic of the ScrollingCursor page, lets assume
> a total result set of 88 titles.  If the lasttitle happens to be the
> 29th title, so the set that is returned is 30 through 34, how do I
> determine that this is the 6th page of a total of 18 pages?


Let's assume 88 titles. For the sake of the argument, say you decide  
to display 12 per page. That would be 88 / 12 = 7.3 = 8 pages. If you  
need to know in which page a specific title lies (say 63), you can do  
something like ceil (63 % 12) = ceil (5.25) = 6th page. Following the  
same logic, ceil (88 / 12) = 8 pages total. Does that answer your  
question?

Cheers,

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

Re: [sqlite] [OT] How to implement paging

[Sam Carleton]

On Mon, May 25, 2009 at 2:31 PM, Simon Slavin
 wrote:
>
> On 25 May 2009, at 6:58pm, Sam Carleton wrote:
>
>> Example:  Following the logic of the ScrollingCursor page, lets assume
>> a total result set of 88 titles.  If the lasttitle happens to be the
>> 29th title, so the set that is returned is 30 through 34, how do I
>> determine that this is the 6th page of a total of 18 pages?
>
>
> You're going to have to know how many rows are displayed on a page.
> When you get your initial results back from the table, count the
> number of rows (or use the library function that returns it) and
> divide one by the other.  This gives you (more or less) the number of
> the last page of results.
>
> As well as keeping track of which page you're on, keep the current
> page number in a variable.  Just modify it when they hit 'next' or
> 'previous'.

So in the end, you are saying that it is completely and totally the
responsibility of the frontend to keep track of the page number,
correct?  The result set should simply return a total count so that
the # of pages can be calculated.  Correct?

> However, there's another way to do it.  If you know that you're never
> going to have more than a couple of hundred results, why display them
> as pages at all ?  Display them all, and provide a search function
> which lets people see just the records which contain their search
> field.  This is faster and more efficient than asking your users to
> wade through many pages.

That is a very good question.  The main reason is that my application
is a kiosk system that can be run on a touch screen display.  Paging
is much easier than scrolling on touch screens.

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

Re: [sqlite] [OT] How to implement paging

[Simon Slavin]

On 25 May 2009, at 6:58pm, Sam Carleton wrote:

> Example:  Following the logic of the ScrollingCursor page, lets assume
> a total result set of 88 titles.  If the lasttitle happens to be the
> 29th title, so the set that is returned is 30 through 34, how do I
> determine that this is the 6th page of a total of 18 pages?


You're going to have to know how many rows are displayed on a page.   
When you get your initial results back from the table, count the  
number of rows (or use the library function that returns it) and  
divide one by the other.  This gives you (more or less) the number of  
the last page of results.

As well as keeping track of which page you're on, keep the current  
page number in a variable.  Just modify it when they hit 'next' or  
'previous'.

However, there's another way to do it.  If you know that you're never  
going to have more than a couple of hundred results, why display them  
as pages at all ?  Display them all, and provide a search function  
which lets people see just the records which contain their search  
field.  This is faster and more efficient than asking your users to  
wade through many pages.

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

Re: [sqlite] Reading pragma's results

[João Eiras]

Well, thank you all.
I managed to solve the problem.
It was some initialization problems with sqlite.

On Mon, May 25, 2009 at 7:20 PM, João Eiras  wrote:
> Okay, but I'm using the latest sqlite source version 3.6
>
> On Mon, May 25, 2009 at 7:17 PM, Derrell Lipman
>  wrote:
>> On Mon, May 25, 2009 at 1:10 PM, João Eiras  wrote:
>>> Btw, the same happens with the command line sqlite program
>>>
>>> $ sqlite
>>> SQLite version 2.8.17
>>
>> These pragmas didn't exist six years ago when 2.8.17 was current. The
>> only pragmas in that version are:
>>
>> default_cache_size
>> cache_size
>> default_synchronous
>> synchronous
>> temp_store
>> default_temp_store
>>
>> Derrell
>> ___
>> 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

[sqlite] [OT] How to implement paging

[Sam Carleton]

I want to thank the mailing list in general you all have been very
helpful in my learning both SQLite and SQL in general.

I am working on paging right now and simply don't know the SQL way of
implementing it.  I have read through the SQLite page on scrolling
cursor (http://www.sqlite.org/cvstrac/wiki?p=ScrollingCursor), VERY
helpful.

One thing the page does not cover, and I think this is more general
SQL than SQLite, but how does one implement paging so that the user
knows which page they are on?

Example:  Following the logic of the ScrollingCursor page, lets assume
a total result set of 88 titles.  If the lasttitle happens to be the
29th title, so the set that is returned is 30 through 34, how do I
determine that this is the 6th page of a total of 18 pages?

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

Re: [sqlite] SQLite3 support for 64-bit unsigned integers

[Jay A. Kreibich]

On Sun, May 24, 2009 at 11:28:59PM -0700, Kelly Jones scratched on the wall:
> I tried inserting 2^63-1 and the two integers after it into an SQLite3
> db, but this happened:
> 
> SQLite version 3.6.11
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite> CREATE TABLE test (test INT);
> sqlite> INSERT INTO test VALUES (9223372036854775807);
> sqlite> INSERT INTO test VALUES (9223372036854775808);
> sqlite> INSERT INTO test VALUES (9223372036854775809);
> sqlite> .mode line
> sqlite> SELECT * FROM test;
>  test = 9223372036854775807
>  test = 9.22337203685478e+18
>  test = 9.22337203685478e+18
> 
> sqlite> SELECT * FROM test WHERE test = '9223372036854775808';
>  test = 9.22337203685478e+18
>  test = 9.22337203685478e+18
> 
> Why the sudden switch to scientific notation and loss of precision?

  For more details, see  http://sqlite.org/datatype3.html

  You're creating a column with an INTEGER affinity, but then
  overflowed what an integer can represent, so SQLite found some other
  representation.  From the docs:

A column that uses INTEGER affinity behaves in the same way as
a column with NUMERIC affinity, except that if a real value
with no fractional component and a magnitude that is less than
or equal to the largest possible integer (or text value that
converts to such) is inserted it is converted to an integer
and stored using the INTEGER storage class.

  It helps to remember that the default "type" of all numbers in SQL is
  some type of real (in the formal mathematical sense, not the
  programming sense of a floating-point number) number.


> Are 64-bit integers signed (ie -2^63 to 2^63-1)? 

  *All* integer values in SQLite are always signed.
  
  (And yes, being two's complement, that's the correct range)

> Workarounds?

  Since you haven't said what you're trying to do, it is difficult to
  propose a workaround.  If all you need is a unsigned long long, you
  can either use an 8-byte BLOB (and a lot of casting) or you can just
  store the value as a signed int and cast back and forth.

  BLOBs are likely the safer choice, since the sort order will be
  correct.

  You could also break things up into two major/minor columns that each 
  hold 32-ish bit numbers (SQLite auto sizes integers to their minimum
  representation of 1, 2, 3, 4, 6, or 8 bytes).  Or 8 bit/48 bit, or
  whatever.
  
   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Our opponent is an alien starship packed with atomic bombs.  We have
 a protractor."   "I'll go home and see if I can scrounge up a ruler
 and a piece of string."  --from Anathem by Neal Stephenson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Reading pragma's results

[João Eiras]

Okay, but I'm using the latest sqlite source version 3.6

On Mon, May 25, 2009 at 7:17 PM, Derrell Lipman
 wrote:
> On Mon, May 25, 2009 at 1:10 PM, João Eiras  wrote:
>> Btw, the same happens with the command line sqlite program
>>
>> $ sqlite
>> SQLite version 2.8.17
>
> These pragmas didn't exist six years ago when 2.8.17 was current. The
> only pragmas in that version are:
>
> default_cache_size
> cache_size
> default_synchronous
> synchronous
> temp_store
> default_temp_store
>
> Derrell
> ___
> 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] Reading pragma's results

[Derrell Lipman]

On Mon, May 25, 2009 at 1:10 PM, João Eiras  wrote:
> Btw, the same happens with the command line sqlite program
>
> $ sqlite
> SQLite version 2.8.17

These pragmas didn't exist six years ago when 2.8.17 was current. The
only pragmas in that version are:

default_cache_size
cache_size
default_synchronous
synchronous
temp_store
default_temp_store

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

Re: [sqlite] Reading pragma's results

[João Eiras]

Btw, the same happens with the command line sqlite program

$ sqlite
SQLite version 2.8.17
Enter ".help" for instructions
sqlite> create table t(a integer);
sqlite> insert into t values(1);
sqlite> select * from t;
1
sqlite> pragma page_size;
sqlite> pragma page_count;
sqlite>

Both pragmas don't return anything

On Mon, May 25, 2009 at 6:55 PM, João Eiras  wrote:
> On Mon, May 25, 2009 at 6:48 PM, Igor Tandetnik  wrote:
>> "João Eiras"  wrote
>> in message
>> news:e72b1b360905250934h1b39c95eycab618faca87d...@mail.gmail.com
>>> Sorry, wrong url...
>>> http://pastebin.ca/1433959
>>>
>>> On Mon, May 25, 2009 at 5:37 PM, João Eiras
>>>  wrote:
 Hi there.
 I'm trying the following code
 http://pastebin.ca/143395
>>
>> Are you, by any chance, building a release version of that code? Realize
>> that, in release build, assert() completely disappears together with
>> whatever expression it contains, so your program becomes mostly a no-op.
>>
>
> If I posted that code, it's safe to assume it is running and I know
> what an assert it.
>
>> How exactly do you determine whether your code works or doesn't work?
>
> Because if breaks many of the asserts, like, I get errors in standards output.
>
>>
>> Igor Tandetnik
>>
>>
>>
>>
>> ___
>> 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] Reading pragma's results

[João Eiras]

On Mon, May 25, 2009 at 6:48 PM, Igor Tandetnik  wrote:
> "João Eiras"  wrote
> in message
> news:e72b1b360905250934h1b39c95eycab618faca87d...@mail.gmail.com
>> Sorry, wrong url...
>> http://pastebin.ca/1433959
>>
>> On Mon, May 25, 2009 at 5:37 PM, João Eiras
>>  wrote:
>>> Hi there.
>>> I'm trying the following code
>>> http://pastebin.ca/143395
>
> Are you, by any chance, building a release version of that code? Realize
> that, in release build, assert() completely disappears together with
> whatever expression it contains, so your program becomes mostly a no-op.
>

If I posted that code, it's safe to assume it is running and I know
what an assert it.

> How exactly do you determine whether your code works or doesn't work?

Because if breaks many of the asserts, like, I get errors in standards output.

>
> Igor Tandetnik
>
>
>
>
> ___
> 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] Reading pragma's results

[Igor Tandetnik]

"João Eiras"  wrote
in message
news:e72b1b360905250934h1b39c95eycab618faca87d...@mail.gmail.com
> Sorry, wrong url...
> http://pastebin.ca/1433959
>
> On Mon, May 25, 2009 at 5:37 PM, João Eiras
>  wrote:
>> Hi there.
>> I'm trying the following code
>> http://pastebin.ca/143395

Are you, by any chance, building a release version of that code? Realize 
that, in release build, assert() completely disappears together with 
whatever expression it contains, so your program becomes mostly a no-op.

How exactly do you determine whether your code works or doesn't work?

Igor Tandetnik



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

Re: [sqlite] Reading pragma's results

[João Eiras]

Sorry, wrong url...
http://pastebin.ca/1433959

On Mon, May 25, 2009 at 5:37 PM, João Eiras  wrote:
> Hi there.
> I'm trying the following code
> http://pastebin.ca/143395
>
> Strangelly, both pragmas don't return any row at all !
> What am I missing ?
> The same happens if I open a data file on disk.
>
> Thanks.
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Reading pragma's results

[João Eiras]

Hi there.
I'm trying the following code
http://pastebin.ca/143395

Strangelly, both pragmas don't return any row at all !
What am I missing ?
The same happens if I open a data file on disk.

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

[sqlite] Older versions of sqlite3 analyzer?

[Michael Schlenker]

Hi all,

is there any pre-built windows sqlite3_analyzer binary for older SQLite
Versions (3.5.9 in particular)?

The download page only shows the 3.6.1 compatible one.

Michael

-- 
Michael Schlenker
Software Engineer

CONTACT Software GmbH   Tel.:   +49 (421) 20153-80
Wiener Straße 1-3   Fax:+49 (421) 20153-41
28359 Bremen
http://www.contact.de/  E-Mail: m...@contact.de

Sitz der Gesellschaft: Bremen
Geschäftsführer: Karl Heinz Zachries, Ralf Holtgrefe
Eingetragen im Handelsregister des Amtsgerichts Bremen unter HRB 13215
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLite version 3.6.14.2

[D. Richard Hipp]

SQLite version 3.6.14.2 is now available on the SQLite website.

Version 3.6.14.2 is a very small change over version 3.6.14.1.   
Version 3.6.14.2 fixes a single bug.  The bug report and patch can be  
seen here:

 http://www.sqlite.org/cvstrac/tktview?tn=3879
 http://www.sqlite.org/cvstrac/chngview?cn=6677

The bug that is fixed is an obscure corner-case in part of the code  
generator within the sqlite3_prepare() logic.  Applications are very  
unlikely to hit this bug.  But if they do, SQLite will give the wrong  
answer.  And, though rare, it is difficult to characterize the kinds  
of queries that might hit this bug.  For that reason, we have done an  
unscheduled patch release to fix the problem.

The bug that is fixed was introduced in version 3.6.14.  So upgrading  
is recommended for users of versions 3.6.14 and 3.6.14.1.

D. Richard Hipp
d...@hwaci.com

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

Re: [sqlite] queries for a fulltext-engine

[Igor Tandetnik]

"Lukas Haase"  wrote in
message news:gve1lh$30...@ger.gmane.org
>> SELECT topic_fulltext.topicID FROM topic_fulltext
>> where exists (select 1 from fulltext
>> WHERE topic_fulltext.fulltextID = fulltext.fulltextID and word
>> LIKE 'word1%')
>> and exists (select 1 from fulltext
>> WHERE topic_fulltext.fulltextID = fulltext.fulltextID and word
>> LIKE 'word2%')
>
> Unfortunately this does not work completely :(
>
> In the first WHERE clause I restrict to entried containing only the
> "word1" (the resultset will contain only entries with "word1"). So the
> second WHERE clause will always fail as there are no rows with
> "word2" left.

Right. I was thinking about a third table, topics, that lists all topics 
(and likely additional information about them), so that you have a 
classic many-to-many relationship. I suspect you have one. In this case 
you can do

SELECT topics.topicID FROM topics
where exists (select 1 from topic_fulltext join fulltext on 
(topic_fulltext.fulltextID = fulltext.fulltextID)
WHERE topic_fulltext.topicID = topics.topicID and word LIKE 
'word1%')
and exists (select 1 from topic_fulltext join fulltext on 
(topic_fulltext.fulltextID = fulltext.fulltextID)
WHERE topic_fulltext.topicID = topics.topicID and word LIKE 
'word2%');

If for some strange reason you don't have topics table, then you can do

SELECT distinct tf1.topicID from topic_fulltext tf1
where exists (select 1 from topic_fulltext tf2 join fulltext on 
(tf2.fulltextID = fulltext.fulltextID)
WHERE tf1.topicID = tf2.topicID and word LIKE 'word1%')
and exists (select 1 from topic_fulltext tf2 join fulltext on 
(tf2.fulltextID = fulltext.fulltextID)
WHERE tf1.topicID = tf2.topicID and word LIKE 'word2%')

Basically, (select distinct topicID from topic_fulltext) plays the role 
of topics table.

>> SELECT topic_fulltext.topicID FROM topic_fulltext
>> where fulltextID in (
>> select fulltextID from topic_fulltext where word LIKE 'word1%'
>> intersect
>> select fulltextID from topic_fulltext where word LIKE 'word2%');
>
> Unfortunately this does not work either.

For the same reason. Make it

select topicID from topic_fulltext join fulltext
  on ( topic_fulltext.fulltextID=fulltext.fulltextID)
  where word LIKE 'word1%'
intersect
select topicID from topic_fulltext join fulltext
  on ( topic_fulltext.fulltextID=fulltext.fulltextID)
  where word LIKE 'word2%'

Igor Tandetnik 



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

Re: [sqlite] Checking if an "integer" column is set to NULL

[John Machin]

On 25/05/2009 10:15 PM, chandan wrote:
> Hi,
> I have used sqlite3_bind_null() API to bind an integer column with 
> NULL. When I read the value of that integer column I get the value as 0 
> (zero). Is there any way I can check if the column is set to NULL?

You do realise that calling it "that integer column" is more hopeful 
than meaningful, don't you?

How are you reading "the value of that integer column"?

Here are some ways you can display it and test it using SQL:

sqlite> create table t (i integer);
sqlite> insert into t values(1);
sqlite> insert into t values(0);
sqlite> insert into t values(-1);
sqlite> insert into t values(null);
sqlite> insert into t values(123.456);
sqlite> insert into t values('abcdef');
sqlite> insert into t values(x'f000baaa');
sqlite> select rowid, i, quote(i), typeof(i) from t;
1|1|1|integer
2|0|0|integer
3|-1|-1|integer
4||NULL|null
5|123.456|123.456|real
6|abcdef|'abcdef'|text
7|­|X'F000BAAA'|blob
sqlite> select rowid, i, quote(i), typeof(i) from t where i is null;
4||NULL|null

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

Re: [sqlite] Checking if an "integer" column is set to NULL

[chandan]

Thanks a lot!. The solution works :-)

Hamish Allan wrote:
> On Mon, May 25, 2009 at 1:15 PM, chandan
>  wrote:
>
>   
>>I have used sqlite3_bind_null() API to bind an integer column with
>> NULL. When I read the value of that integer column I get the value as 0
>> (zero). Is there any way I can check if the column is set to NULL?
>> 
>
> This was something that confused me at first, so perhaps it's not
> crystal clear in the documentation:
>
> http://www.sqlite.org/capi3ref.html#sqlite3_column_blob
>
> [Annotations mine] "The sqlite3_column_type() routine returns the
> datatype code for the initial data type of the result column [NB I
> initially read this as "the initial data type of the column" rather
> than "the initial data type of the result", assuming that it would
> return the column affinity rather than the stored type]. The returned
> value is one of SQLITE_INTEGER, SQLITE_FLOAT, SQLITE_TEXT,
> SQLITE_BLOB, or SQLITE_NULL. The value returned by
> sqlite3_column_type() is only meaningful if no type conversions have
> occurred as described below. After a type conversion, the value
> returned by sqlite3_column_type() is undefined. Future versions of
> SQLite may change the behavior of sqlite3_column_type() following a
> type conversion."
>
> In other words, if you call sqlite3_column_type() before you call
> sqlite3_column_int(), you can differentiate the two cases.
>
> Best wishes,
> Hamish
> ___
> 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] Checking if an "integer" column is set to NULL

[Hamish Allan]

On Mon, May 25, 2009 at 1:15 PM, chandan
 wrote:

>    I have used sqlite3_bind_null() API to bind an integer column with
> NULL. When I read the value of that integer column I get the value as 0
> (zero). Is there any way I can check if the column is set to NULL?

This was something that confused me at first, so perhaps it's not
crystal clear in the documentation:

http://www.sqlite.org/capi3ref.html#sqlite3_column_blob

[Annotations mine] "The sqlite3_column_type() routine returns the
datatype code for the initial data type of the result column [NB I
initially read this as "the initial data type of the column" rather
than "the initial data type of the result", assuming that it would
return the column affinity rather than the stored type]. The returned
value is one of SQLITE_INTEGER, SQLITE_FLOAT, SQLITE_TEXT,
SQLITE_BLOB, or SQLITE_NULL. The value returned by
sqlite3_column_type() is only meaningful if no type conversions have
occurred as described below. After a type conversion, the value
returned by sqlite3_column_type() is undefined. Future versions of
SQLite may change the behavior of sqlite3_column_type() following a
type conversion."

In other words, if you call sqlite3_column_type() before you call
sqlite3_column_int(), you can differentiate the two cases.

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

Re: [sqlite] queries for a fulltext-engine

[Lukas Haase]

Igor Tandetnik schrieb:
> "Lukas Haase"  wrote in
> message news:gv9fcm$5r...@ger.gmane.org
>> I have a database containing thousands of HTML pages ("topics"). There
>> is a fulltext index for these topics. First there is a table
>> containing all single words. Each word is identified by its
>> "fulltextID":
>>
>> CREATE TABLE fulltext(
>> fulltextID INTEGER PRIMARY KEY,
>> word VARCHAR(100) COLLATE NOCASE
>> );
>>
>> Now there is a linking table between the words and the HTML pages
>> (topics):
>>
>> CREATE TABLE topic_fulltext(
>> topicID INTEGER,
>> fulltextID INTEGER,
>> PRIMARY KEY(topicID, fulltextID)
>> );
>>
>> Finding a topic containing a specific word is not too hard:
>>
>> SELECT topic_fulltext.topicID
>> FROM fulltext
>> JOIN topic_fulltext ON topic_fulltext.fulltextID = fulltext.fulltextID
>> WHERE word LIKE 'Word%';
>>
>> But now I want to be able to search with more complex queries. For
>> example:
>>
>> * List all topics containing (word1 AND word2)
> 
> You could do something like this:

Oh, thank you, this seems more like what I am looking for :-) :-)

> SELECT topic_fulltext.topicID FROM topic_fulltext
> where exists (select 1 from fulltext
> WHERE topic_fulltext.fulltextID = fulltext.fulltextID and word LIKE 
> 'word1%')
> and exists (select 1 from fulltext
> WHERE topic_fulltext.fulltextID = fulltext.fulltextID and word LIKE 
> 'word2%')

Unfortunately this does not work completely :(

In the first WHERE clause I restrict to entried containing only the 
"word1" (the resultset will contain only entries with "word1"). So the 
second WHERE clause will always fail as there are no rows with "word2" left.

This seems to work only for my OR-requirement (topics containg either 
word1 OR word2) when replacing AND with OR.

> SELECT topic_fulltext.topicID FROM topic_fulltext
> where fulltextID in (
> select fulltextID from topic_fulltext where word LIKE 'word1%'
> intersect
> select fulltextID from topic_fulltext where word LIKE 'word2%');

Unfortunately this does not work either.

The set is taken from the fullwords, i.e. the result of the inner SELECT 
clause will contain fulltextIDs. And they will obviosly never intersect.

So I have the same problem as above: Replacing with UNION ALL yields my 
OR-requirement but I can't get working it with AND :-(

> Test it, see which one works faster.

The second one seems to be much faster. Though it's too slow (3s or so), 
but I hope I can tune up the query on the end...

> [...]
>> * List all topics containing (word1 AND word2 AND ... AND word10)
>> * List all topics containing ((word1 OR word2) AND word3 OR word3)
> 
> The approach above should work for any boolean combination.

I have forgotten one third type: the NOT. E.g.:

* List all topics containing (NOT(word1 OR word2) AND word3)

But this is no hard requirement, but boolean AND and OR combinations are.

Thank you for your approaches, I tried to get the AND and OR working 
with it but I still do not figure it out :-(

By the way: If there is a better way to organize the index in the 
database: This would be no problem if the queries will get simpler and 
faster. (As long as the memeory requirement stays approx. the same)

Thank you again Igor,
Luke

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

Re: [sqlite] [sqlite-dev] How to install sqlite3-3.6.14.1.bin.gz

[Dan]

On May 25, 2009, at 2:52 PM, Manasi Save wrote:

> Hi,
>
> Can anyone help me on how to run sqlite3-3.6.14.1.bin.gz on Linux  
> machine.
> I am working on Fedora 10. Please if anyone can provide any input on  
> this.

Like this:


   d...@computer2:~/tmp$ wget http://www.sqlite.org/sqlite3-3.6.14.1.bin.gz

 ... output ...

   d...@computer2:~/tmp$ gunzip sqlite3-3.6.14.1.bin.gz
   d...@computer2:~/tmp$ chmod 755 sqlite3-3.6.14.1.bin
   d...@computer2:~/tmp$ ./sqlite3-3.6.14.1.bin
   SQLite version 3.6.14.1
   Enter ".help" for instructions
   Enter SQL statements terminated with a ";"
   sqlite>



> -- 
> Thanks and Regards,
> Manasi Save
>
>
> ___
> sqlite-dev mailing list
> sqlite-...@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-dev

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

Re: [sqlite] read_uncommitted=on; question

[Dan]

On May 25, 2009, at 2:54 PM, Dennis Volodomanov wrote:

> Hello all,
>
> Is it possible that with the read_uncommitted=1 and the shared cache
> mode turned on (multithreaded application), that some data that has  
> been
> inserted into the database, but not yet committed, could not be picked
> up by another thread as being in the database?
>
> I thought (from reading the docs) that with read_uncommitted=1 and
> sqlite3_enable_shared_cache(1), if I INSERT something, it will be  
> picked
> up if I do a SELECT on another thread's connection as being in the
> database, even if a COMMIT has not been issued yet. Am I wrong in my
> understanding?

That's correct.

Normally, when using shared-cache mode, if connection A writes to a  
table
(within a transaction) then a second connection to the same shared- 
cache is
unable to read from that table until connection A either COMMITs or  
ROLLBACKs
its open transaction. Trying to do so returns SQLITE_LOCKED. However,  
when
in read_uncommitted=1 mode, the second connection reads the modified  
contents
of the table, even though that data has not yet been (and indeed may  
never be)
committed.

Dan.


>
>
> Thanks in advance for any insight,
>
>   Dennis
>
>
> ___
> 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

[sqlite] read_uncommitted=on; question

[Dennis Volodomanov]

Hello all,

Is it possible that with the read_uncommitted=1 and the shared cache
mode turned on (multithreaded application), that some data that has been
inserted into the database, but not yet committed, could not be picked
up by another thread as being in the database?

I thought (from reading the docs) that with read_uncommitted=1 and
sqlite3_enable_shared_cache(1), if I INSERT something, it will be picked
up if I do a SELECT on another thread's connection as being in the
database, even if a COMMIT has not been issued yet. Am I wrong in my
understanding?

Thanks in advance for any insight,

   Dennis


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

[sqlite] How to install sqlite3-3.6.14.1.bin.gz

[Manasi Save]

Hi,

Can anyone help me on how to run sqlite3-3.6.14.1.bin.gz on Linux machine.
I am working on Fedora 10. Please if anyone can provide any input on this.

-- 
Thanks and Regards,
Manasi Save


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

Re: [sqlite] SQLite3 support for 64-bit unsigned integers

[John Machin]

On 25/05/2009 4:28 PM, Kelly Jones wrote:
> I tried inserting 2^63-1 and the two integers after it into an SQLite3
> db, but this happened:
> 
> SQLite version 3.6.11
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite> CREATE TABLE test (test INT);
> sqlite> INSERT INTO test VALUES (9223372036854775807);
> sqlite> INSERT INTO test VALUES (9223372036854775808);
> sqlite> INSERT INTO test VALUES (9223372036854775809);
> sqlite> .mode line
> sqlite> SELECT * FROM test;
>  test = 9223372036854775807
>  test = 9.22337203685478e+18
>  test = 9.22337203685478e+18
> 
> sqlite> SELECT * FROM test WHERE test = '9223372036854775808';
>  test = 9.22337203685478e+18
>  test = 9.22337203685478e+18
> 
> Why the sudden switch to scientific notation and loss of precision?

See answer to next question.

> Are 64-bit integers signed (ie -2^63 to 2^63-1)?

SQLite's integers are 64-bit signed two's-complement. Don't bet the 
ranch on -2^63.

> Can I "unsign" them?

No.

> Since sqlite3 uses 64-bit ints for rowid, I figured they'd be unsigned.

Since using rowids at the rate of 1 million per second would bump into 
2^63 after about 292,000 years, and since SQLite has only one integer 
type, to figure that it'd be signed would be a better betting proposition.

> Workarounds?

BLOBs, maybe, depending what you want 64-bit unsigned integers for. 
What's the use case?

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

[sqlite] SQLite3 support for 64-bit unsigned integers

[Kelly Jones]

I tried inserting 2^63-1 and the two integers after it into an SQLite3
db, but this happened:

SQLite version 3.6.11
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> CREATE TABLE test (test INT);
sqlite> INSERT INTO test VALUES (9223372036854775807);
sqlite> INSERT INTO test VALUES (9223372036854775808);
sqlite> INSERT INTO test VALUES (9223372036854775809);
sqlite> .mode line
sqlite> SELECT * FROM test;
 test = 9223372036854775807
 test = 9.22337203685478e+18
 test = 9.22337203685478e+18

sqlite> SELECT * FROM test WHERE test = '9223372036854775808';
 test = 9.22337203685478e+18
 test = 9.22337203685478e+18

Why the sudden switch to scientific notation and loss of precision?

Are 64-bit integers signed (ie -2^63 to 2^63-1)? Can I "unsign" them?

Since sqlite3 uses 64-bit ints for rowid, I figured they'd be unsigned.

Workarounds?

-- 
We're just a Bunch Of Regular Guys, a collective group that's trying
to understand and assimilate technology. We feel that resistance to
new ideas and technology is unwise and ultimately futile.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3.exe and formatting binary (GUID) data

[Philip Warner]

John Machin wrote:
> Assuming your guid is a BLOB, then
>SELECT other_stuff, QUOTE(guid), etc
> will display it as hex e.g. X'01020304'
>   
This is great; now I can see them at least!

>> P.S. Of course even better would be the ability to read/write GUIDs as
>> properly formatted strings! (Note: I am not asking that they be treated
>> internally as anything other than a binary chunk of data -- just the
>> text  form being changed).
>> 
>
> On output, how do you expect it to determine what blobs are guids? 
> column_name like  '%guid%' ??
>   

Yes...is that a problem? I have not really looked at the SQLite code
much. I know it remembers the declared types and assigns appropriate
internal types. My thinking was that for certain types (GUID, UUID,
maybe even Datetime/timestamp etc) it, or the user, could (optionally,
for backward compatibility) assign 'toString' and 'fromString'
operators. ie. not promote the to full types, but assign them a
quasi-type status.

In the case of dates, for example, it would allow me to enter
'1-Jan-1970' and fromString would produce '1970-01-01'. In the case of
GUIDs, it would display and load them in the standard GUID
representation. This would not even necessarily need to form part of the
standard sqlite code, but could be done as plugable functions.

But, as I said, I have not really looked much at sqlite internals to
know if this is completely impractical.

> On input: does the SQL standard define a guid literal?
>   
No; but GUIDs do.


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