The Documentation of LogicSQL

1. Introduction
2. User's Guide

2.1 Data Types
2.2 Functions and Operators
2.3 SQL Commands
2.4 System Utility Commands
2.5 XML Commands
2.6 Security Model
2.7 System Information and Information_schema
2.8 SQL Syntax

3. Database Administrator's Guide

3.1 Installation
3.2 Server Runtime Environment
3.3 System Configuration
3.4 Managing Database Server
3.5 Managing Databases
3.6 Database User's Management
3.7 Database Recovery
3.8 Tutorial for DBA

4. Interfaces

4.1 JDBC
4.2 ODBC
4.3 Embedded C Pre-compiler
4.4 The C Language Interface

5. Development Tools

5.1 DBA Tools
5.2 SQL*Face

6. Database Tests

6.1 SQL Conform Tests
6.2 TPC-C Benchmark Tests

Concept Index
Bug Report and Contact Infomation

1. Introduction

LogicSQL is an object relational database management system implemented with the advanced (formula-lock based) concurrency control protocol.

LogicSQL is designed to provide

1. a reliable and secure database management system for applications ranging from small business applications to multiple-user, high-volume on-line transaction systems,
2. cost-effective and easy-to-use database functionality that is compatible with the SQL99 standard and is supported with the standard user interfaces such as the JDBC, ODBC, and embedded C interface,

The functionality of LogicSQL is demonstrated by the Data Types, Functions and Operators, and SQL Commands it supports. In fact, LogicSQL supports

1. all the basic SQL types, including date, blob, and clob,
2. the query facility that is as expressive as any other commercial database management systems,
3. secondary indexes, SQL views, SQL triggers and SQL sequences,
4. the transaction management facility with the isolation level as high as serializability, and
5. the security facility based on the discretionary access control mechanism.

The interfaces supported by LogicSQL include

1. the standard Java interface JDBC,
2. the standard C interface ODBC,
3. the standard Embedded C Pre-compiler, and
4. The C Language Interface.

1. DBA Tools, a graphic dba/user interface, and
2. SQL*Face, an interactive access program.

The TPC-C Benchmark Tests demonstrate that LogicSQL performs extremely well, and in small computer systems, it outperforms almost all other commercial database management systems, thanks to its advanced system architecture. The system is designed to work with limited system resources. In fact, The server takes less than 10Mb disk space and works smoothly for as little as 20Mb memory. With 256 Mb memory, the server can easily handle more than 1000 concurrent clients.

The following table lists a recent TPC-C benchmark testing results. The test is conducted with a server on a Linux box (a PC running Linux with Pentium 3, 1 GHz, and 1 GB memory), and all clients are connected to the server through the Internet with ADSL connection. For comparison, the results for the Oracle 8 are also included. The performance is measured by the number of transactions per minute, the higher the better. The rollback ratio characterizes the ratio of roll-backed transactions over the committed transactions, and the lower the better.

number of clients	number warehouses	LogicSQL performance	rollback (%)	Oracle 8 performance	rollback (%)
10	1	37	0.0	38	6.40
100	10	290	1.60	380	22.22
150	15	362	3.84	291	42.70

The table above demonstrates that LogicSQL outperforms Oracle 8, noting the high rollback ratio of Oracle 8. Since Oracle 8 cannot work smoothly when the number of clients exceeds 200, only three cases are listed here.

The following table lists another TPC-C benchmark testing results with a LogicSQL server running in the same Linux box as above. In the following tests, however, clients are connected to the server through the local network.

number of clients	number of warehouses	thinking/keying time (% of the specified time)	performance (transactions/minute)
10	1	0	1290
100	10	15	724
250	25	100	664
500	50	100	559
1000	100	100	396
2000	100	200	350

The above table shows that the LogicSQL server installed on a small computer system can easily handle requests from 1000 concurrent clients, which is an impossible task for any other commercial system. (The performance for 10 and 100 clients in this table is significantly higher than that of the previous table. This is because in these two cases we set the thinking time to zero to maximize the working load of the server.) Please note that the above system costs less than US$2000, and thus Price/Performance is around US$4.

2. User's Guide

2.1 Data Types
2.2 Functions and Operators
2.3 SQL Commands
2.5 XML Commands
2.4 System Utility Commands
2.6 Security Model
2.7 System Information and Information_schema
2.8 SQL Syntax

2.1 Data Types

Each literal, column value, or a stored procedure's argument manipulated by LogicSQL has a datatype. Each datatype is associated with a set of properties and thus will be treated accordingly.

When you create a table, you must specify a datatype for each of its columns, and when you create a stored procedure, you must specify a datatype for each of its argument.

LogicSQL has a rich set of predefined (base) data types available to users.

The following table summarizes LogicSQL's redefined datatypes.

LogicSQL	Type SQL99	Type Description
char(size), character(size)	char(size), character(size)	character string having maximum length size bytes. The maximum size is 4096 (or StringSize as specified in configuration ) and the minimum is 1. The default size (when size is not given) is 1.
varchar(size), char varying(size), character varying(size) varchar2(size)	varchar(size)	variable-length character string with the size property the same as char above. Note that the internal data type for all variable-length character strings are varchar(size).
int, integer	int, integer	integers with value between -2147483648 and 2147483647, inclusive.
smallint	smallint	integers with value between -32768 and 32767, inclusive.
tinyint	tinyint	integers with value between -128 and 127, inclusive
bigint	bigint	integers
numeric(P, S), numeric(P), numeric, number(P,S), number(P), number	numeric(P,S)	The numeric datatype stores a fixed or floating number with precision P and scale S, where P < 38, and -15 < S < 15, defaults of P and S are 38 and 15 respectively. Note that the internal data type for all the numbers are numeric.
decimal(P, S), dec(P, S), real	decimal(P,S), etc.	The number datatype stores a fixed or floating number with precision P and scale S, where P < 38, and -15 <S < 15, defaults of P and S are 38 and 15 respectively.
float(P), float	float(P), float	The number datatype stores a fixed or floating number with precision P and scale S, where P < 38, and -15 < S < 15, defaults of P and S are 38 and 15 respectively. (For now, float is treated the same as numeric.)
date	date	The datatype stores the date information in a string of form '2000-10-01' with the interval form the same as that of timestamps below.
time	time	The time type stores the time information in a string of form '18:09:50'.
timestamp	timestamp	The datatype stores the date and time information in a string of form '2000-10-01 18:09:50'.
blob	blob(size)	The blob datatype stores unstructured binary large objects. It can store up to 2 gigabytes of binary data, limited by available memory.
clob	clob(size)	The clob datatype stores single-byte character data. It can store up to 2 gigabytes of character data, limited by available memory.
long	long	character data of variable length, implemented using clob for backward compatibility
long raw	long raw	Raw binary data of variable length, implemented using blob for backward compatibility

2.1.1 CHAR Datatype

Consider a table crated and populated by the following statements.

create table students (sid char(10), sname varchar(100)); insert into students values( '1234567890', 'Sarah'); insert into students values( '12345', 'Tom');

Then three queries below lead to different result tables.

LogicSQL> select * from students; sid sname --------------------- 12345 Tom 1234567890 Sarah LogicSQL> select * from students where sid = '12345 '; sid sname --------------------- 12345 Tom LogicSQL> select * from students where sid = '12345'; sid sname --------------

Note that the last query fails to select any rows since a non-padded string is used for comparison.

2.1.2 Large OBject (LOB) data types

The LOB datatype BLOB, CLOB can store large and unstructured data such as text, image, video, and spatial data up to 2 gigabytes in size. The LOB access methods are based on the following facts.

• The LogicSQL database stores a lob_locater in the LOB columns, and lob locators are a string of characters that refer to out-of-table lob values.
• The lob locators can be accessed by any SQL statements with the following exceptions:
  • there are two special lob locators, 'empty_blob' and 'empty_clob', that represent an empty lob item.
  • a lob locater, other than the above two empty lobs, shall not appear in any SQL statement.
• One can use the INSERT statement with empty lobs to populate a table with lob columns.
• The lob values can only be accessed with some appropriate interfaces such as put_one_lob and get_one_lob in the The C Language Interface, and PreparedStatement in JDBC.

In general, one can create a table with any number of lob columns, populate the table with empty_clob/empty_blob values, and retrieve lob_locators using the standard CREATE, INSERT, and SELECT statements. But the lob values can be updated and retrieved only by either the C-functions or Java methods.

Consider the table employee created with the following statement

CREATE TABLE employee ( e_id integer, e_name varchar(30), photo blob, constraint employee_primary_key primary key( e_id ) );

• To insert a new tuple with a lob value
  • You shall first insert a new tuple using empty_blob as the initial value of the clob column:

    insert into employees ( 612345789, 'Harry Potter', 'empty_blob' )

  • You can then replace the empty blob with a lob value using the update command. This, however, can be accomplished only through some specially designed functions/methods, such as put_one_lob in the C-library or the PreparedStatement in JDBC.
• To retrieve a lob value from the table
  • You can first retrieve the lob locator using the standard SQL statement such as the following one

    select photo from employees where e_id = 612345789 ,

    which will returns a lob_locator for the value.

  • The lob value can the be retrieved from the database system using get_one_lob when the lob locator is known.
  • Using the Java JDBC, one can use a standard executeQuery to obtain a result set and then retrieve the Blob/Clob objects from the result set.

