Tag Archives: DBCREATEIND()

UPDATE

Posted on by vivaclipper

UPDATE

Update current database file from another database file

Syntax

      UPDATE FROM <xcAlias>
            ON <expKey> [RANDOM]
            REPLACE <idField> WITH <exp>
            [, <idField2> WITH    <exp2>...]

Arguments

FROM <xcAlias> specifies the alias of the work area used to update records in the current work area. This argument may be specified either as a literal file name or as a character expression enclosed in parentheses.

ON <expKey> specifies the expression that defines matching records in the FROM work area.

REPLACE <idField> specifies a field in the current work area to replace with a new value.

WITH <exp> specifies the value to replace into the current field. You must reference any field contained in the FROM work area with the correct alias.

RANDOM allows records in the FROM database file to be in any order. If this option is specified, the current database file must be indexed on <expKey>.

Description

UPDATE is a database command that replaces fields in the current work area with values from another work area based on the specified key expression. UPDATE is designed to update only current work area records based on a one-to-one or one-to-many relation with the FROM work area. This means that UPDATE can only update records in the current work area with unique key values. When there is more than one instance of a key value, only the first record with the key value is updated. The FROM work area, however, can have duplicate key values.

There are two formulations of the command depending on whether the FROM work area records are sorted or indexed on <expKey> or not. If RANDOM is not specified, both the current work area and the FROM work area must be indexed or sorted in <expKey> order. If RANDOM is specified, the current work area must be indexed by <expKey>, but the FROM work area records can be in any order.

To use UPDATE in a network environment, the current database file must be locked with FLOCK() or USEed EXCLUSIVEly. The FROM database file may be used in any mode. Refer to the “Network Programming” chapter in the Programming and Utilities Guide for more information.

Notes

. Deleted records: If DELETED is OFF, deleted records in both source files are processed. Records in the file being updated retain their deleted status and are not affected by the deleted status of records in the FROM file. If DELETED is ON, however, no deleted records are processed from either source file.

Examples

      .  This example UPDATEs the Customer database file with
         outstanding invoice amounts:

      USE Invoices NEW
      USE Customer INDEX Customer NEW
      UPDATE FROM Invoices ON Last;
         REPLACE Owed WITH Owed + Invoices->Amount RANDOM

Seealso

DBCREATEIND(), INDEX, JOIN, REPLACE, SET UNIQUE*, SORT

SET UNIQUE

Posted on by vivaclipper

SET UNIQUE*

Toggle inclusion of non-unique keys into an index

Syntax

      SET UNIQUE on | OFF | <xlToggle>

Arguments

ON causes index files to be created with a uniqueness attribute.

OFF causes index files to be created without a uniqueness attribute.

<xlToggle> is a logical expression that must be enclosed in parentheses. A value of true (.T.) is the same as ON, and a value of false (.F.) is the same as OFF.

Description

SET UNIQUE is a database command that controls whether indexes are created with uniqueness as an attribute. With UNIQUE ON, new indexes are created including only unique keys. This is the same as creating an index with the INDEX…UNIQUE command.

If, during the creation or update of an unique index, two or more records are encountered with the same key value, only the first record is included in the index. When the unique index is updated, REINDEXed, or PACKed, only unique records are maintained, without regard to the current SET UNIQUE value.

Changing key values in a unique index has important implications. First, if a unique key is changed to the value of a key already in the index, the changed record is lost from the index. Second, if there is more than one instance of a key value in a database file, changing the visible key value does not bring forward another record with the same key until the index is rebuilt with REINDEX, PACK, or INDEX…UNIQUE.

With UNIQUE OFF, indexes are created with all records in the index. Subsequent updates to the database files add all key values to the index independent of the current UNIQUE SETting.

SET UNIQUE is a compatibility command not recommended. It is superseded by the UNIQUE clause of the INDEX command.

Seealso

DBCREATEIND(), INDEX, PACK, REINDEX, SEEK

INDEX

Posted on by vivaclipper

INDEX

Create an index file

