Re: [sqlite] Questions about sqlite3_result_text*

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Jean-Christophe Deschamps wrote:
> 
>> You are trying really hard to overthink things :-)
> 
> I simply found version c-1) in a widely spread extension and was 
> surprised by this way of doing the return, unduly complicated and 
> inefficient in my poor understanding, hence the question.

The best place to look for best practises and well written code is the
SQLite core implementation itself :-)

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iEYEARECAAYFAkreoKEACgkQmOOfHg372QTngQCgoz1NeQCORlTQQNqLKFs9Avow
E2wAoMMxSNILusw6Z7rHU8Z2LJaB2950
=I1ZW
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Questions about sqlite3_result_text*

[Jean-Christophe Deschamps]

>You are trying really hard to overthink things :-)

I simply found version c-1) in a widely spread extension and was 
surprised by this way of doing the return, unduly complicated and 
inefficient in my poor understanding, hence the question.



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

Re: [sqlite] Questions about sqlite3_result_text*

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Jean-Christophe Deschamps wrote:
> a) is it allowable for a scalar function to modify (in place) an input 
> argument

No.

> b) same question for unmodified arguments: is it possible to avoid 
> duplication
> of an argument if the function determines it is to be returned 
> verbatim?
> As I read it the function to use is sqlite3_result_value but it 
> duplicates.

Yes use sqlite3_result_value - see ifnullFunc in the sqlite source for an
example.  There is no issue with it duplicating (the code is not performance
critical, C is not garbage collected or reference counted etc).  If you
disagree with this then make a patched version and  run a benchmark showing
the difference.

> c) is it true that the following return methods are both acceptable, 
> with method
> 2 being better by not forcing an immediate dupplication of the 
> returned value
> and a byte count of the string?
> 
>1)  [result r obtained from sqlite3_malloc]
>...
>sqlite3_result_text(context, r, -1, SQLITE_TRANSIENT);
>sqlite3_free(r);
>  }
> 
>2)  [result r obtained from sqlite3_malloc]
>...
>sqlite3_result_text(context, r, effective_byte_length, 
> sqlite3_free);
>  }

Correct 1 requires a copy while 2 takes ownership of the string.  You are
trying really hard to overthink things :-)

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iEYEARECAAYFAkrenJEACgkQmOOfHg372QRj/ACfVAQG1HDfFLtL9JcHklyU0K82
2OkAoLLDdaTAxdgeWYfxM4SzDNTtNRIB
=B7Vq
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] help in understanding sqlite_master output

[P Kishor]

On Tue, Oct 20, 2009 at 11:50 PM, Andy  wrote:
> I'm new to sqlite. Just started on a project that comes with a lot of 
> pre-created tables.
>
> Trying to understand the data structure, I did:
>
>>sqlite3 dev.db
>>select * from sqlite_master;
>
> Some of the output of the above query is a bit confusing to me. I checked out 
> the doc in http://www.sqlite.org/sqlite.html but am still unclear.
>
> A typical output I got is:
>
> table|auth_permission|auth_permission|2|CREATE TABLE "auth_permission" (
>    "id" integer NOT NULL PRIMARY KEY,
>    "name" varchar(50) NOT NULL,
>    "content_type_id" integer NOT NULL,
>    "codename" varchar(100) NOT NULL,
>    UNIQUE ("content_type_id", "codename")
> )
> index|sqlite_autoindex_auth_permission_1|auth_permission|3|
> 
> index|auth_permission_content_type_id|auth_permission|180|CREATE INDEX 
> "auth_permission_content_type_id" ON "auth_permission" ("content_type_id")
>
>
>
> So I got a table "auth_permission" that has 4 columns: id, name, 
> content_type_id, codename.
>
> In the first line:
> table|auth_permission|auth_permission|2|CREATE TABLE "auth_permission" (
> why is "auth_permission" repeated twice?
> Also what does the number "2" before "CREATE TABLE" mean?
>
> And for the line:
> index|sqlite_autoindex_auth_permission_1|auth_permission|3|
> what does "sqlite_autoindex_auth_permission_1"?
> and what about the last number "3" - what does it mean?
>
> And for the 2nd index line:
> index|auth_permission_content_type_id|auth_permission|180|CREATE INDEX 
> "auth_permission_content_type_id" ON "auth_permission" ("content_type_id")
> what does "180" mean?
>

At the sqlite prompt, type .mode columns  then type .h on 

sqlite> .m col
sqlite> .h on

Then SELECT * FROM sqlite_master and your questions will be answered.


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



-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] help in understanding sqlite_master output

[Andy]

I'm new to sqlite. Just started on a project that comes with a lot of 
pre-created tables.

Trying to understand the data structure, I did:

>sqlite3 dev.db
>select * from sqlite_master;

Some of the output of the above query is a bit confusing to me. I checked out 
the doc in http://www.sqlite.org/sqlite.html but am still unclear.

A typical output I got is:

table|auth_permission|auth_permission|2|CREATE TABLE "auth_permission" (
"id" integer NOT NULL PRIMARY KEY,
"name" varchar(50) NOT NULL,
"content_type_id" integer NOT NULL,
"codename" varchar(100) NOT NULL,
UNIQUE ("content_type_id", "codename")
)
index|sqlite_autoindex_auth_permission_1|auth_permission|3|

index|auth_permission_content_type_id|auth_permission|180|CREATE INDEX 
"auth_permission_content_type_id" ON "auth_permission" ("content_type_id")



So I got a table "auth_permission" that has 4 columns: id, name, 
content_type_id, codename.

In the first line:
table|auth_permission|auth_permission|2|CREATE TABLE "auth_permission" (
why is "auth_permission" repeated twice?
Also what does the number "2" before "CREATE TABLE" mean?

And for the line:
index|sqlite_autoindex_auth_permission_1|auth_permission|3|
what does "sqlite_autoindex_auth_permission_1"?
and what about the last number "3" - what does it mean?

And for the 2nd index line:
index|auth_permission_content_type_id|auth_permission|180|CREATE INDEX 
"auth_permission_content_type_id" ON "auth_permission" ("content_type_id")
what does "180" mean? 

Thanks for your help.


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

Re: [sqlite] commit time

[John Crenshaw]

Sounds like a great candidate for shared cache with PRAGMA
read_uncommitted = true.

If other threads may also need a write lock on that table, you should
handle SQLITE_LOCKED by incrementing a waiter count and calling
sqlite3_unlock_notify. The thread doing the inserting can check to see
if anybody is waiting (blocked) and yield by committing the current
transaction and waiting for the blocked thread to unblock. Be aware, you
should also close any open cursors before yielding, because open cursors
will prevent write locks and you'll waste time yielding for nothing.

John

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Tom Broadbent
Sent: Tuesday, October 20, 2009 8:05 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] commit time

