[sqlite] How does your sqlite script binding handle aggregate UDFs?

[Stephan Beal]

On Sat, Mar 5, 2016 at 11:21 PM, Stephan Beal  wrote:

> On Sat, Mar 5, 2016 at 10:43 PM, Domingo Alvarez Duarte <
> sqlite-mail at dev.dadbiz.es> wrote:
>
>> Hello !
>>
>> There is an user pointer that you pass and you can get it back using
>> https://www.sqlite.org/c3ref/user_data.html !
>>
>
> i've got that, but that user data pointer is my callback function.
>

To clarify: function as in script-side Function.

In C code, all script-defined UDFs necessarily go through the same 1 (or 2,
for aggregates) C-side UDFs.

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf

[sqlite] How does your sqlite script binding handle aggregate UDFs?

[Stephan Beal]

On Sat, Mar 5, 2016 at 10:43 PM, Domingo Alvarez Duarte <
sqlite-mail at dev.dadbiz.es> wrote:

> Hello !
>
> There is an user pointer that you pass and you can get it back using
> https://www.sqlite.org/c3ref/user_data.html !
>

i've got that, but that user data pointer is my callback function. The
callback doesn't have a way to know if aggregation is just starting,
though, so it doesn't know (except in the final() call) that it can reset
its internal state.

The scenario i'm concerned about is that sqlite calls my aggregate N times,
then an error is triggered elsewhere which keeps sqlite from making the
final() aggregate call. Currently i reset my accumulation state in the
final() bits, but if final() is never called, then the _next_ time someone
calls the aggregate, it will still have accumulated state from the previous
attempt which failed partway through.


-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf

[sqlite] How does your sqlite script binding handle aggregate UDFs?

[Domingo Alvarez Duarte]

Hello !  

There is an user pointer that you pass and you can get it back using?
https://www.sqlite.org/c3ref/user_data.html !  

Cheers !  
>  Sat Mar 05 2016 9:22:23 pm CET CET from "Stephan Beal"
>  Subject: [sqlite] How does your sqlite script
>binding handle aggregate UDFs?
>
>  Hi, all,
> 
> this question is aimed at any of you who have experience adding
> script-defined UDF support to sqlite/script bindings (regardless of the
> scripting language). Everyone can tap delete now :).
> 
> "i've got this friend" who has an sqlite3/script-language binding which (as
> of an hour or so ago) now allows sqlite UDFs to be created via script code,
> e.g.:
> 
> myDb.createUDF('myfunc', function(a,b) { return a+b });
> 
> it also does aggregates, but i've got questions about those...
> 
> Aggregates are _currently_ modeled as a single function which gets called
> just like normal function, but in the aggregate's "final" call the engine
> calls the aggregate function with no arguments (this is how the call knows
> it's the "final" one). The final call does any last-minute work returns
> whatever the accumulated value is. In pseudocode, such an aggregate
> callback might look like:
> 
> function aggregateCallback() {
> if no arguments then
> this is the final call. Return accumulated data.
> else
> this is a "normal" call. Accumulate/calculate/whatever. Result is
> ignored.
> end
> }
> 
> i'm not aware of any aggregates which (in normal use) take no arguments, so
> this "seems" kosher to me (i.e., i "don't think" the convention of passing
> no arguments for the final call is going to bite me). However, i'm
> envisioning corner cases involving resetting of the data accumulator
> (assuming a SUM-like aggregator). Specifically: if sqlite calls the
> aggregate's step() function once, and then is interrupted by an error
> triggered from another function in the same statement, the final() call for
> the aggregate will never(?) be made. i.e., the final() call cannot be
> reliably used as a place to initialize the aggregate before the _next_
> round of calls.
> 
> How are script bindings handling such situations? Where are they
> initializing and resetting any "accumulator data" in their aggregates?
> 
> Any insights and suggestions would be appreciated.
> 
> -- 
> - stephan beal
> http://wanderinghorse.net/home/stephan/
> http://gplus.to/sgbeal
> "Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
> those who insist on a perfect world, freedom will have to do." -- Bigby
>Wolf
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> 
>
>  



?

[sqlite] How does your sqlite script binding handle aggregate UDFs?

[Stephan Beal]

Hi, all,

this question is aimed at any of you who have experience adding
script-defined UDF support to sqlite/script bindings (regardless of the
scripting language). Everyone can tap delete now :).

"i've got this friend" who has an sqlite3/script-language binding which (as
of an hour or so ago) now allows sqlite UDFs to be created via script code,
e.g.:

myDb.createUDF('myfunc', function(a,b) { return a+b });

it also does aggregates, but i've got questions about those...

