A repository for everything

My Visual Database (MVDB) allows you to create applications to store various data and in the volumes that the user needs. But if the number of tables for storage exceeds a dozen, then creating table view display forms and editing forms becomes a boring routine. And when the number of forms is more than 30, the application startup time increases noticeably, since MVDB creates each form when the program starts. When changing the data structure, it is also necessary to edit the corresponding forms.

My Visual Multibase (MVM) will be free from the above disadvantages as it has UI forms tightly coupled to the data structure and automatically changes when changes are made to the data schema. However, I don’t yet have a clear understanding of whether MVM will be in demand in a market that is crowded with various development tools.

I present to your attention Data Keeper – a program for organizing the storage of information of any structure. Program features:

• Object-oriented approach
• The user determines the structure of the stored data himself
• The table view form and editing form are created automatically

Project basis

The project “Developer’s Handbook” was taken as a basis, in which the technology for dynamically creating tabular forms (DTF) has proven itself well.

Most of the program’s functionality is concentrated in modules, the editing of which must be done in a third-party text editor (Norepad++), since MVDB does not have a built-in multi-page script editor.

Data structure

The idea of using a relational DBMS as an object DBMS is not a very good one, since most likely there will be performance problems when processing large amounts of data, but this project will be the very test of the speed and ease of use of existing DBMSs in a new capacity.

The entire data structure is based on the concept of classes, objects and properties

Class

A class is an abstract description of an object. The class has a name (name). The classes form a hierarchy (parent_id), in which all others inherit from the base class. Since the properties of objects are set through classes, to edit them you need to specify the type of component (id_uicontrol). The nullID field is used to implement a mechanism for switching the display of a tree to a linear list, and the description field is used to store an extended description of the class.

CProperty

A class property is an abstract description of an object property that belongs to a specific class (id_class). It has a name and a description. The property marked with the is_name flag is used to store the display name of an object in cases where the object is the value of a property of another object (reference value). The orderNum, visible and col_width fields are responsible for the order, visibility and width of the columns for the tabular representation of objects of this class. The calculated fields vtype and ptype are used to conveniently display information in a tabular representation of class properties. A class property can be represented in an object by either a single value or a set. The ptype field is responsible for this feature. The type of data that can be in the property (id_class1) is a reference to a class, so simple types such as text, numbers, etc. also need to be described in the class hierarchy.

Note. DataKeeper 1.0 only supports single values.

Object

The object has only two fields so far – an identifier (id) and a link to the class (id_class). All other object properties, including the name, are stored in the oproperty table.

OProperty

Object properties are specific values of properties of an object’s class that define the qualities of the object. A property is associated with both an object (id_object) and a class property (id_cproperty). Data is stored in two formats: as text (value_s) and as a reference to an object (id_object1). The text is displayed in a table view (including to display the name of objects that are the value of a reference property, which improves system performance but violates normalization principles), and the reference is used to maintain data integrity.

Note. The orderNum field will henceforth be used as an array index for properties that are sets.

PType

A service table that defines the property value type. Contains two entries:

• Value (single value)
• Set (array of values)

UIControl

A service table with a list of user interface elements that should be used to edit object properties.

Note. In DataKeeper 1.0 this is an editable reference book, but should not be edited as this may cause the program to malfunction.

Forms

The project has several static forms:

• frmMain – main form
• frmClass – container form for displaying classes and objects – the main form of data storage
• frmObject – form for displaying objects
• efmClass – class edit form
• efmObect – object edit form
• efmCProperty – class property editing form
• efmUIControl – form for editing the directory of interface components

And there are also several dynamic forms, the creation of which is based on the description in the dforms.ini file:

• dfmClass_Tree – class tree
• dfmUIControl – list of UI components
• dfmCProperty – list of class property

Scripts

procedure frmObject_btnUpdate_OnClick (Sender: TObject; var Cancel: boolean);
// update display
var
  tmpIDClass: integer;
  tmpDataSet: TDataSet;
  tmpSQL: string;
  tmpFields : string;
  tmpJoins: string;
  tmpCount: integer;
  tmpCaptions: string;
  tmpTableAlias: string;
  tmpButton: TdbButton;