i have a simple join table containing two ids from two other tables.  i
have an index on each of the ids in the join table.

CREATE TABLE ContentWordItem (word_id INT, item_id INT);

CREATE INDEX idx_ContentWordItem_word ON ContentWordItem(word_id);
// index to perform fast queries by word_id

CREATE INDEX idx_ContentWordItem_item ON ContentWordItem(item_id);
// index to perform fast deletes by item_id

i have a large amount of data to insert into this join table on a slow
embedded device.  i need to avoid locking this join table for more than
a second or two at a time so that i can make queries on this table.

so here's the question:  how do i insert small chunks of data into this
table w/o taking a hit each time i commit?

what i'm doing is:

* read a chunk of data from flat data file into vector of id
pairs

* begin transaction

* loop thru vector of id pairs binding and inserting

* commit transaction

* repeat until data is exhausted

i'm seeing that the reading, binding, and inserting is very fast (300
ms) but the commit is taking upwards of 3 seconds.  when i increase my
chunk size by a factor of 10 the insert doesn't appear to take 10x
longer but the commit still takes upwards of 3 seconds.  the point is
that the commit hit appears to be much greater than the insert hit but
doesn't appear to scale directly.

it appears that the commit is updating the indexes and taking a long
time.  is this a correct evaluation?

it also appears that the commit takes longer as the size of the table
grows (i.e. the index is getting bigger).  is this expected?

what i'm worried about is that by reducing the chunk size (to avoid
locking the db for a long time) i add a significant amount of time to
the insert process because the commits are costing several seconds.
however, locking the db for a long time is not desirable.

i'm also concerned about the commit time increasing over time as the
amount of data in the table increases.

is there a better approach?

thanks
tom

__
This email has been scanned by the MessageLabs Email Security System.
For more information please visit http://www.messagelabs.com/email 
__
___
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] Questions about sqlite3_result_text*

[Jean-Christophe Deschamps]

Hi,

The following details about text/blobs returns from scalar functions 
are unclear to me and I prefer doing things right.

a) is it allowable for a scalar function to modify (in place) an input 
argument
and return a pointer to modified argument with 
sqlite3_result_text?  If yes,
what should be the last parameter of sqlite3_result_text?
(Surely, SQLITE_TRANSIENT would work)

b) same question for unmodified arguments: is it possible to avoid 
duplication
of an argument if the function determines it is to be returned 
verbatim?
As I read it the function to use is sqlite3_result_value but it 
duplicates.

c) is it true that the following return methods are both acceptable, 
with method
2 being better by not forcing an immediate dupplication of the 
returned value
and a byte count of the string?

   1)  [result r obtained from sqlite3_malloc]
   ...
   sqlite3_result_text(context, r, -1, SQLITE_TRANSIENT);
   sqlite3_free(r);
 }

   2)  [result r obtained from sqlite3_malloc]
   ...
   sqlite3_result_text(context, r, effective_byte_length, 
sqlite3_free);
 }


I currently avoid a) & b) and use c-2).  Is this the best way?

TIA



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

[sqlite] commit time

[Tom Broadbent]

i have a simple join table containing two ids from two other tables.  i have an 
index on each of the ids in the join table.

CREATE TABLE ContentWordItem (word_id INT, item_id INT);

CREATE INDEX idx_ContentWordItem_word ON ContentWordItem(word_id);  // 
index to perform fast queries by word_id

CREATE INDEX idx_ContentWordItem_item ON ContentWordItem(item_id);  // 
index to perform fast deletes by item_id

i have a large amount of data to insert into this join table on a slow embedded 
device.  i need to avoid locking this join table for more than a second or two 
at a time so that i can make queries on this table.

so here's the question:  how do i insert small chunks of data into this table 
w/o taking a hit each time i commit?

what i'm doing is:

* read a chunk of data from flat data file into vector of id pairs

* begin transaction

* loop thru vector of id pairs binding and inserting

* commit transaction

* repeat until data is exhausted

i'm seeing that the reading, binding, and inserting is very fast (300 ms) but 
the commit is taking upwards of 3 seconds.  when i increase my chunk size by a 
factor of 10 the insert doesn't appear to take 10x longer but the commit still 
takes upwards of 3 seconds.  the point is that the commit hit appears to be 
much greater than the insert hit but doesn't appear to scale directly.

it appears that the commit is updating the indexes and taking a long time.  is 
this a correct evaluation?

it also appears that the commit takes longer as the size of the table grows 
(i.e. the index is getting bigger).  is this expected?

what i'm worried about is that by reducing the chunk size (to avoid locking the 
db for a long time) i add a significant amount of time to the insert process 
because the commits are costing several seconds.  however, locking the db for a 
long time is not desirable.

i'm also concerned about the commit time increasing over time as the amount of 
data in the table increases.

is there a better approach?

thanks
tom

__
This email has been scanned by the MessageLabs Email Security System.
For more information please visit http://www.messagelabs.com/email 
__
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Crash on freeing mutex in sqlite3.c

[Keith Roberts]

On Tue, 20 Oct 2009, Pavel Ivanov wrote:

> To: General Discussion of SQLite Database 
> From: Pavel Ivanov 
> Subject: Re: [sqlite] Crash on freeing mutex in sqlite3.c
> 
> When I say "path ... completely irrelevant" it means path doesn't
> cause the error and it doesn't matter at all what path you're giving -
> you can pass an empty path and the error will still persist.
>
> ddd or gdb or whatever debugger you use - it doesn't show you memory
> access errors in your program (I heard totalview can do something like
> that but I didn't try that). But if you run your program under
> valgrind it will show you those types of errors. So go ahead and do
> it.
>
> Pavel

Eclipse and the CDT plugin allows you to look into memory 
locations in C/C++ if that helps. Not quite the same as 
valgrind does, but may be of some use.

Check out the fourth one down on this page, for the C 
development tools:

http://www.eclipse.org/downloads/

Kind Regards,

Keith Roberts

-
Websites:
http://www.php-debuggers.net
http://www.karsites.net
http://www.raised-from-the-dead.org.uk

All email addresses are challenge-response protected with
TMDA [http://tmda.net]
-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Crash on freeing mutex in sqlite3.c

[Kavita Raghunathan]

Pavel, You're the man! If you are ever in Dallas let me know, I owe you lunch.

Found my bug using valgrind and it had nothing to do with the mutex or sqlite.

Kavita

- Original Message -
From: "Pavel Ivanov" 
To: "General Discussion of SQLite Database" 
Sent: Tuesday, October 20, 2009 3:24:59 PM GMT -06:00 US/Canada Central
Subject: Re: [sqlite] Crash on freeing mutex in sqlite3.c

When I say "path ... completely irrelevant" it means path doesn't
cause the error and it doesn't matter at all what path you're giving -
you can pass an empty path and the error will still persist.

ddd or gdb or whatever debugger you use - it doesn't show you memory
access errors in your program (I heard totalview can do something like
that but I didn't try that). But if you run your program under
valgrind it will show you those types of errors. So go ahead and do
it.

Pavel

On Tue, Oct 20, 2009 at 4:18 PM, Kavita Raghunathan
 wrote:
> I'm using ddd, its the GUI version of gdb.
>
> Are you saying that the file pathname is wrong ? When you say
>  "path give to sqlite is completely irrelevant" do you mean "db_filename"?
> I printed this path out using the debugger and it looks correct.
>
> And you are correct, it does die in sqlite3_initialize while trying to free
> the mutex pointer but the allocation of the pointer succeeds. So still lost.
>
> - Original Message -
> From: "Pavel Ivanov" 
> To: "General Discussion of SQLite Database" 
> Sent: Tuesday, October 20, 2009 3:06:01 PM GMT -06:00 US/Canada Central
> Subject: Re: [sqlite] Crash on freeing mutex in sqlite3.c
>
> The only place I see this line in SQLite's code is
> sqlite3_initialize(). So your path given to sqlite3_open_v2() is
> completely irrelevant and FTR no other process "can have a mutex" so
> that it will lead to crash of your process. The problem is that your
> application corrupts memory. Run it under valgrind and see where the
> problem is.
>
> Pavel
>
> On Tue, Oct 20, 2009 at 3:25 PM, Kavita Raghunathan
>  wrote:
>> Although i have successfully opened the database several times,
>> today I'm seeing crashes on this line when I use sqlite3_open_v2:
>>
>> sqlite3_mutex_free(sqlite3GlobalConfig.pInitMutex);
>>
>> Any ideas what I could be doing wrong to cause this crash ?
>>
>> Here is the exact way I'm using this C wrapper:
>> sqlite3_open_v2(db_filename, , 
>> SQLITE_OPEN_READWRITE|SQLITE_OPEN_FULLMUTEX, NULL)
>>
>>
>> Things I've already tried:
>> 1) Killed any processes that are still running that might have the mutex
>> 2) Checked to see if I have a valid file name and path, I'm using /tmp/abc.db
>>
>> Thanks,
>> Kavita
>>
>> ___
>> 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-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-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Why FTS3 has the limitations it does

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Wanadoo Hartwig wrote:
> It seems to be that ticket 3950 was also closed
> (I actually opened this ticket using version 3.6.16). The reason for  
> closure was that the bug seems to have disappeared 

It is actually closed.  I'm the one who has been going through all the old
tickets and transferring the ones that still seem to be well defined
existing bugs into the new ticket system and closing the rest directing
further discussion to this mailing list to refine in detail what the issue is.

  http://www.sqlite.org/src/wiki?name=Bug+Reports

This also means don't blame the SQLite dev team for the rather terse
comments in some of them :-)  I originally thought there weren't too many
but only worked out yesterday that the report limits to 100.  There would
appear to be about 600 more to process in addition to however many hundred I
already did!

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iEYEARECAAYFAkreHz8ACgkQmOOfHg372QQ0mgCeOaJaKlhvGU/+Czu9a/n4Ltik
+ZsAoMKE1gqx13cbu9lfttR6KcYVqsZQ
=1O+q
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Crash on freeing mutex in sqlite3.c

[Kavita Raghunathan]

ok, thanks, will try that.

- Original Message -
From: "Pavel Ivanov" 
To: "General Discussion of SQLite Database" 
Sent: Tuesday, October 20, 2009 3:24:59 PM GMT -06:00 US/Canada Central
Subject: Re: [sqlite] Crash on freeing mutex in sqlite3.c

When I say "path ... completely irrelevant" it means path doesn't
cause the error and it doesn't matter at all what path you're giving -
you can pass an empty path and the error will still persist.

ddd or gdb or whatever debugger you use - it doesn't show you memory
access errors in your program (I heard totalview can do something like
that but I didn't try that). But if you run your program under
valgrind it will show you those types of errors. So go ahead and do
it.

Pavel

On Tue, Oct 20, 2009 at 4:18 PM, Kavita Raghunathan
 wrote:
> I'm using ddd, its the GUI version of gdb.
>
> Are you saying that the file pathname is wrong ? When you say
>  "path give to sqlite is completely irrelevant" do you mean "db_filename"?
> I printed this path out using the debugger and it looks correct.
>
> And you are correct, it does die in sqlite3_initialize while trying to free
> the mutex pointer but the allocation of the pointer succeeds. So still lost.
>
> - Original Message -
> From: "Pavel Ivanov" 
> To: "General Discussion of SQLite Database" 
> Sent: Tuesday, October 20, 2009 3:06:01 PM GMT -06:00 US/Canada Central
> Subject: Re: [sqlite] Crash on freeing mutex in sqlite3.c
>
> The only place I see this line in SQLite's code is
> sqlite3_initialize(). So your path given to sqlite3_open_v2() is
> completely irrelevant and FTR no other process "can have a mutex" so
> that it will lead to crash of your process. The problem is that your
> application corrupts memory. Run it under valgrind and see where the
> problem is.
>
> Pavel
>
> On Tue, Oct 20, 2009 at 3:25 PM, Kavita Raghunathan
>  wrote:
>> Although i have successfully opened the database several times,
>> today I'm seeing crashes on this line when I use sqlite3_open_v2:
>>
>> sqlite3_mutex_free(sqlite3GlobalConfig.pInitMutex);
>>
>> Any ideas what I could be doing wrong to cause this crash ?
>>
>> Here is the exact way I'm using this C wrapper:
>> sqlite3_open_v2(db_filename, , 
>> SQLITE_OPEN_READWRITE|SQLITE_OPEN_FULLMUTEX, NULL)
>>
>>
>> Things I've already tried:
>> 1) Killed any processes that are still running that might have the mutex
>> 2) Checked to see if I have a valid file name and path, I'm using /tmp/abc.db
>>
>> Thanks,
>> Kavita
>>
>> ___
>> 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-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-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Crash on freeing mutex in sqlite3.c