Aggregates are  _currently_ modeled as a single function which gets called
just like normal function, but in the aggregate's "final" call the engine
calls the aggregate function with no arguments (this is how the call knows
it's the "final" one). The final call does any last-minute work returns
whatever the accumulated value is. In pseudocode, such an aggregate
callback might look like:

function aggregateCallback() {
  if no arguments then
this is the final call. Return accumulated data.
  else
this is a "normal" call. Accumulate/calculate/whatever. Result is
ignored.
  end
}

i'm not aware of any aggregates which (in normal use) take no arguments, so
this "seems" kosher to me (i.e., i "don't think" the convention of passing
no arguments for the final call is going to bite me). However, i'm
envisioning corner cases involving resetting of the data accumulator
(assuming a SUM-like aggregator). Specifically: if sqlite calls the
aggregate's step() function once, and then is interrupted by an error
triggered from another function in the same statement, the final() call for
the aggregate will never(?) be made. i.e., the final() call cannot be
reliably used as a place to initialize the aggregate before the _next_
round of calls.

How are script bindings handling such situations? Where are they
initializing and resetting any "accumulator data" in their aggregates?

Any insights and suggestions would be appreciated.

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf

[sqlite] Changing the default page_size in 3.12.0

[Simon Slavin]

On 5 Mar 2016, at 7:10pm, Paul Sanderson  
wrote:

> The savings as mentioned earlier are IO related due to matching the
> page size to the underlying hardwares block size and for larger
> payloads ensuring less IO due to no or lesss overflow pages.

An important point.  Because of the way I design my schema, most of my tables 
have short rows.  I rarely have more than 8 columns in a table.  Because SQLite 
fetches/writes a whole page for each access (?) having large pages does not 
generally benefit me.

Simon.

[sqlite] Changing the default page_size in 3.12.0

[Paul Sanderson]

Records can span multiple pages and the record itself is unrelated to
the block size.

For smaller tables indexes an increase in page size can increase the
DB size - consider a DB with 1024 byte pages and one table that
occupies 6 pages - i.e. 6K. in a new 4096 page size DB it would take
up two pages i.e. 8K.

Without further investigation I suspect that savings would come in
when you have larger tables with relatively large record payloads, I
would think that the free space in a page would be half the average
record length and so by multiplying the page size by 4 would decrease
the free (wasted) space (due to not enough space for another complete
record) also by a factor of four. This is grossly over simplified
though and takes no account of presumably less overflow pages due the
larger page size, pointer maps, and free space due to deleted records.

In short (no testing done) I am not sure that for most implementations
that increasing page size would make any significant space savings and
may likely as mentioned above increase the DB size..

The savings as mentioned earlier are IO related due to matching the
page size to the underlying hardwares block size and for larger
payloads ensuring less IO due to no or lesss overflow pages.




Paul
www.sandersonforensics.com
skype: r3scue193
twitter: @sandersonforens
Tel +44 (0)1326 572786
http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit
-Forensic Toolkit for SQLite
email from a work address for a fully functional demo licence


On 5 March 2016 at 18:43, Jim Callahan  
wrote:
>
>
>> From: Domingo Alvarez Duarte
>> I have a question, is it enough to vacuum a database to update to the new
>> page size ?
>>
>
> Apparently all you need to is "pragma page_size=4096; vacuum;" using the
>> appropriate page size.
>> This makes very easy to convert any(all) database(s) with a single
>> t
>> command from the command-line, like so (Win7 example):
>> for %i in (*.db) do sqlite3 %i "pragma page_size=4096; vacuum;"
>
>
> I am confused.
> The "page" is an in-memory structure; it is how large a chunk the program
> reads from the file (analogous to how large a scoop or shovel), So, unless
> one is using an index, how would the on disk structure be impacted? How
> does SQLite handle the last block (does it expect the file to be an even
> multiple of the block size, or does it accept that the last read might be
> less than a full block?).
>
> For example, if one encountered an old file, would it be enough to rebuild
> the indices?
>
> Or is it simply a matter of closing the file? (close the file with the old
> version and open the file with the new?).
>
> I haven't read the source code so I don't know what assertions, checks or
> assumptions SQLite
> uses.
>
> Jim
>
>
>
> On Sat, Mar 5, 2016 at 11:04 AM,  wrote:
>
>> From: Domingo Alvarez Duarte
>>> I have a question, is it enough to vacuum a database to update to the new
>>> page size ?
>>>
>>
>> Apparently all you need to is "pragma page_size=4096; vacuum;" using the
>> appropriate page size.
>> This makes very easy to convert any(all) database(s) with a single command
>> from the command-line, like so (Win7 example):
>>
>> for %i in (*.db) do sqlite3 %i "pragma page_size=4096; vacuum;"
>>
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Changing the default page_size in 3.12.0

[Simon Slavin]

On 5 Mar 2016, at 4:21pm, Domingo Alvarez Duarte  
wrote:

> Also could the commands that take time to complete have an option to show the
> completion stats ?  

You can do this using the SQLite shell tool:

.timer ON
VACUUM;
.timer OFF

Simon.

[sqlite] obtainingthe number of rows returned by a query (with a limit)

[Clemens Ladisch]

Paul Sanderson wrote:
> I have users who need to execute queries that generate a large number
> of rows, I have other users that create queries that generate a large
> number of rows by accident (i.e. cross joins). I have no control over
> what they enter but I want to do something to warn them that their
> query might result in an over large number of rows and a limit clause
> might be a solution.

This does not protect against a query that computes many things but
does not actually return many rows.

Just allow them to cancel the query; use sqlite3_progress_handler().


Regards,
Clemens

[sqlite] Changing the default page_size in 3.12.0

[to...@acm.org]

>From: Domingo Alvarez Duarte
>I have a question, is it enough to vacuum a database to update to the new 
>page size ?

Apparently all you need to is "pragma page_size=4096; vacuum;" using the 
appropriate page size.
This makes very easy to convert any(all) database(s) with a single command 
from the command-line, like so (Win7 example):

for %i in (*.db) do sqlite3 %i "pragma page_size=4096; vacuum;"

[sqlite] How does your sqlite script binding handle aggregate UDFs?

[Richard Hipp]

On 3/5/16, Stephan Beal  wrote:
> On Sat, Mar 5, 2016 at 10:43 PM, Domingo Alvarez Duarte <
>
> The scenario i'm concerned about is that sqlite calls my aggregate N times,
> then an error is triggered elsewhere which keeps sqlite from making the
> final() aggregate call.

IIRC, SQLite always calls the final() function.  Even after an error.

-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] Changing the default page_size in 3.12.0

[Domingo Alvarez Duarte]

Sorry I mean resurrect it ?  
>Is it too hard to survive it ? 
>  
>  
>>SQLite version 2 supported COPY That was dropped when we moved to SQLite
>> 3.
>> 

