When developing a database client application it is usually required to implement a capability to export information from database tables into some simple data-exchange representations of tables like the CSV (Comma-Separated Values) format. It is very straightforward to implement the export for a single database table. But some information about the records of a master table may be represented by the records from, sometimes, several detail tables, so the resulting CSV table would be incomplete without this information. We develop AIS (automated information systems) using declarative specifications of database applications (SDA). The AIS'es are implemented using general algorithms, which are directed by the specifications. In this article we'll consider the approach to generation of flat tables from the master-detail groups of tables, which allows users to represent compactly the data from a hierarchy of tables related by the master-detail relationships and to select conveniently which kind of information to include into the resulting table.
We consider the approach to development of AIS (automated information system) using declarative
specifications of database applications (SDA). Automated information systems are designed
to accomplish specific information-handling operations [
In our work we consider database client applications (or, shorter, database applications) — the AIS’es that implement DBMS user interface. The database client application should allow their users to perform CRUD (create, read, update, delete), search and some other operations, for example, report generation.
The traditional way of database application development is based on the usage of some
libraries like VCL (Visual Component Library) [
FCL (Framework Class Library) [
Some libraries like LINQ (Language Integrated Query) [
The approach, which potentially allows programmers to substantially decrease the development
time, is the Model-Driven Architecture (MDA) [
The need for more effective methods of application development becomes widely accepted
nowadays. To present an argument for the need in [
Our approach is based on the specifications of database applications (SDA) and can be
considered as a low-code one when using the modern terms. The specifications of database
applications contain all the information about database structure, which is required to build a
typical AIS. The information is represented in its pure form, so the specifications are rather concise.
The AIS’es are implemented using general algorithms, which are directed by the specifications.
We have developed the algorithms for such tasks as: user interface generation, query building,
report generation, GIS (Geographic Information Systems) interaction. Using the specifications
of database applications and the algorithms the software system MetaDBApp/GeoARM was
implemented. We have described the approach in more details in [
In this article we consider the sub-task of generation of a text or a spreadsheet file from the results of a user-defined query. This task shouldn’t be mixed with that of report generation, because here we are not interested in a particular data formatting according to a report template, but in the export of all the selected data for further analysis outside the application, primarily in the form of a spreadsheet. To be able to formulate this kind of problems it is required to be in possession of the meta-information about the tables and their relations, like that we have in SDA. That’s why no developers try to solve this problem when using the traditional approaches and it is hard to find any articles directly considering this kind of tasks.
The main contributions of the paper are: (i) We suggest a human-readable representation of hierarchic master-detail data in the form of single grid. (ii) We consider the capabilities of the SDA-controlled algorithms as exemplified by the algorithms of master-detail data export and interactive query building.
While the task of export into CSV-like file format of a single table is very simple, it becomes non-trivial for a group of tables interconnected by the master-detail relationships. For example, if we have a table of Persons and suppose, that each person may have several contacts, then the Contacts should be stored in a separate detail table. The same may happen to the other attributes of the Persons entity, which may have several values, e.g. Addresses, Documents. If we’ll export just the attributes of the Persons table itself from the database, we will miss all the multi-valued attributes of the entity.
There exist structured text file formats, which are well-suited for representation of hierarchical data, such as XML (eXtensible Markup Language), JSON (JavaScript Object Notation), YAML (Yet Another Markup Language). After implementation of the export into flat tables we can now easily add to our software the capability to export into the structured formats if it will be required. But the primary goal of the structured text formats is the information exchange between applications. And the formats are not intended for data visualization for humans. It doesn’t matter that user can easily read any line of a text file, when the file has, say, 100000 lines. In the YAML representation the attributes of a single record will hardly fit into one screen, so it will be difficult to compare even two consecutive records.
Another area, where we can see tabular representations of rather complex relations between
objects is OLAP (On-Line Analytical Processing). The OLAP grids represent the contents
of multidimensional cubes in 2D [
Out
The OLAP data representation can be very dense from the point of view of the number of values per screen. And the approach considered in this article is inspired by the OLAP representation. Indeed, we also have a tree — the tree of tables connected by the master-detail relationships. The leafs of the tree are the fields of the tables. To reflect this hierarchy we can use the OLAP-like top table headers.
But in contrast to the OLAP grids we will not use the left headers. Instead, we will represent the master-detail hierarchy of the table records by placing the 1st detail record after its master in the same row, and the next detail record in the next row with the master fields in the next row being empty.
Let us consider our approach in more details. Figure 2 presents an example of the masterdetail tree for some illustrative tables. The names of the tables correspond to their levels in the hierarchy. The selected fields in the tables to be exported are shown in the round brackets here.
And the Figure 3 illustrates the suggested idea. It shows the grid representation of a fragment of the tables from the Figure 2, corresponding to couple of records of the main table. The fields have synthetic values here, which consist of the field name, the master record key and the detail Main(A,B)
Detail1(C,D,E)
Subdetail1(F)
Subdetail2(G,H) Detail2(I)
Subdetail3(J,K) Detail3(L) record ordinal number. The key feature of the data representation is that the cells in the columns, corresponding to different detail tables, even from the same resulting grid row, are mutually independent and are governed by their master records only.
A1 B B1 A2 B2
C1.1 C1.2 C2.1 C2.2 D D1.1 D1.2 D2.1 D2.2 E E1.1 E1.2 E2.1 E2.2
F1.1.1 F1.1.2 F1.1.3 F1.2.1 F2.1.1 F2.2.1 F2.2.2
G1.1.1 H1.1.1 G1.1.2 H1.1.2 G1.2.1 G1.2.2 G2.1.1 G2.1.2 G2.2.1 H1.2.1 H1.2.2 H2.1.1 H2.1.2 H2.2.1
The specification of database application contains information about the database table fields and the master-detail links between the tables. The availability of this information makes it possible to create universal export setup dialogs, which allow users to control the data export. Let us consider the dialogs that we use here in more details.
The export setup dialog allows users to select the tables to be exported, their records and fields (Figure 4). The dialog represents the hierarchy of details of the current table using the TreeView nodes. We use the information about the master-detail links from SDA to create the detail nodes. We build the tree dynamically, so the dialog will work correctly even in the presence of loops in the graph of master-detail relations (for example, when a table has a field named, say id parent, with the foreign key pointing to the parent record in the same table).
The first sub-node of each table node represents the list of the table fields. Using the checkmarks of the tree nodes user can select the tables and fields to be exported. And to select the records to be exported we use the query builder dialogs, which can be called for any detail table node.
To select the records of the tables to be exported we use another universal dialog controlled by specification — the query builder dialog (Figure 5).
First of all we may use the query builder to filter the records of the main table. Then we may filter the records of some detail tables by calling the query builder dialog from the export setup dialog.
The query builder dialog has two modes: simple and advanced. In the simple mode it allows user to fill some positions in the list of conditions on the values of the table fields and the resulting condition will be the conjunction of the filled conditions from the list. And the advanced mode allows user to construct an arbitrary logical formula by combining any number of conditions on the values of the fields and the conditions on the detail data-sets. The conditions on the detail data-sets are the requirements on the existence or on the number of their records satisfying some criteria. And the criteria on the records of the detail data-sets are constructed using the recursive calls of the same query builder dialog.
The query-builder dialogs get from SDA the information about the table fields, their types and roles in the application, and about the links to the detail tables. Using the information entered in the dialog we generate SQL queries to the tables (Figure 6 for an example).
To perform the export we use array of buffers of cell values for each resulting grid column. The main loop processes the records of the master data-set. For each record it clears the buffers, fills them using the recursive function EnumDetails, and flushes the resulting values to the resulting grid. select T. BusinessEntityID ,T0. ModifiedDate F_1 ,T. PersonType ,T. NameStyle ,T.Title , T. FirstName ,T. MiddleName ,T. LastName ,T. Suffix ,T. EmailPromotion ,
T. AdditionalContactInfo ,T. Demographics ,T. rowguid ,T. ModifiedDate from Person .[ Person ] T left outer join Person .[ BusinessEntity ] T0 on (T. BusinessEntityID =T0. BusinessEntityID ) where ( exists ( select * from HumanResources .[ Employee ] S where (S. BusinessEntityID =T. BusinessEntityID ) and (( select count (*) from HumanResources .[ EmployeeDepartmentHistory ] R
where (R. BusinessEntityID =S. BusinessEntityID )) >1)) and
T. LastName like ’A%’) order by T. BusinessEntityID about the table to be exported, and l0 — the buffer row, starting from which the table data will be placed (Figure 7). The TableInfo object has the following attributes: • Fields — the list of fields selected for export; • Children — the list of detail tables selected for export; • StartCol — the starting column of the table in the resulting grid; • Dataset — already opened table or query, which contains the selected records.
The TableInfo data structures are constructed using the information entered in the export setup dialog. And all the detail data-sets are linked to their master data-sets by the master-detail relations, so that their records are automatically filtered after moving to the next master record to show only the detail records of the current master record. The SDA engine already had the operation for generation of the detail data-sets, which is required for construction of the data view/edit forms, so here we just reuse this capability.
In the Figure 8 we can see an output of the algorithm considered. The output file was generated in the Microsoft Excel XLS file format with some additional styling and headers in comparison with the CSV file format.
To demonstrate the algorithm we use the well-known AdventureWorks MS SQL sample database. Here we select the records from the Person table and from its detail tables Employee and Email Address. And the table Employee also has its own detail table EmployeeDepartmentHistory. The hierarchy of the tables is reflected by the 3-level header (the rows 3 − 5) above the field names (the row 6).
Using the query builder we have selected from the main table only the persons, which are employees, this condition is shown in human-readable form before the table header (in the row 2).
In the figure we can see two persons, who have two records in their job history (see the rows 10 − 11 and 23 − 24). So they have two sub-detail records in the EmployeeDepartmentHistory table and all the other cells in the 2nd row of the resulting table are empty.
And the Figure 9 shows another export example from the other master table SalesPerson, where the master records have much more detail records, than in the previous case.
Note, that when a master record has several details, the records of the two distinct details from the same resulting table row are not related to each other, they just have the same ordinal number in the corresponding lists of the detail records.
In fact the specification of database application for the AdventureWorks database, that we use here, was generated automatically by the database structure analysis algorithm. The algorithm allows developer to quickly create a starting database application specification, that can be then corrected manually, if it will be required. And because the AdventureWorks database is a showcase MS SQL database of high quality, it was not required here to correct the generated specification, and we used it as is.
We have proposed and implemented a method for compact representation of information from the group of associated by the master-detail relationships tables. The algorithms get the information about the relations between tables from the declarative specifications of database applications. The export parameters dialogue allows user to select the tables and their fields to export. It is also possible to select the records of the detail tables of interest using the interactive query builder, which is also controlled by specifications of database applications. The practical usage of the suggested approach to data export has shown, that even nontechnical people easily understand this kind of data representation.
The results were obtained within the framework of the State Assignment of the Ministry of Education and Science of the Russian Federation for the project ”Methods and technologies of cloud-based service-oriented platform for collecting, storing and processing large volumes of multi-format interdisciplinary data and knowledge based upon the use of artificial intelligence, model-guided approach and machine learning”. Some results were obtained using the facilities of the Centre of collective usage ”Integrated information network of Irkutsk scientific educational complex”.