Let’s Be Friends With Tables

Continuation of the article “Copy-paste treatment”

As stated when declaring the capabilities of the program being created, the search must be global, for all entities. But how to organize this in practice, if you need to search in different tables, and form the result in one query? The UNION SQL command will help us with this.

The task seems simple: you need to collect a query from several SELECT, combining them with the UNION ALL command. The main condition for such a union to work is the coincidence of the types of the fields being combined. In our case, there is such a match.

Here’s what you can learn about the UNION command from the tutorial “Advanced Part 1”.

Combining the results of multiple queries. UNION

You can merge the results of individual queries, subject to a few conditions:

1. The number of columns in queries must be the same.
2. The corresponding columns must be of compatible types.
3. The field names of the resulting query are taken from the first query.
4. Groupings are valid only within each individual query.
5. Sorting is valid only for the entire selection.

SELECT <sample parameters>
UNION [ALL]
SELECT <sample parameters>
UNION [ALL]
…
SELECT <sample parameters>
[ORDER BY <sort options>]Code language: SQL (Structured Query Language) (sql)

Union options

Without going into the details of implementing the search interface yet, I can say that you will need a SQL query that will be assigned to the dbSQL button property. The button is configured to display the SQL query in a table view.

Since the topic is the previous article was “Copy-paste treatment”, I will not give the source text of the SQL query formation procedure, because there was a continuous copy-paste. So, in addition to procedures, to treat copy-paste, you may need a table or several tables in which

const
  // entity categories
  CT_CLASS = 1;
  CT_TYPE = 2;
  CT_METHOD = 3;
  CT_FUNCTION = 4;
  CT_PROPERTY = 5;
  CT_VAR = 6;
  CT_EVENT = 7;
  CT_CONST = 8;
  CT_TASK = 9;Code language: Delphi (delphi)

We need string arrays to store data. And you will have to store the names of tables and categories, filters and expressions to display the name of the entity. The latter circumstance became clear during the practical use of the system, since some entities in the original had the same names, but more on that later.

Note: if you plan to use such data only in processing cycles, then you can not create arrays, but add a table into which to write all the data once, and then retrieve them with a query. This method is well suited if the data is static, does not require adjustments or random access. But if you need access by index or the composition of the data is not yet fully formed, then it is more convenient to use arrays: to access data, you do not need to write a query to the database every time; they are easier to edit.

var
  // entity names
  CTNames: array[1..9] of string;
  CTTables: array[1..9] of string;
  CTFilters: array[1..9] of string;
  CTNameFld: array[1..9] of string;Code language: Delphi (delphi)

To initialize arrays, it is convenient to use a separate procedure that can be called in the main script block.

procedure InitVar;
begin
  // entity names
  CTNames[CT_CLASS]:= 'Class';
  CTNames[CT_TYPE]:= 'Type';
  CTNames[CT_METHOD]:= 'Method';
  CTNames[CT_FUNCTION]:= 'Function';
  CTNames[CT_PROPERTY]:= 'Property';
  CTNames[CT_VAR]:= 'Variable';
  CTNames[CT_EVENT]:= 'Event';
  CTNames[CT_CONST]:= 'Constant';
  CTNames[CT_TASK]:= 'Task';
  // entity storage tables
  CTTables[CT_CLASS]:= 'classType';
  CTTables[CT_TYPE]:= 'classType';
  CTTables[CT_METHOD]:= 'funcProc';
  CTTables[CT_FUNCTION]:= 'funcProc';
  CTTables[CT_PROPERTY]:= 'property';
  CTTables[CT_VAR]:= 'property';
  CTTables[CT_EVENT]:= 'classEvent';
  CTTables[CT_CONST]:= 'typeConst';
  CTTables[CT_TASK]:= 'task';
  // entity filters
  CTFilters[CT_CLASS]:= '(isType = 0)';
  CTFilters[CT_TYPE]:= '(isType = 1)';
  CTFilters[CT_METHOD]:= '(id_classType IS NOT NULL)';
  CTFilters[CT_FUNCTION]:= '(id_classType IS NULL) AND (isGroup=0)';
  CTFilters[CT_PROPERTY]:= '(id_classType IS NOT NULL)';
  CTFilters[CT_VAR]:= '(id_classType IS NULL)';
  CTFilters[CT_EVENT]:= '';
  CTFilters[CT_CONST]:= '';
  CTFilters[CT_TASK]:= '(isGroup=0)';
  // display fields
  CTNameFld[CT_CLASS]:= 'name';
  CTNameFld[CT_TYPE]:= 'name';
  CTNameFld[CT_METHOD]:= '( (SELECT name FROM classType WHERE classType.id = funcProc.id_classType) || "."|| funcProc.name ) as name';
  CTNameFld[CT_FUNCTION]:= 'name';
  CTNameFld[CT_PROPERTY]:= '( (SELECT name FROM classType WHERE classType.id = property.id_classType) || "."|| property.name ) as name';
  CTNameFld[CT_VAR]:= 'name';
  CTNameFld[CT_EVENT]:= '( (SELECT name FROM classType WHERE classType.id = classEvent.id_classType) || "."|| classEvent.name ) as name';
  CTNameFld[CT_CONST]:= 'name';
  CTNameFld[CT_TASK]:= 'name';
end;Code language: Delphi (delphi)

All this looks quite cumbersome, but it allows you to implement an elegant procedure for generating a request.

procedure frmSearchResult_btnUpdate_OnClick (Sender: TObject; var Cancel: boolean);
var
  tmpSQL: string;
  s: string;
  i: integer;
 
  // the function generates a query for selecting data by category
  function SEL( ACategory:integer; ):string;
  begin
    Result :=
      ' SELECT '+chr(13)+//
      '   '+CTNameFld[ACategory]+', '+chr(13)+
      '   "'+CTNames[ACategory]+'",'+chr(13)+// name of category
      '   description, '+chr(13)+// description of category
      '   '+IntToStr(ACategory)+', '+chr(13)+// number of category
      '   id_example, '+chr(13)+// example ID 
      '   id '+chr(13)+// category record ID 
      ' FROM '+CTTables[ACategory]+chr(13)+ // storage table
      ' WHERE ( (name LIKE "%'+s+'%") OR (description LIKE "%'+s+'%") )'+chr(13);
    if CTFilters[ACategory] <> '' then
      Result := Result + ' AND '+CTFilters[ACategory]+chr(13);
  end;
 
begin
  s := frmSearchResult.edtSearchText.Text;
  // if the search string is empty, then we are not looking for anything
  if s = '' then
  begin
    Cancel := True;
    Exit;
  end;
  // form a search for all entities
  tmpSQL := '';
  for i:=1 to Length(CTNames) do
  begin
    tmpSQL := tmpSQL + SEL(i);
    if i <> Length(CTNames) then
      tmpSQL := tmpSQL + ' UNION ALL '+chr(13);//
  end;
  frmSearchResult.btnUpdate.dbSQL := tmpSQL;
end;Code language: Delphi (delphi)

As a result, we have a system for generating a query to the database. It will return records in the title or description of which the search string occurs.