24.2.3. Editing external data source structures

<< Prev   Next >>

24.2.3.1. General information

To operate tables of external data sources, in the configuration tree, use the branch with the name of the relevant external data source: External data sources ‑ <External data source name> ‑ Tables. To determine an external data source:

1. Determine the external data source.
   Determine data source tables.
   Determine fields for each table of the external data source.

The configuration can describe fewer tables and fields in them than there are in the real database, but you cannot set a table or a field not included in the real database.

You can generate an external data source structure both in manual mode and when loading this structure using a special constructor.

24.2.3.2. External data sources

When you create an external data source, specify its name. With this object, you can identify a group of tables when accessing the data it contains. A data source consists of one or several tables that consist of fields.

If you intend to make a record to an external data source, set the required value in the Data lock management mode property. If the lock management mode is set to Automatic and managed for an external data source, the lock mode for each table is determined by the value of the relevant table property. If a certain lock management mode is selected for an external data source, the property of the external data source table with the same name is ignored.

24.2.3.3. External data source tables

When you create a table, the object editing window opens.2.12. Object editing window

To identify the table within the application, use the Name table property. For the system to know which physical table of the external data source is related to the configuration object, fill in the Name in the data source property. The value in this property must exactly match the name of the table in the external data source. Placing data from several physical tables of an external data source in the same application table is not supported.

The Table data type property determines what entities are stored in this table: object-based or non-object-based. If a table has a single field that uniquely identifies a record in the table, then the table can store object data. The closest analog of these tables can be a catalog. If a record in a table is identified by several key fields, then the table contains non-object-based data. The closest analog of these tables is an information register. Specify the key fields of the table in the Key fields property.

For tables containing object-based data, you can specify a field that will be used as the object presentation. To do this, use the Presentation field property.

Depending on which DBMS object describes the generated object in 1C:Enterprise, correctly set the Table type property. If the property is set to Table, the configuration object will be an analog of a real database table or view. In other cases, set the Table type property to Expression. If an external data source table has the Table type, specify the name of the real table or non-parameterized view in the Name in the data source property, for example, dbo.ErrorLog. For a table of the Expression type, specify the Expression in the data source property. In the simplest case, this would be a call to a function that returns table data, for example, dbo.ufnGetContractInformation(&1). If an expression is written to this field, it is recommended that you specify it in brackets to minimize various conflicts during the actual execution of a query to the DBMS.

If the table stores hierarchical data, you can inform the system about it using following properties: Parent field, Empty parent flag, and Empty parent value. These properties are available only for tables that store object data. With the Parent field property, specify the field in the database table that stores the reference to the parent for this record. The fields that can be selected in this property must have the type of the reference to the used table. For the products table from the example, the parent field must have the ExternalDataSourceTableLink.DBF.products type.The Empty parent flag and Empty parent value properties determine what value will determine the record without a parent record. Once these properties are indicated:

• The data composition system uses them to operate hierarchical groupings and when checking hierarchy conditions.
• The query language uses them to operate the IN HIERARCHY construct.
• These properties are used to display tables that store hierarchical data (display options are similar to those used to display hierarchical catalogs).

With the String input property, you can specify the names of the fields that will be searched for in a field whose data type specifies an external data source table.5.4.5. Predefined data

When you create tables manually, you can specify the Key field, Presentation field, and String input properties only after the external data source table fields are generated.

To manage writing to an external data source table, select or clear the Read only check box. If the check box is cleared, information can be written to the specified table. The check box is set to True automatically for table based on views and functions. Note the following properties for a table to which information can be written:

• Transaction isolation level. Determines the transaction isolation level that will be set on implicit transactions of a record to this table.
• Data version field. Specifies the field of the external data source that increases its value automatically after each record to this table. If the field is specified, the system uses the field value on interactive object modification. When writing, the field values in the database and in the form are compared. If they are different, the object is modified.

  If the field is not specified, the object is read into the memory when the form is opened interactively. The object is read again and compared with the copy in the memory on writing. If two copies are different, it means that the object is modified.

• Data lock fields. Indicates fields for which data can be locked.9.3.4. Operations with managed locks using 1C:Enterprise language tools
• Generated. Specifes configuration objects that can be used to generate table records.
• Data lock management mode. Specifies which lock management mode will be applied when writing to this table.

