[sqlite] Consequences of resetting sqlite_sequence.seq

[Nikolaus Rath]

Hello,

I would like to store rows with an id column that is unique (at least
most of the time, see below), but must be below some maximum value, and
I would like to do so in a very space efficient way.


My idea is to use the rowid as the id column and declare it as
autoincrement. After every insertion, I would check that the generated
rowid is less than the maximum value. If the rowid exceeds the max
value, I would reset the seq no in the sqlite_sequence table back to 1,
and do some external processing to handle the fact that previously
deleted rowids may now be reused.

This seems to work in practice. After 'seq' is reset, new rowids start again
with the smallest free value and skip occupied ones.

However, can someone tell me if:

a) this is reasonably efficient, e.g. if there are 1 used rowids
   before the next free one, will sqlite try each one in turn until it
   finds an unused one, or can it jump directly to the unused one.

b) how (un-)likely it is that future version of SQLite will behave in
   the same way.


Best,

   -Nikolaus

-- 
 »Time flies like an arrow, fruit flies like a Banana.«

  PGP fingerprint: 5B93 61F8 4EA2 E279 ABF6  02CF A9AD B7F8 AE4E 425C
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Referring to column alias

[Igor Tandetnik]

On 10/28/2011 4:28 PM, Pete wrote:

I have another variation of this issue:

SELECT col1 - col2 as Total, Total * price FROM tst

... gives an error " no such column: Total".  I can just repeat "col1 - col2"
of course, but wondering if there is a way to refer to Total within the
SELECT.


This is by design, blessed by SQL-92 standard. The closest you can get 
is something like


SELECT Total, Total * price FROM
(select col1 - col2 as Total, price from tst);

This will likely be noticeably slower though.
--
Igor Tandetnik

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

Re: [sqlite] Referring to column alias

[Pete]

I have another variation of this issue:

SELECT col1 - col2 as Total, Total * price FROM tst

.. gives an error " no such column: Total".  I can just repeat "col1 - col2"
of course, but wondering if there is a way to refer to Total within the
SELECT.

Thanks,


Pete




> >
> > Message: 6
> > Date: Sun, 23 Oct 2011 21:02:07 +0200
> > From: Kees Nuyt 
> > To: sqlite-users@sqlite.org
> > Subject: Re: [sqlite] sqlite-users Digest, Vol 46, Issue 23
> > Message-ID: 
> > Content-Type: text/plain; charset=us-ascii
> >
> > On Sun, 23 Oct 2011 10:26:14 -0700, Pete 
> > wrote:
> >
> > >Apologies, I omitted what is the real cause of the problem.  This
> > simplified
> > >SELECT illustrates the error:
> > >
> > >SELECT sum( colc * cold ) as total from tst where total > 1000
> > >
> > >The error message is "misuse of aggregate: sum()".  No error if I remove
> > the
> > >where clause.
> >
> > A condition on an aggregate is expressed with a HAVING clause, not
> > a WHERE clause.
> >
> > That is because WHERE and HAVING work on different stages of the
> > SELECT statement: WHERE decides which rows to include in the
> > aggregate, HAVING decides which results to present after
> > aggregation.
> > --
> >  (  Kees Nuyt
> >  )
> > c[_]
> >
> >
> >
> > 
> >
> >5
> 
>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Using SQLite on Windows 64bit

[J Trahair]

Thank you, but it seems to be working now - I didn't have VC++2010 redist.

Regards

Jonathan
  - Original Message - 
  From: Dimiter 'malkia' Stanev 
  To: General Discussion of SQLite Database 
  Sent: Friday, October 28, 2011 12:26 AM
  Subject: Re: [sqlite] Using SQLite on Windows 64bit





  I haven't used the SQLite .net modules, a coworker of mine did, and he 
  ran into the same issue.

  It looks like that the bundled Managed .DLL (or was it native .DLL) was 
  compiled for 32-bit, and his .NET application was set to "Default Mode".

  Once it was forced to be for 32-bit, it worked.

  This could be even changed from the command-line, but I forgot the tool 
  doing it. I'll dig up, if you need to.

  Thanks!

  On 10/26/2011 2:17 AM, J Trahair wrote:
  > Hi.
  >
  > I want to install a VB.Net application onto a customer's Windows 64bit 
computer.
  >
  > I have my own Windows 64bit laptop with Visual Studio 2010 installed. I 
have a VB.Net application which uses SQLite successfully, both in the 
development runtime, and as an 'installed' program in a separate folder on the 
same laptop.
  >
  > However, I have installed the same installation setup.exe on a separate 
Windows 64bit computer, and it comes up with the error message
  > 'Could not load file or assembly System.Data.SQLite.dll or one of its 
