Overview

Introduction

We developed Conneo Datastore several years ago to create a standardized and easy way for storing and getting data to and from SQL Server. With Conneo Datastore the database structure is the same for all customers and instances. Conneo Datastore is an object database similar to MongoDB, but with more strict rules and with less options.

Conneo Datastore is implemented as a HTTP API with endpoints like (some examples):

newObject saveProperty getObjectListTable getObjectListByFilter dropObject saveRelationship

The Conneo Datastore software is written in Delphi and is normally installed as an ISAPI DLL in Microsoft IIS Webserver on a customer's Windows server or on one of our own Windows servers.

Authentication

For more details about the authentication mechanism, see this page.

Database structure

A Conneo Database contains always these 3 tables:

Objects Properties Relationships

There are also some helper tables for storing ObjectNumbers and PropertyConfig

Objects

For every object one row is added to the Objects table.

Unique ObjectId

The auto-increment ObjectId column will provide a unique object ID over all object types.

ObjectType

Every object has an ObjectType. This is set on the initial newObject call. For every ObjectType a set of properties is configured in DSConfig.

It is possible to change the ObjectType of an object by using the changeObjectType endpoint. We use this mainly to archive objects to exclude them from lists. We change the objectType then to "ARCH_"

Object properties

One object could have one or more properties. Every property is stored as a row in the properties table.

The value in the OwnerObjectId column defines to which object the property belongs.

Property types

In Conneo Datastore we implemented these property types:

String (default) Integer Number (Double / Real) Date Datetime Time Currency Lookup List Switch (Boolean) Image EncodedString JsonArray FileLink StaticFileLink

For now it is enough to now that there are different types. In almost all cases you can write property values as string or number. The Conneo Datastore API will execute conversions automatically.

Uniqueness

The properties table has a constraint on OwnerObjectId and PropertyName to prevent multiple properties with the same name for the same object.

Automatic conversion

When a property is added or changed, Conneo Datastore executes an automatic conversion from the given string value to number value in the column PropertyAsDouble.

When a property is configured as a lookup value and some id is given as the property value, the string value of the lookup property is saved in the column PropertyAsString.

List properties

When a property is defined as List property, than it is possible to enter multiple list items in the DSConfig tool. A list item consists of a field Code and a field Value.

When a list property is used in a Conneo Flow form, it will be displayed as a dropdown element.

Lookup properties

It is possible to define a property as Lookup. By defining the lookup in the DSConfig tool it then is possible to save an objectId of a different object as the propertyValue. As mentioned above, the configured propertyValue of that object wil be saved in the PropertyAsString field. This will be declared later in more detail, for now it is enough to know that there are lookup properties.

Object linking

One of the most powerful features in Conneo Datastore is Object linking. Via the Relationship table we can connect objects in a parent-child relation.

Strict or Non-strict relations

On setting a relationship it is possible to mark the relationship as 'strict'. When an parent object has a strict relationship with one or more children, it is not possible to delete the object via a standard dropObject call. The objectTypes of the child(ren) should be explicit provided in the dropObject call to indicate that the object should be deleted as well as it's children. This is implemented to prevent orphan objects.

Object numbers

When configured, auto increasing object numbers will be calculated for every new object of a given objectType.

At this moment the only way to activate the use of object numbers is by adding a record to the ObjectNumbers table manually.

Ask one of the Dutch team members to do this if needed.

Table view

Because properties are saved as separate rows in the Properties table, there are some Pivot like functions built in to provide the stored properties as flat table data.

There are a number of endpoints for getting lists of data, like:

getObjectListTable getObjectListByFilter

It is possible to add filter values to limit the amount of data in the list.

List data is sent as JSON array containing objects per objectId with propertyName as attribute and propertyValue as value.