Re: [sqlite] [BUG] sqlite3_exec "BEGIN; ROLLBACK" corrupts statement already running

2012-08-23 Thread Joey Adams 
On Fri, Aug 24, 2012 at 12:45 AM, Pavel Ivanov  wrote:
> This is a documented change. See http://www.sqlite.org/releaselog/3_7_11.html:
>
> "Pending statements no longer block ROLLBACK. Instead, the pending
> statement will return SQLITE_ABORT upon next access after the
> ROLLBACK."
>
> There was even some explanation of reasons for that somewhere on the list.

Thanks for the quick response.  This looks like the thread you are referring to:

http://osdir.com/ml/sqlite-users/2012-04/msg00638.html
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [BUG] sqlite3_exec "BEGIN; ROLLBACK" corrupts statement already running

2012-08-23 Thread Pavel Ivanov 
This is a documented change. See http://www.sqlite.org/releaselog/3_7_11.html:

"Pending statements no longer block ROLLBACK. Instead, the pending
statement will return SQLITE_ABORT upon next access after the
ROLLBACK."

There was even some explanation of reasons for that somewhere on the list.


Pavel


On Thu, Aug 23, 2012 at 9:03 PM, Joey Adams  wrote:
> Consider the following operations (full test program attached):
>
> stmt <- prepare conn "SELECT * FROM foo"
> Row <- step stmt
> exec conn "BEGIN; ROLLBACK"
> Row <- step stmt
>
> Namely, we prepare a statement with sqlite3_prepare_v2, call
> sqlite3_step (giving us SQLITE_ROW).  While the statement is busy, we
> jump in and do this:
>
> rc = sqlite3_exec(conn, "BEGIN; ROLLBACK", NULL, NULL, NULL);
>
> On SQLite 3.6.22, this sqlite3_exec call returns SQLITE_BUSY, and the
> subsequent sqlite3_step returns SQLITE_ROW.
>
> On SQLite 3.7.13, this sqlite3_exec call returns SQLITE_OK, but the
> subsequent sqlite3_step returns SQLITE_ABORT.
>
> The latter result looks bogus to me.
>
> #define SQLITE_ABORT4   /* Callback routine requested an abort */
>
> We're not doing anything with a callback routine.  And according to
> the documentation for ROLLBACK:
>
> The ROLLBACK will fail with an error code SQLITE_BUSY if there are
> any pending queries.
>
> Does this include queries started before the transaction begin?  Should it?
>
> When the sequence above is performed, I would expect one of two
> results (preferably the first):
>
>  * The exec "BEGIN; ROLLBACK" has no effect on the prepared statement
> that has already started.
>
>  * The ROLLBACK fails with SQLITE_BUSY (the 3.6.22 behavior).
>
> The 3.7.13 behavior definitely looks wrong.  Is this a bug, or is it
> undefined behavior to BEGIN or ROLLBACK while a prepared statement is
> running on the same connection?
>
> Thanks,
> -Joey
>
> ___
> 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] [BUG] sqlite3_exec "BEGIN; ROLLBACK" corrupts statement already running

2012-08-23 Thread Joey Adams 
Consider the following operations (full test program attached):

stmt <- prepare conn "SELECT * FROM foo"
Row <- step stmt
exec conn "BEGIN; ROLLBACK"
Row <- step stmt

Namely, we prepare a statement with sqlite3_prepare_v2, call
sqlite3_step (giving us SQLITE_ROW).  While the statement is busy, we
jump in and do this:

rc = sqlite3_exec(conn, "BEGIN; ROLLBACK", NULL, NULL, NULL);

On SQLite 3.6.22, this sqlite3_exec call returns SQLITE_BUSY, and the
subsequent sqlite3_step returns SQLITE_ROW.

On SQLite 3.7.13, this sqlite3_exec call returns SQLITE_OK, but the
subsequent sqlite3_step returns SQLITE_ABORT.

The latter result looks bogus to me.

#define SQLITE_ABORT4   /* Callback routine requested an abort */

We're not doing anything with a callback routine.  And according to
the documentation for ROLLBACK:

The ROLLBACK will fail with an error code SQLITE_BUSY if there are
any pending queries.

Does this include queries started before the transaction begin?  Should it?

When the sequence above is performed, I would expect one of two
results (preferably the first):

 * The exec "BEGIN; ROLLBACK" has no effect on the prepared statement
that has already started.

 * The ROLLBACK fails with SQLITE_BUSY (the 3.6.22 behavior).

The 3.7.13 behavior definitely looks wrong.  Is this a bug, or is it
undefined behavior to BEGIN or ROLLBACK while a prepared statement is
running on the same connection?

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

Re: [sqlite] [PATCH] Fix "Symbol not found: _OSAtomicCompareAndSwapPtrBarrier" on Mac OS X 10.4 (Tiger)

2012-08-23 Thread Richard Hipp 
On Tue, Aug 21, 2012 at 8:54 PM, Dwayne Litzenberger wrote:

> Recent versions of SQLite fail to run on Mac OS X 10.4 (Tiger) with
> the following error message:
>

