Difference between revisions of "Using Navigational Data Access Commands in Recital"

Revision as of 10:11, 13 January 2010

Using Navigational Data Access Commands in Recital

Why use Navigational Data Access Commands

Creating a Recital Database

The create database command is used to create a new database. This will create a sub-directory in the DB_DATADIR directory and a database catalog with the same name as the database specified.

create database [if not exists] <database>

A database, its tables and other associated files can be physically deleted using the drop database command.

drop database [if exists] <database>

Example

create database test
drop database test

Opening a Recital Database

To open a database and set it as the default database for any subsequent operations, use the open database command.

open database <database> [EXCLUSIVE | SHARED] [NOUPDATE] [VALIDATE]

The open database command triggers the DBC_OPENDATA database event. If a dbc_opendata.prg program file exists in the database's directory, this will be run. If the dbc_opendata.prg program returns .F. (False), the open database operation will be abandoned.

Databases can also have an associated procedure library that is activated automatically when the database is opened. The procedure library must follow the naming convention dbc_<database>_library.prg and must exist in the database's directory. When the database is closed, the procedure library is also closed.

open database southwind

Creating a Recital Table

Tables can be created using the SQL create table statement. For full details, please see Using Recital SQL. The following alternatives are also supported.

The copy command can be used to copy all or part of an active table to a new table. The records to be copied can be restricted using while and for conditions.

copy to <filename> [<scope>] [fields <field list>] [while <condition>] [for <condition>] 

Example

use example order account_no
copy to new;
  rest;
  fields account_no, last_name;
  while account_no < "00050";
  for state = "MA"

The copy structure command allows the entire structure or selected fields from an existing active table to be copied to a new table.

copy structure to <tablename> [fields <field list>] 

Example

use example
copy structure to new;
  fields account_no, last_name

The copy structure extended command copies out all or part of a table's structure as the records of a new table. These records can then be modified, deleted or new records added as with any Recital table. The create from command uses such records as the source structure for a new table.

copy structure extended to <structure-tablename> [fields <field list>]

create <tablename> from <structure-tablename> 

Example

use example
copy structure extended to stru_example;
  fields account_no, last_name
// modify records in stru_example table as required
create new from stru_example

The create worksurface in the Recital Workbench provides a full screen form for creating new Recital tables.

create <tablename> 

Example

create new

Creating Recital Indexes

Indexes provide the means to order the way in which records are viewed in a table without actually physically rearranging them and high performance searching. Indexes can be built during the creation of the table with SQL create and create or after table creation using the index command.

index on <key as expression> tag <tagname as character> 
[of <indexfile as character>] 
[for <condition as logical>] 
[unique] [descending] 

Examples

use orders
index on orderid tag orderid unique
index on orderid+dtos(orderdate) tag order_date
index on customerid+str(orderid) tag cust_order for empty(shippeddate)
index on dtos(orderdate) tag latest descending

The reindex command can be used to rebuild indexes in the active or all open tables.

reindex [all] [unique]

Example

use orders
reindex

Opening a Recital Table

The use command opens a Recital table. A Recital table is opened in a cursor (also know as a workarea). At any given time, a particular cursor is active and selected and contains the context for the table that has been opened in that cursor: the current record pointer, the current record, the screen format file, the index files, the filter condition, and the relationships to other tables. The cursor can be selected (by number or letter with the select command) prior to opening the table or specified on the use command itself. Cursors start from 1 or a; specifying cursor 0 selects the lowest available cursor.

select <cursor as numeric | cursor as character>

The use command can also include the open mode, the index files to open and master index to set, the alias name to give the table and the encryption key in the case of encrypted tables.

use [<table> 

[alias <alias as character>]

[again] [automem] [exclusive] [nodbx] [noupdate]

[encryption <key as character>]

[in <cursor as numeric | cursor as character>]

[index <index as list>]

[order <ndxfile> | [tag] <tagname> [of <dbxfile>]]]

Example

use example exclusive in 0 order tag account_no

The use command with no table specified, closes an open table in the selected cursor.

Navigating a Recital Table

Recital tables can be navigated via their physical record order or via the master index.

The goto command is used to position the record pointer on a specified absolute record number.

go[to] <record as numeric> [in <cursor as numeric> | <alias as character>]

