Re: [sqlite] basic "Window function"

[Darren Duncan]

On 2014-03-13, 8:17 AM, big stone wrote:

Hello again,

Windowing functions are :
- not easy to workaround with create_function(),
- a truly "sql core" motor functionality.

The only use case that I have, is the small subset I described earlier :
- inside one 'select' :
.  several sum/min/avg/max (different fields) ,
. all with exactly the same "(over partition by ... a series of fields)",
- no ranking, no order by inside this partition.

==> It allows to show a list of records at a certain level, with
statistical analysis done at a completely different (higher or lower) level.

Is it a feature SQLite team would like to do ?

Is there anyone else, (besides little bee), that would "like" this request
?


I know I would like to see this, a lot.

Its one of those things that, if implemented in the core, should not be any more 
effort than it takes to implement aggregate functions with GROUP BY, and it 
would give users a great amount of power.


I was very happy to see WITH get into the core, and windowing is similarly 
something you can get a lot of power from with relatively small core effort.


If SQLite does this, I will be happy that there is yet another significant way 
in which SQLite is more powerful than MySQL (but not PostgreSQL), the WITH 
support being another, and subjecting data definition to transactions is another.


-- Darren Duncan

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

[sqlite] failure to build static library with -DSQLITE_ENABLE_ICU

[Alex Loukissas]

Hello,

I have been trying to build sqlite 3.8.4.1 using the ICU extension and have
seem to have hit an apparent bug in the sqlite code. After much debugging,
it looks like the header  is missing, which causes warnings
like the following to appear:

sqlite3.c: In function 'icuLikeCompare':
sqlite3.c:145704:5: warning: implicit declaration of function
'U8_NEXT_UNSAFE' [-Wimplicit-function-declaration]
 U8_NEXT_UNSAFE(zPattern, iPattern, uPattern);
 ^
sqlite3.c:145725:11: warning: implicit declaration of function
'U8_FWD_1_UNSAFE' [-Wimplicit-function-declaration]
   U8_FWD_1_UNSAFE(zString, iString);
   ^
sqlite3.c: In function 'icuLikeFunc':
sqlite3.c:145804:5: warning: implicit declaration of function 'U8_NEXT'
[-Wimplicit-function-declaration]
 U8_NEXT(zE, i, nE, uEsc);
 ^

This, in turn, manifests as undefined symbol errors in the linker:

./.libs/libsqlite3.a(sqlite3.o): In function `icuLikeCompare':
/home/alex/src/vulcan/build/workdir/sqlite-autoconf-3080401/sqlite3.c:145704:
undefined reference to `U8_NEXT_UNSAFE'
/home/alex/src/vulcan/build/workdir/sqlite-autoconf-3080401/sqlite3.c:145743:
undefined reference to `U8_FWD_1_UNSAFE'
/home/alex/src/vulcan/build/workdir/sqlite-autoconf-3080401/sqlite3.c:145752:
undefined reference to `U8_NEXT_UNSAFE'
/home/alex/src/vulcan/build/workdir/sqlite-autoconf-3080401/sqlite3.c:145725:
undefined reference to `U8_FWD_1_UNSAFE'
/home/alex/src/vulcan/build/workdir/sqlite-autoconf-3080401/sqlite3.c:145736:
undefined reference to `U8_FWD_1_UNSAFE'
./.libs/libsqlite3.a(sqlite3.o): In function `icuOpen':
/home/alex/src/vulcan/build/workdir/sqlite-autoconf-3080401/sqlite3.c:146257:
undefined reference to `U8_NEXT'
./.libs/libsqlite3.a(sqlite3.o): In function `icuLikeFunc':
/home/alex/src/vulcan/build/workdir/sqlite-autoconf-3080401/sqlite3.c:145804:
undefined reference to `U8_NEXT'
collect2: error: ld returned 1 exit status
make[1]: *** [sqlite3] Error 1
make: *** [sqlite] Error 2


I have verified that adding the said include fixes the issue (1-line patch):

