Click above to get retro games delivered to your door ever month!

INDEX                Create an index
------------------------------------------------------------------------------

Synopsis

    INDEX ON <key>
          TO <cBag> | TAG <cTag> | TAG <cTag> TO <cBag>
          [FOR <for>]
          [WHILE <while>]
          [USECURRENT]
          [EVAL <eval> [ EVERY <nEvery> ]]
          [UNIQUE]
          [ADDITIVE]
          [DESCENDING]
          [CUSTOM]
          [NOOPTIMIZE]

Arguments

    <key> is the key expression on which to create the index.  The maximum
    length of the <key> + <for> expression is 510 characters.  The key
    length (length of what the key expression evaluates to) must be 240
    characters or less.

    TO <cBag> _or_ TAG <cTag> _or_ TAG <cTag> TO <cBag> may be used.

        TO <cBag> (no TAG clause) causes any previously existing index with
        that name to be _overwritten_.

        TAG <cTag> (no TO clause) causes a TAG to be added to the structural
        index file for the database.
        
        TAG <cTag> TO <cBag> causes the index file <cBag> to be opened if
        not already open (it is created if necessary), and the tag <cTag>
        is added to the index file.

        The maximum length of a TAG is 10 characters.  Any trailing blanks
        in the tag name will be removed and it will be converted to upper
        case.

    If FOR <for> is specified, only keys for records meeting the specified
    condition will be inserted into the index.  This is stored as part of
    the index, so if a record did meet a condition, and no longer does, the
    key will be removed from the index.  Conversely, if a record did not
    meet the condition, but now does, the key will be added to the index.
    The maximum length of the <for> + <key> expressions is 510 characters.

    If WHILE <while> is specified, the indexing process will start with the
    current record, and will continue as long as the while condition is met
    (_and_ records are within the current index scope).  This provides a
    means to rapidly create temporary indexes in some conditions (just like
    you might use a SEEK/WHILE in some situations).

    If USECURRENT is specified, only records in the current index (_and_
    within the current index scope) will be indexed.  This is useful when
    you have already created a conditional index and want to reorder the
    records which meet that condition, and/or to further restrict the
    records meeting a condition.

    If EVAL <eval> is specified, the expression represented by <eval> will
    be evaluated for each record accessed.  Indexing will only continue so
    long as the expression returns .T.  If the expression returns .F., then
    index creation will stop.  This can be used to display status messages
    as the index creation proceeds (the recno() function can be referenced
    to determine % through the index).

    If EVERY <nEvery> is also specified, the <eval> will be evaluated every
    <nEvery>'th record (instead of every record).  The <eval> will be
    evaluated as follows: before the first key is generated, then every
    <nEvery>'th record, and finally after the last key is is generated. 
    This logic insures the status meters can show 100% complete.

    You should use an EVERY <nEvery> clause if you're using the EVAL clause;
    EVAL'ing for every record is usually unnecessary and can greatly slow
    down index creation, especially if any screen display is done.

    If UNIQUE is specified, the index will only include UNIQUE keys.

    If ADDITIVE is specified, any open indexes will remain open.  By default
    they are closed.  (Note that the structural index is never closed).

    If DESCENDING is specified, the index will be viewed in descending order
    by default (i.e., the highest key first).  Note that any index can be
    dynamically switched from ascending to descending order using the SET
    DESCENDING command.

    If CUSTOM is specified, an empty Custom Built Index will be created.  A
    Custom Built Index gives you complete control over index maintenance.
    The system does not automatically add and delete keys from a custom
    index.  Instead, you explicitly add and delete keys from the index using
    the cmxKeyAdd() and cmxKeyDel() functions.  This capability is excellent
    for generating pick lists of specific records and other custom
    applications.

    If NOOPTIMIZE is specified and you are using ClipMore, then the FOR
    condition will not be optimized.  Comix alone never optimizes the
    FOR condition, but ClipMore does optimize the FOR condition by
    default.  Specifying NOOPTIMIZE allows you to override this
    behavior.

Description

    INDEX allows you to create an index, exactly like the standard Clipper
    INDEX command.

    The enhancements provided are described in the Arguments section above.

Example

        //  Create an index (overwrite any previously existing LAST.CDX)
    use demo
    index on last to last

Example

        //  Add a tag to the structural index
    use demo
    index on last + first tag lastFirst


Example

        //  Add the tag first to the index file LAST.CDX
    use demo
    index on first tag first to last

Example

        //  Create an index of only unique last names
    use demo
    index on last to last unique

Example

        //  Create an index in age order of only people who have a
        //  last name that starts with 'J'
    use demo
    index on age tag ageJ for last = 'J'

Example

        /*  This example quickly creates an index of all the people whose
            last name starts with J in age order.  (It assumes that a
            tag on last has already been created).
         */
    use demo                    && Brings demo.cdx into use automatically
    set order to tag last
    seek 'J'
    index on age to ageJ while last = 'J'

Example

        /*  This example is similar to the above, but highlights an
            important difference between WHILE and FOR:
         */

    use demo            && Brings demo.cdx into use automatically
    set order to tag last
    index on age to ageJ while last = 'J' for last = 'J'

    Note the inclusion of the FOR clause.  The difference is that this index
    will maintain the condition (i.e., only people with a last name of 'J'
    will ever exist in this index).  The index created with only the WHILE
    condition will add anybody to the index (since there is no FOR
    condition).

Example

        /*  This example shows the USECURRENT clause.  Suppose that we have
            the index ageJ available (as defined above):
         */

    use demo
    set index to ageJ
    index on lastcall to lastcall usecurrent

    This will create an index on lastcall, but only for those people
    contained in the original ageJ index (i.e., people with a last name
    starting with 'J').  Please note, however, that the FOR condition is not
    transferred from the ageJ index.  Thus, if you want that condition, you
    will have to give an explicit FOR condition.

Example

        /*  This example creates a new index while keeping all indexes
            open:
         */

    use demo
    index on first to first
    index on last to last additive

    In this case, the index first stays open while last is being created. If
    the additive clause had not been specified, the index first would have
    been closed.  Please note that the structural index file is never
    closed.

Example

    /*  This example prints out a dot as each record is accessed during
        index creation, and returns true as long as the user hasn't pressed
        ESC.  If the user presses ESC, the function will return false
        causing index creation to stop
     */
    use demo
    index on last to last eval ourUDF()

    function ourUDF()
        ?? '.'
    return (inkey() <> 27)

    First, this example will run slowly (that's a whole lot of dots to print
    out if the database is large).  Second, in the event the user presses
    ESC, the index key generation stops;  however, the index is created with
    whatever keys have been generated up to that time.  It is the
    programmer's responsibility to discard the index (or do whatever you
    want to with it).

Example

        //  This example runs more efficiently by using the EVERY clause.
    use demo
    clear
    index on last to last eval ourUDF() every 1000

    function ourUDF()
    @ 0, 0 say str(recno()) + " Records Processed"
    @ 1, 0 say str(cmxKeysIncluded()) + " Keys Generated"
    return .t.

    You may mix and match any or all of the above clauses.

See Also

    XINDEX, DELETE TAG, SET ORDER TO, cmxKeysIncluded()