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 exist, what benefits it brings to the developers and how it can be used.

Note: At the time of writing this article we do not know any other DBMS with such feature.

Almost any RDBMS (relational database management system) has objects such as Database, Table, Field, and Index, and these objects have some accessible properties. Table has such properties as RecordCount, FieldCount, Encoding and the like. Some properties are read-only, like RecordCount, while other are read-write, like MaxLength of the field. But those 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?

RDBMS do not allow to add the properties of database objects - they simply operate only with Tables and Columns. So how will you 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 have accessible objects in themselves.

PROBLEM: What if the 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 to try to 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 a 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 the field properties.

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.

A new feature introduced with Valentina 3.5 implements this Object-Relational feature as the 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. The system tables are always located in the .vdb volume of a Valentina database.

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

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

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.

SQL can be the common language of properties and it is 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.

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

For Valentina, properties 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.

Since support of properties is available at 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.

Many of those who ported from other databases to Valentina became 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.

The properties of objects are loaded when the 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.

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

User Defined Properties of Database Objects reduces your dependence on proprietary code from your IDE vendor and reduces the size of the API side of your application. If you are a Valentina Developer Network developer or work 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 proof your product by implementing them.