NAME
SQL::Routine::EnumTypes - What enumerated types are part of SQL::Routine's language or grammar
COPYRIGHT AND LICENSE
This file is part of the SQL::Routine database portability library.
SQL::Routine is Copyright (c) 2002-2005, Darren R. Duncan. All rights reserved. Address comments, suggestions, and bug reports to perl@DarrenDuncan.net, or visit http://www.DarrenDuncan.net/ for more information.
SQL::Routine is free software; you can redistribute it and/or modify it under the terms of the GNU General Public License (GPL) as published by the Free Software Foundation (http://www.fsf.org/); either version 2 of the License, or (at your option) any later version. You should have received a copy of the GPL as part of the SQL::Routine distribution, in the file named "GPL"; if not, write to the Free Software Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA.
Linking SQL::Routine statically or dynamically with other modules is making a combined work based on SQL::Routine. Thus, the terms and conditions of the GPL cover the whole combination. As a special exception, the copyright holders of SQL::Routine give you permission to link SQL::Routine with independent modules, regardless of the license terms of these independent modules, and to copy and distribute the resulting combined work under terms of your choice, provided that every copy of the combined work is accompanied by a complete copy of the source code of SQL::Routine (the version of SQL::Routine used to produce the combined work), being distributed under the terms of the GPL plus this exception. An independent module is a module which is not derived from or based on SQL::Routine, and which is fully useable when not linked to SQL::Routine in any form.
Any versions of SQL::Routine that you modify and distribute must carry prominent notices stating that you changed the files and the date of any changes, in addition to preserving this original copyright notice and other credits. SQL::Routine is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
While it is by no means required, the copyright holders of SQL::Routine would appreciate being informed any time you create a modified version of SQL::Routine that you are willing to distribute, because that is a practical way of suggesting improvements to the standard version.
DESCRIPTION
This POD file contains most of the details about the enumerated types that are part of SQL::Routine's language or grammar. You should read the summary introduction in SQL::Routine::Language before this document.
ENUMERATED TYPES
This document details the enumerated types that SQL::Routine knows about.
container_type
These are high level structures that can represent contexts in which standard built-in routines execute, and they can represent general variable types that routine arguments, variables, or return types, or intermediate values of expressions can be. Note that all table and view column values have an implicit container_type of SCALAR.
ERROR - an error message object (a message key, named list of
values
)
SCALAR - a typed
scalar
variable, such as a boolean or number or cstring or bstring
ROW - a row variable; a named list of SCALAR (SQL:2003 type is
'ROW'
);
each
elem is a
"field"
SC_ARY - an ordered list of SCALAR (SQL:2003 type is
'ARRAY'
)
RW_ARY - an ordered list of ROW (SQL:2003 type is
'ARRAY'
)
CONN - a database connection context (may be closed or
open
)
CURSOR - a database cursor context (may be closed or
open
)
LIST -
for
exprs that are a generic list or explicit grouping of
sub
-exprs (comma-delim, paren-bound)
SRT_NODE -
for
client code only; stores a SQL::Routine::Node object
SRT_NODE_LIST -
for
client code only; stores a list of SQL::Routine::Node objects
If either of SRT_NODE[|_LIST] appear
with
a
":foo"
appended, such as
"SRT_NODE:foo"
, that means only a
"foo"
Node (or list thereof) is taken,
rather than any kind of Node;
if
multiple specific Node types are accepted,
then
"foo"
is a comma-delimited list of them.
The following won't be implemented in the short term:
ENVI - a db/app/dlp environment context; usually just one (conceptually), which is global
TRAN - a database transaction context
Note that matters such as variable-vs-constant and bind vars or in-vs-out-vs-inout or literal vs var/col/arg are not distinguished by this enumerated type.
exception_type
These represent the basic types of exception objects which both standard built-in routines and user-defined routines can throw if they fail; they can also be caught by user-defined routines. If stored in a routine variable, they have a container_type of ERROR.
Note: The SQL:2003 Standard says to use the keyword 'CONDITION' rather than 'EXCEPTION' to refer to this sort of thing; it has deprecated the use of the latter keyword as an alias for the former; see SQL:2003 Foundation p1171.
SRTX_NO_ENVI_LOAD_FAILED
- If data
link
product failed to load, from non-existence or missing dependencies.
SRTX_ENVI_EXEC_FAILED
- If data
link
product loaded successfully, but later had non-conn related failure.
SRTX_NO_CONN_SERVER_ABSENT
- If server doesn't exist, is inactive, is ignoring connection requests.
SRTX_NO_CONN_BAD_AUTH
- If server explicitly rejects connection attempt due to bad login credentials.
SRTX_NO_CONN_ACTIVE_LOST
- If an active server connection halts prematurely, from server or network failure.
standard_routine, standard_routine_context, standard_routine_arg
These represent the basic types of actions that a database engine or database client can perform, and are the "built-in" functions of the SQL::Routine virtual database language. The action types include: connection and transaction management, queries and cursors, DML and DDL, user and privilege management, and error handling. You always invoke these within user-defined routines, sometimes directly as routine statements, and sometimes indirectly when they form parts of user-defined view or query definitions.
Each standard routine may take arguments and/or return a value. All arguments are named, as with user-defined routines; likewise, there is exactly one un-named return value, if any. Where [routine_stmt, routine_expr, view_expr] SRT Nodes can have a 'standard_routine' attribute set, only child [routine_expr, view_expr] Nodes can have a 'standard_routine_arg' attribute set. Each standard routine will throw an exception (container_type is ERROR) if it fails, instead of returning a value; any standard routine may throw more than one type of exception depending on the circumstance.
Beneath each routine name is up to 6 kinds of information, having the following labels; for brevity, a label won't be shown if its value would be 'none':
purpose: what the routine does that validates its existence
context: what context type the routine must be called within; it is a special argument
args: what non-context arguments the routine takes,
if
any
effects: what side-effects there are of executing it, besides its
return
value
returns: what type of value/container the routine returns
throws: what types of exceptions are thrown
if
routine fails
Most arguments take one value each, and are indicated with the "(1)"; some arguments take a list of one or more similarly used elements, indicated by a "(N)". For view_expr or routine_expr Nodes that have call_sroutine_arg valued, the expr_type will always be LIST with "(N)" arguments (the child exprs are the elements); with "(1)" arguments, the expr Nodes are ordinary; this allows a set of expr Nodes to guarantee distinct call_sroutine_arg values under a call_sroutine.
This set of connection management built-ins may only be invoked in a client-side routine, usually with 'routine_stmt' Nodes.
CATALOG_LIST()
purpose:
- Fetches a list of auto-detectable database instances.
- Skips by
default
any details that CATALOG_INFO() and CONN-context
built-ins can handle; it is minimalist
for
speed.
args:
- RECURSIVE(1) - SCALAR.bool - Opt: To scan inside
each
db as well.
effects:
- A new
'catalog'
Node is added to the SQL::Routine::Container
for
each
one found, plus associated
'catalog_link'
,
'catalog_instance'
,
and
'catalog_link_instance'
Nodes (one
each
per
'catalog'
), plus
'data_storage_product'
and
'data_link_product'
Nodes
if
necessary.
- If RECURSIVE is true, call CATALOG_INFO() in recursive mode
for
each
database catalog found.
returns:
- SRT_NODE_LIST:catalog_link - The newly added cli Nodes.
throws:
- SRTX_NO_ENVI_LOAD_FAILED, SRTX_ENVI_EXEC_FAILED,
SRTX_NO_CONN_SERVER_ABSENT, SRTX_NO_CONN_BAD_AUTH,
SRTX_NO_CONN_ACTIVE_LOST
CATALOG_INFO()
purpose:
- Fetches some detailed info about a database instance.
- Skips by
default
any details that other CONN-context built-ins
can handle; it is minimalist
for
speed.
- Note: There may not be any info to gather in minimalist mode.
args:
- LINK_BP(1) - SRT_NODE:catalog_link - The catalog to scan.
- RECURSIVE(1) - SCALAR.bool - Opt: To get all determinable details.
effects:
- May set some catalog Node attributes, or add child Nodes.
- If RECURSIVE is true, call these built-ins in recursive mode:
OWNER_LIST(), SCHEMA_LIST(), ROLE_LIST(), USER_LIST().
returns:
- SRT_NODE:catalog_link - The same cli that LINK_BP
has
.
throws:
- SRTX_NO_ENVI_LOAD_FAILED, SRTX_ENVI_EXEC_FAILED,
SRTX_NO_CONN_SERVER_ABSENT, SRTX_NO_CONN_BAD_AUTH,
SRTX_NO_CONN_ACTIVE_LOST
CATALOG_VERIFY()
purpose:
- Compares a provided db spec to an actual database.
- Functions analogously to CATALOG_INFO() except that
no
changes
are made to the SQL::Routine::Container; existing Nodes therein are
compared to instead.
args:
- LINK_BP(1) - SRT_NODE:catalog_link - The catalog to scan.
- RECURSIVE(1) - SCALAR.bool - Opt: To verify all possible details.
returns:
- SCALAR.bool - A true value
if
the db matches the spec, false
if
not.
- If the details that the database product stores or reports about
itself are more ambiguous or less detailed than the SRT model that
it is being compared to, but the former is still valid as a
degraded case of the latter, then CATALOG_VERIFY() will still
return
true. This would happen,
for
example,
if
the model defines
a foreign key constraint, and the database engine
has
no
support
for
those. So just the details a db does support are tested.
throws:
- SRTX_NO_ENVI_LOAD_FAILED, SRTX_ENVI_EXEC_FAILED,
SRTX_NO_CONN_SERVER_ABSENT, SRTX_NO_CONN_BAD_AUTH,
SRTX_NO_CONN_ACTIVE_LOST
CATALOG_CREATE()
purpose:
- Creates a new database instance.
- By
default
, creates just the catalog but puts nothing inside it.
args:
- LINK_BP(1) - SRT_NODE:catalog_link - The catalog to create.
- RECURSIVE(1) - SCALAR.bool - Opt: To create all possible details.
effects:
- If RECURSIVE is true, creates all database schemas and users also.
throws:
- SRTX_NO_ENVI_LOAD_FAILED, SRTX_ENVI_EXEC_FAILED,
SRTX_NO_CONN_SERVER_ABSENT, SRTX_NO_CONN_BAD_AUTH,
SRTX_NO_CONN_ACTIVE_LOST
CATALOG_DELETE()
purpose:
- Deletes a database instance.
args:
- LINK_BP(1) - SRT_NODE:catalog_link - The catalog to
delete
.
CATALOG_CLONE()
purpose:
- Makes a clone of a db instance in another storage loc.
args:
- SOURCE_LINK_BP(1) - SRT_NODE:catalog_link - The catalog to copy from.
- DEST_LINK_BP(1) - SRT_NODE:catalog_link - The catalog to copy to / create.
CATALOG_MOVE()
purpose:
- Moves a db inst from one storage location to another.
args:
- SOURCE_LINK_BP(1) - SRT_NODE:catalog_link - The catalog location to move from.
- DEST_LINK_BP(1) - SRT_NODE:catalog_link - The catalog location to move to.
This set of connection management built-ins may only be invoked in a routine, client or database side, usually with 'routine_stmt' Nodes.
CATALOG_OPEN()
purpose:
- Opens a
link
or connection to a database instance from an application.
- Note that it is the routine_var or routine_arg declaration SRT
Node that references the catalog_link which defines
our
basic
connection details; this var/arg is provided in CONN_CX.
context:
- CONN_CX - CONN - A routine var or arg that will hold the new connection handle.
args:
- LOGIN_NAME(1) - SCALAR.cstr - Opt: A runtime-provided username to
connect
with
.
- LOGIN_PASS(1) - SCALAR.cstr - Opt: A runtime-provided password to
connect
with
.
effects:
- The CONN_CX changes from an inactive state to active, ready to
pipe
data.
throws:
- SRTX_NO_ENVI_LOAD_FAILED, SRTX_ENVI_EXEC_FAILED,
SRTX_NO_CONN_SERVER_ABSENT, SRTX_NO_CONN_BAD_AUTH,
SRTX_NO_CONN_ACTIVE_LOST
TODO: Provide a way to clone a connection and/or allow multiple
"lightweight connections"
or independent transactional contexts to
share the same actual/heavy connection.
CATALOG_CLOSE()
purpose:
- Closes a
link
or connection to a database instance from an application.
context:
- CONN_CX - CONN - The
open
database connection context to be closed.
effects:
- The CONN_CX changes from an active to an inactive state.
throws:
- SRTX_NO_CONN_ACTIVE_LOST
CATALOG_PING()
purpose:
- Verifies that a db connection is still
open
, that the db is accessible.
context:
- CONN_CX - CONN - The
open
database connection context to be pinged.
returns:
- SCALAR.bool - A true value
if
the db connection is still active,
false
if
not.
- Since we are explicitly testing it,
no
SRTX_NO_CONN_ACTIVE_LOST
exception is thrown
if
the connection is down.
CATALOG_ATTACH()
purpose:
- Attaches a
link
or connection to a database instance from another db instance.
context:
- CONN_CX - CONN - An
open
database connection to provide
our
action context.
args:
- LINK_BP(1) - SRT_NODE:catalog_link - The catalog to
connect
to.
throws:
- SRTX_NO_CONN_ACTIVE_LOST
CATALOG_DETACH()
purpose:
- Detaches a
link
or connection to a database instance from another db instance.
context:
- CONN_CX - CONN - An
open
database connection to provide
our
action context.
args:
- LINK_BP(1) - SRT_NODE:catalog_link - The catalog to
connect
to.
throws:
- SRTX_NO_CONN_ACTIVE_LOST
This set of schema management or data definition built-ins may only be invoked in a routine, client or database side, usually with 'routine_stmt' Nodes.
SCHEMA_LIST() - fetches a list of schemas of
open
db instance
SCHEMA_INFO(schema) - fetches some detailed info about a schemas
SCHEMA_VERIFY(schema) - compares a provided schema spec to an actual schemas
SCHEMA_CREATE(schema) - creates a new schema in catalog of
open
db inst
SCHEMA_DELETE(schema) - deletes an existing schema in
open
db inst (
if
it can)
SCHEMA_CLONE(schema,schema) - creates a clone of an existing schema
with
different name
SCHEMA_UPDATE(schema) - alters details
for
an existing schema
DOMAIN_LIST(schema) - fetches a list of domains of
open
db instance
DOMAIN_INFO(domain) - fetches some detailed info about a domain
DOMAIN_VERIFY(domain) - compares a provided domain spec to an actual domain
DOMAIN_CREATE(domain) - creates a new domain in schema of
open
db inst
DOMAIN_DELETE(domain) - deletes an existing domain in
open
db inst (
if
it can)
DOMAIN_CLONE(domain,domain) - creates a clone of an existing domain
with
different name
DOMAIN_UPDATE(domain) - alters details
for
an existing domain
SEQU_LIST(schema) - fetches a list of sequence generators in schema of
open
db instance
SEQU_INFO(sequence) - fetches some detailed info about a sequence
SEQU_VERIFY(sequence) - compares a provided sequence generator spec to an actual table
SEQU_CREATE(sequence) - creates a new sequence generator in schema of
open
db inst
SEQU_DELETE(sequence) - deletes an existing sequence generator in
open
db inst (
if
it can)
SEQU_CLONE(sequence,sequence) - creates a clone of an existing sequence
with
different name
SEQU_UPDATE(sequence) - alters details
for
an existing sequence generator
TABLE_LIST(schema) - fetches a list of tables in schema of
open
db instance
TABLE_INFO(table) - fetches some detailed info about a table
TABLE_VERIFY(table) - compares a provided table spec to an actual table
TABLE_CREATE(table) - creates a new table in schema of
open
db inst
TABLE_DELETE(table) - deletes an existing table in
open
db inst (
if
it can)
TABLE_CLONE(table,table) - creates a clone of an existing table
with
different name
TABLE_UPDATE(table) - alters details
for
an existing table
VIEW_LIST(schema) - fetches a list of views in schema of
open
db instance
VIEW_INFO(view) - fetches some detailed info about a view
VIEW_VERIFY(view) - compares a provided view spec to an actual view
VIEW_CREATE(view) - creates a new view in schema of
open
db inst
VIEW_DELETE(view) - deletes an existing view in
open
db inst (
if
it can)
VIEW_CLONE(view,view) - creates a clone of an existing view
with
different name
VIEW_UPDATE(view) - alters details
for
an existing view
ROUTINE_LIST(schema) - fetches a list of routines/procs/funcs of
open
db instance
ROUTINE_INFO(routine) - fetches some detailed info about a routine
ROUTINE_VERIFY(routine) - compares a provided routine spec to an actual routine
ROUTINE_CREATE(routine) - creates a new routine in schema of
open
db inst
ROUTINE_DELETE(routine) - deletes an existing routine in
open
db inst (
if
it can)
ROUTINE_CLONE(routine,routine) - creates a clone of an existing routine
with
different name
ROUTINE_UPDATE(routine) - alters details
for
an existing routine
This set of user management built-ins may only be invoked in a routine, client or database side, usually with 'routine_stmt' Nodes.
USER_LIST() - fetches a list of users in
open
db instance
USER_INFO(user) - fetches some detailed info about a database user
USER_VERIFY(user) - compares a provided user spec to an actual user
USER_CREATE(user) - creates new db user one can auth CATALOG_OPEN
with
(
if
privs)
USER_DELETE(user) - deletes db user (as well as all schema it owns,
if
applic)
USER_CLONE(user,user) - creates a clone of an existing user
with
different name
USER_UPDATE(user) - alters some details
for
a database user
USER_GRANT(user) - grants a db privilege to a db user
USER_REVOKE(user) - revokes a db privilege from a db user
This set of data management or logic or program flow control or miscellaneous built-ins may only be invoked in a routine, client or database side, usually with 'routine_stmt' Nodes.
REC_FETCH(view) - fetches records from a table or view of multiple tables
REC_VERIFY(view) - compares a provided set of records to stored ones
REC_INSERT(view) - inserts new recs into a table or view of multiple tables
REC_UPDATE(view) - updates exist recs in a table or view of multiple tables
REC_DELETE(view) - deletes exist recs from a table or view of multiple tables
REC_REPLACE(view) - either updates or inserts recs
if
they exist or not
REC_CLONE(view) - creates a clone of a record in same table/view (
if
possible)
REC_LOCK(view) - places a
lock
on recs
for
a consistent
read
plus update
REC_UNLOCK(view) - releases an existing
lock
on recs
RETURN()
purpose:
- Causes the parent routine to stop right away, and optionally returns a value.
args:
- RETURN_VALUE(1) - Opt: The container expression value that the parent routine returns.
CURSOR_OPEN()
purpose:
- Opens a
select
cursor context
for
reading from (or performs a
select
if
in right context).
context:
- CURSOR_CX - CURSOR - A routine var or arg that holds a cursor definition.
effects:
- The CURSOR_CX changes from an inactive state to active, ready to be fetched from.
CURSOR_CLOSE()
purpose:
- Closes a
select
cursor context
when
you're done
with
it.
context:
- CURSOR_CX - CURSOR - The
open
cursor context to be closed.
effects:
- The CURSOR_CX changes from an active to an inactive state.
CURSOR_FETCH()
purpose:
- Reads one row from an opened cursor and puts it in a ROW variable.
context:
- CURSOR_CX - CURSOR - An
open
cursor to provide
our
action context.
args:
- INTO(1) - ROW - The ROW variable being fetched into.
SELECT()
purpose:
- Fetches either one row or all rows from a table/view and puts it in a ROW/RW_ARY variable.
- The container type of the target variable determines how many rows are fetched.
context:
- CONN_CX - CONN - An
open
database connection to provide
our
action context.
args:
- SELECT_DEFN(1) - SRT_NODE:view - The view that defines this
select
action.
- INTO(1) - ROW|RW_ARY - The ROW/RW_ARY variable being fetched into.
INSERT()
purpose:
- Inserts the contents of a ROW/RW_ARY variable into a table/view.
- The container type of the source variable determines how many rows are inserted.
context:
- CONN_CX - CONN - An
open
database connection to provide
our
action context.
args:
- INSERT_DEFN(1) - SRT_NODE:view - The view that defines this insert action.
UPDATE()
purpose:
- Updates between zero and all rows in a table/view.
- The
"where clause"
of the view determines how many rows are updated.
context:
- CONN_CX - CONN - An
open
database connection to provide
our
action context.
args:
- UPDATE_DEFN(1) - SRT_NODE:view - The view that defines this update action.
DELETE()
purpose:
- Deletes between zero and all rows in a table/view.
- The
"where clause"
of the view determines how many rows are deleted.
context:
- CONN_CX - CONN - An
open
database connection to provide
our
action context.
args:
- DELETE_DEFN(1) - SRT_NODE:view - The view that defines this
delete
action.
COMMIT()
purpose:
- Commits the current transaction, then starts a new one.
context:
- CONN_CX - CONN - An
open
database connection to provide
our
action context.
ROLLBACK()
purpose:
- Rolls back the current transaction, then starts a new one.
context:
- CONN_CX - CONN - An
open
database connection to provide
our
action context.
According to SQL-1999, a transaction affects all types of SQL statements;
if
one is rolled back then it
has
no
affect on SQL-schemas or SQL-data. Or
maybe it isn't quite that simple; see section 4.8.5 of SQL-1999.
The following possible built-ins currently lack rigorous definitions.
LOCK -
UNLOCK -
PLAIN -
THROW -
TRY -
CATCH -
IF -
ELSEIF -
ELSE -
SWITCH -
CASE -
OTHERWISE -
FOREACH -
FOR -
WHILE -
UNTIL -
MAP -
GREP -
REGEXP -
LOOP - a loop/iterator context inside a routine, such as a
'foreach'
CONDITION - a conditional context inside a routine, such as an
'if'
LOGIC - some control-flow logic operation ...
This set of built-in standard routines may be invoked within any routine or view, client or db side, usually with 'routine_expr' or 'view_expr' Nodes.
CAST()
purpose:
- Cast a single child expression into a different specified
scalar
data-type.
args:
- CAST_TARGET(1) - SRT_NODE:scalar_data_type,scalar_domain - The data type to cast the operand as.
- CAST_OPERAND(1) - SCALAR - The data value we are casting into a data type.
returns:
- SCALAR - The new value having the target data type.
NOT - a logical
'not'
, true iif lone arg is false
FACTOR(1) - the value being not-ed
AND - a logical
'and'
, true iif every arg is true
FACTORS(N) - the list of
values
being and-ed
OR - a logical
'or'
, true iif at least one arg is true
FACTORS(N) - the list of
values
being or-ed
XOR - a logical
'xor'
, true iif 1+ arg true and 1+ arg false
FACTORS(N) - the list of
values
being xor-ed
EQ - true
if
both args are equal (both args cast same tp)
LHS(1) - the left-hand operand
RHS(1) - the right-hand operand
NE - true
if
both args are unequal (
when
same data type)
LHS(1) - the left-hand operand
RHS(1) - the right-hand operand
LT - true
if
first arg is less than second
LHS(1) - the left-hand operand
RHS(1) - the right-hand operand
GT - true
if
first arg is greater than second
LHS(1) - the left-hand operand
RHS(1) - the right-hand operand
LE - true
if
first arg is less than or equal to second
LHS(1) - the left-hand operand
RHS(1) - the right-hand operand
GE - true
if
first arg is greater than or equal to second
LHS(1) - the left-hand operand
RHS(1) - the right-hand operand
IS_NULL - true
if
only arg is a null value
ARG(1) - the value being evaluated
NOT_NULL - true
if
only arg is not a null value
ARG(1) - the value being evaluated
COALESCE - returns first arg which is not null (like Oracle
'NVL'
)
TERMS(N) - the list of
values
being coalesced
SWITCH - a logical switch-case expr (like Oracle
'DECODE'
)
LOOK_IN(1) - the value to evaluate
for
the switch expression
CASES(N) - the list of pairs (two-val-LISTs) of test expressions plus
return
values
DEFAULT(1) - the
default
return
value
LIKE - true
if
first arg contains second; args 3,4 are flags
LOOK_IN(1) - the string to look in
LOOK_FOR(1) - the string to look
for
FIXED_LEFT(1) - the look-
for
string must match the leftmost look-in chars
FIXED_RIGHT(1) - the look-
for
string must match the rightmost look-in chars
ADD - sum result of adding all args as numbers
TERMS(N) - the list of
values
being added
SUB - difference result of subtracting second arg from first
START(1) - the value being subtracted from
REMOVE(1) - the list of
values
being subtracted
MUL - product result of multiplying all arguments
FACTORS(N) - the list of
values
being multiplied
DIV - quotient result of dividing first argument by second
DIVIDEND(1) - value being divided up
DIVISOR(1) - value being divided by
DIVI - integral division of first arg by second
DIVIDEND(1) - value being divided up
DIVISOR(1) - value being divided by
MOD - modulus of integral division of first arg by second
DIVIDEND(1) - value being divided up
DIVISOR(1) - value being divided by
ROUND - rounds first arg to N dec places; N is second arg or 0
START(1) - value being rounded
PLACES(1) - how many decimal places to round to (
default
is 0, to get a whole number)
ABS - absolute value of the operand (distance from zero)
OPERAND(1) - value we start
with
POWER - raises first arg to the power of the second
RADIX(1) - the radix/base
EXPONENT(1) - the exponent
LOG - logarithm of the first arg on the base of second
START(1) - the starting value
RADIX(1) - the radix/base
SCONCAT - L.cstr concat of all arguments
FACTORS(N) - the strings being concatenated
SLENGTH -
length
of input string in characters
SOURCE(1) - the string to find the
length
of
SINDEX -
pos
in arg 1 of arg 2
if
present, start at arg 3
LOOK_IN(1) - the string to look in
LOOK_FOR(1) - the string to look
for
START_POS(1) - how many chars from the start of the string to start (
default
is first)
SUBSTR -
substr
in arg 1 starting
pos
arg 2 of
length
arg 3
LOOK_IN(1) - the string to extract from
START_POS(1) - how many chars from the start of the string to start (
default
is first)
STR_LEN(1) - how many chars to extract (
default
is all from offset)
SREPEAT - L.cstr concat arg 1 to self repeated by arg 2 instances
FACTOR(1) - the starting value
REPEAT(1) - how many
times
to repeat
STRIM - trims leading and trailing whitespace from arg 1
SOURCE(1) - the starting value
SPAD - lengthens arg 1 to
length
of arg 2 using arg 3 or space
SOURCE(1) - the starting value
SPADL - like spad but add filler on left rather than right
SOURCE(1) - the starting value
LC - lowercases latin chars in a string (SQL:2003 says this is a type of
"folding"
)
SOURCE(1) - the starting value
UC - uppercases latin chars in a string (SQL:2003 says this is a type of
"folding"
)
SOURCE(1) - the starting value
If any description contains
"aggregate"
, it means the function is an
aggregate function (a grouping func, like
'count'
).
COUNT - aggregate - count of rows a view/cursor can see
MIN - aggregate - minimum of
values
in all records in one view col
FACTOR(1) - the table col or
sub
-query whose
return
values
we aggregate
MAX - aggregate - maximum of
values
in all records in one view col
FACTOR(1) - the table col or
sub
-query whose
return
values
we aggregate
SUM - aggregate - sum of
values
in all records in one view col
FACTOR(1) - the table col or
sub
-query whose
return
values
we aggregate
AVG - aggregate - average of
values
in all records in one view col
FACTOR(1) - the table col or
sub
-query whose
return
values
we aggregate
CONCAT - aggregate - L.cstr concat of
values
in all records in one view col
FACTOR(1) - the table col or
sub
-query whose
return
values
we aggregate
EVERY - aggregate - is true
when
all rec
values
in one col are true
FACTOR(1) - the table col or
sub
-query whose
return
values
we aggregate
ANY - aggregate - is true
when
at least one rec value in one col is true
-
'SOME'
is a synonym
for
'ANY'
, according to MySQL
FACTOR(1) - the table col or
sub
-query whose
return
values
we aggregate
EXISTS - aggregate - is true
when
if
there are > 0 rows
FACTOR(1) - the table col or
sub
-query whose
return
values
we aggregate
FACTORS(N) - the
sub
-exprs defining the grouping operation
FACTORS(N) - the
sub
-exprs defining the grouping operation
FACTORS(N) - the
sub
-exprs defining the grouping operation
Note that the function list is incomplete and will grow over
time
. It does not currently
mention functions
for
working
with
view/cursor subqueries, such as
"in"
(field in subquery),
and also doesn't include any list operators, such as
for
comparing a list of field names to
a subquery that returns multiple columns (as I suspect MySQL 4.1 supports).
I took out CROWID, CROWNUM, CLEVEL; there exist replacements
for
such as these,
such as in SQL:2003, 7.14
"<search or cycle clause>"
(p365).
Note: While it might conceptually go here, statements of type DECLARE will
be added to a block/routine implicitely by the fact that routine_var Nodes exist.
The SQL:2003 standard says DECLARE appears inside a BEGIN ... END block, not
before
it such as Oracle does. It looks like
"DECLARE <var name> <type name> [DEFAULT <expr>]"
.
The standard also says
"SET foo = expr;"
rather than the
"foo := expr;"
as in Oracle.
simple_scalar_type
These represent the basic scalar data value types that can be used to further qualify variables (or literals) whose container_type is SCALAR. The 1999 SQL standard indicates 5 classes of simple (Predefined) data types: Numeric, String, Boolean, Datetime, Interval; all of the data types in those classes are represented by at least one of the following enumerated values. The 2003 SQL standard adds a 6th one, XML, but we will ignore that one for the moment; you can store XML in a STR_CHAR.
NUM_INT - an exact integral or whole number/numeric
NUM_EXA - an exact decimal or fractional number/numeric
NUM_APR - an approximately represented number/numeric (eg: floating point)
STR_BIT - a string of bits or generic binary data (can store anything)
STR_CHAR - a string of characters (encoding spec
defined
separately)
BOOLEAN - only two
values
: false or true
DATM_FULL - a full datetime (
'timestamp'
) having both YMD and HMS components
DATM_DATE - a valid Gregorian (says the 2003,1999 standard) date having just YMD
DATM_TIME - a valid
time
of day having just HMS components
INTRVL_YM - a
time
duration having year-month (YM) intervals (year or month or both)
INTRVL_DT - a
time
duration having day-
time
(DHMS) intervals (but not year or month)
char_enc_type
These represent the set of known character string encodings or named sets of characters (character repertoires) that can be used by character data, with Unicode (or UTF-8 specifically) being the recommended one to use when possible, and with Ascii being the most common legacy encoding. Encodings are only applicable to data whose simple_scalar_type is STR_CHAR.
UTF8 - unicode UTF-8 (always byte-order preserving;
no
big/little endian, usually 1 octet)
UTF16 - unicode UTF-16 (
has
big/little endian versions)
UTF32 - unicode UTF-32 (
has
big/little endian versions)
ASCII - 8-bit ascii
ANSEL - 8-bit ansel (a standard-
defined
super-set of ascii, used by libraries, genealogy)
EBCDIC - 8-bit ebcdic
Others will be added as appropriate.
calendar
These represent the set of known calendars which a date and time (data) can be relative to and counted with. For each, year zero could be at a different event, and other differences like what months or other yearly divisions there are would also distinguish the calendars. Use "absolute" if you don't want to pick any calendar in particular. These are only applicable to data whose simple_scalar_type is DATM_FULL or DATM_DATE (or perhaps DATM_TIME also).
ABS -
"absolute"
,
for
non-calendar-specific dates and
times
GRE - the Gregorian calendar
JUL - the Julian/Roman calendar
CHI - the Chinese calendar
HEB - the Hebrew calendar
ISL - the Islamic calendar
JPN - the Japanese calendar
privilege_type
These represent the basic types of privileges that database roles can be granted, and that by extension users granted those roles can have.
ALL - user/role
has
all privileges possible
for
the associated object
SELECT - user may
select
rows from a table
DELETE - user may
delete
rows from a table
INSERT - user may insert rows into a table
UPDATE - user may update rows in a table
The above options are understood. These next options I'm less sure about, and most of them aren't mentioned in section 12.3 (p739) of SQL:2003.
CONNECT - user may
connect
/
open
/attach to the catalog that is the parent of the role
EXECUTE - user may execute a routine
CREATE - user may create objects in a schema
ALTER - user may alter objects in a schema
DROP - user may drop objects in a schema
Aside from ALL, SELECT, DELETE, INSERT, UPDATE, the SQL:2003 standard also defines REFERENCES, USAGE, TRIGGER, UNDER, EXECUTE, but I don't yet understand what most of these mean.
table_index_type
These represent the basic index types that database tables can have applied to them; each instance of these can apply to multiple columns at once. All indexes are intended to make accessing table data faster, but some also will impose constraints on what data can be put in the columns.
ATOMIC -
"normal"
index
makes comparisons against col value as a whole faster,
no
constraints
FULLTEXT -
index
makes searches
for
words within a col value faster (and weighted),
no
constraints
UNIQUE -
index
imposes a unique key constraint on a set of columns
FOREIGN -
index
imposes a many-to-one foreign key constraint on a set of columns
UFOREIGN -
index
imposes a one-to-one foreign key (assuming target is a unique key)
Note that the FULLTEXT
index
currently takes
no
arguments
when
it is getting
set up, such as a customizable stopword list; that will be added later.
Note that the UFOREIGN type is interchangeable
with
UNIQUE+FOREIGN
for
same table cols.
view_type
These represent the basic types that a database view or SQL query can be. All types of views are very similar in structure, but they are not the same; by setting the view_type of a view, you are saying which differences apply to that view and possibly any child views.
ALIAS - view is an alias
for
a single other view, or a table, or a ROW/RW_ARY routine var/arg
JOINED - view is a non-grouped query having >= 1 other views joined together
GROUPED - view is a grouped query having >= 1 other views joined together
COMPOUND - view is a compound query of >= 1 other views
INSERT - view is an
"insert statement"
definition
UPDATE - view is an
"update statement"
definition
DELETE - view is a
"delete statement"
definition
compound_operator
These represent the standard ways that rows returned from multiple compatible views/queries can be merged into a single result set, where all of the input rows have the same set of columns as the the output rows, and each single output row matches a distinct input row. A union is the most common such operation. Each of these compound operators is by definition ambiguous as to whether the output set includes all allowed input rows or just distinct ones; you must keep an associated "set quantifier" value to specify ALL or DISTINCT.
UNION - all rows that are
each
in at least one input set (logical
'or'
or
'plus'
)
DIFFERENCE - all rows from one input set that are not in the others (
'except'
,
'minus'
)
INTERSECTION - all rows that are
each
in every input set (logical
'and'
or
'multiply'
)
EXCLUSION - all rows that are
each
in exactly one input set (logical
'xor'
)
The above terms are generic set logic nouns, SQL differs in places.
join_operator
These represent the standard ways that columns from two tables or views can be joined side by side into a single row set, where all values in each output column are from exactly one input, and values in columns from separate sources are aligned into common rows on related row values. This is the most common way of "relating" data in relational databases.
CROSS - a cross-
join
; a cartesian product; every combination of first and second rows kept
INNER - an inner/equal-
join
; only rows
with
related entries in both sources are kept
LEFT - a left-outer-
join
; all rows from first source kept even
if
not matched in second
RIGHT - a right-outer-
join
; all rows from second source kept even
if
not matched in first
FULL - a full-outer-
join
; all rows from both sources kept even
if
un-matched
There is
no
concept of a
"natural"
join
at this level of activity, and should not be.
Use CROSS
if
normal joins not sufficient; where-clause used
for
basic matching.
Note: SQLite supports this: [NATURAL ][LEFT |RIGHT |FULL ][OUTER |INNER |CROSS ].
view_part
These represent the standard parts of a view/query definition that are not in the "from" part.
RESULT - the SQL
"select list"
part; one item per view column/field
SET -
when
outermost view is an
"update statement"
, this is the
"set"
part
FROM -
when
view source in
"from"
part is a named subquery
with
args, these are arg
values
WHERE - the SQL
"where"
part
GROUP - the SQL
"group by"
part
HAVING - the SQL
"having"
part
WINDOW - ... to replace ORDER,MAXR,SKIPR; or such as those will implement WINDOW instead
ORDER - the SQL
"order by"
part
MAXR - says to page the results, and how many rows per page, corresponds to SQL
"limit"
first part
SKIPR - says how many of the first rows to skip
before
returing the
next
ones, corr to
"limit"
second part
Note that SQL::Routine may need more metadata to represent parts of a GROUP BY
clause, such as OLAP extensions ... or it may not.
Note that details
for
the INTO portion of a
"select|fetch into"
query is
stored in
'view'
Nodes, and so isn't represented by the view_part
enumerated list. So is the SET portion of an
"insert statement"
.
According to SQL:2003 Foundation 7.11 (p331), the WINDOW clause in a query seems
to contain/replace the likes of ORDER BY, LIMIT/MAXR, OFFSET/SKIPR, but it is
more powerful than that. SQL::Routine may need more metadata to
do
this right.
and OFFSET
for
SKIPR. I may
do
that too.
routine_type
These represent the basic types that a routine can be, since SQL::Routine "routines" are generic enough that each of the following can be represented by them: stored procedures and functions, triggers, and packages containing stored procedures and functions; all of the previous can also have nested stored procedures and functions; all of the previous can have nested un-named routines as well, called BLOCK.
All routine types (except packages?) can incorporate a series of statements plus conditionals or iterators, and all kinds can declare or use local variables, and they can all contain other routines of certain types. As such, routines arranged in a hierarchy of parents and children, where a root routine is one that is not contained in another. All routine types can take arguments of some sort, but your choice of type can severely limit what kinds of arguments you can take. Only some routine types, functions mainly, can return a value. Only some routine types have names.
PACKAGE - an Oracle
package
; a namespace beneath a schema
for
related funcs and procs
TRIGGER - a trigger on a table or schema view
PROCEDURE - a named stored procedure, under a schema|
package
|app|other-rtn
FUNCTION - a named stored function, under a schema|
package
|app|other-rtn
BLOCK - declares a compound statement that can contain other statements; eg BEGIN ... END
This list is probably missing some items.
basic_trigger_event
These represent the basic event types that can cause a trigger routine to execute.
BEFR_INS -
before
insert
AFTR_INS -
after
insert
INST_INS - instead of insert
BEFR_UPD -
before
update
AFTR_UPD -
after
update
INST_UPD - instead of update
BEFR_DEL -
before
delete
AFTR_DEL -
after
delete
INST_DEL - instead of
delete
Note: Some databases (eg: SQLServer 2000) can let a trigger run
"instead"
of the triggering
event, though the SQL:2003 standard does not mention such a feature.
Note: It appears that triggers (in Oracle at least) can have names and a much more complicated
condition as its
"event"
; it also can be attached to things other than tables.
It can also exist in a separate schema. Keep that in mind
for
a future update.
user_type
These represent the basic types of database users that you can have.
ROOT - the
default
user that owns the entire database install; login name usually set by RDBMS product
SCHEMA_OWNER - owns one or more database schema that are shared
with
others, perform DDL operations there
DATA_EDITOR - does not own any shared schema, usually can
do
DML only, typ stores data in a shared schema
ANONYMOUS - can
connect
to a database without username or password, owns nothing, usually very limited
SEE ALSO
SQL::Routine and the various other modules mentioned in its SEE ALSO.