Alternative DBMS Architecture and Application Development Approach

I will talk about a technology platform suitable for creating the information core of a system or application. The platform contains a simple high-level data model constructor and a basic interface for working with it, supports a role-based access model, SQL query emulator (CRUD), API, and also allows you to load arbitrary workstations - UI elements - and fill them with data.

The platform has some fundamental differences from an infinite number of "designers", which is why it appeared. Some of the differences are worthy of a quality holivar, others simply simplify the life of the developer, no matter who he is. Several applications already work with live clients, of which working examples of tasks will be given.

Here you can build a web application without learning a programming language: we operate only with business terms and formulas, no more complicated than in MS Excel. Of course, an understanding of the principles of the databases will help you develop a more robust, large-scale and rich functional product, but this service does not require specific knowledge for simple solutions, which, without a hitch, make up at least 80% of the application development (for example, artisanal and everything that is now works in Excel).

Disclaimer

Now it’s just a simple service, a concept that implements an approach to data storage and application development, whose followers are a small team of the project of the same name. The service does not pretend to be full of possibilities, we still have a lot of work ahead, and for now we have only confirmed for ourselves the concept of the original application designer, we are creating a product and are ready to develop it in cooperation with everyone.

Integral

An integral is an architecture-forming core that provides a basic user interface for specifying a data model (metadata) and working with data. The basic interface uses a few simple commands to manage the data structure (DDL) and 6 commands to change the data itself (DML). These commands are hidden from the user - he sees only the input fields, tables and buttons. You can, without knowing any commands, use the basic interface to work with your data and quickly create an information system.

The general scheme of interaction between the service and the outside world looks like this:



The kernel does all the dirty work: data binding, simple validation, integrity control, building queries in the database language, type casting and other little things that are not interesting to you. You only indicate what data you need to transfer or request, which request to run, how the result will be formatted in the user interface. Of course, if you wish, you can intervene and adjust the kernel in the part that relates to business logic.

Integral "programming" is as follows:

• setting process parameters - stages, roles, properties, structures, formats, etc .;
• creating queries to select and modify data, including creating and modifying the queries themselves;
• Create user interface templates with insertion points for data.

The user logs into the system, receives a menu according to his role, views information, enters data, launches queries, moving the process through predetermined stages, receives reports. All interaction with the system occurs through the forms specified by the templates. It is very similar to the work of CMS and, as our designer says, "made as in the first Bitra."

For work, a basic interface is used, which can be supplemented and expanded to fit your needs. The forms of the basic interface are available on  Github , you can freely use them - rewrite them completely with blackjack and ladies, or simply “pull” the necessary styles.

Data structure

Tools for building a data model and working with it are the main advantage of the Integral approach. Here we can quickly create a diagram of the objects of our business, which, if necessary, can also be quickly rebuilt.

Initially, we get a database that describes some entities (hereinafter referred to as objects or types). The core of Integral already knows about their nature and principles of operation. There are few of them:

• User;
• User role;
• Objects included in the Role (all Integral objects, including the Role and the User);
• Level of access to the object;
• A query (an analogue of an SQL query in a relational database);
• Request fields (names of all system objects, including newly created ones).

The objects listed have several properties that the kernel also knows about. Taken together, the initial filling gives a minimum of functionality that is inevitably present in any application and which allows you to collect everything else.

The structure is created in the Type Editor, where it all looks something like this:



Here is a hierarchy of objects: A Role is assigned to the user, which includes several Objects, each of which is assigned Access. There is also a Request object - this is an analogue of SQL queries in databases, it contains Fields to which you can apply Functions, Formats, and calculate the total values ​​for them. Here, for clarity, the connections are shown by arrows between objects, however, in the Type Editor, rectangle objects are not connected by arrows, and the hierarchy is indicated only by the location of the children to the right of the parent with the list down. This is because the subordination of objects in a small diagram is already obvious, while in a large diagram arrows do not add convenience, they only clutter up the picture.

Also, for ease of perception in the hierarchy, only reference and table details are displayed, and simple ones, such as Email or Phone, are located in a list (alphabetically) at the bottom of the Type Editor.

