Re: [sqlite] Syntax. table-function-name vs table-naming-function-name

[petern]

Richard, thank you for your reply.  I really appreciate it.  The fact that
you have carefully thought about how to cross the FROM clause barrier with
expressions is itself a useful fact.  If you say the current implementation
is painted into a corner on this issue I believe you.  It would be
impossible to deduce this fact by simply studying documentation or source
code.  Thank you very much for taking my question.

At some point I may try what you suggest and see what I can learn.  In the
meantime, the CSV virtual table example appears to be closest to where I'd
like to go next.

https://sqlite.org/csv.html

That example allows the caller to specify a creation schema.  Expanding on
the schema specifier, perhaps there is a way to make xBestIndex() and
xFilter() work more generally in the problem domain of lightly encapsulated
dynamic tables.







On Sun, Mar 26, 2017 at 11:44 AM, Richard Hipp  wrote:

> On 3/25/17, petern  wrote:
> >
> > Why can't we have a parallel syntax branch for scalar valued
> > "table-naming-function-name"?  In other words, why not have support for
> > simply naming an existing table or view by return value of a scalar
> > function?
> >
>
> The easiest way for me to answer this is to ask you to provide a
> sample implementation.  After you've work on the problem for a while,
> I think you will begin to understand why it is not possible.  I can't
> seem to come up with words to help make that realization any easier.
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Syntax. table-function-name vs table-naming-function-name

[Richard Hipp]

On 3/25/17, petern  wrote:
>
> Why can't we have a parallel syntax branch for scalar valued
> "table-naming-function-name"?  In other words, why not have support for
> simply naming an existing table or view by return value of a scalar
> function?
>

The easiest way for me to answer this is to ask you to provide a
sample implementation.  After you've work on the problem for a while,
I think you will begin to understand why it is not possible.  I can't
seem to come up with words to help make that realization any easier.

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Syntax. table-function-name vs table-naming-function-name

[petern]

Consider the "table-or-subquery" syntax chart linked below.

https://www.sqlite.org/syntax/table-or-subquery.html

A modest proposal.

In the "table-or-subquery" syntax there exists a branch for
"table-function-name", aka table valued virtual tables.

Why can't we have a parallel syntax branch for scalar valued
"table-naming-function-name"?  In other words, why not have support for
simply naming an existing table or view by return value of a scalar
function?

There are very good reasons why this relatively simple change is a
worthwhile improvement.

1. Since a named table or view is created by executing ordinary data
definition SQL statements inside the table-naming-function, the dynamically
named table/view and indexes can directly and fully participate in query
optimization of the outer scope.

2. There are many dynamic table applications where a light wrapper on an
ordinary table is all that is necessary.  But, under the current API, one
must write a lot of finicky optimizer hint code to expose a dynamically
generated ordinary table through the requisite virtual table API.

3.  I will speculate a bit.  I see that DRH, with great surprise, very
recently asked a participant on this forum why they are using such an old
version of SQLite.  Based on both the frequent need for ordinary but
dynamic tables and the lack of a thin wrapper for such tables, there surely
must be a lot of otherwise unnecessary "heavy" virtual table code in the
wild.  For credible production use, this heavy code must undergo a
prohibitively time consuming requalification process after every engine
update.  Thus, it's no wonder that important SQLite applications cannot
keep up with engine releases.

I look forward to reading the forum comments on this modest proposal.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users