Index [examples]

Creates an index for the specified table
CREATE INDEX
// Create an index on staff number
CREATE INDEX staff_no;
  ON staff (staff_no)
 
// Create an index on descending hire date and ascending staff number
CREATE INDEX hiredate;
  ON staff (hiredate DESC, staff_no ASC)
 
// Create an index on last name converted to lower case
CREATE INDEX lname;
  ON staff (lower(lastname))

Close index files in the current workarea
CLOSE INDEX
use patrons index events, dates, names
seek "BALLET"
list while event = "BALLET"
// Close index files in the current workarea 
close index
list for event = "BALLET"

Display index information about the current table
DISPLAY INDEXES
use example
display indexes to file ind_info

List index information about the current table
LIST INDEXES
use example
list indexes to file ind_info

Delete an index tag
DELETE TAG
use accounts exclusive
delete tag zip

Create an index for a table on a specified key
INDEX
use patrons
index on event tag event

https://www.lianja.com/doc/index.php/INDEX


Rebuild all of the active index files
REINDEX
set exclusive on
use patrons index events, names, dates
reindex

https://www.lianja.com/doc/index.php/REINDEX


Open a list of index files associated with the active table
SET INDEX
use patrons
set index to names, events    && opens up the index files
seek "smith"

https://www.lianja.com/doc/index.php/SET_INDEX


Copy single indexes to a multiple index file
COPY INDEXES
use accounts
set index to ordno, invno
copy indexes ordno, invno

https://www.lianja.com/doc/index.php/COPY_INDEXES


Restrict access to a set of records based on their index keys
SET KEY
open database southwind
use products
set order tag productid
set key to range 20, 30
list
set order tag productname
set key to "Chai"
list
set key to range "A","C"
list

https://www.lianja.com/doc/index.php/SET_KEY


Select the master index file from the active index file list
SET ORDER
use patrons index address.dbx
set order to tag zip of address

https://www.lianja.com/doc/index.php/SET_ORDER


Search for specified key in the master index and if found, position the record pointer in the table
SEEK
use patrons
index on event to events
seek "BALLET"
 
index on date to dates
seek ctod("01/01/2000")
 
index on dtos(eventdate) + "/" + event to dates
seek "20010101/BALLET"

https://www.lianja.com/doc/index.php/SEEK


Search for a key in the master index file
FIND
use patrons index events, name
m_find = "OPERA"
find &m_find
if found()
    edit
else
    dialog message "Record not found."
endif

https://www.lianja.com/doc/index.php/FIND


Specify number of buffers to use for index files
SET ICACHE
set icache to 100
use patrons index events, dates, names
 
// Another example
use accounts index acc_prefix, paid_date
set icache to reccount()
reindex

https://www.lianja.com/doc/index.php/SET_ICACHE


Determine whether duplicate keys are to be included in index files
SET UNIQUE
use patrons
set unique on
index on name to persons

https://www.lianja.com/doc/index.php/SET_UNIQUE


Rebuilds an existing index file for the specified table
ALTER INDEX
// Rebuild the index staff_no index on staff table
ALTER INDEX staff_no;
  ON staff;
  REBUILD;
  SHARED

https://www.lianja.com/doc/index.php/ALTER_INDEX


Removes an index from a table
DROP INDEX
DROP INDEX staff_no ON staff

https://www.lianja.com/doc/index.php/DROP_INDEX


Table constraint to define an index key
CREATE TABLE contact;
  (LastName char(25), FirstName char(25),;
  INDEX FullName (LastName, FirstName))

https://www.lianja.com/doc/index.php/INDEX_Table_Constraint


Table constraint to define an index key
CREATE TABLE contact;
  (LastName char(25), FirstName char(25),;
  KEY FullName (LastName, FirstName))

https://www.lianja.com/doc/index.php/KEY_Table_Constraint


Column constraint to define table’s Primary Key
CREATE TABLE newcust;
  (acc_ref char(5);
  default strzero(seqno(),5);
  PRIMARY KEY,;
  acc_name char(20))

https://www.lianja.com/doc/index.php/PRIMARY_KEY_Column_Constraint


Table constraint to define table’s Primary Key
CREATE TABLE newcust;
  (acc_ref char(5) default strzero(seqno(),5),;
  acc_name char(20))
 
ALTER TABLE newcust;
  ADD PRIMARY KEY acc_ref+acc_name TAG RefName

https://www.lianja.com/doc/index.php/PRIMARY_KEY_Table_Constraint


Column constraint to define a column as a Foreign Key for a parent table
CREATE TABLE orderitem;
  (ord_ref char(5) FOREIGN KEY,;
  item_ref char(5) PRIMARY KEY)

https://www.lianja.com/doc/index.php/FOREIGN_KEY_Column_Constraint


