Re: [sqlite] unreached code in sqlite3.c?

[Doug Currie]

On Thu, Feb 12, 2015 at 1:35 PM, R.Smith rsm...@rsweb.co.za wrote:


 Now one could argue the warning should not be issued for it, or some
 warnings are fine as information. Personally I prefer zero unneeded
 warnings/clutter but that's just my pedantism.


My pedantism is to prefer the warning since it might catch cases where the
code inadvertently neglects to define TERM_VNULL at all.

It's easy enough to fix if you want 0 to be a valid value for TERM_VNULL:

#if TERM_VNULL
 if( pTerm-wtFlags  TERM_VNULL ) continue;
#endif

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

Re: [sqlite] unreached code in sqlite3.c?

[Doug Currie]

Well, if TERM_VNULL is 0, then the code is truly unreachable, so I wouldn't
call it a compiler bug.

e


On Thu, Feb 12, 2015 at 9:26 AM, Richard Hipp d...@sqlite.org wrote:

 Dan is right.  I think I'd calling this a clang bug.
 On Feb 12, 2015 9:06 AM, Dan Kennedy danielk1...@gmail.com wrote:

  On 02/12/2015 09:02 PM, Jens Miltner wrote:
 
  Hi,
 
  I'm getting the following two warnings when compiling sqlite3.c with the
  latest clang tools:
 
   sqlite3.c:116769:39: warning: code will never be executed
  [-Wunreachable-code]
   if( pTerm-wtFlags  TERM_VNULL ) continue;
 ^~~~
  sqlite3.c:116716:39: warning: code will never be executed
  [-Wunreachable-code]
   if( pTerm-wtFlags  TERM_VNULL ) continue;
 ^~~~
  2 warnings generated.
 
   (This is for SQLite version 3.8.8.2).
 
 
   From the code, I don't immediately see why the compiler would think
 this
  code will never be executed, so I thought I'd bring it up with you guys.
 
 
 
  Unless you have defined SQLITE_ENABLE_STAT4 (or STAT3), TERM_VNULL is
  defined as 0:
 
http://www.sqlite.org/src/artifact/d3633e9b59210324?ln=273-277
 
  Dan.
 
  ___
  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] ordinary CTE containing sum()

[Doug Currie]

For those interested in the initial misuse of aggregate issue of this
thread, there is now a ticket:

http://www.sqlite.org/src/tktview?name=2f7170d73b

e


On Mon, Feb 9, 2015 at 9:19 AM, Keith Medcalf kmedc...@dessus.com wrote:


 Not exactly since aggregates are implemented as functions.

 In the case of sum(a + b + c) you have the overhead of one aggregate setup
 call, one call per row (after the three additions are done) and one
 finalizer call to retrieve the aggregate and release the context.

 In the case of sum(a) + sum(b) + sum(c) you have three initializer calls
 being made to set up three different aggregate contexts.  Then on each row
 you call the increment function three times for three different contexts,
 then after the aggregate is complete you make three calls to finalize the
 three aggregates and release their contexts, then add up the sum.

 The number of additions is the same, but the latter (multiplicity of
 aggregate contexts) adds significantly to the size of the code path.

 This may be on the order of only a couple thousand instructions per row,
 but it is a couple *more* thousands of instructions per row than the former
 sum(a + b + c) case.

 This will not be significant where you are dealing with 10 rows, but when
 you have thousands or millions of rows it is quite significant.  It will
 also use more energy and concomitantly increase the temperature of the CPU,
 thus contributing to global warming.

 ---
 Theory is when you know everything but nothing works.  Practice is when
 everything works but no one knows why.  Sometimes theory and practice are
 combined:  nothing works and no one knows why.

 -Original Message-
 From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
 boun...@sqlite.org] On Behalf Of R.Smith
 Sent: Monday, 9 February, 2015 04:51
 To: sqlite-users@sqlite.org
 Subject: Re: [sqlite] ordinary CTE containing sum()
 
 
 On 2/8/2015 10:23 PM, James K. Lowden wrote:
 
  I have a couple of efficiency questions for those who know:
 
  1.  Is the left-join on a CTE apt to be more effecient than the version
  that uses a correlated subquery in the SELECT clause?
 
 I'm guessing it matters in some DBs but from testing it seems much the
 same in SQLite.
 
 
  2.  Is there any performance difference between
 
   sum(ca1 +ca2 + exam)
  and
   sum(ca1) + sum(ca2) + sum(exam)
 
  I would expect the left join is faster than a correlated subquery, and
  that fewer aggregates is better than more.
 
 Now this is easy to check but the answer is simple too - I know it looks
 in SQL terms like something more complicated is taking place, but in
 reality it's all the same, consider that it is just like asking which of
 these are faster:
 
 (1 + 2 + 3 + 4 + 5 + 6 + 7 + 8 + 9)
 --  OR --
 (1 + 2 + 3) + (4 + 5 + 6) + (7 + 8 + 9)
 
 Count the plusses, they are the same. The difference to the
 processor/engine (ultimately) of your two queries are merely order of
 adding, but no difference to addition operations or amount of function
 calls. (Unless adding by itself is a significantly different/slower
 operation when done inside the aggregate function than outside it, but
 that would fit somewhere between devious and insane).
 
 IF you could somehow get rid of the loop or change the compound
 iteration count it might have a viable effect, but that is not the case
 here.
 
 
 ___
 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] ordinary CTE containing sum()

[Doug Currie]

Thank you, Richard. It works for me now.

e


On Mon, Feb 9, 2015 at 1:30 PM, Richard Hipp d...@sqlite.org wrote:

 On 2/7/15, Doug Currie doug.cur...@gmail.com wrote:
  In response to this SO question:
 
 
 http://stackoverflow.com/questions/28377210/how-to-retrieve-rank-based-on-total-mark-in-sqlite-table
 
  I tried to formulate a query without temp tables using an ordinary CTE,
 but
  received an error misuse of aggregate: sum().
 

 Possibly fixed on trunk now.  Please test and confirm.

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

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

Re: [sqlite] ordinary CTE containing sum()

[Doug Currie]

  In response to this SO question:
 
 
 http://stackoverflow.com/questions/28377210/how-to-retrieve-rank-based-on-total-mark-in-sqlite-table
 
  I tried to formulate a query without temp tables using an ordinary
  CTE, but received an error misuse of aggregate: sum().


tonypdmtr http://stackoverflow.com/users/3735873/tonypdmtr on SO posted a
CTE solution; it is something like this, which works for me:

with tt (S_id, total) as
   (select S_id, sum(ca1) + sum(ca2) + sum(exam) as total
   from t group by S_id
   union values (NULL, 0))
select s.S_id, s.total,
   (select count(*)+1 from tt as r where r.total  s.total) as rank
   from tt as s where S_id is not NULL;

But my question remains, why is the UNION necessary in the  CTE?

why doesn't this work? ...

with tt (S_id, total) as
   (select S_id, sum(ca1) + sum(ca2) + sum(exam) as total
   from t group by S_id)
select s.S_id, s.total,
   (select count(*)+1 from tt as r where r.total  s.total) as rank
   from tt as s;

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

[sqlite] ordinary CTE containing sum()

[Doug Currie]

In response to this SO question:

http://stackoverflow.com/questions/28377210/how-to-retrieve-rank-based-on-total-mark-in-sqlite-table

I tried to formulate a query without temp tables using an ordinary CTE, but
received an error misuse of aggregate: sum().

This works:

sqlite with tt (S_id, total) as
   ...(select S_id, sum(ca1) + sum(ca2) + sum(exam) as total
   ... from t group by S_id)
   ... select * from tt ;
1|143
2|198
3|165
4|198
5|183

but with the same CTE this fails, even though the select statement after
the CTE works with an equivalent temporary table:

sqlite with tt (S_id, total) as
   ...(select S_id, sum(ca1) + sum(ca2) + sum(exam) as total
   ... from t group by S_id)
   ... select s.S_id, s.total,
   ...   (select count(*)+1 from tt as r where r.total  s.total)
as rank
   ...   from tt as s;
Error: misuse of aggregate: sum()

Any suggestions?

Thanks.

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

Re: [sqlite] Best Practice: Storing Dates

[Doug Nebeker]

Whatever format you choose to store it in, I highly recommend storing the UTC 
time.  It might be a little more work, but:

1. your program can display the correct local time, even if the 
database/app/user is in/changes to another timezone
2. you won't have to deal with seeing two 1:30am on the day that day light 
savings kicks in


-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Chris Keilitz
Sent: Wednesday, January 14, 2015 7:09 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] Best Practice: Storing Dates

I'm a new sqlite user and new to this mailing list. I hope this question is 
appropriate.

I am writing an application that needs to track a timestamp - date + time down 
to the seconds. I'd like to store the date/time in a standard, efficient, 
usable, portable format.  I have looked over the sqlite date/time functions / 
data types and the ISO 8601 standard and have landed on these two options:

1. Storing it in TEXT format e.g., YY-MM-DD HH:MM:SS or 2. Storing it as an 
INTEGER (LONG) in Unix Time (AKA: POSIX or Epoch
time) - number of seconds since 01/01/1970

Since sqlite and most RDMS implementations have functions to convert to and 
from both options and using a LONG should allow the date/time to function way 
past 2038, it seems it comes down to how many bytes it takes to store the 
timestamp and how fast are the conversion routines. The application I'm writing 
won't push any performance boundaries and likely won't need to overly worry 
about storage.  I just want to make the right call on data type and format and 
learn something in the process.

Again, I hope this is an appropriate post for this mailing list. If not, I 
apologize.

Thanks!

Chris
___
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] decoding a bitmask

[Doug Currie]

 The query is on a visits table from a google chrome history database. The
 query seems to work OK if a single bit is set, but fails (a blank string is
 returned) when multiple bits are set. Any ideas why?


It's because none of the WHEN 0x... cases, except 0xC0...,  have multiple
bits set. The approach you've chosen requires enumerating all the possible
combinations (all 2^5 of them in this case). You are better off with one of
the other suggested approaches by Richard Hipp or RSmith.

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

Re: [sqlite] Hexadecimal integer literals

[Doug Currie]

 There is this range of negative
 values smack in the middle of an otherwise uniformly increasing sequence of
 positive numbers.  That negative range seems discombobulating.


Why are hex literals interpreted as signed at all? You could simply
consider all hex literals as unsigned values. If you need a negative value,
prefix it with the - operator, e.g., -0x77.

With this approach (a) there is no discombobulating segment, (b) all 64 bit
bit-masks are supported, and (c) the gradual overflow to double makes sense.

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

Re: [sqlite] Hexadecimal integer literals

[Doug Currie]

 Why are hex literals interpreted as signed at all? You could simply
  consider all hex literals as unsigned values. If you need a negative
 value,
  prefix it with the - operator, e.g., -0x77.
 
  With this approach (a) there is no discombobulating segment, (b) all 64
 bit
  bit-masks are supported, and (c) the gradual overflow to double makes
  sense.


 Because SQLite only supports signed integers internally.  If hex literals
 must be unsigned, that limits them to 63 bits.


Here's an analogy: a sequence of decimal digits is unsigned; it only
becomes negative when you put a - in front of it.

Why shouldn't hex work the same way? (to eliminate the discombobulating
segment)

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

Re: [sqlite] Hexadecimal integer literals

[Doug Currie]

  Here's an analogy: a sequence of decimal digits is unsigned; it only
  becomes negative when you put a - in front of it.
 
  Why shouldn't hex work the same way? (to eliminate the discombobulating
  segment)
 

 Because then you would not be able to write (in hex) a 64-bit bitmap that
 had the most significant bit set.


Ah, you want convenience! You could write -0x8000, but that
does become a hassle.

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

Re: [sqlite] Primary Key without DataAnnotation for Windows Runtime

[Doug McDonald]

We achieved something similar by doing the following:

Creating a seperate assembly for our entities
Define the primary key attribute in that assembly, but inside the SQLite
namespace.
Comment out the primary key attribute in the SQLite/net project to avoid
re-defining it.
We were then able to share this assembly between projects, without being
tied to SQLite explicitely, but when we include SQLite in a project, the
primary key attribute is included.

Maybe that's of some use as to an approach.


On Mon, Mar 31, 2014 at 3:52 PM, Benedikt Neuhold bened...@neuhold.prowrote:

 Hi,



 we are building an App for Windows 8 and want to use sqlite as local DB. We
 want to separate our Models from our DB Engine. So we need a way to set the
 Primary Key without DataAnnotation. Is there a way?



 Thanks a lot!

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




-- 
Doug McDonald

BSc(Hons) | MCTS | MBCS
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Documentation error: sqlite3_mutex_try

[Doug Nebeker]

The documentation says that sqlite3_mutex_try will always return SQLITE_BUSY  
for some systems (for example, Windows 95).

That's not quite accurate from what I see in the latest implementation of 
winMutexTry.  It will ALWAYS return SQLITE_BUSY for any Windows usage, making 
the existence of the function somewhat misleading in this case.




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

Re: [sqlite] Concrete example of corruption

[Doug Currie]

On Dec 5, 2013, at 8:55 PM, Warren Young war...@etr-usa.com wrote:

 On 12/5/2013 17:00, Scott Robison wrote:
 Might there be a way to implement a custom VFS for Mac to deal with this?
 
 Wouldn't it be a lot simpler to just put the DB file into a Mac package (i.e. 
 directory) so the associated WAL and whatever other files get created in the 
 package, too?

Yes, I was wondering the same thing...

https://developer.apple.com/library/mac/documentation/CoreFoundation/Conceptual/CFBundles/DocumentPackages/DocumentPackages.html

e


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

Re: [sqlite] SQLite version 3.8.2 running 2.5x faster for some queries.

[Doug Currie]

On Nov 24, 2013, at 6:47 AM, Alek Paunov a...@declera.com wrote:
 
 BTW, I see the term deterministic in the SQL99 BNFs:
 …
 but different in PostgreSQL (immutable, stable, etc):


There is value in compatibility, but those adjectives are awful. In computer 
science we have referential transparency

