Programming SQL

Programming SQL mk Thu, 06/30/2011 - 16:39

SQL comes with  imperative programming concepts, such as variable declarations, compound statements, while-loops, and conditional-statements. The are grouped into procedures, functions, and triggers, which strongly differ in their execution paradigm and semantics.

Procedure definition

Procedure definition mk Sat, 05/28/2011 - 21:42

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

Function definitions mk Sat, 03/27/2010 - 22:47

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

func_def:
     CREATE FUNCTION qname '(' [ paramlist ] ')' RETURNS func_data_type  routine_body
   | CREATE FUNCTION qname '(' [ paramlist ] ')' RETURNS func_data_type EXTERNAL NAME external_function_name
   | CREATE 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,
    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;

 

Note: CREATE AGGREGATE is a MonetDB language extension. The SQL standard does not provide for user-defined aggregate functions.

Trigger definitions

Trigger definitions mk Sun, 03/28/2010 - 23:32

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

trigger_def: CREATE 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

Variables mk Sat, 03/27/2010 - 22:55

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

Flow of control mk Fri, 04/02/2010 - 12:48

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