Re: [sqlite] Documentation for Prev. Versions

[Jon Polfer]

DRH - Thanks for the docs; you are very kind.

-Jon

On Thu, Aug 12, 2010 at 11:59 AM, Jon Polfer
wrote:

> Is there a way to get at the documentation for previous versions of 
> SQLite?
>
> I'm running 3.5.9, and don't have much of an opportunity to upgrade.  
> Is there a way that I can get a snapshot of what the wiki / website 
> documentation looked like for 3.5.9?
>

http://www.sqlite.org/sqlite_docs_3_5_9.zip


>
> Thanks,
> Jon
> __
> Jon Polfer
> Project Engineer - High Level Software
>
> Engineering Office Phone: 262-832-0049  (Ext. 5 for Jon Polfer)
> Fax:
> E-mail: jpol...@forceamerica.com
>
> FORCE America Inc.
> W229 N1433 Westwood Drive, Suite 200
> Waukesha, WI 53186
> www.forceamerica.com
>
> The Leading Innovator in Mobile Hydraulic Solutions
>
>
>
>
> The information contained in this message and any attachment may be 
> proprietary, confidential, and privileged or subject to the work 
> product doctrine and thus protected from disclosure.  If the reader of

> this message is not the intended recipient, or an employee or agent 
> responsible for delivering this message to the intended recipient, you

> are hereby notified that any dissemination, distribution or copying of

> this communication is strictly prohibited.
> If you have received this communication in error, please notify me 
> immediately by replying to this message and deleting it and all copies

> and backups thereof.  Thank you.
>
>
> Disclaimer added by CodeTwo Exchange Rules http://www.codetwo.com 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



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


__ 
Jon Polfer
Project Engineer - High Level Software

Engineering Office Phone: 262-832-0049  (Ext. 5 for Jon Polfer)
Fax: 
E-mail: jpol...@forceamerica.com

FORCE America Inc. 
W229 N1433 Westwood Drive, Suite 200 
Waukesha, WI 53186
www.forceamerica.com

The Leading Innovator in Mobile Hydraulic Solutions



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

Re: [sqlite] Distinct Bug

[Dan Kennedy]

On Aug 13, 2010, at 10:34 PM, Andy Chambers wrote:

> Is this a bug?
>
> create table t_distinct_bug (
>  a,
>  b,
>  c
> );
>
> insert into t_distinct_bug values ('1', '1', 'a');
> insert into t_distinct_bug values ('1', '2', 'b');
> insert into t_distinct_bug values ('1', '3', 'c');
> insert into t_distinct_bug values ('1', '1', 'd');
> insert into t_distinct_bug values ('1', '2', 'e');
> insert into t_distinct_bug values ('1', '3', 'f');
>
> select a
>  from (select distinct a, b
>  from t_distinct_bug)
> => 1
>
> I'd have thought it should return
> 1
> 1
> 1

Thanks for this report. Now fixed in fossil tip. Bug here:

   http://www.sqlite.org/src/info/e4b8a2ba6e

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

Re: [sqlite] Is there a way to inner join on named intervals?

[Pavel Ivanov]

> My next question is what index I should create on table
> A and B to speed up such an inner join? Are indexes on each of the
> first three column of each table enough? What is the best comparison
> (along with the appropriate indexes) in term of the performance?

"Indexes on each field" never help. SQLite uses one index per table
per query only.
For your query you should have index on name field in bigger table
(either A or B). If you write query as I did (A.left < B.right and
A.right > B.left) then depending on data distribution either (name,
left) or (name, right) index could help better than just (name). But
cases when those indices would help are very specific, so in general
your only option is index on name only.


Pavel

