Builtin types

CHAR[ACTER] '(' length ')'	character string with length upperbound
VARCHAR '(' length ')' | CHARACTER VARYING '(' length ')'	string with atmost length upperbound
CLOB | CHARACTER LARGE OBJECT
BLOB | BINARY LARGE OBJECT
DECIMAL '(' P ',' S ')' | NUMERIC '(' P ',' S ')'	with precision P and scale S
TINYINT	8 bit integer
SMALLINT	16 bit integer
INT	32 bit integer
BIGINT	64 bit integer
REAL	32 bit floating point
DOUBLE [PRECISION]	64 bit floating point
BOOLEAN
DATE
TIME  [ '(' posint ')' ] [WITH TIME ZONE]	time of day with precision and time zone
TIMESTAMP [ '(' posint ')' ] [WITH TIME ZONE]	date concatenated with unique time, precision and time zone
INTERVAL interval_qualifier	a temporal interval

The integer types align with the storage of 2,4, and 8 bytes. Their domain contains a NULL representation, which may cause unexpected side effects if you recast them to a larger size. The system does not provide overflow detection.

The numeric types are represented as fixed length integers, whose decimal point is produced during result rendering.

The types float and double map to the underlying implementation system. No special attention is given to the value NaN.

interval_qualifier :      { YEAR | MONTH  | DAY | HOUR | MINUTE | SECOND  } [time_precision]    |  start_field  TO  end_field start_field :     { YEAR | MONTH  | DAY | HOUR | MINUTE  } [time_precision] end_field:       { YEAR | MONTH  | DAY | HOUR | MINUTE | SECOND  [time_precision] } time_precision :      '(' posint ')'

 

Temporal types

MonetDB/SQL supports a rich set of time-related operations within the kernel. The starting point are SQL 92 and the ODBC time-related data types.  There are four basic types and operations on them:

date	a date in the Gregorian calendar, e.g. 1999-JAN-31
daytime	a time of day to the detail of milliseconds, e.g. 23:59:59:000
timestamp	a combination of date and time, indicating an exact point in time (GMT). GMT is the time at the Greenwich meridian without a daylight savings time (DST) regime. Absence of DST means that hours are consecutive (no jumps) which makes it easy to perform time difference calculations.
timezone	the local time is often different from GMT (even at Greenwich in summer, as the UK also has DST). Therefore, whenever a timestamp is composed from a local daytime and date, a timezone should be specified in order to translate the local daytime to GMT (and vice versa if a timestamp is to be decomposed in a local date and daytime). To adjust the local time can issue a command such as SET TIME ZONE INTERVAL '1' HOUR TO MINUTE.

We provide predefined timezone objects for a number of timezones (see below). Also, there is one timezone called the local timezone, which can be set to one global value in a running MonetDB server, that is used if the timezone parameter is omitted from a command that needs it (if not set, the default value of the local timezone is plain GMT).

The value ranges and lexical denotations are defined as follows:

Min and max years. The maximum and minimum dates and timestamps that can be stored are in the years 5,867,411 and -5,867,411, respectively. Interestingly, the year 0 is not a valid year. The year before 1 is called -1.

Dates. Fall in a valid year, and have a month and day that is valid in that year. The first day in the year is January 1, the last December 31. Months with 31 days are January, March, May, July, August, October, and December, while April, June, September and November have 30 days. February has 28 days, expect in a leap year, when it has 29. A leap year is a year that is an exact multiple of 4. Years that are a multiple of 100 but not of 400 are an exception; they are no leap years.

