#28 Extensions for Plugin/Stored Procedure
Extensions to the Core engine to allow a Plugin
Architecture and allow stored procedures in other
langauges. Introduced the "module" concept and
function factory concept to allow function factories
for languages such as prolog, javascript, or for
external pacakges such as WEKA. This zip file includes
the package RsLibrary. RsLibrary provides
the "execute" statement and allows connecting to other
databases via jdbc. I will submit other packages such
as JavaScript, Weka and Prolog shortly.
In order to use RsLibrary for example, you must call
the stored procedure load_module:
call load_module('org.hsqldb.RsLibrary')
after you shutdown your databse, it will remember that
you have RsLibrary in your space.
Here's the description of what's in RsLibrary
example usage of execute, get_conn and close_conn...
for local connection
call execute('select * from address')
for external hsqldb connection
call execute('select * from address',
get_conn
('jdbc:hsqldb:dbname', 'sa', '', 'org.hsqldb.jdbcDriver
'))
or to do an external select
call execute('select * from address',
get_conn
('jdbc:odbc:dbname', 'sa', '', 'sun.jdbc.odbc.JdbcOdbcD
river'))
or to do an external join
select * from address
inner join (execute('select * from
address',
get_conna
('jdbc:odbc:dbname', 'sa', '', 'sun.jdbc.odbc.JdbcOdbcD
river')))
as address_ext
on address.name = address_ext.name
...
There's also a entry point for changing the parser
dynamicaly.
...
Here's the list of files in the zip.
Column.java
Database.java
DatabaseInformation.java
DynamicResult.java
Expression.java
ExpressionList.java
Function.java
HsqlModule.java
Library.java
ModuleManager.java
Parser.java
ParserFactory.java
Result.java
RsLibrary.java
Select.java
Tokenizer.java
jdbcConnection.java
Cheers,
Hiep H Nguyen
hiep256@yahoo.com
This is the zip file with all the *.java files. Compile and put in your path.
Logged In: YES
user_id=150940
This revision of the HSQLDB should form a separate branch,
continued to be synchronised with the patches to the stable
code, tested and hopefully released in the future.
Logged In: NO
How do I form a sepearate branch? I've had some request for
the lucene module which requires this library.
-hiep
Logged In: YES
user_id=150940
Hiep,
I'm sorry for the delay responding. I was away when you
posted the message and have just spotted it.
As you know your enhancements were put on the CVS on the
hsqldb trunk. We are now thinking in terms of separate
branches for major restructured codebase such as yours.
This will allow eventual merging of different development
efforts. We will shotly set up the branch for you to upload
the code.
I would like to thank you for what you've done. I think
your enhancments will be really usefull to a large number
of users. The ability to join across different databases is
great and support for Lucene will open a whole new range of
possibilities.
Logged In: YES
user_id=32217
Fred,
This and #456700, #456709: are they going to make it? All
of this work in the Trackers are to clear my SF.net
Dashboard, so I can concentrate on the re-org of fora,
Trackers and readying the CVS and doc management.
Hiep's patches for embedding other languages, eventually
could include XML (to make hsqldb an 'active' XML source
for J2EE and CMS/DbPrism), python, php, (both have Java
implementations, for instance, in Coccoon)...even PL/SQL
and perl...
Interesting, but do we need to do this now or in FUTURE?
Mike
Logged In: YES
user_id=199381
Re-uptake and starting review for inclusion in 1.7.1 today, along with
Karl's bulk save/restore and my metadata patch.
Bumped up pri, set to Remind and reassigned to me for the moment.
Will be doing a refresher reading over the next week and will get in
touch with Fred and Hiep over perhaps taking this all bit further (there
are already the seeds of several ideas that will hopefully become
central in hsqldb 2.x) as well as integrating this with metadata patch
(nice to know what modules are available, what the declared source
code for one's SQL functions/stored procedures is, etc.).
I am especially very interested in reworking the syntax extentions and
adding a new database object type: database link so that the
examples above become like:
CREATE DATABASELINK dbname (connection_spec,
authorization_spec);
GRANT ALL ON DATABASELINK dbname TO PUBLIC | user
select * from address@dbname;
select col1 from address@dbname where col1 > somevalue
and extending the recent query rewriting patch (making SQL command
execution a stream of command objects rather than processing in-line,
allowing a simple RULE system to be implemented post as well as pre
parsing) so that we can hopefully start playing around with the first
steps of SQL-based declaratively defined distributed hsqldb
databases.
Logged In: YES
user_id=199381
Oops:
Inclusion in 1.7.1 as an optional item (diffs) with static configuration in
properties and then official inclusion in 1.8. Reason: minor point
releases do not introduce new syntax that introduces .script file
incompatibility -> 1.8, not 1.7.x.
Logged In: NO
Hi Guys,
I haven't monitored this group in a while. Did you guys ever
integrate my contrib? Just wondering. Good luck.
Best,
Hiep Huu Nguyen
Logged In: NO
Hi Guys,
I haven't monitored this group in a while. Did you guys ever
integrate my contrib? Just wondering. Good luck.
Best,
Hiep Huu Nguyen
Logged In: NO
Hi Guys,
I haven't monitored this group in a while. Did you guys ever
integrate my contrib? Just wondering. Good luck.
Best,
Hiep Huu Nguyen
Logged In: NO
Hi Guys,
I haven't monitored this group in a while. Did you guys ever
integrate my contrib? Just wondering. Good luck.
Best,
Hiep Huu Nguyen
Logged In: NO
Hi Guys,
I haven't monitored this group in a while. Did you guys ever
integrate my contrib? Just wondering. Good luck.
Best,
Hiep Huu Nguyen
Logged In: YES
user_id=199381
Hi Hiep. Long time, no hear from.
IMO, your's was a really great contrib that was before its time
as of 1.6.1.
As of 1.7.2, it's certainly far less ahead of its time, but
certain modularizations and standards for them still need
working out and implementing before your contrib is
integrated whole-hog into the default distro. Regardless, it is
certainly still intended to add features identical or similar to
those in your contribution. That is, there is still the goal of
modular extention of the database, including language
extentions. There is still the goal of allowing remote/external
execution to be integrated into the query engine. There is
still the goal of more competely integrating the functionality
of result sets generated by stored procedures. However,
many legacy cleanup subprojects have taken priority over the
last 18 months, and there have, most recently, been other
important subprojects such as the unified database
instance/url managment subsystem, proper cooperative file
locking, logging and persistence subsystem, refinement of in-
memory row and node representation for reduction of memory
consuption, database metadata, true paramentric compiled
statements, database replication, value pooling and
efficient/tailored collection ADTS, including primitive key
maps, etc.
Things are getting closer to the point where all of this is fully
integrated and modular. The current next step is a proper
session execution stack and proper (by the spec)
implementation of routines generating multiple result sets.
After that, implementation of OUT parameters (at least for
scalar return values) for CallableStatement (stored
procedures) and an API/metadata system for stored
procedures that provide result sets and (possibly) have OUT
and IN OUT parameters. When that is in place, then I think it
really makes sense to start working on truly integrating the
functionality you contributed.
So far, this is looking like work for 1.7.3 or later, certainly not
1.7.2 given its current scope and the current work load.
Logged In: YES
user_id=199381
Hiep:
By the way, I'd be very interested in working with you on
refining the code base toward inclusion of the functionality in
your contributions.
Here is what I would eventually like to see:
1.) Work toward SQL standards based embedded language
support
2.) Work toward SQL standards based remote data source
support
3.) SQL standards based session execution stack and a
mature execution stack aware API exposed for stored
procedure writers.
4.) include a persistent, table-based custom database class
loader, allowing installation of jars directly in the database (no
more worries over external class path dependencies for
optional java-based modules...), a la the SQL standard spec
for JARs and java routines in DBMS.
On point 4.), as Fred can attest to, I've already implemented
a fairly comprehensive table-based database class loader. Its
inclusion and related SQL syntax for managing the installation
and removal of jars is simply waiting for present work lists to
be completed and a full discussion and development of an
HSQLDB standard for persistent SYSTEM tables to be worked
out. When that is done, we can also start to look at things
like providing an advanced transactional queue sybsystem,
like found in Oracle's AQ option.
Logged In: YES
user_id=256036
Hi boucherb!
1, 2, sounds good. I'll look at he documentation.
As for 3: do you mean an execution stack for stored
procedures. I'll have to look at SQL standards again, but
certainly sounds doable.
As for 4: it sounds like you are on your way with this. This
is a really intriguing idea. So you distribute your jars as
database objects?
Will get back to you.
Best Regards,
Hiep
Logged In: YES
user_id=199381
Thanks Hiep.
I look forward to further discussions.
I'll do the required reading on 1.) and 2.) as well to get back
up to speed on details, as soon as I sumbit my latest round of
work (ramp up toward multiple result sets), which will be
RealSoonNow(tm).
Re: 3.), yes: It's possible, for instance, for a nested stored
procedure invocation to change the session user and other
session attributes, call other stored procedures that do the
same thing, ad infinitum. There is a spec for how to scope
nested contexts and we should read up on it and implement
it, at least as much as makes sense in the current HSQLDB
context. Also, although there are many potential APIs and/or
conventions we could choose from for allowing stored
procedures to register results for retrieval by the top-level
caller, if we try to stick to the spec where things are not
specified implementation-defined, this would be better I think.
Re: 4.) part of the SQL 200n FCD defines this also in quite a
straight forward manner, although my first effort certainly did
not follow it to the letter. The spec indicates the ability to
load and remove jars and provides SQL syntax and behaviour
definitions. I implemented the ability to load and unload jars,
but my first approach was a simplistic one, where there is a
row for the binary materialization of each jar entry and no
record of which jar it came from (loading a second jar with an
identically named jar entry will overwrite the entry loaded
from the first jar and unloading the first jar will remove the
identically named entry loaded from the second jar, etc).
Since we aim at JDK 1.1, I also did not bother with reading or
following META-INF information. I'm not sure what the SQL
spec has to say about jar META-INF. One of the biggest jobs
was implementing an hsqldb: URL so that code reading
content retrieved via a URL connection would work. That
part is done and works quite well, really, although there is the
concern of remote clients needing access to the classes
stored in the database. That's a hard question for me that
I've been mulling in the back of my mind for quite some time.
The way I see it, the problem is that, more than likely, an app
accessing a database remotely is not loaded by the hosting
JVM using the database class loader in network mode (the
app may be quite large, for instance, so it is unreasonable to
load if from the remote database). Indeed, I never
implemented that part of the url connection framework, only
the local resource part. And without the client app itself
being loaded by the database class loader, it is possible that
significant reflection must be used to deal with class
instances coming back from the remote database and whose
class was also loaded using the network implementations of
the database class loader, perhaps over the same socket as
the jdbc connection. OTOH, its just as easy to restrict
database class loading to just the classes implementing
stored procedures and disallow the use of value objects in the
database whose class is loaded by database custom class
loader used to load the installed jars from lobs in a persistent
system table.
Anyway, once again, looking forward to talking with you,
Campbell
Logged In: YES
user_id=199381
PS
Yes: 4.) is an intriguing idea. Modularity and the ability to
extend the database in a modular fashion is really nice, but
without a persistent, transactional (table-based) database
class loader, all modules are still at the mercy of the external
class path settings, as well as version changes. With the
ability to load and unload jars in the database, modularity
becomes portable and production quality. That is part of
what I was referring to when I said earlier that I thought your
contribution was slightly (well, getting close to 2 years now)
ahead of its time.
Logged In: YES
user_id=199381
Hi.
Another avenue I'd like to persue is the concept of a SQL
PACKAGE database object.
In simplest terms, a Java class to be used as a SQL PACKAGE
database object would:
1.) be a bean (in essense, have a no-args constructor)
2.) implement an interface named classNamePackageBean
that defines which public instance methods are to be
accessible from the SQL context
3.) (currently) ensure that the classNamePackageBean has no
method name overloads, as HSQLDB still cannot properly
(deterministically, from one JVM/class file compiler to another)
resolve which method to use when there are several of the
same name with different signatures)
2.) if efficient, transparent database access is desired (e.g.
not have to get the session's internal connection through the
standard but slow method of using
the "jdbc:default:connection" url), the SQL PACKAGE class
would also have to implement an Interface, perhaps named
org.hsqldb.Connectable, whose sole method is setConnection
(java.sql.Connection). If a package class was determined to
implement such an interface, then the setConnection method
would be called immediately after construction, passing in
the Session's internal connection.
Upon first of any method of the classNamePackageBean
interface of such a class (via Function), an instance of it
would be registered with the calling Session. This would allow
package state to be maintained from one invocation to the
next against the SQL PACKAGE methods of the bean, for the
duration of the Session, while ensuring that the SQL PACKAGE
state is relative only to the owning session.