In addition to the objects listed above, Integral is a blank sheet on which you can sketch out the data structure you need with their relationships. Any structure that can be implemented in a relational database. As you probably already guessed, the Integral itself is assembled by means of the Integral.

To add your objects - the terms of your business - you simply specify a name and indicate the basic type: string, number, file, etc. A total of 16 basic types are available, most of which determine the presentation of the data, the rest determine the behavior - for example, contextual action.

To make life easier for the user, we have simplified the basic data types as much as possible. The data itself of any type is stored in the same way - as a sequence of bytes, and the basic type determines the output format, the rules of comparison and validation, and so on, which is hidden from the user. For example, the user does not need to think in advance how many characters he will have after the decimal point in each attribute. You can enter numbers with any number of digits, and in the vast majority of cases (10-12 digits before or after the decimal point) this will work correctly without unnecessary questions (by default, 2 decimal places are displayed for SIGNED). If you need to display numbers in the desired format, you can bind these formats in the form of additional details and use them in user-created templates, but more on that later.

The types are named with the corresponding English words, so that it is more common for programmers to start working with them. If you wish, you can translate them into human language by tweaking one reference in the Type Editor. So far, we have not seen difficulties in understanding the type values ​​of any category of Integral users, and there is a suspicion that they will just appear if we invent Russian-language substitutes. Of course, when teaching a MS Excel user how to work with Integral, we do not use the concepts of essence, props and the like, but operate with simpler concepts such as a table, field, column, etc.

Suppose we work with clients who have a name, phone, email and address. All these new terms for Integral, which can also be called objects, types or entities, are added using such a simple form:



So in Integral all business terms are defined. Then all the entities with which the business is dealing with are collected from them, and they form a data structure. Some terms, such as phone, email, address are already in Integral, and they can be used for your own purposes.

Requisites

By clicking on the name of any type, you can add the details to it:



We have at our disposal the entire set of types that we have already added to the system. Of course, we can add the Requisites, creating them as necessary, delete, swap, set the simplest rules for them: mandatory props, calculated value, alias, default value. The same type can be added as a props to different objects.

All objects created in the type editor are immediately available for use: you can create instances of these types and fill in their details. Objects are in the Dictionary in the form of a list of all independent entities, that is, those types that are not anybody's details. So we see the Access type in the list, because the Access attribute of the same name for the Object type is a link to an independent Access directory. But the types Objects and Menus are not in the list, as they are directly subordinate to the Role type:



Instances of the Dictionary objects are available in the form of tables to which you can apply filters to search for the necessary entries (by any attribute), list deletion, export and import. By clicking “User” in the Dictionary, you can see a list of all instances of type User:



To edit an entry in a table, click on the value in the first column of this table, in this case the first field is “User”.

Note: The ability to delete, edit, export, the visibility of details and other restrictions on working with objects are set in the User Roles accurate to individual details. You can also apply masks to the values ​​of objects and their details to restrict access by value.

To add a new record, the value of an instance of this type is first indicated:



Then the details of this type are filled in:



The simple rules set in the type editor are applied to the details: email and Name are required fields, Date is the automatically calculated value of the current date.

There are a couple of tricks in the Type Editor that allow you to create relationships between entities:

1. For any type, you can create a link to this type, and then use this link as a property - a pointer to an instance of the original type
2. The entity requisite, which itself has the requisites, is available for editing in the form of a subordinate table into which you can add ordered records

Props - type reference

In this case, everything is simple and familiar: you can create a Category type, and then, clicking on it, select the "Create Link" option:



In the type editor you will get two types with the name Category: the type itself and a link to its instance.



There will also be two of them in the list of details, the link is marked with a prefix in the form of an arrow:



Adding the reference attribute, we will get two related tables, and the Integral will arrange them in the type editor according to the hierarchy (the reference and table details are located to the right of their parent): You



can assign an alias to the reference attribute (bold in the picture), which will be convenient when using the same directory in different details with different meanings, for example, legal. a person may be a customer, contractor, shipper, and so on.



We can use any other registered type as a proxy, indicating the ID of its instance in the form of a numeric props and substituting this ID in a link on the editing form or in the report. For example, you can make sure that people, cars, buildings, or copyrights are protected.

