Re: [sqlite] Named parameters and spaces.

[Steven Michalske]

On Jan 10, 2012, at 5:02 PM, Darren Duncan wrote:

> Steven Michalske wrote:
>> On Tue, Jan 10, 2012 at 3:19 PM, Darren Duncan  
>> wrote:
>>> Steven Michalske wrote:
 I would like to use the :nnn named parameters but have spaces in the
 named parameters.
 
 It seems that ":nnn nnn", :"nn nn", or :nn\ nn are all not recognized.
 
 Is there a way to use spaces in named parameters?
>>> 
>>> I would expect : to be the correct format for a named parameter,
>>> meaning :foo or :"bar" as the case may be.  So, you should be able to write
>>> 
>>> :"nn nn"
>>> 
>>> ... and if you can't, then the DBMS should be fixed so that you can do that.
>>> The other 2 options you gave should not be supported.
>> The python sqlite driver calls functions for parameter substitution
>> from the sqlite3 c library.  There is no manipulation of the string
>> before it is passed into the sqlite parser.
>> I could not find documentation on the requirements for the :nnn form
>> of parameter substitution.  Perhaps a bug is in order to improve the
>> documentation.   Perhaps include a regex of the acceptable
>> identifiers.
>> Unfortunately the format :"foo bar" is not accepted as a parameter by 
>> sqlite3.
> 
> Just to be clear, the :"foo bar" is what you write in the SQL having the 
> parameter, for example, 'select * from abc where def = :"foo bar"'.  And then 
> in the SQLite parameter binding call you just use 'foo bar' (because that is 
> the actual parameter name) as the parameter name argument. -- Darren Duncan

The following would be a psudo code

c.execute('''insert col into the_table values(:"foo bar", :param2)''', {"foo 
bar":"value_to_insert", "param2":3.4})

This is what would not work.

Steve

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

[sqlite] Enabling Shared-Cache Mode

[smallboat]

Hi, 


I am emailing to ask how I can know if the shared cache is 
acutally working and how many shared cache is created for the process.

In
 my embedded sqlite running inside a process, we want to have all 
read-only db connections to share same cache to reduce memory while 
there are multiple read-only and read-write connections request coming 
to the database. 

What we did is that using (SQLITE_OPEN_SHAREDCACHE || SQLITE_OPEN_READONLY) as 
the third parameter in 
sqlite3_enable_shared_cache(int)

We
 hope that sqlites creates only one shared cache for all read-only 
connections while sqlites open privatate cache for read-write ones. 

But we do not see memory reduction after using SQLITE_OPEN_SHAREDCACHE in 
sqlite3_enable_shared_cache(int).


So our questions are:
1) how many shared cache is created for the all read-only db connections?
2) how to know the number of shared caches and know the db connections share 
the caches? 

Thanks.

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

Re: [sqlite] primary key autoincrement updation problem in case refereced

[Simon Slavin]

On 11 Jan 2012, at 6:05am, Durga D wrote:

> But, sqlite3_step(stmtoft1); is failing always. What might be the wrong
> with this approach.

What code is it reporting when it fails ?

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

Re: [sqlite] primary key autoincrement updation problem in case refereced

[jr]

On 11/01/12 06:58, Durga D wrote:


hh field updation in t1 table is the issue.

If I do in sqlite shell, it's working fine. In C language only, I have this
issue.


> Any idea?
>

hmm, you'd need to provide (excerpts of) the actual code before I could 
say any more.  if you can post (as plain text attachment) examples of 
your shell use and the relevant C code, plus a few sample records, I'd 
be happy to look it over (later tonight, off to work soon).


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

Re: [sqlite] primary key autoincrement updation problem in case refereced

[Durga D]

then, how to insert the record with binding and step apis in C language
with second approach?

hh field updation in t1 table is the issue.

If I do in sqlite shell, it's working fine. In C language only, I have this
issue.

Any idea?


On Wed, Jan 11, 2012 at 12:17 PM, jr  wrote:

> On 11/01/12 06:29, Durga D wrote:
>
>> Deleted sqlite data base before executing first approach.
>> Again deleted sqlite database before executing second approach.
>>
>
> ok, misread this and thought 'first approach' relevant.
>
>
>
>> Issue is related to database design or insertion procedure.
>>
>>
> from what I can see there's no problem with the table design (created them
> here).
>
>
> jr.
> __**_
> 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] primary key autoincrement updation problem in case refereced

[jr]

On 11/01/12 06:29, Durga D wrote:

Deleted sqlite data base before executing first approach.
and again delete sqlite database before executing second approach.


ok, misread this and thought 'first approach' relevant.



Issue is related to database design or insertion procedure.



from what I can see there's no problem with the table design (created 
them here).


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

Re: [sqlite] primary key autoincrement updation problem in case refereced

[Durga D]

