Lemke IT Homepage

5 The Cego SQL language
Back to TOC

The Cego SQL query language enables the database user to perform any database requests via the client program or any of the supported APIs. Cego SQL is very close to SQL language standard as specified with SQL-92. This includes the query commands for inserting, selecting, updating and deleting tuples. The following chapter describes all provided SQL language features of the Cego database system. Each query type is introduced by a syntax description followed by an illustrating sample query.

For a better understanding of the samples, it is recommended to invoke the cgclt client program, connect to a running cego database system and execute the given code.

For a detailed description of the console client program, please refer to the section cgclt.

More advanced users can use one of the provided JDBC , DBD , C or C++ API.

5.1 Fundamentals

Before discussing any SQL statement, some prerequisites are explained.

5.1.1 Datatypes

Cego provides serveral datatypes which can be used as column type for any tables or views but also as in/out parameters for stored procedures.

Datatype	Meaning
int	Integer type
long	Long type ( 64 bit )
string(<dim>)	Variable string type of max len <dim>
datetime	Date and time values
bool	Boolean type
bigint(<dim>)	Arbitrary integer type of max len <dim>
float	machine dependent floating point type
double	machine dependent double point type
decimal(<dim>)	decimal value with scale <dim>
fixed(<dim>)	fixed decimal value with maximum length of 30 bytes and fixed scale <dim>
fixed(<dim>,<dim>)	fixed decimal value with given maximum length and fixed scale <dim>
smallint	small integer value ( 16 bit )
tinyint	tiny integer value ( 8 bit )
blob	binary large objects
clob	character large objects

5.1.2 Language Tokens

Each programming language consists of a number of tokens as the smallest units of the language. To give a full understanding of Cego SQL, we will first describe the available tokens now. These tokens are used later on for the description of the several query commands.

Token	Meaning	Regular Expression
<Id>	Any identifier used for tables, attributes, procedures and so on	[ a-zA-Z ] [ a-zA-Z0-9_* ]
<QualifiedId>	Any identifier used for tables, attributes, procedures and so on	[ a-zA-Z ] [ a-zA-Z0-9_* ] ( @ \| . ) [ a-zA-Z ] [ a-zA-Z0-9_* ]
<VarRef>	Reference to a procedure variable	: [ a-zA-Z ] [ a-zA-Z0-9_* ]
<IntVal>	Basic integer value	( 0\|[1-9][0-9*])
<StrVal>	Basic string value	' ... '
<FloatVal>	Floating point value	( 0\|[1-9][0-9])'.'[0-9]
<BoolVal>	Boolean value	( true\|false)
<Comparison>	Value comparison tokens	( =\|<\|>\|<=\|>=\|!=)

5.1.3 Base Productions

Based on the described token set, in the following are defined some productions which are used in the later defined syntax description tables.

Symbol	Production
<ObjSpec>	<Id> \| <QualifiedId>
<DataType>	int \| long \| string '(' <IntVal> ')' \| bigint '(' <IntVal> ')' \| decimal '(' <IntVal> ')' \| fixed '(' <IntVal> ')' \| fixed '(' <IntVal> ',' <IntVal> ')' \| datetime \| bool \| float \| double \| smallint \| tinyint \| blob \| clob
<Expr>	<Expr> ( + \| - \| \| ) <Term> \| <Term>
<Term>	<Term> ( * \| / ) <Factor> \| <Factor>
<Factor>	:<Id> \| <Constant> \| fetch <Id> into ( :<Id> , :<Id> ... ) \| <Attribute> \| <Function> \| ( <Expr> ) \| ( <SelectStmt> )
<Constant>	<StrVal> \| <IntVal> \| (long) <IntVal> \| (bigint) <IntVal> \| (smallint) <IntVal> \| (tinyint) <IntVal> \| <FloatVal> \| (fixed)<FloatVal> \| (double)<FloatVal> \| (decimal)<FloatVal> \| <BoolVal>

The detailed syntax of the <SelectStmt> production is described below

5.2 Creating a table

Table objects ( and all other database objects ) are created using the create command. Following the standard SQL, a schema description of the table is given to the create statement

<CreateStatement>	:=	create table <ObjSpec> '(' <Schema> ')' ;
<Schema>	:=	<Schema> ',' <AttrSpec>
<Schema>	:=	<AttrSpec>
<AttrSpec>	:=	[ primary ] <id> <DataType> [ <DefaultOption>] [ <NullOption> ]
<DefaultOption>	:=	default <Constant>
<NullOption>	:=	null \| not null

The schema definition specifies the type and name of the table columns. If the keyword primary is added, a primary index for the corresponding column set is created during data table creation.

Please note : primary key columns must be specified as not null

To illustrate the creation of a table, we will create a first table called mytab. The table schema consists of two attribute called a and b of type int and type string. Column a is used as primary key, so it is specified as not null. For the string type, we give a maximum size of 30 bytes.

CGCLT > create table mytab as ( primary a int not null, b string(30) );
Table mytab created
ok ( 0.002 s )

After creation the table can be described using the desc command.

CGCLT > desc table mytab;
+-----------+-----------+-----------+-----------+
| TABLEDESC | TABLEDESC | TABLEDESC | TABLEDESC |
|      ATTR |      TYPE |   DEFAULT |  NULLABLE |
+-----------+-----------+-----------+-----------+
|         a |       INT |      null |        no |
|         b |    STRING |      null |       yes |
+-----------+-----------+-----------+-----------+
ok ( 0.002 s )

Note, that if not specified, all attribute values are nullable at default. If not null is required, this must be specified at creation time using the option not null for the corresponding attribute.
Default values are used for table insert operations to set unspecified columns to appropriate values. The default extension is used for this purpose.

CGCLT > create table mytab2 as ( primary a int not null, b string(30) default 'Peter');
Table mytab2 created
ok ( 0.002 s )

Now the table looks like

CGCLT > desc table mytab;
+-----------+-----------+-----------+-----------+
| TABLEDESC | TABLEDESC | TABLEDESC | TABLEDESC |
|      ATTR |      TYPE |   DEFAULT |  NULLABLE |
+-----------+-----------+-----------+-----------+
|         a |       INT |      null |        no |
|         b |    STRING |     Peter |       yes |
+-----------+-----------+-----------+-----------+
ok ( 0.002 s )

5.3 Inserting into a table

If a table object has been created succesfully, it can be filled with data. This can be done using the insert command

<InsertStatement>	:=	insert into <ObjSpec> [ <InsertArgSpec> ] <InsertValueSpec> ;
<InsertArgSpec>	:=	'(' <InsertArgList> ')'
<InsertArgList>	:=	<InsertArgList> ',' <Id>
<InsertArgList>	:=	<Id>
<InsertValueSpec>	:=	' values (' <InsertValueList> ')'
<InsertValueSpec>	:=	<SelectStmt>
<InsertValueList>	:=	<InsertValueList> ',' <Expr>
<InsertValueList>	:=	<Expr>

We will insert a first tuple in our previous created table mytab. Since we have defined two attributes called a and b, we will specify two column values for the insert command in the full qualified manner.

CGCLT > insert into mytab ( a, b ) values ( 42, 'Hello world');
1 tuples inserted
ok ( 0.002 s )

Any expression values are also allowed for the insert argument value list. This might be look like the following

CGCLT > insert into mytab ( a, b ) values ( 3*3, 'Hello ' |  'world');
1 tuples inserted
ok ( 0.002 s )

The following select query shows the resulting tuple values

CGCLT > select * from mytab;
+-----------+-------------------------------+
|   mytab_1 |                       mytab_1 |
|         a |                             b |
+-----------+-------------------------------+
|         9 |                   hello world |
+-----------+-------------------------------+
ok ( 0.002 s )

5.4 Attribute value handling

Cego provides several attribute datatypes to store any user data. In the following, all provided datatypes are described in more detail.

5.4.1 Integer and long values

For numerical values in a range from -2^31 to 2^31, machine dependent integer values can be used in tables as type int. Depending on the machine architecture long values represent 32 or 64bit values in a range of -2^63 to 2^63. To create a table with any int or long values, you just specify the row type as int or long

CGCLT > create table mytab( a int, b long );
Table mytab created
ok ( 0.001 s )

Integer and long values are inserted as numerical values. You may specify this as

CGCLT > insert into mytab values ( 1, 2 );
1 tuples inserted
ok ( 0.001 s )

Please note that numerical values are parsed as integer values and casted internally as needed to long values. To specify long values explicit you have to use the cast operator

CGCLT > insert into mytab values ( 1, (long)234567826353934096 );
1 tuples inserted
ok ( 0.001 s )

5.4.2 String values

For string values of any length the string is used.

CGCLT > create table mytab( a string(10), b string(40) );
Table mytab created
ok ( 0.001 s )

String values are inserted as quated values. You may specify this as

CGCLT > insert into mytab values ( 'hello', 'world' );
1 tuples inserted
ok ( 0.001 s )

5.4.3 Boolean values

Boolean columns are defined using the bool datatype

CGCLT > create table mytab( a bool );
Table mytab created
ok ( 0.001 s )

Boolean values normally inserted as with true or false values. You may specify this as

CGCLT > insert into mytab values ( true );
1 tuples inserted
ok ( 0.001 s )
CGCLT > insert into mytab values ( false );
1 tuples inserted
ok ( 0.001 s )

Boolean values also can be given as string or numerical values. In this case, strings of value 'Y' or 'yes' are casted to a true value. Numerical values greater than zero also casted to a true value. All other values are casted to false

CGCLT > insert into mytab values ( 'Y' );
1 tuples inserted
ok ( 0.001 s )
CGCLT > insert into mytab values ( 'yes' );
1 tuples inserted
ok ( 0.001 s )
CGCLT > insert into mytab values ( 1 );
1 tuples inserted
ok ( 0.001 s )

5.4.4 Datetime values

In the Cego database system, one data type for any data, time and datetime values can be used. In fact, cego stores the value for any data or time entry in a system time struct value ( time_t ) and provides some powerful conversion functions for scanning and retrieving input and output. To create a table with a datatime value, you just specify the row type as datetime

CGCLT > create table mytab( a datetime );
Table mytab created
ok ( 0.001 s )

Now, any date and time values can be inserted using the scandate scanning function. This function expects two arguments. First, a format string is given according to the standard unix strftime format specification ( see the strftime man page for a detailed information about format string tokens ). As second, the date value is expected, which must fit the specified format.

CGCLT > insert into mytab values ( scandate('%d.%m.%Y %H:%M:%S %Z', '01.02.2007 2:23:00 CET'));
1 tuples inserted
ok ( 0.001 s )

A more comfortable way to insert datetime values are via string casts.

ws > insert into t1 values ( '14.07.2016 12:00:00');
1 tuples inserted
ok ( 0.000 s )

The supported datetime formats of the input string value can be customized in the database xml file. Please check section Date format handling for further details, how to customize datetime formats.

5.4.5 Arbitrary integer values

The bigint column type provides arbitray integer handling, which can be used for large integer number. With the type specification, you have to give the dimension for the maximum numbers to store. Please choose this value large enough, so you won't run out of value range.

CGCLT > create table mytab ( a bigint(20) );
Table mytab created
ok ( 0.001 s )

To avoid confusion with the native integer value, a type casting is expected for any data insert queries.

CGCLT > insert into mytab values ( 2345324533212 );
Mismatched datatype  in value list for argument 1
Query failed
ok ( 0.007 s )
CGCLT > insert into mytab values ( (bigint)2345324533212 );
1 tuples inserted
ok ( 0.001 s )

Values can be retrieved in a normal way with any select statement.

CGCLT > select * from mytab;
+---------------------+
|             mytab_1 |
|                   a |
+---------------------+
|       2345324533212 |
+---------------------+
ok ( 0.002 s )

5.4.6 Float values

To store any king of floating values into the Cego database, the datatype float could be used. The float type corresponds to the native float type of the operating system ( 4 byte ).