>  
>
>  



Also could the commands that take time to complete have an option to show the
completion stats ?  

Cheers !

[sqlite] obtainingthe number of rows returned by a query (with a limit)

[Paul Sanderson]

> Just allow them to cancel the query; use sqlite3_progress_handler().

I am using a third party data access component so this is out of my control.

If it computes many things and doesn't return many rows then I don't
really care. I only want to know how many rows a query will return
before I execute it in full. If I can tell that it is going to be a
huge return data set then I can prompt them to change it.

[sqlite] Fastest way to find whether at least one row has a certain column value

[Tim Streater]

On 05 Mar 2016 at 16:12, Richard Hipp  wrote: 

> On 3/5/16, Paul Sanderson  wrote:
>> Clemens,
>>
>> Tim has the same issue as me, while
>>
>>   SELECT EXISTS (select status from mytable where status=1);
>>
>> works the portion in brackets is still executed in full and this is
>> what we are trying to avoid.
>
> The query planner in SQLite, while not brilliant, is smart enough to
> know that it can stop and return true as soon as it sees the first row
> from the query inside of EXISTS().
>
> So if there is an index on mytable.status, the above will be very
> query.  Or if there are instances of status=1 early in mytable, the
> above will be quick.  But if mytable.status is never 1 and if there is
> no index on mytable.status, then SQLite will scan the entire table to
> verify that status is never 1 before returning 0.  I don't see anyway
> around the latter case, however.

Thanks - that allows me to use that method with the confidence that it's 
optimum. That index should always exist, in my case.

--
Cheers  --  Tim

[sqlite] Can I implement a scrolling window using LIMIT and OFFSET ?

[Keith Medcalf]

On Saturday, 5 March, 2016 14:03, James K. Lowden  
said:

> To: sqlite-users at mailinglists.sqlite.org
> Subject: Re: [sqlite] Can I implement a scrolling window using LIMIT and
> OFFSET ?

> On Fri, 04 Mar 2016 00:35:47 -0800
> Darren Duncan  wrote:

> > > How exactly is the first way "easiest"?

> > If these are pages displayed to the user, they may want to scroll
> > backwards at some point;

> They might, and if you say it's easier to go back to the database than
> to keep track of previously fetched data for re-display then, thanks,
> at least I understand your point of view.

> For myself I can't imagine such a design.  After I've gone to the work
> of preparing the query fetching the results, and placing them in
> whatever construct is needed for display to the user, I'd certainly
> hang onto my display structures until the user was done with the
> data.   If the user wants to see it again, the last thing I'd want to
> do is repeat all that.

This is common and why there are so many programs that work find when selecting 
the "customer" from a list when using a testing database with 137 customers.  
When the application moves to production where there are hundreds of thousands 
of customers, the wheels fall off the bus.

> Sometimes it's faster to recompute something than to cache it for later
> re-use. That's rare where I/O is involved, and vanishing rare where SQL
> is involved.

Unless, of course, the result set contains more than just a few records.

Interestingly enough, the wheels fall off the LIMIT + OFFSET bus at about the 
same place as they fall off the result-set cacheing bus.

The only thing worse is retrieving the entire result set and cacheing the whole 
think in the gooey structures.  Those wheels fall off the bus much sooner.

[sqlite] Changing the default page_size in 3.12.0

[Domingo Alvarez Duarte]

Is it too hard to survive it ?  
>
>  SQLite version 2 supported COPY That was dropped when we moved to SQLite
>3.
> 
> 
>
>

[sqlite] Changing the default page_size in 3.12.0

[Howard Chu]

Jim Callahan wrote:
> Is 4096 bytes a large enough page size?
>
> Apparently the disk drive industry has shifted from 512 byte sectors to
> 4096 byte sectors.
> http://tilt.lib.tsinghua.edu.cn/docs/tech/tp613_transition_to_4k_sectors.pdf
>
> Should SQLite maintain a 1:1 ratio between page size and sector size?
> or should the page size be a multiple of the sector size? Say 2:1 (8096 or
> 8K)? or 4:1 (16K).
>
> What sizes do other databases use? (SQL Server and Postgres both use 8096
> default)

You mean 8192.

> For years, virtual machines (VM) have used 4K pages (I think this started
> with IBM VM/370);
> while disk drives had 512 byte sectors (an 8:1 ratio).
>
> With a 2:1 ratio, in terms of seek time, one gets the second page for free.
>
> Would 8096 bytes (8K) be too much for a multi-tasking embedded device (such
> as a smart phone?).