dependencies, the specified module could not be found.' However, the 
System.Data.SQLite.dll IS there (v.1.0.76). What dependencies does it need 
(64bit)?
  >
  > Thanks in advance.
  >
  > Jonathan Trahair


  ___
  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] Unique id

[Simon Slavin]

On 28 Oct 2011, at 8:20am, Tamara Cattivelli wrote:

> how can I let the database generate a generally unique id(guid, String)?

The database will generate a unique integer for the row itself whenever you 
save a row without specifying the id.  You can insert a row then tell what 
integer it used using the C function 'sqlite3_last_insert_rowid()'



or the SQLite function 'last_insert_rowid()'



SQLite has no understanding of GUIDs.  By all means, generate one yourself and 
use it in a SQLite database, but SQLite won't generate one for you.

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

Re: [sqlite] Unique id

[Tamara Cattivelli]

Hi,
how can I let the database generate a generally unique id(guid, String)?
Thanks, Tamara

2011/10/27, Simon Slavin :
>
> On 27 Oct 2011, at 10:04pm, Kees Nuyt wrote:
>
>> On Thu, 27 Oct 2011 21:47:17 +0100, Simon Slavin
>>  wrote:
>>
>>> On 27 Oct 2011, at 8:12pm, Tim Streater wrote:
>>>
 Is there a way to get a unique id from a database
 without actually creating a new row?
>>>
>>> Sure.  Do something like
>>>
>>> SELECT max(id) FROM mytab;
>>>
>>> Then in your own code, add 1 to it and use that as
>>> the "id" for the row you're about to save.
>>> Make sure you handle the NULL case (where mytab
>>> doesn't have any rows in it yet) correctly.
>>
>> And wrap it all in an IMMEDIATE or EXCLUSIVE transaction, or
>> you'll get a race condition if some other process tries to do the
>> same at the same time.
>
> Oh right.  Yes, if you have more than one process doing this at the same
> time, you're screwed.  Better just to pick a random integer out of a huge
> range and use that.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>

-- 
Gesendet von meinem Mobilgerät
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Using SQLite on Windows 64bit

[Dimiter 'malkia' Stanev]

I haven't used the SQLite .net modules, a coworker of mine did, and he 
ran into the same issue.


It looks like that the bundled Managed .DLL (or was it native .DLL) was 
compiled for 32-bit, and his .NET application was set to "Default Mode".


Once it was forced to be for 32-bit, it worked.

This could be even changed from the command-line, but I forgot the tool 
doing it. I'll dig up, if you need to.


Thanks!

On 10/26/2011 2:17 AM, J Trahair wrote:

Hi.

I want to install a VB.Net application onto a customer's Windows 64bit computer.

I have my own Windows 64bit laptop with Visual Studio 2010 installed. I have a 
VB.Net application which uses SQLite successfully, both in the development 
runtime, and as an 'installed' program in a separate folder on the same laptop.

However, I have installed the same installation setup.exe on a separate Windows 
64bit computer, and it comes up with the error message
'Could not load file or assembly System.Data.SQLite.dll or one of its 
dependencies, the specified module could not be found.' However, the 
System.Data.SQLite.dll IS there (v.1.0.76). What dependencies does it need 
(64bit)?

Thanks in advance.

Jonathan Trahair



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

[sqlite] Minor inconsistency between sqlite3_value_numeric_type() and sqlite3_value_double(), etc.

[Peter Aronson]

Not that this is a big difference, but I noticed when looking at the code for 
sqlite3_value_numeric_type() that it checks for numeric types in text values, 
but not in blobs values, but that sqlite3_value_double(), sqlite3_value_int() 
and sqlite3_value_int64() appear to look for numeric strings in blob values as 
well.  Which come to think about it, is a bit of an odd thing to do.  I'm not 
particularly worried about being passed numeric strings in blob values, but it 
does seem that sqlite3_value_numeric_type() and the sqlite3_value_* numeric 
functions ought to behave the same.

Best regards,

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

Re: [sqlite] ORDER BY disregarded for GROUP BY

[Sean Pieper]

your problem is that v refers to data in the original row-- which v you see 
when you group is totally arbitrary, and there's no requirement on sql to use 
the same one for ordering. You need to specify an aggregate on the non-grouping 
columns to really have any sort of defined behavior.

What I would suggest is something like:

select g,min(v) as val from t group by g order by val asc;

-sean

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Tobias Sjösten
Sent: Friday, October 28, 2011 9:42 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] ORDER BY disregarded for GROUP BY

I have a table:

CREATE TABLE t
(
i INT,
g VARCHAR(1),
v INT
);

And the data:

INSERT INTO t (i,g,v) VALUES (1,'a',2);
INSERT INTO t (i,g,v) VALUES (1,'a',1);
INSERT INTO t (i,g,v) VALUES (1,'a',3);
INSERT INTO t (i,g,v) VALUES (1,'b',2);
INSERT INTO t (i,g,v) VALUES (1,'b',1);
INSERT INTO t (i,g,v) VALUES (1,'b',3);

