CODB-QUERY

Description

codb-query library provides unified access to CODB databases by queries instead call of methods of classes CODBDEPOSITORY, CODBDICTIONARY, CODBLIST and CODBIDLIST.

Queries are written on CQL language (SQL dialect adapted for CODB). You can use this library in own CLIP program or use console program called codb.

Using In Programs.

For use codb-query library in programs you should build program with this library. Just append '-lcodb-query' to build command.

Typical session of work with database contains following steps:

db := codb_connect() // Connect to databases
e := codb_get_error( db ) // Check error
if e != NIL
?? "ERROR codb_connect(): " + e + chr(10)
return 1
endif

// Query execution
oRes := codb_execute( db, 'show databases' )

// Process result
aResult := codb_get_result( oRes )

codb_close( db ) // Close databases

Connect to databases is provided by function codb_connect(). This function opens all available databases. Function codb_close() closes all databases.

Real operations with databases are provided by functions: codb_execute(), codb_get_metaobject(), codb_get_object(), codb_put_metaobject(), codb_put_object().

The difference between these functions is codb_execute() returns object, from which you can extract data by function codb_get_result(), but other functions operate only real objects.

For error check use codb_get_answer() and codb_get_error().

Query result returned from codb_execute(), you can processed by codb_get_result(), codb_num_columns(), codb_num_rows() and codb_get_column().

Besides you can get result as formatted string. Just pass result object in CODBFormatter() method show.

Using codb.

codb [options] [database[:depository]]

On start codb without any parameters it will be work in interactive mode.

codb options:

• -c 'command' - Execute query and exit.

• --hide-titles - Suppress column names.

• --delim='DELIM' - Set columns delimiter.

Complete list of options is available on run codb -h.

Examples:

codb ETC01 -c 'show classes;'

codb ETC01:ETC0101 -c 'select * from mng_vendor,mng_author;' --delim='|'

Note: All data outputs in formatted mode so fields are accompanied with spaces to maximum width of element in each column.

CQL Commands.

Each CODB command should be ended by semicolon. You can write comments begin with '#' character and end with line end ('\n'). String are delimited by single quote ('). If single quote is part of string it should be escaped by backslash (\').

You can pass several commands in -c parameter and through pipe:

echo 'show classes;show indexes;' | codb ETC01

Brief CQL Commands Description.

• help - Help about database commands;

• create - Create metaobject;

• delete - Delete object or metaobject;

• describe - Show class structure;

• drop - Delete metaobject by its name;

• get - Get object from depository;

• metaget - Get metaobject;

• metaput - Create or update metaobject;

• put - Create or update object in depository;

• select - Query object attributes;

• show - Show list of metaobjects;

• use - Open database.

There is command quit - quits the program in interactive mode. You can quit from codb to press Esc key.

Verbose CQL Command Description.

help [<command>] Help on command. If 'help' call without command name show all available commands.

create <metaclass> (<attr1>=<value1>[,...]) or create database <db_name> <path>[<description>] Create database or metaobject. Examples: create database EAS01 /var/lib/db 'E/AS Database'; create attribute (name='phone',type='C',len=20);

Supported metaclasses:

• depository - depository;

• extent - namespace;

• attribute - attribute;

• counter - counter;

• index - index;

• class - class;

• tcolumn - view column;

• tview - view;

• report - report;

• plugin - plugin;

• user - user;

• group - group of users.

delete <id> Delete object or metaobject by its id.

describe <class_name> Show class structure by its name.

drop <metaclass> <metaobject_name> Delete metaobject by its name.

Supported metaclasses:

• depository - depository;

• extent - namespace;

• attribute - attribute;

• counter - counter;

• index - index;

• class - class;

• tcolumn - view column;

• tview - view;

• report - report;

• plugin - plugin;

• user - user;

• group - group of users.

get <id> Get object content from depository by its id.

metaget <id> Get metaobject content from dictionary by its id.

metaput (<attr1>=<value1>[, ...]) Create or change metaobject in dictionary. If 'id' is defined in attributes list, exist object will be replaced by new values.

put (<attr1>=<value1>[, ...]) Create or change metaobject in depository. If 'id' is defined in attributes list, exist object will be replaced by new values.

select <attributes> from <class> [where <condition>] Get list of attribute values matched condition.

<attributes> - list of attribute names delimited by comma. Names with spaces put in single quotes. You can use * for all attribute names for all specified classes.

<class> - list of class names delimited by comma. Names with spaces put in single quotes.

<condition> - condition by CLIP rules. There are supported operators: '==', '!=', '>', '>=', '<=', '<' and logical operators '.not.', '.and.', '.or.'. Value should be put in double quotes and there are no spaces between operators.

Example:

select id,author_name,email from mng_author where code=="Uri";

show <metaclass> Show metaobject list.

Supported metaclasses:

• databases - databases;

• depositories - depositories;

• extents - namespaces;

• attributes - attributes;

• counters - counters;

• indexes - indices;

• classes - classes;

• tcolumns - view columns;

• tviews - views;

• reports - reports;

• plugins - plugins;

• users - users;

• groups - user groups;

• statistics - total statistics with number metaobjects in each metaclass.

use <db> [<depository>] Change current work database and depository.

 codb_close(<oDB>) --> NIL
 

 codb_connect() --> oDB

 codb_execute(<oDB>,<sQuery>,[<sDatabase>],[<aFiles>]) --> oRes

 codb_get_answer(<oRes>) --> '' | 'ERROR'

 codb_get_column(<oRes>, <nIndex>) --> sName

 codb_get_error(<oRes>) --> sError | NIL

 codb_get_metaobject(<oDB>,<sID>,[<sDatabase>]) --> oMetaobject

 codb_get_object(<oDB>,<sID>,[<sDatabase>]) --> oObject

 codb_get_result(<oRes>) --> aResult

 codb_num_columns(<oRes>) --> nColumns

 codb_num_rows(<oRes>) --> nRows

 codb_put_metaobject(<oDB>,<oContent>,[<sDatabase>],<sClass>) --> oResult

 codb_put_object(<oDB>,<oContent>,[<sDatabase>],<sClass>) --> oResult

 db := codb_connect()
 e := codb_get_error( db )
 if e != NIL
 ?? "ERROR codb_connect(): " + e + chr(10)
 return 1
 endif
 fmt := CODBFormatter()
 fmt:obj:options:hide_titles := .T.
 res := codb_execute( db, 'show databases' )
 ? fmt:show()
 codb_close( db )