Some features of the programming language development environment tmaplatform

This post is about programming languages ​​and tmaplatform accounting application development environment requests.

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 that will appear soon. All post examples are written in Similar Pascal.
')
The query language, called “Internal SQL”, is integrated into the platform, most of the post will be about it.

Independence from DBMS.

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

The tmaplatform development environment makes it easy (easily means without changing the code) to transfer programs between different database servers: MySQL (works), PgSQL (testing), MSSQL (testing), SQLite (testing), later on the list will be expanded. And we all know that with all the universality of the SQL language, it is not possible to simply transfer a program for working with a database from one server to another. The active use of triggers, stored procedures and functions will further complicate the transition.

Therefore, we have added to the platform an internal query language that is as similar to SQL92 as possible (as the basic SQL standard). When executing your program, the platform transmits requests from the internal language to 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 have left the possibility to use the database query language (direct queries) in conjunction 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 ease your program transfer and debugging by several orders of magnitude.

Immediate requests

Database queries can be written directly in the program code. The query language is part of the programming language. Therefore, immediately when entering a request, the platform checks the presence of tables, fields and their types. Even before launching 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 variable ClientId differs from the type of the field Client, then the platform will notify about it (underscore the error with a red line).

Also available in the platform editor is a hint for syntax, field names, database functions, and auto-completion.

When refactoring a database, the platform automatically adjusts all queries. That is, when you divide 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 of a programming language.

AutoJoin technology

Nothing so briefly and concisely 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, who owns the account 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 

Is it easier? Request volume decreased 5 times. These 4 Join blocks fall on one field only. And if we have 20 fields? Well, in general, you understand.

Single, new SQL command

The Select statement returns a dataset (hereinafter referred to as 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 statement works in the same way as Select, but returns a single value (the first field in the first line).

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

Appeal to external requests

When using subqueries, you can refer to any external query by specifying several points before the variable name (the number 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 (after all, 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 to use all the capabilities of the DBMS 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 

Also, this tool allows you to empirically verify the identity of the results of the work queries and compare the execution time. Optimize the program without complicating it!

Typed datasets.

Any Select query returns a dataset (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 last example, Dataset [x, y] returns the data type of the Variant and type control at the stage of entering the program text was not performed. Typed datasets allow you to access certain fields of a specific data type - Dataset [x]. Field Name. That is, type control works during input, auto-completion, and so on.

 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 do without them? Everyone remembers the first example in any textbook with transferring money from one account to another? :)

 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 is completed. Conversely, you cannot see changes made by other clients. 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 query and automatically updates the interface. Moreover, the tables used in the view and calculated fields are taken into account. And only 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. (For example, 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; 

In this case, you can use any functions of the program in queries.

Stored procedures.

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

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 intuitive than stored procedure languages ​​(for example, MySQL).

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

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

Sample 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 programs. These factors, of course, are fundamental when choosing a development environment. Want to try tmaplatform - sign up for beta testing (throw your email to me ( smirnovss ) in PM)
For a full description of the platform with help, see here tmaplatform.ru