Some features of the tmaplatform development environment programming language

This post talks about programming languages ​​and queries of the tmaplatform accounting application development environment .

Everyone has different tastes ...

When working with the platform, you can use several programming languages: Pascal (Similar Pascal), BASIC (Similar Basic). A Java-like language is coming up soon. All post examples are written in Similar Pascal.

The query language is integrated into the platform, which we called “Internal SQL”, most of the post will be about it.

Independence from DBMS.

Each DBMS has its pros and cons; different DBMSs are designed for different tasks and the amount of data stored. And just different developers are used to different database servers. This is an axiom.

The tmaplatform development environment allows you to easily (easily - without changing the code) transfer programs between different database servers: MySQL (works), PgSQL (testing), MSSQL (testing), SQLite (testing), in the future this list will be expanded. And we all know that with all the universality of the SQL language, it just won’t be possible to transfer a program for working with a database from one server to another. The transition to the active use of triggers, stored procedures and functions will complicate the transition.

Therefore, we added to the platform an internal query language that is as similar as possible to SQL92 (as the basic standard of SQL). When executing your program, the platform translates queries from the internal language into the language of the database used.

This query language does not allow you to use 100% of the capabilities of your favorite database server. Therefore, we left the opportunity to use the database query language (direct queries) together with the internal query language. When using direct queries, portability is lost. But if you use the internal query language in 98% of the program, this will make it easier for you to transfer the program and debug by several orders of magnitude.

Direct requests

Database queries can be written directly in the program code. The query language is part of a programming language. Therefore, immediately at the time of entering the request, the platform checks for the presence of tables, fields and their types. Even before starting the program, the platform will check the correctness of all requests.

ClientId := 3;
D := (Select Sum(Summa) From Documents Where Client = :ClientId);

For example, if the type of the ClientId variable is different from the type of the Client field, then the platform will notify you about this (it will underline the error location with a red line).

Also in the platform editor is available a hint on syntax, field names, database functions and autocompletion.

When refactoring a database, the platform will automatically adjust all queries. That is, when you split the table into two parts, the query " Select A, B From Table " will be replaced by " Select A, LinkField.B From Table1 ".
Query parameters are specified after the ":" symbol, and you can specify not only a variable, but also any expression in a programming language.

AutoJoin Technology

Nothing so briefly and succinctly describes anything in programming, as an example:

Select Account.Client.City.Country.Name From Document

In this request, we get the name of the country in which the city is located in which the client is registered, to whom the account belongs to which the document is issued.

In standard SQL, this query looks like this:

Select Countries.Name From Document
Left Join Accounts On Accounts.Id = Document.Account
Left Join Clients On Clients.Id = Accounts.Client
Left Join Cities On Cities.Id = Clients.City
Left Join Countries On Countries.Id = Cities.Country

True, easier? The volume of the request decreased by 5 times. These 4 Join blocks fall on only one field. And if we have 20 fields? Well, in general, you understand.

Single, new SQL command

The Select operator returns a data set (hereinafter dataset), and if you need to get a single value, you have to add data extraction commands to the program.

D := (Select Sum(S) From Documents Where Client=:Client);
If D.Count=0 Then S := 0 Else S := D[0].S;

The Single operator works similarly to Select, but returns a single value (the first field of the first line).

S := (Single Sum(S) From Documents Where Client=:Client);

Address external queries

When using subqueries, any external query can be addressed by specifying several points in front of the variable name (the quantity depends on the nesting level of the subquery).

Select Name, (Select Sum(Summa) From Money Where Client=.Id) From Clients
Select Name, (Select (Select Sum(Summa) From Money Where Client=..Id)) From Clients

Query optimization

Requests in the program should be as simple and clear as possible (because, perhaps, not only you, but also someone else will have to understand this program). But often such requests are not the fastest. And, besides, the internal query language does not allow using all the DBMS features to write the optimal query.

The platform contains the Query Optimizer tool. With it, you can specify pairs of queries in the internal and external language. That is, specify the fastest query:

Replace with

Select Name, (Select Sum(Summa) From Documents Where Client=.Id) From Clients

Replaced by

Select Client.Name, Sum(Document.Summa) From Clients, Documents Where Clients.Id=Documents.Client

