NAME
SQL::Routine::Language - What language or grammar SQL::Routine speaks
COPYRIGHT AND LICENSE
This file is part of the SQL::Routine library (libSQLRT).
SQL::Routine is Copyright (c) 1999-2004, 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) version 2 as published by the Free Software Foundation (http://www.fsf.org/). You should have received a copy of the GPL as part of the SQL::Routine distribution, in the file named "LICENSE"; 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
SQL::Routine contains SQL schemas and queries, represented as a tree of atomic tokens; it is structurally like an abstract syntax tree or an XML DOM, but one that only accepts, respectively, a specific source language or XML schema. See the POD built in to SQL::Routine for more details.
The module's API and code make it look like a generic tree, composed of related Nodes. The restrictions for which attributes each Node can have, and its relationship to others, is defined by data (though for efficiency, that data is also contained in the same module and can't be changed at runtime).
As an analogy, the module has an API like a generic XML DOM, but it can enforce a specific XML Schema (the data). The context in which it is used is like a generic database interface. The API basically has an "execute" function, to which a SQL string is given, within that, there is a huge amount of flexibility of what the SQL string can say, but it must conform to a specific grammar.
This document, SQL::Routine::Language, is meant to say what all the 'types' of Nodes are, and what attributes and relationships are allowed for each. It is meant to say what 'grammar' for SQL::Routine's 'language' is, or what 'schema' it accepts.
The type of information this document would provide is functionally similar to the SQL design documents, or vendor-specific ones.
FEATURE SCOPE AND STANDARDS COMPLIANCE
SQL::Routine mainly follows its own path regarding its language structure; it approximates other SQL variants, including both ANSI/ISO standard SQL and proprietary versions like PL-SQL and T-SQL, but it matches none of those. I believe that such existing versions of SQL are simply too large and complex to support in their entirety. Moreover, such as with most software, we have a situation where 10% of the features are used 90% of the time, or 90% of users never exercise more than the same 10% of the available features. Or, perhaps those numbers should read 99% and 1% instead of 90 and 10.
SQL::Routine aims to be a "90%+ solution", natively supporting what I consider to be the "most important" features of SQL, those which have stood the test of time, become widely used, and supported by multiple vendors. This includes many "advanced" features which shipping database products often lack, and even a handful which no database may support now but "should". I have mainly avoided features which are new and unused, or unproven, or complicated, or badly designed, or antiquated, or otherwise look like a product of creeping featuritis. This is not to say that I reject new and little used features; if I see their benefit and they are easy to implement, they get supported now.
SQL::Routine is designed largely in the hindsight of the ANSI/ISO SQL:1999 standard (and with some foresight of the yet-unfinalized SQL:2003) and aims to conform to it as much as possible. Where SQL:2003 and other SQL versions talk about the same features using different words, SQL::Routine will either match the standard or resemble it the most. Similarly, as SQL:2003 and SQL:1999 are fully upward compatible with SQL:1992, so the latter will be matched. Unlike most shipping database products, which support mainly SQL:1992, I will support SQL:2003 (and 1999). Note that SQL:2003 and SQL:1999 has identical ANSI and ISO standards, while SQL:1992 does not; this is a practical reason to focus on just 2003 and 1999.
During my work on this project, I found the following resources to be particularly useful:
http://www.wiscorp.com/sql/sql1999_c3.zip (warning, large file)
http://www.wiscorp.com/sql/sql_2003_standard.zip (warning, large file)
http://www.sql-scripts.com/BookExcerpts/SQL99.zip (warning, large file)
Likewise, the vendor documentation for MySQL and SQLite was consulted, plus third-party and vendor documentation for Oracle, and the odd other SQL site.
This is a brief and incomplete list of major supported features (the rest of this document goes into more detail on them):
- native Unicode 4 (UTF-8) support for all identifiers and character data
- multiple character encoding support in the same database for character data
- global binary/bit object/data support
- both exact and inexact numbers, the former being of any length
- large object support; all character or binary fields can contain 2GB or more
- multi-column indexes, unique constraints, foreign key constraints
- large/full text indexing
- boolean data type
- datetime/date, interval/time data types
- multiple schemas in the same catalogue
- objects in any schema can reference those in other schemas or other catalogues
- identifiers are internally case-sensitive; case-insensitive ones match as fully uppercase
- no effective limits; eg: number of tables or columns or rows, size of data per field or row
- simple domains and user-defined data types (UDTs)
- enumerated data types
- arbitrary 'check' constraints (?)
- routines (stored procedures, user defined functions, triggers)
- named sub-routines inside routines
- row, array/rowset data types as variables inside routines, and arguments and return values
- multiple column unique and foreign key constraints on tables
- viewed tables / views
- expressions in any part of a query
- regular expressions / pattern matching
- multiple-column predicates in any part of a query
- cross, inner, and full|left|right outer table joins
- self joins (same table or view joined to itself N times)
- compound queries such as union, difference (except), intersection, exclusion
- 'all' and 'distinct' set quantifiers on both normal and compound queries
- 'where', 'group by', 'having', 'order by', 'limit'
- anonymous subqueries in any part of main query
- named subqueries ("with") likewise
- both correlated (takes arguments) and uncorrelated (no arguments) subqueries
- recursive queries
- all routine and subquery args are declared and used with named syntax (rather than positional)
- all built-in standard routine args are used with named syntax (rather than positional)
- select/insert/update/delete rows in tables and viewed tables
- named (rather than positional) mapping of 'into' clause to 'select list'
- named (rather than positional) mapping of all input data for both inserts and updates
- OLAP extensions, rollup, cube, grouping sets (related to group-by)
- updateable views; inserts, updates, deletes through any view (if key data not lost)
- "insert as select"
- multiple users, roles, privileges
- create/alter/delete schema objects, including user defined functions
- create/alter/delete catalogs, schemas, users
- cursors
- multiple row insert / "write cursors"
- application host parameters for any data type
- all host parameters are named (rather than positional)
- explicit transactions and nested transactions (and savepoints?)
- record locking for read and locking for write
- throwing exceptions
- catching and handling exceptions
- create, use simple sequence generators, named and on table cols
- temporary objects, usually tables
- triggers firing on DML events of either tables or views
- "base tables" and "temporary base tables"This is a brief list of major non-supported features (at present), or current limitations:
- multiple distinct values per row field (this violates level-1 normalization)
- complex domains and compound user-defined data types
- 'set' data types
- collection types, such as arrays, stored in table fields
- reference data types, stored in tables
- table hierarchies or subtables; anything not a "base table" or "viewed table"
- object data types
- user defined "operators" (as being distinct from user defined functions)
- geographical data types and related extensions
- referencing objects in an "information schema" by users like normal schema objects (?)
- dynamic SQL; eg: generating and executing new SQL in a stored procedure
- creating user defined functions that aren't SQL (though they can be called)
- sequence generators will only produce integers, and not fractions or strings
- explic ref most "optimization" features; eg: hints, cache sett, res pre-alloc or limits, "tablespaces"
- explicit references to logs or replication managed by the database product itself
- named Oracle "synonyms" (?)
- triggers firing on non-DML eventsGenerally speaking, SQL::Routine holds to a purely relational database model, where every intersection of a column and a row in a table or view is an atomic value. Anything which breaks from this mold is currently not supported. This said, transient data such as variables in routines (stored procedures) can and often are more complex types like arrays or records or references; likewise input and output values of routines, and host parameters, can be complex types.
CIRCUMVENTING FEATURE LIMITATIONS
For such situations where you want to use SQL::Routine to mediate your access to a database, and it doesn't natively support a database product feature that you want to use, SQL::Routine does provide a "back door" where you have un-mediated access to the database. Speaking more accurately, SQL::Routine can store fragments of any raw SQL that you specify, to be injected into the SQL that is generated from its objects.
The trade-off is that these portions probably won't work with more than one database product, and so you would be best to just use them for "optimization" type SQL that will help your app on a supported product, but the app will still work (perhaps slower) without them. If you can't do that, then SQL::Routine lets you store alternative SQL fragments that are keyed to a database product; if you store fragments with the same function in multiple versions, then the one for the product you are interfacing now will be used and the others ignored.
SQL fragments can either be pieces of a larger to-be-generated statement which you attach them to, or they can be complete SQL statements on their own, where you specify that they run between certain generated statements, or inside a generated routine.
For the present, you can also store "SQL comments" in this fashion.
All this said and done, the use of the circumvention features are not recommended, and should only be attempted by savvy / power users or DBAs. Ultimately, adding native support for a feature is better.
LANGUAGE CONCEPT STRUCTURE
SQL::Routine manages SQL concepts that we arrange into 5 main categories for convenience: 'elements', 'blueprints', 'tools', 'sites', 'circumventions'.
The first two of these, 'elements' and 'blueprints', are the most important; they describe completely the composition and behaviour of your database schemas and application-level routines that talk to them. For all intents and purposes, they are part of your core application code and would get distributed as such; like your core application code, everyone's installation will be identical. We keep simple and widely re-used details in 'elements', such as scalar and row data type definitions. We describe most model structures in 'blueprints', including database tables (with constraints), arbitrarily complex database views, and various kinds of routines (such as stored procedures, user defined functions, and basic DML like selects, inserts, updates, deletes), both those stored in a database schema or in an application. We can represent multiple database catalogs and applications at once, whether or not they talk to each other; how they inter-relate is also represented.
The second two categories, 'tools' and 'sites', are effectively locale or site-specific configuration data, and each installation of your application would be different in these areas. We use 'tools' to store a small list of one or more externally (to SQL::Routine and your application core) recognizable database product identifiers (such as SQLite or Oracle) that are used on a site to implement the database catalogs. We use 'sites' to indicate localized details of your database (and application) installations, such as the product used for each catalog instance, or its DSN or server address or file path, or what users it has and their privileges. If you have multiple copies of a database installed, such as for test data vs production data, you can distinguish between them in 'sites'.
The last category, 'circumventions', is used to implement SQL::Routine's "circumvention" features, to access concepts that a specific database product understands but nothing in the SQL::Routine code does.
While a database and application are actually being *used* (post-installation at a site), structures belonging to each of the 5 categories would be stored persistantly in two main places. One of these is the native schema of a database itself. The second of these is structures that are not stored in a database, but in an application that uses the database. Many structures, such as views and routines, can be stored in either place; some, like tables, can probably only be stored in the database; either way, the places can overlap greatly. In practice, you would pick what to put in each place on a per-case basis, accounting for how powerful your database is, what arrangement is more secure, or is faster, or is easier to manage, and so on.
Here is a tree diagram, formatted visually like XML, which shows the high level SQL::Routine language concepts; each distinct concept is represented by one tree node. Parent nodes provide a context in which their children live (eg: a table only exists within a schema). The top 2 tree levels don't represent anything themselves, but just group the actual concepts that exist in the 3rd level and below. The node attribute 'c' shows the cardinality of that node type beneath its parent; a value of 'M' means 'many' and '1' means 'one'. The node attribute 'r' indicates recursion, where a node can be a child of another of the same type (eg: a sub-query or sub-routine).
<root>
<elements c="1">
<scalar_data_type c="M">
<scalar_data_type_opt c="M" />
</scalar_data_type>
<row_data_type c="M">
<row_data_type_field c="M" />
</row_data_type>
</elements>
<blueprints c="1">
<catalog c="M">
<owner c="M" />
<catalog_link c="M" />
<schema c="M">
<scalar_domain c="M" />
<row_domain c="M" />
<sequence c="M" />
<table c="M" />
<view c="M" />
<routine c="M" />
</schema>
<role c="M">
<privilege_on c="M">
<privilege_for c="M" />
</privilege_on>
</role>
</catalog>
<application c="M">
<catalog_link c="M" />
<scalar_domain c="M" />
<row_domain c="M" />
<sequence c="M" />
<table c="M" />
<view c="M" />
<routine c="M" />
</application>
</blueprints>
<tools c="1">
<data_storage_product c="M" />
<data_link_product c="M" />
</tools>
<sites c="1">
<catalog_instance c="M">
<catalog_instance_opt c="M" />
<catalog_link_instance c="M" />
<user c="M">
<user_role c="M" />
</user>
</catalog_instance>
<application_instance c="M">
<catalog_link_instance c="M" />
</application_instance>
</sites>
<circumventions c="1">
<sql_fragment c="M" />
</circumventions>
</root>
<table>
<table_field c="M" />
<table_index c="M">
<table_index_field c="M" />
</table_index>
</table>
<view r="Y">
<view_arg c="M" />
<view_src c="M">
<view_src_arg c="M" />
<view_src_field c="M" />
</view_src>
<view_field c="M" />
<view_join c="M">
<view_join_field c="M" />
</view_join>
<view_compound_elem c="M" />
<view_expr c="M" r="Y" />
</view>
<routine r="Y">
<view c="M" />
<routine_context c="1" />
<routine_arg c="M" />
<routine_var c="M" />
<routine_stmt c="M">
<routine_expr c="M" r="Y" />
</routine_stmt>
</routine>
<catalog_link_instance r="Y">
<catalog_link_instance_opt c="M" />
</catalog_link_instance>For the most part, each 'concept' shown in the tree above corresponds to a single SQL::Routine Node Type. Each Node Type is explained in further detail below.
Besides the Node it is a child of, a Node can reference other Nodes almost anywhere else in the tree, with the following exceptions: 1. Considering the order of the 5 main categories above, Nodes in any category can not reference Nodes in a later category; they can only reference self or earlier; 2. Nodes in 'tools' can't reference anything else; 3. Catalog Nodes can't reference Application Nodes.
LANGUAGE PROCEDURAL STRUCTURE
The SQL::Routine language is designed with an embedded SQL paradigm in mind, where all SQL can be invoked as if it was written in the same language as the program code which invokes it. The SQL can take arguments like a normal program routine, and it can hand return values like a normal program routine; either of those can be considered named host parameters.
The SQL::Routine language is designed so that you would do all types of database interaction with it; not only would you use SQL to issue typical DML or DDL commands, but you would attach (connect to) or create a database in the first place using SQL. This is much like CLI database clients and their "connect to ... identified by ..." or "attach ... as ..." SQL statements, or similar statements that run inside a database stored procedure that connect to a second database. In fact, the SQL::Routine language is designed such that you should be able to run the same SQL commands either in a client application or in a database stored procedure, without changes. Likewise, your application should be able to invoke a SQL routine the same way whether it is stored in the application or the database. By contrast, some other modules for letting your application talk to a database require you to call non-sql routines for some operations like connecting to a database.
The SQL::Routine language takes a broad definition as to what constitutes a SQL routine. While some RDBMS products don't support this in stored procedures, my understanding is that the SQL standard defines a SQL routine as an ordered (and usually named) sequence of any type of SQL statements that are to be run together. Usually, SQL routines stored in a database only contain DML, but some RDBMS products also let you manipulate users or schema objects within stored procedures. Regardless of those limitations, my understanding is that any SQL which an RDBMS product understands at all can be issued from an application based routine.
In practice, the way of providing arguments to named database stored routines can be different from a way passing arguments to un-named routines or queries constructed on a client application; the latter often uses 'host parameters' for sending or receiving data, while the former uses named routine arguments and/or has an explicit return value. The SQL::Routine language unifies these concepts and makes it as if all SQL statements take named arguments, and "return" a value; in database stored procedures, this is the same as usual; on the client side, the named arguments each correspond to a named host parameter.
Similarly, all SQL "select" queries are treated as cursors; on the database side this is the same as usual; on the application/client side, this may or may not be the same as usual. When an application invokes a "select", they will get a cursor handle returned to them (either a real cursor or a fake cursor), off of which they fetch the rows or values that the "select" retrieves.
It is expected that modules which interface a database on an application's behalf, and that take SQL::Routine language constructs as input, will be structured with a Command Design Pattern. They should essentially provide a "SQL Engine" object that has a single "Execute" function. The latter would take a SQL::Routine "routine" Node (or an object wrapping one that also holds values for the routine's arguments) as input. The function would return a "Result" object that encapsulates any return values from the SQL, such as a select cursor handle or other types of returned data or details of an error that occurred. The object returned as output from "Execute" should also have its own "Execute" method when the object can be considered a context for future commands; eg: the object returned from an "open database" command should represent that database for further queries against it.
The SQL::Routine language does not require you to explicitly run a connect/open/attach command before running SQL against a database. Since the info it needs to do this is provided in 'catalog link' definitions that are already related to the SQL definitions, an implementer can just implicitely connect to a database for you when you ask it to run the SQL. And that implicit link can either be cached or new, such as the implementer decides. Note that this idea may need to be thought out further.
NODE ATTRIBUTE TYPES
There are 4 major SQL::Routine Node attribute types, and a Node object provides separate accessor methods for those attributes of each type (a consistency held between the Perl and C versions). Each major type may be further sub-divided into minor types, which are tested for by the above methods. Each instance of a Node attribute corresponds to one XML tag attribute, if a Node object were an XML tag. The types are:
node_id - There is exactly one of these per Node; it is a positive (<gt>=1) integer which uniquely identifies this particular Node among all others of the same Node Type. There are no minor types under this major type.
literal - This is the normal kind of Node attribute, and it can contain any scalar value, such as a string or a number. Some attributes of this type may actually be restricted, such as to only valid integer or date or boolean values; that restriction is what differentiates the literal minor types.
enumerated - This is like literal except that the values you can store are restricted to an explicit enumerated set of values. Each minor type is a specific enumerated value set. (A subsequent documentation section lists the currently known enumerated sets.)
node_ref - This is a number which is equal to the node_id of another Node that has a particular Node Type; it is used to implement relations between Nodes. This attribute type is for attributes whose conceptual values are either multi-part or are possibly shared by multiple Nodes. Each minor type is a Node Type.
LITERAL ATTRIBUTE TYPES
Currently, SQL::Routine recognizes these explicit literal attribute types:
bool - This is a boolean type, and its values may only be '0' or '1'. Attributes of this type are "flags", saying 'true' when set or 'false' when not.
uint - This is an "unsigned integer", also known as a non-negative integer; its values may only be integers greater than or equal to 0. Attributes of this type indicate the "scale" of something, meaning how 'big' or 'long' it is.
sint - This is an "signed integer", and its values may be any integer. Attributes of this type are normally used in concert with sequence generators.
cstr - This is a "character string" where each character can be anything except the null character. Attributes of this type are usually "labels" and "name" something.
misc - This is a "miscellaneous scalar"; its values may be anything a Perl scalar can be. Attributes of this type usually hold literal data that only has meaning to a user and is not interpreted by a machine; alternately, they are used to store any literal whose type varies or isn't a bool or uint or cstr.
ENUMERATED ATTRIBUTE TYPES
These are the enumerated attribute 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 contextNote 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 failsMost 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 - 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 - SRT_NODE:catalog_link - The catalog to scan.
- RECURSIVE - 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 analagously to CATALOG_INFO() except that no changes
are made to the SQL::Routine::Container; existing Nodes therein are
compared to instead.
args:
- LINK_BP - SRT_NODE:catalog_link - The catalog to scan.
- RECURSIVE - 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 - SRT_NODE:catalog_link - The catalog to create.
- RECURSIVE - 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 - 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 - SRT_NODE:catalog_link - The catalog to copy from.
- DEST_LINK_BP - 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 - SRT_NODE:catalog_link - The catalog location to move from.
- DEST_LINK_BP - 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 - SCALAR.cstr - Opt: A runtime-provided username to connect with.
- LOGIN_PASS - 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 - 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 - SRT_NODE:catalog_link - The catalog to connect to.
throws:
- SRTX_NO_CONN_ACTIVE_LOSTThis 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 routineThis 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 userThis 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 consistant 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 - 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.
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 - SRT_NODE:view - The view that defines this select action.
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 - 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 - 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 - 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 data-type specified by a scalar_domain node.
args:
- CAST_TARGET - SRT_NODE:scalar_data_type - The data type to cast the operand as.
- CAST_OPERAND - 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
GB_SETS - olap, use in group-by - produces GROUPING SETS ( sub-exprs )
FACTORS(N) - the sub-exprs defining the grouping operation
GB_RLUP - olap, use in group-by - produces ROLLUP ( sub-exprs )
FACTORS(N) - the sub-exprs defining the grouping operation
GB_CUBE - olap, use in group-by - produces CUBE ( sub-exprs )
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.
Note that some of these examples may be wrong for practical use and be changed later.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
Note that some of these examples may be wrong for practical use and be changed later.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 tableThe 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 schemaAside 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" definitioncompound_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.
Note that several databases like Postgres and SQLite use LIMIT for MAXR
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.
See this: http://www.cit.uws.edu.au/docs/oracle/sqlref/ch4h.htmuser_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 limitedNODE TYPES
These are the Node Types that SQL::Routine knows about; each one corresponds to a SQL::Routine concept. A Node Ref attribute of a certain type will reference another Node of that Node Type.
Each Node Type description below includes the attributes that are valid for Nodes of that type. For each attribute, it says what the attribute means, what major+minor type it is, and what kind of value constraints it must obey, including both independent and dependent constraints. Some attribute value constraints are constantly applied, while many of them are deferrable.
Each pseudo-Node also has a section that looks like one for a normal Node type; these sections are where certain details about Nodes that have pseudo-Node parents go, because those details are explained in terms of "child Nodes".
For each Node Type, there are several detail lists: Attribute List, Exclusive Attribute Groups List, Local Attribute Dependencies List, Child Quantity List, Distinct Child Groups List, Example List.
The Attribute List says what all of the attributes are, in order, along with each one's major+minor type, an indicator of whether it is a primary parent node candidate, and a description. Each list element is in this format:
<attr-name> - <major-type>.<minor-type>[ - <parent-flag>|<surrogate-id-flag>|<mandatory-flag>] - <description>In the above usage, the major-type is shortened from its full name like this: "literal" -> "L", "enumerated" -> "E", "node_ref" -> "N". When the major-type is "node_id" (the attr-name is always "id"), then "NODE_ID" appears in place of the combined major and minor types on the line. Every Node type's "id"/"NODE_ID" attribute is implicitly always-mandatory; moreover, that attribute is implicitly always-distinct within Nodes of the same Node type.
The parent-flag indicates that the attribute can be used to link the current Node to a "primary parent" Node. When a Container (or subset of its Nodes) is converted to a tree-like representation (such as XML), each Node will be output beneath its primary parent. For each Node, it is mandatory for exactly one primary parent candidate attribute to be set, and the others not set (unless the Node type has no candidates, has a pseudonode parent). The flag says "PP".
While there is no special code to draw attention to it, note that some primary-parent Node ref attributes are special in that they are directly-recursive; they facilitate the construction of N-depth Node trees where all Nodes in the tree have the same Node type. You can spot one of these by the fact that the attribute is both marked 'PP' and its minor type is the same as the Node type the attribute belongs to. The most common such trees are made from "[view|routine]_expr" Nodes, but others are also possible.
The surrogate-id-flag indicates that the attribute holds the surrogate identifier for the Node; this attribute's value is guaranteed to be distinct for all Nodes that have the same primary-parent Node, or that have a pseudo-Node parent, though the values for two Nodes with two different primary-parent Nodes can be the same. Every Node type's surrogate id attribute is always implicitly mandatory. For most Node types, the surrogate id attribute is called 'si_name' and is an "L.cstr" data type; their values correspond to the ANSI/ISO SQL:2003 standard's impression of a SQL "identifier", specifically an un-qualified SQL identifier. (Eg, a table's "si_name" is the table's "identifier".) The flag says "SI".
The mandatory-flag indicates that the attribute is always-mandatory, meaning the attribute must always be populated (not be null); in some cases, it means you explicitly provide a 'default' value such as zero. The flag says "MA".
The "Attribute List" contains all details that pertain to constantly applied attribute value constraints. One of these is that you can never set values of the wrong major+minor type to an attribute, guaranteeing they are type-correct at all times. You can also never set a Node's primary parent to a node ref attribute which isn't "PP". Another of these is that, when a Node is in a Container, its NODE_ID attribute must always be set, and have a distinct value relative to other Nodes of its type in the same Container. Another of these is that, when a Node is in a Container, any Node ref attributes can only point to Nodes that are simultaneously in the same container. As another, you can never set a primary parent for a Node which is a direct or indirect descendent of that Node, so circular references are guaranteed to never exist.
The "Attribute List" also contains a few details that pertain to deferrable constraints. One deferrable constraint is that a Node's NODE_ID attribute must be set; that is, this constraint is only deferrable for Nodes that are not in Containers. Another deferrable constraint is that exactly one primary-parent attribute must be set, if the Node has any "PP" attributes. Another deferrable constraint is that any "MA" attributes are populated. All "Attribute List" deferrable constraints can be tested on Nodes of any status, including "Alone".
The Exclusive Attribute Groups List says what groups of mutually exclusive attributes there are for a Node type besides the "PP" group; no more than one member of a group may have a set value at any given time. Each list element is in this format:
<mutex-name> - <attr-name>(,<attr-name>){1,}[ - <mandatory-flag>]For each mutex-name group, there are 2 or more attr-name which are members of the group; if mandatory-flag is present, which looks like "MA", then one member of the group must always be set; if mandatory-flag is not present, then it is fine for all group members to not be set. All "Exclusive Attribute Groups List" constraints are deferrable and can be tested on Nodes of any status, including "Alone".
The Local Attribute Dependencies List says what Node type attributes have dependencies in other attributes of the same Node they are in, such that the dependent attributes can not be set unless the depended-on attributes are valued and/or have specific determined values. Each list element is in this format:
<dep-on-attr-name>:
<dep-attr-name>(,<dep-attr-name>){0,}[ - <dep-on-attr-val>(,<dep-on-attr-val>){0,}][ - <mandatory-flag>]For each distinct depended-on attribute, there can be one or more dependent attributes; the latter can be of any attribute type. A dependent attribute may never be set unless its dependency is set, and, in the case of the depended-on attribute being an enumerated type, the depended-on attribute has the appropriate value; if mandatory-flag is present, then the dependent attribute must always be set when it may be set. It is possible that more than one distinct depended-on attribute value will satisfy the same dependent attribute, though in most cases only one value will do. If there are multiple dependent attributes shown on the same line (comma-delimited), then they are a mutual-exclusion group; only one of those may be set when the conditions say that any may be set. All "Local Attribute Dependencies List" constraints are deferrable and can be tested on Nodes of any status, including "Alone".
The Child Quantity List says when a Node must have a specific number of primary-child Nodes of a particular Node type (usually >= 1). Each list element is in this format:
<child-node-type> - <range-min>[ - <range-max>]For each child-node-type, there can be a limit on how few or how many primary-child Nodes of that Node type are allowed. A common range-min is 1, but if it is 0 then range-max will be valued. All "Child Quantity List" constraints are deferrable and can only be tested on "Well Known" Nodes.
The Distinct Child Groups List says what groups of mutually distinct primary-child Nodes there are for Nodes of a Node type; each member Node of a group must have distinct/unique values for particular attributes (usually "name") or attribute combinations. Each list element is in this format:
<mudi-name>:
<child-node-type> - <child-attr-name>(,<child-attr-name>){0,}For each mudi-name group there can be one or more Node attributes where the attribute value combination must be distinct, besides the constantly applied distinct NODE_ID/id constraint; a single child-attr-name on a line means only that one attribute is distinct within itself, where multiple child-attr-name indicate a combination to check. If there are multiple lines under a mudi-name, then each one will have a different child-node-type; the attributes that line up vertically between different Node types are meant to correspond, and would be of the same major+minor attribute type; each corresponding line would also have the same number of child-attr-name. For all comparisons, an undefined attribute value always counts as a distinct value; multiple undefined corresponding values will not violate the constraint (other constraints eliminate undefined values when they are a problem). The implicit distinct-id constraints all have a mudi-name of 'primary'. All "Distinct Child Groups List" constraints are deferrable and can only be tested on "Well Known" Nodes.
The Example List is a list of example Nodes for the Node type, formatted like a table, one row per Node, one column per attribute. Note that the order in which "Example List" rows/Nodes appear is significant.
root
This pseudo-Node is the root of a SQL::Routine and has as its children the other 5 pseudo-Nodes: elements, blueprints, tools, sites, circumventions.
elements
This pseudo-Node has two child Node-types: scalar_data_type, row_data_type.
Distinct Child Groups List:
ak_name:
scalar_data_type - si_name
row_data_type - si_namescalar_data_type
This type of Node describes a user-defined customization of a base simple_scalar_type, the latter of which SQL::Routine recognizes in code. This customization encompases several things: 1. The scalar_data_type can be referenced by entities such as domain definitions, table column definitions, view column definitions, and routine variables, as their "data type"; 2. The scalar_data_type can impose restrictions within the base data type as to what values can be stored (eg: a STR_CHAR may only be 30 characters long, or a NUM_EXA may only be 5 digits wide with 2 digits on the right of the radix point); 3. The scalar_data_type can define simple pre or post processing of values, such as trimming of bounding whitespace or uppercasing its contents.
For example, the user may decide to create a custom data type called "currency", which is a decimal number of precision 15 and a scale of 2. Or, they may want to have an "ip address" type, which is a string that can hold 15 characters (how ips are normally displayed to humans). Or, they may want a custom SIN or telephone-no or person-name type. They can then refer to these custom types everywhere as if they were types built-in to the database.
Note that SQL::Routine does not support the antiquated notion of having separate fixed-width and variable-width character strings as being distinct base data types. That is an implementation detail which database products should not expose in the user interface, or if they do, it should be demoted to an "optimization" feature used with a single character string type. When you make a scalar_data_type using the STR_CHAR base_type, it will have the semantics of SQL's "VARCHAR" by default; you can make it like "CHAR" by setting the store_fixed attribute to true.
Attribute List:
id - NODE_ID - unique identifier for each Node within this Node Type
si_name - L.cstr - SI - the name this scalar data type is identified by
base_type - E.simple_scalar_type - MA - name of the base data type this data_type is customized from
num_precision - L.uint - max length in digits for exact number, or count of significant digits for inexact
num_scale - L.uint - for non-integral numbers, how many digits can be on the right of the radix point
num_octets - L.uint - for integral or inexact (floating point) numbers, fixed storage size in octets
num_unsigned - L.bool - for numbers, true means only values >= 0 may be stored
max_octets - L.uint - maximum size in octets for a binary/bit string
max_chars - L.uint - maximum size in characters for a character string
store_fixed - L.bool - when true then try to reserve max size on disk even if not all used
char_enc - E.char_enc_type - which character encoding to use
trim_white - L.bool - when true then trim lead/trailing whitespace on save into STR_CHAR field/var
uc_latin - L.bool - do we uppercase latin chars in STR_CHAR before writing (no change on reading)
lc_latin - L.bool - do we lowercase latin chars in STR_CHAR before writing (no change on reading)
pad_char - L.cstr - when store_fixed is true then pad actual value with this to max size on save
trim_pad - L.bool - when store_fixed is true then trim padding on read from STR_CHAR field/var
calendar - E.calendar - what calendar the date and time (or date only) is in
with_zone - L.sint - if timestamp/time to include a 'with time zone', this says what time zone
range_min - L.misc - when applicable, what the allowed minimum value is (for bounds/check constraint)
range_max - L.misc - when applicable, what the allowed maximum value is (for bounds/check constraint)Exclusive Attribute Groups List:
num_size - num_precision,num_octetsLocal Attribute Dependencies List:
base_type:
num_precision - NUM_INT,NUM_EXA,NUM_APR
num_scale - NUM_EXA,NUM_APR
num_octets - NUM_INT,NUM_APR
num_unsigned - NUM_INT,NUM_EXA,NUM_APR
max_octets - STR_BIT - MA
max_chars - STR_CHAR - MA
char_enc - STR_CHAR - MA
trim_white - STR_CHAR
uc_latin,lc_latin - STR_CHAR
pad_char - STR_CHAR
trim_pad - STR_CHAR
calendar - DATM_FULL,DATM_DATE - MA
with_zone - DATM_FULL,DATM_DATE,DATM_TIME
num_precision:
num_scaleExample List:
|id|si_name |base |np |ns|no|nu|mxoct|mxch|fx|chenc|tw|uc|pc|tp|cal|
|--|--------|---------|---|--|--|--|-----|----|--|-----|--|--|--|--|---|
| 1|bin1k |STR_BIT | | | | | 1000| | | | | | | | | - a hunk of binary data of maximum 1000 octets
| 2|bin32k |STR_BIT | | | | |32000| | | | | | | | | - a hunk of binary data of maximum 32000 octets
| 3|str4 |STR_CHAR | | | | | | 4|1 |ASCII|1 |1 |20|1 | | - a fix-w 4 char ascii cstr, uppercased, spc-pad
| 4|str10 |STR_CHAR | | | | | | 10|1 |ASCII|1 | |20|1 | | - a fix-w 10 char ascii cstr, case-preserve, spc-pad
| 5|str30 |STR_CHAR | | | | | | 30| |ASCII|1 | | | | | - a fix-w 30 char ascii cstr, case-preserving
| 6|str2k |STR_CHAR | | | | | |2000| |UTF8 | | | | | | - a var-w 2000 char unicode cstr, case-preserving
| 7|byte |NUM_INT | | | 1| | | | | | | | | | | - an 8-bit precise int btwn -128 and +127
| 8|short |NUM_INT | | | 2| | | | | | | | | | | - a 16-bit precise int btwn -32,768 and +32,767
| 9|int |NUM_INT | | | 4| | | | | | | | | | | - a 32-bit precise int btwn -2,147,483,648 and +2,147,483,647
|10|long |NUM_INT | | | 8| | | | | | | | | | | - a 64-bit precise int btwn -(2^63) and +(2^63)-1
|11|ubyte |NUM_INT | | | 1|1 | | | | | | | | | | - an 8-bit precise integer between 0 and +255
|12|ushort |NUM_INT | | | 2|1 | | | | | | | | | | - a 16-bit precise integer between 0 and +65,535
|13|uint |NUM_INT | | | 4|1 | | | | | | | | | | - a 32-bit precise integer between 0 and +4,294,967,295
|14|ulong |NUM_INT | | | 8|1 | | | | | | | | | | - a 64-bit precise integer between 0 and +(2^64)-1
|15|float |NUM_APR | | | 4| | | | | | | | | | | - a 32-bit imprecise fractional number
|16|double |NUM_APR | | | 8| | | | | | | | | | | - a 64-bit imprecise fractional number
|17|dec10p2 |NUM_EXA | 10| 2| | | | | | | | | | | | - a precise 10-digit num with hundredths scale
|18|dec255 |NUM_EXA |255| | | | | | | | | | | | | - a precise 255-digit num with arbitrary scale
|19|boolean |BOOLEAN | | | | | | | | | | | | | | - a boolean; only 2 values of false or true
|20|datetime|DATM_FULL| | | | | | | | | | | | |ABS| - a date and time or date, non-calendar specific
|21|dtchines|DATM_FULL| | | | | | | | | | | | |CHI| - a date and time or date, in the Chinese calendar
|22|sex |STR_CHAR | | | | | | 1| | | | | | | | - a 1-char string, constrained to 'M' and 'F'
|23|str20 |STR_CHAR | | | | | | 20| | | | | | | | - a 20-char string
|24|str100 |STR_CHAR | | | | | | 100| | | | | | | | - a 100-char string
|25|str250 |STR_CHAR | | | | | | 250| | | | | | | | - a 250-char string
|26|entitynm|STR_CHAR | | | | | | 30| | | | | | | | - a short string suitable for naming things
|27|generic |STR_CHAR | | | | | | 250| | | | | | | | - a longer string that most kinds of data will fit inscalar_data_type_opt
When a "scalar_data_type" Node is meant to impose an enumerated list restriction, meaning that only a specific set of values may be stored, then you use a "scalar_data_type_opt" Node to define each enumerated value that is allowed. Keep in mind that the SQL NULL (undefined) value is always distinct from anything in this option list.
Attribute List:
id - NODE_ID - unique identifier for each Node within this Node Type
pp_scalar_data_type - N.scalar_data_type - PP - what "scalar_data_type" Node this option is associated with
si_value - L.misc - SI - this is the actual enumerated value that the scalar_data_type_opt Node representsExample List:
|id|dt|v|
|--|--|-|
| 1|22|M| - "sex" is "Male"
| 2|22|F| - "sex" is "Female"row_data_type
This type of Node describes the core of a row composite data type, one of which contains a list of named and typed scalar fields. Each 'table' (base table) and 'view' (viewed table or query definition) Node has a single associated 'row' Node that defines its "interface"; each 'table' and 'view' is composed of a set of rows (usually unordered) that all have the same type. Any routine variable with a container type of 'ROW' is defined by an associated 'row' Node, as is any 'RW_ARY' variable, which is an ordered set (array) of rows of the same type. Some database actions can act on a generic row as a single unit, rather than having to specify each component field separately.
Attribute List:
id - NODE_ID - unique identifier for each Node within this Node Type
si_name - L.cstr - SI - the name this row data type is identified byChild Quantity List:
row_data_type_field - 1row_data_type_field
This type of Node describes a single field in a row data type, and has details that are specific to that field, primarily a name and data type.
Attribute List:
id - NODE_ID - unique identifier for each Node within this Node Type
pp_row_data_type - N.row_data_type - PP - what row this field is a part of
si_name - L.cstr - SI - unique identifier for this field within the row
scalar_data_type - N.scalar_data_type - MA - the user-customized scalar data type for this fieldblueprints
This pseudo-Node has two child Node-types: catalog, application.
Distinct Child Groups List:
ak_name:
catalog - si_name
application - si_namecatalog
This type of Node describes a distinct unrealized "database" or "catalog" which contains everything else, both schema and users. Often you will have just one "catalog" Node; however, the rationale for being able to describe multiple databases in the same SQL::Routine is, for example, when one database wants to talk directly to another, or an application wants to use several. This Node type does not describe an instance of a database; rather, it contains all the details that are common to a set of databases having identical schema; it is their template. See also the "catalog_instance" Node type. Note that while "user" Nodes can be children of this, you should only define users here that either own a schema which is shared with multiple users or are otherwise universal in all instances of this blueprint; no 'normal' users should be here.
Attribute List:
id - NODE_ID - unique identifier for each Node within this Node Type
si_name - L.cstr - SI - lets you label the blueprint if you want; this isn't used when making SQL
single_schema - L.bool - true, catalog lim to 1 schema, rendered in 1-sch prod w no nm prefixDistinct Child Groups List:
ak_name:
catalog_link - si_name
schema - si_name
role - si_nameExample List:
|id|si |
|--|----|
| 1| | - a default database/catalog, usually just used if it is the only database
| 2|gene| - a multi-schema genealogy database/catalog that you manageapplication
This type of Node collects information that is specific to a database-using application but is not stored in a database. Those details mainly are the unrealized catalog links that it uses and the SQL (all in routine form) that it executes. Often you will have just one "application" Node; however, the rationale for being able to describe multiple applications in the same SQL::Routine is, for example, when you have several different applications that use the same database or database set. This Node type does not describe an instance or installation of an application. See also the "application_instance" Node type.
Relating this to the SQL standard, an application is essentially a SQL:1999 Call-Level Interface (CLI) client; each 'routine' it runs is a set of one or more SQL statements pushed over the command line, and even the 'attach' / 'connect' is done this way.
Attribute List:
id - NODE_ID - unique identifier for each Node within this Node Type
si_name - L.cstr - SI - lets you label the blueprint if you wantDistinct Child Groups List:
ak_name:
catalog_link - si_name
scalar_domain - si_name
row_domain - si_name
sequence - si_name
table - si_name
view - si_name
routine - si_nameowner
This type of Node represents an unrealized user which owns a database catalog. It is here for "schema" Nodes to reference as their owner. A separate "user" Node will be matched with this "owner" Node on a per-catalog-instance basis; this allows each site to have different usernames or passwords for the schema owner.
Attribute List:
id - NODE_ID - unique identifier for each Node within this Node Type
pp_catalog - N.catalog - PP - the catalog that this user is inExample List:
|id|ca|
|--|--|
| 1| 1|
| 2| 2|catalog_link
This type of Node represents a distinct "catalog link" or "data source" local name which SQL under a 'catalog' or 'application' Node refers to when it wants to talk to an external database catalog. This Node is particularly necessary when either two instances of the same catalog want to communicate or an application wants to talk to two instances of the same catalog; SQL::Routine has no way to know which instance regular SQL is referring to except by referencing these catalog link Nodes. The 'catalog_link' Node just says what the remote 'catalog' is so local SQL knows what it can refer to; the 'catalog_link_instance' Node is required to say which specific catalog instance to talk to.
Note that SQL does not have to mention a link if it either is referring to its own catalog instance or there is only one instance of a blueprint in the system; if all catalog instances are based on different blueprints, then no SQL has to reference the links; the schema objects to reference are un-ambiguous.
According to the SQL:1999 standard, a fully qualified schema object name has 3 main parts: the catalog name (provided by this Node), the schema name, and the object's unqalified name; when leaving the catalog name out but using the schema name, one is using a partially qualified name.
Attribute List:
id - NODE_ID - unique identifier for each Node within this Node Type
pp_catalog - N.catalog - PP - the schema in this blueprint may use the unrealized catalog link
pp_application - N.application - PP - the routines in this app bp may use the unrealized catalog link
si_name - L.cstr - SI - the "catalog link" that is the front of the fully qualified remote schema object name
target - N.catalog - MA - this is what the link connects toschema
This type of Node describes a "schema" or "name space" into which your schema objects can be collected. Some database products support multiple schemas per catalogue (and some don't); such support is specified by the SQL:1999 standard, which also says that each schema is owned by a single database user. Often you will have just one "schema" Node, under which all of your schema objects are grouped.
According to the SQL:1999 standard, a fully qualified schema object name has 3 main parts: the catalog name, the schema name (provided by this Node), and the object's unqalified name; when leaving the catalog name out but using the schema name, one is using a partially qualified name.
SQL::Routine will let you define your schema objects (tables, views, routines, etc) as if multiple schemas were supported natively, and if you are deploying to a database product that doesn't, then SQL::Routine can put a common name prefix in front of all the schema objects (the schema/namespace name), to simulate the lacking functionality. That said, it is better for you to design your schemas so different namespaces don't contain same-named objects anyway, if practical.
Attribute List:
id - NODE_ID - unique identifier for each Node within this Node Type
pp_catalog - N.catalog - PP - the catalog that this schema/namespace is in
si_name - L.cstr - SI - the name this schema/namespace is identified by
owner - N.owner - MA - the user that owns this schemaDistinct Child Groups List:
ak_name:
scalar_domain - si_name
row_domain - si_name
sequence - si_name
table - si_name
view - si_name
routine - si_nameExample List:
|id|ca|si |ow|
|--|--|----|--|
| 1| 1|foo | 1| - a default schema/namespace
| 2| 2|gene| 2| - store all the normal app data that constitutes a genealogy database
| 3| 2|dd | 2| - store a data dictionary describing an application for editing gene datarole
This type of Node describes a database role, which is a named collection of user privileges.
Attribute List:
id - NODE_ID - unique identifier for each Node within this Node Type
pp_catalog - N.catalog - PP - the catalog that this role is in
si_name - L.cstr - SI - the name this role is identified byprivilege_on
This type of Node associates a role with some entity (usually a schema or schema object) on which it has some kind of privileges; privilege_on Nodes are children of role Nodes. All users given that role will have these privileges. Exactly what privileges on the entity the role has are defined by child privilege_for Nodes, which are children of the privilege_on Node.
Attribute List:
id - NODE_ID - unique identifier for each Node within this Node Type
pp_role - N.role - PP - the role that this privilege is granted to
schema - N.schema - if the privilege relates to a whole schema, this is the schema
scalar_domain - N.scalar_domain - if the privilege type is scalar_domain-related, this is the scalar_domain
row_domain - N.row_domain - if the privilege type is row_domain-related, this is the row_domain
sequence - N.sequence - if the privilege type is sequence-related, this is the sequence
table - N.table - if the privilege type is table-related, this is the table
view - N.view - if the privilege type is view-related, this is the view
routine - N.routine - if the privilege type is routine-related, this is the routineExclusive Attribute Groups List:
privilege_on - schema,scalar_domain,row_domain,sequence,table,view,routine - MAprivilege_for
This type of Node details one privilege that a role has on some other entity that is defined by this Node's privilege_on parent Node.
Attribute List:
id - NODE_ID - unique identifier for each Node within this Node Type
pp_priv_on - N.privilege_on - PP - what "privilege_on" Node this option is associated with
si_priv_type - E.privilege_type - SI - what manner of privilege this isscalar_domain
This type of Node allows you to store a 'scalar_data_type' definition as a named database schema object, and reference it by its own identifier as the "data type" of such entities as table column definitions, view column definitions, and routine variables.
Attribute List:
id - NODE_ID - unique identifier for each Node within this Node Type
pp_schema - N.schema - PP - the schema that this domain is in, if any
pp_application - N.application - PP - the app-space that this domain is in, if any
si_name - L.cstr - SI - the name this domain is identified by
data_type - N.scalar_data_type - MA - the core defining details of this domainrow_domain
This type of Node allows you to store a 'row_data_type' definition as a named database schema object, and reference it by its own identifier as the "data type" of such entities as routine variables.
Attribute List:
id - NODE_ID - unique identifier for each Node within this Node Type
pp_schema - N.schema - PP - the schema that this domain is in, if any
pp_application - N.application - PP - the app-space that this domain is in, if any
si_name - L.cstr - SI - the name this domain is identified by
data_type - N.row_data_type - MA - the core defining details of this domainsequence
This type of Node describes a simple named sequence number generator, which will produce integers (normal purpose) but not fractional numbers. Sequence generators are part of the SQL:2003 standard (see "4.21 Sequence generators"), and many database products have them; apparently they are not part of older SQL standards. The standard says that only integers can be produced by sequences.
Attribute List:
id - NODE_ID - unique identifier for each Node within this Node Type
pp_schema - N.schema - PP - the schema that this sequence generator is in
pp_application - N.application - PP - the app-space that this sequence generator is in
si_name - L.cstr - SI - the name this sequence generator is identified by
increment - L.sint - the interval between sequence numbers; defaults to 1 if not set
min_val - L.sint - the minimum value that the sequence can generate; no minimum if not set
max_val - L.sint - the maximum value that the sequence can generate; no maximum if not set
start_val - L.sint - specify first value; def min_val if asc seq, max if desc, 1 if min/max not set
cycle - L.bool - if true, sequence continues after reaching bound, at other end; defaults to false
order - L.bool - if true, seq guaranteed to ret numbers in order; def false, will just be uniquetable
This type of Node describes the core of a database table, which is the single core concept of any database; it is where the "data" that the database exists to store is kept. Each table has one or more columns, which define its "interface", and one or more indexes or constraints for data integrity. SQL:2003,1999 calls this a "base table".
SQL::Routine supports the concept of temporary tables such that you say a table is temporary by putting it in the application space rather than the catalog-schema space. This makes the most sense, since temporary tables only persist during an active database connection, and they do not exist in the same name-space of any database schema.
Attribute List:
id - NODE_ID - unique identifier for each Node within this Node Type
pp_schema - N.schema - PP - the schema that this table is in
pp_application - N.application - PP - the app-space that this table is in
si_name - L.cstr - SI - the name this table is identified by
row_data_type - N.row_data_type - defines the names and types of this table's column set
row_domain - N.row_domain - defines the names and types of this table's column setExclusive Attribute Groups List:
row_data_type - row_data_type,row_domain - MAExample List:
|id|sc|si_name |
|--|--|------------|
| 1| 1|user_auth | - authentication related details for application users
| 2| 1|user_profile| - public personal details for application users
| 3| 1|user_pref | - application preference values for users
| 4| 1|person | - core data node in a genealogical programtable_field
This type of Node describes a single table column, and has details that are specific to that column.
Attribute List:
id - NODE_ID - unique identifier for each Node within this Node Type
pp_table - N.table - PP - what table this column is a part of
si_row_field - N.row_data_type_field - SI - defines the name and type of this column
mandatory - L.bool - the col's "nullability characteristic; true, "known not nullable"; else, "possibly nullable"
default_val - L.misc - an optional default value for the column, or starting val for auto_inc
auto_inc - L.bool - when true, col val defaults from a special auto-incrementing sequence
default_seq - N.sequence - when set, the column defaults to a value from this named sequence
Note: See SQL:2003 Foundation 4.13 "Columns, fields, and attributes" (p49).Exclusive Attribute Groups List:
default - default_val,default_seqExample List:
Note: The rows below are actually a join where table_field = row_data_type_field.
|id|tb|si_name |dt|ma|def|inc|
|--|--|-------------|--|--|---|---|
| 1| 1|user_id | 9|1 |1 |1 | - unique internal id for user, not changeable
| 2| 1|login_name |23|1 | | | - who user authenticates into app as
| 3| 1|login_pass |23|1 | | | - also used with authentication
| 4| 1|private_name |24|1 | | | - real name that app service provider can know
| 5| 1|private_email|24|1 | | | - email that service provider can contact user with
| 6| 1|may_login |19|1 | | | - true when user may use app at all
| 7| 1|max_sessions | 7|1 |3 | | - max concurrent app logins user may have
| 8| 2|user_id | 9|1 | | | - unique internal id for user, not changeable
| 9| 2|public_name |25|1 | | | - public name or pen name or public alias
|10| 2|public_email |25|0 | | | - email address the public may use for contact
|11| 2|web_url |25|0 | | | - personal/business website address of user
|12| 2|contact_net |25|0 | | | - info like ICQ number or other internet handles
|13| 2|contact_phy |25|0 | | | - physical address or city or telephone number
|14| 2|bio |25|0 | | | - brief description of the person
|15| 2|plan |25|0 | | | - what the person plans to do next
|16| 2|comments |25|0 | | | - other comments
|17| 3|user_id | 9|1 | | | - unique internal id for user, not changeable
|18| 3|pref_name |26|1 | | | - app-specific preference name
|19| 3|pref_value |27|0 | | | - preference value for this user
|20| 4|person_id | 9|1 |1 |1 | - unique actual id for person, not changeable
|21| 4|alternate_id |23|0 | | | - user's own way of ident people, if wanted
|22| 4|name |24|1 | | | - actual name of the person they are called by
|23| 4|sex |22|0 | | | - sex of the person, if known
|24| 4|father_id | 9|0 | | | - person_id of this person's father, if known
|25| 4|mother_id | 9|0 | | | - person_id of this person's mother, if knowntable_index
This type of Node describes a single table index, and has details that are specific to that index.
Note that the primary key for a table is defined to be the first (by sequence) index_type=UNIQUE/UFOREIGN index where all of the index columns are required. If no table indexes qualify then the table has no primary key.
Attribute List:
id - NODE_ID - unique identifier for each Node within this Node Type
pp_table - N.table - PP - unique identifier for this table
si_name - L.cstr - SI - unique identifier for index within table
index_type - E.table_index_type - MA - what type of index this is (eg: UNIQUE, FOREIGN)
f_table - N.table - foreign key constraining tableLocal Attribute Dependencies List:
index_type:
f_table - FOREIGN,UFOREIGN - MAChild Quantity List:
table_index_field - 1Distinct Child Groups List:
ak_f_table_field:
table_index_field - f_fieldExample List:
|id|tb|si_name |type |ftbl|
|--|--|----------------|-------|----|
| 1| 1|primary |UNIQUE | | - ensure all [user_id] are unique
| 2| 1|ak_login_name |UNIQUE | | - ensure all [login_name] are unique
| 3| 1|ak_private_email|UNIQUE | | - ensure all [private_email] are unique
| 4| 2|primary |UNIQUE | | - ensure all [user_id] are unique
| 5| 2|ak_public_name |UNIQUE | | - ensure all [public_name] are unique
| 6| 2|fk_user |FOREIGN| 1| - ensure user_profile matches user_auth
| 7| 3|primary |UNIQUE | | - ensure all [user_id,pref_name] are unique
| 8| 3|fk_user |FOREIGN| 1| - ensure user_pref matches user_auth
| 9| 4|primary |UNIQUE | | - ensure all [person_id] are unique
|10| 4|ak_alternate_id |UNIQUE | | - ensure all [alternate_id] are unique
|11| 4|fk_father |FOREIGN| 4| - link child to father
|12| 4|fk_mother |FOREIGN| 4| - link child to mothertable_index_field
This type of Node intersects between a table_index and a table field/col; it says which table columns are involved in each table index.
Attribute List:
id - NODE_ID - unique identifier for each Node within this Node Type
pp_table_index - N.table_index - PP - unique identifier for index within table
si_field - N.row_data_type_field - SI - unique identifier for column within table
f_field - N.row_data_type_field - when the index type is a foreign key, this is the constraining columnExample List:
|id|ind|fld|ffld|
|--|---|---|----|
| 1| 1| 1| | - ensure all [user_id] are unique
| 2| 2| 2| | - ensure all [login_name] are unique
| 3| 3| 5| | - ensure all [private_email] are unique
| 4| 4| 8| | - ensure all [user_id] are unique
| 5| 5| 9| | - ensure all [login_name] are unique
| 6| 6| 8| 1| - ensure user_profile matches user_auth
| 7| 7| 17| | - ensure all [user_id,pref_name] are unique
| 8| 7| 18| | - ensure all [user_id,pref_name] are unique
| 9| 8| 17| 1| - ensure user_pref matches user_auth
|10| 9| 20| | - ensure all [person_id] are unique
|11| 10| 21| | - ensure all [alternate_id] are unique
|12| 11| 24| 20| - link child to father
|13| 12| 25| 20| - link child to motherview
This type of Node describes the core (single cardinality) description of a database view or a SQL query; SQL::Routine uses the same set of Node Types to describe both as they are almost identical in structure. The main way they differ is that stored views have a name and live as their own object in a database schema; SQL queries only exist either inside a routine or an application context, and usually don't have names. Both types can have named or un-named sub-queries inside them which are the same format as the main view/query. Views/queries are interfaces to one or more tables that are related in a specific way. Each view looks outwardly like a table, in that it has rows or columns, but it does not describe how data is stored, but mainly just how it is retrieved. Each view has a set of "columns", which is its public interface by which rows are retrieved like from a table. You can also insert, update, or delete rows through some views/queries as with a table.
Views are often fully defined using a set of multiple linked Nodes of varying Node Types, which makes them fully customizable. However, a short-cut is available which allows a view to be defined entirely with as few as two Nodes, one "view" having type of ALIAS and one "view_src". This short cut is mainly for use when we want to simulate full direct table access, which is needed for mass back-ups or restores of tables, or when implementing a utility that lets users make direct changes to a table. Other part-way short cuts are available too.
The view_join[/_field] Nodes that belong to a "view" ("viewed table") Node, together with the view_src[/_field] Nodes that the former reference, define the single (recursive) "joined table" (see SQL:2003 Foundations 7.7, p312) that is the basis for the "viewed table". In the joined table, every field value matches exactly a (or several identical) value in a source table or view. Given that SQL::Routine does not directly support the concept of a "natural join" (but it is indirectly supported), the number of columns in the joined table is always the sum of the number of columns in all the joined source table/views. Also, the number of joined table rows is at most equal to the product of the number of rows in each source (and as few as zero). The other parts of a query build on the joined table. The where/group/having/window clauses can reduce (but never increase) the number of result rows. The select-list clause can change (to more or fewer) the number of result columns.
Since the ability to change database records is more complicated than simply viewing records, and usually requires more information to be available (such as primary keys for rows being updated), SQL::Routine allows you to store a hint with a view, called "may_write", that says whether or not you will even try to make updates against a view, so we will know in advance and save ourselves unnecessary work or complexity. The flag defaults to false.
SQL::Routine also uses "views" to describe editing DML operations, such as Insert, Update, Delete operations. The view definition says what the conditions for each DML operation is, namely what rows they will affect. With Update and Delete operations, all rows that would be returned by the view will be correspondingly updated or deleted. An Insert (single or multiple row) won't affect the existing rows, but will add to what the view sees after the Insert happens. The frequent case in such situations, though, is that said view is only matching one row, since a user typically just edits or deletes or inserts one row at a time. However, in such situation where your conceptual view is selecting more rows than you intend to edit the same way afterwards, you would want to make a second view which uses the first as its source, and the second/outer view would have a more restrictive 'where' clause that limits to the rows being changed.
Attribute List:
id - NODE_ID - unique identifier for each Node within this Node Type
pp_view - N.view - PP - parent view of this one, if any; empty if this is the root view
pp_routine - N.routine - PP - parent routine of this one, if any
pp_schema - N.schema - PP - the schema that this view is in, if any
pp_application - N.application - PP - the app-space that this view is in, if any
si_name - L.cstr - SI - the name this view is identified by, if any
view_type - E.view_type - MA - what kind of view this is; eg: alias, joined, grouped, compound, IUD
row_data_type - N.row_data_type - defines the names and types of this view's column set
row_domain - N.row_domain - defines the names and types of this view's column set
recursive - L.bool - if true, this view is a recursive correlated subquery
compound_op - E.compound_operator - how to merge any child rowsets/views of this one together, if any
distinct_rows - L.bool - (set quantifier) true, select|compound returns distinct rows; false, return all rows
may_write - L.bool - true when we should at least try allowing updates through the selecting view
set_p_routine_arg - N.routine_arg - the ROW/RW_ARY routine arg we are setting value of via "select into"
set_p_routine_var - N.routine_var - the ROW/RW_ARY routine var we are setting value of via "select into"
ins_p_routine_arg - N.routine_arg - the ROW/RW_ARY routine arg we are inserting into the table/view
ins_p_routine_var - N.routine_var - the ROW/RW_ARY routine var we are inserting into the table/view
Note: setting distinct_rows to true results in "[SELECT|UNION|...] DISTINCT";
a false or undefined value results in "[SELECT|UNION|...] ALL".Local Attribute Dependencies List:
pp_view:
recursive
view_type:
row_data_type,row_domain - ALIAS,JOINED,GROUPED,COMPOUND,INSERT - MA
recursive - JOINED,GROUPED,COMPOUND
compound_op - COMPOUND - MA
distinct_rows - JOINED,GROUPED,COMPOUND
may_write - ALIAS,JOINED,GROUPED,COMPOUND
set_p_routine_arg,set_p_routine_var - ALIAS,JOINED,GROUPED,COMPOUND
ins_p_routine_arg,ins_p_routine_var - INSERT - MA
Note: Not expressable yet; view_type can be [INSERT,UPDATE,DELETE] only when pp_routine set.
Note: Not expressable yet; if pp_routine set and view_type is [A,J,G,C], then one of set_* must be set.Distinct Child Groups List:
ak_join:
view_join - lhs_src,rhs_src
ak_join_limit_one:
view_join - rhs_src
ak_expr_set_result_field:
view_expr - set_result_field
ak_expr_set_src_field:
view_expr - set_src_field
ak_expr_call_src_arg:
view_expr - call_src_argExample List:
|id|type |
|--|-------|
| 1|JOINED | - user - joins the "user_auth" and "user_profile" tables
| 2|JOINED | - person_with_parents - joins the "person" table with itself twice
| 3|GROUPED| - user_theme - shows how many users use each theme pref value
| 4|ALIAS | - get_person - simply the "person" table
| 5|JOINED | - get_user - simply the "user" view
| 6|JOINED | - get_pwp - simply the "person_with_parents" view
| 7|ALIAS | - get_theme - simply the "user_theme" viewview_arg
When this view is a correlated subquery, then it takes one or more arguments to define its operating context; each "view_arg" Node you attach to a "view" Node names an argument declaration. This Node type is not used for anything else; it is not like a routine arg that doubles for an app host param.
The presence or absense of arguments in a view (assuming they are not 'constant') determines whether the view is correlated or uncorrelated when used as a subquery (named or embedded) in another query. Uncorrelated subqueries, having no arguments, are simpler and can fire exactly once, fully prior to the outer query; this kind can be implemented easily, or emulated simply with temporary tables and multiple queries. Correlated subqueries, having 1 or more arguments, are more complicated and can fire multiple (or zero) times for each execution of the outer query; it executes during the outer query (after it starts and before it ends) because it uses one or more values generated by the outer query (for example, it may fire once per each row either examined or returned by the outer query); this kind is more complicated to implement or emulate. The term "correlated" is the standard term for this differentiation. The difference between correlated vs un- is analagous to a routine that takes or does not take any arguments.
Attribute List:
id - NODE_ID - unique identifier for each Node within this Node Type
pp_view - N.view - PP - what named sub-query view this is an argument for
si_name - L.cstr - SI - name of the argument
cont_type - E.container_type - MA - what basic container type the argument is
scalar_data_type - N.scalar_data_type - user customized data type of SCALAR/SC_ARY argument
scalar_domain - N.scalar_domain - user customized data type of SCALAR/SC_ARY argument
row_data_type - N.row_data_type - user customized data type of ROW/RW_ARY argument
row_domain - N.row_domain - user customized data type of ROW/RW_ARY argumentExclusive Attribute Groups List:
data_type - scalar_data_type,scalar_domain,row_data_type,row_domain - MA
Notes: This helps force cont_type to be one of [SCALAR,ROW,SC_ARY,RW_ARY].Local Attribute Dependencies List:
cont_type:
scalar_data_type,scalar_domain - SCALAR,SC_ARY - MA
row_data_type,row_domain - ROW,RW_ARY - MAview_src
This type of Node describes a source table or view that is used by this view. Specifically it applies only to the "from" clause in the generated SQL, where applicable. A table or view may be used for multiple sources, each of which would be a unique alias for it.
Attribute List:
id - NODE_ID - unique identifier for each Node within this Node Type
pp_view - N.view - PP - unique identifier for view
si_name - L.cstr - SI - unique "alias" identifier for source within view
match_table - N.table - if set, the source is a table (and match_view must not be set)
match_view - N.view - if set, the source is another view (and match_table must not be set)
match_p_view_arg - N.view_arg - when view is a correlated subquery, use value of a ROW/RW_ARY argument to the view
match_p_routine_arg - N.routine_arg - use value of a ROW/RW_ARY argument to the current routine
match_p_routine_var - N.routine_var - use value of a ROW/RW_ARY variable declared in the current routine
catalog_link - N.catalog_link - if match_[table|view] in another catalog, say via which catalog link
may_write - L.bool - alternately to the same 'view' attribute, say here which exact sources we want to 'select for update'Exclusive Attribute Groups List:
match - match_table,match_view,match_p_view_arg,match_p_routine_arg,match_p_routine_var - MAExample List:
|id|vw|si_name |mtb|mvw|
|--|--|------------|---|---|
| 1| 1|user_auth | 1| | - match "user_auth" table
| 2| 1|user_profile| 2| | - match "user_profile" table
| 3| 2|self | 4| | - match "person" table
| 4| 2|father | 4| | - match "person" table
| 5| 2|mother | 4| | - match "person" table
| 6| 3|user_pref | 3| | - match "user_pref" table
| 7| 4|person | 4| | - match "person" table
| 8| 5|m | | 1| - match "user" view
| 9| 6|m | | 2| - match "person_with_parents" view
|10| 7|m | | 3| - match "user_theme" viewview_src_arg
When a view_src matches a named correlated subquery (has arguments), a view_src_arg Node is used to correlate a subquery view_arg for the view that uses the view_src. This allows such named subqueries to be passed argument values when used in a "from" clause.
Attribute List:
id - NODE_ID - unique identifier for each Node within this Node Type
pp_src - N.view_src - PP - which view source this column is associated with
si_match_view_arg - N.view_arg - SI - which argument in the source view is representedview_src_field
This type of Node describes a column in a source table or view that is used by this view, as specified in the view_src table.
Attribute List:
id - NODE_ID - unique identifier for each Node within this Node Type
pp_src - N.view_src - PP - which view source this column is associated with
si_match_field - N.row_data_type_field - SI - which field from the source table/view/var is representedExample List:
Note: This example is out of date, based on a time when there were separate col Nodes for tables/views.
|id|vs|mtc|mvc|
|--|--|---|---|
| 1| 1| 1| | - "user_auth"."user_id"
| 2| 1| 2| | - "user_auth"."login_name"
| 3| 1| 3| | - "user_auth"."login_pass"
| 4| 1| 4| | - "user_auth"."private_name"
| 5| 1| 5| | - "user_auth"."private_email"
| 6| 1| 6| | - "user_auth"."may_login"
| 7| 1| 7| | - "user_auth"."max_sessions"
| 8| 2| 8| | - "user_profile"."user_id"
| 9| 2| 9| | - "user_profile"."public_name"
|10| 2| 10| | - "user_profile"."public_email"
|11| 2| 11| | - "user_profile"."web_url"
|12| 2| 12| | - "user_profile"."contact_net"
|13| 2| 13| | - "user_profile"."contact_phy"
|14| 2| 14| | - "user_profile"."bio"
|15| 2| 15| | - "user_profile"."plan"
|16| 2| 16| | - "user_profile"."comments"
|17| 3| 20| | - "self"."person_id"
|18| 3| 22| | - "self"."name"
|19| 4| 20| | - "father"."person_id"
|20| 4| 22| | - "father"."name"
|21| 5| 20| | - "mother"."person_id"
|22| 5| 22| | - "mother"."name"
|23| 6| 18| | - "user_pref"."pref_name"
|24| 6| 19| | - "user_pref"."pref_value"
|25| 3| 24| | - "self"."father_id"
|26| 3| 25| | - "self"."mother_id"
|27| 9| | 17| - "m"."self_name"
|28| 9| | 19| - "m"."father_name"
|29| 9| | 21| - "m"."mother_name"
|30| 8| | 1| - "m"."user_id"
|31| 8| | 2| - "m"."login_name"view_field
This type of Node describes a single view column public interface, and has details that are specific to that column; the view column implementation (where the data comes from or goes to) is not described here.
Attribute List:
id - NODE_ID - unique identifier for each Node within this Node Type
pp_view - N.view - PP - what view this column is a result/interface for
si_row_field - N.row_data_type_field - SI - defines the name and type of this column
src_field - N.view_src_field - simply match this src col rather than defining with a view_expr
is_writeable - L.bool - true when enough info is known to write changes to this column
mandatory - L.bool - true when corresponding value must always be populated
is_key - L.bool - true when this column is a part of unique id for source table or for view rowExample List:
Note: The rows below are actually a join where view_field = row_data_type_field.
|id|vw|si_name |dt|src|
|--|--|-------------|--|---|
| 1| 1|user_id | 9| 1| - unique internal id for user, not changeable
| 2| 1|login_name |23| 2| - who user authenticates into app as
| 3| 1|login_pass |23| 3| - also used with authentication
| 4| 1|private_name |24| 4| - real name that app service provider can know
| 5| 1|private_email|24| 5| - email that service provider can contact user with
| 6| 1|may_login |19| 6| - true when user may use app at all
| 7| 1|max_sessions | 7| 7| - max concurrent app logins user may have
| 8| 1|public_name |25| 9| - public name or pen name or public alias
| 9| 1|public_email |25| 10| - email address the public may use for contact
|10| 1|web_url |25| 11| - personal/business website address of user
|11| 1|contact_net |25| 12| - info like ICQ number or other internet handles
|12| 1|contact_phy |25| 13| - physical address or city or telephone number
|13| 1|bio |25| 14| - brief description of the person
|14| 1|plan |25| 15| - what the person plans to do next
|15| 1|comments |25| 16| - other comments
|16| 2|self_id | 9| 17| - unique actual id for person, not changeable
|17| 2|self_name |24| 18| - actual name of the person they are called by
|18| 2|father_id | 9| 19| - person_id of this person's father, if known
|19| 2|father_name |24| 20| - actual name of the person's father
|20| 2|mother_id | 9| 21| - person_id of this person's mother, if known
|21| 2|mother_name |24| 22| - actual name of the person's mother
|22| 3|theme_name |27| | - name of a user theme
|23| 3|theme_count | 9| | - count of users that use this themeview_join
This type of Node describes a join operation being done between two sources (each a table or a view), both in the same view. There are usually just two types of joins used, "equal" and "left". Note that there can only be one join between the same two sources. Also the same source can never be on the right side of more than one join (?). A "right" join may be discouraged as it would help prevent the same source from being outer joined to more than one other source, which in many databases is illegal.
Attribute List:
id - NODE_ID - unique identifier for each Node within this Node Type
pp_view - N.view - PP - unique identifier for this view
lhs_src - N.view_src - MA - which view source is on the left side of the join
rhs_src - N.view_src - MA - which view source is on the right side of the join
join_op - E.join_operator - MA - what kind of table/view join is being doneChild Quantity List:
view_join_field - 1Distinct Child Groups List:
ak_lhs_field:
view_join_field - lhs_src_field
ak_rhs_field:
view_join_field - rhs_src_fieldExample List:
|id|vw|lhs|rhs|op |
|--|--|---|---|----|
| 1| 1| 1| 2|LEFT| - "user_auth" left join "user_profile"
| 2| 2| 3| 4|LEFT| - "self" left join "father"
| 3| 2| 3| 5|LEFT| - "self" left join "mother"view_join_field
This type of Node intersects between a view_src and a table/view/var field/col; it says which table or view columns are involved in each view join. Each Node of this Node Type uniquely corresponds to part of a join operation being done between two sources in the same view. There is one Node for each pair of source columns (one from each source) that are being matched.
Attribute List:
id - NODE_ID - unique identifier for each Node within this Node Type
pp_join - N.view_join - PP - which view join this column is associated with
lhs_src_field - N.view_src_field - MA - which column from the left source is being matched on
rhs_src_field - N.view_src_field - MA - which column from the right source is being matched onExample List:
|id|vj|lhc|rhc|
|--|--|---|---|
| 1| 1| 1| 8| - on "user_profile"."user_id" = "user_auth"."user_id"
| 2| 2| 25| 19| - on "father"."person_id" = "self"."father_id"
| 3| 3| 26| 21| - on "mother"."person_id" = "self"."mother_id"view_compound_elem
This type of Node specifies a source that is an operand in a compound view.
Attribute List:
id - NODE_ID - unique identifier for each Node within this Node Type
pp_view - N.view - PP - this is the resulting compound view
operand - N.view_src - MA - this is an operand in the compound viewview_expr
This type of Node is intended to describe most of the parts of a SQL "select" which is the basis for a view definition. The parts described here correspond to these SQL terms: "result list", "where", "group by", "having", "order by". All of these parts are optional, and often mutually exclusive. However, "having" can only be used with "group by".
The main parts that are not described here are the joined table definitions, most related parts of the "from" clause (except for invoked 'with' subquery arguments), compound operations (such as "union"), and any hierarchical operations. See the other view_* tables for those.
This type of Node describes a sub-expression that is part of one of several optional main/root expressions in a view definition. All of these sub-expressions form a hierarchy where children are "arguments" to their parent expressions. There can only be one root expression for a "where" and a "having" part, and that is expected to return a boolean value. The "group by" and "order by" parts can each have multiple root expressions, each of which is part of an ordered list of "columns" arranged in the priority that they are applied.
This type of Node also describes details that would only be used in Inserts or Updates, where applicable; this allows you to set values based on expressions or function calls, and not just literal values.
Attribute List:
id - NODE_ID - unique identifier for each Node within this Node Type
pp_expr - N.view_expr - PP - parent expression of this one, if any; empty if this is the root expression
pp_view - N.view - PP - what view this expression has a part in defining
view_part - E.view_part - what "part" of a view definition this is defining
set_result_field - N.row_data_type_field - the view interface/result column we are defining the value of
set_src_field - N.view_src_field - this is the column we are setting the value of in insert or update op
call_src_arg - N.view_src_arg - arg of named subquery used in "from" we are passing value to
call_view_arg - N.view_arg - if parent expr set valf_call_view, name view arg that curr expr passes value to
call_sroutine_cxt - E.standard_routine_context - if parent expr set valf_call_sroutine, this cxt curr expr passes val to
call_sroutine_arg - E.standard_routine_arg - if parent expr set valf_call_sroutine, this arg curr expr passes val to
call_uroutine_cxt - N.routine_context - if parent expr set valf_call_uroutine, name routine cxt that curr expr passes value to
call_uroutine_arg - N.routine_arg - if parent expr set valf_call_uroutine, name routine arg that curr expr passes value to
cont_type - E.container_type - MA - what basic container type the expression value is
valf_literal - L.misc - use a literal value, such as number or string
scalar_data_type - N.scalar_data_type - cast or declare curr literal expr into this data type / domain
scalar_domain - N.scalar_domain - cast or declare curr literal expr into this data type / domain
valf_src_field - N.view_src_field - use the value of a local view source column
valf_result_field - N.row_data_type_field - match a result column; used mainly in 'having' or 'order' sql
valf_p_view_arg - N.view_arg - when view is a correlated subquery, use value of an argument to the view
valf_p_routine_cxt - N.routine_context - use value of a context arg to the current routine
valf_p_routine_arg - N.routine_arg - use value of an arg to the current routine
valf_p_routine_var - N.routine_var - use value of a variable declared in the current routine
valf_seq_next - N.sequence - use "next" value from a named user defined sequence generator (schema object)
valf_call_view - N.view - use result of a call to a view, such as a subquery or a cursor
valf_call_sroutine - E.standard_routine - use result of a call to a standard built-in function (or directive); one of standard_routine
valf_call_uroutine - N.routine - use result of a call to a user-defined stored function (routine)
catalog_link - N.catalog_link - if uroutine in another catalog, say via which catalog linkExclusive Attribute Groups List:
expr_root_view_part - pp_expr,view_part - MALocal Attribute Dependencies List:
view_part:
set_result_field - RESULT - MA
set_src_field - SET - MA
call_src_arg - FROM - MA
valf_literal:
scalar_data_type,scalar_domain - MA
valf_call_uroutine:
catalog_linkDistinct Child Groups List:
ak_view_arg:
view_expr - call_view_arg
ak_sroutine_arg:
view_expr - call_sroutine_cxt
view_expr - call_sroutine_arg
ak_uroutine_arg:
view_expr - call_uroutine_cxt
view_expr - call_uroutine_argExample List:
|id|cl|vw|part |pe|lit |blv|rta |src|mtc|sf |uf|
|--|--|--|------|--|-----|---|--------|---|---|-----|--|
| 1| | 5|WHERE | | | | | | |EQ | | - standard function of "eq(a,b)"
| 2| | | | 1| | | | 30| | | | - match column "user_auth"."user_id"
| 3| | | | 1| | |curr_uid| | | | | - value of host param "curr_uid"
| 4| | 6|WHERE | | | | | | |AND | | - standard function of "and(a,b)"
| 5| | | | 4| | | | | |LIKE | | - standard function of "like(a,b)"
| 6| | | | 5| | | | 28| | | | - match column "m"."father_name"
| 7| | | | 5| | |srchw_fa| | | | | - value of host param "srchw_fa"
| 8| | | | 4| | | | | |LIKE | | - standard function of "like(a,b)"
| 9| | | | 8| | | | 29| | | | - match column "m"."mother_name"
|10| | | | 8| | |srchw_mo| | | | | - value of host param "srchw_mo"
|11| | 3|WHERE | | | | | | |EQ | | - standard function of "eq(a,b)"
|12| | | |11| | | | 23| | | | - match column "user_pref"."pref_name"
|13| | | |11|theme| | | | | | | - match literal value of "theme"
|14| | 3|GROUP | | | | | 24| | | | - group by "user_pref"."pref_value"
|15| | 3|HAVING| | | | | | |GT | | - standard function of "gt(a,b)"
|16| | | |15| | | | | |COUNT| | - standard function of "count(col)"
|17| | | |15| 1| | | | | | | - match literal value of "theme"
|42|22| 3|RESULT| | | | | 24| | | |
|43|23| 3|RESULT| | | | | | |COUNT| |
|44| | | |43| | | | 24| | | |
|51| | 5|ORDER | | | | | 31| | | |
|52| | 6|ORDER | | | | | 27| | | |
|53| | 6|ORDER | | | | | 28| | | |
|54| | 6|ORDER | | | | | 29| | | |
|55| | 3|ORDER | | | | | | 23| | |
|56| | 3|ORDER | | | | | | 22| | |
Notes: In Oracle notation: the first view says match where
"user_auth.user_id = :curr_uid", the second says match where "father.name
like '%'||:srchw_fa||'%' and mother.name like '%'||:srchw_mo||'%'", the
third says match where "user_pref.pref_name = 'theme'".
Notes: In Oracle notation: the third view says match having "count(*) > 1" ("count" has no args).
Notes: The example 'routine_var' values are actually incorrect and will be changed to id numbers.routine
This type of Node describes the core (single cardinality) description of a database routine, such as a user defined function, stored procedure, trigger, or un-named block; SQL::Routine uses the same set of Node Types to describe them as they are almost identical in structure. Un-named blocks normally just appear in application-space; the others normally appear in database schemas or as sub-routines inside other routines.
For the present we are also representing Oracle's "package" as a routine, though that may change soon.
Attribute List:
id - NODE_ID - unique identifier for each Node within this Node Type
pp_routine - N.routine - PP - parent routine of this one, if any; empty if this is the root routine
pp_schema - N.schema - PP - the schema that this routine is in, if any
pp_application - N.application - PP - the app-space that this routine is in, if any
si_name - L.cstr - SI - name routine ident by in a [schema, pp_routine, app], or name of trigger
routine_type - E.routine_type - MA - what kind of routine this is
return_cont_type - E.container_type - container type of function's return value
return_scalar_data_type - N.scalar_data_type - data type of a function's SCALAR/SC_ARY return value
return_scalar_domain - N.scalar_domain - data type of a function's SCALAR/SC_ARY return value
return_row_data_type - N.row_data_type - data type of a function's ROW/RW_ARY return value
return_row_domain - N.row_domain - data type of a function's ROW/RW_ARY return value
trigger_on_table - N.table - the table that the trigger watches
trigger_on_view - N.view - the view that the trigger watches
trigger_event - E.basic_trigger_event - when a trigger executes
trigger_per_stmt - L.bool - true, exec each stmt, false, each rowLocal Attribute Dependencies List:
routine_type:
return_cont_type - FUNCTION - MA
trigger_on_table,trigger_on_view - TRIGGER - MA
trigger_event - TRIGGER - MA
trigger_per_stmt - TRIGGER - MA
return_cont_type:
return_scalar_data_type,return_scalar_domain - SCALAR,SC_ARY - MA
return_row_data_type,return_row_domain - ROW,RW_ARY - MAChild Quantity List:
routine_context - 0 - 1
routine_stmt - 1Distinct Child Groups List:
ak_name:
routine_context - si_name
routine_arg - si_name
routine_var - si_nameroutine_context
This type of Node describes a special context argument variable to a routine.
Attribute List:
id - NODE_ID - unique identifier for each Node within this Node Type
pp_routine - N.routine - PP - what routine this context argument is for
si_name - L.cstr - SI - unique identifier for variable within view
cont_type - E.container_type - MA - what basic container type the context is
conn_link - N.catalog_link - for CONN context type, ref to cat link def the conn handle
curs_view - N.view - for CURSOR context type, ref to view def the cursorExclusive Attribute Groups List:
context - conn_link,curs_view - MA
Notes: This helps force cont_type to be one of [CONN, CURSOR].Local Attribute Dependencies List:
cont_type:
conn_link - CONN - MA
curs_view - CURSOR - MAroutine_arg
This type of Node describes an input and/or output argument variable to a routine.
Attribute List:
id - NODE_ID - unique identifier for each Node within this Node Type
pp_routine - N.routine - PP - what routine this argument is for
si_name - L.cstr - SI - unique identifier for variable within view
cont_type - E.container_type - MA - what basic container type the argument is
scalar_data_type - N.scalar_data_type - user customized data type of SCALAR/SC_ARY argument
scalar_domain - N.scalar_domain - user customized data type of SCALAR/SC_ARY argument
row_data_type - N.row_data_type - user customized data type of ROW/RW_ARY argument
row_domain - N.row_domain - user customized data type of ROW/RW_ARY argument
conn_link - N.catalog_link - put ref to catalog_link defining this db conn handle here
curs_view - N.view - put reference to the view defining this cursor hereLocal Attribute Dependencies List:
cont_type:
scalar_data_type,scalar_domain - SCALAR,SC_ARY - MA
row_data_type,row_domain - ROW,RW_ARY - MA
conn_link - CONN - MA
curs_view - CURSOR - MAroutine_var
This type of Node describes a variable within the scope of a routine; it can be used by that routine and by any child routines, where applicable. If the current routine is a package, these are package-level variables.
Attribute List:
id - NODE_ID - unique identifier for each Node within this Node Type
pp_routine - N.routine - PP - what routine this variable is in
si_name - L.cstr - SI - unique identifier for variable within view
cont_type - E.container_type - MA - what basic container type the variable is
scalar_data_type - N.scalar_data_type - user customized data type of SCALAR/SC_ARY variable
scalar_domain - N.scalar_domain - user customized data type of SCALAR/SC_ARY variable
row_data_type - N.row_data_type - user customized data type of ROW/RW_ARY variable
row_domain - N.row_domain - user customized data type of ROW/RW_ARY variable
init_lit_val - L.misc - an initial value for a SCALAR var can go here
is_constant - L.bool - this says a SCALAR value may not be changed
conn_link - N.catalog_link - put the catalog_link defining db conn handle here
curs_view - N.view - put the view defining cursor here
curs_for_update - L.bool - true if CURSOR is select for update, false (def) if r/oLocal Attribute Dependencies List:
cont_type:
scalar_data_type,scalar_domain - SCALAR,SC_ARY - MA
row_data_type,row_domain - ROW,RW_ARY - MA
init_lit_val - SCALAR
is_constant - SCALAR
conn_link - CONN - MA
curs_view - CURSOR - MA
curs_for_update - CURSORroutine_stmt
This type of Node describes a statement or logic control or line within a routine, which includes such operations as variable assignment or reading a cursor or executing a DML operation. Often, most details for a statement are in child expression Nodes.
Attribute List:
id - NODE_ID - unique identifier for each Node within this Node Type
pp_routine - N.routine - PP - what routine this statement is in
block_routine - N.routine - this child routine defs contents of BEGIN ... END
assign_dest_cxt - N.routine_context - this context arg may be where the expression result goes
assign_dest_arg - N.routine_arg - this 'out' arg may be where the expression result goes
assign_dest_var - N.routine_var - this var may be where the expression result goes
call_sroutine - E.standard_routine - this is the standard built-in procedure to call
call_uroutine - N.routine - this is the user-defined stored procedure (routine) to call
catalog_link - N.catalog_link - if uroutine in another catalog, say via which catalog link
If 'block_routine' is set, the routine statement declares a compound
statement that can contain other statements; eg BEGIN ... END. If either
of 'assign_dest_[cxt|arg|var]' is set, the action is an assignment of an
expression value to a variable (SQL:2003 calls it 'SET'). If either of
'call_[s|u]routine' is set, the action is to call that built-in or routine.
TODO: Relevant structures to specify certain exceptions to catch or throw.
Meanwhile, all exceptions are thrown out to the application that is using SRT.Exclusive Attribute Groups List:
stmt_type - block_routine,assign_dest_cxt,assign_dest_arg,assign_dest_var,
call_sroutine,call_uroutine - MALocal Attribute Dependencies List:
call_uroutine:
catalog_linkDistinct Child Groups List:
ak_sroutine_arg:
routine_expr - call_sroutine_cxt
routine_expr - call_sroutine_arg
ak_uroutine_arg:
routine_expr - call_uroutine_cxt
routine_expr - call_uroutine_argroutine_expr
This type of Node describes a sub-expression that is part of a main/root expression which is the right hand side of a variable assignment statement in a routine. All of these sub-expressions form a hierarchy where children are "arguments" to their parent expressions. The root expression is expected to return a value that can be stored in the routine variable on the left side of the statement.
Attribute List:
id - NODE_ID - unique identifier for each Node within this Node Type
pp_expr - N.routine_expr - PP - parent expression of this one, if any; empty if this is the root expression
pp_stmt - N.routine_stmt - PP - unique identifier for routine routine_stmt this expression is part of
call_sroutine_cxt - E.standard_routine_context - if parent expr set valf_call_sroutine, this cxt curr expr passes val to
call_sroutine_arg - E.standard_routine_arg - if parent expr set valf_call_sroutine, this arg curr expr passes val to
call_uroutine_cxt - N.routine_context - if parent expr set valf_call_uroutine, name routine cxt that curr expr passes value to
call_uroutine_arg - N.routine_arg - if parent expr set valf_call_uroutine, name routine arg that curr expr passes value to
cont_type - E.container_type - MA - what basic container type the expression value is
valf_literal - L.misc - use a literal value, such as number or string
scalar_data_type - N.scalar_data_type - cast or declare curr literal expr into this data type / domain
scalar_domain - N.scalar_domain - cast or declare curr literal expr into this data type / domain
valf_p_routine_cxt - N.routine_context - use value of a context arg to the current routine
valf_p_routine_arg - N.routine_arg - use value of an arg to the current routine
valf_p_routine_var - N.routine_var - use value of a variable declared in the current routine
valf_seq_next - N.sequence - use "next" value from a named user defined sequence generator (schema object)
valf_call_sroutine - E.standard_routine - use result of a call to a standard built-in function (or directive); one of standard_routine
valf_call_uroutine - N.routine - use result of a call to a user-defined stored function (routine)
catalog_link - N.catalog_link - if uroutine in another catalog, say via which catalog link
actn_catalog_link - N.catalog_link - if act-on sroutine arg should be a catalog_link Node, this is it
actn_schema - N.schema - if act-on sroutine arg should be a schema Node, this is it
actn_scalar_domain - N.scalar_domain - if act-on sroutine arg should be a scalar_domain Node, this is it
actn_row_domain - N.row_domain - if act-on sroutine arg should be a row_domain Node, this is it
actn_sequence - N.sequence - if act-on sroutine arg should be a sequence Node, this is it
actn_table - N.table - if act-on sroutine arg should be a table Node, this is it
actn_view - N.view - if act-on sroutine arg should be a view Node, this is it
actn_routine - N.routine - if act-on sroutine arg should be a routine Node, this is it
actn_user - N.user - if act-on sroutine arg should be a user Node, this is itLocal Attribute Dependencies List:
cont_type:
actn_catalog_link,actn_schema,actn_scalar_domain,actn_row_domain,actn_sequence,actn_table,
actn_view,actn_routine,actn_user - SRT_NODE - MA
valf_literal:
scalar_data_type,scalar_domain - MA
valf_call_uroutine:
catalog_linkDistinct Child Groups List:
ak_sroutine_arg:
routine_expr - call_sroutine_cxt
routine_expr - call_sroutine_arg
ak_uroutine_arg:
routine_expr - call_uroutine_cxt
routine_expr - call_uroutine_argtools
This pseudo-Node has two child Node-types: data_storage_product, data_link_product.
Distinct Child Groups List:
ak_name:
data_storage_product - si_name
data_link_product - si_namedata_storage_product
This type of Node describes a distinct data storage product (database) that someone can use to implement a database in, such as Oracle 9i or PostgreSQL 7.4 or SQLite 2.8.12. Details of how you connect to said product are generally not considered here as, for example, the SQL you generate usually doesn't depend on the connection method, but just the SQL processor at the end of the line.
The attributes in this Node mainly just have meaning to external code that mediates between a SQL::Routine and the native drivers for database products; typically they are used to map a specific native driver to use, and/or a specific SQL generator to use for the mediation. If you are using multiple versions of the same database product line for different database instances, you should have a separate one of these Nodes for each, as the different versions may not support the same features or interface.
Attribute List:
id - NODE_ID - unique identifier for each Node within this Node Type
si_name - L.cstr - SI - lets you label the product in the model if you want
product_code - L.cstr - MA - a string recognized by the mediation layer as what product we have
is_memory_based - L.bool - if true, db data is held entirely in RAM memory of our own process
is_file_based - L.bool - if true, db data held in named file(s) that we ref to open in our own process
is_local_proc - L.bool - if true, database is a local service we access via IPC or something
is_network_svc - L.bool - if true, database is on a network server, access by network protocolExclusive Attribute Groups List:
type - is_memory_based,is_file_based,is_local_proc,is_network_svc - MAdata_link_product
This type of Node describes a distinct data link product that someone can use to connect to a data storage product, such as ODBC 2 or OCI8 or a proxy. Your choice here doesn't necessarily reflect on what data storage product you are using as the link product may abstract its details away. That said, while a catalog blueprint typically just needs to know its storage product, an application linking to one should know a link product and possibly a storage.
The attributes in this Node mainly just have meaning to external code that mediates between a SQL::Routine and the native drivers for database products; typically they are used to map a specific native driver to use, and/or a specific SQL generator to use for the mediation. If you are using multiple versions of the same database product line for different database instances, you should have a separate one of these Nodes for each, as the different versions may not support the same features or interface.
Attribute List:
id - NODE_ID - unique identifier for each Node within this Node Type
si_name - L.cstr - SI - lets you label the product in the model if you want
product_code - L.cstr - MA - a string recognized by the mediation layer as what product we have
is_proxy - L.bool - if true, link product is a transparent proxy which calls another link prod (we may spec)sites
This pseudo-Node has two child Node-types: catalog_instance, application_instance.
Distinct Child Groups List:
ak_name:
catalog_instance - si_name
application_instance - si_namecatalog_instance
This type of Node describes a distinct "database instance" or "server" or "host" which is described by an "catalog". This is what an application "connects to" when it will use a database. What often happens in real life is that you make a database/catalog design and then instantiate multiple copies; for example, one may contain data for testers of your application, while another has clean data for marketers to show off, while various others are installed at customer sites. Since there can be in real life multiple copies of a database design within communications range of each other, such that a client can 'pick one', or when you want to clone data between them, SQL::Routine lets you model this by having separate "catalog" and "catalog_instance" Nodes. The 'instance' Node stores the instance specific details like server IP address or domain name. Most of your "user" Nodes should be children of this, since they are usually different between installations; only those that own a shared schema can't be, but normal users shouldn't log in as those.
Attribute List:
id - NODE_ID - unique identifier for each Node within this Node Type
si_name - L.cstr - SI - lets you label the instance if you want; this isn't used when making SQL
blueprint - N.catalog - MA - what catalog blueprint describes this instance
product - N.data_storage_product - MA - what product implements this; determines SQL dialect to use
file_path - L.cstr - Path on a file system for a file-based database, if applicable
server_ip - L.cstr - Internet IP address of the database server, if applicable
server_domain - L.cstr - Internet domain name of the database server, if applicable
server_port - L.uint - TCP/IP port the database listens to, if applicableDistinct Child Groups List:
ak_cat_link_inst:
catalog_link_instance - blueprintExample List:
|id|bp|si |
|--|--|----|
| 1| 1| | - a default database/catalog, usually just used if it is the only database
| 2| 2|site| - a multi-schema genealogy database/catalog that you manage
| 3| 2|dist| - a similar database/catalog that someone else managescatalog_instance_opt
The attributes in catalog_instance_opt Nodes mainly just have meaning to external code that is associated with the storage product you are using to implement this current catalog_instance. (Each one is like an associative array element, having a distinct key name and a value.) These Nodes are intended for use in such situations where information in the model can be lost or be made more ambiguous when it is realized by an implementing product.
For example, an opt Node could say what to do with a storage product that doesn't support multiple schemas in the same catalog; eg: does it implement using a separate catalog for each schema (where catalog names have a common prefix), or does it combine the multiple schemas into a single schema where each schema object has a common name prefix. Such information is said to be lost because, for example, if one were to scan an existing such database, second case, to build a new model, all it could do is create a single schema containing all of the longer-name objects, unless it was explicitly told through external data like these Nodes to treat the first parts of each implemented object name as a schema name and hence put multiple schemas in the model. Or in the first case, whether multiple catalogs should get combined.
Attribute List:
id - NODE_ID - unique identifier for each Node within this Node Type
pp_catalog - N.catalog_instance - PP - the catalog_instance that this option customizes
si_key - L.cstr - SI - this is the option key that the the external code looks for
value - L.misc - MA - this is the associated option valueapplication_instance
This type of Node describes a distinct instance of an "application". Mainly it just resolves the application's potential catalog links to actual ones; it says which specific database instances we talk to, while the blueprint only knows about the blueprint for said instances.
Attribute List:
id - NODE_ID - unique identifier for each Node within this Node Type
si_name - L.cstr - SI - lets you label the instance if you want
blueprint - N.application - MA - what application blueprint describes this instanceDistinct Child Groups List:
ak_cat_link_inst:
catalog_link_instance - blueprintcatalog_link_instance
This type of Node describes a distinct instance of an 'catalog_link'. This mainly holds client install specific details, like what catalog instance to talk to, what its local DSN is, such as would be registered with ODBC or in tsnames.ora, or would be a filename, or stuff like that. Also connect string details perhaps, such as username and password, lest they be run-time arguments instead.
Note: There can only be a single catalog_link_instance per distinct catalog_link + [catalog|application]_instance, so it is safe for the CATALOG_*() built-in standard functions to use catalog_link as arguments or return values; a program can always determine what catalog_link_instance actually gets used given the 1:1 relationship.
In situations where proxys are involved, and the proxy is transparent such that the proxy lets a client tell it what it is to be a proxy for at runtime, you would normally have an extra catalog_link_instance Node in the client space to specify details of that for each proxy added; eg: no proxies means one link, one proxy means two. In SQL::Routine, if one catalog_link_instance Node is the child of another, then the parent is what the application invokes to talk to the proxy, and the child says what to tell the proxy to connect to. On the other hand, if a proxy is opaque such as for example an application can't control what it is a proxy for at runtime, then only one catalog_link_instance Node gets used, which points to the address of the proxy.
Attribute List:
id - NODE_ID - unique identifier for each Node within this Node Type
pp_link - N.catalog_link_instance - PP - parent link of this one (that which calls this), if any
pp_catalog - N.catalog_instance - PP - the schema in this instance may use the actual catalog link
pp_application - N.application_instance - PP - the routines in this app inst may use the actual catalog link
blueprint - N.catalog_link - the unrealized catalog link that we are 'implementing'
product - N.data_link_product - MA - what product implements this; determines driver to use
target - N.catalog_instance - this is what the link connects to
local_dsn - L.cstr - locally known driver-specific name for the catalog link (to cat or ch prox)
login_name - L.cstr - if we want to spec a username to always connect with, put here; else get it at runtime
login_pass - L.cstr - if we always use same password, put it here; else get it at runtimeExclusive Attribute Groups List:
link_root_blueprint - pp_link,blueprint - MA
link_root_target - pp_link,target - MAChild Quantity List:
catalog_link_instance - 0 - 1catalog_link_instance_opt
The attributes in catalog_link_instance_opt Nodes mainly just have meaning to external code that is associated with the link product you are using to implement this current catalog_link_instance. (Each one is like an associative array element, having a distinct key name and a value.) These Nodes are intended for use in customizing how the link product is used for this link, in ways other than what the dsn and login name/pass are.
Attribute List:
id - NODE_ID - unique identifier for each Node within this Node Type
pp_link - N.catalog_link_instance - PP - the catalog_link_instance that this option customizes
si_key - L.cstr - SI - this is the option key that the the external code looks for
value - L.misc - MA - this is the associated option valueuser
This type of Node describes a "user" who would connect to and/or has privileges in a database/catalog. Most database products support multiple users per catalog, though some only support one. Each database schema may/must be owned by exactly one user (according to the SQL:1999 spec) but it may be used by several. With some products like Oracle, a "user" and a "schema" are one and the same entity (the schema may not have any objects, though); however, SQL::Routine is designed such that they are separate, and one user may own multiple schemas.
Most database products have a granular security system where each user must be explicitly granted the rights to do certain kinds of tasks there, such as read data or write data or edit a schema or even connect to the database. In SQL::Routine, you create "role" Nodes and assign them privileges (roles are named groups of privileges); you then allocate roles to the users that may have the privileges they group.
Note that, in the best interests of security, it is recommended that the database user which your application connects as for normal use should not be the same as one that owns any schemas; if your application creates its own schema, then it should have an additional database user for that purpose.
Attribute List:
id - NODE_ID - unique identifier for each Node within this Node Type
pp_catalog - N.catalog_instance - PP - the catalog_instance that this user is in
si_name - L.cstr - SI - the name this user is identified by, and what he authenticates himself with
user_type - E.user_type - MA - what type of user this is; eg: schema owner vs data editor
match_owner - N.owner - the catalog that this user is in
password - L.cstr - the password this user provides when authenticating themself
default_schema - N.schema - this user's default schema; unqualified object references look hereLocal Attribute Dependencies List:
user_type:
match_owner - SCHEMA_OWNER - MA
password - ROOT,SCHEMA_OWNER,DATA_EDITOR - MA
Note: In other words to the above, all user types *except* ANONYMOUS have a user name and password.Example List:
|id|ci|ow|si_name |pass |sc|
|--|--|--|--------|------|--|
| 1| 1| 1| | | 1| - a default and/or 'public' user, or for db without 'users'
| 2| 2| 2|ronsealy|K34dsD| 2| - Ron Sealy, owns all schemas in instance 2
| 3| 2| |joesmith|fdsKJ4| 3| - Joe Smith is a normal user in instance 2
| 4| 3| 2|florence|0sfs8G| 2| - Florence, owns all schemas in instance 3
| 5| 3| |thainuff|9340sd| 3| - Thainuff is a normal user in instance 3user_role
Each Node of this type indicates that a specific user has been granted a specific role, and the privileges that come with it.
Attribute List:
id - NODE_ID - unique identifier for each Node within this Node Type
pp_user - N.user - PP - the user that a role is granted to
si_role - N.role - SI - the role that is being grantedcircumventions
This pseudo-Node has one child Node-type: sql_fragment.
sql_fragment
This type of Node holds literal SQL fragments that you can inject into SQL that is generated from SQL::Routine Nodes. Details may be changed later.
Attribute List:
id - NODE_ID - unique identifier for each Node within this Node Type
product - N.data_storage_product - if set, only use with this product; if not, use with all prods
att_node_type - L.cstr - the Node Type of the normal Node we attach this SQL fragment to
att_node_id - L.uint - the Node Id of the normal Node we attach this SQL fragment to
is_inside - L.bool - the fragment goes "inside" the SQL generated from attached-to Node
is_before - L.bool - the fragment goes "before" the SQL
is_after - L.bool - the fragment goes "after" the SQL
fragment - L.cstr - this is the actual SQL string fragment to be injectedExclusive Attribute Groups List:
is_where - is_inside,is_before,is_afterOLDER DOCUMENTATION
These paragraphs were more or less the original documentation for high-level SQL::Routine concepts, back when each concept was planned to be a separate class of its own. They were originally published in Framework.pod since the first release of the Rosetta distribution (2003-01-05), and up until 2004-02-04 inclusive. They are here in Language.pod as of 2004-02-12. Some details you see here shouldn't be considered accurate.
SQL::Routine::Command - This Model class describes an action that needs to be done against a database; the action may include several steps, and all of them must be done when executing the Command. A Command object has one mandatory string property named 'type' (eg: 'database_open', 'table_create', 'data_insert'), which sets the context for all of its other properties, which are in a hash property named 'args'. Elements of 'args' often include other Model class objects like 'Table' or 'DataType'. Like all Model objects, this is static and context-independant; it is intended to be serialized and passed to code like DBI->prepare(). While host params are often used with them (such as values to insert or update, or record matching conditions), it is always up to the calling code to resolve the host params such as with a user prompt (as SQL*Plus does), and pass those to code like DBI->execute(). See also Rosetta::Command.
SQL::Routine::DataType - This Model class describes a simple data type, which serves as metadata for a single atomic or scalar unit of data, or a column whose members are all of the same data type, such as in a regular database table or in row sets read from or to be written to one. This class would be used both when manipulating database schema and when manipulating database data.
SQL::Routine::Table - This Model class describes a single database table, and would be used for such things as managing schema for the table (eg: create, alter, destroy), and describing the table's "public interface" so other functionality like views or various DML operations know how to use the table. In its simplest sense, a Table object consists of a table name, a list of table columns, a list of keys, a list of constraints, and a few other implementation details. This class does not describe anything that is changed by DML activity, such as a count of stored records, or the current values of sequence generators attached to columns. This class would be used both when manipulating database schema and when manipulating database data. This class can generate SQL::Routine::Command objects having types of: 'table_verify', 'table_create', 'table_alter', 'table_destroy'.
SQL::Routine::View - This Model class describes a single database view, which conceptually looks like a table, but it is used differently. Tables and views are similar in that they both represent or store a matrix of data, which has uniquely identifiable columns, and rows which can be uniquely identifiable but may not be. With the way that Rosetta implements views, you can do all of the same DML operations with them that you can do with tables: select, insert, update, delete rows; that said, the process for doing any of those with views is more complicated than with tables, but this complexity is usually internal to Rosetta so you shouldn't have to code any differently between them. Tables and views are different in that tables actually store data in themselves, while views don't. A view is actually a custom abstracted interface to one or more database tables which are related to each other in a specific way; when you issue DML against a view, you are actually fetching from or modifying the data stored in one (simplest case) or more tables. This class can generate SQL::Routine::Command objects having types of: 'data_select', 'data_insert', 'data_update', 'data_delete', 'data_lock', 'data_unlock', 'view_verify', 'view_create', 'view_alter', 'view_destroy'.
SEE ALSO
SQL::Routine and the various other modules mentioned in its SEE ALSO.