OrientDB JDBC driver

[Luca Garulli]

What the hell? A JDBC driver for a NoSQL DBMS like OrientDB?

Yes, you read right. A JDBC driver for OrientDB means several cool things:

• allows to use tons of tools for Reporting, Data Mining, etc. like Jasper Reports
• make ETL easier by using classic tools that supports JDBC
• integrate at no cost in Spring using the JDBC Template
• replace your RDBMS and let to old applications to get working at low cost
• I don't know yet :-)

Roberto Franchini is working to the JDBC driver for OrientDB since a couple of months. Today it's quite forward on the development phase since already supports classic CRUD operations like SELECT, INSERT, UPDATE and DELETE.

This is the URL: https://github.com/robfrank/orientdb-jdbc. Feel free to fork it and contribute to the project!

What is missing, apart more tests, is the management of the schema.

Many thanks Roberto! To discuss about it look at: http://code.google.com/p/orient/issues/detail?id=441

Lvc@

[Salvatore Piccione]

Cool! Thank you very much guys!!!

By the way, I have just downloaded it and in order to install it using Maven I added the following lines to pom.xml:

<repositories>
    <repository>
      <id>orientechnologies-repository</id>
      <name>Orient Technologies Maven2 Repository</name>
      <url>http://www.orientechnologies.com/listing/m2</url>
      <snapshots>
        <enabled>true</enabled>
        <updatePolicy>always</updatePolicy>
      </snapshots>
    </repository>
</repositories>

otherwise the Maven installer won't be able to resolve the orientdb parent relation and dependency. I've pulled this change on GitHub.

Cheers,

Salvatore

2011/10/10 Luca Garulli <l.ga...@gmail.com>

[Roberto Franchini]

On Mon, Oct 10, 2011 at 12:29 PM, Salvatore Piccione
<salvo...@gmail.com> wrote:
> Cool! Thank you very much guys!!!
>
> By the way, I have just downloaded it and in order to install it using Maven
> I added the following lines to pom.xml:
>
> <repositories>
>     <repository>
>       <id>orientechnologies-repository</id>
>       <name>Orient Technologies Maven2 Repository</name>
>       <url>http://www.orientechnologies.com/listing/m2</url>
>       <snapshots>
>         <enabled>true</enabled>
>         <updatePolicy>always</updatePolicy>
>       </snapshots>
>     </repository>
> </repositories>
>
> otherwise the Maven installer won't be able to resolve the orientdb parent
> relation and dependency. I've pulled this change on GitHub.
>
> Cheers,
>

Thank you very much for the pull.
It's merged, do a git pull to be updated.

Let me know what works and what's broken.

Cheers.
FRANK
--
Roberto Franchini
L'impossibile è inevitabile.
http://www.celi.it
http://www.blogmeter.it
Tel +39.011.562.71.15
jabber:ro.fra...@gmail.com skype:ro.franchini

[Salvatore Piccione]

Hello,

I'm adding some code to the stubs of OrientJdbcMetadata and OrientJdbcResultSet (I think they go in the direction of solving the schema management issues) and I've also defined some other tests which performs queries on a Tinkerpop Graph Database: I'll pull such changes as soon as they are finalised.
In the mean time I would like to know why in the method com.orientechnologies.orient.jdbc.OrientJdbcStatement.executeQuery(final String sql) don't we use database.query(OSQLSynchQuery<oDocument>(querySTR)) instead of database.command(query).execute()? In this way we could avoid the warning of unchecked cast from Object to List<ODocument>. I don't know if this could be applied also to
com.orientechnologies.orient.jdbc.OrientJdbcStatement.execute(final String sql) and com.orientechnologies.orient.jdbc.OrientJdbcStatement.executeCommand().

Cheers,

Salvatore

2011/10/10 Roberto Franchini <fran...@celi.it>

[Luca Garulli]

Hi,

nice! Schema part is missing yet and I'm sure help is welcomed to Roberto. About the query() method probably it's the best choice.

Lvc@

[Roberto Franchini]

On Tue, Oct 11, 2011 at 10:33 AM, Salvatore Piccione
<salvo...@gmail.com> wrote:
> Hello,
>
> I'm adding some code to the stubs of OrientJdbcMetadata and
> OrientJdbcResultSet (I think they go in the direction of solving the schema
> management issues) and I've also defined some other tests which performs
> queries on a Tinkerpop Graph Database: I'll pull such changes as soon as
> they are finalised.
> In the mean time I would like to know why in the method
> com.orientechnologies.orient.jdbc.OrientJdbcStatement.executeQuery(final
> String sql) don't we use database.query(OSQLSynchQuery<oDocument>(querySTR))
> instead of database.command(query).execute()? In this way we could avoid the
> warning of unchecked cast from Object to List<ODocument>. I don't know if
> this could be applied also to
> com.orientechnologies.orient.jdbc.OrientJdbcStatement.execute(final String
> sql) and
> com.orientechnologies.orient.jdbc.OrientJdbcStatement.executeCommand().

