Re: [sqlite] SQL Statement Help(selecting days).

[Jean-Christophe Deschamps]

> > Since we use recursive triggers, set recursive_triggers pragma
> > beforehand if not yet done.
>
>Cunning. A bit of a Rube Goldberg apparatus though, no?

Huh? Still way more flexible than having to modify C source of a vtable 
module, should you have to adapt anything.

Yeah, it's kind of convoluted as you seem to say (I had to look up what 
that meant to a Yank ;-)) but it has the great advantage to require no 
line of code, just any SQLite manager to install, change or discard in 
seconds.

Look 'Ma: no compiler!


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

Re: [sqlite] SQL Statement Help(selecting days).

[Petite Abeille]

On May 19, 2011, at 11:44 AM, Jean-Christophe Deschamps wrote:

> Since we use recursive triggers, set recursive_triggers pragma 
> beforehand if not yet done. 

Cunning. A bit of a Rube Goldberg apparatus though, no?

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

Re: [sqlite] SQL Statement Help(selecting days).

[Jean-Christophe Deschamps]

>On Wed, May 18, 2011 at 4:10 PM, Petite Abeille
> wrote:
> > On May 18, 2011, at 10:50 PM, Danilo Cicerone wrote:
> >> How can I simulate a
> >> calendar table(maybe using the strftime funtion)?
> >
> > Well, you have two broad options:
> >
> > (1) materialize the calendar as a table
> > (2) virtualize the calendar as a generator
> >
> > The first option is brutal, but simple. E.g. create a table with, 
> say, all the julian date from 2101 to 21001231 (2451544 to 
> 2488433 JD, about 36,889 records for a century worth of date)
> >
> > The second option is a bit more involved, but you could have a 
> virtual calendar table that generate the relevant date span on demand:
> >
> > http://www.sqlite.org/vtab.html
> >
> > Unfortunately, there is no direct way to generate rows in SQLite as 
> there is, for example, in Oracle or such:
>
>I have a virtual table that allows you to split strings and count
>numbers, which could be used as a row generator.  I really want to
>polish it off and even, some day, add syntactic sugar (calling this
>"table functions"), but lack for time.  Would it help if I posted this
>somewhere?
>
>Nico

A vtable for that may be a bit of a caterpillar unless you look for top 
efficiency or large ranges and, yes, SQLite perfectly allows such range 
of values to be created within SQLite's SQL.

I regularly use such constructs to keep generating (reasonably small) 
sequences or date ranges entirely within SQLite:


CREATE TABLE "Dates" (
   "jDate" INTEGER PRIMARY KEY,
   "sDate" CHAR);

CREATE TABLE "Sequence" (
   "N" INTEGER PRIMARY KEY AUTOINCREMENT);

CREATE TABLE "Counts" (
   "jStartDate" INTEGER,
   "Start" INTEGER DEFAULT (0),
   "Counter" INTEGER DEFAULT (0));

CREATE TRIGGER "trUpdCount"
AFTER UPDATE OF "Counter"
ON "Counts"
WHEN new.counter > 0
BEGIN
  insert or replace into sequence (N) values ((select start + 
counter from counts));
  insert or replace into dates (jdate, sdate) values ((select 
jstartdate + counter from counts), date((select jstartdate + counter 
from counts)));
  update counts set counter = new.counter - 1;
END;

To use, set first a Julian day start date and/or a start integer. Then 
update counter to specify how many dates and/or numbers you want 
created in tables.
Empty data tables between runs (that can be automated with more triggers).

Since we use recursive triggers, set recursive_triggers pragma 
beforehand if not yet done.  This scheme can be adapted to your actual 
needs, merge data tables, use other types, etc.

Don't request large counts as the recursion limit may bite you. As the 
doc says:
The depth of recursion for triggers has a hard upper limit set by the 
SQLITE_MAX_TRIGGER_DEPTH 
compile-time option and a run-time limit set by 
sqlite3_limit(db,SQLITE_LIMIT_TRIGGER_DEPTH,...).
 


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

Re: [sqlite] SQL Statement Help(selecting days).