diff --git sqlite3.c sqlite3.c
index 7c6d3e2..a9399e4 100644
--- sqlite3.c
+++ sqlite3.c
@@ -145656,6 +145656,7 @@ SQLITE_API int sqlite3_rtree_init(
 #include 
 #include 
 #include 
+#include 

 /* #include  */

Hope this helps others that run into this issue.

-- 
Alex Loukissas

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

Re: [sqlite] [sqlite-dev] Final preparations for the release of System.Data.SQLite v1.0.92.0 have begun...

[Joe Mistachkin]

Stefano Ravagni wrote:
> 
> Only SQL Server don't follow this line for what i know, and because of 
> that i abandon it in favor of SQlite (as file system database) and 
> PostgreSQL.
> 

Well, the SQL Server provider is included with the .NET Framework itself
and I assume that they know how to properly implement their own database
access interfaces.

>
> Tell me please because i'm not sure to had understand in next 
> version could i check dati.hasrows and have to return TRUE value also 
> after data association or have i to combine the use with StepCount 
> properties ?
>

No, for several reasons:

1. It would not be a backward compatible change.

2. It would not be compatible with several other ADO.NET providers
   (e.g. SQL Server).

3. The IDataReader.Read() method return value can be used instead (see
   example below) and has the additional benefit of being more widely
   available since it is required on any class that implements the
   IDataReader interface, not just those derived from the DbDataReader
   abstract class.

bool hasRows = false;

do {
if (dataReader.Read()) {
hasRows = true;
// process this row...
} else {
// no more rows.
break;
}
} while (true);

if (hasRows) {
// more code here...
}

--
Joe Mistachkin

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

Re: [sqlite] Reader locks writer in truncate mode?

[Simon Slavin]

On 13 Mar 2014, at 11:12pm, veeresh kumar  wrote:

> Thanks a lot for the response. Is there any way to identify which 
> thread/process is actually blocking the reader thread or vise versa?

Only in that it's the one that didn't get the error message.  The one that's in 
the middle of a SQLite API call.

Simon.

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

Re: [sqlite] Reader locks writer in truncate mode?

[veeresh kumar]

Thanks a lot for the response. Is there any way to identify which 
thread/process is actually blocking the reader thread or vise versa?
 
-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On
Behalf Of Simon Slavin
Sent: Thursday, March 13, 2014 2:30 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Reader locks writer in truncate mode?
 
 
On 13 Mar 2014, at 9:27pm, Igor Tandetnik  wrote:
 
> On 3/13/2014 5:24 PM, veeresh kumar wrote:
>> In a multi-threaded application, say a reader
thread has read 100 records from the table and reading is still in progress
before which writer thread writes data and tries to commit. Its causing
database lock.Is this expected?
> 
> Assuming the two threads use two distinct database
connections - yes, this is expected.
 
Don't forget that by default SQLite does /no/ retries if
there is an access clash.  You can change
this by setting a timeout:
 

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

Re: [sqlite] Reader locks writer in truncate mode?

[Igor Tandetnik]

On 3/13/2014 5:24 PM, veeresh kumar wrote:

In a multi-threaded application, say a reader thread has read 100 records from 
the table and reading is still in progress before which writer thread writes 
data and tries to commit. Its causing database lock.Is this expected?


Assuming the two threads use two distinct database connections - yes, 
this is expected.

--
Igor Tandetnik

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

[sqlite] Reader locks writer in truncate mode?

[veeresh kumar]

In a multi-threaded application, say a reader thread has read 100 records from 
the table and reading is still in progress before which writer thread writes 
data and tries to commit. Its causing database lock.Is this expected? 


Note : journal_mode is set as truncate
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Reader locks writer in truncate mode?

[Simon Slavin]

On 13 Mar 2014, at 9:27pm, Igor Tandetnik  wrote:

> On 3/13/2014 5:24 PM, veeresh kumar wrote:
>> In a multi-threaded application, say a reader thread has read 100 records 
>> from the table and reading is still in progress before which writer thread 
>> writes data and tries to commit. Its causing database lock.Is this expected?
> 
> Assuming the two threads use two distinct database connections - yes, this is 
> expected.

Don't forget that by default SQLite does /no/ retries if there is an access 
clash.  You can change this by setting a timeout:



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

Re: [sqlite] [sqlite-dev] Final preparations for the release of System.Data.SQLite v1.0.92.0 have begun...

[Joe Mistachkin]

Stefano Ravagni wrote:
> 
> Yes,  i think tue value have to remain constant as far as i explicit close
datareader.
> This have an important effect; possibility to valle a datareader in a
specific routine
> and obtain hasrows value out of this routine.
> 

The MSDN docs are unclear on this point; however, other ADO.NET providers
seem to follow
the System.Data.SQLite behavior you are seeing.

Meanwhile, I've added a new SQLiteDataReader.StepCount property on trunk
that will return
the number of rows seen so far.  Checking if this value is greater than zero
may work for
your use case, when combined with the HasRows property, e.g.:

If Dati.HasRows = True Or Dati.StepCount > 0 Then
... code here ...
End If

This new property will be present in the 1.0.92.0 release, due out next
week.

--
Joe Mistachkin

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

Re: [sqlite] Crash if "sqlite3_db_release_memory" has no memory to release

[Richard Hipp]

On Thu, Mar 13, 2014 at 2:57 PM, Eduardo Morras  wrote:

>
> Hi, I get app crash if sqlite3_db_release_memory(db) is called and Sqlite3
> has no memory to release, for example calling it twice.
>
> The backtrace is:
>
> #0  0x0042ab60 in sqlite3PcacheShrink (pCache=0x6f6c6f63207b2065)
> at /usr/home/gowen/clang/scgi/src/sqlite3.c:37835
> #1  0x00415f3c in sqlite3PagerShrink (pPager=0x801072308)
> at /usr/home/gowen/clang/scgi/src/sqlite3.c:42949
> #2  0x00415ee8 in sqlite3_db_release_memory (db=0x801007808)
> at /usr/home/gowen/clang/scgi/src/sqlite3.c:120647
> #3  0x004ab3e3 in db_get_binary_content (fn=0x7fffcf10
> "background.jpeg",
> size=0x7fffce4c, ret=0x7fffd6e8) at
> /usr/home/gowen/clang/scgi/src/db.c:116
>
> Line 116 on #3 is the call to sqlite3_db_release_memory(db).
>
> I know it's an internal sqlite3 call and I should use
> sqlite3_release_memory instead.
>