Now when I select that data, ordered by 'v', it shows correctly:

> SELECT g,v FROM t ORDER BY v ASC;
a|1
b|1
a|2
b|2
a|3
b|3

But when I group it by 'g' it completely disregards the ordering:

> SELECT g,v FROM t GROUP BY g ORDER BY v ASC;
a|3
b|3

Using descending order does not matter, btw. The result is the same.

I'm not sure if this is the expected behavior in SQLite but for me it
certainly wasn't. I was thinking SQLite would act as MySQL and respect
the ordering when grouping.

Is there a way around this or is it a bug with SQLite?

--

tobiassjosten.net // +46 (0) 70 - 091 17 55 // twitter.com/tobiassjosten
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
---
This email message is for the sole use of the intended recipient(s) and may 
contain
confidential information.  Any unauthorized review, use, disclosure or 
distribution
is prohibited.  If you are not the intended recipient, please contact the 
sender by
reply email and destroy all copies of the original message.
---
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] EXT : ORDER BY disregarded for GROUP BY

[Black, Michael (IS)]

I think this is (or ought to be) an FAQ.



v is considered to be random and unreliable -- you only want one record and 
you're asking it to pick one-from-N without any logic (you assume order by does 
this but it still returns a set and not a single value).  I don't know if mysql 
will give you a different answer for ascending or descending but that's 
probably coincidental.  I don't think this is considered standard behavior 
(though I could quite likely be wrong about that).



What you want is this:



select g,min(v) from t group by g;



It's explicit and will give you the same answer on all implementations.



Michael D. Black

Senior Scientist

Advanced Analytics Directorate

Advanced GEOINT Solutions Operating Unit

Northrop Grumman Information Systems


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Tobias Sjösten [tobias.sjos...@gmail.com]
Sent: Friday, October 28, 2011 11:42 AM
To: sqlite-users@sqlite.org
Subject: EXT :[sqlite] ORDER BY disregarded for GROUP BY

I have a table:

CREATE TABLE t
(
i INT,
g VARCHAR(1),
v INT
);

And the data:

INSERT INTO t (i,g,v) VALUES (1,'a',2);
INSERT INTO t (i,g,v) VALUES (1,'a',1);
INSERT INTO t (i,g,v) VALUES (1,'a',3);
INSERT INTO t (i,g,v) VALUES (1,'b',2);
INSERT INTO t (i,g,v) VALUES (1,'b',1);
INSERT INTO t (i,g,v) VALUES (1,'b',3);

Now when I select that data, ordered by 'v', it shows correctly:

> SELECT g,v FROM t ORDER BY v ASC;
a|1
b|1
a|2
b|2
a|3
b|3

But when I group it by 'g' it completely disregards the ordering:

> SELECT g,v FROM t GROUP BY g ORDER BY v ASC;
a|3
b|3

Using descending order does not matter, btw. The result is the same.

I'm not sure if this is the expected behavior in SQLite but for me it
certainly wasn't. I was thinking SQLite would act as MySQL and respect
the ordering when grouping.

Is there a way around this or is it a bug with SQLite?

--

tobiassjosten.net // +46 (0) 70 - 091 17 55 // twitter.com/tobiassjosten
___
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] ORDER BY disregarded for GROUP BY

[Igor Tandetnik]

On 10/28/2011 12:42 PM, Tobias Sjösten wrote:

But when I group it by 'g' it completely disregards the ordering:


SELECT g,v FROM t GROUP BY g ORDER BY v ASC;

a|3
b|3


What seems to be the problem? The resultset is ordered by the second 
column, isn't it? What did you expect to happen differently?



Using descending order does not matter, btw. The result is the same.


You have two identical values - why would you expect the order to matter?


I'm not sure if this is the expected behavior in SQLite but for me it
certainly wasn't. I was thinking SQLite would act as MySQL and respect
the ordering when grouping.


I'm pretty sure MySQL would produce an error on your query. I don't 
believe it allows a column that is neither in a GROUP BY clause nor in 
an argument of an aggregate function.

--
Igor Tandetnik

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

[sqlite] ORDER BY disregarded for GROUP BY

[Tobias Sjösten]

I have a table:

CREATE TABLE t
(
i INT,
g VARCHAR(1),
v INT
);

And the data:

INSERT INTO t (i,g,v) VALUES (1,'a',2);
INSERT INTO t (i,g,v) VALUES (1,'a',1);
INSERT INTO t (i,g,v) VALUES (1,'a',3);
INSERT INTO t (i,g,v) VALUES (1,'b',2);
INSERT INTO t (i,g,v) VALUES (1,'b',1);
INSERT INTO t (i,g,v) VALUES (1,'b',3);