Props - subordinate table

This type of props is convenient for quick navigation between related data: you can subordinate an array of records to the object, which will always be at hand when working with the parent object. For example, the Role entity contains two details: lists of Objects and Menu items, each of which has details, therefore, it allows you to set many values ​​of the corresponding attribute to the Role type:



In the data dictionary, this will look like a set of tables between which you can quickly navigate between, instead of just one The record will display an array. In the transition from Roles to objects, we will see all the objects included in this role.



The above methods of creating types, adding details and two types of relationships already allow you to create any data structure that is possible in a relational database.

Inquiries

To work with data in the Integral there is a query designer. In the simplest case, which covers much more than half of the needs of users, it is enough to simply list the required data fields, and the Integral itself will combine the corresponding tables in the query, taking into account the relationships specified in the type editor.

For example, the selection of all accesses of all users of the service according to their roles will be reduced to a simple enumeration of data fields. In the Dictionary menu, select the Request object:



Set a name for our request and click Add:



Go to the requisite Data field for the created request:



We see an empty table of fields into which you can add objects from the list. The list is relatively large, because it includes all the objects registered in our service instance. We need a User - select, add.



The created User data field has many details that we don’t need yet (we will definitely consider them later in this article). We save the record as it is:



Note : the query designer can be wrapped in a more attractive visual interface, compact and without unnecessary details, as was done in the example about composite types . We deliberately did not do this in the basic interface, so as not to impair its functionality.

The User line appeared in the table of data fields, and the list of available objects has been reduced - now it contains only objects associated with the User. Choose the Role that we need from the statement of the problem:



Now the Objects and Menus associated with the User via the Role appear in the list. Add Objects:



Finally, add a description of the access level, which is the Access requisite:



That's it, our request is ready, it includes four data fields from four different tables.



By clicking on the name of the request, we will go from the list of fields to the form of the request itself, where there is a “Generate” button. This button is the attribute that launches the programmed contextual action, in this case, the call of the report form with the ID of this report (request).



A launched query will return a report on all users, roles, objects, and access levels:



We did not think about the relationships between the tables of objects and made this report in less than a minute. This is a fairly simple report, but it reflects the capabilities of the query designer. The report can be filtered by field values, and also upload it to Excel.

Any objects created in the service can be used in data sampling, and the Integral itself will link all the involved tables. The query designer allows you to create analogues of almost any SQL query (except exotic, for example, recursive queries), while it removes the user’s concern for indexes, foreign keys, and other details not directly related to the user's business.

In some cases, you may need to specify your own rules for joining tables, as you are used to doing when writing SQL queries, and Integral will allow you to do this. I will talk about this later in this article.

User interface templates

In addition to the basic interface, the user can create arbitrary forms in the form of templates with insertion points into which the Integral will substitute data. Data can be selected by request from the database or taken from the context of working with the template: name and user ID; parameters passed through GET and POST; data of higher and neighboring blocks; global constants etc.

Processing HTML-content based on templates is performed on the server, and each user action leads to the formation of a new page. Initially, the interface has only 6 form templates - one for each mode of operation, plus one main page.

Forms have the following purpose:

• The main page, as a rule, containing styles and the main menu (or several);
• Type editor that displays the data structure;
• Dictionary - a list of all independent types;
• A list of instances of the selected type (lists of users, roles, objects discussed above);
• A form for editing an instance of a type;
• Report.

User forms can be downloaded in the Files menu, into the templates directory, where the main page and an empty initial form are located (containing a welcome message and a link to the Integral User Guide):



If anyone needs to replace other forms of the basic interface, you can download them from the  github and attach to the same folder.

At the time of this writing, the insertion points used in the templates are of three types:

• layout blocks of the template;
• data field;
• file.

“Layout of blocks” defines pattern fragments repeated zero or more times, depending on how many records a data source will provide for this block — the name of the source is indicated in the markup. “Data fields” indicate where to insert specific values ​​from data source records. “File” contains the text of the template, which will be found in the file system, parsed and inserted into the specified location. The nesting level of blocks and files is not limited to the system.

