You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by "Shazin Sadakath (JIRA)" <ji...@apache.org> on 2009/12/09 09:15:18 UTC
[jira] Created: (DERBY-4467) Non Java Stored Routines Using Java
Scripting API
Non Java Stored Routines Using Java Scripting API
-------------------------------------------------
Key: DERBY-4467
URL: https://issues.apache.org/jira/browse/DERBY-4467
Project: Derby
Issue Type: Improvement
Components: Network Server
Environment: Windows XP, Java 1.6
Reporter: Shazin Sadakath
Priority: Minor
I have been working on an idea that lets you create Stored Procedures using languages other than Java with a perspective of Apache Derby as a Network Server. I tried this by using Sun Java Scripting API for JRE 1.6+ and Apache BSF http://jakarta.apache.org/bsf/ for JRE 1.4+.
This has certain advantages when it comes to compiled java stored routines.
1. It is dynamic.
2. Requires no configuration of classpath.
3. Requires no use of SQLJ.INSTALL_JAR and SQLJ.REPLACE_JAR.
4. Very easy to maintain
5. Allows the use of Sun Java Runtime Library via Object Binding.
6. Supports JavaScript, Jython, Dynamic Java and many more scripting languages. https://scripting.dev.java.net/
7. No need of introducing a new SQL/PSM grammar to the current SQLParser.
Just an idea in its very early stage. Would like to hear your responses.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-4467) Non Java Stored Routines Using Java
Scripting API
Posted by "Shazin Sadakath (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4467?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Shazin Sadakath updated DERBY-4467:
-----------------------------------
Attachment: DDL.sql
ScreenShot.JPG
Screen Shot of the Demonstration
> Non Java Stored Routines Using Java Scripting API
> -------------------------------------------------
>
> Key: DERBY-4467
> URL: https://issues.apache.org/jira/browse/DERBY-4467
> Project: Derby
> Issue Type: Improvement
> Components: Network Server
> Environment: Windows XP, Java 1.6
> Reporter: Shazin Sadakath
> Priority: Minor
> Attachments: DDL.sql, ScreenShot.JPG
>
>
> I have been working on an idea that lets you create Stored Procedures using languages other than Java with a perspective of Apache Derby as a Network Server. I tried this by using Sun Java Scripting API for JRE 1.6+ and Apache BSF http://jakarta.apache.org/bsf/ for JRE 1.4+.
> This has certain advantages when it comes to compiled java stored routines.
> 1. It is dynamic.
> 2. Requires no configuration of classpath.
> 3. Requires no use of SQLJ.INSTALL_JAR and SQLJ.REPLACE_JAR.
> 4. Very easy to maintain
> 5. Allows the use of Sun Java Runtime Library via Object Binding.
> 6. Supports JavaScript, Jython, Dynamic Java and many more scripting languages. https://scripting.dev.java.net/
> 7. No need of introducing a new SQL/PSM grammar to the current SQLParser.
> There are two approaches for this enhancement.
> 1. Having this as an external utility component such as ij, dblooks
> 2. Having this as a built in feature.
> I have done this in the former method for demonstration purpose.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-4467) Non Java Stored Routines Using Java
Scripting API
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4467?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12788934#action_12788934 ]
Rick Hillegas commented on DERBY-4467:
--------------------------------------
Hi Shazin,
I think this is a really cool technique. As I understand it, you have provided some code to enable the following:
1) Declare an external procedure which is written in a scripting language that runs on the VM...
2) ...and include the source code for that procedure inline in the SQL statement which declares the procedure
Dag has raised an interesting point:
a) God-like powers are conferred on any user who can declare and execute the CREATE_PROCEDURE proc.
I can see other possibilities for this technique, in particular, support for table functions that are coded in scripting languages.
At a minimum, we can refer users to your code as an example of how to declare dynamic, inline routines written in scripting languages. I would have to give some thought to whether we could integrate this more tightly in Derby. Let me push your proposed syntax forward a bit more. Bear with me because I'm not very familiar with the specifics of these scripting languages:
create function foo( a int, b varchar( 100 ) )
returns double
language javascript
parameter style derby_scripting_style
external name '...'
The elipsis is where you would put your inline scripting code. I don't know what the meaning of the function arguments would be, however. Can you give us an example of how you would map SQL parameters to arguments that are passed to the script interpreter?
Thanks!
> Non Java Stored Routines Using Java Scripting API
> -------------------------------------------------
>
> Key: DERBY-4467
> URL: https://issues.apache.org/jira/browse/DERBY-4467
> Project: Derby
> Issue Type: Improvement
> Components: Network Server
> Environment: Windows XP, Java 1.6
> Reporter: Shazin Sadakath
> Priority: Minor
> Attachments: DDL.sql, DerbyStoredRoutine.zip, ScreenShot.JPG
>
>
> I have been working on an idea that lets you create Stored Procedures using languages other than Java with a perspective of Apache Derby as a Network Server. I tried this by using Sun Java Scripting API for JRE 1.6+ and Apache BSF http://jakarta.apache.org/bsf/ for JRE 1.4+.
> This has certain advantages when it comes to compiled java stored routines.
> 1. It is dynamic.
> 2. Requires no configuration of classpath.
> 3. Requires no use of SQLJ.INSTALL_JAR and SQLJ.REPLACE_JAR.
> 4. Very easy to maintain
> 5. Allows the use of Sun Java Runtime Library via Object Binding.
> 6. Supports JavaScript, Jython, Dynamic Java and many more scripting languages. https://scripting.dev.java.net/
> 7. No need of introducing a new SQL/PSM grammar to the current SQLParser.
> There are two approaches for this enhancement.
> 1. Having this as an external utility component such as ij, dblooks
> 2. Having this as a built in feature.
> I have done this in the former method for demonstration purpose.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-4467) Non Java Stored Routines Using Java
Scripting API
Posted by "Shazin Sadakath (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4467?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Shazin Sadakath updated DERBY-4467:
-----------------------------------
Description:
I have been working on an idea that lets you create Stored Procedures using languages other than Java with a perspective of Apache Derby as a Network Server. I tried this by using Sun Java Scripting API for JRE 1.6+ and Apache BSF http://jakarta.apache.org/bsf/ for JRE 1.4+.
This has certain advantages when it comes to compiled java stored routines.
1. It is dynamic.
2. Requires no configuration of classpath.
3. Requires no use of SQLJ.INSTALL_JAR and SQLJ.REPLACE_JAR.
4. Very easy to maintain
5. Allows the use of Sun Java Runtime Library via Object Binding.
6. Supports JavaScript, Jython, Dynamic Java and many more scripting languages. https://scripting.dev.java.net/
7. No need of introducing a new SQL/PSM grammar to the current SQLParser.
There are two approaches for this enhancement.
1. Having this as an external utility component such as ij, dblooks
2. Having this as a built in feature.
I have done this in the former method for demonstration purpose.
was:
I have been working on an idea that lets you create Stored Procedures using languages other than Java with a perspective of Apache Derby as a Network Server. I tried this by using Sun Java Scripting API for JRE 1.6+ and Apache BSF http://jakarta.apache.org/bsf/ for JRE 1.4+.
This has certain advantages when it comes to compiled java stored routines.
1. It is dynamic.
2. Requires no configuration of classpath.
3. Requires no use of SQLJ.INSTALL_JAR and SQLJ.REPLACE_JAR.
4. Very easy to maintain
5. Allows the use of Sun Java Runtime Library via Object Binding.
6. Supports JavaScript, Jython, Dynamic Java and many more scripting languages. https://scripting.dev.java.net/
7. No need of introducing a new SQL/PSM grammar to the current SQLParser.
Just an idea in its very early stage. Would like to hear your responses.
> Non Java Stored Routines Using Java Scripting API
> -------------------------------------------------
>
> Key: DERBY-4467
> URL: https://issues.apache.org/jira/browse/DERBY-4467
> Project: Derby
> Issue Type: Improvement
> Components: Network Server
> Environment: Windows XP, Java 1.6
> Reporter: Shazin Sadakath
> Priority: Minor
>
> I have been working on an idea that lets you create Stored Procedures using languages other than Java with a perspective of Apache Derby as a Network Server. I tried this by using Sun Java Scripting API for JRE 1.6+ and Apache BSF http://jakarta.apache.org/bsf/ for JRE 1.4+.
> This has certain advantages when it comes to compiled java stored routines.
> 1. It is dynamic.
> 2. Requires no configuration of classpath.
> 3. Requires no use of SQLJ.INSTALL_JAR and SQLJ.REPLACE_JAR.
> 4. Very easy to maintain
> 5. Allows the use of Sun Java Runtime Library via Object Binding.
> 6. Supports JavaScript, Jython, Dynamic Java and many more scripting languages. https://scripting.dev.java.net/
> 7. No need of introducing a new SQL/PSM grammar to the current SQLParser.
> There are two approaches for this enhancement.
> 1. Having this as an external utility component such as ij, dblooks
> 2. Having this as a built in feature.
> I have done this in the former method for demonstration purpose.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-4467) Non Java Stored Routines Using Java
Scripting API
Posted by "Shazin Sadakath (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4467?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Shazin Sadakath updated DERBY-4467:
-----------------------------------
Attachment: ScreenShot.JPG
Screen Shot of the Prototype with JavaScript Stored Routine
> Non Java Stored Routines Using Java Scripting API
> -------------------------------------------------
>
> Key: DERBY-4467
> URL: https://issues.apache.org/jira/browse/DERBY-4467
> Project: Derby
> Issue Type: Improvement
> Components: Network Server
> Environment: Windows XP, Java 1.6
> Reporter: Shazin Sadakath
> Priority: Minor
>
> I have been working on an idea that lets you create Stored Procedures using languages other than Java with a perspective of Apache Derby as a Network Server. I tried this by using Sun Java Scripting API for JRE 1.6+ and Apache BSF http://jakarta.apache.org/bsf/ for JRE 1.4+.
> This has certain advantages when it comes to compiled java stored routines.
> 1. It is dynamic.
> 2. Requires no configuration of classpath.
> 3. Requires no use of SQLJ.INSTALL_JAR and SQLJ.REPLACE_JAR.
> 4. Very easy to maintain
> 5. Allows the use of Sun Java Runtime Library via Object Binding.
> 6. Supports JavaScript, Jython, Dynamic Java and many more scripting languages. https://scripting.dev.java.net/
> 7. No need of introducing a new SQL/PSM grammar to the current SQLParser.
> There are two approaches for this enhancement.
> 1. Having this as an external utility component such as ij, dblooks
> 2. Having this as a built in feature.
> I have done this in the former method for demonstration purpose.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-4467) Non Java Stored Routines Using Java
Scripting API
Posted by "Shazin Sadakath (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4467?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Shazin Sadakath updated DERBY-4467:
-----------------------------------
Attachment: DerbyStoredRoutine.zip
Sources
> Non Java Stored Routines Using Java Scripting API
> -------------------------------------------------
>
> Key: DERBY-4467
> URL: https://issues.apache.org/jira/browse/DERBY-4467
> Project: Derby
> Issue Type: Improvement
> Components: Network Server
> Environment: Windows XP, Java 1.6
> Reporter: Shazin Sadakath
> Priority: Minor
> Attachments: DDL.sql, DerbyStoredRoutine.zip, ScreenShot.JPG
>
>
> I have been working on an idea that lets you create Stored Procedures using languages other than Java with a perspective of Apache Derby as a Network Server. I tried this by using Sun Java Scripting API for JRE 1.6+ and Apache BSF http://jakarta.apache.org/bsf/ for JRE 1.4+.
> This has certain advantages when it comes to compiled java stored routines.
> 1. It is dynamic.
> 2. Requires no configuration of classpath.
> 3. Requires no use of SQLJ.INSTALL_JAR and SQLJ.REPLACE_JAR.
> 4. Very easy to maintain
> 5. Allows the use of Sun Java Runtime Library via Object Binding.
> 6. Supports JavaScript, Jython, Dynamic Java and many more scripting languages. https://scripting.dev.java.net/
> 7. No need of introducing a new SQL/PSM grammar to the current SQLParser.
> There are two approaches for this enhancement.
> 1. Having this as an external utility component such as ij, dblooks
> 2. Having this as a built in feature.
> I have done this in the former method for demonstration purpose.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-4467) Non Java Stored Routines Using Java
Scripting API
Posted by "Shazin Sadakath (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4467?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12788485#action_12788485 ]
Shazin Sadakath commented on DERBY-4467:
----------------------------------------
With current approach arguments and results can be passed in as VARCHAR.
Multiple arguments can be passed as CSV.
EXECUTE_PROCEDURE(<Procedure Name>,<Arguments>);
Or
Can you this as a built in feature and allow to create a Stored Routine like
CREATE PROCEDURE TestProc(ARG1 VARCHAR(10))
PARAMETER STYLE JAVA
SCRIPT 'function procedure(){
---
}'
> Non Java Stored Routines Using Java Scripting API
> -------------------------------------------------
>
> Key: DERBY-4467
> URL: https://issues.apache.org/jira/browse/DERBY-4467
> Project: Derby
> Issue Type: Improvement
> Components: Network Server
> Environment: Windows XP, Java 1.6
> Reporter: Shazin Sadakath
> Priority: Minor
> Attachments: DDL.sql, DerbyStoredRoutine.zip, ScreenShot.JPG
>
>
> I have been working on an idea that lets you create Stored Procedures using languages other than Java with a perspective of Apache Derby as a Network Server. I tried this by using Sun Java Scripting API for JRE 1.6+ and Apache BSF http://jakarta.apache.org/bsf/ for JRE 1.4+.
> This has certain advantages when it comes to compiled java stored routines.
> 1. It is dynamic.
> 2. Requires no configuration of classpath.
> 3. Requires no use of SQLJ.INSTALL_JAR and SQLJ.REPLACE_JAR.
> 4. Very easy to maintain
> 5. Allows the use of Sun Java Runtime Library via Object Binding.
> 6. Supports JavaScript, Jython, Dynamic Java and many more scripting languages. https://scripting.dev.java.net/
> 7. No need of introducing a new SQL/PSM grammar to the current SQLParser.
> There are two approaches for this enhancement.
> 1. Having this as an external utility component such as ij, dblooks
> 2. Having this as a built in feature.
> I have done this in the former method for demonstration purpose.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-4467) Non Java Stored Routines Using Java
Scripting API
Posted by "Shazin Sadakath (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4467?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Shazin Sadakath updated DERBY-4467:
-----------------------------------
Attachment: (was: ScreenShot.JPG)
> Non Java Stored Routines Using Java Scripting API
> -------------------------------------------------
>
> Key: DERBY-4467
> URL: https://issues.apache.org/jira/browse/DERBY-4467
> Project: Derby
> Issue Type: Improvement
> Components: Network Server
> Environment: Windows XP, Java 1.6
> Reporter: Shazin Sadakath
> Priority: Minor
>
> I have been working on an idea that lets you create Stored Procedures using languages other than Java with a perspective of Apache Derby as a Network Server. I tried this by using Sun Java Scripting API for JRE 1.6+ and Apache BSF http://jakarta.apache.org/bsf/ for JRE 1.4+.
> This has certain advantages when it comes to compiled java stored routines.
> 1. It is dynamic.
> 2. Requires no configuration of classpath.
> 3. Requires no use of SQLJ.INSTALL_JAR and SQLJ.REPLACE_JAR.
> 4. Very easy to maintain
> 5. Allows the use of Sun Java Runtime Library via Object Binding.
> 6. Supports JavaScript, Jython, Dynamic Java and many more scripting languages. https://scripting.dev.java.net/
> 7. No need of introducing a new SQL/PSM grammar to the current SQLParser.
> There are two approaches for this enhancement.
> 1. Having this as an external utility component such as ij, dblooks
> 2. Having this as a built in feature.
> I have done this in the former method for demonstration purpose.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-4467) Non Java Stored Routines Using Java
Scripting API
Posted by "Shazin Sadakath (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4467?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12791882#action_12791882 ]
Shazin Sadakath commented on DERBY-4467:
----------------------------------------
Hi Rick,
What I have done thus far was working on an idea to make Derby as friendly as possible to non Java Developers thus enabling
derby to reach a large no of developers. As a solution for that I have just brought forward this.
I have already gone through what are the changes necessary to enabling declaring of these sort of Stored Routines but I haven't implemented yet.
That is because I knew that my idea would also have a lot of disadvantages just as the one Dag pointed out.
Anyway I will try my best to implement what you asked and submit so that you can decide what needs to done.
Thanks,
Shazin
> Non Java Stored Routines Using Java Scripting API
> -------------------------------------------------
>
> Key: DERBY-4467
> URL: https://issues.apache.org/jira/browse/DERBY-4467
> Project: Derby
> Issue Type: Improvement
> Components: Network Server
> Environment: Windows XP, Java 1.6
> Reporter: Shazin Sadakath
> Priority: Minor
> Attachments: DDL.sql, DerbyStoredRoutine.zip, ScreenShot.JPG
>
>
> I have been working on an idea that lets you create Stored Procedures using languages other than Java with a perspective of Apache Derby as a Network Server. I tried this by using Sun Java Scripting API for JRE 1.6+ and Apache BSF http://jakarta.apache.org/bsf/ for JRE 1.4+.
> This has certain advantages when it comes to compiled java stored routines.
> 1. It is dynamic.
> 2. Requires no configuration of classpath.
> 3. Requires no use of SQLJ.INSTALL_JAR and SQLJ.REPLACE_JAR.
> 4. Very easy to maintain
> 5. Allows the use of Sun Java Runtime Library via Object Binding.
> 6. Supports JavaScript, Jython, Dynamic Java and many more scripting languages. https://scripting.dev.java.net/
> 7. No need of introducing a new SQL/PSM grammar to the current SQLParser.
> There are two approaches for this enhancement.
> 1. Having this as an external utility component such as ij, dblooks
> 2. Having this as a built in feature.
> I have done this in the former method for demonstration purpose.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-4467) Non Java Stored Routines Using Java
Scripting API
Posted by "Shazin Sadakath (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4467?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Shazin Sadakath updated DERBY-4467:
-----------------------------------
Comment: was deleted
(was: Screen Shot of the Demonstration)
> Non Java Stored Routines Using Java Scripting API
> -------------------------------------------------
>
> Key: DERBY-4467
> URL: https://issues.apache.org/jira/browse/DERBY-4467
> Project: Derby
> Issue Type: Improvement
> Components: Network Server
> Environment: Windows XP, Java 1.6
> Reporter: Shazin Sadakath
> Priority: Minor
> Attachments: DDL.sql, DerbyStoredRoutine.zip, ScreenShot.JPG
>
>
> I have been working on an idea that lets you create Stored Procedures using languages other than Java with a perspective of Apache Derby as a Network Server. I tried this by using Sun Java Scripting API for JRE 1.6+ and Apache BSF http://jakarta.apache.org/bsf/ for JRE 1.4+.
> This has certain advantages when it comes to compiled java stored routines.
> 1. It is dynamic.
> 2. Requires no configuration of classpath.
> 3. Requires no use of SQLJ.INSTALL_JAR and SQLJ.REPLACE_JAR.
> 4. Very easy to maintain
> 5. Allows the use of Sun Java Runtime Library via Object Binding.
> 6. Supports JavaScript, Jython, Dynamic Java and many more scripting languages. https://scripting.dev.java.net/
> 7. No need of introducing a new SQL/PSM grammar to the current SQLParser.
> There are two approaches for this enhancement.
> 1. Having this as an external utility component such as ij, dblooks
> 2. Having this as a built in feature.
> I have done this in the former method for demonstration purpose.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-4467) Non Java Stored Routines Using Java
Scripting API
Posted by "Dag H. Wanvik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4467?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12788220#action_12788220 ]
Dag H. Wanvik commented on DERBY-4467:
--------------------------------------
Interesting!
Would you handle any args & results via a string (to be parsed using some convention)?
We should also consider the security implication of permanently enabling CREATE_PROCEDURE and EXECUTE_PROCEDURE; it would allow a client to register and run arbitrary code on the server modulo any Java security policy in effect on the server.
> Non Java Stored Routines Using Java Scripting API
> -------------------------------------------------
>
> Key: DERBY-4467
> URL: https://issues.apache.org/jira/browse/DERBY-4467
> Project: Derby
> Issue Type: Improvement
> Components: Network Server
> Environment: Windows XP, Java 1.6
> Reporter: Shazin Sadakath
> Priority: Minor
> Attachments: DDL.sql, DerbyStoredRoutine.zip, ScreenShot.JPG
>
>
> I have been working on an idea that lets you create Stored Procedures using languages other than Java with a perspective of Apache Derby as a Network Server. I tried this by using Sun Java Scripting API for JRE 1.6+ and Apache BSF http://jakarta.apache.org/bsf/ for JRE 1.4+.
> This has certain advantages when it comes to compiled java stored routines.
> 1. It is dynamic.
> 2. Requires no configuration of classpath.
> 3. Requires no use of SQLJ.INSTALL_JAR and SQLJ.REPLACE_JAR.
> 4. Very easy to maintain
> 5. Allows the use of Sun Java Runtime Library via Object Binding.
> 6. Supports JavaScript, Jython, Dynamic Java and many more scripting languages. https://scripting.dev.java.net/
> 7. No need of introducing a new SQL/PSM grammar to the current SQLParser.
> There are two approaches for this enhancement.
> 1. Having this as an external utility component such as ij, dblooks
> 2. Having this as a built in feature.
> I have done this in the former method for demonstration purpose.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.