The 1C 8 query language is an indispensable tool for a 1C programmer; it allows you to write more concise, simple, understandable code, and use fewer system resources when working with data. This article opens a series of lessons dedicated to the 1C 8 query language. In the first lesson we will look at the structure of the main operator of this language - CHOOSE. Using this operator, you can create selections from database tables. Selected table data can be sorted, conditions placed on it, linked and combined with data from other tables, grouped by various fields, and much more.

Query language 1C enterprise 8 - Operator structure SELECT

Let's look at the structure of the SELECT operator (optional parts of the operator are indicated in square brackets). The 1C query language provides a wide range of tools for creating data samples.

SELECT [ALLOWED] [DIFFERENT] [FIRST A] [Field1] [AS Alias1], [Field2] [AS Alias2], ... [FieldM] [AS AliasB] [PUT TemporaryTableName] [FROM Table1 AS AliasTableTable1 [[INNER JOIN ][LEFT JOIN][FULL JOIN] Table2 AS Alias ​​Table2 [[INNER JOIN][LEFT JOIN][FULL JOIN] TableC AS Alias ​​TablesC BY Expression1 [And Expression2]...[And ExpressionD]] ... ... BY Expression1 [And Expression2]...[And ExpressionE]] ... [TableF AS TableF Alias] ... ] [GROUP BY GroupingField1[,] ... [GroupingFieldG]] [WHERE Expression1 [AND Expression2] ... [AND ExpressionH]] [UNITE ALL...] [; ...] [INDEX BY Alias1 ... AliasB] [TOTALS [AggregationFunction(Field1)][,] [AggregationFunction(Field2)][,] ... [AggregationFunction(FieldI)] BY [GENERAL][,] [ GroupingField1][,] ... [GroupingFieldj]]

Keywords and blocks for working with fields

• CHOOSE— a keyword indicating the beginning of the operator;
• ALLOWED — indicates that the selection should include table records that have read access for the given user;
  
• VARIOUS — indicates that the sample should include only different (across all fields) flows. In other words, duplicate rows will be excluded from the sample;
  
• FIRST A — if you specify this keyword, then only the first A of the rows selected by the query will be included in the selection, where A is a natural number;
  
• Field block— this block indicates the fields that need to be included in the selection. These fields will be selected columns. In the very simple case the field looks like this: Table Alias.TableFieldName AS Field Alias

  This way we indicate which table we are taking this field from. The 1C query language allows you to specify any aliases, but they should not be repeated in the same SELECT statement. A field can be more complex, consisting of various combinations of table fields, query language functions, and aggregate functions, but we won't cover those cases in this tutorial;

Keywords and blocks for working with tables

• PUT TemporaryTableName- keyword PLACE is intended to create a temporary table with a specific name, which will be stored in random access memory in this 1C 8 session until it ends or until the temporary table is destroyed. It should be noted that the names of temporary tables in one 1C 8 session should not be repeated;
• Block of tables and relationships— the block indicates all the tables used in this request, as well as connections between them. The block begins with a keyword FROM, followed by the name and alias of the first table. If this table is related to other tables, then the relationships are indicated. The 1C query language contains the following set of connection types:
  • INNER JOIN— a record from the left table will be included in the selection only if the connection condition is met, a record from the right table will be included in the selection only if the connection condition is met;
  • LEFT CONNECTION— a record from the left table will be included in the selection in any case, a record from the right table will be included in the selection only if the connection condition is met;
  • FULL CONNECTION— a record from the left table will be included in the selection first in any case, then only if the connection condition is met, a record from the right table will be included in the selection first in any case, then only if the connection condition is met. In this case, the resulting duplicate rows are excluded from the sample.

  After the connection type, the name and alias of the second table are indicated. Next comes the keyword BY, followed by communication conditions connected with each other by logical operators AND, OR. Each expression in the condition must return a Boolean value (True, False). If the first table is connected to some tables other than the second, then the connection type is again indicated, and so on. Each of the tables participating in the connection, in turn, can be connected to other tables, this is shown in the query structure diagram. If the table is not related to the first one, then it is indicated without a connection type, then its connections may follow, and so on;

Keywords and data conversion blocks

• Group block— this block is used to group table rows. Rows are combined into one if the values ​​of the fields specified after the keyword GROUP BY turn out to be the same. In this case, all other fields are summed, averaged, maximized, or minimized using aggregate functions. Aggregate functions are used in a field block. Example: Maximum(TableAlias.TableFieldName) AS FieldAlias
• Condition block- in this block after the keyword WHERE conditional expressions separated by logical operators are indicated AND, OR, in order for any of the selected rows to be included in the sample, it is necessary that all conditions in the aggregate have a value True.
• COMBINE EVERYTHING— this keyword is used to combine queries (operators CHOOSE). The 1C query language allows you to combine several queries into one. In order for queries to be merged, they must have the same set of fields;
• «;» - semicolons are used to separate statements that are independent of each other CHOOSE;
  
• INDEX BY— the keyword is used to index the fields specified after it;
  
• Summary block— used to build tree-like samples. For each of the grouping fields specified after the keyword BY, a separate row will be created in the selection. In this line, using aggregate functions, the total values ​​of the fields specified after the keyword will be calculated RESULTS.

Do you want to continue learning the 1C 8 query language? Then read the next article.

17 rules for drawing up an optimal QUERY for 1C database data

To generate and execute queries to database tables in the 1C platform, a special programming language object is used Request. This object is created by calling the construct New request. The query is convenient to use when you need to obtain a complex data sample, grouped and sorted as necessary. A classic example of using a query is to obtain a summary of the state of the accumulation register at a certain point in time. Also, the query mechanism makes it easy to obtain information in different time periods.

The request body is the instruction according to which the request must be executed. The request body describes:

• infobase tables used as query data sources;
• table fields that need to be processed in the query;
• grouping rules;
• sorting results;
• etc.