• Example of using lobs_operation in the C-interface,
• Example of using PreparedStatement in JDBC
• Examples with Java Servlets

  (http://luscar.cs.ualberta.ca:8080/yuan/servlets/logicsql.html).

2.1.3 User-defined Data Types

2.2 Functions and Operators

2.2.1 Operators

The list of all operators supported by LogicSQL is summarized in the following table.

Operator Classes	Operators
Arithmetic Operators	+, -, *, /
String Operators	||
Comparison Operators	=, <>, >, <, >=, <=, =<, <=, IS NULL, IS NOT NULL IN, NOT IN, BETWEEN, NOT BETWEEN, LIKE, NOT LIKE
Logic Operators	NOT, AND, OR
SET Operators	UNION, UNION ALL, INTERSECT, EXCEPT (MINUS)

We will describe all the operators in details.

Precedence

Precedence is the order in which LogicSQL evaluates different operators in the same expression. When evaluating an expression containing multiple operators, Operators with higher precedence will be evaluated before those with lower precedence. LogicSQL evaluates operators with equal precedence from left to right within an expression.

The following table lists the levels of precedence among SQL operators from high to low. Operators listed on the same line have the same precedence.

Operator	Operation
+, -	numeric sign
*, /	multiplication, division
+, -, ||	addition, subtraction, concatenation
=, !=, <>, >, <, >=, =<, IS NULL, IS NOT NULL IN, NOT IN, BETWEEN, NOT BETWEEN, LIKE, NOT LIKE	comparison
NOT	logical negation
AND	logical conjunction
OR	logical disjunction

Table SQL Operator Precedence

Details and examples about how to use these operators shall be here.

2.2.2 SQL Functions

Similar to an operator, an SQL function manipulates data items and returns a result. SQL functions differ from operators in the format in which they appear with their arguments. This format allows them to operate on zero, one, two, or more arguments:

function(argument, argument, ...)

If you call a SQL function with an argument of a datatype other than the datatype expected by the SQL function, LogicSQL implicitly converts the argument to the expected datatype before performing the SQL function.

If you call a SQL function with a null argument, the SQL function automatically returns null.

There are two types of SQL functions:

• scalar (single-row) functions
• aggregate (group row) functions

Scalar functions can appear in select lists (if the SELECT statement does not contain a GROUP BY clause) and WHERE clauses.

Following SQL'99, aggregate functions can appear in select lists and HAVING clauses. If the GROUP BY clause is specified in a SELECT statement, LogicSQL divides the rows of a queried table or view into groups. In a query containing a GROUP BY clause, all elements of the select list which are not aggregate functions or constants must be included in the GROUP BY clause,

If you omit the GROUP BY clause, LogicSQL applies group functions in the select list to all the rows in the queried table or view. The having clause is used to eliminate groups from the output based on the results of the group functions, rather than on the values of the individual rows of the queried table or view.

The following table lists all the LogicSQL aggregate functions as specified in SQL99.

Aggregate Functions	Result Returned
COUNT(*)	the number of all rows
COUNT(expression )	the number of all non-null values for the given expression
COUNT(DISTINCT expression)	the number of all non-null distinct values for the given expression
SUM(expression)	the sum of all non-null values for the given expression
SUM(DISTINCT expression)	the sum of all non-null distinct values for the given expression
AVG(expression)	the average value of all non-null values for the given expression
AVG(DISTINCT expression)	the average value of all non-null distinct values for the given expression
MAX(expression)	the maximal value of all non-null values for the given expression
MIN(expression)	the minimal value of all non-null values for the given expression

Table LogicSQL Aggregate Functions

Nested aggregate functions are not allowed. That is, aggregate functions are not allowed to appear in an expression inside an aggregate function.

Note that DISTINCT inside aggregate functions has not been implemented yet.

Scalar functions can be classified as the follows.

• Numeric functions

  Numeric functions accept numeric arguments and return a numeric value.

• Character functions:

  Character functions accept string arguments and return a string or numeric value.

• Date and time functions

  Date and time functions accept date and/or time values and return a date/time value or a numeric value.

• Cast functions

  Cast functions are used to convert a value from one datatype to another.

Numeric Functions

Function	Result Returned
ABS(n)	the absolute value of the numeric expression n
ACOS(n)	the arc cosine of n in the range of -1 and 1. The result is in the range of 0 and \pi and is in radians.
ASIN(n)	the arc sine of n in the range of -1 and 1.
ATAN(n)	the arc tangent of n
CEIL(exps)	the smallest integer greater than or equal to the numeric expression exps
COS(n)	the cosine of n, where n is an angle expressed in radians
EXP(n)	e to the power of n
FLOOR(n)	the smallest integer greater than or equal n
LN(n)	the natural logarithm of n, where n > 0
LOG(m, n)	the logarithm, base m, of n, where m > 0, m != 1, and n >= 0
MOD(m,n)	the remainder of m divided by n. It returns m when n = 0.
POWER(m,n)	m raised to the nth power.
ROUND(n [,m])	n rounded to m places right to the decimal point; if m is omitted, to 0.
SIGN(n)	the sign of n, i.e., -1, 0, or 1, depending if n<0, n = 0, or n > 0.
SIN(n)	the sine of n (an angle expressed in radians)
SQRT(n)	the square root of n, where n >= 0
TAN(n)	the tangent of n (an angle expressed in radians)
TRUNC(n [,m])	n truncated to m decimal places; if m is omitted, to 0 places

Numeric Functions with Char/Datetime Arguments

The following table lists character functions that return number values.

Function	Result Returned
ASCII(char)	the decimal representation of the first character of char
CHAR_LENGTH(char), CHARACTER_LENGTH(char) LENGTH(char)	the length of char
INSTR(char1,char2,[,n[,m]])	Searches char1 beginning with its nth character for the mth occurrence of char2
POSITION(char1 IN char2)	the relative position of char1 in char2

Character (String) Functions

Function	Result Returned
CHR(n)	the character having the binary equivalent to n
CONCAT(char1, char2)	char1 || char2
EXTRACT(field FROM d)	extracted field value from d of date datatype
INITCAP(string)	the string with the first letter of each word in uppercase, and all other in lowercase
LOWER(string)	the string with all letters lowercase.
LPAD(char1,n [, char2])	char1, left-padded to length n with the sequence of char2
LTRIM(char [,set])	removes characters from char, with all the leftmost chars in set removed.
REPLACE(char, search_string [,replacement_string])	char with every occurrence of search_string replaced.
RPAD(char1,n [, char2])	char1, right-padded to length n with the sequence of char2
RTRIM(char [,set])	removes characters from char, with all the rightmost chars in set removed.
SUBSTRING(char FROM m [FOR n]) (SQL99)	a substring of char, beginning at character m, n characters long
SUBSTR(char, m [,n])	it is different from SUBSTRING, but it is the same for now
TRIM([[LEADING|TRAILING|BOTH] [char2] FROM] char1 )	removes characters from char1, with char2 removed from
UPPER(char)	char with all letters uppercase

Date and Time Functions

Date and time functions take the input of date/time datatype and returns a value of date/time datatype or an integer/string datatype.

Function	Result Returned
ADD_MONTHS(d, n)	the date d plus n months.
CURRENT_DATE	the current date
CURRENT_TIME	the current time
CURRENT_TIMESTAMP	the current timestamp
LAST_DAY(d)	the date of the last day of the month that contains d
NEW_TIME(d, timezone)	d converted into the date/time for the specified time zone.
NEXT_DAY(d, char)	the date d the first weekday named by char that is later than d
SYSDATE	the current date and time
Date Arithmetic	manipulate on the date values

Date Arithmetic

You can add and subtract number constants as well as other dates from dates. LogicSQL interprets number constants in arithmetic date expressions as numbers of days. For example, SYSDATE + 1 is tomorrow. SYSDATE - 7 is one week ago. SYSDATE + (10/1440) is ten minutes from now. Subtracting the HIREDATE column of the EMP table from SYSDATE returns the number of days since each employee was hired. You cannot multiply or divide DATE values.

Because each date contains a time component, most results of date operations include a fraction. This fraction means a portion of one day. For example, 1.5 days is 36 hours.

Cast Functions

Cast functions convert a value from one datatype to another.

Function	Result Returned
TO_CHAR(d [,fmt ])	Converts d of DATE datatype to a value of VARCHAR datatype in the format specified by the date format fmt, which is the same as that of strftime in time.h.
TO_CHAR(n [,fmt ])	Converts n of NUMERIC datatype to a value of VARCHAR datatype. The format is the same as that for printf in stdio.h.
TO_DATE(char )	Converts char of VARCHAR type to a value of DATE/TIME/TIMESTAMP datatype
TO_NUMBER(char)	Converts char of VARCHAR datatype containing a number to a value of NUMERIC datatype.

Other Functions

Function	Result Returned
CURRENT_USER	the current user
USER	the current user
NVL(Expr1, Expr2)	If expr1 is NULL returns Expr2; otherwise return Expr1

2.2.3 CHR

• Syntax

  CHR(n)

• Function

  CHR returns the character having the binary equivalent to n.

  If n > 256, then LogicSQL returns the binary equivalent of n mod 256.

• Examples

  LogicSQL> SELECT CHR(67)||CHR(65)||CHR(84) FROM INFORMATION_SCHEMA.DUAL; chr(67)||chr(65)||chr(84) ---------------------------- CAT

2.2.4 INSTR

• Syntax

  INSTR(string1,string2 [,n[,m]])

• Function

  Searches string1 beginning with its nth character for the mth occurrence of string2 and returns the position of the character in string1 that is the first character of this occurrence. If n is negative, LogicSQL counts and searches backward from the end of string1. The value of m must be positive. The default values of both n and m are 1, meaning LogicSQL begins searching at the first character of string1 for the first occurrence of string2. The return value is relative to the beginning of string1, regardless of the value of n, and is expressed in integer. If the search is unsuccessful (if string2 does not appear m times after the nth character of char1) the return value is 0.

  If the value of n or m is out of the range, it will then set to 1.

• Examples

  LogicSQL> SELECT INSTR('CORPORATE FLOOR','OR', 3, 2) instring_position FROM INFORMATION_SCHEMA.DUAL; instring_position -------------------- 14 LogicSQL> SELECT INSTR('CORPORATE FLOOR','OR', -3, 2) FROM INFORMATION_SCHEMA.DUAL; instr(string(CORPORATE FLOOR),string(OR),-3,2) ------------------------------------------------- 2

2.2.5 NVL

• Syntax

  NVL(expr1, expr2)

• Function

  {NVL} returns expr2 if expr1 is NULL; otherwise, returns expr1.

• Examples

  LogicSQL> SELECT sid, name, NVL(major, 'not determined') FROM student; sid name nvl(major,not determined) ---------------------------------------------- 10 susan cs 20 peter not determined 30 sarah md 40 tom ce

  Expr1 and Expr2 must have the same datatype, though they can be any type.

2.3 SQL Commands

This section describes, in alphabetical order, LogicSQL commands and statements.

All LogicSQL statements are classified into the following catagories:

• Data Definition Language (DDL) statements
• Data Manipulation Language (DML) statements
• Transaction Control statements
• Session Control statements
• System Utility statements

Data Definition Language (DDL) statements are used to perform the following tasks

• create and drop database objects such as tables, views, indexes, and triggers,
• grant and revoke access privileges and roles

LogicSQL implicitly comments the current transaction after each DDL statements.

Data Manipulation Language (DML) statements are used to retrieve and update data stored in various database objects, including

• INSERT
• DELETE
• UPDATE
• SELECT

Transaction Control statements controls the execution of transactions. It contains the following SQL statements

• SET TRANSACTION (START TRANSACTION)
• COMMIT
• ROLLBACK

Session Control statements dynamically change the behavior of a user session.

• SET AUTO COMMIT ON/OFF
• SET ROLE

Running Examples Through out this section, we will use a sample database with three tables listed below for demonstration.

student(sid, name, major, gpa) course(cid, title, description) registration(cid, sid, grade) LogicSQL> select * from student; sid name major gpa ------------------------------- 5500 Susan Law 12001 Sarah Math 12345 Peter 54321 Bob Buines LogicSQL> select * from course; cid title description --------------------------------------------------------- AR100 Cartoon Drawing A commic book BS499 Marketing How to make quick bucks CS291 Introduction to DBMS first database course MA101 Calculus An easy course LogicSQL> select * from registration; cid sid grade ------------------------ BS499 54321 B CS291 12001 CS291 54321 A MA101 12001 D

The script file to set up the running database is given below.

/* * to set up the initial database */ DROP TABLE student; DROP TABLE course; DROP TABLE registration; CREATE TABLE student ( sid INT, name VARCHAR(64) NOT NULL, major VARCHAR(10), gpa NUMERIC(5, 2), PRIMARY KEY(sid) ); CREATE TABLE course ( cid CHAR(5), title VARCHAR(64) NOT NULL, description VARCHAR(1024), PRIMARY KEY(cid) ); CREATE TABLE registration ( cid CHAR(5), sid INT, grade CHAR(1), PRIMARY KEY(cid, sid), CONSTRAINT cid_foreign_key FOREIGN KEY (cid) REFERENCES course, CONSTRAINT sid_foreign_key FOREIGN KEY (sid) REFERENCES student, CONSTRAINT grade_constraint CHECK ( grade IN ('A', 'B', 'C', 'D', 'F')) ); INSERT INTO student (sid, name, major) VALUES (12001,'Sarah', 'Math'); INSERT INTO student (sid, name, major) VALUES (12345,'Peter', null); INSERT INTO student (sid, name, major) VALUES (54321,'Bob', 'Buines'); INSERT INTO student (sid, name, major) VALUES (5500, 'Susan', 'Law'); INSERT INTO course VALUES ('CS291','Introduction to DBMS','first database course'); INSERT INTO course VALUES ('MA101', 'Calculus', 'An easy course'); INSERT INTO course VALUES ('BS499', 'Marketing', 'How to make quick bucks'); INSERT INTO course VALUES ('AR100', 'Cartoon Drawing', 'A commic book'); INSERT INTO registration VALUES('CS291', 12001, null); INSERT INTO registration VALUES('CS291', 54321, 'A'); INSERT INTO registration VALUES('MA101', 12001, 'D'); INSERT INTO registration VALUES('BS499', 54321, 'B');

The list of All LogicSQL statements.

2.3.1 ABORT		Abort the current operation
2.3.2 ALTER USER		Alter the user password
2.3.3 CLOSE CURSOR		Close a given cusor
2.3.4 COMMIT		Commit the current transaction
2.3.5 CREATE DATABASE		Create a new database
2.3.6 CREATE DATATYPE		To be implemented
2.3.7 CREATE INDEX		Create an index for a given table
2.3.8 CREATE PROCEDURE		Create a stored procedure
2.3.9 CREATE ROLE		Create an authorization role
2.3.10 CREATE SCHEMA		Create tables and views and perform multiple grants in a single transaction
2.3.11 CREATE SEQUENCE		Create a sequence of integers
2.3.12 CREATE TABLE		Create a new table
2.3.13 CREATE TRIGGER		Create a new trigger
2.3.14 CREATE USER		Create a new user
2.3.15 CREATE VIEW		Create a view
2.3.16 DECLARE CURSOR		Declare a session cursor
2.3.17 DELETE		Delete rows from a table
2.3.18 DROP DATABASE		Remove an existing database
2.3.19 DROP INDEX		Remove an existing index
2.3.20 DROP PROCEDURE		Remove a stored procedure
2.3.21 DROP ROLE		Remove an existing role
2.3.23 DROP SEQUENCE		Remove a existing sequence
2.3.24 DROP TABLE		Remove an existing table
2.3.25 DROP TRIGGER		Remove an existing trigger
2.3.26 DROP USER		Remove an existing user
2.3.27 DROP VIEW		Remove an existing view
2.3.28 EXECUTE PROCEDURE		Execute a stored procedure
2.3.29 FETCH		FETCH a row from the result set pointed by the given cursor
2.3.30 GRANT PRIVILEGE		Grants access privilege
2.3.31 GRANT ROLE		Grants roles to users and roles
2.3.32 INSERT		Inserts new rows into a table
2.3.33 OPEN CURSOR		Open a cursor
2.3.34 REVOKE PRIVILEGE		Revoke access privilege
2.3.35 REVOKE ROLE		Revoke roles from users and roles
2.3.36 ROLLBACK		Aborts the current transaction
2.3.37 SET ROLE		Set the role for the current SQL session
2.3.38 SELECT and Subquery		Query the database
2.3.41 SET AUTO_COMMIT		Set the auto_commit on/off for the client
2.3.40 SET SESSION USER		Set the current session user
2.3.39 SET TRANSACTION		Set the isolation level of the current transaction
2.3.42 UPDATE		Update column values of a table

2.3.1 ABORT

• Function Abort the current operation.

• Syntax

  ABORT

• Usage

  The command aborts the current operation. It is mainly used in a trigger definition to abort the trigger event.

2.3.2 ALTER USER

• Function Modify user account information.

• Syntax

  ALTER USER < identifier > WITH PASSWORD < password string>

• Usage

  ALTER USER sarah WITH PASSWORD 'hgd2f23Abd'

  Ordinary users can only change their own password.
  A password must be a string of characters, digits, and '_', and the password string shall not be constructed from dictionary words.

2.3.3 CLOSE CURSOR

• Function Close a session cursor

• Syntax

  CLOSE < cursor name>

• Usage

  CLOSE current_student;

  This will close the given cusor.

See also DECLARE CURSOR, OPEN CURSOR, and FETCH statements.

2.3.4 COMMIT

• Description Terminate the current SQL-transaction.

  All changes made by the transaction after COMMIT are guaranteed to be durable if a crash occurs.

• Syntax

  COMMIT

• Usage COMMIT

• Note

  See also SET TRANSACTION and ROLLBACK.

2.3.5 CREATE DATABASE

• Function Create a database ( catalog in SQL99 terminology ).

• Syntax

  CREATE CATALOG < catalog name>

• Usage

  CREATE CATALOG my_database

  This will create a database ( catalog ) my_database.

  CREATE CATALOG her_database

  This will create a database (catalog) her_database. her_database

  One needs CREATE ANY CATALOG privilege to create a catalog.

  See Also: DROP DATABASE

2.3.6 CREATE DATATYPE

2.3.7 CREATE INDEX

• Function Use the CREATE INDEX statement to create an index on one or more columns of a table. An index is a schema object that contains an entry for each value that appears in the indexed column(s) of the table and provides direct, fast access to rows. LogicSQL supports conventional (B-tree) indexes.

• Syntax

  CREATE [UNIQUE] INDEX < index name> ON < table name> ( < column list>)

• Usage

  CREATE INDEX customer_index ON customer ( customer_name )

  This will create a secondary index on column customer_name of the table customer such that the retrieval from customer can be processed efficiently as long as the customer name is given.

  If a table has the primary key, then the second index will point to the primary key. Otherwise, it will point to the whole data.

  Creating an index may improve the performance of data retrieval, but it will certainly increase the overhead of data updates. Thus, don't create an index unless your applications involve frequent retrieval based on the values of columns to be indexed.

  It is very time-consuming When an index is created after the table has been populated with some rows because LogicSQL has to build the index file.

  See Also: DROP INDEX

  To create an index for a table in a schema, including your own schema, you must have CREATE INDEX schema privilege on the schema, unless you have CREATE ANY INDEX system privilege.

2.3.8 CREATE PROCEDURE

• Function Use the CREATE PROCEDURE statement to create a standalone stored procedure.

  A stored procedure is a group of extended SQL statements that one can call by name.

  With the CREATE PROCEDURE command, you can create a procedure and store it in the database. You can call a stored procedure from any environment from which you can issue an SQL statement.

  Stored procedures offer advantages in the areas of development, integrity, security, and performance.

  To create a procedure in your own schema, you must have CREATE PROCEDURE system privilege. To create a procedure in another schema, you must have CREATE ANY PROCEDURE system privilege.

  To call a stored procedure, you must have EXECUTE PROCEDURE privileges on the the procedure, or EXECUTE ANY PROCEDURE system privilege.

• Prerequisites

  To create a procedure in your own schema, you must have CREATE PROCEDURE schema privilege. To create a procedure in another schema, you must have CREATE ANY PROCEDURE system privilege.

  Similarly, one needs EXECUTE PROCEDURE schema privilege and/or DROP PROCEDURE schema privilege to execute and/or drop a procedure in his/her own schema.

  The following three statements from system will enable sarah to use procedures in her own schema.

  GRANT create procedure ON sarah TO sarah; GRANT execute procedure ON sarah TO sarah; GRANT drop procedure ON sarah TO sarah;

• Syntax

  <create procedure> ::= CREATE PROCEDURE < procedure name> [< argument list>] < SQL procedure block> <SQL procedure block> ::= [< declare clause>] < SQL procedure statement list> <declare clause> ::= DECLARE < variable name> < data type>[DEFAULT < literal value>];> [{< variable name>< data type> [DEFAULT < literal value>]; ...}] <SQL procedure statement list> :: = BEGIN < SQL procedure statement>; [{< SQL procedure statement>; }] END <SQL procedure statement> ::= < Any SQL statement> | < assignment statement | < if-then-else statement | < case statement | < for loop statement> | < while loop statement> | < repeat loop statement> | < select into statement> | < raise exception statement> | < SQL procedure block> <argument list> ::= ( < argument name> [ IN | OUT ] < data type> [{,< argument name> [ IN | OUT ] < data type> ... } ] ) <parameter list> ::= (< literal value> [{, < literal value>}] ) <assignment statement> ::= :< variable name> = < value expression> <if-then-else statement> ::= IF < boolean value expression> THEN < SQL procedure statement list> [ELSE < SQL procedure statement list>] END IF <case statement> ::= CASE WHEN < variable name> = < integer> TEHN < SQL procedure statement list> [{ WHEN < variable name> = < integer> TEHN < SQL procedure statement list> } ... ] [ELSE < SQL procedure statement list>] END CASE <for loop statement> ::= FOR ( :< variable name> = < integer>; :< variable name> < comparison operator> < integer>; < assignment statement ) < SQL procedure statement list> END FOR <while loop statement> ::= WHILE < boolean value expression> DO < SQL procedure statement list> END WHILE <repeat loop statement> ::= REPEAT < SQL procedure statement list> UNTIL< boolean value expression> <raise exception statement> ::= RAISE EXCEPTION ( < string value expression> ) <select into statement> ::= SELECT [ <DISTINCT|ALL> ] < * | < derived column> [{,< derived column> ... }]> INTO :< variable name> [{:< variable name> ... } ] FROM < table reference> [ , < table reference> ] [WHERE < search condition>] [GROUP BY < item reference> {, < item reference> ... }] [HAVING < search condition >] [ <UNION|UNION ALL|EXCEPT|MINUS|INTERSECT> ( subquery ) ] [< order by clause>] <Any SQL statement> ::= Any LogicSQL statement, except < subquery>, which is replaced by < select into statement>.

  Some Notations

  1. The scope of any declared variable is the immedicate block after the DECLARE clause.
  2. A special variable of INTEGER type, named sqlstate, has been declared automatically to denote the execution status of the very last SQL statement within the stored procedure. The value of sqlstate is assigned by LogicSQL right after the SQL statement is executed as follows:
     1. sqlstate = 0, the execution fails,
     2. sqlstate = 1, the execution succeeds, and
     3. sqlstate = 2, the SQL statement is a SELECT statement but an empty result set is returned.
  3. When the execution of an SQL statement failed or a SELECT statemetn returns an empty result set, the execution of the stored procedure will continue. That is, LogicSQL will not rollback the current transaction when the execution of a statement inside a stored procedure or a trigger, unless the procedure/trigger raises an exception explicitly.
  4. When the raise exception is executed, LogicSQL will first rollback the current transaction, send an error message to the current client, and then exits the stored procedure.
  5. We will, in the next release, declare a special variable of ResultSet type which is used to store and return the queries to the client. We assume that all the result set will be stored in the server temporarily, and it will be removed if the subsequent statements are not SELECT FROM the procedure.name.resultset. Sept. 23, 2004 by Li-Yan Yuan

  6. All system-defined datatypes, except BLOB, CLOB, Long, and Long RAW, are valid in the SQL procedure block.

• Keywords and Explanations

• Usage

  After a stored procedure has been created, it can be called from any environment from which an SQL statement can be issued.

  See Also: EXECUTE PROCEDURE, DROP PROCEDURE.

  Example 1 The following statement creates a stored procedure that can be used to raise an employees salary.

  drop table employee; create table employee (emp_id int, salary real); insert into employee values( 100, 1000000); insert into employee values( 200, 1999900); insert into employee values( 300, 2000000); drop procedure raise_salary; CREATE PROCEDURE raise_salary ( emp_id INTEGER, increase REAL ) DECLARE salary_cap REAL; current_salary REAL; BEGIN SELECT max(salary) INTO :salary_cap FROM employee; IF (sqlstate = 0 ) THEN raise_exception('fail to find the maximum salary'); END IF; SELECT salary INTO :current_salary FROM employee WHERE emp_id = :emp_id; IF (sqlstate <> 1 ) THEN raise_exception('failed to find the salary of the given employee'); END IF; IF ( :current_salary + :increase < :salary_cap ) THEN UPDATE employee SET salary = :current_salary + :increase WHERE emp_id = :emp_id; ELSE raise_exception('no way to raise his/her salary'); END IF; END; call procedure raise_salary(100, 200); call procedure raise_salary(200, 200); call procedure raise_salary(400, 200);

  Example 2 The following example demonstrates how to use sqlstat.

  create table students (sid int, sname varchar(100)); /* * This procedure can be used to pick up one student id from the given table students. * The procedure will output -1 if the selection fail, output 0 if students is * empty, and a student id if there exists at lease one student. * */ create procedure pick_one_student ( sid out integer ) begin select top 1 sid into :sid from students; if sqlstate = 2 then :sid = 0; else begin if sqlstate = 0 then :sid = -1; end if; end; end if; end; insert into students values( 10, 'susan' ); insert into students values( 20, 'peter' ); call procedure pick_one_student();

2.3.9 CREATE ROLE

• Function To create a role. A role is a set of privileges that can be granted to users or to other roles. See also Security Model.
• Syntax

  CREATE ROLE < role name> [WITH ADMIN < grantor>]

  <role name> ::= < identifier>

  <grantor> ::= CURRENT_USER | CURRENT_ROLE

• Prerequisites

  You must have CREATE ANY ROLE system privilege to create a role.

• Usage This statement will create a role authorization descriptor (of the given role name) with the admin option granted to the current user or the current role.

  If [WITH ADMIN < grantor>] is not specified, then the admin option is granted to the current user.

  See Also: DROP ROLE, GRANT ROLE, REVOKE ROLE.

2.3.10 CREATE SCHEMA

• Purpose Use the CREATE SCHEMA statement to create multiple tables and views and perform multiple grants in a single transaction.

  To execute a CREATE SCHEMA statement, LogicSQL executes each included statement. If all statements execute successfully, LogicSQL commits the transaction. If any statement results in an error, LogicSQL rolls back all the statements.

  Note: This statement does not necessarily create a schema. LogicSQL automatically creates a schema named after user when one creates a user (see CREATE USER). This statement, however, can be used by a user to create an additional schema.

  This statement lets you populate your schema with tables and views and grant privileges on those objects without having to issue multiple SQL statements in multiple transactions.

• Syntax

  CREATE SCHEMA [< schema name>] AUTHORIZATION [< user name>] { < CREATE TABLE STATEMENT> | < CREATE VIEW STATEMENT> | < GRANT STATEMENT> }

  or,

  CREATE SCHEMA < schema name>

  The default user name (when it is omitted) is the user who issues the statement. The default schema name (when is omitted) is the same as the user name.

  If the schema name is omitted, or the same as the user name, this statement will create all the database objects in a single transaction.

  Otherwise, the statement will first create a schema with the given name, if it has not been create before, and then create tables, views and grants privileges, all in a single transaction.

• Usage

  CREATE SCHEMA AUTHORIZATION blair CREATE TABLE sox (color VARCHAR2(10) PRIMARY KEY, quantity NUMBER) CREATE VIEW red_sox AS SELECT color, quantity FROM sox WHERE color = 'RED' GRANT select ON red_sox TO waites;

  This statement creates a schema named blair for the user Blair, creates the table sox, creates the view red_sox, and grants SELECT privilege on the red_sox view to the user waites.

2.3.11 CREATE SEQUENCE

• Purpose To create a sequence.

  A sequence is a database object from which multiple users may generate unique integers. One may use sequences to automatically generate primary key values.

• Prerequisites To create a sequence in your own schema, you must have CREATE SEQUENCE privilege. To create a sequence in another user's schema, you must have CREATE ANY SEQUENCE privilege.

• Syntax

  CREATE SEQUENCE < sequence name> [START WITH < signed integer>] [INCREMENT BY < signed integer>] [MINVALUE < signed integer>] [MAXVALUE < signed integer>] [CYCLE|NOCYLE]

• Using Sequences

  Sequence numbers can be used to automatically generate unique primary key values for your data, and they can also be used to coordinate the keys across multiple rows or tables.

  Values for a given sequence are automatically generated by LogicSQL routines, and only one sequence number can be generated at a time and sequences permit the simultaneous generation of multiple sequence numbers while guaranteeing that every sequence number is unique.

  When a sequence number is generated, the sequence is incremented, independent of the transaction committing or rolling back. If two users concurrently increment the same sequence, the sequence numbers each user acquires may have gaps because sequence numbers are being generated by the other user. One user can never acquire the sequence number generated by another user. Once a sequence value is generated by one user, that user can continue to access that value regardless of whether the sequence is incremented by another user.

  Sequence numbers are generated independently of tables, so the same sequence can be used for one or for multiple tables. It is possible that individual sequence numbers will appear to be skipped, because they were generated and used in a transaction that ultimately rolled back. Additionally, a single user may not realize that other users are drawing from the same sequence.

• Sequence Defaults

  You can create a sequence so that its values increment in one of following ways:

  • The sequence values increment without bound,
  • The sequence values increment to a predefined limit and then stop.
  • The sequence values increment to a predefined limit and then restart.

  To create a sequence that increments without bound, you shall not specify the MAXVALUE parameter. To create a sequence that increments to a predefined limit and then restart, one must specify the CYCLE option.

  Without specifying any option, you will create an ascending sequence that starts with 1 and increases by 1 with no upper limit. Specifying only INCREMENT BY -1 creates a descending sequence that starts with -1 and decreases with no lower limit.

• Accessing Sequence Values Once a sequence is created, you can access its values in SQL statements with the following pseudocolumn:
  • NEXTVAL: increments the sequence and returns the new value.

• Example 1

  CREATE SEQUENCE item_id START WITH 10 INCREMENT BY 2 MAXVALUE 2000000000; SELECT NEXTVAL FROM item_id;

  The CREATE SEQUENCE statement will create a sequence named item_id and the SELECT statement will return 10. Note that the absolute value of MAXVALUE and MINVALUE cannot exceed 2000000000.

• Example 2

  The following example demonstrates how to use a sequence to automatically generate unique ID for a table.

  CREATE SEQUENCE item_id START WITH 10 INCREMENT BY 2; CREATE TABLE items ( item_id int, item_name varchar(100), primary key (item_id) ); CREATE PROCEDURE insert_one_item(item_name varchar(100) ) declare sec integer; BEGIN SELECT NEXTVAL INTO :sec FROM item_id; INSERT INTO items VALUES( :sec, :item_name); END; CALL PROCEDURE insert_one_item('Nikon 5400'); CALL PROCEDURE insert_one_item('DELL Inspiration'); CALL PROCEDURE insert_one_item('Volswagon Rabit'); SELECT * FROM items;

  The execution of the above program populates items as follows.

  item_id item_name ---------------------------- 10 Nikon 5400 12 DELL Inspiration 14 Volswagon Rabit

2.3.12 CREATE TABLE

• Function Create a table.

• Syntax

  CREATE TABLE < table name> (
< column name> < data type> [{, < column name> < data type> }... ]
[,CONSTRAINT < constraint name> PRIMARY KEY ( < column name> [{, < column name> }... ] ) ] )

  CREATE TABLE < table name> AS < subquery>

• Usage

  CREATE TABLE take ( student varchar(30), course integer, grade varchar(1), CONSTRAINT take_primary_key PRIMARY KEY ( student, course ) )

  This will create a table take under the default schema.

  CREATE TABLE database.sarah.take ( student varchar(30), course integer, grade varchar(1), CONSTRAINT take_primary_key PRIMARY KEY ( student, course ) )

  This will create a table take under schema database.sarah.

  See Also: DROP TABLE

Note that one may specify foreign key constraints but they have yet been enforced.

Also note that one cannot create a table with a lob column without specifying its primary key.

2.3.13 CREATE TRIGGER

• Function

  Create and enable a database trigger which is a sequence of SQL statements associated with a table. LogicSQL automatically executes a trigger when specified conditions occur.

• Syntax

  <create trigger> ::= CREATE TRIGGER < trigger name> <BEFORE | AFTER | INSTEAD OF > < trigger event > ON < table name> [REFERENCING [OLD [AS] < correlation name>] [NEW [AS] < correlation name>]>] [FOR EACH <ROW | STATEMENT> ] < SQL procedure block>

• Syntax and Semantic Restrictions

  A CREATE TRIGGER must satisfy following restrictions.

  • The OLD reference does not apply to INSERT,
  • The NEW reference does not apply to DELETE,
  • An OLD/New reference variable is valid only if its value is uniquely determined by the event. For example, consider a variable, :OLD.employee_id referenced in a trigger that is defined on an update event with FOR EACH STATEMENT. This reference is valid only if the where clause in the update statement specifies the value for employee_id.
  • FOR EACH ROW orientation is supported for INSERT only.
  • An event can only trigger one trigger, even if there are more than one trigger related to the same event. LogicSQL will choose one trigger to be executed.
  • Instead of taking any measure to detect circular executions of triggers, LogicSQL limits the number of triggers triggered by the execution of one trigger. The limitation is specified by a configuration variable MaxTriggerCalls.

• Usage

  See Also: DROP TRIGGER

  To create a trigger for a table in a schema, including your own schema, you must have CREATE TRIGGER schema privilege on the schema, unless you have CREATE ANY TRIGGER system privilege. For an obvious reason, one shall not create a recursive trigger. LogicSQL does not provide a mechanism to detect recursive triggers. Instead, LogicSQL will limit the number of trigger operations allowed within a transaction. The limit is set to 1024, though this can be changed by modifying the configuration variable MaxTriggerCalls.

• Examples

  The following are a few examples demonstrated how to use triggers for various applications.

  DROP TABLE employee; CREATE TABLE employee( eid integer, ename varchar(100), salary int, pos varchar(100)); INSERT INTO employee VALUES ( 100, 'Peter Jenny', 100, 'New Anchor'); INSERT INTO employee VALUES ( 200, 'Sarah Smith', 300, 'President'); DROP TRIGGER salary_check; CREATE TRIGGER salary_check BEFORE INSERT ON employee DECLARE minsal integer; maxsal integer; BEGIN SELECT min(salary), max(salary) INTO :minsal, :maxsal FROM employee ; IF ( :new.salary < :minsal OR :new.salary > :maxsal) THEN raise_exception( 'salary vialation'); END IF; END; INSERT INTO employee VALUES ( 300, 'Peter Jenny', 10, 'New Anchor'); INSERT INTO employee VALUES ( 400, 'Sarah Smith', 400, 'President'); INSERT INTO employee VALUES ( 500, 'Tim Yuan', 200, 'Chair of the board');

  drop table ttt; create table ttt (tno int, tname varchar(100)); drop table sss; create table sss (sno int, sname varchar(100)); insert into sss values(10, 'just a test'); insert into sss values(20, 'be cool'); drop trigger circle; create trigger circle instead of update on sss FOR EACH STATEMENT declare x string; begin x = 'so what'; insert into ttt values(:old.sno, :new.sname); end; update sss set sname = 'no more test' where sno = 20; select * from ttt; select * from sss;

2.3.14 CREATE USER

• Purpose Use the CREATE USER statement to create a database user or an account through which one can log in to the database.

• Syntax

  CREATE USER [< catalog name>. ] < user name> [IDENTIFIED BY < password string>]

• Usage

  CREATE USER sarah

  This will create a user sarah with password sarah under the default database. When a user is created, the system will automatically create a schema with the same name as the user.

  CREATE USER other_database.sarah IDENTIFIED BY 'headf123dfa'

  This will create a user sarah under the database other_database with password headf123dfa

  Please advise new users to change the password immediately using ALTER USER.

  You must have the CRATE ANY USER system privilege to use this command.

  Note that a user is defined with respect to a given database, and consequently, the default schema for the user must be under the given database.

  See Also: DROP USER

2.3.15 CREATE VIEW

• Function Create a view, a logical table based on one or more tables or views. A view contains no data itself. The tables upon which a view is based are called base tables.

• Syntax

  CREATE VIEW < view name> [< column name> {, < column name> }] AS < subquery> [ WITH READ ONLY ]

• Usage

  CREATE VIEW database.sarah.all_tables AS SELECT t.table_catalog,t.table_schema,t.table_name, t.degree FROM system.definition_schema.tables as t, system.definition_schema.table_privileges as p, WHERE ( t.table_catalog = database AND t.table_schema = sarah ) OR ( p.grantee = sarah AND p.table_catalog = t.table_catalog AND p.table_schema = t.table_schema AND p.table_name = t.table_name ) WITH READ ONLY

  This will create a view under schema sarah that contains information of all the tables accessible to sarah.

  To create a view in a schema, including your own schema, you must have CREATE VIEW schema privilege on the schema, unless you have CREATE ANY VIEW system privilege.

A view created using a query expression (QE) is updatable if all of the following conditions are satisfied.

• QE must be a simple query expression without (a) any relational operators, such as UNION, EXCEPT, (b) it shall not contain any aggregate functions and GROUP BY, HAVING clauses.
• The SELECT clause in QE must be a list of column references, that is, direct references to columns of the tables specified in the from clause.

• For the time being, QE cannot be a join of two or more tables, and the view is defined without CHECK OPTION.

2.3.16 DECLARE CURSOR

• Function Define a session cursor

• Syntax

  DECLARE < cursor name> CURSOR
FOR < subquery>
[ < order by clause> ]
[FOR <READ ONLY | UPDATE> ]

• Notes
  • All cursors in LogicSQL are scrollable, that is, the fetchh operation of a cursor can specify either NEXT, PRIOR, or any row through ABSOLUTE. See the FETCH statement
  • For now, LogicSQL supports only INSENSITIVE cursor, that is, any significant change to the underlying tables is not visible.
  • LogicSQL supports updatable cursors.

• Usage

  DECLARE CURSOR current_student FOR SELECT * FROM students FOR READ ONLY;

  This will declare a cusor that can be used later by OPEN CURSOR, CLOSE CURSOR, and FETCH statements.

A cursor can be classfified by one of the following properties.

• A cursor is holdable or not. Various properties specified for a cursor determines whether or not it is a holdable cursor.
• A cursor is in eihter an open or close state.
• A cursor is updatable or not updatable. A cursor is not updatable if the table it identifies is not updatable or INSENSITIVE is specified for the cursor. Otherwise, the cursor is updatable.

• Example 1

  The following example demonstrates how to use cursors in a stored procedure.

  /* * This example demonstrates how to use cursors in a stored procedure. */ select 'Demo Cursors in Procedures ' from dual; drop table student; drop procedure get_top_gpa; CREATE TABLE student ( sid int, name varchar(64), major varchar(10), gpa numeric(5, 2), primary key(sid) ); /* * This procedure can be used to obtain the average gpa for the specified number * of students. * */ create procedure get_top_gpa (no_sid integer, gpa out numeric) declare i integer; gpa_sum numeric; one_gpa numeric; begin declare gpas cursor for select gpa from student order by gpa for read only; open gpas; gpa_sum = 0; i = 0; sqlstate = 1; while ( :i < :no_sid and sqlstate = 1 ) do if i = 0 then fetch first gpas into :one_gpa; else fetch next gpas into :one_gpa; end if; if (sqlstate = 1) then gpa_sum = one_gpa + gpa_sum; i = i+1; end if; end while; if ( i>0 ) then :gpa = gpa_sum / i; else :gpa = 0; end if; end; insert into student values( 10, 'susan', 'cs', 3.6 ); insert into student values( 20, 'peter', 'ee', 2.8 ); insert into student values( 30, 'sarah', 'md', 3.8 ); insert into student values( 40, 'tom', 'ce', 1.8 ); call procedure get_top_gpa(2);

2.3.17 DELETE

• Function Delete one or more tuples from the given table.

• Syntax

  DELETE FROM < table name> [WHERE < search condition>]

  DELETE FROM < table name> WHERE CURRENT OF < cursor name>

  DELETE < table name> [WHERE < column name> IN ( < subquery>] )

• Usage

  DELETE FROM sarah.take where course = 391

  This will delete all tuples from sarah.take whose course = 391.

2.3.18 DROP DATABASE

• Function Remove a database (catalog) from the database.

  See Also: CREATE DATABASE

• Syntax

  DROP CATALOG < catalog name>

• Usage

  DROP CATALOG her_database

  This will drop the database named her_database and all the data objects stored in the database.

  See Also: CREATE DATABASE One needs CREATE ANY CATALOG privilege to create and/or drop a catalog.

2.3.19 DROP INDEX

• Function Remove an index from the database.

  See Also: CREATE INDEX

• Syntax

  DROP INDEX < index name>

• Usage

  DROP INDEX customer_name_index

  This will drop the index

  See Also: CREATE INDEX

One can not drop an index without CREATE INDEX schema privilege on the schema or CREATE INDEX system privilege.

2.3.20 DROP PROCEDURE

• Function Remove a stored procedure from the database.
• Syntax DROP PROCEDURE < procedure name>

  One needs the CREATE PROCEDURE schema privilege or CREATE ANY PROCEDURE system privilege to drop a procedure.

See Also: CREATE PROCEDURE

2.3.21 DROP ROLE

• Function Remove a role from the database. When a role is dropped, LogicSQL revokes it from all the users and roles to whom it has been granted and removes it from the database.

• Syntax

  DROP ROLE < role name>

• Usage

  DROP ROLE regular_user

  See Also: CREATE ROLE, GRANT ROLE, REVOKE ROLE.

• Prerequisites You must have been granted the role with the ADMIN OPTION or you must have DROP ANY ROLE system privilege.

2.3.22 DROP SCHEMA

• Function Remove a schema (not a user) from the database.

  When a schema is dropped, all the database objects in the schema will also be dropped.

  See Also: CREATE SCHEMA

• Syntax

  DROP SCHEMA < schema name>

• Usage

  DROP SCHEMA database.sarah

  See Also: CREATE USER

Only a system user (system ) is authorized to use this command.

2.3.23 DROP SEQUENCE

• Function Remove an existing sequence from the database.

• Syntax

  DROP SEQUENCE < sequence name>

• Usage

  DROP SEQUENCE employee_id_sequence

  See Also: CREATE SEQUENCE.

• Prerequisites You must have DROP SEQUENCE privilege to drop a sequence in your own schema, and DROP ANY SEQUENCE privilege to drop a sequence in another user's schema.

2.3.24 DROP TABLE

• Function Remove a user table from the database.

  See Also: CREATE TABLE

• Syntax

  DROP TABLE < table name>

• Usage

  DROP TABLE database.sarah.testing

  This will drop table testing owned by the schema database.sarah.

  Note that when a table is dropped, all indexed created on the table as well as all triggers that are triggered by an event on the table will also be dropped.

  Only the owner of the table (and a system user) may use this command to remove the specified table.

  See Also: CREATE TABLE.

2.3.25 DROP TRIGGER

• Function

  Remove a database trigger from the database.

• Syntax

  DROP TRIGGER < trigger name>

• Usage

  DROP TRIGGER personnel_dept.salary_increase_trigger

  This will drop the trigger owned by the schema personnel_dept.

  See Also: CREATE TRIGGER. Only the owner of the table (and a system user) may user this command to remove the specified trigger.

2.3.26 DROP USER

• Function Remove a user (schema) from the database.

  When a user is dropped, the corresponding schema and all the database objects in the schema will also be dropped.

  See Also: CREATE USER

• Syntax

  DROP USER < user name>

• Usage

  DROP USER database.sarah

  See Also: CREATE USER

You must have the DROP ANY USER system privilege to use this command.

2.3.27 DROP VIEW

• Function Remove a view from the database. You can change the definition of a view by dropping and re-creating it.

  See Also: CREATE VIEW

• Syntax

  DROP VIEW < view name>

• Usage

  DROP VIEW database.yuan.all_tables

  One can not drop a view without CREATE VIEW on the schema or CREATE ANY VIEW system privilege.

2.3.28 EXECUTE PROCEDURE

• Function Execute a given stored procedure

• Prerequisites

  To execute a procedure in your own schema, you must have EXECUTE PROCEDURE schema privilege. To create a procedure in another schema, you must have EXECUTE ANY PROCEDURE system privilege.

  Any one can execute a procedure in his/her own schema, but cannot execute a procedure in another schema, unless an explicit authorization of EXECUTE on the procedure is granted.

• Syntax

  <execute procedure> ::= EXECUTE PROCEDURE < procedure name> < parameter list>

  The paramter list must match the list of all non-out paramers, in the same order, specified in the procedure definition.

• Keywords and Explanations
• Usage

  After a stored procedure has been created, it can be called from any environment from which an SQL statement can be issued.

  The value of all the output parameters will be returned by the LogicSQL server in the format of

  0000008234 PROCEDURE OUTPUT 2|x|integere|0|y|string|be cool|

  where the first intger following OUTPUT stands for the number of output parameters, '|' stands for SEPARATOR, and the output value for each out-paramer will be specified by three items, i.e., the variable name, its data type, and its value. If the value of a variable is undefined, then a variable of the form $VAR(0) will be returned.

  For example, the above output represents the fact that the procedure has two out-variables, x of the integer type and y of the string type, and the returned values for x and y are 0 and 'be cool' respectively.

  See Also: CREATE PROCEDURE, DROP PROCEDURE.

2.3.29 FETCH

• Function Fetch a specified number of rows from the result set pointed by the given cursor.

• Syntax

  FETCH [< fetch orientation>] [FROM] < cursor name> [< target specification>]

  <fetch orientation> ::= NEXT|PRIOR|FIRST|LAST|CURRENT| <ABSOLUTE|RELATIVE>['-']< integer>]

  <target specification> ::= INTO < variable list>