You shouldn't even be discussing a hardcoded number. The page size should be 
equal to the page size of the underlying memory management system. 4K on 
common x86 systems, 8K on SPARC, etc. Choosing a number smaller than this will 
cost you in RMW ops whenever the filesystem tries to do an update. Choosing a 
number larger than this is generally going to waste memory.
>
> Are there any benchmarks?
>
> Jim
>
>
>
>
>
>
>
> On Fri, Mar 4, 2016 at 10:48 AM, Richard Hipp  wrote:
>
>> The tip of trunk (3.12.0 alpha) changes the default page size for new
>> database file from 1024 to 4096 bytes.
>>
>>  https://www.sqlite.org/draft/releaselog/3_12_0.html
>>  https://www.sqlite.org/draft/pgszchng2016.html
>>
>> This seems like a potentially disruptive change, so I want to give
>> you, the user community, plenty of time to consider the consequences
>> and potentially talk me out of it.
>>
>> The "Pre-release Snapshot" on the download page
>> (https://www.sqlite.org/download.html) contains this change, if you
>> want to actually evaluate the latest changes in your application.
>>
>> We hope to release 3.12.0 in early April, or maybe even sooner, so if
>> you want to provide feedback, you should do so without unnecessary
>> delay.
>> --
>> D. Richard Hipp
>> drh at sqlite.org
>> ___
>> sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


-- 
   -- Howard Chu
   CTO, Symas Corp.   http://www.symas.com
   Director, Highland Sun http://highlandsun.com/hyc/
   Chief Architect, OpenLDAP  http://www.openldap.org/project/

[sqlite] Changing the default page_size in 3.12.0

[Domingo Alvarez Duarte]

Hello !  

I have a question, is it enough to vacuum a database to update to the new
page size ?  

Or need a complete backup restore ?  

Also I have a database not too big 6GB but only 12GB free space on that disk,
if I try a dump/restore with the actual sqlite3 maybe I'll run out of disk
space, there is any chance to have compressed backup/restore ?  

Also PostgreSQL have different options for backup/restore like using the
"COPY" command (databases dumped using COPY have smaller dumps).  


Cheers !

[sqlite] Fastest way to find whether at least one row has a certain column value

[Paul Sanderson]

Thanks Richard

so suppose I have two tables table1 and table2 each with 1000 rows and
say 100 columns some containing large blobs.

My user choose a query "select * from table1, table2"

I can modify the query and do a "select count(*)  from table1, table2"
to determine that 1M rows will be returned and ask him if this is what
he really wants to do.

But what if he has "select * from table1, table2 limit 1000"

In this instance there is no need for me to warn him as he has already
applied a limit to the amount of data returned.

Any suggestions as to how I can detect this , other than parsing the
query for "limit x"

Thanks

Paul
www.sandersonforensics.com
skype: r3scue193
twitter: @sandersonforens
Tel +44 (0)1326 572786
http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit
-Forensic Toolkit for SQLite
email from a work address for a fully functional demo licence


On 5 March 2016 at 16:12, Richard Hipp  wrote:
> On 3/5/16, Paul Sanderson  wrote:
>> Clemens,
>>
>> Tim has the same issue as me, while
>>
>>   SELECT EXISTS (select status from mytable where status=1);
>>
>> works the portion in brackets is still executed in full and this is
>> what we are trying to avoid.
>
> The query planner in SQLite, while not brilliant, is smart enough to
> know that it can stop and return true as soon as it sees the first row
> from the query inside of EXISTS().
>
> So if there is an index on mytable.status, the above will be very
> query.  Or if there are instances of status=1 early in mytable, the
> above will be quick.  But if mytable.status is never 1 and if there is
> no index on mytable.status, then SQLite will scan the entire table to
> verify that status is never 1 before returning 0.  I don't see anyway
> around the latter case, however.
>
> --
> D. Richard Hipp
> drh at sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Mixing text and numeric values in comparisons

[Igor Tandetnik]

On 3/5/2016 7:48 AM, Paul van Helden wrote:
> SELECT * FROM tleft, tright WHERE Txt=Flt; -- returns a match!
>
> Is it because of the following from https://www.sqlite.org/datatype3.html

No. It is because Flt has REAL affinity, but 1.0 has no affinity. You 
can use CAST to explicitly give it one:

SELECT * FROM test WHERE A = CAST(1.0 as REAL); -- returns 1.

See also: https://www.sqlite.org/datatype3.html#compaff
-- 
Igor Tandetnik

[sqlite] Fastest way to find whether at least one row has a certain column value

[Clemens Ladisch]

Tim Streater wrote:
> On 05 Mar 2016 at 13:05, Clemens Ladisch  wrote:
>> But why do you want to determine the number of rows in the first place?
>
> In my case I want to know whether at least one row exists that has a certain 
> column which has a given value. At the minute I do this:
>
>   select count(*) from mytable where status=1 limit 1;
>
> Would this:
>
>   select count(*) from (select status from mytable where status=1 limit 1);
>
> or some other query be faster. Really, I'd like SQLite to stop after finding 
> one row.

So you want to know whether such a row exists? Then ask for that:

  SELECT EXISTS (select status from mytable where status=1);

This returns a boolean value.


Regards,
Clemens

[sqlite] obtainingthe number of rows returned by a query (with a limit)

[Igor Tandetnik]

On 3/5/2016 12:20 PM, Paul Sanderson wrote:
> If it computes many things and doesn't return many rows then I don't
> really care. I only want to know how many rows a query will return
> before I execute it in full.

That would require a crystal ball or a time machine. Absent those, it's 
impossible to know how many rows a query will produce until it actually 
runs and produces them.

Again, you can wrap an arbitrary query like this:

select count(*) from (
   select whatever ...
);

This query always returns exactly one row and one column, with the value 
being the number of rows that the inner query would have returned. Is 
this not what you want? Of course, it would take approximately as much 
time to run this new query as it would the original query.
-- 
Igor Tandetnik

[sqlite] Can I implement a scrolling window using LIMIT and OFFSET ?

[James K. Lowden]

On Fri, 04 Mar 2016 00:35:47 -0800
Darren Duncan  wrote:

> > How exactly is the first way "easiest"?
> 
> If these are pages displayed to the user, they may want to scroll
> backwards at some point; 

They might, and if you say it's easier to go back to the database than
to keep track of previously fetched data for re-display then, thanks,
at least I understand your point of view.  

For myself I can't imagine such a design.  After I've gone to the work
of preparing the query fetching the results, and placing them in
whatever construct is needed for display to the user, I'd certainly
hang onto my display structures until the user was done with the
data.   If the user wants to see it again, the last thing I'd want to
do is repeat all that.  

Sometimes it's faster to recompute something than to cache it for later
re-use. That's rare where I/O is involved, and vanishing rare where SQL
is involved.  

--jkl

[sqlite] Fastest way to find whether at least one row has a certain column value

[Paul Sanderson]

Clemens,

Tim has the same issue as me, while

  SELECT EXISTS (select status from mytable where status=1);

works the portion in brackets is still executed in full and this is
what we are trying to avoid.

I am not working directly with the sqlite library but rather though a
DAC so I am also limited (no pun intended) there.




Paul
www.sandersonforensics.com
skype: r3scue193
twitter: @sandersonforens
Tel +44 (0)1326 572786
http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit
-Forensic Toolkit for SQLite
email from a work address for a fully functional demo licence


On 5 March 2016 at 15:12, Clemens Ladisch  wrote:
> Tim Streater wrote:
>> On 05 Mar 2016 at 13:05, Clemens Ladisch  wrote:
>>> But why do you want to determine the number of rows in the first place?
>>
>> In my case I want to know whether at least one row exists that has a certain 
>> column which has a given value. At the minute I do this:
>>
>>   select count(*) from mytable where status=1 limit 1;
>>
>> Would this:
>>
>>   select count(*) from (select status from mytable where status=1 limit 1);
>>
>> or some other query be faster. Really, I'd like SQLite to stop after finding 
>> one row.
>
> So you want to know whether such a row exists? Then ask for that:
>
>   SELECT EXISTS (select status from mytable where status=1);
>
> This returns a boolean value.
>
>
> Regards,
> Clemens
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] obtainingthe number of rows returned by a query (with a limit)