Deleted sqlite data base before executing first approach.
and again delete sqlite database before executing second approach.

Issue is related to database design or insertion procedure.

There is no syntax problems in queries also.

On Wed, Jan 11, 2012 at 11:54 AM, jr  wrote:

> On 11/01/12 06:05, Durga D wrote:
>
> if I understand correctly: first you created tables t1 and t2, then you
> say 'create if not exist' -- but they already do.  instead of (re-)creating
> tables t1 and t2, ALTER TABLE statements can be used to modify the existing.
>
> jr.
> __**_
> 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] primary key autoincrement updation problem in case refereced

[jr]

On 11/01/12 06:05, Durga D wrote:

if I understand correctly: first you created tables t1 and t2, then you 
say 'create if not exist' -- but they already do.  instead of 
(re-)creating tables t1 and t2, ALTER TABLE statements can be used to 
modify the existing.


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

[sqlite] Enabling Shared-Cache Mode

[smallboat]

Hi, 

I am emailing to ask how I can know if the shared cache is acutally working and 
how many shared cache is created for the process.

In my embedded sqlite running inside a process, we want to have all read-only 
db connections to share same cache to reduce memory while there are multiple 
read-only and read-write connections request coming to the database. 

What we did is that using (SQLITE_OPEN_SHAREDCACHE || SQLITE_OPEN_READONLY) as 
the third parameter in 
sqlite3_enable_shared_cache(int)

We hope that sqlites creates only one shared cache for all read-only 
connections while sqlites open privatate cache for read-write ones. 

But we do not see memory reduction after using SQLITE_OPEN_SHAREDCACHE in 
sqlite3_enable_shared_cache(int).


So our questions are:
1) how many shared cache is created for the all read-only db connections?
2) how to know the number of shared caches and know the db connections share 
the caches? 

Thanks.

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

[sqlite] primary key autoincrement updation problem in case refereced

[Durga D]

First approach:

I have created database with below schema:

create table if not exists t1 ( i integer primary key autoincrement, t1info
text);
create table if not exists t2 (ii integer primary key, t2info text, foreign
key (ii) references t1 (i) );

I created two prepared statement for t1 and t2.
Updated the records (t1 and t2 table) by using bind and step apis in
trasaction.
Always binding t1info for t1 table and t2info for t2 table only. It's
working fine.
primary key is auto increment, so it's increasing and updating in t1 and t2
tables. It' working fine.

-

Second approach:

Now, I need to change the schema this way:


create table if not exists t0 ( h integer primary key autoincrement, t0info
text);
create table if not exists t1 ( i integer primary key autoincrement, t1info
text, hh integer not null, unique(i, hh), foreign key (hh) references t0
(h) );
create table if not exists t2 (ii integer primary key, t2info text, foreign
key (ii) references t1 (i) );

I created three prepared statement for t0, t1 and t2.
Updated the records (t0, t1 and t2 table) by using bind and step apis in
trasaction.
Binding t0info for t0, t1info for t1 table and t2info for t2 table only.

But, sqlite3_step(stmtoft1); is failing always. What might be the wrong
with this approach.

realtions among tables are not proper with second approach? if yes, please
suggest.

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

Re: [sqlite] Named parameters and spaces.

[Darren Duncan]

Steven Michalske wrote:

On Tue, Jan 10, 2012 at 3:19 PM, Darren Duncan  wrote:

Steven Michalske wrote:

I would like to use the :nnn named parameters but have spaces in the
named parameters.

It seems that ":nnn nnn", :"nn nn", or :nn\ nn are all not recognized.

Is there a way to use spaces in named parameters?


I would expect : to be the correct format for a named parameter,
meaning :foo or :"bar" as the case may be.  So, you should be able to write

 :"nn nn"

... and if you can't, then the DBMS should be fixed so that you can do that.
The other 2 options you gave should not be supported.


The python sqlite driver calls functions for parameter substitution
from the sqlite3 c library.  There is no manipulation of the string
before it is passed into the sqlite parser.

I could not find documentation on the requirements for the :nnn form
of parameter substitution.  Perhaps a bug is in order to improve the
documentation.   Perhaps include a regex of the acceptable
identifiers.

Unfortunately the format :"foo bar" is not accepted as a parameter by sqlite3.


Just to be clear, the :"foo bar" is what you write in the SQL having the 
parameter, for example, 'select * from abc where def = :"foo bar"'.  And then in 
the SQLite parameter binding call you just use 'foo bar' (because that is the 
actual parameter name) as the parameter name argument. -- Darren Duncan

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

Re: [sqlite] Named parameters and spaces.

[Simon Slavin]

On 10 Jan 2012, at 11:57pm, Steven Michalske wrote:

> Some day they might have a field that is named with the underscore as
> a second column; not saying it makes sense.  We find all kinds on the
> internet :-)

