Re: [sqlite] SQL:2003 -- Window Functions

[Igor Tandetnik]

Mohd Radzi Ibrahim  wrote:
> On Thu, Sep 20, 2012 at 12:58 AM, Igor Tandetnik wrote:
> 
>> On 9/19/2012 12:51 PM, joe.fis...@tanguaylab.com wrote:
>> 
>>> Too bad SQLite doesn't yet support SQL Window Functions.
>>> 
>>> Are there any SQLite Extension Libraries that support "SQL:2003 type
>>> Window Functions"?
>>> I specifically need LEAD and LAG to calculate an event integer timestamp
>>> delta between consecutive rows.
>>> I've played with some self-join code but that's proving to be complicated.
>>> 
>> 
>> The easiest approach is to maintain the window in your application code,
>> as you iterate over a simple SELECT statement.
>> 
> Could it not be done with inner select

Yes it could, and in fact the OP has stated that he "played with some 
self-join" but was unhappy with the approach.

Such complicated queries also tend to run noticeably slower than a 
straightforward linear pass.
-- 
Igor Tandetnik

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

Re: [sqlite] SQL:2003 -- Window Functions

[Keith Medcalf]

> > Could it not be done with inner select of ROWID-n and ROWID+n to get the
> > LEAD and LAG row ?
> >
> > select
> >  logtime as timeNow,
> >  (select logtime from logtable where rowid=a.rowid-1) as timeBefore,
> >  (select logtime from logtable where rowid=a.rowid+1) as timeAfter
> > from logtime;
> 
> This will work only if the logtime table has consecutive rowids which
> is almost never the case.

create temporary table tlogtime as select logtime from logtime order by rowid;
select logtime as timeNow,
   (select logtime from tlogtable where rowid=a.rowid-1) as timeBefore,
   (select logtime from tlogtable where rowid=a.rowid+1) as timeAfter
  from tlogtime as a;
drop temp.tlogtime;

That is, create a temporary table with the data correctly ordered you want in 
sequentially numbered rowid's, then the correlated subqueries will work.  
Performance will be entirely dependant on how many rows you are dealing with in 
the temp table.


---
()  ascii ribbon campaign against html e-mail
/\  www.asciiribbon.org




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

Re: [sqlite] SQL:2003 -- Window Functions

[Pavel Ivanov]

On Wed, Sep 19, 2012 at 7:10 PM, Mohd Radzi Ibrahim  wrote:
> On Thu, Sep 20, 2012 at 12:58 AM, Igor Tandetnik wrote:
>
>> On 9/19/2012 12:51 PM, joe.fis...@tanguaylab.com wrote:
>>
>>> Too bad SQLite doesn't yet support SQL Window Functions.
>>>
>>> Are there any SQLite Extension Libraries that support "SQL:2003 type
>>> Window Functions"?
>>> I specifically need LEAD and LAG to calculate an event integer timestamp
>>> delta between consecutive rows.
>>> I've played with some self-join code but that's proving to be complicated.
>>>
>>
>> The easiest approach is to maintain the window in your application code,
>> as you iterate over a simple SELECT statement.
>> --
>> Igor Tandetnik
>>
>>
>>
> Could it not be done with inner select of ROWID-n and ROWID+n to get the
> LEAD and LAG row ?
>
> select
>  logtime as timeNow,
>  (select logtime from logtable where rowid=a.rowid-1) as timeBefore,
>  (select logtime from logtable where rowid=a.rowid+1) as timeAfter
> from logtime;

This will work only if the logtime table has consecutive rowids which
is almost never the case.

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

Re: [sqlite] SQL:2003 -- Window Functions

[Mohd Radzi Ibrahim]

On Thu, Sep 20, 2012 at 12:58 AM, Igor Tandetnik wrote:

> On 9/19/2012 12:51 PM, joe.fis...@tanguaylab.com wrote:
>
>> Too bad SQLite doesn't yet support SQL Window Functions.
>>
>> Are there any SQLite Extension Libraries that support "SQL:2003 type
>> Window Functions"?
>> I specifically need LEAD and LAG to calculate an event integer timestamp
>> delta between consecutive rows.
>> I've played with some self-join code but that's proving to be complicated.
>>
>
> The easiest approach is to maintain the window in your application code,
> as you iterate over a simple SELECT statement.
> --
> Igor Tandetnik
>
>
>
Could it not be done with inner select of ROWID-n and ROWID+n to get the
LEAD and LAG row ?

select
 logtime as timeNow,
 (select logtime from logtable where rowid=a.rowid-1) as timeBefore,
 (select logtime from logtable where rowid=a.rowid+1) as timeAfter
from logtime;

best regards,
Radzi.


__**_
> 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] SQL:2003 -- Window Functions

[Igor Tandetnik]

On 9/19/2012 12:51 PM, 
joe.fis...@tanguaylab.com wrote:

Too bad SQLite doesn't yet support SQL Window Functions.

Are there any SQLite Extension Libraries that support "SQL:2003 type
Window Functions"?
I specifically need LEAD and LAG to calculate an event integer timestamp
delta between consecutive rows.
I've played with some self-join code but that's proving to be complicated.


The easiest approach is to maintain the window in your application code, 
as you iterate over a simple SELECT statement.

--
Igor Tandetnik

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

[sqlite] SQL:2003 -- Window Functions

[joe.fis...@tanguaylab.com]

Too bad SQLite doesn't yet support SQL Window Functions.

Are there any SQLite Extension Libraries that support "SQL:2003 type 
Window Functions"?
I specifically need LEAD and LAG to calculate an event integer timestamp 
delta between consecutive rows.

I've played with some self-join code but that's proving to be complicated.

Joe Fisher
Oregon State University



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

Re: [sqlite] Does a search on an external content FTS table use the index of the underlying content?

[E. Timothy Uy]

Thanks Richard. In the case of using a index modifying tokenizer like
Porter, is there a way to know which of the MATCH terms it actually matched
on or is that lost information?

On Wed, Sep 19, 2012 at 3:44 AM, Richard Hipp  wrote:

> On Wed, Sep 19, 2012 at 2:42 AM, E. Timothy Uy  wrote:
>
> > Let's say I have a table Data_content(term TEXT UNIQUE) which has an
> > indexed column 'term'. I then create an FTS4 table using
> >
> > CREATE VIRTUAL TABLE Data USING fts4 (content="Data_content", term);
> >
> > If I later search using SELECT * FROM Data WHERE term IN (...), does it
> use
> > the indexes generated in Data_content, or do I have to generate another
> set
> > (or search Data_content directly).
> >
>
> FTS4 indices are only used with the MATCH operator.  And you cannot create
> an index on a virtual table.  So you'll either want to translate your IN
> into a MATCH or else search the Data_content table directly.
>
>
>
> >
> > Thanks.
> >
> > Respectfully,
> > Tim
> > ___
> > 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-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Potential SQLITE_LOCKED bug?

[Richard Hipp]

On Tue, Sep 18, 2012 at 11:58 AM, Helmut Grohne  wrote:

> Dear sqlite users and developers,
>
> I am occasionally receiving SQLITE_LOCKED in a forking Python
> application. Now according to the documentation
> http://www.sqlite.org/cvstrac/wiki/wiki?p=DatabaseIsLocked and
> http://www.sqlite.org/c_interface.html I should only get this error if
> two actions happen on the same connection in parallel (either via
> threads or via recursive calls). Since I have no clue why this happens I
> reproduced the problem in a small example C program. Find sessiontest.c
> attached.
>

The mailing list strips attachments.  Please post in-line.


