Re: [sqlite] Bug in sqlite3_step

2012-07-03 Thread Dan Kennedy 

On 07/04/2012 03:30 AM, Jay A. Kreibich wrote:

On Tue, Jul 03, 2012 at 04:26:42PM -0400, Richard Hipp scratched on the wall:


Actually, you can bind on a DDL statement, but bindings are only valid for
the lifetime of the statement itself, not for the whole lifetime of the
object created by the CREATE statement.


   Is that a side-effect of the fact that CREATE statements are copied
   into the sqlite_master table as literals, and not re-written?  (Is
   that even true?)


Pretty much. SQLite messes around with the start of CREATE TABLE
statements to remove the "TEMP" keyword or fully qualified database
name if one is present, but otherwise just copies the users input
into the sqlite_master table. i.e. if you do:

  CREATE TABLE main.x1(a, b);

SQLite rewrites "CREATE TABLE main." as "CREATE TABLE ". The
rest of the CREATE TABLE is stored in sqlite_master as is.


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

Re: [sqlite] Bug in sqlite3_step

2012-07-03 Thread Igor Tandetnik 
Robert Myers  wrote:
> DROP TABLE ? would've been useful for me.

Parameters can only appear where literals would be allowed by the syntax. A 
table name is not a literal.
-- 
Igor Tandetnik

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

Re: [sqlite] Bug in sqlite3_step

2012-07-03 Thread Robert Myers 

On 7/3/2012 3:26 PM, Richard Hipp wrote:


Igor is correct.

Actually, you can bind on a DDL statement, but bindings are only valid for
the lifetime of the statement itself, not for the whole lifetime of the
object created by the CREATE statement.  So doing such bindings are
pointless.  And you can't really bind to a DROP statement, because there
are no expressions in a DROP statement to bind to.  But these are all
details.  Igor's explanation is simple and to the point.



DROP TABLE ? would've been useful for me. As it is, I just generate the 
statement on the fly. That's something that gives me the willies, even 
though I'm fairly certain I have full control of the statement since I'm 
in SEE.


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

Re: [sqlite] Can I rely on this being ordered?

2012-07-03 Thread Bart Smissaert 
Thanks for that tip, useful to know that one.

RBS


On Tue, Jul 3, 2012 at 11:37 PM, Roger Binns  wrote:
> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> On 03/07/12 14:03, Bart Smissaert wrote:
>> OK, thanks, that confirms my suspicion then.
>
> SQLite can also help you.  Run your test suite normally, and then run
> again with this pragma which gives a different order to unordered selects.
>  Your test suite should help pick up places where you assumed an ordering:
>
>  http://www.sqlite.org/pragma.html#pragma_reverse_unordered_selects
>
> Roger
> -BEGIN PGP SIGNATURE-
> Version: GnuPG v1.4.11 (GNU/Linux)
>
> iEYEARECAAYFAk/zdDAACgkQmOOfHg372QQNDACgtxix1/92FxlMKP2ZfYIxFDLz
> qf8AoIityNNnSao0Jh75Vs67swDqvQkw
> =QF+6
> -END PGP SIGNATURE-
> ___
> 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] Can I rely on this being ordered?

2012-07-03 Thread Petite Abeille 

On Jul 3, 2012, at 11:03 PM, Bart Smissaert wrote:

> OK, thanks, that confirms my suspicion then.


PRAGMA reverse_unordered_selects = boolean;

When enabled, this PRAGMA causes SELECT statements without an ORDER BY clause 
to emit their results in the reverse order of what they normally would. This 
can help debug applications that are making invalid assumptions about the 
result order.

SQLite makes no guarantees about the order of results if a SELECT omits the 
ORDER BY clause. Even so, the order of results does not change from one run to 
the next, and so many applications mistakenly come to depend on the arbitrary 
output order whatever that order happens to be. However, sometimes new versions 
of SQLite will contain optimizer enhancements that will cause the output order 
of queries without ORDER BY clauses to shift. When that happens, applications 
that depend on a certain output order might malfunction. By running the 
application multiple times with this pragma both disabled and enabled, cases 
where the application makes faulty assumptions about output order can be 
identified and fixed early, reducing problems that might be caused by linking 
against a different version of SQLite.

http://www.sqlite.org/pragma.html#pragma_reverse_unordered_selects

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

Re: [sqlite] Can I rely on this being ordered?

2012-07-03 Thread Roger Binns 
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 03/07/12 14:03, Bart Smissaert wrote:
> OK, thanks, that confirms my suspicion then.

SQLite can also help you.  Run your test suite normally, and then run
again with this pragma which gives a different order to unordered selects.
 Your test suite should help pick up places where you assumed an ordering:

  http://www.sqlite.org/pragma.html#pragma_reverse_unordered_selects

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAk/zdDAACgkQmOOfHg372QQNDACgtxix1/92FxlMKP2ZfYIxFDLz
qf8AoIityNNnSao0Jh75Vs67swDqvQkw
=QF+6
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Can I rely on this being ordered?