sqlite3_db_release_memory() is a valid API that anybody can call.  It
should just work.

We are unable to reproduce your crash.  Perhaps there is heap corruption
caused by some other part of the application and that this is tripping up
SQLite.



>
> Versions tried 3.8.3, 3.8.4, 3.8.4.1. amalgamation under FreeBSD 9.2
> x86_64. using clang 3.4 compiler.
>
> ---   ---
> Eduardo Morras 
> ___
> 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] which of these is faster?

[Richard Hipp]

On Thu, Mar 13, 2014 at 2:37 PM, Stephan Beal  wrote:

> Hi, all,
>
> i know this is probably splitting hairs, and i ask only out of curiosity,
> not because i'm looking to optimize at this level...
>
> Given a Fossil repository db (namely the event.mtime value, a Julian Day),
> which of the following is faster for finding the min/max value of that
> field:
>
> SELECT MIN(mtime) FROM event;
>
> or:
>
> SELECT mtime FROM event ORDER BY mtime LIMIT 1;
>
> My intuition says that the first one would be faster, but EXPLAIN tells me
> that #1 uses 21 ops where #2 uses 16. The EXPLAIN output means nothing to
> me, though - maybe those 16 represent more work:
>

The output of EXPLAIN looks much nicer if you do ".explain" first to set up
appropriate formatting.

Once you do that, you'll see that the opcode sequence is only slightly
different between the two.  They should both run at about the same speed.
I doubt you'll be able to measure the difference.