[Petite Abeille]

On May 19, 2011, at 12:28 AM, Igor Tandetnik wrote:

> That's SQLite extension. I don't believe it's legal standard SQL. The 
> alias in the SELECT clause acts kind of like a macro; its use in the 
> rest of the statement is simply replaced with the corresponding expression.

Mind-boggling. Border line insane :)

> The query could be written without using this feature, but it wouldn't 
> be quite as neat and readable.

Neat indeed. Well played :))

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

Re: [sqlite] SQL Statement Help(selecting days).

[Igor Tandetnik]

On 5/18/2011 6:11 PM, Petite Abeille wrote:
> In other words, how come the following works in SQLite:
>
> select 1 as value where value>  0;
>
> Or even weirder:
> select 1 as value where value = 0;
>
> There is no from clause, there is no column 'value' per se, but nonetheless 
> that non existing column can be referenced in the where clause. What gives?

That's SQLite extension. I don't believe it's legal standard SQL. The 
alias in the SELECT clause acts kind of like a macro; its use in the 
rest of the statement is simply replaced with the corresponding expression.

The query could be written without using this feature, but it wouldn't 
be quite as neat and readable.
-- 
Igor Tandetnik

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

Re: [sqlite] SQL Statement Help(selecting days).

[Nico Williams]

On Wed, May 18, 2011 at 5:11 PM, Petite Abeille
 wrote:
> Where does the start_gap and end_gap come from? They are only declared in the 
> select part of the inner select statement, and nowhere in the from part. But 
> nonetheless, SQLite manages to use these non existing columns in the where 
> clause. What gives?
>
> In other words, how come the following works in SQLite:
>
> select 1 as value where value > 0;
>
> Or even weirder:
> select 1 as value where value = 0;
>
> There is no from clause, there is no column 'value' per se, but nonetheless 
> that non existing column can be referenced in the where clause. What gives?

You don't need a table source in order to evaluate expressions, nor to
have a WHERE condition.  A SELECT without table sources will produce a
single row defined by the column expressions, and filtered by the
WHERE clause, if there is one.  So a SELECT without table sources can
produce zero or one rows.

Why would you want to do that?  I can think of some reasons:

 - So you can invoke a user-defined function and use that to produce a
zero- or one-row result for use in IN clauses and so on.

 - So you can define a zero-row VIEW, which you might want to do if
you want to allow only INSERTs on a view (with INSTEAD OF INSERT
triggers) as a way to emulate stored procedures.  (I've done this
plenty, and indeed, I rely on this approach in my DB triggers patch to
make the patch very small.)

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

Re: [sqlite] SQL Statement Help(selecting days).

[Petite Abeille]

On May 18, 2011, at 11:28 PM, Igor Tandetnik wrote:

> On 5/18/2011 4:17 PM, Pavel Ivanov wrote:
>>> I need help to build a statement in order to select all days free from
>>> events in a specific time range.
>> 
>> This kind of task should be implemented in your application. SQL
>> wasn't intended for and can't solve such tasks.
> 
> Sounds like a challenge:

Nicely done :)

Lets break it apart.

First, a test set:

create view event 
as

select 1 as id,
   date( '2011-01-02' ) as start_date,
   date( '2011-01-08' ) as end_date
   
union all
select 2 as id,
   date( '2011-01-06' ) as start_date,
   date( '2011-01-12' ) as end_date
   
union all
select 3 as id,
   date( '2011-01-18' ) as start_date,
   date( '2011-01-21' ) as end_date;

> select * from event;
1|2011-01-02|2011-01-08
2|2011-01-06|2011-01-12
3|2011-01-18|2011-01-21

Then the query itself:

select  start_gap, 
min( end_gap ) as end_gap
from(
select  date( e1.end_date, '+1 day' ) as start_gap, 
date( e2.start_date, '-1 day' ) as end_gap

from(
select  start_date, 
end_date 
fromevent
union all
select  '' as start_date, 
date('2011-01-01', '-1 day') as end_date
) e1

cross join (
select  start_date, 
end_date 
fromevent
union all
select  date('2011-02-01', '+1 day') as 
start_date, 
'' as end_date
) e2

where   start_gap <= end_gap 
and start_gap >= '2011-01-01' 
and end_gap <= '2011-02-01'
and not exists 
( 
select  1 
fromevent 

where   start_date between start_gap and 
end_gap 
or  end_date between start_gap and end_gap 
)
)
group bystart_gap;