On Fri, Aug 13, 2010 at 12:48 PM, Peng Yu  wrote:
> On Fri, Aug 13, 2010 at 11:32 AM, Pavel Ivanov  wrote:
>> I don't understand where do you see a problem but it looks like this
>> join will do what you want:
>>
>> select * from A, B
>> where A.name = B.name
>> and A.left < B.right
>> and A.right > B.left
>>
>>> I could use an external program (such as python
>>> sqlite package) to enumerate all the named interval from table A and
>>> search for overlapping named intervals in table B, but this operation
>>> has a complexity of M log (N), where M is the length of table A and N
>>> is the length of table B. If some sort of "inner join" could be used,
>>> the complexity should be reduced to log(M+N).
>>
>> How did you come to this conclusion? Any inner join will execute with
>> complexity either M log(N) or N log(M) anyway. The only benefit is
>> that SQLite can decide which way to join depending on relative size of
>> tables A and B. And constant in operation complexity is a bit smaller
>> with C code that in python code...
>
> Thank all the people that replied my email. I don't really understand
> how inner join work. Sorry for the confusion.
>
> The comparison that I actually need is the following one (logically
> correct, but I'm not sure if it is the fastest comparison in term of
> performance). My next question is what index I should create on table
> A and B to speed up such an inner join? Are indexes on each of the
> first three column of each table enough? What is the best comparison
> (along with the appropriate indexes) in term of the performance?
>
> create table A (name text, left integer, right integer, tag text);
> create table B (name text, left integer, right integer, attr text);
> insert into A values('a', 1, 10, 'tag1');
> insert into A values('a', 5, 15, 'tag2');
> insert into A values('a', 21, 30, 'tag3');
> insert into A values('b', 3, 12, 'tag4');
> insert into A values('b', 15, 25, 'tag5');
> insert into A values('b', 19, 30, 'tag6');
>
> insert into B values('a', 3, 7, 'attr1');
> insert into B values('a', 8, 12, 'attr2');
> insert into B values('a', 16, 18, 'attr3');
> insert into B values('a', 25, 35, 'attr4');
> insert into B values('b', 31, 32, 'attr5');
>
> select * from A inner join B on A.name=B.name AND max(A.left, B.left)
> < min(A.right, B.right);
>
> name        left        right       tag         name        left
>  right       attr
> --  --  --  --  --  --
>  --  --
> a           1           10          tag1        a           3
>  7           attr1
> a           1           10          tag1        a           8
>  12          attr2
> a           5           15          tag2        a           3
>  7           attr1
> a           5           15          tag2        a           8
>  12          attr2
> a           21          30          tag3        a           25
>  35          attr4
>
>
>> On Fri, Aug 13, 2010 at 11:07 AM, Peng Yu  wrote:
>>> Hi,
>>>
>>> Suppose that I have a table "A", each row represents a interval. For
>>> example, the first row represents an interval [1,10) with a name "a".
>>> The first and second rows are considered overlapping because the
>>> interval [1,10) and interval [5,15) intersect and both rows have the
>>> same name "a".
>>>
>>> name left right   tag
>>> -
>>> a          1     10   tag1
>>> a          5     15   tag2
>>> a        21     30   tag3
>>> b          3     12   tag4
>>> b        15     25   tag5
>>> b        19     30   tag6
>>>
>>> I want to "inner join" the above table and the following table "B"
>>> based on the named interval overlapping.
>>>
>>> name left right   attr
>>> -
>>> a          3       7   attr1
>>> a          8     12   attr2
>>> a        16     18   attr3
>>> a        25     35   attr4
>>> b        31     32   attr5
>>>
>>> The result is the following. In each row, the named interval from A
>>> overlaps the named interval from B. I don't see there is an easy way
>>> to do this in sqlite3. I could use an external program (such as python
>>> sqlite package) to enumerate all the named interval 

Re: [sqlite] Is there a table that show all the available function from sqlite3?

[Dan Kennedy]

On Aug 13, 2010, at 11:37 PM, Peng Yu wrote:

> Hi,
>
> http://www.sqlite.org/docs.html
>
> I don't see a table that shows all the available functions in sqlite3.
> Would you please let me know if there is such a table?

   http://www.sqlite.org/lang_corefunc.html
   http://www.sqlite.org/lang_datefunc.html

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

Re: [sqlite] Is there a table that show all the available function from sqlite3?

[Jay A. Kreibich]

On Fri, Aug 13, 2010 at 11:37:59AM -0500, Peng Yu scratched on the wall:
> Hi,
> 
> http://www.sqlite.org/docs.html
> 
> I don't see a table that shows all the available functions in sqlite3.
> Would you please let me know if there is such a table?


  There is not.  As far as I know, there is no way to extract the
  current function list.

   -j

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

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is there a table that show all the available function from sqlite3?

[Adam DeVita]

Are you looking for
http://www.sqlite.org/c3ref/funclist.html
?



On Fri, Aug 13, 2010 at 12:37 PM, Peng Yu  wrote:

> Hi,
>
> http://www.sqlite.org/docs.html
>
> I don't see a table that shows all the available functions in sqlite3.
> Would you please let me know if there is such a table?
>
> --
> Regards,
> Peng
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
VerifEye Technologies Inc.
905-948-0015x245
7100 Warden Ave, Unit 3
Markham ON, L3R 8B5
Canada
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is there a way to inner join on named intervals?

[Peng Yu]

On Fri, Aug 13, 2010 at 11:32 AM, Pavel Ivanov  wrote:
> I don't understand where do you see a problem but it looks like this
> join will do what you want:
>
> select * from A, B
> where A.name = B.name
> and A.left < B.right
> and A.right > B.left
>
>> I could use an external program (such as python
>> sqlite package) to enumerate all the named interval from table A and
>> search for overlapping named intervals in table B, but this operation
>> has a complexity of M log (N), where M is the length of table A and N
>> is the length of table B. If some sort of "inner join" could be used,
>> the complexity should be reduced to log(M+N).
>
> How did you come to this conclusion? Any inner join will execute with
> complexity either M log(N) or N log(M) anyway. The only benefit is
> that SQLite can decide which way to join depending on relative size of
> tables A and B. And constant in operation complexity is a bit smaller
> with C code that in python code...

Thank all the people that replied my email. I don't really understand
how inner join work. Sorry for the confusion.

The comparison that I actually need is the following one (logically
correct, but I'm not sure if it is the fastest comparison in term of
performance). My next question is what index I should create on table
A and B to speed up such an inner join? Are indexes on each of the
first three column of each table enough? What is the best comparison
(along with the appropriate indexes) in term of the performance?

create table A (name text, left integer, right integer, tag text);
create table B (name text, left integer, right integer, attr text);
insert into A values('a', 1, 10, 'tag1');
insert into A values('a', 5, 15, 'tag2');
insert into A values('a', 21, 30, 'tag3');
insert into A values('b', 3, 12, 'tag4');
insert into A values('b', 15, 25, 'tag5');
insert into A values('b', 19, 30, 'tag6');

insert into B values('a', 3, 7, 'attr1');
insert into B values('a', 8, 12, 'attr2');
insert into B values('a', 16, 18, 'attr3');
insert into B values('a', 25, 35, 'attr4');
insert into B values('b', 31, 32, 'attr5');

select * from A inner join B on A.name=B.name AND max(A.left, B.left)
< min(A.right, B.right);

nameleftright   tag nameleft
 right   attr
--  --  --  --  --  --
 --  --
a   1   10  tag1a   3
 7   attr1
a   1   10  tag1a   8
 12  attr2
a   5   15  tag2a   3
 7   attr1
a   5   15  tag2a   8
 12  attr2
a   21  30  tag3a   25
 35  attr4


> On Fri, Aug 13, 2010 at 11:07 AM, Peng Yu  wrote:
>> Hi,
>>
>> Suppose that I have a table "A", each row represents a interval. For
>> example, the first row represents an interval [1,10) with a name "a".
>> The first and second rows are considered overlapping because the
>> interval [1,10) and interval [5,15) intersect and both rows have the
>> same name "a".
>>
>> name left right   tag
>> -
>> a          1     10   tag1
>> a          5     15   tag2
>> a        21     30   tag3
>> b          3     12   tag4
>> b        15     25   tag5
>> b        19     30   tag6
>>
>> I want to "inner join" the above table and the following table "B"
>> based on the named interval overlapping.
>>
>> name left right   attr
>> -
>> a          3       7   attr1
>> a          8     12   attr2
>> a        16     18   attr3
>> a        25     35   attr4
>> b        31     32   attr5
>>
>> The result is the following. In each row, the named interval from A
>> overlaps the named interval from B. I don't see there is an easy way
>> to do this in sqlite3. I could use an external program (such as python
>> sqlite package) to enumerate all the named interval from table A and
>> search for overlapping named intervals in table B, but this operation
>> has a complexity of M log (N), where M is the length of table A and N
>> is the length of table B. If some sort of "inner join" could be used,
>> the complexity should be reduced to log(M+N). I'm wondering if there
>> something that can help do this kind of named interval inner join
>> easily.
>>
>> A.name A.left A.right A.tag B.name B.left B.right B.attr
>> 
>> a                  1     10     tag1        a          3       7    attr1
>> a                  1     10     tag1        a          8     12    attr2
>> a                  5     15     tag2        a          3       7    attr1
>> a                  5     15     tag2        a          8     12    attr2
>> a                21     30     tag3        a        16     18    attr3
>>
>> --
>> Regards,
>> Peng
>> 

