OrientDB API Demo 3

"By Athanassios I. Hatzis, PhD"<>" - (C) "<> DateString[]

Schema Less vs Schema Full Operations

Load Packages

ClearAll["Global`*"]

<<"DBAPI`OrientDB`"

Shortened Package Commands

l2k =DBListSetToSQL92[#,values->False]&;
l2v = DBListSetToSQL92;

e2j=DBexpressionToJSON;
e2jC=DBexpressionToJSON[#,compact->True]&;

j2r=DBjsonToRules;
j2a=DBjsonToAssociations;
j2d=DBjsonToDataset;

odb = ODBapi;
gds = ODBgetDataset;
gfa = ODBgetFieldAttributes;

Database Operations

Del Database

odb[
com->"delDatabase",
db->"TestDB", 
usr->"root",
pwd-> "123"]

Add Database

(odb[
com-> "addDatabase",
db->"TestDB",
dbtype->"plocal/document",
usr-> "root",pwd-> "123"])[[2]]

Get Databases

odb[com->"getDatabases"]//gds

Login

odb[com-> "login", db-> "TestDB",usr->"admin", pwd-> "admin"]

Class Operations

Add Class

Uses the “CREATE CLASS” command

odb[com-> "addClass",db->"TestDB",class->"CLA"]//gds

Del Class

Uses the DROP CLASS command

odb[com->"delClass", db->"TestDB", class-> "CLA"]//gds

Add Class via POST

Uses HTTP POST request http://localhost:2480/class/TestDB/CLA

odb[com-> "addClassViaHTTP",db->"TestDB", class-> "CLA"]

Schema Less Operations

Make a List of Rules

lisR={
"json1"->Thread[{"x","y","z"}->{"a","b",3.9}],
"json2"->{"alpha",3,"beta", 2},
"price"->5.3945,
"title"->"This is a title"
};

Represent as Dataset

lisR//Association//Dataset

Represent as JSON in Compact Form

jrec=lisR//e2jC

jrec//j2d

Represent as Keys and Values Lists

ks=lisR//Association//Keys

vs=lisR//Association//Values

Transform to SQL-92 syntax with Keys, Values as strings parenthesized

ksString=ks//l2k

vsString=vs//l2v

Write Record to OrientDB

Using the POST Document HTTP API command

odb[com-> "addRecord",db-> "TestDB",class-> "CLA", record->jrec,debug->True]//gds

Using the INSERT INTO <class> CONTENT osql command

odb[com-> "addContent",db-> "TestDB",class-> "CLA",record->jrec,construct->"DOCUMENT",debug->True]//gds

Using the INSERT INTO <class> <keys> VALUES <values>

odb[
com-> "addValues",db-> "TestDB",class-> "CLA",keys->ksString,values->vsString,construct->"DOCUMENT",debug->True]//gds

Read ALL Records

odb[com->"getOSQL",db->"TestDB",sql-> "select from CLA"]//gds

Note that the fieldType of the last record added is different from the previous two

Read a Single Record from OrientDB

odb[com->"getRecords", db-> "TestDB", id->"9:2"]//gds

Delete Records permanently from OrientDB

Uses the TRUNCATE RECORD sql command

odb[com->"delRecords", db->"TestDB",id-> "[9:0, 9:1]"]//gds

odb[com->"getOSQL",db->"TestDB",sql-> "select from CLA"]//gds

Uses the TRUNCATE CLASS sql command

odb[com->"delRecords", db->"TestDB", class->"CLA", all->True, construct->"DOCUMENT"]//gds

Schema Full (Mixed) Operations

Add Schema Full Class

odb[com->"addClass",db-> "TestDB", class-> "FRUIT"]//gds

Add Record Fields (Properties)

Get Class Information

odb[com-> "getClass",db-> "TestDB", class-> "FRUIT"]//gds

There are no properties defined and no records added

Add Properties

odb[com->"addProperty", db->"TestDB", class-> "FRUIT", propnam-> "fruitNameField",proptype->"String"]

odb[com->"addProperty", db->"TestDB", class-> "FRUIT", propnam-> "fruitDescrField",proptype->"String"]

odb[com->"addProperty", db->"TestDB", class-> "FRUIT", propnam-> "fruitPriceField", proptype->"DECIMAL"]

Delete Properties

Note that Class properties, i.e. schemafull record fields, are not removed from existing records. 
They are deleted only from the schema of the class

odb[com->"delProperty", db->"TestDB", class-> "FRUIT", propnam-> "fruitNameField"]

Add the property again

odb[com->"addProperty", db->"TestDB", class-> "FRUIT", propnam-> "fruitNameField",proptype->"String"]

Get Class Information

Represent Class Information with Dataset structure

$fruitDS=odb[com-> "getClass",db-> "TestDB", class-> "FRUIT"]//gds

Extract and View the Attributes for all Properties (Fields) of the Class

gfa[$fruitDS,All]

Add Mixed Records

Schema-less

It means that the keys of the record have not been defined as properties, i.e. fields, of the class

Uses the POST document HTTP API command

fruitRec=Thread[{"fruitName","fruitDescr", "fruitPrice"}->{"apple", "this is an apple", 9.55}]//e2j

odb[com-> "addContent",db-> "TestDB", class-> "FRUIT", record->fruitRec,construct->"DOCUMENT",debug->True]//gds

Schema-full - Fields have been defined

odb[
com-> "addOSQL",
db-> "TestDB", 
class-> "FRUIT", 
sql-> "INSERT INTO FRUIT(fruitNameField,fruitDescrField,fruitPriceField) VALUES ('LEMONS','LEMONS ARE GREAT',17.99)"
]//gds

odb[
com-> "addOSQL",
db-> "TestDB",
class-> "FRUIT", 
sql-> "INSERT INTO FRUIT SET fruitNameField='Strawberries', fruitDescrField='I like them', fruitPriceField=18.59"
] //gds

Get Records

Get ‘apple’ Record where fruitPrice : FLOAT

odb[
com-> "getOSQL",
db-> "TestDB", 
class-> "FRUIT", 
sql-> "select from FRUIT where fruitName=='cheap apples'"]//gds

Get ‘LEMONS’ Record where fruitPriceField : DECIMAL

odb[
com-> "getOSQL",
db-> "TestDB", 
class-> "FRUIT", 
sql-> "select from FRUIT where fruitNameField=='LEMONS'"]//gds

Get ‘Strawberries’ Record where fruitPriceField > 18.01

odb[
com-> "getOSQL",
db-> "TestDB", 
class-> "FRUIT", 
sql-> "select from FRUIT where fruitPriceField > 18.01"]//gds

Update Records

Read the Record we want to modify

recOLD=odb[com->"getRecords",db-> "TestDB",id-> "11:0"]//gds

recNEW=recOLD//Normal;

Modify Values

recNEW["fruitDescr"]="this is a delicious apple";
recNEW["fruitPrice"]=9.99;

Transform to JSON

recNEWJSON=recNEW//e2j

Update the Record for the First Time

Uses the PUT document HTTP API command

recOLD=odb[com->"updRecord",db-> "TestDB",id-> "11:0", record-> recNEWJSON]//gds

Note that the @version number has been changed to 2

Modify Values for a Second Time

recNEWJSON=Thread[{"fruitName","fruitDescr", "fruitPrice"}->{"cheap apples", "these are cheap apples", 9.11}]//e2jC

Update the Record for a Second Time

recNEW=odb[com->"updRecord", db->"TestDB", class->"Fruit",id->"11:0",ver-> 2, method->"PATCH",record-> recNEWJSON,debug->True]//gds

Note that the @version number has been changed to 3