Well some day they might have a field that is named with a space, too.  There's 
nothing in SQLite to forbid it.  The real solution is to use named parameters 
the way they were designed, not try to match the name of the parameter with the 
name of the field.

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

Re: [sqlite] Named parameters and spaces.

[Steven Michalske]

On Tue, Jan 10, 2012 at 2:37 PM, Pavel Ivanov  wrote:
>> Is there a way to use spaces in named parameters?
>
> No. Quick fix would be to replace all your spaces with underscores.
>
>
> Pavel

Understood, that what I did to cope, but that involves data
manipulation of outside data.  A feature that I like to minimize.

Some day they might have a field that is named with the underscore as
a second column; not saying it makes sense.  We find all kinds on the
internet :-)

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

Re: [sqlite] Named parameters and spaces.

[Steven Michalske]

On Tue, Jan 10, 2012 at 3:19 PM, Darren Duncan  wrote:
> Steven Michalske wrote:
>>
>> I would like to use the :nnn named parameters but have spaces in the
>> named parameters.
>>
>> It seems that ":nnn nnn", :"nn nn", or :nn\ nn are all not recognized.
>>
>> Is there a way to use spaces in named parameters?
>
>
> I would expect : to be the correct format for a named parameter,
> meaning :foo or :"bar" as the case may be.  So, you should be able to write
>
>  :"nn nn"
>
> ... and if you can't, then the DBMS should be fixed so that you can do that.
> The other 2 options you gave should not be supported.

The python sqlite driver calls functions for parameter substitution
from the sqlite3 c library.  There is no manipulation of the string
before it is passed into the sqlite parser.

I could not find documentation on the requirements for the :nnn form
of parameter substitution.  Perhaps a bug is in order to improve the
documentation.   Perhaps include a regex of the acceptable
identifiers.

Unfortunately the format :"foo bar" is not accepted as a parameter by sqlite3.

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

Re: [sqlite] Named parameters and spaces.

[Darren Duncan]

Steven Michalske wrote:

I would like to use the :nnn named parameters but have spaces in the
named parameters.

It seems that ":nnn nnn", :"nn nn", or :nn\ nn are all not recognized.

Is there a way to use spaces in named parameters?


I would expect : to be the correct format for a named parameter, 
meaning :foo or :"bar" as the case may be.  So, you should be able to write


  :"nn nn"

... and if you can't, then the DBMS should be fixed so that you can do that. 
The other 2 options you gave should not be supported.


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

Re: [sqlite] Named parameters and spaces.

[Pavel Ivanov]

> Is there a way to use spaces in named parameters?

No. Quick fix would be to replace all your spaces with underscores.


Pavel


On Tue, Jan 10, 2012 at 5:31 PM, Steven Michalske  wrote:
> Hello,
>
> I would like to use the :nnn named parameters but have spaces in the
> named parameters.
>
> It seems that ":nnn nnn", :"nn nn", or :nn\ nn are all not recognized.
>
> Is there a way to use spaces in named parameters?
>
> Steve
> ___
> 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] Named parameters and spaces.

[Steven Michalske]

Hello,

I would like to use the :nnn named parameters but have spaces in the
named parameters.

It seems that ":nnn nnn", :"nn nn", or :nn\ nn are all not recognized.

Is there a way to use spaces in named parameters?

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

Re: [sqlite] Import File with Mixed Delimiters

[Simon Slavin]

On 10 Jan 2012, at 5:13pm, rpallotta wrote:

> Thanks for the reply, but do I necessarily need to use php? I'd like the
> ability to do it from the SQL command window using a variation of the
> .import command, if possible. 

You can't.  The .import command can't cope with such things.  Either write a 
program to do the importing, or before you do the importing use an editor to 
massage your text file into a form where all columns are separated by the same 
separator character.

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

Re: [sqlite] aggregate and index.

[trash Spam]

2012/1/10 Richard Hipp 

> On Tue, Jan 10, 2012 at 12:56 PM, trash Spam 
> wrote:
>
> SELECT (SELECT min(col3) FROM t1)+1;
>

Works, thank you.


> > 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
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] aggregate and index.

[Richard Hipp]

On Tue, Jan 10, 2012 at 12:56 PM, trash Spam  wrote:

> Hi,
>
> Simplified example :
> Table t1
> (
> col1number  primary key
>,col2varchar( 50 )
>,col3number
> )
>
> select
>min( col3 )
> fro
>t1
> ;
> explain plan :
> orderfromdetail
> 00TABLE t1 WITH INDEX idx_t1_col3 ORDER BY
>
> select
>min( col3 ) + 1
> fro
>t1
> ;
> orderfromdetail
> 00TABLE t1
>
> Why the second case cannot use index idx_t1_col3 ?
>
> How i can force the query to use the index ?
>