[Paul Sanderson]

That doesn't work for me:

I am trying to avoid (select col1, col2, ... colx ) from ...

and want just a row count which is much less resource intensive.

I have users who need to execute queries that generate a large number
of rows, I have other users that create queries that generate a large
number of rows by accident (i.e. cross joins). I have no control over
what they enter but I want to do something to warn them that their
query might result in an over large number of rows and a limit clause
might be a solution.

if however they have already applied a limit clause there is no need
for a warning.

Before I am asked - for some of my users huge data sets might be OK as
they can use my application to massage the data before creating a
report or exporting the results set.

parsing the query and identifying the limit clause is an option, but I
hope there might be a simpler way.
Paul
www.sandersonforensics.com
skype: r3scue193
twitter: @sandersonforens
Tel +44 (0)1326 572786
http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit
-Forensic Toolkit for SQLite
email from a work address for a fully functional demo licence


On 5 March 2016 at 13:05, Clemens Ladisch  wrote:
> Paul Sanderson wrote:
>> I am trying to determine before a query is executed how many rows will
>> be returned. the following query works as expected
>>
>> select count(*) from table
>>
>> but
>>
>> select count(*) from table limit 100
>>
>> still returns the number of rows in the table not the number of rows
>> that would be returned by the query.
>
> "The query" is the one that has the "limit" clause.  What else should
> the database execute?
>
> As documented , the result rows
> are generated before the LIMIT clause is applied.
>
> To determine how many rows would be returned by an arbitrary query, use:
>
>   SELECT COUNT(*) FROM (SELECT ...);
>
>
> But why do you want to determine the number of rows in the first place?
>
>
> Regards,
> Clemens
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Mixing text and numeric values in comparisons

[Paul van Helden]

Hi again,

CREATE TABLE test (A TEXT);
INSERT INTO test VALUES ('1');
SELECT * FROM test WHERE A=1; -- returns 1
SELECT * FROM test WHERE A=1.0; -- returns nothing because the RHS is
converted to '1.0'

This would not have been a problem if CAST(1.0 AS TEXT) produced '1' (like
FloatToStr does in Delphi)

If this seems silly, what I'm doing above is simulating a
sqlite3_bind_double if you change 1.0 to ? (Integers stored in float fields
will never find a match stored in a text field, without casting)

However:

CREATE TABLE tleft (Txt TEXT);
INSERT INTO tleft VALUES ('1');
CREATE TABLE tright (Flt REAL);
INSERT INTO tright VALUES (1.0);
SELECT * FROM tleft, tright WHERE Txt=Flt; -- returns a match!

Is it because of the following from https://www.sqlite.org/datatype3.html

>  (As an internal optimization, small floating point values with no
> fractional component and stored in columns with REAL affinity are written
> to disk as integers in order to take up less space and are automatically
> converted back into floating point as the value is read out. This
> optimization is completely invisible at the SQL level and can only be
> detected by examining the raw bits of the database file.)
>

Does the cast of Flt to TEXT use the stored integer value in the
comparison? I want my query to do the same!

I therefore make my case that SELECT '1'=1.0 should return 1, and not 0 as
it does at the moment, i.e. CAST(1.0 AS TEXT) must produce '1'

Paul.

[sqlite] Changing the default page_size in 3.12.0

[Richard Hipp]

On 3/5/16, Jim Callahan  wrote:
>
> ?I am confused.
> The "page" is an in-memory structure; i?t is how large a chunk the program
> reads from the file (analogous to how large a scoop or shovel),

That is but one of many definitions of "page".  That word "page" gets
used (and misused) for a lot of things in computer programming.  Like
"virtual", its meaning can shift depending on context.