The instruction is compiled in a special language - the query language and consists of separate parts - sections, sentences, keywords, functions, arithmetic and logical operators, comments, constants and parameters.

The query language of the 1C platform is very similar to the syntax of other SQL languages, but there are differences. The main advantages of the built-in query language are: dereferencing fields, the presence of virtual tables, convenient work with results, untyped fields in queries.

Recommendations for writing database queries in the 1C platform query language:

1) The request body may contain predefined configuration data, such as:

• enum values;
• predefined data:
• reference books;
• plans for types of characteristics;
• charts of accounts;
• plans for types of calculations;
• empty links;
• business process route point values.

Also, the request text can contain the values ​​of system enumerations that can be assigned to fields in the database tables: Accumulation Movement Type, Account Type and Accounting Movement Type. In queries, predefined configuration data and system enumeration values ​​are accessed using a literal of the VALUE function type. This literal allows you to improve the readability of the query and reduce the number of query parameters.

Example of using a literal MEANING:

• WHERE City = VALUE(Directory.Cities.Moscow)
• WHERE City = VALUE(Directory.Cities.EmptyLink)
• WHEREProductType = VALUE(Enumeration.ProductTypes.Service)
• WHEREMovementType = VALUE(MovementTypeAccumulation.Incoming)
• WHERE RoutePoint = VALUE(BusinessProcess.BusinessProcess1.RoutePoint.Action1

2) Using the instructions AUTO ORDER The query can take a long time to complete, so if sorting is not required, then it is better not to use it at all. In most cases, it is best to use instruction sorting SORT BY.

Auto-ordering works according to the following principles:

• If the ORDER BY clause was specified in the request, then each link to the table found in this clause will be replaced by the fields by which the table is sorted by default (for reference books this is the code or name, for documents – the document date). If the field to be sorted refers to a hierarchical directory, then hierarchical sorting by this directory will be applied.
• If the query does not have an ORDER BY clause, but does have a TOTAL clause, then the query result will be ordered by the fields present in the TOTAL clause after the key words software, in the same sequence and, if the totals were calculated by reference fields, then by the default sorting fields of the tables to which there were links.
• If the query does not contain the ORDER BY and TOTAL clauses, but there is a GROUP BY clause, then the query result will be ordered by the fields present in the clause in the same sequence and, if the grouping was carried out by reference fields, then by default sorting fields tables that were referenced.
• If the query does not contain ORDER BY, TOTAL, or GROUP BY clauses, the result will be ordered by the default sort fields for the tables from which the data is selected, in the order they appear in the query.
• If the query contains a TOTAL clause, each total level is ordered separately.

3) To avoid repeating a query to the database when displaying the query result to the user (for example, building a query or displaying the query result using a spreadsheet document), it is useful to use the instruction INTRODUCTIONLINKS, which allows you to get a representation of a reference value. Example:

It is also possible to use instructions PERFORMANCE- designed to obtain a string representation of a value of an arbitrary type. The difference between these instructions is that in the first case, if the instructions pass a link, the result will be a string. In other cases, the result will be the value of the passed parameter. In the second case, the result of the instruction will always be a string!

4) If the request contains a field with a composite type, then for such fields it becomes necessary to convert the field values ​​to a specific type using an instruction EXPRESS, which will allow you to remove unnecessary tables from the left join with a field of a complex data type and speed up query execution. Example:

There is a register for the accumulation of Remaining Goods, in which the Registrar field has a composite type. In the request, the Date and Document Number of Receipt of Goods are selected, while when accessing the document details through the Registrar field, many left connections of the accumulation register table with the tables of registrar documents do not occur.

Code 1C v 8.x SELECT
EXPRESS(Remaining Goods.Registrar AS Document.Receipt of Goods).Number AS Receipt Number,
EXPRESS(Remaining Goods.Registrar AS Document.Receipt of Goods).Date AS Receipt Date
FROM
Register of Accumulations. Remaining Goods AS Remaining Goods

If a type cast is considered not feasible, then the result of the type cast will be the value NULL.

5) Don’t forget about the instructions ALLOWED, which means the query will only select records that the current user has rights to. If this word is not specified, then if the request selects records for which the user does not have rights, the request will fail.

6) If the query uses a join, and some parts of the join contain nested tables (a document with a tabular part), and some do not, it becomes necessary to supplement the selection list with fields - empty nested tables. This is done using a keyword EMPTYTABLE, after which the aliases of the fields that will make up the nested table are indicated in parentheses. Example:

Code 1C v 8.x // Select fields Number and Composition
// from the virtual table Document.Expenditure
SELECT Link.Number, EMPTY TABLE.(No., Item, Quantity) AS Composition
FROM Document.Expense Invoice
COMBINE EVERYTHING
SELECT Link.Number, Contents.(LineNumber, Product, Quantity)
FROM Document.Invoice Document.Invoice.Composition.*

7) To prevent duplicate lines from appearing in the query result, you should use the instruction VARIOUS, because it’s more visual and understandable, and the instructions GROUP BY used for grouping using aggregate functions. Ksati, when using aggregate functions, a suggestion GROUP BY may not be specified at all, but all query results will be grouped into one single line. Example:

Code 1C v 8.x // It is necessary to find out which counterparties
// goods were shipped for the period.
Select Various
Document.Invoice.Counterparty

8) Instructions GROUP BY allows you to access top-level fields, without grouping the results by these fields, if aggregate functions are applied to the fields of a nested table. Although the 1C help says that when grouping query results, aggregate functions must be specified in the list of selection fields, and in addition to aggregate functions in the list of selection fields, it is allowed to indicate only the fields by which grouping is carried out. Example:

Code 1C v 8.x SELECT
Receipt of Goods and Services. Goods. (SUM (Quantity), Nomenclature),
Receipt of Goods and Services. Link,
Receipt of Goods and Services. Counterparty
FROM
Document. Receipt of Goods and Services HOW Receipt of Goods and Services
GROUP BY
Receipt of Goods and Services. Goods. (Nomenclature)

9) Instructions ISNULL is intended to replace the value NULL to another value, but do not forget that the second parameter will be converted to the type of the first if the type of the first parameter is a string or a number.

10) When contacting main table You can access the data in the subordinate table. This feature is called dereferencing fields of a subordinate table.

Example (search for documents containing a specific product in the tabular section):

The advantage of this query over a query on the subordinate table Receipt.Goods is that if there are duplicates in documents, the query result will return only unique documents without using the DIFFERENT keyword.

11) An interesting variant of operator B is checking whether an ordered set is included in the set of such sets (Field1, Field2, ..., FieldN) B (Field1, Field2, ..., FieldN).

Code 1C v 8.x SELECT
Counterparties.Link
WHERE
(Counterparties.Link, Products.Link) B
(SELECT Sales.Customer, Sales.Product
FROM RegisterAccumulation.Sales AS Sales)
FROM
Directory. Counterparties,
Directory.Products

12) Use virtual query tables whenever possible. When creating a query, the system provides a number of virtual tables as data sources - these are tables that are also the result of a query that the system generates at the time the corresponding section of code is executed.

The developer can independently obtain the same data that the system provides to him as virtual tables, but the algorithm for obtaining this data will not be optimized because:

All virtual tables are parameterized, i.e. the developer is given the opportunity to set some parameters that the system will use when generating a request to create a virtual table. Depending on what parameters of the virtual table are specified by the developer, the system can generate VARIOUS queries to obtain the same virtual table, and they will be optimized in terms of the passed parameters.

It is not always possible for a developer to gain access to the data that the system has access to.

13) In the client-server mode of operation, the function SUBSTRING() is implemented using the SUBSTRING() function of the corresponding SQL statement passed to the SQL Server database server, which calculates the type of the result of the SUBSTRING() function using complex rules depending on the type and values ​​of its parameters, as well as depending on the context in which it is used . In most cases, these rules have no effect on query execution, but there are times when the maximum result row length calculated by SQL Server is important to query execution. It is important to keep in mind that in some contexts when using the SUBSTRING() function, the maximum length of its result may be equal to the maximum length of a limited-length string, which in SQL Server is 4000 characters. This may cause the query to crash unexpectedly:

Microsoft OLE DB Provider for SQL Server: Warning: The query processor could not produce a query plan from the optimizer because the total length of all the columns in the GROUP BY or ORDER BY clause exceeds 8000 bytes.

HRESULT=80040E14, SQLSTATE=42000, native=8618

14) Use with caution OR in design WHERE, since using an OR condition can make the query significantly heavier. The problem can be solved by design COMBINE EVERYTHING. Example:

Code 1C v 8.x SELECT

FROM

WHERE
_Demo Contractors.Link =Link1
COMBINE EVERYTHING
CHOOSE
_Demo Contractors.NameFull
FROM
Directory._Demo Counterparties HOW TO _Demo Counterparties
WHERE
_Demo Contractors.Link = Link2

15) Condition NOT IN in design WHERE increases the query execution time, since this is a kind of NOT (OR1 OR2 ... ORn), so for large tables try to use LEFT JOIN with condition IS NULL. Example:

Code 1C v 8.x SELECT
_Demo Contractors.Link
FROM
Directory._Demo Counterparties HOW TO _Demo Counterparties
LEFT CONNECTION Document._Buyer's Demo Order HOW TO _Buyer's Demo Order
Software _Demo Counterparties. Link = _Demo Order of the Buyer. Counterparty
WHERE
_Demo Order of the Buyer. Counterparty IS NULL

16) When using Temporary tables You need to index the condition and join fields in these tables, BUT, when using indexes, the query can be even slower. Therefore, it is necessary to analyze each query with and without an index, measure the speed of query execution and make a final decision.

If you place data in a temporary table that is initially indexed by some fields, then the temporary table will no longer have an index on these fields.

17) If you don't use Temporary Table Manager, then there is no need to explicitly delete the temporary table, it will be deleted after the batch query is completed, otherwise you should delete the temporary table using one of the following methods: command DESTROY in the request, call the method TemporaryTableManager.Close().

And in addition to the video from Evgeny Gilev: Typical errors when writing queries in 1C:

The query language is one of the fundamental mechanisms of 1C 8.3 for developers. Using queries, you can quickly retrieve any data stored in the database. Its syntax is very similar to SQL, but there are some differences.

The main advantages of the 1C 8.3 (8.2) query language over SQL:

• dereferencing reference fields (referring one or more points to object details);
• working with results is very convenient;
• the ability to create virtual tables;
• the request can be written in both English and Russian;
• ability to block data to avoid deadlocks.

Disadvantages of the query language in 1C:

• unlike SQL, in 1C queries do not allow changing data;
• lack of stored procedures;
• impossibility of converting a string to a number.

Let's take a look at our mini tutorial on the basic constructs of the 1C query language.

Due to the fact that queries in 1C only allow you to receive data, any query must begin with the word “SELECT”. After this command, the fields from which data must be obtained are indicated. If you specify “*”, all available fields will be selected. The place from which the data will be selected (documents, registers, directories, etc.) is indicated after the word “FROM”.

In the example discussed below, the names of the entire nomenclature are selected from the “Nomenclature” directory. After the word “HOW”, aliases (names) for tables and fields are indicated.

CHOOSE
Nomenclature. Name AS Name of Nomenclature
FROM
Directory.Nomenclature AS Nomenclature

Next to the “SELECT” command you can specify keywords:

• VARIOUS. The query will select only rows that differ in at least one field (without duplicates).
• FIRST n, Where n– the number of rows from the beginning of the result that need to be selected. Most often, this construction is used in conjunction with sorting (ORDER BY). For example, when you need to select a certain number of documents that are recent by date.
• ALLOWED. This design allows you to select from the database only those records that are available to the current user. Based on the use of this keyword, the user will receive an error message when attempting to query records that they do not have access to.

These keywords can be used together or separately.

FOR CHANGE

This proposal blocks data to prevent mutual conflicts. Locked data will not be read from another connection until the transaction ends. In this clause, you can specify specific tables that need to be locked. IN otherwise everyone will be blocked. The design is relevant only for the automatic locking mode.

Most often, the “FOR CHANGE” clause is used when receiving balances. After all, when several users work in the program simultaneously, while one receives balances, another can change them. In this case, the resulting remainder will no longer be correct. If you block the data with this proposal, then until the first employee receives the correct balance and performs all the necessary manipulations with it, the second employee will be forced to wait.

CHOOSE
Mutual settlements. Employee,
Mutual settlements. Amount of mutual settlements Balance
FROM
Register of Accumulations. Mutual settlements with employees. Balances AS Mutual settlements
FOR CHANGE

WHERE

The design is necessary to impose some kind of selection on the uploaded data. In some cases of obtaining data from registers, it is more reasonable to specify selection conditions in the parameters of virtual tables. When using "WHERE", all records are retrieved first, and only then selection is applied, which significantly slows down the query.

Below is an example of a request to obtain contact persons for a specific position. The selection parameter has the format: &ParameterName (the parameter name is arbitrary).

SELECTION (CASE)

The design allows you to specify conditions directly in the body of the request.

In the example below, the “AdditionalField” will contain text depending on whether the document is posted or not:

CHOOSE
AdmissionT&U.Link,
CHOICE
WHEN AdmissionT&U.Performed
THEN “The document has been passed!”
ELSE “The document was not posted...”
END AS AdditionalField
FROM
Document. Receipt of Goods and Services HOW Receipt T&C

JOIN

Joins link two tables based on a specific relationship condition.

LEFT/RIGHT CONNECTION

The essence of the LEFT join is that the first specified table is taken in its entirety and the second one is linked to it according to the connection condition. If there are no records corresponding to the first table in the second, then NULL is substituted as their values. Simply put, the main table is the first specified table and the data of the second table (if any) is already substituted for its data.

For example, it is necessary to obtain item items from the “Receipt of goods and services” documents and prices from the information register “Item prices”. In this case, if the price for any position is not found, substitute NULL instead. All items from the document will be selected regardless of whether they have a price or not.

CHOOSE
Receipt&U.Nomenclature,
Prices.Price
FROM
Document. Receipt of Goods and Services. Goods HOW Receipt T&C
INTERNAL JOIN RegisterInformation.PricesNomenclature.SliceLast AS Prices
Software Receipt&U.Nomenclature = Prices.Nomenclature

IN THE RIGHT everything is exactly the opposite.

FULL CONNECTION

This type of connection differs from the previous ones in that as a result all records of both the first table and the second will be returned. If no records are found in the first or second table based on the specified link condition, NULL will be returned instead.

When using a full connection in the previous example, all item items from the “Receipt of Goods and Services” document and all the latest prices from the “Item Prices” register will be selected. The values ​​of not found records in both the first and second tables will be equal to NULL.

INNER JOIN

The difference between an INNER JOIN and a FULL JOIN is that if a record is not found in at least one of the tables, the query will not display it at all. As a result, only those item items from the document “Receipt of goods and services” will be selected for which there are records in the information register “Item prices”, if in the previous example we replace “FULL” with “INTERNAL”.

GROUP BY

Grouping in 1C queries allows you to collapse table rows (grouping fields) according to a specific common feature(grouped fields). Grouping fields can only be displayed using aggregate functions.

The result of the following query will be a list of product types with maximum prices for them.

CHOOSE
,
MAX(Price.Price) AS Price
FROM

GROUP BY
Prices.Nomenclature.Type of Nomenclature

RESULTS

Unlike grouping, when using totals, all records are displayed and total rows are added to them. Grouping displays only generalized records.

Results can be summarized for the entire table (using the keyword “GENERAL”), for several fields, for fields with a hierarchical structure (keywords “HIERARCHY”, “ONLY HIERARCHY”). When summarizing results, it is not necessary to use aggregate functions.

Let's look at an example similar to the example above using grouping. In this case, the query result will return not only grouped fields, but also detailed records.

CHOOSE
Prices.Nomenclature.Type of Nomenclature AS Type of Nomenclature,
Prices.Price AS Price
FROM
Register of Information. Prices of Nomenclature. Snapshot of the Latest AS Prices
RESULTS
MAXIMUM(Price)
BY
TypeNomenclature

HAVING

This operator is similar to the WHERE operator, but is used only for aggregate functions. The remaining fields, except those used by this operator, must be grouped. The WHERE operator is not applicable to aggregate functions.

In the example below, the maximum prices of an item are selected if they exceed 1000, grouped by item type.

CHOOSE

MAX(Price.Price) AS Price
FROM
Register of Information. Prices of Nomenclature. Snapshot of the Latest AS Prices
GROUP BY
Prices.Nomenclature.Type of Nomenclature
HAVING
MAXIMUM(Prices.Price) > 1000

SORT BY

The ORDER BY operator sorts the result of a query. To ensure that records are displayed in a consistent order, AUTO ORDER is used. Primitive types are sorted according to the usual rules. Reference types are sorted by GUID.

An example of getting a list of employees sorted by name:

CHOOSE
Employees.Name AS Name
FROM
Directory.Employees HOW Employees
SORT BY
Name
AUTO ORDER

Other 1C query language constructs