SELECT (SELECT min(col3) FROM t1)+1;


>
> In the real case the query is :
> select
>col2
> from
>t1
> where
>   col3 = (
>  select
>  min( col3 ) + :delta
>  from
> t1
>  )
> ;
> I don't know the explain plan with bind variable.
>
> Thanks.
>
> --
> Philippe.
> ___
> 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

Re: [sqlite] PRAGMA index_info explanation

[Richard Hipp]

On Tue, Jan 10, 2012 at 12:30 PM, Marco Bambini  wrote:

> From the PRAGMA index_info documentation:
> This pragma returns one row each column in the named index. The first
> column of the result is the rank of the column within the index. The second
> column of the result is the rank of the column within the table. The third
> column of output is the name of the column being indexed.
>
> I am particularly interested in the rank fields … what does rank really
> means here?
> Is there a lower/upper bound to this rank value?
>

Rank means the position in the table or index.  The first column of the
table is 0.  The second column of the table is 1.  The third column is 2,
and so forth.  Similarly for the index.


>
> Thanks a lot.
> --
> Marco Bambini
> http://www.sqlabs.com
> http://twitter.com/sqlabs
>
> ___
> 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

[sqlite] aggregate and index.

[trash Spam]

Hi,

Simplified example :
Table t1
(
 col1number  primary key
,col2varchar( 50 )
,col3number
)

select
min( col3 )
fro
t1
;
explain plan :
orderfromdetail
00TABLE t1 WITH INDEX idx_t1_col3 ORDER BY

select
min( col3 ) + 1
fro
t1
;
orderfromdetail
00TABLE t1

Why the second case cannot use index idx_t1_col3 ?

How i can force the query to use the index ?

In the real case the query is :
select
col2
from
t1
where
   col3 = (
  select
  min( col3 ) + :delta
  from
 t1
 )
;
I don't know the explain plan with bind variable.

Thanks.

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

[sqlite] PRAGMA index_info explanation

[Marco Bambini]

>From the PRAGMA index_info documentation:
This pragma returns one row each column in the named index. The first column of 
the result is the rank of the column within the index. The second column of the 
result is the rank of the column within the table. The third column of output 
is the name of the column being indexed.

I am particularly interested in the rank fields … what does rank really means 
here?
Is there a lower/upper bound to this rank value?

Thanks a lot.
--
Marco Bambini
http://www.sqlabs.com
http://twitter.com/sqlabs

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

Re: [sqlite] Import File with Mixed Delimiters

[Kit]

2012/1/10 rpallotta :
> Thanks for the reply, but do I necessarily need to use php? I'd like the
> ability to do it from the SQL command window using a variation of the
> .import command, if possible.

That was my first idea, because I work with SQLite from PHP mainly. I
can not think of anything else, perhaps modify SQLite shell or modify
input data by some filter.
-- 
Kit
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Import File with Mixed Delimiters

[rpallotta]

Thanks for the reply, but do I necessarily need to use php? I'd like the
ability to do it from the SQL command window using a variation of the
.import command, if possible. 


Kit-18 wrote:
> 
> 2012/1/9 rpallotta :
>>
>> I need to import a file to a table but there are two delimiters used in
>> the
>> file; quotes and spaces. The first 2 columns are for metadata and use
>> quotes, the remaining columns are financial data (a column for each
>> month)
>> separated by spaces. For example:
>>
>> "Net Sales" "New York" 1000.00 999.00 1112.00
>> "Expenses" "New York" 555.00 600.00 500.00
> 
>  $f = fopen("example.csv", "r");
> $data=fgetcsv($f, 1000, " ");
> print_r($data);
> ?>
> 
> [0] => Net Sales
> [1] => New York
> [2] => 1000.00
> [3] => 999.00
> [4] => 1112.00
> -- 
> Kit
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 

-- 
View this message in context: 
http://old.nabble.com/Import-File-with-Mixed-Delimiters-tp33107824p33113156.html
Sent from the SQLite mailing list archive at Nabble.com.

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

Re: [sqlite] Problems encountered while upgrading Sqlite from 3.6.7 to 3.7.9

[John Elrick]

I've outputted the results of _sqlite3_libversion and they have
verified that the OBJ files are self contained.  However, doing so has
resulted in one new piece of data.  The two versions are:

3.6.17
3.7.9

I don't know if that information makes any difference, but you should
have all potentially relevant information.

I did add an assertion which runs during initialization to ensure that
the version is as expected.  I have confirmed that both the shell
application and the full application concur on version numbers.