2011-01-01|2011-01-01
2011-01-13|2011-01-17
2011-01-22|2011-02-01

Very nice. One question though, regarding the inner most where clause:

where   start_gap <= end_gap 
and start_gap >= '2011-01-01' 
and end_gap <= '2011-02-01'

Where does the start_gap and end_gap come from? They are only declared in the 
select part of the inner select statement, and nowhere in the from part. But 
nonetheless, SQLite manages to use these non existing columns in the where 
clause. What gives?

In other words, how come the following works in SQLite:

select 1 as value where value > 0;

Or even weirder:
select 1 as value where value = 0;

There is no from clause, there is no column 'value' per se, but nonetheless 
that non existing column can be referenced in the where clause. What gives?

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

Re: [sqlite] SQL Statement Help(selecting days).

[Danilo Cicerone]

Amazing!
Thanks a lot.

2011/5/18 Igor Tandetnik :
> select startGap, min(endGap) from
> (
>   select date(e1.endDate, '+1 day') startGap, date(e2.startDate, '-1
> day') endGap
>   from (select startDate, endDate from events
>         union all
>         select '' startDate, date('2011-01-01', '-1 day') endDate) e1,
>        (select startDate, endDate from events
>         union all
>         select date('2011-02-01', '+1 day') startDate, '' endDate) e2
>   where startGap <= endGap and startGap >= '2011-01-01' and endGap <=
> '2011-02-01'
>     and not exists (
>       select 1 from events where startDate between startGap and endGap
>         or endDate between startGap and endGap)
> )
> group by startGap;
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQL Statement Help(selecting days).

[Igor Tandetnik]

On 5/18/2011 4:17 PM, Pavel Ivanov wrote:
>> I need help to build a statement in order to select all days free from
>> events in a specific time range.
>
> This kind of task should be implemented in your application. SQL
> wasn't intended for and can't solve such tasks.

Sounds like a challenge:

select startGap, min(endGap) from
(
   select date(e1.endDate, '+1 day') startGap, date(e2.startDate, '-1 
day') endGap
   from (select startDate, endDate from events
 union all
 select '' startDate, date('2011-01-01', '-1 day') endDate) e1,
(select startDate, endDate from events
 union all
 select date('2011-02-01', '+1 day') startDate, '' endDate) e2
   where startGap <= endGap and startGap >= '2011-01-01' and endGap <= 
'2011-02-01'
 and not exists (
   select 1 from events where startDate between startGap and endGap
 or endDate between startGap and endGap)
)
group by startGap;

-- 
Igor Tandetnik

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

Re: [sqlite] SQL Statement Help(selecting days).

[Nico Williams]

On Wed, May 18, 2011 at 4:10 PM, Petite Abeille
 wrote:
> On May 18, 2011, at 10:50 PM, Danilo Cicerone wrote:
>> How can I simulate a
>> calendar table(maybe using the strftime funtion)?
>
> Well, you have two broad options:
>
> (1) materialize the calendar as a table
> (2) virtualize the calendar as a generator
>
> The first option is brutal, but simple. E.g. create a table with, say, all 
> the julian date from 2101 to 21001231 (2451544 to 2488433 JD, about 
> 36,889 records for a century worth of date)
>
> The second option is a bit more involved, but you could have a virtual 
> calendar table that generate the relevant date span on demand:
>
> http://www.sqlite.org/vtab.html
>
> Unfortunately, there is no direct way to generate rows in SQLite as there is, 
> for example, in Oracle or such:

I have a virtual table that allows you to split strings and count
numbers, which could be used as a row generator.  I really want to
polish it off and even, some day, add syntactic sugar (calling this
"table functions"), but lack for time.  Would it help if I posted this
somewhere?

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

Re: [sqlite] SQL Statement Help(selecting days).

[Danilo Cicerone]

Thanks again.

2011/5/18 Petite Abeille :
>
> On May 18, 2011, at 10:50 PM, Danilo Cicerone wrote:
> (1) materialize the calendar as a table
> (2) virtualize the calendar as a generator
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQL Statement Help(selecting days).

[Petite Abeille]

On May 18, 2011, at 10:50 PM, Danilo Cicerone wrote:

> How can I simulate a
> calendar table(maybe using the strftime funtion)?

Well, you have two broad options:

(1) materialize the calendar as a table 
(2) virtualize the calendar as a generator

The first option is brutal, but simple. E.g. create a table with, say, all the 
julian date from 2101 to 21001231 (2451544 to 2488433 JD, about 36,889 
records for a century worth of date) 

The second option is a bit more involved, but you could have a virtual calendar 
table that generate the relevant date span on demand:

http://www.sqlite.org/vtab.html

Unfortunately, there is no direct way to generate rows in SQLite as there is, 
for example, in Oracle or such:

http://www.orafaq.com/wiki/Oracle_Row_Generator_Techniques

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

Re: [sqlite] SQL Statement Help(selecting days).

[Danilo Cicerone]

Thanks Petite,
I get it, great. Only another quick question, How can I simulate a
calendar table(maybe using the strftime funtion)?
Danilo

2011/5/18 Petite Abeille :
>
> On May 18, 2011, at 10:17 PM, Pavel Ivanov wrote:
>
>> SQL wasn't intended for and can't solve such tasks.
>
> Of course it can.
>
> Assuming a calendar and an event table:
>
> select calendar.date
> from   calendar
>
> where calendar.date between '20110101' and '20110201'
> and not exists ( select 1 from event where calendar.date between 
> event.start_date and event.end_date)
>
> And where are analytics when one need them? :)
>
> http://www.orafaq.com/node/55
>
> ___
> 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 Statement Help(selecting days).

[Petite Abeille]

On May 18, 2011, at 10:17 PM, Pavel Ivanov wrote:

> SQL wasn't intended for and can't solve such tasks.

Of course it can.

Assuming a calendar and an event table:

select calendar.date
from   calendar

where calendar.date between '20110101' and '20110201'
and not exists ( select 1 from event where calendar.date between 
event.start_date and event.end_date) 

And where are analytics when one need them? :)