CGCLT > create table mytab ( a float );
Table mytab created
ok ( 0.001 s )

Floating type values require a floating point representation of the form <number>.<number>.

CGCLT > create table mytab( a float );
Table mytab created
ok ( 0.002 s )
CGCLT > insert into mytab values ( 24.956);
1 tuples inserted
ok ( 0.002 s )
CGCLT > select * from mytab;
+-----------------------------------------+
|                                 mytab_1 |
|                                       a |
+-----------------------------------------+
|                               24.955999 |
+-----------------------------------------+
ok ( 0.003 s )

5.4.7 Double values

For a larger precision, the double datatype can be used. double represents the operating system double datatype.

CGCLT > create table mytab ( a double );
Table mytab created
ok ( 0.001 s )

Double type values require a floating point representation of the form (double)<number>.<number>. Please note, that double types have to be casted to double to avoid confusion with float values.

CGCLT > create table mytab( a double );
Table mytab created
ok ( 0.002 s )
CGCLT > insert into mytab values ( (double)24.956);
1 tuples inserted
ok ( 0.002 s )
CGCLT > select * from mytab;
+----------------------------------------------+
|                                      mytab_1 |
|                                            a |
+----------------------------------------------+
|                                    24.956000 |
+----------------------------------------------+
ok ( 0.003 s )

5.4.8 Decimal values

The Cego decimal datatype stores arbitrary decimal values ( maximum is the given size ) to the database.

CGCLT > create table mytab( a decimal(3) );
Table mytab created
ok ( 0.002 s )
CGCLT > insert into mytab values ( (decimal)1.23);
1 tuples inserted
ok ( 0.002 s )
CGCLT > select * from mytab;
+-------------------------------+
|                       mytab_1 |
|                             a |
+-------------------------------+
|                          1.23 |
+-------------------------------+
ok ( 0.003 s )

5.4.9 Fixed values

The fixed datatype implements a special decimal type with a maximum length and a fixed precision. This might be useful to store currency values since there are no precision rounding effects on arithmetic operations. If just the precision is specified, the default maximum length is used for this attribute. The following sample illustrates the handling with fixed values.

CGCLT > create  table mytab( a fixed(10,2) );
Table mytab created
ok ( 0.020 s )
CGCLT > desc table mytab;
+-----------+-----------+-----------+-----------+-----------+-----------+
| TABLEDESC | TABLEDESC | TABLEDESC | TABLEDESC | TABLEDESC | TABLEDESC |
|      ATTR |      TYPE |    LENGTH |       DIM |   DEFAULT |  NULLABLE |
+-----------+-----------+-----------+-----------+-----------+-----------+
|         a |     fixed |        10 |         2 |      null |       yes |
+-----------+-----------+-----------+-----------+-----------+-----------+
1 tuples
ok ( 0.033 s )
CGCLT > insert into mytab values ( (fixed)24.95);
1 tuples inserted
ok ( 0.019 s )
CGCLT >  insert into mytab values ( 26.35);
1 tuples inserted
ok ( 0.018 s )
lit > select * from mytab;
+-----------+
|     mytab |
|         a |
+-----------+
|     24.95 |
|     26.35 |
+-----------+
2 tuples
ok ( 0.053 s )
CGCLT > insert into mytab values ( 26.35876);
1 tuples inserted
ok ( 0.019 s )
CGCLT > select * from mytab;
+-----------+
|     mytab |
|         a |
+-----------+
|     24.95 |
|     26.35 |
|     26.35 |
+-----------+
3 tuples
ok ( 0.028 s )

Casts from other datatypes to fixed are done implicitly. Also the required precision is corrected as seen in the samples above.

5.4.10 Small integer values

Smallint columns contain values for small integer values ( 2 byte ).

CGCLT > create table mytab( a smallint );
Table mytab created
ok ( 0.002 s )
CGCLT > insert into mytab values ( (smallint)15 );
1 tuples inserted
ok ( 0.002 s )
CGCLT > select * from mytab;
+---------+
| mytab_1 |
|       a |
+---------+
|      15 |
+---------+
ok ( 0.003 s )

5.4.11 Tiny integer values

Tinyint columns contain values for very small integer values ( 1 byte ).

CGCLT > create table mytab( a tinyint );
Table mytab created
ok ( 0.003 s )
CGCLT > desc table mytab;
+----------------------------------------+
| ObjectName : mytab                     |
| ObjectType : table                     |
+-------------+---------------+----------+
| Attribute   | Type          | Nullable |
+-------------+---------------+----------+
| a           |  tinyint      | y        |
+-------------+---------------+----------+
ok ( 0.002 s )
CGCLT > insert into mytab values ( (tinyint)5);
1 tuples inserted
ok ( 0.002 s )
CGCLT > select * from mytab;
+---------+
| mytab_1 |
|       a |
+---------+
|       5 |
+---------+
ok ( 0.003 s )

5.4.12 Lob values

Normally the size of a table value is restricted to the page size of the database. Lobs are stored in one or more dedicated data pages and in this sense, any kind of binary or character information can be stored. The stored data can just be accessed by using several provided lob functions, which is shown in the following samples.

CGCLT > create table mytab( a clob );
Table mytab created
ok ( 0.003 s )
CGCLT > desc table mytab;
+----------------------------------------------------+
| ObjectName : mytab                                 |
| ObjectType : table                                 |
+-------------+---------------+-----------+----------+
| Attribute   | Type          | Default   | Nullable |
+-------------+---------------+-----------+----------+
| a           | clob          | null      | y        |
+-------------+---------------+-----------+----------+
ok ( 0.001 s )

For any insert or update operation, clob values can be used in the same manner as string values. The database system takes care of an internal conversion.

CGCLT > insert into mytab values ( 'hello world' );
1 tuples inserted
ok ( 0.003 s )
CGCLT > select * from mytab;
+---------+
| mytab_1 |
|       a |
+---------+
|    [32] |
+---------+
ok ( 0.003 s )

To access the clob values, the internal clob function clob2str can be used. This function retrieves the stored clob value and converts it to a string. This clobsize function returns the byte length of the clob as a long value. For performance reasons, the database handles references to clob values in a way, that the same clob value can be referenced by several clob entries ( e.g. copied via an insert by select ). To indicate the number of references for a clob, the clobref function can be used.

CGCLT > select clob2str(a) from mytab;
+-------------------+
|              FUNC |
| clob2str(mytab.a) |
+-------------------+
|       hello world |
+-------------------+
1 tuples
ok ( 0.003 s )
CGCLT > select clobsize(a) from mytab;
+---------------------+
|                FUNC |
|   clobsize(mytab.a) |
+---------------------+
|                  12 |
+---------------------+
1 tuples
ok ( 0.000 s )
CGCLT > select clobref(a) from mytab;
+---------------------+
|                FUNC |
|    clobref(mytab.a) |
+---------------------+
|                   1 |
+---------------------+
1 tuples
ok ( 0.000 s )

Blob values are handled in a similar way, but the values can not be accessed directly via the cgclt program. For this, one of the provided C/C++/JDBC API should be used.

CGCLT > create table mytab( a blob );
Table mytab created
ok ( 0.003 s )
CGCLT > desc table mytab;
+----------------------------------------------------+
| ObjectName : mytab                                 |
| ObjectType : table                                 |
+-------------+---------------+-----------+----------+
| Attribute   | Type          | Default   | Nullable |
+-------------+---------------+-----------+----------+
| a           | blob          | null      | y        |
+-------------+---------------+-----------+----------+
ok ( 0.001 s )

Please note : You should not do any kind of blob reference operations manually

5.4.13 Insert by select

A convenient way to load existing table data into another table is using the insert-by-select statement. The result set of the corresponding select statement is stored into the table specified with the insert statement.

CGCLT > insert into mytargettab ( a, b ) select a, b from mysourcetab;
1 tuples inserted
ok ( 0.002 s )

Please note, that the table schema of the target table must match with the selection list given to the select statement or at least a datatype cast from source attribute type to the target attribute type must be possible.

5.4.14 Bulk insert

To improve performance, one insert statement can contain a bunch of tuples, which are then all added to the corresponding table.

CGCLT > insert into t1 values ( 1, 'alpha'), (2, 'beta'), (3, 'gamma');
3 tuples inserted
ok ( 0.000 s )

This way might be useful in case of large table loads, where the table import could be splitted in bulk insert statements of an appropriate size.

5.5 Selecting from a table

To retrieve data from one or more tables, the SQL select statement is used. Cego SQL supports select queries in a common way as specified with the following grammar definition.

<SelectStatement>	:=	select <Selection> from <TableSource> [ <WhereClause> ] [ <GroupClause> ] [ <OrderingClause> ] [ <RowLimitOpt> ] [ <UnionAllOptOpt> ];
<Selection>	:=	( [ distinct ] <SelectionList> \| * )
<SelectionList>	:=	<SelectItem> , <SelectItem>, ...
<SelectItem>	:=	<Expr> [ as <Alias> ]
<Expr>	:=	( <Factor> (+\|-\|*\|/\|\|) <Expr> \| <Factor> )
<Factor>	:=	( <Attribute> \| <Constant> \| <Function> \| ( <Expr> ) \| ( <SelectStatement> ) \| <Aggregation> )
<Function>	:=	<string or arithmetic function>
<Aggregation>	:=	( *count() \| (count\|sum\|avg\|min\|max**) (<Expr>) )
<TableSource>	:=	where <Condition> )
<TableSource>	:=	<TableList>
<TableSource>	:=	<JoinSpecList>
<JoinSpecList>	:=	<JoinSpecList JoinSpec>
<JoinSpecList>	:=	<Table> <JoinSpec>
<JoinSpec>	:=	<InnerJoin> <Table> on <QueryCondition>
<JoinSpec>	:=	left outer join <Table> on <QueryCondition>
<JoinSpec>	:=	right outer join <Table> on <QueryCondition>
<InnerJoin>	:=	( inner join \| join )
<TableList>	:=	<Table> , <TableList>
<TableList>	:=	<Table>
<Table>	:=	<ObjSpec>
<Table>	:=	<ObjSpec> <Id>
<WhereClause>	:=	where <Condition> )
<Condition>	:=	( <Condition> ( and \| or ) <Predicate> \| <Predicate%gt; )
<Predicate>	:=	( exists\|not exists) ( <SelectStatement> )
<Predicate>	:=	<Expr> ( in\|not in) ( <SelectStatement> )
<Predicate>	:=	<Expr> ( in\|not in) ( <ExprList> )
<ExprList>	:=	<Expr>, <ExprList>
<ExprList>	:=	<Expr>
<Predicate>	:=	<Expr> <Comparison> ( <SelectStatement> )
<Predicate>	:=	<Expr> <Comparison> <Expr>
<Predicate>	:=	<Expr> between <Expr> and <Expr>
<GroupClause>	:=	group by <GroupList> <HavingClause>
<GroupList>	:=	<GroupList> , <Attribute>
<GroupList>	:=	<Attribute>
<OrderingClause>	:=	order by <OrderingList>
<OrderingList>	:=	<OrderingList> , <Expr> <OrderingOpt>
<OrderingList>	:=	<Expr> <OrderingOpt>
<OrderingOpt>	:=	[ (asc\|desc) ]
<HavingClause>	:=	having <Expr> <Comparison> <Expr>
<RowLimitOpt>	:=	rowlimit <IntVal>
<UnionAllOpt>	:=	[ UNION ALL <SelectStatement> ]
<UnionAllOpt>	:=	ION ALL <SelectStatement>
<Attribute>	:=	( <Id> \| <Id> . <Id> )
<Constant>	:=	( [ (bigint) ] <IntVal> \| <StrVal> \| [ (fixed) \| (decimal) ]<FloatVal> \| <BoolVal> \| null \| sysdate )

