SQL

[MrFT]

Hello,

I have been looking into Graph Databases (and some other NoSQL databases) recently, and i found a lot of interesting developments going on, including ArangoDB with its Foxx applications (and Javascript API very similar to MongoDB) and Neo4J, seemingly the most popular GraphDB currently.

I had issues with all of these, leading me to the conclusion that they are not mature enough.

Then I stumbled upon OrientDB, and it seems to have very good performance, and a high stability. So I concluded that this was the best option to explore further.

First thing I do when trying out, is try to get some exising data (originating from a relational database) into the system, and trying to add some edges to replace the foreign keys.

While Neo4J wasn't stable and fast enough, I liked the power of Cypher enormously. I could create edges based on the current foreign key column easily (if I didn't have to many nodes, in that case it wouldn't work :) ).

So far, for something so basic, In OrientDB I have only found the CREATE LINK command, but the alternative CREATE EDGE seems way less powerful. How could I create edges for a whole set of nodes based on some restrictions, by using the OrientSQL language? 'Joining' doesn't seem possible.

In Cypher, you can return values from multiple documents together, which feels very natural to me, for example match (a)-[:likes]->(b) return a. b

( you could even do match (a)-[:adores]->(b) with b as adoree, collect(a) as adorers return { "adoree": adoree, "adorers": adorers } )

The only thing I have found in OrientSQL is a way to return different types of rows in one query, but in different "records", as in:

traverse acted_in from ( select from actor where id = 7 )

I would get an actor record followed by some movie records.

This would force me to build the object or document that I am interested in myself afterwards, instead of the query returning straight what I want.

Also, you state calling it SQL, but the first thing anyone (or am I alone?) would do in a query is returning data from different objects next to each other, so you can quickly see what you are interested in from your database, like Peoples names and the companies they work for, for example.

Anyway, I like a language more than a programming API, and I would be tempted to do as much as possible using OrientSQL (or rather Cypher if OrientDB would support it), instead of using the Gremlin API, if only the languga would allow me to do that...

But my question is: would you consider looking at the current language that OrientDB provides again, or do you stand by your original choices? Maybe even provide something similar to Cypher?

[Andrey Lomakin]

Hi Frederik,

So you mean something like 

"create edge select from a to b match by likes" ?

About your second note 

Also, you state calling it SQL, but the first thing anyone (or am I alone?) would do in a query is returning data from different objects next to each other, so you can quickly see what you are interested in from your database, like Peoples names and the companies they work for, for example.

I am not RDBMS SQL expert,could you provide example, what do you mean ?

And about your question.

We are thinking about reconsidering sql engine and will add new functionality to it but will not change current SQL language for something else in short future.

On Thu, Feb 6, 2014 at 2:39 PM, MrFT <frederi...@vsko.be> wrote:

Hello,

I have been looking into Graph Databases (and some other NoSQL databases) recently, and i found a lot of interesting developments going on, including ArangoDB with its Foxx applications (and Javascript API very similar to MongoDB) and Neo4J, seemingly the most popular GraphDB currently.

I had issues with all of these, leading me to the conclusion that they are not mature enough.

Then I stumbled upon OrientDB, and it seems to have very good performance, and a high stability. So I concluded that this was the best option to explore further.

--

Best regards,
Andrey Lomakin.

Orient Technologies
the Company behind OrientDB

[Luca Garulli]

Hi,

traverse go in depth in your graph. For your use case you could transform:

traverse acted_in from ( select from actor where id = 7 )

In a more simple way:

select in( 'Acted' ) from Actor where id = 7

But you should create your database as a graph, not using document db api. So start from the schema:

create class Actor extends V

create class Movie extends V

create class Acted extends E

And once your schema is ready:

create vertex Actor name = 'Tom', id = 7

create vertex Movie name = 'Vanilla Sky'

create edge from (select from Actor where name = 'Tom') to (select from Movie where name = 'Vanilla Sky')

Now this query returns the movies acted by Tom:

select name, in( 'Acted' ) from Actor where id = 7

This returns the actor name and all the movies names in an string array

select name, in( 'Acted' ).name from Actor where id = 7

This is the same but the array is dumped in a string:

select name, in( 'Acted' ).name.asString() from Actor where id = 7

Lvc@

--
 
---
You received this message because you are subscribed to the Google Groups "OrientDB" group.
To unsubscribe from this group and stop receiving emails from it, send an email to orient-databa...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.

[MrFT]

Hello, 

thanks for you response,

I will try to explain some of my questions further below.



Op vrijdag 7 februari 2014 08:35:43 UTC+1 schreef Andrey Lomakin:

Hi Frederik,

So you mean something like 

"create edge select from a to b match by likes" ?

That could be an option, but it is still limited in my opinion.

Let's say you would like (purely theoretical) to link A to B where A.color matches B.frontDoorColor but when A.color = 'darkgreen' it should also match 'green'

I guess in that case, you (I at least :) ) would like to be able to do something like this on all your records at once.

create edge 'similar_color' from A to B where (A.color= B.frontDoorColor) OR (A.color='darkgreen' AND B.frontDoorColor='green')

and this would create all the links in one go, with any rule that one can think of

 

About your second note 

Also, you state calling it SQL, but the first thing anyone (or am I alone?) would do in a query is returning data from different objects next to each other, so you can quickly see what you are interested in from your database, like Peoples names and the companies they work for, for example.

I am not RDBMS SQL expert,could you provide example, what do you mean ?

The next post by Lvc might be a partial answer to my question, but I'll try to clarify. let's just assume that I want to be able to find relations in my data, even when I have not created links yet. Or let's say I want to check my data quality to see if every node of class A has a related node of class B. It's essentially the same frustration as the first question, the inability to do cross products. I know this is partly because of my experience with Relational databases, but in my opinion it still is a very powerful thing.

