Switch to: V9V8V7V6V5

Properties of Database Objects

This article describes User Defined Properties of Database Objects - the unique object-relational feature of Valentina introduced in release 3.5. We will consider why it should exists, what benefits it brings to developers and how it can be used.

Note: At the time of written of this article, we do not know any other DBMS that have such feature.

Properties Overview

Almost any RDBMS (relational database management system) has objects such as Database, Table, Field, and Index, and these objects have some accessible properties. Table have properties such as RecordCount, FieldCount, Encoding and the like. Some properties are read-only, like RecordCount, while others are read-write, like MaxLength of field. But these properties are hard coded into the engine of database system and do not represent the granularity that modern developers are used to, with object oriented programming. Modern developers expect to be able to add their own properties to the objects that they program - what if you wanted to add properties to database objects?

The Relational Way

RDBMS do not allow add own properties of database objects - they simply operate only with Tables and Columns. So how you will solve this task using traditional relational database techniques? Let's ask some questions and try to answer them.

PROBLEM: Where do you put the logic of your properties?

Not in your database, but in your application code. RDBMS objects do not in themselves have accessible objects.

PROBLEM: What if database must be accessed from several APIs (e.g. C# and PHP)?

For each API, you have to duplicate the logic of your properties at each API layer, with or without using stored procedures of your database system. If your desktop applications are written in C#, but your web interface is through PHP 5, that means you double your investment in coding - coding the same logic.

For RDBMS that have stored procedures its possible try implement logic using them to avoid duplication in different APIs.

PROBLEM: Where would you need to store your properties using an RDBMS?

You would create additional tables to use them as storage for properties.

PROBLEM: What table structure would you end up using?

Lets try to use the single table to keep all properties for all objects:

CREATE TABLE tblProperties(
    fldPropName  VarChar(2044),
    fldPropValue VarChar(2044),
    fldObjectID  ???
)

But how do you specify, in a universal way, common objects like Database, Table and Field? Most if not all database systems do not support this.

Let's try to have tables of properties for each kind of database object we want and assign these properties: tblDatabaseProperties, tblTableProperties and tblFieldProperties.

CREATE TABLE tblDatabaseProperties(
    fldPropName  VarChar(2044),
    fldPropValue VarChar(2044)
)
 
CREATE TABLE tblTableProperties(
    fldPropName  VarChar(2044),
    fldPropValue VarChar(2044),
    fldTableName VarChar(2044)
)
 
CREATE TABLE tblFieldProperties(
    fldPropName  VarChar(2044),
    fldPropValue VarChar(2044),
    fldTableName VarChar(2044),
    fldFieldName VarChar(2044)          
)

This solution has many disadvantages and doesn't really give us a universal solution for interacting with different database objects. And it looks inefficient, especially for field properties.

The Object-Relational Way

The object-oriented perspective is very handy in mapping relational objects:

Table  = Class 
Record = Instance of Class
Field  = Attribute of Instance 

Now, let's remember that object-oriented languages have one more feature: attribute(s) of class, also known as the Static Variable of class.

There exists just one value of such Class Attribute for the whole class; it is stored not in the each class instance (record) but in some other place. Now we are getting someplace!

You can see that Properties of Database Objects is really the same thing.

Valentina Implementation

A new feature introduced with Valentina 3.5 implements this Object-Relational feature as part of the Valentina Database Model. Initially, you can access this through SQL and through the user interface of Valentina Studio Admin 3.5.

User Defined Properties of objects are stored into system tables, just as any other Schema object. System tables are always located in the .vdb volume of a Valentina database.

Valentina SQL for Properties

Valentina SQL offers special extension commands to handle properties of database objects. Here is how you GET, SET and SHOW properties:

get_property
    :	GET PROPERTY props_name_list OF DATABASE
    |	GET PROPERTY props_name_list OF TABLE table_name 
    |	GET PROPERTY props_name_list OF FIELD column_name 
    |	GET PROPERTY props_name_list OF LINK link_name 
    |	GET PROPERTY props_name_list OF SERVER 
    |	GET PROPERTY props_name_list OF CONNECTION
    |	GET PROPERTY props_name_list 

set_property
    :	SET PROPERTY props_name_list OF DATABASE TO value_list
    |	SET PROPERTY props_name_list OF TABLE table_name TO value_list
    |	SET PROPERTY props_name_list OF FIELD column_name TO value_list
    |	SET PROPERTY props_name_list OF LINK link_name TO value_list
    |	SET PROPERTY props_name_list OF SERVER TO value_list
    |	SET PROPERTY props_name_list OF CONNECTION TO value_list
    |	SET PROPERTY props_name_list TO value_list

show_properties
    |	SHOW PROPERTIES 
    |	SHOW PROPERTIES OF DATABASE
    |	SHOW PROPERTIES OF TABLE table_name
    |	SHOW PROPERTIES OF FIELD column_refernce
    |	SHOW PROPERTIES OF LINK link_name
    |	SHOW PROPERTIES OF SERVER
    |	SHOW PROPERTIES OF CONNECTION

Valentina API for Properties

As you can see, object properties are implemented and stored as strings:

Valentina  .Property( inPropName as String ) as String
VDatabase  .Property( inPropName as String ) as String
VTable     .Property( inPropName as String ) as String
VField     .Property( inPropName as String ) as String
VLink      .Property( inPropName as String ) as String
VServer    .Property( inPropName as String ) as String
VConnection.Property( inPropName as String ) as String

Properties in Valentina Studio

Valentina Studio Admin 3.5 also introduces support for User Defined Properties, making quick work of adding them to your database. Here is what you will find:

  • in the Tree View of the Schema Editor, User Defined Properties of objects appear as sub-folders of database objects.
  • You can see parameters of each property in the Property Inspector.
  • You can edit the value of each property in the Editor Pane.
  • You can create new properties and delete existing properties.

Object-Relational Model Advantages

A Universal Solution

SQL can be the common language of properties and its supported in all Valentina development environments - and it is all the same. You can also access these through native API commands as they are implemented.

In the Schema Where it Belongs

With the Relational solution, properties for are just one more table with records. They are stored in the area of regular DATA, but not into system tables.

For Valentina, properties really are the part of Schema. They are stored in the system tables in the .vdb file. If you utilize data stored across several volumes, you can minimize your risk. You can trash your volumes except for the .vdb and your properties are still there.

Less Code is Better Code

Since support of properties is available on all levels, you have no need to spend time to develop your own layer to create, delete, read, write, edit, validate, and search properties for each type.

Fewer Tables is Better

Many who have ported from other databases to Valentina have been able to reduce the size of their tables, or eliminate many tables entirely. User Defined Properties is just another way to keep your schema as simple and clean as possible.

Better Performance

Properties of objects are loaded when a database is opened, and stored in RAM. User Defined Properties take up very little storage and consequently, performance is going to be faster than digging into a table.

Examples of Usage

User Defined Properties allows you EXTEND DBMS in the way you need. For example, you can add the “CreationDate” property to a table, or “DevelopmentStatus”, or “Hidden” or anything else you can dream about…

Where to Go from Here?

User Defined Properties of Database Objects reduces your dependence on proprietary code from your IDE vendor and reducing the size of the API side of your application. If you are a Valentina Developer Network developer or working with Valentina Office Server, this means less work in deploying client solutions to both the desktop and the web. But even if you only write desktop apps, you will be future proofing your product by implementing them.