Now when I select that data, ordered by 'v', it shows correctly:

> SELECT g,v FROM t ORDER BY v ASC;
a|1
b|1
a|2
b|2
a|3
b|3

But when I group it by 'g' it completely disregards the ordering:

> SELECT g,v FROM t GROUP BY g ORDER BY v ASC;
a|3
b|3

Using descending order does not matter, btw. The result is the same.

I'm not sure if this is the expected behavior in SQLite but for me it
certainly wasn't. I was thinking SQLite would act as MySQL and respect
the ordering when grouping.

Is there a way around this or is it a bug with SQLite?

--

tobiassjosten.net // +46 (0) 70 - 091 17 55 // twitter.com/tobiassjosten
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] cache size and insert-only

[Scott Hess]

On Fri, Oct 28, 2011 at 5:36 AM, Jay A. Kreibich  wrote:
> On Fri, Oct 28, 2011 at 01:33:31PM +0200, Gert Corthout scratched on the wall:
>> we have a database that only performs insert statements on a table
>> (database contains only this table), is it in this case a good idea
>> to reduce the cache size to, say, 5?
>
>  No, that would be a bad idea.  Smaller than the default 2000 might
>  work, but you don't want to go that small.

What Jay said.  I just wanted to additionally point out that making
the cache absurdly small gets into diminishing returns.  If you drop
to 100 you've already saved 95% of the potential cache size already
(though you might not have cut the ACTUAL usage that much).

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

Re: [sqlite] Segmentation Fault on SQLITE3_exex

[Black, Michael (IS)]

http://www.sqlite.org/faq.html#q6 seems misleading when also reading 
http://www.sqlite.org/threadsafe.html

Looks like you need to finalize() inside your mutex boundary according to the 
FAQ.

But, if you use serialized mode the other page says "no restriction".

Make sure you use serialized mode and it appears you don't need any mutexes and 
you should be able to reuse prepared statements like I showed you.  You won't 
get 2 requests at the same time to the database as they will be serialized.



Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems



From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Christian [siriu...@gmx.de]
Sent: Friday, October 28, 2011 10:08 AM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] Segmentation Fault on SQLITE3_exex


Many thanks for your instant help so far! I will run a few tests actually I use 
on single class instantiation and each thread has an pointer to the object. The 
code I put below is incomplete but describes the standard behavoir in all the 
database methods, I don't want to give full access to the db, only some 
functionalities should be accessable. So the structure is somehow like this:

Main:
Creates an instance to the DB and opens it.
All Threads using the database are started here and get a pointer to this 
single instance.
Within the instance only methods like: int getAttributeID (int id) are 
accessable.
To ensure that there are not 2 requests at the same time I manually put mutexes 
at the beginning of each function which will block until the mutex is 
available. At the end of each function or before I return from the function I 
release the mutex in order to give access to the next request.

Correct me if I'm wrong but I guess there could not be any call to a function 
at the same time beside I miss to set/release a mutex in one of these functions.

Best regards, Chris








 Original-Nachricht 
> Datum: Fri, 28 Oct 2011 12:51:21 +
> Von: "Black, Michael (IS)" 
> An: General Discussion of SQLite Database 
> Betreff: Re: [sqlite] Segmentation Fault on SQLITE3_exex

