TO_CHAR( given_value [,format_string] )

Description

This function is used to convert a given date value or a numeric value into a string. The optional format string specifies the output format of the converted string.

• If the given_value is a date/time, then the format_string is the same as that of strftime in time.h.
• If the given_value is a numeric then the format_string is the same as that for the C format specification for functions like printf.

Example

LogicSQL> select to_char(date '2005-03-22') "DATE " from dual; "DATE " ---------------------- 2005-03-22 00:00:00 LogicSQL> SELECT TO_CHAR(-10000, 'RMB %.2f') "Amount" FROM DUAL; "Amount" ---------------- RMB -10000.00

Avoid to use the printf format identifiers like %d for non-integers. For example,

LogicSQL> SELECT TO_CHAR(-1000.00, 'RMB %2d') "Amount" FROM DUAL; Amount --------- RMB 0 LogicSQL> SELECT TO_CHAR(-1000.00, 'RMB %2f') "Amount" FROM DUAL; Amount ------------------- RMB -1000.000000 LogicSQL> SELECT TO_CHAR(-1000.00, 'RMB %2.2f') "Amount" FROM DUAL; Amount --------------- RMB -1000.00

2.2.6 CHR

• Syntax

  CHR(n)

• Function

  CHR returns the character having the binary equivalent to n.

  If n > 256, then RubatoDB 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.7 INSTR

• Syntax

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

• Function

  Searches string1 beginning with its nth character for the m'th occurrence of string2 and returns the position of the character in string1 that is the first character of this occurrence. If n is negative, RubatoDB 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 RubatoDB 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.8 CURRENT_CERTIFICATE

Function: CURRENT_CERTIFICATE

• Syntax

  CURRENT_CERTIFICATE

• Function

  CURRENT_CERTIFICATE returns the (security) certificate carried by the current session.

2.2.9 CURRENT_GROUP

Function: CURRENT_GROUP

• Syntax

  CURRENT_GROUP

• Function

  CURRENT_CERTIFICATE returns the list of security groups carried by the current session.

  Usage

  The following example demonstrates how to use the current_group function to list all groups in the current group

  LogicSQL> SELECT GROUP_NAME FROM SYSTEM.DEFINITION_SCHEMA.GROUPS WHERE GROUP_NAME IN CURRENT_GROUP; group_name ------------- capital develop employee enterprise LogicSQL>

2.2.10 ALL_GROUP

Function: ALL_GROUP

• Syntax

  ALL_GROUP ('< group name>')

• Syntax restriction

  The group name must be a constant, i.e., a string representing the given group name.

• Function

  ALL_GROUP returns the list of security groups contained in the given group name, including the given group name.

The following query returns the list of all groups that are contained in the given group named 'employee'.

LogicSQL> SELECT DISTINCT GROUP_NAME FROM SYSTEM.DEFINITION_SCHEMA.GROUPS WHERE GROUP_NAME IN ALL_GROUP('employee'); group_name ------------- develop employee market LogicSQL>

Further, the following query returns the list of all security certificates that are contained in the given group named 'enterprise'.

LogicSQL> SELECT DISTINCT certificate FROM SYSTEM.DEFINITION_SCHEMA.GROUP_authorization_descriptors WHERE status = 'certificate' and GROUP_NAME IN ALL_GROUP('enterprise'); certificate -------------- peter phillis sarah tim LogicSQL>

2.2.11 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, RubatoDB commands and statements.

All RubatoDB statements are classified into the following categories:

• 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

RubatoDB 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
• CLEAR SESSION (NEW SESSION)

Running Example 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 Business LogicSQL> select * from course; cid title description --------------------------------------------------------- AR100 Cartoon Drawing A comic 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', 'Businese'); 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 comic 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 RubatoDB statements

2.3.1 ABORT		Abort the current operation
2.3.2 ALTER TABLE		Alter the table definition
2.3.3 ALTER TRIGGER		Alter the trigger operation
2.3.4 ALTER USER		Alter the user password
2.3.5 CLOSE CURSOR		Close a given cursor
2.3.6 COMMIT		Commit the current transaction
2.3.7 CREATE AIO CURSOR		Create a cursor table for AIO queries
2.3.8 CREATE AIO SCHEMA		Create an artificial intellgent Optimazation Schema
2.3.9 CREATE DATABASE		Create a new database
2.3.10 CREATE DATATYPE		To be implemented
2.3.11 CREATE INDEX		Create an index for a given table
2.3.25 CREATE INVERTED CURSOR		Create a temporary table for text queries
2.3.12 CREATE INVERTED INDEX		Create an inverted index for text query
2.3.13 CREATE PROCEDURE		Create a stored procedure
2.3.14 CREATE ROLE		Create an authorization role
2.3.15 CREATE SCHEMA		Create tables and views and perform multiple grants in a single transaction
2.3.16 CREATE SEQUENCE		Create a sequence of integers
2.3.17 CREATE TABLE		Create a new table
2.3.18 CREATE TRIGGER		Create a new trigger
2.3.19 CREATE USER		Create a new user
2.3.20 CREATE VIEW		Create a view
2.3.21 DEALLOCATE		Deallocate a prepare statement
2.3.22 DECLARE CURSOR		Declare a session cursor
2.3.26 DELETE		Delete rows from a table
2.3.27 DROP CURSOR		Drop a session cursor
2.3.23 DROP AIO CURSOR		Close an AIO cursor for the session
2.3.28 DROP DATABASE		Remove an existing database
2.3.29 DROP INDEX		Remove an existing index
2.3.24 DROP INVERTED CURSOR		Close an inverted cursor for the session
2.3.30 DROP PROCEDURE		Remove a stored procedure
2.3.31 DROP ROLE		Remove an existing role
2.3.32 DROP SCHEMA		Remove an existing schema
2.3.33 DROP SEQUENCE		Remove a existing sequence
2.3.34 DROP TABLE		Remove an existing table
2.3.35 DROP TRIGGER		Remove an existing trigger
2.3.36 DROP USER		Remove an existing user
2.3.37 DROP VIEW		Remove an existing view
2.3.38 DISABLE/ENABLE CONSTRAINT ENFORCEMENT		Enable/disable constraint enforcements
2.3.39 EXECUTE PROCEDURE		Execute a stored procedure
2.3.40 EXECUTE STATEMENT		Execute a prepare statement
2.3.41 FETCH		Fetch a row from the result set pointed by the given cursor
2.3.42 GRANT PRIVILEGE		Grants access privilege
2.3.43 GRANT ROLE		Grants roles to users and roles
2.3.44 INSERT		Inserts new rows into a table
2.3.45 LOCK TABLE		Lock entrie database table in a specific mode
2.3.46 LOAD TABLE		Load the data into the table
2.3.47 OPEN CURSOR		Open a cursor
2.3.48 PREPARE		Declare a prepare statement
2.3.49 RELEASE SAVEPOINT		Release an established savepoint
2.3.50 REMOVE LOB		Remove a BLOB/CLOB
2.3.51 REQUEST LOBLOCATOR		Request a new lob locator
2.3.52 REVOKE PRIVILEGE		Revoke access privilege
2.3.53 REVOKE ROLE		Revoke roles from users and roles
2.3.54 ROLLBACK		Aborts the current transaction
2.3.55 SAVEPOINT		Establish a savepoint for the transaction management
2.3.56 SET LANGUAGE		Set the language code for the current SQL session
2.3.57 SET ROLE		Set the role for the current SQL session
2.3.58 SELECT and Subquery		Query the database
2.3.59 SET AUTO_COMMIT		Set the auto_commit on/off for the client
2.3.60 SET SESSION USER		Set the current session user
2.3.61 SET TRANSACTION		Set the isolation level of the current transaction
2.3.62 SHOW STATEMENT		Retrieve the system information
2.3.63 SQL LOAD/DUMP		Load and/or dump an SQL table
2.3.64 UNLOCK TABLE		Unlock the table (to resume normal accessess)
2.3.65 USE DATABASE		Set the default database (catalog) for the session
2.3.66 UPDATE		Update column values of a table

List of extended SQL statement and functions used to implement 2.8 RubatoDB Text and Search.

SQL statements	Descriptions
2.3.12 CREATE INVERTED INDEX	Create an inverted index for text queries
2.3.25 CREATE INVERTED CURSOR	Create a temporary table for text queries using the top k algorithm
2.3.24 DROP INVERTED CURSOR	Close an inverted cursor
SQL functions	Descriptions
2.8.5 CONTAINS	Return ranking factor for text queries
2.8.4 CURSOR_TABLE	a function mapping an inverted cursor to a temporary table
AGGREGATE FUNCTION	Return a temp table for TOP K queries

List of extended SQL statements and functions used to implement the RubatoDB's 2.6.5 extended Unix security model.

SQL statements	Descriptions
2.3.67 ADD INTO GROUP	Add certificates/groups to a group
2.3.68 ALTER CERTIFICATE	Alter the password of the certificate
2.3.69 CREATE CERTIFICATE	Create a new security certificate
2.3.70 CREATE GROUP	Create a new security group
2.3.71 DROP CERTIFICATE	REMOVE a security certificate
2.3.72 DROP GROUP	Drop an existing security group
2.3.73 RELEASE CERTIFICATE	Release a certificate for the current session
2.3.74 REMOVE FROM GROUP	Remove certificates/groups from a group
2.3.75 REQUEST CERTIFICATE	Request a certificate for the current session
SQL functions	Descriptions
2.2.8 CURRENT_CERTIFICATE	Return the certificate carried by the current session
2.2.9 CURRENT_GROUP	Return the list of security groups of the current certificate
2.2.10 ALL_GROUP	Return the list of all groups contained in the given group

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 TABLE

• Function Modify the table definition

• Syntax

  ALTER TABLE < table name> < alter table action>

  This statement has been partially implemented. More specifically, the following parts of < alter table action> are currently supported.

  • ALTER TABLE < table name> MODIFY [COLUMN] < column name> DEFAULT (< literal value>)

  • ALTER TABLE < table name> ADD [COLUMN] < column definition>

  • ALTER TABLE < table name> DROP [COLUMN] < column name> [CASCADE|RESTRICT]

  • ALTER TABLE < table name> ALTER [COLUMN] < column definition>

  • ALTER TABLE < table name > ADD < table constraint definition>

