Re: [sqlite] Implicitly creating a table containing the first N consecutive integers?

[Jay A. Kreibich]

On Tue, Jun 07, 2011 at 06:12:55PM -0400, Richard Hipp scratched on the wall:
> On Tue, Jun 7, 2011 at 5:44 PM, Guenther Brunthaler 
> wrote:
> 
> See
> http://www.sqlite.org/src/artifact/6129adfbe7c7444f2e60cc785927f3aa74e12290
> for an example implementation of a virtual table that does something
> pretty much like the above.  We've recently started using it some for
> our test cases.

  Ooo... that's even more nifty and simple.

-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] Implicitly creating a table containing the first N consecutive integers?

[Jay A. Kreibich]

On Tue, Jun 07, 2011 at 11:44:20PM +0200, Guenther Brunthaler scratched on the 
wall:
> Hi all,
> 
> I frequently need a table in my queries for several kinds of JOIN
> operations which contains just the integers from 1 to N in its rows.
> 
> I. e.
> 
> SELECT n FROM int_seq where n <= 5;
> 1
> 2
> 3
> 4
> 5
> 
> However, I would like to achieve the same effect without actually
> creating such a table "int_seq".

  Can you use an IN expression, rather than a JOIN?



  If you're doing this from C/C++, you might also check out the
  "intarray" code.  It is designed to be used with IN, but would work
  for this as well.  It basically creates a virtual table that can be
  bound to an in-memory array of integers.  You could setup a large
  array of integers and then bind it to the required length.

  See the notes in the header file.

  src/test_intarray.h
http://www.sqlite.org/cgi/src/artifact/489edb9068bb926583445cb02589344961054207

  src/test_intarray.c
http://www.sqlite.org/cgi/src/artifact/d879bbf8e4ce085ab966d1f3c896a7c8b4f5fc99


   -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] Implicitly creating a table containing the first N consecutive integers?

[Richard Hipp]

On Tue, Jun 7, 2011 at 5:44 PM, Guenther Brunthaler wrote:

> Hi all,
>
> I frequently need a table in my queries for several kinds of JOIN
> operations which contains just the integers from 1 to N in its rows.
>
> I. e.
>
> SELECT n FROM int_seq where n <= 5;
> 1
> 2
> 3
> 4
> 5
>
> However, I would like to achieve the same effect without actually
> creating such a table "int_seq".
>
> It it possible in SQLite to create such a table implicitly "on the fly"
> using some sort of recursive view/query or built-in special function?
>

See
http://www.sqlite.org/src/artifact/6129adfbe7c7444f2e60cc785927f3aa74e12290for
an example implementation of a virtual table that does something
pretty
much like the above.  We've recently started using it some for our test
cases.



>
> Regards,
>
> Guenther
> ___
> 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] Implicitly creating a table containing the first N consecutive integers?

[Petite Abeille]

On Jun 7, 2011, at 11:44 PM, Guenther Brunthaler wrote:

> It it possible in SQLite to create such a table implicitly "on the fly"
> using some sort of recursive view/query or built-in special function?

The short of it: no, not out-of-the-box.


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