> What you're showing should basically work as long it's not a single class
> instantiation being used by multiple threads.
> Also, your missing a bind on the 2nd statement.
> And you're not retrieving the results of the query (I assume you left that
> out for brevity).
>
> What I would do is this to maximize speed...you keep the prepared
> statement around for each query and reuse it.
> Somebody else may have a better idea.  But hopefully this shows how to
> "reuse" a prepared statement.
>
>
> db = MyDBClass("mydb.sqlite"); // opens database
> run_threads(); // you did say you have threads so kick them off
>
> // here's what a thread would look like -- note that I don't have complete
> error checking in here for brevity
> thread()
> {
>   pStmt1=db->getPreparedStatement("SELECT * from model where id=?");
>   pStmt2=db->getPreparedStatement("SELECT * from type where id=?");
>   while (1) { // or whatever loop you're running
>  // 1st query
>  sqlite3_bind_int (pStmt1, 1, dd.deviceIndex);
>  doStmt(pStmt1);
>  int myIndex=sqlite3_column_int(pStmt1,0);
>  // 2nd query
>  sqlite3_bind_int (pStmt2, 1, dd.deviceIndex);
>  doStmt(pStmt2);
>  int myType=sqlite3_column_int(pStmt2,0);
>   }
>   sqlite3_finalize(pStmt1);
>   sqlite3_finalize(pStmt2);
> }
>
> doStmt(sqlite3_stmt *pStmt)
> {
> sqlite3_reset(pStmt); // we reset on the way in so we can use the
> results AFTER we step outside this function
> sqlite3_step(pStmt);
> }
>
> Michael D. Black
> Senior Scientist
> Advanced Analytics Directorate
> Advanced GEOINT Solutions Operating Unit
> Northrop Grumman Information Systems
>
>
>
> From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on
> behalf of Christian [siriu...@gmx.de]
> Sent: Friday, October 28, 2011 3:07 AM
> To: General Discussion of SQLite Database; sqlite-users@sqlite.org
> Subject: EXT :Re: [sqlite] Segmentation Fault on SQLITE3_exex
>
>
> First of all thanks for your detailed reply! According to the
> documentation its not recommended to reuse a preparedStatement after 
> finalizing it. So
> my initial guess to do something like this:
>
> sqlite3_stmt *preparedStatement = 0;
> static char command [1024];
>
> sprintf (command, "SELECT * from model where id=?");
>
> if (sqlite3_prepare_v2 (db, command, strlen(command), &preparedStatement,
> 0) != SQLITE_OK)
>   {
>   LOG_MESSAGE ("SQLite Error: %s error=%d\n", sqlite3_errmsg (db),
> sqlite3_errcode (db));
>   sqlite3_finalize (preparedStatement);
>   return FALSE;
>   }
> sqlite3_bind_int (preparedStatement, 1, dd.deviceIndex);
> success = sqlite3_step(preparedStatement);
>
> //read result
>
> if ((retVal= sqlite3_finalize(preparedStatement)) != SQLITE_OK){
> LOG_MESSAGE ("SQLite Error: %s

Re: [sqlite] Segmentation Fault on SQLITE3_exex

[Christian]

Many thanks for your instant help so far! I will run a few tests actually I use 
on single class instantiation and each thread has an pointer to the object. The 
code I put below is incomplete but describes the standard behavoir in all the 
database methods, I don't want to give full access to the db, only some 
functionalities should be accessable. So the structure is somehow like this:

Main:
Creates an instance to the DB and opens it.
All Threads using the database are started here and get a pointer to this 
single instance. 
Within the instance only methods like: int getAttributeID (int id) are 
accessable.
To ensure that there are not 2 requests at the same time I manually put mutexes 
at the beginning of each function which will block until the mutex is 
available. At the end of each function or before I return from the function I 
release the mutex in order to give access to the next request.

Correct me if I'm wrong but I guess there could not be any call to a function 
at the same time beside I miss to set/release a mutex in one of these functions.

Best regards, Chris








 Original-Nachricht 
> Datum: Fri, 28 Oct 2011 12:51:21 +
> Von: "Black, Michael (IS)" 
> An: General Discussion of SQLite Database 
> Betreff: Re: [sqlite] Segmentation Fault on SQLITE3_exex

> What you're showing should basically work as long it's not a single class
> instantiation being used by multiple threads.
> Also, your missing a bind on the 2nd statement.
> And you're not retrieving the results of the query (I assume you left that
> out for brevity).
> 
> What I would do is this to maximize speed...you keep the prepared
> statement around for each query and reuse it.
> Somebody else may have a better idea.  But hopefully this shows how to
> "reuse" a prepared statement.
> 
> 
> db = MyDBClass("mydb.sqlite"); // opens database
> run_threads(); // you did say you have threads so kick them off
> 
> // here's what a thread would look like -- note that I don't have complete
> error checking in here for brevity
> thread()
> {
>   pStmt1=db->getPreparedStatement("SELECT * from model where id=?");
>   pStmt2=db->getPreparedStatement("SELECT * from type where id=?");
>   while (1) { // or whatever loop you're running
>  // 1st query
>  sqlite3_bind_int (pStmt1, 1, dd.deviceIndex);
>  doStmt(pStmt1);
>  int myIndex=sqlite3_column_int(pStmt1,0);
>  // 2nd query
>  sqlite3_bind_int (pStmt2, 1, dd.deviceIndex);
>  doStmt(pStmt2);
>  int myType=sqlite3_column_int(pStmt2,0);
>   }
>   sqlite3_finalize(pStmt1);
>   sqlite3_finalize(pStmt2);
> }
> 
> doStmt(sqlite3_stmt *pStmt)
> {
> sqlite3_reset(pStmt); // we reset on the way in so we can use the
> results AFTER we step outside this function
> sqlite3_step(pStmt);
> }
> 
> Michael D. Black
> Senior Scientist
> Advanced Analytics Directorate
> Advanced GEOINT Solutions Operating Unit
> Northrop Grumman Information Systems
> 
> 
> 
> From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on
> behalf of Christian [siriu...@gmx.de]
> Sent: Friday, October 28, 2011 3:07 AM
> To: General Discussion of SQLite Database; sqlite-users@sqlite.org
> Subject: EXT :Re: [sqlite] Segmentation Fault on SQLITE3_exex
> 
> 
> First of all thanks for your detailed reply! According to the
> documentation its not recommended to reuse a preparedStatement after 
> finalizing it. So
> my initial guess to do something like this:
> 
> sqlite3_stmt *preparedStatement = 0;
> static char command [1024];
> 
> sprintf (command, "SELECT * from model where id=?");
> 
> if (sqlite3_prepare_v2 (db, command, strlen(command), &preparedStatement,
> 0) != SQLITE_OK)
>   {
>   LOG_MESSAGE ("SQLite Error: %s error=%d\n", sqlite3_errmsg (db),
> sqlite3_errcode (db));
>   sqlite3_finalize (preparedStatement);
>   return FALSE;
>   }
> sqlite3_bind_int (preparedStatement, 1, dd.deviceIndex);
> success = sqlite3_step(preparedStatement);
> 
> //read result
> 
> if ((retVal= sqlite3_finalize(preparedStatement)) != SQLITE_OK){
> LOG_MESSAGE ("SQLite Error: %s error=%d\n", sqlite3_errmsg (db),
> sqlite3_errcode (db));
> }
> else
> preparedStatement=0;
> 
> 
> sprintf (command, "SELECT * FROM type WHERE id=%d", id);
> 
> if (sqlite3_prepare_v2 (db, command, strlen(command), &preparedStatement,
> 0) != SQLITE_OK)
>{
>   LOG_MESSAGE ("SQLite Error: %s error=%d\n", sqlite3_errmsg (db),
> sqlite3_errcode (db));
>   sqlite3_finalize (preparedStatement);
>return FALSE;
>}
> if ((success = sqlite3_step(preparedStatement)) != SQLITE_ROW)
>   {
>   LOG_MESSAGE ("SQLite Error: %s error=%d\n", sqlite3_errmsg (db),
> sqlite3_errcode (db));
>   sqlite3_finalize (preparedStatement);
>   return FALSE;
>   }
> 
> //read result
> 
> sqlite3_finalize (preparedStatement);
> return TRUE;
> 
> 
> Am I right that this is not possible? So I have to use one sqlite3_stmt
> for each prepare?

Re: [sqlite] cache size and insert-only

[Jay A. Kreibich]

On Fri, Oct 28, 2011 at 07:36:24AM -0500, Jay A. Kreibich scratched on the wall:
> On Fri, Oct 28, 2011 at 01:33:31PM +0200, Gert Corthout scratched on the wall:
> > 
> > hello all,
> >  
> > we have a database that only performs insert statements on a table 
> > (database contains only this table), is it in this case a good idea
> > to reduce the cache size to, say, 5?

>   No, that would be a bad idea.  Smaller than the default 2000 might 
>   work, but you don't want to go that small.


  Also, I forgot to mention that you can't actually lower under 10.

   -j

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

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

Re: [sqlite] Segmentation Fault on SQLITE3_exex

[Black, Michael (IS)]

What you're showing should basically work as long it's not a single class 
instantiation being used by multiple threads.
Also, your missing a bind on the 2nd statement.
And you're not retrieving the results of the query (I assume you left that out 
for brevity).