begin
  frmObject.labIDClass.Caption := 'Change'; // block the firing of frmObject_tgrMain_OnColumnResize
  // a query is built to select data - all objects of the specified class
  // get class ID
  tmpIDClass := Form_GetDataViewer( GetFormByName('dtfClass_Tree') ).dbItemId;
  frmObject.labIDClass.Tag := tmpIDClass; // remember the class
  tmpSQL := 'SELECT * FROM cproperty WHERE id_class = '+IntToStr(tmpIDClass)+' ORDER BY orderNum ';
  SQLQuery(tmpSQL,tmpDataSet);
  tmpFields := 'object.id';
  tmpJoins := '';
  tmpCaptions := 'delete_col'; // do not display this column
  tmpCount := 0;
  //
  while not tmpDataSet.EOF do
  begin
    if tmpDataSet.FieldByName('visible').asInteger = 1 then
    begin
      tmpTableAlias := 'OP_'+IntToStr(tmpCount);
      //
      if tmpFields <> '' then
        tmpFields := tmpFields + ', ';
      tmpFields := tmpFields + tmpTableAlias+'.value_s ';
      //
      tmpJoins := tmpJoins + 'LEFT JOIN oproperty '+tmpTableAlias+' ON '+tmpTableAlias+'.id_object = object.id AND '+tmpTableAlias+'.id_cproperty = '+tmpDataSet.FieldByName('id').asString+CR ;
      //
      if tmpCaptions <> '' then
        tmpCaptions := tmpCaptions + ', ';
      tmpCaptions := tmpCaptions + tmpDataSet.FieldByName('name').asString;
      //
      inc(tmpCount);
    end;
    tmpDataSet.Next;
  end;
  tmpDataSet.Free;
  tmpSQL := 'SELECT '+tmpFields+CR+' FROM object '+tmpJoins+CR+' WHERE object.id_class = '+IntToStr(tmpIDClass);
  tmpButton := TdbButton(Sender);
  tmpButton.dbSQL := tmpSQL;
  tmpButton.dbListFieldsNames := tmpCaptions;
end;
Code language: Delphi (delphi)

When you click the btnUpdate button (clicking is done using the built-in DTF mechanisms), the script assembles an SQL query in which each column is pulled from a separate selection, joined using the JOIN keyword. This takes into account the visibility of the properties and the order in which they appear, described when setting the class properties.

Since the number of columns with properties may differ for different objects, it is necessary to adjust their width after loading data into the component:

procedure frmObject_tgrMain_OnChange (Sender: TObject);
// updating data in a table
var
  tmpIDClass: integer;
  tmpDataSet: TDataSet;
  tmpSQL: string;
  tmpColumn: integer;
begin
  // get ID
  tmpIDClass := frmObject.labIDClass.Tag;
  // read column widths from database
  tmpSQL := 'SELECT COALESCE(col_width,100) as width FROM cproperty WHERE id_class = '+IntToStr(tmpIDClass)+' ORDER BY orderNum ';
  SQLQuery(tmpSQL,tmpDataSet);
  //
  tmpColumn := 0;
  while not tmpDataSet.EOF do
  begin
    frmObject.tgrMain.Columns[tmpColumn].Width := tmpDataSet.FieldByName('width').asInteger;
    inc(tmpColumn);
    tmpDataSet.Next;
  end;
  tmpDataSet.Free;
  frmObject.labIDClass.Caption := ''; // unlock frmObject_tgrMain_OnColumnResize
end;
Code language: Delphi (delphi)

We adjust the width directly in the table view of objects, and to save the values to the database, we use the onColumnResize event handler:

procedure frmObject_tgrMain_OnColumnResize (Sender: TObject; ACol: Integer);
// manually changing column width
var
  tmpIDClass: integer;
  tmpSQL : string;
begin
  if frmObject.labIDClass.Caption = '' then
  begin
    // store the column width in the database
    tmpIDClass := frmObject.labIDClass.Tag; //  class
    tmpSQL := 'UPDATE cproperty SET col_width = '+IntToStr(frmObject.tgrMain.Columns[ACol].Width)+' WHERE id_class = '+IntToStr(tmpIDClass)+' AND orderNum = '+IntToStr(ACol+1);
    SQLExecute(tmpSQL);
  end;
end;Code language: Delphi (delphi)

It is necessary to block writing to the database when changing the column width, which is caused by the algorithms of the MVDB itself, so that frmObject_tgrMain_OnColumnResize saves data only during manual editing. To do this, a special text label is written to the frmObject.labIDClass.Caption property.

A similar technology can be used in other projects so that the width settings are stored not in local files, but in the database, which can be useful if several users work at the same workstation, and each of them needs its own setting.

To build the editing form, information from the oproperty table is used.

The name of the property that is used to represent the object in drop-down lists for selection as a reference value is highlighted in bold (1). Data that was entered by the user in the current editing session is also shown in bold (2).

procedure efmObject_OnShow (Sender: TObject; Action: string);
// display edit form
var
  i: integer;
  tmpSQL: string;
  tmpIDClass: integer;
  tmpCount: integer;
  tmpLabel: TdbLabel;
  tmpEdit: TdbEdit;
  tmpComboBox: TdbComboBox;
  tmpForm: TForm;
  tmpParent: TdbPanel;
  tmpDataSet: TDataSet;
  tmpID: string;
  tmpControlID: integer;
begin
  tmpForm := TForm(Sender);
  tmpParent := efmObject.panEdit;
  if Action = 'NewRecord' then
  begin
    efmObject.cmbClass.dbItemID := Form_GetDataViewer( GetFormByName('dtfClass_Tree') ).dbItemId;
  end;
  // remove all components
  for i := tmpParent.ControlCount - 1 downto 0 do
  begin
    tmpParent.Controls[i].Free;
  end;
  tmpIDClass := efmObject.cmbClass.dbItemID;
  tmpCount := 0;
  // create the components that are needed to edit the properties of the current object
  tmpSQL := 'SELECT cproperty.id, cproperty.name, cproperty.is_name, class.name as cname, class.id_uicontrol, class.id as ClassID FROM cproperty LEFT JOIN class ON class.id = cproperty.id_class1 WHERE id_class = '+IntToStr(tmpIDClass)+' ORDER BY orderNum ';
  SQLQuery(tmpSQL,tmpDataSet);
  while not tmpDataSet.EOF do
  begin
    tmpControlID := tmpDataSet.FieldByName('id_uicontrol').asInteger;
    // label
    tmpLabel := TdbLabel.Create( tmpForm );
    with tmpLabel do
    begin
      parent := tmpParent;
      Font.Size := 11;
      top := tmpCount * 50;
      left := 8;
      name := 'labData_'+intToStr(tmpCount);
      Caption := tmpDataSet.FieldByName('name').asString;
      if tmpDataSet.FieldByName('is_name').asInteger = 1 then
      begin
        Font.Style := fsBold;
      end;
    end;
    // edit
    tmpEdit := TdbEdit.Create( tmpForm );
    with tmpEdit do
    begin
      name := 'edtData_'+intToStr(tmpCount);
      parent := tmpParent;
      Font.Size := 11;
      top := tmpCount * 50 + tmpLabel.Height;
      left := 8;
      width := 300;
      tag := tmpDataSet.FieldByName('id').asInteger; //
      tagString := VarToStr( SQLExecute('SELECT id FROM oproperty WHERE id_object = '+IntToStr( efmObject.btnSave.dbGeneralTableId  )+' AND id_cproperty = '+tmpDataSet.FieldByName('id').asString ) );
      text := VarToStr( SQLExecute('SELECT value_s FROM oproperty WHERE id_object = '+IntToStr( efmObject.btnSave.dbGeneralTableId  )+' AND id_cproperty = '+tmpDataSet.FieldByName('id').asString ) );
      if tmpControlID = 3 then // integer number
      begin
        dbCurrency := True;
        dbAccuracy := 0;
        Alignment := taLeftJustify;
      end;
      if tmpControlID = 4 then // real number
      begin
        NumbersOnly := True;
      end;
      onChange := 'efmObject_edtEdit_OnChange';
    end;
    //
    if tmpControlID = 2 then // drop list
    begin
      //
      tmpComboBox := TdbComboBox.Create( tmpForm );
      with tmpComboBox do
      begin
        name := 'cmbData_'+intToStr(tmpCount);
        parent := tmpParent;
        Font.Size := 11;
        top := tmpCount * 50 + tmpLabel.Height;
        left := 8;
        width := 300;
        tagString := VarToStr( SQLExecute('SELECT id_object1 FROM oproperty WHERE id_object = '+IntToStr( efmObject.btnSave.dbGeneralTableId  )+' AND id_cproperty = '+tmpDataSet.FieldByName('id').asString ) );
        dbSQL := 'SELECT object.id, oproperty.value_s FROM object LEFT JOIN oproperty ON oproperty.id_object = object.id WHERE oproperty.id_cproperty = ( SELECT id FROM cproperty WHERE cproperty.id_class = '+tmpDataSet.FieldByName('ClassID').asString+' AND cproperty.is_name = 1 ) AND object.id_class = '+tmpDataSet.FieldByName('ClassID').asString;
        dbUpdate;
        if tagString <> '' then // if there is a reference value, then synchronize the dropdown list
          dbItemID := StrToInt(tagString);
        onChange := 'efmObject_cmbEdit_OnChange';
      end;
    end;
    inc(tmpCount);
    tmpDataSet.Next;
  end;
  tmpDataSet.Free;
  tmpForm.ClientHeight := tmpCount * 50 + 48; // adjust the height of the form