In the following, some sample select statements are given. We use our previously created table mytab for this.
It is out of the scope of this user guide, to give a full SQL language description. Just the basics and Cego specific features are discussed here. The star selection selects all attributes of the corresponding table. Without any where condition, all tuples of the table are selected.

CGCLT > select * from mytab;
+-----------+-------------------------------+
|           |                               |
|         a |                             b |
+-----------+-------------------------------+
|        33 |                           xxx |
|        44 |                           foo |
|        66 |                   hello world |
+-----------+-------------------------------+
ok ( 0.014 s )

To be more specific, we might select the column a from mytab and list the tuple, where the value of a is 44.

CGCLT > select a from mytab where a = 44;
+-----------+
|           |
|         a |
+-----------+
|        44 |
+-----------+
ok ( 0.002 s )

Please note: If using attribute conditions in the where clause, it might be useful to create an appropriate index for the corresponding attribute. How to create and use table indexes are discussed later on in the section Using Indexes To create in index object on attribute afor the table above, just execute the query create index i1 on mytab(a);

5.5.1 Selection

In the selection part of a query, the output colums are specified, which should retrieved and calculated. In this sense, the selection is a list of function calls, expressions, terms, factors or just simple attributes. Following the syntax definition listed above, expressions can be combined and nested as needed.

CGCLT > select a, b, c, (a+b+c)/3 from t2;
+-----------+-----------+-----------+---------------+
|           |           |           |               |
|         a |         b |         c | ((a+(b+c))/3) |
+-----------+-----------+-----------+---------------+
|         1 |         2 |         3 |             2 |
|         5 |         5 |         5 |             5 |
+-----------+-----------+-----------+---------------+
ok ( 0.015 s )

For string attributes the concatenation operator | is also allowed to concatenate the corresponding attribute values.

CGCLT > select a, b, a | ' ' | b from t3;
+----------------+----------------+----------------+
|                |                |                |
|              a |              b |        a|' '|b |
+----------------+----------------+----------------+
|          hello |          world |    hello world |
+----------------+----------------+----------------+
ok ( 1.808 s )

If unique, attributes can be specified without table identifier. Otherwise, the name or alias of the table must be given.

CGCLT > select ta.a, tb.a from t2 ta, t2 tb;
+-----------+-----------+
|           |           |
|         a |         a |
+-----------+-----------+
|         1 |         1 |
|         1 |         5 |
|         5 |         1 |
|         5 |         5 |
+-----------+-----------+
ok ( 0.010 s )

In the sample above, the alias names ta and tb are used to identify the attribute a for the selfjoined table t2 clearly.

5.5.2 Alias

In the previous query, the attribute a is indicated two times, one for table ta and one for table tb. In some cases, it is useful to distinguish attributes or rename the resulting columns for any other reasons. For this, an aliasing of the column names is needed.

CGCLT > select ta.a as A1, tb.a as A2 from t2 ta, t2 tb;
+-----------+-----------+
|           |           |
|        A1 |        A2 |
+-----------+-----------+
|         1 |         1 |
|         1 |         5 |
|         5 |         1 |
|         5 |         5 |
+-----------+-----------+
ok ( 0.010 s )

5.5.3 Joins

Any data table can be correlated with any other data table ( or itself ) using table joins. In fact, table joins are the cartesian product of the two or more tables. Normally, the database user is just interested in a those result tuples of the join, which meet an appropirate join condition. For this, attribute conditions for the tables are added in the where clause.
In the following, we will look at some samples for simple join constructions.
Assume the following two table are created

CGCLT > select * from t1;
+-----------+-------------------------------+
|           |                               |
|         a |                             b |
+-----------+-------------------------------+
|        14 |                         hello |
|        15 |                         ciao  |
|        22 |                       goodbye |
+-----------+-------------------------------+
ok ( 0.003 s )
CGCLT > select * from t2;
+-----------+-------------------------------+
|           |                               |
|         a |                             b |
+-----------+-------------------------------+
|        22 |                           guy |
|        23 |                           you |
|        14 |                         world |
+-----------+-------------------------------+
ok ( 0.002 s )

Now we want to join t1 with t2 by using attribute a for the join condition.

CGCLT >  select * from t1 ta, t2 tb where ta.a = tb.a;
+-----------+-------------------------------+-----------+-------------------------------+
|           |                               |           |                               |
|         a |                             b |         a |                             b |
+-----------+-------------------------------+-----------+-------------------------------+
|        14 |                         hello |        14 |                         world |
|        22 |                       goodbye |        22 |                           guy |
+-----------+-------------------------------+-----------+-------------------------------+
ok ( 0.009 s )

To be more restrictive, furthermore we can filter out just those lines where a has a value of 14.

CGCLT >  select * from t1 ta, t2 tb where ta.a = tb.a and ta.a = 14;
+-----------+-------------------------------+-----------+-------------------------------+
|           |                               |           |                               |
|         a |                             b |         a |                             b |
+-----------+-------------------------------+-----------+-------------------------------+
|        14 |                         hello |        14 |                         world |
+-----------+-------------------------------+-----------+-------------------------------+
ok ( 0.013 s )

5.5.4 Conditions

As seen in the previous section, conditions can be used to reduce the tuple result set to the required values. In general a condition consists of a set of predicates combined with logical and/or operators. A predicate can be a comparison with a constant value or with another attribute value, as also seen in the samples above.

Furthermore predicates can contain null values or like comparisons shown in the following sample

CGCLT > select * from otab1 where a like 'l%';
+-------------------------------+-----------+
|                       otab1_1 |   otab1_1 |
|                             a |         b |
+-------------------------------+-----------+
|                          lida |         4 |
|                          lida |         6 |
|                         lemke |        12 |
|                         lemke |        22 |
|                         lemke |         8 |
+-------------------------------+-----------+
ok ( 0.005 s )

All tuples are listed where the value of attribute a starts with the letter l.

5.5.5 Distinct

To distinct operator is used to just select distinct result tuples. The distinction relates to all referenced attributes which occur in the selection part of the quers. For plain queries this is obvious. Consider the content of the following table

CGCLT > select a, b from t1;
+-----------+-----------+
|        t1 |        t1 |
|         a |         b |
+-----------+-----------+
|         1 |     alpha |
|         2 |      beta |
|         2 |      beta |
|         3 |      beta |
+-----------+-----------+
4 tuples	
ok ( 0.005 s )

If the distinct operator is added, this results in the following

CGCLT > select distinct a, b from t1;
+-----------+-----------+
|        t1 |        t1 |
|         a |         b |
+-----------+-----------+
|         1 |     alpha |
|         2 |      beta |
|         3 |      beta |
+-----------+-----------+
3 tuples
ok ( 0.000 s )

For aggregation queries, also all distinct tuples are treated

CGCLT > select distinct count(a) from t1;
+-----------------------+
|                  AGGR |
|  distinct count(t1.a) |
+-----------------------+
|                     3 |
+-----------------------+
1 tuples
ok ( 0.000 s )
CGCLT > select distinct count(b) from t1;
+-----------------------+
|                  AGGR |
|  distinct count(t1.b) |
+-----------------------+
|                     2 |
+-----------------------+
1 tuples
ok ( 0.000 s )

Please note, that for distinct aggreation, dedicated queries must be used for each aggreation attribute. The following query can lead to an undesirable result, since the distinction of all references attributes is treated.

CGCLT > select distinct count(a), count(b) from t1;
+-----------------------+-----------------------+
|                  AGGR |                  AGGR |
|  distinct count(t1.a) |  distinct count(t1.b) |
+-----------------------+-----------------------+
|                     3 |                     3 |
+-----------------------+-----------------------+
1 tuples
ok ( 0.000 s )

Since for grouping queries, all result tuples are distinct because of the grouping attributes, the distinct option has no effect

CGCLT > select distinct a, count(*) from t1 group by a;
+-----------+--------------------+
|        t1 |               AGGR |
|         a |  distinct count(*) |
+-----------+--------------------+
|         1 |                  1 |
|         2 |                  2 |
|         3 |                  1 |
+-----------+--------------------+
3 tuples
ok ( 0.003 s )

5.5.6 Ordering

If a ordered listing of the result tuples is required, a ordering condition can be given with the select statement.

CGCLT > select * from t1 order by a;
+-----------+-------------------------------+
|      t1_1 |                          t1_1 |
|         a |                             b |
+-----------+-------------------------------+
|        12 |                         erwin |
|        13 |                           ida |
|        14 |                           udo |
|        30 |                           aze |
|        30 |                           aaa |
|        30 |                         anton |
+-----------+-------------------------------+
ok ( 0.007 s )

If more ordering attributes are given, ordering is evaluated for all attribute values

CGCLT > select * from t1 order by a, b;
+-----------+-------------------------------+
|      t1_1 |                          t1_1 |
|         a |                             b |
+-----------+-------------------------------+
|        12 |                         erwin |
|        13 |                           ida |
|        14 |                           udo |
|        30 |                           aaa |
|        30 |                         anton |
|        30 |                           aze |
+-----------+-------------------------------+
ok ( 0.007 s )

5.5.7 Grouping

The grouping clause enables the user to aggregate tuple values grouped by the given grouping attribute. Any kind of aggregation is allowed for grouping.

CGCLT > select a, count(*) from t1 group by a order by a;
+-----------+-----------+
|      t1_1 |           |
|         a |  COUNT(*) |
+-----------+-----------+
|        12 |         1 |
|        13 |         1 |
|        14 |         1 |
|        30 |         3 |
+-----------+-----------+
ok ( 0.008 s )

5.5.8 Having

In addition with the grouping clause, a having condition can be used for further filtering.

CGCLT > select a, count(*) from t1 group by a order by a having count(*) = 1;
+-----------+-----------+
|      t1_1 |           |
|         a |  COUNT(*) |
+-----------+-----------+
|        12 |         1 |
|        13 |         1 |
|        14 |         1 |
+-----------+-----------+
ok ( 0.008 s )

5.5.9 Nested query

Subselects may be useful for some special query purpuses. In general, it is recommended to use table joins instead of nested queries for performance reasons.
Select those suppliers, which deliver no material

CGCLT > select sname from supplier sup where snr not in ( select mat.snr from material mat);

Select those suppliers, which can deliver all ordered material

CGCLT > select sname from supplier sup where not exists
( select * from orders ord where not exists 
( select * from material mat where mat.snr = sup.snr and mat.mname = ord.mname ));

Subselects can also be used in the selection of a query. The following sample lists all material and the corresponding supplier using a nested select in the selection part.

CGCLT > select mname, ( select sname from supplier sup where sup.snr = mat.snr ) from material mat;

5.5.10 Inner Join

As a more modern form of a table join, an inner join statement, can be used to create the cartesian product of two tables. The condition of the join is specified in the on clause as shown below.

CGCLT > select * from t1 inner join t2 on t1.a = t2.a;

The tuple result is the same as using a traditional where condition t1.a = t2.a.

5.5.11 Outer Join

Outer joins are used to select all tuples of one of the joined tables, no matter if the join condition matches or not. The non matching columns of joined table are filled with null values. Either left outer or right outer joins may be specified, depending on what table should be selected completely. Both join methods are described in more detail in the following.

5.5.11.1 Left outer join

In case of a left outer join, all row values of the left table object are expanded. If the condition does not fit any row of the right table object, null values are indicated.

CGCLT > select * from t1 left outer join t2 on t1.a = t2.c;
+-----------+-------------------------------+-----------+-------------------------------+
|      t1_1 |                          t1_1 |      t2_2 |                          t2_2 |
|         a |                             b |         c |                             d |
+-----------+-------------------------------+-----------+-------------------------------+
|         1 |                           xxx |         1 |                           xxx |
|         4 |                           yyy |      null |                          null |
+-----------+-------------------------------+-----------+-------------------------------+
ok ( 0.008 s )

5.5.11.2 Right outer join

