Scope |
ERP 10 and 11 |
In TOPCONN RDD, we do not have the concept of temporary index creation, only of permanent index. A permanent index is physically created in the relational database used, through TOTVS | DBAccess, in which we must specify one or more fields to compose the index key/expression.
When we use Codebase/DBF, we can specify as index key fields, such functions as Str(), DToS() and SubStr(), among other functions natively supported by the application providing access, in this case Local ADS and/or ADS Server.
When we use a SGBD relational database, through TOTVS | DBAccess, we must specify a key expression that always returns a character value, and the only functions allowed for data type adaptations in AdvPL for these index expressions are: Str(), DToS().
The expression used for creating a permanent index in AdvPL, for example: CPOC+DToS(CPOD) + Str(CPON,10), when we use the TOPCONN RDD, it is adjusted for the relational database used for an expression containing only the list of fields desired, in the specified sequence, and functions DToS() and Str() are not conveyed to the database, because there is no need to convert data for the database.
TOTVS | DBAccess handles a field of type "D" Date and saves it in the table at issue as a field of type "C" Character, of 10 bytes, in format YYYYMMDD, and the numbers are saved in a Double field.
Relational databases, mostly, if not all of them, support only the creation of indices in which you specify physical fields of the database. They do not support functions for converting or transforming data at index creation, such as SubString(), Left(), among others. Although some databases let you create calculated columns, these are virtual; that is, they do not physically exist in the table. Also, you cannot use virtual columns to create indices.
We understand that the RDD CodeBase/DBF, in which the ERP was originally developed, gave us some flexibility by allowing such operations. However, this was at a higher processing cost, because for each addition or editing, the RDD has to run string conversion and concatenation functions to update the indices that use this resource.
Thus, to maintain support to all permanent indices, created from expression results, in previous versions of the ERP, we would have a very high cost of complexity, performance and duplication of information in the tables. We would need to create physical columns in the tables, transparent to the user, to keep duplicated copies of grouped column parts, with database triggers internally fired in insert and update operations, a relatively complex mechanism to maintain, of unstable maintenance and performance-wise costly for the database.
Starting from the premise that, if you must index a given information for searching, then such information must exclusively occupy one physical field of the database. We thus avoid the constant operations of concatenation and dismembering of grouped data, placing each piece of information in its due defined space (field). Even with the impact of having to change the applications that used these types of indices, the organization and performance gains we got were very significant.