On Tue, Jan 10, 2012 at 10:35 AM, John Elrick  wrote:
> On Tue, Jan 10, 2012 at 10:14 AM, Richard Hipp  wrote:
>> On Tue, Jan 10, 2012 at 10:08 AM, John Elrick 
>> wrote:
>>
>>> On Tue, Jan 10, 2012 at 8:31 AM, Richard Hipp  wrote:
>>> > On Tue, Jan 10, 2012 at 8:14 AM, John Elrick >> >wrote:
>>> >
>>> >> I will have to get back to you on this.  While running tests against
>>> >> isolated queries, I noticed an unusual circumstance.  When I isolate
>>> >> the queries into a test program, running through our present
>>> >> libraries, 3.7.9 is roughly 4 times faster executing the exact same
>>> >> queries where it is running roughly 5 times slower in the context of
>>> >> the application.  As those queries do not execute in the same order, I
>>> >> suspect that page swapping and caching issues may be involved.  I'm
>>> >> proceeding on that assumption.
>>> >>
>>> >
>>> >
>>> > Are you using the same compile-time options when building your
>>> application
>>> > as were used when building the shell program?
>>>
>>> Yes.
>>>
>>
>> Have you run "SELECT sqlite_source_id();" to verify that your build is
>> really picking up the version of SQLite that you think it is?
>>
>> Do you have code like this in your application:
>>
>> assert( sqlite3_libversion_number()==SQLITE_VERSION_NUMBER );
>> assert( strcmp(sqlite3_sourceid(),SQLITE_SOURCE_ID)==0 );
>> assert( strcmp(sqlite3_libversion(),SQLITE_VERSION)==0 );
>>
>> To verify that your SQLite source code and "sqlite3.h" header file match?
>
> Nope.  I assume that the resulting .OBJ files are self contained.
> I'll run the tests you suggest just to make certain.



-- 
John Elrick
Fenestra Technologies
540-868-1377
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Problems encountered while upgrading Sqlite from 3.6.7 to 3.7.9

[John Elrick]

On Tue, Jan 10, 2012 at 10:14 AM, Richard Hipp  wrote:
> On Tue, Jan 10, 2012 at 10:08 AM, John Elrick wrote:
>
>> On Tue, Jan 10, 2012 at 8:31 AM, Richard Hipp  wrote:
>> > On Tue, Jan 10, 2012 at 8:14 AM, John Elrick > >wrote:
>> >
>> >> I will have to get back to you on this.  While running tests against
>> >> isolated queries, I noticed an unusual circumstance.  When I isolate
>> >> the queries into a test program, running through our present
>> >> libraries, 3.7.9 is roughly 4 times faster executing the exact same
>> >> queries where it is running roughly 5 times slower in the context of
>> >> the application.  As those queries do not execute in the same order, I
>> >> suspect that page swapping and caching issues may be involved.  I'm
>> >> proceeding on that assumption.
>> >>
>> >
>> >
>> > Are you using the same compile-time options when building your
>> application
>> > as were used when building the shell program?
>>
>> Yes.
>>
>
> Have you run "SELECT sqlite_source_id();" to verify that your build is
> really picking up the version of SQLite that you think it is?
>
> Do you have code like this in your application:
>
> assert( sqlite3_libversion_number()==SQLITE_VERSION_NUMBER );
> assert( strcmp(sqlite3_sourceid(),SQLITE_SOURCE_ID)==0 );
> assert( strcmp(sqlite3_libversion(),SQLITE_VERSION)==0 );
>
> To verify that your SQLite source code and "sqlite3.h" header file match?

Nope.  I assume that the resulting .OBJ files are self contained.
I'll run the tests you suggest just to make certain.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Problems encountered while upgrading Sqlite from 3.6.7 to 3.7.9

[Richard Hipp]

On Tue, Jan 10, 2012 at 10:08 AM, John Elrick wrote:

> On Tue, Jan 10, 2012 at 8:31 AM, Richard Hipp  wrote:
> > On Tue, Jan 10, 2012 at 8:14 AM, John Elrick  >wrote:
> >
> >> I will have to get back to you on this.  While running tests against
> >> isolated queries, I noticed an unusual circumstance.  When I isolate
> >> the queries into a test program, running through our present
> >> libraries, 3.7.9 is roughly 4 times faster executing the exact same
> >> queries where it is running roughly 5 times slower in the context of
> >> the application.  As those queries do not execute in the same order, I
> >> suspect that page swapping and caching issues may be involved.  I'm
> >> proceeding on that assumption.
> >>
> >
> >
> > Are you using the same compile-time options when building your
> application
> > as were used when building the shell program?
>
> Yes.
>

Have you run "SELECT sqlite_source_id();" to verify that your build is
really picking up the version of SQLite that you think it is?

Do you have code like this in your application:

assert( sqlite3_libversion_number()==SQLITE_VERSION_NUMBER );
assert( strcmp(sqlite3_sourceid(),SQLITE_SOURCE_ID)==0 );
assert( strcmp(sqlite3_libversion(),SQLITE_VERSION)==0 );

To verify that your SQLite source code and "sqlite3.h" header file match?




