or Multiple value

In some cases, an object property is a set of reference values. For example, a book may have several authors, or a computer game may belong to several genres at once. In this case, a value subtype called “set” will help us out.

On the database side, everything is already ready to store such values. Let’s look at the oproperty table again.

Each entry in this table is one value of a specific property of the object, including:

• value_s – text representation of the value
• id_object1 – object reference

It is worth noting that the value_s field stores not only the property value itself (text, number or date), but also a convenient form of representing the reference property – the name of the object. In the case of a multiple value, this field stores a list of the names of objects included in the set of selected values, and each link is stored in a separate record with the same id_object and id_cproperty attributes as the record with the visualization of the set. To distinguish the property value from the values of multiple references, the orderNum field is used:

• NULL – the record is used to store the displayed property
• <order number> – the record contains an element of multiple meaning.

Now the orderNum field is essentially used as a flag, but in the future we can add functionality related to the order of the elements of the set.

Application improvements

Since Data Keeper is created on My Visual Database, sometimes you have to deal with limitations and errors in the operation of this platform that do not allow you to realize all your ideas. In particular, the TdbComboBox component is designed to be a component that has the ability to display multiple values. But it has no desire to work correctly under Windows 10, as a result of which I had to abandon its use in the multiple value editing mode. Also, an attempt to create a surrogate component failed due to limitations of the MVDB scripting language. And since I was still going to create the ability to alternatively select reference values through a modal form, we’ll start with it, immediately adding functionality for working with multiple values.

frmObject_Sel

The form for selecting values turned out to be similar to the form with a list of instances of the frmObject classes. At least the algorithm for generating a table view is the same, which forced me to design it as a separate procedure btnPrepareOject(), which is passed a button for displaying the result of an SQL query in the table, the ID of the object class, as well as a flag for the need to add a field with checkers – an element multiple choice controls.

procedure btnPrepareOject( AButton: TdbButton; AIDClass: integer; ACheckBox:Boolean = False);
// generating a table view of a class
var
  tmpParentList: string;
  tmpChildList: string;
  tmpDataSet: TDataSet;
  tmpSQL: string;
  tmpFields : string;
  tmpJoins: string;
  tmpCount: integer;
  tmpCaptions: string;
  tmpTableAlias: string;
begin
  GetChildAndParent( AIDClass, tmpParentList, tmpChildList); // get a list of parent and child IDs
  tmpSQL := 'SELECT * FROM cproperty WHERE id_class in ('+tmpParentList+') ORDER BY orderNum ';
  SQLQuery(tmpSQL,tmpDataSet);
  tmpFields := 'object.id';
  if ACheckBox then
    tmpFields := tmpFields + ',"$checkbox"';
  tmpJoins := '';
  tmpCaptions := 'delete_col'; // do not display first column
  if ACheckBox then
    tmpCaptions := tmpCaptions + ',#';
  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+' AND '+tmpTableAlias+'.orderNum is NULL '+CR ;
      //
      if tmpCaptions <> '' then
        tmpCaptions := tmpCaptions + ', ';
      tmpCaptions := tmpCaptions + tmpDataSet.FieldByName('name').asString;
      if tmpDataSet.FieldByName('is_name').asInteger = 1 then
        AButton.Tag := tmpCount;
      //
      inc(tmpCount);
    end;
    tmpDataSet.Next;
  end;
  tmpDataSet.Free;
  tmpSQL := 'SELECT '+tmpFields+CR+' FROM object '+tmpJoins+CR+' WHERE object.id_class in ('+tmpChildList+')';
  AButton.dbSQL := tmpSQL;
  AButton.dbListFieldsNames := tmpCaptions;
end;Code language: JavaScript (javascript)

As can be seen from the first lines of code, generating a list of IDs of ancestors and descendants is also implemented as a separate procedure with GetChildAndParent() parameters. In the future, it will be abolished, since the interval trees to which I plan to transfer the object table allow us to generate lists of parents and children with a fairly simple SQL query.