Syntax

      INDEX ON <expKey> [TAG <cOrderName>] [TO <cOrderBagName>]
             [FOR <lCondition>] [ALL]
             [WHILE <lCondition>] [NEXT <nNumber>]
             [RECORD <nRecord>] [REST]
             [EVAL <bBlock>] [EVERY <nInterval>]
             [UNIQUE] [ASCENDING|DESCENDING]
             [USECURRENT] [ADDITIVE]
             [CUSTOM] [NOOPTIMIZE]

Arguments

<expKey> is an expression that returns the key value to place in the index for each record in the current work area. <expKey> can be character, date, logical, or numeric type. The maximum length of the index key expression is determined by the driver.

TAG <cOrderName> is the name of the order to be created. <cOrderName> can be any Harbour expression that evaluates to a string constant.

TO <cOrderBagName> is the name of a disk file containing one or more orders. The active RDD determines the order capacity of an order bag. The default DBFNTX driver only supports single-order bags, while other RDDs may support multiple-order bags (e.g., the DBFCDX and DBFMDX drivers). You may specify <cOrderBagName> as the file name with or without a path name or extension. If an extension is not provided as part of <cOrderBagName>, Harbour will use the default extension of the current RDD.

Both the TAG and the TO clauses are optional, but you must use at least one of them.

FOR <lCondition> specifies the conditional set of records on which to create the order. Only those records that meet the condition are included in the resulting order. <lCondition> is an expression that may be no longer than 250 characters under the DBFNTX and DBFNDX drivers. The maximum value for these expressions is determined by the RDD. The FOR condition is stored as part of the order bag and used when updating or recreating the index using the REINDEX command. Duplicate key values are not added to the order bag.

Drivers that do not support the FOR condition will produce an “unsupported” error.

The FOR clause provides the only scoping that is maintained for all database changes. All other scope conditions create orders that do not reflect database updates.

ALL specifies all orders in the current or specified work area. ALL is the default scope of INDEX .

WHILE <lCondition> specifies another condition that must be met by each record as it is processed. As soon as a record is encountered that causes the condition to fail, the INDEX command terminates. If a WHILE clause is specified, the data is processed in the controlling order. The WHILE condition is transient (i.e., it is not stored in the file and not used for index updates and REINDEXing purposes). The WHILE clause creates temporary orders, but these orders are not updated.

Drivers that do not support the WHILE condition will produce an “unsupported” error.

Using the WHILE clause is more efficient and faster than using the FOR clause. The WHILE clause only processes data for which <lCondition> is true (.T.) from the current position. The FOR clause, however, processes all data in the data source.

NEXT <nNumber> specifies the portion of the database to process. If you specify NEXT, the database is processed in the controlling order for the <nNumber> number of identities. The scope is transient (i.e., it is not stored in the order and not used for REINDEXing purposes).

RECORD <nRecord> specifies the processing of the specified record.

REST specifies the processing of all records from the current position of the record pointer to the end of file (EOF).

EVAL <bBlock> evaluates a code block every <nInterval>, where <nInterval> is a value specified by the EVERY clause. The default value is 1. This is useful in producing a status bar or odometer that monitors the indexing progress. The return value of <bBlock> must be a logical data type. If <bBlock> returns false (.F.), indexing halts.

EVERY <nInterval> is a clause containing a numeric expression that modifies the number of times <bBlock> is EVALuated. The EVERY option of the EVAL clause offers a performance enhancement by evaluating the condition for every nth record instead of evaluating every record ordered. The EVERY keyword is ignored if you specify no EVAL condition.

UNIQUE specifies that the key value of each record inserted into the order be unique. Duplicate key values are not added to the order.

ASCENDING specifies that the keyed pairs be sorted in increasing order of value. If neither ASCENDING nor DESCENDING is specified, ASCENDING is assumed. Although not stored as an explicit part of the file, ASCENDING is an implicit file attribute that is understood by the REINDEX command.

Drivers that do not support the ASCENDING condition will produce an “unsupported” error. The following keywords are new to Harbour 5.3.