> ___
> 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

Re: [sqlite] Problems encountered while upgrading Sqlite from 3.6.7 to 3.7.9

[John Elrick]

On Tue, Jan 10, 2012 at 8:31 AM, Richard Hipp  wrote:
> On Tue, Jan 10, 2012 at 8:14 AM, John Elrick wrote:
>
>> I will have to get back to you on this.  While running tests against
>> isolated queries, I noticed an unusual circumstance.  When I isolate
>> the queries into a test program, running through our present
>> libraries, 3.7.9 is roughly 4 times faster executing the exact same
>> queries where it is running roughly 5 times slower in the context of
>> the application.  As those queries do not execute in the same order, I
>> suspect that page swapping and caching issues may be involved.  I'm
>> proceeding on that assumption.
>>
>
>
> Are you using the same compile-time options when building your application
> as were used when building the shell program?

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

Re: [sqlite] Problems encountered while upgrading Sqlite from 3.6.7 to 3.7.9

[Richard Hipp]

On Tue, Jan 10, 2012 at 8:14 AM, John Elrick wrote:

> I will have to get back to you on this.  While running tests against
> isolated queries, I noticed an unusual circumstance.  When I isolate
> the queries into a test program, running through our present
> libraries, 3.7.9 is roughly 4 times faster executing the exact same
> queries where it is running roughly 5 times slower in the context of
> the application.  As those queries do not execute in the same order, I
> suspect that page swapping and caching issues may be involved.  I'm
> proceeding on that assumption.
>


Are you using the same compile-time options when building your application
as were used when building the shell program?

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

Re: [sqlite] Problems encountered while upgrading Sqlite from 3.6.7 to 3.7.9

[John Elrick]

I will have to get back to you on this.  While running tests against
isolated queries, I noticed an unusual circumstance.  When I isolate
the queries into a test program, running through our present
libraries, 3.7.9 is roughly 4 times faster executing the exact same
queries where it is running roughly 5 times slower in the context of
the application.  As those queries do not execute in the same order, I
suspect that page swapping and caching issues may be involved.  I'm
proceeding on that assumption.

On Mon, Jan 9, 2012 at 12:11 PM, Richard Hipp  wrote:
> Thank you for sending the sample database file.
>
> Rather than forcing me to understand your log database, and fill in
> parameters with reasonable values, I wonder if you could simply email one
> or more queries (with the parameters filled in) which you find to be slower
> in 3.7.9 versus 3.6.7?
>
> On Mon, Jan 9, 2012 at 11:13 AM, Richard Hipp  wrote:
>
>> Please send private email to d...@sqlite.org.  CC to d...@sqlite.org and
>> j...@sqlite.org.
>>
>>
>> On Mon, Jan 9, 2012 at 11:09 AM, John Elrick wrote:
>>
>>> Sorry "The combined files are 2.4 MB zipped"
>>>
>>> On Mon, Jan 9, 2012 at 11:09 AM, John Elrick 
>>> wrote:
>>> > Richard,
>>> >
>>> > I have the information available for you.  Unfortunately, it appears
>>> > that I cannot actually reduce the case, however, the issues appear to
>>> > be fairly confined.
>>> >
>>> > The test database is 6 MB and I have a database showing the
>>> > performance tracking for each of the queries which is about 140KB.
>>> > The combined files are 2.4 MB.  How would you prefer I send this file
>>> > to you?
>>> >
>>> > On Fri, Jan 6, 2012 at 4:51 PM, John Elrick 
>>> wrote:
>>> >> Yes.  Look for it on Monday.  I'm about to leave for the day. The
>>> >> queries are automatically generated and will require some mining to
>>> >> get specific examples.  I should be able to reduce the range to a
>>> >> simple repeatable case but it may take a while.
>>> >>
>>> >> Thanks very much and have a great weekend.
>>> >>
>>> >> On Fri, Jan 6, 2012 at 4:42 PM, Richard Hipp  wrote:
>>> >>> Can you publish for us:
>>> >>>
>>> >>> (1) A sample database
>>> >>> (2) The specific queries that are causing your problems
>>> >>>
>>> >>> So that we can investigate further?
>>> >>>
>>> >>> On Fri, Jan 6, 2012 at 4:31 PM, John Elrick >> >wrote:
>>> >>>
>>>  Background
>>>  =
>>>  We have been using Sqlite3 in a project since 2007.   Because of the
>>>  release cycles involved, we normally upgrade infrequently.  We
>>>  recently upgraded from 3.6.7 to 3.7.9 and encountered two serious
>>>  issues. We need assistance in narrowing down the causes.
>>> 
>>>  We are developing using Delphi.  We are using the amalgamation and
>>> are
>>>  compiling it using the free Borland BCC complier.  The result is an
>>>  obj file which is statically linked into our application.  We have
>>>  been using this technique since we began using Sqlite.
>>> 
>>>  In the tests below, the strategy for changing between versions is to
>>>  change which obj file is statically linked into the application.  The
>>>  application was recompiled between each test.
>>> 
>>>  Issues Encountered
>>>  ===
>>> 
>>>  1.  After changing there is an order of magnitude performance
>>>  reduction.  Using GP Profile to monitor operations, here are the
>>>  results for identical application uses:
>>> 
>>>  Operation executes _sqlite3_step 49,152 times
>>> 
>>>  3.6.7 time 5.24 seconds
>>>  3.7.9 time 41.19 seconds
>>> 
>>>  2.  During testing we encountered a bug which had not been
>>> encountered
>>>  before.  Research by our CTO established that in 3.7.9 a row was
>>>  returned from this query which contained empty values.  In 3.6.7
>>> there
>>>  were no results from the query.
>>> 
>>>  Summation
>>>  =
>>>  Given how throughly Sqlite is tested I have difficulty believing
>>> there
>>>  is an actual bug in Sqlite.  I believe an interaction is occurring
>>>  which is a result of changes to the Sqlite code base which are
>>>  producing unexpected resulted when compiled into our application.  To
>>>  help resolve the problem, I would like to know if there is an
>>>  historical record of the amalgamations.  If so, I can carry out
>>>  testing to determine in what version the problem manifests.
>>> 
>>>  If anyone has any other recommendations I would be pleased to hear
>>>  them.  For the moment we are reverting to 3.6.7 as a temporary
>>>  solution.
>>> 
>>>  --
>>>  John Elrick
>>>  Fenestra Technologies
>>>  540-868-1377
>>>  ___
>>>  sqlite-users mailing list
>>>  sqlite-users@sqlite.org
>>>  http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>> 
>>> >>>
>>> >>>
>>> >>>
>>> >>> -