The goto() function can be used to reposition the record pointer and check whether the move was successful.

logical = goto(<cursor as numeric> | <alias as character>, <record as numeric>)

Relative navigation is based on the master index order if one is active. The top of the table is the first record in the index, not necessarily record 1.

go[to] bottom | top [in <cursor as numeric> | <alias as character>]

Examples

use example
goto 10
// First physical record
go top
// Last physical record
go bottom
set order tag account_no
// First record in index
go top
// Last record in index
go bottom

Similarly, the skip command will move up and down the records in master index order, but in physical record order if no master index is set.

skip <records as numeric> [in <cursor as numeric> | <alias as character>]

Examples

use example
goto 10
// Skip to record 12
skip 2
// Skip back to record 10
skip -2
set order tag account_no
// First record in index
go top
// Tenth record in index
skip 10

The seek command is used to move to a specific record in the index based on the index key.

seek <key>

Examples

use example order account_no
// Character key
seek "00010"
if found()
    // commands if key found in index
endif
use orders order orderid
// Numeric key
seek 11058

Adding Records to a Recital Table

The append blank command adds one or more new records to a Recital table. All fields in the new record(s) will be empty unless they have a default value set in the table's dictionary.

append blank [<records as numeric>]

Example

use example
// Add 10 blank records
append blank 10

The append from command allows data from a range of sources to be added to a Recital table as new records.

append from <filename as character>
[encryption <key as character>]
[while <condition as logical>] [for <condition as logical>]
[[type] sdf | fixed | delimited | delimited with blank | delimited wih <delimiter as character> | csv] 

Examples

use example
// Append records from another table (temp.dbf)
append from temp for state = "MA"
// Append records from a text file with '|' delimiter (piped.txt)
append from piped type delimited with |
// Append from a Microsoft Excel CSV file (customers.csv)
append from customers csv

The generate command is useful for testing purposes. It adds the specified number of records to a Recital table, filling the fields with data.

generate <records as numeric> 

Example

// Open test table and add 2000 records
use test
generate 2000

The append worksurface in the Recital Workbench provides a full screen form for adding records to the active Recital table.

append 

Example

use example
append

Updating Records in a Recital Table

The replace command updates fields in the active table, or any open table if the alias and alias pointer (.) are used.

replace [<scope>] <fieldname> with <expression> | <memo-fieldname> with <expression> [additive] [, ...]
[while <condition as logical>] [for <condition as logical>]
[reindex] 

Example

use products
replace unitsinstock with unitsinstock + unitsonorder,;
  unitsonorder = 0;
  for supplierid = 1

Replace blank causes all fields in the current record to be set to blank. Default settings defined in the Data Dictionary apply to the replaced record.

replace blank

Example

use products order productid
seek 73
if found()
   replace blank
endif

The gather command updates the current record in the current table based on the contents of an array or a series of memory variables.

gather from <array> | memvar [fields <field list>] [memo] 

Examples

use products order productid
seek 73
if found()
   // Copy contents to a new record
   scatter to temp
   append blank
   gather from temp
endif
// or
seek 73
if found()
   // Copy specified contents to a new record
   scatter memvar fields productid, quantitype
   append blank
   gather memvar
endif

Relating Recital Tables

The set relation command is used to define relationships between Recital tables and enable or disable such relationships.

