Why not 1C?

Most recently, we published an article describing the problems of one of the most popular technologies used in IT, and to our surprise, it aroused quite a lively interest (at least for a technical article). Therefore, we decided not to stop there, and today we are "going to visit" one of the most popular products on the Russian market for developing business applications - the 1C platform.



It so happened that many 1Cs do not like on the hub, but sometimes it seems that few of these people understand well, for which they do not like him. With this article, we fill this gap and kill two birds with one stone: on the one hand, we’ll tell you how everything works in 1C from the inside, and on the other hand, why it doesn’t work the way you want / would like to. I must say that 1C with many of its decisions could really surprise us, however, we will not get ahead of ourselves.



There are enough articles criticizing 1C on Habré (for example, one , two , three ), but, in my opinion, they either pay too much attention to all the little things, like the menu is incorrectly organized, or they talk about too abstract things in which 1C is possibly and not to blame. In the same article, as well as in the article about SQL, we will focus exclusively on fundamental (and quite tangible) problems that concern everyone and everyone who develops / refines solutions in 1C, and lead either to a significant increase in the entry threshold, or to a serious a drop in productivity, or significant labor costs on the part of the developer.



So let's go. There are a lot of problems in 1C, therefore, in order to make it more convenient to navigate, we start with a table of contents (with a list of all these problems):

• Objects: Directories, Documents, etc.
  
  
  • Inefficient retrieval of object data
    
    
• Tables / Views: Registers
  
  
  • Registers are supported in very special cases.
    
    
  • No restrictions and events for register values
    
    
  • You can use only constants in the parameters of virtual tables
    
    
• Inquiries
  
  
  • Queries in Rows
    
    
  • Lack of query optimizer
    
    
  • Lack of advanced SQL features
    
    
  • No change requests
    
    
  • Refusal of automatic locks
    
    
• Forms
  
  
  • Abandoning a single thread of execution: separation of logic between server and client
    
    
  • Failure to synchronize
    
    
  • WYSIWYG abandonment: dividing the interface into write and read
    
    
  • Inability to access form details / current values ​​of other lists in lists
    
    
• Redundant Abstraction Levels
  
  
• Closed Physical Model
  
  
• Lack of inheritance and polymorphism
  
  
• Lack of explicit typing in code
  
  
• Lack of modularity
  
  
• Visual Programming Bet
  
  
• Fatal flaw
  
  
• Licensing and branding disrespectful to developers
  
  

I tried to build the sections in order from basic concepts / problems to more complex ones, although in fact for the most part they are in no way connected with each other. Therefore, if someone is too lazy to read the whole article, you can simply read the individual sections of his interest from the table of contents, there is practically no cross-cutting plot.

Objects: Directories, Documents, etc.

How are ORM frameworks / platforms usually arranged? In the development language of the ORM framework, object classes are supported in one form or another. For each of these classes, the developer can specify its mapping to some table. As a rule, one class corresponds to one table, the only key of which, in turn, corresponds to the identifier of an object of this class. Here, of course, the question arises of what to do with tables that have several keys. Corresponding classes are also created for them, since most ORMs support several fields of the same class as identifiers. Sometimes, of course, with such a mapping, we get rather full-blown abstractions like TovarNaSklad, but in most cases data is handled in the same paradigm (OOP).



In 1C, they decided to go the other way and support both paradigms at once, they simultaneously have objects and records. The logic of records is used by registers and queries (about them in the following sections), the logic of objects resembles the usual ORM, however, with its own characteristics:

• The developer does not control the display on tables, and in general it is hidden from it (although there is nothing special about it)
  
  
• There are no one-to-many, many-to-many mappings; their function is performed by the so-called tabular parts - collections of internal objects that are actually aggregated into the main object.
  
  

Ineffectively obtaining object data

Since untimely or excessive reading of data from the database server and transferring it to the application server can lead to a significant drop in performance, usually ORM frameworks provide the developer with a whole set of tools for managing the data they receive. But not 1C. In 1C, the object is always read in its entirety, including with tabular parts, but no more (without any associated data). As a result, the data is read:

• or too much - if you need to get only one field (props)
  
  
• or too little - if in the loop you need to access other objects by reference, we get the classic N + 1 problem (one request to get N objects and one request for each link).
  
  