• Syntax Rules
  • If the <fetch orientation> is omitted, then NEXT is implicit.
  • If the <target specification> is not specified, LogicSQL returns a specified rows to the client in the same way as for the SELECT statement.
• Usage

  Note that for ABSOLUTE and RELTIVE, the integer representing the distance is required.

  FETCH NEXT 10 student_cursor;

  This will fetch 10 rows, starting from the current row, from the result set pointed by the given cursor. After each fetch, the cursor advances to the next row (or, next specified number of rows) in the result set.

  The result set returned by the FETCH statement will be in the same format as the SELECT statement.

  The FETCH ... INTO statement will fetch the first row into the given variable list, one row at a time, and the fetched column values in the result set must correspond to the number of variables and their data types in the variable list. Of course, the FETCH ... INTO statement will also advance the cursor to point into the next row in the result set.

  FETCH student_cursor INTO :s_id, s_name, s_gpa;

  This will fetch the first row of the result set pointed by the given cursor and store the values into the corresponding variables.

  The FETCH ... INTO statement can only be used by stored procedure, as specified by CREATE PROCEDURE. See the fetch-procedure example.

If the cursor is not in the open state then an exception is raise: invali cursor state.

See also DECLARE CURSOR, OPEN CURSOR, and CLOSE CURSOR.