http://en.wikipedia.org/wiki/Referential_transparency_(computer_science)

and pure functions

http://en.wikipedia.org/wiki/Pure_function

e


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

Re: [sqlite] Mystery why SQLite will not work until System.Data.SQLite has been installed

[Doug Currie]

Paul Bainter wrote:

 
  Not sure what happened to this post previously, so I guess I'll try it
  again with some additional information


GMail considered these messages spam for some reason. Check your spam
folder.

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

Re: [sqlite] Hints for the query planner

[Doug Currie]

On Sep 10, 2013, at 6:23 PM, Scott Robison sc...@casaderobison.com wrote:

 I think I prefer something along the lines of unlikely or likely. The
 problem with a term like selective (at least in my brain) is that it
 doesn't imply (for the single argument version) in what way it is being
 selective.
 
 If a negative form of the magic function is used (unlikely, seldom,
 etc) I would suggest considering inverting the optional second parameter.
 In other words, 0.05 would become 0.95. In my opinion, that reads better:
 unlikely(COLUMN LIKE '%pattern%', 0.95) reads it is unlikely the
 expression will be true 95% of the time.
 
 In like fashion, a positive form of the magic function would keep the
 current meaning of the optional second parameter.

This is the best suggestion. The pseudo-function names do not change the 
meaning of the query, and they are more clear with regard to the optional 
numeric argument.

e

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

Re: [sqlite] To BEGIN or not to BEGIN. That is the question...

[Doug Nebeker]

Igor is naturally correct.  One additional thing to keep in mind - the commit 
phase of a transaction is where a lot of work gets done (meaning slow disk 
access).  So if you have a lot of INSERTs or DELETEs to do, doing many within a 
transaction will give you better performance.

Doug

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Igor Tandetnik
Sent: Wednesday, August 28, 2013 5:44 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] To BEGIN or not to BEGIN. That is the question...

On 8/28/2013 6:28 PM, jose isaias cabrera wrote:
 I know that if I am doing INSERTs and such, I need to,

 BEGIN;
  INSERT...
 END;

No, you don't need to. You can, if you want to, but there's no reason to have 
to.

 But, do I need to begin if I am going to create a table? ie.

 BEGIN;
  CREATE TABLE tableName
  (
  JobID integer primary key, SubProjID integer, ProjID integer
  );
 END;

Same here.

 Also, what other commands should I wrap with BEGINs and ENDs?

BEGIN starts an explicit transaction; END commits the same. You need an 
explicit transaction if you want to execute two or more statements atomically, 
so that either they all succeed, or one fails and then the database is rolled 
back to the original state.

If you don't start a transaction explicitly, then each statement is implicitly 
wrapped in its own transaction.
--
Igor Tandetnik

___
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] CREATE INDEX and column order

[Doug Nebeker]

Thanks Simon, that makes a lot of sense.  Does the order of columns in a WHERE 
clause matter, or will the query optimizer look at them as a set and find the 
best index?  (ignoring all the special cases)



-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Simon Slavin
Sent: Tuesday, August 27, 2013 7:29 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] CREATE INDEX and column order


On 27 Aug 2013, at 1:07pm, Doug Nebeker ad...@poweradmin.com wrote:

 I was reading about the new query planner and came across a few references to 
 that idea that the left most columns in the index definition should be the 
 most unique (as far as values in the column are concerned).
 
 Is that correct?  In my case, many tables have a timestamp column, and I've 
 been using that as my right-most column, but it seems it would be a great 
 candidate to be switched.

When using an index, SQL has to work from the most significant end -- the left 
-- to the least significant end -- the right.  For instance, suppose you have a 
phone book

CREATE TABLE phonebook (firstname TEXT, surname TEXT, phonenumber TEXT) CREATE 
INDEX psf ON phonebook (surname, firstname)

This index is useless for looking someone up by their firstname, because it has 
everyone listed in surname order:

Abelson, David
Abelson, Joan
Smith, David
Smith, Martine
Smith, Tom

If you wanted to look up all the 'Martines' you'd just have to look through the 
whole index anyway.  You might as well scan the original table. [1]

Simon.

[1] Yes, many picky details about this but I'm simplifying for the purpose of 
explanation.
___
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] CREATE INDEX and column order

[Doug Nebeker]

I was reading about the new query planner and came across a few references to 
that idea that the left most columns in the index definition should be the most 
unique (as far as values in the column are concerned).

Is that correct?  In my case, many tables have a timestamp column, and I've 
been using that as my right-most column, but it seems it would be a great 
candidate to be switched.

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

Re: [sqlite] [Bug] sqlite3_finalize() *DOES NOT* return most recent evaluation error code

[Doug Currie]

 I'm unable to reproduce the problem using C.  Maybe it is in lsqlite3.

Yes, lsqlite3 still uses the old sqlite3_prepare() API to maintain 
compatibility with some legacy systems. It is long past time that it should 
have changed to use sqlite3_prepare_v2().

Running Richard's example with sqlite3_prepare_v2 changed to sqlite3_prepare 
gives this output:

first step returns 101
second step returns 1
error message = SQL logic error or missing database
finalize returns 19

This doesn't match the output of lsqlite3 because the wrapper tries to be 
helpful, and when the second step fails, it calls sqlite_reset to get the error 
code. The equivalent C code is:


#include stdio.h
#include sqlite3.h
int main(int argc, char **argv){
  sqlite3 *db;
  sqlite3_stmt *pStmt;
  int rc;
  sqlite3_open(:memory:, db);
  sqlite3_exec(db, create table t(x unique);, 0, 0, 0);

  //sqlite3_prepare_v2(db, insert into t(x) values(?), -1, pStmt, 0);
  sqlite3_prepare(db, insert into t(x) values(?), -1, pStmt, 0);

  sqlite3_bind_int(pStmt, 1, 123);
  rc = sqlite3_step(pStmt);
  printf(first step returns %d\n, rc);
  sqlite3_reset(pStmt);
  rc = sqlite3_step(pStmt);
  printf(second step returns %d\n, rc);
  printf(error message = %s\n, sqlite3_errmsg(db));
  

  if (rc == SQLITE_ERROR)
  {
rc = sqlite3_reset(pStmt);
printf(second step's reset returns %d\n, rc);
printf(error message = %s\n, sqlite3_errmsg(db));
  }

  rc = sqlite3_finalize(pStmt);
  printf(finalize returns %d\n, rc);
  sqlite3_close(db);
  return 0;
}



That prints

first step returns 101
second step returns 1
error message = SQL logic error or missing database
second step's reset returns 19
error message = column x is not unique
finalize returns 0

which matches the output from the Lua script.

The next version of lsqlite3 will use the recommended sqlite3_prepare_v2() API.

e

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

Re: [sqlite] Beginning database question

[Doug Nebeker]

This is a tough one to answer because there is so much context to consider.  
SQLite, or any database, could easily solve the problem you mention (storing 
values that can change without needing to recompile your program).  

Whether it's more efficient is another question.  If you think you'll have many 
more than 10 entries (likes thousands or millions), or the 10 entries change 
very often (once a minute??) and you could automate entering them, a simple app 
using a database like SQLite would be a decent way to go.   Does this need to 
get put on a website?  Is the app going to be mailed around? (in which case a 
spreadsheet might be just as easy).

If you'd like to use this as an excuse to learn more about databases, this 
project would be a perfect opportunity.  It's surprising how often databases, 
especially a small and simple one like SQLite come in handy.

Compared to using Excel:
 coding efficiency - no coding needed to use Excel, so Excel wins
 processing speed - your app would almost certainly start up and run quicker 
 than Excel
 memory usage - your app would definitely use less memory than Excel, but does 
 this really matter with only 10 entries?

That's my best shot.

Doug

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Carl Gross
Sent: Tuesday, April 16, 2013 3:28 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] Beginning database question

Hi All,

I'm an amateur programmer who has never worked with databases before.  I
*think* that getting started with SQLite may help with some of my projects, but 
I'm having trouble determining exactly how.  I'm hoping someone in this list 
may be able to point me in the right direction.

My simplified situation is this.  There are 10 discrete heights' and 10 
discrete weights,' and each height/weight combination corresponds to one of two 
'teams.'  All of this information is hardcoded into my program.  My program 
asks a user to enter his own height and weight, and the program will output the 
user's corresponding team based on the hardcoded data.

My SQLite question is:  Can using SQLite instead of Microsoft Excel be 
beneficial in terms of coding efficiency, processing speed, memory usage, or 
any other aspect?  If so, how?

Sorry for the long, broad, vague question.  I'm not looking for someone to tell 
me how to do something.  I'm looking for someone to assure me that SQLite 
(rather than a simple spreadsheet) is right for me, and to hopefully steer me 
towards some documentation that may be beneficial to me.

Thanks,

Carl
___
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] set journal off working witth SQLiteConfig?

[Doug Crites]

Hello,

I am using the sqlite-jdbc-3.7.8-20111025.014814-1.jar , trying to set the 
journaling off on my 'main' only opened db.

SQLiteConfig config = new SQLiteConfig();
config.setJournalMode(SQLiteConfig.JournalMode.OFF);

writeLog(Timestamp properties);
Properties propSqlite = config.toProperties();

When I run,  I see from my properties display that the journaling is off,  but 
I still see a journal file being created when I run it.
{open_mode=6, journal_mode=OFF)

Does anyone have experience with turning this off thru the SqlLiteConfig 
object?  Any tips on usage?
The reason I'm tring this is that we keep running out of memory during the 
processing of a large transaction (about 52,000 updates).  I try changing cache 
size and ournal size limits,  but I'm not sure if it's really taking affect.
If anyone has some tips on settings for large transactions like this, please 
share!

Thanks,
Doug


Doug Crites
Sr Software Engineer
doug.cri...@asg.commailto:doug.cri...@asg.com
The Commons
708 Goodlette Road N
Naples, FL 34102
Tel: 239.435.2293
Fax: 239.213.3501
Toll-Free: 800.932.5536 USA Only
www.asg.comhttp://www.asg.com/
ASG-CloudFactory - The Single Solution to Build, Deploy  Manage your Cloud 
Environments
Click here for more information on the 
ASG-CloudFactoryhttp://www.asg.com/cloudfactory

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

Re: [sqlite] SQLite strong-typing [WAS: inner vs. outer join inconsistency]

[Doug Currie]

On Mar 7, 2013, at 11:07 AM, Ryan Johnson ryan.john...@cs.utoronto.ca wrote:
 
 That does leave the question of what to do with cast ('1.0' as integer), 
 though. Without the prefix-based matching that would now return NULL rather 
 than 1, even though cast(1.0 as integer) would still return 1. Then again, 
 disallowing all floats might be better than the current practice of returning 
 1 from a cast of both '1e-10' and '1e10' (the real-integer casts do the 
 right thing, as does assignment to a column with integer affinity).

Would

  cast(cast(x as real) as integer)

do what you want?

e

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

Re: [sqlite] VC++ and SQLite

[Doug Nebeker]

You might be surprised at the speed increase you see in compile time if
you've got large projects.  The time isn't lost to CPU as much, but disk I/O
time adds up when hitting many hundreds of small (header) files (even with
an SSD).

Doug

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Rob Richardson
Sent: Monday, November 12, 2012 1:33 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] VC++ and SQLite

I always turn pre-compiled headers off for every VC++ project.  In my
opinion, they are artifacts from a time when processors were a few hundred
times slower than they are now.  The benefit in time saved now is far less
than the confusion they cause when something goes wrong.

RobR

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Adam DeVita
Sent: Monday, November 12, 2012 10:49 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] VC++ and SQLite

It isn't VS2010 specific. Even going back to VS6 writing your own C++
wrapper and including the.c file you had to tell it to not use precompiled
headers for that file. (Both Debug and Release builds)

You should tell VS that this file will not ever be using precompiled
headers.

On VS2012 Professional Edition one can:
Right click on the file within VS10, select Properties.
Open the C/C++ tree.
Select Precompiled Headers.
Set Precompiled Header to Not Using Precompiled Headers.


Adam

On Mon, Nov 12, 2012 at 10:17 AM, John Drescher dresche...@gmail.com
wrote:
 I know this question is not a SQLite question, but I am hoping that 
 someone here has had a similar experience and/or can point me to the 
 right place to ask this question.

 After years or using Code::Blocks and Dev-Cpp, I have recently 
 installed Visual Studio 10 Express; it is the first time I am using 
 it, in my Windows
 7 machine.

 I have written, with the help of this mailing list a wrapper class 
 for the latest SQLite3 library using C::B as my development 
 platform, now that I want to switch to VS10, there were a lot of gcc 
 specific code that I had to repair and after clearing all the C++ 
 discrepancies between MinGW's g++ and MS's VC++ I have been left with
this error message:

 fatal error C1853: 'Debug\sql.pch' precompiled header file is from a 
 previous version of the compiler, or the precompiled header is C++ 
 and you are using it from C (or vice versa



 Does anyone know how to resolve this issue or perhaps a VS10 
 specific

 You can like the other poster said disable PCH in visual studio or 
 just delete all the PCH files and have VS rebuild them. The second is 
 what I do in Visual Studio retail versions when I get this error.

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



--
VerifEye Technologies Inc.
905-948-0015x245
151 Whitehall Dr, Unit 2
Markham ON, L3R 9T1
Canada
___
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] Mac development question

[Doug Currie]

On Oct 23, 2012, at 4:58 PM, Igor Korot ikoro...@gmail.com wrote:

 1. I know on Mac I need to build an application bundle. Where do I
 store the .db file relative to the bundle?
 Inside it? Home directory? Somewhere on the hard drive? What is the
 usual place for it?

If the database is read-only, you can sore it inside the bundle.

If it is application configuration, you should store it in the application's 
directory in the user's ~/Library/Application Support/application name

If the database is a document, you should store it wherever the use directs via 
a file dialog.

SQLite itself doesn't care where the database is stored as long as the 
directory it is in is read/write.

 2. When I done on Windows I should be able to just copy the file and
 drop it on the Mac HD, right?