Select Person.name, Car.color from Person, Car where Car.personid = Person.id

returning 

John, green

Peter, blue

etc.

or the other way around, to find out whether there is missing data (check if we know the 'owner' for every car before creating links as we would like to have in a graph DB), but this only returns data from 1 Class so this isn't a very good example:

Select Car.*, Person.name from Person LEFT JOIN Car ON Car.personid = Person.id

WHERE Person.id is null 

or this could also be written as

Select Car.* from Car WHERE Car.personid NOT IN ( select id from Person ) 

Why would it feel useful to me to have functionality like that? It's just an easy way to ask specific questions to your database. I can imagine situations where you start with some things being a property on a Vertex, but at some point in time your schema might change and you'd want to replace these by a relationship... In that case i would be digging in my data to find out if my assumptions are correct, query a whole lot of stuff without links being present and then update my schema by creating a whole lot of links at the same time, and cleaning up the old properties afterwards. When you created the links, you would also like to check if what you have done is correct, by checking that you get the same results if you query by following the link, than by querying manually.

I'll check the next answer also, and answer over there if I have more questions.

Thanks.

[MrFT]

Hello,

thanks for the asString() tip !

Apart from that

create edge from (select from Actor where name = 'Tom') to (select from Movie where name = 'Vanilla Sky')

still only allows creating one edge at a time.

And what if I would like to have an output like? (being a document database, returning any possible document as the result of a query would be really useful).

ROW 1

{ 

    Actor: {name: "Tom", age: 35 }, 

    Movies: [ 

                { title: "Bad Movie 1", director: "X" }, 

                { title: "Bad Movie 2", subtitle: "The sequel" }

    ]

}

ROW 2

{ 

    Actor: {name: "John", car: "Honda" }, 

    Movies: [ 

                { title: "Better Movie 1", director: "Y" }, 

                { title: "Better Movie 2", subtitle: "The sequel" }

    ]

}

Is that possible?

I am asking all these questions because I like OrientDB very much, it is built upon solid concepts, it's stable and performant, but I just feel very limited by its current query language after having tasted the power of Cypher. And I can see that their language is one of the main reasons that they are being hyped so much currently, because it makes graph databases very easy to use.

Thanks for your replies.

Op vrijdag 7 februari 2014 12:12:18 UTC+1 schreef Lvc@:

[Laurent Quérel]

Hi all,

Do you know if the cypher query language himself (not the implementation) is protected by rights? A tinkerpop/blueprint implementation of the cypher query language seems not very hard to do.

Best

Laurent Querel 

[Luca Garulli]

Hi,

this query:

Select Person.name, Car.color from Person, Car where Car.personid = Person.id

could be written in OrientDB as:

select person.name, color from Car

If you've created a link from Car to Person called "person". As you can see it's much simpler than SQL with joins. And IMHO it's much more readable than Cypher too. If you're using Graph API the query would be:

select out('ofPerson').name, color from Car

Because you should model the relationship as outgoing vertex between Cat and Person. In this case I've used the "ofPerson" label.

This query:

create edge from (select from Actor where name = 'Tom') to (select from Movie where name = 'Vanilla Sky')

Could be used to create N edges in out shot. Example to create all edges in one shot:

create edge from (select from Actor where name = 'Tom') to (select from Movie where name IN ['Vanilla Sky' or 'Mission Impossible'])

About the special output you could export the result in JSON, like:

select this.toJSON() from Actor

Lvc@

[MrFT]

A few more questions below, but also:

In https://github.com/orientechnologies/orientdb/wiki/SQL-Insert i find 

insert into Diver SET name = 'Luca', buddy = (select from Diver where name = 'Marko')

which allows me to insert data by using a sub-select.

Is is possible to insert a whole new set of nodes in one go? (A concrete usecase being: a lot of nodes of class V exist, but instead I would like to create subclasses, like in the GratefulDeadConcerts, let's assume I would like to get rid of the "type" property, create subclasses of V called Song and Artist, and then copy all V where type=Song into new nodes of class Song, because changing the class after node creation is not allowed)

So something like:

insert into Artist (select from V where type = 'artist')
insert into Song (select from V where type = 'song')

Is that possible? And if yes, how?

Also see below for a few more questions.



Op zaterdag 8 februari 2014 19:31:38 UTC+1 schreef Lvc@:

Hi,

this query:

Select Person.name, Car.color from Person, Car where Car.personid = Person.id

could be written in OrientDB as:

select person.name, color from Car

If you've created a link from Car to Person called "person". As you can see it's much simpler than SQL with joins. And IMHO it's much more readable than Cypher too. If you're using Graph API the query would be:

I understand that this works as soon as the link has been created, but my question is: how to do it if the link hasn't been created yet?

Your explanation seems to assume that one always has a perfect model first, and then the data will be added. My experience is that models change after the database already contains a lot of data. It would be nice if the query language is powerful enough to make these changes without actually having to 'write a program'.

select out('ofPerson').name, color from Car

Because you should model the relationship as outgoing vertex between Cat and Person. In this case I've used the "ofPerson" label.

This query:

create edge from (select from Actor where name = 'Tom') to (select from Movie where name = 'Vanilla Sky')

Could be used to create N edges in out shot. Example to create all edges in one shot:

create edge from (select from Actor where name = 'Tom') to (select from Movie where name IN ['Vanilla Sky' or 'Mission Impossible'])

Same thing: this creates all edges for actor Tom in one go, but I fail to see how this command can create edges for ALL actors at once.

 

About the special output you could export the result in JSON, like:

select this.toJSON() from Actor

Nice.