2.3.30 GRANT PRIVILEGE

• Function Grant access privileges to a list of users

• Syntax

  GRANT < privileges > TO < grantee> [ { , < grantee> }... ] [ WITH GRANT OPTION ]

• Usage

  GRANT SELECT, UPDATE on my_database.your_schema.her_table TO PUBLIC

  This will grant the SELECT and UPDATE privileges on table my_database.your_schema.her_table to all users of the system, without the grant option.

  According to its scope, privileges can be classified as

  • system privileges : valid for all schemas, such as create any view, create any index, and all schema privileges;
  • schema privileges: valid for a specified schema, with the default as the grantees own schema, such as create view and create index;
  • object privileges: valid for the specified objects, such as select on her_account.

  For obvious reasons, the following object privileges are granted to all users on all objects in their own schema:

  select, insert, delete, update, create table.

  See also REVOKE PRIVILEGE.

• Prerequisites To grant an object or schema privilege, you must own the object or schema or the owner of the object or schema must have granted you the privileges with the GRANT OPTION. This rule applies to users with any privileges.

  To grant a system privilege, you must either have been granted the system privilege with the GRANT OPTION or have been granted GRANT ANY PRIVILEGE system privilege.

2.3.31 GRANT ROLE

• Function To grant roles to users and roles

• Syntax

  {GRANT < role name > [{, < role name> } ... ] TO < grantee> [ { , < grantee> }... ] [ WITH ADMIN OPTION ] }

• Usage

  Assume we are going to create a role called sys_dba and will grant all schema privileges to {sys_dba}, and then grant this role to a user my_assistant. Logon as system, one can use the following sequence of SQL-statements. CREATE USER my_assistant; CREATE ROLE sys_dba; GRANT ALL SCHEMA PRIVILEGES TO sys_dba; GRANT sys_dba TO my_assistant; This will accomplish the task. After logon to LogicSQL, my_assistant can then do almost anything about this database. However, my_assistant can choose to disable this role by issuing SET ROLE ALL EXCEPT sys_dba; This will disable the role sys_dba.

  This will grant the role named regular_user to john and sarah without the admin option.

• Prerequisites You must either have been granted the role with the ADMIN OPTION or have been granted GRANT ANY ROLE system privilege. See also Security Model.

  See Also: DROP ROLE, GRANT ROLE, REVOKE ROLE.

2.3.32 INSERT

• Function Insert a tuple or a set of tuples as specified by a subquery in to the given table.

• Syntax

  INSERT INTO < table name> [(< column list>)] VALUES (< literal value> [{, < literal value>}] )

  or,

  INSERT INTO < table name> [(< column list>)] < subquery>

• General Rules

  The INSERTION BY QUERY, as specified by the second definition above, may involve a large number of disk operations due to the size of the source tables in the subquery.

  To avoid seriously slow down the system performance, it should be used in an atomic transaction, unless the size of the result set of the subquery is less than the MaxTransactionSize, a configuration variable whose value can be modified.

  LogicSQL will rollback the transaction and return an error message if the number of rows to be inserted in INSERTION BY QUERY within an active transaction exceeds MaxTransactionSize.

• Usage

  INSERT INTO sarah.take VALUES ( 'Peter', 391, 'B' )

  This will insert the tuple into sarah.take.

  Consider a table created with the following statement

  CREATE TABLE schedule (sno int default 100 not null, sname varchar(100))

  Then the following INSERT statement

  INSERT INTO schedule (sname) VALUES( 'nothing to do' )

  will insert a new tuple <100, 'nothing to do'> into the table.

2.3.33 OPEN CURSOR

• Function Open a session cursor

• Syntax

  OPEN < cursor name>

• Usage

  OPEN current_student;

  This will open the given cusor and point the current row before the first row of the underline SELECT statement.

See also DECLARE CURSOR, CLOSE CURSOR, and FETCH statements.

2.3.34 REVOKE PRIVILEGE

• Function Revoke access privileges from a list of users

• Syntax

  REVOKE < privileges > FROM < grantee> [ { , < grantee> }... ]

• Usage

  REVOKE SELECT, UPDATE on my_database.your_schema.her_table FROM peter

  This will revoke the SELECT and UPDATE privileges on table my_database.your_schema.her_table from peter.

  Note that the revoke statement will revoke all privileges that implied by the specified privileges from all the specified grantees, and revoke all privileges that imply the specified privileges.

  For example, revoke all schema privileges from someone will revoke all privileges granted to someone.

  Only the owner of the objects, or those who originally granted the privilege may revoke the privilege. All default privileges on the owners schema cannot be revoked.

  See also GRANT PRIVILEGE.

2.3.35 REVOKE ROLE

• Function Revoke role authorizations from a list of uses and roles

• Syntax

  REVOKE [ADMIN OPTION FOR] < role name> [{, < role name>} ...]
FROM < grantee> [ { , < grantee> }... ]
[ GRANTED BY <CURRENT_USER|CURRENT_ROLE>]

• Usage

  REVOKE students FROM peter This will revoke a role named students from peter.

• Prerequisites

  You must have been granted the role with the ADMIN OPTION or you must have DROP ANY ROLE system privileges.

  See Also: CREATE ROLE, DROP ROLE, GRANT ROLE, GRANT PRIVILEGE.

2.3.36 ROLLBACK

• Syntax

  ROLLBACK

• Description

  Rolls back the current transaction. All the updates made by the current transaction are discarded.

  Warning: This may cause cascading roll backs: All the transactions that are affected by a rolled back transaction will be forced to roll back.

• Usage

  ROLLBACK

  Note

  Use COMMIT to successfully terminate a transaction. See also SET TRANSACTION and COMMIT.

2.3.37 SET ROLE

• Function
• Purpose

  To enable and disable roles for your current session.

• Prerequisites You must already have been granted the roles to be enabled in the SET ROLE statement. See also Privilege Domains

• Syntax

  SET ROLE < role list> | ALL [ EXCEPT < role list> ] | NONE >

  <role list> ::= < role name> [ { < role name> ... } ]

• Usage
  • SET ROLE < role list> enables the given list of roles for the current session. Any roles not listed are disabled for the current session.

  • SET ROLE NONE disables all roles for the current session.

  • SET ROLE ALL enables all roles granted to you explicitly or through other roles for the current session.
  • SET ROLE ALL EXCEPT < role list> enables all roles granted to you for the current session, except those listed in the EXCEPT clause.

    Note that roles listed in the EXCEPT clause must be roles granted directly to you; they cannot be roles granted to you through other roles. Any roles, however, granted to you only through roles listed in the EXCEPT clause will not be enabled.

  Note that the number of roles to be enabled shall not exceed the limit specified by the configuration parameter MaxEnabledRoles for the current session.

• Examples

  SET ROLE regular_student

• Related Topics

  Security Model
CREATE ROLE
GRANT ROLE

2.3.38 SELECT and Subquery

• Function Retrieve data from one or more tables and/or views.
• Prerequisites One must have SELECT privileges on the table/view unless it is in one's own schema.
• Syntax

  <query> ::= subquery [< order by clause>]

  A subquery specifies a table that can be retrieved from tables and/or views. It is used to specify the SELECT statement as well as many other statements, such as CREATE VIEW, CREATE TABLE, and INSERT.

  <subquery> ::=

SELECT [< select quantifier>] [<DISTINCT|ALL> ] < * | < derived column> [{,< derived column> }] >
FROM < table reference> [ , < table reference> ]
[WHERE < search condition>]
[GROUP BY < item reference> {, < item reference> }]
[HAVING < search condition >]
[ <UNION|UNION ALL|EXCEPT|MINUS|INTERSECT> ( subquery ) ]


  <order by clause> ::= ORDER BY < value expression> [ASC|DESC] [{,< value expression>[ASC|DESC] }...]

  <select quantifier> ::= [TOP < integer>] [LIMIT TO < integer>]

• Order By

  An ORDER BY clause is used to order the set of all rows returned by a query. An ORDER BY clause may specify multiple expressions. LogicSQL first sorts the rows according to their values on the first expression, and then sorts the rows with the same values on the first expression according to their values on the second expression, and so on. An expression can be either an expression appearing in the SELECT clause, or a relative position that the corresponding expression holds in the SELECT clause.

  Using the position is useful for you need not repeat the same expression in the order by clause. Also, one must use positions, rather than expressions in the query that involves relational algebra operators, such as UNION, EXCEPT, and INTERSECT.

  Note that, for now, the order by clause cannot use correlation names for specifying the columns to be sorted.

• Select Quantifier

  The SELECT QUANTIFIER clause specifies the number of rows returned by a query, and the number of rows that processed by the query processor of LogicSQL. This clause, however, has no effect on subqueries used in both CREATE VIEW and CREATE TABLE.

  Example 1 To select the top 10 rows from STUDENT, one issues SELECT TOP 10 * FROM student

  This will return the first 10 rows of the query to the client. It will return all rows if the number of rows is less than or equal to 10.

  Using the top clause together with the order by clause, one can select the first N rows in a sorted result table.

  Example 2 The following query selects the top 5 students according to their GPA. SELECT TOP 5 * FROM student ORDER BY gpa DESC

  Note that the TOP clause works properly only in the SELECT statement, but not in other uses of subquery statements because the ORDER BY clause is not supported for these applications. With the order by clause, the rows returned are not necessarily the top ones.

  The LIMIT TO clause specified the max number of rows LogicSQL' may consider during the query evaluation process. The default number of rows considered is 100000, and this can be changed by the configuration variable TUPLE_LIMIT.

  One may change this limit for a particular query by adding LIMIT TO 100, for example, in the SELECT statement.

  Example 3 The following query will select at most 100 rows in each of its evaluation steps, and thus the number of rows returned will not exceed 100. SELECT LIMIT TO 100 * FROM student

  Note that the following constraints applied to the SELECT statement:

  • All items in the group by list must be < item reference>, i.e, columns. Of source, it cannot be a column with an aggregate function, and all the none-aggregate columns must be in the group by list.
  • All aggregate functions are applied only to < item reference>, i.e. columns, without DISTINCT.
  • An order by specification is specified using (Value_Exp, Order_Specification), where Value_Exp is either an integer referencing one column in the result set or a value expression appearing in the SELECT clause; while Order_Specification can be either ASC or DESC with the default being ASC.

  The labels of all the derived columns are column names in the answer tables. You can specify the label for a derived column using [AS] <correlation name>. Otherwise, LogicSQL will use the default label for the derived column.

  If a derived column is a column of a table in the from clause, then its default label will the column name. The default name of the derived column that is an expression will be constructed from the given expression.

  The length of a label in LogicSQL is limited to the configuration parameter MAX_LABEL_LENGTH, and thus one has to specify a label for any derived column of an expression if the length of the expression exceeds this limit.

  [TOP <integer>] is used to specify the number of rows to be returned.