• COMBINE– results of two queries into one.
• COMBINE EVERYTHING– similar to COMBINE, but without grouping identical rows.
• EMPTY TABLE– sometimes used when joining queries to specify an empty nested table.
• PLACE– creates a temporary table to optimize complex 1C queries. Such requests are called batch requests.

Query Language Features

• SUBSTRING truncates a string from a specified position to a specified number of characters.
• YEAR...SECOND allow you to get the selected value of a numeric type. The input parameter is the date.
• BEGINNING OF PERIOD and END OF PERIOD used when working with dates. The type of period (DAY, MONTH, YEAR, etc.) is indicated as an additional parameter.
• ADDKDATE allows you to add or subtract a specified time of a certain type from a date (SECOND, MINUTE, DAY, etc.).
• DIFFERENCEDATE determines the difference between two dates, indicating the type of output value (DAY, YEAR, MONTH, etc.).
• ISNULL replaces the missing value with the specified expression.
• REPRESENTATION and REPRESENTATIONLINKS get a string representation of the specified field. Apply to any values ​​and only reference values, respectively.
• TYPE, TYPE VALUES are used to determine the type of the input parameter.
• LINK is a logical comparison operator for the attribute value type.
• EXPRESS used to convert a value to the desired type.
• DATE TIME gets a value of type "Date" from numeric values ​​(Year, Month, Day, Hour, Minute, Second).
• MEANING in a 1C request it is used to indicate predefined values ​​- directories, enumerations, plans for types of characteristics. Usage example: " Where Legal Individual = Value(Enumeration. Legal Individual. Individual)«.

Query Builder

To create queries with 1C there is a very convenient built-in mechanism - the query designer. It contains the following main tabs:

• “Tables and Fields” - contains the fields that need to be selected and their sources.
• “Connections” - describes the conditions for the CONNECTION structure.
• “Grouping”—contains a description of grouping structures and summed fields based on them.
• “Conditions” - is responsible for selecting data in the request.
• "Additional" - Extra options request, such as keywords of the “SELECT” command, etc.
• “Joins/Aliases” - the possibilities of joining tables are indicated and aliases are specified (the “HOW” construct).
• “Order” is responsible for sorting the result of queries.
• “Totals” - similar to the “Grouping” tab, but used for the “TOTALS” construct.

The text of the request itself can be viewed by clicking on the “Request” button in the lower left corner. In this form, it can be corrected manually or copied.

Request Console

To quickly view the result of a query in Enterprise mode, or debug complex queries, use . It contains the text of the request, sets the parameters, and displays the result.

You can download the query console on the ITS disk, or via .

20.09.2014

There is a "Allowed" directive in the query language. It is designed to allow the platform to filter out records to which the user does not have rights when setting restrictions at the database record level.

It would seem that it is better to always use this directive in queries. I will argue that this is not so. I will also argue that, if possible, you should avoid using it, here’s why.

Let's say we are making a report on mutual settlements individuals. The user has rights to one organization, there is more than one organization in the database, and the database has record-level restrictions enabled. Also, in the database there is a register “Mutual settlements” with the dimensions “Organization” and “Individual”. If there is a request in the system

"Choose

Organization,

Individual

and it will be executed on behalf of a user with permission for one organization, then the request will not be executed if there are records of other organizations in this register. An error will occur, and the error description will be “The user does not have sufficient rights to complete the request!” and this is true, the platform does not cheat, since it does not have rights to the records of other organizations in this register.

What to do in this case, use the “Allowed” directive? In my opinion it's not worth it. You just need to set the selection by organization and the user will be able to generate a report. The query for a report with data composition will look like this

"Choose

Organization,

Individual

(Choose

Organization

Individual)

From the Accumulation Register. Mutual settlements

(Where

Organization

Individual)

If the user executes a query on the table without selection, the report will not be generated, and the user will not recognize the data for other organizations, but if he selects for his organization, it will be generated with the correct data.

You can ask again - “Why shouldn’t you use the Allowed directive?” this immediately imposes selection and will protect the user from unnecessary messages!

The answer to this question will be how in this case the user will know that all the necessary data is included in the report. Let’s say that earlier this user worked under full rights and made a mistake and selected an individual from another organization in the document. There may also be a situation where the data was downloaded - and a division of another organization was recorded in the organization’s documents (the ZUP also imposes restrictions on their owner). In this case, the “Allowed” directive will cut off prohibited data without any messages to the user, and he will not know that not everything that should be included in the report.

Therefore, you should not indiscriminately include this directive in requests for standard configurations, considering this to be an error. It is highly discouraged to do this in regulated reporting requests. You should also not do this in other reports and documents where accuracy of information is needed.

But how can you avoid the error of the program “crashing” if you do not have enough rights?

Yes, it’s very simple, you need to use the “Try” command, here is an example:

Attempt

Request.Run();

Exception

Report(ErrorDescription());

EndAttempt;

In reports from using ACS The program code for executing the report must be written manually, also through an attempt.

As a result, the user will not receive incorrect data and will receive a reasonable error message.

You can familiarize yourself with the nuances of setting up RLS in separate divisions in our article.

The “role” configuration object gives a set of rights to operations (actions) over configuration objects.

Role "Full Rights".

This is just a role (not predefined) in which all types of rights to all configuration objects are checked.

What distinguishes it from other roles is the presence of the “Administration” right.

If at least one user is created, the system begins to check for the presence of the “Administration” right - at least one user must have it.

Record-level access restrictions

Row Level Security (RLS) – record-level restriction.

The data access restrictions mechanism allows you to manage access rights not only at the level of metadata objects, but also at the level of database objects. The following objects can be used to restrict access to data:

• roles,
• session parameters,
• functional options,
• privileged shared modules,
• keyword ALLOWED in the query language.

The mechanism is designed to restrict access to metadata object table records based on arbitrary conditions imposed on the values ​​of the row fields of these tables. For example, to see records only for “your” counterparties, organizations, etc.

Technical implementation of access restrictions in 1C