What I would do is this to maximize speed...you keep the prepared statement 
around for each query and reuse it.
Somebody else may have a better idea.  But hopefully this shows how to "reuse" 
a prepared statement.


db = MyDBClass("mydb.sqlite"); // opens database
run_threads(); // you did say you have threads so kick them off

// here's what a thread would look like -- note that I don't have complete 
error checking in here for brevity
thread()
{
  pStmt1=db->getPreparedStatement("SELECT * from model where id=?");
  pStmt2=db->getPreparedStatement("SELECT * from type where id=?");
  while (1) { // or whatever loop you're running
 // 1st query
 sqlite3_bind_int (pStmt1, 1, dd.deviceIndex);
 doStmt(pStmt1);
 int myIndex=sqlite3_column_int(pStmt1,0);
 // 2nd query
 sqlite3_bind_int (pStmt2, 1, dd.deviceIndex);
 doStmt(pStmt2);
 int myType=sqlite3_column_int(pStmt2,0);
  }
  sqlite3_finalize(pStmt1);
  sqlite3_finalize(pStmt2);
}

doStmt(sqlite3_stmt *pStmt)
{
sqlite3_reset(pStmt); // we reset on the way in so we can use the results 
AFTER we step outside this function
sqlite3_step(pStmt);
}

Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems



From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Christian [siriu...@gmx.de]
Sent: Friday, October 28, 2011 3:07 AM
To: General Discussion of SQLite Database; sqlite-users@sqlite.org
Subject: EXT :Re: [sqlite] Segmentation Fault on SQLITE3_exex


First of all thanks for your detailed reply! According to the documentation its 
not recommended to reuse a preparedStatement after finalizing it. So my initial 
guess to do something like this:

sqlite3_stmt *preparedStatement = 0;
static char command [1024];

