You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@calcite.apache.org by Marc Le Bihan <ml...@gmail.com> on 2015/11/25 07:52:25 UTC

Integrating a DBase III JDBC driver we are currently creating with Calcite

Hello,

    I work on the Apache SIS (Spatial Information System) project. Many geographical files in the public domain are Shapefiles that contains points, polygons or lines, and their underlying datafiles are old, deprecated DBase III files (.dbf extension), that are quite no more redeable since ODBC – JDBC bridge drivers have disappeared, and are no more handled by Java.

    We had to create a DBase III JDBC driver (a real jump in history !) and you can see the current source code if you download the current snapshot version of Apache SIS here : http://sis.apache.org/downloads.html#maven-snapshot
    Our DBase III driver have most of the code needed to read the tables (Driver, Connection, DatabaseMetadata, Metadata, Statement, ResulSet, etc.), but now parsing SQL statements are our greatest problem : I have managed to create a crude parser in response of requests coming from the Statement interface but nothing strong. And I don’t want to do any attempt with AntLR to do that because AntLR is tricky for me. I would like to know how much Calcite can substitute to what we are manually doing in term of SQL parsing in a Statement execution.

Currently we have these packages, and we would like to replace one of them in favor of Calcite :

Package : org.apache.sis.internal.shapefile.jdbc
AbstractDbase3ByteReader.java
AbstractJDBC.java
CommonByteReader.java
Dbase3ByteReader.java
DBase3FieldDescriptor.java
DBaseDataType.java
DBFDriver.java
MappedByteReader.java
SQLConnectionClosedException.java
SQLDbaseFileNotFoundException.java
SQLInvalidDbaseFileFormatException.java

Package : org.apache.sis.internal.shapefile.jdbc.connection
AbstractConnection.java
DBFConnection.java
SQLClosingIOFailureException.java
org.apache.sis.internal.shapefile.jdbc.metadata
AbstractDatabaseMetaData.java
DBFDatabaseMetaData.java
DBFResultSetMataData.java

Package : org.apache.sis.internal.shapefile.jdbc.resultset
AbstractResultSet.java
BuiltInMemoryResultSet.java
DBFBuiltInMemoryResultSetForCatalogNamesListing.java
DBFBuiltInMemoryResultSetForColumnsListing.java
DBFBuiltInMemoryResultSetForSchemaListing.java
DBFBuiltInMemoryResultSetForTablesListing.java
DBFBuiltInMemoryResultSetForTablesTypesListing.java
DBFRecordBasedResultSet.java
DBFResultSet.java
SQLIllegalColumnIndexException.java
SQLNoResultException.java
SQLNoSuchFieldException.java
SQLNotDateException.java
SQLNotNumericException.java

The package I want to replace with Calcite features : org.apache.sis.internal.shapefile.jdbc.sql
ClauseResolver.java
ConditionalClauseResolver.java
CrudeSQLParser.java
SQLIllegalParameterException.java
SQLInvalidStatementException.java
SQLUnsupportedParsingFeatureException.java

Package : org.apache.sis.internal.shapefile.jdbc.statement
AbstractStatement.java
DBFStatement.java

    Do you think that Apache Calcite may fit our needs ? And how ?

Regards,

Marc Le Bihan

Re: Integrating a DBase III JDBC driver we are currently creating with Calcite

Posted by Julian Hyde <jh...@apache.org>.
Since thanksgiving is coming up, I’ll give the short answer: Yes, Avatica/Calcite can do this.

The general advice is as follows. If you already have a SQL parser and execution engine, you could use Avatica to convert it into a solid, JDBC provider supporting both local and remote clients. If you do not have a SQL parser, use Calcite. Calcite can support the gamut from where you just have simple relations (such as CSV files) and no query-processing operators to where you have a rich set of operators. We recommend that you start off with just relations and add more rules to allow pushing down of filters, projects and other operators to your engine.

I’ll give you more specific advice in a day or two. I have downloaded and built your source code and will read through it with the specifics of your proposal in mind.

I hope it can work out — it’s always rewarding to work with other Apache projects.

Julian
 

> On Nov 24, 2015, at 10:52 PM, Marc Le Bihan <ml...@gmail.com> wrote:
> 
> Hello,
> 
>    I work on the Apache SIS (Spatial Information System) project. Many geographical files in the public domain are Shapefiles that contains points, polygons or lines, and their underlying datafiles are old, deprecated DBase III files (.dbf extension), that are quite no more redeable since ODBC – JDBC bridge drivers have disappeared, and are no more handled by Java.
> 
>    We had to create a DBase III JDBC driver (a real jump in history !) and you can see the current source code if you download the current snapshot version of Apache SIS here : http://sis.apache.org/downloads.html#maven-snapshot
>    Our DBase III driver have most of the code needed to read the tables (Driver, Connection, DatabaseMetadata, Metadata, Statement, ResulSet, etc.), but now parsing SQL statements are our greatest problem : I have managed to create a crude parser in response of requests coming from the Statement interface but nothing strong. And I don’t want to do any attempt with AntLR to do that because AntLR is tricky for me. I would like to know how much Calcite can substitute to what we are manually doing in term of SQL parsing in a Statement execution.
> 
> Currently we have these packages, and we would like to replace one of them in favor of Calcite :
> 
> Package : org.apache.sis.internal.shapefile.jdbc
> AbstractDbase3ByteReader.java
> AbstractJDBC.java
> CommonByteReader.java
> Dbase3ByteReader.java
> DBase3FieldDescriptor.java
> DBaseDataType.java
> DBFDriver.java
> MappedByteReader.java
> SQLConnectionClosedException.java
> SQLDbaseFileNotFoundException.java
> SQLInvalidDbaseFileFormatException.java
> 
> Package : org.apache.sis.internal.shapefile.jdbc.connection
> AbstractConnection.java
> DBFConnection.java
> SQLClosingIOFailureException.java
> org.apache.sis.internal.shapefile.jdbc.metadata
> AbstractDatabaseMetaData.java
> DBFDatabaseMetaData.java
> DBFResultSetMataData.java
> 
> Package : org.apache.sis.internal.shapefile.jdbc.resultset
> AbstractResultSet.java
> BuiltInMemoryResultSet.java
> DBFBuiltInMemoryResultSetForCatalogNamesListing.java
> DBFBuiltInMemoryResultSetForColumnsListing.java
> DBFBuiltInMemoryResultSetForSchemaListing.java
> DBFBuiltInMemoryResultSetForTablesListing.java
> DBFBuiltInMemoryResultSetForTablesTypesListing.java
> DBFRecordBasedResultSet.java
> DBFResultSet.java
> SQLIllegalColumnIndexException.java
> SQLNoResultException.java
> SQLNoSuchFieldException.java
> SQLNotDateException.java
> SQLNotNumericException.java
> 
> The package I want to replace with Calcite features : org.apache.sis.internal.shapefile.jdbc.sql
> ClauseResolver.java
> ConditionalClauseResolver.java
> CrudeSQLParser.java
> SQLIllegalParameterException.java
> SQLInvalidStatementException.java
> SQLUnsupportedParsingFeatureException.java
> 
> Package : org.apache.sis.internal.shapefile.jdbc.statement
> AbstractStatement.java
> DBFStatement.java
> 
>    Do you think that Apache Calcite may fit our needs ? And how ?
> 
> Regards,
> 
> Marc Le Bihan