>
> sqlite> explain SELECT MIN(mtime) FROM event;
> 0|Trace|0|0|0||00|
> 1|Null|0|1|2||00|
> 2|Goto|0|17|0||00|
> 3|OpenRead|1|3207|0|k(2,nil,nil)|00|
> 4|Null|0|3|0||00|
> 5|Affinity|3|1|0|c|00|
> 6|SeekGt|1|12|3|1|00|
> 7|Column|1|0|5||00|
> 8|CollSeq|0|0|0|(BINARY)|00|
> 9|AggStep|0|5|1|min(1)|01|
> 10|Goto|0|12|0||00|
> 11|Next|1|7|0||01|
> 12|Close|1|0|0||00|
> 13|AggFinal|1|1|0|min(1)|00|
> 14|Copy|1|6|0||00|
> 15|ResultRow|6|1|0||00|
> 16|Halt|0|0|0||00|
> 17|Transaction|0|0|0||00|
> 18|VerifyCookie|0|657|0||00|
> 19|TableLock|0|2897|0|event|00|
> 20|Goto|0|3|0||00|
>
> (MAX() needs 2 fewer)
>
> sqlite> explain SELECT mtime FROM event ORDER BY mtime LIMIT 1;
> 0|Trace|0|0|0||00|
> 1|Noop|0|0|0||00|
> 2|Integer|1|1|0||00|
> 3|Goto|0|12|0||00|
> 4|OpenRead|2|3207|0|k(2,nil,nil)|00|
> 5|Rewind|2|10|2|0|00|
> 6|Column|2|0|3||00|
> 7|ResultRow|3|1|0||00|
> 8|IfZero|1|10|-1||00|
> 9|Next|2|6|0||01|
> 10|Close|2|0|0||00|
> 11|Halt|0|0|0||00|
> 12|Transaction|0|0|0||00|
> 13|VerifyCookie|0|657|0||00|
> 14|TableLock|0|2897|0|event|00|
> 15|Goto|0|4|0||00|
>
> (the MAX variant also needs 16)
>
> :-?
>
> --
> - stephan beal
> http://wanderinghorse.net/home/stephan/
> http://gplus.to/sgbeal
> "Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
> those who insist on a perfect world, freedom will have to do." -- Bigby
> Wolf
> ___
> 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

[sqlite] Crash if "sqlite3_db_release_memory" has no memory to release

[Eduardo Morras]

Hi, I get app crash if sqlite3_db_release_memory(db) is called and Sqlite3 has 
no memory to release, for example calling it twice. 

The backtrace is:

#0  0x0042ab60 in sqlite3PcacheShrink (pCache=0x6f6c6f63207b2065)
at /usr/home/gowen/clang/scgi/src/sqlite3.c:37835
#1  0x00415f3c in sqlite3PagerShrink (pPager=0x801072308)
at /usr/home/gowen/clang/scgi/src/sqlite3.c:42949
#2  0x00415ee8 in sqlite3_db_release_memory (db=0x801007808)
at /usr/home/gowen/clang/scgi/src/sqlite3.c:120647
#3  0x004ab3e3 in db_get_binary_content (fn=0x7fffcf10 
"background.jpeg", 
size=0x7fffce4c, ret=0x7fffd6e8) at 
/usr/home/gowen/clang/scgi/src/db.c:116

Line 116 on #3 is the call to sqlite3_db_release_memory(db). 

I know it's an internal sqlite3 call and I should use sqlite3_release_memory 
instead.

Versions tried 3.8.3, 3.8.4, 3.8.4.1. amalgamation under FreeBSD 9.2 x86_64. 
using clang 3.4 compiler. 

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

Re: [sqlite] which of these is faster?

[Stephan Beal]

On Thu, Mar 13, 2014 at 7:50 PM, Andreas Kupries
wrote:

> WIBNI regardless of which form is faster, the engine would detect and
> rewrite the slower into the other ?
>

i wouldn't quite expect the engine to figure that out, but of course would
be happy if it could.


> Note: Which is faster might depend on if we have an index on the mtime
> field or not.
>

Now that you mention it...

CREATE TABLE event(
  type TEXT,  -- Type of event: 'ci', 'w', 'e', 't', 'g'
  mtime DATETIME, -- Time of occurrence. Julian day.
  objid INTEGER PRIMARY KEY,  -- Associated record ID
  tagid INTEGER,  -- Associated ticket or wiki name tag
  uid INTEGER REFERENCES user,-- User who caused the event
  bgcolor TEXT,   -- Color set by 'bgcolor' property
  euser TEXT, -- User set by 'user' property
  user TEXT,  -- Name of the user
  ecomment TEXT,  -- Comment set by 'comment' property
  comment TEXT,   -- Comment describing the event
  brief TEXT, -- Short comment when tagid already seen
  omtime DATETIME -- Original unchanged date+time, or NULL
);
CREATE INDEX event_i1 ON event(mtime);