1C generates a request to the DBMS. The server cluster adds a section WHERE to the request, which contains the text of the condition for restricting access via RLS, then this request is sent to the DBMS, the extracted data is returned to the 1C client.

This mechanism will work for any request from the client:

• in reports,
• in dynamic lists and in regular list forms
• in custom queries.

Such an implementation of the mechanism greatly affects performance.

Ways to bypass access restrictions.

In large resource-intensive operations (processing document reposting, for example), part of the code can be moved to privileged modules.

A) Privileged module is a common module with the “Privileged” flag in the properties.

Its peculiarity is that the code in it is executed without any access rights control, including RLS.

B) Also privileged the mode can be turned on for document object modules. This is done in the document properties, flag

• Privileged treatment when conducting
• Privileged mode when canceling a transaction

B) Method SetPrivilegedMode()

System command allows you to make part of the code of any module privileged.

From the next line of code, the privileged execution mode will operate.

It will operate until the line to disable this mode or until the end of the procedure / function

(True);

// any code here will be executed without rights control and RLS

SetPrivilegedMode(Lie ); // or end of procedure / function

The number of times privileged mode is enabled must match the number of times it is disabled. However, if within a procedure or function the privileged mode was turned on (once or more), but was not turned off, then the system will automatically shutdown as many times as there were incomplete turns on in the procedure or function being left

If in a procedure or function calls a method SetPrivilegedMode(False) made more than method calls SetPrivilegedMode(True ), then an exception will be thrown

Function PrivilegedMode() returns True if privileged mode is still enabled, and False if it is completely disabled. This does not analyze the number of privileged mode settings in a particular function.

All called procedures and functions will also be executed in privileged mode.

It is also possible to start a privileged session. This is a session in which privileged mode is established from the very beginning of the system. Moreover, during operation the method PrivilegedMode() will always return True, and the ability to disable privileged mode is not supported. Only a user who has administrative rights (Administration right) can start a privileged session. You can start a session using the key command line launching the client application UsePrivilegedMode or connection string parameter with information base prmod.

The question naturally arises: Why then set up access restrictions at all if it can be so easily bypassed?

Safe mode.

Yes, you can write external processing with a privileged execution mode and unload/corrupt data. To prevent this, the system has a global context method

SetSafeMode().

Safe mode, among other things, ignores privileged mode.

It must be installed before programmatically calling external processors or export procedures and functions from their modules.

When performing prohibited operations, an exception is thrown at runtime.

In addition, at the role settings level, you can disable the ability for users to interactively launch external reports and processing.

Setting up access restrictions

RLS can only be configured for rights:

• read (select)
• adding (insert)
• change (update)
• delete

For read operations and deletion, an object residing in the database must comply with data access restrictions.

For the add operation The data access restriction must correspond to the object that is planned to be written to the database.

For change operation the data access restriction must comply with the object both before the change (so that the object is read) and after the change (so that the object is written).

For all other rights there is no such option.

Let's add a new restriction for the “read” right of the “Nomenclature” directory. A list of fields for which you can configure the added restriction will open.

This means that if you try to access checked fields, the restriction will be triggered, but if you try to access unchecked fields, the restriction will not work.

If you select the flag " Other fields", the restriction will be configured for all table fields, except for fields for which restrictions are explicitly set.

*Feature: for rights to add, change, delete:

• The restriction can only be configured for all fields.
• There can be only one restriction.

For the “Read” right, you can configure several conditions; they will be combined with the logical operator “AND”.

Not all fields of the main constraint data object may be used in restrictions on the following types of database objects:

• in accumulation registers, access restrictions can only contain measurements of the main object of the restriction;
• in accounting registers, restrictions can only use balance sheet measurements of the main object of the restriction

If, under conditions of limited access to data negotiable register accumulation, measurements that are not included in the totals are used, then when accessing the virtual table of revolutions, the stored totals are not used and the query is executed entirely according to the movement table.

Mechanism for imposing access restrictions.

Any operation on data stored in a database in 1C:Enterprise ultimately leads to a call to the database with some request to read or change the data. In the process of executing queries to the database, the internal mechanisms of 1C:Enterprise impose access restrictions. Wherein:

• A list of rights is generated(read, add, modify, delete), a list of database tables, and a list of fields used by this query.
• From all roles of the current user access restrictions are selected to data for all rights, tables and fields involved in the request. Moreover, if a role does not contain restrictions on access to the data of a table or field, this means that the values ​​of the required fields from any record are available in this table. In other words, the absence of a restriction on access to data means the presence of a restriction WHERE IS THE TRUE.
• Retrieves the current values ​​of all session parameters and functional options participating in the selected restrictions.

To get the value of a session parameter or functional option The current user is not required to have permission to retrieve this value. However, if the value of some session parameter has not been set, an error will occur and the database query will not be executed.

Constraints derived from one role are combined using the AND operation.

Constraints derived from different roles are combined using the OR operation.

The constructed conditions are added to the SQL queries with which 1C: Enterprise accesses the DBMS. When accessing data from access restriction conditions, rights check is not performed (neither for metadata objects nor for database objects). Moreover, the mechanism for adding conditions depends on the selected method of operation of the restrictions “all” or “allowed”.

*Feature: If a user has access to several roles with configured restrictions at the record level for one object, then in this case the conditions of the restrictions are added using the logical operation “OR”. In other words, the user's powers are cumulative.

This leads to the following conclusion: do not allow the conditions for restricting access to one object in different roles to intersect, because in this case the text of the request will be greatly complicated and this will affect performance.

"Everything" method.

When imposing restrictions using the “all” method, conditions and fields are added to SQL queries so that 1C:Enterprise can obtain information about whether, during the execution of a database query, data that was prohibited for a given user was used or not. If prohibited data was used, the request will crash due to an access violation.