Re: [sqlite] insert if record not exists without primary key

[Mohd Radzi Ibrahim]

Hi,

I guess, if you could sort your input data by col1 and col2; going thru the
list and insert only when col1 and col2 are different from previous row,
should speed up the loading too.

Regards,
Radzi.

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Simon Slavin
Sent: Tuesday, 10 January, 2012 6:45 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] insert if record not exists without primary key


On 10 Jan 2012, at 10:34am, Durga D wrote:

> What about second approach.
> 
> create table if not exists emp (id integer primary key autoincrement, 
> col1 text, col2 text); //without unique.
> 
> I tried with insert or ignore into emp (col1, col2) values ('a', 'b');
> 
> I noticed, this is fast.

Here you are not asking SQLite to check for uniqueness every time you do an
INSERT.  So you are saving it a lot of work.  Of course, the result is a
database which may include a lot of duplicate rows.  Assuming you are
writing software, the simplest way to remove these duplicates would be to
execute

CREATE INDEX IF NOT EXISTS empCol1Col2 ON emp (col1,col2);

You can then write some software which does

SELECT id,col1,col2 FROM emp ORDER BY col1,col2;

and works its way down the list, deleting all rows where col1 and col2 are
the same as they were for the previous row.  (You can perhaps figure out a
single DELETE FROM command which will do all this, but I see no reason it
should take much less time than an efficiently written program.)

Of course this clean-up operation will probably take so much time you'd have
been better off with your first approach.

Simon.
___
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] insert if record not exists without primary key

[Simon Slavin]

On 10 Jan 2012, at 10:34am, Durga D wrote:

> What about second approach.
> 
> create table if not exists emp (id integer primary key autoincrement, col1
> text, col2 text); //without unique.
> 
> I tried with insert or ignore into emp (col1, col2) values ('a', 'b');
> 
> I noticed, this is fast.

Here you are not asking SQLite to check for uniqueness every time you do an 
INSERT.  So you are saving it a lot of work.  Of course, the result is a 
database which may include a lot of duplicate rows.  Assuming you are writing 
software, the simplest way to remove these duplicates would be to execute

CREATE INDEX IF NOT EXISTS empCol1Col2 ON emp (col1,col2);

You can then write some software which does

SELECT id,col1,col2 FROM emp ORDER BY col1,col2;

and works its way down the list, deleting all rows where col1 and col2 are the 
same as they were for the previous row.  (You can perhaps figure out a single 
DELETE FROM command which will do all this, but I see no reason it should take 
much less time than an efficiently written program.)

Of course this clean-up operation will probably take so much time you'd have 
been better off with your first approach.

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

Re: [sqlite] insert if record not exists without primary key

[Durga D]

Yes. I agree.

What about second approach.

create table if not exists emp (id integer primary key autoincrement, col1
text, col2 text); //without unique.

I tried with insert or ignore into emp (col1, col2) values ('a', 'b');

I noticed, this is fast.