DESCENDING specifies that the keyed pairs be sorted in decreasing order of value. Using this keyword is the same as specifying the DESCEND() function within <expKey>, but without the performance penalty during order updates. If you create a DESCENDING index, you will not need to use the DESCEND() function during a SEEK. DESCENDING is an attribute of the file, where it is stored and used for REINDEXing purposes.

Drivers that do not support the DESCENDING condition will produce an “unsupported” error.

USECURRENT specifies that only records in the controlling order–and within the current range as specified by ORDSETSCOPE()–will be included in this order. This is useful when you have already created a conditional order and want to reorder the records which meet that condition, and/or to further restrict the records meeting a condition. If not specified, all records in the database file are included in the order.

ADDITIVE specifies that any open orders should remain open. If not specified, all open orders are closed before creating the new one. Note, however, that the production index file is never closed.

CUSTOM specifies that a custom built order will be created for RDDs that support them. A custom built order is initially empty, giving you complete control over order maintenance. The system does not automatically add and delete keys from a custom built order. Instead, you explicitly add and delete keys using ORDKEYADD() and ORDKEYDEL(). This capability is excellent for generating pick lists of specific records and other custom applications.

NOOPTIMIZE specifies that the FOR condition will not be optimized. If NOOPTIMIZE is not specified, the FOR condition will be optimized if the RDD supports optimization.

Description

The INDEX command adds a set of keyed pairs, ordered by <expKey> to a file specified by <cOrderBagName> using the database open in the current work area.

In RDDs that support production or structural indexes (e.g., DBFCDX, DBFMDX), if you specify a tag but do not specify an order bag, the tag is created and added to the order bag. If no production or structural index exists, it will be created and the tag will be added to it.

When using RDDs that support multiple order bags, you must explicitly SET ORDER (or ORDSETFOCUS()) to the desired controlling order. If you do not specify a controlling order, the data file will be viewed in natural order.

If <cOrderBagName> does not exist, it is created in accordance with the RDD in the current or specified work area.

If <cOrderBagName> exists and the RDD specifies that order bags can only contain a single order, <cOrderBagName> is erased and the new order is added to the order bag and to the order list in the current or specified work area.

If <cOrderBagName> exists and the RDD specifies that order bags can contain multiple tags, <cOrderName> is created if it does not already exist; otherwise, <cOrderName> is replaced in <cOrderBagName> and the order is added to the order list in the current or specified work area.

ASCENDING or DESCENDING specifies the sequence of keyed pairs in the order. If neither clause is specified, the default is ASCENDING.

If you specify the UNIQUE clause, the resulting order will contain only unique records. Some RDDs may do this by only including record references to a key value once. Others may produce a runtime recoverable error as a non-unique key insertion is attempted.

The EVAL clause lets you specify a code block to be evaluated as each record is placed in the order. The EVERY clause lets you modify how often <bBlock> is called. Instead of evaluation as each record is placed in the order, evaluation only occurs as every <nInterval> records are placed in the order.

The INDEX command accepts certain clauses that let the user create conditional and partial orders. Some orders are intended to be maintained across the application, others are considered “temporary” orders.

The FOR clause provides the only order scoping that is permanent and can be maintained across the life of the application. The string passed as the FOR condition is stored within the order for later use in maintaining the order. Though only accessing part of a database, orders created using this clause exist as long as the database is active. The FOR clause lets you create maintainable scoped orders.

The WHILE, NEXT, REST and RECORD clauses process data from the current position of the database cursor in the default or specified work area. If you specify these clauses, the order list remains open and the active order is used to organize the database while it is being created. These clauses let you create temporary (non-maintainable) orders. Orders created using these clauses contain records in which <lCondition> is true (.T.) at the location of the record pointer.

Notes

RDD support: Not all RDDs support all aspects of the INDEX command. See the “Replaceable Database Driver Architecture” chapter in the Drivers Guide for details on a particular RDD.

Although both the TAG and the TO clauses are optional, you must specify at least one of them.