Yes.

e

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

Re: [sqlite] sqlite3 database unreadable on Mountain Lion

[Doug Currie]

On Aug 6, 2012, at 8:26 AM, Simon Slavin slav...@bigfraud.org wrote:

 So either Apple has made a change between versions, or we have different 
 paths.

I use fully qualified pathnames here:

~ e$ /usr/bin/sqlite3  :memory: 'SELECT sqlite_source_id()'
2012-04-03 19:43:07 86b8481be7e7692d14ce762d21bfb69504af
~ e$ /usr/local/bin/sqlite3 :memory: 'SELECT sqlite_source_id()'
2012-05-14 01:41:23 8654aa9540fe9fd210899d83d17f3f407096c004

I never had a pre-release OSX ML installed. I did update /usr/local/bin/sqlite3 
from sqlite.org.

e

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

Re: [sqlite] sqlite3 database unreadable on Mountain Lion

[Doug Currie]

On Aug 6, 2012, at 4:51 PM, Simon Slavin slav...@bigfraud.org wrote:

 On 6 Aug 2012, at 7:48pm, Doug Currie doug.cur...@gmail.com wrote:
 
 ~ e$ /usr/local/bin/sqlite3 :memory: 'SELECT sqlite_source_id()'
 2012-05-14 01:41:23 8654aa9540fe9fd210899d83d17f3f407096c004
 
 I think this copy has been installed by something else.

Yes, I installed it.

  I don't think it comes with Apple's distribution of Mountain Lion.  If this 
 is the file which is being executed by default (in other words, if that's the 
 file reported by the command 'which sqlite3' on your system) then this may be 
 the cause of your problem.

I don't have a problem, Tobias does, and I suspect it is because the ML version 
of sqlite3 in /usr/bin (2012-04-03) predates the 3.7.12 release, and has the 
bug Dan recalls.

e

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

Re: [sqlite] sqlite3 database unreadable on Mountain Lion

[Doug Currie]

On Aug 3, 2012, at 2:33 PM, Dan Kennedy danielk1...@gmail.com wrote:

 There was a problem similar to your description at one point, but
 it should have been fixed before the 3.7.12 release. What do you
 get from the shell command SELECT sqlite_source_id(); on
 Mountain Lion?

e$ /usr/bin/sqlite3
SQLite version 3.7.12 2012-04-03 19:43:07
Enter .help for instructions
Enter SQL statements terminated with a ;
sqlite SELECT sqlite_source_id();
2012-04-03 19:43:07 86b8481be7e7692d14ce762d21bfb69504af


e


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

Re: [sqlite] sqlite3 database unreadable on Mountain Lion

[Doug Currie]

On Aug 3, 2012, at 3:32 PM, Tobias Giesen tobiasgie...@gmail.com wrote:

 Apparently Apple prevents starting other versions of it and redirects 
 everything to
 their current version in /usr/bin.

On ML here I can launch my version in /user/local/bin just fine.

e$ which sqlite3
/usr/local/bin/sqlite3
e$ sqlite3
SQLite version 3.7.12 2012-05-14 01:41:23
Enter .help for instructions
Enter SQL statements terminated with a ;
sqlite SELECT sqlite_source_id();
2012-05-14 01:41:23 8654aa9540fe9fd210899d83d17f3f407096c004
sqlite .exit
e$ uname -mprsv
Darwin 12.0.0 Darwin Kernel Version 12.0.0: Sun Jun 24 23:00:16 PDT 2012; 
root:xnu-2050.7.9~1/RELEASE_X86_64 x86_64 i386

e


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

[sqlite] leap seconds

[Doug Currie]

The SQLite3 date  time functions are designed assuming

 […] that every day is exactly 86400 seconds in duration.

Before I start implementing TAI (or GPS time) to/from UTC translator plugin, 
has anyone already done this?

Why? In a device that logs data with sub-second resolution, in my case a 
medical device, timestamps need to account for leap seconds, and support 
translation to/from UTC (and local time) for human readability.

-- e

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

Re: [sqlite] how to build sqlite4 (four)?

[Doug Currie]

On Jun 28, 2012, at 4:05 PM, Nico Williams wrote:

 It's also possibly a good idea to just not have autoincrement.  Let
 the application implement it, no?  After all, it can, including via
 triggers.

Or with PostgreSQL-style sequences

http://www.postgresql.org/docs/9.1/static/sql-createsequence.html

(and maybe SERIAL 
http://www.postgresql.org/docs/9.1/static/datatype-numeric.html#DATATYPE-SERIAL 
)

e

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

Re: [sqlite] sqlite time is 2 hours to late

[Doug Nebeker]

  UTC is the right time.  If you're doing anything with dates and
  times I would STRONGLY recommend that all recorded times are in UTC.

Jay is right.  I've been bitten by storing local times before.  Even if your
users
are in the same time zone, that time zone shifts with day light savings.  It
was a 
painful lesson.  Always store times in UTC.

Doug


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

Re: [sqlite] VERY weird rounding error

[Doug Currie]

On Jun 17, 2012, at 12:23 PM, Keith Medcalf wrote:

 SQLITE_SIGNIFICANT_DIGITS defaults to 14, but you can override it.  No matter 
 what is requested, the maximum number of significant digits is limited to the 
 specification, and rounding is applied to the remaining bits of the 
 significand, to round to the specified number of significant digits. 

FYI, the venerable approach:

http://kurtstephens.com/files/p372-steele.pdf

ftp://ftp.ccs.neu.edu/pub/people/will/retrospective.pdf

http://www.cs.washington.edu/education/courses/cse590p/590k_02au/print-fp.pdf

http://www.cesura17.net/~will/Professional/Research/Papers/howtoread.pdf

e

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

Re: [sqlite] Using SQL or C for data analysis?

[Doug Currie]

On Mar 27, 2012, at 3:46 PM, Larry Brasfield wrote:

 A DBMS is a good way to keep your raw data.  But I highly doubt that a 
 majority of your analysis algorithms are going to be expressible in SQL 
 without going way beyond the intended purpose of the language.  You will 
 either find yourself limiting the analyses to what is convenient to express 
 in SQL, or you will spend much more time writing queries than you would spend 
 describing your data processing in a form more suited to functions.  

Yes

 […]  I expect you would find a signal processing library, such as can be 
 found in Matlab, Octave, or Scilab, to be a much better start than what you 
 might write in SQL in reasonable time.

Or use a Statistical Computing language and environment such as R with SQLite

http://www.r-project.org/

http://cran.r-project.org/web/packages/RSQLite/index.html


e

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

Re: [sqlite] SQLite

[Doug Currie]

On Nov 9, 2011, at 11:39 PM, Bhautik Kothadia wrote:

 Is there any Operating System Required for that?

See: http://www.sqlite.org/custombuild.html

especially section 5.0 Porting SQLite To A New Operating System

 If not then How much Memory is required?

See: http://www.sqlite.org/malloc.html

e

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

Re: [sqlite] SQLite

[Doug Currie]

The PIC32MX664F064L has
 64 KiB Program Memory Size
 32 KiB RAM

SQLite as it presently stands will not fit within these constraints.

e

On Nov 9, 2011, at 7:47 AM, Parthiv Shah wrote:

 Respected Sir,
 
 We want to use DB SQLite in our product.
 
 We are using PIC32MX664F064L microcontroller from microchip.
 
 Is it possible to embedded SQLite into it?
 
 Do we need any Operating system for SQLite ?
 
 Product is data acquisition system. 
 
 For data storage we are using SD Card.
 
 We are using FAT32 file system.
 
 Please guide us how we can test it?
 
 For more information about us, please visit our website:
 www.promptsoftech.com
 
 Best Regards
 Parthiv Shah
 
 Prompt Softech
 Ahmedabad
 India
 
 
 ___
 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] triggers : NEW keyword with multiple tables

[Doug Currie]

On Oct 25, 2011, at 10:59 AM, Sébastien Escudier wrote:

 CREATE TRIGGER my_trigger INSTEAD OF INSERT ON my_view
 BEGIN
 INSERT INTO table1(type) VALUES(NEW.table1.type);
 INSERT INTO table2(type) VALUES(NEW.table2.type);
 END;
 
 ...
 
 Why this syntax does not work anymore ?

You haven't given the view explicit column names, and the ones SQLite3 invents 
are arbitrary; try this instead:

CREATE VIEW my_view AS SELECT table1.type as table1_type, table2.type as 
table2_type FROM 

…

CREATE TRIGGER my_trigger INSTEAD OF INSERT ON my_view
BEGIN
INSERT INTO table1(type) VALUES(NEW.table1_type);
INSERT INTO table2(type) VALUES(NEW.table2_type);
END;


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

Re: [sqlite] Using modifiers in julianday function

[Doug Currie]

On Oct 24, 2011, at 11:07 AM, Dilip Ranganathan wrote:

 But as you all know, this doesn't work:
 
 select datetime(time) from table where time =
 julianday(datetime(max(time)),'-2 hour','localtime') order by time desc

Try replacing datetime(max(time)) with (select datetime(max(time)) from table)

as in 

sqlite select datetime(time) from table
   ... where time =
   ... julianday((select datetime(max(time)) from t),'-2 hour','localtime') 
order by time desc;
2011-10-24 15:43:45
2011-10-24 15:43:39
sqlite 


e

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

Re: [sqlite] MC/DC coverage explained wrong in the home page?

[Doug Currie]

On Sep 23, 2011, at 9:17 PM, Richard Hipp wrote:

 paper above completely ignores this issue.  It is as if the authors had
 never heard of short-circuit evaluation.  Or, perhaps they are familiar with
 the problem but could not reach agreement on its solution so simply didn't
 bring it up.

Another way to look at a short-circuit evaluation is that it does not represent 
a Boolean expression at all. It represents a control statement.

A  B = { if A then return B else return true }

A || B = { if A then return true else return B }

e

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

Re: [sqlite] SQLite + unicode

[Doug Currie]

On Aug 10, 2011, at 12:39 PM, NOCaut wrote:

 I work in VS2008 c++ 
 i create data base my.db and wont use U N I C O D E function from this DLL 
 i find class or unit for connect to my base from VS2008
 http://sqlite.org/download.html - this link help me?
 
 you understand me?

No, but maybe these links will help...

http://www.sqlite.org/faq.html#q18

http://old.nabble.com/enable-ICU-in-SQLite-on-windows-platform-td27371403.html

http://www.urban-eye.com/pagesqliteicu.html

http://site.icu-project.org/

e

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

Re: [sqlite] c-api

[Doug Currie]

On Jul 27, 2011, at 9:22 AM, Baruch Burstein wrote:

 Is there an easier way to get a single value (for instance select
 last_insert_rowid(); ) then prepare - step - column - finalize?

http://www.sqlite.org/capi3ref.html#sqlite3_last_insert_rowid

e


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

Re: [sqlite] Handle leak using IIS on windows 7?

[Doug]

This was exactly the problem; I didn't realize the 'static' variables are
persisted between page views in ASP.Net

Adding an '_instance = null;' fixed the issue.

Thanks muchly.

Cheers,
Doug.

On Tue, Jul 19, 2011 at 11:58 AM, Joe Mistachkin sql...@mistachkin.comwrote:


 After reading the code, I noticed the following:

 1. From the static Dump method, an instance of the DbLogger class is
 created via the static Get method and stored in the _instance static
 variable.

 2. The connection itself is opened in the constructor for the DbLogger
 class via the InitDb method.

 3. Prior to returning a result, the Dump method closes the connection
 and sets the _connection instance variable to null.

 4. The second time the Dump method is executed, the existing instance
 of the DbLogger class will be used (i.e. the one stored in the static
 _instance variable).

 5. This existing instance of the DbLogger class no longer has a valid
 connection because it was previously closed (and set to null).

 6. Newly created commands will not have a valid connection.

 7. Attempting to execute a command without a valid connection will
 result in the exception you are seeing.

 --
 Joe Mistachkin

 ___
 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] Handle leak using IIS on windows 7?

[Doug]

Hi there,

I've searched around and found a few threads like this:
http://sqlite.phxsoftware.com/forums/t/2480.aspx

Basically, I have the same issue.

When access the sqlite database via a  website (MVC3 running on IIS) the
first time, sqlite works fine.

I properly call connections.Close() when I'm done...

And the next time I try to access it I get:
System.InvalidOperationException: No connection associated with this command

Manually stopping the dev web server, or restarting the iis application pool
fixes this for one more page view.

It seems like the IIS config is leaving the process hanging around, and
after calling close there (I guess) must be some handle which is being kept
and keeping a reference to the database, preventing anything else from
accessing it.

Seeing as how this has happened to a few people, I was hoping someone here
had seen this before and had a solution?

I'm using the Precompiled Binaries for 32-bit Windows (.NET Framework 4.0)
from http://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki(32
bit mode enabled on iis), but I've tried the 64-bit version with the
same result.

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

Re: [sqlite] Handle leak using IIS on windows 7?

[Doug]

Hm... I'm not doing anything fancy.

I've attached the tiny sqlite logging class below. It's called in an MVC app
simply by invoking:

@Html.Raw(Doug.Utils.Web.DbLogger.Dump())

Yes, it uses transactions; is that a bad thing?

I'm pretty sure the issue is something to do with file handles. For the same
reason after loading the page (from an IIS server) and then closing the
page, waiting 1 minute or two and then attempting to remove the db.sqlite
file, I get an error, file is in use.

(Obviously, if you use File.Open() without a File.Close() this does not
happen; when the page view ends the file handle is automatically released).

If you want to see it in action, create a new MVC project and add:
DbLogger.Get();

To the home index page. Run it in debug mode and you'll see the issue.

Cheers,
Doug.

code (in case the attachment fails):

using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Web;
using System.Data.SQLite;
using System.IO;

