OrientDB graph search support

[agershun]

The differences between ordinary SQL and OrientDB SQL are in http://orientdb.com/docs/last/SQL.

[wuxianliang]

Thank you very much! http://orientdb.com/docs/last/SQL.html

[agershun]

Good morning,

I reviewed OrientDB documentation and course (it is very impressive), and I
have some ideas how to implement some of these features into existing
AlaSQL infrastructure.

1. May I ask you to send me some example of SQL language constructions you
   want to be realized in AlaSQL? What kind of OrientDB SQL-statements you
   usually use in your programs?
2. What is more important for you in OrientDB: document-driven or
   graph-driven part?

Best regards,

Andrey

2015-04-19 12:59 GMT+03:00 Xianliang Wu notifications@github.com:

Thank you very much! http://orientdb.com/docs/last/SQL.html

—
Reply to this email directly or view it on GitHub
#131 (comment).

[agershun]

After some research I think that we can add the following commands to AlaSQL to support some features, exists in OrientDB. Unfortunately, we can not provide 100% compatibility of SQL dialect, because AlaSQL already uses some keywords. Below is the proposed list:

1. Document-oriented database (# operator)

    CREATE CLASS Person (name STRING, age INT, city);
    CREATE CLASS City (name STRING, country);
    CREATE CLASS Country (name STRING);
    INSERT INTO Person SET name = "Romeo", age=33, 
                 city = (INSERT INTO City SET name = "Milano", 
                          country = (INSERT INTO Country SET name = "Italy"));
    SET @italy = (SELECT FROM Country WHERE name = "Italy");
    INSERT INTO Person SET name = "Paola", age=25, 
                 city = (INSERT INTO City SET name = "Venice", country = @italy;
    SELECT name, age, city#name FROM Person WHERE city#country#name = "Italy";
    SELECT @italy#name;

Here INSERT operator returns number of inserted object.

2. SEARCH (traverse) operator on JSON object and documents

    SET @data = @[{a:1,b:{c:1,d:25}},{a:1,b:23}];
    SEARCH a b c=1 parent() FROM @data WHERE d > 20;
    -- returns {c:1,d:25}
    SEARCH a b c=1 parent() FROM @data WHERE d > 20 
             WHILE depth()<2 STRATEGY DEPTH_FIRST;
    -- returns 
    SEARCH city # country # parent() # parent() name FROM  Person;
    -- returns "Romeo", "Paola"

3. Graph support

    SET @word1 = (CREATE VERTEX Word SET name = "John");
    SET @word2 = (CREATE VERTEX  Word word2 SET name = "Maria");
    SET @word3 = (CREATE VERTEX  Word word3 SET name = "Peter");
    CREATE EDGE e12 FROM @word1 TO @word2 SET name="buy";
    CREATE EDGE e23 FROM @word2 TO @word3 SET name="sell";
    SEARCH out(name="buy") out(name="sell") name FROM @word1;
    -- returns ["Peter"]

4. Mix

    SELECT country
         FROM 
              (SEARCH any() fruit="Apple" 
                     FROM {Africa:{fruit:"Banana",country:"Ghana"}, Europe:{fruit:"Apple",country:"Poland"}, 
                              America:{fruit:"Apple", country:"USA"}, Asia:{vedgetable:"Rice",country:"China"}}
                     LET continent = id()
               ) 
         WHERE continent = "Europe";
    -- returns ["Poland"]

These are only ideas of proposed syntax extension to AlaSQL.

[agershun]

SEARCH syntax (unfortunately we can not use SELECT operator because parser ambigousity problems):

    SEARCH selector
        FROM source
        LET col1=expr1, col2 = expr2, ...
        STRATEGY strategy
        WHILE condition
        LIMIT limit
        TIMEOUT timeout

where selector can be a XPath string or number of arguments divided by spaces (AND) and/or commas (OR):

• field
• selector-function(arguments)
• expression

like:

    -- Select all fruits with price = 23 
    SELECT fruit price=23 FROM ?

Selector functions are:

• any()
• all()
• parent()
• child()
• ...

[agershun]

Documents can be realized with AlaSQL tables and columns as properties. Plus, each database will have special storage objects to keep all objects inside. Each reference field is a simple integer number (or complex id like '@10:23') with address to the storage. Plus each object has to have two special fields:

• $id - reference to the storage
• $class - class name

Object can have additional fields:

• $in - for incoming edges
• $out - for outcoming edges

If you need to refer to another object by it's id, you need to use # operator, like:

    SELECT city#country#name FROM Person

that means, that AlaSQL will look to the storage for the refered object.

You can directly refer to the object from the storage with # operator:

    -- Age of persion with id 123
    SELECT 123 # age
    SET @myid = 123;
    SET @myobj = #123;
    SELECT @myid # age 
    SELECT @myobj -> age 
    SELECT #(@myid) -> age 

For compatibility with OrientDB we can use complex reference id, like: #10:4, but in this case we need to use strings to keer addresses.

[agershun]

I started with classes. Here are some examples of operations with classes: (https://github.com/agershun/alasql/blob/develop/test/test302.js), including:

    CREATE CLASS Person;
    INSERT INTO Person VALUES {name:"Peter"};
    SELECT city#country#name FROM Person;

[wuxianliang]

The very feature we need is Graph-Driven, vertexes and edges. I think others will agree with me. @lvca

[wuxianliang]

I am trying to figure it out these days and report them here as soon as I can. Thank you for your amazing works!

[mathiasrw]

@wuxianliang Any news on this?