[sqlite] Is there a table that show all the available function from sqlite3?

[Peng Yu]

Hi,

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

I don't see a table that shows all the available functions in sqlite3.
Would you please let me know if there is such a table?

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

Re: [sqlite] Is there a way to inner join on named intervals?

[Pavel Ivanov]

I don't understand where do you see a problem but it looks like this
join will do what you want:

select * from A, B
where A.name = B.name
and A.left < B.right
and A.right > B.left

> I could use an external program (such as python
> sqlite package) to enumerate all the named interval from table A and
> search for overlapping named intervals in table B, but this operation
> has a complexity of M log (N), where M is the length of table A and N
> is the length of table B. If some sort of "inner join" could be used,
> the complexity should be reduced to log(M+N).

How did you come to this conclusion? Any inner join will execute with
complexity either M log(N) or N log(M) anyway. The only benefit is
that SQLite can decide which way to join depending on relative size of
tables A and B. And constant in operation complexity is a bit smaller
with C code that in python code...


Pavel

On Fri, Aug 13, 2010 at 11:07 AM, Peng Yu  wrote:
> Hi,
>
> Suppose that I have a table "A", each row represents a interval. For
> example, the first row represents an interval [1,10) with a name "a".
> The first and second rows are considered overlapping because the
> interval [1,10) and interval [5,15) intersect and both rows have the
> same name "a".
>
> name left right   tag
> -
> a          1     10   tag1
> a          5     15   tag2
> a        21     30   tag3
> b          3     12   tag4
> b        15     25   tag5
> b        19     30   tag6
>
> I want to "inner join" the above table and the following table "B"
> based on the named interval overlapping.
>
> name left right   attr
> -
> a          3       7   attr1
> a          8     12   attr2
> a        16     18   attr3
> a        25     35   attr4
> b        31     32   attr5
>
> The result is the following. In each row, the named interval from A
> overlaps the named interval from B. I don't see there is an easy way
> to do this in sqlite3. I could use an external program (such as python
> sqlite package) to enumerate all the named interval from table A and
> search for overlapping named intervals in table B, but this operation
> has a complexity of M log (N), where M is the length of table A and N
> is the length of table B. If some sort of "inner join" could be used,
> the complexity should be reduced to log(M+N). I'm wondering if there
> something that can help do this kind of named interval inner join
> easily.
>
> A.name A.left A.right A.tag B.name B.left B.right B.attr
> 
> a                  1     10     tag1        a          3       7    attr1
> a                  1     10     tag1        a          8     12    attr2
> a                  5     15     tag2        a          3       7    attr1
> a                  5     15     tag2        a          8     12    attr2
> a                21     30     tag3        a        16     18    attr3
>
> --
> Regards,
> Peng
> ___
> 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] Distinct Bug

[Simon Davies]

On 13 August 2010 16:34, Andy Chambers  wrote:
> Is this a bug?
>
> create table t_distinct_bug (
>  a,
>  b,
>  c
> );
>
> insert into t_distinct_bug values ('1', '1', 'a');
> insert into t_distinct_bug values ('1', '2', 'b');
> insert into t_distinct_bug values ('1', '3', 'c');
> insert into t_distinct_bug values ('1', '1', 'd');
> insert into t_distinct_bug values ('1', '2', 'e');
> insert into t_distinct_bug values ('1', '3', 'f');
>
> select a
>  from (select distinct a, b
>          from t_distinct_bug)
> => 1
>
> I'd have thought it should return
> 1
> 1
> 1
>
> I'm on SQLite 3.6.22

Doesn't look right to me. I can confirm same results with 3.7.0.1 (Windows 7)

>
> --
> 
> Andy Chambers
> Formedix Ltd

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

[sqlite] Distinct Bug

[Andy Chambers]

Is this a bug?

create table t_distinct_bug (
  a,
  b,
  c
);

insert into t_distinct_bug values ('1', '1', 'a');
insert into t_distinct_bug values ('1', '2', 'b');
insert into t_distinct_bug values ('1', '3', 'c');
insert into t_distinct_bug values ('1', '1', 'd');
insert into t_distinct_bug values ('1', '2', 'e');
insert into t_distinct_bug values ('1', '3', 'f');

