[sqlite] Intel compiler warnings with 3.3.2

[Miguel Angel Latorre Díaz]

Since these are nots a bug I will post them here my findings with 3.3.2 with 
Intel compiler (one of the best and fast compiled code I've seen) under 
Windows:


attach.c
.\Sqlite\v3\attach.c(36): remark #1418: external definition with no prior 
declaration

 int resolveAttachExpr(NameContext *pName, Expr *pExpr)
This means, this function can be made static (static function addresses 
usually occupy a word instead of a sizeof (void*), in win32)


.\Sqlite\v3\attach.c(179): remark #1599: declaration hides variable "i" 
(declared at line 65)

 int i = db->nDb - 1;

build.c
.\Sqlite\v3\build.c(3160): remark #1599: declaration hides variable "z" 
(declared at line 3139)

   char *z = sqliteStrNDup((const char *)pName1->z, pName1->n);

date.c
.\Sqlite\v3\date.c(508): remark #1572: floating-point equality and 
inequality comparisons are unreliable

  && (n=r)==r && n>=0 && r<7 ){
^

.\Sqlite\v3\date.c(508): remark #1572: floating-point equality and 
inequality comparisons are unreliable

  && (n=r)==r && n>=0 && r<7 ){
^

.\Sqlite\v3\date.c(821): remark #1599: declaration hides variable "n" 
(declared at line 758)

   int n; /* Number of days since 1st day of year */
   ^

delete.c
.\Sqlite\v3\delete.c(213): remark #1599: declaration hides variable "addr" 
(declared at line 94)

   int addr;
   ^

expr.c
.\Sqlite\v3\expr.c(921): remark #1599: declaration hides variable "j" 
(declared at line 820)

 int j;
 ^

.\Sqlite\v3\expr.c(1080): remark #593: variable "pSrcList" was set but never 
used

   SrcList *pSrcList;
^
Missing #ifndef NDEBUG around the variable.

.\Sqlite\v3\expr.c(1383): remark #1599: declaration hides variable "i" 
(declared at line 1363)

 int i;
 ^

.\Sqlite\v3\expr.c(1551): remark #1599: declaration hides variable "op" 
(declared at line 1463)

   int aff, op;
^

insert.c
.\Sqlite\v3\insert.c(300): remark #1599: declaration hides variable "base" 
(declared at line 203)

 int base = sqlite3VdbeCurrentAddr(v);
 ^

.\Sqlite\v3\insert.c(683): remark #1599: declaration hides variable "base" 
(declared at line 203)

 int base = sqlite3VdbeCurrentAddr(v);
 ^

os_win.c
.\Sqlite\v3\os_win.c(517): remark #1419: external declaration in primary 
source file

 int allocateWinFile(winFile *pInit, OsFile **pId);
This means, this function can be made static.

.\Sqlite\v3\os_win.c(1482): warning #300: const variable "zeroData" requires 
an initializer

   static const ThreadData zeroData;
This is tricky, according to C, const variables must be initialized, but 
static variables are initiated to zeroes.


pager.c
.\Sqlite\v3\pager.c(1274): remark #279: controlling expression is constant
   while( 1 ){
  ^
These forms can be translated with not penalty in speed-code into for (;;).

.\Sqlite\v3\pager.c(1447): remark #1599: declaration hides variable "nRec" 
(declared at line 1377)

 u32 nRec;
 ^

.\Sqlite\v3\pager.c(2559): remark #1599: declaration hides variable "rc" 
(declared at line 2527)

int rc;
^

.\Sqlite\v3\pager.c(2683): remark #1599: declaration hides variable "rc" 
(declared at line 2527)

   int rc;
   ^

printf.c
.\Sqlite\v3\printf.c(599): remark #1599: declaration hides variable "c" 
(declared at line 212)

 int i, j, n, c, isnull;
  ^
.\Sqlite\v3\printf.c(601): remark #1599: declaration hides parameter "arg" 
(declared at line 207)

 char *arg = va_arg(ap,char*);
   ^

select.c
.\Sqlite\v3\select.c(24): remark #1418: external definition with no prior 
declaration

 void clearSelect(Select *p){
  ^
This means, this function can be made static.

.\Sqlite\v3\select.c(560): remark #1599: declaration hides parameter "aff" 
(declared at line 457)

 char aff = (iParm>>16)&0xFF;
  ^
.\Sqlite\v3\select.c(1212): remark #1599: declaration hides variable "pTab" 
(declared at line 1081)

   Table *pTab = pFrom->pTab;
  ^
.\Sqlite\v3\select.c(1227): remark #1599: declaration hides variable "pLeft" 
(declared at line 1223)

   struct SrcList_item *pLeft = >a[i-1];
^
vdbe.c
.\Sqlite\v3\vdbe.c(2974): remark #1599: declaration hides variable "rc" 
(declared at line 396)

 int res, rc;
  ^
.\Sqlite\v3\vdbe.c(3811): remark #1599: declaration hides variable "rc" 
(declared at line 396)

 int res, rc;
  ^

where.c
.\Sqlite\v3\where.c(427): remark #1599: declaration hides variable "k" 
(declared at line 416)

 int k;
 ^
.\Sqlite\v3\where.c(2088): remark #1599: declaration hides variable "i" 
(declared at line 2026)

   int i, j, last;
   ^ 

Re: [sqlite] Same error is flagged in 3.2.8 for sqlite3GenerateConstraintChecks() [was [sqlite] Known issue in 3.2.7 in vaccum?

[drh]

Russell Leighton <[EMAIL PROTECTED]> wrote:
> Thx.
> 
> I'll re-test with 3.3.1.
> 
> That said, isn't 3.2.8 supposed to be the stable release?
> 

That depends on what you mean by "stable".  If you mean
"unchanging", then yes, 3.2.8 is stable.  The 3.2.x series
is closed and will never again change. All new code, all bug 
fixes, etc, go into 3.3.x.

Please do not be mislead by the "alpha" label on 3.3.1.  
Unless you are using the newer features, the CVS head
probably has far fewer bugs than 3.2.8.

--
D. Richard Hipp <[EMAIL PROTECTED]>

Re: [sqlite] Same error is flagged in 3.2.8 for sqlite3GenerateConstraintChecks() [was [sqlite] Known issue in 3.2.7 in vaccum?

[Russell Leighton]

Thx.

I'll re-test with 3.3.1.

That said, isn't 3.2.8 supposed to be the stable release?

[EMAIL PROTECTED] wrote:


Russell Leighton <[EMAIL PROTECTED]> wrote:
 


Recompiled using 3.2.8 of sqlite, same issue is flagged by valgrind.

   



3.2.8 is a single-line change to 3.2.7.  That they show
the same bug is not surprising.

There have been a bazillion minor tweaks to SQLite since
3.2.7/8.  If you see the same problem in 3.3.1 or the latest
from CVS, then I am interested.  But I cannot reproduce your
problem so I am going to assume it has already been fixed.
--
D. Richard Hipp <[EMAIL PROTECTED]>



 

[sqlite] 3.2.8 'make test' fails tests under linux

[Russell Leighton]

I get the following failures under 2 linux environments which might be 
related to the below valgrind issue:


   Failures on these tests: conflict-6.2 conflict-6.3 conflict-6.7
   conflict-6.8 conflict-6.9 conflict-6.10 conflict-6.11 conflict-6.12
   conflict-6.13

..when doing a 'make test' under:

FedoraCore4 , gcc4.1
Gentoo 2.6.14-gentoo-r5, gcc 3.3.5

On gentoo I also get these date failures (which I don't see under FedoraCore4):


   date-6.1 date-6.4 date-6.5 date-6.8 date-6.13 date-6.16



Russell Leighton wrote:



Recompiled using 3.2.8 of sqlite, same issue is flagged by valgrind.


Russell Leighton wrote:



Also, this happens under any constrained insert...from the stack 
trace below you would that that would be true. This is confirmed 
during another test scenario doing an insert statement into a 
constrained tabled where I got the same warning about insert.c:980


Russell Leighton wrote:



During valgrind ( www.valgrind.org ) testing under linux  I was 
executing "vaccum" and got:


==17449== Conditional jump or move depends on uninitialised value(s)
==17449==at 0x1CF2200C: sqlite3GenerateConstraintChecks 
(insert.c:980)

==17449==by 0x1CF233F6: sqlite3Insert (insert.c:629)
==17449==by 0x1CF2B72E: sqlite3Parser (parse.y:600)
==17449==by 0x1CF377BD: sqlite3RunParser (tokenize.c:388)
==17449==by 0x1CF2ED6B: sqlite3_prepare (prepare.c:440)
==17449==by 0x1CF3B376: execSql (vacuum.c:42)
==17449==by 0x1CF3B429: execExecSql (vacuum.c:61)
==17449==by 0x1CF3B721: sqlite3RunVacuum (vacuum.c:207)
==17449==by 0x1CF3D6AD: sqlite3VdbeExec (vdbe.c:4288)
==17449==by 0x1CF40F7B: sqlite3_step (vdbeapi.c:217)

Is this already known, or should I enter a bug?

Are pre-release regression tests done under valgrind or purify? 
Might be a good idea.


Thx

Russ

Re: [sqlite] Same error is flagged in 3.2.8 for sqlite3GenerateConstraintChecks() [was [sqlite] Known issue in 3.2.7 in vaccum?

[drh]

Russell Leighton <[EMAIL PROTECTED]> wrote:
> Recompiled using 3.2.8 of sqlite, same issue is flagged by valgrind.
> 

3.2.8 is a single-line change to 3.2.7.  That they show
the same bug is not surprising.

There have been a bazillion minor tweaks to SQLite since
3.2.7/8.  If you see the same problem in 3.3.1 or the latest
from CVS, then I am interested.  But I cannot reproduce your
problem so I am going to assume it has already been fixed.
--
D. Richard Hipp <[EMAIL PROTECTED]>

[sqlite] Same error is flagged in 3.2.8 for sqlite3GenerateConstraintChecks() [was Re: [sqlite] Known issue in 3.2.7 in vaccum?

[Russell Leighton]

Recompiled using 3.2.8 of sqlite, same issue is flagged by valgrind.


Russell Leighton wrote:



Also, this happens under any constrained insert...from the stack trace 
below you would that that would be true. This is confirmed during 
another test scenario doing an insert statement into a constrained 
tabled where I got the same warning about insert.c:980


Russell Leighton wrote:



During valgrind ( www.valgrind.org ) testing under linux  I was 
executing "vaccum" and got:


==17449== Conditional jump or move depends on uninitialised value(s)
==17449==at 0x1CF2200C: sqlite3GenerateConstraintChecks 
(insert.c:980)

==17449==by 0x1CF233F6: sqlite3Insert (insert.c:629)
==17449==by 0x1CF2B72E: sqlite3Parser (parse.y:600)
==17449==by 0x1CF377BD: sqlite3RunParser (tokenize.c:388)
==17449==by 0x1CF2ED6B: sqlite3_prepare (prepare.c:440)
==17449==by 0x1CF3B376: execSql (vacuum.c:42)
==17449==by 0x1CF3B429: execExecSql (vacuum.c:61)
==17449==by 0x1CF3B721: sqlite3RunVacuum (vacuum.c:207)
==17449==by 0x1CF3D6AD: sqlite3VdbeExec (vdbe.c:4288)
==17449==by 0x1CF40F7B: sqlite3_step (vdbeapi.c:217)

Is this already known, or should I enter a bug?

Are pre-release regression tests done under valgrind or purify? Might 
be a good idea.


Thx

Russ

Re: [sqlite] PRAGMA table_info oddness

[Mike Ashmore]

Well, it's not the prettiest thing in the world, but it definitely  
works. Brilliant!


Many thanks for your help,
-Mike Ashmore

On Jan 23, 2006, at 1:01 PM, Kurt Welgehausen wrote:


Sorry, I didn't read your code carefully enough the first time.
Your approach won't work because origin has no explicit type,
and SQLite will not infer a type from the literal string; the
unknown type defaults to numeric.

As Dennis said, the problem is really with RoR, but you can
work around it by creating two 1-row, 1-column tables. Here's
the idea; this is more or less the A part of your view, so
you'll have to add the union and the B part.

sqlite> pragma table_info(t1);
cid nametypenotnull dflt_value  pk
--  --  --  --  --  --
0   a   integer 0   <>  1
1   c   char0   <>  0
sqlite> create temp table originA (a char);
sqlite> insert into originA values ('a');
sqlite> create temp view va as select t1.*, originA.a origin from  
t1, originA;

sqlite> pragma table_info(va);
cid nametypenotnull dflt_value  pk
--  --  --  --  --  --
0   a   integer 0   <>  0
1   c   char0   <>  0
2   origin  char0   <>  0


Regards

Re: [sqlite] PRAGMA table_info oddness

[Mike Ashmore]

On Jan 22, 2006, at 7:43 PM, Kurt Welgehausen wrote:


There's no string type in SQL. Go to

and read section 2.1.

Regards


Okay, my previous message related to SQL syntax, so I suppose it's  
fair to point out that "string" isn't a SQL type. In fact, I should  
have used the term 'text' most places where the word 'string'  
appeared. But we're missing the point.


What I was trying to get at was, when I create a view based on the  
query "SELECT *, 'a' as origin from a.foo", the origin column in the  
resulting view has (according to PRAGMA table_info(foo)) a 'NUMERIC'  
affinity.


What I'd like is for table_info to report the origin column's  
affinity as TEXT.


So, now that I've got my semantics sorted, does anybody have any  
suggestions for how to make that happen?


Thanks,
-Mike Ashmore

Re: [sqlite] PRAGMA table_info oddness

[Dennis Cote]

Mike Ashmore wrote:


Hi folks,
I'm trying to create a composite view from multiple database files,  
with an extra field for the origin of a particular record. A sample  
scenario:


There's a table, "foo," which exists in two database files, 'a.db3'  
and 'b.db3'. Let's define it as:

CREATE TABLE foo (f1 integer, f2 string);

Now, we open up a :memory: database and do the following:
ATTACH 'a.db3' as a;
ATTACH 'b.db3' as b;
CREATE TEMP VIEW foo AS
SELECT *, 'a' AS origin FROM a.foo
UNION
SELECT *, 'b' AS origin FROM b.foo;

PRAGMA table_info(foo);
gives:
0|f1|numeric|0||0
1|f2|string|0||0
2|origin|numeric|0||0

The problem is that I'd like origin to be reported as type string. Is  
there something in SQL syntax that I've missed which allows me to  
specify this?


I'm trying to integrate the composite view into a Ruby on Rails  
application, and RoR seems to rely on the type reported by the  
table_info pragma to determine what format to use when updating or  
inserting records [1][2].


I've determined this happens with SQLite 3.2.8 and below; I have not  
yet tested against the 3.3.x series.


Thanks in advance for any help you can provide with this,
-Mike Ashmore

[1] Of course one can't insert, update, or delete on a view directly;  
I have a set of INSTEAD OF triggers which ask a separate process to  
modify the tables in their original database files.


[2] If there are any RoR users here who know how to override this  
behavior cleanly (manually specifying column types), I'd also love to  
know about that mechanism.




Mike,

What the table_info pragma shows you is the type affinity of the column 
in the view. SQLite can store any type of value in any table column 
because it is not strongly typed. What happens with your view is that 
the text constants ('a' and 'b') are stored as text in the column origin 
even though it has numeric affinity. You can check this by doing a 
simple query on your view:


select typeof(origin) from foo;

All the origin values should return type 'text'.

RoR should be using the sqlite3_column_type() API to determine the type 
of the values returned from a query. Other APIs like 
sqlite3_column_decltype() and pragma table_info are returning other 
information, not the type of the result value.


HTH
Dennis Cote

Re: [sqlite] Stale master journal files

[drh]

Doug Nebeker <[EMAIL PROTECTED]> wrote:
> I'm currently seeing something that has never happened--about 90 master
> journal files (the -mjX type) that are popping up in my
> database directory.  I'm using 3.2.7 or 3.2.8 (have to double check) on
> Windows.
>  
> The scenario:
> I have two threads that are reading and writing to the database pretty
> heavily at the moment--this has worked fine for quite some time.  The
> 'database' consists of 9 tables, each in it's own database file.  I open
> a connection to one database, and then run ATTACH commands to pull the
> other 8 in.  The master journal file is named after one of the databases
> (the one that opened the connection?).
>  
> Each thread does all reading and writing within a transaction.
> Performance is great, data looks good, everything seems to be working
> well except for the extra files.
>  
> All of the master journal files were created in the first 3 minutes of
> the run--now 15 minutes later no new ones are getting created (at least
> they don't hang around long enough for me to notice), but the original
> 90 are still there.  
>  
> [Waiting]
> All of the read and writer threads have now finished.  The database
> connection was successfully closed and the application has closed down
> nicely.  
>  
> I read at http://www.sqlite.org/lockingv3.html about deleting stale
> master journals.  Does SQLite do that automatically, or do I need to do
> that myself?
>  
> Did I have some sort of major failure that created these in the first
> place?  What can I do to avoid it in the future?
>  

The master journal files are suppose to be deleted automatically
when the transaction commits.  But perhaps there is a bug.  I will
look into it.
--
D. Richard Hipp <[EMAIL PROTECTED]>

RE: [sqlite] Decimal conversion

[nbiggs]

Thanks Dennis, I will give that a shot.

-Original Message-
From: Dennis Cote [mailto:[EMAIL PROTECTED] 
Sent: Monday, January 23, 2006 11:20 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Decimal conversion

nbiggs wrote:

>I was using version 3.2.1.  I downloaded 3.3.1 and your suggestion
>worked, but it is very slow compared to 3.2.1.  I use the original
query
>to test the speed.  3.2.1 returned the data in a little over 10 seconds
>while 3.3.1 took a minute.  Why is that?  
>
>  
>
I don't know. There were some performance issues reported with version 
3.3.1. I believe that version 3.3.2, which should be released shortly, 
will address these issues, but I don't think any of them were causing 
this much of a slowdown. I though they were on the order of 10% slower, 
not 500%.

I wouldn't have expected the conversion calculation to take very long. 
On the other hand, the round function does the same type of calculation 
internally, but it is implemented in C rather than as SQLite VDBE 
opcodes. And, by looking at the explain output, I can see that SQLIte is

actually executing the calculation (both the round, or the cast) twice 
(once for each input row to build a temp table to sort, and once as each

temp table row is scanned to do he group by). That means that the 
performance improvement of the round function is doubled.

Your best bet may be to create a user defined function to truncate a 
number. This would perform the same calculation as the cast expression, 
but be implemented in C. It should execute even faster than the round 
function (Since it wouldn't need to do the conversion to a string that 
round does. The output does need to be converted to a string once to be 
displayed, but that is only done once for each output row in the 
histogram, not twice for each row in the table).

HTH
Dennis Cote

[sqlite] Read Uncommitted

[Dan Petitt]

Hi

I searched the mail archive and documentation but could not find any
'answer' to the new "READ UNCOMMITTED" ability.

Is it a pragma? If it is; is it set when database is opened, or each time
you start a write transaction, or maybe something else?

Any assistance would be appreciated, thanks
Dan

Re: [sqlite] Decimal conversion

[Dennis Cote]

nbiggs wrote:


I was using version 3.2.1.  I downloaded 3.3.1 and your suggestion
worked, but it is very slow compared to 3.2.1.  I use the original query
to test the speed.  3.2.1 returned the data in a little over 10 seconds
while 3.3.1 took a minute.  Why is that?  

 

I don't know. There were some performance issues reported with version 
3.3.1. I believe that version 3.3.2, which should be released shortly, 
will address these issues, but I don't think any of them were causing 
this much of a slowdown. I though they were on the order of 10% slower, 
not 500%.


I wouldn't have expected the conversion calculation to take very long. 
On the other hand, the round function does the same type of calculation 
internally, but it is implemented in C rather than as SQLite VDBE 
opcodes. And, by looking at the explain output, I can see that SQLIte is 
actually executing the calculation (both the round, or the cast) twice 
(once for each input row to build a temp table to sort, and once as each 
temp table row is scanned to do he group by). That means that the 
performance improvement of the round function is doubled.


Your best bet may be to create a user defined function to truncate a 
number. This would perform the same calculation as the cast expression, 
but be implemented in C. It should execute even faster than the round 
function (Since it wouldn't need to do the conversion to a string that 
round does. The output does need to be converted to a string once to be 
displayed, but that is only done once for each output row in the 
histogram, not twice for each row in the table).


HTH
Dennis Cote

[sqlite] Known issue in 3.2.7 in vaccum?

[Russell Leighton]

During valgrind ( www.valgrind.org ) testing under linux  I was 
executing "vaccum" and got:


==17449== Conditional jump or move depends on uninitialised value(s)
==17449==at 0x1CF2200C: sqlite3GenerateConstraintChecks (insert.c:980)
==17449==by 0x1CF233F6: sqlite3Insert (insert.c:629)
==17449==by 0x1CF2B72E: sqlite3Parser (parse.y:600)
==17449==by 0x1CF377BD: sqlite3RunParser (tokenize.c:388)
==17449==by 0x1CF2ED6B: sqlite3_prepare (prepare.c:440)
==17449==by 0x1CF3B376: execSql (vacuum.c:42)
==17449==by 0x1CF3B429: execExecSql (vacuum.c:61)
==17449==by 0x1CF3B721: sqlite3RunVacuum (vacuum.c:207)
==17449==by 0x1CF3D6AD: sqlite3VdbeExec (vdbe.c:4288)
==17449==by 0x1CF40F7B: sqlite3_step (vdbeapi.c:217)

Is this already known, or should I enter a bug?

Are pre-release regression tests done under valgrind or purify? Might be a good 
idea.

Thx

Russ

Re: [sqlite] Two problems

[Robert Simpson]

- Original Message - 
From: "Paul Tomblin" <[EMAIL PROTECTED]>




Quoting Dan Kennedy ([EMAIL PROTECTED]):

> - The second problem appears to be a problem with self-locking.  I'm
>   inserting a "mapping" into a table.  I have a query active to find 
> ids

>   that require mapping, and then I try to find the current lowest
>   unused "mapping" value, and insert it.

Right. You can't modify a table that is currently being scanned by
a SELECT query. One way around this is to make a copy of the table
in a TEMP table for the duration of the operation.


But actually I'm not currently selecting from it.  I prepare two
statements, one for query and one for insert.  The query should only
return 0 or 1 rows.  I do the query, then if it returns 0 rows, I do the
insert.  I repeat this multiple times, using the same prepared statements
each time.  Like I said, this never caused a problem when I did it in
MySQL or PostgreSQL.

I realize that SQLite has to be simplied compared to full features RDBMS,
but it appears that either the SQLite library, or possibly the perl
wrapper, is somehow mistaking a prepared statement which has finished
executing the current query for one whose query is still open.

I suppose the only way I'm going to know for sure is to try writing the
same sequence of events in C, and it's been so long I'm not sure my C
skills are up to it.


All you need to do is make sure you've reset() your row-selecting reader 
before you execute the insert and then it'll work.


Robert

Re: [sqlite] Two problems

[drh]

Paul Tomblin <[EMAIL PROTECTED]> wrote:
> Quoting Dan Kennedy ([EMAIL PROTECTED]):
> > > - The second problem appears to be a problem with self-locking.  I'm
> > >   inserting a "mapping" into a table.  I have a query active to find ids
> > >   that require mapping, and then I try to find the current lowest
> > >   unused "mapping" value, and insert it.
> > 
> > Right. You can't modify a table that is currently being scanned by
> > a SELECT query. One way around this is to make a copy of the table
> > in a TEMP table for the duration of the operation.
> 
> But actually I'm not currently selecting from it.  I prepare two
> statements, one for query and one for insert.  The query should only
> return 0 or 1 rows.  I do the query, then if it returns 0 rows, I do the
> insert.  I repeat this multiple times, using the same prepared statements
> each time. 

Call sqlite3_reset() on the query before doing the insert.
--
D. Richard Hipp <[EMAIL PROTECTED]>

Re: [sqlite] Slow query after reboot

[Geoff Simonds]

Thanks to everyone for all the help on this problem.  I am going to try 
creating a new thread to touch the tables at startup. 


Chris Schirlinger wrote:

We have the same issue, to get around it we fire a thread when the 
program starts, intelligently "touching" every table that the user is 
likely to access (As Michael Sizaki already mentioned a select 
count(last_column) from big_table; will do it)


Since a user is very unlikely to run a program and start doing 
anything complex within the first 10 to 20 seconds, by the time they 
DO try and run a query, it is the "second time" the query has been 
run and this we get the benefit of the Windows XP cache and thus the 
fast speed


 


I have created a client application that is always running on a users
desktop. The application accepts user input and then uses SQLite to
perform a few simple queries against a single db file that contains 4
tables. The performance is fantastic after the initial install and
normal usage. When the user leaves for the night and tries a query in
the morning, the app hangs for 20 seconds and then finally comes back
with the results. If the user then duplicates the query immediately
afterward, the query is almost instantaneous. In addition, if at any
point the user reboots the machine and then retries the query, the same
delay happens.  The time is spent in the SQLiteDataReader.read()
method.  Does anybody have any thoughts on why this is  happening?
Thanks for any help.


   








 

Re: [sqlite] Two problems

[Paul Tomblin]

Quoting Dan Kennedy ([EMAIL PROTECTED]):
> > - The second problem appears to be a problem with self-locking.  I'm
> >   inserting a "mapping" into a table.  I have a query active to find ids
> >   that require mapping, and then I try to find the current lowest
> >   unused "mapping" value, and insert it.
> 
> Right. You can't modify a table that is currently being scanned by
> a SELECT query. One way around this is to make a copy of the table
> in a TEMP table for the duration of the operation.

But actually I'm not currently selecting from it.  I prepare two
statements, one for query and one for insert.  The query should only
return 0 or 1 rows.  I do the query, then if it returns 0 rows, I do the
insert.  I repeat this multiple times, using the same prepared statements
each time.  Like I said, this never caused a problem when I did it in
MySQL or PostgreSQL.

I realize that SQLite has to be simplied compared to full features RDBMS,
but it appears that either the SQLite library, or possibly the perl
wrapper, is somehow mistaking a prepared statement which has finished
executing the current query for one whose query is still open.

I suppose the only way I'm going to know for sure is to try writing the
same sequence of events in C, and it's been so long I'm not sure my C
skills are up to it.

-- 
Paul Tomblin <[EMAIL PROTECTED]> http://xcski.com/blogs/pt/
If you're not part of the solution, you're part of the precipitate.

Re: [sqlite] Version 3.3.0 (alpha)

[drh]

"Roger Binns" <[EMAIL PROTECTED]> wrote:
> >>  ... But new databases created by
> >>  version 3.3.0 will not be readable by older versions
> >>  of SQLite.  If this is a problem for your application,
> >>  compile SQLite using
> >>
> >> -DSQLITE_DEFAULT_FILE_FORMAT=1
> >>
> >>  and then version 3.3.0 will create new databases in
> >>  the legacy format understood by all prior versions of
> >>  SQLite.  DESC indices only work in the new format.
> 
> Is there any chance that could also be set by a pragma?
> The problem is when supplying binary versions of SQLite,
> it isn't immediately obvious which format to make the
> default and developers can't set it even if they do know
> without recompiling themselves.  I'd imagine the packagers
> for various Linux/BSD distributions will have the same
> problem.
> 

PRAGMA legacy_file_format=ON;

--
D. Richard Hipp <[EMAIL PROTECTED]>

[sqlite] Problem with opening

[manoj marathayil]

hai,
  is it possible for two threads to open same database file simultabiously and 
perform an insert stmt?, if not is ther is any way to implement it?

Send instant messages to your online friends http://in.messenger.yahoo.com