Difference between revisions of "Using Recital SQL"

Revision as of 10:22, 24 December 2009

Using Recital SQL

Creating a Recital Database

The create database statement 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 statement.

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 SQL statements, use the open database statement.

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.

Tables from a database that is not currently open can be accessed by preceding the table name by the database name and a !.

select * from southwind!shippers

Recital also supports the MySQL compatible use statement to open a database.

use <database>

Creating a Recital Table using Recital SQL

The create table statement creates a new table in the current database, or a new free table if no database is open.

Table column names, data types and constraints can be defined. Table constraints can also be specified.

create table [if not exists] <table>
  (<column> <datatype> [<column constraint> [...]][,...] [<table constraint> [...]])

Data Types

Recital supports the following data types. For full details, please see Appendix A.

• BIGINT
• BIT
• CHAR
• CURRENCY
• DATE
• DATETIME
• DECIMAL
• DOUBLE
• FLOAT
• GENERAL
• INTEGER
• LOGICAL
• LONG VARCHAR
• LONG VARBINARY
• MEDIUMINT
• MEMO
• NUMERIC
• OBJECT
• REAL
• SHORT
• SMALLINT
• TEXT
• TIME
• TIMESTAMP
• TINYINT
• VARCHAR

Table Constraints

Column Constraints

Example

create table customer;
  (account_no char(5) description "Account Code";
    default strzero(seqno(),5),;
  title char(3) description "Personal Title",
  last_name char(16) description "Customer's Last Name",;
  first_name char(10) description "Customer's Given Name",;
  initial char(2) description "Customer's Middle Initial",;
  street char(25) description "Street Number and Name",;
  city char(12) description "City",;
  state char(2) description "State Abbreviation";
    check rlookup(customer.state,state);
    error "Invalid State",;
  zip char(10) description "Zip Code",;
  limit decimal(11,2) description "Credit Limit";
    recalculate,;
  balance decimal(11,2) description "Credit Balance";
    recalculate,;
  available decimal(11,2) description "Credit Available";
    calculated limit-balance,;
  notes long varchar description "Customer Notes",;
  start_date date description "Customer Start Date";
    default date(),;
  onopen "customer")

Loading Metadata from an Array or XML File

The create table statement also allows a new table to be created from metadata stored in an array or an XML file. In addition, data can optionally be loaded from the specified XML file.

create table [if not exists] <table>
  [from] xml <.xml file> [load] | from array <array> 

Examples

// Create table from XML file
select * from customer;
  save as xml cust.xml
 
create table customer2;
  from xml cust.xml
 
// Create table from array
create table newtable from array meta1

Modifying a Table

The alter table statement allows columns in an existing table to be added, modified, renamed or dropped. Both table and column constraints may be added or dropped.

Add column(s), column constraint(s) or table constraint

alter table <table> add [column] (<column> <datatype> [<column constraints>] [,...])
  | <table constraint>

Alter column(s), column constraint(s) or table constraint

alter table <table> alter [column] <column> [set default <value> | drop default]
  | (<column> <datatype> [<column constraint>] [,...])
  | constraint (<column> set <column constraint> <value> [,...])
  | <table constraint>

Drop column(s), column constraint(s) or table constraint

alter table <table> drop [column] <column>
  | (<column> [,...])
  | constraint (<column> <column constraint> [,...]) | <table constraint>

Add check table constraint

alter table <table> set check <condition> [error <message>]

Rename column

alter table <table> rename (<column>,<new column>)

Examples

// Add new column with column constraints
alter table customer add column timeref char(8);
  check validtime(timeref);
  error "Not a valid time string"
 
// Alter existing columns to add column constraints
alter table customer;
  alter column available calculated limit-balance;
  alter column limit recalculate;
  alter column balance recalculate
 
//or
alter table customer;
  alter (available calculated limit-balance,;
  limit recalculate,;
  balance recalculate)
 
// Add new column, add column constraint,
// modify column datatype and drop constraints then drop column
alter table customer add (timeref char(8))
 
alter table customer;
  alter constraint ;
  (timeref set check validime(timeref);
  error "Not a valid time string")
 
alter table customer;
  alter (timeref datetime);
  drop constraint (timeref check, timeref error)
 
alter table customer drop (timeref)
 
// Add an onupdate table constraint 
alter table customer;
  add onupdate "do check_update"
 
// Add and then remove check table constraint
alter table customer set check checkit() error "Invalid operation"
 
alter table customer drop check
 
// Rename column
alter table customer rename(first_name,forename)

Deleting a Table