Such wretchedness of the ORM mechanism in 1C is actually due to the fact that in 1C at some point they simply decided to abandon ORM and rely on bare SQL (that is, registers and queries). True, looking a little ahead, given the lack of advanced SQL and DML features in 1C, they periodically return to ORM, but this is rather a necessary necessity. But in general, a typical code of typical solutions in 1C looks something like this:

/ :

1 , :

• ( )
  
  
• ( )
  
  
• / ( , )
  
  

- . — , — , () ( )



— ERP- 1 . , SQL ( ), , — , . 1 , , , . — . ( 1 ).



1 :

• ( ) — ( )
  
  
• , , ( ) — ( )
  
  

SQL, , 1 ( ) SQL.

, 1 : , . , MS SQL, Oracle. , , — ( / ) () .



( ), SQL : 1 , , / , / , ( ).

1 , , . , , 0, - . SQL , 1 . ? , , :



( SQL per-statement trigger on before) :





(on after) + :





( ) , 0.





PS: , , .



( ), 1 ( ). 1 , .

SQL . , :

     =  ;
    . = 
        "
		|	.  ,
		|	.  ,
		|	.  
		|
		|	.  
		|		  ..(.,  = .)  
		|		 . = .
		|		 . = & "
		;
     = .();

      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

2 : “ ” “ ”. - IN () ( 1, ), , , , , , — . SQL JOIN — LATERAL JOIN APPLY, 1 . , , , , . / . , .



, , , 1 , . 1 , , , . , (OLTP) , ( ), (, , , ).

1 SQL, SQL. , , SQL, 1 , .

( ). :

• IDE , , , , ..
  
  
• , , .
  
  

, , 1 (C, ), ( ). , 1 . - , , .

1 — SQL- 1 , . . , (MS SQL, Oracle), 1 PostgreSQL, « , ». , , PostgreSQL Join Predicate Push Down ( ). 1 , .



1 — ( , ). . , :

, - , ( , ). , .

1 « », . « » . . - , . (). :

    .,
    .

    ..  
          ..(,
                               (
                                    
                                    ..
                                     = &))  
         . = .

    . = & 
    (. < . 
        .  NULL)

      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

, , 1 , « » , , PostgreSQL, ( IN / EXISTS PostgreSQL ). , , , ( ).

SQL

, SQL 1 , , , . , - , CTE, 1 , . , , , , 1 , SQL- 1 SQL 92. , , ( ), ORM, , , 1 .

, . , SQL, 1 ( DML), . , , ORM ( ) .

SQL ( CI ACID). ( ). , , , - , . , , , .



. , , . , , «» . (MVCC) Oracle PostgreSQL -, , .



, . , , , , ( , ), , . ? , ( ). , «» , :

• , . , , , , , SQL ( ), 1, .
  
  
• , , «» , FOR UPDATE ( ).
  
  

, , , ( ), , ( ).



1. ? . . ( ). . , . , - . — .



, «» 1 . , , / , , , .

ERP , , , . 1 . 1 Access, .

:

. - , , ( , ), . — ( -, ) , . ( ), — .



1? , , — . , , , — , , .. ( , ). :



. myForm otherForm, ( , ) .



, , 1 , , . / . :

• ( ). & 1 . , ( ), .
  
  
• «» ( Java EE) . , .
  
  
• ( , , windows ) , . ( )
  
  

, , . , 1 , . , (, ), ( ). - - , . . , , , , 1 () , , :

• ( )
  
  
• ,
  
  
• 
  
  

, , , 1 - .



? , , , ? , - , , . 1 - . :

, . , , .



, - .

1 . , , - , . - :

&
 ()
        =  ("", );
       ();


&
 (, ) 
       ();



&
 ()
        =  ("", );
        =  ("", , );
       (, " ?", .);



&
 (, ) 
        = ?( = ., 
             "  ...", 
             " !");
         <>   . <>  
             (., );
       ;


      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

WYSIWYG:

/ WYSIWYG. , , , 1 , WYSIWYG , 1 .



1 . :

• : (, ), ( ) ..
  
  
• : , .
  
  

, , , ( ). 1 — , . , , , :

• , / / . , ( 50), , , , 1C , ( ) — . , :
  
  

  
  			.  ,
  			.  ,
  			.  
  		
  			.  
  				  ..(&, )  
  				 . = ."
  
        
        
  
          
          
          
        
  
      
          
          
          
        
        
  
          
          
          
        
  
      
      

  
  
  ( ) .
  
  
