Skip to main content

CRUD Operations

In ORDA, you access data through entities and entity selections objects. These objects allow you to execute CRUD (Create, Read, Update, Delete) basic operations on data, along with queries and sorts.

Creating an entity​

There are two ways to create a new entity in a dataclass:

  • You create an empty entity using the dataclass.new() function.
  • You create a new reference to a database record using ORDA functions such as entity.next() or entityselection.first().

At this moment, the entity is only created in memory. If you want to add it to the datastore, you must call the entity.save() function.

Entity attributes are directly available as properties of the entity object. For more information, please refer to Using entity attributes.

For example, we want to create a new entity in the "Employee" dataclass in the current datastore with "John" and "Dupont" assigned to the firstname and name attributes:

var myEntity : cs.EmployeeEntity
myEntity=ds.Employee.new() //Create a new object of the entity type
myEntity.name="Dupont" //assign 'Dupont' to the 'name' attribute
myEntity.firstname="John" //assign 'John' to the 'firstname' attribute
myEntity.save() //save the entity

Entities and references​

An entity contains a reference to a database record. Different entities can reference the same record. Also, since an entity can be stored in an object variable, different variables can contain a reference to the same entity.

If you execute the following code:

 var e1, e2 : cs.EmployeeEntity
 e1=ds.Employee.get(1) //access the employee with ID 1
 e2=e1
 e1.name="Hammer"
  //both variables e1 and e2 share the reference to the same entity
  //e2.name contains "Hammer"

This is illustrated by the following graphic:

Now if you execute:

 var e1,e2 : cs.EmployeeEntity
 e1=ds.Employee.get(1)
 e2=ds.Employee.get(1)
 e1.name="Hammer"
  //variable e1 contains a reference to an entity
  //variable e2 contains another reference to another entity
  //e2.name contains "smith"

This is illustrated by the following graphic:

Note however that entities refer to the same record. In all cases, if you call the entity.save() method, the record will be updated (except in case of conflict).

In fact, e1 and e2 are not the entity itself, but a reference to the entity. It means that you can pass them directly to any function or method, and it will act like a pointer. For example:

 forEach(entity,selection)
    do_Capitalize(entity)
 end

And the do_Capitalize method is:

 declare (entity : cs.EmployeeEntity)
 var name : string
 name=entity.lastname
 if(name!=null)
    name=uppercase(substring(name,1,1))+lowercase(substring(name,2))
 end
 entity.lastname=name

You can handle entities like any other object and pass their references directly as parameters.

note

You can use as many entities as you need at the same time, there is no automatic lock on an entity (see Entity locking). When an entity is loaded, it uses the lazy loading mechanism, which means that only the needed information is loaded.

Using entity attributes​

Entity attributes store or reference data and map corresponding fields in the corresponding table of the database, with regards to their kind. Entity attributes of the storage kind can be set or get as simple properties of the entity object, while attributes of the relatedEntity or relatedEntities kind will return an entity or an entity selection. Entity attributes of the calculated and alias kind can return any value type.

For example, to set a storage attribute:


 entity=ds.Employee.get(1) //get employee attribute with ID 1
 name=entity.lastname //get the employee name, e.g. "Smith"
 entity.lastname="Jones" //set the employee name
 entity.save() //save the modifications

Accessing a related attribute depends on the attribute kind. For example, with the following structure:

You can access data through the related object(s):

 var entity : cs.CompanyEntity
 var entitySel : cs.ProjectSelection
 entity=ds.Project.all().first().theClient //get the Company entity associated to the project
 entitySel=ds.Company.all().first().companyProjects //get the selection of projects for the company

Note that both theClient and companyProjects in the above example are primary relation attributes and represent a direct relationship between the two dataclasses. However, relation attributes can also be built upon paths through relationships at several levels, including circular references. For example, consider the following structure:

Each employee can be a manager and can have a manager. To get the manager of the manager of an employee, you can simply write:

 var myEmp : cs.EmployeeEntity
 var manLev2 : string
 myEmp=ds.Employee.get(50)
 manLev2=myEmp.manager.manager.lastname

