dBASE Index Maintenance

PIN file opcode [pinno|loadfile|listfile]

The utility program PIN is provided to allow you to maintain any standalone .NDX files that the CopiaFacts system uses.

Please note that releases of PIN with version numbers 7.2xx and 7.300 to 7.304 had a serious bug (but fortunately a rare one) affecting key deletions. We recommend upgrading immediately to PIN version 7.305 or later if you still have an earlier release.

Migrating from PIN version 7.2xx to PIN version 7.3xx or later

The original use of the PIN program ('PIN' in the sense of Personal Identification Number) was to manage the .NDX index files used by a number of CopiaFacts commands to validate a PIN or password. The PIN utility only operates on the .NDX file and not on any associated dBASE .DBF files, so should not be used on other than standalone NDX files.

PIN is now more often used as a simple means of maintaining DNS.NDX and DNS_MAIL.NDX 'do not send' files. For telephone numbers, a numeric key index is normally used but a character (alphanumeric) index is also supported. The lower-case opcode values should be used for e-mail addresses, of course with alphanumeric key files. Matching alphanumeric keys for do-not-send look-up is always case-sensitive, so the use of lower-case letters when loading and maintaining the index with PIN ensures that the case is always consistent.

In the North America Numbering Plan (NANP) areas (as determined from $country_code), an NDX file which is created (with the C command described below) or re-indexed (with the I command described below) is marked and maintained with no ten-digit numbers: all ten-digit numbers have a leading 1 added.  NDX files created before CopiaFacts release 8.2 and not subsequently re-indexed may contain either 10 or 11 digit NANP numbers, but look-up operations other than in PIN and DNSUPD will find either format.  For more information, see the Telephone Number Formats topic.

PIN is designed to interact with the COPIAFACTS engine to prevent simultaneous look-up and maintenance operations. However if you have a lot of work to do with bulk operations in PIN, it is recommended that this is done with a copy of the NDX file or while COPIAFACTS nodes are not using the file.

PIN also supports a positive integer data value which can be associated with the index entry. This value, shown as m in the descriptions below, can be used for example to record the source of an entry in the file.  If no value is provided, the data value is set to 1 in all index entries.

PIN supports Unicode index entries for alphanumeric indices. Since e-mail addresses are normally limited to ASCII, the use of this capability is not normally recommended.  However you may need to load up index files from lists encoded as Unicode or UTF-8, and provided that the data contains only ASCII characters this can be done without creating a Unicode index: if the list for the F opcode contains a byte-order-mark it will be accepted and the content will be converted to system default encoding for use in a non-Unicode index file.

Command-Line Arguments:

file the full pathname (optionally without .NDX extension) of the .NDX file to be created or processed. Only .NDX extensions will be processed. You may use the standard system variables such as @FFBASE and @FFLOG at the start of an NDX file pathname (but not for the redirection pathname, if used).
opcode the operation to be performed, as follows:
C Create the file with a numeric index. This command should normally be used for phone numbers because the file is a bit smaller and the look-up is a bit quicker.
CnCreate the file with an alphanumeric index. Choose a value of n which is long enough for international phone numbers, if used.  For e-mail a value of 64 is usually long enough. The maximum value is 100.
CnUCreate the file with an alphanumeric index, with entries encoded UTF-8. Choose a value of n which is long enough for international phone numbers, if used.  For e-mail a value of 64 is usually long enough. The maximum value is 100.
Am,amAdd a single key.  Duplicate keys will be rejected with a special exit code, so that you can report if a key was already present in the file. If the lower-case opcode is used, the value entered for the key will be converted to lower case. The key should not be surrounded by quotes or double-quotes. Keys longer than the alphanumeric key length are truncated.  The value m, if present, is used to set a data value for the key. An addition is rejected if the key is present and the value is different from that specified by the m parameter or implied (1) by its absence. Otherwise this operation will update the data value of an existing entry.
Fm,fmAdd each key in the named file to the index. Double-quotes surrounding the key are removed. If the lower-case opcode is used, each key will be converted to lower case before it is added. Keys longer than the alphanumeric key length are truncated.  Processing continues if a single key is duplicated or invalid. The quoted or unquoted key value in the list may be followed by a tab character and a data value to be applied to that key entry, or to update the data value if different. The value m, if present and positive, is used to force a data value for all the added keys; if the value m is negative its positive value will be applied only to those entries in the list which have no data value.  If the file is Unicode or UTF-8 encoded, data loss may occur when keys are added to an alphanumeric index created without the 'U' suffix.
D,dDelete a key. If the lower-case opcode is used, the value entered for the key will be converted to lower case (and will therefore not succeed in deleting any key containing upper-case characters). Deletions are now done by internally setting the data value of the entry to -1, so after a lot of individual deletions you may wish to re-index using the I command.
B,bBulk delete keys from a list. If the lower-case opcode is used, each key will be converted to lower case (and will therefore not succeed in deleting any key containing upper-case characters).  Deletions are now done by internally setting the data value of the entry to -1, so after a lot of deletions you may wish to re-index using the I command.  Alternatively, consider using the J command to delete using re-index.
Im,imRe-Index. The keys are saved internally to a list, the index is cleared, and the keys are then re-loaded. A backup is automatically made before the operation starts, using file extensions .001, .002 etc. Because all NDX files are maintained with unique keys, files created with non-unique keys using earlier releases of PIN, or with other applications, will become unique and the number of duplicates removed will be reported at the end of the operation. Using a lower-case 'i' operation code will also convert alphanumeric keys to lower case. If the country code specified on the $country_code command is 1, keys will be added back with 10-digit numbers converted to 11-digits by adding a leading '1', and the NDX file will be marked to indicate that this has been done. The index file is held locked during the re-index operation, so that access from the COPIAFACTS engine is prevented while the index may be empty or incomplete. The value m, if present and positive, is used to force a data value for all the keys; if the value m is negative its positive value will be applied only to those entries in the list which do not have an explicit (greater than 1) data value. This operation code can be followed by a second code letter to perform a conditional re-index as described below.
J, jBulk Delete using Re-Index. For large bulk deletions it is more efficient to delete using a re-index operation, which will also reduce the index size. The J command will save all active keys, remove those in the supplied list, and then re-load the remaining keys.
E,eProcess a list of keys from the named file to standard output, eliminating from the list any keys that are matched in the index. No changes are made to the index. If the lower-case opcode is used, the values in the list will be converted to lower case before use. The key is extracted from the list line up to the first white space, but the entire line is copied to the output for lines that are not eliminated. Double-quotes surrounding the whole line, or double-quotes surrounding the extracted key in the list are removed if present before the line is tested.
S,s,R,rThese opcode values report whether the key is matched in the index or not.  The S codes report status by means of the errorlevel (exit code) returned by the PIN program. The 'R' codes also report by means of an output message on the console. If the lower-case opcode is used, the value entered for the key will be converted to lower case before it is tested.
L,lThe entire contents of the index file are listed to standard output, in ascending key sequence. No changes are made to the index. If the lower-case opcode is used, the values will be converted to lower case before output. Unlike earlier releases of PIN, only the keys are output, without double-quotes or padding. To create a list file in Unicode encoding, use the listfile parameter instead of redirecting output to a file.
M,mThe entire contents of the index file are listed to standard output, in ascending key sequence. If the data value of an item is greater  than 1, the value is output following the index entry and a tab character.  No changes are made to the index. If the lower-case opcode is used, the values will be converted to lower case before output. Unlike earlier releases of PIN, only the keys are output, without double-quotes or padding. To create a list file in Unicode encoding, use the listfile parameter instead of redirecting output to a file.
Q,qThe entire contents of the index file are listed to standard output, in ascending key sequence. No changes are made to the index. If the lower-case opcode is used, the values will be converted to lower case before output. To match earlier releases of PIN, alphanumeric keys are padded on the right with blanks to the full key length, and the output is enclosed in double-quotes. To create a list file in Unicode encoding, use the listfile parameter instead of redirecting output to a file.
U,uThe entire contents of the index file are listed to standard output, in ascending key sequence, with any eleven-digit numeric keys starting with a '1' modified by deletion of the first digit. No changes are made to the index. If the lower-case opcode is used, values will be converted to lower case before output. Keys are output without double-quotes or padding.  To create a list file in Unicode encoding, use the listfile parameter instead of redirecting output to a file.
pinno the number or string to be Added or Deleted. Values longer than the defined key size are truncated on the right.
loadfile a file containing keys to be loaded (or deleted), one per line. Standard Copia variables such as @FFBASE can be used. The special filename "-" (without  the quotes) indicates that the file is to be read from 'standard input', which allows a command shell pipe to be used to supply the keys to be loaded.
listfilea file named as output, without a redirection symbol, on the L, M, Q and U operation codes will be written with Unicode encoding.

