Linked Tables problem
Dario Fassi
I have a Linked table defined to a Postgresql table, like
CREATE LINKED TABLE IF NOT EXISTS TLKT.EMPLOYEE('org.postgresql.Driver', 'jdbc:postgresql://127.0.0.1/hrla', 'postgres', '***','NOMINA.EMPLOYEE');
TRUNCATE TABLE TLKT.EMPLOYEE;
commit;
This fail with
Error: Cannot truncate "TLKT.EMPLOYEE"; SQL statement:
TRUNCATE TABLE TLKT.EMPLOYEE [90106-127]
SQLState: 90106
ErrorCode: 90106
Error occured in:
TRUNCATE TABLE TLKT.EMPLOYEE
But the same Sql truncate executed with a direct jdbc conncection to Postgresql db finish ok.
I can't figure out what happen when truncate sql is executed via Linked table to make the difference, since Truncate syntax in postgresql is compatible with h2 syntax.
Dario
Thomas Mueller
TRUNCATE is only supported for 'regular' tables. For linked tables,
you need to use DELETE FROM ...
I will document that, and I will add a feature request.
Regards,
Thomas
> --
> You received this message because you are subscribed to the Google Groups
> "H2 Database" group.
> To post to this group, send email to h2-da...@googlegroups.com.
> To unsubscribe from this group, send email to
> h2-database...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/h2-database?hl=en.
>
Dario Fassi
I did not have the time to review the relevant code yet, but since you will add a feature request, I'm thinking that instead of giving specific treatment for every possible sql sentence,
it might be more productive to add an option to linked tables creation such as "pass-through" or something like that.
In pass-through mode, sql sentences that don't have a specific treatment for a linked table, are simply forwarded to linking jdbc connection as-is.
Other wish / alternative, can be add a generalization of linked tables as LINKED or FEDERATED CONNECTION , to use others jdbc sources integrated into database context,
for send commands and cross using of result sets in both directions, but not constrained to a particular table or query:
What do you think of these ideas , it's too crazy ?
Do you think are feasible with reasonable effort?
I'm convinced that H2's linked tables are a very useful feature, that every day I found in a new use case and has become an important reason for choosing this database.
This feature effectively extends the power and scope of use of H2 to the tooling domain, but with many context and integration advantages over most ETL toolboxs.
ETL, integration, data provisioning and exchange are some common areas that benefit greatly from this feature.
Today we are using H2 even in many projects which are restricted to use another database as backend because of its speed, ubiquity and its value as a tool.
thanks for your great work.
Dario.
El 24/01/10 13:30, Thomas Mueller escribió:
Hi, TRUNCATE is only supported for 'regular' tables. For linked tables, you need to use DELETE FROM ... I will document that, and I will add a feature request. Regards, Thomas On Fri, Jan 22, 2010 at 7:47 PM, Dario Fassi <dfa...@gmail.com> wrote:Hi,
CREATE LINKED TABLE IF NOT EXISTS TLKT.EMPLOYEE('org.postgresql.Driver', 'jdbc:postgresql://127.0.0.1/hrla', 'postgres', '***','NOMINA.EMPLOYEE'); TRUNCATE TABLE TLKT.EMPLOYEE; commit;
Sergi Vladykin
I'm now investigating linked tables for my use case. And I thought
about such "pass-through" optimization too if query has no
dependencies on H2 db objects other than the linked table itself and
the table has no per-row triggers. May be I'll try to implement it a
little later.
Dario Fassi
Now I'm in a project that make intensive use of linked tables and I can see many things that can be done to make more powerful the actual implementation.
El 29/01/10 16:15, Sergi Vladykin escribió:
I'm now investigating linked tables for my use case. And I thought about such "pass-through" optimization too if query has no dependencies on H2 db objects other than the linked table itself and the table has no per-row triggers.
Now many uses of linked tables fail but many of them don't have with a valid / real cause.
Because lack of implementation of some command or because H2 don't understand some syntax that is targeted to destination database.
In such cases H2 simple need to know if it will get an ResultSet or scalar result or nothing , and just do plain jdbc execute( ) handling.
If H2 instead of failing in advance when don't understand a sentence (in pass-t mode) just allow the other database handle the sentence for and act in regard of remote db exceptions or results, many things would work.
In case some operation don't fill the conditions needed on H2 for handling such situation or results , at the end they will fail in the same way they fail now - so no loss.
Think about minor sql grammar differences, DDL sentences , void stored procedures calls and how many thing can be working if h2 simple forward any sentence and fail upon remote failure.
It may be promiscuous, but very useful.
May be I'll try to implement it a little later.
Let me know if I can help or collaborate with you in this matter.
regards
Dario.
Sergi Vladykin
> If H2 instead of failing in advance when don't understand a sentence (in
> pass-t mode) just allow the other database handle the sentence for and
> act in regard of remote db exceptions or results, many things would work.
> In case some operation don't fill the conditions needed on H2 for
> handling such situation or results , at the end they will fail in the
> same way they fail now - so no loss.
How h2 will decide to which linked db send query if it can't even
parse it? Why it should parse the query at all if this query is
targeted to another db? I think the right solution is to use stored
procedures returning ResultSet. But to do it more convenient I think
it is possible to add to h2 new db object DBLINK with syntax like
CREATE DBLINK dblink_to_oracle (driver, url, user, password);
and function like
QUERY_DBLINK( dblink_to_oracle,
SELECT xxx FROM some_oracle_table
WHERE yyy = ?
CONNECT BY PRIOR a = b
)
which can be used in more complex queries as function table. And the
query inside this function will not be parsed, only "?" should be
found to set parameters and execute it on this dblink. I think you
want something like this. But this will not be a one day work and I'm
not sure if Thomas will approve this feature..
Thomas Mueller
> linked tables creation such as "pass-through
Why don't you connect to the target database directly?
Regards,
Thomas
Dario Fassi
If H2 instead of failing in advance when don't understand a sentence (in pass-t mode) just allow the other database handle the sentence for and act in regard of remote db exceptions or results, many things would work. In case some operation don't fill the conditions needed on H2 for handling such situation or results , at the end they will fail in the same way they fail now - so no loss.How h2 will decide to which linked db send query if it can't even parse it?
Why it should parse the query at all if this query is
targeted to another db? I think the right solution is to use stored
procedures returning ResultSet. But to do it more convenient I think
it is possible to add to h2 new db object DBLINK with syntax like
CREATE DBLINK dblink_to_oracle (driver, url, user, password);
and function like
QUERY_DBLINK( dblink_to_oracle,
SELECT xxx FROM some_oracle_table
WHERE yyy = ?
CONNECT BY PRIOR a = b
)
which can be used in more complex queries as function table. And the
query inside this function will not be parsed, only "?" should be
found to set parameters and execute it on this dblink. I think you
want something like this.
In a previous post I say to use a generalization of the concept : LINKED_TABLE -> LINKED_SCHEMA -> LINKED_QUERY -> LINKED_DB (or federated connection or DBLINK that sound very intuitive).
I know that QUERY_DBLINK() function it's much more easy to implement that another new database object as LINKED_QUERY,
but may be for the future , a persistent db object LINKED_QUERY as it be a remote view over a DBLINK or LINKEDB would be nice and consistent with the current features.
DBLINK and QUERY_DBLIINK() as you suggest inherently achieves maintain its usefulness as a tool for data extraction mixed querying, as in:
CREATE TABLE localtable AS ( SELECT * FROM QUERY_DBLINK(...)) ;
INSERT INTO localtable ( SELECT * FROM QUERY_DBLINK(...) ) ;
SELECT L.*, R.* FROM localtable as L , LINKED_QUERY() as R where R.id = L.id
With this implementation even query data from mixing local and linked tables will be posible, because at h2 parsing they only have to check the syntax of an UDF/SP call.
But this will not be a one day work and I'm not sure if Thomas will approve this feature.
virtually eliminates all restrictions on the use of an external data source within the context of a session of h2.
regards,
Dario
Dario Fassi
> linked tables creation such as "pass-through"
Why don't you connect to the target database directly?
Connect to the other db , code for execute the remote query , iterate over ResultSet and merge to a local table ;
it's all you must to do to make a direct import of a table in another other DBM to a local table.
All this can be done with 2 sentence is H2 (local database) like :
CREATE LINKED TABLE TLKT.EMP('com.microsoft.sqlserver.jdbc.SQLServerDriver', 'jdbc:sqlserver://nn.nn.nn.nn:1433', 'user', 'pass, 'EMP' ) READONLY ;
CREATE TABLE IF NOT EXISTS LOC.EMP AS ( SELECT * FROM TLKT.EMP );
At the end you have created a convenient table to receive data from the external table, and populated with external data.
This is a very useful feature, Not only for the amount of work and code saved,
but much more because this can be dynamically generated or scripted for tables that you don't know in advance.
What if you need to repeat this over every table or view of a database that has 100's or 1000's of tables ?
I think that is sufficient reason to desire a implementation of LINKED_QUERY that don't have the limitations that linked tables has.
regards
Dario.
Sergi Vladykin
I have some data integration tasks too, and I agree that creating
linked table for any simple thing is not very convenient especially
when parametrized queries needed. Direct access to external data
sources would be much better. Also I think linked tables should be
done over dblink to have ability to change parameters only for dblink
but not to all linked tables if something changed. H2 can become mega-
cool data integration solution then:)
Dario Fassi
El 31/01/10 09:34, Sergi Vladykin escribió:
I have some data integration tasks too, and I agree that creating linked table for any simple thing is not very convenient especially when parametrized queries needed.
Not all databases support that setup via connection URL.
Ex. SET ISOLATION LEVEL , COLLATING , CLIENT ENCODING, etc.
Direct access to external data sources would be much better.
DBLINK as a datasource bookmark on H2 that can be activated or de-activated as needed or on access sound very promising.
Also I think linked tables should be done over dblink to have ability to change parameters only for dblink but not to all linked tables if something changed.
H2 can become mega-cool data integration solution then:)
DBLINK could implement a getConnection() method to open the door to use many wonderful H2's tools over external data too ( Csv , RunScript ).
Thomas Mueller
> DBLINK could implement a getConnection() method
This sounds like DriverManager. You can register you own custom JDBC
driver that returns the 'right' connection.
> Not all databases support that setup via connection URL.
> Ex. SET ISOLATION LEVEL , COLLATING , CLIENT ENCODING, etc.
So far nobody requested such features. Could you provide more details
about what you need?
> Direct access to external data sources would be much better.
Do you mean by setting a connection _object_ versus declarative
(database URL and so on like now)?
> change parameters only for dblink
> but not to all linked tables if something changed.
I will add a feature request for "Support CREATE DATABASE LINK".
Regards,
Thomas
Sergi Vladykin
> > Direct access to external data sources would be much better.
>
> Do you mean by setting a connection _object_ versus declarative
> (database URL and so on like now)?
Yes, but not only. Also I mean an ability to query data from
connection
and execute arbitrary commands against it without creating any other
H2 objects (like linked tables). Preferably with support of parameters
-
linked tables are not suited for this.
Thomas Mueller
> Yes, but not only. Also I mean an ability to query data from
> connection
> and execute arbitrary commands against it without creating any other
> H2 objects (like linked tables). Preferably with support of parameters
> -
> linked tables are not suited for this.
What's the difference to using JDBC yourself?
Regards,
Thomas
Dario Fassi
El 03/02/10 18:00, Thomas Mueller escribió:
1) To use data from external sources , "inside" a H2 session and with H2 tools.
2) To have a way, for use that external datasource (connection) without the restrictions that LINKED TABLES has.
3) To use an ResultSet of external data ( generated by LINKED_QUERY function without sql restrictions ) declaratively within a sql query that combines data with external and H2 data
4) To use scripting ( RUN SCRIPT) mixing H2 sql and "native external sql" inside LINKED_QUERY() function as part of an heterogeneous script-able process .
As I can see It's simply and extended form of H2 actual features that can be much more useful.
I'm feeling not able to adequately convey the picture.
Dario
Sergi Vladykin
procedures without touching of H2 code. It is only
question of convenience to have no dependencies on external jars and
to do things in a cleaner way.
Dario Fassi
>> DBLINK could implement a getConnection() method
>>
> This sounds like DriverManager. You can register you own custom JDBC driver that returns the 'right' connection.
>
Of course you can in java , but not in middle of an sql script.
I just thinking in the possibility of using Csv, and other H2 tools over
an external connection.
>> Not all databases support that setup via connection URL.
>> Ex. SET ISOLATION LEVEL , COLLATING , CLIENT ENCODING, etc.
>>
> So far nobody requested such features. Could you provide more details
> about what you need?
>
By example, a concrete real case I have to query data from a legacy
database in ISOLATION LEVEL UNCOMMITED READ ,
to create a H2 table using a query that mix data from that linked table
and other H2's tables.
That database don't accept setup isolation level via jdbc URL, so I can
use linked tables for this case.
Other case, I need/wish define a linked table with a query that use a
sql grammar that H2 don't accept.
>> Direct access to external data sources would be much better.
>>
> Do you mean by setting a connection _object_ versus declarative
> (database URL and so on like now)?
>
>
No I mean to get an ResultSet from an external source in such a way that
H2 don't parse the sql destinated to linked database, just use the
returning ResultSet.
>> change parameters only for dblink
>> but not to all linked tables if something changed.
>>
> I will add a feature request for "Support CREATE DATABASE LINK".
Regards,
Dario
Ryan How
like they are one database, but also have the ability to "pass-through"
to the originating data source?
Ryan
Dario Fassi
We don't use Access, but yes, that's the idea.
In MS world you have some interesting artifacts to support data
exchange, integration and ETL applications like DTSs , but it's very
common that this type of apps use as intermediary and access db and VB.
Because our main target are *nix+java server environments and
heterogeneous db vendor + legacy , H2 fill this role very well as dbm
and as ETL tool.
An LINK DATABASE feature and LINKED_QUERY() functions it's all we need
to remove some uncomfortable limitations of Linked tables.
Dario.
Thomas Mueller
You can also use JDBC inside H2, using customer functions. Example:
create alias query as 'ResultSet query(Connection conn, String sql)
throws SQLException { return conn.createStatement().executeQuery(sql);
}';
call query('select * from dual');
drop alias query;
If needed, this can be extended to access external databases.
Regards,
Thomas
Sergi Vladykin
> You can also use JDBC inside H2, using customer functions.
> If needed, this can be extended to access external databases.
Since this is a common task, the request was to standardize it in H2.
When database link object will be implemented (you promised to add the
feature request), it will be straight forward to add functions for
external data access.
Dario Fassi
UDFs are great and we use it often.
--
-- THIS ONE CAN'T BE USED - I dont know why ?
--
create alias LINKED_SCRIPT as ' void doextscript( String
driverClassName, String url, String username, String passwd, String
scriptFileName, String charsetName, boolean continueOnError) throws
SQLException {
try {
java.sql.Driver aDrv = (java.sql.Driver)
Class.forName(driverClassName).newInstance();
java.sql.DriverManager.registerDriver(aDrv);
} catch (Exception e) {;}
org.h2.tools.RunScript.execute(url, username, passwd, scriptFileName,
charsetName, continueOnError) ;
}';
--
-- THIS ONE WORK FINE and we use it some times , but has the same
limitations that linked tables.
--
create alias LINKED_QUERY as 'ResultSet doextquery( Connection con,
String driverClassName, String url, String username, String passwd,
String query) throws SQLException {
try {
java.sql.Driver aDrv = (java.sql.Driver)
Class.forName(driverClassName).newInstance();
java.sql.DriverManager.registerDriver(aDrv);
} catch (Exception e) {;}
return java.sql.DriverManager.getConnection(url, username,
passwd).createStatement().executeQuery(query);
}';
Now to use it in an SELECT this look like:
select a.* from LINKED_QUERY(
org.postgresql.Driver','jdbc:postgresql://127.0.0.1:5432/mydb','sa','********',
'select * from mySchema.someTable' ) as a;
you must agree that isn't a nice syntax ;-)
and this is very redundant with LINKED TABLE's connections management. I
think their external connections pool must be shared by all this
"LINKED" artifacts.
When we know what need to be executed at design time, then we can write
a compiled java UDFs jar that use resources of h2 and applications and
all goes right.
But , many time we DON'T know in advance what need to be executed as
"CUSTOM SCRIPTS" that the us or final user can write in SQL as
application's customer procedures.
In data integration applications , new data inputs are ofted handled as
custom procedures (scripts) executed from an application that has an
embedded H2 database.
For those cases , this UDFs are tolerable but not comfortable or efficient.
I insist that almost anything is just inside H2: drivers and external
connections handling.
We only need some expressive and clean way to access that extenal /
linked connections for use in a more general or less restrictive fashion.
Regards,
Dario
El 07/02/10 14:04, Thomas Mueller escribió:
Thomas Mueller
> UDFs
I guess you mean user defined functions?
> -- THIS ONE CAN'T BE USED - I dont know why ?
I don't understand, why not? Does it throw an exception?
> select a.* from LINKED_QUERY(
> org.postgresql.Driver','jdbc:postgresql://127.0.0.1:5432/mydb','sa','********',
> 'select * from mySchema.someTable' ) as a;
> you must agree that isn't a nice syntax ;-)
> and this is very redundant with LINKED TABLE's connections management.
You could create a method to map a 'data source' to a connection:
LINKED_DEFINE_DATASOURCE(dataSource, driver, url, user, password);
LINKED_QUERY(dataSource, query);
Or you could create a function that creates another function:
LINK(dataSource, driver, url, user, password);
This would then create the 'real' <dataSource>_QUERY alias.
> many time we DON'T know in advance what need to be executed as
> "CUSTOM SCRIPTS" that the us or final user can write in SQL as
> application's customer procedures.
I don't understand, how is that related to this problem?
> I insist that almost anything is just inside H2: drivers and external
> connections handling.
As I wrote, it doesn't have high priority for me. I think it doesn't
make sense to add functionality into the database engine itself if the
same functionality can "live" outside. Unless many people need it.
Regards,
Thomas
Dario Fassi
El 13/02/10 12:34, Thomas Mueller escribió:
UDFsI guess you mean user defined functions?
-- THIS ONE CAN'T BE USED - I dont know why ?I don't understand, why not? Does it throw an exception?
Error: Syntax error in SQL statement " void doextscript( String driverClassName, String url, String username, String passwd, String scriptFileName, String charsetName, boolean continueOnError)
throws SQLException {
try {
java.sql.Driver aDrv = (java.sql.Driver) Class.forName(driverClassName).newInstance();
java.sql.DriverManager.registerDriver(aDrv);
} catch (Exception e) {;}
org.h2.tools.RunScript.execute(url, username, passwd, scriptFileName, charsetName, continueOnError) ;
}"; SQL statement:
create alias LINKED_SCRIPT as ' void doextscript( String driverClassName, String url, String username, String passwd, String scriptFileName, String charsetName, boolean continueOnError)
throws SQLException {
try {
java.sql.Driver aDrv = (java.sql.Driver) Class.forName(driverClassName).newInstance();
java.sql.DriverManager.registerDriver(aDrv);
} catch (Exception e) {;}
org.h2.tools.RunScript.execute(url, username, passwd, scriptFileName, charsetName, continueOnError) ;
}' [42000-128]
SQLState: 42000
ErrorCode: 42000
Error occured in:
create alias LINKED_SCRIPT as ' void doextscript( String driverClassName, String url, String username, String passwd, String scriptFileName, String charsetName, boolean continueOnError)
throws SQLException {
try {
java.sql.Driver aDrv = (java.sql.Driver) Class.forName(driverClassName).newInstance();
java.sql.DriverManager.registerDriver(aDrv);
} catch (Exception e) {;}
org.h2.tools.RunScript.execute(url, username, passwd, scriptFileName, charsetName, continueOnError) ;
}'
select a.* from LINKED_QUERY( org.postgresql.Driver','jdbc:postgresql://127.0.0.1:5432/mydb','sa','********', 'select * from mySchema.someTable' ) as a; you must agree that isn't a nice syntax ;-)and this is very redundant with LINKED TABLE's connections management.You could create a method to map a 'data source' to a connection: LINKED_DEFINE_DATASOURCE(dataSource, driver, url, user, password); LINKED_QUERY(dataSource, query);
but I can't figure out what place-holder to use for Connection or DataSource
returned for the first UDF call until until can be used as a parameter from the second UDF call.
If the first UDF don't create a persistent database object for their result Connection/DataSource , to use it we must to do:
call LINKED_QUERY( LINKED_DEFINE_DATASOURCE( dataSource, driver, url, user, password) , query);
Or you could create a function that creates another function: LINK(dataSource, driver, url, user, password); This would then create the 'real' <dataSource>_QUERY alias.
I try to use the alias bellow , but DatabaseMetaData.getFuntions() is in JDK 1.6
create alias SHOW_FUNCTIONS as 'java.sql.ResultSet showfunctions(java.sql.Connection con , String schemaPattern, String functionNamePattern) throws SQLException {
java.sql.DatabaseMetaData dbmd = con.getMetaData();
return dbmd.getFunctions(null, schemaPattern, functionNamePattern);
}';
many time we DON'T know in advance what need to be executed as "CUSTOM SCRIPTS" that the us or final user can write in SQL as application's customer procedures.I don't understand, how is that related to this problem?
As I wrote, it doesn't have high priority for me. I think it doesn't
make sense to add functionality into the database engine itself if the same functionality can "live" outside. Unless many people need it.
But I think too, that there are features, like linked tables, that are incomplete and with a very limited usability for their for typical use case.
- just an idea -
It maybe be better remove it from the core of h2 and pass it to an extension module (h2-linked.jar) , as the "contrib" of other databases.
Then only who need this functionality load that jar, and this features can grow without compromise the core.
I know this is not so easy , and that requires to layout some plug-in or extension method in the core ;
but I can't imagine some other way to maintain the core small and solid without restrict the functionality grow-up.
regards,
Dario
Thomas Mueller
> Me neither and what's wrong - this is the error :.
It does work for me (no error). The root cause of the exception should
show the compiler error. For the next release, I will change the error
message so the actual problem (the compiler error) is included.
By the way Class.forName(jdbcDriverClassName) is enough (no need to
instantiate and register the driver).
> What would be the right way to look up the functions created in the db ?
> I try to use the alias bellow , but DatabaseMetaData.getFuntions() is in
> JDK 1.6
DatabaseMetaData uses the INFORMATION_SCHEMA. See the source code of
DatabaseMetaData.getFuntions().
> It maybe be better remove it from the core of h2 and pass it to an extension
> module (h2-linked.jar) , as the "contrib" of other databases.
Yes, that's a very good idea. The question is what is the 'right'
abstraction, and how does the extension module system look like.
Currently I think it's user defined functions, but there should be a
better way to integrate them.
Regards,
Thomas
Dario Fassi
It maybe be better remove it from the core of h2 and pass it to an extension module (h2-linked.jar) , as the "contrib" of other databases.
The question is what is the 'right' abstraction, and how does the extension module system look like. Currently I think it's user defined functions, but there should be a better way to integrate them.
UDFs are a natural way to make lightweight extensions of core functionality and , as in Postgres contrib,
can be simple sets of related UDFs by problem domain or use , as an sql text file with an additional .jar for the code if it's needed.
Maybe with a new command like: LOAD_EXTENSION some_jar_fullpath
that jar contains code and a sql text file inside for automation of ALIASs creation - all contained in the same jar .
Another advantage of this approach can/would be bypass the requirement of adding extension jars to the classpath.
If don't like create a new command , can be used some system property or environment variable to hold the list of extensions jars to be loaded at startup.
This approach organize and empower actual UDFs implementation, but it's equally or more important to define a public set of core internal classes or methods (or api)
to be exposed and documented for UDF usage , as the "legal" contact points with the H2's core.
This set of exposed core components can be a simple form of public interface to create extensions.
I think that we need one more artifact to create extensions to mate with the core with minimal interdependence.
We need a name-space and map to hold and reference objects accesible from both UDFs and the core ( and SQL).
It can be an extension of actual SET @ , to define variables visible at sql level by name and within the UDF through utility class:
In UDFs;
VariableType objectVarValue = new VariableType( someJavaObject , VariableType.SOME_SUPPORTED_VAR_TYPE );
UnfencedVariablesSpace.getReference().put( "varname", objectVarValue );
VariableType objectVarValue = UnfencedVariablesSpace.getReference().get( "varname" );
Object someJavaObject = objectVarValue.getWrappedObject();
And in SQL:
SET @VARNAME = expresion;
call SETVAR( 'varname', expresion );
call GETVAR( 'varname' );
I hope some of this helps, but if not just throw it away and forget it.
regards,
Dario
Sergi Vladykin
> Maybe with a new command like: * LOAD_EXTENSION some_jar_fullpath*
The bad thing in such approach that each time database created you
will need run initialization script like
LOAD_EXTENSION some_ext1.jar;
LOAD_EXTENSION some_ext2.jar;
LOAD_EXTENSION some_ext3.jar;
etc...
In addition better to have system property in which we will list all
the classnames of plugins
which should be instantiated and initialized for each new database.
By the way. I'm thinking about pluggable table implementations. Like
this
CREATE TABLE T1
(ID INT, NAME VARCHAR)
ENGINE "com.aaa.MegaCoolTableFactory"
this class will have factory method which will return
org.h2.table.Table instance.
Thoughts, suggestions?
Thomas Mueller
What about using the Java "service provider mechanism" for extensions?
How this works is explained here:
http://java.sun.com/javase/6/docs/api/java/util/ServiceLoader.html -
this mechanism also work in Java 5, H2 would use
javax.imageio.spi.ServiceRegistry (which sounds a bit weird, but it's
basically the same thing).
To register an H2 extensions, either
B) add the file "META-INF/services/org.h2.Service" to the jar file
with the list of extension classes
B) or register the extensions at runtime using a Java API
Registering a file system implementation is already possible using
org.h2.store.fs.FileSystem.register (some built-in file systems use
this mechanism already). What is needed is extend this mechanism for
functions and other components. There are already some candidates, for
example org.h2.mode.FunctionsMySQL.
Regards,
Thomas
Dario Fassi
Maybe with a new command like: * LOAD_EXTENSION some_jar_fullpath*The bad thing in such approach that each time database created you will need run initialization script like LOAD_EXTENSION some_ext1.jar; LOAD_EXTENSION some_ext2.jar; LOAD_EXTENSION some_ext3.jar; etc...
Let say something like:
CREATE EXTENSION ext_id_name ext_jar_fullpath;
DROP EXTENSION ext_id_name ext_jar_fullpath;
On CREATE EXTENSION the core must to do;
1) Check availability of referenced jar and previous definition of ext_id
2) Copy the referenced jar into a "./plugins" subdirectory of database directory to assert the jar availability and to be included on database backups.
3) Load referenced jar that is not in classpath ( some way like: http://www.javaworld.com/javaworld/javatips/jw-javatip70.html)
4) Process "create-plugin.sql" included in the jar for CREATE ALIAS automation (can be used some jar manifest attributes to support this)
5) Update system table in INFORMATION_SCHEMA with new extension and ALIASes
On database start , all plugins on ./dbdir/plugins/*.jar , are actived and dynamically loaded as in above step (3).
On drop extension: remove the jar on /plugins , drop alias , delete definition on INFORMATION_SCHEMA.
In addition better to have system property in which we will list all the classnames of plugins which should be instantiated and initialized for each new database.
If you restore a backup on other host , you restore ALIASES that can't work.
By the way. I'm thinking about pluggable table implementations. Like this CREATE TABLE T1 (ID INT, NAME VARCHAR) ENGINE "com.aaa.MegaCoolTableFactory" this class will have factory method which will return org.h2.table.Table instance. Thoughts, suggestions
For now we should concentrate on outlining a proposal to for basic building blocks of an ordered implementation of extensions / plugins in such a way that has minimal impact and dependencies with H2 core.
And as Thomas stated , UDF's are the preferred way for extensions.
Regards,
Dario
Dario Fassi
> What about using the Java "service provider mechanism" for extensions?
> How this works is explained here:
> http://java.sun.com/javase/6/docs/api/java/util/ServiceLoader.html -
> this mechanism also work in Java 5, H2 would use
> javax.imageio.spi.ServiceRegistry (which sounds a bit weird, but it's
> basically the same thing).
>
This seems an standard and very clean way to handle alternative
providers for an actual or expected H2 service , but I can see how this
help for extensions.
In this way H2 extensibility must to be defined in terms of "what's
can to be provided as an extension ?" and I don't know if that question
have a concrete answer.
For and alternate table engine implementation (as Sergi propose) or full
text search engine, or new index types , an spi definition can fit
perfectly.
But for an extension stated as a pack of related UDFs and support code
, in don't believe that an SPI approach can be used.
I think that we are talking of two different things:
1) Plug-ins: SPI candidate with tight relation to core central functions.
2) Extensions: Addons of UDF sets with minimal core compromise that
must to be part of database itself.
Plugins can be database configuration components , on the other hand
Extensions must to be database's objects.
If someone create a database and define a view that use and UDF ; then
create a backup and send to another place;
all need components to maintain that view operative must to be in the
backup as any other database object.
For extension suppose there are ways to simply modify the classpath
dynamically to make jar contained classes available
(http://www.coderanch.com/t/402870/Beginning-Java/java/Adding-jar-classpath-dynamically
);
maybe more tricky than standard or elegant but much more direct.
> To register an H2 extensions, either
>
> B) add the file "META-INF/services/org.h2.Service" to the jar file
> with the list of extension classes
> B) or register the extensions at runtime using a Java API
>
> Registering a file system implementation is already possible using
> org.h2.store.fs.FileSystem.register (some built-in file systems use
> this mechanism already). What is needed is extend this mechanism for
> functions and other components. There are already some candidates, for
> example org.h2.mode.FunctionsMySQL.
>
Yes , database compatibility modes can be implemented as SPI , one for
MySql , PgSql, etc.
and as SPI class loading is lazy even if all db mode plugins are
installed, only the used one will be loaded.
This can reduce core jar size and runtime needed resources.
regards,
Dario
Thomas Mueller
> But for an extension stated as a pack of related UDFs and support code
> , in don't believe that an SPI approach can be used.
Why not?
> I think that we are talking of two different things:
>
> 1) Plug-ins: SPI candidate with tight relation to core central functions.
> 2) Extensions: Addons of UDF sets with minimal core compromise that
> must to be part of database itself.
>
> Plugins can be database configuration components , on the other hand
> Extensions must to be database's objects.
I don't see a clear distinction here. Fulltext indexes are also
'database objects' in a way, yet they are implemented as User Defined
Functions (UDFs).
> If someone create a database and define a view that use and UDF ; then
> create a backup and send to another place;
> all need components to maintain that view operative must to be in the
> backup as any other database object.
Yes. For CREATE ALIAS ... AS ..., the source code of the function is
actually stored in the database. Currently there is no way to create a
function using the bytecode (store the bytecode in the database). This
feature could be added. I will add a feature request for:
"User defined functions: allow to store the bytecode (of just the
class, or the jar file of the extension) in the database."
Regards,
Thomas
Dario Fassi
you are who can evaluate this with criterion from your complete and
thorough knowledge of the code.
My opinions are from point of view of a user who has enjoyed and
suffered different extension implementations in many different databases
over the years;
but I take many assumptions about what approach can be harder to implement.
El 23/02/10 16:35, Thomas Mueller escribió:
>> But for an extension stated as a pack of related UDFs and support code
>> , in don't believe that an SPI approach can be used.
>>
> Why not?
>
SPI , as I understand, it's better suited to define "alternative
implementations of some concrete function or characteristic" that is
by nature or convenience pluggable or replaceable,
and limited by interfaces that define what can be replaced and the
contract of how this need to be done to mate with the core.
Table implementations, indexes implementations, transport protocols,
etc. are good candidates for SPI with specialized versions for distinct
uses .
I cant figure out why such a contract or interface will be necessary to
only add new functions.
In the case of an extension as a set of related UDFs only a few
communication and installation facilities are need:
1) Packaging & Installing : Automation of alias creation and
persistence of definitions as database objects.
2) Extend the VARIABLES possibilities as more generic object-holder
accessible from SQL and UDF as a bus between they for collaboration .
THIS IS THE CENTRAL POINT and main limitation of what can be done
right now.
>> I think that we are talking of two different things:
>>
>> 1) Plug-ins: SPI candidate with tight relation to core central functions.
>> 2) Extensions: Addons of UDF sets with minimal core compromise that
>> must to be part of database itself.
>>
>> Plugins can be database configuration components , on the other hand
>> Extensions must to be database's objects.
>>
> I don't see a clear distinction here. Fulltext indexes are also
> 'database objects' in a way, yet they are implemented as User Defined
> Functions (UDFs).
>
All of this can be implemented as UDFs or with what ever be the best
implementation option ; but since they are different in purpose and
scope it's better to design with that in mind.
Main distinction it's that extensions are mostly to ADD collateral
functions and don't has the hassle to "REPLACE" core functionality ,
even critical functions that need to be contained and directed in some
way by interfaces that define the insertion points for plugins (what
core functions are 3er party provided).
>> If someone create a database and define a view that use and UDF ; then
>> create a backup and send to another place;
>> all need components to maintain that view operative must to be in the
>> backup as any other database object.
>>
> Yes. For CREATE ALIAS ... AS ..., the source code of the function is
> actually stored in the database. Currently there is no way to create a
> function using the bytecode (store the bytecode in the database). This
> feature could be added. I will add a feature request for:
>
> "User defined functions: allow to store the bytecode (of just the
> class, or the jar file of the extension) in the database."
>
This is a great starting point for consistency and portability ; but at
the same time will be necessary some definition / installation command
or tool.
And if we add a simple jar layout to automate alias creation in the same
process , then we can namely call that "an extension" ! :-)
What do you think about simply to use a database subdirectory for
deploy the extensions jars ; and advance over installation first.
Backup only need to include that directory ( until store bytecode can be
done ) to get portability in it's simplest form.
It's a shorter path to cover all the way: packaging, deploy and
activation of an extension , that can be perfected later.
regards
Dario
Sergi Vladykin
Lets make some conclusions and start implementing.
I think plugins (with SPI) and java procedures which are stored as
bytecode or as jars in database are independent features.
And then automated loading of set of UDFs from jars into database can
be also represented as service which can be loaded via SPI mechanism.
So how I see this for the moment the needed services are:
1) Database initialization service. It will do something on each
database creation (run initialization scripts and so on).
2) Functions serivice. It will contain set of functions. But there
will be even no need to execute initialization scripts - they
will work like builtin H2 functions.
3) Tables service. To allow user to create his own table
implementation best suited for his needs.
The last item is needed personally for me :)
Dario Fassi
you have spoken loud and clear. Good resume.
But you don't think that UDFs without an shared variables space between
SQL and UDF space are still very limited ?
As I can see UDFs are limited to behave as static functions ( Invariant
functions in IBM terminology ).
How to make this udfs collaborate with each others or leave some effect
active for repetitive invocations.
More even, that shared variables space can be used to simulate cursor
implementation and useful for simplify column functions.
regards,
Dario.
Thomas Mueller
This is all very abstract. New features should be use case driver, and
not "abstract idea driven".
What about providing some example (client) source code, including some
documentation what it does? That makes it _much_ easier to decide.
Regards,
Thomas
Thomas Mueller
if you want.
Regards,
Thomas
Dario Fassi
> This is all very abstract. New features should be use case driver, and
> not "abstract idea driven".
>
Sergi is suggesting the same thing.
He only resume all that has been said about extensions to end this
theoretical thread and
go from abstract to start with some prototype or proof of concept
implementation.
> What about providing some example (client) source code, including some
> documentation what it does? That makes it _much_ easier to decide
the best ideas and your opinions and guidance before start anything.
We express common interests with the intention of collaborate, but
before this mail we don't even know if you wish or will decide to drop
or drive this initiative personally.
Anyway your response is clear about your expectations.
Regards
Dario
Sergi Vladykin
> But you don't think that UDFs without an shared variables space between
> SQL and UDF space are still very limited ?
> As I can see UDFs are limited to behave as static functions ( Invariant
> functions in IBM terminology ).
This all is not about extensions but about H2 concept of UDFs so
should be discussed separately.
Sergi Vladykin
About use cases
1) Database initialization service. It will do something on each
database creation (run initialization scripts and so on).
This is more powerful and sometimes more convenient replacement
for initialization parameter of url ;INIT=...
Potentially this can be used also for automated loading jars into
new db (if this will be implemented).
2) Functions serivice. It will contain set of functions. But there
will be even no need to execute initialization scripts - they
will work like builtin H2 functions.
This will allow to extend H2 set of builtin functions in easy way -
simply put jar with UDFs to classpath. We are talked a lot of
time about should we include some functions to H2 or not.
So any functions that are usually not needed (for example
external data access and so on) should be placed in separate
plugins. On H2 site this should be also separate downloads.
3) Tables service. To allow user to create his own table
implementation best suited for his needs.
This is needed by me for data transfer from one application to
my application via odbc with maximum performance. Also
I think someone can create table implementations that
are faster for some use cases than standard.