Assigning files to picture or blob attributes​

You can store images in picture attributes; similarly, you can store any binary data in blob attributes.

Qodly lets you assign either the data itself, i.e. an image or a blob object, or a reference to a file containing the data to the attribute. Only the file path is saved within the entity.

Thanks to this feature, you can reuse the same picture in multiple entities without duplicating it, organize the files the way you want, or use them outside of Qodly. Also, you can control the size of the data file.

The file reference can be:

  • a 4D.File object
  • a path in POSIX format

Example:

function createCompany(name : string, logo : 4D.File)

    var company : cs.CompanyEntity
    company = ds.Company.new()

    company.name = name 
        //assignment using a file object
    company.logo = logo 
        //assignment using a path
    company.datablob = "/RESOURCES/"+$name+"/data.bin"
    company.save()

Regardless of how the attribute is assigned (data itself or reference to a file), read access to the attribute is transparent from the application's point of view.

The file does not have to exist on disk at the time of assignment (no error is returned in this case). If the referenced file is not found when the attribute is read, a null value is returned.

tip

Qodly loads images and data into a local cache. If the referenced file is modified after it has been loaded, you must reassign it so that the modification is taken into account in the application.

Assigning values to relation attributes​

In the ORDA architecture, relation attributes directly contain data related to entities:

  • An many-to-one type relation attribute (relatedEntity kind) contains an entity
  • A one-to-many type relation attribute (relatedEntities kind) contains an entity selection

Let's look at the following (simplified) structure:

In this example, an entity in the "Employee" dataclass contains an object of type Entity in the "employer" attribute (or a null value). An entity in the "Company" dataclass contains an object of type EntitySelection in the "staff" attribute (or a null value).

To assign a value directly to the "employer" attribute, you must pass an existing entity from the "Company" dataclass. For example:

 var emp : cs.EmployeeEntity
 emp=ds.Employee.new() // create an employee
 emp.lastname="Smith" // assign a value to an attribute
 emp.employer=ds.Company.query("name =:1","4D")[0]  //assign a company entity
 emp.save()

You can also directly get the "one" related entity through its primary key value (number or string). For example:

 var emp : cs.EmployeeEntity
 emp=ds.Employee.new()
 emp.lastname="Wesson"
 emp.employer=ds.Company.get(2)
  //gets the Company entity with primary key value 2
  //assigns it to the employee
 emp.save()

This is particularly useful when you are importing large amounts of data from a relational database. This type of import usually contains an "ID" column, which references a primary key that you can then assign to a relation attributes.

You can assign or modify the value of a "one" related entity attribute from the "many" dataclass directly through the related attribute. For example, if you want to modify the name attribute of a related Company entity of an Employee entity, you can write:

 var emp : cs.EmployeeEntity
 emp=ds.Employee.get(2) // load the Employee entity with primary key 2

 emp.employer.name="4D, Inc." //modify the name attribute of the related Company
 emp.employer.save() //save the related attribute
  //the related entity is updated

Creating an entity selection​

You can create an object of type entity selection as follows:

You can simultaneously create and use as many different entity selections as you want for a dataclass. Keep in mind that an entity selection only contains references to entities. Different entity selections can contain references to the same entities.

note

An entity selection is usually unordered, but it can be ordered (see Ordered or unordered entity selection).

Entity selections and Storage attributes​

All storage attributes (string, number, boolean, date) are available as properties of entity selections as well as of entities. When used in conjunction with an entity selection, a scalar attribute returns a collection of scalar values. For example:

 var locals : cs.PersonSelection
 var localEmails : collection
 locals=ds.Person.query("city = :1","San Jose") //entity selection of person
 localEmails=locals.emailAddress //collection of email addresses (strings)

Entity selections and Relation attributes​