>
> So compile sessiontest.c and link it against sqlite on a unixoid
> platform. Ensure that the filename "sessiontest.sqlite3" is absent and
> writeable in the working directory. Run the executable.
>
> Observed behaviour:
> sqlite3_exec(COMMIT): database is locked
> sqlite3_prepare: database is locked
>
> Expected behaviour:
> SQLITE_BUSY or no error, but certainly not SQLITE_LOCKED.
>
> Rationale:
> My example program first forks and then opens individual connections. So
> sharing via file descriptors or threads is not possible. Also my program
> does not pass handlers (besides free) to sqlite, so it cannot cause
> recursive calls. Therefore SQLITE_LOCKED should never happen.
>
> System used:
> Debian squeeze amd64 libsqlite3-0 3.7.3-1
> Debian sid amd64 libsqlite3-0 3.7.13-1
>
> Is this behaviour correct? If yes, why?
>
> Any proposed workarounds?
>
> Thanks in advance
>
> Helmut
>
> ___
> 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] Potential SQLITE_LOCKED bug?

[Helmut Grohne]

Dear sqlite users and developers,

I am occasionally receiving SQLITE_LOCKED in a forking Python
application. Now according to the documentation
http://www.sqlite.org/cvstrac/wiki/wiki?p=DatabaseIsLocked and
http://www.sqlite.org/c_interface.html I should only get this error if
two actions happen on the same connection in parallel (either via
threads or via recursive calls). Since I have no clue why this happens I
reproduced the problem in a small example C program. Find sessiontest.c
attached.

So compile sessiontest.c and link it against sqlite on a unixoid
platform. Ensure that the filename "sessiontest.sqlite3" is absent and
writeable in the working directory. Run the executable.

Observed behaviour:
sqlite3_exec(COMMIT): database is locked
sqlite3_prepare: database is locked

Expected behaviour:
SQLITE_BUSY or no error, but certainly not SQLITE_LOCKED.

Rationale:
My example program first forks and then opens individual connections. So
sharing via file descriptors or threads is not possible. Also my program
does not pass handlers (besides free) to sqlite, so it cannot cause
recursive calls. Therefore SQLITE_LOCKED should never happen.

System used:
Debian squeeze amd64 libsqlite3-0 3.7.3-1
Debian sid amd64 libsqlite3-0 3.7.13-1

Is this behaviour correct? If yes, why?

Any proposed workarounds?

Thanks in advance

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

Re: [sqlite] Strategies for checking the version of a database?

[Rui Maciel]

On 09/18/2012 12:05 PM, Marcus Ilgner wrote:

You could use the user_version pragma (don't use schema_version) to
store this information.
Seehttp://www.sqlite.org/pragma.html#pragma_schema_version


Nice.  It looks like pragma user_version is exactly what I was looking for.


Thanks for the help,
Rui Maciel
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Does a search on an external content FTS table use the index of the underlying content?

[Richard Hipp]

On Wed, Sep 19, 2012 at 2:42 AM, E. Timothy Uy  wrote:

> Let's say I have a table Data_content(term TEXT UNIQUE) which has an
> indexed column 'term'. I then create an FTS4 table using
>
> CREATE VIRTUAL TABLE Data USING fts4 (content="Data_content", term);
>
> If I later search using SELECT * FROM Data WHERE term IN (...), does it use
> the indexes generated in Data_content, or do I have to generate another set
> (or search Data_content directly).
>

FTS4 indices are only used with the MATCH operator.  And you cannot create
an index on a virtual table.  So you'll either want to translate your IN
into a MATCH or else search the Data_content table directly.



>
> Thanks.
>
> Respectfully,
> Tim
> ___
> 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] problem about output html in c++

[Simon Slavin]

On 19 Sep 2012, at 6:43am, YAN HONG YE  wrote:

> sqlite3 -html -header t9_engine.db "select id,partnumber,pic,pcs from engine 
> where id>7;" >> mm.html
> 
> the pic result is "c:\abc.jpg"...
> but I wanna the result is 
> ""

You can use '||' to add strings together.  So it could be

SELECT id,partnumber,'',pcs FROM engine 
WHERE id>7

But this includes the characters '<' and '>' and I think the command line will 
try to interpret them.  You will need to escape those characters, depending on 
which shell you are using.

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