Imposing access restrictions using the “all” method is schematically presented in the figure:

“Allowed” method.

When applying restrictions using the “allowed” method, conditions are added to SQL queries so that records that are prohibited for the current user do not affect the result of the query. In other words, when restrictions are imposed in the “allowed” mode, records prohibited for a given user are considered missing and do not affect the result of the operation, which is schematically presented in the figure:

Data access restrictions are imposed on database objects at the time 1C:Enterprise accesses the database.

In the client-server version of 1C:Enterprise, restrictions are applied on the 1C:Enterprise server.

However, this option (ALLOWED) will not work if in a query we refer to a table for which access restrictions are not configured, but which contains references to table rows with configured restrictions. In this case, the query result will display “<Объект не найден>......" instead of the value of the reference field.

If you are developing a report or processing using standard or custom configuration queries, always check the "Allowed" flag for the report to work under any user with any set of rights.

In the case of object reading of data from the database, it is not possible to set the “Allowed” flag. Therefore it is necessary configure selections for object reading, taking into account possible access rights restrictions for the user. There are no means of obtaining only permitted data in object technology.

It is important that if a query does not specify the ALLOWED keyword, then all selections specified in that query must not conflict with any of the read restrictions on the database objects used in the query. Moreover, if the query uses virtual tables, then the corresponding selections must be applied to the virtual tables themselves.

Practice 1. Query builder in RLS settings.

Let’s compose the text of the “WHERE” section in the query to the directory. You can use the query builder.
The designer has a stripped down appearance.

“Tables” tab

The main table will be the table of the object for which the constraint is being configured.

You can also select other tables and set up various connections between them on the “Relations” tab.

Tab "Conditions"

Here you can configure the actual access restriction conditions

Let’s add conditions to the “Price” attribute of the nomenclature directory for the right to “read” to all fields of the table.

“Nomenclature WHERE Nomenclature.Price > 500”

Let's see how this simple rule works. The directory table contains the following elements:

After setting up an access restriction, the table will show only elements that satisfy the condition:

Groups also disappeared. Let's change the text of the restriction

“Nomenclature WHERE Nomenclature.Price > 500

OR Nomenclature.This is a Group"

Well, now that's what you need.

If you remove the display of the “code” field in the list settings, all elements of the directory will be displayed, i.e. the restriction didn't work. If you set the “Code” field to display, the restriction will work.

In this case, despite the fact that the directory element is visible in the list field, its form cannot be opened because a restriction on the attribute is configured. The same thing happens in an arbitrary request: when you try to get a “restricted” property, there will be an access error.

If you try to get the “restricted” credentials programmatically, an access error will also be thrown.

Moreover, it will not be possible to access any fields of an object through a link, because when receiving a link, the system reads the entire object, and if it contains “restricted” details, the object will not be read.

Therefore, when working programmatically with database objects, you need to keep in mind possible restrictions at the record level and obtain all the necessary object data by request and then place them in a structure or execute part of the code in a privileged module.

After setting up the access restriction, the display of the line in the list of rights changed - it became gray and an icon appeared.

Restrictions when setting up access (RLS).

• There is no Summary section;
• Virtual register tables cannot be accessed;
• You cannot use parameters explicitly;
• Can be used in nested queries any>/span> query language tools except:
  • operator IN HIERARCHY;
  • RESULTS proposals;
  • nested query results must not contain table parts>/span>;
  • virtual tables, in particular Balances and Turnovers

Practice 2. Nomenclature with current price.

Make an access restriction if you need to display items with a current price greater than a certain value, for example, 100.

Solution:

We are adding a new access restriction rule for the “Nomenclature” directory with the “read” right.
Select “other fields”.
In the constructor we add a nested query. In it, select the information register table “Item prices”.
There is no “order” tab - this is a feature of the query designer for building an access restriction request.
On the “Advanced” tab, set “first 999999999”, the “order” tab appears.
We set up ordering by the “Period” field in descending order.
Then we set up a connection between the main table and the subquery by reference.

Access Restriction Templates.

Practice 3. Restriction on “counterparties” by value in a constant.

Let's set up an access restriction for the Counterparties directory based on the value stored in the Constant.

In addition, you need to set up a restriction for all objects that use the “Counterparties” directory in the details.

Solution

For the “Counterparties” directory, we will set up a restriction for the “read” right by adding a nested query to the constant in the “Conditions” section. Don't forget This is a Group.

We see a problem, the Counterparties directory is filtered correctly, and all documents with the “Counterparty” attribute are displayed, some with “broken” links in the “Counterparty” attribute.

Now you need to configure access restrictions for all objects that use the link to “Accounts”. Let's find them using the “search for links to an object” service.

Let’s copy and slightly modify the text of the RLS condition from the “Counterparties” directory. This must be done as many times as there are objects found.

Or use an access restrictions pattern to avoid code duplication issues.

Access restriction templates are configured at the role level and can be used for any object within the edited role.

You can add any piece of access restriction text to the template. The template is called using the “#” symbol. For example, #TemplateCounterparty.

Through # in 1C instructions are written to the preprocessor. In the context of executing access restriction settings, the platform replaces the template call text with the template text.

Let’s add the text after the word WHERE to the “Contractor Template” template, except for the text about EtoGroup.

Parameters in access restriction templates.

Let's continue solving problem 2.

The problem now is that the main table in the directory is called “counterparty”, in the document “Receipt invoice”. The field being checked in the directory is called “link”, in the document it is called “Counterparty”.

Let’s change the name of the main table in the template text to “#CurrentTable”

"#CurrentTable" is a predefined parameter.

And through a dot we indicate the number of the input parameter - “.#Parameter(1)

“#Parameter” is also a predefined value. Can contain an arbitrary number of input parameters. They are addressed by serial number.

In the text of the access restrictions for the directory, we indicate the following:

For the document the following:

“Sales of Goods WHERE #TemplateCounterparty (“Counterparty”)”

When calling an access restriction template, parameters must be passed to it only as a String, i.e. in quotes.

Main table - Nomenclature

The template text is:

#CurrentTable WHERE #CurrentTable.#Parameter(1) = #Parameter(2)

The template text contains part of the text in the data access restriction language and may contain parameters that are highlighted using the “#” symbol.

The "#" symbol may be followed by:

• One of the keywords:
  • A parameter followed by the number of the parameter in the template in parentheses;
  • CurrentTable – indicates insertion into the text of the full name of the table for which the constraint is being built;
  • CurrentTableName– denotes insertion into the text of the full name of the table (as a string value, in quotes) to which the instruction is applied, in the current version of the built-in language;
  • NameCurrentAccessRight– contains the name of the right for which the current restriction is executed: READ, ADD, INSERT, CHANGE, UPDATE, DELETE;
• template parameter name – means insertion of the corresponding template parameter constraint into the text;
• symbol “#” – indicates the insertion of one character “#” into the text.

An access restriction expression may contain:

• Access restriction template, which is specified in the format #TemplateName("Template parameter value 1", "Template parameter value 2",...). Each template parameter is enclosed in double quotes. If you need to specify a double quote character in the parameter text, you must use two double quotes.
• Function StrContains(WhereWeLook, WhatWeLook). The function is designed to search for an occurrence of the WhatWeLook string in the WhereWeLook string. Returns True if the occurrence is found and False otherwise.
• The + operator is for string concatenation.

To make it easier to edit the template text, on the Restriction templates tab in the role form, click the Set template text button. In the dialog that opens, enter the template text and click OK.

They cannot be installed using SetParameter() or something similar.

The parameters in this case are:

• Session Options
• Functional options

Reading of session parameters in an access restriction request occurs in privileged mode, that is, without controlling the rights to operate with them.

Practice 4. Access to “your” counterparties

It is necessary to configure restriction of the current user’s access to “their” counterparties.

There is a directory “Users”, a directory “Counterparties”, documents with the details “Counterparty”.

The current user should see data only for those counterparties for whom a connection has been established with him.

Communication also needs to be configured.

Possible options:

Establishing connections between user and counterparty

• Details in the counterparties directory
• Register of information

Possible solutions to the problem:

• Storing a user in a constant is a bad option; the constant is available to all users.
• Storing a fixed array of the current user's counterparties in the session parameters is not very good a good option, there can be many counterparties
• Storing in the session parameters of the current user, then requesting a list of “his” counterparties is an acceptable option.
• Other options.

Solution.

Let's create a new session parameter "CurrentUser" and fill it out in the session module.

Let’s create a register of information “Compliance of managers and contractors”

Let's create a new role and in it a new access restriction for the document “Invoice”.

In the text of the request, we will connect the main table with the information register for Account = Account and Manager = &CurrentUser. Connection type Internal.

If possible, it is better to avoid nested queries in access restriction texts, since it will be executed every time data is read from this object from the database.

Checking - the restrictions work

*Feature: If you change the list of user counterparties in the register, access restrictions will take effect immediately without restarting the user session.

Practice 5. Date of prohibition of changes.

It is necessary to implement a restriction on editing data before the established date for prohibiting changes.
You need to limit it for users.

Let's create a register of information “Dates of Prohibition of Changes” with the dimension User, resource Date of Prohibition.

Let's build the logic of the solution this way:

• if a user is not specified, then the ban applies to all users
• if there is a restriction for all users and a restriction for a specific user, then the restriction applies for a specific user, and for others according to the general principle.

Obviously, such a constraint can be configured for database objects that have some position on the time axis. It can be

• Documentation
• Periodic information registers

Let's create a new role “Restrictions By Date of Prohibition of Changes”.

In it, for the document “Invoice” for the right “change” we will add a new access restriction.

We specify the setting for all fields.

The text of the restriction is:

ReceiptInvoice FROM Document.ReceiptInvoice AS ReceiptInvoice

Change Ban Dates. Ban Date AS Ban Date
FROM

INNER JOIN (SELECT
MAX(Change Prohibited Dates.User) AS User
FROM
Register of Information. Dates of Prohibition of Changes AS Dates of Prohibition of Changes
WHERE
(Change Prohibited Dates.User = &CurrentUser
OR Dates Prohibited Changes.User = VALUE(Directory.users.EmptyLink))) AS VZ_User
BY Date of Prohibition of Changes.User = VZ_User.User) AS NestedQuery
Software Receipt Invoice.Date > Nested Query.Ban Date

Let's check - the restriction works.

Using Preprocessor Instructions

#If Condition1 #Then

Request fragment 1

#ElseIf Condition2 #Then

Request fragment 2

#Otherwise

Request fragment 3

#EndIf

In conditions, you can use logical operations (and, or, not, etc.) and access to session parameters.

This approach in the context of constructing access restrictions is convenient in that, depending on the conditions, a shorter request text will be compiled. A simpler query loads the system less.

The downside is that the query constructor will not work with such text.

*Peculiarity :

In contrast to the instructions to the preprocessor of the built-in language in the access restriction texts, before the operator Then you need to put a hash - #Then

Practice 6. Switch “Use RLS”

Let's supplement our system of restrictions with a switch that turns on/off the use of restrictions at the record level.

To do this, we will add a Constant and a session parameter named “UseRLS”.

Let's write in the Session Module to set the value of the session parameter from the value of the constant.

Let's add the following code to all access restriction texts:

“#If &UseRLS #Then….. #EndIf”

We check - everything works.

However, now after turning on the “use radar” flag, the changes will not immediately take effect. Why?

Because the session parameter is set when the session is started.

It is possible to set the value of the session parameter to be reset when a new constant value is written, but this will only work for the current user session. Other users should be prompted to restart the system.

End of the first part.