Search for solutions to quickly create interfaces DBMS

Almost every person is faced with the maintenance of any accounting, collection and analysis of data: from the use of tables in Excel to work with the data in the client-banking application. Everywhere for such accounting various database management systems (DBMS) are used.

In the article I would like to talk about my way of finding such a system.

Faced with similar DBMSs (client-banks, FSZN systems, cash settlement applications) from the user's point of view, I always wondered why even large companies with sufficient financial capabilities often cannot implement a truly user-friendly interface in their applications.

Obviously, the application interface should be user-friendly, it should be developed on the basis of a well-designed implementation of a set of typical operations on data. The absence of such a foundation either adversely affects the quality of a software product, or significantly increases its development time.

1C: Enterprise

In 2003, when I needed applications for conducting any kind of accounting, I wrote configurations for 1C, which usually looked like this:

At that time, everything was fine with me in this approach. However, after a while I was faced with the need for network access to the accounting system. After analyzing the prices for server parts 1C, I realized that for me they are too high, and began to look for an alternative, preferably free and open source.

Django admin

After analyzing and studying the reviews, I chose the Django framework with its admin interface generator. At the same time, I had to transfer some of the ideas embodied in 1C to the Python code. As a result, we got about the following interfaces:

However, when large amounts of data began to show the disadvantages of this approach.

First of all, Django Admin interfaces have very limited functionality and the convenience of their creation comes to naught if you need a functionality not provided by Django. In this case, you have to poke around in the Django code and look for a place where you could embed your code, inheriting classes or redoing templates. Such modifications are non-systemic in nature and after a while it is difficult to come back to them and remember how they work (which had to be done, in particular, after updating the next version of Django).

Secondly, working with interfaces created by Django Admin was not convenient enough, data entry was difficult and not operative. I wanted interactivity at least at the 1C level, so that the interface does not overload the page every time data is sent, but uses technologies such as Ajax or WebSocket.

MySQL, Navicat and others

As a result, I had to completely abandon the use of the Django Admin module and the client part write independently in JavaScript using the above technologies. So the issue with interactivity was resolved, but the time that began to go on creating interfaces was unreasonably large. Sometimes, to save time, I used pure Mysql with the client part in the form of Navicat to collect and analyze data. As it turned out, thanks to triggers and views, this is not the worst solution, and a huge number of tasks are solved in this way quite simply (which is not surprising, because, according to Wikipedia, Mysql was created initially to solve such problems).

Criteria for an optimal DBMS development tool

Over time, I formulated for myself a list of subjective wishes for a tool for developing a DBMS. He must:

• Allow you to quickly and automatically create interfaces for working with data based on their models.
• To offer not only the mechanisms of interaction of objects with each other, but also mechanisms for displaying these interactions in the interface.
• Provide a choice: to develop the interface or its part faster (but less flexible) or more functional (but slower).
• Work with various data sources.
• Be based on web technologies, be cross-platform and have open source.

Nothing that would meet these requirements, I could not find. Therefore, I decided to write my own framework that solves the tasks. TypeScript was chosen as the main technology in conjunction with Angular2. At the same time, the framework was designed so that it could be used for developing applications not only using Angular, but also using other JavaScript libraries. I would like to share what happened: perhaps someone will come in handy.

The structure of the new framework

The framework is designed to quickly create interfaces for the DBMS. It consists of several parts (modules). Some can be used alone, some only with others.

The core module contains mechanisms for describing models, the interaction of data objects (records) with each other, mechanisms for describing database queries. The core module accesses data sources through the backend module.

The backend module is an interlayer between the core module and the base (source) of data. The data source can be either the database server itself, such as SQL, or an interlayer for accessing models of other frameworks, such as Django or Sequelize.

The model-ui module is responsible for generating the interface: it visualizes the data provided by the core module, using the controls provided by the ui module.

The ui module contains the basic controls that are used by the model-ui module when generating an interface. These elements can also be used independently of the framework.

The windows-manager module manages containers for displaying user interfaces. Depending on the type of windows-manager, applications can be deployed on both computers and mobile devices.

Models and work with data

A feature of the framework, in contrast to the same Django, is that to describe the lists of records and the records themselves use different types of models: ListModel and RecordModel . This approach allows lists to display records not only from one, but also from different models, as well as non-editable lines (which are, for example, the result of working on these records).