select a
  from (select distinct a, b
  from t_distinct_bug)
=> 1

I'd have thought it should return
1
1
1

I'm on SQLite 3.6.22

-- 

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

Re: [sqlite] Is there a way to inner join on named intervals?

[Simon Davies]

On 13 August 2010 16:07, Peng Yu  wrote:
> Hi,
>
> Suppose that I have a table "A", each row represents a interval. For
> example, the first row represents an interval [1,10) with a name "a".
> The first and second rows are considered overlapping because the
> interval [1,10) and interval [5,15) intersect and both rows have the
> same name "a".
>
> name left right   tag
> -
> a          1     10   tag1
> a          5     15   tag2
> a        21     30   tag3
> b          3     12   tag4
> b        15     25   tag5
> b        19     30   tag6
>
> I want to "inner join" the above table and the following table "B"
> based on the named interval overlapping.
>
> name left right   attr
> -
> a          3       7   attr1
> a          8     12   attr2
> a        16     18   attr3
> a        25     35   attr4
> b        31     32   attr5
>
> The result is the following. In each row, the named interval from A
> overlaps the named interval from B. I don't see there is an easy way
> to do this in sqlite3. I could use an external program (such as python
> sqlite package) to enumerate all the named interval from table A and
> search for overlapping named intervals in table B, but this operation
> has a complexity of M log (N), where M is the length of table A and N
> is the length of table B. If some sort of "inner join" could be used,
> the complexity should be reduced to log(M+N). I'm wondering if there
> something that can help do this kind of named interval inner join
> easily.
>
> A.name A.left A.right A.tag B.name B.left B.right B.attr
> 
> a                  1     10     tag1        a          3       7    attr1
> a                  1     10     tag1        a          8     12    attr2
> a                  5     15     tag2        a          3       7    attr1
> a                  5     15     tag2        a          8     12    attr2
> a                21     30     tag3        a        16     18    attr3

Last line does not overlap.

Assuming that is an oversight, then

select * from A inner join B on A.name=B.name and A.left<=B.right and
A.right >=B.left;

seems to do what you want.

>
> --
> Regards,
> Peng

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

Re: [sqlite] Is there a way to inner join on named intervals?

[Jim Morris]

Did you try something like(pseudo code):

select * from A inner join B on A.name = B.name AND ( B.left 
between(A.left,A.right) OR B.right between(A.left,A.right) )

On 8/13/2010 8:07 AM, Peng Yu wrote:
> Hi,
>
> Suppose that I have a table "A", each row represents a interval. For
> example, the first row represents an interval [1,10) with a name "a".
> The first and second rows are considered overlapping because the
> interval [1,10) and interval [5,15) intersect and both rows have the
> same name "a".
>
> name left right   tag
> -
> a  1 10   tag1
> a  5 15   tag2
> a21 30   tag3
> b  3 12   tag4
> b15 25   tag5
> b19 30   tag6
>
> I want to "inner join" the above table and the following table "B"
> based on the named interval overlapping.
>
> name left right   attr
> -
> a  3   7   attr1
> a  8 12   attr2
> a16 18   attr3
> a25 35   attr4
> b31 32   attr5
>
> The result is the following. In each row, the named interval from A
> overlaps the named interval from B. I don't see there is an easy way
> to do this in sqlite3. I could use an external program (such as python
> sqlite package) to enumerate all the named interval from table A and
> search for overlapping named intervals in table B, but this operation
> has a complexity of M log (N), where M is the length of table A and N
> is the length of table B. If some sort of "inner join" could be used,
> the complexity should be reduced to log(M+N). I'm wondering if there
> something that can help do this kind of named interval inner join
> easily.
>
> A.name A.left A.right A.tag B.name B.left B.right B.attr
> 
> a  1 10 tag1a  3   7attr1
> a  1 10 tag1a  8 12attr2
> a  5 15 tag2a  3   7attr1
> a  5 15 tag2a  8 12attr2
> a21 30 tag3a16 18attr3
>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Is there a way to inner join on named intervals?

[Peng Yu]

Hi,

Suppose that I have a table "A", each row represents a interval. For
example, the first row represents an interval [1,10) with a name "a".
The first and second rows are considered overlapping because the
interval [1,10) and interval [5,15) intersect and both rows have the
same name "a".