Daytime. The smallest daytime is 00:00:00:000 and the largest 23:59:59:999 (the hours in a daytime range between [0,23, minutes and seconds between [0,59 and milliseconds between [0:999). Daytime identifies a valid time-of-day, not an amount of time (for denoting amounts of time, or time differences, we use here concepts like "number of days" or "number of milliseconds" denoted by some value of a standard integer type).

Timestamp. A valid timestamp is formed by a combination of a valid date and valid daytime. When creating a timestamp from a date and daytime, a timezone should be specified (if timezone is omitted, the local timezone is assumed). If a timezone is specified, it is used to convert the date and time in that timezone to GMT, which is the internal timestamp representation. One problem here is that the first hour after DST has ended (some Sunday night in autumn, generally), the time is set back one hour, so the same times occur twice. Hence two translations to a timestamp are possible for such date and time combinations. In those case, we act as if it was the first occurrence (still within DST).

For difference calculations between dates (in numbers of days) we use signed integer. Hence, the valid range for difference calculations is between -2147483648 and 2147483647 days (which corresponds to roughly -5,867,411 and 5,867,411 years).

For difference between timestamps (in numbers of milliseconds) we use 64-bit bigint. These are large integers of maximally 19 digits, which therefore impose a limit of about 106,000,000,000 years on the maximum time difference used in computations.
 

datetime_func:      EXTRACT '(' datetime_field FROM scalar_exp ')'    | CURRENT_DATE [ '(' ')' ]    | CURRENT_TIME [ '(' ')' ]    | CURRENT_TIMESTAMP [ '(' ')' ]    | LOCALTIME [ '(' ')' ]    | LOCALTIMESTAMP [ '(' ')' ]    | CURRENT_TIME [ '(' ')'] datetime_field: { YEAR | MONTH | DAY | HOUR | MINUTE | SECOND

Gregorian dates.
The basics of the Gregorian calendar stem from the time of Julius Caesar, when the concept of a solar year as consisting of 365.25 days (365 days plus once in 4 years one extra day) was introduced. However, this Julian Calendar, made a year 11 minutes long, which subsequently accumulated over the ages, causing a shift in seasons. In medieval times this was noticed, and in 1582 Pope Gregory XIII issued a decree, skipped 11 days. This measure was not adopted in the whole of Europe immediately, however.  For this reason, there were many regions in Europe that upheld different dates.

It was only on September 14, 1752 that some consensus was reached and more countries joined the Gregorian Calendar, which also was last modified at that time. The modifications
were twofold: first, 12 more days were skipped. Second, it was determined that the year starts on January 1 (in England, for instance, it had been starting on March 25). Other parts of the world have adopted the Gregorian Calendar even later.

MonetDB implements the Gregorian Calendar in all its regularity. This means that values before the year 1752 probably do not correspond with the dates that people really used in times before that (what they did use, however, was very vague anyway, as explained above). In solar terms, however, this calendar is reasonably accurate (see the "correction seconds" note below).

Timezones
The basic timezone regime was established on November 1, 1884 in the International Meridian Conference held in Greenwich (UK). Before that, a different time held in almost any city. The conference established 24 different time zones defined by regular longitude intervals that all differed by one hour.  Not for long it was that national and political interest started to erode this nicely regular system.  Timezones now often follow country borders, and some regions (like the Guinea areas in Latin America) have times that differ with a 15 minute grain from GMT rather than an hour or even half-an-hour grain.

An extra complication became the introduction of daylight saving time (DST), which causes a time jump in spring, when the clock is skips one hour and in autumn, when the
clock is set back one hour (so in a one hour span, the same times occur twice). The DST regime is a purely political decision made on a country-by-country basis. Countries in the same timezone can have different DST regimes. Even worse, some countries have DST in some years, and not in other years.

To avoid confusion, this temporal type module stores absolute points of time in GMT only (GMT does not have a DST regime). When storing local times in the database, or retrieving local times from absolute timestamps, a correct timezone object should be used for the conversion.

Applications that do not make correct use of timezones, will produce irregular results on e.g. time difference calculations.

Correction seconds
Once every such hundred years, a correction second is added on new year's night. This rule would seriously complicate the temporal type mofule (as then the duration of a day, which is now the fixed number of 24*60*60*1000 milliseconds, becomes parametrized by the date), it is not implemented. Hence these seconds are lost, so time difference calculations in milliseconds (rather than in days) have a small error if the time difference spans many hundreds of years.

We cannot handle well changes in the timezone rules (e.g., DST only exists since 40 years, and some countries make frequent changes to the DST policy). To accommodate this we should make timezone_local a function with a year parameter. The tool should maintain and access a timezone database. Lookup of the correct timezone would be dynamic in this structure. The timezone_setlocal would just set the string name of the timezone.

Timezones

 

Wake Island	GMT+12:00
Melbourne/Australia	GMT+11:00
Brisbane/Australia	GMT+10:00
Japan	GMT+09:00
Singapore	GMT+08:00
Thailand	GMT+07:00
Pakistan	GMT+05:00
United Arab Emirates	GMT+04:00&GMT
GMT
Azore Islands	GMT-01:00
Hawaii/USA	GMT-10:00
American Samoa	GMT-11:00
Kazakhstan	GMT+06:00-DST[first sunday from end of march@02:01,first sunday from end of october@02:01
Moscow/Russia	GMT+03:00-DST[first sunday from end of march@02:01,first sunday from end of october@02:01
East/Europe	GMT+02:00-DST[first sunday from end of march@02:01,first sunday from end of october@02:01
West/Europe	GMT+01:00-DST[first sunday from end of march@02:01,first sunday from end of october@02:01
UK	GMT-DST[first sunday from end of march@02:01,first sunday from end of october@02:01
Eastern/Brazil	GMT-02:00-DST[first sunday from end of october@02:01,first sunday from end of march@02:01
Western/Brazil	GMT-03:00-DST[first sunday from end of october@02:01,first sunday from end of march@02:01
Andes/Brazil	GMT-04:00-DST[first sunday from end of october@02:01,first sunday from end of march@02:01
East/USA	GMT-05:00-DST[first sunday from end of march@02:01,first sunday from end of october@02:01
Central/USA	GMT-06:00-DST[first sunday from end of march@02:01,first sunday from end of october@02:01
Mountain/USA	GMT-07:00-DST[first sunday from end of march@02:01,first sunday from end of october@02:01
Alaska/USA	GMT-09:00-DST[first sunday from end of march@02:01,first sunday from end of october@02:01

 

Serial types

As of 2003 the SQL standard supports serial types (sequences). They are of particular use in auto-generating key values.A serial type is defined as a primary database object over any of the built-in data types. The NEXT VALUE operation generates the next value and can be used anywhere a value expression is allowed. Its name should be unique. It can only be dropped when the tables mentioning it have previously been dropped.

generated_column:     GENERATED ALWAYS AS IDENTITY [ '(' seq_params ')' ]   | AUTO_INCREMENT sequence_def:     SEQUENCE name AS datatype seq_params:     [ START WITH poslng ]     [ INCREMENT BY nonzerolng ]     [ MINVALUE nonzerolng | NO MINVALUE ]     [ MAXVALUE nonzerolng | NO MAXVALUE ]     [ CACHE nonzerolng ]     [ [ NO ] CYCLE ]

Example. The example shown below introduces the column count, which is incremented with each row being added. It is conceptually identical to the value expression max(count)+1in each insert. The column info is a limited range with wrap around.The serial type as found in PostgreSQL and the auto_increment  flag as found in MySQL are both mapped onto a sequence type in MonetDB/SQL.

     CREATE TABLE test (
     	d date,
     	id serial,
     	count int AUTO_INCREMENT,
     	info int GENERATED ALWAYS AS IDENTITY (
     		START WITH 100 INCREMENT BY 2
     		NO MINVALUE MAXVALUE 1000
     		CACHE 2 CYCLE)
     );

Much like other primary database objects, the sequence type can be altered at any time as illustrated below.

     sql>CREATE SEQUENCE "my_test_seq" as integer START WITH 2;
     sql>CREATE TABLE test (t int DEFAULT
     more>        NEXT VALUE FOR "my_test_seq", v char);
     sql>INSERT INTO test(v)  VALUES ('a');
     Rows affected 1
     sql>INSERT INTO test VALUES (10, 'b');
     Rows affected 1
     sql>ALTER SEQUENCE "my_test_seq"
     more>        RESTART WITH (SELECT MAX(t) + 1 FROM test);
     sql>INSERT INTO test(v)  VALUES ('c');
     Rows affected 1
     sql>SELECT * FROM test;
     +----+---+
     | t  | v |
     +====+===+
     |  2 | a |
     | 10 | b |
     | 11 | c |
     +----+---+

 

URL datatype

The URL data type is a subdomain over strings and represent valid URLs. A collection of operators are provided to extract portions for futher manipulation.

	Description
	getAnchor(theUrl url) returns string
	getBasename(theUrl url) returns string
	getContent(theUrl url) returns string
	getContext(theUrl url) returns string
	getDomain(theUrl url) returns string
	getExtension(theUrl url) returns string
	getFile(theUrl url) returns string
	getHost(theUrl url) returns string
	getPort(theUrl url) returns string
	getProtocol(theUrl url) returns string
	getQuery(theUrl url) returns string
	getUser(theUrl url) returns string
	getRobotURL(theUrl url) returns string
	isaURL(theUrl string) returns string
	newurl(protocol string, hostname string, "port" int, file string) returns url
	newurl(protocol string, hostname string, file string) returns url

Network Address Type

The SQL type inet describes IPv4 network addresses. The inet module contains a collection of functions that operate on IPv4
addresses.  The most relevant functions are the `containment' functions that deal with subnet masks. 

	Description
	broadcast(p inet) returns inet
	host(p inet) returns clob
	masklen(p inet) returns int
	setmasklen(p inet) returns inet
	netmask(p inet) returns inet
	hostmask(p inet) returns inet
	network(p inet) returns inet
	text(p inet) returns clob
	abbrev(p inet) returns clob

The functionality of this module is greatly inspired by the PostgreSQL inet atom. It should be extended to also support IPv6.

User defined types

 The MonetDB kernel supports creation of user defined types, e.g. geometric types. The relationship between SQL and MAL world is expressed using an external name.

CREATE TYPE type_name EXTERNAL NAME type_identification DROP TYPE type_name [RESTRICT | CASCADE]

The implementation of new atomary types is best postponed until there is no other performance-wise acceptable solution. Addition of an atomary type in the kernel would be beneficial if it is also complemented with bulk-operations and when type specific optimizers could exploit there semantics.

Table definitions

The parser currently supports the full <table scope> specifier, but the implementation of the TEMPORARY qualifiers is limited to local temporary tables (i.e. the tables are only visible in the client session). The details of the remainder can be looked up in the SQL2003 documentation.

table_def:     CREATE [ TEMPORARY | LOCAL TEMPORARY | GLOBAL TEMPORARY ] TABLE table_name'(' table_content_source ')'    [ ON COMMIT DELETE ROWS | ON COMMIT PRESERVE ROWS | ON COMMIT DROP ] table_content_source:    table_element ',' ... | column_name ',' ... AS select_query [WITH NO DATA | WITH DATA]

 

table_element:     column_name data_type [column_option ...]   | CONSTRAINT constraint_name table_constraint_type   | column_name WITH OPTIONS '(' column_option ','... ')'   | LIKE table_name   | column_name SERIAL   | column_name BIGSERIAL

 

table_constraint_type:     UNIQUE '(' column_name ',' ... ')'    | PRIMARY KEY '(' column_name ',' ... ')'    | FOREIGN KEY '(' column_name ',' ... ')' REFERENCES table_name ['(' column_name ',' ... ')'] [ match_options ] [ ref_actions ]

 

column_option:     DEFAULT default_value    | [ constraint_name ] column_constraint_type    | GENERATED ALWAYS AS IDENTITY [ '(' serial_parameters ')' ]    | AUTO_INCREMENT

 

column_constraint_type:     NOT NULL | NULL | UNIQUE | PRIMARY KEY   | REFERENCES table_name [ '(' column_name ',' ... ')' ] [ match_options ] [ ref_actions ]

 

serial_parameters:     [START WITH nonzero]   | [RESTART | RESTART WITH subquery   | RESTART WITH nonzero]   | [INCREMENT BY nonzero]   | [MINVALUE nonzero | NOMINVALUE] | [MAXVALUE nonzero | NOMAXVALUE ] | [CACHE nonzero ] | [CYCLE | NONCYCLE]

 

match_options:     MATCH { FULL | PARTIAL | SIMPLE } ref_actions:     ON UPDATE { NO ACTION | CASCADE | RESTRICT | SET NULL | SET DEFAULT }   | ON DELETE { NO ACTION | CASCADE | RESTRICT | SET NULL | SET DEFAULT }   | { ON DELETE | ON UPDATE } { NO ACTION | CASCASDE | RESTRICT | SET NULL | SET DEFAULT }   | { ON UPDATE | ON DELETE } { NO ACTION | CASCASDE | RESTRICT | SET NULL | SET DEFAULT }

Alter statement

alter_statement:      ALTER TABLE qname ADD [ COLUMN ] add_table_element    | ALTER TABLE qname ALTER alter_table_element    | ALTER TABLE qname DROP drop_table_element    | ALTER TABLE qname SET READ ONLY    | ALTER USER ident passwd_schema    | ALTER USER ident RENAME TO ident    | ALTER USER SET      [ ENCRYPTED | UNENCRYPTED ] PASSWORD string USING OLD PASSWORD string    | ALTER SEQUENCE name      [ RESTART [ WITH [ poslng | subqeury ] ] ]      [ INCREMENT BY nonzerolng ]      [ MINVALUE nonzerolng | NO MINVALUE ]      [ MAXVALUE nonzerolng | NO MAXVALUE ]      [ CACHE nonzerolng ]      [ [ NO ] CYCLE ]    | ALTER SEQUENCE qname opt_alt_seq_params

 

alter_table_element: [ COLUMN ] ident SET [ NOT ] NULL | [ COLUMN ] ident SET DEFAULT [ default_value ]

 

drop_table_element: [ COLUMN ] ident [ RESTRICT | CASCADE ] | CONSTRAINT ident drop_action

 

constraint_type:      UNIQUE '(' column_name ',' ...')'    | PRIMARY KEY '('column_name ',' ...')'    | FOREIGN KEY '('column_name ','...')' PEFERENCES table_name ['('column_name','...')']       [MATCH [ FULL | PARTIAL | SIMPLE ] ] [ ref_action ]    | CHECK '(' search_condition ')'

 

ref_action: ON {DELETE | UPDATE} {NO ACTION | CASCADE | RESTRICT | SET NULL | SET DEFAULT}

 

passwd_schema: SET SCHEMA ident | WITH [ ENCRYPTED | UNENCRYPTED ] PASSWORD string [ SET SCHEMA ident ]

Drop statement

The objects created can be removed provided the authorization permissions are set. Note: If you do not specify the full signature of the function the DROP query will successfully execute if there is only one function with this name, if not the query is aborted. The DROP ALL is used to drop all the functions with the name specified in the query.

drop_statement:      DROP TABLE qname [ RESTRICT | CASCADE ]    | DROP FUNCTION qname '(' [ data_type ','... ] ')' [ RESTRICT | CASCADE ]    | DROP PROCEDURE qname '(' [ data_type ','... ] ')' [ RESTRICT | CASCADE ]    | DROP ALL FUNCTION qname [ RESTRICT | CASCADE ]    | DROP ALL PROCEDURE qname [ RESTRICT | CASCADE ]    | DROP VIEW qname [ RESTRICT | CASCADE ]    | DROP ROLE qname    | DROP USER ident    | DROP INDEX qname    | DROP CLUSTER qname    | DROP TRIGGER qname    | DROP SEQUENCE qname

 

Schemas

The scheme operations follow the standard definition.

schema:    CREATE SCHEMA schema_name_clause   [ DEFAULT CHARACTER SET ident ]   [ PATH schema_name ','... ]   [ schema_element ... ] | DROP SCHEMA qname drop_action schema_name_clause: ident | [ ident ] AUTHORIZATION ident schema_element: grant | revoke | create_statement | drop_statement | alter_statement

Table updates

Data insertions

A table can be populated using an insert statement. It takes a table name and a value expression list. The expression result types should align with the attributes in the table definition. Otherwise the attribute-value association should be explicitly defined using a column name list. Multiple rows can be inserted in a single statement.

The result of a query can be inserted into a table, provided both source and destination are type compatible. Insertion of a table into itself effectively doubles its content, provided non of the table constraints is violated. MonetDB/SQL doest not support inserts through view definitions.

INSERT INTO table_name [ '(' column_name ',' ... ')' ] [ VALUES '('value_expression','...')' ',' ... | select_query ]

Data updates

The update statement syntax follows the SQL standard, but its semantics for bulk updates on keys may be slightly different than expected from other systems. In particular, the update implementation ensures that you can freely update any column without the danger of run-away values. MonetDB/SQL doest not support updates through views.

UPDATE table_name SET assignment ',' ... [WHERE search_condition ] assignment : column_name '='  ( scalar_exp  | search_condition | NULL )

 

Data deletions

DELETE FROM table_name [WHERE search_condition ]

MonetDB/SQL doest not support deletions through views.

Table expressions

When a query is created a table can be referenced in different ways, sometimes by its name or by a select query or a join result. Here is the syntax to refer a table.

table_reference: simple_table     | '(' select_query ')' [AS] table_name [ '(' column_name ',' ... ')' ]     | '(' select_query ')'     | joined_table | '(' joined_table ')' [AS] table_name [ '(' column_name ',' ... ')' ] joined_table: '(' joined_table ')'     | table_reference { CROSS | NATURAL } JOIN table_reference     | table_reference { JOIN | UNIONJOIN} table_reference join_spec     | table_reference join_type JOIN table_reference join_spec     | table_reference NATURAL join_type JOIN table_reference join_spec : ON search_condition | USING '(colunm_name ',' ...' )' join_type: INNER | { LEFT | RIGHT | FULL } [OUTER]

The SQL framework for table expressions is based on the select-from-where construct.

select_query:     SELECT [ ALL | DISTINCT ]  ( '*' | column_name ',' ... ) [ INTO column_name ',' ... ]     [ FROM table_name ',' ... ]     [ WHERE search_condition ]     [ GROUP BY column_name ',' ... ]     [ HAVING search_condition]     [ ORDER BY ordering_spec ',' ... ]     [ LIMIT posint ] [ OFFSET posint ]

The with clause provides the mechanism to introduce in-line view definitions:

with_clause :  WITH with_element ',' ... select_query with_element: table_name '(' column_name ',' ... ')' AS '(' select_query ')'

The pattern matching operations are used over predicates. It is possible to compare them, see the differences between them, if a predicate is a sub-predicate of another, etc. The following syntax description cover all the operations supported by MonetDB.

predicate:     comparison_predicate     | between_predicate     | like_predicate     | test_for_null     | in_predicate     | all_or_any_predicate     | existence_test     | '(' predicate ')' comparison_predicate:     pred_expr COMPARISON pred_expr     | pred_expr = pred_exp between_predicate:     pred_expr [NOT] BETWEEN [SYMMETRIC | ASYMMETRIC] pred_expr AND pred_expr like_predicate:     pred_expr [NOT] LIKE atom_expr test_for_null:     column_name IS [NOT] NULL in_predicate:     pred_expr [NOT] IN '(' element_name ',' ... ')' all_or_any_predicate :     pred_expr COMPARISON { ANY | ALL | SOME } subquery existence_test:     [NOT] EXISTS subquery pred_expr:     scalar_expr     | predicate atom_expr:     ['('] atom [')']     | ['('] atom [')'] ESCAPE string     | '?'

string_funcs:       SUBSTRING ( scalar_expr FROM scalar_expr FOR scalar_expr ')'     | SUBSTRING '(' scalar_expr ',' scalar_expr ',' scalar_expr ')'     | SUBSTRING '(' scalar_exprFROM scalar_expr ')'     | SUBSTRING '(' scalar_expr ',' scalar_expr ')'     | SUBSTRING '(' scalar_expr int_value scalar_expr ')'     | scalar_expr '||' scalar_expr

 

Bulk input/output

The COPY INTO command enables fast insertion of multiple tuples from an text file or standard input. Each tuple  in the input file is terminated by a record-separator (default '\n') and fields are separated by the field separator (default '|'). The field values should adhere to the  syntax for value literals. Alternative separators should adhere to the SQL lexical syntax for string values. A different NULL value representation can be passed using the NULL as null_string option. Furthermore, the fields are optionally enclosed with a user defined quote character. The text file should use UTF-8 encoding if specified as file_name, and the same encoding as mclient is using if read using FROM STDIN.

It is strongly advised to announce the maximum number of records to be inserted. It avoids guessing by the server and subsequent re-allocation of table space which may involve potentially expensive copying. A portion of the input file can be skipped using the offset feature.  The offset value specifies the record at which loading should commence, the first record having offset 1.

The file read from should be accessible by the server and must use an absolute path name. The STDIN file designator reads data from the client application.

COPY [ int_val [ OFFSET int_val ] RECORDS ] INTO table_name       FROM ( file_name ',' ... | STDIN )       [ [USING] DELIMITERS field_separator [',' record_separator [ ',' string_quote ]]] [ NULL AS null_string ] [LOCKED]

The input syntax should comply to the following grammar: [ [ [quote] [[escape]char] * [quote]] feldspar] * record separator. Quote characters in quoted fields may be escaped with a backslash. Field and record separators can be embedded in quoted fields.

In many bulk loading situations, the original file can be saved as a backup or recreated for disaster handling. This reliefs the database system from having to prepare for recovery as well and to safe significant storage space. The LOCKED qualifier can be used in this situation (and in single user mode!) to skip the logging operation normally performed.

WARNING It is advised to add integrity constraints to the table after the file has been loaded. The ALTER statements perform bulk integrity checking and perform these checks often more efficiently.

Copy into File

The COPY INTO command with a file name argument allows for fast dumping of a result set into an ASCII file. The file should be accessible by the server and a full path name may be required. The file STDOUT can be used to direct the result to the primary output channel.

The delimiters and NULL AS arguments provide control over the layout required.  

COPY subquery INTO file_name [ [USING] DELIMITERS field_separator [',' record_separator [ ',' string_quote ]]] [ NULL AS null_string ]

For both the input and output version of the COPY INTO command one can specify a file name ending with 'gz' or 'bz2'  to use the appropriate compression library (if available).

Copying binary files

Migration of tables between MonetDB/SQL instances can be sped up using the binary COPY INTO/FROM format. See  the recipe for this functionality.

 

 

Procedure definition

func_def:      CREATE PROCEDURE qname '(' [ paramlist] ')' EXTERNAL NAME external_function_name      | CREATE PROCEDURE qname '(' [ paramlist] ')' routine_body routine_body:       procedure_statement      | BEGIN procedure_statement_list END      | BEGIN ATOMIC procedure_statement_list END procedure_statement:        transaction_statement      | update_statement      | schema      | grant      | revoke      | create_statement      | drop_statement      | alter_statement      | declare_statement      | set_statement      | control_statement

Function definitions

The SQL standard allows to create SQL functions. MonetDB supports this feature. The syntax to create a function is:

func_def:      FUNCTION qname '(' [ paramlist] ')' RETURNS func_data_type EXTERNAL NAME external_function_name    | FUNCTION qname '(' [ paramlist ] ')' RETURNS func_data_type routine_body    | AGGREGATE qname '(' [ paramlist ] ')' RETURNS func_data_type EXTERNAL NAME external_function_name func_data_type:       TABLE '(' column data_type ','... ')'     | data_type type_def: TYPE qname EXTERNAL NAME ident

External functions provide a convenient bridge between SQL and MAL. This way also a bridge can be established with dynamically loaded functions written in C. Any SQL function signature can be bound to MAL function or command.

The example below illustrates a binding to the a function that produces a tabular overview of the BAT catalog.

     CREATE FUNCTION bbp ()
         RETURNS TABLE (id int, name string, htype string,
             ttype string, count BIGINT, refcnt int, lrefcnt int,
             location string, heat int, dirty string, status string,
             kind string)
                 EXTERNAL NAME sql.bbp;

A table returning function should be implemented as MAL function that returns a series of synchronized BATs.

     function bbp():bat[:str,:bat];
         b := bat.new(:str,:bat,12);
         ns := bbp.getNames();
         ri := algebra.markT(ns, 0:oid);
     	...
     	kind := algebra.markH(ikind, 0:oid);
         bat.insert(b, "kind", kind);
         return b;
     end bbp;

Two useful Microsoft functions supported are 'STUFF' and 'ROUND'. 

     	SQL> SELECT MS_ROUND(10.0, 1, 0);

The SQL definition for MS_ROUND is:

     CREATE FUNCTION MS_ROUND(num float, precision int, truncat int)
     RETURNS float
     BEGIN
     	IF (truncat = 0)
     		THEN RETURN ROUND(num, precision);
     		ELSE RETURN TRUNCATE(num, precision);
     	END IF;
     END;

The SQL definition for MS_STUFF is:

     CREATE FUNCTION MS_STUFF( s1 varchar(32), st int, len int, s3 varchar(32))
     RETURNS varchar(32)
     BEGIN
     	DECLARE res varchar(32), aux varchar(32);
     	DECLARE ofset int;
     	SET ofset = 0;
     	SET res = SUBSTRING(s1,ofset,st-1);
     	SET res = res || s3;
     	SET ofset = LENGTH(s1)-len;
     	SET aux = SUBSTRING(s1,ofset, len);
     	SET res = res || aux;
     	RETURN res;
     END;

 

Trigger definitions

Triggers are a convenient programming abstraction. They are activated at transaction commit based on updates to the base tables.

trigger_def: TRIGGER qname trigger_action_time trigger_event ON ident opt_referencing_list triggered_action trigger_action_time: BEFORE | AFTER trigger_event: INSERT | DELETE | UPDATE | UPDATE OF ident ','... opt_referencing_list: [ REFERENCING old_or_new_values_alias ... ] old_or_new_values_alias:       OLD [ ROW ] [ AS ] ident | NEW [ ROW ] [ AS ] ident     | OLD TABLE [ AS ] ident | NEW TABLE [ AS ] ident triggered_action: opt_for_each  [ WHEN search_condition ] triggered_statement opt_for_each: /* default for each statement */ | FOR EACH ROW | FOR EACH STATEMENT triggered_statement:      trigger_procedure_statement    | BEGIN ATOMIC trigger_procedure_statement_list END trigger_procedure_statement: transaction_statement | update_statement | grant | revoke | declare_statement | set_statement | control_statement | select_statement_single_row

Example The following example provides a glimpse of their functionality:

     create table t1 (id int, name varchar(1024));
     
     --test FOR EACH STATEMENT (default one)
     insert into t1 values(10, 'monetdb');
     insert into t1 values(20, 'monet');
     create trigger test5
             after update on t1
             for each statement
             when id >0 insert into t1 values(4, 'update_when_statement_true');

All trigger definitions are considered together at the transaction commit. There is no a priori defined order in which they run. Each may in turn activate new triggers, but each trigger definition is also executed only once per transaction commit.

Variables

MonetDB/SQL supports session variables declared by the user. They are indistinguishable from table and column names and can be used anywhere a literal constant is allowed.

declare_statement:      DECLARE    | DECLARE table_def variable_list:      ident ','... data_type    | variable_list ',' ident ','... data_type set_statement:      SET ident '=' simple_atom    | SET SESSION AUTHORIZATION ident    | SET SCHEMA ident    | SET user '=' ident    | SET ROLE ident    | SET TIME ZONE LOCAL    | SET TIME ZONE interval_expression user: [ USER | SESSION_USER | CURRENT_USER ]

 

Examples

     sql>DECLARE high integer;
     sql>DECLARE n varchar(256);
     sql>SET high=4000;
     sql>SET n='monetdb';
     sql>SET trace = 'ticks,stmt';
     sql>SELECT count(*) from tables where id > high;
     +--------+
     | count_ |
     +========+
     |      2 |
     +--------+

The SQL variables (and environment variables) can be accessed through predefined table returning functions var() and env().The debug variable settings are defined in the MonetDB config file. The current_* variables are SQL environment settings. The trace variables is defined in the TRACE command. History denotes a boolean to log all queries.

sql>select * from var();
+------------------+
| name             |
+==================+
| debug            |
| current_schema   |
| current_user     |
| current_role     |
| optimizer        |
| trace            |
| current_timezone |
| cache            |
| history          |
+------------------+

 

Flow of control

The persistent stored module facility of SQL provides a method to encode complex algorithm using well-known programming features.

control_statement:      CALL func_ref    | CALL routine_name '(' argument_list ')'    | while_statement    | if_statement    | case_statement    | RETURN return_value case_statement:     CASE scalar_exp when_statement... [ ELSE procedure_statement_list ] END CASE   | CASE when_search_statement... [ ELSE procedure_statement_list ] END CASE when_statement: WHEN scalar_exp THEN procedure_statement_list when_search_statement: WHEN search_condition THEN procedure_statement_list if_statement: IF search_condition THEN procedure_statement_list if_opt_else END IF if_opt_else:       /* empty */    | ELSE procedure_statement_list    | ELSEIF search_condition THEN procedure_statement_list if_opt_else while_statement:     [ ident ':' ] WHILE search_condition DO procedure_statement_list END WHILE [ ident ] return_statement: RETURN return_value return_value:       select_no_parens_orderby     | search_condition     | TABLE '(' select_no_parens_orderby ')' | NULL

EXPLAIN SQL STMT

The intermediate code produced by the SQL compiler can be made visible using the explain statement modifier. It gives a detailed description of the actions taken to produce the answer.  The example below illustrates what you can expect when a simple query is prepended by the explain modifier.The output strongly depends on the optimizer pipeline. The details of this program are better understood when you have read the MAL  reference.

sql>select count(*) from tables;
+--------+
| count_ |
+========+
|     27 |
+--------+

sql>explain select count(*) from tables;
+---------------------------------------------------------------------------------------------+
| mal                                                                                         |
+=============================================================================================+
| function user.s3_2{autoCommit=true}():void;                                                 |
|     _2 := sql.mvc();                                                                        |
| barrier _143 := language.dataflow();                                                        |
|     _23:bat[:oid,:sht]  := sql.bind(_2,"sys","_tables","type",1);                           |
|     _24 := algebra.thetauselect(_23,2:sht,"<");                                             |
|     _25:bat[:oid,:oid]  := sql.bind_dbat(_2,"sys","_tables",1);                             |
|     _27 := bat.reverse(_25);                                                                |
|     _97 := algebra.kdifference(_24,_27);                                                    |
|     _110 := algebra.markT(_97,5,4);                                                         |
|     _117 := bat.reverse(_110);                                                              |
|     _30:bat[:oid,:int]  := sql.bind(_2,"sys","_tables","id",1);                             |
|     _142 := algebra.leftjoin(_117,_30);                                                     |
|     _64:bat[:oid,:sht]  := sql.bind(_2,"sys","_tables","type",0,27@0,nil:oid);              |
|     _72 := algebra.thetauselect(_64,2:sht,"<");                                             |
|     _20:bat[:oid,:sht]  := sql.bind(_2,"sys","_tables","type",2);                           |
|     _76 := algebra.kdifference(_72,_20);                                                    |
|     _22 := algebra.thetauselect(_20,2:sht,"<");                                             |
|     _80 := algebra.semijoin(_22,_64);                                                       |
|     _85 := algebra.kunion(_76,_80);                                                         |
...
|     _13 := algebra.kdifference(_8,_12);                                                     |
|     _14 := algebra.markT(_13,0@0);                                                          |
|     _15 := bat.reverse(_14);                                                                |
|     _16:bat[:oid,:int]  := sql.bind(_2,"tmp","_tables","id",0);                             |
|     _18 := algebra.leftjoin(_15,_16);                                                       |
| exit _143;                                                                                  |
|     _33:bat[:oid,:int]  := bat.new(nil:oid,nil:int);                                        |
| barrier _146 := language.dataflow();                                                        |
|     _32 := mat.pack(_134,_136,_138,_140,_142);                                              |
|     _36 := bat.append(_33,_32,true);                                                        |
|     _38 := bat.append(_36,_18,true);                                                        |
|     _39 := aggr.count(_38);                                                                 |
| exit _146;                                                                                  |
|     sql.exportValue(1,".tables","L6","wrd",64,0,6,_39,"");                                  |
| end s3_2;                                                                                   |
+---------------------------------------------------------------------------------------------+
86 tuples
sql>

The SQL compiler maintains a cache of compiled queries. Each query is looked up in this cache based on an expression pattern match where the constants may take on different values. If it doesn't exist, the query is converted into a code block and stored in the module user.s0.

The call to the cached function is included in a wrapper function main, which is the only piece of code produced if the query is used more than once. The query cache disappears when the server is brought to a halt.

     +----------------------------+
     | function user.main():void; |
     |     mdb.start();           |
     |     user.s3_2();           |
     |     mdb.stop();            |
     | end main;                  |
     +----------------------------+

TRACE SQL STMT

A performance trace can be obtained using the TRACE statement modifier. It collects all the trace information in a table reproduced by tracelog(), which can be queried using ordinary SQL statements. The internal trace table is reset at each query being traced. Its definition is given below:

create function tracelog()
    returns table (
        event integer,      -- event counter
        clk varchar(20),    -- wallclock, no mtime in kernel
        pc varchar(50),     -- module.function[nr]
        thread int,         -- thread identifier
        "user" int,             -- client identifier
        ticks integer,      -- time in microseconds
        reads integer,      -- number of blocks read
        writes integer,     -- number of blocks written
        rbytes integer,     -- amount of bytes touched
        wbytes integer,     -- amount of bytes written
        type string,        -- return types
        stmt string         -- actual statement executed
    )
    external name sql.dump_trace;

 

 

DEBUG SQL STMT

The SQL statements are translated into MAL programs, which are optimized and stored away in an user module. The generated code can be inspected with the MAL debugger.

The example below illustrates the start of such a session:

>debug select count(*) from tables;
#    mdb.start()
mdb>next
#    user.s1_0()
mdb>next
#    _2:bat[:oid,:int]  := sql.bind(_3="sys", _4="ptables", _5="id", _6=0)
mdb>next
#    _8:bat[:oid,:int]  := sql.bind(_3="sys", _4="ptables", _5="id", _9=1)
mdb> ...

Mapi Library

The Mapi Library

The easiest way to extend the functionality of MonetDB is to construct an independent application, which communicates with a running server using a database driver with a simple API and a textual protocol. The effectiveness of such an approach has been demonstrated by the wide use of database API implementations, such as Perl DBI, PHP, ODBC,...

Sample MAPI Application

The database driver implementation given in this document focuses on developing applications in C. The command collection has been chosen to align with common practice, i.e. queries follow a prepare, execute, and fetch_row paradigm. The output is considered a regular table. An example of a mini application below illustrates the main operations.

     #include <mapi.h>
     #include <stdio.h>
     #include <stdlib.h>
     
     void die(Mapi dbh, MapiHdl hdl)
     {
     	if (hdl != NULL) {
     		mapi_explain_query(hdl, stderr);
     		do {
     			if (mapi_result_error(hdl) != NULL)
     				mapi_explain_result(hdl, stderr);
     		} while (mapi_next_result(hdl) == 1);
     		mapi_close_handle(hdl);
     		mapi_destroy(dbh);
     	} else if (dbh != NULL) {
     		mapi_explain(dbh, stderr);
     		mapi_destroy(dbh);
     	} else {
     		fprintf(stderr, "command failed\n");
     	}
     	exit(-1);
     }
     
     MapiHdl query(Mapi dbh, char *q)
     {
     	MapiHdl ret = NULL;
     	if ((ret = mapi_query(dbh, q)) == NULL || mapi_error(dbh) != MOK)
     		die(dbh, ret);
     	return(ret);
     }
     
     void update(Mapi dbh, char *q)
     {
     	MapiHdl ret = query(dbh, q);
     	if (mapi_close_handle(ret) != MOK)
     		die(dbh, ret);
     }
     
     int main(int argc, char *argv[])
     {
         Mapi dbh;
         MapiHdl hdl = NULL;
         char *name;
         char *age;
     
         dbh = mapi_connect("localhost", 50000, "monetdb", "monetdb", "sql", "demo");
         if (mapi_error(dbh))
             die(dbh, hdl);
     
         update(dbh, "CREATE TABLE emp (name VARCHAR(20), age INT)");
         update(dbh, "INSERT INTO emp VALUES ('John', 23)");
         update(dbh, "INSERT INTO emp VALUES ('Mary', 22)");
     
         hdl = query(dbh, "SELECT * FROM emp");
     
         while (mapi_fetch_row(hdl)) {
             name = mapi_fetch_field(hdl, 0);
             age = mapi_fetch_field(hdl, 1);
             printf("%s is %s\n", name, age);
         }
     
         mapi_close_handle(hdl);
         mapi_destroy(dbh);
     
         return(0);
     }

The mapi_connect() operation establishes a communication channel with a running server. The query language interface is either "sql" or "mal".

Errors on the interaction can be captured using mapi_error(), possibly followed by a request to dump a short error message explanation on a standard file location. It has been abstracted away in a function.

Provided we can establish a connection, the interaction proceeds as in many similar application development packages. Queries are shipped for execution using mapi_query() and an answer table can be consumed one row at a time. In many cases these functions suffice.

The Mapi interface provides caching of rows at the client side. mapi_query() will load tuples into the cache, after which they can be read repeatedly using mapi_fetch_row() or directly accessed (mapi_seek_row()). This facility is particularly handy when small, but stable query results are repeatedly used in the client program.

To ease communication between application code and the cache entries, the user can bind the C-variables both for input and output to the query parameters, and output columns, respectively. The query parameters are indicated by '?' and may appear anywhere in the query template.

The Mapi library expects complete lines from the server as answers to query actions. Incomplete lines leads to Mapi waiting forever on the server. Thus formatted printing is discouraged in favor of tabular printing as offered by the table.print() commands.

The following action is needed to get a working program. Compilation of the application relies on libtool and the pkg-config programs that should be shipped with your installation.  The application above can be compiled and linked as follows:

     % libtool --mode=compile --tag=CC gcc -c `env PKG_CONFIG_PATH=$INSTALL_DIR/lib/pkgconfig pkg-config --cflags monetdb-mapi` test.c
     % libtool --mode=link --tag=CC gcc -o test `env PKG_CONFIG_PATH=$INSTALL_DIR/lib/pkgconfig pkg-config --libs monetdb-mapi` test.o
     % ./test

The example assumes you have set the variable INSTALL_DIR to the prefix location given during configure of MonetDB.  If you use a system installation, you can omit the 'env PKGCONFIG_PATH=.....' part, or set INSTALL_DIR to '/usr'.

The compilation on Windows is slightly more complicated. It requires more attention towards the location of the include files and libraries.

Command Summary

The quick reference guide to the Mapi library is given below. More details on their constraints and defaults are given in the next section.

mapi_bind()	Bind string C-variable to a field
mapi_bind_numeric()	Bind numeric C-variable to field
mapi_bind_var()	Bind typed C-variable to a field
mapi_cache_freeup()	Forcefully shuffle fraction for cache refreshment
mapi_cache_limit()	Set the tuple cache limit
mapi_cache_shuffle()	Set shuffle fraction for cache refreshment
mapi_clear_bindings()	Clear all field bindings
mapi_clear_params()	Clear all parameter bindings
mapi_close_handle()	Close query handle and free resources
mapi_connect()	Connect to a Mserver
mapi_destroy()	Free handle resources
mapi_disconnect()	Disconnect from server
mapi_error()	Test for error occurrence
mapi_execute()	Execute a query
mapi_execute_array()	Execute a query using string arguments
mapi_explain()	Display error message and context on stream
mapi_explain_query()	Display error message and context on stream
mapi_fetch_all_rows()	Fetch all answers from server into cache
mapi_fetch_field()	Fetch a field from the current row
mapi_fetch_field_len()	Fetch the length of a field from the current row
mapi_fetch_field_array()	Fetch all fields from the current row
mapi_fetch_line()	Retrieve the next line
mapi_fetch_reset()	Set the cache reader to the beginning
mapi_fetch_row()	Fetch row of values
mapi_finish()	Terminate the current query
mapi_get_dbname()	Database being served
mapi_get_field_count()	Number of fields in current row
mapi_get_host()	Host name of server
mapi_get_query()	Query being executed
mapi_get_language()	Query language name
mapi_get_mapi_version()	Mapi version name
mapi_get_monet_versionId()	MonetDB version identifier
mapi_get_monet_version()	MonetDB version name
mapi_get_motd()	Get server welcome message
mapi_get_row_count()	Number of rows in cache or -1
mapi_get_last_id()	last inserted id of an auto_increment (or alike) column
mapi_get_from()	Get the stream 'from'
mapi_get_to()	Get the stream 'to'
mapi_get_trace()	Get trace flag
mapi_get_user()	Current user name
mapi_log()	Keep log of client/server interaction
mapi_next_result()	Go to next result set
mapi_needmore()	Return whether more data is needed
mapi_ping()	Test server for accessibility
mapi_prepare()	Prepare a query for execution
mapi_prepare_array()	Prepare a query for execution using arguments
mapi_query()	Send a query for execution
mapi_query_array()	Send a query for execution with arguments
mapi_query_handle()	Send a query for execution
mapi_quick_query_array()	Send a query for execution with arguments
mapi_quick_query()	Send a query for execution
mapi_quick_response()	Quick pass response to stream
mapi_quote()	Escape characters
mapi_reconnect()	Reconnect with a clean session context
mapi_rows_affected()	Obtain number of rows changed
mapi_seek_row()	Move row reader to specific location in cache
mapi_setAutocommit()	Set auto-commit flag
mapi_setAlgebra()	Use algebra backend
mapi_stream_query()	Send query and prepare for reading tuple stream
mapi_table()	Get current table name
mapi_timeout()	Set timeout for long-running queries[TODO]
mapi_output()	Set output format
mapi_stream_into()	Stream document into server
mapi_profile()	Set profile flag
mapi_trace()	Set trace flag
mapi_virtual_result()	Submit a virtual result set
mapi_unquote()	remove escaped characters

Library Synopsis

The routines to build a MonetDB application are grouped in the library MonetDB Programming Interface, or shorthand Mapi.

The protocol information is stored in a Mapi interface descriptor (mid). This descriptor can be used to ship queries, which return a MapiHdl to represent the query answer. The application can set up several channels with the same or a different mserver. It is the programmer's responsibility not to mix the descriptors in retrieving the results.

The application may be multi-threaded as long as the user respects the individual connections represented by the database handlers.

The interface assumes a cautious user, who understands and has experience with the query or programming language model. It should also be clear that references returned by the API point directly into the administrative structures of Mapi. This means that they are valid only for a short period, mostly between successive mapi_fetch_row() commands. It also means that it the values are to retained, they have to be copied. A defensive programming style is advised.

Upon an error, the routines mapi_explain() and mapi_explain_query() give information about the context of the failed call, including the expression shipped and any response received. The side-effect is clearing the error status.

Error Message

Almost every call can fail since the connection with the database server can fail at any time. Functions that return a handle (either Mapi or MapiHdl) may return NULL on failure, or they may return the handle with the error flag set. If the function returns a non-NULL handle, always check for errors with mapi_error.

Functions that return MapiMsg indicate success and failure with the following codes.

 

MOK	No error
MERROR	Mapi internal error.
MTIMEOUT	Error communicating with the server.

 

When these functions return MERROR or MTIMEOUT, an explanation of the error can be had by calling one of the functions mapi_error_str(), mapi_explain(), or mapi_explain_query().

To check for error messages from the server, call mapi_result_error(). This function returns NULL if there was no error, or the error message if there was. A user-friendly message can be printed using map_explain_result(). Typical usage is:

do {
    if ((error = mapi_result_error(hdl)) != NULL)
        mapi_explain_result(hdl, stderr);
    while ((line = mapi_fetch_line(hdl)) != NULL)
        /* use output */;
} while (mapi_next_result(hdl) == 1);

Mapi Function Reference

Connecting and Disconnecting

• Mapi mapi_connect(const char *host, int port, const char *username, const char *password, const char *lang, const char *dbname)

  Setup a connection with a Mserver at a host:port and login with username and password. If host == NULL, the local host is accessed. If host starts with a '/' and the system supports it, host is actually the name of a UNIX domain socket, and port is ignored. If port == 0, a default port is used. If username == NULL, the username of the owner of the client application containing the Mapi code is used. If password == NULL, the password is omitted. The preferred query language is any of {sql,mil,mal,xquery }. On success, the function returns a pointer to a structure with administration about the connection.

• MapiMsg mapi_disconnect(Mapi mid)

  Terminate the session described by mid. The only possible uses of the handle after this call is mapi_destroy() and mapi_reconnect(). Other uses lead to failure.

• MapiMsg mapi_destroy(Mapi mid)

  Terminate the session described by mid if not already done so, and free all resources. The handle cannot be used anymore.

• MapiMsg mapi_reconnect(Mapi mid)

  Close the current channel (if still open) and re-establish a fresh connection. This will remove all global session variables.

• MapiMsg mapi_ping(Mapi mid)

  Test availability of the server. Returns zero upon success.

Sending Queries

• MapiHdl mapi_query(Mapi mid, const char *Command)

  Send the Command to the database server represented by mid. This function returns a query handle with which the results of the query can be retrieved. The handle should be closed with mapi_close_handle(). The command response is buffered for consumption, c.f. mapi_fetch_row().

• MapiMsg mapi_query_handle(MapiHdl hdl, const char *Command)

  Send the Command to the database server represented by hdl, reusing the handle from a previous query. If Command is zero it takes the last query string kept around. The command response is buffered for consumption, e.g. mapi_fetch_row().

• MapiHdl mapi_query_array(Mapi mid, const char *Command, char **argv)

  Send the Command to the database server replacing the placeholders (?) by the string arguments presented.

• MapiHdl mapi_quick_query(Mapi mid, const char *Command, FILE *fd)

  Similar to mapi_query(), except that the response of the server is copied immediately to the file indicated.

• MapiHdl mapi_quick_query_array(Mapi mid, const char *Command, char **argv, FILE *fd)

  Similar to mapi_query_array(), except that the response of the server is not analyzed, but shipped immediately to the file indicated.

• MapiHdl mapi_stream_query(Mapi mid, const char *Command, int windowsize)

  Send the request for processing and fetch a limited number of tuples (determined by the window size) to assess any erroneous situation. Thereafter, prepare for continual reading of tuples from the stream, until an error occurs. Each time a tuple arrives, the cache is shifted one.

• MapiHdl mapi_prepare(Mapi mid, const char *Command)

  Move the query to a newly allocated query handle (which is returned). Possibly interact with the back-end to prepare the query for execution.

• MapiMsg mapi_execute(MapiHdl hdl)

  Ship a previously prepared command to the backend for execution. A single answer is pre-fetched to detect any runtime error. MOK is returned upon success.

• MapiMsg mapi_execute_array(MapiHdl hdl, char **argv)

  Similar to mapi_execute but replacing the placeholders for the string values provided.

• MapiMsg mapi_finish(MapiHdl hdl)

  Terminate a query. This routine is used in the rare cases that consumption of the tuple stream produced should be prematurely terminated. It is automatically called when a new query using the same query handle is shipped to the database and when the query handle is closed with mapi_close_handle().

• MapiMsg mapi_virtual_result(MapiHdl hdl, int columns, const char **columnnames, const char **columntypes, const int *columnlengths, int tuplecount, const char ***tuples)

  Submit a table of results to the library that can then subsequently be accessed as if it came from the server. columns is the number of columns of the result set and must be greater than zero. columnnames is a list of pointers to strings giving the names of the individual columns. Each pointer may be NULL and columnnames may be NULL if there are no names. tuplecount is the length (number of rows) of the result set. If tuplecount is less than zero, the number of rows is determined by a NULL pointer in the list of tuples pointers. tuples is a list of pointers to row values. Each row value is a list of pointers to strings giving the individual results. If one of these pointers is NULL it indicates a NULL/nil value.

Getting Results

• int mapi_get_field_count(MapiHdl mid)

  Return the number of fields in the current row.

• mapi_int64 mapi_get_row_count(MapiHdl mid)

  If possible, return the number of rows in the last select call. A -1 is returned if this information is not available.

• mapi_int64 mapi_get_last_id(MapiHdl mid)

  If possible, return the last inserted id of auto_increment (or alike) column. A -1 is returned if this information is not available. We restrict this to single row inserts and one auto_increment column per table. If the restrictions do not hold, the result is unspecified.

• mapi_int64 mapi_rows_affected(MapiHdl hdl)

  Return the number of rows affected by a database update command such as SQL's INSERT/DELETE/UPDATE statements.

• int mapi_fetch_row(MapiHdl hdl)

  Retrieve a row from the server. The text retrieved is kept around in a buffer linked with the query handle from which selective fields can be extracted. It returns the number of fields recognized. A zero is returned upon encountering end of sequence or error. This can be analyzed in using mapi_error().

• mapi_int64 mapi_fetch_all_rows(MapiHdl hdl)

  All rows are cached at the client side first. Subsequent calls to mapi_fetch_row() will take the row from the cache. The number or rows cached is returned.

• int mapi_quick_response(MapiHdl hdl, FILE *fd)

  Read the answer to a query and pass the results verbatim to a stream. The result is not analyzed or cached.

• MapiMsg mapi_seek_row(MapiHdl hdl, mapi_int64 rownr, int whence)

  Reset the row pointer to the requested row number. If whence is MAPI_SEEK_SET, rownr is the absolute row number (0 being the first row); if whence is MAPI_SEEK_CUR, rownr is relative to the current row; if whence is MAPI_SEEK_END, rownr is relative to the last row.

• MapiMsg mapi_fetch_reset(MapiHdl hdl)

  Reset the row pointer to the first line in the cache. This need not be a tuple. This is mostly used in combination with fetching all tuples at once.

• char **mapi_fetch_field_array(MapiHdl hdl)

  Return an array of string pointers to the individual fields. A zero is returned upon encountering end of sequence or error. This can be analyzed in using mapi_error().

• char *mapi_fetch_field(MapiHdl hdl, int fnr)

  Return a pointer a C-string representation of the value returned. A zero is returned upon encountering an error or when the database value is NULL; this can be analyzed in using mapi_error().

• size_t mapi_fetch_fiels_len(MapiHdl hdl, int fnr)

  Return the length of the C-string representation excluding trailing NULL byte of the value. Zero is returned upon encountering an error, when the database value is NULL, of when the string is the empty string. This can be analyzed by using mapi_error() and mapi_fetch_field().

• MapiMsg mapi_next_result(MapiHdl hdl)

  Go to the next result set, discarding the rest of the output of the current result set.

Errors handling

• MapiMsg mapi_error(Mapi mid)

  Return the last error code or 0 if there is no error.

• char *mapi_error_str(Mapi mid)

  Return a pointer to the last error message.

• char *mapi_result_error(MapiHdl hdl)

  Return a pointer to the last error message from the server.

• MapiMsg mapi_explain(Mapi mid, FILE *fd)

  Write the error message obtained from mserver to a file.

• MapiMsg mapi_explain_query(MapiHdl hdl, FILE *fd)

  Write the error message obtained from mserver to a file.

• MapiMsg mapi_explain_result(MapiHdl hdl, FILE *fd)

  Write the error message obtained from mserver to a file.

Parameters

• MapiMsg mapi_bind(MapiHdl hdl, int fldnr, char **val)

  Bind a string variable with a field in the return table. Upon a successful subsequent mapi_fetch_row() the indicated field is stored in the space pointed to by val. Returns an error if the field identified does not exist.

• MapiMsg mapi_bind_var(MapiHdl hdl, int fldnr, int type, void *val)

  Bind a variable to a field in the return table. Upon a successful subsequent mapi_fetch_row(), the indicated field is converted to the given type and stored in the space pointed to by val. The types recognized are { MAPI_TINY, MAPI_UTINY, MAPI_SHORT, MAPI_USHORT, MAPI_INT, MAPI_UINT, MAPI_LONG, MAPI_ULONG, MAPI_LONGLONG, MAPI_ULONGLONG, MAPI_CHAR, MAPI_VARCHAR, MAPI_FLOAT, MAPI_DOUBLE, MAPI_DATE, MAPI_TIME, MAPI_DATETIME }. The binding operations should be performed after the mapi_execute command. Subsequently all rows being fetched also involve delivery of the field values in the C-variables using proper conversion. For variable length strings a pointer is set into the cache.

• MapiMsg mapi_bind_numeric(MapiHdl hdl, int fldnr, int scale, int precision, void *val)

  Bind to a numeric variable, internally represented by MAPI_INT Describe the location of a numeric parameter in a query template.

• MapiMsg mapi_clear_bindings(MapiHdl hdl)

  Clear all field bindings.

• MapiMsg mapi_param(MapiHdl hdl, int fldnr, char **val)

  Bind a string variable with the n-th placeholder in the query template. No conversion takes place.

• MapiMsg mapi_param_type(MapiHdl hdl, int fldnr, int ctype, int sqltype, void *val)

  Bind a variable whose type is described by ctype to a parameter whose type is described by sqltype.

• MapiMsg mapi_param_numeric(MapiHdl hdl, int fldnr, int scale, int precision, void *val)

  Bind to a numeric variable, internally represented by MAPI_INT.

• MapiMsg mapi_param_string(MapiHdl hdl, int fldnr, int sqltype, char *val, int *sizeptr)

  Bind a string variable, internally represented by MAPI_VARCHAR, to a parameter. The sizeptr parameter points to the length of the string pointed to by val. If sizeptr == NULL or *sizeptr == -1, the string is NULL-terminated.

• MapiMsg mapi_clear_params(MapiHdl hdl)

  Clear all parameter bindings.

Miscellaneous

• MapiMsg mapi_setAutocommit(Mapi mid, int autocommit)

  Set the autocommit flag (default is on). This only has an effect when the language is SQL. In that case, the server commits after each statement sent to the server.

• MapiMsg mapi_setAlgebra(Mapi mid, int algebra)

  Tell the backend to use or stop using the algebra-based compiler.

• MapiMsg mapi_cache_limit(Mapi mid, int maxrows)

  A limited number of tuples are pre-fetched after each execute(). If maxrows is negative, all rows will be fetched before the application is permitted to continue. Once the cache is filled, a number of tuples are shuffled to make room for new ones, but taking into account non-read elements. Filling the cache quicker than reading leads to an error.

• MapiMsg mapi_cache_shuffle(MapiHdl hdl, int percentage)

  Make room in the cache by shuffling percentage tuples out of the cache. It is sometimes handy to do so, for example, when your application is stream-based and you process each tuple as it arrives and still need a limited look-back. This percentage can be set between 0 to 100. Making shuffle= 100% (default) leads to paging behavior, while shuffle==1 leads to a sliding window over a tuple stream with 1% refreshing.

• MapiMsg mapi_cache_freeup(MapiHdl hdl, int percentage)

  Forcefully shuffle the cache making room for new rows. It ignores the read counter, so rows may be lost.

• char * mapi_quote(const char *str, int size)

  Escape special characters such as \n, \t in str with backslashes. The returned value is a newly allocated string which should be freed by the caller.

• char * mapi_unquote(const char *name)

  The reverse action of mapi_quote(), turning the database representation into a C-representation. The storage space is dynamically created and should be freed after use.

• MapiMsg mapi_output(Mapi mid, char *output)

  Set the output format for results send by the server.

• MapiMsg mapi_stream_into(Mapi mid, char *docname, char *colname, FILE *fp)

  Stream a document into the server. The name of the document is specified in docname, the collection is optionally specified in colname (if NULL, it defaults to docname), and the content of the document comes from fp.

• MapiMsg mapi_profile(Mapi mid, int flag)

  Set the profile flag to time commands send to the server.

• MapiMsg mapi_trace(Mapi mid, int flag)

  Set the trace flag to monitor interaction of the client with the library. It is primarilly used for debugging Mapi applications.

• int mapi_get_trace(Mapi mid)

  Return the current value of the trace flag.

• MapiMsg mapi_log(Mapi mid, const char *fname)

  Log the interaction between the client and server for offline inspection. Beware that the log file overwrites any previous log. For detailed interaction trace with the Mapi library itself use mapi_trace().

The remaining operations are wrappers around the data structures maintained. Note that column properties are derived from the table output returned from the server.

• char *mapi_get_name(MapiHdl hdl, int fnr)
• char *mapi_get_type(MapiHdl hdl, int fnr)
• char *mapi_get_table(MapiHdl hdl, int fnr)
• int mapi_get_len(Mapi mid, int fnr)
• char *mapi_get_dbname(Mapi mid)
• char *mapi_get_host(Mapi mid)
• char *mapi_get_user(Mapi mid)
• char *mapi_get_lang(Mapi mid)
• char *mapi_get_motd(Mapi mid)

 

JDBC Driver

MonetDB JDBC Driver

The most obvious way to connect to a data source using the Java programming language is by making use of the in Java defined JDBC framework. MonetDB has a native Java JDBC driver type 4 which allows use of the MonetDB database in a Java alike way.

It is quite difficult to have a fully complete JDBC implementation. Also this driver isn't complete in that sense. However, it is believed that the most prominent parts of the JDBC interface are implemented, and in such a way that they adhere to the specifications. If you make extensive use of JDBC semantics and rely on many of its features, please report any missing functionality on our bugzilla.

This document gives a short description how to use the MonetDB JDBC driver in Java applications. Familiarity with the Java JDBC API is required to fully understand this document. Please note that you can find the complete JDBC API on Oracle's web site http://java.oracle.com/.

In order to use the MonetDB JDBC driver in Java applications you need (of course) a running MonetDB/SQL instance, preferably via monetdbd.

Getting the driver Jar

The easiest way to acquire the driver is to download it from our download area. You will find a file called called monetdb-X.Y-jdbc.jar where X and Y are major and minor version numbers.

Compiling the driver (using ant, optional)

If you prefer to build the driver yourself, make sure you acquire the MonetDB Java repository, e.g. as part of the source downloads. The Java sources are built using Apache's Ant tool. Simply issuing the command ant distjdbc should be sufficient to build the driver jar-archive in the subdirectory jars. See the ant web site for more documentation on the ant build-tool: http://ant.apache.org/. The Java sources currently require at least a Java 6 compatible compiler. The JDBC driver, cannot be compiled any more with a Java 1.5 or earlier due to JDBC4.

Testing the driver using the JdbcClient utility

Before you start developing your programs which use the MonetDB JDBC driver it is generally a good idea to check if the driver actually works in your environment. JdbcClient is a no longer distributed, but when compling from sources, it is still built and put in the jars directory. Follow the steps below to assure your setup is complete:

1. start monetdbd
2. create a database using monetdb create mytest
3. run the JdbcClient utility using java -jar {path/to/jdbcclient.jar} -dmytest -umonetdb (with password monetdb)

The last step should give you something like this:

% java -jar jars/jdbcclient.jar -umonetdb
password:

Welcome to the MonetDB interactive JDBC terminal!
Database: MonetDB 5.0.0
Driver: MonetDB Native Driver 1.5 (Steadfast_pre4 20061124)
Type \q to quit, \h for a list of available commands
auto commit mode: on
monetdb-> 

From here you can execute a simple query to assure yourself everything is setup to work correctly. If the connection fails, observe the error messages from JdbcClient and the merovingian logs for clues.

Using the driver in your Java programs

To use the MonetDB JDBC driver, the monetdb-X.Y-jdbc.jar jar-archive has to be in the Java classpath. Make sure this is actually the case.

Loading the driver in your Java program requires two lines of code:

// make sure the ClassLoader has the MonetDB JDBC driver loaded
Class.forName("nl.cwi.monetdb.jdbc.MonetDriver");
// request a Connection to a MonetDB server running on 'localhost'
Connection con = DriverManager.getConnection("jdbc:monetdb://localhost/database", "monetdb", "monetdb");

The first line makes sure the Java ClassLoader has initialised (and loaded) the Driver class of the MonetDB JDBC package, so it is registered with the DriverManager. The second line requests a Connection object from the DriverManager which is suitable for MonetDB.

The string passed to the "getConnection()"method is defined as "jdbc:monetdb://<host>[:<port>]/<database>" where elements between "<" and ">" are required and elements between "[" and "]" are optional.

A sample Java program

import java.sql.*;

/**
 * This example assumes there exist tables a and b filled with some data.
 * On these tables some queries are executed and the JDBC driver is tested
 * on it's accuracy and robustness against 'users'.
 *
 * @author Fabian Groffen
 */
public class MJDBCTest {
    public static void main(String[] args) throws Exception {
        // make sure the driver is loaded
        Class.forName("nl.cwi.monetdb.jdbc.MonetDriver");
        Connection con = DriverManager.getConnection("jdbc:monetdb://localhost/database", "monetdb", "monetdb");
        Statement st = con.createStatement();
        ResultSet rs;

        rs = st.executeQuery("SELECT a.var1, COUNT(b.id) as total FROM a, b WHERE a.var1 = b.id AND a.var1 = 'andb' GROUP BY a.var1 ORDER BY a.var1, total;");
        // get meta data and print columns with their type
        ResultSetMetaData md = rs.getMetaData();
        for (int i = 1; i <= md.getColumnCount(); i++) {
            System.out.print(md.getColumnName(i) + ":" +
                md.getColumnTypeName(i) + "\t");
        }
        System.out.println("");
        // print the data: only the first 5 rows, while there probably are
        // a lot more. This shouldn't cause any problems afterwards since the
        // result should get properly discarded on the next query
        for (int i = 0; rs.next() && i < 5; i++) {
            for (int j = 1; j <= md.getColumnCount(); j++) {
                System.out.print(rs.getString(j) + "\t");
            }
            System.out.println("");
        }

        // tell the driver to only return 5 rows, it can optimize on this
        // value, and will not fetch any more than 5 rows.
        st.setMaxRows(5);
        // we ask the database for 22 rows, while we set the JDBC driver to
        // 5 rows, this shouldn't be a problem at all...
        rs = st.executeQuery("select * from a limit 22");
        // read till the driver says there are no rows left
        for (int i = 0; rs.next(); i++) {
            System.out.print("[" + rs.getString("var1") + "]");
            System.out.print("[" + rs.getString("var2") + "]");
            System.out.print("[" + rs.getInt("var3") + "]");
            System.out.println("[" + rs.getString("var4") + "]");
        }

        // this close is not needed, should be done by next execute(Query) call
        // however if there can be some time between this point and the next
        // execute call, it is from a resource perspective better to close it.
        //rs.close();

        // unset the row limit; 0 means as much as the database sends us
        st.setMaxRows(0);
        // we only ask 10 rows
        rs = st.executeQuery("select * from b limit 10;");
        // and simply print them
        while (rs.next()) {
            System.out.print(rs.getInt("rowid") + ", ");
            System.out.print(rs.getString("id") + ", ");
            System.out.print(rs.getInt("var1") + ", ");
            System.out.print(rs.getInt("var2") + ", ");
            System.out.print(rs.getString("var3") + ", ");
            System.out.println(rs.getString("var4"));
        }

        // this close is not needed, as the Statement will close the last
        // ResultSet around when it's closed
        // again, if that can take some time, it's nicer to close immediately
        // the reason why these closes are commented out here, is to test if
        // the driver really cleans up it's mess like it should
        //rs.close();

        // perform a ResultSet-less query (with no trailing ; since that should
        // be possible as well and is JDBC standard)
        // Note that this method should return the number of updated rows. This
        // method however always returns -1, since Monet currently doesn't
        // support returning the affected rows.
        st.executeUpdate("delete from a where var1 = 'zzzz'");

        // closing the connection should take care of closing all generated
        // statements from it...
        // don't forget to do it yourself if the connection is reused or much
        // longer alive, since the Statement object contains a lot of things
        // you probably want to reclaim if you don't need them anymore.
        //st.close();
        con.close();
    }
}

Python Library

The MonetDB MAPI and SQL client python API

Introduction

This is the new native python client API. This API is cross-platform, and doesn't depend on any monetdb libraries. It has support for python 2.5, 2.6 and 3.0 and is Python DBAPI 2.0 compatible.

Changes

A number of things are different compared to the old version that uses the mapi library:

• No dependecies on MonetDB libraries anymore
• MAPI protocol is now implemented in pure python
• Added unit tests for the SQL API
• The MAPI module is now named monetdb.mapi
• The SQL module is now named monetdb.sql
• Small changes in argument names for functions
• Type conversion is working (for example a monetdb int becomes a python int)
• Dropped support for the dictionary based cursor

Installation

To install the MonetDB python API run the following command from the python source directory:

# python setup.py install

That's all, now you are ready to start using the API.

Documentation

The python code is well documented, so if you need to find documentation you should have a look at the source code. Below is an interactive example on how to use the monetdb SQL API which should get you started quite fast.

Examples

There are some examples in the 'examples' folder, but here are is a line by line example of the SQL API:

> # import the SQL module
> import monetdb.sql
>
> # set up a connection. arguments below are the defaults
> connection = monetdb.sql.connect(username="monetdb", password="monetdb", hostname="localhost", database="demo")
>
> # create a cursor
> cursor = connection.cursor()
>
> # increase the rows fetched to increase performance (optional)
> cursor.arraysize = 100
>
> # execute a query (return the number of rows to fetch)
> cursor.execute('SELECT * FROM tables')
26
>
> # fetch only one row
> cursor.fetchone()
[1062, 'schemas', 1061, None, 0, True, 0, 0]
>
> # fetch the remaining rows
> cursor.fetchall()
[[1067, 'types', 1061, None, 0, True, 0, 0],
 [1076, 'functions', 1061, None, 0, True, 0, 0],
 [1085, 'args', 1061, None, 0, True, 0, 0],
 [1093, 'sequences', 1061, None, 0, True, 0, 0],
 [1103, 'dependencies', 1061, None, 0, True, 0, 0],
 [1107, 'connections', 1061, None, 0, True, 0, 0],
 [1116, '_tables', 1061, None, 0, True, 0, 0],
 ...
 [4141, 'user_role', 1061, None, 0, True, 0, 0],
 [4144, 'auths', 1061, None, 0, True, 0, 0],
 [4148, 'privileges', 1061, None, 0, True, 0, 0]]
>
> # Show the table meta data
> cursor.description
[('id', 'int', 4, 4, None, None, None),
 ('name', 'varchar', 12, 12, None, None, None),
 ('schema_id', 'int', 4, 4, None, None, None),
 ('query', 'varchar', 168, 168, None, None, None),
 ('type', 'smallint', 1, 1, None, None, None),
 ('system', 'boolean', 5, 5, None, None, None),
 ('commit_action', 'smallint', 1, 1, None, None, None),
 ('temporary', 'tinyint', 1, 1, None, None, None)]

If you would like to communicate with the database at a lower level you can use the MAPI library:

> from monetdb import mapi
> server = mapi.Server()
> server.connect(hostname="localhost", port=50000, username="monetdb", password="monetdb", database="demo", language="sql")
> server.cmd("sSELECT * FROM tables;")
...

Perl Library

MonetDB Perl Library

Perl is one of the more common scripting languages for which a 'standard' database application programming interface is defined. It is called DBI and it was designed to protect you from the API library details of multiple DBMS vendors. It has a very simple interface to execute SQL queries and for processing the results sent back. DBI doesn't know how to talk to any particular database, but it does know how to locate and load in DBD (`Database Driver') modules. The DBD modules encapsulate the interface library's intricacies and knows how to talk to the real databases.

MonetDB comes with its own DBD module which is included in both the source and binary distribution packages. The module is also available via CPAN.

Two sample Perl applications are included in the source distribution; a MIL session and a simple client to interact with a running server.

For further documentation we refer to the Perl community home page.

A Simple Perl Example

use strict;
use warnings;
use DBI();

print "\nStart a simple Monet MIL interaction\n\n";

# determine the data sources:
my @ds = DBI->data_sources('monetdb');
print "data sources: @ds\n";

# connect to the database:
my $dsn = 'dbi:monetdb:database=test;host=localhost;port=50000;language=mil';
my $dbh = DBI->connect( $dsn,
  undef, undef,  # no authentication in MIL
  { PrintError => 0, RaiseError => 1 }  # turn on exception handling
);
{
  # simple MIL statement:
  my $sth = $dbh->prepare('print(2);');
  $sth->execute;
  my @row = $sth->fetchrow_array;
  print "field[0]: $row[0], last index: $#row\n";
}
{
  my $sth = $dbh->prepare('print(3);');
  $sth->execute;
  my @row = $sth->fetchrow_array;
  print "field[0]: $row[0], last index: $#row\n";
}
{
  # deliberately executing a wrong MIL statement:
  my $sth = $dbh->prepare('( xyz 1);');
  eval { $sth->execute }; print "ERROR REPORTED: $@" if $@;
}
$dbh->do('var b:=new(int,str);');
$dbh->do('insert(b,3,"three");');
{
  # variable binding stuff:
  my $sth = $dbh->prepare('insert(b,?,?);');
  $sth->bind_param( 1,     7 , DBI::SQL_INTEGER() );
  $sth->bind_param( 2,'seven' );
  $sth->execute;
}
{
  my $sth = $dbh->prepare('print(b);');
  # get all rows one at a time:
  $sth->execute;
  while ( my $row = $sth->fetch ) {
    print "bun: $row->[0], $row->[1]\n";
  }
  # get all rows at once:
  $sth->execute;
  my $t = $sth->fetchall_arrayref;
  my $r = @$t;         # row count
  my $f = @{$t->[0]};  # field count
  print "rows: $r, fields: $f\n";
  for my $i ( 0 .. $r-1 ) {
    for my $j ( 0 .. $f-1 ) {
      print "field[$i,$j]: $t->[$i][$j]\n";
    }
  }
}
{
  # get values of the first column from each row:
  my $row = $dbh->selectcol_arrayref('print(b);');
  print "head[$_]: $row->[$_]\n" for 0 .. 1;
}
{
  my @row = $dbh->selectrow_array('print(b);');
  print "field[0]: $row[0]\n";
  print "field[1]: $row[1]\n";
}
{
  my $row = $dbh->selectrow_arrayref('print(b);');
  print "field[0]: $row->[0]\n";
  print "field[1]: $row->[1]\n";
}
$dbh->disconnect;
print "\nFinished\n";

ODBC Driver

MonetDB ODBC Driver

Short for Open DataBase Connectivity, a standard database access method developed by the SQL Access group in 1992. The goal of ODBC is to make it possible to access any data from any application, regardless of which database management system (DBMS) is handling the data. ODBC manages this by inserting a middle layer, called a database driver, between an application and the DBMS. The purpose of this layer is to translate the application's data queries into commands that the DBMS understands. For this to work, both the application and the DBMS must be ODBC-compliant – that is, the application must be capable of issuing ODBC commands and the DBMS must be capable of responding to them.

The ODBC driver for MonetDB is included in the Windows installer and Linux RPMs. The source can be found in the SQL CVS tree.

To help you setup your system to use the ODBC driver with MonetDB, two how-tos are available, one for Windows users and one for Linux/UNIX users.

Microsoft Excel demo

A little demo showing how to import data from a MonetDB server into Microsoft Excel.

Using Excel with the MonetDB ODBC Driver

Start up the MonetDB SQL Server and Excel.

In Excel, select from the drop down menu, first Data, then Get External Data, and finally New Database Query...

If MonetDB was installed correctly, there should be an entry MonetDB in the dialog box that opens. Select it and click on OK.

In the wizard that opens, scroll down in the list on the left hand side and select voyages. Then click on the button labeled > and then on Next >.

In the next page of the wizard, click on Next >.

In the next page of the wizard, click on Next >.

In the final page of the wizard, click on Finish.

A new dialog window opens. Click on OK to insert the data into the current Excel worksheet.

That's all.

Installing the MonetDB ODBC Driver for unixODBC

Configuring the MonetDB Driver

As Superuser, start the unixODBC configuration program ODBCConfig and select the Drivers tab.

On this tab, click on the button labeled Add... and fill in the fields as follows.

Name

MonetDB

Description

ODBC Driver for MonetDB SQL Server

Driver

<path-to-MonetDB>/lib(64)/libMonetODBC.so

Setup

<path-to-MonetDB>/lib(64)/libMonetODBCs.so

Don't change the other fields. When done, click on the check mark in the top left corner of the window. The first window should now contain an entry for MonetDB. Click on OK

Configuring a Data Source

Now as normal user start ODBCConfig again.

On the User DSN tab click on the Add... button. A new window pops up in which you have to select the ODBC driver. Click on the entry for MonetDB and click on OK.

A new window pops up. Fill in the fields as follows.

Name

MonetDB

Description

Default MonetDB Data Source

Host

localhost

Port

50000

User

monetdb

Password

monetdb

Don't change the other fields. When done, click on the check mark in the top left corner of the window. The first window should now contain an entry for MonetDB. Click on OK

Supported

Feature ID	Feature name
E011	Numeric data types (FULL support)
E011-01	INTEGER and SMALLINT data types (including all spellings)
E011-02	REAL, DOUBLE PRECISON, and FLOAT data types
E011-03	DECIMAL and NUMERIC data types
E011-04	Arithmetic operators
E011-05	Numeric comparison
E011-06	Implicit casting among the numeric data types
E021	Character data types (PARTIAL support)
E021-01	CHARACTER data type (including all its spellings)
E021-02	CHARACTER VARYING data type (including all its spellings)
E021-03	Character literals
E021-04	CHARACTER_LENGTH function
E021-05	OCTET_LENGTH function
E021-06	SUBSTRING function
E021-07	Character concatenation
E021-08	UPPER and LOWER functions
E021-09	TRIM function
E021-10	Implicit casting among the character data types
E021-11	POSITION function
E021-12	Character comparison
E031	Identifiers (FULL support)
E031-01	Delimited identifiers
E031-02	Lower case identifiers
E031-03	Trailing underscore
E051	Basic query specification (FULL support)
E051-01	SELECT DISTINCT
E051-02	GROUP BY clause
E051-04	GROUP BY can contain columns not in select-list
E051-05	Select list items can be renamed
E051-06	HAVING clause
E051-07	Qualified * in select list
E051-08	Correlation names in the FROM clause
E051-09	Rename columns in the FROM clause
E061	Basic predicates and search conditions (FULL support)
E061-01	Comparison predicate
E061-02	BETWEEN predicate
E061-03	IN predicate with list of values
E061-04	LIKE predicate
E061-05	LIKE predicate: ESCAPE clause
E061-06	NULL predicate
E061-07	Quantified comparison predicate
E061-08	EXISTS predicate
E061-09	Subqueries in comparison predicate
E061-11	Subqueries in IN predicate
E061-12	Subqueries in quantified comparison predicate
E061-13	Correlated subqueries
E061-14	Search condition
E071	Basic query expressions (FULL support)
E071-01	UNION DISTINCT table operator
E071-02	UNION ALL table operator
E071-03	EXCEPT DISTINCT table operator
E071-05	Columns combined via table operators need not have exactly the same data type
E071-06	Table operators in subqueries
E081	Basic Privileges
E081-01	SELECT privilege at the table level
E081-02	DELETE privilege
E081-03	INSERT privilege at the table level
E081-04	UPDATE privilege at the table level
E081-05	UPDATE privilege at the column level
E081-06	REFERENCES privilege at the table level  (SELECT privilege is used)
E081-07	REFERENCES privilege at the column level (SELECT privilege is used)
E081-08	WITH GRANT OPTION
E091	Set functions (FULL support)
E091-01	AVG
E091-02	COUNT
E091-03	MAX
E091-04	MIN
E091-05	SUM
E091-06	ALL quantifier
E091-07	DISTINCT quantifier
E101	Basic data manipulation (FULL support)
E101-01	INSERT statement
E101-03	Searched UPDATE statement
E101-04	Searched DELETE statement
E111	Single row SELECT statement
E121-01	DECLARE CURSOR
E121-02	ORDER BY columns need not be in select list
E131	Null value support (nulls in lieu of values)
E141	Basic integrity constraints
E141-01	NOT NULL constraints
E141-02	UNIQUE constraints of NOT NULL columns
E141-03	PRIMARY KEY constraints
E141-04	Basic FOREIGN KEY constraint with the NO ACTION default
E141-07	Column defaults
E141-08	NOT NULL inferred on PRIMARY KEY
E151	Transaction support
E151-01	COMMIT statement
E151-02	ROLLBACK statement
E152	Basic SET TRANSACTION statement
E152-01	SET TRANSACTION statement: ISOLATION LEVEL SERIALIZABLE clause
E153	Updatable queries with subqueries
E161	SQL comments using leading double minus
E182	Module language
F021-01	COLUMNS view
F021-02	TABLES view
F021-03	VIEWS view
F021-04	TABLE_CONSTRAINTS view
F021-05	REFERENTIAL_CONSTRAINTS view
F021-06	CHECK_CONSTRAINTS view
F031	Basic schema manipulation
F031-01	CREATE TABLE statement to create persistent base tables
F031-02	CREATE VIEW statement
F031-03	GRANT statement
F031-04	ALTER TABLE statement: ADD COLUMN clause
F031-13	DROP TABLE statement: RESTRICT clause
F031-16	DROP VIEW statement: RESTRICT clause
F031-19	REVOKE statement: RESTRICT clause
F041	Basic joined table
F041-01	Inner join (but not necessarily the INNER keyword)
F041-02	INNER keyword
F041-03	LEFT OUTER JOIN
F041-04	RIGHT OUTER JOIN
F041-05	Outer joins can be nested
F041-07	The inner table in a left or right outer join can also be used in an inner join
F041-08	All comparison operators are supported (rather than just =)
F051	Basic date and time
F051-01	DATE data type (including DATE literal)
F051-02	TIME data type (including TIME literal) with fractional seconds precision of 0
F051-03	TIMESTAMP data type (including TIMESTAMP literal) with fractional seconds precision of 0 and 6
F051-04	Comparison predicate on DATE, TIME, and TIMESTAMP data types
F051-05	Explicit CAST between datetime types and character types
F051-06	CURRENT_DATE
F051-07	LOCALTIME
F051-08	LOCALTIMESTAMP
F081	UNION and EXCEPT in views
F131	Grouped operations
F131-01	WHERE, GROUP BY, and HAVING clauses supported in queries with grouped views
F131-02	Multiple tables supported in queries with grouped views
F131-03	Set functions supported in queries with grouped views
F131-04	Subqueries with GROUP BY and HAVING clauses and grouped views
F131-05	Single row SELECT with GROUP BY and HAVING clauses and grouped views
F181	Multiple module support (LIMITED support)
F201	CAST function
F221	Explicit defaults
F261	CASE expression
F261-01	Simple CASE
F261-02	Searched CASE
F261_03	NULLIF
F261-04	COALESCE
F311	Schema definition statement
F311-01	CREATE SCHEMA
F311-02	CREATE TABLE for persistent base tables
F311-03	CREATE VIEW
F311-05	GRANT statement
F471	Scalar subquery values
F481	Expanded NULL predicate
F501-01	SQL_FEATURES view
F501-02	SQL_SIZING view
F501-03	SQL_LANGUAGES view
F521	Assertions
S011	Distinct data types
T321	Basic SQL-invoked routines
T321-01	User-defined functions with no overloading
T321-02	User-defined stored procedures with no overloading
T321-03	Function invocation
T321-04	CALL statement
T321-05	RETURN statement

Unsupported

Feature ID	Feature name
B011-B017	Embedded Language support. Core SQL:1999 says that at least one of Embedded Ada, Embedded C, Embedded Cobol, Embedded Fortran, Embedded MUMPS, Embedded Pascal or Embedded PL/I 1 should be supported.
B111-B117	Module language ADA, COBOL, Fortran MUMPS, Pascal, PL/I
B121-B127	Routine language ADA, COBOL, Fortran MUMPS, Pascal, PL/I
E081-09	USAGE privilege
E081-10	EXECUTE privilege
E153	Updatable queries with subqueries
E121	Basic cursor support (CURSORS are not supported)
E121-01	DECLARE CURSOR
E121-03	Value expressions in ORDER BY clause
E121-04	OPEN statement
E121-06	Positioned UPDATE statement
E121-07	Positioned DELETE statement
E121-08	CLOSE statement
E121-10	FETCH statement: implicit NEXT
E121-17	WITH HOLD cursors
E141-06	CHECK constraints
E141-10	Names in a foreign key can be specified in any order (columns should be in the proper order)
E152-02	SET TRANSACTION statement: READ ONLY and READ WRITE clauses
E171	SQLSTATE support
F021	Basic information schema
F021-01	COLUMNS view
F021-02	TABLES view
F021-03	VIEWS view
F021-04	TABLE_CONSTRAINTS view
F021-05	REFERENTIAL_CONSTRAINTS view
F021-06	CHECK_CONSTRAINTS view
F121	Basic diagnostic management
F121-01	GET DIAGNOSTICS statement
F121-02	SET TRANSACTION statement: DIAGNOSTICS SIZE clause
F122	Enhanced diagnostics statement
F123	All diagnostics
F181	Multiple module support
F202	TRUNCATE TABLE: identify column restart option
F263	Comma-separated predicates in simple CASE expression
F291	UNIQUE predicate
F301	CORRESPONDING in query expressions
F311-04	CREATE VIEW: WITH CHECK OPTION
F312	MERGE statement
F313	Enhanced MERGE statement
F341	Usage tables (ROUTINE_*_USAGE)
F394	Optional normal form specification
F403	Partitioned joined tables
F451	Character set definition
F461	Named character sets
F501	Features and conformance views
F501-01	SQL_FEATURES view
F501-02	SQL_SIZING view
F501-03	SQL_LANGUAGES view
F671	Subqueries in CHECK
F693	SQL-session and client module collations
F695	Translation support
F696	Additional translation documentation
F721	Deferrable constraints
F751	View CHECK enhancements
F812	Basic flagging
F831 - 831-02	Cursor update, scroll, and order
F841	LIKE_REGEX
F842	OCCURRENCES_REGEX function
F843	POSITION_REGEX function
F844	SUBSRING_REGEX function
F845	TRANSLATE_REGEX function
F846	Octet support in regular expression
F847	Nonconstant regular expression
S011-01	USER_DEFINED_TYPES view
S023	Basic structured types
S024	Enhanced structure types
S025	Final structured types
S026	Self-reference structured types
S027	Create method by specific method name
S028	Permutable UDT options list
S051	Create table of type
S081	Subtables
S091	Basic array support
S091-01	Arrays of built-in data types
S091-02	Arrays of distinct types
S091-03	Array expressions
S094	Arrays of reference types
S097	Array element assignment
S151  - S404	Object support features
T321-06	ROUTINES view
T321-07	PARAMETERS view
T011	Timestamp in Information Schema
T021	BINARY and VARBINARY data types
T022	Advanced support for BINARY and VARBINARY data types
T023	Compound binary literal
T024	Spaces in binary literals
T041	Basic LOB data type support
T041-01	BLOB data type
T041-02	CLOB data type
T041-03	POSITION, LENGTH, LOWER, TRIM, UPPER, and SUBSTRING functions for LOB data types
T041-04	Concatenation of LOB data types
T041-05	LOB locator: non-holdable
T042	Extended LOB data type support
T043	Multiplier T
T044	Multiplier P
T051	Row types
T052	MAX and MIN for row types
T053	Explicit aliases for all-fields reference
T061	UCS support
T101	Enhanced nullability determination
T111	Updatable joins, unions, and columns
T174	Identity columns
T175	Generated columns
T176	Sequence generator support
T177	Sequence generator support: simple restart option
T178	Identity columns: simple restart option
T211	Basic trigger capability
T211-06	Support for run-time rules for the interaction of triggers and constraints
T211-08	Multiple triggers for the same event are executed in the order in which they were created in the catalog intentionally omitted
T251	SET TRANSACTION statement: LOCAL option
T261	Chained transactions
T272	Enhanced savepoint management
T285	Enhanced derived column names
T301	Functional dependencies partially supported
T321	Basic SQL-invoked routines
T321-02	User-defined stored procedures with no overloading
T321-04	CALL statement
T321-05	RETURN statement
T324	Explicit security for SQL routines
T325	Qualified SQL parameter references
T326	Table functions
T332	Extended roles mostly supported
T431	Extended grouping capabilities
T432	Nested and concatenated GROUPING SETS
T433	Multiargument GROUPING function
T434	GROUP BY DISTINCT
T471	Result sets return value
T491	LATERAL derived table
T511	Transaction counts
T541	Updatable table references
T561	Holdable locators
T571	Array-returning external SQL-invoked functions
T572	Multiset-returning external SQL-invoked functions
T601	Local cursor references
T611	Elementary OLAP operations most forms supported
T612	Advanced operations some forms supported
T613	Sampling
T616	Null treatment option for LEAD and LAG functions
T618	NTH_VALUE function function exists, but some options missing
T641	Multiple column assignment only some syntax variants supported
T652	SQL-dynamic statements in SQL routines
T653	SQL-schema statements in external routines
T654	SQL-dynamic statements in external routines
M001	Datalinks
M002	Datalinks via SQL/CLI
M003	Datalinks via Embedded SQL
M004	Foreign data support partially supported
M005	Foreign schema support
M006	GetSQLString routine
M007	TransmitRequest
M009	GetOpts and GetStatistics routines
M010	Foreign data wrapper support
M011	Datalinks via Ada, C, COBOL, Fortran, MUMPS, Pascal, PL/I
M018 - M024	Foreign data wrapper interface routines in Ada, C, COBOL, Fortran,MUMPS,Pascal,PL/I
M030	SQL-server foreign data support
M031	Foreign data wrapper general routines
X012	Multisets of XML type
X013	Distinct types of XML type
X014	Attributes of XML type
X015	Fields of XML type
X025	XMLCast
X030	XMLDocument
X038	XMLText
X065	XMLParse: BLOB input and CONTENT option
X066	XMLParse: BLOB input and DOCUMENT option
X068	XMLSerialize: BOM
X069	XMLSerialize: INDENT
X073	XMLSerialize: BLOB serialization and CONTENT option
X074	XMLSerialize: BLOB serialization and DOCUMENT option
X075	XMLSerialize: BLOB serialization
X076	XMLSerialize: VERSION
X077	XMLSerialize: explicit ENCODING option
X078	XMLSerialize: explicit XML declaration
X080	Namespaces in XML publishing
X081	Query-level XML namespace declarations
X082	XML namespace declarations in DML
X083	XML namespace declarations in DDL
X084	XML namespace declarations in compound statements
X085	Predefined namespace prefixes
X086	XML namespace declarations in XMLTable
X091	XML content predicate
X096	XMLExists
X100	Host language support for XML: CONTENT option
X101	Host language support for XML: DOCUMENT option
X110	Host language support for XML: VARCHAR mapping
X111	Host language support for XML: CLOB mapping
X112	Host language support for XML: BLOB mapping
X113	Host language support for XML: STRIP WHITESPACE option
X114	Host language support for XML: PRESERVE WHITESPACE option
X131	Query-level XMLBINARY clause
X132	XMLBINARY clause in DML
X133	XMLBINARY clause in DDL
X134	XMLBINARY clause in compound statements
X135	XMLBINARY clause in subqueries
X141	IS VALID predicate: data-driven case
X142	IS VALID predicate: ACCORDING TO clause
X143	IS VALID predicate: ELEMENT clause
X144	IS VALID predicate: schema location
X145	IS VALID predicate outside check constraints
X151	IS VALID predicate with DOCUMENT option
X152	IS VALID predicate with CONTENT option
X153	IS VALID predicate with SEQUENCE option
X155	IS VALID predicate: NAMESPACE without ELEMENT clause
X157	IS VALID predicate: NO NAMESPACE with ELEMENT clause
X160	Basic Information Schema for registered XML Schemas
X161	Advanced Information Schema for registered XML Schemas
X170	XML null handling options
X171	NIL ON NO CONTENT option
X181	XML(DOCUMENT(UNTYPED)) type
X182	XML(DOCUMENT(ANY)) type
X190	XML(SEQUENCE) type
X191	XML(DOCUMENT(XMLSCHEMA)) type
X192	XML(CONTENT(XMLSCHEMA)) type
X200	XMLQuery
X201	XMLQuery: RETURNING CONTENT
X202	XMLQuery: RETURNING SEQUENCE
X203	XMLQuery: passing a context item
X204	XMLQuery: initializing an XQuery variable
X205	XMLQuery: EMPTY ON EMPTY option
X206	XMLQuery: NULL ON EMPTY option
X211	XML 1.1 support
X221	XML passing mechanism BY VALUE
X222	XML passing mechanism BY REF
X231	XML(CONTENT(UNTYPED)) type
X232	XML(CONTENT(ANY)) type
X241	RETURNING CONTENT in XML publishing
X242	RETURNING SEQUENCE in XML publishing
X251	Persistent XML values of XML(DOCUMENT(UNTYPED)) type
X252	Persistent XML values of XML(DOCUMENT(ANY)) type
X253	Persistent XML values of XML(CONTENT(UNTYPED)) type
X254	Persistent XML values of XML(CONTENT(ANY)) type
X255	Persistent XML values of XML(SEQUENCE) type
X256	Persistent XML values of XML(DOCUMENT(XMLSCHEMA)) type
X257	Persistent XML values of XML(CONTENT(XMLSCHEMA)) type
X260	XML type: ELEMENT clause
X261	XML type: NAMESPACE without ELEMENT clause
X263	XML type: NO NAMESPACE with ELEMENT clause
X264	XML type: schema location
X271	XMLValidate: data-driven case
X272	XMLValidate: ACCORDING TO clause
X273	XMLValidate: ELEMENT clause
X274	XMLValidate: schema location
X281	XMLValidate: with DOCUMENT option
X282	XMLValidate with CONTENT option
X283	XMLValidate with SEQUENCE option
X284	XMLValidate NAMESPACE without ELEMENT clause
X286	XMLValidate: NO NAMESPACE with ELEMENT clause
X300	XMLTable
X301	XMLTable: derived column list option
X302	XMLTable: ordinality column option
X303	XMLTable: column default option
X304	XMLTable: passing a context item
X305	XMLTable: initializing an XQuery variable