[Top] [Contents] [Index] [ ? ]

The Documentation of RubatoDB (LogicSQL)

1. Introduction  
2. User's Guide  

2.1 Data Types  
2.2 Functions and Operators  
2.3 SQL Commands  
2.4 System Utility Commands  
2.5 XML Commands  
2.6 Security Model  
2.7 System Information and Information_schema  
2.8 RubatoDB Text and Search  
2.9 Grid Management and Table Partition  
2.10 SQL Syntax  
3. Database Administrator's Guide  

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

4.1 JDBC  
4.2 ODBC  
4.3 Embedded C Pre-compiler  
4.4 The C Language Interface  
4.5 MySQL Interface  
5. Development Tools  

5.1 DBA Tools  
5.2 SQL*Face  
5.3 Loader  
6. Database Tests  

6.1 SQL Conform Tests  
6.2 TPC-C Benchmark Tests  
Concept Index  
Bug Report and Contact Information  
Copyright (C) 2000-2014 Shanghai Shifang Software, Inc.

The document is available at www.cs.ualberta.ca/~yuan/databases/logicsql/docs/logicsql.html


[ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

1. Introduction

RubatoDB (LogicSQL) is a highly scalable database management system for big data that supports both the ACID and BASE properties.

RubatoDB is implemented with the following two distinguished features:

  1. It uses a grid database architecture, based on the staged architecture, proposed by Welsh for parallel web servers.
  2. It uses a new formula protocol for distributed concurrency control.

RubatoDB, implemented using the aforementioned two features, conforms with the SQL2003 and has been used in some commercial applications.

We have conducted extensive experiments of RubatoDB which clearly show that RubatoDB is highly scalable with ACID under the TPC-C benchmark tests. Our experiments using the YCSB benchmark also demonstrate that the performance of RubatoDB is comparable with some popular big data systems based on the MapReduce framework that supports only the BASE properties.

The figure below demonstrates that the performance (tpmC) of the TPC-C benchmark test of RubatoDB, from 25,000 concurrent clients running on 1 server, to 320,000 concurrent clients running on a collection of 16 commodity servers.

scalable50

The figure clearly shows that under the TPC-C benchmark test, the performance of RubatoDB scales up linearly with the increase of the number of servers used.

The following figure compares the performance of RubatoDB with three other popular key-store systems, in terms of the number of throughput, a standard performance measurement of the YCSB benchmark.

readthroughput

From the graphics, we can see that the performance of RubatoDB is comparable with these big data systems that only support the BASE properties.

RubatoDB (LogicSQL) is designed to provide:

  1. a reliable and secure big database management system for both OLTP applications with the ACID properties to big data applications with the BASE properties.
  2. cost-effective and easy-to-use database functionality that is compatible with the SQL2003 standard and supports standard user interfaces such as the JDBC, ODBC, and embedded C interface.

The functionality of RubatoDB is demonstrated by the 2.1 Data Types, 2.2 Functions and Operators, and 2.3 SQL Commands it supports. Specifically, RubatoDB supports

  1. all basic SQL types, including date, 2.1.3 Blob and Clob, and clob,
  2. a query facility that is as expressive as any other commercial database management systems,
  3. secondary indexes, SQL views, SQL triggers and SQL sequences,
  4. state-of-art text query facilities, and
  5. a transaction management facility with the isolation level as high as serializability, and
  6. a security facility based on the discretionary access control mechanism.

The interfaces supported by RubatoDB include:

  1. the standard Java interface 4.1 JDBC,
  2. the standard C interface 4.2 ODBC,
  3. the .net interface,
  4. the standard 4.3 Embedded C Pre-compiler, and
  5. 4.4 The C Language Interface.
RubatoDB also provides the following convenient developing tools
  1. 5.1 DBA Tools, a graphic dba/user interface,
  2. 5.2 SQL*Face, an interactive access application, and
  3. 5.3 Loader, an SQL loader for loading big tables.


[ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

2. User's Guide

2.1 Data Types  
2.2 Functions and Operators  
2.3 SQL Commands  
2.5 XML Commands  
2.4 System Utility Commands  
2.6 Security Model  
2.7 System Information and Information_schema  
2.8 RubatoDB Text and Search  
2.9 Grid Management and Table Partition  
2.10 SQL Syntax  


[ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

2.1 Data Types

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

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

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

The following table summarizes RubatoDB's redefined datatypes.

RubatoDB Type SQL99 Type Description
char(size),
character(size)
char(size), character(size) character string having maximum length size characters. By the character, we mean the number of valid UTF-8 characters. Note that each ASCII char is one UTF-8 character, and each Chinese character is also counted as one UTF-8 character, despite the fact that a Chinese character may takes up 3 bytes, while each ASCII char takes only one byte. The maximum size is 4096 (or StringSize as specified in configuration ) and the minimum is 1. The default size (when size is not given) is 1.
varchar(size),
char varying(size),
character varying(size)
varchar2(size)
row(size)
varchar(size) the size limit of variable-length character string is the same as the char type given above. Note that the internal data type for all variable-length character strings are varchar(size). The default size (when size is not given) is StringSize, usually 1024. See system limitation for details.
character_data character string with the size limited only to the system constraint
uniqueidentifier char(32) a specially specified datatype for GUID
int, integer int, integer integers with value between -2147483648 and 2147483647, inclusive.
smallint smallint integers with value between -32768 and 32767, inclusive.
tinyint tinyint integers with value between -128 and 127, inclusive
bigint bigint integers
identity sequence a column property associated with integers to automatically generate sequential numbers
long long integers
numeric(P, S),
numeric(P),
numeric,
number(P,S),
number(P),
number
numeric(P,S) The numeric datatype stores a fixed or floating number with precision P and scale S, where P < 38, and -15 < S < 15, defaults of P and S are 38 and 0 respectively. Note that the internal data type for all the numbers are numeric.
decimal(P, S),
dec(P, S),
real
decimal(P,S), etc. The number datatype stores a fixed or floating number with precision P and scale S, where P < 38, and -15 <S < 15, defaults of P and S are 38 and 0 respectively.
float(P), float float(P), float The number datatype stores a fixed or floating number with precision P and scale S, where P < 38, and -14 < S < 14, defaults of P and S are 38 and 0 respectively. (For now, float is treated the same as numeric.)
date date The datatype stores the date information in a string of form '2000-10-01' with the interval form the same as that of timestamps below.
time time The time type stores the time information in a string of form '18:09:50'.
timestamp timestamp The datatype stores the date and time information in a string of form '2000-10-01 18:09:50'.
2.1.3 Blob and Clob blob(size) The blob datatype stores unstructured binary large objects. It can store up to 2 gigabytes of binary data, limited by available memory.
clob clob(size) The clob datatype stores single-byte character data. It can store up to 2 gigabytes of character data, limited by available memory.
long varchar long varchar character data of variable length, implemented using clob for backward compatibility
long raw long raw Raw binary data of variable length, implemented using blob for backward compatibility

CHAR datatype specifies a fixed-length character string. When you create a table with a CHAR column, you supply the column length in bytes. RubatoDB subsequently ensures that all values stored in that column have this length. If you insert a value that is shorter than the column length, RubatoDB adds extra white-space, i.e., ' ', up to column length. If you try to insert a value that is too long for the column, RubatoDB returns an error. To compare a CHAR column with a given string, we compare the white-space padded column value with the given string. Therefore, one shall use a white-space padded column string to avoid any confusion.

Consider a table crated and populated by the following statements.

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

Then three queries below lead to different result tables.

 
LogicSQL> select * from students;

  sid           sname  
  ---------------------
  12345         Tom    
  1234567890    Sarah  

LogicSQL> select * from students where sid = '12345     ';

  sid           sname  
  ---------------------

LogicSQL> select * from students where sid = '12345';

  sid    sname  
  --------------
  12345         Tom    

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


[ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

2.1.1 GUID and Uniqueidentifier

A Globally Unique IDentifier or GUID is a pseudo-random number used in software applications. Each generated GUID is "mathematically guaranteed" to be unique. This is based on the simple principle that the total number of unique keys so large that the possibility of the same number being generated twice is virtually zero.

RubatoDB provides the system specified data type, uniqueidentifier, and SQL function SYS_GUID (also being called as NEWID) to facilitate developers to use GUID in their applications.

SYS_GUID is an SQL function that generates a Globally Unique Identifier or GUID of a 26-character hexadecimal string.

SYS_GUID can be used in the INSERT or UPDATE statement to generate a new value for INSERTION or UPDATE, the same way as any other SQL functions.

The following data types can be used for columns with GUID:

Note that uniqueidentifier is a datatype specially specified for GUID, though it may also be used the same way as VARCHAR(32).

The GUID generated by RubatoDB usually consists of the mac address of the host server, a current transaction identifier, which itself is unique for any working server, and a sequence of random numbers, which guarantees to be globally unique.

The GUID values are randomly generated long strings and the values are meaningless. That means,

The main feature of using RubatoDB generated GUID is that their values are guaranteed to be unique, and thus shall be used only when it is needed.

Examples

The following example demonstrates how to use SYS_GUID() (or NEWID() ) in an INSERT statement

 
  CREATE TABLE student (
     global_id  RAW(13),
     name       VARCHAR(128),
     constraint ts_pk  primary key(global_id)
  );

  INSERT INTO student VALUES (SYS_GUID(), 'Sarah');
  INSERT INTO student VALUES (SYS_GUID(), 'Peter');
  INSERT INTO student VALUES (SYS_GUID(), 'Tim');
 
  SELECT * FROM student;

  global_id                     name   
  -------------------------------------
  50622a3f000002782bcb941199    Sarah  
  50622a3f000003782bcb941199    Peter  
  50622a3f000004782bcb941199    Tim    


The following example demonstrates how to use SYS_GUID() (or NEWID() ) in a CREATE TABLE statement to automatically generate GUID for the specified column.

 
  CREATE TABLE student (
     global_id  UNIQUEIDENTIFIER  DEFAULT NEWID(),
     name       VARCHAR(128),
     constraint ts_pk  primary key(global_id)
  );

  INSERT INTO student (name) VALUES ('Sarah');
  INSERT INTO student (name) VALUES ('Peter');
  INSERT INTO student (name) VALUES ('Tim');

  SELECT * FROM student;

   global_id                     name   
  -------------------------------------
  50623a2c000002782bcb941199    Sarah  
  50623a2c000003782bcb941199    Peter  
  50623a2c000004782bcb941199    Tim    



The following example shows that function SYS_GUID will be called ones for each row to be updated.

 
  CREATE TABLE student (
      global_id  VARCHAR(32)  DEFAULT NEWID(),
      name       VARCHAR(128),
      constraint ts_pk  primary key(global_id)
  );

  INSERT INTO student (name) VALUES ('Sarah');
  INSERT INTO student (name) VALUES ('Peter');
  INSERT INTO student (name) VALUES ('Tim');

  SELECT * FROM student;

  global_id                     name   
  -------------------------------------
  50623a2c000002782bcb941199    Sarah  
  50623a2c000003782bcb941199    Peter  
  50623a2c000004782bcb941199    Tim    

  UPDATE student set global_id = SYS_GUID();

  SELECT * FROM student;
  
  global_id                     name   
  -------------------------------------
  50623a2d000005782bcb941199    Sarah  
  50623a2d000006782bcb941199    Peter  
  50623a2d000007782bcb941199    Tim    


Please note that, after the update, all the GUIDs in the above table are replaced by newly created GUIDs.

The following example shows that updating the table with default may also be used to reset the global unique identifiers.

 
  CREATE TABLE student (
     global_id  CHAR(32)  DEFAULT NEWID(),
     name       VARCHAR(128),
     constraint ts_pk  primary key(global_id)
  );

  INSERT INTO student VALUES ('123456', 'Sarah');
  INSERT INTO student VALUES ('123457', 'Peter');
  INSERT INTO student VALUES ('123458', 'Tim');

  SELECT * FROM student;

  global_id                           name   
  -------------------------------------------
  123456                              Sarah  
  123457                              Peter  
  123458                              Tim    

  UPDATE student set global_id = default;

  SELECT * FROM student;

  global_id                           name   
  -------------------------------------------
  0012d5bc425dcc91000bcd8b36fd0001    Sarah  
  0012d5bd425dcc91000bcd8b36fd0001    Peter  
  0012d5be425dcc91000bcd8b36fd0001    Tim    

Despite of its name, a table may have any number of columns of UNIQUEIDENTIFIER, and their values need not be unique, unless a unique constraint has been explicitly specified.

The following table is created with two columns of UNIQUEIDENTIFIER, and since no unique constraint is specified for either column, the table can be populated with rows of the same value.

 
  CREATE TABLE student (
      global_id  UNIQUEIDENTIFIER  DEFAULT NEWID(),
      name       UNIQUEIDENTIFIER
  );

  INSERT INTO student (name) VALUES (SYS_GUID);
 
  SELECT * FROM student;

  global_id                           name                              
  ----------------------------------------------------------------------
  0012d61f425dce1d000bcd8b36fd0001    0012d61e425dce1d000bcd8b36fd0001  

  INSERT INTO student SELECT * FROM student;
  INSERT INTO student SELECT * FROM student;

  SELECT * FROM student;

  global_id                           name                              
  ----------------------------------------------------------------------
  0012d61f425dce1d000bcd8b36fd0001    0012d61e425dce1d000bcd8b36fd0001  
  0012d61f425dce1d000bcd8b36fd0001    0012d61e425dce1d000bcd8b36fd0001  
  0012d61f425dce1d000bcd8b36fd0001    0012d61e425dce1d000bcd8b36fd0001  
  0012d61f425dce1d000bcd8b36fd0001    0012d61e425dce1d000bcd8b36fd0001  


[ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

2.1.2 Identity and its usage

Identity is not a data type, and is best described as a column property. The identity property is used to automatically generate sequential numbers for a column, in the same manner as the SQL sequence, which is not associated with any column and thus can only be used explicitly in an SQL statement.

A column defined with the identity property is assigned the next sequential number whenever a row is inserted into the table. The use of the identity property is subject to the following restrictions:

  1. When data is inserted into the table with an identity column, a value should not be included for the identity column. Instead, one shall use the DEFAULT VALUES option (available with the INSERT statement), which enables RubatoDB to generate the next sequential value for the identity column.
  2. At most one column per table can be assigned with the identity property.
  3. An identity column must be one of the following data types:
    tinyint, smallint, int, integer, bigint, decimal, numeric(p,0).

Syntax The identity clause is part of the 2.3.17 CREATE TABLE statement, and can be used to replace the default clause

<identity clause> ::= {<IDENTITY [( <seed>, <increment> )] }

where seed and increment are integers to specify the initial value and the increment value of the identity. The default values for both are 1.

See CREATE TABLE statement for the use of the identity clause.

Example The following statements demonstrate how to creates a table with the identity column, and then populate it with INSERT statements.

 
CREATE TABLE email_archive (
   email_id    INT IDENTITY(10, 2) PRIMARY KEY,
   e_subject   VARCHAR(128),
   e_date      DATE
);

INSERT INTO email_archive(e_subject,e_date) values ('ipod on sale', sysdate);
INSERT INTO email_archive(e_subject,e_date) values ('midterm election',sysdate);

LogicSQL> select * from email_archive;

  email_id    e_subject            e_date
  -----------------------------------------------------
  10          ipod on sale         2006-10-21 12:38:43
  12          midterm election     2006-10-21 12:38:48

LogicSQL>

RubatoDB makes no atempt to fill the gap among the identity sequence numbers. The gap may be caused by deleted rows or by cancellation of exceptions.

The the following are features to be implemented in the near futures:

  1. set identity_insert table_name on/off
  2. the numeric function @identity to return the value of the last identity.


[ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

2.1.3 Blob and Clob

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

Important Note about the lob size. The size limit on one BLOB/CLOB by RubatoDB is up to to 2 G bytes. However, for easy management of the memory, one configuration variable, MaxLobSize, set up the upper limit on the size. One may configure variable according to one's needs, up to 2G. See 3.3 System Configuration for details.

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

Consider the table employee created with the following statement

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

More examples can be found below


[ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

2.1.4 User-defined Data Types


[ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

2.2 Functions and Operators

This section describes operators and functions supported by RubatoDB. These functions and operators, including standard arithmetic operators such as addition and subtraction and logical operators such as AND and OR as well as less common functions such as char_length and absolute values, are methods of manipulating individual data items.


[ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

2.2.1 Operators

An operator manipulates a given list of individual data items, called operands, and returns a value (result). Operators are represented by special characters or by keywords. For example, the multiplication operator is represented by an asterisk (*) and the logical conjunction is represented by a key word AND.

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

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

We will describe all the operators in details.

Precedence

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

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

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

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


[ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

2.2.2 SQL Functions

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

function(argument, argument, ...)

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

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

There are two types of SQL functions:

The two types of SQL functions differ in the number of arguments and results returned. A scalar function returns a single result for the given list of arguments; an aggregate function is applied into a group of rows and returns a single result for each group of rows in the queried table.

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

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

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

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

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

Table RubatoDB Aggregate Functions

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

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

Scalar functions can be classified as the follows.

Numeric Functions

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

Numeric Functions with Char/Date-time Arguments

The following table lists character functions that return number values.

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

Character (String) Functions

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

Date and Time Functions

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

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

Cast Functions

Cast functions convert a value from one datatype to another.

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

RubatoDB Text and Search Functions

corresponding CONTAINS function
Function Result Returned
2.8.5 CONTAINS( column name, text expression, label) the text querying function
AGGREGATE FUNCTION Return a temp table for TOP K queries
SCORE(label) Returns the same value as the
2.8.4 CURSOR_TABLE a function mapping an inverted cursor to a temporary table

Other Functions

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


[ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

2.2.3 Date Arithmetic

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

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

Note that RubatoDB supports only the data arithmetic expressions of the form date_expression +/- numeric_expression, and therefore, 10 + SYSDATE is considered an invalid expression.


[ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

2.2.4 IDENTITY

Function Syntax

IDENTITY(datatype [, seed, increment])

Description

IDENTITY is an incremental function used to generate a sequence of numeric values.

Similar to the 2.3.16 CREATE SEQUENCE, this function generates a sequence of numbers, starting with the seed, and then increasing by increment, that is,

seed + increment, seed + 2 * increment, ....

However, the IDENTITY function is only used in the CREATE TABLE BY QUERY statements, that is, the following the following two statements:

Example Assume table student is as specified in the Running Example. Then the following statement creates a new table named graduate as shown below.
 
LogicSQL>  CREATE TABLE graduate AS 
           SELECT IDENTITY(int, 100, 2) AS s_id,
               name AS s_name,
               major AS s_major
           FROM  student
LogicSQL> select * from graduate;

  s_id   s_name    s_major  
  -------------------------
  100      Bob       Business   
  102      Peter              
  104      Sarah     Math     
  106      Susan     Law 

 
    SELECT IDENTITY(int, 100, 2) AS s_id,
           sname,
           gpa
    INTO graduates
    FROM  students


[ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

2.2.5 TO_CHAR

Function: TO_CHAR

  • Syntax

    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.

    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  
    


    [ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

    2.2.6 CHR

    Function: CHR


    [ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

    2.2.7 INSTR

    Function: INSTR


    [ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

    2.2.8 CURRENT_CERTIFICATE

    Function: CURRENT_CERTIFICATE


    [ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

    2.2.9 CURRENT_GROUP

    Function: CURRENT_GROUP


    [ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

    2.2.10 ALL_GROUP

    Function: ALL_GROUP

  • Examples

    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> 
    


    [ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

    2.2.11 NVL

    Function: NVL


    [ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

    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 are used to perform the following tasks

    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

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

    Session Control statements dynamically change the behavior of a user 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


    [ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

    2.3.1 ABORT


    [ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

    2.3.2 ALTER TABLE


    [ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

    2.3.3 ALTER TRIGGER


    [ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

    2.3.4 ALTER USER


    [ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

    2.3.5 CLOSE CURSOR

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


    [ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

    2.3.6 COMMIT


    [ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

    2.3.7 CREATE AIO CURSOR

    See also 2.3.23 DROP AIO CURSOR, 2.3.8 CREATE AIO SCHEMA 2.8.4 CURSOR_TABLE, and 2.3.41 FETCH statements.


    [ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

    2.3.8 CREATE AIO SCHEMA

    Note that the above two types of the queries are the only ones supported by the AIO schema of RubatoDB.


    [ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

    2.3.9 CREATE DATABASE


    [ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

    2.3.10 CREATE DATATYPE

    not implemented


    [ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

    2.3.11 CREATE INDEX


    [ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

    2.3.12 CREATE INVERTED INDEX


    [ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

    2.3.13 CREATE PROCEDURE


    [ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

    2.3.14 CREATE ROLE


    [ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

    2.3.15 CREATE SCHEMA


    [ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

    2.3.16 CREATE SEQUENCE

    See also 2.3.33 DROP SEQUENCE.


    [ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

    2.3.17 CREATE TABLE


    [ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

    2.3.18 CREATE TRIGGER


    [ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

    2.3.19 CREATE USER


    [ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

    2.3.20 CREATE VIEW

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


    [ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

    2.3.21 DEALLOCATE


    [ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

    2.3.22 DECLARE CURSOR

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


    [ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

    2.3.23 DROP AIO CURSOR


    [ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

    2.3.24 DROP INVERTED CURSOR


    [ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

    2.3.25 CREATE INVERTED CURSOR


    [ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

    2.3.26 DELETE


    [ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

    2.3.27 DROP CURSOR

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


    [ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

    2.3.28 DROP DATABASE


    [ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

    2.3.29 DROP INDEX

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


    [ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

    2.3.30 DROP PROCEDURE

    See Also: 2.3.13 CREATE PROCEDURE


    [ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

    2.3.31 DROP ROLE


    [ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

    2.3.32 DROP SCHEMA

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


    [ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

    2.3.33 DROP SEQUENCE


    [ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

    2.3.34 DROP TABLE


    [ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

    2.3.35 DROP TRIGGER


    [ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

    2.3.36 DROP USER

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


    [ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

    2.3.37 DROP VIEW


    [ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

    2.3.38 DISABLE/ENABLE CONSTRAINT ENFORCEMENT

  • Function To enable/disable constraint enforcements.

  • Syntax

     
    <ENABLE|DISABLE> CONSTRAINT ENFORCEMENT
    
    


    [ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

    2.3.39 EXECUTE PROCEDURE


    [ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

    2.3.40 EXECUTE STATEMENT


    [ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

    2.3.41 FETCH

    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.


    [ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

    2.3.42 GRANT PRIVILEGE


    [ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

    2.3.43 GRANT ROLE


    [ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

    2.3.44 INSERT


    [ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

    2.3.45 LOCK TABLE

    See also 2.3.64 UNLOCK TABLE.


    [ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

    2.3.46 LOAD TABLE


    [ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

    2.3.47 OPEN CURSOR

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


    [ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

    2.3.48 PREPARE


    [ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

    2.3.49 RELEASE SAVEPOINT


    [ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

    2.3.50 REMOVE LOB


    [ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

    2.3.51 REQUEST LOBLOCATOR


    [ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

    2.3.52 REVOKE PRIVILEGE


    [ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

    2.3.53 REVOKE ROLE


    [ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

    2.3.54 ROLLBACK


    [ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

    2.3.55 SAVEPOINT


    [ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

    2.3.56 SET LANGUAGE


    [ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

    2.3.57 SET ROLE


    [ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

    2.3.58 SELECT and Subquery


    [ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

    3.7.6 A Typical Recovery Schema

    For your convenience, a typical recovery schema is presented this section, The main feature of this schema is the following
    1. The LOCAL_LOG is used for the backup schema.
    2. The snapshot is taken on the daily basis, incorporating with the system backup procedure if possible. To minimizing disturbance to the operations of the system, the snapshot should be taken around the lowest working load, usually around 2:00 AM.
    3. The clearance of the log system is conducted on weekly basis.
    A user-friendly interface for this typical recovery schema is currently under development and will be available soon.


    [ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

    3.8 Tutorial for DBA

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


    [ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

    4. Interfaces

    There are several approaches to access the RubatoDB database server.

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

    NOTES

    RubatoDB modifies its interface for the result set on October 22, 2006 to accommodate the requirements of the development of the .NET interface to RubatoDB.

    The result set now uses two different coding schemas, one is the original schema, and one the new schema.

    The coding schema of RubatoDB can be configured using the configuration variable name ResultSetCoding. This variable has two values, 1 standing for the new coding schema while 0 the original one.

    The new coding schema enables the result set to send the following information to her clients. That is, for each column in the result set, if all the items in the column are directly retrieved from a column in a persistent table stored in the database, then the new schema includes

    1. the name of the column,
    2. whether the column is part of the primary key of the table,
    3. whether the column is Nullable, and
    4. whether the column is part of the unique constraint for the table,
    where the column refers to the column from which the result set column is directed retrieved from.

    In the new result set coding schema, all the extra information is encoded as five items in the label of the result set column as follows. That is,

    1. the display label of the result set column,
    2. yes/no to indicate whether the column is part of the primary key,
    3. yes/no to indicate whether the column is nullable,
    4. yes/no to indicate whether the column is par of a unique key, and
    5. the name of the original column.

    Let Label denotes the string of the label obtained for a result set column.

    Then the first byte of Label represents the length of the label for the result set column. The exact length of the label is obtained using the formula (int)Label[0] - 64. That is, the underline integer value of the byte minus 64 is then the length of the label.

    Assume the length of the label represented in the first byte of Label is five (5), then the label of the result set column shall be the string starting at the second byte of Label and with 5 bytes long.

    The byte right after the end of the label string is a ASCII digit whose values are 48 to , and is used to code the status of the column as shown in the table below.

    Ascii Char (Value) Is Indentity Is Unique Key Is Primary Key Is Nullable
    0 (48) No No No No
    1 (49) No No No Yes
    2 (50) No No Yes No
    3 (51) No No Yes Yes
    4 (52) No Yes No No
    6 (53) No Yes No Yes
    7 (54) No Yes Yes No
    8 (55) No Yes Yes Yes
    9 (56) Yes No No No
    : (57) Yes No No Yes
    ; (58) Yes No Yes No
    < (59) Yes No Yes Yes
    = (60) Yes Yes No No
    > (61) Yes Yes No Yes
    @ (62) Yes Yes Yes No
    A (63) Yes Yes Yes Yes

    The rest of the string Label represents the underline column name.

    Since the extra information provided by this coding schema is only for the result columns whose values are retrieved from a column of the underline table, some items may not be available in the schema.

    If the result set column is not retrieved from the underline table column directly, then Label represents only the length of the display label, and the label itself.

    Further, the column name will be absent if it coincident with the display label.

    Example

    Assume Label is "Kstudent id 2sno".

    Since the first byte of Label is K while the ASCII number for 'K' is 75, the length of the label is 11. That is, the label is "student id ".

    Further, the byte after the label is 2 which indicates that the underline column is Nullable, but not part of the primary key nor part of any unique key.

    The rest of Label is "sno" which is the name of the column.

    Now consider another Label = "Csno2".

    The first byte 'C' indicates the length is 3 for the ASCII number of 'C' is 67, and therefore, the label shall be "sno". The next digit 2 indicates the status of the underline column is the same as the previous example.

    Since Label ends at 2, the name of the underline column is the same as the given label, i.e., "sno".

    The last Label considered is "Imax( sno)". The first byte I indicates that the length of the label shall be 9 and thus the label shall be "max( sno)". Nothing after the end of the label in Label, which indicates that there is no corresponding column for it. That is, the result column here is not retrieved directly from an underline table.


    [ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

    4.1 JDBC

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

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

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

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

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

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

    Examples of Applications Using JDBC

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

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


    [ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

    4.2 ODBC

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

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

    For more details, see The User Manual of RubatoDB ODBC Driver. (This document, however, may not be available in some of distractions.)


    [ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

    4.3 Embedded C Pre-compiler

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

    For more details, see The User Manual of RubatoDB Embedded C Precompiler. (This document, however, may not be available in some of districutions.)


    [ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

    4.4 The C Language Interface

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

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


    [ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

    4.4.1 Tutorial for the C Interface

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

    The interface focuses on two central data types:

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

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

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

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

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

    Here is what the code to call 4.4.2 sql_connection looks like:

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

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

    Here is what the code to call 4.4.4 sql_execution looks like:

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

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

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

    Here is what the code to call 4.4.5 sql_selection looks like:

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

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

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

    The output of the above program is given below.

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

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

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

    The following program demonstrate how to upload one lob into a table.
     
    /******************************************************************************************
     *  A simple program to demonstrate how to use lobs_operation to
     *  upload an images into a table created by the following statement:
     *
     *  It is assumed that the table using the following created statement
     *  
     *  create table photos (
            photo_id int, 
            title varchar(96), 
            image blob,
            primary key(photo_id)
        );
     *
     *  To Compile this program, use
     *   gcc tools/upload_one_pic.c -Iinclude -I/usr/local/BerkeleyDB.5.0/include/ -L./lib -llsql_c -lm -lssl -lpthread -lcrypto -ldl
     *
     */
    
    #include "lsql_c.h"
    
    #ifndef ROWSIZE
    #define ROWSIZE 1024
    #endif
    
    /*
     *  to specify the default connection variables, 
     *  and the file name
     */
    static char image_file[ROWSIZE]= "sand.jpg";
    static char dbname[ROWSIZE] = "database";
    static char username[ROWSIZE] = "system";
    static char password[ROWSIZE] = "manager";
    static int port = 8000;
    
    /*
     *  the main function
     */
    int main ( int argc, char *argv[] ) {
      int  index,fd, ret;
      char command[ROWSIZE], response[ROWSIZE];  
      char *loblocator;
      char hostname[ROWSIZE];
    
      /*
       *  to get the default hostname
       */
      gethostname(hostname, ROWSIZE);
    
      /*
       *  to connect to the database server with the given parameters
       */
      if ((fd=sql_connection(hostname,port,dbname,username,password))<0) {
        printf("the connection failed\n");   
        exit(0);
      }
    
      /*
       *  to request a lob locator for uploading a lob record.
       */
      sprintf(command, "request loblocator");
      if ( (ret = sql_execution(fd, command, response, ROWSIZE )) < 0 ) {
        printf("the execution failed\n");
        return(-1);
      }
      if ( response[0] != '1' ) {
        printf("Error:  %s\n", &(response[1]));
        exit(0);
      }
    
      /*
       *  to upload one lob using put_one_lob
       */
      loblocator = response+8;
      ret = upload_one_lob(fd,image_file,NULL,loblocator,0);
      if (ret<0) {
        printf("upload_one_lob(%s) failed for %s\n",loblocator,strerror(errno));
        return ret;
      }
    
         
      /*
       *  to insert a tuple into the newly created table
       */
      //  sprintf(command,"insert into photos values(%d,'Windows','%s')",1000,loblocator);
      sprintf(command,"insert into photos values(%d,'Sangs','%s')",2000,loblocator);
      if ( (ret = sql_execution(fd, command, response, ROWSIZE )) < 0 ) {
        printf("the execution failed\n");
        return(-1);
      }
      else if ( response[0] != '1' )
        printf("Error:  %s\n", &(response[1]));
    }
    /******************************************************************************************/
    
    

    The following is a more sophisticated example to upload a number of lobs into a table using multiple concurrent processes.

     
    /*
     *  A simple program to demonstrate how to use lobs_operation
     *  to upload a number of images into a table created by the 
     *  following statement:
     *
     *  It is assumed that the table using the following created statement
     *  
     *  create table photos (
            photo_id int, 
            title varchar(96), 
            image blob,
            primary key(photo_id)
        );
     *
     *  To Compile this program, use
     *     gcc upload_lobs.c liblsql_c.a -I$LOGICSQL/include  -lm -lssl
     *
     *  Ussage:  upload_lobs -n100 -s4
     *    this will created 4 concurrent users, each of which will upload
     *    100 images into the tablw.
     *
     *  Enter upload_lobs -h  for detailed instructions.
     *
     *        
     */
    #include <unistd.h>
    #include <errno.h>
    #include <pthread.h>
    #include <time.h>
    #include <sys/types.h>
    #include <stdio.h>
    #include <getopt.h>
    #include "lsql_c.h"
    
    #define ROWSIZE 1024
    
    /*
     *  to specify the default connection variables, 
     *  and the file name
     */
    static char image_file[ROWSIZE]= "id.jpg";
    static char dbname[ROWSIZE] = "database";
    static char username[ROWSIZE] = "system";
    static char password[ROWSIZE] = "manager";
    static int port = 8000;
    static int no_lobs = 1;
    static int no_threads = 1;
    
    
    /*
     *  the main function
     */
    int main ( int argc, char *argv[] ) {
      int  pid, thread_id;
      char hostname[ROWSIZE];
      long start_time, end_time;
      float timing;
    
    
      start_time = time(NULL);
    
      /*
       *  to get the default hostname
       */
      gethostname(hostname, ROWSIZE);
    
      /*
       *  to process the command line options
       */
      command_line_option(argc,argv);
    
      /* 
       *  start multi-processes, one for each concurrent user 
       */
      for (thread_id = 0; thread_id < no_threads; thread_id++ ) {
        if ( (pid = fork() ) < 0 )
          exit(1);
        else
          if ( pid == 0 ) {
    	upload_lobs_with_one_process(thread_id,no_lobs,hostname,port,dbname,username,password);
          }
      }
    
      if ( waitpid(pid, NULL, 0) < 0 )
        printf("waitpid failed\n");
    
      end_time = time(NULL);
      timing = end_time - start_time;
    
      printf("      %d images have been uploaded in %.2f second(s)\n",no_lobs * no_threads,timing); 
    }
    
    /*
     *  to upload the specified lobs into the table with one process
     *
     *  Parameters:
     *     thread_id:  the process id, used to determine a unique photo_id
     *       no_lobs:  the numbers of lobs to be uploaded
     *    hostname,port,dbname,username,password:  the five connecting paramters    
     *
     *  
     */
    int upload_lobs_with_one_process(int thread_id, 
    				 int no_lobs,
    				 char *hostname,
    				 int  port,
    				 char *dbname,
    				 char *username,
    				 char *password) {
      int  index,fd, ret;
      char command[ROWSIZE], response[ROWSIZE];  
      char *loblocator;
    
      /*
       *  to connect to the database server with the given parameters
       */
      if ((fd=sql_connection(hostname,port,dbname,username,password))<0) {
        printf("the connection failed\n");   
        return(-1);
      }
    
      for ( index = 1; index <= no_lobs; index++ ) {
        upload_one_image(fd,thread_id * no_lobs + index, image_file);
      }
    
      /*
       *  to close the connection
       */
      close_connection( fd );
    
      exit(0);
    }
    
    /*
     *   to upload one image to the table, with the given photo_id
     *   Paramters:
     *          fd:  the socket number of the server
     *    photo_id:  the unique id for the lob
     *   image_file: the name of the file that contains the image.
     *
     *   This function demonstrates how to upload a lob into the table in
     *   the following steps
     *    (1)  request a unique lob-locator from the server for the new lob,using
     *         an (extended) SQL statement
     *            REQUEST LOBLOCATOR;
     *         note that the loblocator starts at the 8th of the response
     *    (2)  upload the lob record from the file using a function
     *         specified in the C-interface
     *           upload_one_lob_with_file(fd,image_file,loblocator,0)
     *         (or from memory using upload_one_lob )
     *    (3)  insert or update the row containing the lob using 
     *         a regular SQL statement, say
     *           insert into photos values(%d,'test','%s')",photo_id,loblocator)
     *   
     *    
     */
    int  upload_one_image(int fd,char *photo_id,char *image_file) {
      int ret;
      char command[ROWSIZE], response[ROWSIZE];  
      char *loblocator;
    
      /*
       *  to request a lob locator for uploading a lob record.
       */
      sprintf(command, "request loblocator");
      if ( (ret = sql_execution(fd, command, response, ROWSIZE )) < 0 ) {
        printf("the execution failed\n");
        return(-1);
      }
      if ( response[0] != '1' ) {
        printf("Error:  %s\n", &(response[1]));
        return -1;
      }
    
      /*
       *  to upload one lob using put_one_lob
       */
      loblocator = response+8;
      ret = upload_one_lob(fd,image_file,NULL,loblocator,0);
      if (ret<0) {
        printf("upload_one_lob( %s ) failed: %s\n",loblocator,strerror(errno));
        return ret;
      }
    
      /*
       *  to insert a tuple into the newly created table
       */
      sprintf(command,"insert into photos values(%d,'test','%s')",photo_id,loblocator);
      if ( (ret = sql_execution(fd, command, response, ROWSIZE )) < 0 ) {
        printf("the execution failed\n");
        return(-1);
      }
      else if ( response[0] != '1' )
        printf("Error:  %s\n", &(response[1]));
      
    }
    
    
    /*
     *  to process the command line options.
     */
    int  command_line_option(int argc, char *argv[]) {
      int choice;
    
       while ((choice = getopt(argc, argv,"u:d:s:p:n:h")) != EOF ) {
         switch( choice ) {
         case 'u':
           strcpy(username, optarg);
           strcpy(password, optarg);
           break;
         case 'd':
           strcpy(password, optarg);
           break;
         case 'p':
           port = atoi(optarg);
           break;
         case 'n':
           no_lobs = atoi(optarg);
           break;
         case 's':
           no_threads = atoi(optarg);
           break;
         case 'h':
           tpctest_help();
           exit(0);
           break;
         default:
           tpctest_help();
           exit(0);
           break;
         }
       }
    }
    
    /*
     * display help info
     */
    int tpctest_help() {
    
       printf("\n\nUsage:  upload_lobs [-options]\n\n where options include\n
      -m <host_name>      specify the host name with default as  localhost\n
      -p <port_number>    specify the port number with default as 8000\n
      -u <user_name>      specify the user name and default is tpcone\n
      -d <password>       specify the password and default is the same as  user name\n
      -n <no. lobs>       specify the number of lobs to be inserted with  default \n
      -s <no. users>      specify the number of concurrent users\n
      -h                  print this message\n");
       return 0;
    }
    

    The following sample program uses a deprecated C-interface API, 4.4.7 put_one_lob to upload lobs.

     
    /*
     *  A simple program to demonstrate how to use lobs_operation to 
     *    (1) create a table with a column of BLOB to store pictures, and
     *    (2) then to upload an image to the table.
     *
     *  Use the following to compile:
     *        gcc lobs.c liblsql_c.a  -Iinclude
     *  here, we assume all the files needed are in the current directory.
     *  Further, an image file named id.jpg is also in the current directory.
     *
     */
    #include <sys/types.h>
    #include <stdio.h>
    #include <getopt.h>
    #include "lsql_c.h"
    
    #define ROWSIZE 1024
    
    int main ( int argc, char *argv[] ) {
    
      int ret, index, fd, port, len;
      char command[ROWSIZE], response[ROWSIZE], hostname[ROWSIZE];  
      char dbname[ROWSIZE], username[ROWSIZE], password[ROWSIZE];
      char buf[ROWSIZE], cell[ROWSIZE], lob_locator[ROWSIZE];
      char *ptr;
    
      RSET *result_set;
    
      strcpy( hostname, "localhost");
      strcpy( dbname, "database");
      strcpy( username, "system");
      strcpy( password, "manager");
    
      port = 8000;  
      ptr = buf;
    
      /*
       *  to connect to the database server with the given parameters
       */
      if ((fd=sql_connection(hostname,port,dbname,username,password))<0) {
         printf("the connection failed\n");   
         return(-1);
      }
    
      /*
       *  use sql_execution to create a table
       */
      strcpy( command, 
      "create table photos (photo_id int, title varchar(96), image blob,constraint photos_primary_key_constraint primary key(photo_id) )");
    
      if ( (ret = sql_execution(fd, command, response, ROWSIZE )) < 0 ) {
        printf("the execution failed\n");
        return(-1);
      }
      else {
        if ( response[0] == '1' ) 
          printf("response: %s\n", &(response[1]));
        else {
          printf("Error:  %s\n", &(response[1]));
        }
      }
    
      /*
       *  to insert a tuple into the newly created table
       */
      strcpy( command, "insert into photos values( 1234, 'test', 'empty_blob')");
      if ( (ret = sql_execution(fd, command, response, ROWSIZE )) < 0 ) {
      printf("the execution failed\n");
         return(-1);
      }
      else {
        if ( response[0] == '1' )
          printf("response: %s\n", &(response[1]));
        else 
          printf("Error:  %s\n", &(response[1]));
      }
         
      /*
       * to update the empty_blob with a lob value stored in a local file
       * with name "id.jpg"
       */
      put_one_lob(fd,"photos","image","where photo_id = 1234","id.jpg",0);
    
      /*
       *  to close the connection
       */
      close_connection( fd );
    }
    

    The following example demonstrates how to use the function get_one_lob to retrieve a list of lobs from a table.

     
    /**************************************************************************************************************
     *   A simple program to demonstrate how to use get_one_lob to retrieve lobs 
     *   from the LogicSQL database. It may also be used to test the lob operations.
     *
     *   The program starts multiple processes, each of which will first connect to
     *   the database, then obtains a list of lob_locators using a select statement, and
     *   finally retrieves a list of lobs using the function get_one_lob and stores
     *   the lobs in to files using the lob_locator as their file names.
     *
     *   The program assume that the database contains a table created by the following
     *   statement: 
     *       create table pictures (
     *            photo_id integer, 
     *            image blob,
     *            constraint pic_pk primary key(photo_id) 
     *      );
     *   and that the table has been populated by a list of blob images.
     *
     *   The program uses functions specified in liblsql_c.a and Berkeley DB, which can be compiled by
     *
     *   gcc tools/get_lobs.c -Iinclude -I/usr/local/BerkeleyDB.5.0/include/ -L./lib -llsql_c -lm -lssl -lpthread -lcrypto -ldl
     *
     */
    #include <sys/stat.h>
    #include "lsql_c.h"
    
    #ifndef ROWSIZE
    #define ROWSIZE 1024
    #endif
    
    #ifndef BUFSIZE
    #define BUFSIZE 65536
    #endif
    
    int main ( int argc, char *argv[] ) {
      int index,pid,port,no_queries;
      char command[ROWSIZE],hostname[ROWSIZE],dbname[ROWSIZE],username[ROWSIZE],password[ROWSIZE];
      
      /*
       *  to determine the number of processes with the default as 1
       */
      if (argc < 2)
        no_queries = 1;
      else
        no_queries = atoi(argv[1]);
    
      /*
       *  to set the database parameters
       */
      strcpy(hostname, "localhost");
      strcpy(dbname,   "database");
      strcpy(username, "system");
      strcpy(password, "manager");
      strcpy(command,  "select image from photos");
      port = 8000;  
    
      /* start multi-processes, one for each concurrent user */
      one_access(hostname,port,dbname,username,password,command);
    }
    
    /*
     *   to query a table and get all the lobs from it.
     */
    int one_access(char *hostname,int port,char *dbname,char *username,char *password,char *command) {
      int ret, index, fd, len,fd_out;
      char buff[ROWSIZE], cell[ROWSIZE], lob_locator[ROWSIZE],file_name[ROWSIZE];
      char *table_name, *ptr;
      DBR Lob;
      RSET *result_set;
    
      ptr = &(buff[0]);
    
      /*
       *  to connect to the database server with the given parameters
       */
      if ((fd=sql_connection(hostname,port,dbname,username,password))<0) 
        return(-1);
    
      /*
       *  to initialize the result_set
       */
      rset_create(&result_set);
    
      /*
       *  to get the result set of the query which is just the list of all lob_locators
       *  specified in the query command.
       */
      if ((ret = sql_selection(fd,command,result_set))<0) {
        close_connection(fd);
        return -1;
      }
    
      /*
       *  to get the table name from the command which the string following "from " in command.
       */
      table_name = command;
      while (strncmp(table_name,"from ",5)!=0) 
        table_name++;
      table_name+=5;
    
      /*
       *   get all lobs from the result_set
       */
      len = result_set->get_next(result_set,&ptr);
      while ( len > 0 ) {
    
        /*
         *  Obtain the lob_locator from the retrieved result set.
         */
        strncpy(lob_locator, ptr,len );
        lob_locator[len] = 0;
    
        /*
         *  The function is used to retrieve one lob from table_name with the given lob_locator.
         *  Note that the retrieved BLOB record is stored in the DBR lob.
         */
        ret = get_one_lob(fd,table_name,lob_locator,&Lob,1);
        if (ret<0) {
          len = result_set->get_next( result_set, &ptr );
          continue;
        }
        
        /*
         *  to write the lob into a file with the same name as the lob_locator
         */
        sprintf(file_name,"%s.jpg",lob_locator);
        fd_out = open(file_name, O_CREAT|O_WRONLY, S_IRWXU );
        write(fd_out,Lob.data,Lob.size);
        close(fd_out);
    
        len = result_set->get_next( result_set, &ptr );
      }
    
      /*
       *  to close the connection
       */
      close_connection( fd );
      return 0;
    }
    /***************************************************************************************************************/
    


    [ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

    4.4.2 sql_connection

    #include <lsql_c.h>

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

    Description

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

    Parameters

    Errors

    HOST_NOT_FOUND, NO_FD_ERROR, FAIL_TO_SEND, FAIL_TO_RECEIVE, AUTHORIZATION_FAILED


    [ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

    4.4.3 close_connection

    #include <lsql_c.h>

    int close_connection( int fd );

    Description

    This function closes the current connection through the given fd to the RubatoDB server. The function returns the length of the response received on success and an error code on failure.

    Note that this function sends a disconneciton command to the RubatoDB server and it neither waits for the response string nor close the specified fd.

    Parameters

    Note that fd must be the descriptor returned by 4.4.2 sql_connection.

    Errors

    HOST_NOT_FOUND, NO_FD_ERROR, FAIL_TO_SEND, FAIL_TO_RECEIVE, AUTHORIZATION_FAILED


    [ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

    4.4.4 sql_execution

    #include <lsql_c.h>

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

    Description

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

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

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

    Parameters


    [ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

    4.4.5 sql_selection

    #include <lsql_c.h>

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

    Description

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

    Parameters


    [ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

    4.4.6 upload_one_lob

    #include <lsql_c.h>

    int
    upload_one_lob(int fd,void *location,int size,char *lob_locator,int flags)

    Description

    This function is used by a client program to upload a lob record into a RubatoDB server, with the given size and lob-locator.

    The function must be used as an atomic operation. The function returns the number of bytes send to the client on success and an error code on failure.

    Parameters

    See Examples of using lobs_operation for details about how to use this function.


    [ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

    4.4.7 put_one_lob

    #include <lsql_c.h>

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

    NOTE This is a deprecated function, that has been replaced by 4.4.6 upload_one_lob.

    Description

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

    UPDATE table SET column = new_lob WHERE condition.

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

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

    Parameters

    Current Status Only the FILE_TO_LOB mode is implemented and thus file must be the name of a local file, and flags must be 0.

    Because of the size of lob values and the time for access, this function should be implemented with a child process. We also need to consider the concurrency control of multiple accesses to the lob values.


    [ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

    4.4.8 get_one_lob

    #include <lsql_c.h>

    long
    get_one_lob(int fd, char *table, char *lob_locator,DBR *lob,int flags)

    Description

    The function is used to retrieve the lob value pointed by the lob locator from the given table. The function returns the size of the lob value if the execution succeeds, and -1 if it fails.

    Parameters


    [ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

    4.4.9 RSET

    Description

    RSET is the struct designed to store all the information of the answer to a selection query.

     
    typedef struct {
    
      int degree;
      int no_tuples;
      int crt;          
    
      DBR buf;
    
      char *columns;
      char *types;
      char *table_name;
      char *error;
    } RSET;
    


    [ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

    4.4.10 rset_create

    #include <lsql_c.h>

    int
    rset_create( RSET **result_set );

    Description

    RSET is the struct designed to store all the information of the answer to a selection query. A result set of RSET cannot be used unless all the buffers in it have been created.

    This function will create a RSET result_set and allocates memory for the buffers in the result_set. You must use this function to initialize a result set of RSET before it can be actually used to store answers.

    Parameters


    [ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

    4.4.11 rset_display

    #include <lsql_c.h>

    int
    rset_display( RSET *result_set );

    Description

    This function is used to display the given result_set on screen. It is mainly used for interactive accesses to RubatoDB, like SQL*FACE, but not very useful for development of application programs. Parameters


    [ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

    4.4.12 RSET->clear


    [ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

    4.4.13 RSET->release


    [ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

    4.4.14 RSET->get_degree


    [ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

    4.4.15 RSET->get_no_tuples


    [ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

    4.4.16 RSET->get_next


    [ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

    4.4.17 RSET->get_previous


    [ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

    4.4.18 RSET->get_component


    [ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

    4.4.19 RSET->get_column


    [ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

    4.4.20 RSET->get_type


    [ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

    4.4.21 RSET->get_error


    [ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

    4.4.22 RSET->reset


    [ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

    4.5 MySQL Interface

    RubatoDB is planing to adapt the MySQL protocol and thus to use all the applicaiton development tools of MySQL. Currently, it provides an additional monitoring socket to enable applicaiton programs to use the MySQL tools to access RubatoDB.

    Assume the monitoring socket is set to be 8010. Then the socket 8020 will then be used to accept all requests using the MYSQL communicaiton protocol.

    We show how to use mysql -- the MySQL command-line tool, to access the RubatoDB as follows. Note that we assume that the tool has been installed in your Linux system.


    [ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

    5. Development Tools

    5.1 DBA Tools  
    5.2 SQL*Face  
    5.3 Loader  


    [ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

    5.1 DBA Tools

    RubatoDB's enterprise manager is a graphic tool for managing the RubatoDB database servers. It is Java-based and thus can be used in various platforms.

    The manager can be used to

    Please read The User Manual of RubatoDB Enterprise Manager to learn how to use this wonderful tool.


    [ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

    5.2 SQL*Face

    The SQL*Face program is an interactive program that enables its users to execute SQL commands to store and retrieve data in RubatoDB. Through SQL*Face, one can

    Guideline to SQL*FACE

    A brief description of SQL*Face is given below. For more details, please read the The User Manual of RubatoDB's SQL*Face.

    Installation of SQL*Face

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

    After the 3.1 Installation of RubatoDB, the binary code for Linux and that for Windows are located at $LOGICSQL/bin, where $LOGICSQL is the installation directory for RubatoDB.

    How to Start SQL*Face

    To use SQL*Face to access a RubatoDB database, you need RubatoDB server specific information, including the host name (or the IP address) of the server, the database name, the port number of the socket that listens to client requests, a user name and the password.

    To run SQL*Face, you may just enter, in your computer system (we assume that your search PATH includes the directory containing sqlface.

    Linux:> sqlface

    You will then be prompted to enter the database host name, socket number, database name, user name, and password, as follows:

     
    Linux:> sqlface  
     
    Please Enter the followings. ( Hit return if its the default in ( )  
      host name (local host):       
          port number (8000): 
    database name (database):      
                   user name:      
                    password:     
      
    

    With proper information entered, you will be greeted with the following:

     
    Linux :> sqlface system/manager
    ===================================================================
     
               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>      
    

    SQL*Face can also be started by entering the following

    Linux:> sqlface user_name/password@database_name:server_host_name:port_number

    where the meaning of the parameters are self explained.

    SQL*Face Commands

    The SQL*Face can be used to interactively execute all the following SQL*Face commands, including all SQL commands:

    QUIT  Quit the SQL*Face
    HELP  Display a help screen
    START  Execute all the SQL commands in a file
    SWITCH  Switch to a different user account
    2.3 SQL Commands  all the SQL commands

    Enter any SQL*Face command ending with ';' after the prompt logic sql, and the result will be displayed.

    List of SQL*Face commands

    Examples

    Example 1 As a system manager, one needs to find all current users for the RubatoDB database installed in luscar.cs.ualbertra.ca with the port number 2000. We assume the password for system is still the default 'manager'.

     
    Linux:> sqlface system/managerluscar.cs.ualberta.ca:2000
     ===================================================================
     
                  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       $1$8+'\Uh|$eMi/VxbUbiH5S75zvghxu/    11         2005-03-17 19:58:04  
      system       $1$b5$5aWyZVAuiTWZXEmkNut9Q/         10         2005-03-17 19:58:03  
    
    LogicSQL> 
    

    Note that, as per SQL99 specification, all meta tables are owned by the schema system.definition_schem'. The default database name is 'database' and can be omitted.

    Example 2 A file with the name 'tpc_create.sql' contains all the SQL statements used to create all table schemas and indexes of a sample TPC-C database. The following illustrates how to execute all the commands in this file using SQL*Face. Note that the initial database creates a user with the user name 'tpcone' in the database with name 'tpc_db'.

     
    Linux:> sqlface tpcone/tpcone@tpc_db
     ==================================================================
    
               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> start tpc_create.sql;
    Answer:  DROP TABLE: the table dropped
     ...
    Answer:  CREATE TABLE: warehouse created
     ...
    

    Example 3 To find the list of all table names owned by 'tpcone', enter the following:

     
    Linux:> sqlface tpcone/tpcone@tpc_db
     =================================================================
    
               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 table_name from information_schema.tables;
    

     
      table_name
      --------------
      customercolumns            
      element_types      
      key_column_usage   
      schemata           
      table_constraints  
      table_privileges   
      tables      
      district
      history
      item
      new_order
      orders
      stock
      warehouse     
    

    Note that we assume the RubatoDB server is installed on the same computer system using the default port 8000; and the database name for 'tpcone' is 'tpc_db'.

    The result displayed shows that the schema owns three default views and all the tables in a TPC database.


    [ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

    5.3 Loader

    Loader is a powerful tool for quickly populating RubatoDB tables with data from external files of Comma-Separated Values (CSV). It has an efficient data parsing engine that puts little limitation on the format of the data in the datafile. Loader is invoked when you specify the Loader command.

    Loader is an integral feature of RubatoDB databases and must be invoked only when the database server is running.

    Key Features

    Loader can be used to do the following:

    A typical Loader session takes as input a control file, which controls the behavior of Loader, and one or more input datafiles. The output of Loader is an RubatoDB database, a log file, a bad file, and potentially, a discard file (to be implemented). An example of the flow of a Loader session is shown in the following figure.

    loader1

    (Note that the log/bad/discard files have yet to be implemented.)


    [ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

    5.3.1 Loader Control File

    A control file is used to control the behavior of Loader, and it contains the following information:

    More specifically, the control file may contain the following lines:

    ---------------------------------------------------- ---------------------------------------------------- -----------------------------------------------
    Key Word Sample Values Default value if optional
    ---------------------------------------------------- ---------------------------------------------------- -----------------------------------------------
    LOADER MODE INSERT INSERT
    FIRST LINE METADATA 0 Required,
    FIELD TERMINATED BY , ,
    TERMINATED BY NEWLINE 1 1
    TALBE_FILE catalog.schema.table_name Required
    ALL_COLS an integer Required
    CODE utf8 uf8
    col_1 details below Required, one for each column
    ---------------------------------------------------- ---------------------------------------------------- -----------------------------------------------

    The meanings and explanation of the control items are given below.

    A sample control file is given below:

     
    # configure loader to load data, including control symbol, data type,etc. 
    # any other comments 
    LOADER MODE                  INSERT
    FIELDS TERMINATED BY         , 
    TERMINATED BY NEWLINE        1 
    FIRST LINE METADATA          0 
    TABLE_FILE                   database.system.stock
    CODE                         utf8                 
    ALL_COLS                     8                
    col_1	smallint    2                    1
    col_2	int         4                    2
    col_3	int         4                    3
    col_4	varchar     24                   4
    col_5	date        '%Y-%m-%d %H:%M:%S'  5
    


    [ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

    5.3.2 Data File

    The data file contains the list of rows to be loaded into the database table and its format is specified by the control file. If FIRST LINE METADATA value is 1 then the first line is used to store some information of the meta data, and otherwise, all lines are data items.

    A sample data file as specified by the above control file is given below.

     
    1, 1, 38, pfjvksktnetiyjywbfvcftpa, 2014-06-30 12:23:43
    1, 2, 86, ozcqmjrjlklqjzkfrkohvjry, 2014-03-31 03:23:43
    1, 3, 73, ospssdlqfjuqvrvnhnsjzxen, 2014-02-28 15:23:03
    1, 4, 20, phuxedfadcknvgsctuoxdkcy, 1956-03-09 22:13:03
    


    [ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

    5.3.3 Usage of Loader

    Loader is contained in the standard distribution, and can be used after the installation of RubatoDB. It takes input from (1) an control file and (2) one or more input datafiles located in one directory; and then populate one table as specified in the control file.

    For example, the following

     
    Linux> loader -c data.ctl -i inputs -m 129.127.4.10
    
    will load all input data files stored in the directory inputs to the database server running at 129.127.4.10, according to the control file data.ctl at the local directory.

    Loader can be called with the following command line options
     
    Linux> loader -h
        Usage:  load [-options]
      -c <control_file_name>  specify the name of the control file
      -i <directory name>     specify the name of the directory containing all input data files
      -u <user name>          specify the user name, default system
      -w <password>           specify the password, default the same as the user name
      -d <database>           specify the database of the user, default database
      -m <ip address>         specify the IP address of the server, default localhost
      -p <port number>        specify the port number, default 8010
      -h                      print this message
    

    Note that


    [ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

    5.3.4 Sample Use of Loader

    In this section, a simple example is used to demo how to use Loader to populate a database table distributed over four grid nodes in a RubatoDB.

    Example 1

    Example 2

    Enjoy it! And any bug report is appreciated


    [ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

    6. Database Tests

    This chapter presents two different types of database tests to facilitate the users to understand the behavior and performance of RubatoDB.

    6.1 SQL Conform Tests  
    6.2 TPC-C Benchmark Tests  


    [ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

    6.1 SQL Conform Tests

    The SQL Conform Test is based on the SQL Test Suite (Version 6.0), which was developed jointly by the U.S. National Institute of Standards and Technology (NIST), National Computing Centre Limited (NCC) in the U.K, and Computer Logic R&D in Greece.

    The SQL Test Suite is used to validate commercial SQL products for conformance to ISO, ANSI, and FIPS SQL standards. The results of the validation service are listed in an on-line Validated Products List. The software for the SQL Test Suite can be downloaded from the Web pages of the NIST Software Diagnostics and Conformance Testing Division. To download this conformance testing software, go to: http://www.itl.nist.gov/div897/ctg/software.htm and select SQL.


    [ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

    6.2 TPC-C Benchmark Tests

    The TPC-C benchmark test is a comprehensive database benchmark test that continues to be a popular yardstick for comparing OLTP performance on various hardware and software configurations. (see http://www.tpc.org for details).

    This section outlines steps to conduct the TPC-C benchmark tests of RubatoDB on a collection of of commodity servers. For your convenience, the test programs for conducting the TPC-C benchmark tests on four servers are included in the current distribution.

    The test can be conducted in the following steps:

    1. Install RubatoDB on a collection of servers.
    2. Start RubatoDB on all servers.
    3. Create the initial TPC-C database,
    4. Conduct the test,
    5. Record and analyze the testing results.

    1. Install RubatoDB on four Linux servers

    We assume that the names and IP addresses of the four machines are luscar1, luscar2, luscar3, luscar4, and 129.12.25.190, 129.12.25.191, 129.12.25.192, 129.12.25.193 respectively. Among all four servers, luscar1 will be used as the main node with the access socket port as 8010.

    Please note that the current version of RubatoDB uses a prolog parser which is very slow and thus we shall use three extra grid nodes in the main node to speed up the parser stage. Therefore, we are going to start seven grid nodes on four servers in which four are regular nodes, and three are prolog parser nodes.

    We assume that all nodes use 10 sockets for the internal communication, the main node uses three groups of sockets, startign at 8000, 7000, and 6000 for three non-main nodes, and all the non-main nodes use 10 sockets, starting at 8000. The three prolog nodes will use socket ports 4500, 4200, and 4000 respectively.

    The installation of RubatoDB on these four servers with three prolog parser nodes is outline below.

    Start the RubatoDB on all servers A RubatoDB running on the collection of four servers starts by running the RubatoDB on each and every server, in the order as specified in the aforementioned grid_initial.sql.

    For example, the RubatoDB running on four servers must start sequentially, as shown Steps 15, 16, 17, and 18.

    2. Creation of the initial TPC database

    First, one has to create an initial database for the test. For your convenience, a script file has been included in the directory named $LOGICSQL/bin. Within this directory on the server with the IP 10.10.0.2, use SQL*Face as follows:

     
    Linux 0:> sqlface system/manager@:129.12.25.101:8010 < $HOME/rubatodb0/bin/tpc_setup.sql
    
    Linux 0:> sqlface system/manager@:129.12.25.101:8010
    
    LogicSQL> start ~/rubatodb0/bin/tpc_setup.sql  
    ... LogicSQL> q

    will create a catalog, named tpc_db, a user name tpcone, and all the necessary TPC-C tables distributed over the four servers.

    3. Populate the TPC-C database tables using the RubatoDB Loader

    4. Conduct the TPC-C Tests

    After the population of the initial TPC database, the testing can be initialized with the provided testing program named tpctest.

    The tpctest can be called with the following options:

     
    Linux0:> tpctest -h
    
    Usage:  tpctest [-options]
    
    where options include
      -m <host_name>      specify the host name with default as localhost
      -p <port_number>    specify the port number with default as 8000
      -u <user_name>      specify the user name and default is tpcone
      -d <password>       specify the password and default is the same as user name
      -s <warehouses id>  specify the first warehouse id with default is 1
      -e <warehouses id>  specify the last warehouse id with default is 1
      -c <no. clients>    specify the number of clients per warehouse, default is 1
      -t <no. transact>   specify the number of transactions, default is 1
      -l                  print out all the queries and answers, default is off
      -b <block factor>   the percentage of thinking time, default 100
      -n <network delay>  the network delay time, default 0
      -h                  print this message
    
    

    4. Analysis of Test Results

    The command set checkpoint will not only set the checkpoint but also record the test parameters in a system table system.definition_schema.checkpoint_record.

    One can use sqlface (or any other interface such as Java Interface using JDBC) to display all the tuples in the above table. For example, one can use select * from system.definition_schema.checkpoint_record under sqlface, as follows:

     
    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.checkpoint_record;
    
      chk_time               clients      transaction_id      roll_backs      committed     transaction_per_minute
      -------------------------------------------------------------------------------------------------------------
      2012-03-13 06:49:51    5000         2072544             229             406316         13549                     
      2012-03-13 07:19:51    5000         2475016             361             402280         13415                     
      2012-03-13 07:49:51    5000         2886215             48              411156         13706                     
      2012-03-13 08:19:51    5000         3297897             27              411653         13722                     
      2012-03-13 08:49:51    5000         3708504             18              410591         13686                     
      2012-03-13 09:19:51    5000         4119752             12              411232         13708                     
      2012-03-13 09:49:51    5000         4530687             20              410907         13697                     
    
    

    The above table shows that the test has 5000 concurrent clients with 500 warehouse. Therefore, the performance is 13700 transactions/minute while the rollback ratio is less than 1%.


    [ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

    Concept Index

    Jump to:   A   B   C   D   E   F   G   I   L   O   P   R   S   T   U  

    Index Entry Section

    A
    add into group2.3.67 ADD INTO GROUP
    aio cursor2.3.7 CREATE AIO CURSOR
    all_group2.2.10 ALL_GROUP
    ALTER TABLE2.3.2 ALTER TABLE

    B
    backup log status3.3.2 Server Configuration

    C
    CHR2.2.6 CHR
    CHR2.2.11 NVL
    close cursor2.3.5 CLOSE CURSOR
    commit2.3.6 COMMIT
    contains2.8.5 CONTAINS
    crash recovery2.4.4 RECOVER DATABASE
    create artificial intelligent Optimization schema2.3.8 CREATE AIO SCHEMA
    create certificate2.3.69 CREATE CERTIFICATE
    create database2.3.9 CREATE DATABASE
    create datatype2.3.10 CREATE DATATYPE
    create group2.3.70 CREATE GROUP
    create index2.3.11 CREATE INDEX
    create inverted index2.3.12 CREATE INVERTED INDEX
    create procedure2.3.13 CREATE PROCEDURE
    create role2.3.14 CREATE ROLE
    create schema2.3.15 CREATE SCHEMA
    create sequence2.3.16 CREATE SEQUENCE
    create table2.3.17 CREATE TABLE
    create trigger2.3.18 CREATE TRIGGER
    create user2.3.19 CREATE USER
    create view2.3.20 CREATE VIEW
    current_certificate2.2.8 CURRENT_CERTIFICATE
    current_group2.2.9 CURRENT_GROUP
    cursor2.3.22 DECLARE CURSOR
    cursor2.3.27 DROP CURSOR
    cursor_table2.8.4 CURSOR_TABLE

    D
    data area3.3.2 Server Configuration
    data cluster3.3.2 Server Configuration
    database logs3.3.2 Server Configuration
    Database Tests6. Database Tests
    Date Arithmetic2.2.3 Date Arithmetic
    DBA Tools5.1 DBA Tools
    DEALLOCATE2.3.21 DEALLOCATE
    delete2.3.26 DELETE
    Development Tools5. Development Tools
    discretionary security model2.6.1 discretionary security model
    drop certificate2.3.71 DROP CERTIFICATE
    drop database2.3.28 DROP DATABASE
    drop group2.3.72 DROP GROUP
    drop index2.3.29 DROP INDEX
    drop procedure2.3.30 DROP PROCEDURE
    drop role2.3.31 DROP ROLE
    drop schema2.3.32 DROP SCHEMA
    drop sequence2.3.33 DROP SEQUENCE
    drop table2.3.34 DROP TABLE
    drop trigger2.3.35 DROP TRIGGER
    drop user2.3.36 DROP USER
    drop view2.3.37 DROP VIEW

    E
    enable/disable constraint enforcement2.3.38 DISABLE/ENABLE CONSTRAINT ENFORCEMENT
    execute procedure2.3.39 EXECUTE PROCEDURE
    EXECUTE STATEMENT2.3.40 EXECUTE STATEMENT
    extended Unix security model2.6.5 extended Unix security model

    F
    fetch2.3.41 FETCH

    G
    grant privilege2.3.42 GRANT PRIVILEGE
    grant role2.3.43 GRANT ROLE
    grid initialization3.3.1 Grid Initialization

    I
    IDENTITY2.2.4 IDENTITY
    insert2.3.44 INSERT
    Installation Examples3.4.4 Installation Examples
    INSTR2.2.7 INSTR
    inverted cursor2.3.25 CREATE INVERTED CURSOR
    inverted index area3.3.2 Server Configuration
    inverted index cluster3.3.2 Server Configuration

    L
    load table2.3.46 LOAD TABLE
    lob cluster3.3.2 Server Configuration
    lobs area3.3.2 Server Configuration
    LOCK table2.3.45 LOCK TABLE
    logicsql3.4.2 Starting the RubatoDB server

    O
    OPEN cursor2.3.47 OPEN CURSOR
    Outer Joins2.3.58 SELECT and Subquery

    P
    PREPARE2.3.48 PREPARE

    R
    release certificate2.3.73 RELEASE CERTIFICATE
    release savepoint2.3.49 RELEASE SAVEPOINT
    remove from group2.3.74 REMOVE FROM GROUP
    REMOVE LOB2.3.50 REMOVE LOB
    REQUEST LOBLOCATOR2.3.51 REQUEST LOBLOCATOR
    request security certificate2.3.75 REQUEST CERTIFICATE
    revoke privilege2.3.52 REVOKE PRIVILEGE
    revoke role2.3.53 REVOKE ROLE
    rollback2.3.54 ROLLBACK
    RSET4.4.9 RSET

    S
    savepoint2.3.55 SAVEPOINT
    score2.8.5 CONTAINS
    server3.4.2 Starting the RubatoDB server
    server configuration3.3.2 Server Configuration
    server port3.3.2 Server Configuration
    server shut down3.4.3 Shutting down the RubatoDB server
    server startup failures3.4.2 Starting the RubatoDB server
    set autocommit2.3.59 SET AUTO_COMMIT
    set checkpoint2.4.1 SET CHECKPOINT
    set language2.3.56 SET LANGUAGE
    set role2.3.57 SET ROLE
    set session user2.3.60 SET SESSION USER
    set transaction2.3.61 SET TRANSACTION
    show statement2.3.62 SHOW STATEMENT
    shutdown2.4.5 SHUTDOWN
    Shutting down the RubatoDB server3.4.3 Shutting down the RubatoDB server
    SQL Conform Tests6.1 SQL Conform Tests
    sql load/dump2.3.63 SQL LOAD/DUMP
    Starting the RubatoDB server3.4.2 Starting the RubatoDB server
    statistics2.4.7 STATISTICS
    stored facts2.4.6 STORED FACTS
    system creation3.4.1 System Creation

    T
    to_char2.2.5 TO_CHAR
    TPC-C Benchmark Tests6.2 TPC-C Benchmark Tests
    Tutorial for the C Interface4.4.1 Tutorial for the C Interface

    U
    UNLOCK table2.3.64 UNLOCK TABLE
    update2.3.66 UPDATE
    use database2.3.65 USE DATABASE
    user account3.2 Server Runtime Environment

    Jump to:   A   B   C   D   E   F   G   I   L   O   P   R   S   T   U  


    [ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

    Bug Report and Contact Information

    Please report all the bugs at the following webpage How to Report bugs of RubatoDB


    [Top] [Contents] [Index] [ ? ]

    Footnotes

    (1)

    By $LOGICSQL we mean the installation directory.


    [Top] [Contents] [Index] [ ? ]

    Table of Contents


    [Top] [Contents] [Index] [ ? ]

    Short Table of Contents

    1. Introduction
    2. User's Guide
    3. Database Administrator's Guide
    4. Interfaces
    5. Development Tools
    6. Database Tests
    Concept Index
    Bug Report and Contact Information

    [Top] [Contents] [Index] [ ? ]

    About this document

    This document was generated by Li-Yan Yuan on August, 20 2014 using texi2html

    The buttons in the navigation panels have the following meaning:

    Button Name Go to From 1.2.3 go to
    [ < ] Back previous section in reading order 1.2.2
    [ > ] Forward next section in reading order 1.2.4
    [ << ] FastBack previous or up-and-previous section 1.1
    [ Up ] Up up section 1.2
    [ >> ] FastForward next or up-and-next section 1.3
    [Top] Top cover (top) of document  
    [Contents] Contents table of contents  
    [Index] Index concept index  
    [ ? ] About this page  

    where the Example assumes that the current position is at Subsubsection One-Two-Three of a document of the following structure:

    This document was generated by Li-Yan Yuan on August, 20 2014 using texi2html