Parents and children

I continue to develop the concept of a custom data warehouse, which can form the basis of the My Visual Multibase project. Below is a description of the implementation of three more possibilities:

• Inheritance of class properties
• Displaying child objects along with their parents
• Extension of basic data types: adding the “Date” type.

Property inheritance

Let me remind you that the principle of inheritance is one of the principles of the object-oriented approach in programming (you can read more about the development of this concept in the article “The Evolution of Abstractions”). And this mechanism really helps save time when creating child classes. Let’s look at this with an example.

It is quite obvious that the ancestor class must have at least two common properties that will be useful to all descendants:

• Unique identificator
• Name

The unique identifier is implemented as an object.id field, which is not editable. But the name is stored in the cproperty table, along with the other properties of the object. This is necessary so as not to further complicate the methods for generating table views, editing forms and filling out drop-down lists for reference values.

It should be noted that only this property has the “Object Display Name” checkbox checked. The probability that the root object can have other properties is non-zero, so let it be for now.

When describing a geographical object, the “Name” property is already present in the list of properties (1) and is highlighted in italics, which indicates the inheritance of this property from an ancestor. All that remains is to add a new property – “Area”.

The “Country” class inherits two properties: “Name” and “Area”, and has its own property “Capital”, which is a reference to an object of the “City” class.

In order for this mechanism to work, changes were required in the procedures responsible for displaying the list of properties: in tabular form on the “Properties” tab; list of columns on the “Objects” tab; list of editing components on the efmObject form. Now, instead of filtering properties by a specific class, you filter by a list of IDs, which contains the ID of the displayed class and the IDs of all its ancestors.

We also had to replace the dynamic form dtfCProperty with the static frmCProperty, in which data is displayed using an SQL query

select
  cproperty.id,
  cproperty.id_class,
  cproperty.orderNum,
  cproperty.name,
  class.name as  className,
  ptype.name  as ptypeName,
  cproperty.description
from cproperty
left join class on class.id = cproperty.id_class1
left join ptype on ptype.id = cproperty.id_ptype
where id_class in ( {edtIDMaster} )
order by orderNum  Code language: SQL (Structured Query Language) (sql)

The frmCProperty.edtIDMaster component contains a list of IDs. The current implementation of the class tree does not allow generating this list using an SQL query (which suggests switching to an interval tree), so you have to create it using a script using the TdbTreeView.GetParent() method:

procedure frmCProperty_btnUpdate_OnClick (Sender: TObject; var Cancel: boolean);
var
  tmpTree: TdbTreeView;
  s: string;
  tmpIndex: integer;
  tmpForm: TForm;
  tmpLevel: integer;
begin
  tmpForm := GetFormByName('dtfClass_Tree');
  if tmpForm <> nil then
  begin
    tmpTree := TdbTreeView( Form_GetDataViewer( tmpForm ) );
    // collect IDs from the node and all parent nodes
    tmpIndex := tmpTree.SelectedRow;
    if tmpIndex = -1 then
      frmCProperty.edtIDMaster.Text := '-1'
    else
    begin
      s := '';
      repeat
        if s <> '' then
          s := s+','
        else
          frmCProperty.edtIDClass.Value := tmpTree.dbIndexToID( tmpIndex );
        s := s + IntToStr( tmpTree.dbIndexToID( tmpIndex ) );
        tmpIndex := tmpTree.GetParent( tmpIndex );
      until tmpIndex < 0;
      frmCProperty.edtIDMaster.Text := s;
    end;
    // collecting child IDs
    tmpIndex := tmpTree.SelectedRow;
    if tmpIndex = -1 then
      frmCProperty.edtIDChilds.Text := '-1'
    else
    begin
      tmpLevel := tmpTree.GetLevel( tmpIndex );
      s := '';
      repeat
        if s <> '' then
          s := s+',';
        s := s + IntToStr( tmpTree.dbIndexToID( tmpIndex ) );
        inc(tmpIndex);
      until (tmpIndex = tmpTree.RowCount) or ( tmpTree.GetLevel( tmpIndex ) <=  tmpLevel );
      frmCProperty.edtIDChilds.Text := s;
    end;
  end;
end;
Code language: JavaScript (javascript)

In the same script, a list of child nodes is generated, which uses the TdbTreeView.GetLevel() method. This list will be useful to us for displaying objects.

Displaying Child Objects