namespace Doug.Utils.Web
{
public class DbLoggerRecord
{
public String Context { get; set; }
public String Message { get; set; }
public DateTime Created { get; set; }
}

public class DbLogger
{
private static DbLogger _instance = null;

/// summary
/// Where to store the logging database.
/// /summary
private const string relativeDbPath = ~/App_Data/DbLogger.sqlite;

private SQLiteConnection _connection = null;

private SQLiteTransaction _transaction = null;

public DbLogger()
{
var dbPath = Path.GetTempFileName();
if (HttpContext.Current != null)
dbPath = HttpContext.Current.Server.MapPath(relativeDbPath);
_connection = InitDb(dbPath);
}

private SQLiteConnection InitDb(String dbPath)
{
bool init = false;
if (!File.Exists(dbPath))
{
SQLiteConnection.CreateFile(dbPath);
init = true;
}
var rtn = new SQLiteConnection(Data Source=+dbPath);
rtn.Open();

// Pragma or this doesn't work in app_data folder.
SQLiteCommand cmd = new SQLiteCommand();
cmd.Connection = rtn;
string pragma = PRAGMA journal_mode = OFF;
cmd.CommandText = pragma;
cmd.ExecuteNonQuery();

// Check if we realy neeed to init?
if (!init) {
try
{
GetRecords(1);
}
catch(Exception)
{
try
{
File.Delete(dbPath);
init = true;
}
catch(Exception)
{
}
}
}

if (init)
CreateTable(rtn);

return rtn;
}

private void CreateTable(SQLiteConnection c)
{
var createTable = new StringBuilder();
createTable.Append(CREATE TABLE Log ();
createTable.Append(  Id PRIMARY KEY,);
createTable.Append(  Context TEXT,);
createTable.Append(  Message TEXT,);
createTable.Append(  Created TEXT));
var cmd = createTable.ToString();
var sqlCmd = new SQLiteCommand(c);
sqlCmd.CommandText = cmd;
try
{
sqlCmd.ExecuteNonQuery();
}
catch(Exception)
{
}
}

public void Trace(String context, String message)
{
if (_transaction == null)
_transaction = _connection.BeginTransaction();

var insertMsg = new StringBuilder();
insertMsg.Append(INSERT INTO Log (Context, Message, Created)
VALUES (@Context, @Message, @Created));
var cmd = insertMsg.ToString();
var sqlCmd = new SQLiteCommand(_connection);
sqlCmd.CommandText = cmd;

// Params
sqlCmd.Parameters.AddWithValue(@Context, context);
sqlCmd.Parameters.AddWithValue(@Message, message);
sqlCmd.Parameters.AddWithValue(@Created,
DateTime.Now.ToString());

sqlCmd.ExecuteNonQuery();
}

public void Close() {
if (_connection != null)
{
_connection.Close();
_connection = null;
}
}

public IEnumerableDbLoggerRecord GetRecords(int limit)
{
Commit();
var rtn = new ListDbLoggerRecord();

string cmd = SELECT * FROM Log ORDER BY Id ASC LIMIT  + limit;
var sqlCmd = new SQLiteCommand(_connection);
sqlCmd.CommandText = cmd;

SQLiteDataReader reader = sqlCmd.ExecuteReader();

while

Re: [sqlite] OSX path

[Doug Currie]

On Jun 17, 2011, at 2:56 PM, john darnell wrote:

 I am attempting to open an SQLite database on the Mac (OSX Snow Leopard) and 
 am getting an error.  This is the code I am using:
 
   char  DBEnginePath[1000];
 
   strcpy(DBEnginePath, Macintosh HD:Applications:Adobe InDesign 
 CS5:Plug-Ins:WPC_ID:IndexData.db);
   fprintf(stderr, %s\n, DBEnginePath);  
   //  Sends correct path to stderr for verification.
   Result = sqlite3_open_v2(DBEnginePath, db_ptr, SQLITE_OPEN_READONLY, 
 NULL);  //  Errors out here.

Your path has colons instead of slashes for separators.

Open a Terminal window, and drag the database file into the window. The 
terminal.app will display the path name on the command line. You can copy and 
paste from there. It's probably something like:

/Applications/Adobe\ InDesign\ CS5/Plug-Ins/WPC_ID/IndexData.db

e

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

Re: [sqlite] [BUG] Silent change in intergeral overflow handling in 3.7.6

[Doug Currie]

On May 26, 2011, at 2:54 AM, Jan Hudec wrote:
 Gotcha! No, it's not. -1-x is equivalent, but -x-1 is not:
 
sqlite select -1-(163), -(163)-1;
9223372036854775807|9.22337203685478e+18
 
 Besides my point was not that it's not possible, but that it would
 be more readable with dedicated operator.

Yes.

The fact that a negative number (-1) minus a large positive number (1  63) 
results in a positive number does not seem to be in concert with the goal of 
handling arithmetic overflows sensibly. 

This is especially egregious in the second case where the result of negating a 
large positive number and subtracting one is positive AND REAL (double float). 

Ideally SQLite would guarantee one of (in my order of preference):

1) Integer operations that overflow 64 bits behave as wrapped twos complement, 
i.e., they return the low 64 bits of the infinite precision twos complement 
integer result

2) Integer operations that overflow 64 bits result in floating point values 
that approximate the result with the precision of IEEE double 

3) Integer operations that overflow have no guaranteed result

I think option 2 is what SQLite is supposed to do (per the release notes), but 
is failing in both cases of this example.

e

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

Re: [sqlite] SQLite Explorer (singular) is missing the STDEV function (standard deviation)

[Doug Currie]

On Mar 31, 2011, at 2:27 PM, Mike Rychener wrote:

 I have tried the latest Explorer and it gets a syntax error on STDEV.  
 However, that function works in Eclipse just fine, to take the standard 
 deviation of a column (like min, max, avg).  Is there a workaround or 
 other fix available?  

See http://www.sqlite.org/contrib  extension-functions.c 

e

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

[sqlite] Update trigger

[Sutter, Doug]

Hi,

 

I am trying to create a trigger that will log changes made to my
database.  I need to log only the columns that were updated (not the
entire record).  So I need to be able to log the column name, old value,
new value and date/time.  I also need to know which row was updated
(identified by the primary key name and value).

 

I know how to create a unique trigger for each column where I hard-code
the column's name as shown below.  But I would like to create a trigger
that would fire when any column is updated and log the specific details
for that column only. 

 

CREATE TRIGGER config_update_log AFTER UPDATE OF hi_flow_rate ON
config_table

BEGIN

INSERT INTO audit_log (audit.name, audit.new_value, audit.old_value,
audit.date_time) VALUES ('High Flow Rate', new.hi_flow_rate,
old.hi_flow_rate, datetime('now','localtime'));

END;

 

I've seen examples where old and new values for all columns were logged
but not where only changed columns were logged.

 

Thanks,

Doug

 

 

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

Re: [sqlite] SQLite.so dynamic library-linux

[Doug]

 and buying a load of hardware isn't really viable

I can't help with the .so, but you could load up a virtual machine with one
of the free VM engines (VMWare Server is free for example -- I'm sure
Microsoft must have one too) and put Linux on it.  That would give you the
benefit of being able to test one of your targets too.

Doug

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Udon Shaun
Sent: Friday, March 18, 2011 11:47 PM
To: Pavel Ivanov; SQLite
Subject: Re: [sqlite] SQLite.so dynamic library-linux

@Pavel
Apologies. I just hit reply and thought it would have gone to the right
place.

 A 3rd party company doesn't compile my code. It's an API interface to the
SQLite library so people can use SQLite in LabVIEW. I don't have linux
(never had linux) and buying a load of hardware isn't really viable just to
compile 1 SO in an open source distribution.

But to answer your question. more fully.LabVIEW is cross platform (MAC,
Win,VXWorks  Linux). Therefore the API I provide can (and does) work under
all those systems (and ,many users have been using it in those OSs for some
time) - you just need the SQLite library compiled for the particular OS. MAC
comes with it already. I can already compile for Windows and VXWorks, so
that just leaves Linux (which I used to download from the SQLite site).

So I'm posting under this forum to try and get the last OS library (linux)
and find some help in cross-compiling the SO in windows so that I can
support it myself for future updates seeing as it's no longer provided. If
it was still posted on the SQLite site (as it was prior to 3.7.4) , I
wouldn't be here aslking!. The alternative is that I just drop linux. But I
at least want to make the effort for my 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] SQLite server

[Doug]

This is where I think the problems will crop up.  Can you imagine making a
network round trip for each row fetch, 
and then for each column fetch of each row (sqlite3_column_type,
sqlite3_column_double, etc).

To avoid that, you would need to fetch a lot (buffer) of data and bring it
back to the client.  And if the response is
large, will you page, or keep the transaction open on the server thus
blocking all other clients?

The devil is always in the details :)

Doug

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Sylvain Pointeau
Sent: Wednesday, December 22, 2010 7:51 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] SQLite server

Why not doing it with DCOM or Corba or what ever even the sockets?
but hidden behind the same API of SQLite. The real sqlite lib will be on
the server.
is it called remote procedure call?
___
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] First(s) select are very slow

[Doug]

Possibly because the OS file cache doesn't have any of your database file 
loaded into memory yet (cold start), so those first selects are experiencing 
I/O hits.  After a number of them have been done, some/much of the file might 
be in OS file cache memory, and selects are then hitting memory instead of the 
much slower disk.

Doug

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Vander Clock Stephane
Sent: Monday, December 20, 2010 8:43 AM
To: General Discussion of SQLite Database
Subject: [sqlite] First(s) select are very slow

hello,

why, on a big table (+5 000 000 rows with RTREE ndex) at the beginning, the 
select is very slow to return (2-5 secondes), but after 1000 selects, it's 
start to return immediatly (10 ms) ??

thanks by advance
st phane
___
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] First(s) select are very slow

[Doug]

Adding to what Simon said, even the SQLite cache has to get filled initially
as well.  So those very first hits to the database are always the most
expensive.  Once commonly used pages (index pages?) are loaded, you're
running closer to memory speed than disk speed.

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Simon Slavin
Sent: Monday, December 20, 2010 9:55 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] First(s) select are very slow


On 20 Dec 2010, at 5:33pm, Vander Clock Stephane wrote:

 so what the difference between the sqlite3 cache and the OS cache ?

SQLite caches a number of database pages.

Your operating system has its own caching.  This depends on which OS you're
using, and how it thinks it's addressing your data.  For further details, do
research on a site of the company that provides your OS.

Your computer hardware, also, has many levels of caching: your hard disk
drive probably has onboard caching; your motherboard has a cache; your
processor chip also has caching.

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


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

Re: [sqlite] Windows performance problems associated with malloc()

[Doug]

I wonder if HeapSetInformation (which can enable a low-fragmentation heap)
would be helpful too.  You can set it on the process
and the CRT heaps.  Note that it's not available in Win2K and earlier.

Doug

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Marcus Grimm
Sent: Friday, December 17, 2010 9:21 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Windows performance problems associated with malloc()

 An SQLite user has brought to our attention a performance issue in 
 SQLite that seems to be associated with malloc().  If you have  
 insights or corroborating experience with this issue please let me know.

We recently had a malloc/free slowdown issue after changing to VS2008 in
combination with XP.
Not sure if it applies in your case but for us this helps:
--
if( _get_sbh_threshold()  512 )
{
  _set_sbh_threshold(512);
}
---

I'm unable to run your sqlite3.exe: MSVCR100.dll no found.

Anyway, maybe the above helps.

KInd regards

Marcus


 SQLite supports a zero-malloc option (see
 http://www.sqlite.org/malloc.html#memsys5 for details) which uses its 
 own internal memory allocator rather than system malloc().  Earlier 
 today, we patched the command-line shell to allow the zero-malloc 
 option to be turned on.  If you do:

  sqlite3 DATABASE

 then the regular system memory allocator is used, but if you say:

  sqlite3 -heap 100M DATABASE

 then the MEMSYS5 memory allocator will be used with a pool of 100MB of 
 memory to work with.  (You can adjust the size of your memory pool for 
 whatever you need.)

 There are win32 and win64 builds of this updated command-line shell 
 compiled using vs2010 here:

  http://www.sqlite.org/draft/download.html

 For certain full-text search queries against a large database, we are 
 seeing speeds which are 3x faster when using -heap 300M (the memsys5 
 memory
 allocator) versus omitting the -heap option and thus using system 
 malloc().
 This is on windows7.  Similar results are seen with both gcc and 
 vs2010 builds.

 If you have any large queries that you can run on windows using the 
 command-line shell, I would appreciate you timing those queries using 
 the new shells from the download page, both with -heap 300M and 
 without it, and letting me know about any performance differences you see.

 I also observe that compiling for 64-bit using vs2010 (not an option 
 with my ancient version 2.95.3 gcc cross-compiler) that the queries 
 are an additional 2x faster.  I was surprised at the dramatic 
 performance increase in going from 32-bit to 64-bit.  Is such a 
 speed-up typical?

 The use of -heap 300M seems to not make any performance difference 
 on Linux.

 Any insights into why this is, what we are doing wrong, or what we can 
 do to improve the performance of malloc() on windows will be 
 appreciated.

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



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


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

Re: [sqlite] assert crash in wal

[Doug]

From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Richard Hipp
The underlying error here is that you are attempting to use threads in the
first place.  You should never do that.  Threads are evil and should be
avoided wherever possible.  Use separate processes  for concurrency.
Threads in application programs always result in subtle bugs (such as this
one) that are hard to reproduce and waste countless hours of developer time.
Just say no to threads.

Richard, you've said this many times on this list.  Wouldn't using separate
processes hurt performance significantly?  It means you can't ever share the
page cache (either by sharing an SQLite connection between threads in a
serial way protected by mutex, etc, or by actually using shared cache
semantics).  And getting concurrency with processes means you introduce the
complexities of interprocess communication/synchronization which is much
easier to handle with threads in the same process.  

But you are right about the costs -- the benefits of using threads incur a
cost -- a cost of being very careful.

Doug



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

Re: [sqlite] Backup-restore behaviour

[Doug Currie]

On Dec 7, 2010, at 10:49 AM, Csom Gyula wrote:

 It clarified the situation, that is backup-restore seems to be the best 
 choice:) Just one more question. As you put backup-restore is based upon data 
 pages (that could be binary a format I guess) not on plain SQL/data records. 
 After all: Is the data page/backup format platform indenpendent? For instance 
 can I restore a database on Windows from a backup created on a Linux box?

If your Linux is on ARM, you should pay attention to the 
SQLITE_MIXED_ENDIAN_64BIT_FLOAT setting for binary compatibility with Windows 
(or x86 Linux for that matter).

e

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

Re: [sqlite] Just compiled SQLite in Visual Studio

[Doug]

Igore didn't mean there is no difference between C and C++.  He was just
saying a 'project' isn't C or C++.  In other words, C and C++ have different
(though similar) compiler rules, syntax, etc.  By default, the compiler will
compile a '.c' file using the C rules, and a '.cpp' file with the C++ rules.
And you can mix .c and .cpp files in the same project.

As for what the difference is?  That's a big question.  Objects exist in
C++, and they don't in C.  That's the tip of the iceberg.

Doug

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Bob Keeland
Sent: Tuesday, November 30, 2010 5:02 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Just compiled SQLite in Visual Studio

OK then I have a newbee question that is actually out of the scope of
SQLite. If the only difference between C and C++ is the file extension, then
what is the difference between C and C++? I'm thinking of adding a language
other than the Visual Basic that I kind of know and would like to know the
difference. I've been thinking about Java, but am not sure yet.
Bob Keeland

--- On Tue, 11/30/10, john darnell john.darn...@walsworth.com wrote:


From: john darnell john.darn...@walsworth.com
Subject: Re: [sqlite] Just compiled SQLite in Visual Studio
To: General Discussion of SQLite Database sqlite-users@sqlite.org
Date: Tuesday, November 30, 2010, 10:25 AM


Thanks Igor.

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Igor Tandetnik
Sent: Monday, November 29, 2010 7:11 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Just compiled SQLite in Visual Studio

john darnell john.darn...@walsworth.com wrote:
 I just added it to a Visual Studio 8 project, turned off the use of 
 precompiled headers (the project is a C++ project) and compiled the
SQLite.c file without any errors.

There is no such thing as a C++ project. A project in Visual Studio can
happily contain both C and C++ files. By default, file extension determines
whether the file is compiled with C or C++ compiler (.c would indicate C),
and this could also be overridden in project settings on a per-file basis.
--
Igor Tandetnik

___
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

__ Information from ESET NOD32 Antivirus, version of virus signature
database 5662 (20101130) __

The message was checked by ESET NOD32 Antivirus.

http://www.eset.com





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

Re: [sqlite] Some floats of 15 digits or less do not round-trip

[Doug Currie]

On Nov 29, 2010, at 9:37 AM, Rick Regan wrote:

 For IEEE 754 double-precision numbers and 64-bit integers roughly
 99.4% of all numbers can be processed efficiently. The remaining 0.6% are
 rejected and need to be printed by a slower complete algorithm.
 
  Hmmm. What's involved in the slower complete algorithm -- bignums?!

That quote refers to Grisu3 which produces the shortest string or else 
indicates failure, in which case Dragon4 or something similar using bignums is 
needed. But for Grisu2, no bignums are needed, and a correct string is always 
returned, though it is not the shortest string in 0.4% of cases. The Grisu2 
strings do round trip correctly, which I think is where this thread started.

e

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

Re: [sqlite] Some floats of 15 digits or less do not round-trip

[Doug Currie]

On Nov 28, 2010, at 11:18 AM, Rick Regan wrote:

 Michael,
 Thanks for the very thorough analysis.

This is a difficult problem; fortunately it was solved 20 years ago...

http://www.mail-archive.com/sqlite-users@sqlite.org/msg09529.html

e

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

Re: [sqlite] Some floats of 15 digits or less do not round-trip

[Doug Currie]

On Nov 28, 2010, at 5:37 PM, Rick Regan wrote:

 On Sun, Nov 28, 2010 at 4:01 PM, Doug Currie doug.cur...@gmail.com wrote:
 
 On Nov 28, 2010, at 11:18 AM, Rick Regan wrote:
 
 Michael,
 Thanks for the very thorough analysis.
 
 This is a difficult problem; fortunately it was solved 20 years ago...
 
 Well, it's not solved on Windows. :)

There is a new publication on this subject that may be of interest to those 
looking at providing solutions:

http://portal.acm.org/citation.cfm?id=1806623

It (Grisu2) works without bignums if you are willing to settle for the shortest 
string in 99.8% of cases, and an accurate but not shortest string in the 
remaining cases.

e

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

Re: [sqlite] Some floats of 15 digits or less do not round-trip

[Doug Currie]

On Nov 28, 2010, at 6:19 PM, Rick Regan wrote:

 On Sun, Nov 28, 2010 at 5:52 PM, Doug Currie doug.cur...@gmail.com wrote:
 
 
 There is a new publication on this subject that may be of interest to those
 looking at providing solutions:
 
 http://portal.acm.org/citation.cfm?id=1806623
 
 It (Grisu2) works without bignums if you are willing to settle for the
 shortest string in 99.8% of cases, and an accurate but not shortest string
 in the remaining cases.
 
 
 Thanks for the reference. I wonder how it compares to David Gay's dtoa.c?

The paper compares the performance of sprintf from glibc 2.11 and Grisu. I 
don't know if glibc sprintf is based on Gay's code; at one point I thought it 
was, but I cannot find an authoritative reference.

In any case, the Grisus benchmarked about 5x to 10x faster than sprintf on 
average for random double inputs. The code should be simpler than dtoa.c since 
there are no bignums involved, and fewer special cases.

Anyone still reading this thread might be interested in the directed rounding 
mode subtleties discovered by Rick Regan: 
http://www.exploringbinary.com/incorrect-directed-conversions-in-david-gays-strtod/

e

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

Re: [sqlite] Scaling of Cache

[Doug]

I'm not going to pretend to understand the SQLite source, but it seems
like having a mutex per PCache1 (ie the param passed in to pcache1Fetch 
and other cache functions) would be a good approach instead of the global
mutex.  But that approach wasn't taken, and I've found everything to be 
very well thought out in SQLite so I'm wondering if anyone can comment 
on the why's.

Thanks
Doug


 -Original Message-
 From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
 boun...@sqlite.org] On Behalf Of Pavel Ivanov
 Sent: Monday, October 18, 2010 2:15 PM
 To: General Discussion of SQLite Database
 Subject: Re: [sqlite] Scaling of Cache
 
 Jeff,
 
 I can agree that on Windows mutex performance can be awful especially
 in such frequently called place as pcache1Fetch. So you have only two
 options to solve the problem:
 1) Split threads into different processes - make it one thread per
 process.
 2) Make your own implementation of pcache. You can probably just take
 SQLite's implementation and change usage of mutex to spin lock for
 example, or make it work with TLS to make threads completely
 independent. In latter case you have to be sure that each connection
 is used solely in one thread though.
 
 
 Pavel
 
 On Mon, Oct 18, 2010 at 1:24 PM, Powell, Jeff jeff.pow...@sap.com
 wrote:
  I did some profiling of our current application through the Intel
 Parallel Studio tools, and it identified the mutex in pcache1Fetch as
 the primary source of waits.  Each thread acts on its own, sharing
 nothing with the other threads, so I would expect that there is zero
 waiting.
 
  -Jeff
 
  -Original Message-
  From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
 boun...@sqlite.org] On Behalf Of Pavel Ivanov
  Sent: Monday, October 18, 2010 12:03 PM
  To: General Discussion of SQLite Database
  Subject: Re: [sqlite] Scaling of Cache
 
  In pcache1Fetch, sqlite mutexes around the cache handling, which
 appears to be causing significant waits/scalability issues in my
 application.  If I disable this mutex, the application crashes.
 
  Why do you think that this mutex causes significant waits?
  Anyway ...
 
  Is it possible to cache on a thread-by-thread basis such that no
 mutexes are necessary?
 
  You can make your own pcache implementation which will work
  independently between threads and thus probably consume a lot more
  memory than it is now.
 
 
  Pavel
 
  On Mon, Oct 18, 2010 at 12:25 PM, Powell, Jeff jeff.pow...@sap.com
 wrote:
  I'm seeing some scaling issues (which I'm hoping someone else has
 encountered before).  I have an application which utilizes multiple
 threads, each of which never writes to the database, and none of which
 shares its cache.
 
  In pcache1Fetch, sqlite mutexes around the cache handling, which
 appears to be causing significant waits/scalability issues in my
 application.  If I disable this mutex, the application crashes.
 
  Is it possible to cache on a thread-by-thread basis such that no
 mutexes are necessary?
 
  -Jeff
 
 
  ___
  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] Run Time Error #1 in VS2008

[Doug]

I use SQLite3 directly from a number of VS2008 projects and have never seen
that issue.  

Can you find the line of code causing the problem?

 
 I am working on a project in VS2008 and I am including the sqlite3 code
 directly (compared to in the past using wrappers).  The program is
 working
 great  (accessing DB, using calls, etc) but I have hit a peculiar issue
 during runtime.  I am getting an error from VS that is:
 
 
 Run-Time Check Failure #1 - A cast to a smaller data type has caused a
 loss of data.  If this was intentional, you should mask the source of
 the
 cast with the appropriate bitmask.  For example:
 
   char c = (i  0xFF);
 
 Changing the code in this way will not affect the quality of the
 resulting
 optimized code.
 
 
 
 Is this a common Multibyte versus Unicode error issue?
 
 Thanks,
 
 K.
 
 
 ___
 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] C++ Unresolved external errors from DLL

[Doug]

What command line are you using with lib?

I usually run the following:
link /lib /def:sqlite3.def

and create a .def file with all the functions that I want to call.  

An example of the .def file contents:
EXPORTS
sqlite3_aggregate_context
sqlite3_aggregate_count
sqlite3_auto_extension
sqlite3_bind_blob
sqlite3_bind_double
sqlite3_bind_int
sqlite3_bind_int64
sqlite3_bind_null
sqlite3_bind_parameter_count
sqlite3_exec
sqlite3_open
...

That's always worked for me.

Doug


 -Original Message-
 From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
 boun...@sqlite.org] On Behalf Of Michael Pateras
 Sent: Saturday, October 09, 2010 1:24 AM
 To: sqlite-users@sqlite.org
 Subject: [sqlite] C++ Unresolved external errors from DLL
 
 I'm trying to get SQLite working in my C++ project via a DLL, but Im
 getting unresolved external symbol errors.
 
 I downloaded SQLiteDLL-3 from the download page, extracted its contents
 (a
 DLL and a .h file), and ran lib.exe on it to produce a .lib file. I
 then set
 the directory containing the .lib and the .dll files to be an
 Additional
 Library Directory, in the project settings, under Linker  General.
 Then I
 downloaded SQLiteSource-3 from the download page, and extracted the
 SQLite3.h file to the directory with the .Lib and .DLL files, and added
 that
 directory as an Additional Include Directory under C/C++  General. I
 added
 #include to my main file, and then added sqlite3.lib as an Additional
 Dependency in Linker  Input.
 
 When I try to compile, I get these errors:
 
 error LNK2019: unresolved external symbol _sqlite3_exec referenced in
 function _main
 
  error LNK2019: unresolved external symbol _sqlite3_open referenced in
 function _main
 
 fatal error LNK1120: 2 unresolved externals
 
 Can somebody point me in the right direction to figuring out why this
 is
 happening, and what I can do to resolve it?
 
 Thank you.
 ___
 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] Query suggestion?

[Doug]

I'm wondering if the SQL gurus here can give me some direction.  I have a
very simple stats table:

CREATE TABLE StatData
(
StatID INTEGER NOT NULL,
Value REAL NOT NULL,
Date INTEGER NOT NULL
);

I'd like to pull out the most recent date and associated value for each
StatID.

I initially thought of:

SELECT StatID, max(Date), max(Value) FROM StatData GROUP BY StatID

That would give me the most recent Date, but not the Value that corresponds
with that Date.  
None of the other aggregate functions seem appropriate either.

Thanks for any ideas.

Doug


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

Re: [sqlite] Query suggestion?

[Doug]

Thank you Igor.  

You've helped me before with what also turned out to be a similar
select referencing the same table twice.  I guess it's a concept 
that I don't fully get.  If there is a name for this technique
I'll go Google and study up on it.

Doug


 -Original Message-
 From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
 boun...@sqlite.org] On Behalf Of Igor Tandetnik
 Sent: Thursday, September 09, 2010 10:59 AM
 To: sqlite-users@sqlite.org
 Subject: Re: [sqlite] Query suggestion?
 
 Doug pa...@poweradmin.com wrote:
  I'm wondering if the SQL gurus here can give me some direction.  I
 have a
  very simple stats table:
 
  CREATE TABLE StatData
  (
  StatID INTEGER NOT NULL,
  Value REAL NOT NULL,
  Date INTEGER NOT NULL
  );
 
  I'd like to pull out the most recent date and associated value for
 each
  StatID.
 
 select StatID, Value, Date from StatData t1 where rowid =
 (select rowid from StatData t2 where t2.StatID = t1.StatID
  order by Date desc limit 1);
 
 -- or
 
 select StatID, Value, Date from StatData t1 where Date =
 (select max(Date) from StatData t2 where t2.StatID = t1.StatID);
 
 The first query is probably slightly faster, the second is easier on
 the eyes.
 
 Igor Tandetnik
 
 ___
 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] Query suggestion?

[Doug]

Thenk you Gerry.  

After some studying I now understand that the inner SELECT is executed 
for each outer row -- so trimming the outer result set early seems 
like a very good optimization idea.

Doug

 -Original Message-
 From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
 boun...@sqlite.org] On Behalf Of Gerry Snyder
 Sent: Thursday, September 09, 2010 1:52 PM
 To: General Discussion of SQLite Database
 Subject: Re: [sqlite] Query suggestion?
 
   On 9/9/2010 11:32 AM, Doug wrote:
  Thank you Igor.
 
  You've helped me before with what also turned out to be a similar
  select referencing the same table twice.  I guess it's a concept
  that I don't fully get.  If there is a name for this technique
  I'll go Google and study up on it.
 
 
 You should be able to find some good info by googling :correlated
 subquery
 
 
 
 Gerry
 ___
 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 the deadlock?

[Doug Currie]

On Aug 24, 2010, at 10:57 AM, Nikolaus Rath wrote:

 Nikolaus Rath nikolaus-bth8mxji...@public.gmane.org writes:
 Still no one able to clarify the issues raised in this thread?
 
 Let me try to summarize what I still don't understand:
 
 - Will SQLite acquire and release an EXCLUSIVE lock while keeping a
   SHARED lock if one executes a UPDATE query with one cursor while a
   different cursor is in the middle of a SELECT query,
 
   -or-
 
   will the EXCLUSIVE lock be held until the SELECT query finishes?

If you want the main thread to hold an exclusive lock until the work is 
completed (which would prevent the deadlock) put the SELECT and DELETE queries 
in a transaction using BEGIN IMMEDIATE or BEGIN EXCLUSIVE. Then the EXCLUSIVE 
lock be held until the SELECT query finishes.

Alternatively, finalize the SELECT before doing the DELETE.

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

Re: [sqlite] playing with triggers

[Doug Currie]

On Aug 19, 2010, at 4:00 PM, David Bicking wrote:

 I haven't tried RAISE(ROLLBACK... as that seems to severe. 
 RAISE(ABORT... removes the initial insert to Table1, which I want to avoid.
 RAISE(FAIL.. on lets say the fourth record inserted in to Table2, would leave 
 the first three there, which I can't let happen. It is all or nothing for 
 data changes below Table1.
 
 Which leads me to believe I can't do what I want without application code 
 supervising the changes.

Would SAVEPOINTs help you here? http://www.sqlite.org/lang_savepoint.html

I've never tried using ROLLBACK TO in a trigger.

e

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

Re: [sqlite] fast string prefix matching

[Doug Reeder]

On Aug 17, 2010, at 9:28 PM, Igor Tandetnik wrote:

 Doug Reeder reeder...@gmail.com wrote:
 I need to search for string prefix matches; for example given the  
 path 'PP',
 I need to find 'PPA', 'PPBJQ', and 'PPz'.  (The character set is all
 characters greater than or equal to 'A', and is case-sensitive.)   A
 statement that does exactly what I want is

 UPDATE item SET hasChildren = EXISTS (SELECT path FROM item AS c  
 WHERE
 substr(c.path, 1, length(item.path)) == item.path AND  
 length(c.path) 
 length(item.path)) WHERE path = ? OR path = ?

 Try this:

 select path from item as c
 where c.path  item.path and c.path = item.path || 'z'

 You can use a large codepoint (say, U+ - it's not a valid  
 Unicode character, but SQLite won't complain) in place of 'z'.
 -- 
 Igor Tandetnik

That does what I need! Thanks!

Does SQLite treat strings as sequences of opaque 16-bit values, except  
for the wildcard operators for LIKE and GLOB?  Does it care about  
surrogate code points?  Does it care about FDD0 to FDEF ?


Doug Reeder
reeder...@gmail.com
http://reeder29.livejournal.com/
https://twitter.com/reeder29

https://twitter.com/hominidsoftware
http://outlinetracker.com








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

[sqlite] fast string prefix matching

[Doug Reeder]

I need to search for string prefix matches; for example given the path 'PP',
I need to find 'PPA', 'PPBJQ', and 'PPz'.  (The character set is all
characters greater than or equal to 'A', and is case-sensitive.)   A
statement that does exactly what I want is

UPDATE item SET hasChildren = EXISTS (SELECT path FROM item AS c WHERE
substr(c.path, 1, length(item.path)) == item.path AND length(c.path) 
length(item.path)) WHERE path = ? OR path = ?


I used to use


UPDATE item SET hasChildren = EXISTS (SELECT path FROM item AS c WHERE
c.path GLOB item.path || '?') WHERE path = ? OR path = ?


(These searches are slightly different, since the first one finds all items
which start with either given string, and the second finds only the
immediate children.  In this example, they produce the same result.)


I can't use the second statement, because GLOB uses brackets as wildcards
(asterisk and question mark are not present in my strings).   However, GLOB
would be faster, because of the index optimization (
http://www.sqlite.org/optoverview.html section 4.0).


When I'm directly querying for items, instead of using the EXISTS
expression, I can do something like this (in JavaScript, using the HTML5
relational database API):


nextPath = project.path.slice(0, -1) + String.fromCharCode(project.path.
charCodeAt(project.path.length-1) + 1);

transaction.executeSql(SELECT dbId, path, summaryText, dueDate FROM item
WHERE path = ? AND path  ? AND dueDate IS NOT NULL, [project.path,nextPath],
...


Is there a faster statement that does what I want (my first SQL statement
above) in pure SQL?


-- 
Doug Reeder
reeder...@gmail.com
http://reeder29.livejournal.com/
https://twitter.com/reeder29

https://twitter.com/hominidsoftware
http://outlinetracker.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Possible Bug SQLITE 3.7.0

[Doug Campbell]

Dear sqlite developers,

I have noticed that the .dump command quotes the table name on INSERT
commands, but does not quote them on CREATE TABLE commands.

Import operations using the generated SQL into Postgresql fails unless one
manually edits the SQL to quote mixed case or upper case table names in
CREATE TABLE commands prior to importing into Postgres, because Postgres
is case-insensitive unless the names are quoted.

Possible resolution:  Modify output syntax for CREATE TABLE generation for
.dump and .schema to quote all table names.

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

Re: [sqlite] Couple of questions about WAL

[Doug]

Thanks for your explanations Dan.  The new WAL feature sounds great and I'm
excited to try it.  Two questions below:

 When in WAL mode, clients use file-locks to implement a kind of
 robust (crash-proof) reference counting for each database file.
 When a client disconnects, if it is the only client connected to
 that database file, it automatically runs a checkpoint and
 then deletes the *-wal file.
 
...
 On the other hand, if the only client connected to a database
 does not disconnect cleanly (i.e. it crashes, the system crashes,
 or the client exits without calling sqlite3_close()), then it
 leaves the *-wal file in place. In this case, when the next
 client connects to the database file it has to read the entire
 *-wal file to reconstruct the wal-index. If the *-wal file is
 large, this might take a while.

With WAL mode if there is a crash, it seems like the reference counting
would be messed up from that point on (meaning too high).  In that case, the
*-wal file will always exist, right?  It wouldn't affect the database
robustness but I guess it would be a case where the startup performance
being discussed would be affected.

Also, is the reference counting per process or per connection?

Thanks
Doug



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

Re: [sqlite] Is the absence of msvcrt.dll a known issue with SQLite Windows 2000

[Doug]

You can find out definitively whether your problems are a missing DLL on
that particular system by using Dependency Walker:

http://www.dependencywalker.com/

Run the app and load Sqlite3.dll.  If dependent DLLs are missing, it will
highlight them.  Similarly, you can load up other DLLs and EXEs in your app
to see if something else is missing a DLL.

Doug



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

Re: [sqlite] What languages can include SQLite statically?

[Doug Currie]

On Jun 1, 2010, at 2:24 PM, Gilles Ganault wrote:

 Actually, it's a Blackfin processor, and since it's an embedded
 environment, RAM and storage (NAND) are an issue.

You may find eLua interesting.  http://www.eluaproject.net/ 
The supported platforms are heavily ARM based, but in the same performance 
class as Blackfin.

e

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

Re: [sqlite] sqlite on mac os x 64 bits

[Doug Currie]

On May 18, 2010, at 4:14 AM, Sylvain Pointeau wrote:
 but is it 64 bits? or do I have to add a special option?

Last time I built a Universal Binary sqlite3 on OS X (March 2010 3.6.22) I had 
to 

CFLAGS='-arch i686 -arch x86_64' LDFLAGS='-arch i686 -arch x86_64' ./configure 
--disable-dependency-tracking

Without the --disable-dependency-tracking configure gets confused; 
alternatively you can
# edit Makefile removing -M options
# basically turning the .c.o and .c.lo rules into 
# $(COMPILE) -c -o $@ $
# $(LTCOMPILE) -c -o $@ $


You can use the file command to reveal the library's compatible machine 
architectures, e.g., 

~ e$ file /usr/local/lib/libsqlite3.dylib 
/usr/local/lib/libsqlite3.dylib: Mach-O universal binary with 2 architectures
/usr/local/lib/libsqlite3.dylib (for architecture i386):Mach-O 
dynamically linked shared library i386
/usr/local/lib/libsqlite3.dylib (for architecture x86_64):  Mach-O 64-bit 
dynamically linked shared library x86_64
~ e$ file /usr/local/lib/libsqlite3.a
/usr/local/lib/libsqlite3.a: Mach-O universal binary with 2 architectures
/usr/local/lib/libsqlite3.a (for architecture i386):current ar archive 
random library
/usr/local/lib/libsqlite3.a (for architecture x86_64):  current ar archive 
random library
~ e$ 


e

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

Re: [sqlite] ANN: O'Reilly book Using SQLite available for pre-order

[Doug]

 I've always been mystified why these kind of books duplicate
 installation instructions that already exist on the download site of
whatever they
 are documenting.  Same thing with the copious amounts of 'reference'
 information that adds no value over what is on the web site and the book
will
 quickly become out of date.

It's quite hard to add comments in the margin of a website (you should see
my OpenSSL book!).  Sure, if the book is just a copy-paste of the website,
that's not too helpful.  But hopefully things are stated differently, or
examples are given which can be valuable.

Good luck with the book Jay.

Doug


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

Re: [sqlite] round problem?

[Doug Currie]

On Feb 15, 2010, at 1:43 PM, Roger Binns wrote:

 Shane Harrelson wrote:
 I'm looking at how this can be improved.
 
 It seems that everyone else is converging on using David Gay's dtoa.c 

We've been converging for a few years!

http://www.mail-archive.com/sqlite-users@sqlite.org/msg09529.html

e


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

Re: [sqlite] In Memory Usage

[Doug Currie]

On Jan 4, 2010, at 6:35 AM, sasikuma...@tcs.com wrote:

 I'm using SQLite DB version 3.6.12. I recently read about the feature of 
 In-Memory Database and tried to implement it. I was able to create a new 
 DB connection in memory, able to create a table and insert some set of 
 records into the memory DB. 
 
 How should I now transfer those records into the real table in the real 
 DB, someone kindly assist and guide me.

Adding to suggestions by Igor and Simon... You can use the ATTACH command to 
attach a disk based db to your memory based db. Then, using (CREATE and) INSERT 
statements you can copy records from the memory based db to the disk based db.

http://www.sqlite.org/lang_attach.html
http://www.sqlite.org/lang_insert.html

e



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

Re: [sqlite] Unique index on REAL values and equality check

[Doug Currie]

On Dec 13, 2009, at 3:16 PM, Alexey Pechnikov wrote:

 As we can see, the unique index can check equlity of REAL values 
 but the = operator can not. it's fantastic I think :-)

The problem is not the = operator...

sqlite create table test (save_date REAL unique);
sqlite insert into test values (julianday('now'));
sqlite select rowid,* from test;
1|2455179.42227787
sqlite insert into test select * from test;
SQL error: column save_date is not unique
sqlite select count(*) from test where save_date=2455179.42227787;
0
sqlite select count(*) from test where save_date in (select save_date from 
test where rowid=1);
1
sqlite 

The problem is that floating point values (in SQLite shell) do not have 
write-read idempotency. I have moaned about that on this mailing list for 
years! ;-)
http://www.mail-archive.com/sqlite-users@sqlite.org/msg09529.html

e

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

Re: [sqlite] sqlite for threads

[Doug]

 Hello,
 
 Maybe many others have asked this question, so I will say sorry if
 that's true.
 I have a program which uses threads, when writing to (sometime even
 reading
  from) SQLite, I always got the error of database is locked.
 I think since SQLite is a file db, so it get locked easily by multi-
 threads or multi-processes.
 But is there a way to resolve this problem instead of switching
 database  to another one?
 
 Thanks.

I used to have similar problems and solved it by following advice 
I got on this list, which is to start the transaction with:
BEGIN IMMEDIATE;
any time the database will be written to (ie an insert, update, 
delete, etc).

That, along with looping on sqlite3_prepare_v2 and sqlite3_step any
time you get SQLITE_BUSY, virtually solved the issue for me.

HTH
Doug


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

Re: [sqlite] SQLite on PocketBook

[Doug Currie]

On Nov 5, 2009, at 5:15 PM, Beau Wilkinson wrote:

 I really think this warrants further discussion. Perhaps the correct  
 answer (that ARMs implement a non-standard FP type which is  
 incompatible with Sqlite) is already out there, but I think the  
 issues I raised with that answer should at least be addressed.

I don't know if this is the problem on PocketBook, but...

We have successfully used the SQLite compile option  
SQLITE_MIXED_ENDIAN_64BIT_FLOAT when building SQLite for ARM to get  
interoperability of databases between ARM Linux and other platforms  
such as x86 Linux, MacOSX, and Windows. Some compilers and runtimes  
for ARM use a format wherein the two 32-bit halves of a double are  
swapped relative to other platforms (the two 32-bit words are in big- 
endian order, whereas the bytes in the words are in little endian  
order, hence the rationale for the MIXED nomenclature in the option  
name).

e

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

Re: [sqlite] feature proposal - strong but dynamic typing

[Doug Currie]

On Oct 30, 2009, at 10:14 AM, P Kishor wrote:

 Actually, there can be one bad effect of Darren's suggestion, now that
 I think of it, and that would be for those who don't care for strong
 typing. They will end up getting strong typing for all non-UNIVERSAL
 columns whether they like it or not, whether they expect it or not,
 unless there is a pragma as well to just disable strong typing
 completely.

On Oct 29, 2009, at 5:33 PM, Darren Duncan wrote:

 Support for what I indicated could conceivably just be added like  
 how support
 for foreign keys was just added, and it could be turned on/off with  
 a pragma
 likewise to aid backwards compatibility, for people who wrote the  
 column types
 in their SQL but expected enforcement to be lax.

e

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

[sqlite] Limiting memory usage

[Doug]

I'm trying to figure out how to limit SQLite's memory usage while still
giving it as much memory as I can.

The app has about 50-60 separate database handles to 50-60 separate database
files.  Each handle is only used by a single thread at a time, and most are
always accessed by the thread that created the handle.

When each handle is created, I execute:
PRAGMA temp_store=1  { can't tell from the docs if this needs to be executed
on each handle or not }
PRAGMA page_size=4096
PRAGMA cache_size= { either 2000 or 6000 depending on the database file
being opened }

So in theory, my cache usage could be anywhere from 50 * 4KB * 2000 =
400,000 KB to 60 * 4KB * 6000 = 1,440,000 KB.

The 1.4 Gig'ish number is too large for the process to handle - I need to
limit it to about 1GB.

Currently I'm calling   sqlite3_soft_heap_limit(1024 * 1024 * 1024) with the
hope that if memory needs to be allocated there should be plenty
(60*6000=360,000) of pages that sqlite3_release_memory could release and
thus the soft limit would hold.  Apparently I'm wrong though -
sqlite3_memory_used is reporting 1,266,186 KB.

Is there a better way to go about it?  I can limit the cache_size value, but
I'd like to optimize and use as much memory, up to the limit, as I can.
Shared cache seems like it might be an option, but there are enough caveats
and considerations that I don't feel I understand it well enough to risk it.

I'm open to any suggestions.

Thanks


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

Re: [sqlite] Limiting memory usage

[Doug]

Yes, correct.  I just showed it to indicate that all databases that are
ever created use this page_size of 4KB.

  PRAGMA page_size=4096
 
 This PRAGMA governs characteristics of a database you have not yet
 created: it's pages on disk, not pages in memory.  The page_size is a
 characteristic of the database file on disk.  It has no effect in a
 program which opens a database file which already exists.  If you're
 trying to manipulate the page_size of a database you must use this
 PRAGMA before the database file is first created.
 
 Simon.


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

Re: [sqlite] Limiting memory usage

[Doug]

 You can implement your own database cache which will respect overall
 limit on memory usage.
 
 Pavel

Was hoping to avoid that :(   

With so many cache pages available, shouldn't the sqlite3_release_memory
calls, caused by the soft limit, have been able to keep memory below the
soft limit?

Doug



___
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] Fwd: sqlite3_prepare_v2

[Doug]

Try renaming the windows\system32\sqlite3.dll to sqlite3.lld (or anything
else so you can easily find it and restore).

Then see which app has problems launching (if any).  Sqlite3.dll probably
shouldn't be there anyway.

Once you know what app needs that DLL, you can copy it into the
application's directory (and rename back to .dll too).  Or, just rename the
original back to .dll to be back to where you're at today.



 -Original Message-
 From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
 boun...@sqlite.org] On Behalf Of D. Richard Hipp
 Sent: Thursday, October 01, 2009 12:12 PM
 To: General Discussion of SQLite Database
 Subject: [sqlite] Fwd: sqlite3_prepare_v2
 
 Any windows users have ideas on how to help MCB?
 
 Begin forwarded message:
 
  From: M.C.B. mcordo...@infosel.net.mx
  Date: September 30, 2009 9:00:16 PM EDT
  To: sql...@hwaci.com
  Subject: sqlite3_prepare_v2
 
  Gentlemen:
 
  About a week ago, when starting my PC appears a warning window
  stating:
 
  Entry or entrance point of procedure sqlite3_prepare_v2 is not found
  within the dynamic links' library SQLite3.dll.
 
  I've been trying to locate this element without success, as no
  further information I get on what program this is missing.
 
  I started a search in the PC unsuccessfully and in the rescue
  deleted files as well. Then I look for the SQLite3.dll. and found
  three places: Windows\System32, Apple\Apple Applications Suport and
  Apple\Mobile Device Support\bin, where I didn't find the referred
  element for obvious reazons, Then I went to the Apple web site and
  found no way to have the missing element.
 
  So, I search more in the Internet and found your web site. There, I
  found no way to identify, which is this missing object and this is
  why I'm asking for your advice for what to do in order to recover
  the missing element.
 
  Can you please tell what to do in this respect?
 
  Thank you in advance for an early reply.
 
  M. Cordova B.
 
  P.S. For your information I have a Shuffle Ipod, which needs the
  ITunes to work. And as this belongs to Apple, I think this might be
  the affected program, not withstanding it works. Besides I have a
  Nokia Cell, which I don't know if for connecting it to the CP might
  need the missing element.
 
 D. Richard Hipp
 d...@hwaci.com
 
 
 
 ___
 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] one liner for insert or update ?

[Doug]

Wouldn't INSERT OR REPLACE do that for you? (which by the way, has to be
one of the coolest features in SQLite of all!)

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