[Pavel Ivanov]

When I say "path ... completely irrelevant" it means path doesn't
cause the error and it doesn't matter at all what path you're giving -
you can pass an empty path and the error will still persist.

ddd or gdb or whatever debugger you use - it doesn't show you memory
access errors in your program (I heard totalview can do something like
that but I didn't try that). But if you run your program under
valgrind it will show you those types of errors. So go ahead and do
it.

Pavel

On Tue, Oct 20, 2009 at 4:18 PM, Kavita Raghunathan
 wrote:
> I'm using ddd, its the GUI version of gdb.
>
> Are you saying that the file pathname is wrong ? When you say
>  "path give to sqlite is completely irrelevant" do you mean "db_filename"?
> I printed this path out using the debugger and it looks correct.
>
> And you are correct, it does die in sqlite3_initialize while trying to free
> the mutex pointer but the allocation of the pointer succeeds. So still lost.
>
> - Original Message -
> From: "Pavel Ivanov" 
> To: "General Discussion of SQLite Database" 
> Sent: Tuesday, October 20, 2009 3:06:01 PM GMT -06:00 US/Canada Central
> Subject: Re: [sqlite] Crash on freeing mutex in sqlite3.c
>
> The only place I see this line in SQLite's code is
> sqlite3_initialize(). So your path given to sqlite3_open_v2() is
> completely irrelevant and FTR no other process "can have a mutex" so
> that it will lead to crash of your process. The problem is that your
> application corrupts memory. Run it under valgrind and see where the
> problem is.
>
> Pavel
>
> On Tue, Oct 20, 2009 at 3:25 PM, Kavita Raghunathan
>  wrote:
>> Although i have successfully opened the database several times,
>> today I'm seeing crashes on this line when I use sqlite3_open_v2:
>>
>> sqlite3_mutex_free(sqlite3GlobalConfig.pInitMutex);
>>
>> Any ideas what I could be doing wrong to cause this crash ?
>>
>> Here is the exact way I'm using this C wrapper:
>> sqlite3_open_v2(db_filename, , 
>> SQLITE_OPEN_READWRITE|SQLITE_OPEN_FULLMUTEX, NULL)
>>
>>
>> Things I've already tried:
>> 1) Killed any processes that are still running that might have the mutex
>> 2) Checked to see if I have a valid file name and path, I'm using /tmp/abc.db
>>
>> Thanks,
>> Kavita
>>
>> ___
>> 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-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] Why FTS3 has the limitations it does

[Wanadoo Hartwig]

Hi Scott,

thanks for the reply. It seems to be that ticket 3950 was also closed  
(I actually opened this ticket using version 3.6.16). The reason for  
closure was that the bug seems to have disappeared in version 3.6.19.  
I have not checked it by myself as I have not installed 3.6.19, yet.

I will report the outcome.

Hartwig

Am 19.10.2009 um 18:27 schrieb Scott Hess:

> Here's a long-ago thread on this:
>   http://www.mail-archive.com/sqlite-users@sqlite.org/msg30540.html
> Looks like it hasn't been addressed, and I've yet to come up for air  
> on it.
>
> There's a ticket out there which looks like the same thing:
>  http://www.sqlite.org/cvstrac/tktview?tn=3338
> which is closed, but it sounds like if you put up a simple repro-case
> you might get a re-hearing on it.
>
> -scott
>
>
> On Mon, Oct 19, 2009 at 9:13 AM, Wanadoo Hartwig
>  wrote:
>>
>> Am 18.10.2009 um 18:55 schrieb Roger Binns:
>>
>>> Wanadoo Hartwig wrote:
 Slightly different question but related to FTS3. Does anybody know
 why
 this fails using FTS3?
>>>
>>> It isn't failing.  Behind the scenes FTS3 is implemented using 3  
>>> other
>>> tables (try .dump to see).  You are indeed seeing the last inserted
>>> rowid.
>>>
>>
>> As the FTS related tables are modified by a trigger the last rowid
>> should still be the one of the table "triggering the trigger" and not
>> of any the tables being modified by the trigger. Isn't this the
>> general idea of a trigger?
>>
>> Hartwig
>>
>>> Roger
>>> ___
>>> 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-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] Crash on freeing mutex in sqlite3.c

[Kavita Raghunathan]

I'm using ddd, its the GUI version of gdb.

Are you saying that the file pathname is wrong ? When you say
 "path give to sqlite is completely irrelevant" do you mean "db_filename"?
I printed this path out using the debugger and it looks correct.

And you are correct, it does die in sqlite3_initialize while trying to free 
the mutex pointer but the allocation of the pointer succeeds. So still lost.

- Original Message -
From: "Pavel Ivanov" 
To: "General Discussion of SQLite Database" 
Sent: Tuesday, October 20, 2009 3:06:01 PM GMT -06:00 US/Canada Central
Subject: Re: [sqlite] Crash on freeing mutex in sqlite3.c

The only place I see this line in SQLite's code is
sqlite3_initialize(). So your path given to sqlite3_open_v2() is
completely irrelevant and FTR no other process "can have a mutex" so
that it will lead to crash of your process. The problem is that your
application corrupts memory. Run it under valgrind and see where the
problem is.

Pavel

On Tue, Oct 20, 2009 at 3:25 PM, Kavita Raghunathan
 wrote:
> Although i have successfully opened the database several times,
> today I'm seeing crashes on this line when I use sqlite3_open_v2:
>
> sqlite3_mutex_free(sqlite3GlobalConfig.pInitMutex);
>
> Any ideas what I could be doing wrong to cause this crash ?
>
> Here is the exact way I'm using this C wrapper:
> sqlite3_open_v2(db_filename, , 
> SQLITE_OPEN_READWRITE|SQLITE_OPEN_FULLMUTEX, NULL)
>
>
> Things I've already tried:
> 1) Killed any processes that are still running that might have the mutex
> 2) Checked to see if I have a valid file name and path, I'm using /tmp/abc.db
>
> Thanks,
> Kavita
>
> ___
> 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-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Crash on freeing mutex in sqlite3.c