• Syntax and Semantic Restrictions
  • No constraints shall be specified when a new column is added into a table. Use ADD CONSTRAINT after a column has been added if one is needed.

  • ADD COLUMN will be rejected if the table is currently updated by other transactions.

  • DROP COLUMN will be rejected if RESTRICT is specified in DROP COLUMN and the column is referenced in any constraints, triggers, or view definitions. If CASCADE is specified, on the other hand, any schema object referencing the column to be dropped will also be dropped.

    The default drop behavior of DROP COLUMN is RESTRICT. (Currently, RubatoDB supports only RESTRICT drop behavior.

• Usage

2.3.3 ALTER TRIGGER

• Function Disable and/or ENABLE a trigger operation

• Syntax

  ALTER TRIGGER < identifier> ENABLE | DISABLE

• Usage

  ALTER USER employee_insert_trigger DISABLE

  Will disable the trigger employee_insert_trigger

2.3.4 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.5 CLOSE CURSOR

• Function Close a session cursor

• Syntax

  CLOSE < cursor name>

• Usage

  CLOSE current_student;

  This will close the given cursor.

See also 2.3.22 DECLARE CURSOR, 2.3.47 OPEN CURSOR, 2.3.27 DROP CURSOR, and 2.3.41 FETCH statements.

2.3.6 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 [<WORKS>]

• Usage COMMIT

• Note

  See also 2.3.61 SET TRANSACTION and 2.3.54 ROLLBACK.

2.3.7 CREATE AIO CURSOR

• Function DECLARE and OPEN an AIO cursor for the current session

• Syntax

  CREATE AIO CURSOR < cursor name>
FOR < aio query>
[ < order by clause> ]


  SELECT [< fetch orientation>]

  CREATE AIO CURSOR < cursor name>
FOR < subquery>
[ < order by clause> ]


• Usage

  It is a difficult but very interesting task to properly order the federated search based on different inverted indexes.

  The inverted cursor of RubatoDB Text and Search, based on the top k query algorithms provides a powerful tool for the federated search.

  Consider the following table, together with three inverted indexes created on different columns of the table.

  create table text_files( t_id int, t_title varchar(100), t_anchor char(1000), content clob, primary key(t_id) ); create inverted index inverted1 on text_files(content); create inverted index inverted2 on text_files(t_anchor); create inverted index inverted3 on text_files(t_title);

  The federated search shall be based on three indexed columns but with different weights for obvious reasons, which can be easily expressed with the following inverted cursor.

  CREATE INVERTED CURSOR search FOR SELECT t_id, t_title, t_anchor FROM text_files WHERE contains(t_title,'database', 1) OR contains(t_anchor, 'database'|'file management', 2)>0 OR contains(content, 'database' & 'file', 3)>0 ORDER BY 6 * score(1) + 2.5 * score(2) + 1.5 *score(3)

  The main advantages of INVERTED CURSOR are the following

  • Because the inverted cursor is implemented using the top k algorithm, the performance of evaluating an inverted cursor with say five (5) inverted indexes is much faster than evaluating a regular query with five (5) contains functions in its where clause, and
  • Using 2.8.4 CURSOR_TABLE, the developer can easily join any other tables with the first 20 rows of the cursor, the next 20 rows of the cursor, and so on, which is a useful feature in a search engine.
• Restrictions

  The inverted cursor subjects to the following restrictions:

  • Only one text file is allowed in the cursor and all the contains in the cursor must be specified on the same document. The reason behind this restriction is obvious: it does not make sense to rank documents in different text files.
  • The where clause in the cursor must be the disjunctions of various contains functions. Since we are going to rank the documents according to all the contains, the conjunction of contains formulas will not serve our purpose.

  Similar to the 2.3.22 DECLARE CURSOR, the AIO CURSORs are valid only for the current session and will be dropped automatically when the session terminates.

2.3.8 CREATE AIO SCHEMA

• Function Create an artificial intelligent optimization (AIO) schema for query optimization.

• Syntax

  CREATE ARTIFICIAL INTELLIGENT OPTIMIZATION SCHEMA ON < table name> ( < column name>)

  An AIO schema is created for a pair of a given table and a given column, and only one schema can be created for one pair of the table and column.

  The following restrictions are also applied for the current version:

  • An AIO schema can only be create on a table with the primary key of a single column;
  • An AIO schema can only be created on a column of the varchar type.

• Usage

  CREATE ARTIFICIAL INTELLIGENT OPTIMIZATION SCHEMA ON customer ( customer_name )

  This will create an AIO schema on column customer_name of the table customer such that all the queries of the form customer_name LIKE 'Ph%llis' can be evaluated efficiently. As with the standard like clause, the formula customer_name LIKE 'Ph%llis' is evaluated to TRUE if and only if the customer name contains 'Ph' followed by 'llis', such as 'Phillis', 'Phyllis', or 'Pheelis'.

  In general, the following two types of queries will be benifited with the AIO schema:

  • the like query of the form:

    SELECT primary_key_column FROM given_table WHERE given_column like '%12%ab';

  • the substring query of the form:

    SELECT primary_key_column FROM given_table WHERE substring(given_columnm,m,n) = 'be cool';

  Consider a table that stors all the information of vehicles recorded by a surveillance system at various intersections.

  CREATE TABLE vehicle_flow ( record_id int primary key, plate_info varchar(8), intersection_id int, pass_time date, vehicle_type varchar(10), vehicle_color int );

  The following SQL script file demos how the AIO schema works.

  create table BV ( VEHICLELSH INTEGER, PLATEINFO VARCHAR2(15), constraint PK_VEHIPASS primary key(VEHICLELSH) ); CREATE Artificial Intelligent Optimization Schema on BV(PlateInfo); INSERT INTO bv VALUES(100,'abcdefgh'); INSERT INTO bv VALUES(200,'abA12345'); INSERT INTO bv VALUES(300,'abA123ab'); INSERT INTO bv VALUES(400,'abA98765'); INSERT INTO bv VALUES(500,'abA09845'); INSERT INTO bv VALUES(600,'abA13243');

  The following are three query results:

  LogicSQL> select * from bv; VEHICLELSH PLATEINFO ------------------------- 100 abcdefgh 200 abA12345 300 abA123ab 400 abA98765 500 abA09845 600 abA13243 LogicSQL> select vehiclelsh from bv where substr(plateinfo,2,3)= 'bA1'; id_number ------------ 200 300 600 LogicSQL> select vehiclelsh from bv where substr(plateinfo,4,3)= '123'; id_number ------------ 200 300 LogicSQL>

  • The following lists all the record_id whose plate_info constains a patten of 'C23'.

    SELECT record_id FROM vechile_flow WHERE plate_info like '%C23%';

  • The following lists all the record_id whose plate_info constains a partten of 'CB', starting at the third char.

    SELECT record_id FROM vechile_flow WHERE substr(plate_info,3,2) = 'CB';

2.3.9 CREATE DATABASE

• Function Create a database ( catalog in SQL99 terminology ) as well as a schema, whose name is the same as the user name, under the new database

• Syntax

  CREATE <CATALOG|DATABASE> < catalog name>

• Usage

  CREATE CATALOG my_database

  This will create a database ( catalog ) my_database.

  CREATE DATABASE her_database

  This will create a database (catalog) her_database. her_database

  One needs CREATE ANY CATALOG privilege to create a catalog.

  See Also: 2.3.28 DROP DATABASE

2.3.10 CREATE DATATYPE

2.3.11 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. RubatoDB 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 RubatoDB has to build the index file.

  See Also: 2.3.29 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.12 CREATE INVERTED INDEX

• Function Use the CREATE INVERTED INDEX statement to create an inverted index on one columns of a table. An inverted index is a schema object that consists a sequence of ({key word}, key_value) pairs where key_value points to the row of the column which contains the key_word. See 2.8 RubatoDB Text and Search for details.

• Syntax

  CREATE INVERTED INDEX < index name> ON < table name> (< column name>)
[DICTIONARY < table name>]
[STOP LIST < table name>]
[GROUP BY < column name>]

• Syntax Restrictions

  The following restrictions are applied to the inverted index.

  • An inverted index can be created only on text columns, that is, on columns whose datatype are VARCHAR, CHAR, CHARACTER_DATA, or CLOB.

  • An inverted index can only be created on a table with a single column as its primary key. The singular primary key column is viewed as the document ID for the inverted index.

  • The table specified in the DICTIONARY clause is called a dictionary table, that is a table with one column storing all the words in the dictionary. Similarly, the table specified in the STOP List clause is also a dictionary table that contains the list of all stop words for the inverted index. (RubatoDB currently supports only the default dictionary and stop list.)

  • All the meta objects in this statement, including the index, the text table, the dictionary table, and the stop list table, must belong to the same schema, for simplicity.

  • An inverted index is created to indexing text documents stored in one column, and a column can be indexed by at most one inverted index.

  • The default dictionary is the list of all words in the documents that are separated by white spaces, and the default stop list is an empty list.

• The group by clause specifies the column by which documents and indexes words are partitioned.
• Usage

  CREATE INVERTED INDEX key_word_index ON book(abstract )

  This will create an inverted index on column abstract of the table book such that you may retrieve all relevant books whose abstracts contain the specified key words

  CREATE INVERTED INDEX subject_index ON book(abstract) GROUP BY subject

  This will create an inverted index the same as the previous one, except that all the words in the inverted index are partitioned according to the values of subject. Consider the following book table

  document_id	subject	abstract	isbn
  10	math	beautiful language	12345
  20	english	strange language	23456
  30	math	math is strange	34567
  40	novel	mission impossible	45678
  50	novel	as strange as impossible	56789
  60	english	language is not math	67890

  For this example the first index, key_word_index, shall consist of the following entries.

  word	document_id
  as	50
  beautiful	10
  impossible	40, 50
  is	30, 60
  language	10, 20, 60
  math	30, 60
  mission	40
  not	60
  strange	20, 50

  The second index, subject_index, shall consist of the following entries.

  subject word	document_id
  english	is	60
  english	language	20, 60
  english	math	60
  english	not	60
  english	strange	20
  math	beautiful	10
  math	is	30
  math	language	10
  math	math	30
  novel	as	50
  novel	impossible	40, 50
  novel	mission	40
  novel	strange	50

  The group by clause can be used to partition the inverted index such that the text query based on specified group can be efficiently evaluated.

  See Also: 2.3.29 DROP INDEX

  you must have CREATE INVERTED INDEX schema privilege on the schema, unless you have CREATE ANY INVERTED INDEX system privilege.

2.3.13 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.

  Note that to facilitate the use of stored procedures, the default length for VARCHAR and CHAR in stored procedures has been re-set to that of string length specified in the configuration file.

• 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> THEN < SQL procedure statement list> [{ WHEN < variable name> = < integer> THEN < 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 RubatoDB statement, except < subquery>, which is replaced by < select into statement>.

  Some Notations

  1. The scope of any declared variable is the immediate 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 RubatoDB 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 statement returns an empty result set, the execution of the stored procedure will continue. That is, RubatoDB 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, RubatoDB 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: 2.3.39 EXECUTE PROCEDURE, 2.3.30 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);

  The executions of the last two call procedure statements raise different exceptions, as expected.

  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.14 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 2.6 Security Model.
• Syntax

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

  <role name> ::= < identifier>

  <granter> ::= 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 < granter>] is not specified, then the admin option is granted to the current user.

  See Also: 2.3.31 DROP ROLE, 2.3.43 GRANT ROLE, 2.3.53 REVOKE ROLE.

2.3.15 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, RubatoDB executes each included statement. If all statements execute successfully, RubatoDB commits the transaction. If any statement results in an error, RubatoDB rolls back all the statements.

  Note: This statement does not necessarily create a schema. RubatoDB automatically creates a schema named after user when one creates a user (see 2.3.19 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 whites;

  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 whites.

2.3.16 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]

  RubatoDB will commit the current transaction following the 2.3.16 CREATE SEQUENCE and/or 2.3.33 DROP SEQUENCE statement.

• 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 RubatoDB 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

  One can also access the next value of a sequence in SQL statements with pseudocoumns:

  sequence.NEXTVAL,

  or qualify the name of the sequence in the schema of another user using

  schema.sequence.NEXTVAL or catalog.schema.sequence.NEXVAL.

  The following sequence of SQL statements demonstrate how to use a sequence to generate a sequence of primary keys for the application.

  CREATE SEQUENCE item_id START WITH 10 INCREMENT BY 2; CREATE TABLE items ( item_id int, item_name varchar(100), primary key (item_id) ); insert into items values( item_id.nextval, 'be cool'); insert into items values( item_id.nextval, 'boring'); insert into items values( item_id.nextval, 'nothing to do'); SELECT * FROM items; LogicSQL> select * from items; item_id item_name -------------------------- 10 be cool 12 boring 14 nothing to do

• Example 3

  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('Volkswagenn Rabbit'); SELECT * FROM items;

  The execution of the above program populates items as follows.

  item_id item_name ---------------------------- 10 Nikon 5400 12 DELL Inspiration 14 Volkswagen Rabbit

2.3.17 CREATE TABLE

• Function Create a table.

• Syntax

  CREATE TABLE < table name> (< table element> [{, < table element> ... } ]) [CACHE SIZE(< gbyte>,< mbyte>)] [GRID (< grid id>)] }} CREATE TABLE < table name> AS < subquery>

  As with all other SQL statements supported by RubatoDB, see the SQL99 for the syntax rules and restrictions of the CREATE TABLE statement.

• Syntax Details

  <table element> ::= < column definition> | < table constraint definition>

  <column definition> ::= < column name> < data type> [ < default clause> | < identity clause>] [ < column constraint definition>... ]

  <column constraint definition> ::= [CONSTRAINT < constraint name> ] < column constraint>

  <column constraint> ::= NOT NULL | <UNIQUE | PRIMARY KEY > | < references specification> | < check constraint definition>

  <table constraint definition> ::= [CONSTRAINT < constraint name> ] < table constraint>

  <table constraint> ::= < unique constraint definition> | < referential constraint definition> | < check constraint definition>

  <unique constraint definition> ::= <UNIQUE | PRIMARY KEY> [ ( < column list> ) ]

  <referential constraint definition> ::= FOREIGN KEY [ ( < column list> ) ] < references specification>

  <references specification> ::= REFERENCES < table name> [ ( < column list> ) ] [ < referential triggered action> ]

  <referential triggered action> ::= < update rule> [ < delete rule> ] | < delete rule> [ < update rule> ]

  <update rule> ::= ON UPDATE < referential action>

  <delete rule> ::= ON DELETE < referential action>

  <referential action> ::= CASCADE | SET NULL | SET DEFAULT | NO ACTION

  <check constraint definition> ::= CHECK ( < search condition> )

  <default clause> ::= {<DEFAULT | := > < default option>}

  <default option> ::= <literal> | < datetime value function> | USER | CURRENT_USER | SESSION_USER | SYSTEM_USER | CURRENT_PATH | NULL

  <grid id> ::= < integer>

  <gbyte> ::= < integer>

  <mbyte> ::= < integer>

• Implementation Status

  The following features are accepted by the RubatoDB's parser, but have not yet been implemented:

  • the check constraint
  • all the constraint enforcement for the update statement.
  Further, because of (the lack of) the check constraint, the enforcement for the foreign constraint is implemented in a very limited and inefficient way (see the program comments for the details).
• 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.

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

  This will create a table take stored in the grid node whose id is 2.

  See Also: 2.3.34 DROP TABLE

2.3.18 CREATE TRIGGER

• Function

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

• Syntax

  <create trigger> ::= CREATE TRIGGER < trigger name> <BEFORE | AFTER | INSTEAD OF > < trigger event > [< firing order>] 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. RubatoDB will choose one trigger to be executed.
  • Instead of taking any measure to detect circular executions of triggers, RubatoDB limits the number of triggers triggered by the execution of one trigger. The limitation is specified by a configuration variable MaxTriggerCalls.

• Usage

  See Also: 2.3.35 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. RubatoDB does not provide a mechanism to detect recursive triggers. Instead, RubatoDB 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.19 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 2.3.4 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: 2.3.36 DROP USER

2.3.20 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|CHECK OPTION> ]

  Note that the order by clause may be accepted in a create view statement, but it will be ignored.

• 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.21 DEALLOCATE

• Function Deallocate (drop) a prepare statement

• Syntax

  DEALLOCATE < statement name>

• Usage

  DEALLOCATE find_student;

  See also 2.3.48 PREPARE, 2.3.40 EXECUTE STATEMENT.

2.3.22 DECLARE CURSOR

• Function Define a session cursor

• Syntax

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

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

• Usage

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

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

  We may also use this statement to declare a cursor for text queries, shown below.

  DECLARE search_paper CURSOR FOR SELECT document_id FROM text_files WHERE contains(author, 'John Done', 1) > 0 OR contains(anchor, 'database', 2) > 0 OR contains(content, 'database' & 'b-tree', 3) > 0 ORDER BY 3 * score(1) + 5 * score(2) + 2 * score(3) FOR SEARCH ONLY

  See also 2.3.47 OPEN CURSOR, 2.3.27 DROP CURSOR, 2.3.5 CLOSE CURSOR, and 2.3.41 FETCH statements.

A cursor can be classified 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 either 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; close(gpas); 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.23 DROP AIO CURSOR

• Function Close an AIO cursor for the current session

• Syntax

  DROP AIO CURSOR < cursor name>

• Notes

  See 2.3.7 CREATE AIO CURSOR.

2.3.24 DROP INVERTED CURSOR

• Function Close an inverted cursor for the current session

• Syntax

  DROP INVERTED CURSOR < cursor name>

• Notes

2.3.25 CREATE INVERTED CURSOR

• Function DECLARE and OPEN an inverted cursor for the current session

• Syntax

  CREATE INVERTED CURSOR < cursor name>
FOR < subquery>
[ < order by clause> ]


• Usage

  It is a difficult but very interesting task to properly order the federated search based on different inverted indexes.

  The inverted cursor of RubatoDB Text and Search, based on the top k query algorithms provides a powerful tool for the federated search.

  Consider the following table, together with three inverted indexes created on different columns of the table.

  create table text_files( t_id int, t_title varchar(100), t_anchor char(1000), content clob, primary key(t_id) ); create inverted index inverted1 on text_files(content); create inverted index inverted2 on text_files(t_anchor); create inverted index inverted3 on text_files(t_title);

  The federated search shall be based on three indexed columns but with different weights for obvious reasons, which can be easily expressed with the following inverted cursor.

  CREATE INVERTED CURSOR search FOR SELECT t_id, t_title, t_anchor FROM text_files WHERE contains(t_title,'database', 1) OR contains(t_anchor, 'database'|'file management', 2)>0 OR contains(content, 'database' & 'file', 3)>0 ORDER BY 6 * score(1) + 2.5 * score(2) + 1.5 *score(3)

  The main advantages of INVERTED CURSOR are the following

  • Because the inverted cursor is implemented using the top k algorithm, the performance of evaluating an inverted cursor with say five (5) inverted indexes is much faster than evaluating a regular query with five (5) contains functions in its where clause, and
  • Using 2.8.4 CURSOR_TABLE, the developer can easily join any other tables with the first 20 rows of the cursor, the next 20 rows of the cursor, and so on, which is a useful feature in a search engine.
• Restrictions

  The inverted cursor subjects to the following restrictions:

  • Only one text file is allowed in the cursor and all the contains in the cursor must be specified on the same document. The reason behind this restriction is obvious: it does not make sense to rank documents in different text files.
  • The where clause in the cursor must be the disjunctions of various contains functions. Since we are going to rank the documents according to all the contains, the conjunction of contains formulas will not serve our purpose.
  See also 2.3.24 DROP INVERTED CURSOR, 2.8.4 CURSOR_TABLE, and 2.3.41 FETCH statements. current session and will be dropped automatically when the session terminates.

2.3.26 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>

• Usage

  DELETE FROM sarah.take where course = 391 DELETE sarah.take where course in (SELECT course FROM courses WHERE term = 'Fall 2007')

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

2.3.27 DROP CURSOR

• Function Drop a session cursor

• Syntax

  DROP < cursor name>