Doug

 -Original Message-
 From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
 boun...@sqlite.org] On Behalf Of yogibabu
 Sent: Saturday, September 12, 2009 11:51 AM
 To: sqlite-users@sqlite.org
 Subject: [sqlite] one liner for insert or update ?
 
 
 my dream is to be able do it like that:
 ?php
 $pdo = new PDO('sqlite:database.DB3');
 $pdo-query(INSERT UPDATE table SET value1='somedata',
 value2=somedata,
 id=$getid )
 ?
 
 when $getid contains data (a number of course) then an update is
 performed,
 otherwise if it is null then database engine ignores `id` valule
 without any
 notification and INSERT is performed with adding new id...
 --
 View this message in context: http://www.nabble.com/one-liner-for-
 insert-or-update---tp25416164p25416164.html
 Sent from the SQLite mailing list archive at Nabble.com.
 
 ___
 sqlite-users mailing list
 sqlite-users@sqlite.org
 http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


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

[sqlite] Speeding up a (simple?) GROUP BY query

[Doug]

I have two simple tables - one that defines a statistic, and one that hold
the data for each statistic:

CREATE TABLE Statistic

(

StatID INTEGER PRIMARY KEY,

OwningComputer TEXT NOT NULL

);

 

CREATE TABLE StatData

(

StatID INTEGER NOT NULL,

Value INTEGER NOT NULL,

Date INTEGER NOT NULL

);

 

and indices.

CREATE INDEX Ind_StatData_StatID on StatData (StatID, Date);

CREATE INDEX Ind_StatData_Date on StatData (Date);

 

I'm trying to figure out the best way to find any entries in Statistic that
don't have any corresponding entries in StatData (ie what Statistic is
defined, but no longer has any data attached).

 

The following query works correctly, but when there is a few 100MB of
StatData entries, it's pretty slow:

 

SELECT StatID FROM Statistic WHERE StatID NOT IN (SELECT StatID FROM
StatData GROUP BY StatID);

 

Is there any slick way to make the GROUP BY faster, since I don't really
need to group by all that data?  Since there is an index on StatData.StatID,
I would assume the GROUP BY would work by just hitting the index, but I've
been wrong before.

 

Thanks for any ideas from the group.

 

Doug

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

Re: [sqlite] Huge numbers of savepoints.

[Doug Currie]

On Aug 23, 2009, at 6:46 AM, Chris Dew wrote:

 Note: this is not for production code, just an experiment in keeping a
 history of application 'state', allowing current state to be
 recalculated if an historic input is received 'late'.  See
 http://www.finalcog.com/haskell-decoupling-time-from-physics for a
 similar idea (implemented in Haskell).

This page might give you some ideas:

http://www.sqlite.org/cvstrac/wiki?p=UndoRedo

e

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

Re: [sqlite] (no subject)

[Doug]

Hi Erick --

I can only help a little with #3.  How are your strings stored in your
program?  If they are stored with wchar_t, then using the '16' APIs is
probably easiest to use (ie sqlite3_open16, sqlite3_prepare16_v2, etc).
That's what I do and all sorts of European and Asian customers don't have
any issues with storing and retrieving local strings.  If you don't use the
wide-char (16) APIs, you would need to explicitly convert your strings to
UTF-8 (which is not the same as ASCII) before handing to SQLite.

Doug


 -Original Message-
 From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
 boun...@sqlite.org] On Behalf Of e...@sitadella.com
 Sent: Thursday, August 20, 2009 4:21 PM
 To: sqlite-users@sqlite.org
 Subject: [sqlite] (no subject)
 
 Hi guys,
 
 This is my first post. I am creating a simple document archiving
 program
 for small businesses. I am creating it in a scripting language called
 www.autohotkey.com. I intend to place the SQLite database file on a
 network share and use sqlite.dll to access and manipulate it.
 
 In general, everything is on a relatively small scale: there will be
 less
 than 10 users who will occasionally interact with the database, there
 will
 be around 4 tables and based on experience with a similar system, I
 don't
 expect a total of more than 5 records after a few years of use. The
 client computers will be Windows XP or newer and the database file will
 be
 located on a network share on a Windows 2000 server or newer.
 
 1. I have read that the file locking mechanisms on older windows
 networks
 are not very reliable and that it is not advisable to use SQLite on NFS
 or
 network shares. Given the robustness and efficiency of SQLite and the
 low
 frequency of use of my application, do I still need to worry about
 placing
 the database on a network share?
 
 2. Should I modify any of the default settings to better suit this
 environment?
 
 3. I am having problems reading and writing international characters to
 and from the database, specifically the norwegian characters æ, ø and
 å.
 If I use sqlite.exe to create a records containing æ, ø or å, I can
 read
 the record using sqlite.exe without any problems. Likewise, if I use
 SQLiteSpy to create a record containing ø, æ or å I can read the record
 using SQLiteSpy without any problems. But if I create a record in
 sqlite.exe and try to read it with SQLiteSpy or vice versa, it doesn't
 work as expected and the special characters are converted to all sorts
 of
 oddball symbols like squares and question marks. I assume this is
 somehow
 due to different ASCII/UTF encodings, but how can these problems be
 avoided?
 
 4. Select commands are case sensitive with æ, ø and å. Is there a
 simple
 workaround for this?
 
 
 Regards,
 Erik
 
 ___
 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] What is a Relation?

[Doug Currie]

On Jul 27, 2009, at 10:33 AM, CityDev wrote:

 It's true that Codd and Date used the term 'relational' (They  
 championed the
 N-ary Relational Model - others were around at the same time) but  
 it's not
 easy to track the origin of the term in mathematics.

http://en.wikipedia.org/wiki/Relation_(mathematics)

e


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

Re: [sqlite] .lib file?

[Doug]

Hi Paul --

When I build SQLite (from the amalgamation) into a DLL VS2008 (and earlier
as I recall) automatically creates an import library in the same directory
as the .DLL file.  I also _used_ to run the following command separately to
create the import lib:

link /lib /def:sqlite3.def

I haven't kept my sqlite3.def file up to date, but it's fairly recent if you
want to use it.  Since we can't post files to the newsgroup, I'll append it
here.

Doug

start file
EXPORTS
sqlite3_aggregate_context
sqlite3_aggregate_count
sqlite3_auto_extension
sqlite3_bind_blob
sqlite3_bind_double
sqlite3_bind_int
sqlite3_bind_int64
sqlite3_bind_null
sqlite3_bind_parameter_count
sqlite3_bind_parameter_index
sqlite3_bind_parameter_name
sqlite3_bind_text
sqlite3_bind_text16
sqlite3_bind_value
sqlite3_bind_zeroblob
sqlite3_blob_bytes
sqlite3_blob_close
sqlite3_blob_open
sqlite3_blob_read
sqlite3_blob_write
sqlite3_busy_handler
sqlite3_busy_timeout
sqlite3_changes
sqlite3_clear_bindings
sqlite3_close
sqlite3_collation_needed
sqlite3_collation_needed16
sqlite3_column_blob
sqlite3_column_bytes
sqlite3_column_bytes16
sqlite3_column_count
sqlite3_column_decltype
sqlite3_column_decltype16
sqlite3_column_double
sqlite3_column_int
sqlite3_column_int64
sqlite3_column_name
sqlite3_column_name16
sqlite3_column_text
sqlite3_column_text16
sqlite3_column_type
sqlite3_column_value
sqlite3_commit_hook
sqlite3_complete
sqlite3_complete16
sqlite3_create_collation
sqlite3_create_collation16
sqlite3_create_collation_v2
sqlite3_create_function
sqlite3_create_function16
sqlite3_create_module
sqlite3_create_module_v2
sqlite3_data_count
sqlite3_db_handle
sqlite3_declare_vtab
sqlite3_enable_load_extension
sqlite3_enable_shared_cache
sqlite3_errcode
sqlite3_errmsg
sqlite3_errmsg16
sqlite3_exec
sqlite3_expired
sqlite3_extended_result_codes
sqlite3_file_control
sqlite3_finalize
sqlite3_free
sqlite3_free_table
sqlite3_get_autocommit
sqlite3_get_auxdata
sqlite3_get_table
sqlite3_global_recover
sqlite3_interrupt
sqlite3_last_insert_rowid
sqlite3_libversion
sqlite3_libversion_number
sqlite3_load_extension
sqlite3_malloc
sqlite3_memory_alarm
sqlite3_memory_highwater
sqlite3_memory_used
sqlite3_mprintf
sqlite3_mutex_alloc
sqlite3_mutex_enter
sqlite3_mutex_free
sqlite3_mutex_leave
sqlite3_mutex_try
sqlite3_open
sqlite3_open16
sqlite3_open_v2
sqlite3_overload_function
sqlite3_prepare
sqlite3_prepare16
sqlite3_prepare16_v2
sqlite3_prepare_v2
sqlite3_profile
sqlite3_progress_handler
sqlite3_realloc
sqlite3_release_memory
sqlite3_reset
sqlite3_reset_auto_extension
sqlite3_result_blob
sqlite3_result_double
sqlite3_result_error
sqlite3_result_error16
sqlite3_result_error_nomem
sqlite3_result_error_toobig
sqlite3_result_int
sqlite3_result_int64
sqlite3_result_null
sqlite3_result_text
sqlite3_result_text16
sqlite3_result_text16be
sqlite3_result_text16le
sqlite3_result_value
sqlite3_result_zeroblob
sqlite3_rollback_hook
sqlite3_set_authorizer
sqlite3_set_auxdata
sqlite3_sleep
sqlite3_snprintf
sqlite3_soft_heap_limit
sqlite3_sql
sqlite3_step
sqlite3_thread_cleanup
sqlite3_threadsafe
sqlite3_total_changes
sqlite3_trace
sqlite3_transfer_bindings
sqlite3_update_hook
sqlite3_user_data
sqlite3_value_blob
sqlite3_value_bytes
sqlite3_value_bytes16
sqlite3_value_double
sqlite3_value_int
sqlite3_value_int64
sqlite3_value_numeric_type
sqlite3_value_text
sqlite3_value_text16
sqlite3_value_text16be
sqlite3_value_text16le
sqlite3_value_type
sqlite3_version
sqlite3_vfs_find
sqlite3_vfs_register
sqlite3_vfs_unregister
sqlite3_vmprintf
end file


 -Original Message-
 From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
 boun...@sqlite.org] On Behalf Of Paul Claessen
 Sent: Saturday, July 25, 2009 10:49 AM
 To: sqlite-users@sqlite.org
 Subject: Re: [sqlite] .lib file?
 
 Thanks Pavel.
 
 Two points though:
 
 1. The amalgated sources were, apparently, not written with MS Visual
 Study in mind, since compiling it results in over 100 errors
 (mostly invalid pointer conversions)
 2. If I have a number of apps, it would really be more efficient to use
 the DLL.
 
 I'm sure I can fix all the pointer casts (shouldn't be necessary if the
 code used more consistent types!), but that would take me a
 lot of time, plus, there should be a way to simply use the .dll: since
 there IS a windows console app, there must be either a .lib
 file somewhere, or there is an alternative way of using DLL's from a
 windows console app, that I'm not aware of.
 
 Kind regards,
 
 ~ Paul Claessen
 
 
  -Original Message-
  From: Pavel Ivanov [mailto:paiva...@gmail.com]
  Sent: Saturday, July 25, 2009 11:31 AM
  To: p...@claessen.com; General Discussion of SQLite Database
  Subject: Re: [sqlite] .lib file?
 
  You can take sqlite3.c from amalgamation package and include it into
  your project. This way SQLite will be included into your application
  and you won't need any .dll or .lib files.
 
  Pavel
 
  On Sat, Jul 25, 2009 at 10:41 AM, Paul Claessenp...@claessen.com
 wrote:
   Greetings

