[
https://issues.apache.org/jira/browse/DERBY-4566?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12840545#action_12840545
]
Sylvain Leroux commented on DERBY-4566:
---------------------------------------
Hi Rick,
and thanks for your comments on this issue.
I agree that DERBY-3973 is of great benefit for joining or filtering metadata.
It was not in my intentions to redo that work here. But there is some use cases
that just can't be handled in the relational world alone. Here is a concrete
example: On a regular basis, I had to audit all the tables from a DB. This
"audit" was as simple as counting the rows in each table:
INSERT INTO Audit(tbl_name, tbl_row_count) VALUES
("table_a", (SELECT COUNT(*) FROM tabla_a),
("table_b", (SELECT COUNT(*) FROM table_b),
...
As far as I know, I can't use a join (nor a prepared statement) here because,
in the relational model, table names are not data. Some kind of external
language is required to bind the table names to the SQL request. On the other
hand, requiring a Java development just for that seems to be an overkill. So my
primary motivation here was to be able to write something like (the exact
syntax yet has to be defined):
FOR EACH table IN table_list
INSERT INTO Audit(tbl_name, tbl_row_count) VALUES (${table}, (SELECT
COUNT(*) FROM ${table}));
As for the question "How to setup table_list? Through metadata? By using a
table function? Manually defined? Other?", I will left that open for now.
Concerning the second use case you identified, I agree with you that having
some kind of loop control using scalar values would be of great benefit. By
looking closer, I think this is mostly the same as the above example, just
replacing the "IN table_list" statement by "IN 1..100". So I think I could come
with some kind of proposal that addresses this use case, without obliterating
the former.
At this point, I think we are talking about several improvements to ij:
a) add support for user defined variables
b) add control structures
c) allow some basic of arithmetic on variables
For now, I will concentrate on a).
As usual, all of this is open to discussion,
- Sylvain
> Make it possible to define variables in ij
> ------------------------------------------
>
> Key: DERBY-4566
> URL: https://issues.apache.org/jira/browse/DERBY-4566
> Project: Derby
> Issue Type: Improvement
> Components: Tools
> Reporter: Sylvain Leroux
> Assignee: Sylvain Leroux
>
> It would be nice to have a way in ij to store statement results in variables
> for later re-use.
> Notably, It would allow to use (meta)data obtained by some statements (such
> as SHOW ....) beyond the simple display of the result. Moreover, it could be
> a first step toward extending the scripting capabilities of ij.
> See http://old.nabble.com/IJ-scripting-(variables-in-IJ-)-td27290712.html
> Related to DERBY-4557
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