The figure below shows the HTML code for the basic interface page template (main.html - the main menu page), where the borders of the TopMenu and File blocks are highlighted in green. When the parser processes this template, it will turn to the database for the TopMenu request and get a set of records in which it will try to find the data fields TOP_MENU_HREF and TOP_MENU. The result of the query — the report — is circled in gray in the figure. The values ​​of these fields will be substituted into the template instead of the names of these fields in curly brackets. The fragment of the template highlighted in gray in the figure will be repeated as many times as the number of records returned by the TopMenu set. Tags themselves and not to be included in the result.



Next, the parser will meet the design, instead of which the contents of the template file will be inserted with the name specified by parameter a, previously also processed by the parser. As a result, the page will display a menu consisting of the available user role items, and the workplace according to the previously selected menu item. By clicking on the desired object, the user will switch to another workstation, similarly assembled by the parser, but with a different form in the File block:



Thus, the service allows you to build any structure of the application interface, apply the logic embedded in the requests to select data, fill out the form with this data and make changes to the database using the basic interface or custom user forms.

Query results are available in the form of JSON, so you can create your one-page application that will use its own template engine, relying on Integral's capabilities for organizing a database and role mechanism, constructing queries. To request a report in JSON format, you must pass the JSON parameter when invoking the report:



Type Editor, Report Builder, and User Forms provide a combination of features that you can hardly find in another development tool. The data blocks and the rules for filling them complete the integral to a complete platform: with all these tools you can build an application that is not limited by the functionality and complexity of the data model.

If desired, you can create a Single-Page Application using Integral and one of the modern reactive UI js frameworks, for example Vue or React.

Perhaps the reader is already a little bored without polymorphism, inheritance, and encapsulation ... Not care, and those in the following sections will have a little hardcore.

A little deeper and more detailed about the above

After a brief overview of the conceptual capabilities of Integral, I will further discuss some of the most interesting, in my opinion, features of this service. If someone wants to know more, then for you there is a Developer's Guide on 40 sheets, which describes almost everything that is known about the service. To whom, on the contrary, there is already enough information, we can proceed to the  conclusion .

Let me remind you that while the service exists in a rather primitive form, and some moments look frankly naive. We see it and are working on it. Our Change request log now contains 500+ hours of work to bring it to a decent level of service.

Indexing and Data Integrity

In Integral, all data of all tables is indexed. Yes, you can create a table with any number of columns, load many, many records there, and then filter the data by arbitrary fields, and the search will be performed using the index, if applicable to the selection condition. You do not need to first worry about what indexes may be needed in the future, moreover, Integral does not provide a choice: everything is indexed.

The query designer will also select the appropriate indexes to filter and combine the data in the report, while still freeing you from thinking about relationships, keys, and indexes wherever possible. As a result, queries are executed approximately as they would be in a relational database with manually configured indexes.


Such indexing requires more space for storing data than a selectively indexed database (2-3 times), but it saves energy and nerves in the development and support, which is incomparably cheaper in money. These overheads increase linearly, and in some cases they are less than the cost of storing data in a database with many composite indices, in the 1C platform with its GUID and denormalization, or even more so in a CMS like Bitrix with its info blocks.

In order not to breed theoretical holivar about indexes and performance, we deployed a test bench, where you can see and touch how it works on several million records. This stand sometimes slows down, like any database with millions of records and dozens of simultaneous queries, so you can get an adequate idea of ​​performance there.

The integral provides minimal means of maintaining data integrity, for example, not allowing you to delete the reference value, if it is used as a props. The same applies to the types and attributes of objects - you cannot delete a type or attribute if there are instances of it or if it is mentioned in the request or role. There is also a check for the uniqueness of the added objects, but only in the simplest case: if you add a non-unique value to an object marked unique in the Type Editor.

Things like triggers, keys, constraints and much more are purposely taken out of the core of the Integral. Also, everything that was possible to hide from the user was hidden, for example, system IDs of records (this is unusual for a programmer, but quickly acquired). Nevertheless, if desired, any ID can be obtained from the system, and in some cases it is simply necessary for communicating by API, for example.