Table constraint to define a Foreign Key
CREATE TABLE supplier;
 (SuppId int PRIMARY KEY,;
  SuppRef char(10) UNIQUE,;
  SuppName char(40) UNIQUE)
 
CREATE TABLE purchase_order;
 (POId int PRIMARY KEY,;
  PO_SuppRef char(10),;
  POtotal num(10,2))
 
ALTER TABLE purchase_order ADD FOREIGN KEY po_suppref TAG fk_po_suppref 
ALTER TABLE purchase_order ADD FOREIGN KEY (po_suppref) TAG fk_po_suppref;
 REFERENCES supplier TAG SuppRef

https://www.lianja.com/doc/index.php/FOREIGN_KEY_Table_Constraint


Table constraint to define a candidate index
CREATE TABLE newcust;
  (acc_ref char(5) default strzero(seqno(),5),;
  acc_name char(20))
ALTER TABLE newcust;
  ADD UNIQUE acc_ref+acc_name TAG RefName
 
CREATE TABLE contact;
  (ContRef char(5), LastName char(25), FirstName char(25),;
  UNIQUE FullName (LastName, FirstName, ContRef))

https://www.lianja.com/doc/index.php/UNIQUE_Table_Constraint


Column constraint to define a candidate index for a table
CREATE TABLE newcust;
  (acc_ref char(5) default strzero(seqno(),5) UNIQUE,;
  acc_name char(20) UNIQUE)

https://www.lianja.com/doc/index.php/UNIQUE_Column_Constraint


36 character unique GUID
GUID
create table country;
 (id char(36) description "ID" default guid(),;
  countryname char(100) description "Name",;
  countrycurr char(100) description "Currency",;
  countrycap char(100) description "Capital")
create index id on country (id)
insert into country (countryname, countrycurr, countrycap);
  values ("United Kingdom","Pound sterling","London"),;
    ("United States of America","United States dollar","Washington, D.C."),;
    ("France","Euro","Paris"),;
    ("Italy","Euro","Rome"),;
    ("Germany","Euro","Berlin"),;
    ("Brazil","Brazilian real","Brasilia"),;
    ("Thailand","Thai baht","Bangkok")
select * from country

https://www.lianja.com/doc/index.php/GUID()


Next unique sequence number from the specified table
NEXTVAL
CREATE TABLE cust (acc_num INT , acc_name char(20))
INSERT INTO cust (acc_num, acc_name) VALUES (NEXTVAL, "Smith")
INSERT INTO cust (acc_name) VALUES ("Brown")
INSERT INTO cust (acc_num, acc_name) VALUES (CURRVAL+2, "Jones")
SELECT * from cust

https://www.lianja.com/doc/index.php/NEXTVAL


Description of a table’s indices and statistics
SYSINDEXINFO
SELECT * FROM sysindexinfo

https://www.lianja.com/doc/index.php/SYSINDEXINFO


Description of the primary key columns in the table
SYSPRIMARYKEYS
SELECT * FROM sysprimarykeys

https://www.lianja.com/doc/index.php/SYSPRIMARYKEYS


Specify file extension for index filenames
SET INDEXEXT
set indexext to ".ntx", ".cdx"
index on name to names
use name index names

https://www.lianja.com/doc/index.php/SET_INDEXEXT


Create a single index file from an index tag
COPY TAG
//Open up dbf with a dbx file
use customer
copy tag zip to zip.ndx

https://www.lianja.com/doc/index.php/COPY_TAG


Active multiple index filename
MDX
?mdx(1, account)
invoice.dbx

https://www.lianja.com/doc/index.php/MDX()


Active multiple index filename
CDX
?cdx(1, account)
invoice.dbx

https://www.lianja.com/doc/index.php/CDX()


FOR condition that was used to create an index tag
FOR
set view to accounting
? for(3)
acct = "CAR"

https://www.lianja.com/doc/index.php/FOR()


Test for unique indexes
UNIQUE
use accounts
index on name to name unique
? unique(0)
.T.

https://www.lianja.com/doc/index.php/UNIQUE()


Index key expression
INDEXKEY
use accounts index acc_no, date_paid
? indexkey(1)
acc_no + dtos(date_rec)
set order to 2
? indexkey(indexorder())
dtos(date_paid) + str(amo_paid,11,2)
index on lower(left(company,20)) tag company
? indexkey()
lower(left(company,20))

https://www.lianja.com/doc/index.php/INDEXKEY()


Default index extension
INDEXEXT
? indexext()
.ndx

https://www.lianja.com/doc/index.php/INDEXEXT()


Name of the currently selected master index
INDEXORDER
open database southwind
use customers
set order tag companyname_search
? indexorder()
companyname_search

https://www.lianja.com/doc/index.php/INDEXORDER()


Number of index files matching the pattern
IFILECOUNT
? ifilecount("*.ndx")
         5