[Pavel Ivanov]

The only place I see this line in SQLite's code is
sqlite3_initialize(). So your path given to sqlite3_open_v2() is
completely irrelevant and FTR no other process "can have a mutex" so
that it will lead to crash of your process. The problem is that your
application corrupts memory. Run it under valgrind and see where the
problem is.

Pavel

On Tue, Oct 20, 2009 at 3:25 PM, Kavita Raghunathan
 wrote:
> Although i have successfully opened the database several times,
> today I'm seeing crashes on this line when I use sqlite3_open_v2:
>
> sqlite3_mutex_free(sqlite3GlobalConfig.pInitMutex);
>
> Any ideas what I could be doing wrong to cause this crash ?
>
> Here is the exact way I'm using this C wrapper:
> sqlite3_open_v2(db_filename, , 
> SQLITE_OPEN_READWRITE|SQLITE_OPEN_FULLMUTEX, NULL)
>
>
> Things I've already tried:
> 1) Killed any processes that are still running that might have the mutex
> 2) Checked to see if I have a valid file name and path, I'm using /tmp/abc.db
>
> Thanks,
> Kavita
>
> ___
> 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] Crash on freeing mutex in sqlite3.c

[Kavita Raghunathan]

Although i have successfully opened the database several times,
today I'm seeing crashes on this line when I use sqlite3_open_v2:

sqlite3_mutex_free(sqlite3GlobalConfig.pInitMutex);

Any ideas what I could be doing wrong to cause this crash ?

Here is the exact way I'm using this C wrapper:
sqlite3_open_v2(db_filename, , 
SQLITE_OPEN_READWRITE|SQLITE_OPEN_FULLMUTEX, NULL)


Things I've already tried:
1) Killed any processes that are still running that might have the mutex
2) Checked to see if I have a valid file name and path, I'm using /tmp/abc.db

Thanks,
Kavita

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

Re: [sqlite] index for a group by

[Pavel Ivanov]

I want to notice, John, that my words are in context "I have table
with a lot of data, I want to create a particular index on it, how can
I do it quickly". In this context only your 5 bullet is applicable, I
admit I've forgot about that. And I don't understand how can one split
creating of index across several transactions.

Pavel

On Tue, Oct 20, 2009 at 2:15 PM, John Crenshaw  wrote:
>> Nothing in this process can be sped up.
>
> Actually, that isn't entirely true. While it always requires a full data
> scan, Some things can make the indexing part of the process faster.
> Since indexing is done basically using a comparative sort, anything that
> would speed up the sort, will speed up the indexing.
> 1. It is faster to sort 1000 data points, than to insert 1000 datapoints
> into a constantly sorted list. Creating the index after all inserts is
> faster than creating the index, then inserting.
> 2. If possible, avoid indexes on long data strings, since the compares
> can be time consuming.
> 3. If you have a field that stores one of several strings (as an "enum")
> consider using integers instead. Integers have lower overhead, and can
> be compared (and sorted) more quickly than strings.
> 4. If you are feeling really gutsy, you could mod the code and implement
> a radix sort or something similar for integer values. I'm not really
> recommending this, just saying, inserts and lookups in a radix index are
> faster than a btree.
> 5. Make sure the memory cache is large enough for the sort. Writing data
> to disk is very costly, compared to sorting in memory. Default is 2000
> pages (2MB) worth of btree data. If you are about to build an index that
> will require more btree than that, increase the size, or split across
> several transactions.
>
> John
>
> -Original Message-
> From: sqlite-users-boun...@sqlite.org
> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Pavel Ivanov
> Sent: Tuesday, October 20, 2009 7:35 AM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] index for a group by
>
>> please could you let me know which index could be better or faster?
>
> For this particular query index on (t,a,b,c) or even on (t,a,b,c,d)
> would be better and cause the query to execute faster (of course if by
> conditions t>x1 and t table).
>
>> also do you know by chance how to speed up the index creation?
>
> There's no way to do that. SQLite have to scan the whole table, read
> data from all rows and put necessary information into the index.
> Nothing in this process can be sped up.
>
> Pavel
>
> On Mon, Oct 19, 2009 at 5:50 PM, Sylvain Pointeau
>  wrote:
>> hello,
>> I have a table T (a,b,c,d,t)
>> where c is a value
>> a,b,c some dimensions
>> and t the time
>>
>> I need to make a subset with a "group by"
>> like
>>
>> select a,b,c,sum(d)
>> from T
>> where t>x1 and t> group by a,b,c
>>
>> I created an index on a,b,c
>> but this table is large and the index creation is time consuming (few
> hours)
>>
>> please could you let me know which index could be better or faster?
>> also do you know by chance how to speed up the index creation?
>>
>> Best regards,
>> Sylvain
>> ___
>> 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-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] PRAGMA scope

[John Crenshaw]

Makes sense. I figured the master pages would have still been created at this 
point, but I suppose that is simple enough to deal with.

John

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of P Kishor
Sent: Tuesday, October 20, 2009 2:37 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] PRAGMA scope

On Tue, Oct 20, 2009 at 1:33 PM, John Crenshaw  wrote:
> database = the physical file itself
> database connection = a specific reference to the database, obtained
> using sqlite3_open
>
> page_size has to be set before the database is created (though I'm NOT
> entirely sure how you would execute the pragma before creating the
> database, since executing the pragma should require a connection, and a
> connection should require a database. Hmm...)

before any table is created...


>
> -Original Message-
> From: sqlite-users-boun...@sqlite.org
> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Doug
> Sent: Tuesday, October 20, 2009 2:28 PM
> To: 'General Discussion of SQLite Database'
> Subject: [sqlite] PRAGMA scope
>
> I'm reading about the different PRAGMA operations. Cache_size mentions
> that
> it is per-database connection.  Page_size says it is per database (must
> be
> used before the database is created), which sounds like per-connection
> (ie
> if I create two databases, I'm guessing I need to set the page_size
> after
> calling sqlite3_open each time).
>
>
>
> Temp_store and synchronous don't make any mention of files or
> connections.
> Can/should it be assumed that they are global to the SQLite library?
>
>
>
> Thanks
>
> Doug
>
>
>
> ___
> 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
>



-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
Sent from Madison, WI, United States
___
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] PRAGMA scope

[P Kishor]