This tool also allows you to experimentally verify the identity of the results of queries and compare the execution time. Optimize the program without complicating it!

Typed datasets.

Any Select query returns a dataset (an object of type Dataset). We did the work with it the same as with the array.

Dataset := DirectQuery('Select * From mysql.user');
For y:=0 To Dataset.Count-1 Do
  For x:=0 To Dataset.Columns.Count-1 Do
   Warning(Dataset[x,y]);

A more interesting feature is typed datasets. In the previous example, Dataset [x, y] returns the Variant data type and type control was not performed at the stage of entering the program text. Typed datasets allow you to access specific fields of a particular data type - Dataset [x]. FieldName. That is, type control works during input, autocompletion, etc.

Procedure OnCreate; 
Var
  D : Dataset Of Record 
        Id : Integer;
        CreateDate : DateTime;
        ClientId : Integer;
        CountryName : String; 
      End;
Begin 
  D := (Select Id, CreateDate, Client, Client.City.Country.Name From Document);
  Warning(D[2].Name); // Обращение к произвольному полю
  Foreach E in D Do // Перебор всех элементов
  Begin
    Warning(E.Id);  // Обращение к полю
    Warning(E.CountryName);  // Обращение к полю
  End;
End;

It should be noted that the field types returned by the Select query, the result of which is assigned to the dataset, must be of the same type as the fields of the dataset itself.

Transactions

The programming language supports the Transaction block. Where without them? Everyone remembers the first example in any textbook with the transfer of money from account to account? :)

Transaction
Begin
  (Insert Table1 ...)
  (Insert Table2 ...)
End;

This block means that other clients will not be able to see the changes in the database until the Transaction block completes. Conversely, you will not be able to see changes made by other customers. If an exception occurs in this block, then all changes made to the database are rolled back.

Transactions can be nested.

You can specify the transaction isolation level.

Transaction Continous Read
Begin
End;

Automatic update

When the database changes, the platform analyzes the request and automatically updates the interface. Moreover, the tables used in the views (view) and calculated fields are taken into account. And only the changed records are updated.

The same mechanism is used in Select triggers and materialized views (view), which the platform implements if the DBMS does not support them. (E.g. MySQL).

SQL to local data

SQL queries can be written not only to database tables, but also to local data.

Procedure OnCreate;
Var
  Data : Dataset Of Record ... End;
  Data1 : Dataset Of Record ... End;
Begin
  ...      
  Data1 := (Select FirstName+' '+LastName, Age From :Data Where Age>20 Order By -Age, Name);
  ...      
End;

At the same time, any functions of the program can be used in queries.

Stored procedures.

Stored procedures, functions and triggers are programs in the DBMS procedural language (T-SQL, PL / SQL, etc.) that are stored and executed on the database server. The platform provides the ability to write these programs in their own language for independence from any particular DBMS (the text of the procedure / trigger is broadcast).

Using the same language and API throughout the program is a definite plus. In addition, Similar Pascal with an integrated query language is more convenient, predictable, and visual than stored procedure languages ​​(e.g., MySQL).

You can simply transfer the code from the program to the stored procedure and vice versa.

And the main plus is that the once written stored procedure will work on all supported database servers.

An example of a stored procedure:

Procedure Recalc(Curr, Contragent : Integer);
Var
  S, CurrRate, NewCurrRate : Currency;
begin
  Foreach C In (Select Id, DocDate, Sum, Curr, NewSum, NewCurr From Documents Where Curr=:Curr And Contragent=:Contragent) Do
  Begin
    CurrRate := GetCurrRate(C.DocDate, C.Curr);
    NewCurrRate := GetCurrRate(C.DocDate, C.NewCurr);
    S := RoundDiv(RoundMul(C.Sum, CurrRate, 4), NewCurrRate, 2);
    If C.Summa <> S Then
      (Update Documents Set Summa = :S Where Id = :C.Id);
  End;
End;

Conclusion

All of the above increases the speed of development, understanding and debugging of the program. These factors, of course, are fundamental when choosing a development environment. If you want to try tmaplatform - sign up for beta testing (throw your email to me ( smirnovss ) in a personal
account ) For a full description of the platform with help, see tmaplatform.ru