An SQLite database file (see https://www.sqlite.org/fileformat2.html)
consists of one or more "pages" of content.  A "page" in an SQLite
database file is not the same thing as a "page" in the virtual memory
design of your CPU.  But they are often roughly the same size.

A key point of this email thread is that SQLite performance seems to
be maximized when the database page size matches the CPU memory page
size and the disk sector size - currently 4096 bytes on most hardware.

> So, unless
> one is using an index, how would the on disk structure be impacted? How
> does SQLite handle the last block (does it expect the file to be an even
> multiple of the block size, or does it accept that the last read might be
> less than a full block?).
>
> For example, if one encountered an old file, would it be enough to rebuild
> the indices?
> ?
> Or is it simply a matter of closing the file? (close the file with the old
> version and open the file with the new?).
>
> I haven't read the source code so I don't know what assertions, checks or
> assumptions SQLite
> uses.
>
> Jim?
>
>
>
> On Sat, Mar 5, 2016 at 11:04 AM,  wrote:
>
>> From: Domingo Alvarez Duarte
>>> I have a question, is it enough to vacuum a database to update to the new
>>> page size ?
>>>
>>
>> Apparently all you need to is "pragma page_size=4096; vacuum;" using the
>> appropriate page size.
>> This makes very easy to convert any(all) database(s) with a single command
>> from the command-line, like so (Win7 example):
>>
>> for %i in (*.db) do sqlite3 %i "pragma page_size=4096; vacuum;"
>>
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] obtainingthe number of rows returned by a query (with a limit)

[Clemens Ladisch]

Paul Sanderson wrote:
> I am trying to determine before a query is executed how many rows will
> be returned. the following query works as expected
>
> select count(*) from table
>
> but
>
> select count(*) from table limit 100
>
> still returns the number of rows in the table not the number of rows
> that would be returned by the query.

"The query" is the one that has the "limit" clause.  What else should
the database execute?

As documented , the result rows
are generated before the LIMIT clause is applied.

To determine how many rows would be returned by an arbitrary query, use:

  SELECT COUNT(*) FROM (SELECT ...);


But why do you want to determine the number of rows in the first place?


Regards,
Clemens

[sqlite] Changing the default page_size in 3.12.0

[Jim Callahan]

??

> From: Domingo Alvarez Duarte
> I have a question, is it enough to vacuum a database to update to the new
> page size ?
>

Apparently all you need to is "pragma page_size=4096; vacuum;" using the
> appropriate page size.
> This makes very easy to convert any(all) database(s) with a single
> ?t?
> command from the command-line, like so (Win7 example):
> for %i in (*.db) do sqlite3 %i "pragma page_size=4096; vacuum;"


?I am confused.
The "page" is an in-memory structure; i?t is how large a chunk the program
reads from the file (analogous to how large a scoop or shovel), So, unless
one is using an index, how would the on disk structure be impacted? How
does SQLite handle the last block (does it expect the file to be an even
multiple of the block size, or does it accept that the last read might be
less than a full block?).

For example, if one encountered an old file, would it be enough to rebuild
the indices?
?
Or is it simply a matter of closing the file? (close the file with the old
version and open the file with the new?).

I haven't read the source code so I don't know what assertions, checks or
assumptions SQLite
uses.

Jim?



On Sat, Mar 5, 2016 at 11:04 AM,  wrote:

> From: Domingo Alvarez Duarte
>> I have a question, is it enough to vacuum a database to update to the new
>> page size ?
>>
>
> Apparently all you need to is "pragma page_size=4096; vacuum;" using the
> appropriate page size.
> This makes very easy to convert any(all) database(s) with a single command
> from the command-line, like so (Win7 example):
>
> for %i in (*.db) do sqlite3 %i "pragma page_size=4096; vacuum;"
>
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] Fastest way to find whether at least one row has a certain column value

[Tim Streater]

On 05 Mar 2016 at 13:05, Clemens Ladisch  wrote:

> Paul Sanderson wrote:
>> I am trying to determine before a query is executed how many rows will
>> be returned. the following query works as expected
>>
>> select count(*) from table
>>
>> but
>>
>> select count(*) from table limit 100
>>
>> still returns the number of rows in the table not the number of rows
>> that would be returned by the query.
>
> "The query" is the one that has the "limit" clause.  What else should
> the database execute?
>
> As documented , the result rows
> are generated before the LIMIT clause is applied.
>
> To determine how many rows would be returned by an arbitrary query, use:
>
>  SELECT COUNT(*) FROM (SELECT ...);
>
> But why do you want to determine the number of rows in the first place?

In my case I want to know whether at least one row exists that has a certain 
column which has a given value. At the minute I do this:

  select count(*) from mytable where status=1 limit 1;

and mytable has this index:

  CREATE INDEX stat on mytable (status asc);

Would this:

  select count(*) from (select status from mytable where status=1 limit 1);

or some other query be faster. Really, I'd like SQLite to stop after finding 
one row.


--
Cheers  --  Tim

[sqlite] obtainingthe number of rows returned by a query (with a limit)

[Paul Sanderson]

I am trying to determine before a query is executed how many rows will
be returned. the following query works as expected

select count(*) from table

but

select count(*) from table limit 100

still returns the number of rows in the table not the number of rows
that would be returned by the query. is this operation correct?

I can of course parse the query to determine any limit on what will be
returned, but wondered if this is a bug or a design feature.




Paul
www.sandersonforensics.com
skype: r3scue193
twitter: @sandersonforens
Tel +44 (0)1326 572786
http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit
-Forensic Toolkit for SQLite
email from a work address for a fully functional demo licence

[sqlite] Changing the default page_size in 3.12.0

[to...@acm.org]

>From: Richard Hipp
>Can you run sqlite3_analyzer on some of your databases and send me the 
>output?

I will as soon as I can manage to build it under Windows.  Although I 
regularly build sqlite3, lemon, and sqldiff, with sqlite3_analyzer I get a 
lot of errors like these:

...
sqlite3_analyzer.obj : error LNK2019: unresolved external symbol 
__imp__Tcl_NRCallObjProc referenced in function _DbObjCmdAdaptor
sqlite3_analyzer.exe : fatal error LNK1120: 59 unresolved externals 

[sqlite] Changing the default page_size in 3.12.0

[Richard Hipp]