https://www.lianja.com/doc/index.php/IFILECOUNT()


Determine whether an index tag was created with the DESCENDING keyword
DESCENDING
set view to accounting
?descending(3)
.T.

https://www.lianja.com/doc/index.php/DESCENDING()


Index key expression
KEY
use accounts index acc_no, date_paid
? key(1)
acc_no + dtos(date_rec)
set order to 2
? key(indexorder())
dtos(date_paid) + str(amo_paid,11,2)
index on lower(left(company,20)) tag company
? key()
lower(left(company,20))

https://www.lianja.com/doc/index.php/KEY()


Index file name
NDX
use accounts index acc_no,date_paid
? ndx(1)
acc_no.ndx
set order to 2
? ndx(indexorder())
date_paid.ndx

https://www.lianja.com/doc/index.php/NDX()


Name of the currently selected master index
ORDER
open database southwind
use customers
set order tag companyname_search
? order()
companyname_search

https://www.lianja.com/doc/index.php/ORDER()


Name of an index file or index tag
TAG
?tag(2)
LAST_NAME
tag("alternate.dbx",3)
ZIPCODE

https://www.lianja.com/doc/index.php/TAG()


Check whether index exists
INDEXEXISTS
if indexexists("southwind","customers","products","productid")
    open database southwind
    use products order productid
else
    open database southwind
    use products
    index on productid tag productid
endif

https://www.lianja.com/doc/index.php/INDEXEXISTS()


Number of tags in the active .dbx file
TAGCOUNT
use accounts
? tagcount()
          4
set order to 0
?tagcount()
         0

https://www.lianja.com/doc/index.php/TAGCOUNT()


Number of an index tag
TAGNO
?tagno("city")
         3
?tagno("last_name", "customer.dbx", "customer")
         1

https://www.lianja.com/doc/index.php/TAGNO()


Key expression of an index tag
TAGEXPR
open database southwind
use customers in 1
select 2
use orders
?tagexpr(2,"customers")

https://www.lianja.com/doc/index.php/TAGEXPR()


FOR expression of an index tag
TAGFOR
open database southwind
use customers
? tagfor("customerid") // returns empty string
? tagfor("label_germany") // returns 'CONTAINS("Germany", .f.)'
? tagno("label_germany", "customers.dbx", "customers") // returns 'CONTAINS("Germany", .f.)'

https://www.lianja.com/doc/index.php/TAGFOR()


Key expression of an index tag
TAGKEY
open database southwind
use customers in 1
select 2
use orders
?tagkey(2,"customers")

https://www.lianja.com/doc/index.php/TAGKEY()


Test whether an index tag is unique
TAGUNIQUE
open database southwind
use customers in 1
select 2
use orders
?tagunique(2,"customers")

https://www.lianja.com/doc/index.php/TAGUNIQUE()


Character expression in inverse character sequence
DESCEND
index on descend(name) to descendname
seek descend("Smith")

https://www.lianja.com/doc/index.php/DESCEND()


Determine whether an index tag is a candidate index tag
CANDIDATE
if tagcount() > 0
    for i = 1 to tagcount()
        ? "Candidate status for " + tag() + " is " + ltos(candidate(i))
    next
endif

https://www.lianja.com/doc/index.php/CANDIDATE()


Function used in the creation and searching of descending order tag indexes
DBXDESCEND
index on name tag descendname descending
seek dbxdescend("Smith")

https://www.lianja.com/doc/index.php/DBXDESCEND()


Return optimum ICACHE size for a given index expression
ICACHE
use names index names
size = icache(indexkey(1))
set icache to size
reindex
use names
set icache to icache(name+left(address, 5))
index on name+left(address, 5) to names

https://www.lianja.com/doc/index.php/ICACHE()


Determine whether a specified key value already exists
KEYMATCH
m_val=part_no
if keymatch(m_val)
    dialog box "This part number already exists!"
endif

https://www.lianja.com/doc/index.php/KEYMATCH()


Verify presence of an index key
SEEK

 

use accounts in 1
use supp in 2 index name
select 1
display name
BCD CORPORATION
? iif(seek(name,supp),supp_name,"***name not on file***")
BCD CORPORATION

https://www.lianja.com/doc/index.php/SEEK()


create index lname;
  on example (lower(last_name))
alter index lname;
  on example;
  rebuild
drop index lname;
  on example

https://www.lianja.com/doc/index.php/Using_Recital_SQL


create index lname;
  on example (lower(last_name))
alter index lname;
  on example;
  rebuild
drop index lname;
  on example

https://www.lianja.com/doc/index.php/Using_Lianja_SQL


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
use orders
reindex

https://www.lianja.com/doc/index.php/Using_Navigational_Data_Access_Commands_in_Lianja


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.