• Nested Queries

  LogicSQL supports the subqueries nested in the both where and having clauses , as specified above.

  The following example demonstrates how it works.

  Example 4 drop table ttt; create table ttt (tno int, tname varchar(100)); insert into ttt values(100, 'be cool'); insert into ttt values(200, 'so what'); insert into ttt values(300, 'are you crazy'); insert into ttt values(400, 'just say no'); drop table sss; create table sss (sno int, sname varchar(100)); insert into sss values(100, 'so be it'); insert into sss values(100, 'be cool'); insert into sss values(200, 'so what'); insert into sss values(300, 'are you crazy'); select * from ttt; select * from sss; select * from ttt where tno in (select sno from sss where sname = 'be cool' or sno = 300) or tname = 'just say no'; select * from ttt where tno in (select sno from sss where sname = 'be cool' or sno = 300 ); select * from ttt where tno in ( select sno from sss where tname = 'so what' ); select * from ttt where tno in ( select sno from sss where tname = sname ); select * from ttt where tno in (select sno from sss where sno = 100) or tno in (select sno from sss where sname = 'are you crazy' );

• Joins

  A join is a query that combines rows from two or more tables and/or views. LogicSQL performs a join whenever multiple tables appear in the query's FROM clause.

  Most join queries contain WHERE clause conditions that compare two columns, each from a different table. Such a condition is called a join condition .

  In addition to join conditions, the WHERE clause of a join query can also contain other conditions that refer to columns of only one table. These conditions can further restrict the rows returned by the join query.

  Examle 5 Consider a query to list all students' name for each course with at lease one student registered. The query can be expressed by the following join operation.

  SELECT r.cid, s.name FROM registration r, student s WHERE r.sid = s.sid

  Example 6 Now consider the query to list the number of all regitered students for each course, and the following SQL query.

  SELECT cid, count(sid) FROM registration GROUP BY cid The query result is shown below: cid count(sid) --------------------- BS499 1 CS291 2 MA101 1

  This SQL statement, however, fails to list the number of regitered students for any courses with no student regitered yet.