Although the framework contains the necessary mechanisms for this, it is not required to describe the models when developing an application. The backend module automatically generates internal models based on those that already exist and are described in other environments (such as Django, Sequelize, SQL, and others).

The framework has similarities with working with Django. For example, classes for working with samples and queries ( QuerySet and Query ) are equivalents of the Django classes of the same name, adapted from Python code to TypeScript code. For example, to fetch data from a data source, you need to write something like the following code:

backend .getListModel('product') .getQueryset() .filter({name__icontains: ''}) .orderBy('-price') .limit(0,10) .getRows() .subscribe(products => {…}); 

Another distinctive feature of the framework is support for virtual fields. When a virtual field changes, it can change the real fields of objects, and when the values ​​of real fields change, the virtual ones can change. Something similar is in Django, when through an object we have access to data that is not stored in the database in the form in which it is available in this object - the receipt of other objects that refer to the object through the xxxxxx_set property or access to an object through a property, when only the id of this object is stored in the database.

In the illustration below, the product_id and product_name fields are real, and the product field is virtual.

The framework implements “lazy loading of dependent records”. Unlike Django, here the developer can decide in which cases it is better not to use this mechanism, but to receive data immediately, thereby reducing the number of requests between the client and the server. So, in the example above, the product product has a supplier field that refers to the supplier. By default, suppliers will be requested from the database only when accessing the product ['supplier'] field. However, if the above example is modified as follows: ... getRows ('supplier'). Subscribe (products => {...}); - then each product from the list of products will already contain data about the supplier and when accessing them there will be no query to the database.

Interface

To speed up application development, the framework allows the use of a built-in interface based on model-ui and ui modules. This is convenient when there is no need for a specific interface or you need to deploy the application as quickly as possible. However, by abandoning these modules, you can use an arbitrary interface (written, say, based on Bootstrap or Angular Material).

All data entry controls in the standard interface are made so that any of them can be used both in forms and in table cells. Elements are also endowed with properties that simplify working with data. Here, for example, is the text field.

Clicking on the expansion button opens an enlarged input field: this is convenient when the width of the input field is smaller than the text placed in it. Such a situation may arise when the elements are closely spaced on the form or when editing data in a table.

If we consider the number input field, then in it, besides the number itself, you can enter mathematical expressions.

The selection field of another object also has several input methods: it allows you to select an object both from the drop-down list by the occurrence of words, and from a separate pop-up form.

As for input incorrectness errors, they are displayed not by the text under the control, but by a special marker. This approach allows us to preserve the density of form elements and not to change their position when displaying errors. Similarly, errors are displayed for cells in a table with this control.

Interface and data interaction

In the framework, much attention is paid to the interaction of records (objects) with each other.

When you change an entry on a form, it is automatically updated in the list.

If the record is simultaneously edited from different computers, then the user receives a warning.

The framework interface also implements a mechanism for sorting rows of dependent entries (if this is provided by the model and the backend module).

The above examples of interface elements are only a small part of what is incorporated in the framework. A more detailed description of all elements and principles of the interface is a topic for individual articles. It is also worth repeating that these examples of interfaces are not imposed, but only offered as a basis and can be customized at the discretion of the developer.

Future plans

The framework contains the main features that I wanted to see in it. It is quite efficient, but nevertheless, the stage at which it is at the moment, I would call it only a concept.

For example, at the moment there is a backend, which allows the framework to work only with the MySQL database, because of which it can only be run on Electron. Also, the interface for working on mobile devices and a number of other features are not implemented.

Nearest development plans for the framework:

• Implement merge and grouping mechanisms in queries in the Query class.
• Add controls for working with associations and groupings.
• Develop a backend to convert a Query object to json or xml, as well as develop a backend for working with Django models.
• Implement a mechanism for caching requests to the data server.
• Implement a large number of other ideas.

How to try?

If you have a desire to try out the framework in work, you can see an example of the application written on it. It can be installed as follows:

 git clone https://github.com/astoniocom/astonio-demo.git 

Next, you need to install the dependencies:

 npm install 

Then open the app.module.ts file and configure access to any MySQL database, and then build the application:

 npm run watch 

and run it with the command:

 npm run app 

The framework is available on github .

Project website

Instructions for creating an application based on the framework

An example of a more complex application that implements basic development concepts is here (installation instructions in the README.md file).