end;Code language: PHP (php)

The components are arranged from top to bottom. If the property is a reference property, then a component with a drop-down list is added on top of the text input field.

Data saving occurs in several stages. First, the text from the drop-down list is transferred to the edit field:

procedure efmObject_btnSave_OnClick (Sender: TObject; var Cancel: boolean);
// save data
var
  i: integer;
  tmpParent: TdbPanel;
  tmpEdit: TdbEdit;
  tmpSQL: string;
  tmpForm: TForm;
  tmpCombo: TdbComboBox;
  tmpName: string;
begin
  CForm( Sender, tmpForm );
  // Enter data from the combobox into Text
  tmpParent := efmObject.panEdit;
  for i := 0 to tmpParent.ControlCount - 1 do
  begin
    if tmpParent.Controls[i] is TdbComboBox then
    begin
      tmpCombo := TdbComboBox(tmpParent.Controls[i]);
      tmpName := 'edt'+DeleteClassName(tmpCombo.Name);
      FindC( tmpForm, tmpName, tmpEdit );
      tmpEdit.Text := tmpCombo.Text;
    end;
  end;
end;Code language: JavaScript (javascript)

Then the main saving is performed – writing to the object table. This is implemented by setting the “Save” button.

Then changes to the data on the editing form are analyzed and, if necessary, recorded in the database:

procedure efmObject_btnSave_OnAfterClick (Sender: TObject);
// after the main save
var
  i: integer;
  tmpParent: TdbPanel;
  tmpEdit: TdbEdit;
  tmpSQL: string;
  tmpForm: TForm;
  tmpCombo: TdbComboBox;
  tmpLabel: TdbLabel;
  tmpID: string;
  tmpName: string;