set relation on | off
set relation to [<key as expression> into <alias as character | cursor as number>[, ... [additive]]

Tables to be related must be open and the parent table selected. The child table or tables should be indexed on the key used to relate the tables and this foreign key should be the master index order.

open database southwind
use order_details order orderid in 0
use orders order orderid in 0
set relation to orderid into order_details

For one-to-many relationships, the set skip command can be used in combination with the display and list commands to automatically scan for all detail records when a relationship has been defined.

set skip on | off
set skip to [<alias as character | cursor as number>, ...]

open database southwind
use order_details order orderid in 0
use orders order orderid in 0
set relation to orderid into order_details

Selecting Records from a Recital Table

The display and list commands output records from the active and related tables. The default scope for display is the current record and that for list is all records in the current table.

display [<scope>] [<expression-list as list> | [fields] <fieldlist as list>]
[while <condition as logical>] [for <condition as logical>]
[to file <filename> | print]

list [<scope>] [<expression-list as list> | [fields] <fieldlist as list>]
[while <condition as logical>] [for <condition as logical>]
[to file <filename> | print]

Example

// List order table order number and associated order_details table records
open database southwind
use order_details order orderid in 0
use orders order orderid in 0
set relation to orderid into order_details
set skip on
set skip to order_details
list orders.orderid,;
  order_details.unitprice, order_details.quantity,;
  order_details.discount;
  for orders.employeeid = 5;
  and orders.shipvia = 3

The scan...endscan command executes a command block repeatedly for a specified selection of records while an optionally specified condition is true, or until an exit is encountered.

scan [<scope>] [while <condition as logical>] [for <condition as logical>]
     [exit]
     [loop]
endscan

Example

// List order value totals for selected orders
open database southwind
use order_details order orderid in 0
use orders order orderid in 0
set relation to orderid into order_details
scan for orders.employeeid = 5 and orders.shipvia = 3
    select order_details
    echo sumvalues(unitprice * quantity * (1-discount))
    echo "\n"
    select orders
endscan

• AVERAGE
• COUNT
• SUM
• TOTAL

Deleting Records in a Recital Table

The deletion of records in a Recital table is a two stage process. The delete command is used to mark a record for deletion. At this stage, the marked record is hidden if set deleted is on. With set deleted set to off, the record is still accessible as normal. Record lists (list and display) will show the deletion marker * next to the record number and the deleted() function will return .T. (true). A record marked for deletion can have the mark removed using the recall command.

delete [<scope>] [for | while <condition as logical>] 

recall [<scope>] [for | while <condition as logical>] 

set deleted on | off 

set deleted off
use example
goto 10
delete
echo deleted() // .T.
recall
echo deleted() // .F.

To physically remove records marked for deletion, the pack command must be issued on the table. The table must be open for exclusive use.

use example exclusive
pack

The zap command can be used to physically delete all the records from a table immediately. The table must be open for exclusive use.

use example exclusive
zap

Obtain Information about a Recital Table

Table name, table status, cursor and alias information can be determined as follows:

• dbf() - return the name of the table in the current or specified cursor or with the specified alias

character = dbf([cursor as numeric | alias as character])

• inuse() - check if a specified table is open

logical = inuse(alias as character)

• used() - check if a table is open in the current or specified cursor or with the specified alias

logical = used([cursor as numeric | alias as character])

• alias() - return the table alias name for the current or specified cursor

character = alias([cursor as numeric])

• select() - return the cursor number for the current cursor or the specified alias

numeric = select([alias as character])

The current record pointer position is obtained as follows:

• recno() - return the record number of the active or specified table

numeric = recno([cursor as numeric | alias as character])

• bof() - evaluate if the record pointer is at the beginning of file marker

logical = bof([cursor as numeric | alias as character])

• eof() - evaluate if the record pointer is at the end of file marker

logical = eof([cursor as numeric | alias as character])

Recital supports a wide range of display and list commands giving information about Recital tables and their associated files and the environment generally.

• display / list - list records from the active and related tables

list [<scope>] [<expression-list as list> | [fields] <fieldlist as list>] [while <condition as logical>] [for <condition as logical>] [to file <filename> | print]

• display / list structure - list the structure of the active table

list structure [to file <filename> | print]

• display / list index - list details of the open indexes of the active table

list indexes [to file <filename> | print]

Further information about fields can be obtained with these functions:

• field() / fieldname() - return name of specified field in the current or specified cursor or with the specified alias

character = field[name](field as number[, cursor as numeric | alias as character]])

• fldlist() - return a comma-separated list of field names in the current or specified cursor or with the specified alias

character = fieldlist([cursor as numeric | alias as character])

Further information about indexes can be obtained with these functions:

• cdx() - return the index filename for the active or specified index file

character = cdx([tag as numeric[, cursor as numeric | alias as character]])

• key() - return the index key expression for the master or specified tag

character = key([tag as numeric])

• tag() - return the index tag name for the master or specified tag

character = tag([indexfile as character,] [tag as numeric[, cursor as numeric | alias as character]])

• tagno() - return the index tag number for the specified tag

numeric = tag(tag as character[, indexfile as character[, cursor as numeric | alias as character]])

• tagcount() - return the number of tags in the active or specified index file

numeric = tag([indexfile as character[, cursor as numeric | alias as character]])