Examples

      .  The following example creates a simple order (index) based on
         one field (Acct):
         USE Customer NEW
         INDEX ON Customer->Acct TO CuAcct
      .  This example creates a conditional order (index) based on a
         FOR clause.  This index will contain only records whose field
         TransDate contains a date greater than or equal to January 1, 1995:
         USE Invoice NEW
         INDEX ON Invoice->TransDate      ;
            TO InDate      ;
            FOR ( Invoice->TransDate >= CTOD( "01/01/95" ) )
      .  This example creates an order in a multiple-order bag (i.e., a
         tag in an index that can support multiple tags in an index file):
         USE Customer NEW
         INDEX ON Customer->Acct TAG CuAcct TO Customer
      .  The following example creates an order that calls a routine,
         MyMeter, during its creation:
         #define MTR_INCREMENT   10
         USE Customer NEW
         INDEX ON Customer->Acct TO CuAcct EVAL ;
               {|| MYMETER() } EVERY MTR_INCREMENT
         FUNCTION MYMETER()
            STATIC nRecsDone := 0
            nRecsDone := += MTR_INCREMENT
            ? ( nRecsDone/LASTREC() ) * 100
            RETURN (.T.)

Seealso

CLOSE, DBCREATEIND(), DBORDERINFO(), DBREINDEX()

C5 Index Commands and Functions

Posted on by vivaclipper

Index Commands and Functions

Commands :

DELETE TAG :

Delete a Tag

DELETE TAG <cOrderName> [IN <xcOrderBagName>]
    [, <cOrderName> [IN xcOrderBagName] list>]

INDEX ON … :

Create an index file

INDEX ON <expKey>
    [TAG <cOrderName>]
    TO <xcOrderBagName>
    [FOR <lCondition>] [ALL]
    [WHILE <lCondition>]
    [NEXT <nNumber>]
    [RECORD <nRecord>]
    [REST]
    [EVAL <bBlock>
    [EVERY <nInterval>]
    [UNIQUE]
    [ASCENDING|DESCENDING]

REINDEX :

Rebuild open indexes in the current workarea

REINDEX
    [EVAL <lCondition>]
    [EVERY <nRecords>]]

SET INDEX : 

Open index file(s) in the current work area

SET INDEX TO [<xcIndex list>]

SET ORDER : 

Set a new controlling index

SET ORDER TO [<nOrder> | [TAG <cOrderName>]
    [IN <xcOrderBagName>]]>

SET UNIQUE* : 

Toggle the inclusion of nonunique keys into an index

SET UNIQUE on | OFF | <xlToggle>

Functions :

DBCLEARINDEX() :

Close all indexes for the current work area

DBCLEARINDEX() --> NIL

DBCREATEINDEX() :

Create an index file

DBCREATEINDEX( <cIndexName>, <cKeyExpr>,
    <bKeyExpr>, 
    [<lUnique>] ) --> NIL

DBREINDEX() : 

Recreate all active indexes for the current work area

DBREINDEX() --> NIL

DBSEEK() : 

Move to the record having the specified key value

DBSEEK( <expKey>, [<lSoftSeek>] ) --> lFound

DBSETINDEX() : 

Open an index for the current work area

 DBSETINDEX( <cIndexName> ) --> NIL
 DBSETINDEX( <cOrderBagName> ) --> NIL

DBSETORDER() : 

Set the controlling order for the current work area

DBSETORDER( <nOrderNum> ) --> NIL

DESCEND() : 

Return a descending index key value

DESCEND( <exp> ) --> ValueInverted

FOUND() : 

Determine if the previous search operation succeeded

FOUND() --> lSuccess

INDEXEXT() : 

Return the default index extension

INDEXEXT() --> cExtension

INDEXKEY() : 

Return the key expression of a specified index

INDEXKEY( <nOrder> ) --> cKeyExp

INDEXORD() : 

Return the order position of the controlling index

INDEXORD() --> nOrder

ORDBAGEXT() :

Return the default Order Bag RDD extension

ORDBAGEXT() --> cBagExt

ORDBAGNAME() :

Return the Order Bag name of a specific Order

ORDBAGNAME(<nOrder> | <cOrderName>) --> cOrderBagName

ORDCOND()

Specify conditions for ordering

ORDCOND([FOR <lCondition>]
             [ALL] [WHILE <;lCondition>]
             [EVAL <bBlock> [EVERY <nInterval>]]
             [RECORD <nRecord>] [NEXT <nNumber>]
             [REST] [DESCENDING])

