home > A computer > The column does not belong to the collection. Naming objects in Oracle

The column does not belong to the collection. Naming objects in Oracle

"Database object naming standards" and "coding rules" topics are not new. One way or another, all development teams come to the issue of developing or borrowing such standards and rules. If you wish, you can find articles and presentations on this topic on the net, as well as examples and templates for various agreements. Many of them are certainly useful, some are almost perfect, if not for one small caveat: they are written by developers and for developers.

Unfortunately, in my subjective reality, developers are just an abstraction. A kind of phantoms on the other side of the handset, from which I am separated by thousands of kilometers and 3 time zones. I don't have direct access to their collective brain. Only images generated by the given brain are available - in the form of objects of the operated system.

In principle, the wishes for the design and naming of the "applier" (application administrator / technologist) and the developer are 90 percent the same. But there are still some differences in the perception of the "reader" and "writer" that I would like to talk about.

The purpose of this article is to develop a set of rules for naming database objects (I like the term Naming conventions – NC, by analogy with Code Conventions) for application by the development team software products when designing information systems based on Oracle DBMS that meets the following requirements:

NC should be as complete as possible, i.e. contain naming rules for all types of database objects
NC should be as short as possible. Ideal option: 1 A4 sheet.

Why Oracle DBMS? She is nearer and dearer to me. And attempts to embrace the immensity with claims to universality are beyond my teeth and competencies. In this topic, I tried to summarize the materials of articles by Bill Coulam (Bill Coulam), Steven Feuerstein (Steven Feuerstein) and Tom Kyte (Tom Kyte) on this topic and my modest experience in designing, developing and operating various information systems.

Those who are too lazy to read about different approaches to naming and wade through the arguments in defense of one or another approach can simply scroll to the end of the article, where I provided a link to my own "Oracle NC" poster. There you can also find other useful links on the subject of the topic.

Are you here now? Were you able to restrain yourself and not start scrolling? Congratulations, you get a special bonus: you can download the "Oracle NC" poster right here, on the spot. The fact is that I have already read my article (yes, yes) and I warn you in advance: it is cumbersome and replete with templates that are obscure at first glance. It will be much easier to perceive it, having at hand all the rules and examples on one sheet.

Everyone knows that the main thing is in the tank, but not everyone can restrain himself...

It would seem that the idea of developing NC for an IT project lies on the surface. Compliance with NC requirements in design and development is also not a task, God knows. However, my experience of working with solutions from leading Russian developers in the market of billing systems for telecommunications companies suggests that the culture of object naming and design source code actually extremely low. In principle, all the solutions that I have studied on duty can be divided into four groups:

Complete absence of signs of NC (2 products out of 12). A nightmare. Operating such a system is like assembling a jigsaw puzzle face down. At the cost of incredible efforts, only separate threads of interconnections and logic can be pulled out of the tangle. And each time, with a new problem, the tangle has to be unraveled again.
Use of different NCs in one project (3 out of 12 products). It is immediately clear that the developers heard about NC, developed their own style and learned to follow it. The problem is that there were too many developers and styles for one project. The operation of such solutions is hampered by the fact that the experience of studying a single module is useless in another. Having known the part, one cannot get a reliable idea of the whole.
NC, which was clearly visible in earlier versions, is buried under a pile of crutches and washed out by the powerful pressure of patches (6 products out of 12). The most common option. The same tank in which they could not restrain themselves.
Clearly fixed NCs in compliance with all requirements and restrictions throughout life cycle systems. (1 product out of 12). Here it is the dream of an applier ...

I do not want to delve into the analysis of the causes of p.p. 1-3, I'm just stating a fact. All I want is to help take a step towards the “dream” of paragraph 4. So, let's start. Let's start with what to remember and what to follow when developing for the Oracle DBMS:

Remember that Oracle object names are limited to 30 characters in length. Exhaustive. From myself I can only add a wish. If you don't want people working with your system through applications that don't support "code hint" going crazy, be sensible - try to keep the names of your objects as short as possible.
Remember that object names cannot start with a number. No comment.
Use the same language when naming objects. Preferably in English. Avoid transliteration. Believe me, the table with the title ORDERS perceived better than a table with a title order. And further. Very often in commercial systems one has to deal with the transliteration of abbreviations. Avoid her too. USSR clearer than SSSR, a USA clearer than SSHA Something like this.
Yes, I almost forgot. Use only Latin in names! Of course, this is a recommendation for idiots, but I almost lost my mind once, trying to select from a table in SQLPLUS. And all because at the very end there was a symbol "e" in the Russian layout. In PL/SQL Developer, I didn't have to type the full name - the code hint worked. The funny thing is that the table lived in this form for more than a month and no one complained about it before me.
During the design of your system (immediately after the domain survey), rewrite all identified entities in separate file(table - glossary). Do not be too lazy to rummage through the Internet - for most of your entities, there have long been generally accepted and well-established English-language names. Record them in a glossary. For the rest of the entities, make a good translation. Do the same for the proposed abbreviations. Well, and then the most important thing: always use the same names for elements that are identical in meaning.
Avoid using Oracle reserved words as names (a list of all reserved words can be obtained by sampling from the system view V$RESERVED_WORDS). By the way, some words from this representation of Oracle will not be allowed to be used anyway. But there are also those that are not directly prohibited to use, but it is better, after all, not to do this.
Separate tokens in object names with underscores. Remember that Oracle is case insensitive, so your camels like MySuperPuperTableName become completely unreadable in the dictionary MYSUPERPUPERTABLENAME.
A small lyrical digression
To be honest, in Oracle you can set a case-sensitive name for an object. For example, like this:
Create table "MyTable" (a number);
In short, avoid such perversions.

Object Naming Rules

tables

On the issue of naming tables, I almost completely share the point of view of Bill Cowlam. The standard developed by him is exhaustive and almost ideal, both for the developer and for the “exploiter”. I will not give a full translation here, I will focus only on the fundamental points.

