VisionR® Schema

Share
print

VisionR has its own database schema. It is used to define Objects an their interactions in an object oriented manner. Objects are organised in modules. Every object can have an arbitrary number of properties. There are different types of properties. All information on how the object interact with each other is also stored in the VisionR Schema.

VisionR® Schema
Description

VisionR Schema is used to describe database objects and their interactions in an object oriented manner. Object definitions are organised in modules. Every objectdefinition can have many properties of different types. Inheritance and overwriting of inherited properties is possible.

Properties can have calculated values. There are also properties of type "script", which can be used to describe custom workflows through execution on database or mask events, such as: "on update value", "on insert object", "on click" etc.

The VisionR Schema, unlike relational database schema, supports object inheritance. Internationalization is also part of the schema. It allows definitions of properties containing internationalized values, which means that a property can have many values in different languages simultaneously.

Role based access on the different schema levels is also defined directly in the schema. Interaction between objects in different modules is possible.

The VisionR Schema is self descriptive. It contains all information on databases used to store the data. System reports show the relational schemas, tables and columns, where VisionR property values are stored. Changing the storage place of a VisionR object definition or property in runtime is possible (e.g. on migration of the relational databases used by VisionR to store its data). Object definitions from the same module can be stored in different relational databases.

Many different relational databases from different vendors (Oracle, PostgreSQL, Microsoft SQL Server) can be used simultaneously to store user data in VisionR. The VisionR Schema puts all definitions together and presents all data structures in a comprehensible manner. Creating and editing of object definitions doesn't require knowledge of the SQL language, as the VisionR Schema translates automatically all queries in SQL and sends them to the database over JDBC.

Schema hierarchy

Definitions in the VisionR Schema are created on three major levels:

  • modules (similar to "schema" in the relational database model)
  • object definitions (similar to "table" in the relational database model)
  • properties (similar to "column" in the relational database model)
Object Relational Mapping (ORM)

All modules, object definitions and properties in the VisionR Schema are mapped to tables and columns in one or many relational databases. No special knowledge of relational databases is required to edit the VisionR Schema, because all translations between the object-oriented and the relational model are made automatically in the VisionR Core. The system keeps track of all used resources like: used databases, table names, column names, indexes, sequence numbers. The storage place for a VisionR object definition or property can be easily changed, pointing to another table or column in another database.

Templates

Object definitions can be be created using an existing object definition as a template. On creation of an object definition from a template all properties of the template are copied and can be overwritten with new property definitions.

Formats

Properties in the VisionR Schema can have an input and output format. Formats use masks to represent the values of a property in different ways on the user interface. Formats can be used to define the precision of a double value or to use a date/time property as storage for only time, only date or date and time together. There is a number of predefined formats. Custom formats can be created by the user.

Display types

Display types are objects in the VisionR Schema. Properties can be displayed in different ways on the user interface, using different display types. There is a predefined set of display types in the standard VisionR installation. Additional display types for relations, numbers, text areas etc. can be easily created by the user.

Inheritance

As the VisionR schema supports object oriented definitions, objects can build an inheritance structure. An object definition can have one parent. Object definitions without parents are called "top objectdefs". Object definitions, which are inherited show automatically all properties of the parents on the chain, leading to the top objectdef. Inherited properties can be overwritten and have a different data source or data type. Queries on object definitions, which are being inherited show all the properties and the data of the children object definitions and their instances.

Property definition

An object definition in the VisionR Schema can have an unlimited number of properties (only performance must be taken into account). There are different definitions for properties, depending on their data source, data type, the database events they trigger and so on. Properties can have default values on instance creation. Properties can also reference Scripts, so that a property becomes a dynamic component of the VisionR Schema. Every property definition contains information on input and output format.

Data sources

Properties in the VisionR Schema can have one of the following data sources:

  • input
  • relation
  • option
  • system
Data types

Properties with data source "input" must be defined to have a specific data type:

  • integer
  • double
  • varchar
  • text
  • date/time

There are some special data types, used in the VisionR Module "Graphics":

  • point
  • line
  • polygon
  • multipoint
  • multiline
  • multipolygon
Standard properties

Every object definition in the VisionR Schema must have an "id" property, which has to be an integer. Other standard properties like "code", "name", "insert_time", "update_time" etc. are usually copied from templates, when creating a new object definition.

Scripting

Properties can be directly reference to scripts, so that the call of property executes a VisionR Script in order to describe certain business logic. Script execution can be triggered by database events.

Database events

There are different database events, which trigger the execution of a linked VisionR Script:

  • on insert object
  • on update object
  • on delete object
  • on insert value
  • on update value
  • on delete value
  • on update relation (for bidirectional properties)

All events have access to the new and the old object / value.

Relation types

VisionR Schema supports all kinds of relation types as property definition. Unlike the definition of relations in a relational database, VisionR Schema supports also relations n:m and n:1. Normally those kinds of relations need the creation of a middle table in the relational database, in order to save the multiple values. In VisionR all the necessary tables and columns are created and indexed automatically by the system, so that administrators don't have to worry about complex database relation logics.

Relation type Reverse relation Property definition as XML
1:1 1:1 <is_unique>true</is_unique>
<is_multiple>false</is_multiple>
1:n n:1 <is_unique>false</is_unique>
<is_multiple>false</is_multiple>
n:1 1:n <is_unique>true</is_unique>
<is_multiple>true</is_multiple>
n:m n:m <is_unique>false</is_unique>
<is_multiple>true</is_multiple>
Bidirectional relations

VisionR Schema supports definitions of bidirectional relations. It means that each "relation"-property can have a reverse property in the related object definition, which shows automatically all referenced object instances. For example:

  • the object definition "person" has a property "department", referencing an instance from the object definition "department" (both objects are from the module "people")
  • the object definition "people.department" can have a reverse relation property "persons", which shows automatically all the persons, who reference a particular department
  • the property "people.department.persons" is the reverse relation of "people.person.department"
  • these two properties are together an example for a "bidirectional relation"
  • instances can be edited on both sides of this bidirectional relation, leading to the same result
  • "people.person.department" <> "people.department.persons" is a relation from type 1:n
  • "people.department.persons" <> "people.person.department" is a relation from type n:1
Relation paths

Properties with data source "relation" can be defined as relation paths. This means that the property shows a value of a referenced object, which is found somewhere on the chain of references on a relation path. For example a property of the object definition "invoice" can be set to display the value of the country code of the customer in it as follows: invoices.invoice.customer.country.code.

Relation filters

When a property in the VisionR Schema is of the type "relation", a relation filter can be set for it. Relation filters define which of the instances of the related object definition are displayed on the user interface, when the related instance is picked. Relation filters return script generated conditions, when the instances of the related object definition are displayed. Relation filter can also be used to set the values of newly created instances in the related object definition.

Relation display

Properties of the type "relation" point to whole objects and not only to single values. Relation display definitions are used to define a set of properties from the referenced object(s), which are displayed in the current object.

Internationalization

Every property in the VisionR Schema can be defined to have values in different languages simultaneously. As the Schema itself and the application masks and components are also described in the Schema, practiclly everythig in VisionR can be subject to translation in additional languages. The system library contains a list of all official languages from an ISO standard. All that has to be done in order to add a new language to the system, is to look for it in the list and to set it to "active".

Property types

There are over fifty settings for a property definition. The combination of those settings generates different property types, depending on what data should the property store and how it should appear on the user interface. Some of those settings are:

  • Is it internationalized? [Yes/No]
  • Does it have multiple values? [Yes/No]
  • Related object definition [Object]
  • Option set [Object]
  • Display type [Object]
  • Output format [Object]
  • Input format [Object]
  • Symbol set [Object]
  • ...and so on
Calculated properties

Properties from any data source (input, relation, option) and any data type (integer, text, ...) can have calculated values. There are predefined calculations like average, sum, count for number properties.

Default values

Properties in the VisionR Schema can have default values. On creating a new object instance the default value is automatically set to the property. Normal default values must be from the same data source and data type as the property. There are also calculated default values. On creating a new object instance the value of the property is calculated in a script. Using calculation allows for example different default values, depending on the user, which creates the new object instance.

Archiving

Objects in VisionR can be archived, instead of deleted. There is a special access permission for archiving data. Archived object instances are normally hidden. Archived data can be used in special reports.

Change protocol

All changes of property values can be preserved as history. In each object definition the most important properties keep a change protocol. Every property can be set to have a change protocol. Users must decide which properties are important to them and which changes will be analysed in the future. Change protocols contain normally the following informations: who made the change, time of change, new value, old value, comment. Change protocols can be customized.

Access permissions

User management in the VisionR Schema is role based. Access permissions can be used to define access rights for the user roles on data row level. Modules, object definitions, properties and object instances can have one or more of the following permissions:

  • read
  • insert
  • update
  • delete
  • execute
  • archive
  • copy
Encryption

Properties with data types varchar and text can be saved in the database using encryption. There is an encryption object in the VisionR Schema, containing predefined encryption algorithms. Encrypted property values are normally used for passwords.

Sessions

Session informations (like session begin, user, logout time etc.) are stored in the database. All expired sessions are stored in a session log. Active and expired sessions can be displayed by using a database query.

Transactions

VisionR has its own transaction mechanism. Changes in the values of properties in the VisionR Schema can be made using a transaction. Transactions can be switched on and off for object definitions and properties. Database transactions in the relational databases used are normally closed immediately, in order to free the resources for other applications. Transactions are not fully implemented in the current version of VisionR.

Object hierarchies

Object definitions in the VisionR Schema can be displayed in different hierarchy trees. An object hierarchy defines the relation path, in which the instances of an object definition must be displayed. For example the "contract" objects can be viewed by the user in different ways:

  • Customer hierarchy: country > customer > contracts
  • Building hierarchy: country > property > building > contracts

Users can switch between the predefined hierarchy trees for an object definition on the user interface in order to display the viewed object, grouped by different criteria.

Examples for property definitions
Property type Description Property definition as XML
single integer can contain one indexed value with data type "integer" <data_source>input</data_source>
<data_type>integer</data_type>
<is_multiple>false</is_multiple>
<is_inmaintbl>true</is_inmaintbl>
<index_code>123</index_code>
single double can contain one value with data type "double" with 2 places after the comma <data_source>input</data_source>
<data_type>double</data_type>
<is_multiple>false</is_multiple>
<is_inmaintbl>true</is_inmaintbl>
<input_format>input_format_double_2_decimal_places</input_format>
<output_format>output_format_double_2_decimal_places</output_format>
single varchar can contain one value with data type "varchar" with up to 500 chars <data_source>input</data_source>
<data_type>varchar</data_type>
<is_multiple>false</is_multiple>
<is_inmaintbl>true</is_inmaintbl>
<max_length>500</max_length>
single date can contain one value with data type "datetime", saving only the date part <data_source>input</data_source>
<data_type>datetime</data_type>
<is_multiple>false</is_multiple>
<is_inmaintbl>true</is_inmaintbl>
<input_format>default_input_format_date</input_format>
<output_format>default_input_format_date</output_format>
single time can contain one value with data type "datetime", saving only the time part <data_source>input</data_source>
<data_type>datetime</data_type>
<is_multiple>false</is_multiple>
<is_inmaintbl>true</is_inmaintbl>
<input_format>default_input_format_time</input_format>
<output_format>default_input_format_time</output_format>
single relation can contain one value as reference to a person, containing the whole person object <data_source>relation</data_source>
<related_objectdef>contacts.person</related_objectdef>
<is_multiple>false</is_multiple>
<is_inmaintbl>true</is_inmaintbl>
boolean option can contain one of the values true or false, defined in the option_set "boolean" <data_source>option</data_source>
<option_set>boolean</option_set>
<is_multiple>false</is_multiple>
<is_inmaintbl>true</is_inmaintbl>
<default_value>false</default_value>
multiple varchar can contain many values with data type "varchar", each of them with up to 500 chars <data_source>input</data_source>
<data_type>varchar</data_type>
<is_multiple>true</is_multiple>
<max_length>500</max_length>
multiple relation can contain many values as references to persons, containing the whole person objects <data_source>relation</data_source>
<related_objectdef>contacts.person</related_objectdef>
<is_multiple>true</is_multiple>
international text can contain translated values with data type "text" in all active languages <data_source>input</data_source>
<data_type>text</data_type>
<is_multiple>false</is_multiple>
<is_i18n>true</is_i18n>
reverse relation if this ist the property "persons" of the object definition "department", it contains all the persons from this department <data_source>relation</data_source>
<relation_parent>contacts.person.department</relation_parent>
encrypted varchar can contain one encrypted value with data type "varchar" with up to 20 chars <data_source>input</data_source>
<data_type>varchar</data_type>
<is_multiple>false</is_multiple>
<is_inmaintbl>true</is_inmaintbl>
<encryption>default_password_encryption</encryption>
<max_length>20</max_length>
and so on... The combination of data_source, data_type, is_multiple, is_unique, is_i18n results in many other possible property definitions...