name left right   tag
-
a  1 10   tag1
a  5 15   tag2
a21 30   tag3
b  3 12   tag4
b15 25   tag5
b19 30   tag6

I want to "inner join" the above table and the following table "B"
based on the named interval overlapping.

name left right   attr
-
a  3   7   attr1
a  8 12   attr2
a16 18   attr3
a25 35   attr4
b31 32   attr5

The result is the following. In each row, the named interval from A
overlaps the named interval from B. I don't see there is an easy way
to do this in sqlite3. I could use an external program (such as python
sqlite package) to enumerate all the named interval from table A and
search for overlapping named intervals in table B, but this operation
has a complexity of M log (N), where M is the length of table A and N
is the length of table B. If some sort of "inner join" could be used,
the complexity should be reduced to log(M+N). I'm wondering if there
something that can help do this kind of named interval inner join
easily.

A.name A.left A.right A.tag B.name B.left B.right B.attr

a  1 10 tag1a  3   7attr1
a  1 10 tag1a  8 12attr2
a  5 15 tag2a  3   7attr1
a  5 15 tag2a  8 12attr2
a21 30 tag3a16 18attr3

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

Re: [sqlite] Reserve database pages

[Dan Kennedy]

On Aug 13, 2010, at 8:41 PM, Max Vlasov wrote:

> On Fri, Aug 13, 2010 at 1:38 PM, Max Vlasov   
> wrote:
>
>>
>> I can approximately calculate, how big the new database will grow. Is
>>> there a way to tell SQLite to reserve an inital space or numer of  
>>> pages
>>> instead of letting the database file grow again and again?
>>>
>>
>>
>> Thought about this recently. Another idea is to tweak VFS. Since  
>> xWrite
>> method is supposed to accept iOfst that is bigger than the current  
>> file
>> size, one can check whether the new write query is going to  
>> allocate new
>> space for the file (vs internal space writing), and before actual  
>> call make
>> prior call of the same function writing for example a single zero  
>> byte a
>> little far and after that perform the original request.

The fossil tip at present supports the SQLITE_FCNTL_CHUNK_SIZE
argument to sqlite3_file_control(). To allocate space
in 1MB chunks:

void setOneMBChunkSize(sqlite3 *db){
   int szChunk = 1024*1024;
   sqlite3_file_control(db, "main", SQLITE_FCNTL_CHUNK_SIZE,  
(void*));
}

 From that point on, connection "db" extends and truncates
the db file in 1MB chunks.

Works on unix and win32.

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

Re: [sqlite] Reserve database pages

[Max Vlasov]

On Fri, Aug 13, 2010 at 1:38 PM, Max Vlasov  wrote:

>
> I can approximately calculate, how big the new database will grow. Is
>> there a way to tell SQLite to reserve an inital space or numer of pages
>> instead of letting the database file grow again and again?
>>
>
>
> Thought about this recently. Another idea is to tweak VFS. Since xWrite
> method is supposed to accept iOfst that is bigger than the current file
> size, one can check whether the new write query is going to allocate new
> space for the file (vs internal space writing), and before actual call make
> prior call of the same function writing for example a single zero byte a
> little far and after that perform the original request.


Took not so much time to try it, the good news is that the tweak technically
works (thanks to the flexible VFS sqlite uses), the bad news is that it
gives no significant improvement, at least on Windows XP, both FAT32 and
NTFS, only in some specific cases a little difference was noticeable.

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

Re: [sqlite] Reserve database pages

[Max Vlasov]

> I can approximately calculate, how big the new database will grow. Is
> there a way to tell SQLite to reserve an inital space or numer of pages
> instead of letting the database file grow again and again?
>


Thought about this recently. Another idea is to tweak VFS. Since xWrite
method is supposed to accept iOfst that is bigger than the current file
size, one can check whether the new write query is going to allocate new
space for the file (vs internal space writing), and before actual call make
prior call of the same function writing for example a single zero byte a
little far and after that perform the original request. The only thing that
can be affected in this case is the routines that use xFileSize. I looked at
the sources, at least backup Api uses it, it either should be
straightforward in this case or "pretend" and report the expected file size.
>From what I see, it seems that the sqlite internally doesn't rely on the
file size for the core functionality and xFileSize looks more like a service
function, but I may be wrong

Max Vlasov,
www.maxerist.net
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users