It should be noted that Integral does not develop in the direction of strengthening technical barriers, such as the mechanism of transactions, locks and restrictions, but rather encourages the compilation of a business process in such a way as to avoid conflict in principle, having a transparent picture of what is happening. Figuratively, than to beat hands in case of an error, it is better to simply not allow the user to make a mistake. This slippery topic will be discussed in a separate article, supported by a more specific theory and examples from life.

Запросы на изменение данных

The integral can make changes to the data selected as a result of the request. Its architecture allows you to do this, so it’s a sin not to seize the opportunity. By default, changes are made in two steps: a preview of upcoming changes, then confirmation. You can also execute the request without confirmation.

In the report designer, the Data Field has the attribute "Assign". You can write some value into this attribute, be it a constant or an expression, calculated from the values ​​of other fields of the same query. This expression will be evaluated for each record returned by the query and assigned to the data field for which it is specified.

A change request can create, modify, and delete data. This can be convenient, for example, if you need to schedule meetings with clients, conduct billing operations, or simply delete old records. Let's see how this works with a small data structure as an example:

Data change

First, put down the note “Autumn 2017” for all customers with whom contracts were concluded in the fall of 2017. To do this, we will compose a request that selects such clients, and it will look something like this: The



request selects the necessary data by applying a filter from September 1 to November 30, 2017 to the contract date. The result is the following report:



Now for these clients we’ll set a new value for the Note attribute (the attribute “Assign”), and also give the column names more preferable headings:



By launching our updated report, we will get a hint about what changes will be made to the data by this request:



In the “Run” column that appears, we see how the data will be changed, and it seems that we can lose the old value of the note to the client Dmitry M. To prevent this, we will change the “Assign” attribute by applying the SQL language function, which will link the existing text with new. We will assign the MEMO alias to this column, and by this alias we can substitute its value into the CONCAT () function that concatenates the text fragments:



Now the query will do exactly what we want:



By clicking on “Run” the query will make changes to the database by issuing the corresponding report :



In the Dictionary, in the customer table we will see new customer data:



You may have a question: what will happen if some object appears in the report more than once, and we are trying to change it. Answer: the object will be changed only 1 time. For example, in the next report, we add the Client Note, which is mentioned in several lines of the report, and the Integral will make the change only 1 time for each client:



This is a fairly simple example of using data change requests. In practice, it may be necessary to update several details in various tables, for example, to obfuscate (depersonalize) personal data of clients before uploading data to a test server.

The service allows you to specify new values ​​for the desired number of data fields, including from different entities, - Integral will carefully post all these changes to the necessary tables.

Adding Records

Now demonstrate the creation of new records. Example: we need to schedule a meeting with all clients in the “Client” status by noon on February 1, 2018. Let's create such a request: the



report obtained from it will display a hint about the created records, and by clicking on “Run”, the meeting records will be added, while restarting the request will not duplicate the already created records, if any.



In the Customer Meetings Dictionary, these entries will appear:

Delete records

To delete records, just assign them empty values ​​- "". The example of deleting records by appointment is very simple: the



Integral does not waste space on empty values, so the records will simply be deleted, with all its details and subordinate tables, if any:

Using external sources queries

The integral allows you to access external systems (sites, APIs, files, etc.) via the http (s) protocol during query execution. To do this, you must specify the external source URL pattern in the report URL field (in this example, I renamed the type Request to Report in the Type Editor). In the template, you can use the values ​​of the report fields by enclosing their names in square brackets.



Then, when choosing report columns, you can give them names, and the values ​​of these columns will be substituted into the URL template:



The abn_URL function will replace the contents of its field with the result of the query to the received URL calculated for each line of the report. As a result of, for example, executing the above request to the API of the SMS service provider, we will receive a service response with the identifier of the sent message, which will be sent to the given phone [phone] to the recipient with the name [name]:



The result of the request to the URL can also be saved in the database assigning its value to any field.

Example task with composite types

In the end, I would like to give an example that for some reason greatly impressed our client from the 1C environment - the task of composite types. The task was to make a report on operations in the context of different analysts, the composition of which depended on the set of accounts in the operation. For this report, I had to write a little javascript in the user interface for ease of perception, forms with all this disgrace (made up quickly and dirty, sorry) are available on the github: Aggregate Types .