On Tue, Oct 20, 2009 at 1:33 PM, John Crenshaw  wrote:
> database = the physical file itself
> database connection = a specific reference to the database, obtained
> using sqlite3_open
>
> page_size has to be set before the database is created (though I'm NOT
> entirely sure how you would execute the pragma before creating the
> database, since executing the pragma should require a connection, and a
> connection should require a database. Hmm...)

before any table is created...


>
> -Original Message-
> From: sqlite-users-boun...@sqlite.org
> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Doug
> Sent: Tuesday, October 20, 2009 2:28 PM
> To: 'General Discussion of SQLite Database'
> Subject: [sqlite] PRAGMA scope
>
> I'm reading about the different PRAGMA operations. Cache_size mentions
> that
> it is per-database connection.  Page_size says it is per database (must
> be
> used before the database is created), which sounds like per-connection
> (ie
> if I create two databases, I'm guessing I need to set the page_size
> after
> calling sqlite3_open each time).
>
>
>
> Temp_store and synchronous don't make any mention of files or
> connections.
> Can/should it be assumed that they are global to the SQLite library?
>
>
>
> Thanks
>
> Doug
>
>
>
> ___
> 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
>



-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
Sent from Madison, WI, United States
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] PRAGMA scope

[John Crenshaw]

database = the physical file itself
database connection = a specific reference to the database, obtained
using sqlite3_open

page_size has to be set before the database is created (though I'm NOT
entirely sure how you would execute the pragma before creating the
database, since executing the pragma should require a connection, and a
connection should require a database. Hmm...)

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Doug
Sent: Tuesday, October 20, 2009 2:28 PM
To: 'General Discussion of SQLite Database'
Subject: [sqlite] PRAGMA scope

I'm reading about the different PRAGMA operations. Cache_size mentions
that
it is per-database connection.  Page_size says it is per database (must
be
used before the database is created), which sounds like per-connection
(ie
if I create two databases, I'm guessing I need to set the page_size
after
calling sqlite3_open each time).

 

Temp_store and synchronous don't make any mention of files or
connections.
Can/should it be assumed that they are global to the SQLite library?

 

Thanks

Doug

 

___
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] PRAGMA scope

[Doug]

I'm reading about the different PRAGMA operations. Cache_size mentions that
it is per-database connection.  Page_size says it is per database (must be
used before the database is created), which sounds like per-connection (ie
if I create two databases, I'm guessing I need to set the page_size after
calling sqlite3_open each time).

 

Temp_store and synchronous don't make any mention of files or connections.
Can/should it be assumed that they are global to the SQLite library?

 

Thanks

Doug

 

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

Re: [sqlite] index for a group by

[John Crenshaw]

> Nothing in this process can be sped up.

Actually, that isn't entirely true. While it always requires a full data
scan, Some things can make the indexing part of the process faster.
Since indexing is done basically using a comparative sort, anything that
would speed up the sort, will speed up the indexing.
1. It is faster to sort 1000 data points, than to insert 1000 datapoints
into a constantly sorted list. Creating the index after all inserts is
faster than creating the index, then inserting.
2. If possible, avoid indexes on long data strings, since the compares
can be time consuming.
3. If you have a field that stores one of several strings (as an "enum")
consider using integers instead. Integers have lower overhead, and can
be compared (and sorted) more quickly than strings.
4. If you are feeling really gutsy, you could mod the code and implement
a radix sort or something similar for integer values. I'm not really
recommending this, just saying, inserts and lookups in a radix index are
faster than a btree.
5. Make sure the memory cache is large enough for the sort. Writing data
to disk is very costly, compared to sorting in memory. Default is 2000
pages (2MB) worth of btree data. If you are about to build an index that
will require more btree than that, increase the size, or split across
several transactions.

John

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Pavel Ivanov
Sent: Tuesday, October 20, 2009 7:35 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] index for a group by

> please could you let me know which index could be better or faster?