procedure GetChildAndParent(AID:integer; var AParentList:string; var AChildList:string);
// creating a list of parents and children
var
  tmpTree: TdbTreeView;
  s: string;
  tmpIndex: integer;
  tmpForm: TForm;
  tmpLevel: integer;
begin
  AParentList := '-1';
  AChildList := '-1';
  tmpForm := GetFormByName('dtfClass_Tree');
  if tmpForm <> nil then
  begin
    tmpTree := TdbTreeView( Form_GetDataViewer( tmpForm ) );
    // collect IDs from the node and all parent nodes
    tmpIndex := Grid_IdToIndex( tmpTree, AID);
    if tmpIndex <> -1 then
    begin
      s := '';
      repeat
        if s <> '' then
          s := s+',';
        s := s + IntToStr( tmpTree.dbIndexToID( tmpIndex ) );
        tmpIndex := tmpTree.GetParent( tmpIndex );
      until tmpIndex < 0;
      AParentList := s;
      // collecting child IDs
      tmpIndex := Grid_IdToIndex( tmpTree, AID);
      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 );
      AChildList := s;
    end;
  end;
end;
Code language: JavaScript (javascript)

The frmObject_tgrMain_OnChange and frmObject_tgrMain_OnColumnResize procedures have been rewritten so that they can be used on different forms, with and without checkers. To simplify the algorithm, the convention is that the column with checkers has the heading “#”.

procedure frmObject_tgrMain_OnChange (Sender: TObject);
// updating data in a grid
var
  tmpIDClass: integer;
  tmpSQL: string;
  tmpColumn: integer;
  s: string;
  tmpColWidth: array of string;

  tmpForm:TForm;
  tmpLabel:TdbLabel;
  tmpGrid: TdbStringGridEx;
  tmpFirstColumn: integer;
  tmpIDs: array of string;
  i: integer;
  tmpIndex: integer;
begin
  tmpGrid := TdbStringGridEx(Sender);
  CForm(Sender,tmpForm);
  FindC(tmpForm,'labIDClass',tmpLabel);
  // получаем ID
  tmpIDClass := tmpLabel.Tag;
  // read column widths from database
  tmpSQL := 'SELECT COALESCE(col_widths,"") FROM class WHERE id = '+IntToStr(tmpIDClass);
  s := SQLExecute(tmpSQL);
  tmpColWidth := SplitString(s,',');

  tmpFirstColumn := 0;
  if (tmpGrid.Columns.Count > 0) and (tmpGrid.Columns[tmpFirstColumn].Header.Caption = '#')  then
  begin
    tmpGrid.Columns[tmpFirstColumn].Width := 20;
    tmpFirstColumn := 1;
  end;
  for tmpColumn := tmpFirstColumn to tmpGrid.Columns.Count - 1 do
  begin
    if tmpColumn < length(tmpColWidth)+tmpFirstColumn then
      tmpGrid.Columns[tmpColumn].Width := StrToInt(tmpColWidth[tmpColumn-tmpFirstColumn] )
    else
      tmpGrid.Columns[tmpColumn].Width := 200;
  end;
  tmpLabel.Caption := ''; // разблокируем

  // positioning
  FindC(tmpForm,'labID',tmpLabel,False);
  if (tmpLabel<>nil) and (tmpLabel.Caption <> '') then
  begin
    if tmpGrid.Columns[0].Header.Caption = '#' then
    begin
      tmpIDs := SplitString(tmpLabel.Caption,',');
      for i:=0 to length(tmpIDs) - 1 do
      begin
        tmpIndex := Grid_IdToIndex(tmpGrid, StrToInt( tmpIDs[i] ) );
        tmpGrid.Cell[0,tmpIndex].asBoolean := True;
      end;
    end
    else
      tmpGrid.dbItemID := StrToInt(tmpLabel.Caption);
  end;
end;

procedure frmObject_tgrMain_OnColumnResize (Sender: TObject; ACol: Integer);
// changing column width
var
  tmpIDClass: integer;
  tmpSQL : string;
  i: integer;
  s: string;
  tmpForm:TForm;
  tmpLabel:TdbLabel;
  tmpGrid: TdbStringGridEx;
  tmpFirstColumn: integer;