• Usage

  DROP current_student;

  This will drop the cursor with the given name.

  A cursor, after its declaration, can be opened and closed repeatedly. It is valid until the end of the current session or being either explicitly dropped by the 2.3.27 DROP CURSOR statement.

  Note that 2.3.27 DROP CURSOR will remove the cursor definition from the system while a cursor closed by the 2.3.5 CLOSE CURSOR statement may be opened again using the 2.3.47 OPEN CURSOR statement.

See also 2.3.22 DECLARE CURSOR, 2.3.47 OPEN CURSOR, 2.3.5 CLOSE CURSOR, and 2.3.41 FETCH statements.

2.3.28 DROP DATABASE

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

  See Also: 2.3.9 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: 2.3.9 CREATE DATABASE One needs CREATE ANY CATALOG privilege to create and/or drop a catalog.

2.3.29 DROP INDEX

• Function Remove an index from the database.

  See Also: 2.3.11 CREATE INDEX

• Syntax

  DROP INDEX < index name>

• Usage

  DROP INDEX customer_name_index

  This will drop the index

  See Also: 2.3.11 CREATE INDEX

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

2.3.30 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: 2.3.13 CREATE PROCEDURE

2.3.31 DROP ROLE

• Function Remove a role from the database. When a role is dropped, RubatoDB 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: 2.3.14 CREATE ROLE, 2.3.43 GRANT ROLE, 2.3.53 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.32 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: 2.3.15 CREATE SCHEMA

• Syntax

  DROP SCHEMA < schema name>

• Usage

  DROP SCHEMA database.sarah

  See Also: 2.3.19 CREATE USER

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

2.3.33 DROP SEQUENCE

• Function Remove an existing sequence from the database.

• Syntax

  DROP SEQUENCE < sequence name>

  RubatoDB will commit the current transaction following the 2.3.16 CREATE SEQUENCE and/or 2.3.33 DROP SEQUENCE statement.

• Usage

  DROP SEQUENCE employee_id_sequence

  See Also: 2.3.16 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.34 DROP TABLE

• Function Remove a user table from the database.

  See Also: 2.3.17 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: 2.3.17 CREATE TABLE.

2.3.35 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: 2.3.18 CREATE TRIGGER. Only the owner of the table (and a system user) may user this command to remove the specified trigger.

2.3.36 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: 2.3.19 CREATE USER

• Syntax

  DROP USER < user name>

• Usage

  DROP USER database.sarah

  See Also: 2.3.19 CREATE USER

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

2.3.37 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: 2.3.20 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.38 DISABLE/ENABLE CONSTRAINT ENFORCEMENT

<ENABLE|DISABLE> CONSTRAINT ENFORCEMENT

2.3.39 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 RubatoDB server in the format of

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

  where the first integer following OUTPUT stands for the number of output parameters, '|' stands for SEPARATOR, and the output value for each out-parameter 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: 2.3.13 CREATE PROCEDURE, 2.3.30 DROP PROCEDURE.

2.3.40 EXECUTE STATEMENT

• Function Execute a prepare statement

• Syntax

  EXECUTE [SELECT] STATEMENT < statement name> (< parameter list>)

• Usage

  SELECT is optional and mainly used to inform various interfaces to receive the result set.

  EXECUTE SELECT STATEMENT find_student(12345);

  See also 2.3.48 PREPARE, 2.3.40 EXECUTE STATEMENT.

2.3.41 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>] | <START> integer <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, RubatoDB returns a specified rows to the client in the same way as for the SELECT statement.
• Usage

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

  FETCH RELATIVE 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.

  FETCH START 100 RELATIVE 10 student_cursor;

  This will fetch 10 rows, starting from the 100th row, that is, from 100th row to 109th row, from the result set.

  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 2.3.13 CREATE PROCEDURE. See the fetch-procedure example.

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

See also 2.3.22 DECLARE CURSOR, 2.3.47 OPEN CURSOR, and 2.3.5 CLOSE CURSOR.

2.3.42 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 2.3.52 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.43 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 login to RubatoDB, 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 2.6 Security Model.

  See Also: 2.3.31 DROP ROLE, 2.3.43 GRANT ROLE, 2.3.53 REVOKE ROLE.

2.3.44 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.

  RubatoDB 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.45 LOCK TABLE

• Function Lock a database table

• Syntax

  LOCK TABLE < table name>

• Usage

  LOCK TABLE student;

  This will lock the entire database table such that no one can access it. One needs a specific privilege to lock any table.

See also 2.3.64 UNLOCK TABLE.

2.3.46 LOAD TABLE

• Function Load data into a database table

• Syntax

  LOAD INTO TABLE < table name> [<AT NODE < integer>] CONTROL < file name> DATA < file name>

  The table name refers to the name of the table to be loaded. The integer following AT NODE refers to the node id at which the control and data files are located, and the default is 0.

  The last two clauses are used to specify the file names for both the control file and the data directory containing the data file.

• Usage

  LOAD INTO TABLE item CONTROL '/home/me/item.ctl' DATA '/home/me/inputs'

  This will load into the database table item with files locaed at NODE 0.

  See 5.3 Loader for details.

2.3.47 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 2.3.22 DECLARE CURSOR, 2.3.5 CLOSE CURSOR, and 2.3.41 FETCH statements.

2.3.48 PREPARE

• Function Prepare (create) a prepared statement

  A prepared statement, similar to a stored procedure, is an executable program stored inside the RubatoDB server that can be used to optimize performance. Unlike a stored procedure which specifies a sequence of SQL-statements and other programming statements, a prepared statement represents a single SQL statement, such as SELECT and UPDATE.

  Prepared statements can take parameters, that is, values that are substituted into the statement when it is executed. The parameters in a prepared statement are represented by position by position using $1, $2, etc. When executing the statement, specify the actual values for these parameters in the EXECUTE statement. Refer to 2.3.40 EXECUTE STATEMENT for more information about that.

  When this prepare statement is executed, the specified statement is parsed, rewritten, and planned. When an EXECUTE command is subsequently issued, the prepared statement need only be executed. Thus, the parsing, rewriting, and planning stages are only performed once, instead of every time the statement is executed.

  A prepared statement can be dropped using the 2.3.21 DEALLOCATE statement.

• Syntax

  PREPARE < statement name> FROM < prepare statement>

  <prepare statement> ::= any SELECT, UPDATE, DELETE, and INSERT statement with parameters represented by $1, $2, ..., etc.

• Usage

  PREPARE find_student FROM SELECT * FROM student WHERE sid = $1; EXECUTE STATEMENT find_student (100); DEALLOCATE STATEMENT find_student;

  See also 2.3.21 DEALLOCATE, 2.3.40 EXECUTE STATEMENT.

2.3.49 RELEASE SAVEPOINT

• Syntax

  RELEASESAVEPOINT < savepoint name>

• Description

  Release an establish a savepoint

• Usage

  RELEASE SAVEPOINT last_mile_stone

  See also 2.3.55 SAVEPOINT and 2.3.54 ROLLBACK.

2.3.50 REMOVE LOB

• Function Remove a BLOB/CLOB with the given lob-locator

• Syntax

  REMOVE LOB WITH < loblocator>

• Usage

  This will statement is used to retrieve a new lob locator for uploading a BLOB or CLOB into the server.

  RubatoDB will automatically generate a unique lob-locator whenever requested.

  The statement will return a lob locator when it succeeds, and raise an error code when it fails.

2.3.51 REQUEST LOBLOCATOR

• Function Request a new lob locator for uploading a BLOB/CLOB

• Syntax

  REQUEST LOBLOCATOR

• Usage

  This will statement is used to retrieve a new lob locator for uploading a BLOB or CLOB into the server.

  RubatoDB will automatically generate a unique lob-locator whenever requested.

  The statement will return a lob locator when it succeeds, and raise an error code when it fails.

2.3.52 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 2.3.42 GRANT PRIVILEGE.

2.3.53 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: 2.3.14 CREATE ROLE, 2.3.31 DROP ROLE, 2.3.43 GRANT ROLE, 2.3.42 GRANT PRIVILEGE.

2.3.54 ROLLBACK

• Syntax

  ROLLBACK

  ROLLBACK TO SAVEPOINT < savepoint name>

• 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 2.3.6 COMMIT to successfully terminate a transaction.

  See also 2.3.61 SET TRANSACTION, 2.3.6 COMMIT and 2.3.55 SAVEPOINT.

2.3.55 SAVEPOINT

• Syntax

  SAVEPOINT < savepoint name>

• Description

  Establish a savepoint for the transaction management.

• Usage

  SAVEPOINT first_mile_stone

  See also 2.3.49 RELEASE SAVEPOINT and 2.3.54 ROLLBACK.

• Example

  The following example demonstrates the effects of the SAVEPOINT and ROLLBACK statements.

  SET AUTO COMMIT OFF; CREATE TABLE student ( sid INT, name VARCHAR(64) NOT NULL, major VARCHAR(10), gpa NUMERIC(5, 2), PRIMARY KEY(sid) ); INSERT INTO student (sid, name, major) VALUES (12001,'Sarah', 'Math'); SAVEPOINT temp; INSERT INTO student (sid, name, major) VALUES (12345,'Peter', null); INSERT INTO student (sid, name, major) VALUES (55003, 'Susan', 'Law'); ROLLBACK TO SAVEPOINT temp; INSERT INTO student (sid, name, major) VALUES (54321,'Bob', 'Business');

  After the insertion of the first row, a SAVEPOINT temp was declared, and therefore, the rollback statement will only rollback all the update statements after the declaration of the savepoint.

  The student table will be populated as following:

  LogicSQL> SELECT * FROM student; sid name major gpa ------------------------------- 12001 Sarah Math 54321 Bob Business

2.3.56 SET LANGUAGE

• Function to set the language code for the current session. Currently, RubatoDB supports the following language code:
  • UTF8
  • GBK
  with the default code is GBK.

• Syntax

  SET LNAGUAGE <UTF8|GBK>>

• Usage
  • SET LANGUAGE UTF8 > will assume all the strings are coded according to the UTF8 standard.

2.3.57 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 2.6.4 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

  2.6 Security Model
2.3.14 CREATE ROLE
2.3.43 GRANT ROLE

2.3.58 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 2.3.20 CREATE VIEW, 2.3.17 CREATE TABLE, and 2.3.44 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. RubatoDB 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 RubatoDB. This clause, however, has no effect on subqueries used in both CREATE VIEW and CREATE TABLE.

  • Hierarchical Queries

    <hierarchical query> ::=

SELECT [< select quantifier>] [<DISTINCT|ALL> ] < * | < derived column| pseudocolumns> [{,< derived column| pseudocolumns> }] >
FROM < table reference> [ , < table reference> ]
[START WITH < search condition>]
CONNECT BY [NOCYCLE] < connect by search condition>
[WHERE < search condition>]
[ORDER SIBLINGS BY] < derived column| pseudocolumns> [{,< derived column| pseudocolumns> }]

    <connect by search condition> ::= PRIOR < value expression < comparison operator> < value expression |
< value expression < comparison operator> PRIOR < value expression>

    <pseudocolumns> ::= LEVEL | CONNECT_BY_ISCYCLE | CONNECT_BY_ISLEAF

  • Examples

    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 RubatoDB 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, RubatoDB 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 RubatoDB 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

    RubatoDB 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. RubatoDB 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.

    Example 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 The query result is shown below: cid name ---------------- BS499 Bob CS291 Bob CS291 Sarah MA101 Sarah

    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 registered students for any courses with no student registered 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 previous query.

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

    SELECT c.cid, count(r.sid) FROM course c left 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

    A subquery in the FROM clause may also be used to expression joined queries, as demonstrated below:

    SELECT c.cid, count(r.sid) FROM ( select * from course) as c left 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 8 The following uses the right join to list the number of all registered students for each course, and the following SQL query.

    SELECT c.cid, count(r.sid) FROM registration r right 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

    Similarly, we have the following query using the subquery in the FROM clause with a right join.

    SELECT c.cid, count(r.sid) FROM (SELECT * FROM registration) r right 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 9 The following demonstrates the use of outer joins in nested queies.

    SELECT * FROM course WHERE cid in ( SELECT c.cid FROM course c left join registration r on r.cid = c.cid ); cid title description --------------------------------------------------------- AR100 Cartoon Drawing A comic 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 with the same name.

    SELECT student.sid, name, cid, grade FROM student NATURAL 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 RubatoDB.

    • 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, RubatoDB 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;

  ---

  [ < ]

  [ > ]

  [ << ]

  [ Up ]

  [ >> ]

  [Top]

  [Contents]

  [Index]

  [ ? ]

  2.3.59 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 2.3.61 SET TRANSACTION statement.

    Note that the default auto_commit state for clients is ON.

  ---

  [ < ]

  [ > ]

  [ << ]

  [ Up ]

  [ >> ]

  [Top]

  [Contents]

  [Index]

  [ ? ]

  2.3.60 SET SESSION USER

  • Syntax SET SESSION USER

  • Description

    Note implemented yet.

  ---

  [ < ]

  [ > ]

  [ << ]

  [ Up ]

  [ >> ]

  [Top]

  [Contents]

  [Index]

  [ ? ]

  2.3.61 SET TRANSACTION

  • Syntax SET TRANSACTION isolation level serializable

  • Usage SET TRANSACTION isolation level serializable

  • Description

    This will start a new transaction.

  • Note

    See also 2.3.54 ROLLBACK and 2.3.6 COMMIT.

  ---

  [ < ]

  [ > ]

  [ << ]

  [ Up ]

  [ >> ]

  [Top]

  [Contents]

  [Index]

  [ ? ]

  2.3.62 SHOW STATEMENT

  RubatoDB provides the following SHOW statements (of the MySQL styple) to access information about databases, tables, columns, or status information about the server.

  • SHOW [FULL] TABLES [<FROM | IN> ] < catalog name> [LIKE 'pattern' | WHERE expr]

  • SHOW [FULL] COLUMNS {FROM|IN} < table name> [{FROM|IN} < catalog name> ] [LIKE 'PATTERN'|WHERE EXPR]

  ---

  [ < ]

  [ > ]

  [ << ]

  [ Up ]

  [ >> ]

  [Top]

  [Contents]

  [Index]

  [ ? ]

  2.3.63 SQL LOAD/DUMP

  • Syntax

    SQL DUMP < table name> INTO <file name> WITH DELIMITER (59,10)

    SQL LOAD <file name> INTO < table name> WITH DELIMITER (59,10)

  • Usage The first one dumps all the data from the given table into the specified local <file name>, with ';' and 'new-line' as delimiters for columns and rows respectively.

    The second statement loads all the data from the specified local <file name> in to the given table. The data in the file must be properly organized with ',' and 'new-line' as delimiters for columns and rows respectively.

    Please note that, as with the SQL standard, all varchar/char strings must be single-quoted.

  • Description

    This is used to load data from the specified data file into a given table and to dump a table into the specified data file.

  • Note These two statements can only be used by the system user because the data file can only be accessed by the database owner.

  ---

  [ < ]

  [ > ]

  [ << ]

  [ Up ]

  [ >> ]

  [Top]

  [Contents]

  [Index]

  [ ? ]

  2.3.64 UNLOCK TABLE

  • Function UNLock a database table

  • Syntax

    UNLOCK TABLE < table name>

  • Usage

    UNLOCK TABLE student;

    This will unlock the given database table to resume normal accesses to it. One needs the lock privilege to unlock any table.

  See also 2.3.45 LOCK TABLE.

  ---

  [ < ]

  [ > ]

  [ << ]

  [ Up ]

  [ >> ]

  [Top]

  [Contents]

  [Index]

  [ ? ]

  2.3.65 USE DATABASE

  • Syntax USE DATABASE < catalog name>

  • Description

    To set the default database to the given catalog name for the current session.

  ---

  [ < ]

  [ > ]

  [ << ]

  [ Up ]

  [ >> ]

  [Top]

  [Contents]

  [Index]

  [ ? ]

  2.3.66 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>]

    UPDATE < table name> aliasA