So, Coleham proposes the following universal form of table naming (curly brackets include mandatory components, and "straight" brackets are optional):
[Module_][Group_](Name)[_Role]
Under module(in Cowlam's terms - system group) refers to the name of a subsystem within our database. Usually a 2-4 character abbreviation is used. For example, the tables of the "Tarifier" module may have the prefix "TAR_", and the tables of the Payment module may have the prefix "PAY_". I’ll add on my own that if development is carried out in a “foreign” scheme, it is advisable to add an additional prefix to separate your own and other people's objects. I usually add the abbreviated name of my organization to the prefix. Of course, this lengthens the names of objects, but it allows you to clearly highlight "your" objects in the project tree. If you are confused by this approach, it is enough to add a single character before the module code (“local developers” usually prefer X or XX -legacy OEBS ?!).

Group is used for the same purposes: it allows you to group entities that are logically related to each other (usually up to 20 objects in a group). It is also an abbreviation of 2-4 characters. The use of system and logical groups allows not only grouping entities in the object tree, but also greatly simplifies the development and maintenance of the system as a whole. Indeed, the need to remember the names of specific objects disappears; it is enough to remember the abbreviations of the module and logical group, and then the code hint will help you easily find the object you need.

FROM Name all clear. This is the actual name of the entity. Bill Cowlam recommends using the singular, but I personally prefer the plural (Stephen Feurstein, hi!). Both Steven and Bill advise avoiding abbreviations in entity names. Exceptions are words longer than 8 characters.

It is not always possible to express the purpose of the table in one word. In this case, some domestic developers out of habit use the rule, which I silently call the “sales receipt rule”, when the word order goes from general to particular, from essence to properties. Those. “Toilet paper”, instead of “Toilet paper”, “Pickled cucumbers”, and “Tomato paste”. Unfortunately, in English-language names, this most often looks terrible. Compare YELLOW_SUBMARINE and SUBMARINE_YELLOW. In this case, I see no reason to rely on a single template, but I recommend using the order that is more appropriate in a particular context.

Role– essentially the purpose (assignment type) of the table in the system. Coleham identifies about two dozen roles, but for my taste some of them are redundant. Here are just the ones I use:

_AUD- Tables that store the history of changes in the rows of the base table (log tables). Met in different systems for of this type table suffixes _JN(magazine) and _HIST. I used to use the suffix _AUDIT, now _JN. BUT _AUD I'm more accustomed to seeing in the name of the trigger.
_LOG– application message fixation tables (log tables)
_HIST– Archive tables into which obsolete data is unloaded. For this type of table I use the suffix _ARCH
_TYPE– reference tables, the rows of which are a list of unique values. Domestic developers often use the suffix for lookup tables _REF. me too suffix _REF more to my liking because the word _TYPE much loved by developers and often part of a component Name.
_GT– Global temporary tables. Stephen Feurstein recommends using the suffix for them _TMP or _TEMP, but this, in my opinion, contradicts our mentality. A Russian programmer is used to using the lexeme _TEMP to mark any temporary "garbage", so _GT but only _GT.

In a separate class, Coleham allocates tables through which the "many-to-many" relationship is implemented. For such tables, he suggests the following naming pattern:
[Module_](Name/Code of table 1_Name/Code of table 2)
In most of the projects I've come across, developers used "meaningful" names for such association tables. I myself use the template:
[Module_][Group_](Table code 1_)(Table code 2)
Table code in this case, it is the abbreviated name of the table that participates in the link (2-4 characters). For example, a table storing student relationships ( STUDENTS), attending lectures of teachers ( TEACHERS) in this standard will be called STUD_TCHR. Yes, at first glance it looks repulsive, but over time you understand the convenience: at first glance you classify the table as a “bundle” (thanks to the use of codes / abbreviations instead of full names), you immediately see which entities are connected.

Columns (columns) of tables

Let's start with the restrictions on the total length of the name - try to keep within 15 characters (better - less). You will need the margin to the top constraint for subsequent naming of constraints, indexes, and foreign key columns.
In my projects I use the following pattern:
[Table code_](Column name)[_Role]
Table code represents the abbreviated name of the table to which the column belongs (2-4 characters). Although I made this prefix optional, I use it for almost all columns. The exception is "service" columns that store the value of some properties of an abstract record of any table, and not the properties of a specific object (for example, UPDATE_DATE, UPDATE_BY etc.).

Column name speaks for itself. Separately, I would like to say only about the rule for generating a name for a foreign key - it consists of the code of the child table plus the full name of the parent primary key.

Role is an optional suffix. Please note that this is the value type of the column, not the data type code of this column! Most often I use the following roles (value types):

_ID– object identifier based on a surrogate key
_NUM– a string field containing some number.
_CODE– entity string key (unique for lookup tables).
_DESC– Short description entities
_YN– a field of CHAR(1) type that takes the values Y(es) or N(o)

Many people consider the pattern (and, specifically, the prefix in the form of a table code) to be redundant. However, having the opportunity to compare different approaches, I chose it for myself. I'll give my reasons:

More readable queries. By the column with the prefix, you immediately understand what kind of table we are talking about. It's no secret that developers are often too lazy to qualify columns in detail, so naming a column with a prefix makes it easier to work with "foreign" queries.
The diagnostics of exceptions (errors) is facilitated. Of course, most of them refer to constraints rather than specific columns, but the constraint name is almost always based on the column name.
The probability of a column name matching with reserved words from the system dictionary is reduced. This is especially true for such common names as NAME, ID, COMMENT and DATE. As a result, the developer is freed from the need to add other redundant combinations of characters to the name.
In our company, it so happened that most of the client software used was developed on the basis of Oracle Forms, where for any field, by pressing the F1 button, you can see the name of the source column. The ability to instantly link an object on the form with an object in the database helps a lot both during the initial acquaintance with the system and during further operation.

Restrictions

Coleham recommends naming constraints by prefixing them with the full name of the table that the constraint applies to. I consider such naming to be an unreasonable waste, especially given the general 30-character limit on the length of a name. Therefore, I try to use wherever possible Table code instead of full name. Thus, for the primary key we get:
[Module_][Group_](Table code)(_PK)
Here and below the prefixes Module and Group constraints are inherited from the table they are associated with. This avoids uniqueness violations when forming names in large systems, and it is also convenient to group restrictions by modules.

For a unique key built on a single column:
[Module_][Group_](Column Template)(_UK)
I remind you that our column template includes Table code. So for a column PRM_CODE tables UTL_PARAMS_REF unique key will be called UTL_PRM_CODE_UK

For a unique key built on multiple columns:
[Module_][Group_](Table code_)(COMP_UK)[_#]
COMP- in this case means COMPOSITE(sign of a composite key), # (serial number) is used if there are several unique composite keys (to be honest, I can't think of a sane example for such a case).

Foreign key based on one column:
[Module_][Group_](Column Template)(_FK)
Since the full name of the foreign key column we have contains Codes child and parent tables, then for the column PVL_PRM_ID tables UTL_PARAM_VALUES the foreign key will be called UTL_PVL_PRM_ID_FK(refers to column PRM_ID tables UTL_PARAMS_REF)

Foreign key built on multiple columns:
[Module_][Group_](Table code_)(COMP_FK)[_#]
Column level constraint:
[Module_][Group_](Column template)(_CK)
Table level restriction:
[Module_][Group_](Table code_)(COMP_CK)[_#]
On the Internet, I often met heated discussions about the need to name a type constraint NOT NULL. Yes, I agree, lazy, but if you strictly adhere to the concept, then:
[Module_][Group_](Column template)(_NN)

Indices

I usually divide indexes into three categories:

Key-based indexes (primary and unique)
Indexes based on a single column
Composite (based on multiple columns)

Indexes based on keys (primary and unique) are named the same as their corresponding constraints:
[Module_][Group_](Table Code)(_PK) [Module_][Group_](Column Template)(_UK) [Module_][Group_](Table Code_)(COMP_UK)[_#]
Indexes based on a single column:
[Module_][Group_](Column Template)[_Role](_IDX)
Under role in this template, the index type modifier is understood. Coleham recommends using the following modifiers:

L– local partitioned index
G– Global index on a partitioned table
B– Bitmap index
F– Feature based index
C– compressed-index (compressed)
R– reverse key index (index with reverse key order)
U- unique index

Indexes based on multiple columns. Cowlam recommends the following form:
[Module_][Group_](Table ID)(_COMP)[_Role](_IDX)[#]
I consider the Cowlam pattern to be a special case, and I always try to list all columns (as long as it doesn't violate the length limit) in the index name:
[Module_][Group_](Table ID_)(Column List)[_Role](_IDX)
Why am I limited to a modifier COMP for constraints but try not to use it for indexes? The point is that composite constraints are the exception rather than the rule. There are usually not very many of them, and error messages about their violation are not very common. Another thing is composite indexes. First, there are simply too many of them. Secondly, there are often more than one per table. And, thirdly, the developer and application administrator works with them constantly, checking query plans.

triggers

In this article, I only consider DML triggers, because I think that all other types are more the responsibility of the DBA, and not the developer. Triggers are named according to the following rules:
[Module_][Group_](Table code)[_Purpose/Role]_(B|A|C (I|U|D)[S])[_#]
Where are the abbreviations B, BUT, FROM (BEFORE, AFTER, COMPOUND), determine the "moment" of the trigger; I, U, D(INSERT, UPDATE, DELETE) – actuation event; S (STATEMENT) - determine the "level" of operation.

In my projects, I distinguish two "typed" Targets (roles) of triggers:

AUDIT– triggers fixing changes to the main table in the journal table (for example, UTL_PRM_AUDIT_AIUD)
INIT- "initializing" triggers responsible for generating surrogate keys and filling in default values for columns (for example, UTL_PRM_INIT_BI)

Representation

The rules for naming views are no different from the rules for naming tables. The only wish is to include in the name a sign that given object is just a representation. Approaches here may be different. I met this sign in the form of a name prefix. For example, V_ or even V$, like the Oracle system views. Personally, I use suffixes:

$V- for normal performances
$MV- for materialized

But I will not advise you. The dollar sign as a separator is a matter of habit. This is the "anchor" for my eyes, which allows you to distinguish between a table and a view. Objectively, I can’t look at these approaches, so I have nothing against the “underscore” sign, like in Verstein (suffixes _V and _MW).

Sequences

I distinguish sequences among other objects with a suffix _SEQ and I recommend naming according to the following rule:
[Module_][Group_](Table ID | Full column name | Purpose)(_SEQ)
Table code(abbreviated table name 2-4 characters) is used for sequences used to generate a surrogate primary key of a table.

Column name(and in our case it, let me remind you, includes the table code) is used to generate the value of a column that is not included in the primary key. In fact, this is a degenerate case that I don't really use. If the sequence is not used to generate primary key values, I try to reflect in the name Target this sequence.

For example, the sequence to generate the primary key of a table is INTERNET_LOGINS I will call ILG_SEQ, and the sequence for generating a specific login account the Internet - LOGIN_SEQ.

Synonyms

Synonyms are named the same as the objects they refer to.

Types

As for the types, I don't have a definitive opinion. I met different approaches to naming these objects, but I could not fully decide which approach is closer to me. I will describe here those that do not cause negative reactions in me:
[Module_][Group_](Name)[_Collection attribute]T
This template is recommended by Colem. The attribute of a type collection is denoted by the symbol T. Thus, a single object type always has the suffix _T, collection type - _TT. For example, UTL_PARAMETER_T, UTL_PARAMETER_TT.
[Module_][Group_](Name)[S]_TYP
Here S denotes the plural, and the suffix TYPE qualifies a database object as a type. For example, UTL_PARAMETER_TYP, UTL_PARAMETERS_TYP. I like this approach least of all, because the attribute of the collection is not selected and the eye does not cling to it.
[Module_][Group_](Name)_(OBJ | TAB)
In this notation, if the name of the database object ends with OBJ or TAB, then the object is of type ( TAB- collection, OBJ is a single object). For example, UTL_PARAMETER_OBJ, UTL_PARAMETERS_TAB

Software modules

Rules for formatting the code of program modules, I would like to single out a separate article. Here is the template suggested by Coleham. For treatment packages, Bill uses the following rule:
[Module_][Group_](Purpose/Purpose)[_Function. modifier][_PKG]
In terms of NC Cowlam Function modifier(for me the term is clearer Subgroup) is used when separating some functions into a separate package during refactoring. Let's just say it's an additional level of the logical group. For example, a package UTIL contained functions for working with numbers and strings. It was split into two: UTIL_NUMBER and UTIL_STRING.

When developing in PL / SQL, a specialist constantly operates with the functions and procedures of other packages. To keep the code from looking unwieldy, I try to avoid unnecessary lengthening in package names. Therefore the suffix _PKG I use it only in cases where the name of the package can coincide with the name of another schema object.

For individual procedures and functions, Coleham recommends the following template:
[Action_](Purpose/Destination)
Action is a verb GET, SET, ASSIGN, RUN), under the goal - what needs to be done. For my part, I try not to use procedures and functions outside of packages at all when developing. In addition, the same functions I often group by the objects they work with, so I usually use the template
(Target)[_Action]
So the code hint does a great job of grouping procedures by object: PARAM_GET, PARAM_SET, PARAM_CHECK etc.

Conclusion

As promised, here is a link to my own "Oracle NC" poster.
In no case do I impose my rules and standards on anyone and do not insist on their use. I simply consider the presence of NC and compliance with its requirements in the team as good style - the "politeness" of the developer to the one who will subsequently work with the system.
Good luck with your projects.

P.S. The attentive reader has certainly noticed my little deception. The first goal of the article was never achieved: far from all types of Oracle DBMS objects are described in the article and are present in the NC poster. Well, you have a chance to correct this defect. You can download the "source" of the NC poster and edit it to suit your goals and objectives.

The article uses the following

What is this article about

This article continues the series of articles "First steps in development on 1C". It discusses the principles of working with generic collections. After reading the article, you will learn:

What are generic collections, when and in what cases should they be used?
What do all universal collections have in common? What techniques can be used to work with all of them?
What is an array, how and when to use it? What methods does he have?
Why use a structure? How is it different from an array?
When to use a list of values? How to display it on the form?
Compliance - what is it and when to use it? What are the advantages regarding structure?
What is a table of values used for? How to describe its structure? How to add/remove lines? How to bring it to the form?
Value tree - what is it used for? How to fill out and display on the form? How to work with him?

Applicability

The article discusses the 1C:Enterprise 8.3 platform of the current edition.

How to work with universal collections in 1C

A collection of values is a kind of container that can usually contain any number of elements. At the same time, there are often no strict restrictions on the data type.

You can add values to a generic collection. All values in the collection can be traversed. These collections are mainly used for some kind of processing in algorithms. Those. these are some dynamic structures that exist for the duration of the algorithm.

It is important to understand that collections are not stored in the database (we are not talking about the Value Store data type, which can store almost any data type).

There are different kinds of generic collections: Array, Structure, Mapping, Fixed Array, Value Table, tabular part etc. But all collections have similar behavior.

A collection can be created as a result of some function (the function returns a generic collection as a value).

You can get the new collection manually by calling the constructor and creating an instance of the class.

For example: OurArray = New Array;

Constructors for many generic collections are parameterized.

So, in the constructor for you can specify the number of elements in the corresponding dimensions. Those. you can immediately declare multidimensional .

The corresponding description of the constructor is in the syntax helper.

Thus, using constructor parameters, you can immediately set the desired behavior of this object.

But the parameters are optional, the developer can not set them and further define the behavior of the Array as he sees fit.

Almost any generic collection can be created using a constructor (with the exception of tableparts, which act as configuration objects).

For generic collections, there are general concepts like index and number. Each element of the collection has an index. The index starts from zero.

To access an element OurArray, you can use index access, for this the index is indicated in square brackets.

For example, OurArray. Note that in this case the system returns the element of the Array at index 3, and in order this is the fourth element of the Array.

For some collections, there is also the concept of a row number. The line number starts with one. For example, for a tabular section there is such a property as a row number. It is important to keep in mind that if we know the row number and want to access by index, then the value one less than the row number should be used as the index.

The concept of a line number does not exist for all collections, but mainly for those that can be displayed in the user interface.

For all collections, traversal of the elements of the collection is used. Bypass is possible in two ways: cycle For and cycle For each.

For most generic collections, the following methods apply: Count, Index, Add, Insert, Delete, and Find.

Count is a function that returns the number of elements in a collection. It can be used before the loop For, as shown in the figure.

The Index method does not exist for all collections, but only for those whose elements can be referenced. An example is Value Table.

Value Table is a specific collection of strings, the strings can contain different columns with different types values.

Each line is an independent entity. You can get a link to it, through this line you can access the values of the columns in this line.

The Index method allows you to determine which index corresponds to a given row (that is, the current position of the row in the table). Index values start at zero.

Methods for adding new values to this collection exist in almost any generic collection. The figure shows how to fill an Array with values from 0 to 10 in two ways.

To add an element to an Array, we can use the method Add, indicate the added value in brackets. In this case, the value will be added to the end of the list, i.e. The array will constantly increase due to the last position.

Another method that allows you to add values to a collection is the method Insert. It differs from the method Add so that you can specify where to insert the added element.

Syntax: Insert (,)

The first parameter specifies the index into which the new value will be inserted. Those. we, for example, can specify that each value should be inserted at the beginning of the list (the second way in the figure above).

The method is used to remove elements from a collection. Delete. In the Delete method, it is indicated by index which element we will delete.

Syntax: Delete()
Usage example: OurArray.Delete(5);

It should be noted that for those collections where strings represent an independent entity (for example, for TableValues), we can also use the get index method to remove the given row later.

Almost all collections have a method for finding a value - Find. The method is passed the value that we want to find. In some collections, you can put any restrictions.

For example, in Value Table you can specify those rows, those columns in which you want to search.

If the value is found, then this method returns the index or a specific string. If no value is found, a value of type is returned. Undefined. When applied to an Array, returns Index, or the value Undefined.

Usage example: OurVariable = OurArray.Find(8);

Generic collections can be cleaned up very quickly, i.e. remove absolutely all elements. For this, the method is used clear(), which removes the elements of an Array, strings TableValues, or data from other collections.

Additional Methods for Array

Method Bboundary() returns the number of elements minus one. Those. if we use a loop For, then instead of the Quantity method, we can immediately use the method The border().

In particular, the variable NumberInArray could have been defined differently:

NumberInArray = OurArray.InBorder();
Then, when describing the cycle itself, one should not be subtracted from this variable.

The Set method allows you to assign a value to an Array element by index.

Syntax: Install(,)

Example: OurArray.Set(2,8);

Alternative option: OurArray = 8;

You can use the method for an Array Get, in order to read the value at index without resorting to the use of square brackets.

Syntax: Get()

Example: OurVariable = OurArray.Get(2);

Alternative option: OurVariable = OurArray;

Universal Collection Structure

A structure, like an Array, can have unlimited quantity elements, but the content of the element is different from the Array.

The structure is a collection, each value of which consists of a pair. The first element of a pair is called Key. The second element of the pair is Meaning.

Key is a strictly string data type that describes a value. For example, key"Code" can correspond to the value 113; key"Name" meaning "Vasya". There is no data type constraint on the Value itself.

The structure is very convenient to use if we want to create a list of parameters. If this Structure called OurStructure, then we will refer to its two values as follows: OurStructure.Code and OurStructure.Name.

Such an appeal is much more convenient than if we defined all the parameters in an Array and accessed them by index.

The structure makes the program code readable (understandable). The structure is used quite often, much more often than the Array.

It is used to describe some parameters, of which there are often enough a large number of in all algorithms.

In addition, the Structure is used if the procedure and function contain a large number of passed parameters.

Then it is much more convenient to write all the parameters into the Structure and pass it on. Those. there is a "packing" of parameters of procedures and functions.

Separately, it should be noted that as key not absolutely any string can appear in the Structure. Certain restrictions apply.

Key should act as an identifier. This means that in key there must be no spaces and it cannot start with a number.

Permissible start key with a letter or underscore. In this way, Key must satisfy the requirements for creating identifiers.

Let's note how the Structure differs from the Array. The struct has a method Insert, Array has two methods for insertion: Insert(to a certain position) and Add(at the end of the list). In an Array, all elements are ordered.

A structure is a kind of unordered set. That is why there is only an insert method for a Struct.

The value is inserted not at a specific position, but into the specified set. A Struct cannot be referenced by index like other generic collections.

The elements of the Structure are referred to only by the name of the Key. However, the For each of loop also works for the Structure, but you should not rely on the order of the elements of the Structure.

A structure is created in the same way as other generic collections by using the New constructor, specifying the data type of Structure.

Like an Array, a Struct's constructor can have parameters. Those. it is possible to describe the contents of the Structure itself using a constructor.

Unlike an Array, where you can simply specify the number of elements for all dimensions, in a Structure it is possible to set the content itself.

For example: Our Structure = New Structure(“Code, Name”, 133, “Vasya”);

First, the names of the Keys are listed separated by commas, and then, respectively, in the same sequence, the values of the parameters.

To add a new value to the Structure, there is a method Insert, which inserts a new pair (Key and Value).

For example: OurStructure.Insert("Family Members",3);

The Structure is characterized by another method that is used quite often. This is the method Property.

Using this method, you can understand if there is such an element in this Structure, for which the Key has such and such a name.

If such an element exists, the system will return True, otherwise it will return False.

For example, the expression OurStructure.Property (“Family Members”) will be equal to True. This method is used quite often in the analysis of the Structure.

As for any universal collection, it is permissible to access the properties of the Structure by index. But the index for the Structure is a string value.

For example: Report(OurStructure["Family Members"]);

However, one should not forget that a Structure is not an ordered set of objects, which is why reference by index 0, 1, 2 is not allowed.

Generic Collection List of Values

List of Values is a linear list of elements of any data type.

Each element consists of several values. Schematically, a list of values can be represented as a list with four columns.

First column - mark. It has a boolean data type and allows the user to either check the boxes or uncheck them.

The other column is a picture that can somehow visually depict this element, i.e. match this line with any picture.

The third column is the stored value itself, i.e. this is any data type, and it can be different in different rows.

The fourth column is the view, i.e. this is a string description given value. The view will be displayed to the user when he views this element. In this case, if the view is not set, the system will try to get views for the element contained in this position.

List of Values- this is the object with which the user can visually work. Those. List of Values can be displayed on the form.

The user can perform some actions with it. Besides, List of Values can be inferred independently using methods, i.e. show on the screen in some branch of the algorithm (with the exception of the server code), so that the user selects some line or puts some checkmarks.

Let's find List of Values in sitax helper. Constructor List of Values not parameterized (no default values can be set).

There are methods like:

Insert(,) ;
Add(,);
Quantity();
Index().

There are also special methods, for example, UnloadValues(). This creates an Array into which the list of values is copied. For example:

ArrayElements = ListPriceTypes.UnloadValues();

There is also a reverse method:
PriceTypeList.LoadValues(ElementsArray);

There are search methods:
FindByValue(); FindByIdentifier().

There is a copy method:
CopyList = ListPriceTypes.Copy();
This method is intended to make some kind of modification with a copy.

There are methods:
SortByValue();
SortByView().

Methods SelectItem(,) and MarkItems() call a modal dialog box that stops the execution of the algorithm until the user closes this window.

To use these methods in configuration properties Modality usage mode must be set to Use.

Sample code called from a Managed Application module:

Display this code in user mode (modal dialog box).

Below List of Values used as the available data type for the form attribute. We create a new attribute for the processing form, determine the type for it List of Values and display it on the form.

We create new team StoreGifts, transfer it to the form and define an action handler for it.

In user mode, when you click the Fill in Gifts button in the processing form, a completed list will appear.

If desired, the list can be edited: add some elements, remove some.

Universal Collection Compliance

This collection is very similar to Structure. Just like a Struct, a Match is a set of values that consists of a key and the value itself.

The main difference is that any data type can be specified as a Key, as well as for a value. In view of this feature, it is necessary to access the match value by index; the key value is specified as the index value.

The key can be a data type other than a string. The properties and methods of working with Matching are almost the same as those of the Structure.

The Match Constructor, unlike a Struct, does not contain the ability to specify parameters.

Usage example:

Correspondence is useful when it is necessary to link any two structures. For example, each row of the tabular part must be matched with a row from the table of values.
In this case, the row of the tabular section is used as the Matching key and the corresponding value is indicated.

When inserting elements into a Match collection other than the method Insert(,) There is another way to insert a value, and that is to use the normal assignment operator.

For example: OurMatch = New Match;
Match = 999;

Those. if the element was not present in the collection, then it will be added using the assignment operator, and if present, it will be updated.

This is different from Structure.

Generic Collection Table of Values

Value Table is a table with an arbitrary number of rows and an arbitrary number of columns. An intersection can store values of any data type. If necessary, columns can be typed, i.e., determine in which column which type of data is stored.

You can leave the columns untyped, then the same column in different rows can store values of different types.

Differences TableValues from a 2D Array:

this is an object that the user can work with (the table of values can be displayed on the screen, the user can fill it in, in the future the entered data can be read);
building indexes for fast searching;
clone, fill certain value the entire column, unloading all columns into an array.

Value Table used as a kind of buffer for storing information. Value Table is returned and accepted as a parameter by many of the system's methods. It is possible to build a query to the Table of Values.

So, Value Table consists of a set of rows and a set of columns. Both rows and columns are collections.

Those. inside the collection Value Table there are two more collections. Let's turn to the syntax assistant and find Value Table.

Supported data types: itself Value Table, which consists of strings. Each row is represented by a data type RowTableValues, which has its own properties and methods. Available CollectionColumns TableValues also has certain properties.

Important point! The procedure that generates Value Table, should compile & OnServer.

Before you start working with Value Table, you need to determine which columns it will contain (i.e. create them). Syntax:

Add(,)
(optional)
Type: String.
(optional)
Type: Description Types
(optional)
Type: String.
(optional)
Type: Number.

For example:

To call this procedure, we will use the command.

In description TableValues as elements of the collection are exactly RowsTableValues.

Unlike columns, which consist only of properties (Name, Type, Title, Width), in RowTableValues there are both properties (reference by column name) and methods (you can get and set a value, work with owners).

To add a new row to the table, you need to use the method either Add(), or Insert(). In the second case, you should specify which position the required string should be placed in.

To assign a value to a column, we refer to it by the column name or index (using square brackets) separated by a dot.

For filling TableValues the following methods can be used:

clear()- to remove all rows from TableValues.

FillValues(,)– allows you to fill all columns or selected columns with one value.
LoadColumn(,)– loads a column from an array.
UnloadColumn()– unloads a column into an array.

The last two methods are useful when you need to move a column from one table of values to another.

Copy(,)- allows you to create a new table based on an existing table Value Table, while specifying not all rows and all columns, but only some of them. Return value - Value Table.

You can copy the structure TableValues. There is a corresponding method for this. CopyColumns(). We will receive an empty Value Table with the desired structure.

AT Value Table there is a method total(). You can specify the column in which you want to sum the numerical values. With regard to the previously shown code in the Tableau, you can calculate the value: TK.Total(“Sum”).

AT Value Table it is possible to group (collapse) numerical values by the same values of certain columns using the method Collapse(,).

With regard to the previously shown code in the Tableau, you can calculate the value: TK.Collapse(“Day of the Week”, “Amount”).

Value Table can be shown on the user's screen so that you can perform any actions with it. But unlike List of Values from the program code, you can’t just call the table on the screen.

To display Value Table on the screen, create a form attribute and assign it a data type Value Table.

After that, the resulting table should be displayed on the form.

In the form module, at the end of the previously compiled algorithm (in the Procedure for Creating a Table of Values), add:
ValueVFormData(TK, Table);

Generic Collection Tree of Values

a versatile collection that is very similar to Value Table. The difference from the table is that the rows of the tree can be subordinate to each other, i.e. some kind of hierarchy can be formed.

It can also be displayed on the screen. The value tree explicitly consists of a collection of rows and a collection of columns. There are two properties in the tree, Rows and Columns.

Since rows can be subordinate to each other, then for each row a Parent can be specified, as well as rows subordinate to it.

Let's create the appropriate Tree command and its processing procedure.

Let's create in which there is one parent row and two subordinate rows.

Create a form attribute DerZn(data type - Value Tree).

For this attribute, we will create the Year and Month columns.

Move the corresponding element DerZn to the form.

In the end Procedures TreeOnServer() add:

ValueVFormData(TreeZn, DerZn);

Let's check what happened in user mode.

With button Add you can add new lines. They can also form a hierarchy.

To iterate over all elements of the value tree, we need to use recursion, i.e. calling a procedure from itself. For example, processing a value tree might look like this: