ORM Basics

Overview

ORM stands for Object-Relational Mapping. It is a technique that allows developers to interact with a relational database using an object-oriented programming language. ORM tools provide a way to map the objects in a program to the tables in a relational database, thus allowing developers to perform CRUD (create, read, update, delete) operations on the database using the objects and methods of the programming language, rather than writing raw SQL queries. The ORM in Decisions is used for storing and retrieving entities in the main Decisions database.

ORM Base Classes

There are three ORM base classes that you can inherit to create an ORM object. 

Creating an ORM Object

1. Create a class and decorate the class with " [ORMEntity("nameofyourtype")] ". This will mark the class as an ORM entity and allow the developer to name the database table that is generated to hold the data the user saves of the class. 
2. Decorate the class to inherit from DecisionsFramework.ServiceLayer.AbstractEntity.

   If the ORM class will be used in Decisions steps, decorate it with the attributes [DataContract] and [Writable]. Also, decorate the first property of the class with [ORMPrimaryKeyField] and [PropertyHidden]).

3. All other properties of the class should be decorated with [ORMField] attribute.
   The [ORMField] attribute provides many overrides that allows the developer to set the database type, field name, and other specifications.

The code example below demonstrates a full ORM class:

[ORMEntity("sampleormentityperson")]
[DataContract]
[Writable]
public class SampleORMPersonEntity : AbstractEntity
{
    [ORMPrimaryKeyField]
    [PropertyHidden]
    [DataMember]
    public string ID { get; set; }


    [ORMField("fullname")]
    [DataMember]
    [WritableValue]
    public string FullName { get; set; }

    [ORMField("age")]
    [DataMember]
    [WritableValue]
    public int Age { get; set; }

    [PropertyHidden]
    [DataMember]
    public override string EntityName
    {
        get
        {
            return FullName;
        }
        set
        {
            FullName = value;
        }
    }

    [PropertyHidden]
    [DataMember]
    public override string EntityDescription
    {
        get
        {
            return FullName;
        }
        set
        {
            FullName = value;
        }
    }

    public override BaseActionType[] GetActions(AbstractUserContext userContext, EntityActionType[] types)
    {
        return new BaseActionType[0];
    }
}

public static void DeleteSamplePersonById(string id)
{
    new ORM<SampleORMPersonEntity>().Delete(id);
}

public static void StoreSamplePerson(SampleORMPersonEntity newSamplePerson)
{
    new ORM<SampleORMPersonEntity>().Store(newSamplePerson);
}

public static bool SamplePersonExists(string id, bool includeDeleted)
{
    return new ORM<SampleORMPersonEntity>().Exists(id, includeDeleted);
}

public static SampleORMPersonEntity[] GetPersonByName(string fullName, QueryMatchType matchType)
{
    return new ORM<SampleORMPersonEntity>().Fetch(new WhereCondition[]
    {
        new FieldWhereCondition("fullname", matchType, fullName)
    });
}

Object Relationships

Using the ORM, objects can have relationships of one-to-one, one-to-many, and many-to-many by specifying the desired relationship in the field. 

Below is an example property on an ORM object that specifies a field of another ORM type with a one-to-many relationship type. The property PersonList is an array on the ORM object. When creating the new ORMOneToManyRelationship object, specify a field name of "_SampleOrmWithRelationship". This adds a SampleOrmWithRelationship field to the sampleormentityperson database table, which will contain the ID of the SampleOrmWithRelationship object to which it is related.

The code below is an example of the full ORM object on which the one-to-many relationship of PersonList is specified:

public class SampleOrmWithRelationship : AbstractEntity
{
    #region Fields

    [ORMPrimaryKeyField]
    string id;

    [DataMember]
    [WritableValue]
    [PropertyHidden(false)]
    public string ID
    {
        get { return id; }
        set { id = value; }
    }

    [ORMField]
    string SimpleStringMember;

    ORMOneToManyRelationship<sampleormpersonentity> PersonList = new ORMOneToManyRelationship<sampleormpersonentity>("SampleOrmWithRelationship", false);

    #endregion

    #region Properties

    [DataMember]
    [WritableValue]
    [FieldName("SimpleStringMember")]

    public string SimpleStringMember
    {
        get { return this.SimpleStringMember; }
        set { this.SimpleStringMember = value; }
    }

    [DataMember]
    [WritableValue]
    [FieldName("PersonList")]
    public SampleOrmPersonEntity[] PersonList
    {
        get { return this.PersonList.Items; }
        set { this.PersonList.Items = value; }
    }

    #endregion
}</sampleormpersonentity></sampleormpersonentity>

Adding Indexes

Adding Index to Field

Indexes can be declared on fields that have been marked to be stored by the ORM. An index name and a flag to say if the index is unique can be added to this. Below is an example of specifying an index on a field.

[ORMField("entity_folder_id", typeof(KeyFieldConverter))]
[ORMFieldIndex("entity_folder_id_INDEX", false)]
private string entityFolderID;

Adding Index to Table

If an index includes more than one field, it can be specified at the table level. Below is an example of specifying an index on a table.

[ORMEntity("cache_entity")]
[ORMTableIndex("cache_entity_index", new string[]{ "configName", "instanceName", "itemId"}, true)]
class CacheEntity : BaseORMEntity