In case of a right outer join, all row values of the right table object are expanded. If the condition does not fit any row of the left table object, null values are indicated.

CGCLT > select * from t1 tx right outer join t2 ty on tx.a = ty.c;
+-----------+-------------------------------+-----------+-------------------------------+
|        tx |                            tx |        ty |                            ty |
|         a |                             b |         c |                             d |
+-----------+-------------------------------+-----------+-------------------------------+
|         1 |                           xxx |         1 |                           xxx |
|      null |                          null |         2 |                           vvv |
|      null |                          null |         3 |                           zzz |
+-----------+-------------------------------+-----------+-------------------------------+
ok ( 0.008 s )

5.5.12 Union all

To concatenate the rows of two or more select statements, a union all clause can be used. As a prerequisite, the resulting output schema of all select statements must be compatible. This might be achieved by natural or by appropriate type casts and aliases.

First, we give a sample of compatible schemas

CGCLT > desc table t1;
+-----------+-----------+-----------+-----------+-----------+-----------+
| TABLEDESC | TABLEDESC | TABLEDESC | TABLEDESC | TABLEDESC | TABLEDESC |
|      ATTR |      TYPE |    LENGTH |       DIM |   DEFAULT |  NULLABLE |
+-----------+-----------+-----------+-----------+-----------+-----------+
|         a |       int |         4 |         0 |      null |       yes |
|         b |    string |        10 |         0 |      null |       yes |
+-----------+-----------+-----------+-----------+-----------+-----------+
2 tuples
ok ( 0.000 s )
CGCLT > desc table t2;
+-----------+-----------+-----------+-----------+-----------+-----------+
| TABLEDESC | TABLEDESC | TABLEDESC | TABLEDESC | TABLEDESC | TABLEDESC |
|      ATTR |      TYPE |    LENGTH |       DIM |   DEFAULT |  NULLABLE |
+-----------+-----------+-----------+-----------+-----------+-----------+
|         c |       int |         4 |         0 |      null |       yes |
|         d |    string |        30 |         0 |      null |       yes |
+-----------+-----------+-----------+-----------+-----------+-----------+
2 tuples
ok ( 0.000 s )
CGCLT > select a, b from t1;
+-----------+-----------+
|        t1 |        t1 |
|         a |         b |
+-----------+-----------+
|         1 |     alpha |
|         2 |      beta |
|         1 |     alpha |
|         2 |      beta |
|         3 |     gamma |
+-----------+-----------+
5 tuples
ok ( 0.000 s )
CGCLT > select a, b from t2;
+-----------+-------------------------------+
|        t2 |                            t2 |
|         a |                             b |
+-----------+-------------------------------+
|        42 |                           xxx |
|        43 |                           yyy |
|        44 |                           zzz |
+-----------+-------------------------------+
3 tuples
ok ( 0.000 s )
CGCLT > select a, b from t1 union all select a, b from t2;
+-----------+-----------+
|        t1 |        t1 |
|         a |         b |
+-----------+-----------+
|         1 |     alpha |
|         2 |      beta |
|         1 |     alpha |
|         2 |      beta |
|         3 |     gamma |
|        42 |       xxx |
|        43 |       yyy |
|        44 |       zzz |
+-----------+-----------+
8 tuples
ok ( 0.000 s )

If the schemas do not match, casts and aliases are required

CGCLT > desc table t3;
+-----------+-----------+-----------+-----------+-----------+-----------+
| TABLEDESC | TABLEDESC | TABLEDESC | TABLEDESC | TABLEDESC | TABLEDESC |
|      ATTR |      TYPE |    LENGTH |       DIM |   DEFAULT |  NULLABLE |
+-----------+-----------+-----------+-----------+-----------+-----------+
|         x |    string |        10 |         0 |      null |       yes |
|         y |    string |        20 |         0 |      null |       yes |
+-----------+-----------+-----------+-----------+-----------+-----------+
2 tuples
ok ( 0.000 s )
litdev > select a, b from t1 union all select str2int(x) as a, y as b from t3;
+-----------+-----------+
|        t1 |        t1 |
|         a |         b |
+-----------+-----------+
|         1 |     alpha |
|         2 |      beta |
|         1 |     alpha |
|         2 |      beta |
|         3 |     gamma |
|         1 |       XXX |
|         2 |       FFF |
|         3 |       UUU |
+-----------+-----------+
8 tuples
ok ( 0.000 s )

5.5.13 Numeric functions

There are several numeric functions available in cego

Function	Description
round(<expr1>[,<expr2>])	Rounding the expression in the first argument to the precision in the second argument. The precision is zero, if the second argument is missing
mod(<expr1>, <expr2>)	The function returns on a integer based arithmetic the remainder of expr1 divided by expr2
div(<expr1>, <expr2>)	The function returns on a integer based arithemtic the division of expr1 and expr2
ldiv(<expr1>, <expr2>)	The function returns on a long based arithmetic the division of expr1 and expr2
lmod(<expr1>, <expr2>)	The function returns on a long based arithmetic the remainder of expr1 divided expr2
power(<expr1>, <expr2>)	The function returns expr1 raised to the expr2 power based on integer values
bitand(<expr1>,<expr2>)	Bitwise and operation on integer values
bitor(<expr1>,<expr2>)	Bitwise or operation on integer values
bitxor(<expr1>,<expr2>)	Bitwise xor operation on integer values
randint(<expr>)	Generates a randon integer value between 0 and the maxium value given with the expression ( integer value expected )
int2asc(<expr>)	Converts the integer value to its corresponding ascii character ( integer between 0 and 127 expected )

5.5.14 String functions

For string manipulation, cego provides a number of functions with can be used for any statement. Each of the function described in the following expects one or more expression arguments, which can be any valid expression as already seen.
As a function return value either a result string or an integer value is returned to the caller.

Function	Description
trim(<expr1>,<expr2>)	Removes any leading and trailing characters specified in expr2 from the given expression expr1 and returns the resulting string
ltrim(<expr1>,<expr2>)	Removes any leading characters specified in expr2 from the given expression expr1 and returns the resulting string
rtrim(<expr1>,<expr2>)	Removes any trailing characters specified in expr2 from the given expression expr1 and returns the resulting string
lower(<expr>)	Converts the given expression to lower case characters
upper(<expr>)	Converts the given expression to upper case characters
left(<expr1>,<expr2>)	Returns expr2 characters from the left end of the string evaluated with expr1. Please note that expr2 is expected to be an integer value
right (<expr1>,<expr2>)	Returns expr2 characters from the right end of the string evaluated with expr1. Please note that expr2 is expected to be an integer value
substr(<expr1>,<expr2>)	Returns the substring of string expr1 at position expr2 to the end
substr(<expr1>,<expr2>,<expr3>)	Returns the substring of string expr1 at position expr2and length expr3
getpos(<expr1>,<expr2>)	Returns the position of substring expr2 in given expression expr1
getpos(<expr1>,<expr2>,<expr3>)	Returns the position of substring expr2 in given expression expr1 starting search at position given with expr3
getpos(<expr1>,<expr2>,<expr3>,<expr4>)	Returns the position of expr4 occurence of substring expr2 in given expression expr1 starting search at position given with expr3
length(<expr>)	Returns the length of expr
replace(<expr1>,<expr2>,<expr3>)	For the given expression expr1 replace the substring given with expr1 substring expr2 with substring expr3 and returns the resulting string
str2int(<expr>)	Converts the given expression to an integer value
str2long(<expr>)	Converts the given expression to a long value
randstr(<expr>)	Generates a random string of length given with the expression value ( integer value expected )
asc2int(<expr>)	Converts the character in the given expression to the corresponding ascii integer value ( string with len 1 expected )

We give a sample for the function substr

CGCLT > create table mytab( a string(30) );
Table mytab created
ok ( 0.003 s )
CGCLT > insert into mytab values ( 'This is a string');
1 tuples inserted
ok ( 0.002 s )
CGCLT > select substr(a,6,2) from mytab;
+-----------------------+
|                       |
| substr(mytab_1.a,6,2) |
+-----------------------+
|                    is |
+-----------------------+
ok ( 0.006 s )

5.5.15 Date functions

The following date functions are available

Function	Description
newdate(<expr1> , <expr2> ... )	Converts the given numeric arguments to a datetime value Arguments are year, month, day, hour, minute and second. If any of the parameters are ommittted, the minimum value is set up. Samples : newdate(2017,8) results in 01.08.2017 00:00:00 newdate(2017,8, 12, 10) results in 12.08.2017 10:00:00
date2str(<expr1> , <expr2> )	Converts the the given datetime value in expr1 to a formatted string. The format is specified with expr2
scandate(<expr1> , <expr2> )	The scandate function parses the string given with <expr2> and the date format given wit <expr1>. The date format string corresponds to the format tring of the standard strptime c library function
date2long(<expr>)	Converts the the given datetime value in the expr argument to a long value. The long value represents the datetime value in seconds since january, 01, 1970 ( Unix time convention )
long2date(<expr>)	Converts the the given long value to a datetime value

To retrieve a date or time value, the date2str can be used to print the date value in an appropriate way. The date2int and int2date functions can be used to perform any arithmetic date operations.

CGCLT > select date2str(a, '%H:%M') from mytab;
+-----------------------------+
|                             |
| date2str(mytab_1.a,'%H:%M') |
+-----------------------------+
|                       02:23 |
+-----------------------------+
ok ( 0.003 s )

The format string for the second parameter corresponds to the standard unix date format string as specified with the strftime library function.
The date2int function provides a conversion of the datetime value to an integer value

CGCLT > select date2int(a) from mytab;
+---------------------+
|                FUNC |
| date2int(mytab_1.a) |
+---------------------+
|          1170292980 |
+---------------------+
ok ( 0.003 s )
CGCLT > select int2date(date2int(a) + 60 * 60 * 24) from mytab;
+----------------------------------------------+
|                                         FUNC |
| int2date(date2int(mytab_1.a) + 60 * 60 * 24) |
+----------------------------------------------+
|                          02.02.2007 02:23:00 |
+----------------------------------------------+
ok ( 0.003 s )

Datetime functions can be combined with any other functions and can also be used for evaluation purposes
To select all entries for the year 2010 and beyond, you can use the following query

CGCLT > select a from mytab where str2int(date2str(a, '%Y')) >= 2010;
+-------------------------------+
|                         mytab |
|                             a |
+-------------------------------+
|           29.10.2010 12:00:42 |
+-------------------------------+
ok ( 0.002 s )

5.5.16 Blob functions

The following blob functions are available

Function	Description
blobsize(<expr1> )	Retrieves the size for the given blob Expression must return a valid blob value
blobref(<expr1> )	Retrieves the number of references for the given blob Expression must return a valid blob value

5.5.17 clob functions

The following blob functions are available

Function	Description
clobsize(<expr1> )	Retrieves the size for the given clob Expression must return a valid clob value
clobref(<expr1> )	Retrieves the number of clob references for the given clob Expression must return a valid clob value
clob2str(<expr1> )	Retrieves the coresponding string value for the given clob Expression must return a valid clob value

5.5.18 Arithmetic functions

Actually, there are no arithmetic functions implemented.

Function	Description
N/A (<N/A> )	N/A

5.5.19 Case when clause

The case-when-clause is used in a selection query to implement a mapping from a condition to any expression values. The clause is used in a similar way as known from common programming languages. to distinguish several cases, the when condition may appear several times.

CGCLT > select case when a = 1 then 'alpha' when a = 2 then 'beta' else 'gamma' end from t1;
+---------------------+
|      CASE CONDITION |
|       case when ... |
+---------------------+
|               alpha |
|               alpha |
|                beta |
|                beta |
|               gamma |
+---------------------+
ok ( 0.003 s )

5.5.20 Execution plan

