Alternative DBMS Architecture and Application Development Approach
I will talk about the technological platform suitable for creating the information core of a system or application. The platform contains a simple high-level data model designer and a basic interface for working with it, supports role-based access model, SQL query emulator (CRUD), API, and also allows you to load arbitrary jobs - elements of the UI - and fill them with data.
The platform has some fundamental differences from the infinite set of "constructors", because of which it appeared. Some of the differences are worthy of a quality holivar, others simply simplify the developer’s life, whoever it is. Several applications are already working with live clients, of which working examples of performing tasks will be given.
Here you can build a web application without learning a programming language: we use only business terms and formulas, no more difficult than in MS Excel. Of course, an understanding of the principles of database operation will help you develop a more robust, large-scale and functional-rich product, but this service does not require specific knowledge for simple solutions that make up at least 80% of applied development (for example, handicraft and everything that is now). works in Excel).
Now this is just a simple service, a concept that implements an approach to data storage and application development, the adherents of which are a small team of the project of the same name. The service does not claim to be full of possibilities, we still have a lot of work ahead, but for now we have only confirmed the concept of the original application designer for ourselves, create the product and are ready to develop it in cooperation with everyone.
')
The integral is an architecture core that provides a basic user interface for defining a data model (metadata) and working with data. The basic interface uses several simplest commands to control the data structure (DDL) and 6 commands to modify 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 service interaction with the outside world is as follows:
The kernel does all the dirty work: data binding, simplest validation, integrity monitoring, building queries in the database language, type casting and other uninteresting stuff. You only specify what data you need to send or request, what request to run, how the result will be formatted in the user interface. Of course, if you wish, you can intervene and adjust the work of the kernel in the part related to business logic.
Integral "programming" is as follows:
The user logs in, receives menus according to his role, scans information, enters data, runs requests, moves the process along specified stages, receives reports. All interaction with the system occurs through the forms specified by the templates. Very similar to the work of CMS and, as our designer says, “done as in the first Bitra”.
To work using a basic interface that can be supplemented and expanded to fit your needs. Forms of the basic interface are available on Github , you can use them freely - rewrite completely with blackjack and ladies or simply “pull” the desired styles.
The 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 reconfigured.
Initially, we get a database in which some entities are described (called hereafter objects or types). Integral core already knows about their nature and principles of operation. There are some of them:
The listed objects have several properties that the kernel also knows about. In the aggregate, the initial content gives a minimum of functionality, which 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: the User is assigned a Role, 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 Functions, Formats can be applied, and the totals for them can be counted. Here, for clarity, the links are depicted by arrows between objects, but in the Type Editor, rectangle objects are not linked by arrows, and the hierarchy is indicated only by placing children to the right of the parent with the list spreading down. This is done because on a small scheme the subordination of objects is already obvious, while on a large scheme the arrows do not add convenience, but only clutter up the picture.
Also, for ease of perception, only reference and table details are shown in the hierarchy, and simple ones, such as Email or Phone, are listed (alphabetically) at the bottom of the Type Editor.
In addition to the objects listed above, the Integral is a blank sheet on which you can sketch the data structure you need with their connections. Any structure that can be implemented in a relational database. As you probably already guessed, the Integral itself is assembled by Integral means.
To add your objects - the terms of your business - you simply specify a name and specify the base type: string, number, file, etc. A total of 16 basic types are available, most of which determine the presentation of the data, the rest define the behavior — for example, a context action.
To make life easier for the user, we simplify the basic data types as much as possible. The data of any type is stored in the same way - as a sequence of bytes, and the base type defines the output format, comparison and validation rules, 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 requisite. You can enter numbers with any number of characters, and in the overwhelming majority of cases (10-12 characters before or after the comma) this will work correctly without any extra questions (the default for SIGNED is 2 decimal places). If you need to output numbers in the desired format, you can bind these formats as additional details and use them in user-created templates, but more on that later.
Types are named with the appropriate English words so that programmers will be accustomed to start working with them. If you wish, you can translate them into human language by correcting one reference book in the Type Editor. So far we have not observed any difficulties with understanding the values ​​of the types of any category of Integral users, and there is a suspicion that they will appear just if we invent Russian-language substitutes. Of course, teaching the MS Excel user how to work with the Integral, we do not use the concepts of entity, props and the like, but operate with simpler concepts such as table, field, column, etc.
Suppose we work with Clients who have a name, telephone, email and address. All of these new to Integral terms, which can also be called objects, types or entities, are added using this simplest form:
So in the Integral all business terms are defined. Then all the entities with which the business deals are collected from them, and they form a data structure. Some terms, such as telephone, email, address already exist in the Integral, and they can be used for their own purposes.
By clicking on a name of any type, you can add 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 Requisites, creating them as necessary, delete, change places, set the simplest rules for them: a mandatory requisite, a calculated value, a pseudonym, a default value. The same type can be added as a prop to different objects.
All objects created in the type editor are immediately available for use: you can create instances of these types, 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 whose details. So we see the Access type in the list, because the access attribute of the Object type is the link to the independent Access Reference. But the Objects and Menu types are missing from the list, since 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 (for any requisite), list deletion, export and import. By clicking "User" in the Dictionary, you can see a list of all instances of the type User:
To edit an entry in the table, click on the value in the first column of this table, in this case the first field - “User”.
Note: The ability to delete, edit, export, visibility of details and other restrictions in working with objects are set in the user's Roles with the accuracy of 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, first specify the value of the instance of this type:
Then fill in the details of this type:
The simplest rules specified in the type editor are applied to the details: email and Name - required fields, Date - automatically calculated value of the current date.
In the Type Editor, there are a couple of techniques that allow you to create relationships between entities:
In this case, everything is simple and familiar: you can create the Category type, and then, clicking on it, select the “Create a link” option:
In the type editor, you get two types named Category: the type itself and a link to its instance.
In the list of details, there will also be two of them, the link is marked with a prefix in the form of an arrow:
Adding the reference attribute, we will get two related tables, and Integral will build them in the type editor according to the hierarchy (reference and table details are located to the right of its parent):
You can assign a pseudonym to the reference props (highlighted in the picture in bold), which will be convenient when using the same directory in different requisites with different meanings, for example, legal. A person can be a customer, contractor, shipper, and so on.
We can use any other registered type as a requisite by specifying the instance ID as a numerical requisite and substituting this ID in a link on the editing form or in the report. For example, you can make people, cars, buildings, or copyrights the object of protection.
This type of props is convenient for quick navigation between the associated data: you can subordinate to the object an array of records that will always be at hand when working with the parent object. For example, the Role entity contains two attributes: Objects and Menu items, each of which has details, therefore, allows you to specify a set of values ​​of the corresponding attribute for the Role type:
In the data dictionary, this will look like a set of tables between which you can quickly move, only an array will be displayed instead of one related record. When moving from Roles to objects, we will see all the objects included in this role.
The methods described above for creating types, adding details and two types of links already allow you to create any data structure possible in a relational database.
To work with data in the Integral there is a query designer. In the simplest case, which covers much more than half of user needs, it is sufficient to simply list the required data fields, and 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 access 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 object Query:
Set the name of our request and click Add:
Go to the requisite Data field of the created query:
We see an empty table of fields in which you can add objects from the list. The list is relatively long because it includes all the objects registered in our instance of the service. We need a user - choose, add.
The created User data field has a lot of details that we do not need yet (we will definitely consider them later in this article). Save the record as it is:
Note : the query designer can be clothed in a more attractive visual interface, compact and without unnecessary details, as is done in the example about composite types . We deliberately did not do this in the basic interface in order not to impair its functionality.
The row User appeared in the table of data fields, and the list of available objects was reduced - now there are only objects associated with the User. Select the role that we need from the problem statement:
Now the Objects and Menus associated with the User through the Role have appeared in the list. Adding Objects:
Finally, we add an access level description, which is an Access attribute:
Everything, our request is ready, it includes four data fields from four different tables.
Clicking the name of the request, we will move from the list of fields to the form of the request itself, where there is a “Generate” button. This button is a requisite that launches the programmed context action, in this case, a call to a report form with the ID of this report (request).
A running query will return a report on all users, roles, objects, and access levels:
We did not think about the relationships between the object tables 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, as well as downloaded to Excel.
Any objects created in the service can be used in the data sampling, and the Integral itself will link all the tables involved. The query designer allows you to create analogs of almost any SQL queries (so far, except for exotics, such as recursive queries), while it removes from the user the care of indexes, foreign keys and other details that are not directly related to the user's business.
In some cases, you may need to specify your table join rules, as you used to do when writing SQL queries, and Integral will allow you to do this. I will discuss this later in this article.
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 insert data. Data can be selected by a query from the database or taken from the context of working with the template: name and user ID; parameters transmitted via GET and POST; data of higher and neighboring blocks; global constants, etc.
Processing HTML-based content templates is made on the server, and each user action leads to the formation of a new page. Initially, there are only 6 form templates in the interface - one for each mode of operation plus one main page.
Forms have the following purpose:
Custom forms can be loaded in the Files menu, in the templates directory, where the main page and the empty initial form (containing a greeting and a link to the Integral User Guide) are initially located:
If anyone needs to replace the other forms of the basic interface, then you can download them from Github and put them in the same folder.
At the time of this writing, the insertion points used in the templates are of three types:
“Layout of blocks” defines fragments of the template that are repeated zero or more times, depending on how many records the data source for this block provides - the name of the source is indicated in the markup. "Data fields" indicate where to insert specific values ​​from data source records. The “File” contains the text of the template that will be found in the file system, parsed and inserted into the specified location. The nesting level of blocks and files is not limited by the system.
The figure below shows the HTML code for the template of the basic interface page (main.html is 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 access the database for a TopMenu request and get a set of records in which it will try to find the TOP_MENU_HREF and TOP_MENU data fields. The result of the query - the report - is surrounded by a gray frame in the picture. The values ​​of these fields will be substituted into the template instead of the names of these fields in curly brackets. The template fragment, highlighted in gray in the figure, will be repeated as many times as the records will be returned by the TopMenu set. The tags <! - Begin: ... -> and <! - End: ... -> will not be included in the result.
Next, the parser will encounter the <! - File: a -> construct, instead of which the contents of the template file with the name specified by the a parameter, previously also processed by the parser, will be inserted.As a result, the page will display a menu consisting of the items available to the user's role, and the workplace according to the menu item selected earlier. Clicking the desired object, the user will move to another workstation, which is also collected 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 queries to select data, fill the forms with these data and make changes to the database by means of the base interface or customized user forms.
The results of the queries are available in the form of JSON, so you can make your one-page application that will use your template engine, relying on the capabilities of Integral for organizing the database and the mechanism of roles for constructing queries. To request a report in JSON format, you must pass the JSON parameter when calling the report:
Type editor, report builder and custom forms provide a combination of possibilities that you will hardly find in another development tool. The data blocks and the rules for their completion complement the integral to the complete platform: with the help of 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 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 next sections will be waiting for you a bit hardcore.
After a brief review of the conceptual possibilities of Integral, I will talk about 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 Guide on 40 pages, where almost everything that is known about the service is described. Who, on the contrary, already has enough information, you can go 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 service level.
In the Integral indexed all the data of all tables. Yes, you can make a table with any number of columns, load there many, many records, and then filter the data by arbitrary fields, and the search will be performed using an index if it applies to the sample condition. You do not need to pre-care 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 for filtering and merging data in the report, still freeing you from thinking about links, keys and indexes wherever possible. As a result, queries are executed in approximately the same way as they would be executed in a relational database with manually configured indexes.
Such indexing requires more space for data storage than a selectively indexed database (2-3 times), but it saves energy and nerves with the development and support, which is incomparably cheaper in money. These overheads increase linearly, and in some cases turn out to be less than the cost of storing data in a database with multiple composite indices, in the 1C platform with its GUID and denormalization or, especially, in a CMS like Bitrix with its information blocks.
In order not to breed theoretical holivar about indexes and performance, we deployed a test benchwhere you can watch 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 requests, so you can get an adequate idea of ​​the performance.
The integral provides minimal means of maintaining data integrity, for example, by not allowing the reference value to be removed if it is used as a prop. The same applies to the types and details of objects - you cannot delete a type or attribute if there are instances of it or it is mentioned in a request or role. There is also a check for the uniqueness of the added objects, but only in the simplest case: if a non-unique value of an object is added that is marked unique in the Type Editor.
Such things as triggers, keys, constraints and much more are intentionally removed from the Integral core. Also, everything that was possible to hide from the user was hidden, for example, the system ID of records (this is unusual for a programmer, but is quickly absorbed). However, if desired, any ID can be obtained from the system, and in some cases it is just necessary to communicate via API, for example.
It should be noted that the Integral does not develop in the direction of enhancing technical barrier means, such as the mechanism of transactions, locks and restrictions, but rather encourages drawing up 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 the hands in case of an error, it is better to simply prevent the user from making a mistake. This slippery topic will be discussed in a separate article, supported by a more specific theory and real life examples.
Integral can make changes to the data selected as a result of the query. Its architecture allows to do so, so it’s a sin not to seize the opportunity. By default, changes are made in two stages: a preview of the upcoming changes, then confirmation. You can also execute the request without confirmation.
In Report Designer, the Data Field has a “Assign” attribute. In this attribute, you can write a value, whether it is 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 is convenient, for example, if you need to schedule meetings with clients, conduct billing operations, or simply delete old entries. Let's see how this works on the example of a small data structure:
To begin, let us write down the note “Autumn 2017” for all customers with whom contracts were concluded in the fall of 2017. To do this, we will create a query that selects such customers, and it will look something like this: The
query 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 will set a new attribute value Note (attribute “Assign”), and also give the column names more attractive headings:
Having launched our updated report, we will get a hint about what changes will be made to the data by this query:
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 attribute “Assign” using the SQL language function, which concatenates the existing text with new. We will assign the MEMO alias to this column, and according to this alias we can substitute its value into the CONCAT () function concatenating the text fragments:
Now the request will do exactly what we want:
By clicking “Run” the request will make changes to the database, issuing the corresponding report :
In the Dictionary, in the customer table, we will see new customer data:
You may ask: what will happen if an object is found in the report more than once, and we are trying to change it. Answer: the object will be changed only once. For example, in the following report, we add a Client Note, which is mentioned in several lines of the report, and Integral will perform the change only once 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 different tables, for example, to obfuscate (impersonate) the personal data of clients before uploading data to the test server.
The service allows you to specify new values ​​for the required number of data fields, including from different entities, - the Integral will accurately spread all these changes to the required tables.
Now we will demonstrate the creation of new records. Example: we need to schedule a meeting with all clients in the “Client” status at noon on February 1, 2018. Create such a query:
The resulting report will display a hint about the records being created, and by clicking “Run” the meeting records will be added, while rerunning the query will not duplicate the records already created, if any.
These entries will appear in the Dictionary in Customer Meetings:
To delete records, it is enough to assign them empty values ​​- "". An example of deleting meeting entries is quite simple:
The integral does not waste space on empty values, so the records will be simply deleted, with all their details and subordinate tables, if any:
The integral allows access to external systems (websites, APIs, files, etc.) using the http (s) protocol during the execution of a request. To do this, you need to set the external source URL pattern in the report URL field (in this example, I renamed the Request to Report type in the Type Editor). You can use report field values ​​in a template by enclosing their names in square brackets.
Further, when selecting report columns, you can give them names, and the values ​​of these columns will be substituted into the URL pattern:
The abn_URL function replaces the contents of its field with the result of the query to the resulting URL, calculated for each row of the report. As a result of executing, for example, a request to the API of the SMS service provider specified above, we will receive a response from the service with the ID of the sent message that will be sent to the [phone] phone that we specified to the recipient with the name [name]:
The result of the query to the URL can also be saved in the database, assigning its value to a field.
Finally, I would like to give an example, which for some reason greatly impressed our client from 1C environment - the problem of composite types. The task was to make a report on the operations in the context of different analytics, whose composition 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 (quickly and dirty, I'm sorry) are available on GitHub: Aggregate Types .
So, a chart of accounts is given with an indication of a set of analytics for each account (in the figure - a fragment of the plan):
The chart of accounts was imported into Integral, and analytic objects — types with a set of attributes, were created in the Type Editor, for example:
Nomenclature is a hierarchical reference with reference to the parent element.
Further, in the Type Editor, we created the Operation type with accounts and , for which, in turn, a valid set of analytics objects is specified:
Unfilled details of the object do not occupy space in the database. If the set of analytics objects is very large, then you can not clutter up the list of details with them, but create these types with a certain attribute that can be added to the complex type. Although you can even do without it and add everything the user has access to.
When setting up operations, you can set an analytics according to the involved accounts and its actual parameters:
Next, we form a report on operations, where you can choose an arbitrary set of analyst of interest to us separately for the accounts Dt and Kt. For each analytics, you can set the conditions for selecting records (the 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 up for this form of input. In fact, during an analyst assignment, records are added and deleted in the “Analytics Report” Request Data Fields, and this is how it looks in the Dictionary:
This report will select all transactions that have the required set of analytics that satisfy the selection conditions. Of course, we can include in the report the details of analytics objects for all their depth, when the customer needs it.
The result of the report is the sum of operations for each analytic combination:
The main task of the Integral for the broad user is to enable it to load its data directly, without using or even knowing programming languages ​​and database theory. So, in its time, the SQL language appeared, which offered any accountant to independently work with data in the human language. We go the same way, but we try to do without language and theories altogether, since any need to do something that does not concern business discourages a person from using an automation tool.
Integral's motto is “not more complicated than in Excel”. Integral can replace Excel with a more flexible and secure means of storing and processing data. Is someone worried that this is SaaS? Ask him if he is mailing his Excel and does not store them in Google boxes, that is, services that professionally know how to wool data, extracting from them all the necessary information for themselves and authorized services?
First of all, it is a small business service, in which often the owner has to develop himself or at least manage it. The IT component is critical for the company, since it is this that determines the agility and flexibility of the business. We try to give the opportunity to start work immediately, not to be distracted by non-core topics and be able to radically redo everything if necessary. Has the accounting principle changed? Turn the entire model inside out, throw out the excess and add the one you need! Integral allows you to do this quickly and quite clearly.
Programmers have always dreamed of programming less commonplace things, so that there was more time for great accomplishments.
Our project is an attempt to make a tool for this, and the Integral team managed to achieve its practical performance on the data volumes that can be encountered in the applied development of the lower and middle price segment. Under the "practical performance" refers to the following criteria:
The Integral core is rather compact, and low-level work with the data on our calculations can be performed in hardware at all. Goodbye Meltdown and Specter; hello, import substitution ... Suddenly? In the meantime, nothing fundamentally new has been happening in IT for a long time (may the apologists of quantum computers forgive me, but they are still far from practical application), and that dreamer who does not seek to do something in practice is bad. The service architecture is fundamentally different from existing platforms, and we believe it can change the IT world, as it did, for example, the emergence of an open IBM PC architecture.
The service has been actively developing only the last couple of years, and we see that with such a small (and unstable, because everyone needs money) team, we have a decade to work with a normal product, and the team has to sacrifice a lot to continue the project. At the same time, in the very direction we see undeniable prospects, which, in particular, prompted to write this article - we want to cooperate with all who are ready to develop, improve and popularize Integral's approach.
Developer's Guide
Register your reference copy of the service: https://tryint.ru
The platform has some fundamental differences from the infinite set of "constructors", because of which it appeared. Some of the differences are worthy of a quality holivar, others simply simplify the developer’s life, whoever it is. Several applications are already working with live clients, of which working examples of performing tasks will be given.
Here you can build a web application without learning a programming language: we use only business terms and formulas, no more difficult than in MS Excel. Of course, an understanding of the principles of database operation will help you develop a more robust, large-scale and functional-rich product, but this service does not require specific knowledge for simple solutions that make up at least 80% of applied development (for example, handicraft and everything that is now). works in Excel).
Disclaimer
Now this is just a simple service, a concept that implements an approach to data storage and application development, the adherents of which are a small team of the project of the same name. The service does not claim to be full of possibilities, we still have a lot of work ahead, but for now we have only confirmed the concept of the original application designer for ourselves, create the product and are ready to develop it in cooperation with everyone.
')
Integral
The integral is an architecture core that provides a basic user interface for defining a data model (metadata) and working with data. The basic interface uses several simplest commands to control the data structure (DDL) and 6 commands to modify 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 service interaction with the outside world is as follows:
The kernel does all the dirty work: data binding, simplest validation, integrity monitoring, building queries in the database language, type casting and other uninteresting stuff. You only specify what data you need to send or request, what request to run, how the result will be formatted in the user interface. Of course, if you wish, you can intervene and adjust the work of the kernel in the part related to business logic.
Integral "programming" is as follows:
- setting process parameters - stages, roles, properties, structures, formats, etc .;
- creating queries for sampling and modifying data, including creating and modifying queries themselves;
- creating user interface templates with insertion points for data.
The user logs in, receives menus according to his role, scans information, enters data, runs requests, moves the process along specified stages, receives reports. All interaction with the system occurs through the forms specified by the templates. Very similar to the work of CMS and, as our designer says, “done as in the first Bitra”.
To work using a basic interface that can be supplemented and expanded to fit your needs. Forms of the basic interface are available on Github , you can use them freely - rewrite completely with blackjack and ladies or simply “pull” the desired styles.
Data structure
The 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 reconfigured.
Initially, we get a database in which some entities are described (called hereafter objects or types). Integral core already knows about their nature and principles of operation. There are some of them:
- User;
- User role;
- Objects included in the Role (all Integral objects, including Role and User);
- Object access level;
- Query (analogue SQL query in a relational database);
- Request fields (names of all system objects, including newly created ones).
The listed objects have several properties that the kernel also knows about. In the aggregate, the initial content gives a minimum of functionality, which 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: the User is assigned a Role, 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 Functions, Formats can be applied, and the totals for them can be counted. Here, for clarity, the links are depicted by arrows between objects, but in the Type Editor, rectangle objects are not linked by arrows, and the hierarchy is indicated only by placing children to the right of the parent with the list spreading down. This is done because on a small scheme the subordination of objects is already obvious, while on a large scheme the arrows do not add convenience, but only clutter up the picture.
Also, for ease of perception, only reference and table details are shown in the hierarchy, and simple ones, such as Email or Phone, are listed (alphabetically) at the bottom of the Type Editor.
In addition to the objects listed above, the Integral is a blank sheet on which you can sketch the data structure you need with their connections. Any structure that can be implemented in a relational database. As you probably already guessed, the Integral itself is assembled by Integral means.
To add your objects - the terms of your business - you simply specify a name and specify the base type: string, number, file, etc. A total of 16 basic types are available, most of which determine the presentation of the data, the rest define the behavior — for example, a context action.
| Type of | Description |
|---|---|
| SHORT | Short string (up to 127 characters) |
| CHARS | Unlimited string |
| Date | date |
| NUMBER | Integer |
| SIGNED | Number with decimal point |
| Boolean | Boolean (Yes / No) |
| MEMO | Multi-line text (differs from CHARS only in the input window) |
| DATETIME | Date with time |
| FILE | File |
| HTML | HTML code (tags are not escaped when displayed in queries) |
| BUTTON | Button for context action (report, query, link) |
| Pwd | Password (hash, salted base name, user, and something else) |
| GRANT | Access object (link to any system object) |
| CALCULATABLE | Calculated value (temporarily abolished) |
| REPORT_COLUMN | Data field (reference to the system object) |
| FULLTEXT | Full text search string (in the next release) |
To make life easier for the user, we simplify the basic data types as much as possible. The data of any type is stored in the same way - as a sequence of bytes, and the base type defines the output format, comparison and validation rules, 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 requisite. You can enter numbers with any number of characters, and in the overwhelming majority of cases (10-12 characters before or after the comma) this will work correctly without any extra questions (the default for SIGNED is 2 decimal places). If you need to output numbers in the desired format, you can bind these formats as additional details and use them in user-created templates, but more on that later.
Types are named with the appropriate English words so that programmers will be accustomed to start working with them. If you wish, you can translate them into human language by correcting one reference book in the Type Editor. So far we have not observed any difficulties with understanding the values ​​of the types of any category of Integral users, and there is a suspicion that they will appear just if we invent Russian-language substitutes. Of course, teaching the MS Excel user how to work with the Integral, we do not use the concepts of entity, props and the like, but operate with simpler concepts such as table, field, column, etc.
Suppose we work with Clients who have a name, telephone, email and address. All of these new to Integral terms, which can also be called objects, types or entities, are added using this simplest form:
So in the Integral all business terms are defined. Then all the entities with which the business deals are collected from them, and they form a data structure. Some terms, such as telephone, email, address already exist in the Integral, and they can be used for their own purposes.
Requisites
By clicking on a name of any type, you can add 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 Requisites, creating them as necessary, delete, change places, set the simplest rules for them: a mandatory requisite, a calculated value, a pseudonym, a default value. The same type can be added as a prop to different objects.
All objects created in the type editor are immediately available for use: you can create instances of these types, 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 whose details. So we see the Access type in the list, because the access attribute of the Object type is the link to the independent Access Reference. But the Objects and Menu types are missing from the list, since 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 (for any requisite), list deletion, export and import. By clicking "User" in the Dictionary, you can see a list of all instances of the type User:
To edit an entry in the table, click on the value in the first column of this table, in this case the first field - “User”.
Note: The ability to delete, edit, export, visibility of details and other restrictions in working with objects are set in the user's Roles with the accuracy of 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, first specify the value of the instance of this type:
Then fill in the details of this type:
The simplest rules specified in the type editor are applied to the details: email and Name - required fields, Date - automatically calculated value of the current date.
In the Type Editor, there are a couple of techniques that allow you to create relationships between entities:
- For any type, you can create a link to this type, and then use this link as a prop - a pointer to an instance of the original type.
- An entity's requisite itself is editable as a subordinate table, to which you can add ordered records.
Props - type reference
In this case, everything is simple and familiar: you can create the Category type, and then, clicking on it, select the “Create a link” option:
In the type editor, you get two types named Category: the type itself and a link to its instance.
In the list of details, there will also be two of them, the link is marked with a prefix in the form of an arrow:
Adding the reference attribute, we will get two related tables, and Integral will build them in the type editor according to the hierarchy (reference and table details are located to the right of its parent):
You can assign a pseudonym to the reference props (highlighted in the picture in bold), which will be convenient when using the same directory in different requisites with different meanings, for example, legal. A person can be a customer, contractor, shipper, and so on.
We can use any other registered type as a requisite by specifying the instance ID as a numerical requisite and substituting this ID in a link on the editing form or in the report. For example, you can make people, cars, buildings, or copyrights the object of protection.
Props - subordinate table
This type of props is convenient for quick navigation between the associated data: you can subordinate to the object an array of records that will always be at hand when working with the parent object. For example, the Role entity contains two attributes: Objects and Menu items, each of which has details, therefore, allows you to specify a set of values ​​of the corresponding attribute for the Role type:
In the data dictionary, this will look like a set of tables between which you can quickly move, only an array will be displayed instead of one related record. When moving from Roles to objects, we will see all the objects included in this role.
The methods described above for creating types, adding details and two types of links already allow you to create any data structure possible in a relational database.
Requests
To work with data in the Integral there is a query designer. In the simplest case, which covers much more than half of user needs, it is sufficient to simply list the required data fields, and 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 access 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 object Query:
Set the name of our request and click Add:
Go to the requisite Data field of the created query:
We see an empty table of fields in which you can add objects from the list. The list is relatively long because it includes all the objects registered in our instance of the service. We need a user - choose, add.
The created User data field has a lot of details that we do not need yet (we will definitely consider them later in this article). Save the record as it is:
Note : the query designer can be clothed in a more attractive visual interface, compact and without unnecessary details, as is done in the example about composite types . We deliberately did not do this in the basic interface in order not to impair its functionality.
The row User appeared in the table of data fields, and the list of available objects was reduced - now there are only objects associated with the User. Select the role that we need from the problem statement:
Now the Objects and Menus associated with the User through the Role have appeared in the list. Adding Objects:
Finally, we add an access level description, which is an Access attribute:
Everything, our request is ready, it includes four data fields from four different tables.
Clicking the name of the request, we will move from the list of fields to the form of the request itself, where there is a “Generate” button. This button is a requisite that launches the programmed context action, in this case, a call to a report form with the ID of this report (request).
A running query will return a report on all users, roles, objects, and access levels:
We did not think about the relationships between the object tables 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, as well as downloaded to Excel.
Any objects created in the service can be used in the data sampling, and the Integral itself will link all the tables involved. The query designer allows you to create analogs of almost any SQL queries (so far, except for exotics, such as recursive queries), while it removes from the user the care of indexes, foreign keys and other details that are not directly related to the user's business.
In some cases, you may need to specify your table join rules, as you used to do when writing SQL queries, and Integral will allow you to do this. I will discuss 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 insert data. Data can be selected by a query from the database or taken from the context of working with the template: name and user ID; parameters transmitted via GET and POST; data of higher and neighboring blocks; global constants, etc.
Processing HTML-based content templates is made on the server, and each user action leads to the formation of a new page. Initially, there are only 6 form templates in the interface - one for each mode of operation plus one main page.
Forms have the following purpose:
- The main page, as a rule, contains styles and the main menu (or several);
- A type editor that displays the data structure;
- Dictionary - a list of all independent types;
- The list of instances of the selected type (lists of users, roles, objects, discussed above);
- Type edit form;
- Report.
Custom forms can be loaded in the Files menu, in the templates directory, where the main page and the empty initial form (containing a greeting and a link to the Integral User Guide) are initially located:
If anyone needs to replace the other forms of the basic interface, then you can download them from Github and put them in the same folder.
At the time of this writing, the insertion points used in the templates are of three types:
- pattern markup;
- data field;
- file.
“Layout of blocks” defines fragments of the template that are repeated zero or more times, depending on how many records the data source for this block provides - the name of the source is indicated in the markup. "Data fields" indicate where to insert specific values ​​from data source records. The “File” contains the text of the template that will be found in the file system, parsed and inserted into the specified location. The nesting level of blocks and files is not limited by the system.
The figure below shows the HTML code for the template of the basic interface page (main.html is 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 access the database for a TopMenu request and get a set of records in which it will try to find the TOP_MENU_HREF and TOP_MENU data fields. The result of the query - the report - is surrounded by a gray frame in the picture. The values ​​of these fields will be substituted into the template instead of the names of these fields in curly brackets. The template fragment, highlighted in gray in the figure, will be repeated as many times as the records will be returned by the TopMenu set. The tags <! - Begin: ... -> and <! - End: ... -> will not be included in the result.
Next, the parser will encounter the <! - File: a -> construct, instead of which the contents of the template file with the name specified by the a parameter, previously also processed by the parser, will be inserted.As a result, the page will display a menu consisting of the items available to the user's role, and the workplace according to the menu item selected earlier. Clicking the desired object, the user will move to another workstation, which is also collected 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 queries to select data, fill the forms with these data and make changes to the database by means of the base interface or customized user forms.
The results of the queries are available in the form of JSON, so you can make your one-page application that will use your template engine, relying on the capabilities of Integral for organizing the database and the mechanism of roles for constructing queries. To request a report in JSON format, you must pass the JSON parameter when calling the report:
Type editor, report builder and custom forms provide a combination of possibilities that you will hardly find in another development tool. The data blocks and the rules for their completion complement the integral to the complete platform: with the help of 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 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 next sections will be waiting for you a bit hardcore.
A little deeper and more about the above
After a brief review of the conceptual possibilities of Integral, I will talk about 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 Guide on 40 pages, where almost everything that is known about the service is described. Who, on the contrary, already has enough information, you can go 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 service level.
Indexing and data integrity
In the Integral indexed all the data of all tables. Yes, you can make a table with any number of columns, load there many, many records, and then filter the data by arbitrary fields, and the search will be performed using an index if it applies to the sample condition. You do not need to pre-care 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 for filtering and merging data in the report, still freeing you from thinking about links, keys and indexes wherever possible. As a result, queries are executed in approximately the same way as they would be executed in a relational database with manually configured indexes.
Lyrical digression about optimization
, ( , ) . , , , . , , JIRA AJAX-spinner -.
, . , . , , «» , , . «» , , . , .
, , , , , . .
, . , . , , «» , , . «» , , . , .
, , , , , . .
Such indexing requires more space for data storage than a selectively indexed database (2-3 times), but it saves energy and nerves with the development and support, which is incomparably cheaper in money. These overheads increase linearly, and in some cases turn out to be less than the cost of storing data in a database with multiple composite indices, in the 1C platform with its GUID and denormalization or, especially, in a CMS like Bitrix with its information blocks.
In order not to breed theoretical holivar about indexes and performance, we deployed a test benchwhere you can watch 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 requests, so you can get an adequate idea of ​​the performance.
The integral provides minimal means of maintaining data integrity, for example, by not allowing the reference value to be removed if it is used as a prop. The same applies to the types and details of objects - you cannot delete a type or attribute if there are instances of it or it is mentioned in a request or role. There is also a check for the uniqueness of the added objects, but only in the simplest case: if a non-unique value of an object is added that is marked unique in the Type Editor.
Such things as triggers, keys, constraints and much more are intentionally removed from the Integral core. Also, everything that was possible to hide from the user was hidden, for example, the system ID of records (this is unusual for a programmer, but is quickly absorbed). However, if desired, any ID can be obtained from the system, and in some cases it is just necessary to communicate via API, for example.
It should be noted that the Integral does not develop in the direction of enhancing technical barrier means, such as the mechanism of transactions, locks and restrictions, but rather encourages drawing up 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 the hands in case of an error, it is better to simply prevent the user from making a mistake. This slippery topic will be discussed in a separate article, supported by a more specific theory and real life examples.
Data fields: read more
:
— , — , .
. , , SQL ( , , MySQL), . .
(), () — ( ) ( ). .
— , . : AVG (), COUNT (), MAX (), MIN (), SUM (), , , , ( ).
(), () — ( ) ( ), , ( HAVING SQL), .
— , , , .
— . .
. — . ( ).
— , . , .
— . .
Alias — .
— , — , .
. , , SQL ( , , MySQL), . .
(), () — ( ) ( ). .
— , . : AVG (), COUNT (), MAX (), MIN (), SUM (), , , , ( ).
(), () — ( ) ( ), , ( HAVING SQL), .
— , , , .
— . .
. — . ( ).
— , . , .
— . .
Alias — .
Data change requests
Integral can make changes to the data selected as a result of the query. Its architecture allows to do so, so it’s a sin not to seize the opportunity. By default, changes are made in two stages: a preview of the upcoming changes, then confirmation. You can also execute the request without confirmation.
In Report Designer, the Data Field has a “Assign” attribute. In this attribute, you can write a value, whether it is 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 is convenient, for example, if you need to schedule meetings with clients, conduct billing operations, or simply delete old entries. Let's see how this works on the example of a small data structure:
Data change
To begin, let us write down the note “Autumn 2017” for all customers with whom contracts were concluded in the fall of 2017. To do this, we will create a query that selects such customers, and it will look something like this: The
query 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 will set a new attribute value Note (attribute “Assign”), and also give the column names more attractive headings:
Having launched our updated report, we will get a hint about what changes will be made to the data by this query:
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 attribute “Assign” using the SQL language function, which concatenates the existing text with new. We will assign the MEMO alias to this column, and according to this alias we can substitute its value into the CONCAT () function concatenating the text fragments:
Now the request will do exactly what we want:
By clicking “Run” the request will make changes to the database, issuing the corresponding report :
In the Dictionary, in the customer table, we will see new customer data:
You may ask: what will happen if an object is found in the report more than once, and we are trying to change it. Answer: the object will be changed only once. For example, in the following report, we add a Client Note, which is mentioned in several lines of the report, and Integral will perform the change only once 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 different tables, for example, to obfuscate (impersonate) the personal data of clients before uploading data to the test server.
The service allows you to specify new values ​​for the required number of data fields, including from different entities, - the Integral will accurately spread all these changes to the required tables.
Adding records
Now we will demonstrate the creation of new records. Example: we need to schedule a meeting with all clients in the “Client” status at noon on February 1, 2018. Create such a query:
The resulting report will display a hint about the records being created, and by clicking “Run” the meeting records will be added, while rerunning the query will not duplicate the records already created, if any.
These entries will appear in the Dictionary in Customer Meetings:
Deleting records
To delete records, it is enough to assign them empty values ​​- "". An example of deleting meeting entries is quite simple:
The integral does not waste space on empty values, so the records will be simply deleted, with all their details and subordinate tables, if any:
Using requests to external sources
The integral allows access to external systems (websites, APIs, files, etc.) using the http (s) protocol during the execution of a request. To do this, you need to set the external source URL pattern in the report URL field (in this example, I renamed the Request to Report type in the Type Editor). You can use report field values ​​in a template by enclosing their names in square brackets.
Further, when selecting report columns, you can give them names, and the values ​​of these columns will be substituted into the URL pattern:
The abn_URL function replaces the contents of its field with the result of the query to the resulting URL, calculated for each row of the report. As a result of executing, for example, a request to the API of the SMS service provider specified above, we will receive a response from the service with the ID of the sent message that will be sent to the [phone] phone that we specified to the recipient with the name [name]:
The result of the query to the URL can also be saved in the database, assigning its value to a field.
Example task with composite types
Finally, I would like to give an example, which for some reason greatly impressed our client from 1C environment - the problem of composite types. The task was to make a report on the operations in the context of different analytics, whose composition 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 (quickly and dirty, I'm sorry) are available on GitHub: Aggregate Types .
So, a chart of accounts is given with an indication of a set of analytics for each account (in the figure - a fragment of the plan):
The chart of accounts was imported into Integral, and analytic objects — types with a set of attributes, were created in the Type Editor, for example:
Nomenclature is a hierarchical reference with reference to the parent element.
Further, in the Type Editor, we created the Operation type with accounts and , for which, in turn, a valid set of analytics objects is specified:
Unfilled details of the object do not occupy space in the database. If the set of analytics objects is very large, then you can not clutter up the list of details with them, but create these types with a certain attribute that can be added to the complex type. Although you can even do without it and add everything the user has access to.
When setting up operations, you can set an analytics according to the involved accounts and its actual parameters:
Next, we form a report on operations, where you can choose an arbitrary set of analyst of interest to us separately for the accounts Dt and Kt. For each analytics, you can set the conditions for selecting records (the 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 up for this form of input. In fact, during an analyst assignment, records are added and deleted in the “Analytics Report” Request Data Fields, and this is how it looks in the Dictionary:
This report will select all transactions that have the required set of analytics that satisfy the selection conditions. Of course, we can include in the report the details of analytics objects for all their depth, when the customer needs it.
The result of the report is the sum of operations for each analytic combination:
Business orientation
The main task of the Integral for the broad user is to enable it to load its data directly, without using or even knowing programming languages ​​and database theory. So, in its time, the SQL language appeared, which offered any accountant to independently work with data in the human language. We go the same way, but we try to do without language and theories altogether, since any need to do something that does not concern business discourages a person from using an automation tool.
Integral's motto is “not more complicated than in Excel”. Integral can replace Excel with a more flexible and secure means of storing and processing data. Is someone worried that this is SaaS? Ask him if he is mailing his Excel and does not store them in Google boxes, that is, services that professionally know how to wool data, extracting from them all the necessary information for themselves and authorized services?
First of all, it is a small business service, in which often the owner has to develop himself or at least manage it. The IT component is critical for the company, since it is this that determines the agility and flexibility of the business. We try to give the opportunity to start work immediately, not to be distracted by non-core topics and be able to radically redo everything if necessary. Has the accounting principle changed? Turn the entire model inside out, throw out the excess and add the one you need! Integral allows you to do this quickly and quite clearly.
Why all this for us?
Programmers have always dreamed of programming less commonplace things, so that there was more time for great accomplishments.
Our project is an attempt to make a tool for this, and the Integral team managed to achieve its practical performance on the data volumes that can be encountered in the applied development of the lower and middle price segment. Under the "practical performance" refers to the following criteria:
- the rate of performance degradation with the growth of volume and complexity is not higher than that of the information system, made in the classical way, and much better than that of existing designers;
- the absence of restrictions inherent in the "designers" in terms of creating and using a data model;
- reduced threshold of entry from the point of view of knowledge of programming languages ​​(knowledge of the architectural foundations of information systems, finally, takes its proper place here, pushing aside the expertise in short-term technologies).
The Integral core is rather compact, and low-level work with the data on our calculations can be performed in hardware at all. Goodbye Meltdown and Specter; hello, import substitution ... Suddenly? In the meantime, nothing fundamentally new has been happening in IT for a long time (may the apologists of quantum computers forgive me, but they are still far from practical application), and that dreamer who does not seek to do something in practice is bad. The service architecture is fundamentally different from existing platforms, and we believe it can change the IT world, as it did, for example, the emergence of an open IBM PC architecture.
The service has been actively developing only the last couple of years, and we see that with such a small (and unstable, because everyone needs money) team, we have a decade to work with a normal product, and the team has to sacrifice a lot to continue the project. At the same time, in the very direction we see undeniable prospects, which, in particular, prompted to write this article - we want to cooperate with all who are ready to develop, improve and popularize Integral's approach.
Links
Developer's Guide
Register your reference copy of the service: https://tryint.ru
Source: https://habr.com/ru/post/346816/
All Articles