24.2.3.4. External data source table fields

Table fields describe what physical table data will be available from the application. Use the Name field property to identify table fields in the application. To specify the mapping between an application field and a physical table field, use the Name in the data source property. The value in this property must exactly match the table column name specified in the Name in the data source property of the parent object of this field. You cannot combine data from several columns of one or several external data source tables in a single table field.

If the value in the Name in the data source property is enclosed in single quotes, this value is included in the SQL query to the database without transformations, regardless of the characters. If the value in the field is not enclosed in single quotes, this value will be included in the SQL query to the database enclosed in double quotes if the name contains special characters.

The Type field allows you to specify the type of this field. A limited set of types is available for selection. Field types can be:

• Number
• String
• Date
• Boolean
• UUID
• BinaryData
• Types defined by external data source tables

If you need to specify a flexible type for a field, only the following types can be included in this flexible type: Number, String, Date, and Boolean.

When you receive data from external data sources, the system automatically converts this data to the type specified for the relevant field in the application.

The Read only property specifies that this field cannot be written in the table. Set this property to True for fields with automatic modification, automatically generated key fields, calculated fields, and so on.

However, when you operate an external data source, you will need to write fields that are normally read-only, and vice versa. Such need is temporary, for example, it is necessary to perform a single or scheduled operation. You should use methods GetChangeableFields() and SetChangeableFields() to modify the list of recorded fields. Thus, the state of the Read only property describes the default behavior of the field. In some rare cases, you can modify the behavior of the field using the said methods.

The NULL allowed property specifies whether you can write NULL to a table field. If NULL is written to the table field, this value will be displayed as Not filled in the form.

24.2.3.5. Other properties

The form used to display a record and list of an external data source table can be generated by the system automatically or by the application developer. Keep in mind that if tables are non-object-based and key fields are not set, then:

• The record form cannot be displayed.
• The ExternalDataSourceTableRecordSet object cannot be accessed.

This happens because it is impossible to uniquely identify the required record set in the table.

24.2.3.6. Transactions

You can use both automatic and managed locks can be used for writing to external data sources.9.3. Managed locks

To operate transactions, use the following methods of the external data source manager: BeginTransaction(), CommitTransaction(), RollbackTransaction(), and TransactionActive. In particular, it means that transactions for multiple data sources (distributed transactions) are unavailable.

To manage locks, use the following properties and objects:

• Property Data lock management mode of the external data source.
• Property Data lock management mode of an external data source table.
• Fields specified in the Data lock fields property of an external data source table.
• Property Transaction isolation level of an external data source table.
• Parameters of the BeginTransaction() method of the external data source manager.

Use the Data lock management mode property of an external data source to set the default lock mode for external data source tables (similar to the Data lock management mode configuration property).5.1.6. Specifying compatibility properties This property can have the following values:

• Automatic. Mode of automatic locks for all external data source tables. Serializable is used as the transaction isolation level. This mode allows you to use transactions to the external source database simultaneously from the platform and from other clients. In this mode, the property with the same name in the external data source table is ignored.
• Managed. Managed lock mode for all external data source tables. Read committed is used as the transaction isolation level. You can use this mode when only a 1C:Enterprise-based system is related to an external source database. In this mode, the property with the same name in the external data source table is ignored.
• Automatic and managed. Lock mode is selected for each table depending on the property of the table with the same name. The transaction isolation level is selected depending on the Transaction isolation level property of an external data source table.

If the Transaction isolation level property of an external data source table is set to Auto, the actual transaction isolation mode is set based on the Data lock management mode property (described above). If a specific property is specified, it will be used. If DBMS of the external data source does not support the transaction isolation level specified in this property, the level will be automatically modified in the following order (until the first supported one): Read uncommitted à Read committed à Repeatable reads à Serializable.

In managed lock mode, database-level locks are imposed by setting the relevant transaction isolation level and specifying the FOR UPDATE expression in database queries.

The TransactionActive() method allows you to determine the current transaction state in the used external data source.

24.2.3.7. System behavior features when writing to external data sources

When operation of an external data source is not interactive, keep in mind the following behavior features:

• When you create a record in an external data source table:
  • You cannot change attributes of an object to be written that have the ReadOnly property set to True.
  • If the attribute included in the key fields has the ReadOnly property set to True, this attribute can be changed only as a result of calling the SetNewObjectRef() method. If this attribute has the ReadOnly property set to False, its value can be changed explicitly (using an assignment operation).
  • The system does not automatically fill in the values of the attributes used to generate the object key or the record set. This means that the recording will be completed with an error if the values of the key attributes are not set for recorded object to be recorded.
• When a record is changed in an external data source table:
  • Only attributes for which the ReadOnly property is set to False are changed.
  • When key fields are changed, the value of the reference is changed.
  • If the DataVersion attribute is filled in, this value is used in the WHERE expression for a a data update request. After the request is successfully completed, the value of the attribute is read again from the database.
• When a record is deleted from an external data source table:
  • Only direct deletion is completed. The deletion mark is unavailable and not supported.
  • Deletion does not check referential integrity.

When DBMS PostgreSQL is used as an external data source, writing fields of the UUID type is not supported.

When a UUID is written to an external data source table field, 1C:Enterprise platform rearranges the UUID data. Thus, the data will be moved to the external database in a modified form. When the UUID is read, the data is rearranged back. Therefore, the UUID retains its initial value on writing/reading.

Storing a value of the UUID type in computer memory can be represented as follows (byte order is "lower-higher"):

• The Data1 value 4 bytes long
• The Data2 value 2 bytes long
• The Data3 value 2 bytes long
• The Data4 value 8 bytes long

A value of the UUID type will be written to the database for such storage as follows:

• The entire Data4 value
• 1st byte of the Data3 value
• 0 byte of the Data3 value
• 1st byte of the Data2 value
• 0 byte of the Data2 value
• 3rd byte of the Data1 value
• 2nd byte of the Data1 value
• 1st byte of the Data1 value
• 0 byte of the Data1 value

24.2.3.8. Loading the table structure from external data sources

You can load the table structure from an external data source available for the developer (for example, copies of a real database).

To do this, when you create a table, specify Select from list of external data source tables in the external data source table wizard. Then specify the external database connection string using the Connect to data source window. While generating the connection string, you can use the wizard of the string for connection to an external data source. For this, click "..." to the right of the Connection string field.

After the connection to the external database is successfully completed, a list with tables and fields of the attached data source will be opened. Then select the tables and fields that will be used by Designer to generate a structure of objects that describe the current data source.

While receiving the structure of an external data source, the system performs the following actions:

• It attempts to determine whether object-based or non-object-based data is located in the table. The table will be considered object-based if only one key field is specified for it. It will be non-object-based otherwise. If the system made an error while specifying the key fields, you can manually modify the fields that form the table key. If the table is determined as object-based, you can specify its field that generates a presentation of this type of data. Specify the presentation manually.
• It converts data source column types to 1C:Enterprise types that will be used to specify types of table attributes. All types from an external data source are converted into the following 1C:Enterprise types: number, string, date, boolean, UUID, binary data, and types related to object-based tables of data sources.
• It attempts to determine types for table fields. In this case, the system tries to determine what type of data is stored in the table column. If it can be considered a reference to data in another table, the system specifies the relevant type in the column. If the system selected the incorrect table column type, you can change it manually.
• When you set up load, you can set up the following parameters of the data structure to be loaded:
  • Name of the object in the configuration. The Name in the configuration column.
  • Type of the attribute to be loaded. The Type column.
  • Specify the fields included in the key (for object-based data). The Key field column.
  • Specify the field that stores the object presentation. The Presentation field column.
  • Specify the field that stores the object version (for optimistic data locks). The Version field column.
  • Specify that the table or attribute is read-only. The Read only column.
  • Specify the option to specify the NULL value in the attribute. The NULL allowed column.
  • Specify the filling value for the attribute. The Filling value column.

Then select the check boxes for the tables and fields to be moved to the application metadata.

If the Remove tables and fields not available in the external data source from the configuration check box is selected, the tables and fields absent in the external data source (for example, tables or fields have been deleted in the external source) will be deleted from the configuration once the table wizard stops operating.

Once you click Finish, the structure of the external data source will be loaded.

<< Prev   Next >>