For query performance analysis, it may be useful to see what strategy the database uses to retrieve the selected tuples. A execution plan of each select statement can be retrieved without executing the given query. Before the select statement, just put the keyword plan and cego will inform you about the retrieve strategy. You will get information about what way cego will use to scan through the table ( full table scan or index based ) and the order, the tables are read.
In the first sample, we print the execution plan of a simple select statement for table mytab. Since no condition is specified and all tuples of the table should be retrieved, cego decides to use a full table scan in the target table.

CGCLT > plan select * from mytab;
Execution Plan :

+------------------------------------------------------------------------------+
| PLAN                                                                         |
| DESCRIPTION                                                                  |
+------------------------------------------------------------------------------+
| Execution plan                                                               |
| ---------------                                                              |
|   Joining mytab(mytab_1) with full table scan                                |
+------------------------------------------------------------------------------+
ok ( 0.001 s )

As second, the query is performed on table t1 with an available index on attribute a. For this query, select will use this index to retrieve all tuples, matching the index condition.

CGCLT > plan select * from t1 where a = 42;
+------------------------------------------------------------------------------+
| PLAN                                                                         |
| DESCRIPTION                                                                  |
+------------------------------------------------------------------------------+
| Execution plan                                                               |
| ---------------                                                              |
|   Joining t1(t1_1) with full index trace on t1.a = 42                        |
+------------------------------------------------------------------------------+
ok ( 0.002 s )

Also queries including views and sub query are analyzed in detail. The following sample illustrates the execution plan for a more complex query

CGCLT > plan select a from t1, v1 where t1.a = t1.a and v1.b not in ( select b from t3 where a = 47 and exists ( select b from t3 ) ) and exists ( select a from t1 );
+------------------------------------------------------------------------------+
| PLAN                                                                         |
| DESCRIPTION                                                                  |
+------------------------------------------------------------------------------+
| Execution plan                                                               |
| ---------------                                                              |
|   Joining table t1 (t1) with full table scan with no condition               |
|   Joining view v1 (v1)                                                       |
|     Execution plan for v1                                                    |
|     ----------------------                                                   |
|       Joining table t1 (t1) with full table scan using condition t1.b = 'XX' |
|   Execution plan for subquery                                                |
|   ----------------------------                                               |
|     Joining table t3 (t3) with full table scan using condition t3.a = 47     |
|     Execution plan for subquery                                              |
|     ----------------------------                                             |
|       Joining table t3 (t3) with full table scan with no condition           |
|   Execution plan for subquery                                                |
|   ----------------------------                                               |
|     Joining table t1 (t1) with full table scan with no condition             |
+------------------------------------------------------------------------------+
ok ( 0.001 s )

5.5.21 System tables

System tables contain information about tableset objects. A system table cannot be modified but information about the tableset objects can be retrieved. System table are identified by the $ prefix. Here is an example

CGCLT > select * from $table;
+---------------------+
|            $table_1 |
|                NAME |
+---------------------+
|                tab1 |
|                tab2 |
|                tab3 |
+---------------------+
ok ( 0.002 s )

The available system objects can be retrieved with the list command

CGCLT > list sysobj;
+---------------------------------------------------+
|                                            SysObj |
|                                              Name |
+---------------------------------------------------+
|                                             table |
|                                         procedure |
|                                              view |
|                                             index |
|                                             btree |
|                                               key |
|                                            bustat |
+---------------------------------------------------+
7 tuples
ok ( 0.073 s )

5.6 Updating a table

To change the values of some tuples, the update statement is used. The changing columns have to be specified with the new values and the changing rows have to qualified by the where clause.

<UpdateStatement>	:=	update <ObjSpec> [ <Alias> ] set <UpdateList&t; [ <WhereClause> ] [ <ReturnOpt> ] ;
<UpdateList>	:=	<UpdateList> , <Assignment>
<UpdateList>	:=	<Assignment>
<Assignment>	:=	<Id> = <Expr>
<ReturnOpt>	:=	return <ReturnVarList>
<ReturnVarList>	:=	<ReturnVarList> , <ReturnVar>
<ReturnVarList>	:=	<ReturnVar>
<ReturnVar>	:=	<VarRef> = <Expr>

The sample below updates the table mytab in a kind, that all values of column a are set to value 222 and all values of column b are set to value 'foo' where column a matches the value of 33

CGCLT > update mytab set a = 222, b = 'foo' where a = 33;
1 tuples updated
ok ( 0.002 s )

You also can use select queries in the update assignment with references to the current updated tuple.

CGCLT > select * from t1;
+-----------+---------------------+
|      t1_1 |                t1_1 |
|         a |                   b |
+-----------+---------------------+
|         1 |               alpha |
|         2 |                beta |
|         3 |               gamma |
+-----------+---------------------+
ok ( 0.002 s )
CGCLT > select * from t2;
+-----------+---------------------+
|      t2_1 |                t2_1 |
|         a |                   b |
+-----------+---------------------+
|         1 |                 xxx |
|         2 |                 xxx |
|         3 |                 xxx |
+-----------+---------------------+
ok ( 0.002 s )
CGCLT > update t2 set b = ( select b from t1 where a = t2.a );
3 tuples updated
ok ( 0.003 s )
CGCLT > select * from t2;
+-----------+---------------------+
|      t2_1 |                t2_1 |
|         a |                   b |
+-----------+---------------------+
|         1 |               alpha |
|         2 |                beta |
|         3 |               gamma |
+-----------+---------------------+
ok ( 0.002 s )

With the return option, updated values from the corresponding table may be returned to the calling session. The return option can be used for updating multiple rows, where just the last row result would be stored to the given procedure variables. By using return on first just the first matching tuple is updated and the result is returned. If no tuple was found, the result tuple just contains null values. The values are stored in the given variables as shown in the following

CGCLT > select * from t1;
+-----------+---------------------+
|        t1 |                  t1 |
|         a |                   b |
+-----------+---------------------+
|         1 |               Alpha |
|         2 |                Beta |
|         3 |               Gamma |
|         4 |               Gamma |
|         5 |               Gamma |
+-----------+---------------------+
5 tuples
ok ( 0.000 s )
CGCLT > set a=0;
Value assigned
ok ( 0.000 s )
CGCLT > update t1 set a = a + 1 where b = 'Gamma' return on first :a = a;
1 tuples updated
ok ( 0.000 s )
CGCLT > print :a;
4
ok ( 0.000 s )
CGCLT > select * from t1;
+-----------+---------------------+
|        t1 |                  t1 |
|         a |                   b |
+-----------+---------------------+
|         1 |               Alpha |
|         2 |                Beta |
|         4 |               Gamma |
|         5 |               Gamma |
|         4 |               Gamma |
+-----------+---------------------+
5 tuples
ok ( 0.000 s )
CGCLT > CGCLT > update t1 set a = a + 1 where b = 'Gamma' return  :a = a;
3 tuples updated
ok ( 0.000 s )
CGCLT > print :a;
5
ok ( 0.000 s )
CGCLT > select * from t1;
+-----------+---------------------+
|        t1 |                  t1 |
|         a |                   b |
+-----------+---------------------+
|         1 |               Alpha |
|         2 |                Beta |
|         5 |               Gamma |
|         6 |               Gamma |
|         5 |               Gamma |
+-----------+---------------------+
5 tuples
ok ( 0.000 s )

Return on updates might be useful if the updated value should be retrieved in one transaction. For example, this could be used for individual counters or update state tables.

5.7 Deleting from a table

To delete tuples, which have been inserted into a table, you have to use the delete statement as seen below

delete from <ObjSpec> [ <Alias> ] [ <WhereClause> ] ;

The defintion of the where clause is quite the same as seen for the select statement. If no where clause is given, all tuples of the table are deleted.

CGCLT > delete from mytab where a = 44;
1 tuples deleted
ok ( 0.045 s )

5.8 Truncating a table

If the content of a table table is not needed anymore, it can be deleted in a fast way using the truncate statement.

CGCLT > truncate table mytab;
Table mytab truncated
ok ( 0.001 s )

5.9 Dropping a table

If a table is not needed anymore, it can be removed with the drop statement. If the table is dropped, all corresponding index and key entries are also dropped by the drop statement.

CGCLT > drop table mytab;
Table mytab dropped
ok ( 0.001 s )

To avoid an error if the corresponding table does not exists anymore, for the drop operation an if-exists option can be added.

CGCLT > drop if exists table mytab_not_exist;
Table does not exist
ok ( 0.001 s )

5.10 Altering a table

Sometimes, it is required to change the structure of a datatable. Any table columns or table contraints should be added or removed. For this, the alter statement can be used.

The following syntax is defined for this

<AlterStatement>	:=	alter table <ObjSpec> <AlterList> ;
<AlterList>	:=	<AlterOption> , <AlterList>
<AlterList>	:=	<AlterOption>
<AlterOption>	:=	drop column <Id>
<AlterOption>	:=	add column <ColumnDesc>
<AlterOption>	:=	modify column <ColumnDesc>
<AlterOption>	:=	rename column <Id> to <Id>
<RenameStatement>	:=	rename <ObjType> <ObjSpec> to <Id> ;
<ObjType>	:=	table \| index \| btree \| key \| procedure \| view \| check

The following sample illustrates the altering of a table

CGCLT > create table mytab( a string(30) );
Table mytab created
ok ( 0.003 s )
CGCLT > insert into mytab values ( 'This is a string');
1 tuples inserted
ok ( 0.002 s )
CGCLT > alter table mytab add column b int;
Table altered
ok ( 0.002 s )
CGCLT > select * from mytab;
+-------------------------------+-----------+
|                       mytab_1 |   mytab_1 |
|                             a |         b |
+-------------------------------+-----------+
|              This is a string |      null |
+-------------------------------+-----------+
ok ( 0.004 s )

Please note, that you can just add nullable columns, since all existing row values for the new added column are set to null.

Table columns are dropped using the drop clause for the alter statement.

CGCLT > alter table mytab drop column b;
Table altered
ok ( 0.001 s )
CGCLT > select * from mytab;
+-------------------------------+
|                       mytab_1 |
|                             a |
+-------------------------------+
|              This is a string |
+-------------------------------+
ok ( 0.003 s )

Object renaming can be done using the rename query command.

CGCLT > rename table mytab to mytab2;
Object renamed
ok ( 0.002 s )

5.11 Using indexes

For constraint and performance reasons, index tables are important data objects within a relational database. Originally, index have been implemented based on AVL binary tree, which still can be used, but are not recommended. Since version 2.16 and up, btree+ index objects are supported which are recommended for any index objects. Both types of index objects can be used in the same manner. It is recommended to use btree index objets, since the creation time and space consumption is much less than for AVL index objects.

Index objects are created using the following statements

<CreateIndexStatement>	:=	create primary ( avltree \| index\| btree ) on <Id> ( <AttrList> );
<CreateIndexStatement>	:=	create [ unique ] ( avltree \| index\| btree ) <Id> on <Id> ( <AttrList> );

This results in three types of Cego index objects

Primary index
Unique index
Ordinary index

Primary index objects are used for primary key implementation in a way, that the index is unique and the attribute values must be not null. Primary indexes can either be created within data table creation using the primary key clause, or explicit using the primary key word. Just one primary key is allowed per table. To use foreign keys, a primary key defintion must exist for the corresponding table. Unique and ordinary indexes are used for further table indexing. Multiple definitions can be used, depending on the data model requirements

CGCLT > create table mytab2 ( a int not null, b string(30) );
Table mytab2 created
ok ( 0.002 s )
CGCLT > create primary index on mytab2 ( a );
Primary index created
ok ( 0.001 s )
CGCLT > create btree b1 on mytab2 ( b );
BTree b1 created
ok ( 0.001 s )

5.11.1 Index check

The AVL algorithm used for Cego index tables requires tree balancing information for each node entry. The verify the consisteny of a created index, a check can be performed on a created index. If the index check was successful, the height of the index tree is printed.