We don't test SQLite in MacOS 10.4, but we do test every release on MacOS
10.2.  For that platform, we add the compile-time option:

-DSQLITE_WITHOUT_ZONEMALLOC

Doing the same should clear the problem on MacOS 10.4 as well.  And using
that approach will save us from having to maintain a bunch of hard-to-test
preprocessor logic.


>
> $ DYLD_LIBRARY_PATH=. ./sqlite3
> dyld: lazy symbol binding failed: Symbol not found:
> _OSAtomicCompareAndSwapPtrBarrier
>   Referenced from: ./libsqlite3.0.dylib
>   Expected in: /usr/lib/libSystem.B.dylib
>
> dyld: Symbol not found: _OSAtomicCompareAndSwapPtrBarrier
>   Referenced from: ./libsqlite3.0.dylib
>   Expected in: /usr/lib/libSystem.B.dylib
>
> Trace/BPT trap
>
> This is because OSAtomicCompareAndSwapPtrBarrier was not introduced
> until Mac OS X 10.5 (Leopard).
>
> Affected versions:
>
> - Latest trunk (fossil 45cdc32f1e cloned from https://sqlite.org/src/)
> - SQLite 3.7.13
> - SQLite 3.7.12
>
> Not affected:
>
> - SQLite 3.7.11
> - SQLite 3.7.10
> - SQLite 3.7.9
>
> Build environment:
>
> Mac OS X SDK: MacOSX10.6.sdk
>
> $ xcodebuild -version
> Xcode 3.2.5
> Component versions: DevToolsCore-1763.0; DevToolsSupport-1758.0
> BuildVersion: 10M2423
>
> $ gcc --version
> i686-apple-darwin10-gcc-4.2.1 (GCC) 4.2.1 (Apple Inc. build 5664)
>
> How to reproduce:
>
> I built SQLite on a Mac OS X 10.5 (Leopard) machine, then copied
> .libs/ to a machine running 10.4 (Tiger) and ran sqlite3 from there:
>
> tar xzf sqlite-autoconf-3071300.tar.gz
> mkdir bld
> cd bld
> CFLAGS='-arch ppc -arch i386 -mmacosx-version-min=10.4' \
> LDFLAGS='-arch ppc -arch i386 -mmacosx-version-min=10.4' \
> ../sqlite-autoconf-3071300/configure --without-dependency-tracking
> make
> rsync --delete -a .libs/ tigerbox:~/sqlite-scratch/
> ssh -t tigerbox "cd ~/sqlite-scratch && DYLD_LIBRARY_PATH=. ./sqlite3"
>
> I should get a regular sqlite prompt.  What I actually got was the
> "Symbol not found" error above.
>
> It looks like other people have seen this issue before[1], but it does
> not look like it's actually been fixed.
>
> Please apply the attached patch against the latest trunk (45cdc32f1e),
> which fixes this bug.  (You can ignore the git commit id in the patch.
>  It wasn't obvious to me how to make fossil output a commit as a
> patch, so just I did a "git init" inside the fossil working directory
> and then worked from that.)
>
> Cheers,
> - Dwayne
>
> [1] http://sqlite.org:8080/pipermail/sqlite-users/2012-January/036184.html
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>


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

Re: [sqlite] error group_concat

2012-08-23 Thread Richard Hipp 
On Fri, Jun 15, 2012 at 9:22 AM, Luigi  wrote:

> CREATE TABLE t1 (A1 INTEGER NOT NULL,A2 INTEGER NOT NULL,A3 INTEGER NOT
> NULL,A4 INTEGER NOT NULL,PRIMARY KEY(A1));
> REPLACE INTO t1 VALUES(1,11,111,);
> REPLACE INTO t1 VALUES(2,22,222,);
> REPLACE INTO t1 VALUES(3,33,333,);
> CREATE TABLE t2 (B1 INTEGER NOT NULL,B2 INTEGER NOT NULL,B3 INTEGER NOT
> NULL,B4 INTEGER NOT NULL,PRIMARY KEY(B1));
> REPLACE INTO t2 VALUES(1,88,888,);
> REPLACE INTO t2 VALUES(2,99,999,);
>
> SELECT (SELECT GROUP_CONCAT(CASE WHEN a1=1 THEN'A' ELSE 'B' END) FROM
> t2),t1.*
> FROM t1;
>
> from Sqlite 3.7.12
> return error misuse of aggregate
>

Fixed in http://www.sqlite.org/src/info/d4cd6017c9


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



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

Re: [sqlite] to table update

2012-08-23 Thread Rob Richardson 
That sets the value of the col1 field in every row in tb1 to the value 
retrieved from tb2.  I doubt that's what he wants, but he didn't tell us, so 
maybe it is.

RobR

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Igor Tandetnik
Sent: Thursday, August 23, 2012 9:12 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] to table update

yanhong.ye  wrote:
> update tb1 set col1=(select col1 from tb2 ) where tb1.co2=tb2.co2;

The closing parenthesis is in the wrong place:

update tb1 set col1=(select col1 from tb2 where tb1.co2=tb2.co2);

