[OEP 4] OrientDB SQL v3

[luigidellaquila]

Summary:

Rationalize current SQL syntax to allow easy, complete and consistent manipulation of multi-model data structures

Goals:

• Remove ambiguities of current SQL syntax
• Enrich the grammar with new operators, to support all the possible data manipulations on a Multi-Model domain (Eg. array split and concatenation, bitwise operations)
• Make SQL syntax easier to understand, use and to document
• Use the OEP as a starting point for the formal grammar documentation

Non-Goals:

• change the parsing technology (current JavaCC parser will remain)
• discuss the query planning and execution (it will be managed in another OEP)

Motivation:

OrientDB SQL query syntax/engine is probably the first component that was developed in OrientDB.

In the years, the syntax evolved by addition of new keywords and features, sometimes in a way that is not completely consistent.

The result is that current OrientDB SQL is sometimes ambiguous or incomplete (see https://github.com/orientechnologies/orientdb/issues/5950).

Description:

Redefine the SQL syntax and semantics, validate its consistency. Some work is already in progress on a parallel docs branch

https://github.com/orientechnologies/orientdb-docs/blob/newexecutor/SQL-Syntax.md https://github.com/orientechnologies/orientdb-docs/blob/newexecutor/SQL-Projections.md https://github.com/orientechnologies/orientdb-docs/blob/newexecutor/SQL-Update.md

Alternatives:

• leave it as is and only implement the query execution part

Risks and assumptions:

• SQL v.3 won't be 100% backward compatible with the previous versions

Impact matrix

• [ ] Storage engine
• [x] SQL
• [ ] Protocols
• [ ] Indexes
• [ ] Console
• [ ] Java API
• [ ] Geospatial
• [ ] Lucene
• [ ] Security
• [ ] Hooks
• [ ] EE
• [x] drivers

[luigidellaquila]

We have to consider that these changes will impact 3rd party drivers, especially those who have query builders.

[francisco1844]

Has there ever been discussion about what SQL commands, not currently supported, could be added in future versions? One of the reasons I started to look at OrientDB was the SQL compatibility. For many of us with years and years of RDBMs having that available highly reduces our onboarding to the product.

I am new and still going over the docs, but I think others who have used it longer may have suggestions to that regard.

As for considering impact on third party drivers, we could have a period where we have features listed as to be removed in future versions (if removing), however changes to existing commands are much harder since it could potentiall break third party drivers so that needs to be informed to developers

Although developers of drivers may be able to do some workaround based on the version of the DB.

[luigidellaquila]

Hi @francisco1844

There are many enhancement requests about this in the main issue tracker, I think we should check them and link them here...

[lvca]

In the UPDATE I think there is a typo:

orientdb> UPDATE Account REMOVE addresses['Luca'] = UNDEFINED

in ordert to be coherent should be

orientdb> UPDATE Account SET addresses['Luca'] = UNDEFINED

However, removing REMOVE and UPDATE would break existent queries. Why don't keep it?

[luigidellaquila]

ops, typo... Just fixed it, thanks!

From current docs, all the following are supported syntaxes

UPDATE Profile REMOVE nick                             // remove a field
UPDATE Account REMOVE addresses = addresses[1]         // remove an item from a list
UPDATE Account REMOVE addresses = 'Luca'               // remove values from a map (???? why values and not keys?)

IMHO the first case is quite clear, the second one is almost acceptable, the third one is just a mess. The reasons to drop the backward compatibility are:

• avoid messy behaviors, that have to be documented and explained to users
• three less pieces of code (not one) to be maintained

But again, we can decide to keep it, the UPDATE REMOVE is not my biggest concern. (but please, let's drop UPDATE PUT, that's really ugly...)

Luigi

[luigidellaquila]

sorry, rephrasing

• three less pieces of code (not one) to be rewritten (as we are dropping the old executor)

[tglman]

we could even do this:

UPDATE Profile REMOVE nick                             // remove a field
UPDATE Account REMOVE addresses[1]         // remove an item from a list
UPDATE Account REMOVE addresses['Key']               // remove values from a map

that would be a middle way, but not strong opinion on that actually at the end i would prefer another proposal from luigi:

UPDATE Account SET addresses = addresses.remove(1)      
UPDATE Account SET addresses = addresses.removeKey('Luca')             

[francisco1844]

New user here... So in OrientDB one can use an update command to drop a field? Instead of the more common (standard?) alter table? I would much rather see us using, when it makes sense, commands closer to the SQL standard for the SQL interface.

I would have never expected the update command to allow to drop a field. In my opinion update command should really be only DML, not DDL.

Staying close to the SQL standard, when possible, makes it much easier for DBAs coming from RDBMS DBs to get up and running faster.

On Wed, Jun 22, 2016 at 10:59 AM, tglman notifications@github.com wrote:

we could even do this:

UPDATE Profile REMOVE nick // remove a field UPDATE Account REMOVE addresses[1] // remove an item from a list UPDATE Account REMOVE addresses['Key'] // remove values from a map

that would be a middle way, but not strong opinion on that actually at the end i would prefer another proposal from luigi:

UPDATE Account SET addresses = addresses.remove(1) UPDATE Account SET addresses = addresses.removeKey('Luca')

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/orientechnologies/orientdb-labs/issues/4#issuecomment-227771742, or mute the thread https://github.com/notifications/unsubscribe/ADEin40FRTC-muqm7a1gpJf6CzL4xhdaks5qOU3dgaJpZM4I6aL3 .

[tglman]

@francisco1844 orientdb is a schemaless/schemamixed database, the remove remove only a field or a value from a nested structure for a specific document, and do not manipulate the schema.

[luigidellaquila]

Implemented, closing