OrientDB console commands and query syntax

Run console via
[OrientDB_Home]/bin/console.sh

Type the "help" or "?" command to see all available console commands.

All avilable console commands at this url: Tutorial:-Run-the-console



Some usefull console commands and query

---

Connect to database or a remote server instance
Synopsis: 
connect <url> <user> <password>
Ex:  
connect remote:localhost root someUglyPassword
connect remote:localhost/dbname admin admin

Let's analyze the URL we have used: remote:localhost/demo.
The first part is the protocol, remote in this case, which means to contact the server using the TCP/IP protocol. localhost is the host name or the IP address where the server resides, in this case it is on the same machine. demo is the name of the database with which we want to connect.

---

List all databases
Synopsis: 
list databases

---

Database info
Synopsis: 
info

---

Create a new database
Synopsis: 
create database <database-url> <user> <password> <storage-type> <db-type>
Parameters:
- storage-type (local or memory)
- db-type (document or graph)
Ex:
create database remote:localhost/mytest root admin local graph

---

Display all classes on database
Synopsis: 
classes

---

Display all clusters on database
Synopsis: 
clusters

---

Class info
Synopsis: 
info class <class-name>
Ex:
info class Student

---

Create a class
Synopsis: 
create class <command-text>
Ex:
create class Student 

---

Create a class property
Synopsis: 
create property <command-text>
Ex:
create property Student.surname string
create property Student.birthDate date

---

Alter a class property
Synopsis: 
alter property <command-text>
Ex:
alter property Student.name min 3

---

Retrieve all records of a class
Synopsis: 
browse class <class-name>
Ex:
browse class OUser

---

Display  record attributes
Synopsis: 
display record <number>
Parameters:
- number (record number)
Ex:
display record 0

---

Select a record (OrientDB syntax)
Synopsis: 
load record <record-id>
Ex:
load record #12:4

---

Select records (SQL like syntax)
Synopsis: 
select <query-text>

Base Ex:

• select from MyClass (select work on classes by default)
• select from cluster:MyCluster (peform select on cluster)
• select from #10:3 oppure select from [#10:3, #10:3, #10:5](select specific records by RID)
• select from OUser where name like 'l%'
• select from Employee where city = 'Rome' order by surname asc, name asc
• select sum(salary) from Employee where age < 40 group by job
• select from Employee where gender = 'male' limit 20
• select from Employee where gender = 'male' skip 40 limit 20 (usefull for pagination)
• select name, phones from student where phone.keys() in ('mobile') (select by the key "mobile" of th asociative map attribute "phone")

Join Ex:

• Perform JOIN between "city" and "country" tables (classes)
  select * from city where country.name = 'Italy'
• Select first 3 names of city from records of type "address" for italian (country) address
  select city.name from address where city.country.name = 'Italy' limit 3 
• Select all account with atleast  one address (Note: "addresses" is a special attribute of type collection in the class (table) "account".  It realizes a 1-N relation).
  select from account where addresses.size() > 0
• Find accounts with at least one address of Washington.
  Note: "addresses" is a special attribute of type collection in the class (table) "account".  It realizes a 1-N relation.
  Note: FLATTEN() ectrat results from different collections and put all them in a single collection to facilitate data management.
  select flatten(addresses) from account where addresses contains ( city.country.name = 'Washington' ) 

Note: when we perform a select, values inside an array are not shown. By default we can see only a number of element inside the collection.

---

Insert records (SQL like syntax)
Note:
in OrientDB sql insert syntax, the list of fields is mandatory. The reason is simple: OrientDB is a schema-less DBMS;
is it possible to insert array and associatives mapps values using "[]" and "{}".

Ex:
insert into student (name,surname) values ('Jay','Miner')

---

Update records (SQL like syntax)

Ex:
UPDATE test SET city = 'Rome' WHERE name = 'Luca'

UPDATE has been extended with new keywords:

• ADD and PUT, to insert elementin collections as array or map respectively. If the array or mapp does not existit will be created automatically;
• REMOVE, to remove an element from a collection. Creiamo una nuova collection di string, che rappresentarà il set di tag che assoceremo ai record: 

Add element to array:
UPDATE student ADD tags = 'tutorial'

Add element to associative ma:
UPDATE test put phones = 'mobile', '+393333888' WHERE name = 'Luca'

Note: when we perform a select, values inside an array are not shown. By default we can see only a number of element inside the collection. 

---

Delete records (SQL like syntax) 

Ex:
DELETE FROM student WHERE tags.size() > 0

---