Conditional Re-Index

In special cases, when upgrading a system which automates the use of PIN, it may be necessary to convert NDX file to unique-key format while the index is in use.  To do this, change an existing "PIN dnsfile a" or "PIN dnsfile f" command in your batch file to read "PIN dnsfile ia" or "PIN dnsfile if" respectively.  This special command syntax will rebuild the index, if and only if necessary, then continue to perform the operation specified by the second letter. A data value m may follow the a or f command letter.

The re-index will be deemed necessary if the existing index file header indicates that duplicate keys are permitted in the file. The re-index operation sets the flag that indicates that keys must be unique. This syntax may be used with any combination of an upper or lower case i combined with one of upper- or lower-case a or f.

Exit Codes:

The S, s, R and r operation codes cause the program to return an errorlevel which can be tested in a batch file.  The value is 0 if the key is matched or -5, -6 or -7 if the key is not matched; any other value (listed in PIN Errors) indicates that an error occurred in the look-up.

Creation of an index or addition or deletion of a single key value returns 0 in the errorlevel on success and a non-zero result on error.

List addition returns -1 if duplicates were encountered and list deletion returns -1 if missing items were encountered; otherwise 0 is returned if the whole list was successfully processed or another non-zero value if an error occurred.

All operations return -2 if the index cannot be locked after the specified number of retries.

All operations which modify the file fail and return -3 if the index file permits duplicate keys. Re-indexing the file will convert it to a unique key file.

On a re-index, if an error occurs during the backup or list phase, the reset of the index and the reload are skipped.

       Trace Information   Additional trace information on the operation of PIN can be obtained by specifying this program name under File/Applications in FFTRACE.

Examples:

Create a numeric index, for example for a do-not-send phone number list:

PIN DNS C        :Create DNS.NDX

Create an alphanumeric index, for example for a do-not-send email address list:

PIN DNS_MAIL C40 

PIN @FFLOG\DNS_MAIL C64

Add and delete values:

PIN DNS A 1234567890

PIN DNS D 6303866900

PIN DNS_DOMN a7 copia.com

PIN DNS_MAIL d steve@copia.com

List index values:

PIN DNS_MAIL L >emails.lst

List index values into a Unicode file:

PIN DNS_MAIL L emailunicode.lst

Load index values:

PIN DNS F faxdns.lst

Clean a list by reference to the index:

PIN DNS E key.lst > newkeylist.lst

Report a look-up result:

c:\copia\faxfacts\log> PIN DNS_MAIL r steve@copia.com

Key steve@copia.com IS matched in file DNS_MAIL.NDX

Convert a list containing numbers with and without a leading '1', to have all NANP numbers with a '1'.

c:\copia\faxfacts\log> PIN DNS i

Added back 185626 records from 249998 (64372 duplicates)