On 3/5/16, Domingo Alvarez Duarte  wrote:
> Is it too hard to survive it ?
>>
>>  SQLite version 2 supported COPY That was dropped when we moved to SQLite
>>3.
>>

Yes.

-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] Fastest way to find whether at least one row has a certain column value

[Richard Hipp]

On 3/5/16, Paul Sanderson  wrote:
> Clemens,
>
> Tim has the same issue as me, while
>
>   SELECT EXISTS (select status from mytable where status=1);
>
> works the portion in brackets is still executed in full and this is
> what we are trying to avoid.

The query planner in SQLite, while not brilliant, is smart enough to
know that it can stop and return true as soon as it sees the first row
from the query inside of EXISTS().

So if there is an index on mytable.status, the above will be very
query.  Or if there are instances of status=1 early in mytable, the
above will be quick.  But if mytable.status is never 1 and if there is
no index on mytable.status, then SQLite will scan the entire table to
verify that status is never 1 before returning 0.  I don't see anyway
around the latter case, however.

-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] Changing the default page_size in 3.12.0

[Richard Hipp]

On 3/5/16, Domingo Alvarez Duarte  wrote:
>
> I have a question, is it enough to vacuum a database to update to the new
> page size ?

The new page size only applies to newly created database files.
VACUUM-ing an existing database file will not change its page size.

However, if you say "PRAGMA page_size=N; VACUUM;" (for some N) that
will change the page size if you are not in WAL mode.

>
> Also I have a database not too big 6GB but only 12GB free space on that
> disk,
> if I try a dump/restore with the actual sqlite3 maybe I'll run out of disk
> space, there is any chance to have compressed backup/restore ?

You'll probably run out of space if you try to VACUUM.

12GB isn't very much.  USB thumb-drives are usually bigger than that.
You can't get better hardware?

>
> Also PostgreSQL have different options for backup/restore like using the
> "COPY" command (databases dumped using COPY have smaller dumps).
>

SQLite version 2 supported COPY.  That was dropped when we moved to SQLite 3.

-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] Changing the default page_size in 3.12.0

[Simon Slavin]

On 5 Mar 2016, at 10:07am, tonyp at acm.org wrote:

>> From: Richard Hipp
>> Can you run sqlite3_analyzer on some of your databases and send me the 
>> output?
> 
> I will as soon as I can manage to build it under Windows.

Can download pre-built binaries for Windows and other popular platforms.

Simon.

[sqlite] Changing the default page_size in 3.12.0

[Quan Yong Zhai]

>From: tonyp at acm.org
>To: SQLite mailing list
>Subject: Re: [sqlite] Changing the default page_size in 3.12.0

>I ran some tests and almost all of my databases (about 100 of them with 
>different content mix and with the biggest one being around 500MB) inflated 
>by a lot while only a couple or so shrunk in size by not much.

Really?

[sqlite] Fastest way to find whether at least one row has a certain column value

[Richard Hipp]

On Sat, 05 Mar 2016 13:24 +, Tim Streater  wrote:
>
> In my case I want to know whether at least one row exists that has a certain
> column which has a given value. At the minute I do this:
>
>   select count(*) from mytable where status=1 limit 1;
>

SELECT 1 FROM mytable WHERE status=1 LIMIT 1;

Then if sqlite3_step() returns SQLITE_ROW you know that a row with
status=1 exists, but if sqlite3_step() returns SQLITE_DONE, you know
that no such row exists.

-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] Changing the default page_size in 3.12.0

[Richard Hipp]

On 3/5/16, Jim Callahan  wrote:
> Is 4096 bytes a large enough page size?
>
> Are there any benchmarks?
>

https://www.sqlite.org/tmp/small-v-large-cache.jpg

Results from running "rm -f x.db; time ./speedtest1 x.db --size 400
--release-memory --pagesize N" (after "make speedtest1") off of the
tip of trunk yesterday, on a fast Ubuntu machine.

-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] Changing the default page_size in 3.12.0

[Jim Callahan]

Is 4096 bytes a large enough page size?

Apparently the disk drive industry has shifted from 512 byte sectors to
4096 byte sectors.
http://tilt.lib.tsinghua.edu.cn/docs/tech/tp613_transition_to_4k_sectors.pdf

Should SQLite maintain a 1:1 ratio between page size and sector size?
or should the page size be a multiple of the sector size? Say 2:1 (8096 or
8K)? or 4:1 (16K).

What sizes do other databases use? (SQL Server and Postgres both use 8096
default)

For years, virtual machines (VM) have used 4K pages (I think this started
with IBM VM/370);
while disk drives had 512 byte sectors (an 8:1 ratio).

With a 2:1 ratio, in terms of seek time, one gets the second page for free.

Would 8096 bytes (8K) be too much for a multi-tasking embedded device (such
as a smart phone?).

Are there any benchmarks?

Jim







On Fri, Mar 4, 2016 at 10:48 AM, Richard Hipp  wrote:

> The tip of trunk (3.12.0 alpha) changes the default page size for new
> database file from 1024 to 4096 bytes.
>
> https://www.sqlite.org/draft/releaselog/3_12_0.html
> https://www.sqlite.org/draft/pgszchng2016.html
>
> This seems like a potentially disruptive change, so I want to give
> you, the user community, plenty of time to consider the consequences
> and potentially talk me out of it.
>
> The "Pre-release Snapshot" on the download page
> (https://www.sqlite.org/download.html) contains this change, if you
> want to actually evaluate the latest changes in your application.
>
> We hope to release 3.12.0 in early April, or maybe even sooner, so if
> you want to provide feedback, you should do so without unnecessary
> delay.
> --
> D. Richard Hipp
> drh at sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] Changing the default page_size in 3.12.0

[Richard Hipp]