2012-07-03 Thread Bart Smissaert 
OK, thanks, that confirms my suspicion then.

RBS


On Tue, Jul 3, 2012 at 10:00 PM, Igor Tandetnik  wrote:
> On 7/3/2012 4:53 PM, Bart Smissaert wrote:
>>
>> However if I do this:
>>
>> SELECT READ_CODE, TERM30, TERM60, ENTRY_COUNT
>> FROM
>> READCODE
>> WHERE
>> TERM30 LIKE '%ANGINA%'
>> UNION
>> SELECT READ_CODE, TERM30, TERM60, ENTRY_COUNT
>> FROM
>> READCODE
>> WHERE
>> TERM60 LIKE '%ANGINA%'
>>
>> Then I get the required ascending order on READ_CODE. This looks good
>> as this query is some 50% faster.
>> However, I am not sure if I can rely on this order to always happen.
>
>
> I'd rather not if I were you. It's likely just an accident of
> implementation. Generally, SQLite doesn't guarantee any particular order of
> the records unless there's an explicit ORDER BY clause.
> --
> 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] Can I rely on this being ordered?

2012-07-03 Thread Nico Williams 
In SQL you cannot rely on the result set being in any order unless you
use ORDER BY.  This is true in SQLite3 as well.

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

[sqlite] Can I rely on this being ordered?

2012-07-03 Thread Bart Smissaert 
Have the following table:

CREATE TABLE READCODE(
[SUBJECT_TYPE] TEXT,
[READ_CODE] TEXT,
[TERM30] TEXT,
[TERM60] TEXT,
[ENTRY_COUNT] INTEGER)

Records are ordered ascending on READ_CODE as the records are obtained
from an ordered array and inserted sequentially.
There is a non-unique index on Read code.

Now I need to run queries like this:

SELECT DISTINCT
READ_CODE, TERM30, TERM60, ENTRY_COUNT
FROM
READCODE
WHERE
TERM30 LIKE '%ANGINA%' OR TERM60 LIKE '%ANGINA%'

Now if I do this then the ascending order on READ_CODE is lost, so I
need to add an ORDER BY

However if I do this:

SELECT READ_CODE, TERM30, TERM60, ENTRY_COUNT
FROM
READCODE
WHERE
TERM30 LIKE '%ANGINA%'
UNION
SELECT READ_CODE, TERM30, TERM60, ENTRY_COUNT
FROM
READCODE
WHERE
TERM60 LIKE '%ANGINA%'

Then I get the required ascending order on READ_CODE. This looks good
as this query is some 50% faster.
However, I am not sure if I can rely on this order to always happen.
I take it that this an unintended outcome that might change in future versions?
Is this indeed the case?

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

Re: [sqlite] Bug in sqlite3_step

2012-07-03 Thread Jay A. Kreibich 
On Tue, Jul 03, 2012 at 04:26:42PM -0400, Richard Hipp scratched on the wall:

> Actually, you can bind on a DDL statement, but bindings are only valid for
> the lifetime of the statement itself, not for the whole lifetime of the
> object created by the CREATE statement.

  Is that a side-effect of the fact that CREATE statements are copied
  into the sqlite_master table as literals, and not re-written?  (Is
  that even true?)

   -j

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

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

Re: [sqlite] Bug in sqlite3_step

2012-07-03 Thread Richard Hipp 
On Tue, Jul 3, 2012 at 4:18 PM, Yuriy Kaminskiy  wrote:

> Igor Tandetnik wrote:
> > On 7/3/2012 10:05 AM, Unsupported wrote:
> >>  // case 1: exception
> >>  //verify(sqlite3_prepare_v2(db, "create trigger updater
> >> update of result on plugins"
> >>  //  " begin"
> >>  //  " update mails set kav=case old.result when
> >> 'infected' then ? else 0 end where uid=old.uid;"
> >>  //  " end;"
> >>  //  , -1, , 0) == SQLITE_OK);
> >>  //verify(sqlite3_bind_int(stmt, ++idx, -1) == SQLITE_OK);
> >
> > You can't use parameters with DDL statements (all kinds of CREATE
> > SOMETHING, DROP SOMETHING et al). You can only parameterize DML
> > statements - SELECT, INSERT, UPDATE and DELETE.
>
> Hmm... I don't think this is incorrect per se, but where is this
> documented?
> I have not found any warning about this in prepare/bind/lang_expr/...
> documentation.
>

Igor is correct.

Actually, you can bind on a DDL statement, but bindings are only valid for
the lifetime of the statement itself, not for the whole lifetime of the
object created by the CREATE statement.  So doing such bindings are
pointless.  And you can't really bind to a DROP statement, because there
are no expressions in a DROP statement to bind to.  But these are all
details.  Igor's explanation is simple and to the point.