• — . 1 , , - , . , , , , — . .
  
  
  
  1? . ( , ) . , , ( , , ). « ». , . , ( ), , , .
  
  
  
  , 1 , , , ( -WYSIWYG). , , . , , .
  
  

/

, , , . , / 1 , .



, , , :

• &
  
  
• :
  
  
  • 
    
    
  • 
    
    
  • (, )
    
    
  • ( , )
    
    

. « » , .



, ( ) — 1 (, , ).



, , 1 , . , , , , , , .

1, , 1 , - . , , 1 , . :







: ? , , , , , 1, , .

• /
  
  
  
  ORM. , ( ), , 1 , (), . , , 1 .
  
  
• /
  
  
  
  , . , / C#, C++. . , , — . — - ( ), ( ) . , ( ORM, ORM) .
  
  
• /
  
  
  
  ( ) , . 1 - , (, ) (, ) . 1 , . , . , , ( ). , ( ).
  
  
  
  , , , , . - .
  
  
  
  1 , . , ( , ).
  
  
• / / (BI)
  
  
  
  - : OLTP OLAP. - -: . OLAP — , «» , . OLTP , OLAP . , , OLTP - , , / , , -, ( ), -, - , . , , , ( OLAP), , .
  
  
  
  OLAP , SQL - , . .
  
  
  
  1 . () :
  
  
  
  
  • ( )
    
    
  • ( , ) BI
    
    
  • , , , ,
    
    
  • ,
    
    
  
  
  - , 1. (, ) . , , — .
  
  

1 - . , . , , , ( , , ), , ( ) , .



: OLAP , , ( , — _Fld16719 _Document5759).



1 , , 1.

, . 1, , 1 - SQL, SQL , 1 .



, , «, , if'» . , 1, , , :

• , ( , ORM, )
  
  
• :
  
  
  
  

   (, , ) 
  	
  	  =  
  		 = .();
  	;
  	
  	(                             										, );
  	( 										, , );
  	( 										, , );
  	( 										, , );
  	(     										, , );
  	(  										, , );
  	(												, , );
  	.(					, );
  	GTIN(                                                                           , );
  	.(				, , ); 
  	.(	, );
  	.(						, , );
  	.(				, , );
  	.(						, , );
  	.(				, , );
  	.(						    , , );
  	.(						, , );
  	.(						, , );
  	.(								, , );
  	.(							, , );
  	.(					, , );
  	(                              										, ); 
  	(                          										, , );
  	(            										, , );
  	(                          										, , );
  	(                        										, , );
  	(                       										, );
  	(                        										, );
  	(                            										, , );
  	(              										, );
  	(                                    										, );
  	.(					, , );
  	.(						, , );
  	.(
  		, , );
  	.(					, , );
  	.(								, , );
  	.(						, , );
  	.(				, , );
  	.(				, , );
  	.(							, , );
  	(                     										, );
  	.(				, );
  	.(		, , );
  	.(				, , );
  	.(		, , );
  	(  										, , );
  	(                           										, , );
  	.(								, , );
  	.(							, );
  	.(								, , );
  	.(								, , );
  	.(							, , );
  	.(						, , );
  	(              										, , );
  	.(                       	, , );
  	.(                   	, );
  	.(                             	, , );
  	(                       										, , );
  	(											, , );
  	(                 										, , );
  	(                   										, );
  	.(                 	, );
  	.(                   	, );
  	.(                  	, );
  	(                              										, );
  	(            										, );
  	.(                                      	, );
  	(                                     										, );
  	(                                      										, );
  	(                 										, );
  	(										, );
  	.(	                     	, );
  	.(              	, );
  	.(            	, );
  	.(                  	, );
  	(                         										, );
  	.(                   	, , );
  	(       										, );
  	.(       	, );
  	(              										, );
  	.(               	, , );
  	.(             	, , );
  	(           										, , );
  	(           										, , );
  	(                       										, , );
  	(            										, , );
  	( 										, , );
  	(       										, , );
  	(               																, );
  	(                     											, );
  	(															, );
  	.(                 	, , );
  	.(                          , );
  	.(                           , );
  	.(                                       , );
  	.(                  , );
  	.(                 , );
  	.(							, );
  	.(			, );
  	(, );	
  	.(, );
  	.(                  	, , );
  	.(                            , , );
  	(                                                                     , , );
  	.(                  , );
  	(                                                       , );
  	(                                                        , );
  	(                                                 , );
  	(                                               , , );
  	
  
  
        
        
  
          
          
          
        
  
      
          
          
          
        
        
  
          
          
          
        
  
      
      

  
  
  