-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] which of these is faster?

[Andreas Kupries]

On Thu, Mar 13, 2014 at 11:37 AM, Stephan Beal  wrote:
> Hi, all,
>
> i know this is probably splitting hairs, and i ask only out of curiosity,
> not because i'm looking to optimize at this level...
>
> Given a Fossil repository db (namely the event.mtime value, a Julian Day),
> which of the following is faster for finding the min/max value of that
> field:
>
> SELECT MIN(mtime) FROM event;
>
> or:
>
> SELECT mtime FROM event ORDER BY mtime LIMIT 1;

WIBNI regardless of which form is faster, the engine would detect and
rewrite the slower into the other ?

Note: Which is faster might depend on if we have an index on the mtime
field or not.

-- 
Andreas Kupries
Senior Tcl Developer
Code to Cloud: Smarter, Safer, Faster(tm)
F: 778.786.1133
andre...@activestate.com
http://www.activestate.com
Learn about Stackato for Private PaaS: http://www.activestate.com/stackato

EuroTcl'2014, July 12-13, Munich, GER
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] which of these is faster?

[Stephan Beal]

Hi, all,

i know this is probably splitting hairs, and i ask only out of curiosity,
not because i'm looking to optimize at this level...

Given a Fossil repository db (namely the event.mtime value, a Julian Day),
which of the following is faster for finding the min/max value of that
field:

SELECT MIN(mtime) FROM event;

or:

SELECT mtime FROM event ORDER BY mtime LIMIT 1;

My intuition says that the first one would be faster, but EXPLAIN tells me
that #1 uses 21 ops where #2 uses 16. The EXPLAIN output means nothing to
me, though - maybe those 16 represent more work:

sqlite> explain SELECT MIN(mtime) FROM event;
0|Trace|0|0|0||00|
1|Null|0|1|2||00|
2|Goto|0|17|0||00|
3|OpenRead|1|3207|0|k(2,nil,nil)|00|
4|Null|0|3|0||00|
5|Affinity|3|1|0|c|00|
6|SeekGt|1|12|3|1|00|
7|Column|1|0|5||00|
8|CollSeq|0|0|0|(BINARY)|00|
9|AggStep|0|5|1|min(1)|01|
10|Goto|0|12|0||00|
11|Next|1|7|0||01|
12|Close|1|0|0||00|
13|AggFinal|1|1|0|min(1)|00|
14|Copy|1|6|0||00|
15|ResultRow|6|1|0||00|
16|Halt|0|0|0||00|
17|Transaction|0|0|0||00|
18|VerifyCookie|0|657|0||00|
19|TableLock|0|2897|0|event|00|
20|Goto|0|3|0||00|

(MAX() needs 2 fewer)

sqlite> explain SELECT mtime FROM event ORDER BY mtime LIMIT 1;
0|Trace|0|0|0||00|
1|Noop|0|0|0||00|
2|Integer|1|1|0||00|
3|Goto|0|12|0||00|
4|OpenRead|2|3207|0|k(2,nil,nil)|00|
5|Rewind|2|10|2|0|00|
6|Column|2|0|3||00|
7|ResultRow|3|1|0||00|
8|IfZero|1|10|-1||00|
9|Next|2|6|0||01|
10|Close|2|0|0||00|
11|Halt|0|0|0||00|
12|Transaction|0|0|0||00|
13|VerifyCookie|0|657|0||00|
14|TableLock|0|2897|0|event|00|
15|Goto|0|4|0||00|

(the MAX variant also needs 16)

:-?

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] basic "Window function"

[RSmith]

On 2014/03/13 20:02, Petite Abeille wrote:

On Mar 13, 2014, at 4:17 PM, big stone  wrote:


Is there anyone else, (besides little bee), that would "like" this request?

"Oh! Oh pick me! Pick me! Me! Me! M!” — Donkey, Shrek