sprintf (command, "SELECT * from model where id=?");

if (sqlite3_prepare_v2 (db, command, strlen(command), &preparedStatement, 0) != 
SQLITE_OK)
  {
  LOG_MESSAGE ("SQLite Error: %s error=%d\n", sqlite3_errmsg (db), 
sqlite3_errcode (db));
  sqlite3_finalize (preparedStatement);
  return FALSE;
  }
sqlite3_bind_int (preparedStatement, 1, dd.deviceIndex);
success = sqlite3_step(preparedStatement);

//read result

if ((retVal= sqlite3_finalize(preparedStatement)) != SQLITE_OK){
LOG_MESSAGE ("SQLite Error: %s error=%d\n", sqlite3_errmsg (db), 
sqlite3_errcode (db));
}
else
preparedStatement=0;


sprintf (command, "SELECT * FROM type WHERE id=%d", id);

if (sqlite3_prepare_v2 (db, command, strlen(command), &preparedStatement, 0) != 
SQLITE_OK)
   {
  LOG_MESSAGE ("SQLite Error: %s error=%d\n", sqlite3_errmsg (db), 
sqlite3_errcode (db));
  sqlite3_finalize (preparedStatement);
   return FALSE;
   }
if ((success = sqlite3_step(preparedStatement)) != SQLITE_ROW)
  {
  LOG_MESSAGE ("SQLite Error: %s error=%d\n", sqlite3_errmsg (db), 
sqlite3_errcode (db));
  sqlite3_finalize (preparedStatement);
  return FALSE;
  }

//read result

sqlite3_finalize (preparedStatement);
return TRUE;


Am I right that this is not possible? So I have to use one sqlite3_stmt for 
each prepare?

Best regards,
Chris.

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

Re: [sqlite] cache size and insert-only

[Jay A. Kreibich]

On Fri, Oct 28, 2011 at 01:33:31PM +0200, Gert Corthout scratched on the wall:
> 
> hello all,
>  
> we have a database that only performs insert statements on a table 
> (database contains only this table), is it in this case a good idea
> to reduce the cache size to, say, 5?

  No, that would be a bad idea.  Smaller than the default 2000 might 
  work, but you don't want to go that small.

> Because if I understand things correctly the cache doesn't really
> help you when you do nothing but inserts, is this correct?

  Not really.  *All* I/O is done through the cache.  Inserts require
  accessing and manipulating the table's B-Tree, in addition to the
  pages that contain the actual data.  If you have to keep re-reading
  the intermediate nodes, you'll see a performance hit, especially
  if you're working on a platform that doesn't have a good OS-level
  file-system cache.  
  
  This issue is multiplied if you have any indexes on the table, which
  also have a B-Tree that must be updated.

  Cutting the cache way back may not cause any issues for small test
  cases, but if the table already has a million rows in it, I would
  expect a slow-down.

  The best advice is to just run some tests, but be sure you run them
  with realistic data sets.  This is a case where the size of the
  table counts.  If you have no indexes on the table, I wouldn't be
  surprised if you can get away with a ~100 page cache, but I'd be very
  hesitant to cut it way way down unless performance is not an issue.

   -j

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

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

[sqlite] cache size and insert-only

[Gert Corthout]

hello all,
 
we have a database that only performs insert statements on a table (database 
contains only this table), is it in this case a good idea to reduce the cache 
size to, say, 5? Because if I understand things correctly the cache doesn't 
really help you when you do nothing but inserts, is this correct?
 
thanks in advance,
Gert  
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Segmentation Fault on SQLITE3_exex

[Stephan Beal]

On Fri, Oct 28, 2011 at 10:07 AM, Christian  wrote:

> static char command [1024];
> ...
> sprintf (command, "SELECT * from model where id=?");
>

FYI: that can be simplified to:

static char const * command = "SELECT * from model where id=?";

There's no reason why the overhead of sprintf() should be applied to a
string which contains no formatting specifiers.

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Segmentation Fault on SQLITE3_exex

[Christian]

First of all thanks for your detailed reply! According to the documentation its 
not recommended to reuse a preparedStatement after finalizing it. So my initial 
guess to do something like this:

sqlite3_stmt *preparedStatement = 0;
static char command [1024];

sprintf (command, "SELECT * from model where id=?");

if (sqlite3_prepare_v2 (db, command, strlen(command), &preparedStatement, 0) != 
SQLITE_OK)
  {
  LOG_MESSAGE ("SQLite Error: %s error=%d\n", sqlite3_errmsg (db), 
sqlite3_errcode (db));
  sqlite3_finalize (preparedStatement);  
  return FALSE;
  }
sqlite3_bind_int (preparedStatement, 1, dd.deviceIndex);
success = sqlite3_step(preparedStatement);

//read result