So, we have given a chart of accounts with an indication of the set of analytics for each account (a fragment of the plan is shown in the figure):



The chart of accounts was imported into Integral, and the analytic objects - types with a set of details were created in the Type Editor, for example:



Nomenclature - hierarchical reference with reference to the parent element.

Next, in the Type Editor, we created the Operation type with accounts Dt and Kt, for which, in turn, a valid set of analytic objects was set: The



unfilled details of the object do not take up space in the database. If the set of analytic objects is very large, then you can not clutter up the list of details with them, but create these types with a specific attribute that allows adding a complex type to the composition. Although you can even do without it and add everything that the user has access to.

When initiating operations, you can set the analytics according to the accounts involved and its actual parameters:



Next, we generate a report on operations where you can choose an arbitrary set of analysts of interest to us separately for the accounts Dt and Ct. For each analytics, you can specify the conditions for selecting records (Filter field):



We do not want to force the end user to work with the Integral's basic interface even in the prototype of his application, so we made such an input form for him. In fact, during the assignment of the analyst, entries are added and deleted in the Data Fields of the Request “Analyst Report”, and this is how it looks in the Dictionary:



This report will select all the operations that have the required set of analysts that satisfy the selection conditions. Of course, we can include in the report the details of analytic objects to their entire depth, when the customer needs it.

The result of the report is the sum of operations for each combination of analytics:

Business orientation

The main task of Integral for a wide user is to give him the opportunity to turn his data directly without using or even not knowing programming languages ​​and database theory. So at one time, the SQL language appeared, which offered any accountant to independently work with data in the human language. We go the same way, but try to do without language and theories at all, since any need to do something that is not related to business repels a person from using an automation tool.

Integral's motto: "no more complicated than in Excel." Integral can replace Excel with a more flexible and secure means of storing and processing data. Does anyone worry that this is SaaS? Ask him if he doesn’t forward his exels by mail and doesn’t store them in guglodoxes, that is, services that professionally know how to wool data, extracting from them all the necessary information for himself and the authorized services?

First of all, it is a service of a small business, in which the owner often has to conduct the development himself or at least manage it. The IT component is critical for the company, because it is it that determines the agility and flexibility of the business. We try to give the opportunity to start work immediately, not be distracted by non-core topics and be able to completely redo everything if necessary. Has the accounting principle changed? Turn the whole model inside out, throw out the excess and add the right one! Integral allows you to do this quickly and quite clearly.

Why do we need all this?

Programmers have always dreamed of programming less commonplace things, so that there is more time for great achievements.

Our project is an attempt to make a tool for this, and the Integral team managed to achieve its practical operability on the volumes of data that can be found in the applied development of the low and middle price segment. By “practical operability” is meant the following criteria:

• the rate of productivity degradation with the growth of volume and complexity is not higher than that of an information system made in the classical way, and noticeably better than that of existing designers;
• the lack of restrictions inherent in the "designers" in terms of creating and using a data model;
• reduced entry threshold from the point of view of knowledge of programming languages ​​(knowledge of the architectural foundations of information systems, finally, takes its rightful place here, cramping aside expertise in momentary technologies).

The core of the Integral is quite compact, and the low-level work with the data according to our calculations can be generally performed in hardware. Farewell to Meltdown and Specter; hello import substitution ... unexpectedly? Meanwhile, nothing fundamentally new has been happening in IT for a long time (yes apologists for quantum computers will forgive me, but they are still far from practical application), and the dreamer who does not seek to do something like that in practice is bad . The service architecture is fundamentally different from existing platforms, and, we believe, can change the IT world, as it was, for example, the emergence of an open architecture IBM PC.

The service has been actively developing only for the last couple of years, and we see that with such a small (and unstable, because everyone needs money) team, we have to work a decade before a normal product, and the team already has to sacrifice a lot to continue the project. At the same time, we see undoubted prospects in the direction itself, which, in particular, prompted us to write this article - we want to cooperate with everyone who is ready to develop, improve and popularize the Integral approach.

References

Developer's guide

Register your evaluation instance of the database on the service: https://tryint.ru