Code is still in development and there are some duplications.

You are free to improve the code.

[Salvatore Piccione]

I'm trying to use the JDBC driver in order to execute SQL query on a Tinkerpop Graph database. I defined the following test classes:

1. a utility class that creates the graph database to be used in the test (https://gist.github.com/1278398);
2. an abstract class that should be extended by the actual test classes (https://gist.github.com/1278405);
3. a class testing a simple query (https://gist.github.com/1278414).

I've noticed that the fields in and out are of type com.orientechnologies.orient.core.db.record.ORecordLazySet instead of being instances of com.orientechnologies.orient.core.db.graph.OGraphEdge. I guess this happens because those fields are sets of links to records but is there a way to return the underlying record? Should I execute a new query using the returned IDs (how can I detect the name of class to be used in the FROM clause of the query?) or is there a way to make the ResultSet to return the pointed record?

TIA,

Salvatore

2011/10/11 Roberto Franchini <fran...@celi.it>

[Luca Garulli]

Hi Salvatore,

Vertex.in and Vertex.out are ORecordLazySet (a java.util.Set implementation) that contains the records of type Edge. Edges have in and out as references to Vertex instances.

To return the record contained inside lazy collections, just browse them.

Lvc@

[aemadrid]

Is the jdbc driver workingfor anybody? Or still being worked on actively? It would be nice to use it for me so I can use ActiveRecord from JRuby as usual. I tired setting up a jdbc/AR connector but getting a bunch of errors of which I have no clue what is going on:

arjdbc.jdbc.RubyJdbcConnection.unmarshalResult(RubyJdbcConnection.java:1151)

arjdbc.jdbc.RubyJdbcConnection.set_native_database_types(RubyJdbcConnection.java:518)

arjdbc.jdbc.RubyJdbcConnection$i$0$0$set_native_database_types.call(RubyJdbcConnection$i$0$0$set_native_database_types.gen:65535)

Just trying to find out what is up with this driver...

[Roberto Franchini]

i was very busy but the driver is still alive. My plans are to work hard on the driver after xmas. can you post more details about your errpr?

[Roberto Franchini]

Can you add more details? Orient version, type of storage etc..
The driver is not complete: I wrote some tests, but a production
environment is a different thing.
Fill a bug on https://github.com/robfrank/orientdb-jdbc
THX,
FRANK

[Adrian Madrid]

I will get it up there. On the latest (rc7 at the time) the first problem was some metadata test was not passing so I removed it to get the jar. I'll try again with rc8 and see how far I get. Part of my problem is getting from JRuby the real Java error.

Adrian Madrid
aema...@gmail.com

[Luca Garulli]

Hi Adrian,

AFAIK metadata part is not complete, all the rest (SQL select+cursors/insert/update/delete) works quite well. Roberto is it right?

Lvc@

[Roberto Franchini]

On Sun, Dec 18, 2011 at 12:48 PM, Luca Garulli <l.ga...@gmail.com> wrote:
> Hi Adrian,
> AFAIK metadata part is not complete, all the rest (SQL
> select+cursors/insert/update/delete) works quite well. Roberto is it right?
>

Select , update and insert are working. The driver isn't used in any
production deployment yet.
Keep in mind that the SQL language to be used is the Orientdb-SQL.
This means that the driver couldn't work behind a framework that
generates SQL for RDBMS.
At the moment the driver is bound to rc7, but next week I will update
to rc8-SNAP.
REgards,

[Adrian Madrid]

You mean that special features (maps/hashes) wouldn't work in an ORM or that the rids would be a problem too?

Adrian Madrid
aema...@gmail.com

[Roberto Franchini]

On Mon, Dec 19, 2011 at 5:00 PM, Adrian Madrid <aema...@gmail.com> wrote:
> You mean that special features (maps/hashes) wouldn't work in an ORM or that
> the rids would be a problem too?

I haven't tested maps.
What I mean is SQL generation.

The driver is just a "proxy" to orient. So if you pass to the driver a
query with a SQL92 JOIN syntax, it will fail.

IMHO the scope of the driver is to give access to orient to legacy
applications.
If you are developing a green-field project, or you are evolving an
old one, and you can, use plain access to Orient, DON'T limit yoursef
to JDBC.
JDBC, as specificaton, is bounded to RDMBS world.
Listen to me: use plain orient, if you can!!!!
My 2€cents.

[Adrian Madrid]

I know. I like using plain OrientDB but moving from a large codebase becomes too much work. If I could make my code work just by changing the connection string I'd be one happy camper. I developed a JRuby gem to use plain OrientDB but moving into relationships and all that got me stumped. I'm working on a REST gem too so I can use OrientDB from plain old Ruby too.

Adrian Madrid
aema...@gmail.com

[Roberto Franchini]

On Mon, Dec 19, 2011 at 6:13 PM, Adrian Madrid <aema...@gmail.com> wrote:
> I know. I like using plain OrientDB but moving from a large codebase becomes
> too much work. If I could make my code work just by changing the connection
> string I'd be one happy camper. I developed a JRuby gem to use plain
> OrientDB but moving into relationships and all that got me stumped. I'm
> working on a REST gem too so I can use OrientDB from plain old Ruby too.
>

Ok, I will work hard next week on the driver.
Pull the last changes I made: rc7 and some fix to tests.
Feel free to push patches or open tickets on github.

[Luca Garulli]

Hi,

I think SQL JOIN could be implemented at OrientDB engine level without too much efforts. At the beginning it will be not so efficient but just to get it working.

Can anyone open a new issue for it?

Lvc@

[Adrian Madrid]

http://code.google.com/p/orient/issues/detail?id=653&thanks=653&ts=1324318214

Done.

Adrian Madrid
aema...@gmail.com

On Mon, Dec 19, 2011 at 10:50, Luca Garulli <l.ga...@gmail.com> wrote:

Lvc@

[Adrian Madrid]

Update: Luca accepted the issue: 

http://code.google.com/p/orient/issues/detail?id=653

Adrian Madrid
aema...@gmail.com

[Salvatore Piccione]

Hello,

these are my 2 cents about my view on the possible usage of an OrientDB JDBC-enabled driver.
I don't think that the JDBC driver will be used only for legacy application in order to have a smooth shift from a RDMS to an Object/Document/Graph database. IMHO, having a JDBC driver (which provides either java.sql.* and javax.sql.* implementations) makes it possible to use the robust and rich Spring data access library. I know that Spring is pushing for a Data Graph library which aims to include the existing graph database implementations but, for the moment, I think that they are far from the robustness of the traditional JDBC support.
I'm working on the JDBC driver too and I hope to be able to pull my changes before Xmas holidays. What I would like to do is to provide implementations of java.sql.Connection and javax.sql.DataSource tailored to each flavour of OrientDB:

• Object Database
• Native Graph Database
• Tinkerpop Graph Database
• Document Database (this is the ground of each implementation)

so that users can still have a direct access to their preferred OrientDB flavour once they executed a SQL query and it is more convenient (i.e. more efficient) to use directly OrientDB APIs.

Cheers,

Salvatore

2011/12/19 Roberto Franchini <fran...@celi.it>

[Luca Garulli]

Hi Salvatore,

have you started from Roberto's project or from scratch?

Lvc@

[Salvatore Piccione]

from Roberto's project on github.com

2011/12/20 Luca Garulli <l.ga...@gmail.com>

[Luca Garulli]

Nice!

In the background there is a task to synchronize an OrientDB instance with any RDBMS. This covers other use cases but mainly a company could inject OrientDB as primary dbms and leave the RDBMS as updated replica to get all the apps compatibles.

In few word OrientDB could be used as a persistent cache.

Lvc@

[Roberto Franchini]

On Tue, Dec 20, 2011 at 1:06 PM, Salvatore Piccione
<salvo...@gmail.com> wrote:
> from Roberto's project on github.com
>

send the pull request!!!
We can work togheter. And finish it for the 1.0!!

[Salvatore Piccione]

Hello,

I've sent a pull request with some of the work I made: https://github.com/robfrank/orientdb-jdbc/pull/2

HTH,

Salvatore

2011/12/21 Roberto Franchini <fran...@celi.it>

[Roberto Franchini]

On Jan 3, 2012 3:44 PM, "Salvatore Piccione" <salvo...@gmail.com> wrote:
>
> Hello,
>
> I've sent a pull request with some of the work I made: https://github.com/robfrank/orientdb-jdbc/pull/2
>

thx.  i will merge it later

[Roberto Franchini]

Merged.
Now I'm fixing some bugs in tests.
I hope to push working code this afternoon, or at least tonight.

[Roberto Franchini]

On Wed, Jan 4, 2012 at 11:03 AM, Roberto Franchini <fran...@celi.it> wrote:
> On Tue, Jan 3, 2012 at 6:59 PM, Roberto Franchini <fran...@celi.it> wrote:
>>
>> On Jan 3, 2012 3:44 PM, "Salvatore Piccione" <salvo...@gmail.com> wrote:
>>>

[cut]

>
> Merged.
> Now I'm fixing some bugs in tests.
> I hope to push working code this afternoon, or at least tonight.
> FRANK
>

I've fixed some bugs in tests and now are all green.

Tests are all working in memory database.

I will add tests for blobs on the Document side (Salvatore adds Blobs
and tests for the graph api).

I will add some examples, maybe in a separate module.

[Luca Garulli]

Great!

Have you tested it with a generic database tool like DbVisualizer?

Lvc@

[Roberto Franchini]

On Thu, Jan 5, 2012 at 1:01 AM, Luca Garulli <l.ga...@gmail.com> wrote:
> Great!
> Have you tested it with a generic database tool like DbVisualizer?
>

not yet.
Today I will work on db metadata to be able to test with tools like
DbVisualizer.