For this particular query index on (t,a,b,c) or even on (t,a,b,c,d)
would be better and cause the query to execute faster (of course if by
conditions t>x1 and t also do you know by chance how to speed up the index creation?

There's no way to do that. SQLite have to scan the whole table, read
data from all rows and put necessary information into the index.
Nothing in this process can be sped up.

Pavel

On Mon, Oct 19, 2009 at 5:50 PM, Sylvain Pointeau
 wrote:
> hello,
> I have a table T (a,b,c,d,t)
> where c is a value
> a,b,c some dimensions
> and t the time
>
> I need to make a subset with a "group by"
> like
>
> select a,b,c,sum(d)
> from T
> where t>x1 and t group by a,b,c
>
> I created an index on a,b,c
> but this table is large and the index creation is time consuming (few
hours)
>
> please could you let me know which index could be better or faster?
> also do you know by chance how to speed up the index creation?
>
> Best regards,
> Sylvain
> ___
> 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-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Infinity

[Michael Chen]

thanks for the notes. After change "double infinity" to
std::numeric_limits(), here is the output:
1.10e+00
inf
-inf
3.30e+00

it is clear that the sqlite3 does store "infinity", and select statement can
retrieve it as well. The "printf" of C++ prints "infinity" as literal "inf".

Michael Chen



On Tue, Oct 20, 2009 at 12:20 AM, John Crenshaw wrote:

> Sorry, I think I gave you slightly buggy instructions. I just realized
> that max() should be the max true value capable of being stored, which
> should be less than the infinity value. std::numeric_limits
> provides another function named infinity() for getting positive
> infinity. I believe this value will be different than max().
>
> Sorry for the mistake.
>
> John
>
> -Original Message-
> From: sqlite-users-boun...@sqlite.org
> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Michael Chen
> Sent: Monday, October 19, 2009 11:40 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Infinity
>
> Thanks John. After incorporate a few changes, the code can compile and
> run.
> The result seems reasonable, the input infinity
> std::numeric_limits::max() is sent to and retrieved from a
> sqlite3
> database correctly.
>
> --terminal output
>
> sqlite3 tempdb
>
> sqlite> select * from tl;
> 1.1
> 1.79769313486232e+308
> -1.79769313486232e+308
> 3.3
>
>
> --source code --
>
> #include 
> #include 
> #include 
> #include 
> #include 
> using namespace std;
>
> int main(){
> double infinity =   std::numeric_limits::max();
> double ninfinity =  - std::numeric_limits::max();
>  sqlite3 *db;
>  char *zErrMsg = 0;
>  int rc;
>  rc = sqlite3_open("tempdb", );
>  if( rc ){
>fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
>sqlite3_close(db);
>exit(1);
>  }
>
>  sqlite3_stmt* create_tl;
>  rc = sqlite3_prepare_v2(db, "create table tl (number real)", -1,
> _tl,NULL);
>
>  if ( rc != SQLITE_OK) exit(rc);
>  rc = sqlite3_step(create_tl);
>  if ( rc != SQLITE_DONE) exit(rc);
>
>  sqlite3_stmt* insert_tl;
>  rc = sqlite3_prepare_v2(db,"insert into tl values(:number)",-1,
> _tl,NULL);
>  if ( rc != SQLITE_OK) exit(rc);
>
>  sqlite3_bind_double(insert_tl, 1, 1.1);
>  rc = sqlite3_step(insert_tl);
>  if (rc != SQLITE_DONE)
>exit(rc);
>
>  sqlite3_reset(insert_tl);
>  sqlite3_bind_double(insert_tl, 1, infinity);
>  rc = sqlite3_step(insert_tl);
>  if (rc != SQLITE_DONE)
>exit(rc);
>
>  sqlite3_reset(insert_tl);
>  sqlite3_bind_double(insert_tl, 1, ninfinity);
>  rc = sqlite3_step(insert_tl);
>  if (rc != SQLITE_DONE)
>exit(rc);
>
>  sqlite3_reset(insert_tl);
>  sqlite3_bind_double(insert_tl, 1, 3.3);
>  rc = sqlite3_step(insert_tl);
>  if (rc != SQLITE_DONE)
>exit(rc);
>
>
>  sqlite3_stmt* select_tl;
>  rc = sqlite3_prepare_v2(db, "select * from tl",-1,_tl,NULL);
>  if (rc != SQLITE_OK)
>exit(rc);
>
>  while ( sqlite3_step(select_tl) == SQLITE_ROW){
>printf("%e",sqlite3_column_double(select_tl,0));
>printf("\n");
>  }
>
>  if (rc != SQLITE_DONE)
>exit(rc);
>  sqlite3_finalize(select_tl);
>
>  sqlite3_close(db);
>  printf("exit normally\n");
> }
>
>
>
>
> --
> Best regards,
> Michael Chen
> Google Voice Phone.: 847-448-0647
> ___
> 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
>



-- 
Best regards,
Michael Chen
Google Voice Phone.: 847-448-0647
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Ticket 3810: SQLITE_ERROR on concurrent CREATE TEMP TRIGGER and sqlite3_close()

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Dave Toll wrote:
> Could you point me to the test case you referred to?

Look near the bottom of the ticket where it lists the various checkins.
[6955] is the test case.  You can examine the current source in case that
has changed at http://sqlite.org/src/dir

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iEYEARECAAYFAkrd8TYACgkQmOOfHg372QQmLgCffJjKBUwjZ6uND78KbHe7fuWq
kGsAoM993+BlkjyBMeACJBYQapF0lQBV
=/txR
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question about the update of tuples in sqlite-views

[Pavel Ivanov]

Views don't have any particular materialization or storage, it's just
a select statement that is merged into your query every time you
select something from the view. So to answer your question only
changed tuple in the table is updated physically, views don't need to
be updated. But in practical sense any occurrence of this tuple in
result sets generated by all three views is changed, so all views are
"updated".

Pavel

On Tue, Oct 20, 2009 at 12:17 PM, Koston, Thorsten (ICT)
 wrote:
> Hello,
>
> i have a question about the update machanism for tuples in different
> views:
>
> For example from a table we have three different views.
> How will be the view updated if one Tuple updated or changed from the
> DB?
> (a) only the particular Tuple updated in the different view?
> (b) or updated the complete views?
>
> Thank you in advance
>
> T.
>
> ___
> 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] Question about the update of tuples in sqlite-views

[Martin Engelschalk]

Hi,

a view does not need to be updated. Think of a view as a stored select 
statement.

Martin

Koston, Thorsten (ICT) wrote:
> Hello,
>
> i have a question about the update machanism for tuples in different
> views:
>
> For example from a table we have three different views. 
> How will be the view updated if one Tuple updated or changed from the
> DB? 
> (a) only the particular Tuple updated in the different view? 
> (b) or updated the complete views?
>
> Thank you in advance 
>
> T.
>
> ___
> 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] Question about the update of tuples in sqlite-views

[Koston, Thorsten (ICT)]

Hello,

i have a question about the update machanism for tuples in different
views:

For example from a table we have three different views. 
How will be the view updated if one Tuple updated or changed from the
DB? 
(a) only the particular Tuple updated in the different view? 
(b) or updated the complete views?

Thank you in advance 

T.

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

Re: [sqlite] Ticket 3810: SQLITE_ERROR on concurrent CREATE TEMP TRIGGER and sqlite3_close()

[Dave Toll]

Hi Roger

Your point about the preprocessor definitions is well-taken - I use them to 
optimize for my environment but they are not pertinent to the ticket. I 
recompiled sqlite3.c without all the definitions mentioned in the ticket 
(except -DSQLITE_OS_UNIX=1) and was still able to reproduce the error with my 
test code.

Could you point me to the test case you referred to?

Cheers,
Dave.


-Original Message-
From: Roger Binns [mailto:rog...@rogerbinns.com]
Sent: Mon 10/19/2009 9:05 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Ticket 3810: SQLITE_ERROR on concurrent CREATE TEMP 
TRIGGER and sqlite3_close()
 
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Dave Toll wrote:
> The race condition described in http://www.sqlite.org/cvstrac/tktview?tn=3810 
> is
> still present in SQLite 3.6.19 (amalgamation version running on Linux)

My biggest problem with the ticket is the huge list of omits and other
definitions at the bottom.  Are they really necessary and pertinent to the
ticket?  They definitely shouldn't be used with the amalgamation.

One of the checkins is a test case for the test suite.  Perhaps supplying an
improved test case would also help a lot, and prevent a regression in the
future.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iEYEARECAAYFAkrcxnMACgkQmOOfHg372QSjTACffDE0K7pWwlyiVZx8Kl7GeVJ2
hVUAoKuBI7uN5x14opprsvrrML0fG8Ly
=oRps
-END PGP SIGNATURE-


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

Re: [sqlite] Problems with SQLite under Cygwin

[Pavel Ivanov]