The drop table statement is used to delete a table.

drop table <table>

Creating an Index

Indexes may be created using the column and table constraints (foreign key, primary key, unique) and the create table or alter table statements or with the create index statement.

create [unique] index <index> on <table> (<column> [asc | desc] [,...])
create [unique] index <index> on <table> (<expression> [asc | desc]) 

Altering or Removing an Index

Existing indexes may be altered using the alter index statement.

alter index <index> on <table> 

The drop index statement is used to remove an index.

drop index <index> on <table>

Inserting Records into a Recital Table using Recital SQL

• INSERT

insert into <table> [(<column> [,...])]

values (<expr> [, ] | NULL [, ] | <empty> [, ] [,...]) | <sub-query> | [FROM] XML <.xml file>

insert into <table>

from array <array> | from memvar | from name <object> 

Selecting Records from a Recital Table Using Recital SQL

• SELECT

Updating Records in a Recital Table using Recital SQL

• UPDATE

update <table>
set <column> = <expr> [,...]
[where <condition> | current of <cursor>]

• UPDATE FROM XML

update <table> FROM XML <xml filename>

• BEGIN...END TRANSACTION

begin transaction [<transaction>]
<statements>
end transaction [<transaction>]

• COMMIT

commit [transaction <transaction>]

• ROLLBACK

rollback [transaction <transaction> | <savepoint>] [to savepoint <savepoint>]

Deleting Records in a Recital Table using Recital SQL

The delete statement is used to delete records. It executes an immediate physical deletion of the specified records: records are permanently removed from the table and cannot be recalled.

delete from <table> [where <condition>]

Example

delete from currorders where shippeddate < date()

Using a Cursor to Select, Update and Delete Records

A cursor is a temporary selection of rows that can be fetched one at a time and processed further. The update and delete statements can both operate on the most recently fetched row from a cursor.

update <table> set <column> = <expr> [,...] where current of <cursor>

delete from <table> where current of <cursor>

A cursor must be declared:

declare <cursor> cursor for select <statement>

then opened:

open <cursor>

then fetched into memory variables or an XML file:

fetch [next | previous | first | last | absolute <row> | current | relative <row>] <cursor>
  into [<variable> [, ...] | xml <xml filename>]

Once processing is complete, the cursor can be closed:

close <cursor>

then released:

drop cursor <cursor> 

Example

open database southwind
 
// Declare the cursor to select records from the orders table
declare cursor1;
  cursor FOR;
  select orderid, customerid, orderdate;
  from orders;
  where requireddate < date()
 
// Open the cursor
open cursor1
 
// Fetch records one at a time from the cursor and update them
fetch cursor1;
  into m_order, m_customer, m_orderdate
  do while sqlcode = 0
    if not empty(m_order) and empty(shippeddate)
        update orders;
          set shippeddate = date();
          where current of cursor1
    endif
    fetch cursor1;
      into m_order, m_customer, m_orderdate
enddo
 
// Close the cursor and free up any resources used for the cursor
close cursor1
drop cursor cursor1
 
close databases

Obtaining Information about a Recital Table using SQL

System defined read-only system tables can be queried using the select statment to provide a range of information about Recital tables.

System tables include the following:

sysbestrowidentifier - description of a table’s optimal set of columns that uniquely identifies a row syscolumnconstraints - description of the constraints for a table’s columns syscolumnprivileges - description of the access rights for a table’s columns syscolumns - description of the table columns available in the catalog syscrossreference - description of how one table imports the keys of another table sysexportedkeys - description of the foreign key columns that reference the primary key columns sysimportedkeys - description of the primary key columns that are referenced by the foreign key sysindexinfo - description of a table’s indices and statistics sysprimarykeys - description of the primary key columns in the table systableconstraints - description of the constraints for each table available in the catalog systableprivileges - description of the access rights for each table available in the catalog systables - description of the tables available in the catalog systabletypes - table types available in the database system sysversioncolumns - description of the columns in a table that are automatically updated when any row is updated

Executing Procedures and Prepared Statements using Recital SQL

• CREATE PROCEDURE

create procedure <procedure> as <procedure source code>
endcreate

• DROP PROCEDURE

drop procedure <procedure> 

• EXECUTE

execute <statement> using :<variable>[,<variable2>[,...]]

• EXECUTE IMMEDIATE

execute immediate <statement> 

• PREPARE

prepare <statement> from :<variable>

• SYSRESULTSET Used to return the singleton result from any Recital expression

select set("exclusive") as Excl, time() as Time from sysresultset