• Outer Joins

  The outer join extends the result of a simple join. An outer join returns all rows that satisfy the join condition and those rows from one table for which no rows from the other satisfy the join condition. Such rows are not returned by a simple join.

  Using outer join, we are able to expression the privous query.

  Example 7 Now conider the query to list the number of all regitered students for each course, and the following SQL query.

  SELECT c.cid, count(r.sid) FROM registration r left join course c on r.cid = c.cid GROUP BY c.cid; c.cid count(r.sid) ----------------------- AR100 0 BS499 1 CS291 2 MA101 1

  Example 8 The following uses the right join to list the number of all regitered students for each course, and the following SQL query.

  SELECT c.cid, count(r.sid) FROM course c right join registration r on r.cid = c.cid GROUP BY c.cid; c.cid count(r.sid) ----------------------- AR100 0 BS499 1 CS291 2 MA101 1

  Example 9 The following demonstrates the use of outer joins in nested queies.

  SELECT * FROM course WHERE cid in ( SELECT c.cid FROM course c right join registration r on r.cid = c.cid ); cid title description --------------------------------------------------------- AR100 Cartoon Drawing A commic book BS499 Marketing How to make quick bucks CS291 Introduction to DBMS first database course MA101 Calculus An easy course

  Example 10 The following example shows that the natural join can be specified without giving the join condition, as long as both tables have one and exact one attributes wth the same name.

  SELECT student.sid, name, cid, grade FROM student JOIN registration; student.sid name cid grade -------------------------------------- 12001 Sarah CS291 12001 Sarah MA101 D 54321 Bob BS499 B 54321 Bob CS291 A

  There are two approaches to expression an outer join in LogicSQL.

  • SQL99 Style

    The following example uses an outer join to extend the join of students and registration.

    SELECT s.name, c.title FROM student s left outer join registration r on s.sid = r.sid, course c WHERE r.cid = c.cid

    This query returns all course titles with their students names, even if a course has no registered students.

  • Oracle Style Note that the Oracle style has not yet been implemented!.

    The following example demonstrates a different approach to express the above query.

    SELECT s.name, c.title FROM student s right, registration r, course c WHERE s.sid (+) = r.sid and r.cid = c.cid

  To write a query that performs an outer join of tables A and B and returns all rows from A, apply the outer join operator (+) to all columns of B in the join condition. For all rows in A that have no matching rows in B, LogicSQL returns NULL for any select list expressions containing columns of B.

  This is the basic syntax of an outer join of two tables from Oracle.

  Outer join queries are subject to the following rules and restrictions:

  • The (+) operator can appear only in the WHERE clause, not in the select list, and can be applied only to a column of a table or view.
  • If A and B are joined by multiple join conditions, the (+) operator must be used in all of these conditions.
  • The (+) operator can be applied only to a column, not to an arbitrary expression. However, an arbitrary expression can contain a column marked with the (+) operator.
  • A condition containing the (+) operator cannot be combined with another condition using the OR logical operator.
  • A condition cannot use the IN comparison operator to compare with another expression a column marked with the (+) operator.
  • A condition cannot compare with a subquery any column marked with the (+) operator.

  Example Consider a simple table created by the following statement: create table ttt(tno int, tname varchar(100)) The following are all valid SELECT statements select * from ttt; select count(*) from ttt; select avg(tno), max(tno) from ttt; select 'just a ' || 'test' , report.tno * 2 from ttt as report; select user from ttt; select tno, count(tname) from ttt group by tno; select tname from ttt order by tname asc; select * from ttt where tno = 10 + 10 or tname = 'just a test' and tno = 100; select * from ttt where not tno = 20; select * from ttt where tno <> 20; select * from ttt t1, ttt t2 where t1.tno = 20; select * from ttt t1, ttt t2 where t1.tno < t2.tno + 30; select * from ttt t1, ttt t2 where t1.tno = t2.tno + 130 or t1.tno = 20; select tno from ttt group by tno order by count(tname); select tno from ttt group by tno having count(tname) > 1; select tno * 2 from ttt; select tno , count(tname) from ttt group by tno; select t1.tno + t2.tno, count(t1.tname), count(t2.tname) from ttt t1, ttt t2 group by t1.tno,t2.tno; select t1.tno + t2.tno, count(t1.tname), count(t2.tname) from ttt t1, ttt t2 group by t1.tno,t2.tno having count(t1.tname) = count(t2.tname); select t1.tno + t2.tno, t2.tno from ttt t1, ttt t2; select tname || tname from ttt; select t1.tno + 2 * t2.tno from ttt t1, ttt t2; select tno, user from ttt; select t1.tno + 2 * t2.tno from ttt t1, ttt t2; select * from ttt t1, ttt t2 where t1.tno + t2.tno = 200; select t1.tno + t2.tno, user from ttt t1, ttt t2 where t1.tno + t2.tno = 200; select * from ttt where tno in (100, 200) and tno = 100; select * from ttt where tno in (tno * 2 -200, 150 ) or tname = null; select * from ttt where tno in (tno * 2 -200, 150 ); select 'just a ' || 'test' as title, report.tno * 2 from ttt as report; select tno as numbers, count(tname) from ttt group by tno; select * from ttt where tname in ('one more test', 'nothing much'); select * from ttt where tname not in ('one more' || ' test', 'nothing much'); select * from ttt where tname not in (null, so what'); select * from ttt where tname not like 'one more_'; select * from ttt where tname like 'one more_'; select * from ttt where tno between 10 and 50 or tno in (20, 150); select * from ttt where tname between 'abc' and so s'; select * from ttt where tname between 'nothing' and so'; select * from ttt order by tname desc; select * from ttt order by 2 desc; select tno, tname from ttt order by tno desc; select tname, sum(tno) from ttt group by tname order by sum(tno); The following queries are not supported: select avg( distinct tno ) from ttt; select sum(distinct tno) from ttt; select avg( sum(tno) * 2 ) from ttt;

2.3.39 SET TRANSACTION

• Syntax SET TRANSACTION isolation level serializable

• Usage SET TRANSACTION isolation level serializable

• Description

  This will start a new transaction.

• Note

  See also ROLLBACK and COMMIT.

2.3.40 SET SESSION USER

• Syntax SET SESSION USER

• Description

  Note implemented yet.

2.3.41 SET AUTO_COMMIT

• Syntax SET AUTO COMMIT <ON|OFF>

• Description

  To set on (or off) the auto_commit state for the current client.

  A clients auto_commit status is on if each statement issued from the client is considered as an atomic transaction and thus the system will automatically commit or rollback the statement, unless a transaction has been previously set up by the client using the SET TRANSACTION statement.

  Note that the default auto_commit state for clients is ON.

2.3.42 UPDATE

• Function Update a given table

• Syntax

  UPDATE < table name> SET < set clause list> [WHERE < search condition>]

  UPDATE < table name> SET < set clause list> [WHERE CURRENT OF < cursor name>]

• Usage

  UPDATE sarah.take SET grade = 'B' where student = 'Peter' and course = 391

  This will update Peters grade for 391.

2.4 System Utility Commands

System Utility statements are LogicSQL commands used by a system user to perform system operations, such as RECOVER DATABASE and SHUTDOWN the server. It is also called System Utility Commands.

2.4.1 SET CHECKPOINT		Set the check point
2.4.2 SET ADMISSION		Set admission control level
2.4.3 RECORD SNAPSHOT		Record the snapshot in the log system
2.4.4 RECOVER DATABASE		Recover the database after crash
2.4.5 SHUTDOWN		Shutdown the database server

2.4.1 SET CHECKPOINT

• Function Set the checkpoint.
  

• Syntax SET CHECKPOINT

• Usage SET CHECKPOINT

• Description

  This will set the checkpoint. That is, the system will first force the disk updates, and then record the checkpoint on the backup log.

• Note

  Only a system user can issue this statement.

2.4.2 SET ADMISSION

• Function
  

  Set the admission control level. That is, to determine which requests from clients will be rejected and which ones will be processed.

• Syntax
  

  SET ADMISSION LEVEL < admission control level>

  <admission control level> ::= OPEN | EXISTING CLIENT | WITHIN TRANSACTION | BLOCK

• Usage

  The command used by a system user to set the control level of the admission of client requests.

  1. OPEN all requests are admitted to the system.
  2. EXISTING CLIENT only requests from existing clients are admitted.
  3. WITHIN TRANSACTION only requests that are within the current transaction are admitted. That means, neither a request for new connection nor a request that starting a new transaction is admissible.
  4. BLOCK all requests will be rejected.

  For example, SET ADMISSION LEVEL WITHIN TRANSACTION will process only requests from an active transaction, and thus reject all requests from new transactions. This will allow the system to finish all current active transactions but stop all other requests.

Note that (1) a system user does not subject to the admission control, and (2) all requests will be rejected if it is not allowed at the current admission control.

2.4.3 RECORD SNAPSHOT

• Function
  

  Record SNAPSHOT in the log system to mark the starting point for crash recovery.

• Syntax
  

  RECORD SNAPSHOT

• Usage

  The command is used by a system user to mark SNAPSHOT in the log system. It should used only after a snapshot is secured in the backup media since the crash recovery cannot go beyond the mark.

Note that (1) a system user does not subject to the admission control, and (2) all requests will be rejected if it is not allowed at the current admission control.

2.4.4 RECOVER DATABASE

• Function Recover the database after crash
• Syntax

  RECOVER DATABASE [SINCE < LAST SNAPSHOT | BEGINNING > ]

• Description

  Recover the database after crash. A standard recovery process is to redo all updates of committed transactions. The database first scans the log system to construct the list of all committed transactions that need to be redone, and then execute all updates of the transactions in the list.

  There are one option for this command, which to specifies the type of recovering. It is normal recovery by default, and catastrophic recovering if SINCE <LAST SNAPSHOT|BEGINNING> is specified.

  The catastrophic recovery will be based on all the log records upto the last snapshot if SINCE LAST SNAPSHOT is specified and all the log records in the log system if SINCE BEGINNING is specified.

  See Database Recovery for details.

• Usage
  • RECOVER DATABASE recovers the database from the (default) local log system with normal recovery.
  • RECOVER DATABASE SINCE LAST SNAPSHOT recovers the database from the log system, upto the last snapshot, with catastrophic recovery.
  • RECOVER DATABASE SINCE BEGINNING recovers the database from the log system with catastrophic recovery.

• Note

  Only the system user can use this command.

  FROM STANDBY DB has yet to be implemented. (It is better to copy the log files from the site in which the standby database is located to the local log directory directly.)

2.4.5 SHUTDOWN

• Function Shutdown the database server

• Syntax

  SHUTDOWN

• Usage

  SHUTDOWN

  See also shutdown_server.

  One nees SHUTDOWN SERVER or ALL SCHEMA PRIVILEGES to use this statement.

2.5 XML Commands

( It is currently under development. )

2.5.1 SELECT RETURN		Search the SQL database and return a table
2.5.2 FOR WHERE
2.5.3 FOR WHERE RETURN		Search the XML database and return an XML document

2.5.1 SELECT RETURN

2.5.2 FOR WHERE

2.5.3 FOR WHERE RETURN

<constructor> ::= < element constructor> | < xml comment> | < xml processing instruction> | < c data section> <element constructor> ::= "<" < q name> < attribute list> "/>" | "<" < q name> < attribute list> ">" {< element content>} "</" < q name> ">" <element content> ::= < char> | <"{{"> | <"}}"> | < element constructor> | < enclosed expr> | < c data section> | < char ref> | < predefined entity ref> | < xml comment> | < xml processing instruction> <xml comment> ::= "<!--" < string> "-->" <xml processing instruction> ::= "<?" < pi target> < string> "?>" <c data section> ::= "<![CDATA[" < string> "]]>" <pi target> ::= < identifier> <predefined entity ref> ::= "&" < IT | GT | AMP | QUOT | APOS > ";" <char ref> ::= "&#"< integer> | "x"< hex integer> <enclosed expr> ::= { < expr sequence> } <expr sequence> ::= { < expr> {, < expr> } <expr> ::= Xquery expression <q name> ::= [:] < identifier> { : < identifier>} <attribute list> ::= { q name "=" < attribute value> } <attribute value> ::= <string> ::= {< char>} <char> ::= a specified subset of ASCII letters

2.6 Security Model

The discretionary security model governs the access of uses to the information on the basis of the authorization identity and of rules that specify, for each user and object in the system, the types of access the user is allowed for the object.

Assume, for instance, that the database contains a user with the identifier one_hiker, and a table named credit_card under the schema banks.

Without an explicit authorization from the boss as follows:

GRANT SELECT ON banks.credit_cars TO one_hiker,

one_hiker cannot examine the table.

In this example, one_hiker is an authorization identify, banks.credit_card is a database object, SELECT ON is an action. The above statement then grants the access rule, called privilege, that authorizes the selection on the specified table to the user.

LogicSQL provides a set of statements that can be used to grant and revoke authorization for any user to access any database object.

2.6.1 Authorization Identity

All accesses to LogicSQL are through SQL-sessions, and thus the security control is through the restrictions on the accesses of SQL-sessions.

An SQL-session spans the execution of a sequence of consecutive SQL-statements invoked by a single user from a single SQL-agent.

The authorization of an SQL-session is then identified by its authorization identity.

Two types of authorization identity are classified:

• user identifier
• role name

User Identifier

Each SQL-session has a user identifier called the SQL-session user identifier. When an SQL-session is initiated by a connect statement through any LogicSQL interface, such as SQL*Face or sql_connection in the C-Interface, LogicSQL set the SQL-session user identifier to the user specified in the connection statement.

The SQL-session user identifier is also referenced as the current user (CURRENT_USER) or the session user (SESSION_USER). The value of the current user can be determined by current_user or session_user. It can also be queries by using

SELECT CURRENT_USER FROM INFORMATION_SCHEMA.dual.

Role Name

A role is identified by a role name, and an SQL-session has a set of roles, called the current roles. A role can be granted to a user or a role by a GRANT PRIVILEGE statement. When an SQL-session is initiated, LogicSQL set the default set of current roles as the set of all roles that have been granted directly to the current user.

The set of all roles that have been granted directly to the current user and the set of all roles that have been granted to the current user througth other roles can be viewed in views INFORMATION_SCHEMA.ADMINISTRABLE_ROLE_AUTHORIZATIONS and INFORMATION_SCHEMA.APPLICABLE_ROLES respectively.

Changes of authorization identity

The current user of an SQL-session can be modified by SET SESSION USER statement. The set of current roles of an SQL-session can be changed with SET ROLE statement.

2.6.2 Privileges

A privilege is a rule that authorizes a given category of object actions to be performed on a specified database object (by a specified authorization identifier, a user identifier or a role).

A privilege can only be granted to a user or a role by an GRANT PRIVILEGE statement.

A privilege is specified by two elements, i.e., the action to be performed and the database object on which the action will be performed.

The actions that can be specified are

• SELECT
• INSERT
• UPDATE
• DELETE
• REFERENCES
• EXECUTE
• CONNECT
• CREATE TABLE
• CREATE VIEW
• CREATE INDEX
• CREATE TRIGGER

Almost all the information stored in LogicSQL databases are in a form of database objects. For instance, he access to the following objects requires a privilege, i.e., an explicit authorization:

• tables, views, and columns in tables and views,
• triggers and indexes,
• catalog and schema,
• datatypes,
• stored procedures.

According to its scope, privileges can be classified as

• object privileges

  This type of privileges authorize that a specified action, such as SELECT or UPDATE, to be performed on a specified object. For instance, SELECT ON account_table

• schema privileges

  It is valid for certain actions such as create view and create index on a specified schema. For instance, CREATE INDEX ON registra_schema authorizes the grantee to create an index on any table belong to registra_schema.

• system privileges

  This type of privileges valid for all schemes, and thus only a few specified actions are specified in this category, including create any view, create any index, connect, create user, create role, and all schema privileges. Because of its scope, we need not specify the database object for such a privilege.

  For instance, if create any view is granted to a user, then the user can create a view under any schema.

  Note that all schema privileges gives the grantee the authorization to access almost any database object, and thus must be granted with caution.

For obvious reasons, the following object and schema privileges are granted to all users on all objects in their own schema, that is, the schema whose owner is the current user.

select, insert, delete, update, create table.

When a user is created, the connect privilege is usually granted. A user, however, cannot connect into the LogicSQL server if ones connect privilege has been revoked.

All privileges can be granted or revoked through GRANT PRIVILEGE and REVOKE PRIVILEGE statements.

Examples

When a LogicSQL database is first established, one super user identifier, named system, is created with all the authority. Without explicit authorization from system, no one else can do anything about the database.

2.6.3 Privilege Domains

An SQL-session has a current user identifier and a set of current roles. The privilege domain of an SQL-session contains all privileges granted explicitly to the current user and all current roles. The SQL-session can then perform any operations authorized by the privileges in the privilege domain.

Changing the Privilege Domain

During an SQL-session, the current user can change the privilege domain of the session with the SET ROLE command, which changes the roles currently enabled for the session. One can change the enabled roles any number of times during a session. The number of roles that can be concurrently enabled is limited by the configuration parameter MaxEnabledRoles which is bounded by the compilation constant MAX_ENABLED_ROLES.

The current user can use the SET ROLE command to enable or disable any of the following roles:

• roles that have been granted directly to you
• roles granted to you through other roles

The current privilege domain is also changed in the following cases:

• if a privilege is granted,
• if one of the granted privileges is revoked
• if one of the enabled roles is revoked
• if the privilege domain of one of the enabled roles is changed

If none of the above conditions occur and one does not issue the SET ROLE command, the current privilege domain remains in effect for the duration of the SQL-session. In the last two cases, the change in the privilege domain does not take effect until the current user log on to LogicSQL again or issue a SET ROLE statement.

You can determine which roles are in the current privilege domain at any time by examining INFORMATION_SCHEMA.ENABLED_ROLES view.

Example I

To enable the role GARDENER for your current session, issue the following statement:

SET ROLE gardener;

Example II

To enable all roles granted to you for the current session, issue the following statement:

SET ROLE ALL;

Example III

To enable all roles granted to you except BANKER, issue the following statement:

SET ROLE ALL EXCEPT banker IV;

Example IV

To disable all roles granted to you for the current session, issue the following statement:

SET ROLE NONE;

All security-related SQL statements are listed below:

• CREATE USER
• DROP USER
• ALTER USER
• CREATE ROLE
• DROP ROLE
• SET ROLE
• SET SESSION USER
• GRANT PRIVILEGE
• REVOKE PRIVILEGE
• REVOKE ROLE

2.7 System Information and Information_schema

The Information Schema, named INFORMATION_SCHEMA, is specified by LogicSQL (based on SQL'99), to provide the user all relevant meta data. The schema contains a list of views and base tables that describe detailed information of tables, data types, table constraints, privileges, etc. The views of the Information Schema are viewed tables defined in terms of the base tables of the Definition Schema.

The Information Schema views are defined as being in a schema named INFORMATION_SCHEMA, enabling these views to be accessed in the same way as any other tables in any other schema. SELECT on most of these views is granted to PUBLIC WITH GRANT OPTION, so that they can be queried by any user and so that SELECT privilege can be further granted on views that reference these Information Schema views. No other privilege is granted on them, so they cannot be updated.

For example, any user can access INFORMATION_SCHEMA.TABLES using

LogicSQL> select table_schema, table_name,table_type,table_degree from information_schema.tables where table_schema = 'information_schema'; table_schema table_name table_type table_degree ------------------------------------------------------------------------------------- information_schema administrable_role_authorizations view 3 information_schema applicable_role_authorizations view 3 information_schema catalog_name view 4 information_schema columns view 8 information_schema dual view 1 information_schema element_types view 24 information_schema enabled_roles view 1 information_schema indexes view 5 information_schema key_column_usage view 8 information_schema procedures view 4 information_schema role_authorizations view 3 information_schema schemata view 7 information_schema sequences view 9 information_schema sqlstate_message view 3 information_schema system_property view 2 information_schema table_constraints view 9 information_schema table_privileges view 8 information_schema tables view 6 information_schema triggers view 15 information_schema users view 3 information_schema views view 6 LogicSQL>

Note: For lack of space, in the printout above, we omitted the column table_type, which is of type view throughout. Also, ad_role_authorizations and app_role_authorizations stand for administrable_role_authorizations and applicable_role_authorizations, respectively

INFORMATION_SCHEMA.TABLES contains one row for each table that is allowed to be selected by the user. This includes the tables in any schema that is owned by the user and tables on which SELECT is granted either to the user or to 'PUBLIC'. As a matter of fact, the above table lists all the views/tables in INFORMATION_SCHEMA that are accessible to any user.

The detailed descriptions of these INFORMATION SCHEMA views are given below (to be completed).

• INFORMATION_SCHEMA.ADMINISTRABLE_ROLE_AUTHORIZATIONS

  Identify role authorizations for which the current user has WITH ADMIN OPTION.

• INFORMATION_SCHEMA.ROLE_AUTHORIZATIONS

  Identifies the roles that have been explicitly granted to the current user.

• INFORMATION_SCHEMA.APPLICABLE_ROLE_AUTHORIZATIONS

  Identifies the applicable roles for the current user.

  The set of applicable roles for a user consists of all roles granted to the user either directly or through other roles.

• INFORMATION_SCHEMA.ENABLED_ROLES

  Identifies the set of all roles enabled for the current SQL-session.

  Note that the privileges domain of the current SQL-session is determined by the set of all roles in the current role domain.

2.8 SQL Syntax

<identifier> ::= < simple Latin letter> [{ < digit> | < simple Latin letter> | _ }... ]

<password string> ::= a string (a sequence) of letters, numbers, and '_' that shall start with a letter and shall not resemble any sequence of words in any language, and shall at least 5 characters long. Like any string, it should be quoted by two single quotations

<derived column> ::= < item reference> | < set function specification> | < value expression> [ [AS] < correlation name> ]

<set function specification> ::= <COUNT(*)> | < aggregate function( item reference)>

<aggregate function> ::= AVG | MIN | MAX | SUM | COUNT

<set clause list> ::= < column name> = < value expression> [ {, < column name> = < value expression>}...]

<search condition> ::= < boolean value expression>

<value expression> ::= < numeric value expression> | < string value expression> | < boolean value expression> | < datetime value expression>

<unsigned value specification> ::= < literal value> | USER |CURRENT_USER

<string value expression> ::= < value expression primary> || < value expression primary>

<numeric value expression> ::= < term> | < numeric value expression> '+'|'-' < term>

<term> ::= < factor> | < boolean term> '*'|'/' < factor>

<factor> ::= ['-'|'+' ] < value expression primary>| < numeric value function>

<value expression primary> ::= < unsigned value specification>| < item reference>

<numeric value function> See Numeric Functions

<datetime value expression> ::= < datetime term> | < datetime value expression> <'+'|'-'> < integer>

<datetime term> ::= < datetime factor>

<datetime factor> ::= < datetime primary> [AT TIME ZONE < time zone>]

<datetime primary> ::= < datetime literal>| < item reference> | < datetime value function>

<datetime literal> ::= DATE < date string>|< timestamp string> | TIME < time string> | TIMESTAMP < timestamp string>

<date string> ::= <quote><digit><digit><digit><digit>'-'<digit><digit>'-'<digit><digit><quote>

<time string> ::= <quote><digit><digit>':'<digit><digit>':' <digit><digit><quote>

<timestamp string> ::= <quote><digit><digit><digit><digit>'-'<digit><digit>'-' <digit><digit> ' ' <digit><digit>':'<digit><digit>':' <digit><digit><quote>

<datetime value function> See Date and Time Functions

<time zone> ::= BEIJING | GMT | EST | CST | MST | PST | GMT <'+'|'-'> <integer>

<boolean value expression> ::= < boolean term> | < boolean value expression> OR < boolean term>

<boolean term> ::= < boolean factor> | < boolean term> AND < boolean factor>

<boolean factor> ::= [ NOT ] < boolean primary>

<boolean primary> ::= < predicate>

<predicate> ::= < comparison predicate> | < in predicate> | < between predicate> | < like predicate>

<comparison predicate> ::= < value expression> < comparison operator> < value expression>

<comparison operator> ::= = | <> | < |=< | <= | > | >= | =>

<like predicate> ::= < string value expression> [NOT] LIKE < string value expression> [ESCAPE < string value expression>]

<between predicate> ::= < value expression> [NOT] BETWEEN < value expression> AND < value expression>}

<in predicate> ::= < value expression> [NOT] IN ( < value expression> [{, < value expression>} ] )

<relational algebra operator> ::= UNION | UNION ALL | EXCEPT | MINUS | INTERSECT

<table reference> ::= < table primary | joined table>

<table primary> ::= < table name | view name> [ [AS] < correlation name> ]

<joined table> ::= < table reference> [ INNER | <LEFT|RIGHT|FULL>[OUTER] ] JOIN < table primary> [ON < search condition>]

<catalog name> ::= < identifier>

<schema name> ::= < identifier> | < catalog name>.< identifier>

<sequence name> ::= < identifier> | < schema name>.< identifier>

<table name> ::= < identifier> | < schema name>.< identifier>

<cursor name name> ::= < identifier> | < schema name>.< identifier>

<procedure name> ::= < identifier> | < schema name>.< identifier>

<argument name> ::= < identifier>

<variable name> ::= < identifier>

<trigger name> ::= < identifier> | < schema name>.< identifier>

<user name> ::= < identifier>

<view name> ::= < identifier> | < schema name>.< identifier>

<index name> ::= < identifier> | < schema name>.< identifier>

<column name> ::= < identifier> | < table name>.< identifier>

<column list> ::= < column name> [{, < column name> }]

<item reference> ::= < column name>

<constraint name> ::= < identifier >

<correlation name> ::= < identifier >

<privileges> ::= < system privileges> | < schema privileges> | < object privileges>

<system privileges> ::= ALL SCHEMA PRIVILEGES | CREATE ANY TABLE | CREATE ANY VIEW | CREATE ANY INDEX | CREATE ANY TRIGGER | CREATE ANY ROLE

<schema privileges> ::= < schema action> [{< schema action> ...} ] ON < schema name>

<object privileges> ::= ALL PRIVILEGES | < object action> {, < object action> } ON [TABLE] < table name>

<schema action> ::= CREATE TABLE | CREATE VIEW | CREATE INDEX | CREATE TRIGGER

<object action> ::= SELECT | DELETE | INSERT | UPDATE

<trigger event> ::= DELETE | INSERT | UPDATE

<grantee> ::= PUBLIC | < identifier >

<data type> ::= INT | INTEGER |VARCHAR( < integer > ) | NUMBER( < integer, integer > ) | DATE | BLOB | CLOB |LONG| LONG RAW | SQL_IDENTIFIER

<variable list> ::= : variable name | [{<: variable name> ... }]

<variable name> ::= identifier

<signed integer> ::= ['+'|'-'] < integer>

<hex integer> ::= < digit|a| ... |f> [{< digit|a|...|f>}...]

<quote> ::= '

<digit> ::= 0|1|2|3|4|5|6|7|8|9

<simple Latin letter> ::= a|b|c|d| ... |x|y|z

<integer> ::= [+|-] < digit> [ { < digit> ... } ]

<numeric number> ::= [+|-] [ { < digit> ... }] ['.' < digit> [ { < digit> ... }] ] | < integer> E ['+'|'-' < integer>

<literal value> ::= all valid literal values for the data type.

Note that all ASCII characters less than 13 cannot be used in a string literal.

3. Database Administrator's Guide

3.1 Installation
3.2 Server Runtime Environment
3.3 System Configuration
3.4 Managing Database Server
3.5 Managing Databases
3.6 Database User's Management
3.7 Database Recovery
3.8 Tutorial for DBA

3.1 Installation

The proper installation of LogicSQL takes the following steps:

• pre-installation,
• installation of the system code from the distribution package,
• installation of application tools from the distribution package,
• set up of the server runtime environment,
• system configuration, and
• creation of the initial database.

Pre-installation

• Resources Requirements

  Make sure you have sufficient disk space. You will need 20 MB for the installation directory, and at least 50 MB for an empty database. It takes about five times the amount of space that a flat text file with the same data would take.

  The disk space requirement for the backup log depends on the frequency of updates in your applications and the frequency of database archive operations. See Database Recovery for more details.

• Environment Setup There are a few environment variables that must be set up properly, including, LOGICSQL for the installation directory, and PATH and LD_LIBRARY_PATH for the search paths.
  • Installation Directory: The database can be installed under any directory, usually /usr/local/lsql. You do not need the root access to install the database if you choose to install it under your own directory.

    You need to set up an environment variable LOGICSQL to be your installation directory. This can be done in csh or tcsh

    setenv LOGICSQL /usr/local/lsql

    Replace /usr/local/lsql with your choice of the installation directory if you wish.

  • Search Paths: You also need to include $LOGICSQL/bin(1) in your search paths. Again, if you are using csh or tcsh, use

    setenv PATH $LOGICSQL/bin:$PATH

    LogicSQL will need to load several files stored in $LOGICSQL/lib and thus this directory must be in the library search path. This can be done using

    setenv LD_LIBRARY_PATH $LOGICSQL/lib:$LD_LIBRARY_PATH

    if one uses csh or tcsh.

  • Limit on file descriptors and file sizes: The operating system usually set the upper limit on the number of file descriptors that can be open. In a Unix system, this limit can be found using the command limit, as shown below.

    % limit cputime unlimited filesize unlimited datasize unlimited stacksize 8192 kbytes coredumpsize unlimited memoryuse unlimited descriptors 1024 memory locked unlimited maxproc 2048 openfiles 1024

    This shows that the limit on the number of descriptors/openfiles is 1024, which means the total number of tables and concurrent clients cannot exceed 1024. Usually, a database has a few hundred tables, and therefore, this limit is not sufficient for most applications. One must change this limit to a larger number, say 16384, to make sure the system will not run into trouble later.

    Many operating system also limits the size of files, usually around 2GB. Again, change this into unlimitted to save future trouble. Consult one's system administrator to find out how to increase the limits on both file descriptors and file sizes.

Installation of the System Code

All documents and programs are contained in the directory LogicSQL_Dist in the distribution package. The LogicSQL can be easily installed from the distribution package in the following two simple approaches.

1. Automatic Installation

   Change into the distribution directory (Distribution) and then start the Java Intaller by entering

   ./auto_run.sh

   A graphic user interface will then be displayed. Follwing the instruction, one can install the system code and set up the values for all the system configuration variables.

2. Manual Installation

   This is done by simply copying all files and directories in the distriution directory LogicSQL_Dist into the installation directory, which will complete the installation of the system code.

Installation of Application Tools

All development tools and application interfaces can be intalled from LogicSQL_Dist in the distribution package, with or without installation of a LogciSQL server.

• JDBC

  The LogicSQL's JDBC driver is located as LogicSQL_Dist/jdbc/lib/logicsql_jdbc.jar. To use it in your computer system, being a Linux or Windws, you need first copy the driver into your system, and then define the enviornment variable CLASSPATH to include the driver.

• ODBC

  The ODBC driver is located at LogicSQL_Dist/odbc, and the installation guideline is given at LogicSQL_Dist/odbc/Readme.txt.

• Embedded C Pre-compiler

  The embeeded C pre-compiler package is located at LogicSQL_Dist/embedded_c. To use it, one needs to copy the precompiler, LogicSQL_Dist/embbedded_c/bin/prec, the head files localted at LogicSQL_Dist/embedded_c/include, and the library file LogicSQL_Dist/embedded_c/lib/liblsql_pc.a into appropreate bin, include, and lib directories respectively.

• C Interface

  The C Interface included in the distribution package consists of two files, i.e., LogicSQL_Dist/include/lsql_c.h and LogicSQL_Dist/lib/liblsql_c.a. To use it, one needs only to store them into their respective proper directories such that your C-compiler knows where to find them.

• DBA-Tools

  The installation guideline for DBA-Tools can be found at LogciSQL_Dist/dba/Readme.txt.

• SQL*Face

  The SQL*Face is a simple program that runs on many different kinds of computer systems with many different operating systems.

  • Linux

    To install SQL*Face in a Linux system, simply copy LogicSQL_Dist/bin/sqlface into /usr/local/bin or any other directory included in your search parth ($PATH).

  • Windows

    To install SQL*Face in a Window system,

    • create a new folder sqlface in Program Files, and then
    • copy both $LOGICSQL/bin/sqlface.exe and $LOGICSQL/bin/cgywin1.dll into Program Files/sqlface
    • create a short cut to Program Files/sqlface/sqlface.exe.
    Note that SQL*Face for Windows in the current distribution is a simpler version than that for Linux.

    To run SQL*Face in Windows, click the short cut to SQL*Face.

3.2 Server Runtime Environment

LogicSQL user account As with any other server daemon that is connected to the world at large, it is advisable, though not necessary, to run LogicSQL under a separate user account. This user account should only own the data itself that is being managed by the server, and should not be shared with other daemons. To add a user account to your system, look for a command useradd or adduser. The user name "logicsql" is often used but by no means required.

3.3 System Configuration

The LogicSQL installer used to intall the LogicSQl may also be used to set up desired values for the configuration variables.

In the sequence, by setting a configuration variable to be a specified value, we mean editing the line (or inserting a new line) that contains the variable such that the line will starts with the variable, followed by the value with one white space in between. For example, by setting ServerPort to be 8000, we edit the line that contains ServerPort to the following

ServerPort 8000

• Database Cluster You must initialize a database storage area on disk, called a database cluster. A database cluster is a collection of databases that will be accessible through a single instance of a running database server. After the creation of the system, the database cluster will contain two default databases, (two catalogs in SQL terminology). One is system with all the meta tables, and the other is database. Of course, the system dba can create as many databases as he/she likes later using CREATE DATABASE.

  In file system terms, a database cluster will be a single directory under which all data will be stored. We call this data area. The default data area is $LOGICSQL/data, though you may set DataDirectory to be the full path name of the data area of your choice.

• LOB Cluster You may also need to initialize a database storage area on disk, called a lob cluster if you ever need to use clob and blob. A lob cluster, similar to the data cluster, is a single directory under which all lob items will be stored. We call this lobs area. The default lobs area is $LOGICSQL/lobs, though you may set LobDirectory to be the path name of your choice.

• Database Logs LogicSQL stores all the log files, including the ones used for crash recovery, in one directory, called the database log area, The default log area will be $LOGICSQL/logs, but you may choose any directory for log files by setting LogDirectory to be the full path name of your choice.

• Server Port Number In order for LogicSQL to accept TCP/IP connections, you need to specify the socket number by setting the value of ServerPort. The initial value is 8000 but you are free to set ServerPort to any socket you wish.

• Backup Log Status There are several schemas designed for database crash recovering and you need to make your choice by setting LogStatus to be one of local_log, no_log, standby_log, both_log, with the default value being local_log. See Database Recovery for details.

  If you choose standby_log or both_logs as your choice of crash recovery then you must create another LogicSQL database system as your standby database and set StandbyDatabaseHost, StandbyDatabasePort, and StandbyDatabasePassword to be the IP address of the server machine, the server port, and the password of schema logman, based on the information of the standby database system. Note that logman is a user id used by the system to access the standby database system and its original password is logman but you shall change its password using ALTER USER before setting up the standby database system.

The following table summaries the basic task of configuration.

Config. Variable	Meaning of the Value	Status
DataDirectory	path name of the database cluster	optional with default being $LOGICSQL/data
LobDirectory	path name of the lob cluster	optional with default being $LOGICSQL/lobs
LogDirectory	path name of the log area	optional with default being $LOGICSQL/logs
ServerPort	port number of the server socket	mandatory with the initial being 8000
LogStatus	the crash recovery schema	mandatory with initial being local_log
UserLog	the log used to store the client info	optional with default being off.
CommandLog	the log used to store all SQL statements received	optional with default being off.
StandbyDatabaseHost	IP address of the standby database server	mandatory if LogStatus is standby_log or both_logs
StandbyDatabasePort	socket number of the standby database server	mandatory if LogStatus is standby_log or both_logs
StandbyDatabasePassword	password of logman of the standby db server	mandatory if LogStatus is standby_log or both_logs

The following table lists the configuration variables that specify the limit of the underling SQL statements.

Config. Variable	Meaning of the Value	Status
MaxTables	the maximal number of tables allowed in SELECT sta tements	optional with default 8
MaxDegrees	the maximum number of columns in any SELECT statement	optional with default 64
MaxIndexes	the maximum number of indexes created for one table.	optional with default 32
MaxThreads	the maximum number of threads allowed for the server.	optional with default 1
CommandSize	the length limit of an SQL statement.	optional with default 4096.
TupleLimit	maximum number to tuples retrieved from any table	optional with default 10000000
IdSize	the size limit for any SQL identifier	optional with default 64
StringSize	the size limit of a string or a component of any table, excluding that of BLOB/CLOB.	optional with default 2048
CACHESIZE	the buff size for the disk-file management module.	optional with default 8388608.

Note that MaxClients is bound by FD_SETSIZE = 4096, the maximum number of sockets permitted in the system. FD_SETSIZE has been set before the compilation of the system code, and cannot be changed without re-compilation.

The following table list the configuration variables that specify the parameters used by the underlying Berkeley DBs file structures.

Config. Variable	Meaning of the Value	Status
PAGESIZE	the size of B-tree files	optional with default 8192
PAGEORDER	the order of B-trees	optional with default 512
CASHSIZE	the size of cash	optional with default 4194304

All the configuration variables that specify the system limitation can be retrieved from the meta table system.definition_schema.system_limitation, as demonstrated below.

LogicSQL> select * from system.definition_schema.system_limitation; item limitation -------------------------------- IdSize 64 LazyRatio 75 LimitNewRequests 3 MaxClients 2400 MaxDegrees 64 MaxIndexes 16 MaxInteger 38 MaxOpenTables 1000 MaxPrecision 38 MaxScale 15 MaxTables 8 RowSize 131072 SelectNumber 10 StringSize 1024 TupleLimit 10000000 LogicSQL>

3.4 Managing Database Server

.

3.4.1 System Creation

The initial database system can be created using

% logicsql -c

This will create the initial database system in the database cluster, and the log files. This will also automatically create a system user named 'system' with the default password 'manager'.

3.4.2 Starting the LogicSQL server

You must start the LogicSQL server before anyone can access the database using logicsql.

To start the logicsql, use

% logicsql

Normally, you will want to start the database server when the computer boots up. This is not required; the LogicSQL server can be run successfully from non-privileged accounts without root intervention.

For each database cluster, only ONE database server should be started at any time. A separate LogicSQL user account and the corresponding database cluster must be set up if you wish to run two servers in one computer system.

Command Line Options of logicsql

logicsql is used to create the initial database as well as start the server. It may also take some command line options, as listed below. Note that the command line option will override the configuration values if applicable.

logicsql -h Usage: logicsql [-options] where options include -p <port_number> specify the port number used by the server, the default is specifed by the configuation file -c create the initial database -h print this message

Server Startup Failures

There are several common reasons for the failure. Some of possible reasons are: the socket is busy with other applications; the inappropriate setup of environment variables. Check the log file db_err.log in the log area for details.

3.4.3 Shutting down the LogicSQL server

The LogicSQL server can be shut down with

% shutdown_server

There are several command line options for shutdown_server to specify the port number, user name, host machine, etc. Enter shutdown_server -h for details.

The server can also be shutdown with an SQL command shutdown. (See SHUTDOWN.)

Note that using shutdown_server will not release the database socket used by the server immedicately. If you wish to shutdown and then start the server right way, you may use the SQL statement shutdown instead.

By an SQL command, we mean an SQL statement that can be executed by sending it to a LogicSQL server using any standard SQL interface, such as SQL*Face and/or DBA Tools, while by a system command, we mean a command that can be executed by entering its name and options in your operation system. LogicSQL provides only a few system commands, such as logicsql, shutdown_server, and sqlface, but supports almost all standard SQL commands. Thus, almost all commands introduced below are SQL statements, not a system command.

3.5 Managing Databases

A database is a named collection of SQL objects ("database objects"); every database object (schema, tables, function, etc.) belongs to one and only one database. An application that connects to the database server specifies with its connection request the name of the database it wants to connect to. It is possible for an application to access more than one database per connection, as long as the application enjoys the access privilege.

Note: SQL calls databases "catalogs", but there is no difference in practice.

Databases are created and removed with the SQL query language command

CREATE DATABASE

and

DROP DATABASE

respectively.

There are two different ways to store all the database objects in disk files. In the mono-file mode, all the objects of a database will be stored in one disk file of the form: database_name.db, and the multi-file mode, a database will take one directory under the database area, and each database object will be stored as one file under the directory. The mono-file mode is suitable only for those small databases whose objects will take the space less than 2G. For those big databases, you must use the multi-file mode.

The storage mode of a database shall be specified when it is created.

3.6 Database User's Management

Managing database users and their privileges is in concept similar to that of Unix operating systems, but then again not identical enough to not warrant explanation.

There are two groups of database users, that is, a group of system users and a group of database users.

• A system user is a user with all privileges, including creation and drop databases, schemes and other users. Thus the number of system users must be kept minimum. One system user, name system' with password 'manager', is created automatically when the initial database is created.

• Database users ( schemes in SQL terms) are conceptually completely separate from any operating system users. In practice it might be convenient to maintain a correspondence, but this is not required. Database user names are global across a database cluster installation (and not per individual database). To create a user use the SQL command CREATE USER, such as

  CREATE USER your_account

  To remove an existing user, use the analog SQL command DROP USER, such as

  DROP USER her_account

  If not specified, all users will be created with the password the same as the schema name,

3.7 Database Recovery

Database recovery concerns the recoverability of the database. There are several components in database recovering, and the most critical component of database recovering is to how to store update logs into a stable storage system.

It is the database administrator's responsibility to make sure the log cluster, i.e., the directory as specified by LogDirectory, is stored in a secured disk system that is separated from the data cluster.

For your convenience, A Typical Recovery Schema is presented at the end of this section.

3.7.1 The Schemes of the Backup Logs

1. NO_LOG

   There is no backup log and therefore, no recovery is possible.

2. LOCAL_LOG

   The backup log is stored in the log area of the same computer system. The database can be recovered using the local log tables. Therefore, the log area shall be located in a different disk file system to avoid disk failure.

   The local_log backup schema is very efficient and reliable. Since the backup log is in the same computer system, it is prone catastrophic failures such as fire and earthquake.

3. STANDBY_DB_LOG This schema stores the backup log in a separate LogicSQL database system, and therefore, increases the recoverability after catastrophic failures.

   In this schema, the standby database system can be set up in anywhere as long as the system is connected to the server by the network. To use this schema, the configuration file must provide the following information for the standby database system, i.e., the the IP address, the port number, and the password of the system, through the configuration variables StandbyDatabaseHost, StandbyDatabasePort, and StandbyDatabasePassword respectively.

4. BOTH_LOGS

   This is the combination of both LOCAL_LOG and STANDBY_DB_LOG. That is, the update log is stored in the log area of the same computer system as well as in a separate LogicSQL database system, and therefore, it enjoys advantages of both schemas.

The schema of the backup log is specified when initializing the database server using logicsql with an appropriate option. If the STANDBY_DB_LOG is used, please make sure the standby database server is working properly before start the LogicSQL server.

3.7.2 The Types of Recovery

Depending on the type of crash and the availability of the backup log files. there are different types of recovery

1. No Recovery There is no need for recoverability and/or there are no backup log files (i.e., NO_LOG was specified when the database was initialized).

   In this case, you can either restart the database or recreate an initial database first and then restart the database using logicsql.

2. Catastrophic Recovery. A failure that requires catastrophic recovery is a failure where the database has been destroyed or corrupted. For example, catastrophic failure includes the case where the disk drive on which the database has been physically destroyed, or when the systems normal recovery is unable to bring the database to a consistent state. This is often difficult to detect, and perhaps the most common sign of the need for catastrophic recovery is when the normal recovery procedures fail.

   To recover the database into the most recent consistent state after catastrophic failure, the system needs to redo updates of all committed transactions recorded on the backup log system using RECOVER DATABASE SINCE LAST SNAPSHOT.

3.7.3 Snapshot of the Database

The database state is uniquely determined by two data directories, i.e., Data Cluster and Lob Cluster. Consequently, a snapshot of the database consists of a copy of these two directories. To make a snapshot of the database is thus to make a copy of these two directories. The copying itself is straightforward and system-dependent, which will not be discussed here.

The main concern is how to make a consistent snapshot of the database. That is, we have to make sure the two directories represent a consistent database state before we make the snapshot, and we also have to make sure no database accesses during the coping.

This can be done using a system utility command SET ADMISSION as a system user in three steps.

1. First, SET ADMISSION LEVEL WITHIN TRANSACTION will enable the system to process only requests from active transactions.
2. A few seconds later(depending on the load of the system), SET ADMISSION LEVEL BLOCK will block all requests.
3. Finally, SET CHECKPOINT will be used to force write all updates to the database.

After the snapshot is secured, SET ADMISSION LEVEL OPEN will restore the system to its normal state.

It is recommended that the snapshot is made on the daily basis, which will speed up the crash recovery when it is needed.

To reduce the size of the log system, we may clear all the log records once for a while, as long as we have a consistent snapshot secured.

The log system can be cleared as follows.

1. First, take a snapshot as discussed above.
2. Instead of restoring the system into a normal state using SET ADMISSION LEVEL OPEN, use SHUTDOWN to take the system down.
3. After the system is shut down, delete all the log files in the log_area.
4. Finally, restart the server using logicsql.

3.7.4 Recovery Procedures

• Catastrophic Recovery with LOCAL_LOG

  The recovery will be processed in two steps.

  The first step is to restore the most recent snapshot of the database from the backup media into the system directories by copying both data and lobs into their appropriate directories. And then restart the server using logicsql without -r option.

  The next step is to issue an SQL statement

  RECOVER DATABASE SINCE LAST SNAPSHOT or RECOVER DATABASE SINCE BEGINNING

  as a system user, which will recovery the database.

• STANDBY_DB_LOG

  The recovery must recover both database systems.

  The standby database system can be recovered the same way as it is a database using the LOCAL_LOG schema. After the recovery, the standby database system is ready to replace the original server as the working server for the system.

  The working database can be recovered in two steps.

  1. First, copy all log files of the standby database system (located in the LogDirectory of the standby database system), into the LogDirectory of the working database system.
  2. Then recover the working database as if it is a system using the LOCAL_LOG schema.

  Note that after the recovery, RESTART the server again. This is very important because the transaction id must be chosen independent of the recovery procedure.

3.7.5 A Typical Recovery Schema

1. The LOCAL_LOG is used for the backup schema.
2. The snapshot is taken on the daily basis, incorporating with the system backup procedure if possible. To minimizing disturbance to the operations of the system, the snapshot should be taken around the lowest working load, usually around 2:00 AM.
3. The clearance of the log system is conducted on weekly basis.

3.8 Tutorial for DBA

This section provides a simple tutorial for database administrators about how to manage LogicSQL systems.

4. Interfaces

There are several approaches to access the LogicSQL database server.

4.1 JDBC
4.2 ODBC
4.3 Embedded C Pre-compiler
4.4 The C Language Interface

4.1 JDBC

A JDBC driver for the LogicSQL database, Release 1.0, is included in the distribution package.

LogicSQL's JDBC drivers implement the standard JDBC (Java Database Connectivity) interface as defined by JavaSoft, except the following ones, and an SQLException will be thrown out if a method used has not been implemented.

The public interfaces in java.sql that have not been implemented are:

• Array
• CallableStatement
• DatabaseMetaData
• Ref
• Struct

A description of JDBC can be found at http://www.javasoft.com

The JDBC driver is located as $LOGICSQL/jdbc/lib/logicsql_jdbc.jar. It can be installed by simply defining CLASSPATH to include the driver.

For more details, read The Introduction to LogicSQL JDBC Driver.

Examples of Applications Using JDBC

The following is a simple example of using the JDBC driver to insert a tuple with Blob. More examples of using the JDBC driver and Java Servlets can be found at http://luscar.cs.ualberta.ca:8080/yuan/servlets/logicsql.html.

import java.io.*; import java.sql.*; /** * A simple example to demonstrate how to use JDBCs PreparedStatement * to insert a Blob into a table. The table PICTURE used in the example * is created with * create table picture ( * photo_id integer, * title varchar(48), * place varchar(48), * sm_image blob, * image blob, * constraint picture_primary_key_constraint primary key(photo_id) ) * * @author Li-Yan Yuan * */ public class InsertLobs { public static void main( String[] args) { // change the following parameters to connect to other databases String username = "yuan"; String password = "******"; String drivername = "com.shifang.logicsql.jdbc.driver.LogicSqlDriver"; String dbstring = "jdbc.logicsql@luscar.cs.ualberta.ca:2000:database"; // two local files of pictures String file1 = "PEiffelTower.jpg"; String file2 = "EiffelTower.jpg"; try { // to connect to the database Connection conn = getConnected(drivername,dbstring, username,password); // create a preparedStatement with // ? represents the lobs to be inserted PreparedStatement stmt = conn.preparedStatement( "insert into pictures values (20,'Eiffel Tower','Paris',?,? )" ); // Set the first parameter File file = new File( file1 ); stmt.setBinaryStream(1,new FileInputStream(file),(int)file.length()); // set the second parameter file = new File(file2); stmt.setBinaryStream(2,new FileInputStream(file),(int)file.length()); // execute the insert statement stmt.executeUpdate(); System.out.println( "the execution succeeds"); conn.close(); } catch( Exception ex ) { System.out.println( ex.getMessage()); } } /* * To connect to the specified database */ private static Connection getConnected( String drivername, String dbstring, String username, String password ) throws Exception { Class drvClass = Class.forName(drivername); DriverManager.registerDriver((Driver) drvClass.newInstance()); return( DriverManager.getConnection(dbstring,username,password)); } }

4.2 ODBC

The Open DataBase Connectivity (ODBC for short) is a standard database API (Application Programming Interface) first developed by the SQL Access Group (SAG). The goal of ODBC is to make it possible to access any data from any application, regardless of which database management system (DBMS) is used to store and retrieve data. ODBC manages this by inserting a middle layer, called a database driver , between an application and the DBMS. The purpose of this layer is to translate the application's data queries into commands that the DBMS understands. For this to work, both the application and the DBMS must be ODBC-compliant -- that is, the application must be capable of issuing ODBC commands and the DBMS must be capable of responding to them.

The LogicSQL ODBC driver implements almost all the public interfaces specified by Microsoft Open DataBase Connectivity

For more details, see The User Manual of LogicSQL ODBC Driver.

4.3 Embedded C Pre-compiler

The LogicSQL's Embedded C Pre-compiler (or LogicSQL prec for short) based on the SQL standard is a pre-compiler tools that allows you to embed SQL statements in a C source program.The prec accepts the source program as input, translates the embedded SQL statements into standard runtime library calls, and generates a modified C source program that you can compile, link, and execute against a LogicSQL Database in the usual way.

For more details, see The User Manual of LogicSQL Embedded C Precompiler.

4.4 The C Language Interface

The C interface is designed to facilitates the development of LogicSQL-embedded C programs, i.e., the programs that can access any LogicSQL server directly.

The C Interface included in the distribution package consists of two files, i.e., lsql_c.h located in $LOGICSQL/include and liblsql_c.a located in $LOGICSQL/lib. To use it, one needs only to store them into their respective proper directories such that your C-compiler know where to find them.

• Tutorial for the C Interface.
• List of functions and the access methods of the RSET struct.

  Functions	Description
  sql_connection	connects to an LogicSQL server
  close_connection	close the current connection to an LogicSQL server
  sql_execution	executes an SQL command
  sql_selection	executes an SQL query
  put_one_lob	to update an existing (often empty_lob) with a new lob value
  get_one_lob	to get a lob value with the given lob_locator
  Result Set	Description
  RSET	a structure for representing the result set from selection
  rset_create	creates an result set
  rset_display	display the given result set on screen
  RSET->clear	clears the result set for future use
  RSET->release	release the result by freeing all its memory
  RSET->get_degree	gets the number of columns of the result table
  RSET->get_no_tuples	gets the number of tuples of the result table
  RSET->get_next	gets the next component of the result table
  RSET->get_previous	gets the previous component of the result table
  RSET->get_component	gets the given component of the result table
  RSET->get_column	gets the ith column name
  RSET->get_type	gets the ith column types
  RSET->get_error	get the error message
  RSET->reset	reset the current row of the result set to the first one

4.4.1 Tutorial for the C Interface

The C interface is designed to facilitates the development of LogicSQL-embedded C programs, i.e., the programs that can access any LogicSQL server directly.

The interface focuses on two central data types:

• int: the file descriptor
• RSET: the result set

The file descriptor represents the connection between the program and the database server. To connect to a database server, a clients program needs to establish a file descriptor such that all the interactions between the client and the server are carried out through the file descriptor.

After the connection established, a client sends all requests to the database server to the file descriptor, and receive all the response from the same file descriptor.

The RSET, standing for Result Set, is a struct define to storing the set of all the answers to an SQL query

Each RSET uses a buffer to sequentially store all the table components in the form of a string such that two components are separated by a SEPARATOR, and has two pointers, one pointing to the first component and other the current component. Because the buff has no other mechanisms for tracking down the table components, The only way to access the components in a RSET is sequential access.

Example One can use sql_connection to establish the connection to a database server by providing the following information:

• the host name or IP address of the computer system that runs the server
• the port number used by the server
• the database name to be connected
• the user name
• the password of the user

Here is what the code to call sql_connection looks like:

int ret, fd, port; char hostname[BUFSIZE], dbname[BUFSIZE]; char username[BUFSIZE], password[BUFSIZE]; strcpy( hostname, "localhost"); strcpy( dbname, "database"); strcpy( username, "yuan"); strcpy( password, "yuan"); port = 8000; /* * to connect to the database server with the given parameters */ if ((fd=sql_connection(hostname,port,dbname,username,password))<0) { printf("the connection failed\n"); return(-1); } /* * to close the connection */ close_connection( fd );

Example With the connection established, one can use sql_execution to send any SQL command to the database server for execution and receive the response back.

Here is what the code to call sql_execution looks like:

char command[BUFSIZE], response[BUFSIZE]; int fd, rset; ... /* * use sql_execution to create a table */ strcpy(command,"create table student (s_id int,s_name varchar(20))"); if ( (ret = sql_execution(fd, command, response, BUFSIZE )) < 0 ) { printf("the execution failed\n"); return(-1); } else { if ( response[0] == '1' ) printf("response: %s\n", &(response[1])); else printf("Error: %s\n", &(response[1])); }

Note that the response of the LogicSQL to any SQL command is a string starting with either 1 or 0, where 1 standing for the success of the execution while 0 for the failure of the execution.

Example Function sql_execution can be used to execute any SQL command, including SELECT statements. However, it is much better to use sql_selection to execute SELECT statements because the answer returned by sql_execution is just a string while the answer returned by sql_selection is a RSET.

Here is what the code to call sql_selection looks like:

int ret; RSET result_set; char command[BUFSIZE]; /* * to establish the connection to the database server */ ... /* * the result_set must be initialized before its use */ rset_create( &result_set ); /* * to select all the tuples in student table */ strcpy( command, "select * from student"); if ( ( ret = sql_selection( fd, command, result_set )) < 0 ) { printf("the selection failed\n"); return(-1); }

Example The following program first connects to the database server, and then creates a table named student and inserts one tuple into the newly created table. Finally, the program uses sql_selection to retrieve all the tuples in the table, and displays the result.

/* * A simple program to demonstrate how to use C interface */ #include <stdio.h> #include "lsql_c.h" #define BUFSIZE 1024 int main ( int argc, char *argv[] ) { int ret, index, fd, port, len; char command[BUFSIZE], response[BUFSIZE], hostname[BUFSIZE]; char dbname[BUFSIZE], username[BUFSIZE], password[BUFSIZE]; char buf[BUFSIZE], cell[BUFSIZE]; char *ptr; RSET *result_set; strcpy( hostname, "localhost"); strcpy( dbname, "database"); strcpy( username, "yuan"); strcpy( password, "yuan"); port = 8000; ptr = buf; /* * to connect to the database server with the given parameters */ if ((fd=sql_connection(hostname,port,dbname,username,password))<0) { printf("the connection failed\n"); return(-1); } /* * use sql_execution to create a table */ strcpy(command,"create table student(s_id int,s_name varchar(20))"); if ( (ret = sql_execution(fd, command, response, BUFSIZE )) < 0 ) { printf("the execution failed\n"); return(-1); } else { if ( response[0] == '1' ) printf("response: %s\n", &(response[1])); else printf("Error: %s\n", &(response[1])); } /* * to insert a tuple into the newly created table */ strcpy(command,"insert into student values(1234,'Harry Potter')"); if ( (ret = sql_execution(fd, command, response, BUFSIZE )) < 0 ) { printf("the execution failed\n"); return(-1); } else { if ( response[0] == '1' ) printf("response: %s\n", &(response[1])); else printf("Error: %s\n", &(response[1])); } strcpy( command, "insert into student values( 5678, 'Peter Pan')"); if ( (ret = sql_execution(fd, command, response, BUFSIZE )) < 0 ) { printf("the execution failed\n"); return(-1); } else { if ( response[0] == '1' ) printf("response: %s\n", &(response[1])); else printf("Error: %s\n", &(response[1])); } /* * the result_set must be initialized before its use */ rset_create( &result_set ); /* * to select all the tuples in student table */ strcpy( command, "select * from student"); if ( ( ret = sql_selection( fd, command, result_set )) < 0 ) { printf("the selection failed\n"); return(-1); } printf("\n\n"); /* * display the column name */ for (index=1; index<=result_set->get_degree(result_set);index++ ) { len = result_set->get_column( result_set, index, &ptr ); strncpy( cell, ptr, len ); cell[len] = '\000'; printf(" %s ", cell); } printf("\n----------------------------------\n"); /* * display all the rows in result_set */ len = 1; while ( len > 0 ) { for (index=1; index<=result_set->get_degree(result_set);index++) { len = result_set->get_next( result_set, &ptr ); if ( len < 1 ) break; strncpy( cell, ptr,len ); cell[len] = '\000'; printf(" %s ", cell); } printf("\n"); } printf("\n\n"); /* * to close the connection */ close_connection( fd ); }

The output of the above program is given below.

response: the table is created. response: the tuple has been inserted response: the tuple has been inserted s_id s_name ---------------------------------- 1234 Harry Potter 5678 Peter Pan

Running the above program second time will generate the following output.

Error: CREATE TABLE: the table exists Error: INSERTION: the tuple exists Error: INSERTION: the tuple exists s_id s_name ---------------------------------- 1234 Harry Potter 5678 Peter Pan

The following program demonstrate how to create a table with a lob column and how to populate it.

/* * A simple program to demonstrate how to use lobs_operation */ #include <sys/types.h> #include <stdio.h> #include <getopt.h> #include "lsql_c.h" #define ROWSIZE 1024 #define BUFSIZE 65536 int main ( int argc, char *argv[] ) { int ret, index, fd, port, len; char command[ROWSIZE], response[ROWSIZE], hostname[ROWSIZE]; char dbname[ROWSIZE], username[ROWSIZE], password[ROWSIZE]; char buf[ROWSIZE], cell[ROWSIZE], lob_locator[ROWSIZE]; char *ptr; RSET *result_set; strcpy( hostname, "localhost"); strcpy( dbname, "database"); strcpy( username, "yuan"); strcpy( password, "yuan"); port = 8000; ptr = buf; /* * to connect to the database server with the given parameters */ if ((fd=sql_connection(hostname,port,dbname,username,password))<0) { printf("the connection failed\n"); return(-1); } /* * use sql_execution to create a table */ strcpy( command, "create table photos (photo_id int, title varchar(96), image blob, constraint photos_primary_key_constraint primary key(photo_id) )"); if ( (ret = sql_execution(fd, command, response, ROWSIZE )) < 0 ) { printf("the execution failed\n"); return(-1); } else { if ( response[0] == '1' ) printf("response: %s\n", &(response[1])); else { printf("Error: %s\n", &(response[1])); } } /* * to insert a tuple into the newly created table */ strcpy( command, "insert into photos values( 1234, 'test', empty_blob )"); if ( (ret = sql_execution(fd, command, response, ROWSIZE )) < 0 ) { printf("the execution failed\n"); return(-1); } else { if ( response[0] == '1' ) printf("response: %s\n", &(response[1])); else printf("Error: %s\n", &(response[1])); } /* * to update the empty_blob with a lob value stored in a local file * with name "id.jpg" */ put_one_lob( fd, "photos", "image", "where photo_id = 1234", "id.jpg", 0); /* * to close the connection */ close_connection( fd ); }

4.4.2 sql_connection

int
sql_connection(char *host, int port, char *dbname, char *user, char *passwd);

Description

This function makes a new connection to the LogicSQL server. A valid file descriptor is returned on success and a negative error number is returned if otherwise.

Parameters

• host Name or IP address of the host to connect to. The IP address must be in standard numbers-and-dots form. Using the name of the host will cause a hostname look-up. The localhost will be used if it is an empty string.
• port Port number to connect to at the server host.
• dbname The database name to be connected.
• user The user name to connect as.
• passwd The password of the user.

Errors

HOST_NOT_FOUND, NO_FD_ERROR, FAIL_TO_SEND, FAIL_TO_RECEIVE, AUTHORIZATION_FAILED

4.4.3 close_connection

int close_connection( int fd );

Description

This function closes the current connection through the given fd to the LogicSQL server.

Parameters

• fd the file descriptor that connects to the logicsql.

Errors

HOST_NOT_FOUND, NO_FD_ERROR, FAIL_TO_SEND, FAIL_TO_RECEIVE, AUTHORIZATION_FAILED

4.4.4 sql_execution

int
sql_execution( int fd, char *command, char *buf, int buf_size );

Description

This function sends a command to the server to execute and stores the returned message from the server in buf. The function returns the length of the message received from the server on success and -1 on failure.

The success of this function does not necessarily imply the success of the execution.

The success of the execution depends on the first char in buf. In fact, the execution succeeds if *buf == '1' and fails if *buf == '0' . Therefore, buf stores the error message returned from the server if *buf == '0'.

Parameters

• fd the file descriptor of the client connection. This should be the one returned by sql_connection.
• command the starting address of the SQL command to be executed. It can be any SQL command supported by LogicSQL. However, it is better to use sql_selection for queries because the answer received there is a result set RSET, not a plain string.
• buf the starting address of the contiguous memory location to which the response string from the server will be copies.
• buf_size the size of the buf, which must be larger than STRING_SIZE.

4.4.5 sql_selection

int
sql_selection( int fd, char *command, RSET *result_set );

Description

Similar to sql_execution, sql_selection, as its name suggested, executes an SQL selection command. If the execution succeeds, the function assigns the answer to the result set, and returns 1. If the execution fails, the function returns 0. The function returns -1 if it fails.

Parameters

• fd the file descriptor of the client connection. This should be the one returned by sql_connection.
• command the starting address of the select statement to be executed. It must be an selection command, including those containing relational operators such as UNION, EXCEPT, etc.
• result_set A structure storing the query answer.

4.4.6 put_one_lob

long
put_one_lob(int fd, char *table, char *column, char *where, char *file, int flags );

Description

The function is used to update a lob value (usually an empty lob) with a new lob value stored in the given file. The function is based on a system command

UPDATE table SET column = new_lob WHERE condition.

The function returns the size of the lob value if the execution succeeds, and -1 if it fails.

The function must be used as an atomic operation. It will return -1 if it is used within an active transaction ( between set transaction and commit ).

Parameters

• fd the file descriptor of the client connection. This should be the one returned by sql_connection.
• table the table in which the lob is inserted. This must be a standard SQL representation of the table name such as database.yuan.photos, or photos.
• column the column whose data type is clob, blob, or long raw.
• where the valid SQL WHERE clause.
• file the source/destination of the lob value. It is either a file name in the clients local disk system or a memory space in the client system, depending on the flag.
• flags the flags with the following possible values:
  • FILE_TO_LOB: to up load the lob value stored in the given file to the storage specified by the lob locator. The file name must be a valid disk name.
  • MEMORY_TO_LOB: to up load the lob string pointed by the file name.
  • LOB_TO_FILE: to down load the lob value pointed by the lob locator to the file with the given file name.
  • LOB_TO_MEMORY: to down load the lob value pointed by the lob locator to the file referenced by sql_connection.