No more joins

OrientDB and SilverStripe

What is OrientDB?


  • Document based graph databse
  • NoSQL - in this case SQL "dialect" or Gremlin
  • Open source and written in Java

Learn more about OrientDB

Why use OrientDB?

  • Open source Apache 2 licence
  • Tonne of documentation
  • Active community

More reasons...

  • OrientDB is very fast and well suited to queries that would usually need joins in relational databases.
  • Both a document and graph database in one
  • Can store up to 150,000 records per second on common hardware
  • A single server does the work of about 125 servers running MySQL
  • Supporting up to 302,231,454,903,657 billion (2^78) records
  • Uses MVRB-Tree algorithm which is fast and consumes less memory than Red-Black tree alone
  • ACID transaction support
(Claims from the Github repo)

What is a document database?

  • Data stored in documents in JSON format
  • Documents are similar to records/tuples/rows in RDBMS
  • Less rigid than RDBMS, documents can be schema-less/schema-full or a mixture
  • POJOs - plain old javascript objects

Example documents

{
    FirstName: "Bob",
    LastName: "Smith",
    Age: 27
}

{
    FirstName: "Bob",
    LastName: "Smith",
    Age: 27,
    Title: "Mr",
    Nationality: "New Zealander"
}

Document relationships

  • Do not use joins
  • Relationships are physical connection between documents
  • 1-1 relations use Link data type
  • 1-N and N-M relations use container data types LinkSet/LinkList/LinkMap
  • Can have embedded documents also

What is a graph database?

 

  • Introduces vertices as a way to navigate relaionships
  • Nodes are essentially documents
  • Vertices can have properties also

Getting started

  • Download OrientDB graph or standard edition
  • Extract to a directory
  • The /config directory holds XML config files
  • The /databases directory holds the databases

Excellent tutorial here on getting started

Create a database

  1. Start the server
  2. Open a console
  3. Connect to server as server user
  4. Create database
  5. Connect to database as database user

Start the server

# Location of OrientDB binaries
cd ~/Scripts/orientdb-graphed-1.5.0/bin/

# Start server
./server.sh

# Stop server
./shutdown.sh

Open a console

# Location of OrientDB binaries
cd ~/Scripts/orientdb-graphed-1.5.0/bin/

# Open console to perform queries etc.
./console.sh

Connect to server as server user

OrientDB has server users that can be found in config/orientdb-server-config.xml.
These are the users used to connect to the server and create databases. 

<users>
  <user resources="*" password="A5D6...H796" name="root"/>
  <user resources="connect,server.listDatabases,server.dblist" password="guest" name="guest"/>
</users>

# Connect to the server
connect remote:localhost root [password]

Create database

# Create a database
create database remote:localhost/[dbname] root [password] plocal document

Connect to database

# Connect to the database
connect remote:localhost/[dbname] admin admin

Create database structure

  1. Create class
  2. Create properties

Create class

Class is equivalent to table basically, OO ideas incorporated. 

# List classes
list classes

# Create new class
create class Page

# Create abstract class
create class Person abstract

# Create concrete class from abstract
create class Student extends Person

# Create subclass
create class Freshman extends Student

# Display class structure
desc Student

Create properties

  1. properties defined (schema-full)
  2. properties not defined (schema-less)
  3. some properties defined (mixed)
# Create properties
create property Person.FirstName string
create property Person.Age integer

# Add constraint of minimum 3 characters
alter property Person.FirstName min 3

Using data

  1. Insert records
  2. Select records
  3. Update records
  4. Delete records

Insert records

# Inserting multiple records
create class Posts

insert into Posts (Title,Text) values ("title 1","text 1"), ("title 2","text 2");

create property Posts.URLSegment string

insert into Posts (Title,URLSegment) values ("title 3","title-3");

Select records

# Select all records
select * from Posts

# More common:
select from Posts

# Particular record
select from #27:0
  • Often use the @rid of a record in query "FROM" condition
  • Much faster than in "WHERE" condition
    e.g: "WHERE @rid = '#27:0'"

Update records

# Update a record
update #25:0 set Title = 'Post title 25:0'

# No double quote delimiters for class and property names
# This tries to find a field called "Title" (including quotes) to update
update #25:0 set "Title" = 'Some title'

Delete records

# Delete a record
delete from #25:2

Inheritance

# Create structure
create class Parent
create class Child extends Parent
insert into Child (Name) values ("Eric Forman")

select from Child
# ----+-----+------------
# #   |@RID |Name        
# ----+-----+------------
# 0   |#16:0|Eric Forman
# ----+-----+------------
select from Parent
# ----+-----+------------
# #   |@RID |Name        
# ----+-----+------------
# 0   |#16:0|Eric Forman
# ----+-----+------------
  • Similar to OO idea of inheritance.
  • Child records are included when querying the parent table.

Inheritance

insert into Parent (Name) values ("Red Forman")

select from Parent
# ----+-----+------------
# #   |@RID |Name        
# ----+-----+------------
# 0   |#15:0|Red Forman         
# 1   |#16:0|Eric Forman
# ----+-----+------------

select from Child
# ----+-----+------------
# #   |@RID |Name        
# ----+-----+------------
# 0   |#16:0|Eric Forman
# ----+-----+------------
  • Parent records are not included when querying child class.

Relations

  • OrientDB is not relaional DB so no "Join" operator
  • Relations establish physical connection between documents
  • Referenced or embedded relationships

Referenced relationships

  • Reference other records
  • 1-1, 1-M, N-M relations
  • Query with traversals (no joins)
  • Loosely coupled

Has One/Has Many

# Creating link for reference to an object in Authors class
create property Posts.Author link Authors

Use a "Link" data type to reference other objects

Many Many

# Creating link sets for references to other types of objects
create property Posts.Categories LinkSet Categories
create property Categories.Posts LinkSet Posts

Use a container of references which can be one of 3 data types:

  • LinkSet - Unordered set of elements, unique
  • LinkList - Ordered set of elements, duplicates allowed
  • LinkMap - Set of key/value pairs

Embedded relationships

  • Do not reference other records
  • 1-1, 1-N, N-M relations
  • Query with traversals (no joins)
  • Tightly coupled - an embedded document

Querying relations

# Traverse Link
select Author.Name from #15:0

# Traverse containers
traverse Posts from #16:2
  • Traversing very quick, faster than joins
  • Traversing containers returns parent record in the results
  • Can traverse fieldname/*/any()/all()
  • Context variables like $parent, $current, $depth, $path

Example

select from #18:0

# ----+-----+---------+-----------+----+----
# #   |@RID |ClassName|Title      |ID  |Tags
# ----+-----+---------+-----------+----+----
# 0   |#18:0|Article  |Article 278|18:0|[2] 
# ----+-----+---------+-----------+----+----

traverse Tags from #18:0

# ----+-----+----+---------+-----------+----+-----+--------
# #   |@RID |Tags|ClassName|Title      |ID  |Name |Articles
# ----+-----+----+---------+-----------+----+-----+--------
# 0   |#18:0|[2] |Article  |Article 278|18:0|null |null    
# 1   |#26:0|null|Tag      |null       |26:0|Tag 1|[1]     
# 2   |#26:2|null|Tag      |null       |26:2|Tag 2|[1]     
# ----+-----+----+---------+-----------+----+-----+--------
  • Tags is a LinkSet
  • Traversing to the 2 tags records linked to the article

SilverStripe Implementation

Made with Slides.com