The basic task of OLEDB provider for InterBase as any other component of access to the databases is control of the information that selected from the database. Since IBProvider is general-purpose engine, there are realized in it the solutions intended both for the use in the trivial tasks, and in the tasks of operating with volumes of data.

Effectiveness is ensured due to the specialized mechanisms of control of the set of rows. At the current moment you can use:

• Read-only, unidirectional access
• Read-only, random access
• Read-write, random access
• Read-write in delayed transmission of changes mode, random access

Read-only row sets

The row sets, which do not make it possible to modify selected data, are the most highly productive method of information processing.

Fundamental characteristics

• Optimized rows manager.
• The presentation of row sets in the form of array permits implementation of effective scrolling and access to row sets via the absolute number.
• Tracing and retention in RAM of the most frequently used rows.
• Displacement of the least used rows into temporary file.
• 64-bits access to temporary file
• Maximal number of rows is 2³²

Automatic creation and use of temporary file is one of the most important characteristics of IBProvider. By change of " Memory Usage" property value you can yourself regulate the threshold of the start of the mechanism of data unloading into temporary file.

This mechanism is present in both types of read-only row sets. This is explained by the fact that even with the unidirectional access you can have a simultaneous access to the data of several rows. Therefore if user begins to retain more than can be placed in the indicated RAM volume, provider nevertheless will unload the part of the data into temporary file.

Why swap-file and support from the operating system are not used?

1. Swap is system resource and is shared between all applications, which work on the computer.
2. InterBase supports 2³² rows. Therefore the use of 32-bit addressing RAM does not make it possible to load and to process such volumes.
3. Read-only row sets make caching at the level of one row, but not at the level of the RAM page, as swap does.
4. Explicit control possesses the larger predictability.

Updateble row sets

For the tasks, in which it is necessary not only to select data, but also to have a possibility to modify it and to save some changes back into the database, they are realized two kinds of row sets optimized for these purposes in provider:

• Row set with immediate update.
• Row set with support of delayed transmission of changes.

The identical mechanism of rows control is used for the both types of row sets. However, the realization of delayed transmission of changes somewhat more complex, and therefore it requires more resources.

Basic characteristics

• Page organization of local storage; mixing different data on one page is not allowed to increase the productivity. Storage operates with the following types of the data:
  • List of rows of set
  • Data of rows of set
  • Data of arrays
  • Headers of BLOB-fields
  • Segments of BLOB-fields
  • List of delayed changes
• Caching at the level of pages
• 64-bit addressing virtual space allows to store much more than 2³² rows
• 64-bit access to temporary file
• Inner data presentation uses IB types format
• Resistance in case of failure of the multistage operations
• Automatic and controlled mode of database change holding
• Disconnected row set
• Pool of SQL-queries
• Separate transactions for read and write
• Special possibilities

Page organization and caching

In the process of design and testing of the productivity of local storage different schemes of data storage were tested. However, after a series of experiments we arrived at the classical architecture of storage of all data within the framework of one file.

Presentation of set in the form of list ensures a good productivity of operations of row insert and deleting. However, it combines very badly with the operation of scrolling and access to row via the absolute index. In particular when using IRowsetScroll::GetApproximatePosition method to obtain the absolute row index provider sorts out all elements of list. At the same time IBProvider traces the total number of rows in the set; therefore overhead expenses for obtaining of this information are absent.

Nevertheless, the standard methods of navigation on the set with the use of sequential access or bookmarks ensure a good productivity. It is reached due to the fact that when navigating only row headers are selected - data are stored separately.

List of delayed changes ensures the priority of the fixation of changes in database. Other positive side of this list keeping is the possibility of the rapid enumeration of rows that expect update.

Storing of data in IB-format

The identical method of storage is used also in the read-only row sets, but in this case this presentation gives the following advantages:

• Storage will not pass new user data, which are not compatible with the column type. In the case of using the delayed changes this allows to reveal errors immediately, but not to put it aside before saving of changes in database.
• Formation of query parameters to update the data comes to setting of XSQLVAR pointers to the data loaded from the local storage. It excludes intermediate conversions and data copying and it ensures a good productivity of operation of saving some changes in database.