-- 
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] SQLite - Database disk size difference when tables copied

2012-08-23 Thread Richard Hipp 
On Thu, Aug 23, 2012 at 1:31 AM, Pavel Ivanov  wrote:

> > When I implement the same mechanism for saving the
> > memory database back to disk, the size of disk file is 4x of the original
> > disk file size.
>
> What is "original disk file size" here? Is it an empty database,
> database with some data, database with exactly the same data you are
> restoring? If the latter then do you restore right over the existing
> data? If yes how?
>

Please get the "sqlite3_analyzer.exe" utility from the download
pageand run it on your database
file both before and after the 4x size
increase.   Maybe send us the output of each run, if it isn't obvious where
the size increase is coming from.



>
> Pavel
>
>
> On Wed, Aug 22, 2012 at 10:00 PM, Madhur Kashyap
>  wrote:
> > Hello,
> >
> > I am using Tcl Extension (TEA) of SQLite inside an EDA tool which
> supports
> > TCL User Shell. I have a strange problem that SQLite version 3.7.10 is
> > downgraded to 3.5.9 when I load the necessary .so files. Due to this
> change
> > the backup and restore commands are probably causing a core dump in the
> EDA
> > tool execution.
> >
> > I have to implement save and restore capability for the memory database
> > used in the application. Restore I have implemented using traditional
> > method to first attach the disk database and then copy all tables listed
> in
> > sqlite_master table. When I implement the same mechanism for saving the
> > memory database back to disk, the size of disk file is 4x of the original
> > disk file size.
> >
> > What is the reason for this size increase? What can I do to avoid this
> file
> > size increase? A long pending question - Why the version is getting
> > downgraded to 3.5.9 ? The Tcl version is 8.4.x within the EDA tool. Also
> I
> > cannot find any SQLite libraries within the tool installation.
> >
> > dbcmd eval "ATTACH DATABASE 'test_disk.db' AS copyto"
> > set tableNames [ dbcmd eval {SELECT name FROM sqlite_master WHERE type
> > = 'table'} ]
> > foreach tname $tableNames {
> > dbcmd eval "CREATE TABLE copyto.$tname AS SELECT * FROM $tname"
> > }
> >
> > --
> > Regards
> > Madhur Kashyap
> > ___
> > 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
>



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

Re: [sqlite] to table update

2012-08-23 Thread Igor Tandetnik 
yanhong.ye  wrote:
> update tb1 set col1=(select col1 from tb2 ) where tb1.co2=tb2.co2;

The closing parenthesis is in the wrong place:

update tb1 set col1=(select col1 from tb2 where tb1.co2=tb2.co2);

-- 
Igor Tandetnik

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

Re: [sqlite] to table update

2012-08-23 Thread Rob Richardson 
Are you certain there exist rows in tb1 and tb2 that satisfy the condition?  
What happens when you try?  Is any error message or number returned?  Can you 
run the same query inside an SQLite management tool like SQLite Spy?  Does it 
work there?  Please provide us ALL of the relevant information when you post.

But in this case, it's fairly easy.  (Easy enough for me to get it right?  
We'll see.)  The subquery is returning more than one value.  That's illegal.  
And I hope you mean to update every single row in tb1, because that's what your 
query will do.

And your main query references tb2 without defining it.

RobR

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of yanhong.ye
Sent: Thursday, August 23, 2012 7:32 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] to table update

update tb1 set col1=(select col1 from tb2 ) where tb1.co2=tb2.co2;

it couldn't work

___
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] Logging only SQL commands which make changes, parsing PRAGMAs

2012-08-23 Thread Kit 
2012/8/23 Simon Slavin :
> I'm trying to log SQL commands which might make changes to the database, but 
> not those which just read it...
> Simon.

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

[sqlite] Logging only SQL commands which make changes, parsing PRAGMAs

2012-08-23 Thread Simon Slavin 
I'm trying to log SQL commands which might make changes to the database, but 
not those which just read it.  This routine does /not/ need to deal with 
arbitrary tricksy SQL commands generated by third parties, just SQL commands I 
myself have written or my program has generated, intended specifically for 
SQLite.  And I'm going to assume that I will be sensible when I program so my 
commands won't start with spaces or play mind games.

Since this routine introduces load on every SQL command, it's okay to have a 
few false positives (but preferably not false negatives), if this makes the 
test simpler and faster.  So as a first approximation it logged everything 
except commands which start with 'SELECT'.  Which is fine.

But I do PRAGMAs every so often and now I notice a few PRAGMA commands in my 
logs which don't need to be there because they just test modes and schema.  So 
I want to log PRAGMA commands which might change things, and ignore those that 
won't.  So does the list think that simply looking for commands which start 
with 'PRAGMA' and have an '=' in them is a good fast way to do this ?  Or is 
there a better way that's still pretty fast ?

The routine is written in PHP, so I don't have access to the low-level VM parts 
of SQLite to put traps in.

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

[sqlite] to table update

2012-08-23 Thread yanhong.ye 
update tb1 set col1=(select col1 from tb2 ) where tb1.co2=tb2.co2;

it couldn't work

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