>On Wed, May 18, 2011 at 4:10 PM, Petite Abeille
><petite.abei...@gmail.com> 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 20000101 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
<http://www.sqlite.org/limits.html#max_trigger_depth>SQLITE_MAX_TRIGGER_DEPTH
compile-time option and a run-time limit set by
<http://www.sqlite.org/c3ref/limit.html>sqlite3_limit(db,<http://www.sqlite.org/c3ref/c_limit_attached.html>SQLITE_LIMIT_TRIGGER_DEPTH,...).
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