if ((retVal= sqlite3_finalize(preparedStatement)) != SQLITE_OK){
LOG_MESSAGE ("SQLite Error: %s error=%d\n", sqlite3_errmsg (db), 
sqlite3_errcode (db));
}
else 
preparedStatement=0;


sprintf (command, "SELECT * FROM type WHERE id=%d", id);

if (sqlite3_prepare_v2 (db, command, strlen(command), &preparedStatement, 0) != 
SQLITE_OK)
  {
  LOG_MESSAGE ("SQLite Error: %s error=%d\n", sqlite3_errmsg (db), 
sqlite3_errcode (db));
  sqlite3_finalize (preparedStatement);  
  return FALSE;
  }
if ((success = sqlite3_step(preparedStatement)) != SQLITE_ROW)
  {
  LOG_MESSAGE ("SQLite Error: %s error=%d\n", sqlite3_errmsg (db), 
sqlite3_errcode (db));
  sqlite3_finalize (preparedStatement);  
  return FALSE;
  }

//read result

sqlite3_finalize (preparedStatement);
return TRUE;


Am I right that this is not possible? So I have to use one sqlite3_stmt for 
each prepare?

Best regards,
Chris.


 Original-Nachricht 
> Datum: Thu, 27 Oct 2011 16:00:52 +
> Von: "Black, Michael (IS)" 
> An: General Discussion of SQLite Database 
> Betreff: Re: [sqlite] Segmentation Fault on SQLITE3_exex

> I think we went throught something similar to this a while ago.
> 
> 
> 
> Does each class have it's own instantiation of your database object?
> 
> 
> 
> The way you describe it they are all using the same one.
> 
> 
> 
> sqlite3_reset does not "clean up" -- it "resets" the statement to its
> initial state:
> 
> http://www.sqlite.org/c3ref/reset.html
> 
> 
> 
> sqlite3_finalize is what essentially "deletes" the statement and frees
> memory.
> 
> 
> 
> You quite obviously cannot reuse a prepared statement if you only have one
> class object instantiated.  They'll walk on each other.
> 
> 
> 
> If you want to reuse a prepared statement your threads are going to have
> to maintain the statements and pass it in to your db object.
> 
> 
> 
> But you didn't describe reusing a prepared statement.Your descriptoin
> is a memory leak as you have two prepare's without a finalize between them.
> 
> 
> 
> 
> 
> Michael D. Black
> 
> Senior Scientist
> 
> Advanced Analytics Directorate
> 
> Advanced GEOINT Solutions Operating Unit
> 
> Northrop Grumman Information Systems
> 
> 
> From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on
> behalf of Christian [siriu...@gmx.de]
> Sent: Thursday, October 27, 2011 10:24 AM
> To: General Discussion of SQLite Database; sqlite-users@sqlite.org
> Subject: EXT :Re: [sqlite] Segmentation Fault on SQLITE3_exex
> 
> Currently I'm using valgrinds memcheck tools to solve the issue, I already
> found some undelete memory which is definitly not causing the error, but I
> receive a lot of these messages:
> 
> ==13965== 1,006,200 bytes in 975 blocks are possibly lost in loss record
> 226 of 228
> ==13965==at 0x4024F20: malloc (vg_replace_malloc.c:236)
> ==13965==by 0x8096FD6: sqlite3MemMalloc (sqlite3.c:14854)
> ==13965==by 0x8097957: mallocWithAlarm (sqlite3.c:18369)
> ==13965==by 0x80979EE: sqlite3Malloc (sqlite3.c:18402)
> ==13965==by 0x809813B: sqlite3DbMallocRaw (sqlite3.c:18734)
> ==13965==by 0x809818A: sqlite3DbRealloc (sqlite3.c:18753)
> ==13965==by 0x80BAA5B: growOpArray (sqlite3.c:58415)
> ==13965==by 0x80BAAD4: sqlite3VdbeAddOp3 (sqlite3.c:58447)
> ==13965==by 0x80BAB79: sqlite3VdbeAddOp0 (sqlite3.c:58471)
> ==13965==by 0x80EF9EF: sqlite3GetVdbe (sqlite3.c:93756)
> ==13965==by 0x80F3C8E: sqlite3Select (sqlite3.c:96128)
> ==13965==by 0x8103B4D: yy_reduce (sqlite3.c:107894)
> 
> A lot means 200 of them until the program crashes. You're right that I'm
> not able to show you the complete code, beside the fact that the project
> consists of different threads using sqlite3 database. Thats why I'm thinking
> that something is wrong with the setup I use, or the sqlite usage in
> general. I shortly explain what I'm doing. For timing reasons I just have one 
> open
> connection to the database, which seems to be fine according the man page.
> From my main thread I give every accessing thread a pointer to the object
> which maintains all database functionality, there I always use my own
> mutexes to synchronize the access, I also tried