Hehe, I live in a Country with 11 official languages. Needless to say the actual dialects used are regularly somewhere in between 
those official lines... the sort of thing would make a purist shudder.  Either way, when lots of people from different linguistic 
origins work together, they sometimes speak a common language that invariably ends up being a bit of a mishmash and often misses 
complete words for which a descriptive might be pressed into service and, on occasion, becomes a mainstream constituent of the 
vernacular.


So it is that in one of these work-languages most widely spoken, my most favourite term is for a a Bee (a rather small English word) 
which ended up being referred to as "Picannini flymasjien yena kona lo jam", which in direct translation amounts to: "Very small 
fly-machine he's got jam".


Some beauty lies in the fact that the whole "machine" bit is completely unneeded "small fly" would have sufficed, but the lovely 
rhyme and metrum surfaces everywhere in the culture.


:)


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

Re: [sqlite] basic "Window function"

[Petite Abeille]

On Mar 13, 2014, at 4:17 PM, big stone  wrote:

> Is there anyone else, (besides little bee), that would "like" this request?

"Oh! Oh pick me! Pick me! Me! Me! M!” — Donkey, Shrek

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

Re: [sqlite] basic "Window function"

[big stone]

Hello again,

Windowing functions are :
- not easy to workaround with create_function(),
- a truly "sql core" motor functionality.

The only use case that I have, is the small subset I described earlier :
- inside one 'select' :
   .  several sum/min/avg/max (different fields) ,
   . all with exactly the same "(over partition by ... a series of fields)",
- no ranking, no order by inside this partition.

==> It allows to show a list of records at a certain level, with
statistical analysis done at a completely different (higher or lower) level.

Is it a feature SQLite team would like to do ?

Is there anyone else, (besides little bee), that would "like" this request
?

(http://upload.wikimedia.org/wikipedia/commons/1/13/Facebook_like_thumb.png)
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3_progress_handler callback behavior change

[Andreas Stieger]

Hi,

> On 13 Mar 2014, at 13:21, big stone  wrote:
> Does it mean we may have SQLite3.8.4.1 in imminent Python 3.4 release ?
> 
> http://bugs.python.org/issue20901

This question did come up when building/packaging that and other versions of 
Python with SQLite 3.8.4(.1), where that caused a test failure. I was told that 
Python source does not contain a specific version of SQLite, while binaries 
will.
As such the answer clarifies that the Python tests needed to be adjusted to 
support 3.8.4, and binaries may or may not contain it at some point.

Many thanks for the replies. 
Andreas

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

Re: [sqlite] sqlite3_progress_handler callback behavior change

[big stone]

Hello Andreas,

Does it mean we may have SQLite3.8.4.1 in imminent Python 3.4 release ?

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

Re: [sqlite] Problem with sqlite3_exec

[Igor Tandetnik]

On 3/13/2014 6:33 AM, khaloud1987 wrote:

I have a problem with sqlite3_exec that the first delete execute
suuccesfully but the second delete (table2) does not.
  My qustion is : sqlite_exec can return a value different with  SQLITE_OK
and it works ok


What value does the second call return? What does sqlite3_errmsg return 
when sqlite3_exec fails? What does table2 macro expand to, and do you 
actually have a table with that name?

--
Igor Tandetnik

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

Re: [sqlite] sqlite3_progress_handler callback behavior change

[Richard Hipp]

On Wed, Mar 12, 2014 at 5:27 PM, Andreas Stieger wrote:

> Hello,
>
> I noticed a change in behavior of sqlite3_progress_handler for CREATE
> TABLE, or rather the calls to the callbacks, and just wanted to get
> clarification if this was intentional.
>
> Specifically, given the code below for a callback that prints one "."
> for each code for this: (full code below)
> sqlite3_progress_handler(db, 1, progress_callback, NULL);
> sqlite3_exec(db, "create table foo(a,b)", callback, 0, &zErrMsg);
> \n
> sqlite3_progress_handler(db, 2, progress_callback, NULL);
> sqlite3_exec(db, "create table bar(a,b)", callback, 0, &zErrMsg);
>
> I get the following:
> 3.7.17> ./test
> ..
> ...
> 3.8.3.1> ./test
> 
> ...
> 3.8.4> ./test
> .
> .
> 3.8.4.1> ./test
> .
> .
>
> Is this intentional, a side effect of  optimization or unintended
> behavior? Documentation does day the callbacks are approximate...
>

Side-effect of optimization.

The progress-callback is now only checked at jump opcodes, not after every
opcode, since checking after every opcode uses a measurable fraction of CPU
cycles for a feature that is very rarely used.   The reduced checking
frequency makes not different if you put a reasonable number into the
progress callback, like say "100" or "1000".  It only shows up with a
callback interval of 1 or 2.

The second fact is that newer versions of SQLite use fewer VDBE opcodes to
accomplish the same task.


-- 
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] [sqlite-dev] Final preparations for the release of System.Data.SQLite v1.0.92.0 have begun...