Another idea: make sure that you're telling linker to link with
libsqlite3.a - it cannot guess it for you.

Pavel

On Tue, Oct 20, 2009 at 6:13 AM, Michal Seliga  wrote:
> just an idea - make sure you are crosscompiling and libsqlite3.a is for
> arm-linux and not for cygwin
>
> j.hermanussen wrote:
>> Hi to all, I'm new on this list.
>>
>> I'm working on an application that runs on a small ARM-LINUX device, too
>> small to contain GCC. So I've installed Cygwin on my Win XP desktop PC.
>> Cygwin has arm-linux.gcc installed. Compiling & link works fine. The
>> exec is FTP'd to the ARM device. Works great.
>> Now I want to incorporate SQLite in my application. I unzipped it to a
>> directory under Cygwin, did the configure, make & make install steps.
>> The library libsqlite3.a is in place but when I make a small main.c with
>> a call to sqlite3_open(...) in it, the linker says:
>> undefined reference for sqlite3_open
>>
>> Does anybody have any idea on what causes this link error?
>>
>> Thanks for your attention,
>> John
>>
>> ___
>> 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-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] index for a group by

[Pavel Ivanov]

> please could you let me know which index could be better or faster?

For this particular query index on (t,a,b,c) or even on (t,a,b,c,d)
would be better and cause the query to execute faster (of course if by
conditions t>x1 and t also do you know by chance how to speed up the index creation?

There's no way to do that. SQLite have to scan the whole table, read
data from all rows and put necessary information into the index.
Nothing in this process can be sped up.

Pavel

On Mon, Oct 19, 2009 at 5:50 PM, Sylvain Pointeau
 wrote:
> hello,
> I have a table T (a,b,c,d,t)
> where c is a value
> a,b,c some dimensions
> and t the time
>
> I need to make a subset with a "group by"
> like
>
> select a,b,c,sum(d)
> from T
> where t>x1 and t group by a,b,c
>
> I created an index on a,b,c
> but this table is large and the index creation is time consuming (few hours)
>
> please could you let me know which index could be better or faster?
> also do you know by chance how to speed up the index creation?
>
> Best regards,
> Sylvain
> ___
> 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] Problems with SQLite under Cygwin

[Michal Seliga]

just an idea - make sure you are crosscompiling and libsqlite3.a is for
arm-linux and not for cygwin

j.hermanussen wrote:
> Hi to all, I'm new on this list.
> 
> I'm working on an application that runs on a small ARM-LINUX device, too 
> small to contain GCC. So I've installed Cygwin on my Win XP desktop PC. 
> Cygwin has arm-linux.gcc installed. Compiling & link works fine. The 
> exec is FTP'd to the ARM device. Works great.
> Now I want to incorporate SQLite in my application. I unzipped it to a 
> directory under Cygwin, did the configure, make & make install steps. 
> The library libsqlite3.a is in place but when I make a small main.c with 
> a call to sqlite3_open(...) in it, the linker says:
> undefined reference for sqlite3_open
> 
> Does anybody have any idea on what causes this link error?
> 
> Thanks for your attention,
> John
> 
> ___
> 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] Problems with SQLite under Cygwin

[j.hermanussen]

Hi to all, I'm new on this list.

I'm working on an application that runs on a small ARM-LINUX device, too 
small to contain GCC. So I've installed Cygwin on my Win XP desktop PC. 
Cygwin has arm-linux.gcc installed. Compiling & link works fine. The 
exec is FTP'd to the ARM device. Works great.
Now I want to incorporate SQLite in my application. I unzipped it to a 
directory under Cygwin, did the configure, make & make install steps. 
The library libsqlite3.a is in place but when I make a small main.c with 
a call to sqlite3_open(...) in it, the linker says:
undefined reference for sqlite3_open

Does anybody have any idea on what causes this link error?

Thanks for your attention,
John

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

Re: [sqlite] Does single thread SQLite have bugs?

[Dan Kennedy]

On Oct 20, 2009, at 12:49 PM, TEZ wrote:

> Hi
>
> When I ran "make test", some errors were shown.
> Is it meaning that SQLite has bugs?

In this case it means the test script is buggy. Not SQLite
itself.

Dan.




> $ uname -a
> Linux localhost.localdomain 2.6.27-43vl5 #1 SMP Sat Aug 15 22:17:55  
> JST
> 2009 i686 i686 i386 GNU/Linux
> $ wget http://www.sqlite.org/sqlite-3.6.19.tar.gz
> $ tar zxf sqlite-3.6.19.tar.gz
> $ cd sqlite-3.6.19
> $ ./configure --enable-threadsafe=no
> $ make test
>:
> init-1.1.2...
> Expected: [mutex mem pcache]
> Got: [mem pcache]
>:
> init-1.1.5...
> Expected: [mutex mem pcache]
> Got: [mem pcache]
>:
> init-1.1.7...
> Expected: [mutex mem pcache]
> Got: [mem pcache]
>:
> init-1.2.1...
> Expected: [SQLITE_ERROR]
> Got: [SQLITE_OK]
>:
> init-1.2.2...
> Expected: []
> Got: [mem pcache]
>:
> init-1.2.4...
> Expected: [SQLITE_ERROR]
> Got: [SQLITE_OK]
>:
> init-1.2.5...
> Expected: []
> Got: [mem pcache]
>:
> init-1.2.7...
> Expected: [mutex mem pcache]
> Got: [mem pcache]
>:
> init-1.3.2...
> Expected: [mutex]
> Got: []
>:
> init-1.3.5...
> Expected: [mutex]
> Got: []
>:
> init-1.3.7...
> Expected: [mutex mem pcache]
> Got: [mem pcache]
>:
> init-1.4.2...
> Expected: [mutex mem]
> Got: [mem]
>:
> init-1.4.5...
> Expected: [mutex mem]
> Got: [mem]
>:
> init-1.4.7...
> Expected: [mutex mem pcache]
> Got: [mem pcache]
>:
> 14 errors out of 42574 tests
> Failures on these tests: init-1.1.2 init-1.1.5 init-1.1.7 init-1.2.1
> init-1.2.2 init-1.2.4 init-1.2.5 init-1.2.7 init-1.3.2 init-1.3.5
> init-1.3.7 init-1.4.2 init-1.4.5 init-1.4.7
>:
> $ echo $?
> 0
>
> -- 
> Thanks.
>
> TEZ
> ___
> 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