http://www.orafaq.com/node/55

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

Re: [sqlite] SQL Statement Help(selecting days).

[Pavel Ivanov]

> I need help to build a statement in order to select all days free from
> events in a specific time range.

This kind of task should be implemented in your application. SQL
wasn't intended for and can't solve such tasks.


Pavel


On Wed, May 18, 2011 at 4:06 PM, Danilo Cicerone  wrote:
> Hi to all,
> I need help to build a statement in order to select all days free from
> events in a specific time range.
> E.g.:
>
> - Range(-MM-DD) from 2011-01-01 to 2011-02-01.
> - Event 1 from 2011-01-02 to 2011-01-08
> - Event 2 from 2011-01-06 to 2011-01-12
> - Event 3 from 2011-01-18 to 2011-01-21
>
> Resulting Days Free:
> - from 2011-01-01 to 2011-01-01
> - from 2011-01-13 to 2011-01-17
> - from 2011-01-22 to 2011-02-01
>
> Thanks for your help in advance.
> Danilo
> ___
> 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] SQL Statement Help(selecting days).

[Danilo Cicerone]

Hi to all,
I need help to build a statement in order to select all days free from
events in a specific time range.
E.g.:

- Range(-MM-DD) from 2011-01-01 to 2011-02-01.
- Event 1 from 2011-01-02 to 2011-01-08
- Event 2 from 2011-01-06 to 2011-01-12
- Event 3 from 2011-01-18 to 2011-01-21

Resulting Days Free:
- from 2011-01-01 to 2011-01-01
- from 2011-01-13 to 2011-01-17
- from 2011-01-22 to 2011-02-01

Thanks for your help in advance.
Danilo
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users