[Stefano Ravagni]

i encounter problem with datareader hasrows properties...

After data association the property became FALSE also if data is present

Is not good thing for who use datareader.hasrows for check data presence...

Is possible to fix this problem ?


Il giorno martedì 11 marzo 2014 02:22:31 UTC+1, Joe Mistachkin ha scritto:
>
>
> If you have any issues with the current code, please report them via this 
> mailing 
> list (and/or by creating a ticket on "https://system.data.sqlite.org/";) 
> prior to 
> this coming Friday, March 14th. 
>
> Thanks. 
>
> -- 
> Joe Mistachkin 
>
> ___ 
> sqlite-dev mailing list 
> sqlit...@sqlite.org  
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-dev 
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] sqlite3_progress_handler callback behavior change

[Andreas Stieger]

Hello,

I noticed a change in behavior of sqlite3_progress_handler for CREATE
TABLE, or rather the calls to the callbacks, and just wanted to get
clarification if this was intentional.

Specifically, given the code below for a callback that prints one "."
for each code for this: (full code below)
sqlite3_progress_handler(db, 1, progress_callback, NULL);
sqlite3_exec(db, "create table foo(a,b)", callback, 0, &zErrMsg);
\n
sqlite3_progress_handler(db, 2, progress_callback, NULL);
sqlite3_exec(db, "create table bar(a,b)", callback, 0, &zErrMsg);

I get the following:
3.7.17> ./test
..
...
3.8.3.1> ./test

...
3.8.4> ./test
.
.
3.8.4.1> ./test
.
.

Is this intentional, a side effect of  optimization or unintended
behavior? Documentation does day the callbacks are approximate...


Thanks,
Andreas

test code follows...


#include 
#include 

static int callback(void *NotUsed, int argc, char **argv, char **azColName){
  int i;
  for(i=0; ihttp://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Problem with sqlite3_exec

[khaloud1987]

Hello,

I have a problem with sqlite3_exec that the first delete execute
suuccesfully but the second delete (table2) does not.
 My qustion is : sqlite_exec can return a value different with  SQLITE_OK
and it works ok 

this is my code:

snprintf(query,q_size,"DELETE FROM " table1 " where record = %d", id);
  retval = sqlite3_exec(handle,query,0,0,0);
 
  if ( retval != SQLITE_OK ) 
  {
 TRACE_ERROR("Error in sql query: %s", query);
  }
 
  else
  {
  snprintf(query,q_size,"DELETE FROM " table2 " where record_id = %d", id);
  retval = sqlite3_exec(handle,query,0,0,0); 
  }



--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/Problem-with-sqlite3-exec-tp74519.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Datareader HasRows became empty after binding source

[Stefano Ravagni]

Hello, 
i'm a .NET developer and i'm trying to use Sqlite as connector.

I could see in an instruction as

If Dati.HasRows = True Then
 sorgente.DataSource = Dati
end if

where DATI is a datareader object and SORGENTE is a BINDINGSOURCE object, 
after association with from datareader results to bindingsource the 
property HasRows became FALSE (if was TRUE).

This block my code because after i check again 

If Dati.HasRows = True then 

and it result in FALSE

is normal ?

In MySQL.dll connector for example,not happennot happen in ODBC and 
nether in OLEDB connection.

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