ORDCONDSET()

Set the condition and scope for an order

     ORDCONDSET([<cForCondition>],
        [<bForCondition>],
        [<lAll>],
        [<bWhileCondition>],
        [<bEval>],
        [<nInterval>],
        [<nStart>],
        [<nNext>],
        [<nRecord>],
        [<lRest>],
        [<lDescend>],
        [<lAdditive>],
        [<lCurrent>],
        [<lCustom>],
        [<lNoOptimize>]) --> lSuccess

ORDCREATE():

Create an Order in an Order Bag

ORDCREATE(<cOrderBagName>,[<cOrderName>], <cExpKey>,

    [<bExpKey>], [<lUnique>]) --> NIL

ORDDESCEND()

Return and optionally change the descending flag of an order

ORDDESCEND([<cOrder> | <nPosition>],[<cIndexFile>],
            [<lNewDescend>]) --> lCurrentDescend

ORDDESTROY() :

Remove a specified Order from an Order Bag

ORDDESTROY(<cOrderName> [, <cOrderBagName> ]) --> NIL

ORDFOR() :

Return the FOR expression of an Order

ORDFOR(<cOrderName> | <nOrder>
    [, <cOrderBagName>]) --> cForExp

ORDISUNIQUE()

          Return the status of the unique flag for a given order

    ORDISUNIQUE([<cOrder> | <nPosition>],
        [<cIndexFile>]) --> lUnique

ORDKEY() :

Return the Key expression of an Order

ORDKEY(<cOrderName> | <nOrder>
 [, <cOrderBagName>]) --> cExpKey

ORDKEYADD()

Add a key to a custom built order

         ORDKEYADD([<cOrder> | <nPosition>],
            [<cIndexFile>],[<expKeyValue>]) --> lSuccess

 

ORDKEYCOUNT()

Return the number of keys in an order

         ORDKEYCOUNT([<cOrder> | <nPosition>],
              [<cIndexFile>]) --> nKeys

ORDKEYDEL()

Delete a key from a custom built order

        ORDKEYDEL([<cOrder> | <nPosition>],
            [<cIndexFile>],
            [<expKeyValue>]) --> lSuccess

ORDKEYGOTO()

Move to a record specified by its logical record number

ORDKEYGOTO(<nKeyNo>) --> lSuccess

ORDKEYNO()

 Get the logical record number of the current record

         ORDKEYNO([<cOrder> | <nPosition>],
             [<cIndexFile>]) --> nKeyNo

 ORDKEYVAL()

Get key value of the current record from controlling order

ORDKEYVAL() --> xKeyValue

 

ORDLISTADD() :

Add Order Bag contents or single Order to the Order List

ORDLISTADD(<cOrderBagName>
    [, <cOrderName>]) --> NIL

ORDLISTCLEAR() :

Clear the current Order List

ORDLISTCLEAR() --> NIL

ORDLISTREBUILD() :

Rebuild all Orders in the Order List of the current work area

ORDLISTREBUILD() --> NIL

ORDNAME() :

Return the name of an Order in the work area

ORDNAME(<nOrder>[,<cOrderBagName>])
    --> cOrderName

ORDNUMBER() :

Return the position of an Order in the current Order List

ORDNUMBER(<cOrderName>[, <cOrderBagName>]) --> nOrderNo

 ORDSCOPE()

Set or clear the boundaries for scoping key values

ORDSCOPE(<nScope>, [<expNewValue>]) --> uCurrentValue

ORDSETFOCUS() :

Set focus to an Order in an Order List

ORDSETFOCUS([<cOrderName> | <nOrder>]
    [,<cOrderBagName>]) --> cPrevOrderNameInFocus

ORDSETRELAT()

Relate a specified work area to the current work area

    ORDSETRELATION(<nArea> | <cAlias>,<bKey>, [<cKey>])

             --> NIL

ORDSKIPUNIQUE()

Move record pointer to the next or previous unique key

ORDSKIPUNIQUE([<nDirection>]) –> lSuccess