eSQL – Basics

yuuvis® RAD Subsystems

The yuuvis® RAD system is divided into different subsystems.

The division is based on content criteria. This allows different functions that do not necessarily belong together in terms of their content to not be implemented as a mixture in the new system. This results in many subsystems which each focus on a clearly defined aspect.

Each system offers interfaces which provide the functionality. This creates a highly flexible system that can also meet requirements that change in the long term.

The division is mainly conceptual. It also affects the Java project structure. This does not necessarily mean that the subsystems have to be exchangeable or maintainable individually or independently.

The individual subsystems are given in the following list.

System- and User-Defined Types

The generic management is not deployed exclusively by instances and their description by types for the project-specific types of a customer and transferred into the system using yuuvis® RAD designer but are also used intensively internally. "System types" are therefore differentiated from user-defined types.

The system types are anchored in the software. A new type of object often corresponds both with a Java class and an object type of the type system. This connection can be specified for system types, so that all associated instances are generated with the Java class. The same applies for relations. System types are identified by the prefix sys.

Inheritance

The class types of the type system follow an inheritance hierarchy. A derived type therefore receives all elements of the parent type. You can also add further elements to the derived type. The behavior properties defined for the type are transferred and can be overwritten if necessary. If there are assigned Java classes for generating instances, the same derivations are to be used there.

Multiple inheritance in the type system is possible. The derived type contains the elements of all parent types. If the parent types inherit an element from a common parent type, the element is only present once in the multiple-inheriting, derived type.

Types can only be inherited within domains.

In order to be able to better model and structure the types, there is the option of introducing abstract types. No instances can be created for these.

If you want to set up two very similar, related object types which only differ slightly, you can create a basic object type that contains the common elements and is defined abstractly. Each of both specific types is then defined as a derivation of the base type and supplemented with additional elements.

For example, there should be both incoming and outgoing invoices. Incoming invoices have the elements 'Amount,' 'Invoice date,' 'Comment,' and 'Document number.' Outgoing invoices have the elements 'Amount,' 'Invoice date,' 'Comment,' and 'Paid.'

In this case, first create the base type 'invoice'. Derive the object type 'incoming invoice' from this and add an element 'document number' of the String type. You can also derive the object type 'outgoing invoice' from the base type 'invoice' and add an element 'paid' of the Boolean type.

The derivation not only serves for simpler type management; it can also be used for expressing the relationship between types. As an object of a derived type is also an object of the base type, unbound queries are possible using the base type.

As all business objects derive from the DmsObject object type via which the object properties are available, an object property query over all types can be depicted by a simple query of the object type DmsObject. For example, the following query identifies both all incoming and outgoing invoices:

SELECT *
FROM [invoice]
WHERE ...

Note that a query of this type is very time-intensive, as all instances of all business object types are searched. For this reason, limit the query to one or several types if possible:

SELECT *
FROM [dms]:[sysObject]
WHERE ...

This example assumes that in the data modeling, not only business partners, but rather more special types, i.e., suppliers and customers, can be modeled, which in each case are derived from the business partner type. This derivation hierarchy can be visualized as follows.

The following statement can be used to query all customers:

SELECT *
FROM [dms]:[customer]

All suppliers can be determined in the same way:

SELECT *
FROM [dms]:[supplier]

However, the following statement identifies all business partners, i.e., all customers and suppliers:

SELECT *
FROM [dms]:[businesspartner]

The inheritability of types is very useful, as first and foremost it is more practical and avoids duplicated data storage.

Business Objects

Business objects are the central objects, i.e., documents and folders that you manage with yuuvis® RAD.

The system type dms:sysobject (DmsObject for short) defines the basic properties of a business object. DmsObject is an abstract type, meaning that no instances of this type can be created. The specific system types DmsObject, DmsDocument, and indirectly also DmsFolder derive from DmsRoot.

The individual business object types of an installation are set up as user-defined types in yuuvis® RAD designer and can be exchanged between systems in file form. All user-defined types inherit directly or indirectly from the DmsObject system type.

The elements of business objects are called metadata. For example, an invoice is classified by its date, number, and vendor/customer. The corresponding fields have at least one name, data type, and value range. The values are the data given, i.e., the invoice number '2389929438' or invoice date '04/24/2012.'

The elements inherited from the system type dms:sysobject are named object properties. Object properties such as an ID, creation date, or owner are managed by the system.

Binary content comprised of the actual document files e.g., scanned invoices, images, text, Microsoft Office documents, etc. can be assigned to business objects. So, for an invoice for example, the scanned paper document, the EDIFACT data, or an HL representation.

The metadata for user-defined object types can be individually designed in yuuvis® RAD designer. For business objects without assigned content, the metadata are the only usable content, for example personal information and address data of the Person type. They supplement business objects to which content has been assigned.

In terms of metadata and content, a business object is unchanged after saving. Before a change can be made, a new business object has to be created as a new version of the existing business object. This new business object also has the same status and can now be manipulated in the memory. As soon as it is saved, it is again unchangeable for its part.

Visibility

The visibility or rights system is implicitly considered for each eSQL query. Only the data that the querying user has permission to see is returned. The guide does not always refer to this.

For example, if it is claimed that an eSQL query delivers all objects of the person type, this always means that the query delivers all objects of the person type that are visible to the querying user.

First eSQL Queries

eSQL is a query language for identifying data and objects managed in yuuvis® RAD. The language is mostly identical to the widespread database query language SQL (structured query language) that is offered by nearly all relational database management systems (RDBMS) e.g., Oracle, Microsoft SQL Server, or IBM DB2.

As eSQL is a query language for an enterprise content management system, there are of course some differences in the formulation of statements and the identified results. The most important difference is that in contrast to relational databases, no physical tables are queried, but instead "types" of the yuuvis® RAD system. These types can either be specified by the system or caused by the special data model within a project. eSQL thus abstracts and hides the physical data storage, so that in a very easy way only the interesting data is queried, without needing to know how these are saved.

Thus

SELECT [p]
FROM [person] AS [p]

delivers all objects of the person type and

SELECT [s]
FROM [sysobject] AS [s]

delivers all objects of the system type sysobject.

eSQL is flexible in many areas. The first example can be formulated in the following ways, for example.

SELECT p FROM person AS p
SELECT p FROM person p
SELECT p FROM dms:person AS p
SELECT [p] FROM [dms]:[person] AS [p]
SELECT p FROM [dms]:person AS [p]
SELECT person FROM [person]
SELECT per FROM dms:person AS per
SELECT * FROM [person] AS [p]
SELECT $1 FROM [person] AS [p]