Re: [sqlite] Case-sensitivity, performance and LIKE

2010-03-31 Thread Simon Slavin 

On 1 Apr 2010, at 2:55am, Rashed Iqbal wrote:

> Thank you, all for your responses. I did not have access to table
> definition so I used collate no-case within statements. However, I still
> have very slow performance of the DB.

If you are using a different collation to the one the column is defined with, 
any index based on that column cannot be used.  Because an index with one 
collation is not the same as an index on the same columns but with a different 
collation.  So the slowdown you are seeing is because your SELECT query can no 
longer use any indexes: SQL has to go through every record in the table every 
time you execute a query.

If you absolutely cannot change the table definition, you need to be able to 
create indexes with your preferred collation.  Or perhaps you can simulate one 
or the other using a VIEW.

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

Re: [sqlite] no modlue named _sqlite error using phyton and pysqlite2

2010-03-31 Thread Roger Binns 
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Marcelo Reyes wrote:
> I downloaded pysqlite2 module, 

There is a dedicated Python SQLite mailing list at
http://groups.google.com/group/python-sqlite

> but when I ran my code I get a "No module named _sqlite error". 

That indicates that the binary portion of pysqlite is not available or not
in the right place.  You'll need to provide further details about what you
downloaded and installed.

Roger






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

iEYEARECAAYFAku0BwUACgkQmOOfHg372QSXvgCggtqQWOlOvR8c+l7wb/0WcUfL
v4kAnRQdDjXc1fTkUHE2f9URhOms+8KU
=ULAm
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Berkeley DB adds SQL using SQLite API !!

2010-03-31 Thread Nicolas Williams 
On Wed, Mar 31, 2010 at 06:16:13PM -0700, Jim "Jed" Dodgen wrote:
> On Wed, Mar 31, 2010 at 8:50 AM, Wiktor Adamski
> >> (3) Each table and index is in a
> >> separate file so your "database" was a directory full of files instead
> >> of a single file
> >
> > This one is not a problem. Actually I don't see how 1 file is better
> > than 1 directory. [...]
> 
> Two reasons I prefer the single file approach:
> 
> 1. Simpler copy, tables and indexes don't get lost or mismatched.
> 
> 2. fewer handles to open a database. Lower overhead.

3. One fsync() vs. many (or a full sync()).

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

Re: [sqlite] Berkeley DB adds SQL using SQLite API !!

2010-03-31 Thread Neville Franks 
Thursday, April 1, 2010, 12:16:13 PM, you wrote:

JJD> On Wed, Mar 31, 2010 at 8:50 AM, Wiktor Adamski
JJD>  wrote:
>>> There were many problems with
>>> that approach:
>> ...
>>> (3) Each table and index is in a
>>> separate file so your "database" was a directory full of files instead
>>> of a single file
>>
>> This one is not a problem. Actually I don't see how 1 file is better
>> than 1 directory. For example mac application is a directory not a
>> file and no one complains. And with several files database would be
>> faster (for example dropping a table is instant or fragmentation is
>> handled by OS without need for vacuuming whole database). Also with
>> current SQLite implementation only tables would be locked by a
>> transation not a whole database (a few years ago there were even
>> document on SQLite website listing  splittnig database to several
>> files as one way to implement table level locks in SQLite).
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>

JJD> Two reasons I prefer the single file approach:

JJD> 1. Simpler copy, tables and indexes don't get lost or mismatched.

JJD> 2. fewer handles to open a database. Lower overhead.

JJD> This still is a small footprint, high-performance, low overhead SQL
JJD> implementation. It does what it needs to do and no more.


Also from the "end user" perspective it is so much easier for them to
backup or copy a single file.


---
Best regards,
  Neville Franks, http://www.surfulater.com http://blog.surfulater.com
 

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

Re: [sqlite] Case-sensitivity, performance and LIKE

2010-03-31 Thread Rashed Iqbal 
Thank you, all for your responses. I did not have access to table
definition so I used collate no-case within statements. However, I still
have very slow performance of the DB. I ran my application using Intel's
VTune performance analyzer and about 90% of time is being spent in the
file sqlite.c. Some of the high-consuming functions are listed below:

sqlite3VdbeExec = 16.87%
sqlite3VdbeMemReleaseExternal = 5.26%
sqlite3ValueText = 4.68%
isLookaside = 4.45%
sqlite3DbFree= 4.40%
sqlite3VdbeChangeEncoding = 3.83%
sqlite3_free= 3.23%
patternCompare = 2.77%
sqlite3VdbeMemNulTerminate = 2.77%
sqlite3VdbeMemRelease = 2.66%
sqlite3Utf8Read = 2.40%
sqlite3VdbeMemStoreType = 2.23%
likeFunc = 2.11%
btreeParseCellPtr = 2.10%
sqlite3BtreeNext = 2.04%


I need to spend time to investigate this further and do optimize but
would be grateful if anyone could offer some insight. 


Thanks!

~r

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Jay A. Kreibich
Sent: Tuesday, March 30, 2010 8:13 PM
To: Tom Holden
Cc: General Discussion of SQLite Database
Subject: Re: [sqlite] Case-sensitivity, performance and LIKE

On Tue, Mar 30, 2010 at 10:59:49PM -0400, Tom Holden scratched on the
wall:
>
> - Original Message - From: "Jay A. Kreibich" 
> To: "General Discussion of SQLite Database" 
> Sent: Tuesday, March 30, 2010 9:26 PM
> Subject: Re: [sqlite] Case-sensitivity, performance and LIKE
>
>
>> On Tue, Mar 30, 2010 at 08:41:09PM -0400, Tom Holden scratched on the

>> wall:
>>>
>>> - Original Message - From: "Simon Slavin" 
>>> 
>>
>>> > columnName TEXT COLLATE NOCASE
>>> >
>>> > then all sorting and SELECT queries on it will ignore case.
>>>
>>> You don't even have to change the defined collation as you can
impose the
>>> NOCASE collation in the SELECT statement as:
>>>
>>> SELECT * from Customers WHERE LastName = 'Shaw' COLLATE NOCASE AND  
>>> FirstName
>>> = 'Gioia' COLLATE NOCASE;

>>  If either/both of these columns has/have indexes, it is best to
change
>>  it in the table definition.  Otherwise you also need to define it in
the
>>  index definition, as well as everywhere you expect to use the index.
>>  This is all automatic if everything is built with the collation in
>>  the table definition.

> Maybe it works OK because the custom collation is a superset of the 
> integral NOCASE collation and my data does not lie outside the latter.

  Sorry... I didn't mean to imply this was wrong.  Using a specific
  collation in a query is perfectly OK, and should always result in the
  correct answer.  The issue is that a query will only use an index if
  the query collation and the index collation match.  Given the OP's
  performance motivations, it seemed important.
  
  Hence, for general situations where performance is a concern, it is
  best to just declare the whole column with whatever collation you
  want to use.  Otherwise, it is all too easy to miss something
  somewhere and end up with a much slower query.

  For one-off specific instances when you want to use a specific 
  collation (or, as in your situation when you cannot re-define the
  table) it is perfectly valid to simply add the collation to the query.
  However, that will result in a full table-scan, even if an index is
  otherwise available.

   -j

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

"Our opponent is an alien starship packed with atomic bombs.  We have
 a protractor."   "I'll go home and see if I can scrounge up a ruler
 and a piece of string."  --from Anathem by Neal Stephenson
___
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] Berkeley DB adds SQL using SQLite API !!

2010-03-31 Thread Jim "Jed" Dodgen 
On Wed, Mar 31, 2010 at 8:50 AM, Wiktor Adamski
 wrote:
>> There were many problems with
>> that approach:
> ...
>> (3) Each table and index is in a
>> separate file so your "database" was a directory full of files instead
>> of a single file
>
> This one is not a problem. Actually I don't see how 1 file is better
> than 1 directory. For example mac application is a directory not a
> file and no one complains. And with several files database would be
> faster (for example dropping a table is instant or fragmentation is
> handled by OS without need for vacuuming whole database). Also with
> current SQLite implementation only tables would be locked by a
> transation not a whole database (a few years ago there were even
> document on SQLite website listing  splittnig database to several
> files as one way to implement table level locks in SQLite).
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>

Two reasons I prefer the single file approach:

1. Simpler copy, tables and indexes don't get lost or mismatched.

2. fewer handles to open a database. Lower overhead.

This still is a small footprint, high-performance, low overhead SQL
implementation. It does what it needs to do and no more.





-- 
Jim "Jed" Dodgen
j...@dodgen.us
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] no modlue named _sqlite error using phyton and pysqlite2

2010-03-31 Thread Marcelo Reyes 
Hello,

I downloaded pysqlite2 module, but when I ran my code I get a "No module
named _sqlite error". I'm using python 2.5 and Windows XP.
I need to load spatialite library and thats why I want to upgrade the
sqlite3 module that comes with python 2.5
Can anyone help me? thanks.

Here's the code:

import sqlite3

conn =  sqlite3.connect('new2.sqlite')
c = conn.cursor()
res= c.execute("SELECT spatialite_version()")
print res
c.execute('''create table socks(date text, trans text, symbol text, qty
real, price real)''')

c.execute("""insert into stocksvalues
('2006-01-05','BUY','RHAT',100,35.14)""")
c.execute("""UPDATE signs SET codigoPropio='ttt' WHERE id_sign=1""")

conn.commit()
c.close()

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

Re: [sqlite] Fwd: Sqlite3 crashing on multithreaded program

2010-03-31 Thread Periasamy, Karthikeyan 


> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Jens Miltner
> Sent: Wednesday, March 31, 2010 8:17 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Fwd: Sqlite3 crashing on multithreaded program
> 
> 
> Am 31.03.2010 um 15:14 schrieb Dan Kennedy:
> 
> >
> >
> > Begin forwarded message:
> >
> >> From: "Periasamy, Karthikeyan" 
> >> Date: March 31, 2010 7:52:55 PM GMT+07:00
> >> To: "danielk1...@gmail.com" 
> >> Subject: Re: [sqlite] Sqlite3 crashing on multithreaded program
> >>
> >> Hi,
> >>
> >> Thanks for your reply.
> >>
> >> In our application the block of code which access the DB looks like
> >> this,
> >>
> >> QueryDB(char *queryStr)
> >> {
> >>   result = dbi_conn_query(DbConnection, queryStr);
> >>   if (result)
> >>   {
> >>if(ZERO_RESULT == dbi_result_get_numrows(result))
> >>{
> >>  syslog( LOG_DEBUG,"[%s:%s] Empty Result. Freeing result pointer
> >> \n", __FILE__, __FUNCTION__);
> >>  dbi_result_free(result);
> >>  result = NULL;
> >>}
> >>   }
> >>  return result;
> >> }
> >>
> >> In threads,
> >>
> >> dbi_result result = QueryDB(queryStr);
> >> if (NULL != result)
> >> {
> >>  while (dbi_result_next_row(result))
> >>  {
> >>varibale = dbi_result_get_ (...);
> >>... /* Do some processing */
> >>  }
> >>  dbi_result_free(result);
> >> }
> >>
> >> We do not know what we are doing wrong. Do we have to take care of
> >> locking across threads in our application?
> 
> 
> Did you read ?
> How did you build your SQLite libraries?
> Is DbConnection a shared object?
> 
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[Karthik] 
SQLite library is compiled with -DSQLITE_THREADSAFE=1.
Yes, DbConnection is a shared object. My bad.

Thank a lot for your help.

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

Re: [sqlite] Transactions + .read

2010-03-31 Thread Simon Slavin 

On 31 Mar 2010, at 6:39pm, Kevin M. wrote:

>>> So, is there an API or query I can run to load an sql file all in one go 
>>> (one transaction) without having to read in the file manually and query one 
>>> line at a time?
> 
>> No, sorry.  That's what programming languages are for.  Open the file, read 
>> a line, execute it using sqlite3_exec(), read the next line, execute that, 
>> etc..  It shouldn't be much more than a five line loop.  Don't forget to 
>> trap errors.
> 
> Guess I'll adapt some existing code then to wrap all the lines sent in one 
> big transaction.

Just a note that the SQL command files produced by the command-line tool itself 
already have all necessary transaction commands in.  And they have other 
characteristics which may look strange, but actually speed up the execution of 
the SQL commands.  For instance, indices are created after the data is entered 
into the tables.  Where this does have any effect it does lead to smaller 
runtimes.

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

Re: [sqlite] Transactions + .read

2010-03-31 Thread Kevin M. 
>> Actually I was wanting to put that into a program.


> I don't know how I guessed that but I'm feeling really smug now.

I'm feeling very amused by your smugness ;-)

>> So, is there an API or query I can run to load an sql file all in one go 
>> (one transaction) without having to read in the file manually and query one 
>> line at a time?

> No, sorry.  That's what programming languages are for.  Open the file, read a 
> line, execute it using sqlite3_exec(), read the next line, execute that, 
> etc..  It shouldn't be much more than a five line loop.  Don't forget to trap 
> errors.

Guess I'll adapt some existing code then to wrap all the lines sent in one big 
transaction.

> Of course, sqlite3_exec() can execute multiple commands in one go.  So you 
> could open the file, read the whole thing into one huge string, close the 
> file, then execute the string.  That will work perfectly if your have enough 
> memory and disk space.  But if you aren't confident about the size of the 
> text file before you start, it's probably better to do it line by line.

Most of the sql files probably are ok with that approach, but others not so 
much.  Maybe a hybrid approach ... small files all in one go, big ones broken 
up.

> Simon.


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

[sqlite] A proposed change to SQLITE_STMTSTATUS_FULLSCAN_STEP

2010-03-31 Thread D. Richard Hipp 
The sqlite3_stmt_status() interface with the  
SQLITE_STMTSTATUS_FULLSCAN_STEP operator returns the number of full- 
table scan steps that occurred during the evaluation of a prepared  
statement.  This information can be used by debugging logic within an  
application to help locate cases where new indices would improve  
performance.

We are proposing to change the meaning of  
SQLITE_STMTSTATUS_FULLSCAN_STEP slightly so that it only counts full- 
table scan steps for tables in queries that do not include the NOT  
INDEXED clause.

This would technically be an incompatible change.  However, we are  
thinking that nobody ever uses the NOT INDEXED clause (it is an SQLite  
extension and is not standard SQL) nor do many people use  
SQLITE_STMTSTATUS_FULLSCAN_STEP.  And hence, we are guessing that we  
can make this change without breaking any legacy code.  If you think  
otherwise, please let me know.

D. Richard Hipp
d...@hwaci.com



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

Re: [sqlite] Transactions + .read

2010-03-31 Thread Simon Slavin 

On 31 Mar 2010, at 4:48pm, Kevin M. wrote:

>> Warning: From your phrasing it's possible you're thinking of putting a 
>> '.read' command into your program.  The '.read' command is only a command to 
>> the command-line tool.  It's not a function of SQLite, and you can't submit 
>> it as a query in your program.
>> 
>> Simon.
> 
> Actually I was wanting to put that into a program.

I don't know how I guessed that but I'm feeling really smug now.

> So, is there an API or query I can run to load an sql file all in one go (one 
> transaction) without having to read in the file manually and query one line 
> at a time?

No, sorry.  That's what programming languages are for.  Open the file, read a 
line, execute it using sqlite3_exec(), read the next line, execute that, etc..  
It shouldn't be much more than a five line loop.  Don't forget to trap errors.

Of course, sqlite3_exec() can execute multiple commands in one go.  So you 
could open the file, read the whole thing into one huge string, close the file, 
then execute the string.  That will work perfectly if your have enough memory 
and disk space.  But if you aren't confident about the size of the text file 
before you start, it's probably better to do it line by line.

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

Re: [sqlite] Select * from tablename results in an exception

2010-03-31 Thread Burnett, Joe 
Hi Pavel,

Thank you for the background information. It helps me understand what I
observed.

Thanks again,

Joe 

Joe Burnett | Principal Software Engineer | FIDELITY INSTITUTIONAL
TECHNOLOGY
2 Contra Way
Merrimack, NH 03054
603.791.5113
cell: 603.289.0481


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Pavel Ivanov
Sent: Wednesday, March 31, 2010 11:30 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Select * from tablename results in an exception

> I had issues in my SQLite DB schema - VARCHAR(64) was not big enough.

Just a note: SQLite doesn't impose any limits on text columns. So for
SQLite VARCHAR(64) is the same type as VARCHAR(1234567), and both
types can accept strings of any length. Apparently your .NET library
parsed declared type of the column and enforced the length you
mentioned.


Pavel

On Tue, Mar 30, 2010 at 5:23 PM, Burnett, Joe 
wrote:
> Thank you all for the replies. The message was a bit of a red herring.
> I had issues in my SQLite DB schema - VARCHAR(64) was not big enough.
>
> Joe Burnett | Principal Software Engineer | FIDELITY INSTITUTIONAL
> TECHNOLOGY
> 2 Contra Way
> Merrimack, NH 03054
> 603.791.5113
> cell: 603.289.0481
>
>
> -Original Message-
> From: sqlite-users-boun...@sqlite.org
> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Tim Romano
> Sent: Tuesday, March 30, 2010 5:20 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Select * from tablename results in an exception
>
>
http://social.msdn.microsoft.com/Forums/en-US/Vsexpressvb/thread/27aec61
> 2-5ca4-41ba-80d6-0204893fdcd1
>
> Maybe related.
> Regards
> Tim Romano
> ___
> 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] Berkeley DB adds SQL using SQLite API !!

2010-03-31 Thread Wiktor Adamski 
> There were many problems with  
> that approach:
...
> (3) Each table and index is in a  
> separate file so your "database" was a directory full of files instead  
> of a single file

This one is not a problem. Actually I don't see how 1 file is better
than 1 directory. For example mac application is a directory not a
file and no one complains. And with several files database would be
faster (for example dropping a table is instant or fragmentation is
handled by OS without need for vacuuming whole database). Also with
current SQLite implementation only tables would be locked by a
transation not a whole database (a few years ago there were even
document on SQLite website listing  splittnig database to several
files as one way to implement table level locks in SQLite).
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Transactions + .read

2010-03-31 Thread Kevin M. 
> Warning: From your phrasing it's possible you're thinking of putting a 
> '.read' command into your program.  The '.read' command is only a command to 
> the command-line tool.  It's not a function of SQLite, and you can't submit 
> it as a query in your program.


> Simon.


Actually I was wanting to put that into a program.  So, is there an API or 
query I can run to load an sql file all in one go (one transaction) without 
having to read in the file manually and query one line at a time?

-- Kevin


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

Re: [sqlite] Transactions + .read

2010-03-31 Thread Simon Slavin 

On 31 Mar 2010, at 4:21pm, Kevin M. wrote:

> Hopefully I'm not duplicating a question, but I looked on sqlite.org and did 
> not find the answer...
> 
> Will this:
> BEGIN TRANSACTION;
> .read somefile.sql
> END TRANSACTION;
> 
> Put everything in the sql file into one transaction?  Or do I need to put the 
> BEGIN/END TRANSACTION statements in the sql file itself?

Either way will work.  Actually I don't know how '.read' works; the code may 
already wrap it inside a transaction, which would make both pointless.  But 
they won't do any harm.

Warning: From your phrasing it's possible you're thinking of putting a '.read' 
command into your program.  The '.read' command is only a command to the 
command-line tool.  It's not a function of SQLite, and you can't submit it as a 
query in your program.

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

Re: [sqlite] Select * from tablename results in an exception

2010-03-31 Thread Pavel Ivanov 
> I had issues in my SQLite DB schema - VARCHAR(64) was not big enough.

Just a note: SQLite doesn't impose any limits on text columns. So for
SQLite VARCHAR(64) is the same type as VARCHAR(1234567), and both
types can accept strings of any length. Apparently your .NET library
parsed declared type of the column and enforced the length you
mentioned.


Pavel

On Tue, Mar 30, 2010 at 5:23 PM, Burnett, Joe  wrote:
> Thank you all for the replies. The message was a bit of a red herring.
> I had issues in my SQLite DB schema - VARCHAR(64) was not big enough.
>
> Joe Burnett | Principal Software Engineer | FIDELITY INSTITUTIONAL
> TECHNOLOGY
> 2 Contra Way
> Merrimack, NH 03054
> 603.791.5113
> cell: 603.289.0481
>
>
> -Original Message-
> From: sqlite-users-boun...@sqlite.org
> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Tim Romano
> Sent: Tuesday, March 30, 2010 5:20 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Select * from tablename results in an exception
>
> http://social.msdn.microsoft.com/Forums/en-US/Vsexpressvb/thread/27aec61
> 2-5ca4-41ba-80d6-0204893fdcd1
>
> Maybe related.
> Regards
> Tim Romano
> ___
> 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] Transactions + .read

2010-03-31 Thread Kevin M. 
Hopefully I'm not duplicating a question, but I looked on sqlite.org and did 
not find the answer...

Will this:
BEGIN TRANSACTION;
.read somefile.sql
END TRANSACTION;

Put everything in the sql file into one transaction?  Or do I need to put the 
BEGIN/END TRANSACTION statements in the sql file itself?

-- Kevin


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

Re: [sqlite] bug: output contains values of a column not listed in SELECT

2010-03-31 Thread Dan Kennedy 

On Mar 31, 2010, at 10:23 AM, Kirill Simonov wrote:

> Hi,
>
> I encountered a weird bug.  My query has the form
> SELECT col_x, ... FROM my_table ... GROUP BY 1
> but the output of the query looks as if it was produced by a query of
> the form
> SELECT col_y, ... FROM my_table ... GROUP BY 1
> That is, instead of values of col_x, I'm getting values of col_y,  
> which
> is not in the SELECT clause.

   http://www.sqlite.org/src/info/883034dcb5

Should be fixed now:

   http://www.sqlite.org/src/ci/ffc23409c7

Please test out the change in your application if you are able to.

Dan.

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

[sqlite] Typo in SQLite documentation

2010-03-31 Thread Nicolas Kaiser 
Hi there!

I just spotted a typo in the SQLite documentation at
http://www.sqlite.org/lang_datefunc.html
"SQL As Understood By SQLite: Date And Time Functions"


In the "Examples" section,

 Compute the number of days since the signing of the US Declaration of 
Independent.

 SELECT julianday('now') - julianday('1776-07-04');

should better be

 Compute the number of days since the signing of the US Declaration of 
Independence.


Moreover, according to its Wikipedia article,
http://en.wikipedia.org/wiki/United_States_Declaration_of_Independence#Signing

 quote "Although the wording of the Declaration was approved on July 4, the 
date of its signing has been disputed. Most historians have concluded that it 
was signed nearly a month after its adoption, on August 2, 1776, and not on 
July 4 as is commonly believed."

it might be safer to say

 Compute the number of days since the ratification of the US Declaration of 
Independence.


:)

Best wishes,
Nicolas Kaiser
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Fwd: Sqlite3 crashing on multithreaded program

2010-03-31 Thread Jens Miltner 

Am 31.03.2010 um 15:14 schrieb Dan Kennedy:

> 
> 
> Begin forwarded message:
> 
>> From: "Periasamy, Karthikeyan" 
>> Date: March 31, 2010 7:52:55 PM GMT+07:00
>> To: "danielk1...@gmail.com" 
>> Subject: Re: [sqlite] Sqlite3 crashing on multithreaded program
>> 
>> Hi,
>> 
>> Thanks for your reply.
>> 
>> In our application the block of code which access the DB looks like  
>> this,
>> 
>> QueryDB(char *queryStr)
>> {
>>   result = dbi_conn_query(DbConnection, queryStr);
>>   if (result)
>>   {
>>if(ZERO_RESULT == dbi_result_get_numrows(result))
>>{
>>  syslog( LOG_DEBUG,"[%s:%s] Empty Result. Freeing result pointer 
>> \n", __FILE__, __FUNCTION__);
>>  dbi_result_free(result);
>>  result = NULL;
>>}
>>   }
>>  return result;
>> }
>> 
>> In threads,
>> 
>> dbi_result result = QueryDB(queryStr);
>> if (NULL != result)
>> {
>>  while (dbi_result_next_row(result))
>>  {
>>varibale = dbi_result_get_ (...);
>>... /* Do some processing */
>>  }
>>  dbi_result_free(result);
>> }
>> 
>> We do not know what we are doing wrong. Do we have to take care of  
>> locking across threads in our application?


Did you read ?
How did you build your SQLite libraries?
Is DbConnection a shared object?


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

[sqlite] Intermittent "database or disk is full" errors with BEGIN EXCLUSIVE on windows

2010-03-31 Thread Nasron Cheong 
Occasionally a user is getting a "database or disk is full" during the call
to start an exclusive transaction. My conditions:

- The disk has plenty of space.
- I'm using persistent journaling mode.

I'm assuming this is due to some antivirus or other process holding the file
and causing this failure.

My guess brings me to the function "winDelete" in the amalgamation, which is
retrying the deletion of a file up to 5 times (.5 seconds) and fails after.
When I hit this issue the first time, I turned on persistent journaling to
avoid calls to the delete function, but I don't know if anything else is
calling delete, or if any other os functions fail after a certain number of
retries.

If I increase MX_DELETION_ATTEMPTS, will this help with my issue? Or are
there other options, or situations that can cause the error? Technically we
can tell users to turn off antivirus on the folder where the sqlite file
resides, but it's not a robust solution.

Thanks!

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

[sqlite] Fwd: Sqlite3 crashing on multithreaded program

2010-03-31 Thread Dan Kennedy 


Begin forwarded message:

> From: "Periasamy, Karthikeyan" 
> Date: March 31, 2010 7:52:55 PM GMT+07:00
> To: "danielk1...@gmail.com" 
> Subject: Re: [sqlite] Sqlite3 crashing on multithreaded program
>
> Hi,
>
> Thanks for your reply.
>
> In our application the block of code which access the DB looks like  
> this,
>
> QueryDB(char *queryStr)
> {
>result = dbi_conn_query(DbConnection, queryStr);
>if (result)
>{
> if(ZERO_RESULT == dbi_result_get_numrows(result))
> {
>   syslog( LOG_DEBUG,"[%s:%s] Empty Result. Freeing result pointer 
> \n", __FILE__, __FUNCTION__);
>   dbi_result_free(result);
>   result = NULL;
> }
>}
>   return result;
> }
>
> In threads,
>
> dbi_result result = QueryDB(queryStr);
> if (NULL != result)
> {
>   while (dbi_result_next_row(result))
>   {
> varibale = dbi_result_get_ (...);
> ... /* Do some processing */
>   }
>   dbi_result_free(result);
> }
>
> We do not know what we are doing wrong. Do we have to take care of  
> locking across threads in our application?
>
> Regards,
> Karthik

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

Re: [sqlite] output contains values of a column not listed inSELECT

2010-03-31 Thread Igor Tandetnik 
Kirill Simonov wrote:
> The following code reproduces the problem:
> ==
> CREATE TABLE person (
> org_id  TEXT NOT NULL,
> nicknameTEXT NOT NULL,
> license TEXT,
> CONSTRAINT person_pk
> PRIMARY KEY (org_id, nickname),
> CONSTRAINT person_license_uk
> UNIQUE (license)
> );
> 
> INSERT INTO person (org_id, nickname, license) VALUES
> ('meyers', 'jack', '2GAT123');
> INSERT INTO person (org_id, nickname, license) VALUES
> ('meyers', 'hill', 'V345FMP');
> INSERT INTO person (org_id, nickname, license) VALUES
> ('meyers', 'jim', '2GAT138');
> INSERT INTO person (org_id, nickname, license) VALUES
> ('smith', 'maggy', '');
> INSERT INTO person (org_id, nickname, license) VALUES
> ('smith', 'jose', 'JJZ109');
> INSERT INTO person (org_id, nickname, license) VALUES
> ('smith', 'jack', 'THX138');
> INSERT INTO person (org_id, nickname, license) VALUES
> ('lakeside', 'dave', '953OKG');
> INSERT INTO person (org_id, nickname, license) VALUES
> ('lakeside', 'amy', NULL);
> INSERT INTO person (org_id, nickname, license) VALUES
> ('lake-apts', 'tom', NULL);
> INSERT INTO person (org_id, nickname, license) VALUES
> ('acorn', 'hideo', 'CQB421');
> 
> SELECT
> org_id,
> COUNT((NOT (org_id IS NULL)) AND (NOT (nickname IS NULL)))
> FROM person
> WHERE (CASE WHEN license != '' THEN 1 ELSE 0 END)
> GROUP BY 1;
> ==
> 
> The final SELECT statement produces the following output:
> 953OKG|1
> V345FMP|1
> THX138|3
>> 2

Yes, reproduces here too. Certainly looks like a bug. If I simplify WHERE 
clause to "where license != '' " then it works as expected.
-- 
Igor Tandetnik

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

[sqlite] bug: output contains values of a column not listed in SELECT

2010-03-31 Thread Kirill Simonov 
Hi,

I encountered a weird bug.  My query has the form
 SELECT col_x, ... FROM my_table ... GROUP BY 1
but the output of the query looks as if it was produced by a query of 
the form
 SELECT col_y, ... FROM my_table ... GROUP BY 1
That is, instead of values of col_x, I'm getting values of col_y, which 
is not in the SELECT clause.


The following code reproduces the problem:
==
CREATE TABLE person (
 org_id  TEXT NOT NULL,
 nicknameTEXT NOT NULL,
 license TEXT,
 CONSTRAINT person_pk
 PRIMARY KEY (org_id, nickname),
 CONSTRAINT person_license_uk
 UNIQUE (license)
);

INSERT INTO person (org_id, nickname, license) VALUES
 ('meyers', 'jack', '2GAT123');
INSERT INTO person (org_id, nickname, license) VALUES
 ('meyers', 'hill', 'V345FMP');
INSERT INTO person (org_id, nickname, license) VALUES
 ('meyers', 'jim', '2GAT138');
INSERT INTO person (org_id, nickname, license) VALUES
 ('smith', 'maggy', '');
INSERT INTO person (org_id, nickname, license) VALUES
 ('smith', 'jose', 'JJZ109');
INSERT INTO person (org_id, nickname, license) VALUES
 ('smith', 'jack', 'THX138');
INSERT INTO person (org_id, nickname, license) VALUES
 ('lakeside', 'dave', '953OKG');
INSERT INTO person (org_id, nickname, license) VALUES
 ('lakeside', 'amy', NULL);
INSERT INTO person (org_id, nickname, license) VALUES
 ('lake-apts', 'tom', NULL);
INSERT INTO person (org_id, nickname, license) VALUES
 ('acorn', 'hideo', 'CQB421');

SELECT
 org_id,
 COUNT((NOT (org_id IS NULL)) AND (NOT (nickname IS NULL)))
FROM person
WHERE (CASE WHEN license != '' THEN 1 ELSE 0 END)
GROUP BY 1;
==

The final SELECT statement produces the following output:
953OKG|1
V345FMP|1
THX138|3
|2

which is totally incorrect.  Note that the values in the first column of 
the output are not from the column 'org_id', which is listed in the 
SELECT clause, but from the column 'license'.

The correct output is:
acorn|1
lakeside|1
meyers|3
smith|2

Minor modifications either in the query itself or in the table data or 
metadata make the problem disappear.

Tested with the latest sqlite binary from www.sqlite.org under Linux.


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