Re: [sqlite] SQLite: Porting to another Operating system.

[Doug Currie]

On Jul 24, 2009, at 8:44 AM, D. Richard Hipp wrote:

 SQLite database files are cross-platform.  All you have to do is copy
 the file to the new machine.  There is no separate external format.
 The same database file format work on all platforms.

Just make sure that if you are moving to a new platform, that the data  
formats match those expected by SQLite. This is especially important  
for platforms with weird floating point formats. For example, on ARM  
platforms there are a couple floating point formats, and the  
SQLITE_MIXED_ENDIAN_64BIT_FLOAT compile switch helps accommodate them.

SQLite provides support to get this right:

** Developers using SQLite on an ARM7 should compile and run their
** application using -DSQLITE_DEBUG=1 at least once.  With DEBUG
** enabled, some asserts below will ensure that the byte order of
** floating point values is correct.

e

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

[sqlite] Heirarchical queries question

[Doug]

I'm trying to get my head around doing hierarchies in SQL.  I've been
Googling and it appears Oracle and MS SQL have some extensions to help, but
I'm trying to figure out what can be done with 'plain' SQL.

 

Imagine a directory table:

CREATE TABLE IF NOT EXISTS Directory 

(

  DirID INTEGER,

  Path TEXT,

  ParentDirID INTEGER

);

 

and some data that represents this table structure:

/

/users

/users/doug

/users/brett

/users/brett/work

/users/brett/research

/users/brett/research/SQL

 

INSERT INTO Directory (DirID, Path, ParentDirID) VALUES (1, '/', 0);

INSERT INTO Directory (DirID, Path, ParentDirID) VALUES (2, '/users', 1); 

INSERT INTO Directory (DirID, Path, ParentDirID) VALUES (3, '/users/doug',
2);

INSERT INTO Directory (DirID, Path, ParentDirID) VALUES (4, '/users/brett',
2);

INSERT INTO Directory (DirID, Path, ParentDirID) VALUES (5,
'/users/brett/work', 4);

INSERT INTO Directory (DirID, Path, ParentDirID) VALUES (6,
'/users/brett/research', 4);

INSERT INTO Directory (DirID, Path, ParentDirID) VALUES (7,
'/users/brett/research/SQL', 6);

 

Assuming I have /users (or the DirID of 2), is there a query that can return
the number of subdirectories each child directory has?  Ie an output of:

/users/doug  0   

/users/brett 3

 

(or if the child was counted

/users/doug  1

/users/brett 4

)

 

I suppose I could manually grab all entries where ParentDirID=2 (ie the
/users/doug and /users/brett) and then for each of those run a query:
SELECT COUNT(*) FROM Directory WHERE Path LIKE '/users/doug%';

SELECT COUNT(*) FROM Directory WHERE Path LIKE '/users/brett%';

 

At least that's an algorithm where the only input is '/users', but
ultimately I'd like a SELECT statement where the only input is '/users'.

 

But is there any way that SQL can accomplish that without needing to
iterate?  I keep thinking a GROUP BY that used LIKE instead of = might get
me closer, but as far as I know that's not an option anyway (I don't want to
use a custom function if possible - trying to end up with portable SQL as
much as possible).

 

I'm looking forward to see what insight you guys have.  (This list always
impresses)

 

Thanks

Doug

 

 

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

Re: [sqlite] Heirarchical queries question

[Doug]

Wow Pavel, that's a cool approach.

I understand the issue about having % in the path (which is a problem I need
to work around), but what is special about '_' ?

Thanks
Doug


 -Original Message-
 From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
 boun...@sqlite.org] On Behalf Of Pavel Ivanov
 Sent: Friday, July 17, 2009 10:53 AM
 To: General Discussion of SQLite Database
 Subject: Re: [sqlite] Heirarchical queries question
 
 Maybe this:
 
 select childs.Path, count(*)
 from Directory childs, Directory hierarchy
 where childs.ParentDirID = ?
 and hierarchy.Path like childs.Path || '%'
 group by childs.Path
 
 You should have indexes on ParentDirID and on Path to make this query
 somewhat effective. And you shouldn't have '%' and '_' signs in the
 Path (or add another column where will be some modification of Path
 suitable for this query).
 
 Pavel
 
 On Fri, Jul 17, 2009 at 11:39 AM, Dougpa...@poweradmin.com wrote:
  I'm trying to get my head around doing hierarchies in SQL.  I've been
  Googling and it appears Oracle and MS SQL have some extensions to
 help, but
  I'm trying to figure out what can be done with 'plain' SQL.
 
 
 
  Imagine a directory table:
 
  CREATE TABLE IF NOT EXISTS Directory
 
  (
 
       DirID INTEGER,
 
       Path TEXT,
 
       ParentDirID INTEGER
 
  );
 
 
 
  and some data that represents this table structure:
 
  /
 
  /users
 
  /users/doug
 
  /users/brett
 
  /users/brett/work
 
  /users/brett/research
 
  /users/brett/research/SQL
 
 
 
  INSERT INTO Directory (DirID, Path, ParentDirID) VALUES (1, '/', 0);
 
  INSERT INTO Directory (DirID, Path, ParentDirID) VALUES (2, '/users',
 1);
 
  INSERT INTO Directory (DirID, Path, ParentDirID) VALUES (3,
 '/users/doug',
  2);
 
  INSERT INTO Directory (DirID, Path, ParentDirID) VALUES (4,
 '/users/brett',
  2);
 
  INSERT INTO Directory (DirID, Path, ParentDirID) VALUES (5,
  '/users/brett/work', 4);
 
  INSERT INTO Directory (DirID, Path, ParentDirID) VALUES (6,
  '/users/brett/research', 4);
 
  INSERT INTO Directory (DirID, Path, ParentDirID) VALUES (7,
  '/users/brett/research/SQL', 6);
 
 
 
  Assuming I have /users (or the DirID of 2), is there a query that can
 return
  the number of subdirectories each child directory has?  Ie an output
 of:
 
  /users/doug  0
 
  /users/brett 3
 
 
 
  (or if the child was counted
 
  /users/doug  1
 
  /users/brett 4
 
  )
 
 
 
  I suppose I could manually grab all entries where ParentDirID=2 (ie
 the
  /users/doug and /users/brett) and then for each of those run a query:
  SELECT COUNT(*) FROM Directory WHERE Path LIKE '/users/doug%';
 
  SELECT COUNT(*) FROM Directory WHERE Path LIKE '/users/brett%';
 
 
 
  At least that's an algorithm where the only input is '/users', but
  ultimately I'd like a SELECT statement where the only input is
 '/users'.
 
 
 
  But is there any way that SQL can accomplish that without needing to
  iterate?  I keep thinking a GROUP BY that used LIKE instead of =
 might get
  me closer, but as far as I know that's not an option anyway (I don't
 want to
  use a custom function if possible - trying to end up with portable
 SQL as
  much as possible).
 
 
 
  I'm looking forward to see what insight you guys have.  (This list
 always
  impresses)
 
 
 
  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-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3.OperationalError: unable to open database file

[Doug Currie]

On Jul 7, 2009, at 4:36 PM, nixonron wrote:

 conn = sqlite3.connect('c:\Ujimadata\aid.sqlite')

Perhaps you meant

conn = sqlite3.connect('c:\\Ujimadata\\aid.sqlite')

or

conn = sqlite3.connect('c:/Ujimadata/aid.sqlite')

e

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

Re: [sqlite] Building sqlite

[Doug]

You may need SQLITE_THREADSAFE depending on how you're using the library

 -Original Message-
 From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
 boun...@sqlite.org] On Behalf Of Robert Dailey
 Sent: Monday, June 29, 2009 1:51 PM
 To: General Discussion of SQLite Database
 Subject: Re: [sqlite] Building sqlite
 
 On Mon, Jun 29, 2009 at 12:58 PM, Eric Minbiole
 eminbi...@mavroimaging.comwrote:
 
   I'm currently on Windows and I've set up a python script to
 download the
   sqlite3 amalgamation. However, the ZIP file contains no build
 system for
   sqlite. I had to create a custom CMake script to build sqlite3 into
 a
   library. I do not wish to compile the C file with my source, it
 needs to
  be
   a static library.
  
   Do you guys have a package that contains a build system for sqlite3
 for
   Windows?
 
  Can you use a dynamic library instead of static?  If so, there is a
  precompiled Windows dll for download on the SQLite site.  Otherwise,
 you
  will probably need to build manually, as you have done.
 
  (Another option might be to use one of the myriad of Dll - Static
 lib
  converters available, though this seems like more work than it's
 worth.)
 
 
 Thanks for the response. I actually have no need to use a shared
 library,
 since it isn't going to be shared. A static library is what I need and
 what
 I'm currently building. As long as there are no preprocessor
 definitions or
 other compilation flags that I need to be aware of for sqlite3, then
 what I
 have now will work just fine and it can be automated. It's a very
 simple
 CMake script and I'd be willing to contribute it to the project if you
 would
 like. You can package this up with the ZIP file and the tarball
 amalgamations so that people have the option of building a static
 library.
 
 Let me know. Thanks again for the help.
 ___
 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] sqlite3_exec unresponsive for insert, delete and update

[Doug]

I don't know anything about xcode, but I've been burned by bugs like this in
the past.  It was always my fault: console was connected to one DB, and my
app was connected to another.  Make REALLY sure you're using the database
file that you think you are.

Doug


 -Original Message-
 From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
 boun...@sqlite.org] On Behalf Of Edward Wong
 Sent: Monday, June 22, 2009 11:38 AM
 To: sqlite-users@sqlite.org
 Subject: [sqlite] sqlite3_exec unresponsive for insert, delete and
 update
 
 
 hi all,
 
 i've been banging my head against the keyboard, trying to figure out
 what is
 going on.  i am writing a program in xcode that uses the c/c++ sqlite3
 library.  the problem i am having is the following:
 
 1.  i open a connection to my db using sqlite3_open()
 
   NSString *dbFile = [[NSBundle mainBundle]
 pathForResource:@data
 ofType:@db];
   if (sqlite3_open([dbFile UTF8String], db) == SQLITE_OK) {
   //NSLog(@SQLite connection open SUCCEEDED!);
   } else {
   //NSLog(@SQLite connection open FAILED!);
   }
 
 2.  i run a sqlite3_exec function call to either insert,delete or
 update
 
 NSString query = @delete from table where id =3;
   char *error;
   int rc = sqlite3_exec(db, [query UTF8String], NULL, NULL,
 error);
   if (rc == SQLITE_OK) {
   //debug start
   NSLog(@DBObject.update. SQLITE_OK);
   //debug end
   return YES;
   } else {
   //debug start
   NSLog(@Error: %@, [NSString stringWithUTF8String:error]);
   sqlite3_free(error);
   //debug end
   return NO;
   }
 
 3.  the return value from sqlite3_exec is SQLITE_OK.  however, when i
 check
 the database table nothing has changed. (select sql statements work
 fine.)
 4.  when i type in the insert, delete or update sql statement into the
 console it works fine.
 
 i don't know what is going on.  can anyone please help?  thanks so
 much!!
 
 --
 View this message in context: http://www.nabble.com/sqlite3_exec-
 unresponsive-for-insert%2C-delete-and-update-tp24150955p24150955.html
 Sent from the SQLite mailing list archive at Nabble.com.
 
 ___
 sqlite-users mailing list
 sqlite-users@sqlite.org
 http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


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