Yuriy is also correct that I need to update the documentation to make this
clear.  Or, maybe even throw an error at prepare-time or run-time if you
attempt to bind on a CREATE statement, so that people to don't normally
ready the documentation will also become aware of the limitation.


>
> ___
> 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] Bug in sqlite3_step

2012-07-03 Thread Yuriy Kaminskiy 
Igor Tandetnik wrote:
> On 7/3/2012 10:05 AM, Unsupported wrote:
>>  // case 1: exception
>>  //verify(sqlite3_prepare_v2(db, "create trigger updater
>> update of result on plugins"
>>  //  " begin"
>>  //  " update mails set kav=case old.result when
>> 'infected' then ? else 0 end where uid=old.uid;"
>>  //  " end;"
>>  //  , -1, , 0) == SQLITE_OK);
>>  //verify(sqlite3_bind_int(stmt, ++idx, -1) == SQLITE_OK);
> 
> You can't use parameters with DDL statements (all kinds of CREATE
> SOMETHING, DROP SOMETHING et al). You can only parameterize DML
> statements - SELECT, INSERT, UPDATE and DELETE.

Hmm... I don't think this is incorrect per se, but where is this documented?
I have not found any warning about this in prepare/bind/lang_expr/... 
documentation.

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

Re: [sqlite] Consequences of lexicographic sorting of keys in SQLite4?

2012-07-03 Thread Richard Hipp 
On Tue, Jul 3, 2012 at 3:59 PM, Igor Tandetnik  wrote:

> On 7/3/2012 3:50 PM, Steven E. Harris wrote:
>
>> The first paragraph mentions that the encoding allows comparison of keys
>> with memcmp(), which makes it sound like an entire key -- meaning the
>> concatenation of several values -- can be be compared in one operation
>> against another key.
>>
>> The second paragraph notes:
>>
>> ,
>> | Keys are compared value by value, from left to right, until a difference
>> | if found. The first difference determines the key order.
>> `
>>
>> Does "value by value" here mean that before comparison takes place, the
>> key must be split apart into values, and then each pairwise value from
>> each of the two keys can be compared with one call to memcmp() for each
>> pair of values, or does "value by value" mean simply that the
>> concatenated values' bytes will be compared from left to right, with one
>> call to memcmp() for the entire key?
>>
>
> It doesn't matter. The whole point is that the key is constructed in such
> a way that both procedures would arrive at the same result.
>

Exactly.

"Value by value" means conceptually what happens.  The actual
implementation is a single memcmp() since that is so much faster.


>  --
> Igor Tandetnik
>
>
> __**_
> 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] Consequences of lexicographic sorting of keys in SQLite4?

2012-07-03 Thread Igor Tandetnik 

On 7/3/2012 3:50 PM, Steven E. Harris wrote:

The first paragraph mentions that the encoding allows comparison of keys
with memcmp(), which makes it sound like an entire key -- meaning the
concatenation of several values -- can be be compared in one operation
against another key.

The second paragraph notes:

,
| Keys are compared value by value, from left to right, until a difference
| if found. The first difference determines the key order.
`

Does "value by value" here mean that before comparison takes place, the
key must be split apart into values, and then each pairwise value from
each of the two keys can be compared with one call to memcmp() for each
pair of values, or does "value by value" mean simply that the
concatenated values' bytes will be compared from left to right, with one
call to memcmp() for the entire key?


It doesn't matter. The whole point is that the key is constructed in 
such a way that both procedures would arrive at the same result.

--
Igor Tandetnik

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

Re: [sqlite] Consequences of lexicographic sorting of keys in SQLite4?

2012-07-03 Thread Steven E. Harris 
Richard Hipp  writes:

> The keys are encoded (see
> http://www.sqlite.org/src4/doc/trunk/www/key_encoding.wiki) in a way
> that causes a lexicographical ordering of the keys to correspond to
> what the user wants out of ORDER BY.

The first paragraph mentions that the encoding allows comparison of keys
with memcmp(), which makes it sound like an entire key -- meaning the
concatenation of several values -- can be be compared in one operation
against another key.

The second paragraph notes:

,
| Keys are compared value by value, from left to right, until a difference
| if found. The first difference determines the key order.
`

Does "value by value" here mean that before comparison takes place, the
key must be split apart into values, and then each pairwise value from
each of the two keys can be compared with one call to memcmp() for each
pair of values, or does "value by value" mean simply that the
concatenated values' bytes will be compared from left to right, with one
call to memcmp() for the entire key?

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

Re: [sqlite] Consequences of lexicographic sorting of keys in SQLite4?

2012-07-03 Thread Steven E. Harris 
Richard Hipp  writes:

> The keys are encoded (see
> http://www.sqlite.org/src4/doc/trunk/www/key_encoding.wiki) in a way
> that causes a lexicographical ordering of the keys to correspond to
> what the user wants out of ORDER BY.

I don't understand why the example entries for values 99.0, 99.01, and
99.0001 have first bytes of 0xb4 and 0xb5, respectively.

If we take 99 and use it as "X" in the "2*X+0" formula, shouldn't we
wind up with 198, or 0xC6? Likewise, for the second two examples, I'd
expect 2*99+1 to yield 199, or 0xC7. (The later entry for  has bytes
0xC7 and 0xC6, which match my expectation.)

One more nit: In the paragraph beginning with "If the numeric value is
exactly zero," there's an extra "then" which should have been "the"
instead:

  s/then then/then the/

Thank you for documenting all of this. It's ugly for a good reason,
which makes it beautiful at the same time.

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

Re: [sqlite] Consequences of lexicographic sorting of keys in SQLite4?

2012-07-03 Thread Steven E. Harris 
Richard Hipp  writes:

> The keys are encoded (see
> http://www.sqlite.org/src4/doc/trunk/www/key_encoding.wiki) in a way
> that causes a lexicographical ordering of the keys to correspond to
> what the user wants out of ORDER BY.

I don't understand why the example entries for values 99.0, 99.01, and
99.0001 have first bytes of 0xb4 and 0xb5, respectively.

If we take 99 and use it as "X" in the "2*X+0" formula, shouldn't we
wind up with 198, or 0xC6? Likewise, for the second two examples, I'd
expect 2*99+1 to yield 199, or 0xC7. (The later entry for  has bytes
0xC7 and 0xC6, which match my expectation.)

One more nit: In the paragraph beginning with "If the numeric value is
exactly zero," there's an extra "then" which should have been "the"
instead:

  s/then then/then the/

Thank you for documenting all of this. It's ugly for a good reason,
which makes it beautiful at the same time.

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

Re: [sqlite] Bug in sqlite3_step

2012-07-03 Thread Igor Tandetnik 

On 7/3/2012 10:05 AM, Unsupported wrote:

 // case 1: exception
 //verify(sqlite3_prepare_v2(db, "create trigger updater update of result on 
plugins"
 //  " begin"
 //  " update mails set kav=case old.result when 'infected' then ? else 
0 end where uid=old.uid;"
 //  " end;"
 //  , -1, , 0) == SQLITE_OK);
 //verify(sqlite3_bind_int(stmt, ++idx, -1) == SQLITE_OK);


You can't use parameters with DDL statements (all kinds of CREATE 
SOMETHING, DROP SOMETHING et al). You can only parameterize DML 
statements - SELECT, INSERT, UPDATE and DELETE.

--
Igor Tandetnik

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

[sqlite] Bug: outer visibility of table names inside nested joins is position dependent

2012-07-03 Thread biziclop 
This query works as expected:

SELECT aa.*,bb.*
FROM ((SELECT 1 AS a) AS aa JOIN (SELECT 3 AS b) AS bb)
JOIN ((SELECT 2 AS c) AS cc JOIN (SELECT 3 AS d) AS dd);

But If we choose a table from the second nest, SQLite throws "no such table" 
error:

SELECT cc.*,dd.*
FROM ((SELECT 1 AS a) AS aa JOIN (SELECT 3 AS b) AS bb)
JOIN ((SELECT 2 AS c) AS cc JOIN (SELECT 3 AS d) AS dd);

Bug is present in "SQLite version 3.7.13 2012-06-11 02:05:22"

SQLFiddle:
http://sqlfiddle.com/#!5/d41d8/109




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

[sqlite] Bug in sqlite3_step

2012-07-03 Thread Unsupported 
Hello.

It looks like I found a bug in sqlite3_step. Here is the sample
code.

#include 
#include 
#include 
#include "sqlite3.h"

#ifdef _DEBUG
#define verify(f)  assert(f)
#else
#define verify(f)  ((void)(f))
#endif

char const * const fname = "bug.db3";

int cb(void*, int, char**, char**)
{
// If here, then got kav=-1 for uid=2
printf("ok");
return 0;
}

int main()
{
sqlite3* db = 0;
char* err;

DeleteFileA(fname);

verify(sqlite3_open(fname, ) == SQLITE_OK);

verify(sqlite3_exec(db, "drop table if exists mails;", 0, 0, ) == 
SQLITE_OK);
verify(sqlite3_exec(db, "drop table if exists plugins;", 0, 0, ) == 
SQLITE_OK);

verify(sqlite3_exec(db
, "create table if not exists mails ("
"uid integer not null primary key unique"
", kav integer not null default 0"
");"
, 0, 0, ) == SQLITE_OK);

verify(sqlite3_exec(db
, "create table plugins ("
"uid integer not null"
", plugin varchar(32) not null"
", result varchar(32) not null"
", primary key (uid, plugin));"
, 0, 0, ) == SQLITE_OK);

verify(sqlite3_exec(db, "insert into mails (uid) values (1);", 0, 0, 
) == SQLITE_OK);
verify(sqlite3_exec(db, "insert into mails (uid) values (2);", 0, 0, 
) == SQLITE_OK);
verify(sqlite3_exec(db, "insert into plugins (uid, plugin, result) 
values (1, 'kav', 'clean');", 0, 0, ) == SQLITE_OK);
verify(sqlite3_exec(db, "insert into plugins (uid, plugin, result) 
values (2, 'kav', 'infected');", 0, 0, ) == SQLITE_OK);

sqlite3_stmt* stmt = 0;

#if 0

// Work's correctly 
verify(sqlite3_prepare_v2(db, "create trigger updater update of result 
on plugins"
" begin"
" update mails set kav=case old.result when 'infected' then -1 
else 0 end where uid=old.uid;"
" end;"
, -1, , 0) == SQLITE_OK);

// In this case I get kav=-1 for uid=2 in mails table
#else

int idx = 0;

// case 1: exception
//verify(sqlite3_prepare_v2(db, "create trigger updater update of 
result on plugins"
//  " begin"
//  " update mails set kav=case old.result when 'infected' then ? 
else 0 end where uid=old.uid;"
//  " end;"
//  , -1, , 0) == SQLITE_OK);
//verify(sqlite3_bind_int(stmt, ++idx, -1) == SQLITE_OK);

// case 2: exception
//verify(sqlite3_prepare_v2(db, "create trigger updater update of 
result on plugins"
//  " begin"
//  " update mails set kav=case old.result when 'infected' then -1 
else ? end where uid=old.uid;"
//  " end;"
//  , -1, , 0) == SQLITE_OK);
//verify(sqlite3_bind_int(stmt, ++idx, 0) == SQLITE_OK);

// case 3: nothing happens
//verify(sqlite3_prepare_v2(db, "create trigger updater update of 
result on plugins"
//  " begin"
//  " update mails set kav=case old.result when ? then -1 else 0 
end where uid=old.uid;"
//  " end;"
//  , -1, , 0) == SQLITE_OK);
//verify(sqlite3_bind_text(stmt, ++idx, "infected", -1, SQLITE_STATIC) 
== SQLITE_OK);

// case 4: nothing happens
verify(sqlite3_prepare_v2(db, "create trigger updater update of result 
on plugins"
" begin"
" update mails set kav=case old.result when ? then ? else 0 end 
where uid=old.uid;"
" end;"
, -1, , 0) == SQLITE_OK);

verify(sqlite3_bind_text(stmt, ++idx, "infected", -1, SQLITE_STATIC) == 
SQLITE_OK);
verify(sqlite3_bind_int(stmt, ++idx, -1) == SQLITE_OK);

// In this case I get kav=0 for uid=2 in mails table

#endif

verify(sqlite3_step(stmt) == SQLITE_DONE);
verify(sqlite3_finalize(stmt) == SQLITE_OK);

verify(sqlite3_exec(db, "update plugins set result=result;", 0, 0, 
) == SQLITE_OK);

verify(sqlite3_exec(db, "select kav from mails where kav=-1;", cb, 0, 
) == SQLITE_OK);
verify(sqlite3_close(db) == SQLITE_OK);

return 0;
}

-- 
Best regards,
 Unsupported  mailto:unsuppor...@mail.ru

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

Re: [sqlite] max size of a TEXT field

2012-07-03 Thread nobre 
http://www.sqlite.org/limits.html

--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/max-size-of-a-TEXT-field-tp63069p63070.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

Re: [sqlite] Update trigger fires for all rows even if sqlite3_changes returns 0

2012-07-03 Thread Paul van Helden 
>
>
>   Then why do you keep hammering on the idea that SQLite is somehow
>   incorrect or wrong?
>
>   You've explained what you're trying to do.  We've explained there is
>   a better way to do that, that also happens to provide the correct
>   answer on all platforms, AND likely runs faster-- especially if any
>   of those columns has an index on them.
>

I gave a simple example. I work with generic cases. My application doesn't
have all static SQL. A lot is from the user or built dynamically.

>
> > What if the SET and WHERE contain many columns?
>
>   Then you're asking for a more complex operation.  Your SQL gets a bit
>   more complex as well.
>
> > Now I have to add a "WHERE column<>mynewval" for every column in SET
> > to get the actual changes, something like UPDATE testtable SET col1=?1,
> > col2=?2, col3=? WHERE  complex where clause
> > AND col1<>?1 AND col2<>?2 AND col3<>?3.
>
> > (passing a null parameter to the above won't even work!)
>
>   Well, no, it won't, because you're using the wrong operator.
>
>   Use "WHERE col1 IS NOT ?1 AND..." and it all works fine.
>
> OK thanks, so I should always use IS NOT where I always used <>. Oh well
(talk about yuck!)


> > No surprises there. Oracle has never managed to impress me.
>
>   I know what you mean.  That MySQL database they make is difficult to
>   take seriously.
>
> Very funny. They didn't make it, they own it now.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Update trigger fires for all rows even if sqlite3_changes returns 0

2012-07-03 Thread Jay A. Kreibich 
On Tue, Jul 03, 2012 at 03:21:57PM +0200, Paul van Helden scratched on the wall:
> On Tue, Jul 3, 2012 at 3:03 PM, Black, Michael (IS)
> wrote:
> 
> > And Oracle says the opposite:
> >
> > Yet they all give the same answer when done with "update testtable set
> > testrow=null where testrow not null;
>
> You keep hammering this one, it is obvious, I understand, THANKS!

  Then why do you keep hammering on the idea that SQLite is somehow
  incorrect or wrong?

  You've explained what you're trying to do.  We've explained there is
  a better way to do that, that also happens to provide the correct
  answer on all platforms, AND likely runs faster-- especially if any
  of those columns has an index on them.

> What if the SET and WHERE contain many columns?

  Then you're asking for a more complex operation.  Your SQL gets a bit
  more complex as well.

> Now I have to add a "WHERE column<>mynewval" for every column in SET
> to get the actual changes, something like UPDATE testtable SET col1=?1,
> col2=?2, col3=? WHERE  complex where clause
> AND col1<>?1 AND col2<>?2 AND col3<>?3.

> (passing a null parameter to the above won't even work!)

  Well, no, it won't, because you're using the wrong operator.

  Use "WHERE col1 IS NOT ?1 AND..." and it all works fine.

> No surprises there. Oracle has never managed to impress me.

  I know what you mean.  That MySQL database they make is difficult to
  take seriously. 

   -j

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

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

Re: [sqlite] Update trigger fires for all rows even if sqlite3_changes returns 0

2012-07-03 Thread Pavel Ivanov 
On Tue, Jul 3, 2012 at 10:55 AM, Jay A. Kreibich  wrote:
> On Tue, Jul 03, 2012 at 02:43:29PM +0200, Paul van Helden scratched on the 
> wall:
>
>> >   The statement "UPDATE table SET column=NULL" updates every row in the
>> >   table.  The fact that some rows may already have a NULL in that
>> >   column is not important.
>> >
>> > Well, it is important to me, the word "change" means before != after :-)
>
>   You can argue about the naming of the _change() function all you
>   want.  It is a non-standard extension and the function operates as
>   documented.  If you want to call it poorly named, go ahead.  That
>   doesn't change what it does.
>
>   There is, however, little argument that the trigger is doing exactly
>   what one would expect.  You are applying an update operation to every
>   row, and the trigger is firing for every row.

BTW, I think you can add to trigger "WHEN NEW.column IS NOT
OLD.column" and it will fire only for rows where column value has
really changed (beware "IS NOT" with arbitrary right side works only
on SQLite 3.6.19 and above).

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

Re: [sqlite] Update trigger fires for all rows even if sqlite3_changes returns 0

2012-07-03 Thread Jay A. Kreibich 
On Tue, Jul 03, 2012 at 02:43:29PM +0200, Paul van Helden scratched on the wall:

> >   The statement "UPDATE table SET column=NULL" updates every row in the
> >   table.  The fact that some rows may already have a NULL in that
> >   column is not important.
> >
> > Well, it is important to me, the word "change" means before != after :-)

  You can argue about the naming of the _change() function all you
  want.  It is a non-standard extension and the function operates as
  documented.  If you want to call it poorly named, go ahead.  That
  doesn't change what it does.

  There is, however, little argument that the trigger is doing exactly
  what one would expect.  You are applying an update operation to every
  row, and the trigger is firing for every row.

   -j

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

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

Re: [sqlite] Update trigger fires for all rows even if sqlite3_changes returns 0

2012-07-03 Thread Pavel Ivanov 
On Tue, Jul 3, 2012 at 9:21 AM, Paul van Helden  wrote:
>> So rather than holding your breath for Oracle to change I'd recommend you
>> do it the portable way.
>>
> I'm not waiting for anything. My last question was simple: which is
> better? Since MySQL does it the "correct way" perhaps we can just think
> about this for sqlite4?

That's definitely not a correct way. It could be "intuitive" for those
who doesn't know SQL well. But for anybody else it's counter-intuitive
and I would be really disappointed if SQLite will implement that.

So the answer to your last question (as Michael already said): better
to write in SQL what you really want to do and not expect for SQL
engine to guess it for you. If you find writing complex WHERE clause
too complicated then don't use sqlite3_changes() function.


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

Re: [sqlite] Update trigger fires for all rows even if sqlite3_changes returns 0

2012-07-03 Thread Paul van Helden 
On Tue, Jul 3, 2012 at 3:03 PM, Black, Michael (IS)
wrote:

> And Oracle says the opposite:
>
> Yet they all give the same answer when done with "update testtable set
> testrow=null where testrow not null;
>
> You keep hammering this one, it is obvious, I understand, THANKS!  What if
the SET and WHERE contain many columns? Now I have to add a "WHERE
column<>mynewval" for every column in SET to get the actual changes,
something like UPDATE testtable SET col1=?1, col2=?2, col3=? WHERE  AND col1<>?1 AND col2<>?2 AND col3<>?3.
(passing a null parameter to the above won't even work!)

>
> Connected to:
> Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit
> Production
> With the Partitioning, Oracle Label Security, OLAP, Data Mining,
> Oracle Database Vault and Real Application Testing options
> SQL> create table testtable(testrow number);
> Table created.
> SQL> insert into testtable values(NULL);
> 1 row created.
> SQL> insert into testtable values(NULL);
> 1 row created.
> SQL> insert into testtable values(NULL);
> 1 row created.
> SQL> update testtable set testrow=null;
> 3 rows updated.
> SQL> update testtable set testrow=null;
> 3 rows updated.
>

No surprises there. Oracle has never managed to impress me.


> SQL> update testtable set testrow=null where testrow is not null;
>
> 0 rows updated.
>
> So rather than holding your breath for Oracle to change I'd recommend you
> do it the portable way.
>
>  I'm not waiting for anything. My last question was simple: which is
better? Since MySQL does it the "correct way" perhaps we can just think
about this for sqlite4?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Update trigger fires for all rows even if sqlite3_changes returns 0

2012-07-03 Thread Black, Michael (IS) 
And Oracle says the opposite:

Yet they all give the same answer when done with "update testtable set 
testrow=null where testrow not null;


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
SQL> create table testtable(testrow number);
Table created.
SQL> insert into testtable values(NULL);
1 row created.
SQL> insert into testtable values(NULL);
1 row created.
SQL> insert into testtable values(NULL);
1 row created.
SQL> update testtable set testrow=null;
3 rows updated.
SQL> update testtable set testrow=null;
3 rows updated.
SQL> update testtable set testrow=null where testrow is not null;

0 rows updated.

So rather than holding your breath for Oracle to change I'd recommend you do it 
the portable way.


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


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Paul van Helden [p...@planetgis.co.za]
Sent: Tuesday, July 03, 2012 7:47 AM
To: j...@kreibi.ch; General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] Update trigger fires for all rows even if 
sqlite3_changes returns 0

On Tue, Jul 3, 2012 at 2:43 PM, Paul van Helden wrote:

>
>>   The statement "UPDATE table SET column=NULL" updates every row in the
>>   table.  The fact that some rows may already have a NULL in that
>>   column is not important.
>>
>> Well, it is important to me, the word "change" means before != after :-)
>

Just checked MySQL:

UPDATE testtable SET testrow=NULL;
Affected rows: 40
UPDATE testtable SET testrow=NULL;
Affected rows: 0

That is what I'm familiar with.
___
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] Update trigger fires for all rows even if sqlite3_changes returns 0

2012-07-03 Thread Paul van Helden 
On Tue, Jul 3, 2012 at 2:45 PM, Black, Michael (IS)
wrote:

> What's better is that it tells you what you asked for...not what you think
> you asked for...which it does.
>
I asked for changes :-)

>
> You've already been shown the correct solution...a WHERE clause...
>
> I've done that even before posting here, just thought it odd.

> You want sqlite to do a complete record compare, including following
> update triggers, on EVERY record it looks at to see if something happened
> to change???
>
Just the fields in the SET clause.

>
> Yuck...
>
> As compare to the WHERE clause which does exactly what you want and runs
> oodles faster (in all likelihood).
>
> I always keep an eye on the "affected rows" to see what my statements have
done (whether I used a WHERE or not). Even with a WHERE, I would prefer
seeing actual changes :-)
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Update trigger fires for all rows even if sqlite3_changes returns 0

2012-07-03 Thread Paul van Helden 
On Tue, Jul 3, 2012 at 2:43 PM, Paul van Helden wrote:

>
>>   The statement "UPDATE table SET column=NULL" updates every row in the
>>   table.  The fact that some rows may already have a NULL in that
>>   column is not important.
>>
>> Well, it is important to me, the word "change" means before != after :-)
>

Just checked MySQL:

UPDATE testtable SET testrow=NULL;
Affected rows: 40
UPDATE testtable SET testrow=NULL;
Affected rows: 0

That is what I'm familiar with.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Update trigger fires for all rows even if sqlite3_changes returns 0

2012-07-03 Thread Black, Michael (IS) 
What's better is that it tells you what you asked for...not what you think you 
asked for...which it does.

You've already been shown the correct solution...a WHERE clause...

You want sqlite to do a complete record compare, including following update 
triggers, on EVERY record it looks at to see if something happened to change???

Yuck...

As compare to the WHERE clause which does exactly what you want and runs oodles 
faster (in all likelihood).



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


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Paul van Helden [p...@planetgis.co.za]
Sent: Tuesday, July 03, 2012 7:39 AM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] Update trigger fires for all rows even if 
sqlite3_changes returns 0

You are right, sorry, just checked. sqlite3_changes returns number of
records hit, not changed. Have been using sqlite for 2 years now and was
always under the impression this was for actual changes.

But which is better behaviour, reporting "row hits" versus real changes?
Especially when it comes to triggers?

On Tue, Jul 3, 2012 at 2:19 PM, Yuriy Kaminskiy  wrote:

> Paul van Helden wrote:
> > Is this correct? Should update triggers not only fire for actual
> changes? I
> > have a large table with a column which contains all NULL values except
> for
> > 4. I expected an UPDATE table SET column=NULL to only fire 4 triggers,
> > except it fires for every row.
>
> I'm pretty sure that sqlite3_changes() in this case also returns *all*
> rows, not
> only 4 "really" changed. If you want triggers to only fire for really
> changed
> rows (and sqlite3_changes() to return only those 4 rows), you should add
> WHERE
> clause:
> UPDATE table SET column=NULL WHERE column IS NOT NULL;
>
> ___
> 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] Update trigger fires for all rows even if sqlite3_changes returns 0

2012-07-03 Thread Paul van Helden 
>
>
>   The statement "UPDATE table SET column=NULL" updates every row in the
>   table.  The fact that some rows may already have a NULL in that
>   column is not important.
>
> Well, it is important to me, the word "change" means before != after :-)
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Update trigger fires for all rows even if sqlite3_changes returns 0

2012-07-03 Thread Paul van Helden 
You are right, sorry, just checked. sqlite3_changes returns number of
records hit, not changed. Have been using sqlite for 2 years now and was
always under the impression this was for actual changes.

But which is better behaviour, reporting "row hits" versus real changes?
Especially when it comes to triggers?

On Tue, Jul 3, 2012 at 2:19 PM, Yuriy Kaminskiy  wrote:

> Paul van Helden wrote:
> > Is this correct? Should update triggers not only fire for actual
> changes? I
> > have a large table with a column which contains all NULL values except
> for
> > 4. I expected an UPDATE table SET column=NULL to only fire 4 triggers,
> > except it fires for every row.
>
> I'm pretty sure that sqlite3_changes() in this case also returns *all*
> rows, not
> only 4 "really" changed. If you want triggers to only fire for really
> changed
> rows (and sqlite3_changes() to return only those 4 rows), you should add
> WHERE
> clause:
> UPDATE table SET column=NULL WHERE column IS NOT NULL;
>
> ___
> 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] Update trigger fires for all rows even if sqlite3_changes returns 0

2012-07-03 Thread Jay A. Kreibich 
On Tue, Jul 03, 2012 at 01:32:14PM +0200, Paul van Helden scratched on the wall:
> Hi,
> 
> Is this correct? Should update triggers not only fire for actual changes? I
> have a large table with a column which contains all NULL values except for
> 4. I expected an UPDATE table SET column=NULL to only fire 4 triggers,
> except it fires for every row.

  The statement "UPDATE table SET column=NULL" updates every row in the
  table.  The fact that some rows may already have a NULL in that
  column is not important.

  If you only want the trigger to fire for non-NULL rows, you need to
  update only the non-NULL rows:

UPDATE table SET column=NULL WHERE column IS NOT NULL;

  As for sqlite3_changes() returning 0, that doesn't sound right unless
  you're checking inside the trigger.

   -j

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

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

Re: [sqlite] Update trigger fires for all rows even if sqlite3_changes returns 0

2012-07-03 Thread Yuriy Kaminskiy 
Paul van Helden wrote:
> Is this correct? Should update triggers not only fire for actual changes? I
> have a large table with a column which contains all NULL values except for
> 4. I expected an UPDATE table SET column=NULL to only fire 4 triggers,
> except it fires for every row.

I'm pretty sure that sqlite3_changes() in this case also returns *all* rows, not
only 4 "really" changed. If you want triggers to only fire for really changed
rows (and sqlite3_changes() to return only those 4 rows), you should add WHERE
clause:
UPDATE table SET column=NULL WHERE column IS NOT NULL;

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

[sqlite] Update trigger fires for all rows even if sqlite3_changes returns 0

2012-07-03 Thread Paul van Helden 
Hi,

Is this correct? Should update triggers not only fire for actual changes? I
have a large table with a column which contains all NULL values except for
4. I expected an UPDATE table SET column=NULL to only fire 4 triggers,
except it fires for every row.

Thanks,

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