In addition to the variety of ways you can query, you can also use relation attributes as properties of entity selections to return new entity selections. For example, consider the following structure: 

 var myParts : cs.PartSelection
 var myInvoices : cs.InvoiceSelection
 myParts=ds.Part.query("ID < 100") //Return parts with ID less than 100
 myInvoices=myParts.invoiceItems.invoice
  //All invoices with at least one line item related to a part in myParts

The last line will return in myInvoices an entity selection of all invoices that have at least one invoice item related to a part in the entity selection myParts. When a relation attribute is used as a property of an entity selection, the result is always another entity selection, even if only one entity is returned. When a relation attribute is used as a property of an entity selection and no entities are returned, the result is an empty entity selection, not null.

Shareable or alterable entity selections​

An entity selection can be shareable (readable by multiple processes, but not alterable after creation) or alterable (supports the .add() function, but only usable by the current process).

Properties​

A shareable entity selection has the following characteristics:

  • it can be stored in a shared object or shared collection, and can be passed as parameter between several processes or workers;
  • it can be stored in several shared objects or collections, or in a shared object or collection which already belongs to a group;
  • it does not allow the addition of new entities. Trying to add an entity to a shareable entity selection will trigger an error (1637 - This entity selection cannot be altered). To add an entity to a shareable entity selection, you must first transform it into a non-shareable entity selection using the .copy() function, before calling .add().
note

Most entity selection functions (such as .slice(), .and()...) support shareable entity selections since they do not need to alter the original entity selection (they return a new one).

An alterable entity selection has the following characteristics:

  • it cannot be shared between processes, nor be stored in a shared object or collection. Trying to store a non-shareable entity selection in a shared object or collection will trigger an error (-10721 - Not supported value type in a shared object or shared collection);
  • it accepts the addition of new entities, i.e. it is supports the .add() function.

How are they defined?​

The shareable or alterable nature of an entity selection is defined when the entity selection is created (it cannot be modified afterwards). You can know the nature of an entity selection using the .isAlterable() function.

A new entity selection is shareable in the following cases:

Example: 

var myComp : cs.CompanyEntity
var employees : cs.EmployeeSelection
myComp=ds.Company.get(2) //myComp does not belong to an entity selection
employees=myComp.employees //employees is shareable

A new entity selection is alterable in the following cases:

Example:

var toModify : cs.CompanySelection
toModify=ds.Company.all().copy() //toModify is alterable

A new entity selection inherits from the original entity selection nature in the following cases:

  • the new entity selection results from one of the various ORDA class functions applied to an existing entity selection (.query(), .slice(), etc.) .
  • the new entity selection is based upon a relation:
    • entity.attributeName (e.g. "company.employees") when attributeName is a one-to-many related attribute and the entity belongs to an entity selection (same nature as .getSelection() entity selection),
    • entitySelection.attributeName (e.g. "employees.employer") when attributeName is a related attribute (same nature as the entity selection),
    • .extract() when the resulting collection contains entity selections (same nature as the entity selection).

Examples:

var highSal, lowSal : cs.EmployeeSelection
var comp, comp2 : cs.CompanySelection
highSal=ds.Employee.query("salary >= :1", 1000000)   
    //highSal is shareable because of the query on dataClass
comp=highSal.employer //comp is shareable because highSal is shareable

lowSal=ds.Employee.query("salary <= :1", 10000).copy() 
    //lowSal is alterable because of the copy()
comp2=lowSal.employer //comp2 is alterable because lowSal is alterable

Sharing an entity selection between processes (example)​

You work with two entity selections that you want to pass to a worker process so that it can send mails to appropriate persons:


var paid,unpaid : cs.InvoicesSelection
//We get entity selections for paid and unpaid invoices
paid=ds.Invoices.query("status=:1", "Paid")
unpaid=ds.Invoices.query("status=:1", "Unpaid")

//We pass entity selection references as parameters to the worker
callWorker("mailing", "sendMails", paid, unpaid)