CGCLT > create avltree a1 on t1(a);
Index a1 created
ok ( 0.003 s )
CGCLT > check avltree a1;
Index ok ( Height = 12 )
ok ( 0.001 s )

Please note: Any check routine for btree objects is actually not available

5.12 Using foreign keys

In databases, referential constraints ( also called foreign keys ) are used to ensure consistency of the user data in terms of referential integrity. In this sense, a foreign key column of a data tables refers to any primary key ( either of the same or a different data table ). This avoids a row insertion for those rows, where a reference key does not exist but also row deletion, if this would lead to a constraint violation.

<AlterForeignKeyStatement>	:=	alter table <Id> add foreign key <Id> ( <AttrList> ) references <Id> ( <AttrList> );
<AttrList>	:=	<Id> , <Id> , ...

In the following, we give an example, how to create a foreign key object for a previous created table mytab.

CGCLT > create table mytab2 ( primary x int not null, a int );
Table mytab2 created
ok ( 0.002 s )
CGCLT > alter table mytab2 add foreign key myfkey ( a ) references mytab ( a );
Foreign key myfkey created
ok ( 0.001 s )

5.13 Using check constraints

Check constraints are useful, if you would like to restrict table data to a valid subset instead of all possible values of the corresponding datatype. A check constraint is added to a table and is defined as a condition, which contains any predicates with attributes of the table.

alter table <Id> add check <Id> on <Condition>;

CGCLT > create table mytab2 ( primary a int not null, b int );
Table mytab2 created
ok ( 0.002 s )
CGCLT > alter table mytab2 add check chk1 on a < 1000;
Check chk1 created
ok ( 0.001 s )
CGCLT > insert into mytab2 values ( 1000, 10);
Error : Check constraint chk1 violated
Query failed
ok ( 0.001 s )
CGCLT > insert into mytab2 values ( 100, 10);
1 tuples inserted
ok ( 0.001 s )

Please note, that actually sub selects are not supported in check constraint conditions.

5.14 Using table aliases

To refer to table data using an alternate tablename and alternate attribute names, table alias could be used. Other than view objects, which can be accesed read only, table alias objects can be accessed read / write. A table objects can be created with the following statement.

<CreateAliasStatement>	:=	create alias <Id> on <Id> ( <AliasList> );
<AliasList>	:=	<Alias> , <AliasList>
<AliasList>	:=	<Alias>
<Alias>	:=	<Id> as <Id>

CGCLT > create alias myalias on t1 ( a as ax, b as bx );
Alias myalias created
ok ( 0.002 s )

5.15 Views

Any ( complicated ) select queries, which should be used in a more convenient form can be encapsulated into view statements. A view statement contains the defined select statement as defined with the create view statement. If the view is accessed, the evaluation of the select statement is done and the tuple result is returned as same as calling the native select statement. The view then can be used as a normal table object inside other select statements.

create view <Id> as <SelectStatement> ;

To demonstrate the usage of views, two native tables are created and a view is created based on these two tables.

CGCLT > create table tab1 ( primary a int, b string(30));
Table tab1 created
ok ( 0.003 s )
CGCLT > create table tab2 ( primary a int, b int, c string(30));
Table tab2 created
ok ( 0.003 s )
CGCLT > create view v1 as select t1.a , t1.b , t2.c  from tab1 t1, tab2 t2 where t1.a = t2.a;
View v1 created
ok ( 0.002 s )

The view now can be used as a normal table object.

CGCLT > select count(*) from v1;
ok ( 0.002 s )

Views can be removed using the drop operation.

CGCLT > drop if exists view v1;
ok ( 0.002 s )

In combination with the if-exists option, the drop operation will not fail in case that the view does not exist anymore.

5.16 Procedures

Stored procedures enable the database programmer to encapsulate database specific application code directly into the database. With the cego database system, stored procedures are provided in the same way as for several other database systems. A procedure has to be created inside a SQL batch script, embedding the create statement with the delimiter sign @. Stored procedures must have a unique name and a number of input and output parameters. Optionally, a procedure return value can be specified to turn the procedure into a function. Within the procedure, several database queries may occur but also control blocks for loops and conditions.

<ProcCreateStatement>	:=	create procedure <Id> ( <ParameterList> ) [ <ReturnOption> ] begin <ProcStmtBlock> end ;
<ParameterList>	:=	<Parameter> , <Parameter> ...
<ReturnOpt>	:=	<Id> return <Datatype>
<Parameter>	:=	<Id> ( in \| out ) <Datatype>
<ProcStmtBlock>	:=	<ProcStatementList> <ProcExceptionList>
<ProcStatementList>	:=	<ProcStatementList> ; <ProcStatement>
<ProcStatementList>	:=	<ProcStatement> ;
<ProcStatement>	:=	var <Id> <DataType>
<ProcStatement>	:=	cursor <Id> as <SelectStatement>
<ProcStatement>	:=	close <Id>
<ProcStatement>	:=	<Id> = <Expr>
<ProcStatement>	:=	<Expr>
<ProcStatement>	:=	throw <Expr>
<ProcStatement>	:=	if <ProcCondition> then <ProcStmtBlock> [ elsif<ProcCondition> then* <ProcStmtBlock> ] [ else <ProcStmtBlock> ] end
<ProcStatement>	:=	while <ProcCondition> begin <ProcStmtBlock> [ end*
<ProcStatement>	:=	<InsertStatement>
<ProcStatement>	:=	<UpdateStatement>
<ProcStatement>	:=	<DeleteStatement>
<ProcStatement>	:=	start transaction
<ProcStatement>	:=	commit
<ProcStatement>	:=	rollback
<ProcStatement>	:=	lock table <Id>
<ProcStatement>	:=	unlock table <Id>
<ProcStatement>	:=	return <Expr> ;
<ProcStatement>	:=	return ;
<ProcCondition>	:=	<ProcCondition> and <ProcPredicate>
<ProcCondition>	:=	<ProcCondition> or <ProcPredicate>
<ProcCondition>	:=	<ProcPredicate>
<ProcPredicate>	:=	( <ProcCondition> )
<ProcPredicate>	:=	<Expr> <Comparison> <Expr>
<ProcPredicate>	:=	<Expr> is null
<ProcPredicate>	:=	<Expr> is not null
<ProcExceptionList>	:=	<ProcExceptionList> <ProcExceptionStmt>
<ProcExceptionStmt>	:=	exception when <ProcExceptionType> then <ProcStatementList>
<ProcValueListOpt>	:=	<ProcValueList>
<ProcValueListOpt>	:=
<ProcValueList>	:=	<ProcValueList> , <Expr>
<ProcValueList>	:=	<Expr>
<ProcExceptionType>	:=	core_op \| other \| any

If a single SQL statement does not provide enough power, a stored procedure might be useful. In general, stored procedures are used to encapsulate application specific SQL code inside the database. This can be useful for performance reasons, but also for code maintenance.
To illustrate the usage of stored procedures in Cego, the following small sample is given.

5.16.1 Procedure sample 1 - Simple table copy

drop table srctab;
drop table desttab;
drop procedure copytab;

create table srctab ( primary a int, b string(30));
create table desttab ( primary a int, b string(30));

insert into srctab values ( 1, 'fred');
insert into srctab values ( 2, 'mark');
insert into srctab values ( 3, 'berta');
insert into srctab values ( 4, 'john');
insert into srctab values ( 5, 'anna');

@
create procedure copytab ( copyCond in int ) return int
begin

   var copyCount int;
   :copyCount = 0;

   var ca int;
   var cb string(30);

   cursor copyCursor as select a, b from srctab where a = :copyCond;

   while fetch copyCursor into ( :ca, :cb ) = true
   begin
      insert into desttab values ( :ca, :cb );
      :copyCount = :copyCount + 1 ;
   end;

   return :copyCount;

end;
@

set a = 3;
set r = 0;
:r = call copytab(:a);
print 'Number of rows copied : ' | :r;

A procedure called copytab is created which copies the table srctab to the table desttab. To demonstrate the usage of procedure input parameters, a copy condition is given which must match the selected tuple from srctab. If finished successful, the procedure returns the number of copied rows with the procedure return value.

To execute the procedure, both tables must be available. So at the beginning of the sample, the tableset is cleaned up and both tables are created. Please note, that we have to create an index on attribute a in srctab to satisfy the where condition in the select statement.

Some tuples are inserted in srctab as the demo data.

Next, the procedure is created. Since the procedure contains semicolon signs for statement separation, but the procedure creation statement should be given to the parser as a single statement, we need to encapsulate the create statement into the special characters @.

The procedure is created with on input parameter called copyCond of type integer. The procedure returns a value of type int.

Inside the create statement, the procedure variable copyCount is declared using the var keyword and it is initialized to zero. Two other variables called ca and cb are declared, to store the fetched data from the select statement. Then a cursor is declared with the conditioned select statement on table srctab.

A while loop is used to trace through the srctab table. The fetch predicate is used as the loop invariant. If no more data can be fetched, a value of false is returned and the loop is terminated.

The while loop block consits of two statement. An insert statement, to store the fetched values into desttab and a variable assignment to increase the copy counter. After the loop has been finished, the variable copy counter is returned to the caller.

Now the procedure can be called. In an Cego SQL interpreter, variables can also be used outside of procedures. We set up two variables named a and r to be used as the input value and to store the return value of the procedure.

At last, the procedure is called and the return value is printed out

To test the given sample, put the code into a file called procsample.sql. Assuming the Cego database backend is running on localhost port 2200 and tableset TS1 is started, you can invoke the Cego client program with the following command

$ cgclt --server=localhost --port=2200 --user=lemke/lemke --batchfile=procsample.sql

5.16.2 Procedure sample 2 - Insert with id counter

create table orders (primary onr int not null, mname string(30), orderdate datetime);
create table ids ( primary id string(30) not null, idval int);

insert into ids values ('ONR', 10000);

@
create procedure nextId ( id in string(30) ) return int 
begin 

   var actId int;
   var res bool;

   cursor actIdCur as select idval from ids where id = :id;

   :actId = 0;

   if fetch actIdCur into ( :actId  ) = true
   then      
      close actIdCur;	
      :actId = :actId + 1;
      update ids set idval = :actId where id = :id;       	      
   end;

   return :actId;

end;
@

@
create procedure new_order (mname in string(30) ) return int 
begin 
 
   var onr int;

   :onr = nextId('ONR');
   insert into orders values ( :onr, :mname, sysdate );

   return :onr;

end;
@

:onr = call new_order('spoon');
:onr = call new_order('screw');
:onr = call new_order('hammer');

select * from orders;

A smarter form of the nextId procedure can be implemented using the update return clause. The return clause enables the caller to store any updated values of the corresponding statement into procedure variables.

@
create procedure nextId ( id in string(30) ) return int 
begin 

   var nextVal int;

   update ids set idval = idval + 1 where id = :id return :nextVal = idval;

   return :nextVal;

end;
@

5.16.3 Procedure sample 3 - Exception Handling

A cego procedure allows you to catch several exceptions, which can occur during query execution. The exception part is added at the end of a procedure block and is introduced with exception when followed by the exception which should be catched. The following exception can be catched

Exception	Meaning
core_op	Error which occured during execution of a statment
other	User thrown exceptions
any	Any exception is catched

More information about the exception is stored in the special procedure block variable excep_info of type string. The value of excep_info can be assigned to a return or output parameter of the calling procedure.

@
create procedure myexcep ( resultinfo out string(30) ) return int 
begin

   var c int;
   -- table does not exist, exception core_op is thrown
   insert into XXX values ( 1000, 'xxx' );

   :c = ( select count(*) from t1 );
   if :c > 3
   then
	throw 'Too many inserts';
   end;
   :resultinfo = 'OK';

   return 0;
exception when core_op
then
    -- exception for invalid objects is catched here
    :resultinfo = :excep_info;
    return 1;
exception when other
then
    -- exception is catched, exception information is assigned from special var excep_info to return parameter 
    :resultinfo = :excep_info;
    return 2;
end;
@

set x = 'XXX';
:r = call myexcep(:x);
print :r;
print :x;

drop procedure myexcep;

If a procedure is not used anymore, it can be dropped.

CGCLT > drop procedure nextId;
Procedure dropped
ok ( 0.001 s )

Procedures can also be dropped in combination with the if-exists option

CGCLT > drop if exists procedure nextId_notexist;
Procedure does not exist
ok ( 0.001 s )

5.17 Trigger

Database triggers are used to perform further database operations before or after any insert, update or delete operation. If triggers are not used carefully, this might result in complex and difficult to maintain database configurations. From my perspective, triggers should mostly be used, if the corresponding database application can not handle the required operations or if it is not possible to encapsulate the operation with the trigger into a dedicated stored procedure.

<CreateTriggerStatement>	:=	create trigger <Id> ( before \| after ) <TriggerConditionList> on begin <ProcStmtBlock> end
<TriggerConditionList>	:=	<TriggerConditionList> or <TriggerCondition>
<TriggerConditionList>	:=	<TriggerCondition>
<TriggerCondition>	:=	( insert \| update \| delete )

Here comes a sample for a trigger configuration

drop if exists table t1;
create table t1 ( a int, b string(30));

drop if exists table t2;
create table t2 ( c int, d string(30));

drop if exists table t3;
create table t3 ( m string(50));

@
create trigger tg1 before insert on t1
begin
	-- while t1 in increasing, t2 is decreasing
	delete from t2 where c = a;	
end;
@

@
create trigger tg2 before update on t1
begin
	-- store update protocol in t3
	var s string(30);
	:s = 'T1 updated at ' | sysdate | ' to ' | b;
	insert into t3 values ( :s );	
end;
@

list trigger;
show trigger tg1;
show trigger tg2;

insert into t2 values ( 1, 'XXX');
insert into t2 values ( 2, 'XXX');
insert into t2 values ( 3, 'XXX');
insert into t2 values ( 4, 'XXX');

insert into t1 values ( 1, 'HUGO');
select * from t2;

insert into t1 values ( 2, 'HUGO');
select * from t2;

insert into t1 values ( 3, 'HUGO');
select * from t2;

select * from t3;
update t1 set b = 'Bert' where a = 1;
update t1 set b = 'Gabi';
update t1 set b = 'Werner';	
select * from t3;

If a trigger is not used anymore, it can be dropped. Like all other database objects, this can also be done in a defensive way using the if-exists clause

CGCLT > drop if exists trigger tg1;
Trigger tg1 dropped
ok ( 0.001 s )

5.18 Counter

Counter objects provide a convenient way to manage unique id values. All counters are of type long, but are casted to the appropriate datatype ( int, smallint, tinyint ). A counter object is created and used inside a tableset as described in the following.

5.18.1 Adding a counter

Before using a counter, one has to be added to a tableset. A counter is added with the following command

CGCLT > create counter mycounter;
Counter added
ok ( 0.001 s )

5.18.2 Listing counters

Counters can be listed with the following command

CGCLT > list counter;
+-------------------------------+-------------------------------+
|                       Counter |                       Counter |
|                          Name |                         Value |
+-------------------------------+-------------------------------+
|                          hurz |                             0 |
|                          iwan |                             7 |
|                     mycounter |                             0 |
+-------------------------------+-------------------------------+
ok ( 0.002 s )

5.18.3 Using a counter

To access counters, the internal function nextcount should be used. Each method call increases the corresponding counter.

CGCLT > insert into t1 values ( nextcount(mycounter) );
1 tuples inserted
ok ( 0.001 s )

5.18.4 Setting a counter

A counter value can be set to a specific value

CGCLT > create counter c1;
Counter created
ok ( 0.000 s )
CGCLT > set counter c1 to 30;
Counter c1 set
ok ( 0.000 s )
CGCLT > list counter;
+-------------------------------+-------------------------------+
|                       Counter |                       Counter |
|                          Name |                         Value |
+-------------------------------+-------------------------------+
|                            c1 |                            30 |
+-------------------------------+-------------------------------+
ok ( 0.002 s )
CGCLT > create table t1 ( a int );
Table t1 created
ok ( 0.001 s )
CGCLT > insert into t1 values ( 1220);
1 tuples inserted
ok ( 0.001 s )
CGCLT > set counter c1 to ( select a from t1 );
Counter c1 set
ok ( 0.001 s )
CGCLT > list counter;
+-------------------------------+-------------------------------+
|                       Counter |                       Counter |
|                          Name |                         Value |
+-------------------------------+-------------------------------+
|                            c1 |                          1220 |
+-------------------------------+-------------------------------+
ok ( 0.002 s )
CGCLT > set counter c1 to ( select count(*)  from t1 );
Counter c1 set
ok ( 0.001 s )
CGCLT > list counter;
+-------------------------------+-------------------------------+
|                       Counter |                       Counter |
|                          Name |                         Value |
+-------------------------------+-------------------------------+
|                            c1 |                             1 |
+-------------------------------+-------------------------------+
ok ( 0.002 s )

5.18.5 Geting the current value of a counter

To retrieve the value of a specific counters, the internal function getcount can be used.

CGCLT > select getcount(mycounter) );
+---------------------+
|                FUNC |
| getcount(mycounter) |
+---------------------+
|                   1 |
+---------------------+
1 tuples
ok ( 0.033 s )

