db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Sylvain Leroux (JIRA)" <j...@apache.org>
Subject [jira] Commented: (DERBY-4566) Make it possible to define variables in ij
Date Wed, 03 Mar 2010 09:21:27 GMT

    [ 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
> 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.

View raw message