I am investigating how to manage a schema in OrientDB (using source control). I have a working SQL script which can be run via OrientDB's "bin/console.sh" to create classes and functions. My script only works for creating a new database because the CREATE... statements will fail the second time around. I'd like to be able to re-run the script as changes are made. So, how can we conditionally do things like CREATE CLASS or CREATE PROPERTY?
So far, I tried using IF to skip such statements, but IF is only allowed inside a script sql...end block, and if you try to put any CREATE... statements in such a block, you get the error: "Cannot change the schema while a transaction is active." The only thing we've been able to come up with is to use set ignoreErrors true, but that is far from ideal, because the errors still get thrown, so it becomes very difficult to distinguish expected errors from actual problems.
If I'm asking the wrong question (meaning, there's a better way to manage source control for the schema besides SQL scripts), please let me know.
In OrientDB v2.2.13, there is a new "IF EXISTS" clause that lets you gracefully create or update a database with the same script. It's part of the CREATE and DROP statements:
CREATE CLASS <class> [IF NOT EXISTS] ...;
CREATE PROPERTY <class>.<property> [IF NOT EXISTS] ...;
DROP CLASS <class> [IF EXISTS];
DROP PROPERTY <class>.<property> [IF EXISTS];
This feature can be found in the official documentation.