5.18.6 Dropping a counter

If a counter is not used anymore, it can be dropped.

CGCLT > drop counter mycounter;
Counter dropped
ok ( 0.001 s )

For a more defensive kind, you can also use the if exists clause.

CGCLT > drop if exists counter mycounter;
Counter dropped
ok ( 0.001 s )

Please note, that counter values are not recovered after a system crash. They should be adjusted using a tableset init file, which can be defined and is executed after tableset startup

5.19 Tableset information commands

Beside the native SQL query commands, other commands are available for database and tableset information retrievel.

5.19.1 uptime

The uptime command indicates the overall uptime of the bufferpool. In fact, this is also the uptime of the database.

CGCLT > uptime;
+---------------------------------------------------+
|                                        BUFFERPOOL |
|                                            UPTIME |
+---------------------------------------------------+
|                                  3 days, 12:37:54 |
+---------------------------------------------------+
1 tuples
ok ( 0.081 s )

5.19.2 show

With the show command, information about several kind of the selected tableset database objects can be retrieved.

To get an overview about the current status of the bufferpool, you can use the show pool command. Status information about all pages of the bufferpool is printed out.

CGCLT > show pool;
+---------------------+---------------------+
|            POOLINFO |            POOLINFO |
|           PARAMETER |               VALUE |
+---------------------+---------------------+
|           Page Size |               16384 |
|         Total Pages |               12000 |
|          Used Pages |                4287 |
|          Free Pages |                7713 |
|         Dirty Pages |                   5 |
|         Fixed Pages |                   0 |
|    Persistent Pages |                  59 |
|       No Sync Pages |                   0 |
|         Spread Rate |               1.115 |
| ------------------- | ------------------- |
|          Stat Start | 10.11.2018 11:05:56 |
|            Hit Rate |              95.08% |
|       Cur Fix Count |               91394 |
|       Max Fix Count |              100000 |
|         Avg Fix Try |                   5 |
|          Disk Reads |                4499 |
|         Disk Writes |                  32 |
|          Read Delay |          0.040 msec |
|         Write Delay |          5.584 msec |
| ------------------- | ------------------- |
|         Pool Uptime |          1d 3:36:13 |
+---------------------+---------------------+
21 tuples
ok ( 0.125 s )

Please note, that the output of show pool is not tableset dependent.

To get usage information about the allocated datafiles, you can use the show systemspace command

CGCLT > show systemspace;
+-----------+-----------+-----------+
|           |           |           |
|     SPACE |  NUMPAGES | USEDPAGES |
+-----------+-----------+-----------+
|    SYSTEM |       200 |         2 |
|      TEMP |       200 |         1 |
|  DATAFILE |      5000 |      5000 |
|  DATAFILE |      5000 |       602 |
|  DATAFILE |      5000 |         0 |
|  DATAFILE |      5000 |         0 |
|  DATAFILE |      5000 |         0 |
|  DATAFILE |      5000 |         0 |
|  DATAFILE |      5000 |         0 |
|  DATAFILE |      5000 |         0 |
|  DATAFILE |      5000 |         0 |
|  DATAFILE |      5000 |         0 |
+-----------+-----------+-----------+
ok ( 0.017 s )

The show command can also be used to list procedure or view defintions as stored in the database.

CGCLT > show procedure copytab;
+-------------------------------------------------------------+
| PROCTEXT                                                    |
| copytab                                                     |
+-------------------------------------------------------------+
| procedure copytab(copycond in int) return int               |
| begin                                                       |
|    var copycount int;                                       |
|    var ca int;                                              |
|    var cb string(30);                                       |
|    :copycount = 0;                                          |
|    cursor copycursor as                                     |
|       select a,                                             |
|             b                                               |
|       from srctab@TS1 srctab_1                              |
|       where a = :copycond;                                  |
|    while fetch copycursor into (:ca, :cb) = true            |
|    begin                                                    |
|       insert into desttab values (:ca, :cb);                |
|       :copycount = :copycount - 1;                          |
|    end;                                                     |
|    return :copycount;                                       |
| end;                                                        |
+-------------------------------------------------------------+
ok ( 0.001 s )

CGCLT > show view v1;
+-------------------------------------------------------------+
| VIEWTEXT                                                    |
| v1                                                          |
+-------------------------------------------------------------+
| view v1 as                                                  |
| select *                                                    |
| from t1@TS1 t1_1;                                           |
+-------------------------------------------------------------+
ok ( 0.001 s )

5.19.3 list

An overview about several database objects can be retrieved with the list <object type> command. <objecttype> is one of the following values

tableset
table
index
btree
view
procedure
key
counter
sysobj
tmpobj
check

For example, all user tables in the selected tableset are listed with the following command

CGCLT > list table;
+-------------------------------+
|                               |
|                     TABLENAME |
+-------------------------------+
|                            t1 |
|                         mytab |
+-------------------------------+
ok ( 0.005 s )

5.19.4 tableinfo

To get a list of all releated objects for a table, you can retrieve information using the tableinfo command. The command lists all related indexes, foreign keys and checks for the corresponding table.

CGCLT > tableinfo t1;
+-------------------------------+----------------+
|                     TABLEINFO |      TABLEINFO |
|                          NAME |           TYPE |
+-------------------------------+----------------+
|                            t1 |          table |
|                            b1 |          btree |
+-------------------------------+----------------+
ok ( 0.000 s )

5.19.5 tablesize

To get more detailed info about a single table object, you can retrieve information using the tablesize command. The command lists all related objects ( indexes and foreign keys ) but also the used tableset space in pages. For btree object, the relevance is indicated which is the number of distinct entries. ( The join optimizer takes the btree object with the strongest relevance )

CGCLT > tablesize t1;
+-------------------------------+----------------+----------------+----------------+
|                     TABLEINFO |      TABLEINFO |      TABLEINFO |      TABLEINFO |
|                          NAME |           TYPE |          PAGES |      RELEVANCE |
+-------------------------------+----------------+----------------+----------------+
|                            t1 |          table |              1 |              0 |
|                            b1 |          btree |              1 |              2 |
+-------------------------------+----------------+----------------+----------------+
ok ( 0.000 s )

5.19.6 tupleinfo

To inspect the tuple status for a single table, the tupleinfo command might be useful. The number of overall rows but also transaction based committed, deleted, inserted and obsolete rows are indicated.

