Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Development" <derby-dev@db.apache.org>
Message-ID: <494673552.1118180921889.JavaMail.jira@ajax.apache.org>
Date: Tue, 7 Jun 2005 23:48:41 +0200 (CEST)
From: "A B (JIRA)" <derby-dev@db.apache.org>
To: derby-dev@db.apache.org
Subject: [jira] Updated: (DERBY-337) dblook doesn't generate SQL statements
 for SQL functions.
In-Reply-To: <1267330225.1117990780036.JavaMail.jira@ajax.apache.org>
Mime-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit

     [ http://issues.apache.org/jira/browse/DERBY-337?page=all ]

A B updated DERBY-337:
----------------------

    Attachment: derby-337.stat
                derby-337.patch

Attaching patch for this issue.  In order to get dblook to generate the DDL for Derby functions, this patch does the following:

1 - Renames "impl/tools/dblook/DB_StoredProcedure.java" to "impl/tools/dblook/DB_Alias.java" because that file now handles stored procedures AND functions AND synonyms, all of which are based on the SYSALIASES system catalog.

2 - Adds logic to new DB_Alias.java file to generate DDL for functions.

3 - Modifies the "toString()" method of the catalog/types/RoutineAliasInfo file to generate a string that is SYNTACTICALLY VALID based on whether an instance of that class is for a PROCEDURE or for a FUNCTION.  The reason this change is required is because the "ALIASINFO" column that is returned as part of the SYS.SYSALIASES result set is an instance of RoutineAliasInfo, and thus a call to ResultSet.getString() on the ALIASINFO column ultimately makes a call to RoutineAliasInfo.toString().  That said, the dblook utility makes a "getString()" call on the ALIASINFO column and uses the result to generate the corresponding DDL.  Before this patch, the result of the toString() method always corresponded to the PROCEDURE syntax; but now, since dblook is generating DDL for FUNCTIONs, the RoutineAliasInfo.toString() method must recognize if an instance is a PROCEDURE or FUNCTION and return the appropriate syntax.

4 - Adds test cases for FUNCTIONS to the dblook tests and updates the master files accordingly.

I ran derbyall with these changes on Windows 2000 with Sun JDK 1.4.2, and all tests passed (except for one failure (testij.java) that is unrelated to my changes).

NOTE TO COMMITTERS: Because this patch renames an existing file, it's a bit tricky to apply.  I had to copy "DB_StoredProcedure.java" to "DB_Alias.java" before applying the patch in order to get it to work.  I think Andrew suggested some time ago that a commiter use "svn move" to retain the history of the file that has changed names--I tried that, but after I did so the patch wouldn't apply.  So I just copied the file to it's new name, then applied the patch.  But I leave it up to people more familiar with svn to do the right thing here...

> dblook doesn't generate SQL statements for SQL functions.
> ---------------------------------------------------------
>
>          Key: DERBY-337
>          URL: http://issues.apache.org/jira/browse/DERBY-337
>      Project: Derby
>         Type: Bug
>   Components: Tools
>     Versions: 10.0.2.0
>  Environment: All platforms.
>     Reporter: Satheesh Bandaram
>     Assignee: A B
>     Priority: Critical
>      Fix For: 10.1.0.0
>  Attachments: derby-337.patch, derby-337.stat
>
> DBLOOK schema dumping tool doesn't emit SQL statements for functions. It covers procedures and all other database objects, but not funtions. Here is an example I tried:
> [bandaram:satheesh] java org.apache.derby.tools.dblook -d 'jdbc:derby:tdb'
> -- Timestamp: 2005-06-05 09:41:20.603
> -- Source database is: tdb
> -- Connection URL is: jdbc:derby:tdb
> -- appendLogs: false
> -- ----------------------------------------------
> -- DDL Statements for tables
> -- ----------------------------------------------
> CREATE TABLE "APP"."T" ("I" INTEGER);
> At this point, the database has only one table 'T'. Now create a function:
> C:\DerbyCode\bug>java -Dij.protocol=jdbc:derby: org.apache.derby.tools.ij
> ij version 10.1
> ij> connect 'tdb';
> ij> create function gatp(SCH VARCHAR(128), TBL VARCHAR(128)) RETURNS VARCHAR(100
> 0)
> EXTERNAL NAME 'org.apache.derbyTesting.functionTests.util.TestPropertyInfo.getAllTableProperties'
> LANGUAGE JAVA PARAMETER STYLE JAVA;
> 0 rows inserted/updated/deleted
> ij> quit;
> Now try dblook. Should show a function, but doesn't.
> [bandaram:satheesh] java org.apache.derby.tools.dblook -d 'jdbc:derby:tdb'
> -- Timestamp: 2005-06-05 09:41:20.603
> -- Source database is: tdb
> -- Connection URL is: jdbc:derby:tdb
> -- appendLogs: false
> -- ----------------------------------------------
> -- DDL Statements for tables
> -- ----------------------------------------------
> CREATE TABLE "APP"."T" ("I" INTEGER);
> Now create a procedure:
> C:\DerbyCode\bug>java -Dij.protocol=jdbc:derby: org.apache.derby.tools.ij
> ij version 10.1
> ij> connect 'tdb';
> ij> create procedure insertAsciiColumn( stmtText varchar( 256), colNumber int) P
> ARAMETER STYLE JAVA LANGUAGE JAVA MODIFIES SQL DATA
>   EXTERNAL NAME 'org.apache.derbyTesting.functionTests.util.StreamUtil.insertAsc
> iiColumn';
> 0 rows inserted/updated/deleted
> ij> 
> DBLOOK output include newly created procedure, but still not function statement.
> [bandaram:satheesh] java org.apache.derby.tools.dblook -d 'jdbc:derby:tdb'
> -- Timestamp: 2005-06-05 09:43:09.5
> -- Source database is: tdb
> -- Connection URL is: jdbc:derby:tdb
> -- appendLogs: false
> -- ----------------------------------------------
> -- DDL Statements for stored procedures
> -- ----------------------------------------------
> CREATE PROCEDURE "APP"."INSERTASCIICOLUMN" (IN STMTTEXT VARCHAR(256),IN COLNUMBE
> R INTEGER) LANGUAGE JAVA PARAMETER STYLE JAVA MODIFIES SQL DATA EXTERNAL NAME 'o
> rg.apache.derbyTesting.functionTests.util.StreamUtil.insertAsciiColumn' ;
> -- ----------------------------------------------
> -- DDL Statements for tables
> -- ----------------------------------------------
> CREATE TABLE "APP"."T" ("I" INTEGER);

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
   http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
   http://www.atlassian.com/software/jira