ActiveOrient: Joins and Links

In the Introduction we created a self-referencing join.
Lets compare with ActiveRecord

# (ActiveRecord) in file /model/person.rb
class Person
  has_many :children, class_name: 'Person', foreign_key: 'father_id'
  belongs_to :father, :class_name => 'Person'
end
# then 
 Person.find( name: 'Hugo').first.father 
 Person.find( name: 'Hugo').first.children

We omited the tricky part of setting up a database-definition, defining proper indices and perform a migration through rake-tasks and note, that data have to be inserted, too.

Insert Data

With ActiveOrient, we just create the database-table, insert our data and are done.

> V.create_class :person
> hugo = Person.create name: 'Hugo', 
                       father: Person.create( name: "Reimund" ),
                       children: ["Eva", "Ulli", "Uwe", "George"].map{|c| Person.create( name: c) }
=> "<Person[150:0]: children: [#146:0, 147:0, #148:0, #149:0], father : <Person[145:0]: name: Reimund>, name: Hugo>" 

> hugo.father.name   => "Reimund"
> hugo.children.name => ["Eva", "Ulli", "Uwe", "George"]

Note that children are represented by their database-id's only, this is equal to the foreign_key used by ActiveRecord.

The schemaless approach reveals a quick solution and exposes the basic functionality of a comparable RDMS-Database. But there are limits. The possibility to work schemaless does not mean, that ad-hoc designs are favorable. It is, for instance, not possible, to query linked records, unless a proper schema is defined. We are still in the database-world, the world of structured data.

Define Properties

It is a good practice, to put an index on any property, which is subject of a query. We want to create a notunique-Index on the :name property. :children are just a list of links, containing Person objects and
the :father is a linkto a Person-record. But first, lets delete anything.

> Person.delete all: true
> Person.create_property  :name, type: :string, index: :notunique
> Person.create_property  :children, type: :link_list, :linked_class: Person
> Person.create_property  :father, type: :link, linked_class: Person
# check
> Person.print_properties
  Detected Properties for class person
    person.children -> LINKLIST -> person
    person.father   -> LINK -> person
    person.name     -> STRING

To insert another record, we must avoid to create a duplicate of the Reimund-Person.

pete = Person.create name: 'Pete', 
                     father: Person.upsert( where: {name: "Reimund"} ),
                     children: ["Eva", "Mathew" ].map{|c| Person.create( name: c) }

Instead of creating a new record for the father, we search the database and use the existing entry. Upsert inserts a record if no entry is found. That guarantees that :father is always a valid link.

Query the Database

Orientdb Database classes and -records are queried with a SQL dialect. ActiveOrient provides a method query to build such queries similar to ActiveRecord.

> Person.query.where( "father is NOT NULL" ).execute.name
 #INFO->select from person where  father is NOT NULL 
 => ["Peter", "Hugo"] 
> Person.query.where( 'father.name' => 'Reimund' ).projection('name').execute
 #INFO->select name from person where father.name = 'Reimund' 
 => [{:name=>"Pete"}, {:name=>"Anton"}] 

The first query returns two Person-records. We display only the property :name. The second query specifies a projection and returns a list of hashes.

Model-helper

Complex queries can be stored in the model-file of the class. Queries are class-methods. Thus, in /model/person.rb

 class Person
  def self.grandchilds of:
    query.where('father.name' => of)
         .projection( :children )
   end
 end

The class method returns a OrientQuery. It can further modified, inspected by calling to_s and finally executed.

> Person.grandchilds( of: 'Reimund' ).execute.to_human
# INFO->select children from person where father.name = 'Reimund' 
["{ children: #29:5#30:5#31:5#32:4 }", "{ children: #26:5#27:5 }"]
# or
Person.grandchilds( of: 'Reimund' ).execute{|y| y[:children]}.name
 => ["Eva", "Ulli", "Uwe", "George", "Eva", "Mathew"] 

For pre-prosessing OrientQuery.execute exposes any returned record to the optional block. Obviously, we are interested in :children. The query returns links to Person-Objects which are expanded by ActiveOrient. Thus .name simply addresses the name-attribute of the returned records.

Conclusion

The database has basic support for joined tables. Anybody familiar with RDMS-(SQL)-Databases should be comfortable with unidirectional links and joins and their ActiveRecord-like querying. We can develop a draft sketch (schemaless) without any configuration. If necessary, constrains, properties, indices and validations are added. The power of nested projections goes far beyond anything in the RDMS-world.

ActiveOrient combines the power and beauty of ruby with SQL for the 21st Century.

Unidirectional Links and Joins are nice and familiar. In the next part, we extend this approach to bidirectional Links and take a walk into the world of graphs.