• ( ):
  
  
  
  

  	 (, ) 
  
  	  .("") 
  		
  		;
  		
  	;
  	
  	. =  (.);
  	. =  (.);
  
  
  
        
        
  
          
          
          
        
  
      
          
          
          
        
        
  
          
          
          
        
  
      
      

  
  
  

20 Foxpro ( IF TYPE(«tableA.fldB») = «C») , . , 2019 .



P.S.: 1 , .

1 . — (). , , ( , , 1 ). 1 , , . , , :





, ( ). : « — ».



( , ..). . , , . IDE () - , , , , .



, , 1 . , -, , , , .

- . ( ) , , , , ( ), - , . .



1 . , -, , , . , ( ), 1 . , 1 :

, . -, , . -, , , , .

…

, .

-, — :

• , , , 1 ( -)
  
  
• , , N+1, , . 1 , (, ), , 1 .
  
  
• , , , 1
• , ,

, , , 1 . — . :

• , ( , )
  
  
• , - ( )
  
  

, 1 « SAP» — , : « , best practice, ». , , ( «--»). - . — . , , , :

• ( , / )
  
  
• ( : , , )
  
  
• . , ( , ), .

, low-code / no-code , . , / . — . / , . , / , , , . « , » , .



(--- vs ). , :

• — gitflow ( )
  
  
• / — / IDE
• — , , , « , » , ,
• — , , ..
• — IDE ( , , ..). .
• —
• — , / , , :
  
  
  
  
  
  
  
  
  
  

:

• . .
  
  
• . , , , .
  
  

. , . , «», «» . , , «» , «» , ? , , .



1. , 1 EDT, « ». language-based, 1 , , XML. . XML , :

• ( , ). ( ) 1336 .
  
  
• id, , , , .
  
  

/ — . ---, 1, . , , , .

, Microsoft 1, . , 1 , :

• IDE — IDEA, Eclipse, Microsoft Visual Studio / VSCode . IDEA , .
  
  
• — git , subversion mercurial . 1 git, , .
  
  
• — / , PostgreSQL, . , , 1 - SQL-92 , .
  
  
  
  PostgreSQL, ( , 1 ), . .
  
  
• — , , . 1 - ( ) BI. 1 , .
  
  
• — 1 . , , , , ( -). language-based (, , SAP SQL, ), . 1 , . : « , , ». 1 - . , , , / — , , , callback' — .
  
  
• / — Jenkins TeamCity, Java — Maven, Gradle, JavaScript — npm, Python — pip .. community, , , IDE . 1 -, , , , , , .
  
  
• UI . , , Linux, MacOS ( Java ), , « » .
  
  

, 1 , Eclipse. , , , ( ERP) . , , Eclipse stub , (chameleon ), language injection .., IDEA ( Eclipse , , EDT ).



, EDT 1 IDE. , « ». , , Eclipse IDEA, 1 IDE.

1 . : 1: 8. 22 600 . (!). ? , : 1: 8 . 100 — 360 000. 1: 8.3 . — 86 400. , , (!) . , , , . , 22 600 . .



1 , , — 1 , ( ) — . , IT, : « / 1». , , : , — 1. — . , , , ( ) . :

• 1 , , , , - , ( ). : « 20 1 , ?». - , , ( ).
  
  
• , / 1 ( , , ..). , 1, - , , «1 », 1.
  
  

, IT- . , , . 1 , 1 . , , , , .

, , , 1 :

• ORM
  
  
• SQL:
  
  
  • 
    
    
  • 
    
    
  • 
    
    
• control flow
  
  
• 
  
  
• 
  
  
• WYSIWYG
  
  

, goto. . , «» . , , . — . , , — .



, , — , . , , . , , lsFusion , , , . .



, 1 , , (, , ) . , . , . , : , . IT , ( ) Oracle, SAP Microsoft .



, 1 - . , . , — , , , « ». 1 , - ( «») — , , . lsFusion, , .Net+MSSQL Python+PostgreSQL . , , , . — ( , .Net) ? - : — , . IT. - ? , , , , , .



Axapta SAP. , ( 1 ), , , , , , . , , . , , , , , , , ( Axapta SAP , 1).