SET (< column name>, ..., < column name> ) =
( SELECT < column name>, ..., < column name>)
FROM < table name> aliasB
WHERE <aliasA.column_name> = <aliasB.column_name>
)

  • Usage

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

    This will update Peters grade for 391.

  List of extended SQL statements/functions used to implement the RubatoDB's 2.8 extended Unix security model.

  ---

  [ < ]

  [ > ]

  [ << ]

  [ Up ]

  [ >> ]

  [Top]

  [Contents]

  [Index]

  [ ? ]

  2.3.67 ADD INTO GROUP

  • Function To add security certificates and groups to a group

  • Syntax

    ADD INTO GROUP < group name> WITH < security name>[{,< security name> }...] <security name> ::= < certificate name>|< group name>

  • Usage

    Assume that we have four security certificates, peter, sarah, susan, and tom; and that we are going to create three security groups, engineers, developers, and testers such that peter and sarah are engineers, susan and tom are testers, and both developers and testers are contained in engineers. This can be established with the following three statements: ADD INTO GROUP engineers WITH developes, testers; ADD INTO GROUP developes WITH peter, sarah; ADD INTO GROUP testers WITH developers, testers;

  • Prerequisites

    You must have been granted GRANT ANY GROUP system privilege to use this statement. See also 2.6 Security Model.

    See Also: 2.3.70 CREATE GROUP, 2.3.72 DROP GROUP, and 2.3.74 REMOVE FROM GROUP.

  ---

  [ < ]

  [ > ]

  [ << ]

  [ Up ]

  [ >> ]

  [Top]

  [Contents]

  [Index]

  [ ? ]

  2.3.68 ALTER CERTIFICATE

  • Function Modify the security certificate information

  • Syntax

    ALTER CERTIFICATE < certificate name > WITH PASSWORD < password string>

  • Usage

    ALTER CERTIFICATE sarah WITH PASSWORD 'hgd2f23Abd'

    You must have the CREATE ANY CERTIFICATE system privilege as well as have requested the same certificate for the session to use this command.

    See Also: 2.3.69 CREATE CERTIFICATE,2.3.75 REQUEST CERTIFICATE, 2.3.73 RELEASE CERTIFICATE.

  ---

  [ < ]

  [ > ]

  [ << ]

  [ Up ]

  [ >> ]

  [Top]

  [Contents]

  [Index]

  [ ? ]

  2.3.69 CREATE CERTIFICATE

  • Purpose Create a security certificate, simulating the unix user, for the extended Unix security model.

  • Syntax

    CREATE CERTIFICATE < certificate name> [IDENTIFIED BY < password string>]

  • Usage

    CREATE CERTIFICATE sarah

    This will create a security certificate sarah with password sarah.

    CREATE USER peter IDENTIFIED BY 'headf123dfa'

    This will create a security certificate peter with password headf123dfa

    Please change the password immediately using 2.3.68 ALTER CERTIFICATE.

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

    See Also: 2.3.68 ALTER CERTIFICATE, 2.3.75 REQUEST CERTIFICATE, 2.3.73 RELEASE CERTIFICATE.

  ---

  [ < ]

  [ > ]

  [ << ]

  [ Up ]

  [ >> ]

  [Top]

  [Contents]

  [Index]

  [ ? ]

  2.3.70 CREATE GROUP

  • Function To create a security group.

    A (security) group, similar to a group in the Unix security model, is a security label that can be used to specify the record-based access control. See 2.6 Security Model for details.

  • Syntax

    CREATE GROUP < group name>

  • Prerequisites

    You must have CREATE GROUP system privilege to create a group.

  • Usage

    CREATE GROUP developer

    This will create a new group named developer.

  See also 2.3.72 DROP GROUP, 2.3.67 ADD INTO GROUP, 2.3.74 REMOVE FROM GROUP.

  ---

  [ < ]

  [ > ]

  [ << ]

  [ Up ]

  [ >> ]

  [Top]

  [Contents]

  [Index]

  [ ? ]

  2.3.71 DROP CERTIFICATE

  • Function Remove a security certificate.

    When a security certificate is dropped, it will be also be removed from all the security groups.

  • Syntax

    DROP CERTIFICATE < certificate name>

  • Usage

    DROP CERTIFCATE sarah

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

  See Also: <2.3.68 ALTER CERTIFICATE>, <2.3.69 CREATE CERTIFICATE>,<2.3.75 REQUEST CERTIFICATE>, <2.3.73 RELEASE CERTIFICATE>.

  ---

  [ < ]

  [ > ]

  [ << ]

  [ Up ]

  [ >> ]

  [Top]

  [Contents]

  [Index]

  [ ? ]

  2.3.72 DROP GROUP

  • Function Remove an existing security group.

  • Syntax

    DROP GROUP < group name>

  • Usage

    DROP GROUP developer

    See Also: 2.3.70 CREATE GROUP.

  • Prerequisites You must have CREATE GROUP system privilege to use this statement.

  See also 2.3.70 CREATE GROUP, 2.3.67 ADD INTO GROUP, 2.3.74 REMOVE FROM GROUP.

  ---

  [ < ]

  [ > ]

  [ << ]

  [ Up ]

  [ >> ]

  [Top]

  [Contents]

  [Index]

  [ ? ]

  2.3.73 RELEASE CERTIFICATE

  • Function Release a security certificate for the current session

  • Syntax

    RELEASE CERTIFICATE < certificate name>

  • Usage

  • Prerequisites A session cannot release a security certificate that is not currently held.

    See Also: 2.3.69 CREATE CERTIFICATE, 2.3.68 ALTER CERTIFICATE, 2.3.75 REQUEST CERTIFICATE.

  ---

  [ < ]

  [ > ]

  [ << ]

  [ Up ]

  [ >> ]

  [Top]

  [Contents]

  [Index]

  [ ? ]

  2.3.74 REMOVE FROM GROUP

  • Function Remove security certificates and/or groups from a group

  • Syntax

    REMOVE < security name> [{,< security name>}...] FROM GROUP < group name>

  • Usage

    REMOVE sarah FROM GROUP developers

    This will remove the certificate sarah from any groups contained in the group developers, as well as the group developer itself.

  • Prerequisites

    You must have CREATE GROUP system privileges to use this statement.

    See Also: 2.3.70 CREATE GROUP, 2.3.72 DROP GROUP, 2.3.67 ADD INTO GROUP.

  ---

  [ < ]

  [ > ]

  [ << ]

  [ Up ]

  [ >> ]

  [Top]

  [Contents]

  [Index]

  [ ? ]

  2.3.75 REQUEST CERTIFICATE

  • Function Request a security certificate for the current session

  • Syntax

    REQUEST CERTIFICATE < certificate name> WITH PASSWORD < password string>

  • Usage

  • Prerequisites

    See Also: 2.3.69 CREATE CERTIFICATE, 2.3.68 ALTER CERTIFICATE, 2.3.73 RELEASE CERTIFICATE.

  ---

  [ < ]

  [ > ]

  [ << ]

  [ Up ]

  [ >> ]

  [Top]

  [Contents]

  [Index]

  [ ? ]

  2.4 System Utility Commands

  System Utility statements are RubatoDB commands used by a system user to perform system operations, such as RECOVER DATABASE and SHUTDOWN the server. It is also called 2.4 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.7 STATISTICS		Find the statistics of the server
  2.4.6 STORED FACTS		Check if stored facts grow infinitely
  2.3.38 DISABLE/ENABLE CONSTRAINT ENFORCEMENT		Enable/disable constraint enforcements

  ---

  [ < ]

  [ > ]

  [ << ]

  [ Up ]

  [ >> ]

  [Top]

  [Contents]

  [Index]

  [ ? ]

  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.

  ---

  [ < ]

  [ > ]

  [ << ]

  [ Up ]

  [ >> ]

  [Top]

  [Contents]

  [Index]

  [ ? ]

  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.

  ---

  [ < ]

  [ > ]

  [ << ]

  [ Up ]

  [ >> ]

  [Top]

  [Contents]

  [Index]

  [ ? ]

  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.

  ---

  [ < ]

  [ > ]

  [ << ]

  [ Up ]

  [ >> ]

  [Top]

  [Contents]

  [Index]

  [ ? ]

  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 3.7 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.)

  ---

  [ < ]

  [ > ]

  [ << ]

  [ Up ]

  [ >> ]

  [Top]

  [Contents]

  [Index]

  [ ? ]

  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.

  ---

  [ < ]

  [ > ]

  [ << ]

  [ Up ]

  [ >> ]

  [Top]

  [Contents]

  [Index]

  [ ? ]

  2.4.6 STORED FACTS

  • Function Display the number of various facts stored in the internal database. The increasing number implies memory leak.

    This is for the internal use of debug.

  • Syntax

    SELECT ALL FROM stored_prolog_facts

    LogicSQL> select all from stored_prolog_facts; stored_fact arity number ---------------------------------------------- cleared_tid 1 1 tables 9 0 stored_procedures 8 64 table_privileges 7 0 op_record 9 0 select_record 6 0 read_by 7 2 commit_wait_for 2 0 active_transaction 1 19 committed_transaction 1 0 table_removed 4 0 current_roles 2 35 current_securities 3 0 get_key_list 7 181 get_column_list 5 161 get_datatype_list 5 161 get_default_list 5 161 get_is_nullable_list 5 161 get_unique_column_list 5 0 get_real_columns 4 161 get_used_tables 4 2 system_limit 2 28 table_constraints 9 0 crt_oid 1 4 crt_code 1 1 admission 1 2 crt_log_table 1 2 get_trigger 8 1 triggers 9 0 triggers 10 1 is_literal_list 1 1 is_literal_value 1 0 prolog_info 2 1 check_constraint_formula 7 49 get_query_term_for_unique 7 4 fk2reference 11 40 table_select_blocks 9 54 table_constraint_list 4 80 meta_data 2 0 table_created 5 329 referential_constraints 4 14 get_unique_key_list 6 4 table_index_list 4 82 table_inverted_list 4 46 get_view_table_query 6 1 view_tables 4 0 inverted_query_table 5 0 cursor_list 9 0 temp_tables 4 0 cursor_state 3 0 triggers_of_transaction 2 0 crt_sibling 3 0 sequence_orientation 7 0 client_crt_result 3 0 savepoint 3 0 prepared_delete_with_query 3 0

  ---

  [ < ]

  [ > ]

  [ << ]

  [ Up ]

  [ >> ]

  [Top]

  [Contents]

  [Index]

  [ ? ]

  2.4.7 STATISTICS

  • Function Display the statistics of the server
  • Syntax

    SELECT * FROM statistics

    LogicSQL> select * from statistics; parameter size_used free ----------------------------------------------------- memory 7700400 0 program 3016432 3254312 global_stack 827456 443788 local_stack 212 43572 trail 79324 17576 atoms 6875 219672 atom_garbage_collection 158 58854684 choice 152 17498 runtime 3722260 1006960 no_clients 28 Current Tid 86105 No Transactions 18 Current Committed not available Current Active 86085

  ---

  [ < ]

  [ > ]

  [ << ]

  [ Up ]

  [ >> ]

  [Top]

  [Contents]

  [Index]

  [ ? ]

  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

  ---

  [ < ]

  [ > ]

  [ << ]

  [ Up ]

  [ >> ]

  [Top]

  [Contents]

  [Index]

  [ ? ]

  2.5.1 SELECT RETURN

  ---

  [ < ]

  [ > ]

  [ << ]

  [ Up ]

  [ >> ]

  [Top]

  [Contents]

  [Index]

  [ ? ]

  2.5.2 FOR WHERE

  ---

  [ < ]

  [ > ]

  [ << ]

  [ Up ]

  [ >> ]

  [Top]

  [Contents]

  [Index]

  [ ? ]

  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

  ---

  [ < ]

  [ > ]

  [ << ]

  [ Up ]

  [ >> ]

  [Top]

  [Contents]

  [Index]

  [ ? ]

  2.6 Security Model

  RubatoDB implements the following security facilities:
  • the 2.6.1 discretionary security model as outlined in SQL99; and
  • the 2.6.5 extended Unix security model, similar to that of the Unix system, for record-based access control.

  ---

  [ < ]

  [ > ]

  [ << ]

  [ Up ]

  [ >> ]

  [Top]

  [Contents]

  [Index]

  [ ? ]

  2.6.1 discretionary 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.

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

  ---

  [ < ]

  [ > ]

  [ << ]

  [ Up ]

  [ >> ]

  [Top]

  [Contents]

  [Index]

  [ ? ]

  2.6.2 Authorization Identity

  All accesses to RubatoDB 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 RubatoDB interface, such as 5.2 SQL*Face or 4.4.2 sql_connection in the C-Interface, RubatoDB 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 2.3.42 GRANT PRIVILEGE statement. When an SQL-session is initiated, RubatoDB 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 2.3.60 SET SESSION USER statement. The set of current roles of an SQL-session can be changed with 2.3.57 SET ROLE statement.

  ---

  [ < ]

  [ > ]

  [ << ]

  [ Up ]

  [ >> ]

  [Top]

  [Contents]

  [Index]

  [ ? ]

  2.6.3 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 2.3.42 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 RubatoDB 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 RubatoDB server if ones connect privilege has been revoked.

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

  Examples

  When a RubatoDB 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.

  ---

  [ < ]

  [ > ]

  [ << ]

  [ Up ]

  [ >> ]

  [Top]

  [Contents]

  [Index]

  [ ? ]

  2.6.4 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 2.3.57 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 2.3.57 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
  One, however, cannot use the 2.3.57 SET ROLE command to enable roles that have not been granted either directly or 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 2.3.57 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 RubatoDB 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:

  • 2.3.19 CREATE USER
  • 2.3.36 DROP USER
  • 2.3.4 ALTER USER
  • 2.3.14 CREATE ROLE
  • 2.3.31 DROP ROLE
  • 2.3.57 SET ROLE
  • 2.3.60 SET SESSION USER
  • 2.3.42 GRANT PRIVILEGE
  • 2.3.52 REVOKE PRIVILEGE
  • 2.3.53 REVOKE ROLE

  ---

  [ < ]

  [ > ]

  [ << ]

  [ Up ]

  [ >> ]

  [Top]

  [Contents]

  [Index]

  [ ? ]

  2.6.5 extended Unix security model

  The discretionary security model, though the standard security model for commercial database management systems, cannot be used to enforce the record-based access control. RubatoDB, therefore, implements the extended Unix security model, similar to that of the Unix system.

  The basic idea of the extended Unix security model is that each session is associated with one security certificate, simulating the user name of the Unix security model, which will be used to determine whether the session is allowed to access a given record in a table.

  The extended Unix security model maintain a list of security certificates, and a list of security groups, similar to the Unix users and groups. That is, each certificate representing a corresponding user, and each group consists of a list of certificates and groups. Unlike the Unix security model, the extended Unix security model of RubatoDB, however, supports the recursive groups, that is, a security group is specified as a list of security certificates and/or security groups.

  In addition to the database user, each session to a RubatoDB server carries a security certificate which can be terminated or changed with an extended SQL statement. The SQL function CURRENT_CERTIFICATE, which returns the certificate carried in the current session, can then be used to enforce the record-based access control.

  Consider the following scenario: You are going to create a table to store all the files in a Unix system, and a session is allowed to access a file stored in the table as one record if and only if the certificate carried with the session is the owner of the file or is contained in the group allowed to access the file.

  First, you may create a table using the following statement:

  CREATE TABLE unix_file ( file_id INT, file_owner VARCHAR(128), file_group VARCHAR(128), file_name VARCHAR(512), file_content blob, PRIMARY KEY (file_id), CONSTRAINT owner_certificate FOREIGN KEY (security_certificate), CONSTRAINT group_reference FOREIGN KEY (security_group) )

  To enable the record-level access control, you may then create a view using the following:

  CREATE VIEW unix_file_access AS SELECT file_id, file_name, file_content FROM unix_file WHERE file_owner = CURRENT_CERTIFICATE OR file_group IN CURRENT_GROUP;

  Finally, you grant the access to the view to a RubatoDB user developer using

  GRANT SELECT on unix_file_access to developer

  Then developer can read a file only if that file is allowed to read by the certificate of the current session.

  The following extended SQL statements are used to implement the RubatoDB's extended Unix security model.

  SQL statements	Descriptions
  2.3.67 ADD INTO GROUP	Add certificates/groups to a group
  2.3.68 ALTER CERTIFICATE	Alter the password of the certificate
  2.3.69 CREATE CERTIFICATE	Create a new security certificate
  2.3.70 CREATE GROUP	Create a new security group
  2.3.71 DROP CERTIFICATE	REMOVE a security certificate
  2.3.72 DROP GROUP	Drop an existing security group
  2.3.73 RELEASE CERTIFICATE	Release a certificate for the current session
  2.3.74 REMOVE FROM GROUP	Remove certificates/groups from a group
  2.3.75 REQUEST CERTIFICATE	Request a certificate for the current session
  SQL functions	Descriptions
  2.2.8 CURRENT_CERTIFICATE	Return the certificate carried by the current session
  2.2.9 CURRENT_GROUP	Return the list of security groups of the current certificate
  2.2.10 ALL_GROUP	Return the list of all groups contained in the given group

  We conclude this section with the following conprehensive example using the extended security model.

  The following script file first creates a table with two "security columns", that is, owner indicating who is allowed to read the row, and groups indicating which group is allowed to read the row. It then creates two security certificates and three security groups, and assigns the groups.

  DROP CERTIFICATE sarah; DROP CERTIFICATE peter; DROP GROUP market; DROP GROUP develop; DROP TABLE files; /* * Create and populate a table with three rows */ create table files ( file_id int, file_name varchar(100), owner varchar(100), groups varchar(100), primary key (file_id) ); insert into files values(100, 'just_test', 'sarah', 'market'); insert into files values(200, 'no_way', 'peter', 'develop'); insert into files values(300, 'white paper', 'sarah', 'employee'); /* * Create and assign two certificates and three groups as follows: * employee * / \ * market develop * | | * sarah peter */ CREATE CERTIFICATE sarah; CREATE CERTIFICATE peter; CREATE GROUP market; CREATE GROUP develop; CREATE GROUP employee; add into group employee with market, develop; add into group develop with peter; add into group market with sarah;

  The result sets to the same queries will then depend on the requested certificates, as shown below:

  LogicSQL> request certificate sarah with password 'sarah'; 0000008000 certificate requested LogicSQL> SELECT * FROM files where owner = current_certificate; file_id file_name owner groups ------------------------------------------- 100 just_test sarah market 300 white paper sarah employee LogicSQL> SELECT * FROM files where groups in current_group; file_id file_name owner groups ------------------------------------------- 100 just_test sarah market 300 white paper sarah employee LogicSQL> request certificate peter with password 'peter'; 0000008000 certificate requested LogicSQL> SELECT * FROM files where owner = current_certificate; file_id file_name owner groups ---------------------------------------- 200 no_way peter develop LogicSQL> SELECT * FROM files where groups in current_group; file_id file_name owner groups ------------------------------------------- 200 no_way peter develop 300 white paper sarah employee

  A view, named INFORMATION_SCHEMA.CURRENT_GROUPS, has been specified for any user to find the list of the current groups. For example, the results to the following queries demonstrate that the view contains exactly the list of all groups the current certificate belongs to.

  LogicSQL> request certificate sarah with password 'sarah'; 0000000000 certificate granted LogicSQL> SELECT * FROM INFORMATION_SCHEMA.CURRENT_GROUPS; current_certificate group_name ----------------------------------- sarah employee sarah market LogicSQL> request certificate peter with password 'peter'; 0000000000 certificate granted LogicSQL> SELECT * FROM INFORMATION_SCHEMA.CURRENT_GROUPS; current_certificate group_name ----------------------------------- peter develop peter employee LogicSQL>

  ---

  [ < ]

  [ > ]

  [ << ]

  [ Up ]

  [ >> ]

  [Top]

  [Contents]

  [Index]

  [ ? ]

  2.7 System Information and Information_schema

  The Information Schema, named INFORMATION_SCHEMA, is specified by RubatoDB (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 current_groups view 1 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 6 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 7 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.

  RubatoDB provides the following SHOW statements (of the MySQL styple) to access information about databases, tables, columns, or status information about the server. See 2.3.62 SHOW STATEMENT statements for details.

  ---

  [ < ]

  [ > ]

  [ << ]

  [ Up ]

  [ >> ]

  [Top]

  [Contents]

  [Index]

  [ ? ]

  2.8 RubatoDB Text and Search

  This chapter discusses RubatoDB Text technology and describes how to use RubatoDB Text to develop search applications (search engine) for Internet, intra net and enterprise searches.

  • 2.8.1 What is RubatoDB Text and Search?
  • 2.8.2 Inverted Index for Text Queries
  • 2.8.3 Text Queries on Document Collections
  • 2.8.6 Aggregate Functions and Top K Queries
  • 2.8.7 How to Specify Text Queries
  • 2.8.8 Search with Constrains
  • 2.8.9 List of RubatoDB Text and Search Facilities
  • 2.8.10 Query Efficiency and Partition of Inverted Indexes

  Further, the extended security model of RubatoDB enables users to implements row-level access controls, which is critical for enterprise search.

  ---

  [ < ]

  [ > ]

  [ << ]

  [ Up ]

  [ >> ]

  [Top]

  [Contents]

  [Index]

  [ ? ]

  2.8.1 What is RubatoDB Text and Search?

  RubatoDB Text and Search is a technology that enables users to build text query and various search applications, including Internet, intra-net, and enterprise searches. Logicsql Text provides inverted indexing, text querying, ranking the query results based on the top-k theory, and viewing capabilities for text search.

  More specifically, RubatoDB Text and Search provides the following classes of facilities:

  • Inverted Index: With RubatoDB Text and Search, you can create any number of inverted indexes for efficient text queries.

  • Text Query: Several text query functions are provides to formulate various text queries, including search with key word, phrase, and logical operators AND and OR.
  • Federated Search: This is used to rank returned documents from different inverted indexes and/or ranking methods, based on the top-k query algorithms.
  • Document Crawlers: RubatoDB Text and Search provides search crawlers to archive and index various documents and files to be searched, including web pages, files of all formats, emails, and structured documents stored in databases.

  ---

  [ < ]

  [ > ]

  [ << ]

  [ Up ]

  [ >> ]

  [Top]

  [Contents]

  [Index]

  [ ? ]

  2.8.2 Inverted Index for Text Queries

  An inverted index is an index structure storing a mapping from key words to documents or segments of text paragraphs that contain the words. That is, an inverted index is a sequence of ({key_word}, pointer) pairs where each pointer points to a document in a database which contains the key_word in some particular field. The index is "inverted" in the sense that the key word is used to find the document rather than the other way round.

  An inverted index can be used to efficiently identify all the documents stored in a database that contain a specified word and/or a sequence of words.

  To create an inverted index, your documents must be stored in a column of VARCHAR or CLOB type. You can then use 2.3.12 CREATE INVERTED INDEX SQL statement to create an inverted index with the specified dictionary and the list of stop words. (For now, only the default dictinary and list of stop words are supported.)

  RubatoDB will automatically detects the format and language of the text, and sets indexing accordingly.

  Supported Column Type An inverted index can only be created on the columns with the following data types:

  • VARCHAR
  • CHARACTER_DATA
  • BLOB
  • CLOB

  Supported Document Formats

  The column on which an inverted index is created shall be the text format only. It is the developer's responsibility to extract the full text from documents of various formats, such as PDF, MS WORD, MS PowerPoint, etc.

  Supported Document Languages RubatoDB Text and Search supports, by default, English and Chinese in GBK, though, by 2.3.56 SET LANGUAGE, one may develop applications for any languages.

  ---

  [ < ]

  [ > ]

  [ << ]

  [ Up ]

  [ >> ]

  [Top]

  [Contents]

  [Index]

  [ ? ]

  2.8.3 Text Queries on Document Collections

  The basic RubatoDB Text and Search query takes a query expression, usually a word or a list of words, with or without operators, as input, and returns the pointers to all documents (previously indexed) that satisfy the expression along with a relevance score for each document. Scores can be used to order (rank) the documents in the result set.

  To issue a RubatoDB Text and Search query, use the SQL SELECT statement, extended with the CONTAINS function.

  First, a simple example below is used to demonstrate the text querying facility.

  Assume the following table is used to store the documents, together with the owner and title.

  CREATE TABLE documents ( d_id INT, title VARCHAR(100), owner VARCHAR(128), create_date date, content CLOB, PRIMARY KEY (d_id) )

  Further, an inverted index is created on the content of documents, as follows:

  CREATE INVERTED INDEX docs_index ON documents(content);

  Then the following SELECT statement with the CONTAINS function

  SELECT d_id, title FROM document WHERE CONTAINS(content, 'database', 1) > 0 ORDER BY score(1) DESC;

  will return the list of d_id and title of all documents that contain 'database', sorted by the number of occurrences of 'database' in the document.

  Note that the third parameter of CONTAINS specifies the label of the function, and the score function score(1) with the corresponding label returns the number of occurrences of CONTAINS with the same label.

  ---

  [ < ]

  [ > ]

  [ << ]

  [ Up ]

  [ >> ]

  [Top]

  [Contents]

  [Index]

  [ ? ]

  2.8.4 CURSOR_TABLE

  The cursor_table is a function that maps a given inverted cursor into a temporary table.

  An inverted cursor is used to specify a temporary table for storing the query result from a federated search of inverted indexes based on the top k algorithm. Because of implementation consideration, an inverted cursor is neither a temporary table nor a cursor table. To facilitate the use of inverted cursors, the cursor_table is then introduced to transform a given inverted cursor into a (temporary) table that can be used to join other tables or specify an SQL cursor, just like any other tables.

  More specifically, given the name of an inverted cursor, with optional fetch orientation, the following function

  cursor_table(< cursor name> [,< fetch orientation>] )

  specifies a temporary table that can be used in any SQL statement.

  The table specified by cursor_table has two columns, that is, document_id and score, both are of INTEGER type. The first one specifies the document id and the second one the number of occurrences of the searched words in the respective document.

  The default fetch orientation of the cursor_table is

  START 1 RELATIVE 10000,

  that is, the cursor_table will contain the first 10,000 rows. To include all available rows in the cursor_table, the following is sufficient:

  CUURSOR_TABLE( cursor_name, START 1 RELATIVE 0)

  Note that since an inverted cursor is not a real cursor, the optional fetch orientation in the cursor_table can be used to specify the subset of the table, like in any standard 2.3.41 FETCH statement, but repeatly calling the cursor_table with the fetch orientation will generate the same table, unlike in any standard 2.3.41 FETCH statement.

  ---

  [ < ]

  [ > ]

  [ << ]

  [ Up ]

  [ >> ]

  [Top]

  [Contents]

  [Index]

  [ ? ]

  2.8.5 CONTAINS

  The key facility of RubatoDB Text and Search is the extended SQL function CONTAINS which is defined with respect the involving inverted index over the specified column. More specifically,

  CONTAINS(< column name>,< text expression>, < integer> [< value expression] ) is a function with three arguments and one optional argument (total 4), and returns the number of occurrences of the text expression for every row (document) selected. in the givne column_name. The three parameters are

  • column name: the name of the column of text documents, on which a unique inverted index has been created;
  • text expression: the text expression specifying the query input string;
  • label: an integer used to denote the current CONTAINS function.
  • value expression: the value of the group by column, on which the unique inverted index row has been created

  A text expression is an expression that specifies the query input for 2.8.5 CONTAINS function with the following syntax:

  Operation	Syntax	Description of Operation
  ' '	'abc def'	Returns rows that contain the word (phrase) 'abc def'
  Logic AND	a & b & c	Returns rows that contains a, b, and c
  Logic OR	a | b | c	Returns rows that contains a, b, or c

  For example, CONTAINS(document.abstract, 'database' & 'file system', 2) returns, for each row selected, the occurrences of the document, stored in the column document.abstract of the current row, that contain both 'database' and 'file system'. That is,

  SCORE(label) is a function returns the same value as CONTAINS(column, text_expression, label).

  The third parameter provides a unique label for the function that will be used in the function score(label).

  Therefore, it is easy to understand that the label in any CONTAINS function must be unique, and label in any score(label) must be specified in a CONTAINS function.

  To understand the group by column, consider the subject_index in the CREATE INDEX example.

  The following query lists all the document_id whose subject is english, and whose abstract contains at least one word math, ordered by their frequences of the word.

  SELECT document_id FROM book WHERE CONTAINS(abstract, 'math', 1, 'english') ORDER BY score(1)

  RubatoDB will return an error message if the CONTAINS clause specifies the fourth argument but the corresponding index is created without the group by option. On the other hand, the query evaluation will be relatively inefficient if the inverted index is created with the group by clause but the CONTAINS clause is specified without the fourth argument for the query is evaluated by taking the union of all the result sets of each and every partitioned group.

  ---

  [ < ]

  [ > ]

  [ << ]

  [ Up ]

  [ >> ]

  [Top]

  [Contents]

  [Index]

  [ ? ]

  2.8.6 Aggregate Functions and Top K Queries

  In order to properly order the federated search based on different contains formulas, RubatoDB Text and Search implements the top k query algorithms such that the use is able to specify a specific ranking method for his/her applications.

  An aggregate function is any monotonic function defined over a list of non-negative ranking functions. More specifically, an aggregate function is any monotonic function

  whose value is specified by a numeric value expression with all variables are of the form

  score(1), score(2), ..., score(N)

  such that

  • all score(Label) functions are refereed to an inverted index,
  • the function must be monotonic.

  The TOP K query facility of RubatoDB Text and Search is then implemented in the 2.3.25 CREATE INVERTED CURSOR facility.

  The TOP K query returns the first K rows with the specified columns from the union of all contains formulas specified as the arguments of the aggregate function, and then the next K rows, ..., and so on.

  An aggregate function is usually used in the ORDER-BY clause of an inverted cursor that is used to rank results from the federated search of a list of inverted indexes.

  ---

  [ < ]

  [ > ]

  [ << ]

  [ Up ]

  [ >> ]

  [Top]

  [Contents]

  [Index]

  [ ? ]

  2.8.7 How to Specify Text Queries

  There are different approaches to specify text search on a column of a table.

  Consider a document table created by the following SQL statement.

  CREATE TABLE document ( d_id int, publisher varchar(128), owner varchar(128), title varchar(256), anchor_text clob, content clob, primary key(d_id) );

  We assume that three inverted indexes are created using the following

  CREATE INVERTED INDEX title_index on document(title); CREATE INVERTED INDEX anchor_index on document(anchor_text); CREATE INVERTED INDEX content_index on document(content);

  The following examples demonstrate different ways to specify text search on this table

  • A simple SELECT statement

    The following SQL statement can be used to find the list of all docuemnt id whose content contains world cup, and order the result according to the number of occurrences of world cup in respective documents.

    SELECT d_id FROM document WHERE CONTAINS(content,'word cup',1)>1 ORDER BY score(1) DESC;

  • Using inverted cursor for the federated search

    The above statement rank the result documents according to their frequences of the key word in the full text. If one wishes to rank the results according to the weighted frequences of the key word, then the inverted cursor is a handy tool, as demonstrated by the following example.

    CREATE INVERTED CURSOR temp_search FOR SELECT d_id FROM document WHERE CONTAINS(title, 'world cup',1) > 0 OR CONTAINS(anchor_text,'world cup',2) > 0 OR CONTAINS(content, 'world cup',3) > 0 ORDER BY 100 * score(1) + 10 * score(2) + 10 * score(3) desc; SELECT d.d_id, d.title, s.score FROM cursor_table(temp_search) s, document d WHERE s.document_id = d.d_id ORDER BY s.score DESC;

    The first statement creates an inverted cursor such that the value of the score in the corrensponding cursor_table is specified by the value expression (aggregate function) of the ORDER BY clause.

    The second statement then returns the list of d_id, title, and score of all documents that contains "world cup" in either title, anchor_text, or the full text.

    Obviously, the result list is ranked by the given aggregate function in the definition of the inverted cursor.

  • Fetch specified number of results at a time

    In many applications, a search engine needs not return all searched results to its users. Rather, one may need only the top, say, 20 results, and the number of all results for the query in the first step.

    The inverted cursor and the standard SQL cursor statement can be used to develop such sophiscated applciations, as demonstrated below.

    CREATE INVERTED CURSOR temp_search FOR SELECT d_id FROM document WHERE CONTAINS(title, 'world cup',1) > 0 OR CONTAINS(anchor_text,'world cup',2) > 0 OR CONTAINS(content, 'world cup',3) > 0 ORDER BY 100 * score(1) + 10 * score(2) + 10 * score(3) desc; DECLARE search CURSOR FOR SELECT d.d_id, d.title, s.score FROM cursor_table(temp_search) s, document d WHERE s.document_id = d.d_id ORDER BY s.score DESC; OPEN search; FETCH RELATIVE 20 search; FETCH START 100 RELATIVE 20 SEARCH;

    The only difference between this one and the previous one is that the second statement in this example creates a CURSOR table for the session, and then send the top 20 results, in the fourth statement to the client. Note that the OPEN cursor statement not just open the cursor, but also returns, to the client, the number of rows in the cursor table.

    The last statement in this example, sends the 20 results, starting in the 100th to the client.

  • Partitioned search

    Consider the following scenario: the document table contains 100 million documents, with one million owners, with average 100 document per owner. Further, assume that search on a popular key word search, say iPod, returns 2 million results.

    What if one needs to search all documents ownned by a particular client Sarah that contain iPod ?

    The join of 2 million resuts with all documents ownned by Sarah for sure is not a very efficient one.

    The group-by clause of the inverted index can then be used to significaly improve efficiency of such queries, as shown below.

    We first assume that the document table is exteneded with the following inverted index:

    CREATE INVERTED INDEX content_index ON document(content) GROUP BY owner;

    Then the following SQL statement will return the desired result.

    SELECT d_id, title, score(1) FROM document WHERE CONTAINS(content,'iPod',1,'Sarah')>0 ORDER BY score(1);

    ---

    [ < ]

    [ > ]

    [ << ]

    [ Up ]

    [ >> ]

    [Top]

    [Contents]

    [Index]

    [ ? ]

    2.8.8 Search with Constrains

    There are many applications in which the size of an inverted index is huge but a search may focus on a small range of the index table. Consider, for example, an on-line email server system with 10 million users. The inverted index table created for all the emails, in and/or out of each mail box, is, of course, very huge, but the search for the given key word usually involves only the list of emails received or sent by a given mail box which is very small.

    For such applications, it is important to reduce the size of the inverted index table before other evaluations. The constrained search describe below is introduced for such purpose.

    Below is an example.

    SELECT d_id, title, score(1) FROM document WHERE CONTAINS(content,'iPod',1)>0 ORDER BY score(1) CONSTRAINED BY SELECT d_id FROM document WHERE owner = 'Sarah';

    The difference between the constrained search and the group value are obvious: The former is much more flexible while the later is more efficient. They fit different applications.

    The restrictions of the constrained search:

    • The subquery of the constrained search (constrained subquery) is independent of all other subclauses of the statement
    • The SELECT list of the constrained subquery consists of one column of the integer type.
    • The semantics of the constrained search is that the only rows in the corresponding inverted index tables are considered in the query are those identical to one of the rows in the constrained table.

    More examples are given below.

    create inverted cursor temp_search for select file_id from archive_file where contains(full_text,'ipot|MacBook',2)>0 order by score(2) desc constrained by select file_id from archive_file where file_owner = 'kim'; DECLARE search cursor FOR SELECT f.file_id, f.file_owner, f.file_name, s.score FROM cursor_table(temp_search,start 1 relative 0) s, archive_file f WHERE s.document_id = f.file_id ORDER BY s.score desc; open search; fetch relative 10 search; select count(*) from archive_file where contains(full_text,'ipot',2)>0 constrained by select file_id from archive_file where file_owner='sarah'; select * from archive_file where contains(full_text,'flood',2)>0 constrained by select file_id from archive_file;

  ---

  [ < ]

  [ > ]

  [ << ]

  [ Up ]

  [ >> ]

  [Top]

  [Contents]

  [Index]

  [ ? ]

  2.8.9 List of RubatoDB Text and Search Facilities

  SQL statements	Descriptions
  2.3.12 CREATE INVERTED INDEX	Create an inverted index for text queries
  2.3.25 CREATE INVERTED CURSOR	Create a temporary table for text queries using the top k algorithm
  2.3.24 DROP INVERTED CURSOR	Close an inverted cursor
  SQL functions	Descriptions
  2.8.5 CONTAINS	Return ranking factor for text queries
  2.8.4 CURSOR_TABLE	a function mapping an inverted cursor to a temporary table
  AGGREGATE FUNCTION	Return a temp table for TOP K queries

  ---

  [ < ]

  [ > ]

  [ << ]

  [ Up ]

  [ >> ]

  [Top]

  [Contents]

  [Index]

  [ ? ]

  2.8.10 Query Efficiency and Partition of Inverted Indexes

  For efficiency, RubatoDB Text and Search provides two important options for you to partition inverted indexes, that is,

  • GROUP BY As demonstrated below, one may create an inverted index using GROUP BY clause such that the query can be efficient evaluated with the specified GROUP BY value.

    CREATE INVERTED INDEX email_index ON emails (content) GROUP BY sender;

    Then the search all emails containing a key word, for example, ipod, by the sender named Sarah can be specified by

    SELECT email_id FROM emails WHERE contains(content,'ipod', 1, 'Sarah')>0 ORDER BY score(1);

    For simplicity, a composite partitining follows the specified order, that is, the grid partitioning, if any, must be the first one while, the column partitioning, if any, must be the last one.

  • Partition By Time RubatoDB Text and Search stores all the inverted indexes in the disk according to the timing scheme. That is, for a specified time period, each inverted index is stored as one B-tree in disk, and therefore, a search (query) can be evaluated with respect to any number of B-trees.

    One needs only to specify two configuration variables, one is for the timing schema, and the other the number of inverted B-trees when the initial database was created. The rest will be taken cared by RubatoDB Text and Search.

    Currently, the following timing schemas are provided:

    Timing Schema	Description
    DAY	One B-tree table for each day
    TENDAYS	One B-tree table for every ten days
    MONTH	One B-tree table for every month
    YEAR	One B-tree table for every year

    The other variable is NoInverted, which indicates how many inverted tables a query shall be evaluated.

    Both configuration variables, i.e., Timing and NoInverted, are specified in the configuration file.

  ---

  [ < ]

  [ > ]

  [ << ]

  [ Up ]

  [ >> ]

  [Top]

  [Contents]

  [Index]

  [ ? ]

  2.9 Grid Management and Table Partition

  ---

  [ < ]

  [ > ]

  [ << ]

  [ Up ]

  [ >> ]

  [Top]

  [Contents]

  [Index]

  [ ? ]

  2.9.1 Grid Management

  RubatoDB, as a grid database management system, is designed to run on a set of servers connected by TPC/IP. This subsection describes how to install, configure, and set up a RubatoDB running on any number of servers.

  ---

  [ < ]

  [ > ]

  [ << ]

  [ Up ]

  [ >> ]

  [Top]

  [Contents]

  [Index]

  [ ? ]

  2.9.2 Table Partition

  Table partition provides facilities to store and query vary large tables by decomposing them into smaller and more manageable pieces called partitions. SQL queries and DML statements do not need to be modified in order to access partitioned tables.

  However, after partitions are defined, DDL statements can access and manipulate individuals partitions rather than entire tables or indexes. This is how partitioning can simplify the manageability of large database objects. Also, partitioning is entirely transparent to applications.

  RubatoDB provides the following partitioning methods:

  • Grid Partitioning
  • Column Partitioning
  • Composite Partitioning
  The first two partitioning are also called horizontal partition while the last one vertical partition.

  Grid Partitioning

  Grid Partitioning distributes data into a set of grid nodes to increase the performance as well as scalability. Grid Partitioning maps different rows in to different grid nodes by the specified values of a set of columns, usually the key column(s).

  A typical example of grid partitioning, based on the key value, is given below.

  Grid Partitioning Example CREATE TABLE calling_detail_record ( SEQUENCE_NUMBER bigint primary key, CITY_ID int, MSC_CODE int, START_TIME date, END_TIME date, PERIOD int, LAST_BTS_USER_LABEL varchar(20), LAST_CELL_USER_LABEL varchar(20), SUBSCRIBER_NUMBER varchar(20), MIN0 varchar(20), IMSI varchar(20), SYSTEM_ID int ) PARTITION BY GRID (sequence_number) ( PARTITION calling_detail_record_1 IN grid(0) IF MOD(sequence_number,4)=0, PARTITION calling_detail_record_2 IN grid(1) IF MOD(sequence_number,4)=1, PARTITION calling_detail_record_3 IN grid(2) IF MOD(sequence_number,4)=2, PARTITION calling_detail_record_4 IN grid(3) IF MOD(sequence_number,4)=3 );

  WARNING: For now, the only partitioning by grid rules are MOD operation, that is, rows are distributed in to Node $i$ if and only if the MOD(column, N)==i, where N is the number of grid nodes considered.

  Column Partitioning Column Partitioning allows users to vertically partition the table into any number of tables such that (1) each partitioned table contains the key of the table, and (2) all the non-key columns of the partitioned tables form a partition of the non-key columns of the given table.

  Column Partitioning is a novel implementation of the so called hybrid storage schema for both row-stored and column-stored databases.

  A typical example is given below. The statement creates a table that is column partitioned into three tables.

  Column Partitioning Example CREATE TABLE stock ( s_w_id integer, s_i_id integer, s_dist_01 varchar(24), s_dist_02 varchar(24), s_dist_03 varchar(24), s_dist_04 varchar(24), s_dist_05 varchar(24), s_ytd integer, s_data varchar(500), s_quantity integer, constraint stock_primary_key primary key (s_w_id,s_i_id) ) PARTITION BY COLUMN ( PARTITION column_1 (s_w_id,s_i_id,s_dist_01,s_dist_02,s_dist_03,s_dist_04,s_dist_05,s_data), PARTITION column_2 (s_w_id,s_i_id,s_ytd), PARTITION column_3 (s_w_id,s_i_id,s_quantity) );

  The above statement partitions the warehouse table into three tables that share the same primary key which is the primary key of the warehouse table. RubatoDB will store the three partitioned tables on disk, and all the queries and DML requests will be evaluated against the partitioned table, transparent to the users.

  Note that the column partitioned tables are actual tables stored on disk, and therefore, these tables can also be accessed just like any other tables using SQL statements. As a matter of fact, the following query will return all the tables including the partitioned tables.

  SELECT * FROM INFORMATION_SCHEMA.tables

  Composite Partitioning Composite partitioning partitions data using two or more levels of the partitioning methods.

  For example, one may first partition data using the grid method, and within each partition, subpartitions it using the column partitioning.

  It is the Composite partitioning that provides the scalability to RubatoDB database management system. It can be used to distribute data into a set of grid nodes and thus not just to increase the data capacity but also improve the performance by parallelism.

  Composite partitioning supports historical operations, such as adding new grid partitions, but also provides higher degrees of parallelism for DML operations and finer granularity of data placement through subpartitioning.

  The following CREATE TABLE statement first partitions the table and then distributes the partitions in to four grid nodes, and then column-partitions each partition into two sub-tables.

  Column Partitioning Example CREATE TABLE stock ( s_w_id integer, s_i_id integer, s_dist_01 varchar(24), s_dist_02 varchar(24), s_dist_03 varchar(24), s_dist_04 varchar(24), s_dist_05 varchar(24), s_ytd integer, s_data varchar(500), s_quantity integer, constraint stock_primary_key primary key (s_w_id,s_i_id) ) PARTITION BY GRID (s_w_id) ( PARTITION grid_1 IN grid(0) IF MOD(s_w_id,4)=0, PARTITION grid_2 IN grid(1) IF MOD(s_w_id,4)=1, PARTITION grid_3 IN grid(2) IF MOD(s_w_id,4)=2, PARTITION grid_4 IN grid(3) IF MOD(s_w_id,4)=3 ) PARTITION BY COLUMN ( PARTITION column_1 (s_w_id,s_i_id,s_dist_01,s_dist_02,s_dist_03,s_dist_04,s_dist_05,s_data), PARTITION column_2 (s_w_id,s_i_id,s_ytd), PARTITION column_3 (s_w_id,s_i_id,s_quantity) );

  In the above statement, The partition by column will be applied to all four partitions of the first level, and therefore, the created table has twelve (12) partitions. The names of the 12 partitions are constructed by concatenating one of the first partition name and one of the second partition name. That is,

  stock_column_1_grid_1, stock_column_2_grid_1, stock_column_3_grid_1 stock_column_1_grid_2, stock_column_2_grid_2, stock_column_3_grid_2 stock_column_1_grid_3, stock_column_2_grid_3, stock_column_3_grid_3 stock_column_1_grid_4, stock_column_2_grid_4, stock_column_3_grid_4

  ---

  [ < ]

  [ > ]

  [ << ]

  [ Up ]

  [ >> ]

  [Top]

  [Contents]

  [Index]

  [ ? ]

  2.9.3 Partition Index

  ---

  [ < ]

  [ > ]

  [ << ]

  [ Up ]

  [ >> ]

  [Top]

  [Contents]

  [Index]

  [ ? ]

  2.10 SQL Syntax

  Follow the standard SQL99 syntax.

  <identifier> ::= < simple Latin letter> [{ < digit> | < simple Latin letter> | _ }... ] | '"' < nondouble quoted char> [{ < nondouble quoted char>}] '"'

  <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| subquery>

  <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>

  <certificate name> ::= < identifier>

  <group name> ::= < identifier>

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

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

  <statement name> ::= < identifier>

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

  <file name> ::= An absolute file name

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

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

  <argument name> ::= < identifier>

  <saveponnt name> ::= < identifier>

  <group 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> ::= <schema qualified name>

  <schema qualified name> ::= [ < schema 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

  <firing order> ::= ORDER < integer>

  <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

  <loblocator> :: A string generated by RubatoDB using 2.3.51 REQUEST LOBLOCATOR

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

  <nondouble quoted char> ::= any char other than '"'

  <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.

  ---

  [ < ]

  [ > ]

  [ << ]

  [ Up ]

  [ >> ]

  [Top]

  [Contents]

  [Index]

  [ ? ]

  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

  ---

  [ < ]

  [ > ]

  [ << ]

  [ Up ]

  [ >> ]

  [Top]

  [Contents]

  [Index]

  [ ? ]

  3.1 Installation

  The proper installation of RubatoDB (LogicSQL) takes the following steps:

  • download the distribution package if needed:

    http://webdocs.cs.ualberta.ca/%7Eyuan/databases/logicsql/rubatodb_dist.tgz

    Note that the package includes the server (for Glibc 2.11), SQLFace, and SQL loader.

  • 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.

  Two installation examples present detail steps to install and set up RubatoDB.

  ---

  [ < ]

  [ > ]

  [ << ]

  [ Up ]

  [ >> ]

  [Top]

  [Contents]

  [Index]

  [ ? ]

  3.1.1 Linux Installation

  Please make sure that the Linux distribution contains the following files and directories:

  • bin
  • config
  • docs
  • include
  • lib
  • README

  Pre-installation

  RubatoDB is designed to run on a set of commodity machines as one DBMS server, and the pre-installation outlined below must be done on each and every one of the machines.

  • Resources Requirements

    Make sure you have sufficient disk space. You will need 100 MB for the installation directory, and at least 1GB for an empty database. It takes about two to three 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 3.7 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 server can be installed under any directory, usually $HOME/logicsql. 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 bash or tcsh as follows:

      bash:> export LOGICSQL=$HOME/logicsql

      tcsh:> setenv LOGICSQL $HOME/logicsql

      One may replace the directory name $HOME/logicsql with any directory as long as one has accesses to it.

    • Search Paths: Since the binary code of RubatoDB is stored in $LOGICSQL/bin(1), it is better to include this in your search paths. Again, this can be done as follows, depending on bcsh or tcsh,

      bcsh:> export PATH=$LOGICSQL/bin:$PATH

      tcsh:> setenv PATH $LOGICSQL/bin:$PATH

    • 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.

      Linux:> limit cputime unlimited filesize unlimited datasize unlimited stacksize 8192 kbytes coredumpsize 0 kbytes memoryuse unlimited vmemoryuse unlimited descriptors 1024 memorylocked 64 kbytes maxproc 14852 maxlocks unlimited maxsignal 14852 maxmessage 819200

      This shows that the limit on the number of descriptors/open files 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 unlimited to save future trouble. Consult one's system administrator to find out how to increase the limits on both file descriptors and file sizes.

      For example, to set a proper limit on descriptors on a Linux system, one shall first edit the system file
      

      /etc/security/limits.conf
      

      with one line
      

      username hard nofile 32768,
      

      and then use
      

      Linux:> limit descriptors 8192
      

      to set the limit on the number of open files to be 8192. or
      

      Linux:> unlimit descriptors
      

      to set the file descriptors to the limit specified by in the file named /etc/security/limits.conf .

      Note that the default is usually set to 1024, and thus editing the file is necessary. If you have any difficulty to set up proper environment variables and/or limit variables consult your local system administrators.

  Installation of the System Code

  All documents and programs are contained in the directory rubatodb_dist in the distribution package. The RubatoDB can be easily installed from the distribution package in the following simple approach.

  1. Manual Installation

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

  The installation of the system code must be done in each and every server node.

  Installation of Application Tools

  All development tools and application interfaces can be installed from rubatodb_dist in the distribution package, with or without installation of a RubatoDB server. In the current distribution, three development tools are provided, that is, the 4.1 JDBC driver, 4.4 The C Language Interface, and 5.2 SQL*Face.

  • JDBC

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

  • C Interface

    The C Interface included in the distribution package consists of two files, i.e., rubatodb_dist/include/lsql_c.h and rubatodb_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.

  • SQL*Face

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

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

  ---

  [ < ]

  [ > ]

  [ << ]

  [ Up ]

  [ >> ]

  [Top]

  [Contents]

  [Index]

  [ ? ]

  3.2 Server Runtime Environment

  RubatoDB user account As with any other server daemon that is connected to the world at large, it is advisable, though not necessary, to run RubatoDB 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.

  ---

  [ < ]

  [ > ]

  [ << ]

  [ Up ]

  [ >> ]

  [Top]

  [Contents]

  [Index]

  [ ? ]

  3.3 System Configuration

  Before creating the initial database and/or starting the RubatoDB server, you must configure the system according to your needs by editing the following two files:
  • $LOGICSQL/bin/grid_initial.sql: the grid initialization file used to specify the number of grid nodes, the IP addresses and connection sockets of all grid nodes; and
  • $LOGICSQL/config/logicsql.conf: the system configuration file used to set up desired values for the configuration variables.

  .

  ---

  [ < ]

  [ > ]

  [ << ]

  [ Up ]

  [ >> ]

  [Top]

  [Contents]

  [Index]

  [ ? ]

  3.3.1 Grid Initialization

  RubatoDB is designed to run on a collection of commodity servers, called grid nodes, connected using the TPC/IP protocol. Therefore, one has to specify the number of grid nodes, the IP address of all the grid nodes, and the sockets used to connect different grid nodes.

  RubatoDB uses two system tables, system.definition_schema.grid_nodes and system.definition_schema.grid_sockets, to store such information.

  The following demonstrates the actual rows of these two tables for a RubatoDB server runs on one machine with two grid nodes. (It is possible to run RubatoDB on one machine with two or more grid nodes.)

  LogicSQL> select * from system.definition_schema.grid_nodes; grid_id ip_address grid_name is_main_grid -------------------------------------------------- 0 127.0.0.1 node0 y 1 127.0.0.1 node1 n LogicSQL> select * from system.definition_schema.grid_sockets; source_grid_id destination_grid_id no_connections destination_port port_increment ---------------------------------------------------------------------------------------- 0 1 10 7000 10 1 0 10 8000 10

  The first table contains one row for each grid node, including information such as the grid id, ip address, grid name. The fourth column of the table, is_main_grid, is used to indicate if the node is used as the main node with the access socket. Note that at this moment, only one node can be used as the main node.

  The second table stores the communication channels among grid nodes. For example, the first row in the table indicates that (1) the source and destination of grid node ids are 0 and 1 respectively, (2) the source node will establish 10 connections to the destination port, and (3) the socket port of the destination is 7000. The last column is not used for now.

  The grid initialization file is, in fact, an SQL script file used by the RubatoDB to initialize these two system tables.

  The following is a sample grid initialization file used to set up the system as the above two tables.

  ------------------------------------------------------------------------------------------------------------ /* * The Grid Node configuration file */ -- The number of nodes and its ip address insert into system.definition_schema.grid_nodes values (0,'127.0.0.1','node0','y'); insert into system.definition_schema.grid_nodes values (1,'127.0.0.1','node1','n'); -- The communication sockets of the main node insert into system.definition_schema.grid_sockets values(0,1,10,7000,10); -- The communication sockets of the non-main node insert into system.definition_schema.grid_sockets values(1,0,10,8000,10); ------------------------------------------------------------------------------------------------------------

  If one needs to run RubatoDB on four grid nodes, then one needs to have four rows on the table system.definition_schema.grid_nodes, one for each node. One also needs to designate one of those nodes as as the main node. The main node is the one containing the socket for all clients to access to the RubatoDB server.

  One also has to set up the number of sockets on each node for other grid nodes to connect for internal communication. In the current implementation, the only communication channels in RubatoDB are between the main node and other nodes, and therefore, only the following ports must be set up.

  • The main nodes must set up one group of sockets for each non-main node; and
  • Each non-main node must set up one group of sockets for the main node to assess.

  The following is another sample grid initialization file used to set up the RubatoDB running on four grid nodes.

  The first table contains four (4) rows: one for each grid node, and designates the grid node 0 as the main node.

  The second table contains six (6) row: the first three rows indicate that the main node establishes 30 ports, 10 for each non-main node, and the last three rows indicate that each non-main node has 10 sockets open for the main node to access. Please note that all 30 ports in the main node must distinct and must be different from the access port used by the main node for clients to use.

  ------------------------------------------------------------------------------------------------------------ /* * The Grid Node configuration file for RubatoDB running on the four servers * */ -- The number of nodes and its ip address insert into system.definition_schema.grid_nodes values (0,'129.12.25.191','node0','y'); insert into system.definition_schema.grid_nodes values (1,'129.12.25.192','node1','n'); insert into system.definition_schema.grid_nodes values (2,'129.12.25.193','node2','n'); insert into system.definition_schema.grid_nodes values (3,'129.12.25.194','node3','n'); -- The communication sockets of the main node insert into system.definition_schema.grid_sockets values(0,1,10,7000,10); insert into system.definition_schema.grid_sockets values(0,2,10,6000,10); insert into system.definition_schema.grid_sockets values(0,3,10,5000,10); -- The communication sockets of the non-main node insert into system.definition_schema.grid_sockets values(1,0,10,8000,10); insert into system.definition_schema.grid_sockets values(2,0,10,8000,10); insert into system.definition_schema.grid_sockets values(3,0,10,8000,10); ---------------------------------------------------------------------------------------------------------------

  According to your needs, you have to set up the grid initialization file $LOGICSQL/bin/grid_initial.sql as described. All grid nodes will use the same grid initialization file.

  ---

  [ < ]

  [ > ]

  [ << ]

  [ Up ]

  [ >> ]

  [Top]

  [Contents]

  [Index]

  [ ? ]

  3.3.2 Server Configuration

  The system configuration file $LOGICSQL/config/logicsql.conf is 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

  • Data Cluster You must initialize a database storage area on disk, called a data cluster. A data 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 data 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 2.3.9 CREATE DATABASE.

    In file system terms, a data 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 2.1.3 Blob and Clob. 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.

  • Inverted Index Cluster You may also need to initialize a database storage area on disk, called a inverted index cluster if your applications use the search facilities extensively.

    The default inverted index area is LOGICSQL/ind, though you may set IndDictory to be the path name of your choice.

  • Database Logs RubatoDB 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 RubatoDB 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 3.7 Database Recovery for details.

    If you choose standby_log or both_logs as your choice of crash recovery then you must create another RubatoDB 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 2.3.4 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 data cluster	optional with default being $LOGICSQL/data
  LobDirectory	path name of the lob cluster	optional with default being $LOGICSQL/lobs
  IndDirectory	path name of the inverted index cluster	optional with default being $LOGICSQL/ind
  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.

  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 statements	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
  MaxLobSize	the size limit for any BLOB/CLOB	optional with default 4Mbytes
  StringSize	the size limit of a string or a component of any table, excluding that of BLOB/CLOB.	optional with default 2048

  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
  CACHSIZE	the size of cash	optional with default 4194304
  IndCachSize	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 ---------------------------------- AioEnd 9 AioStart 1 CurrentNode 0 DataMinKey 0 DefaultGridNode 0 DocumentStatus 1 IdSize 64 IndMinKey 100 IndPageSize 2048 LazyRatio 85 LimitNewRequests 3 MaxClients 16384 MaxDegrees 64 MaxEnabledRoles 8 MaxIndexes 16 MaxInteger 38 MaxKeys 8 MaxLobSize 134217728 MaxOpenTables 1024 MaxPrecision 38 MaxScale 15 MaxTables 8 MaxTransactionSize 16384 MaxTriggerCalls 32 NormalLobSize 4194304 NoWordTables 8 PageSize 512 ResultSetCoding 0 RowSize 131072 SelectNumber 10 StringSize 1024 TimeSchema 7 TupleLimit 1000000 LogicSQL>

  Note that some configuration variables shall NOT be modified after the installation of the initial database.

  Note that different grid nodes need not to use the same the configuration file, especially the path names of data cluster, lob cluster, inverted index cluster, etc.

  ---

  [ < ]

  [ > ]

  [ << ]

  [ Up ]

  [ >> ]

  [Top]

  [Contents]

  [Index]

  [ ? ]

  3.4 Managing Database Server

  .

  ---

  [ < ]

  [ > ]

  [ << ]

  [ Up ]

  [ >> ]

  [Top]

  [Contents]

  [Index]

  [ ? ]

  3.4.1 System Creation

  After successful installation of RubatoDB in all grid nodes, one has to create the initial database on all nodes. This can be done by using the following command in each and every node:

  Linux:> logicsql -c

  This will create the initial database system in the data cluster of the current node. This will also automatically create a system user named 'system' with the default password 'manager'.

  The initial database will be created independently in each node, and thus, it can be done in any ordering of grid nodes.

  ---

  [ < ]

  [ > ]

  [ << ]

  [ Up ]

  [ >> ]

  [Top]

  [Contents]

  [Index]

  [ ? ]

  3.4.2 Starting the RubatoDB server

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

  Assume that you are going to run RubatoDB on four (4) grid nodes, and have configured the system accordingly. Further, all the initial databases have been created as well on all four grid nodes.

  Start the RubatoDB using the following command on fall our nodes, in the order of node1, node2, node3, node4, as follows:

  Node0 Linux:> logicsql -n0

  Node1 Linux:> logicsql -n1

  Node2 Linux:> logicsql -n2

  Node3 Linux:> logicsql -n3

  One shall not start the server on Node 1 until the start at Node0 complete, and shall not start the server on Node2 until the start at Node1 completes, and so on so forth.

  For each data cluster, only ONE grid node should be started at any time. A separate RubatoDB user account or the corresponding data cluster must be set up if you wish to run two nodes 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.

  Linux:> logicsql -h Usage: logicsql [-options] where options include -p <port_number> specify the port number used by the server, the default is specified by the configuration file -n the grid node ID -c create the initial database -h print this message Linux:>

  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.

  ---

  [ < ]

  [ > ]

  [ << ]

  [ Up ]

  [ >> ]

  [Top]

  [Contents]

  [Index]

  [ ? ]

  3.4.3 Shutting down the RubatoDB server

  The RubatoDB server can be shut down with an SQL command shutdown

  Linux:> sqlface system/manager@:127.0.0.1:8010 ========================================================== Welcome to the LogicSQL system ========================================================== Enter an sql command ending with ';', or any of the following letter h)elp, q)uit, s)tart a file of commands LogicSQL> shutdown; 0000008046 Shutdown SERVER: the database will be shut down immediately; LogicSQL>

  See 5.2 SQL*Face and 2.4.5 SHUTDOWN for details.

  Note that using shutdown_server will not release the database socket used by the server immediately. 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 RubatoDB server using any standard SQL interface, such as 5.2 SQL*Face and/or 5.1 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. RubatoDB 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.

  ---

  [ < ]

  [ > ]

  [ << ]

  [ Up ]

  [ >> ]

  [Top]

  [Contents]

  [Index]

  [ ? ]

  3.4.4 Installation Examples

  In this example we show how to configure and set up the RubatoDB on four grid nodes, each of which runs on one independent Linux server.

  Installation Example 1 In this example we are going to demonstrate how to install RubatoDB running on two grid nodes in two Linux machines.

  • Step 1 Preparation.

    Download the RubatoDB distribution file rubatodb_dist.tgz into the home directory $HOME, and then unpack the distribution file in the directory using

    Linux:> cd

    Linux:> gzip -cd rubatodb_dist.tgz |tar xf -
    

    Then $HOME/rubatodb_dist contains the following list of directories and files:

    • bin
    • config
    • docs
    • include
    • lib
    • README

  • Step 2 Configure the grid initialization file: grid_initial.sql.

    Assume that the IP addresses of the two machines are 120.121.122.101 and 120.121.122.102, and the first one will be used as the main node. Further, we also assume that the main node uses 8010 as the monitoring socket, and 8000 as the first socket for internal uses. The second node uses 7000 as the socket for internal uses.

    The file shall contain four lines: the first two lines represent the two rows in system.definition_schema.grid_nodes, one for each grid node; and the second two lines represent the two rows in system.definition_schema.grid_sockets, one for the main node, and one for each (there is only one) non-main node.

    The following is the grid configuration file.

    /* * The Grid Node configuration file */ -- The number of nodes and its ip address insert into system.definition_schema.grid_nodes values (0,'120.121.122.101','node0','y'); insert into system.definition_schema.grid_nodes values (1,'120.121.122.102','node1','n'); -- The internal communication sockets of the main nodes insert into system.definition_schema.grid_sockets values(0,1,10,8000,10); -- The internal communication sockets of all the non-main nodes insert into system.definition_schema.grid_sockets values(1,0,10,7000,10);

    This file can be obtained by first copying the the provided template file using

    /bin/cp -f $HOME/rubatodb_dist/bin/templates/grid_initial.2.sql $HOME/rubatodb_dist/bin/grid_initial.sql

    and then editing it accordingly.

    If one wishes to run RubatoDB on two grid nodes on the one machine, replace the above two IP addresses with an identical one (or, with 127.0.0.1).

  • Step 3 Configure the configuration file: logicsql.conf.

    For simplicity, we assume that all data clusters are located at the installation directory, and we use a command line option to specify the main socket. Hence, the configuration file in the distribution package needs not be modified. Therefore, one only needs to modify one line in the configuration file to indicate that the number of regular nodes is 2. This can be done by coping the provided template configuration file @HOME/rubatodb_dist/config/logicsql.2.conf using

    Linux:> /bin/cp -f $HOME/rubatodb_dist/config/templates/logicsql.2.conf $HOME/rubatodb_dist/config/logicsql.conf

  • Step 4 Open two terminals, one for each node. In the sequence, we use Linux 0:> and Linux 1:> to denote the prompt in the respective terminals.

  • Step 5 Create the installation directory on each grid node.

    We use $HOME/rubatodb0 and $HOME/rubatodb1 as the installation directories on node0 and node1 respectively.

    The installation directories can then be created using using the following commands at the home directory on the respective node.

    Linux 0:> mkdir $HOME/rubatodb0

    Linux 1:> mkdir $HOME/rubatodb1

  • Step 6 Copy all the directories and files in the RubatoDB distribution directory in to the installation directory of each node using

    Linux 0:> /bin/cp -fr $HOME/rubatodb_dist/* $HOME/rubatodb0

    Linux 1:> /bin/cp -fr $HOME/rubatodb_dist/* $HOME/rubatodb1

  • Step 7 Set up the environment variables for X Term 1.

    For the tcsh, use the following:

    Linux 0:> setenv LOGICSQL $HOME/rubatodb0

    Linux 0:> setenv PATH $LOGICSQL/bin:$PATH

    For the bash, use the following:

    Linux 0:> export LOGICSQL=$HOME/rubatodb0

    Linux 0:> export PATH=$LOGICSQL/bin:$PATH

  • Step 8 Set up the environment variables for X Term 2.

    For the tcsh, use the following:

    Linux 1:> setenv LOGICSQL $HOME/rubatodb1

    Linux 1:> setenv PATH $LOGICSQL/bin:$PATH

    For the bash, use the following:

    Linux 1:> export LOGICSQL=$HOME/rubatodb1

    Linux 1:> export PATH=$LOGICSQL/bin:$PATH

  • Step 9 Create the initial database for Node0 using X Term 1.

    Linux 0:> logicsql -c

  • Step 10 Create the initial database for Node1 using X Term 2.

    Linux 1:> logicsql -c

  • Step 11 Start the RubatoDB server on Node0 using X Term 1.

    Linux 0:> logicsql -n0 &

  • Step 12 Start the RubatoDB server on Node1 using X Term 2.

    Linux 1:> logicsql -p7000 -n1 &

    The RubatoDB server is now running on the two grid nodes and it is ready to process all requests, though the socket port 8010 at the node 1.

    Now we demo how to access the RubatoDB using SQL*Face.

  • Step 13 Create a table and populate it with four rows. using X Term 1.

    Linux 0:> sqlface system/manager@:127.0.0.1:8010 ========================================================== Welcome to the LogicSQL system ========================================================== Enter an sql command ending with ';', or any of the following letter h)elp, q)uit, s)tart a file of commands LogicSQL> select * from system.definition_schema.users; user_name password user_id date_created --------------------------------------------------------------------------------------- logman 815001753825e824a9cdcf74c4fd76a85cc8e6a5 11 2014-04-27 20:21:33 system 7d2abff56c15d67445082fbb4acd2dcd26c0ed57 10 2014-04-27 20:21:26 LogicSQL> CREATE TABLE stock ( s_w_id integer, s_i_id integer, s_dist_01 varchar(24), s_dist_02 varchar(24), s_ytd integer, s_data varchar(500), s_quantity integer, constraint stock_primary_key primary key (s_w_id,s_i_id) ) PARTITION BY GRID (s_w_id) ( PARTITION g_1 in grid(0) if mod(s_w_id,2)=0, PARTITION g_2 IN grid(1) if mod(s_w_id,2)=1 ) PARTITION BY COLUMN ( PARTITION column_1 (s_w_id,s_i_id,s_dist_01,s_dist_02,s_data), PARTITION column_2 (s_w_id,s_i_id,s_ytd), PARTITION column_3 (s_w_id,s_i_id,s_quantity) ); 0000008020 CREATE TABLE: the table has been created; LogicSQL> select * from stock; s_w_id s_i_id s_dist_01 s_dist_02 s_ytd s_data s_quantity ------------------------------------------------------------------------ LogicSQL> insert into stock values(1,101,'mvxr','faws',0,'ahwav',55); 0000008004 INSERT STATEMENT: the tuple has been inserted; LogicSQL> insert into stock values(1,102,'ab','aws',10,'kifxsl',67); 0000008004 INSERT STATEMENT: the tuple has been inserted; LogicSQL> insert into stock values(2,302,'pdch','pwe',4,'bdgo',34); 0000008004 INSERT STATEMENT: the tuple has been inserted; LogicSQL> insert into stock values(2,301,'tey','xskf',32,'rsyv',29); 0000008004 INSERT STATEMENT: the tuple has been inserted; LogicSQL>

    Step 13 creates one table named stock that is partitioned into three column partitions over two grid nodes, and then insert four rows into the table.

  • Step 14 Query the table using X Term 2.

    Linux 1:> sqlface system/manager@:127.0.0.1:8010 ========================================================== Welcome to the LogicSQL system ========================================================== Enter an sql command ending with ';', or any of the following letter h)elp, q)uit, s)tart a file of commands LogicSQL> select * from stock; s_w_id s_i_id s_dist_01 s_dist_02 s_ytd s_data s_quantity ------------------------------------------------------------------------ 1 101 mvxr faws 0 ahwav 55 1 102 ab aws 10 kifxsl 67 2 301 tey xskf 32 rsyv 29 2 302 pdch pwe 4 bdgo 34 LogicSQL> select table_name,grid_order,column_order,table_partition,grid_column, list_of_columns from system.definition_schema.table_partitions; table_name grid_order column_order table_partition grid_column list_of_columns ------------------------------------------------------------------------------------------------------------------------------------------ stock 0 1 stock_column_1_g_1 s_w_id [s_w_id,s_i_id,s_dist_01,s_dist_02,s_data] stock 0 2 stock_column_2_g_1 s_w_id [s_w_id,s_i_id,s_ytd] stock 0 3 stock_column_3_g_1 s_w_id [s_w_id,s_i_id,s_quantity] stock 1 1 stock_column_1_g_2 s_w_id [s_w_id,s_i_id,s_dist_01,s_dist_02,s_data] stock 1 2 stock_column_2_g_2 s_w_id [s_w_id,s_i_id,s_ytd] stock 1 3 stock_column_3_g_2 s_w_id [s_w_id,s_i_id,s_quantity] LogicSQL>

    The last query retrieves the table partitions for the table stock.

  • Step 15 One may use SQL*Face to shutdown the RubatoDB server as follows:

    Linux 1:> sqlface system/manager@:127.0.0.1:8010 LogicSQL> shutdown; The server will be shurtodwn soon 0000008046 Shutdown SERVER: the database will be shut down immediately ! LogicSQL> q

    To start the server, repeat Steps 9 and 10.

  Installation Example 2 In this example we first show how to configure and set up the RubatoDB on four grid nodes, each of which runs on one independent Linux server, and then demo how to take the TPC-C benchmark test on RubatoDB.

  See Installation RubatoDB on four servers for details.

  ---

  [ < ]

  [ > ]

  [ << ]

  [ Up ]

  [ >> ]

  [Top]

  [Contents]

  [Index]

  [ ? ]

  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

  2.3.9 CREATE DATABASE

  and

  2.3.28 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.

  ---

  [ < ]

  [ > ]

  [ << ]

  [ Up ]

  [ >> ]

  [Top]

  [Contents]

  [Index]

  [ ? ]

  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 data cluster installation (and not per individual database). To create a user use the SQL command 2.3.19 CREATE USER, such as

    CREATE USER your_account

    To remove an existing user, use the analog SQL command 2.3.36 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,

  ---

  [ < ]

  [ > ]

  [ << ]

  [ Up ]

  [ >> ]

  [Top]

  [Contents]

  [Index]

  [ ? ]

  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, 3.7.6 A Typical Recovery Schema is presented at the end of this section.

  ---

  [ < ]

  [ > ]

  [ << ]

  [ Up ]

  [ >> ]

  [Top]

  [Contents]

  [Index]

  [ ? ]

  3.7.1 The Schemes of the Backup Logs

  RubatoDB provides facilities for storing backup log files at different disk file systems and/or different locations with three different options when initializing the database server with logicsql.
  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 RubatoDB 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 RubatoDB 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 RubatoDB server.

  ---

  [ < ]

  [ > ]

  [ << ]

  [ Up ]

  [ >> ]

  [Top]

  [Contents]

  [Index]

  [ ? ]

  3.7.2 The Types of Recovery

  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.

  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.

  ---

  [ < ]

  [ > ]

  [ << ]

  [ Up ]

  [ >> ]

  [Top]

  [Contents]

  [Index]

  [ ? ]

  3.7.3 Snapshot of the Database

  For crash recovery, it is critical to keep a snapshot of the database that is consistent.

  The database state is uniquely determined by three data directories, i.e., Data Cluster, Lob Cluster, and Inverted Index Cluster. Consequently, a snapshot of the database shall consist of a copy of all these three directories.

  Since the Lob Cluster usually stores a large collection of lob files, it is not realistic to contain all lobs files in each and every snapshot of the database. RubatoDB is, therefore, designed to store lob files in the Lob Cluster incrementally. That is, all lobs are stored in files based on the time schema. Take the daily schema as an example. All lobs uploaded in different days will be stored in different lob files, and each lob file is backup ed in the Log Cluster. By this schema, all lob files in the Lob Cluster are backuped in the lob Cluster, and therefore there is no need to take a copy of the Lob Cluster in the snapshot.

  To make a snapshot of the database is thus to make a copy of two directories: Data Cluster and Inverted Index Cluster.

  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.
  The execution of these three commands guarantees that the database is consistent and no one, except system users, can access the server. A consistent snapshot of the database can then be obtained by simply coping the directories into ones backup media.

  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.
  The clearance of the log system will significantly reduce the size of the log files which not just saves the disk space but also speed up the recovery process.

  ---

  [ < ]

  [ > ]

  [ << ]

  [ Up ]

  [ >> ]

  [Top]

  [Contents]

  [Index]

  [ ? ]

  3.7.4 Snapshot and LOB data

  For databases with huge amount of data, it is very difficult, if not impossible, to copy the snapshot on daily bases. For example, the size of the database underline a hospital's PACS (Picture Archiving and Communication) system may be 2-10 TB.

  Since most voluminous data are stored as BLOB/CLOB records, RubatoDB adapts an incremental storage system, called time sliced schema for storing LOB data as follows:

  • All BLOB/CLOB data are stored in the lob cluster, which is a possibly different disk system that than of the data cluster.

  • RubatoDB creates a sequence of lob tables to store BLOB/CLOB records in the lob cluster. Each lob table (or a set of lob tables) is used to store lob records generated in a specified period of time.

    For example, all lob records generated within specified day, say September 1, 2005, will be stored in the table named lob2005-09-01_1.

  • According to the data size, RubatoDB allows the system users to specify the time period of lob tables through the configuration file. More specifically, the configuration variable LobSchema can be configured to one of the following values, with the default as TENDAYS:
    • day
    • tendays
    • month
    • year
    If the default TENDAYS is adapted, RubatoDB will automatically create a group of lob tables in the beginning of first day, 11th day, and 21st day of each month to store all lob records inserted into the server during that period of time.
  • The same group of lob tables are also created to store the same lob records in the log-area, i.e., LogDirectory, for the purpose of backup and recovery.
  • With such time sliced logging schema, it is straightforward to use an incremental snapshot procedure for only the very current group of lob tables are needed in the snapshot and all other information can be recovered from the old snapshots if needed.

  For example, consider the database underline a PACS system for a hospital of the median size, which usually generates about 1 TB lob data each year. Further, assume that a snapshot is taken daily, and that the Lob Schema is also configured as daily, that is, all the lob records generated each day are stored in their respected group of tables. Thus, the average size of the snapshot taken each day is less than 3 GB which can be easily handled.

  ---

  [ < ]

  [ > ]

  [ << ]

  [ Up ]

  [ >> ]

  [Top]

  [Contents]

  [Index]

  [ ? ]

  3.7.5 Recovery Procedures

  In this subsection, we present the detailed procedures for database recovery.

• 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.