Since OLEDB types cover the much wider spectrum of data than InterBase supports, the built-in type converter takes basic load.

Failure resistance

At first sight the task of the setting of new values of columns includes no any complexities. However, taking into account

• Separate storing of BLOB and arrays
• Possibility of the failure of the record of changes into the local storage

many problems appear linked with the guarantee of integrity of the settings of new row data. Briefly problem was solved as follows:

• Provider carries out all setting and checking data operations and records all stages into rollback journal.
• In the case of the successful passage of all stages provider cleans the rollback journal and returns control to the client.
• In the case of the error of provider returns its description. The rollback journal will be processed with any subsequent operation linked with the data modification.

This scheme with such level of failure resistance was created for only purpose - to exclude the place leakage in the structured storage.

Automatic and controlled modes of record of changes in database

To save the changes in row set provider uses the usual interface of interaction with the database server in form of the parameterized SQL queries.

Automatic mode is used for the tasks, in which the user does not have a possibility to tune the created row set. For example, when using tools of the review of the database tables through IBProvider or using the provider as linked MSSQL server. Since, in the general case, it is impossible to automate the process of the generation of SQL queries for the record of changes in database, therefore into IBProvider there is realized the support only of simplest queries of the form "select * from table", where table - table with the primary key.

To execute row set update in automatic mode provider are carried out system queries for obtaining the additional table information:

• List of the table primary key columns; for update and delete operations the presence of primary key is compulsory.
• The list of the columns, for which record is permitted; it allows to exclude the computable columns, which can be only read, from update operation.

On the ground of this information provider can form correctly the necessary SQL queries.

In spite of the closure of automatic process, you have the possibility to make prompts concerning the generation of the queries. See row set properties " auto_insert_field_rule" and " auto_update_field_rule". Manipulating by these properties it is possible to process correctly the presence of columns with the values by default (DEFAULT) and to minimize net traffic passing only new data of row.

Furthermore, if the possibility to set the property of the opened row set presents nevertheless, then it is possible to determine the rules of the generation of the primary keys of new rows. It needs to indicate to row set via " auto_gen_key_rule" property what generators must be used for what columns.

Controlled mode. For overcoming the restrictions of the automatic mode IBProvider makes it possible to explicitly determine the queries, which should be used for the backward record of changes in database. Three properties are determined for this in row set: " delete_sql", " insert_sql", " update_sql". For the tying SQL queries to the structure of the selected set the composite parameters are used:

parameter = <prefix> { <param_type> | <gen_param_type> . <gen_name> } . <column_id>

<prefix> = "named_param_prefix"

<param_type> = {NEW | OLD}

<gen_param_type> = {GEN | GEN_N | GEN_NI }

<gen_name> = the name of the generator used for obtaining the unique value of the column

<column_id> = {<aliase_column_name> | <full_column_name>}

<aliase_column_name> = the name of column in the result set. It can differ from the base name of the column.

<full_column_name> = <base_table_name>.<base_column_name>

<base_table_name> = the base name of the table, in which the column is located

<base_column_name> = column base name

When determining the names of generators, tables and columns it is possible to use the quoted names.

After changing the value of the property " named_param_prefix" (default value is colon), it is possible to change the prefix of the parameters of the query.

Example:

Original query
"select cust_no as c_id, customer as c_name, on_hold from customer"

insert_sql
"insert into customer (cust_no, customer, on_hold)
 values (:gen.cust_no_gen.c_id, :new.c_name, :new.on_hold)"

delete_sql
"delete from customer where cust_no=:old.customer.cust_no"

update_sql
"update customer
 set cust_no=:new.c_id, customer=:new.c_name, on_hold=:new.on_hold
 where cust_no=:old.c_id"

If you want to have a normal work with table CUSTOMER (database employers.gdb) you should either remove or rewrite the trigger SET_CUST_NO

Focus attention, when provider forms the query parameters it uses data in format of column linked with the parameter. Therefore if the type of column will differ from the expected type of the parameter then server will have to carry out the necessary conversions. The incapacity to carry out the conversions between BLOB and strings is among the known restrictions of the mechanism of the conversion in some versions of InterBase. The indicated restriction does not apply to the execution of parameterized queries through the commands, in which the data transmission in format that server expects is ensured.