begin
  tmpGrid := TdbStringGridEx(Sender);
  if (tmpGrid.Columns.Count > 0) then
  begin
  CForm(Sender,tmpForm);
  FindC(tmpForm,'labIDClass',tmpLabel);
  //
  if tmpLabel.Caption = '' then
  begin
    s := '';
    tmpFirstColumn := 0;
    if  tmpGrid.Columns[tmpFirstColumn].Header.Caption = '#' then
      tmpFirstColumn := 1;
    for i:=tmpFirstColumn to tmpGrid.Columns.Count - 1 do
      s := s + IntToStr(tmpGrid.Columns[i].Width) + ',';
    delete(s,length(s),1);
    // remember the widths of all columns in the database
    tmpIDClass := tmpLabel.Tag; //  класс
    tmpSQL := 'UPDATE class SET col_widths = "'+s+'" WHERE id = '+IntToStr(tmpIDClass);
    SQLExecute(tmpSQL);
  end;
  end;
end;Code language: PHP (php)

The form also contains a selection confirmation button (1) and an additional label element, which is used to transfer parameters to the form: the value of the reference parameter and its visual display. The button contains a handler that can generate from checkers a line with the ID of the selected values and a visual display line:

procedure frmObject_Sel_btnSelect_OnClick (Sender: TObject; var Cancel: boolean);
var
  i: integer;
  tmpCaption: string;
  tmpIDs: string;
begin
  if  frmObject_Sel.tgrMain.Columns[0].Header.Caption = '#' then
  begin
    tmpCaption := '';
    tmpIDs := '';
    for i := 0 to frmObject_Sel.tgrMain.RowCount - 1 do
    begin
      if frmObject_Sel.tgrMain.Cell[0,i].AsBoolean then
      begin
        if tmpCaption <> '' then
          tmpCaption := tmpCaption + ', ';
        tmpCaption := tmpCaption + frmObject_Sel.tgrMain.cells[ frmObject_Sel.btnUpdate.Tag + 1, i  ];
        if tmpIDs <> '' then
          tmpIDs := tmpIDs + ',';
        tmpIDs := tmpIDs + IntToStr(frmObject_Sel.tgrMain.dbIndexToID( i ));
      end;
    end;
    frmObject_Sel.labID.Caption := tmpIDs;
    frmObject_Sel.labID.TagString := tmpCaption;
  end
  else
  begin
    frmObject_Sel.labID.Caption := IntToStr(frmObject_Sel.tgrMain.dbItemID);
    frmObject_Sel.labID.TagString := frmObject_Sel.tgrMain.cells[ frmObject_Sel.btnUpdate.Tag, frmObject_Sel.tgrMain.SelectedRow  ];
  end;
  frmObject_Sel.ModalResult := mrOK;
end;Code language: JavaScript (javascript)

Other procedures and functions have also been improved.

UI Components

We register a new component in the database. It is implemented as a button that appears next to the text view’s display field:

For everything to work, we configure the component type in the class tree:

Then we set the value type of “Author” to “Set”.

As a result, when editing a book, you can select several authors, which corresponds to the real picture of the world.

Results

It is now possible to edit multiple values, which are displayed as a list (1) and edited by checkers (2) on a separate form.

A plural value should not be confused with a list of objects that are part of another object, such as a recipe for a dish or an invoice detail. The main difference is that an element of such a list is not a simple object, but contains several unique properties (ingredients – information about the quantity, a line in the invoice – the quantity and cost of the goods). It’s just more convenient to display the details of the invoice along with the invoice itself. The task of displaying dependent objects is on the list of priority improvements:

• 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.

Looking at the prospect of implementing this system for the web, I think about the fact that the web interface does not imply the presence of modal forms created by the server. You will have to do a lot of trickery with client scripts in order to implement such a simple and necessary concept as modality and nesting of form display.

Links

• Data Keeper 1.2 – project source