just to know, what might be the reason?


On Tue, Jan 10, 2012 at 2:36 PM, Simon Slavin  wrote:

>
> On 10 Jan 2012, at 8:37am, Durga D wrote:
>
> > I noticed, with first approach, huge performance hit when database grows.
> > for ex: database has 500,000 records. now insert 25,000 within a
> > transaction. It takes lot of time when compared to insert 25,000 records
> > with empty database.
> >
> > approximately 15 times slower. because of unique(col1, col2).
>
> This makes some sense.  Every INSERT command involves SQLite looking
> through the records which are already in the table.  So a new insert when
> there are only 100 rows in the table is going to be pretty quick, but an
> insert when there are 500,000 rows in the table will involve lots of work.
>
> SQLite has to look up the data of the new record in the index it makes of
> (col1, col2) so it can find out whether the new row is a duplicate of an
> existing row.  So it has to do 25,000 searches of a balanced tree.  And if
> the tree has 500,000 records in it that means it's doing around ... hmm,
> it's over 3/4 of 1020, call it 765 ... tests for each search of the tree,
> 765 test for each INSERT command.
>
> So inserting 25,000 new rows when you already have 500,000 rows involves
> more than 25,000 * 765 string comparisons.  Call it 19 million string
> comparisons.  Plus, of course, all the other work involved in messing with
> the database.  Since you know how long your strings are you can get an idea
> of how much data SQLite is having to handle to do this.
>
> > I did with transactions for every 25,000 records.
>
> That is a reasonable way to do it.  People may be able to recommend
> PRAGMAs which will speed this up a bit but what you're doing really does
> involve a lot of work for the computer to do.
>
> Simon.
> ___
> 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] insert if record not exists without primary key

[Simon Slavin]

On 10 Jan 2012, at 8:37am, Durga D wrote:

> I noticed, with first approach, huge performance hit when database grows.
> for ex: database has 500,000 records. now insert 25,000 within a
> transaction. It takes lot of time when compared to insert 25,000 records
> with empty database.
> 
> approximately 15 times slower. because of unique(col1, col2).

This makes some sense.  Every INSERT command involves SQLite looking through 
the records which are already in the table.  So a new insert when there are 
only 100 rows in the table is going to be pretty quick, but an insert when 
there are 500,000 rows in the table will involve lots of work.

SQLite has to look up the data of the new record in the index it makes of 
(col1, col2) so it can find out whether the new row is a duplicate of an 
existing row.  So it has to do 25,000 searches of a balanced tree.  And if the 
tree has 500,000 records in it that means it's doing around ... hmm, it's over 
3/4 of 1020, call it 765 ... tests for each search of the tree, 765 test for 
each INSERT command.

So inserting 25,000 new rows when you already have 500,000 rows involves more 
than 25,000 * 765 string comparisons.  Call it 19 million string comparisons.  
Plus, of course, all the other work involved in messing with the database.  
Since you know how long your strings are you can get an idea of how much data 
SQLite is having to handle to do this.

> I did with transactions for every 25,000 records.

That is a reasonable way to do it.  People may be able to recommend PRAGMAs 
which will speed this up a bit but what you're doing really does involve a lot 
of work for the computer to do.

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

Re: [sqlite] insert if record not exists without primary key

[Durga D]

Simon,

Thank you.

>>create table if not exists emp(id integer primary key
>>autoincrement, col1 text, col2 text, unique (col1, col2));

I noticed, with first approach, huge performance hit when database grows.
for ex: database has 500,000 records. now insert 25,000 within a
transaction. It takes lot of time when compared to insert 25,000 records
with empty database.

approximately 15 times slower. because of unique(col1, col2).

I did with transactions for every 25,000 records.

Any suggestions?

Durga.

On Tue, Jan 10, 2012 at 1:00 PM, Simon Slavin  wrote:

>
> On 10 Jan 2012, at 7:23am, Durga D wrote:
>
> >   create table if not exists emp(id integer primary key autoincrement,
> > col1 text, col2 text, unique (col1, col2));
> >
> > here, col1 and col2 should be unique. I tried to insert 1000 records
> > with unique(col1, col2). It's very slow. So, I choosed id as primary key
> > (surrogate key) and without unique key.
> >
> > create table if not exists emp(id integer primary key autoincrement, col1
> > text, col2 text);
> >
> > I want to insert a record if not exists. How?
>
> You did it correctly the first time.  That is the correct way to prevent
> duplicates.
>
> Your test is unrealistic and will not reflect how fast the database will
> be in real life (are you really going to have your application insert
> 10,000,000 rows often ?) but if you really do want to do this, put all your
> insert commands into a transaction:
>
> BEGIN TRANSACTION;
> insert ...;
> insert ...;
> insert ...;
> ...;
> END TRANSACTION;
>
> Simon.
> ___
> 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