We modify frmObject_btnUpdate_OnClick() so that the list of objects displays objects of both the selected class itself and all its descendants:

procedure frmObject_btnUpdate_OnClick (Sender: TObject; var Cancel: boolean);
// update table view
var
  tmpIDClass: integer;
  tmpDataSet: TDataSet;
  tmpSQL: string;
  tmpFields : string;
  tmpJoins: string;
  tmpCount: integer;
  tmpCaptions: string;
  tmpTableAlias: string;
  tmpButton: TdbButton;
  tmpIDClassList: string;
  tmpIDChildList: string;
begin
  frmObject.labIDClass.Caption := 'Change'; // block the event processing of frmObject_tgrMain_OnColumnResize
  // a query is built to select data - all objects of the specified class
  tmpIDClass := Form_GetDataViewer( GetFormByName('dtfClass_Tree') ).dbItemId;
  frmObject.labIDClass.Tag := tmpIDClass; // запоминаем класс
  tmpIDClassList := frmCProperty.edtIDMaster.Text; // список классов
  tmpIDChildList := frmCProperty.edtIDChilds.Text; // список классов
  tmpSQL := 'SELECT * FROM cproperty WHERE id_class in ('+tmpIDClassList+') ORDER BY orderNum ';
  SQLQuery(tmpSQL,tmpDataSet);
  tmpFields := 'object.id';
  tmpJoins := '';
  tmpCaptions := 'delete_col'; // не отображать
  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 in ('+tmpIDChildList+')';
  tmpButton := TdbButton(Sender);
  tmpButton.dbSQL := tmpSQL;
  tmpButton.dbListFieldsNames := tmpCaptions;
end;Code language: Delphi (delphi)

Now in the list of objects you can see how objects of the selected class…

…as well as all objects of child classes:

Date data type

Add the type to the visual components directory.

Then we add a new class to store date data.

Now we add the procedure for generating components on the object editing form, adding the creation of a component for entering the date.

By the way, we had to change the concept of building the form, namely, to abandon the removal of unnecessary components in favor of their reuse. This is due to a bug within MVDB that causes the TdbComboBox.Free method to throw an Access Violation if you ever open the combobox’s dropdown list. However, there is also a plus in this approach – the form opens faster and memory fragmentation is reduced.

procedure efmObject_OnShow (Sender: TObject; Action: string);
// display the editing 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;
  tmpIDClassList: string;
  tmpDataEdit : TdbDateTimePicker;
begin
  tmpIDClassList := frmCProperty.edtIDMaster.Text; // список классов
  tmpForm := TForm(Sender);
  tmpParent := efmObject.panEdit;
  if Action = 'NewRecord' then
  begin
    efmObject.cmbClass.dbItemID := Form_GetDataViewer( GetFormByName('dtfClass_Tree') ).dbItemId;
  end;
  // hide all components
  for i := tmpParent.ControlCount - 1 downto 0 do
  begin
    tmpParent.Controls[i].Visible := False;
  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 in ('+tmpIDClassList+') ORDER BY orderNum ';
  SQLQuery(tmpSQL,tmpDataSet);
  while not tmpDataSet.EOF do
  begin
    tmpControlID := tmpDataSet.FieldByName('id_uicontrol').asInteger;
    // метка
    FindC(tmpForm,'labData_'+intToStr(tmpCount),tmpLabel,False);
    if tmpLabel = nil then
      tmpLabel := TdbLabel.Create( tmpForm );
    with tmpLabel do
    begin
      visible := True;
      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
        Font.Style := fsBold
      else
        Font.Style :=0;
    end;
    // Edit
    FindC(tmpForm,'edtData_'+intToStr(tmpCount),tmpEdit,False);
    if tmpEdit = nil then
      tmpEdit := TdbEdit.Create( tmpForm );
    with tmpEdit do
    begin
      visible := True;
      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 ) );
      dbCurrency := False;
      NumbersOnly := False;
      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';
      Font.Style := 0;
    end;
    // ComboBox
    if tmpControlID = 2 then 
    begin
      //
      FindC(tmpForm,'cmbData_'+intToStr(tmpCount),tmpComboBox,False);
      if tmpComboBox = nil then
        tmpComboBox := TdbComboBox.Create( tmpForm );
      with tmpComboBox do
      begin
        visible := True;
        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.is_name = 1 ) AND object.id_class = '+tmpDataSet.FieldByName('ClassID').asString+' ORDER BY 2';
        dbUpdate;
        if tagString <> '' then // если есть ссылочное значение, то синхронизировать выпадающий список
          dbItemID := StrToInt(tagString);
        onChange := 'efmObject_cmbEdit_OnChange';
        Font.Style :=0;
      end;
    end;
    // Date
    if tmpControlID = 5 then 
    begin
      FindC(tmpForm,'dtpData_'+intToStr(tmpCount),tmpDataEdit,False);
      if tmpDataEdit = nil then
      begin
        tmpDataEdit := TdbDateTimePicker.Create( tmpForm );
        AssignEvents(tmpDataEdit);
      end;
      with tmpDataEdit do
      begin
        visible := True;
        name := 'dtpData_'+intToStr(tmpCount);
        parent := tmpParent;
        Font.Size := 11;
        top := tmpCount * 50 + tmpLabel.Height;
        left := 8;
        width := 300;
        ShowCheckBox := True;
        if ValidDate( tmpEdit.Text ) then
        begin
          DateTime := StrToDate( tmpEdit.Text );
          Checked := True;
        end
        else
          Checked := False;
        //
        dbOnChange := 'efmObject_dtpEdit_OnChange';
        Font.Style :=0;
      end;
    end;
    inc(tmpCount);
    tmpDataSet.Next;
  end;
  tmpDataSet.Free;
  tmpForm.ClientHeight := tmpCount * 50 + 48; // adjust the height of the form