The sendMails method:


 declare (paid : cs.InvoicesSelection, unpaid : cs.InvoicesSelection)
 var invoice : cs.InvoicesEntity
 
 var server, transporter, email, status : object
 
  //Prepare emails
 server=newObject()
 server.host="exchange.company.com"
 server.user="myName@company.com"
 server.password="my!!password"
 transporter=smtpNewTransporter(server)
 email=newObject()
 email.from="myName@company.com"
 
  //Loops on entity selections
 forEach(invoice,paid)
    email.to=invoice.customer.address // email address of the customer
    email.subject="Payment OK for invoice # "+string(invoice.number)
    status=transporter.send(email)
 end
 
 forEach(invoice,unpaid)
    email.to=invoice.customer.address // email address of the customer
    email.subject="Please pay invoice # "+string(invoice.number)
    status=transporter.send(email)
 end

Entity Locking​

You often need to manage possible conflicts that might arise when several users or processes load and attempt to modify the same entities at the same time. Record locking is a methodology used in relational databases to avoid inconsistent updates to data. The concept is to either lock a record upon read so that no other process can update it, or alternatively, to check when saving a record to verify that some other process hasn’t modified it since it was read. The former is referred to as pessimistic record locking and it ensures that a modified record can be written at the expense of locking records to other users. The latter is referred to as optimistic record locking and it trades the guarantee of write privileges to the record for the flexibility of deciding write privileges only if the record needs to be updated. In pessimistic record locking, the record is locked even if there is no need to update it. In optimistic record locking, the validity of a record’s modification is decided at update time.

ORDA provides you with two entity locking modes:

  • an automatic "optimistic" mode, suitable for most applications,
  • a "pessimistic" mode allowing you to lock entities prior to their access.

Automatic optimistic lock​

This automatic mechanism is based on the concept of "optimistic locking" which is particularly suited to the issues of web applications. This concept is characterized by the following operating principles:

  • All entities can always be loaded in read-write; there is no a priori "locking" of entities.
  • Each entity has an internal locking stamp that is incremented each time it is saved.
  • When a user or process tries to save an entity using the entity.save() function, Qodly compares the stamp value of the entity to be saved with that of the entity found in the data (in the case of a modification):
    • When the values match, the entity is saved and the internal stamp value is incremented.
    • When the values do not match, it means that another user has modified this entity in the meantime. The save is not performed and an error is returned.

The following diagram illustrates optimistic locking:

  1. Two processes load the same entity.

  1. The first process modifies the entity and validates the change. The entity.save() fucntion is called. The Qodly engine automatically compares the internal stamp value of the modified entity with that of the entity stored in the data. Since they match, the entity is saved and its stamp value is incremented.

  1. The second process also modifies the loaded entity and validates its changes. The entity.save() function is called. Since the stamp value of the modified entity does not match the one of the entity stored in the data, the save is not performed and an error is returned.

This can also be illustrated by the following code:

 person1=ds.Person.get(1) //Reference to entity
 person2=ds.Person.get(1) //Other reference to same entity
 person1.name="Bill"
 result=person1.save() //result.success=true, change saved
 person2.name="William"
 result=person2.save() //result.success=false, change not saved

In this example, we assign to person1 a reference to the person entity with a key of 1. Then, we assign another reference of the same entity to variable person2. Using person1, we change the first name of the person and save the entity. When we attempt to do the same thing with person2, ORDA checks to make sure the entity on disk is the same as when the reference in person1 was first assigned. Since it isn't the same, it returns false in the success property and doesn’t save the second modification.

When this situation occurs, you can, for example, reload the entity from the disk using the entity.reload() function so that you can try to make the modification again. The entity.save() function also proposes an "automerge" option to save the entity in case processes modified attributes that were not the same.

Record stamps are not used in transactions because only a single copy of a record exists in this context. Whatever the number of entities that reference a record, the same copy is modified thus entity.save() operations will never generate stamp errors.

Pessimistic lock​

You can lock and unlock entities on demand when accessing data. When an entity is getting locked by a process, it is loaded in read/write in this process but it is locked for all other processes. The entity can only be loaded in read-only mode in these processes; its values cannot be edited or saved.

This feature is based upon two functions of the Entity class:

For more information, please refer to the descriptions for these functions.