begin
  // the values from each component are stored in a separate entry in the opropety table
  CForm( Sender, tmpForm );
  tmpParent := efmObject.panEdit;
  for i := 0 to tmpParent.ControlCount - 1 do
  begin
    if tmpParent.Controls[i] is TdbEdit then
    begin
      tmpEdit := TdbEdit(tmpParent.Controls[i]);
      // in some cases, a connection with the object that is the value of the property is maintained
      tmpName := 'cmb'+DeleteClassName(tmpEdit.Name);
      FindC( tmpForm, tmpName, tmpCombo, False );
      if tmpCombo = nil then
        tmpID := 'NULL'
      else
        tmpID := tmpCombo.SQLValue;
      // two options: adding and editing
      if tmpEdit.tagString = '' then // if there is no record ID, then add
      begin
        tmpSQL:= 'INSERT INTO oproperty (id_object,id_cproperty,value_s,id_object1 ) VALUES ('+IntToStr( efmObject.btnSave.dbGeneralTableId  )+','+IntToStr( tmpEdit.Tag )+',"'+tmpEdit.Text+'", '+tmpID+') ';
        SQLExecute(tmpSQL);
      end
      else // update
      begin
        if tmpEdit.Font.Style = fsBold then
        begin
          tmpSQL:= 'UPDATE oproperty SET value_s = "'+tmpEdit.Text+'", id_object1 = '+tmpID+' WHERE id = '+tmpEdit.tagString;
          SQLExecute(tmpSQL);
          // since in addition to links, the database stores text, you need to update it if this is the first parameter, which is the title
          tmpName := 'lab'+DeleteClassName(tmpEdit.Name);
          FindC( tmpForm, tmpName, tmpLabel );
          if tmpLabel.Font.Style = fsBold then
          begin
            tmpSQL := 'UPDATE oproperty SET value_s = "'+tmpEdit.Text+'" WHERE id_object1 = '+IntToStr( efmObject.btnSave.dbGeneralTableId  );
            SQLExecute(tmpSQL);
          end;
        end;
      end;
    end;
  end;
  frmObject.btnUpdate.Click; // update table view
end;Code language: Delphi (delphi)

Such an algorithm is necessary to reduce the number of calls to the database, which are already more than with the classic relational approach. In particular, when you edit a property that is used as the display name of an object, you must update all text values in the dependent properties.

A couple more procedures ensure that the user has edited the data. To do this, change the data display style to bold.

procedure efmObject_cmbEdit_OnChange (Sender: TObject);
// selecting a new value from the dropdown list
var
  tmpEdit: TdbEdit;
  tmpCombo: TdbComboBox;
  tmpForm: TForm;
begin
  CForm(Sender,tmpForm);
  tmpCombo := TdbComboBox(Sender);
  FindC(tmpForm,'edt'+DeleteClassName(tmpCombo.Name),tmpEdit);
  tmpCombo.Font.Style := fsBold;
  // set data update flag
  tmpEdit.Font.Style := fsBold;
end;

procedure efmObject_edtEdit_OnChange (Sender: TObject);
// entering a new value in the text field
var
  tmpEdit: TdbEdit;
begin
  tmpEdit := TdbEdit(Sender);
  // set data update flag
  tmpEdit.Font.Style := fsBold;
end;Code language: Delphi (delphi)

Results

So far, only basic functionality has been implemented: creating classes, creating and editing objects. In my plans I would like to implement all the features of the object-oriented approach:

• Inheriting class properties when creating a descendant class
• Set property support
• Displaying dependent objects (objects for which this object is the value of a property)
• Displays child objects along with their parents (currently only objects of the selected class are displayed).
• Creation of arbitrary views (using an analogue of SQL for OOP).
• Search (search for objects by a given property value)
• Filtering (automatic construction of a filter panel for objects of a given class based on a list of properties)
• Flag for mandatory completion of an object property (analogous to a required field)
• Flag of the uniqueness of a property of a class object (analogous to the uniqueness of a field value)
• Global uniqueness flag of an object property (or property with GUID generation)
• Extension of basic data types (date/time, yes/no, images/files, etc.)
• Adding control for data types (analogous to domains) – range of values for numbers and dates, text length for strings, etc.

Links

• Data Keeper 1.0 – project source