CGCLT > tupleinfo t1;
+----------------+----------------+----------------+----------------+----------------+
|      TUPLEINFO |      TUPLEINFO |      TUPLEINFO |      TUPLEINFO |      TUPLEINFO |
|           ROWS |      COMMITTED |        DELETED |       INSERTED |       OBSOLETE |
+----------------+----------------+----------------+----------------+----------------+
|           2534 |              0 |              0 |              0 |              0 |
+----------------+----------------+----------------+----------------+----------------+
1 tuples
ok ( 0.000 s )

The following tuple states may occur

COMMITTED - Tuple is already commited by an ongoing transaction
DELETED - Tuple is marked to be deleted
INSERTED - Tuple is marked as new
OBSOLETE - A tuple which has been inserted and then deleted inside the same transaction

5.19.7 desc

Table column information for table objects can be retrieved with the desc statement. The column name, it's datatype and the corresponding nullable flag is printed.

CGCLT > desc table mytab;
+-----------+-----------+-----------+-----------+-----------+
| TABLEDESC | TABLEDESC | TABLEDESC | TABLEDESC | TABLEDESC |
|      ATTR |      TYPE |    LENGTH |   DEFAULT |  NULLABLE |
+-----------+-----------+-----------+-----------+-----------+
|         a |       INT |         4 |      null |       yes |
|         b |    STRING |        30 |      null |       yes |
+-----------+-----------+-----------+-----------+-----------+
ok ( 0.002 s )

CGCLT > desc index i1;
+-----------+-----------+-----------+-----------+
| TABLEDESC | TABLEDESC | TABLEDESC | TABLEDESC |
|      ATTR |      TYPE |   DEFAULT |  NULLABLE |
+-----------+-----------+-----------+-----------+
|         b |    STRING |      null |       yes |
+-----------+-----------+-----------+-----------+
ok ( 0.002 s )

With the desc command, also procedure and view interfaces can be retrieved.

CGCLT > desc procedure checkinout;
+-----------+-----------+-----------+-----------+
|  PROCDESC |  PROCDESC |  PROCDESC |  PROCDESC |
|      ATTR |      TYPE |    LENGTH |     INOUT |
+-----------+-----------+-----------+-----------+
|     inval |    STRING |        30 |        in |
|    inval2 |       INT |         4 |        in |
|    outval |    STRING |        30 |       out |
+-----------+-----------+-----------+-----------+
ok ( 0.001 s )

CGCLT > desc view matview;
+-----------+-----------+-----------+
|  VIEWDESC |  VIEWDESC |  VIEWDESC |
|      ATTR |      TYPE |    LENGTH |
+-----------+-----------+-----------+
|     sname |    STRING |        30 |
|     mname |    STRING |        30 |
+-----------+-----------+-----------+
ok ( 0.001 s )

5.19.8 sync

The sync command enables the database user, to force a checkpoint for the current tableset. In this sense, all dirty pages of the bufferpool are written to the datafiles and a logfile switch is performed.

CGCLT > sync;
TableSet TS1 in sync
ok ( 0.002 s )

Please note : The sync operation is performed in a synchronous way, this means that the prompt just returns, if all data is written completely. For large database configurations, this may take a while.

5.20 Reorganisation

During the lifetime of a database table, size is increased caused by insert update or delete. Especially if append mode is enabled, the table is not searched for free data slots but instead data is appended to the end of the table. This may result in empty data pages which are still allocated to the table. To free these empty pages, a table reorganisation is required

CGCLT > tablesize t1;
+-------------------------------+----------------+----------------+
|                     TABLEINFO |      TABLEINFO |      TABLEINFO |
|                          NAME |           TYPE |          PAGES |
+-------------------------------+----------------+----------------+
|                            t1 |          table |             24 |
+-------------------------------+----------------+----------------+
ok ( 0.002 s )
CGCLT > reorganize table t1;
Table t1 reorganized
ok ( 0.001 s )
CGCLT > tableinfo t1;
+-------------------------------+----------------+----------------+
|                     TABLEINFO |      TABLEINFO |      TABLEINFO |
|                          NAME |           TYPE |          PAGES |
+-------------------------------+----------------+----------------+
|                            t1 |          table |             11 |
+-------------------------------+----------------+----------------+
ok ( 0.003 s )

Please note: Reorganisation on a table just affects the specified object. Reorganisation of index object must be done explicitly.

5.21 Append mode

If any insert or update operations on tables are performed, new tuples are added to a table. Adding tuples can be done in two ways. If performance is the major issue, tuples can be appended to a table instead of searching the whole table for an appropriate free data slot. Especially for very large tables, appending data can increase the insert performance significantly. With append mode enabled, excessive insert and update operations let the table increase very fast, which requires a reorganization of the table to free all unused emty pages. The database user hase to decide depending on the corresponding query or the performance requirements, if append mode is appropriate or not. Append mode can be enabled or disabled on the fly with the following queries
To disable append mode

CGCLT > set append off;
Append mode disabled;
ok ( 0.000 s )

And to enable append mode

CGCLT > set append on;
Append mode enabled
ok ( 0.000 s )

Please note: Append mode is set globally for the database session and affects all corresponding data and index tables.

The following sample illustrated the effect of enabling or disabling append mode

create table t1 ( primary a int not null, b string(10));
@
create procedure filltab ( c in int ) return int
begin

   var i int;
   :i = 0;
   while :i < :c
   begin
      insert into t1 values ( :i, 'AAAAAAA');
      :i = :i + 1;
   end;

   return :i;

end;
@

@
create procedure updtab ( c in int ) return int
begin

   var i int;
   :i = 0;
   while :i < :c
   begin
      update t1 set b = 'xxxx' where a = :i;
      :i = :i + 1;
   end;

   return :i;

end;
@

First, we fill the table with some data and perform some update operations on it

CGCLT > call filltab(10000);
Procedure filltab executed
ok ( 2.049 s )
CGCLT > call updtab(10000);
Procedure updtab executed
ok ( 9.799 s )
CGCLT > tableinfo t1;
+-------------------------------+----------------+----------------+
|                     TABLEINFO |      TABLEINFO |      TABLEINFO |
|                          NAME |           TYPE |          PAGES |
+-------------------------------+----------------+----------------+
|                            t1 |          table |             71 |
|                       t1_pidx |  primary index |             93 |
+-------------------------------+----------------+----------------+
ok ( 0.002 s )
CGCLT > call updtab(10000);
Procedure updtab executed
ok ( 9.877 s )
CGCLT > tableinfo t1;
+-------------------------------+----------------+----------------+
|                     TABLEINFO |      TABLEINFO |      TABLEINFO |
|                          NAME |           TYPE |          PAGES |
+-------------------------------+----------------+----------------+
|                            t1 |          table |            107 |
|                       t1_pidx |  primary index |            139 |
+-------------------------------+----------------+----------------+
ok ( 0.003 s )

Now we disable the append mode and call the update procedure another time

CGCLT > set append off;
Append mode disabled
ok ( 0.000 s )
CGCLT > call updtab(10000);
Procedure updtab executed
ok ( 11.086 s )
CGCLT > tableinfo t1;
+-------------------------------+----------------+----------------+
|                     TABLEINFO |      TABLEINFO |      TABLEINFO |
|                          NAME |           TYPE |          PAGES |
+-------------------------------+----------------+----------------+
|                            t1 |          table |            107 |
|                       t1_pidx |  primary index |            139 |
+-------------------------------+----------------+----------------+
ok ( 0.003 s )

The tableinfo command indicates no more grow of the table since the tuples are inserted in already allocated but unused pages. But update performance has decreased since the search for free slots takes additional time.

5.22 Grace mode

Several database objects can depend on each other. If a tableset is exported using the cgclt dump option, a reload of the exported sql file might result in object creation errors, since the referenced objecs are still not available. To avoid these errors, the grace mode must be enabled. In grace mode, referred objects are not checked for existance and the corresponding database objects could be created. If the referred objects is still not available at runtime, this results in a runtime error.

The tableset grace mode is enabled for the current tableset with the following command

CGCLT > set grace on;
Grace mode enabled
ok ( 0.000 s )

With enabled grace mode, a script like the sample below can be loaded without errors. A database view is created, which refers to a non-existing procedure and a procedure is created, which refers to a non-existing view.

set grace on;
drop if exists table bill;
create table bill ( primary billid int not null, netto fixed(10,2));

insert into bill values ( 1, 12.23);
insert into bill values ( 2, 67.11);

-- we create a view, which uses non existing procedure getVat
drop if exists view plainvat;
create view plainvat as
select getVat() as vat;

-- now we create a procedure, which uses non-existing view vatview 
drop if exists procedure getVat;
@
create procedure getVat() return fixed(10,2)
begin
        var vat fixed(10,2);
        :vat = ( select vat from vatview );
        return :vat;
end;
@

-- we provide vatview now
drop if exists view vatview;
create view vatview as
select (fixed)0.19 as vat;

-- no critical dependencies anymore on billview
drop if exists view billview;
create view billview as
select billid as billid,
netto as netto,
getVat() * netto as vat
from bill;

-- verify billview, objects are recompiled
select * from billview;

select * from vatview;
select * from plainvat;
set grace off;

5.23 BTree Cache

For very large tables, the creation of btree objects is pretty much time consuming. Since the btree is managed as same as other table objects, the data pages are accesed via the buffer pool. During creation, the btree must be reorganized which results in many bufferpool read and write requests. For small buffer pool configurations, this slows down the performance significantly. Since the creation of a btree is not critical in terms of data consistency, it is useful to use a dedicated memory buffer for this. It has been observed, that for a table of about 100 million tuples, the creation tome was reduced from about 20 hours to 2.5 hours by using the btree cache.

The btree cache is used with the cached option given wit the create btree command. Please note the the btree cache allocates additional memory for the build up procedure.

CGCLT > create btree b1 on t1(a) cached;
Btree b1 created
ok ( 0.039 s )

5.23.1 BTree Cache Performance

To illustrate the performance of the btree cache, the picture below shows the progress of the btree creation time with enabled and disabled btree cache. The system, which was used for this measurement, was a Intel Core i7 CPU, 16 GB Memory and a Fusion Disk Drive ( combined SSD and hard disk drive ) running OSX El Capitan.

It can be seen, that the btree creation time with disabled cache increases with an exponential growth, while the growth is rather linear with enabled cache. For systems with non SSD storage, is has been observed that the curve for disabled btree cache raises much faster. Of course this requires enough heap memory available for the dynamic allocation of the btree.

5.24 Table locking

At default, the database system allows concurrency access on the same data for any update transaction, since for normal operations, there should be no issues. In case of heavy concurrent updates on a single table, deadlock errors might occur. The reason is, that several threads are traversing the same data and perform update operations on it. Since several parts of the data is lockes by dedicated mutex semaphores, there is a chance for such a deadlock situation. Furthermore, it is not ensured, that a concurrent transaction is currently updating the same tuple. In this case, the update can not be performed which is indicated by the returned integer value from the update operation. To avoid this situation, explicit locks can be set up for a table. This synchronizes the concurrency on table level, which should avoid the described effects.

To set a lock on a table, just enter the command

CGCLT > lock table t1;
Table t1 locked
ok ( 0.000 s )

Now the update operation can occur on the table

CGCLT > update 1 set b = 'UPDATED' where a = 4711;
1 tuple updated
ok ( 0.000 s )

After finishing the update operation, the table is unlocked

CGCLT > unlock table t1;
Table t1 unlocked
ok ( 0.000 s )

Please note, that the the table can just be unlocked by the calling database thread. The unlock table operation is idempotent.

For the use of the lock statement in stored procedures, it is recommended to catch any exception during a table is manually locked. In the exception block, another unlock operation should occur to ensure, that the table can be accessed now by other database threads. We give a sample for a procedure, which returns the next available counter value managed by an id table tab_id

@
create procedure nextId(id in string(30)) return long
begin
   var idval long;
   lock table tab_id;
   update tab_id set idval=idval + 1 where id = :id return :idval = idval;
   unlock table tab_id;
   return :idval;
exception when any
then
   unlock table tab_id;
end;
@