end;
Code language: Delphi (delphi)

The date can be entered or not entered – the TdbDateTimePicker component checker is responsible for this

Column width

The column width of a class table view is no longer tied to any specific class property, since different classes can now have the same (inherited) properties. This circumstance led to a change in the data structure and the algorithm for saving data on setting column widths, which are now stored in the class.col_widths field.

The data is stored as a string with integer values separated by commas. Modified procedures for loading and saving the setting are given below.

procedure frmObject_tgrMain_OnChange (Sender: TObject);
// updating data in a table
var
  tmpIDClass: integer;
  tmpSQL: string;
  tmpColumn: integer;
  s: string;
  tmpColWidth: array of string;
begin
  // get ID
  tmpIDClass := frmObject.labIDClass.Tag;
  // read column widths from base
  tmpSQL := 'SELECT COALESCE(col_widths,"") FROM class WHERE id = '+IntToStr(tmpIDClass);
  s := SQLExecute(tmpSQL);
  tmpColWidth := SplitString(s,',');
  for tmpColumn := 0 to frmObject.tgrMain.Columns.Count - 1 do
  begin
    if tmpColumn < length(tmpColWidth) then
      frmObject.tgrMain.Columns[tmpColumn].Width := StrToInt(tmpColWidth[tmpColumn] )
    else
      frmObject.tgrMain.Columns[tmpColumn].Width := 200;
  end;
  frmObject.labIDClass.Caption := ''; // unlock frmObject_tgrMain_OnColumnResize
end;

procedure frmObject_tgrMain_OnColumnResize (Sender: TObject; ACol: Integer);
// column resize
var
  tmpIDClass: integer;
  tmpSQL : string;
  i: integer;
  s: string;
begin
  if frmObject.labIDClass.Caption = '' then
  begin
    s := '';
    for i:=0 to frmObject.tgrMain.Columns.Count - 1 do
      s := s + IntToStr(frmObject.tgrMain.Columns[i].Width) + ',';
    delete(s,length(s),1);
    // remember the widths of all columns in the database
    tmpIDClass := frmObject.labIDClass.Tag; //  класс
    tmpSQL := 'UPDATE class SET col_widths = "'+s+'" WHERE id = '+IntToStr(tmpIDClass);
    SQLExecute(tmpSQL);
  end;
end;
Code language: Delphi (delphi)

Results

The Data Keeper project is developing, algorithms for generating and retrieving data are being refined.

What should be done:

• Set property support
• Interval class tree
• Searching for objects by a given property value (several properties)
• Displaying dependent objects (objects for which this object is the value of a property)
• Creation of arbitrary views (using an analogue of SQL for OOP).
• 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 (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.
• Manually adjusting the position and size of components on the class editing form.
• Adding new reference values directly from the object editing form.

Links

• Data Keeper 1.1 – project sources