Re: [sqlite] Feasability of a Range function
Hi Alexey, >1. See internal sqlite instarray interface: >http://sqlite.mobigroup.ru/src/finfo?name=src/test_intarray.c >http://sqlite.mobigroup.ru/src/finfo?name=src/test_intarray.h >http://sqlite.mobigroup.ru/src/finfo?name=test/intarray.test > >Note: http://sqlite.mobigroup.ru include official SQLite trunk >branch but anonymous autorization is not required. > >2. See my Tablefunc extension here: >http://sqlite.mobigroup.ru/src/dir?name=ext/tablefunc Warm thanks to the pointer. That will prove helpful for other purpose as well (testing, as I believe this is why you wrote it). Cheers, ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Feasability of a Range function
Hello! 1. See internal sqlite instarray interface: http://sqlite.mobigroup.ru/src/finfo?name=src/test_intarray.c http://sqlite.mobigroup.ru/src/finfo?name=src/test_intarray.h http://sqlite.mobigroup.ru/src/finfo?name=test/intarray.test Note: http://sqlite.mobigroup.ru include official SQLite trunk branch but anonymous autorization is not required. 2. See my Tablefunc extension here: http://sqlite.mobigroup.ru/src/dir?name=ext/tablefunc As example: create table testrange(rowid); select intrange2table (1,10,1,'testrange'); select * from testrange; 1 2 3 4 5 6 7 8 9 10 select intrange2table (100,1000,100,'testrange'); select * from testrange; 1 2 3 4 5 6 7 8 9 10 100 200 300 400 500 600 700 800 900 1000 Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Feasability of a Range function
Jean-Christophe Deschamps wrote: >> Wouldn't it make more sense for "i in 1..5" to expand to "i >= 1 and i >> <= 5"? >> >> Then it would also work for ordered types that aren't ordinal, such as >> rationals >> and strings and blobs and dates etc, and it would work for very large >> ranges, >> since there's no conceptual need to generate all the individual values. >> >> Of course, you'd want to support all 4 variants: 1..5, 1..^5, 1^..5, >> 1^..^5, >> where a ^ means exclude that endpoint and its absence means include. >> >> This is more flexible than SQL's BETWEEN, which I believe only covers >> one of >> those 4 options. > > That's getting into generic interval support. I find this interesting > even if its really much more ambitious than my simple-minded (and > highly optional) initial need/question. > > I might dig further in this direction someday. Good point. > > Thanks Darren. Getting into, yes. Generally speaking, any time someone is talking about a range in terms of 2 endpoint values, there are 2 distinct things they want: 1. Test if a value is between those 2 endpoints, in which case we have an interval, and the endpoints can be any ordered type. Alternate ways to spell that generally is a pair of binary order-comparison tests. Or you support having your interval as a value in and of itself, but that would be overkill in a system that doesn't already support collection-typed values. That is, I would not recommend SQLite goes to support actual interval/range types, but supporting the .. etc as simply a shorthand syntax for existing comparison ops it already supports, I would say is reasonable to support, essentially an expansion of BETWEEN. 2. Generate a list of values, in which case you need an ordinal type, or a closure to explicitly generate the next list element from a prior one. Such as how one may generically define a "sequence generator". FYI, my Muldis D language for RDBMSs, and Perl 6, as well as other languages, have actual interval types, so you can say "foo in bar" if you want. -- Darren Duncan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Feasability of a Range function
>Wouldn't it make more sense for "i in 1..5" to expand to "i >= 1 and i ><= 5"? > >Then it would also work for ordered types that aren't ordinal, such as >rationals >and strings and blobs and dates etc, and it would work for very large >ranges, >since there's no conceptual need to generate all the individual values. > >Of course, you'd want to support all 4 variants: 1..5, 1..^5, 1^..5, >1^..^5, >where a ^ means exclude that endpoint and its absence means include. > >This is more flexible than SQL's BETWEEN, which I believe only covers >one of >those 4 options. That's getting into generic interval support. I find this interesting even if its really much more ambitious than my simple-minded (and highly optional) initial need/question. I might dig further in this direction someday. Good point. Thanks Darren. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Feasability of a Range function
Jean-Christophe Deschamps wrote: > I'm trying to determine if a Range(from, to) function can be made as an > extension function. > > Its effect would be to expand, for instance, range(1, 5) into (1, 2, 3, > 4, 5) for use in constructs similar to > select some_scalar_function(i) where i in range(1, 5); > without having to build a table holding integers 1..5 > > I don't see how that's possible, but maybe someone with better internal > knowledge can advise. Wouldn't it make more sense for "i in 1..5" to expand to "i >= 1 and i <= 5"? Then it would also work for ordered types that aren't ordinal, such as rationals and strings and blobs and dates etc, and it would work for very large ranges, since there's no conceptual need to generate all the individual values. Of course, you'd want to support all 4 variants: 1..5, 1..^5, 1^..5, 1^..^5, where a ^ means exclude that endpoint and its absence means include. This is more flexible than SQL's BETWEEN, which I believe only covers one of those 4 options. -- Darren Duncan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Feasability of a Range function
>Ah. You want table-valued functions, like this: > >http://msdn.microsoft.com/en-us/library/ms191165.aspx Thanks Igor, that's what I had in mind. >In any case, SQLite doesn't support table-valued functions. The >closest thing to it is a virtual table: OK, got it, but this is a bit of heavy engine for such a marginal use. I'll do the simple way for now. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Feasability of a Range function
Jean-Christophe Deschamps wrote: >> Why not just >> >> select some_scalar_function(i) where i between 1 and 5; > > That's because we then get > No such column: i. Ah. You want table-valued functions, like this: http://msdn.microsoft.com/en-us/library/ms191165.aspx Those should appear where a table may - in your example, in a FROM clause. Having one in a WHERE clause makes no sense. WHERE clause never introduces a new identifier. In any case, SQLite doesn't support table-valued functions. The closest thing to it is a virtual table: http://www.sqlite.org/cvstrac/wiki?p=VirtualTables -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Feasability of a Range function
>Why not just > >select some_scalar_function(i) where i between 1 and 5; That's because we then get No such column: i. That was not very important. I would have the use for such possibility but I can live without. My question was just curiosity about whether something along the line could work, without any materialized table holding successive integers in the needed range. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Feasability of a Range function
Jean-Christophe Deschamps wrote: > I'm trying to determine if a Range(from, to) function can be made as an > extension function. > > Its effect would be to expand, for instance, range(1, 5) into (1, 2, 3, > 4, 5) for use in constructs similar to > select some_scalar_function(i) where i in range(1, 5); > without having to build a table holding integers 1..5 I don't see how SQLite would accept that as a valid syntax, no matter how you define the function. However, I don't understand the point of the exercise. Why not just select some_scalar_function(i) where i between 1 and 5; -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Feasability of a Range function
I'm trying to determine if a Range(from, to) function can be made as an extension function. Its effect would be to expand, for instance, range(1, 5) into (1, 2, 3, 4, 5) for use in constructs similar to select some_scalar_function(i) where i in range(1, 5); without having to build a table holding integers 1..5 I don't see how that's possible, but maybe someone with better internal knowledge can advise. Thank you. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