Disconnected row set

In the case if it is necessary to select and modify data without backward record of changes in database, it is possible to set row set property " disconnected_rowset" to true. The creation of the external mechanism of backward record of changes with using of commands is one of the ultimate purposes of this mode. During the creation of this mechanism it is also expedient to turn on the mode of the delayed changes ( See the rowset property IRowsetUpdate), which makes it possible to organize the transaction changes of row set.

Pool of SQL queries

The pool of SQL queries functions for increasing the productivity of the operations of record of changes in database at the level of each row set that supports update. This mechanism allows saving and repeatedly using of the previously executed queries. In the general case the value of the query execution is summarized from following expenditures:

• Query creation
• Query preparation
• Query execution

InterBase SQL server allows:

• Re-preparation of query. Gain in the passage of the query creation stage.
• Repeatedly query execution. The maximum gain that can be obtained.

The largest effect from the use of the pool appears in the automatic mode with the record only of modified data. For example, there is the table with the columns (A1, A2, A3), in which one time you change only one column. In this case provider will generate queries of the form:
   "UPDATE ... SET A1=? WHERE..."
   "UPDATE ... SET A2=? WHERE..."
   "UPDATE ... SET A3=? WHERE..."

Additionally two system queries for obtaining are added to these queries

• List of table primary keys
• List table columns that can be modified

The optimum pool size ( See the rowset property query_pool_size) to do this work is equal to three.

1. Repeatedly using query for:
   • Obtaining of list of primary keys (executed one time)
   • Obtaining of list of updatable columns (executed one time)
   • "UPDATE ... SET A1=? WHERE ..."
2. "UPDATE ... SET A2=? WHERE ..."
3. "UPDATE ... SET A3=? WHERE ..."

In this case during further modification of any column A1... A3 there is chance of the repeated execution of the already prepared query. The search for the suitable query is achieved through the comparison of SQL text. If identical query absents in pool then provider will re-prepare query with the maximum dead time.

Focus attention that provider re-prepares queries used for the system needs.

In the case of using the controlled mode the optimum size of pool is the sum of SQL queries, enumerated in properties " delete_sql", " insert_sql", " update_sql", and the number of generators indicated in the parameters GEN_XXX of queries " insert_sql".

Manipulating by property " query_pool_size" it is possible

• to use only one query to execute all operations (value 1)
• to forbid the repeated use of the queries (value 0)

Focus attention that " query_pool_size" determines the maximum number of queries. Queries are created as far as necessary.

Separate transactions for read and write

The updatable row sets support two methods of the database change recording - with the use of the main transaction, in which data reading is produced, and with the use of other short transaction. Furthermore, you can indicate "mutable" mode:

• If the selection is produced in the automatic transaction (which is not controlled via the methods CommitTrans, RollbackTrans), that a separate transaction will be used.
• If user starts the explicit transaction, in which the data selection is produced, then namely it will be used for change of data.

The kind of transactions for data modification is determined by the row set property " modify_trans_type".

Special possibilities

By default updatable row set have some standard settings, which completely ensure the expected behavior. However, OLEDB specification determines the specific mode, which can be required for some class of the tasks:

• Visible of own inserts; if put property " Own Inserts Visible" into false then new rows will be invisible for the row set. In delayed changes mode this property is ignored.
• Visible of own changes and deleting; if put property " Own Changes Visible" into false then modified and deleted rows will be invisible for the row set. In delayed mode this property is ignored.
• Do not remove deleted rows; when you put " Remove Deleted Rows" into false, you may not remove deleted rows. Of course you cannot select any data from this rows.
• Do not change new rows; put property " Change Inserted Rows" into false to forbid to modified new rows.
• Return rows expecting inserts; put property " Return Pending Inserts" into false to forbid the provider to select new rows added during delayed changes mode.
• Selective permission of the data modification operations; separate bits of property " Updatability" is responsible for permission/prohibition of insert/delete/update operations.
• Open row set to add new rows; after you set property " Append-Only Rowset" into true, you open empty set with the structure of initial query. It is convenient, if just you want to add new rows into table and you have not to load already existing rows. In principle, you can simply achieve the same effect adding new rows, without carrying out the operations of movement over row set.