NDatabase Queries


NDatabase has five ways to retrieve objects:

All Objects of a specific class


If you just want to retrieve all the objects of a specific class, the following method can be used:

var objects = odb.Query<MyClass>();

Criteria Queries


CriteriaQuery let’s you specify restrictions on objects that the query result must contain. The NDatabase CriteriaQuery API is very close to the Hibernate Criteria API.

Example of a Criteria Query

var odb = ODBFactory.Open("query.db");

var query = new CriteriaQuery<Player>(Where.Equal("name", "julia"));
var players = odb.Query<Player>(query);

Console.WriteLine("\nPlayers with name julia");

// display each object
while(players.MoveNext())
	Console.WriteLine((i + 1) + "\t: " + players.Current);

odb.Close()

How Does it work?

CriteriaQuery is defined by 4 parameters:
  • The class of the objects that must be loaded
  • The criteria to filter objects
  • An order by clause
  • The range of the objects to be returned

CriteriaQueryExecutor first defines the execution plan (CriteriaQueryExecutionPlan). If an index can be used, then the GenericQueryExecutor.ExecuteUsingIndex() will be used. If not, the GenericQueryExecutor.ExecuteFullScan() will be used.

The goal of the execution is to get all the possible candidate objects to enter the query result.
  • If no index can be found, a full class scan (all objects will be checked) will be necessary.
  • Then the criteria query is executed against all cadidate objects.
  • Check to optimize the query by reducing the number of candidates.

Optimizations
  • Object Loading - when loading candidate objects to execute query, NDatabase does not read the entire object nor creates an instance of it, it only reads the attributes that are used in the query (CriteriaQuery.GetAllInvolvedFields()) and keep the values in attributes values map (AttributeValuesMap). The full object will only be read and instanciated if the query returns true meaning that the object is part of the result.
  • Query Range - while executing a query, one can ask NDatabase to return a part of the result: from object x to object y. It is very using for paging results.

Order by clause
  • When using an order clause, NDatabase used a BTree to guarantee the order of the objects. The criteria query manager computes the key (composed key, CriteriaQueryExecutor.BuildOrderByKey()) of the BTree based upon the order by clause definitition for each object that must be included in the query result. Then, the key is used to insert the object if the BTree.

Full scan object loading
  • To read all the objects of a specific class NDatabase first gets the ClassInfo of the class from the metamodel.
  • The ClassInfo is a meta representation of the class. It contains all the info about the class (name, package, attributes, indexes) and some pointers to its objects. ClassInfo holds the number of objects (check why the next release will not keep it anymore), a pointer (the OID) to its first object and a pointer(OID) to its last object. Then each object have a header that contains its oid, its class oid, the oid of the previous object of the same class, the oid of the next object of the same class (see the new odb file format to understand why this will change).
  • So, in the current release of NDatabase, to read all the objects of a class, go to the class info, take the oid of the first object, ask NDatabase the position of the object with the oid, reads the object (header and values), from the header, gets the next object oid. Until next object oid is null. Check current odb format file.

Restrictions
  • Restrictions on objects are build by the NDatabase2.Odb.Core.Query.Criteria.Where Factory. All the methods of the Where static class return objects of type IConstraint.
  • The IConstraint interface is the standard way to expressed an object restriction. Here the interface definitions:

 /// <summary>
 ///   An interface for all criteria
 /// </summary>
 public interface IConstraint
 {
     /// <summary>
     ///   To check if an object matches this criterion
     /// </summary>
     /// <returns> true if object matches the criteria </returns>
     bool Match(object @object);

     /// <summary>
     ///   to be able to optimize query execution.
     /// </summary>
     /// <remarks>
     ///   to be able to optimize query execution. 
     ///   Get only the field involved in the query instead of getting all the object
     /// </remarks>
     /// <returns> All involved fields in criteria, List of String </returns>
     IOdbList<string> GetAllInvolvedFields();

     AttributeValuesMap GetValues();

     /// <summary>
     ///   Gets thes whole query
     /// </summary>
     /// <returns> The owner query </returns>
     IQuery GetQuery();

     void SetQuery(IQuery query);

     bool CanUseIndex();

     /// <summary>
     ///   a method to explicitly indicate that the criteria is ready.
     /// </summary>
     void Ready();

     IConstraint And(IConstraint with);

     IConstraint Or(IConstraint with);

     IConstraint Not();
 }

The most important method if the match which will return true if a specific object match the restriction.

Here is the example of the EqualCriterion.Match method:

public bool Match(Object valueToMatch)
{
	// If it is a AttributeValuesMap, then gets the real value from the map
	// AttributeValuesMap is used to optimize Criteria Query 
	// (reading only values of the object that the query needs to be 
	// evaluated instead of reading the entire object)
	if(valueToMatch is AttributeValuesMap)
	{
		AttributeValuesMap attributeValues = (AttributeValuesMap) valueToMatch;
		valueToMatch = attributeValues.GetAttributeValue(attributeName);
	}

	if (valueToMatch == null && criterionValue == null) 
	{
		return true;
	}
	return valueToMatch != null && valueToMatch.Equals(criterionValue);
}

The EqualCriterion simply returns true when the value of the criterion is equal to the value of the object.

Here are all the methods of the Restrictions factory:
Method Description
equal Returns an EqualCriterion
like to execute a like
ilike To execute a case insentive like
gt A greater than criterion
ge A greater or Equal criterion
lt A lesser than criterion
le A lesser or equal criterio
contain To search in list. Returns true if a list contains a specific element
isNull Returns true if the object is null
isNotNull Returns true if Object is not null
sizeEq Returns true if the size of a list or array is equal to
vsizeGt Returns true if the size of a list or array is greater than
sizeGe Returns true if the size of a list or array is greater or equal to
sizeLt Returns true if the size of a list or array is lesser than
sizeLe Returns true if the size of a list or array is lesser or equal to
or To combine criterion with or : return criterio1 or criterio2 or criterion3
and To combine criterion with and : return criterio1 and criterio2 and criterion3
not To negate criterion

Object by OID


If you have the OID of an object, the following method can be used to get the object:

var object = (MyClass) odb.GetObjectFromId(oid);

Object Values API


The Object Values API is the third query type that NDatabase supports.

The Object Values API is a very powerful API to let one retrieve values of object attributes instead of the full object itself.

Sometime, you just need to retrieve the name of a user (to build a combo, for example), and traditional Object Databases force you to retrieve the whole object. NDatabase let you retrieve the attributes you need without needing instancianting a single object. This leverage a very fast and powerfull API for building reports for example.

Check a single code snippet to check how it work:

var odb = OdbFactory.Open("values.odb");
odb.Store(new User("user1","email1",new Profile("profile name",new Function("f111"))));
odb.Close();
 
odb = OdbFactory.Open("values.odb");
var values = odb.GetValues(new ValuesCriteriaQuery<User>().Field("name"));
 
var ov = values.NextValues();
Console.WriteLine("user1", ov.GetByAlias("name"));
Console.WriteLine("user1", ov.GetByIndex(0));
odb.close();

As a user have a profile and the profile has a name, you may want to retrieve the name of the profile directly, like this:

var values = odb.GetValues(new ValuesCriteriaQuery<User>().Field("name").Field("profile.name"));

Object Values API provides more features to query object values like:
  • sum
  • average
  • min
  • max
  • count
  • group by
  • Custom function

Sum

var values = odb.GetValues(new ValuesCriteriaQuery<TestClass>().Sum("int1","sum of int1"))

Average

var values = odb.GetValues(new ValuesCriteriaQuery(typeof(TestClass)).Avg("int1","average of int1"))

Min and Max

var values = odb.GetValues(new ValuesCriteriaQuery<TestClass>()
       .Min("int1","min of int1")
       .Max("int1","max of int1"));

Count

var values = odb.GetValues(new ValuesCriteriaQuery<TestClass>().Count("my count"));

Custom function

If you need a specific function, you can always implement you own Action do compute what you need. This is done by extending the CustomQueryFieldAction class.

Here is a simple implementation example:

public class TestCustomQueryFieldAction : CustomQueryFieldAction
{ 
    private Decimal myValue;
 
    public TestCustomQueryFieldAction()
    {
        this.myValue = new Decimal(0);
    }
 
    public void Execute(OID oid, AttributeValuesMap values)
    {
        var number = ValuesUtil.Convert((Number) values.Get(attributeName));
        myValue = myValue.add(new Decimal(n.ToString()).Multiply(new Decimal(2)));
    }
 
    public Object GetValue()
    {
        return myValue;
    }
 
    public boolean IsMultiRow()
    {
        return false;
    }
 
    public void Start()
    {
        // Nothing to do 
    }

    public void End() 
    {
        // Nothing to do 
    }
}

And here is how you use this custom funcion:

ICustomQueryFieldAction custom = new TestCustomQueryFieldAction();
var values = odb.GetValues(new ValuesCriteriaQuery<TestClass>()
    .Custom("int1", "custom of int1",custom));

Last edited Jan 6, 2013 at 3:23 PM by jacek, version 21

Comments

hoyoch May 31, 2013 at 8:42 AM 
CriteriaQuery class does not exist anymore. Update Criteria Queries document.