On 3/5/16, tonyp at acm.org  wrote:
>>From: Richard Hipp
>>Can you run sqlite3_analyzer on some of your databases and send me the
>>output?
>
> I will as soon as I can manage to build it under Windows.  Although I
> regularly build sqlite3, lemon, and sqldiff, with sqlite3_analyzer I get a
> lot of errors like these:
>
> ...
> sqlite3_analyzer.obj : error LNK2019: unresolved external symbol
> __imp__Tcl_NRCallObjProc referenced in function _DbObjCmdAdaptor
> sqlite3_analyzer.exe : fatal error LNK1120: 59 unresolved externals
>

The sqlite3_analyzer.exe links against libtcl.dll.  Either you don't
have that, or else the version you have is really old, or maybe you
have the 32-bit version and you are trying to link it into a 64-bit
build.

But if you download the pre-release snapshot and type "nmake /f
Makefile.msc sqlite3.dll" or "nmake /f Makefile.msc sqlite3.exe" those
should work just fine.
-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] Changing the default page_size in 3.12.0

[Yongil Jang]

AFAIK, 4, 8, 16 KB sized I/O based on flash memory shows better
performance. (1.x ~ 3.x)

As a result, Android uses 4KB page size.

It's good to embedded devices using flash memory.

2016? 3? 5? (?) 11:22, Donald Shepherd ?? ??:

> On Sat, 5 Mar 2016 at 09:19 Roger Binns  wrote:
>
> > -BEGIN PGP SIGNED MESSAGE-
> > Hash: SHA1
> >
> > On 04/03/16 07:48, Richard Hipp wrote:
> > > The tip of trunk (3.12.0 alpha) changes the default page size for
> > > new database file from 1024 to 4096 bytes. ... This seems like a
> > > potentially disruptive change, so I want to give you, the user
> > > community, plenty of time to consider the consequences and
> > > potentially talk me out of it.
> >
> > Can I talk you into it instead :-)  My standard boilerplate for new
> > databases is to set the page size to 4,096 bytes, and to turn on WAL.
> >
> > Roger
> >
>
> We've headed the same way.  4,096 significantly reduced the size of our
> databases when we switched to it some time ago.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] Changing the default page_size in 3.12.0

[Donald Shepherd]

On Sat, 5 Mar 2016 at 09:19 Roger Binns  wrote:

> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> On 04/03/16 07:48, Richard Hipp wrote:
> > The tip of trunk (3.12.0 alpha) changes the default page size for
> > new database file from 1024 to 4096 bytes. ... This seems like a
> > potentially disruptive change, so I want to give you, the user
> > community, plenty of time to consider the consequences and
> > potentially talk me out of it.
>
> Can I talk you into it instead :-)  My standard boilerplate for new
> databases is to set the page size to 4,096 bytes, and to turn on WAL.
>
> Roger
>

We've headed the same way.  4,096 significantly reduced the size of our
databases when we switched to it some time ago.

[sqlite] Changing the default page_size in 3.12.0

[to...@acm.org]

I ran some tests and almost all of my databases (about 100 of them with 
different content mix and with the biggest one being around 500MB) inflated 
by a lot while only a couple or so shrunk in size by not much.  (It could be 
that I'm just unlucky with my database contents and this change simply 
happens to affect me in a negative way while for most users the results 
would be different.)

For me, this effect alone is enough reason to not wish a change of the 
default size as I would always have to manually change it back.  Database 
size is more important (to me) than access times.

What is the expected gain of this change supposed to be (not only in terms 
of database size but in general)?  Is it just for hypothetical access speed 
improvements?  And, if so, for what size databases? (I would suspect only 
for the very large ones for which usually one wouldn't normally choose 
SQLite3.)

(BTW, will this change also affect FOSSIL repos?)

-Original Message- 
From: Richard Hipp
Sent: Friday, March 04, 2016 5:48 PM
To: General Discussion of SQLite Database ; sqlite-dev
Subject: [sqlite] Changing the default page_size in 3.12.0

The tip of trunk (3.12.0 alpha) changes the default page size for new
database file from 1024 to 4096 bytes.

https://www.sqlite.org/draft/releaselog/3_12_0.html
https://www.sqlite.org/draft/pgszchng2016.html

This seems like a potentially disruptive change, so I want to give
you, the user community, plenty of time to consider the consequences
and potentially talk me out of it.

The "Pre-release Snapshot" on the download page
(https://www.sqlite.org/download.html) contains this change, if you
want to actually evaluate the latest changes in your application.

We hope to release 3.12.0 in early April, or maybe even sooner, so if
you want to provide feedback, you should do so without unnecessary
delay.
-- 
D. Richard Hipp
drh at sqlite.org
___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users 

[sqlite] Changing the default page_size in 3.12.0

[Dan Kennedy]

On 03/04/2016 11:03 PM, Dominique Devienne wrote:
> On Fri, Mar 4, 2016 at 4:48 PM, Richard Hipp  wrote:
>
>>  https://www.sqlite.org/draft/releaselog/3_12_0.html
>
> - from 2000 to -2000
> + from 2000 to 500
>
> [OT] The PRAGMA defer_foreign_keys=ON statement now also disables RESTRICT
> actions on foreign key.
>
> I've confused by that Richard. Does that mean handling of the RESTRICT is
> also postponed to commit-time,
> or that RESTRICT is not honored in deferred FK mode (and thus violating
> referential integrity). --DD
Technically SQLite does the latter, but I don't think that opens up any 
new opportunities to violate referential integrity.

But as far as I can tell "the RESTRICT is also postponed to commit-time" 
and "RESTRICT is not honored" are indistinguishable from the users point 
of view. See the notes on RESTRICT here:

   http://sqlite.org/foreignkeys.html#fk_actions

Dan.