NAME
SQL::SyntaxModel::Language - What language or grammar SQL::SyntaxModel speaks
COPYRIGHT AND LICENSE
This file is part of the SQL::SyntaxModel library (libSQLSM).
SQL::SyntaxModel 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::SyntaxModel 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::SyntaxModel 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.
Any versions of SQL::SyntaxModel 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::SyntaxModel 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. See the GPL for more details.
Linking SQL::SyntaxModel statically or dynamically with other modules is making a combined work based on SQL::SyntaxModel. Thus, the terms and conditions of the GPL cover the whole combination.
As a special exception, the copyright holders of SQL::SyntaxModel give you permission to link SQL::SyntaxModel with independent modules that are interfaces to or implementations of databases, 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::SyntaxModel (the version of SQL::SyntaxModel 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::SyntaxModel, and which is fully useable when not linked to SQL::SyntaxModel in any form.
Note that people who make modified versions of SQL::SyntaxModel are not obligated to grant this special exception for their modified versions; it is their choice whether to do so. The GPL gives permission to release a modified version without this exception; this exception also makes it possible to release a modified version which carries forward this exception.
While it is by no means required, the copyright holders of SQL::SyntaxModel would appreciate being informed any time you create a modified version of SQL::SyntaxModel that you are willing to distribute, because that is a practical way of suggesting improvements to the standard version.
DESCRIPTION
SQL::SyntaxModel 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::SyntaxModel 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::SyntaxModel::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::SyntaxModel'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.
CAVEAT: THIS DOCUMENT STILL NEEDS SOME MAJOR EDITING BEFORE IT WILL BE "DONE"; IN THE MEAN TIME, IT IS MAINLY THINGS ASSOCIATED WITH ROUTINES (AND VIEWS) THAT NEED FIXING.
FEATURE SCOPE AND STANDARDS COMPLIANCE
SQL::SyntaxModel 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::SyntaxModel 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::SyntaxModel is designed largely in the hindsight of the ANSI/ISO SQL-1999 standard (and with some foresight of the yet-unpublished SQL-2003) and aims to conform to it as much as possible. Where SQL-1999 and other SQL versions talk about the same features using different words, SQL::SyntaxModel will either match the standard or resemble it the most. Similarly, as SQL-1999 is 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-1999 (and 2003). Note that SQL-1999 has identical ANSI and ISO standards, while SQL-1992 does not; this is a practical reason to focus on just 1999.
During my work on this project, I found the following resources to be particularly useful:
http://www.wiscorp.com/SQLStandards.html
http://www.wiscorp.com/sql/sql1999_c3.zip (warning, large file)
http://www.wiscorp.com/sql/sql_2003_standard.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 case-sensitive by default; 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 data types as variables inside routines
- 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
- inner and outer table joins
- compound queries such as union, union all, intercept, except, distinct, etc
- 'where', 'group by', 'having', 'order by', 'limit'
- anonymous subqueries in any part of main query
- named subqueries ("with") likewise
- recursive queries
- OLAP extensions (related to group-by)
- updateable views; inserts, updates, deletes through any view (if key data not lost)
- "insert as select"
- multiple users, roles, privileges
- select/insert/update/delete rows in tables and viewed tables (Oracle supports latter, apparently)
- create/alter/delete schema objects
- create/alter/delete user defined functions
- create/alter/delete catalogs, schemas, users
- cursors
- multiple row insert / "write cursors"
- application bind variables for any data type
- all bind variables are named and not 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 (not in SQL standard, apparently)
- temporary objects, usually tables
- triggers firing on DML eventsThis is a brief list of major non-supported features (at present):
- multiple distinct values per row field
- complex domains and compound user-defined data types
- 'set' data types
- collection types such as arrays, stored in tables
- reference data types, stored in tables
- table hierarchies or subtables
- 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" 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)
- sequences 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::SyntaxModel 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 bind variables, can be complex types.
CIRCUMVENTING FEATURE LIMITATIONS
For such situations where you want to use SQL::SyntaxModel to mediate your access to a database, and it doesn't natively support a database product feature that you want to use, SQL::SyntaxModel does provide a "back door" where you have un-mediated access to the database. Speaking more accurately, SQL::SyntaxModel 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::SyntaxModel 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 it to, or it can be a complete SQL statement on its own, where you specify that it runs 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::SyntaxModel manages SQL concepts that fit into 2 main categories. The first of these is structures that are stored persistently in 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. For the context of this document, we will call the two "database-space" and "application-space". These two categories can overlap greatly, with most concepts being storable in either place; in practice, you would pick what to put in each space 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.
We also use a third main category for some simple concepts that are conceptually global, such as data types or domains. In the model we call this "common-space"; in practice the concepts persist in both of the first 2 spaces.
We also use a fourth main category to implement SQL::SyntaxModel's "circumvention" features, to access a concept that a specific database product understands but nothing in the SQL::SyntaxModel code does. In the model we call this "circumvention-space".
Here is a tree diagram, formatted visually like XML, which shows how the high level SQL::SyntaxModel 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 c="1">
<common_space c="1">
<domain c="M">
<domain_opt c="M" />
</domain>
</common_space>
<database_space c="1">
<catalog c="M">
<user c="M">
<user_role c="M" />
</user>
<role c="M">
<privilege c="M" />
<user_role c="M" />
</role>
<schema c="M">
<sequence c="M" />
<table c="M" />
<view c="M" r="Y" />
<routine c="M" r="Y" />
</schema>
</catalog>
</database_space>
<application_space c="1">
<application c="M">
<routine c="M" r="Y" />
</application>
</application_space>
<circumvention_space c="1">
<sql_fragment c="M" />
</circumvention_space>
</root>
<table c="M">
<table_col c="M" />
<table_ind c="M">
<table_ind_col c="M" />
</table_ind>
<trigger c="M">
<routine c="1" r="Y" />
</trigger>
</table>
<view c="M" r="Y">
<view_arg c="M" />
<view_col c="M" />
<view_src c="M">
<view_src_col c="M" />
</view_src>
<view_join c="M">
<view_join_col c="M" />
</view_join>
<view_hierarchy c="1" />
<view_expr c="M" r="Y" />
</view>
<routine c="M" r="Y">
<view c="M" r="Y" />
<routine_arg c="M" />
<routine_var c="M" />
<command c="M" r="Y" />
<routine_expr c="M" r="Y" />
</routine>For the most part, each 'concept' shown in the tree above corresponds to a single SQL::SyntaxModel 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. Nodes in database-space can't reference those in application-space; 2. Nodes in common-space can't reference those in the other top two spaces; 3. Nodes in circumvention-space can reference those in the first 3 spaces, but can't be referenced by them.
LANGUAGE PROCEDURAL STRUCTURE
The SQL::SyntaxModel 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 bind variables.
The SQL::SyntaxModel 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 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 ..." SQL statements, or similar statements that run inside a database stored procedure that connect to a second database. In fact, the SQL::SyntaxModel 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::SyntaxModel 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 'bind variables' for sending or receiving data, while the former uses named routine arguments and/or has an explicit return value. The SQL::SyntaxModel 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 bind variable.
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::SyntaxModel 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::SyntaxModel "command" or "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.
NODE ATTRIBUTE TYPES
There are 4 major SQL::SyntaxModel 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::SyntaxModel 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::SyntaxModel knows about.
simple_data_type
These are the basic scalar data value types that table columns or view columns or routine variables or arguments can be.
NUM_INT - an exact integral or whole number
NUM_EXA - an exact decimal or fractional number
NUM_APR - an approximately represented number (eg: floating point)
STR_BIT - a string of bits or generic binary data (can store anything)
STR_CHAR - a string of characters (encoding spec separately)
BOOLEAN - two values: false or true
DATETIME - a date or time or both (when an event happens; also implements a 'DATE')
INTERVAL - the amount of a time difference between two events (also implements a 'TIME')
Geographical types are expected to be added as well.
Maybe enumerated or set types also.char_enc_type
These are the set of known character string encodings 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_data_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
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 are 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_data_type is DATETIME.
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.table_index_type
These are 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_context
These are the basic context types that a database view or SQL query can be declared in. This enumeration may not actually be necessary (redundant with the fact that a Node's primary parent is known), but rather this is a convenience.
SCHEMA - a named schema object, the classic "view"; its parent must be a schema, not a view
APPLIC - an un-named select query that is declared at application level; p is routine inside app
CURSOR - a select cursor declared inside a routine; its parent must be a routine or routine_var
INSIDE - a subquery, named or not, inside another viewFirst, the "classic" type of view is stored in a database schema as a schema object, and it has a name, like a table; these views can not usually have variables in their definitions, and so will always return the same thing if their source tables are unchanged.
The second type of "view" corresponds to a SQL "select" statement which an application executes against a database, but it is not part of the database schema, and do not have names; these views can have variables anywhere in their definitions.
The third type of "view" corresponds to a "cursor" definition in a SQL "procedure"; while these do have names, they are only known by those names inside a SQL procedure or other SQL routine; they can usually take variables.
The fourth type of "view" corresponds to a "subquery" or "subselect" declared inside any other view (types 1-4); these never have names (they are "part" of the parent view), except for when they are in the SQL-From, in which case they can have aliases local to the parent view, and they can take variables if and only if their parent views can.
view_type
These are 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.
TABLE - this one-Node view/query is trivial and matches a table exactly, like "select * from [tbl]"
SIMPLE - a "normal" view/query (with joins), not a compound query, has no sub-queries, is fully self-defined
COMPOUND - consists of several "select ..." (each defined by a child view) connected by a compound operator
SUBQUERY - contains subqueries but not recursive, either in "from", "where", elsewhere
RECURSIVE - a recursive query, either invoking itself in a "with" clause, or using "start-with connect-by"compound_operator
These are 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.
DISTINCT - all distinct rows from a single input set, like a "select distinct ..."
ALL - all input rows are returned, like a "union all" (the default)
UNION - all distinct rows that are each in at least one input set
INTERSECT - all distinct rows that are each in every input set
EXCLUSIVE - all distinct rows that are each in exactly one input set (exclusive or)
EXCEPT - all distinct rows from one input set that are not in the othersjoin_operator
These are 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.
EQUAL - an equal-join; only rows with related entries in both sources are kept
LEFT - a left-join; all rows from first source kept even if not matched in second
A right-join may also be added, but its value is dubious at the moment.
Also, there is no concept of a "natural" join at this level of activity, and should not be.
Full outer join may also be added.
Note: SQLite supports this: [NATURAL ][LEFT |RIGHT |FULL ][OUTER |INNER |CROSS ].view_part
These are the standard parts of a view/query definition that are not in the "from" part.
RESULT - the result list at the top; one item per "view_col" per "view compound member"
WHERE - the SQL "where" part
GROUP - the SQL "group by" part
HAVING - the SQL "having" part
ORDER - the SQL "order by" partbasic_expr_type
These are the basic expression types that views or routines can contain. An expression is conceptually like a function which takes zero or more arguments and returns a value; each argument is also an expression. This is recursive until the expression is one that either takes no arguments (includes constant values or names of variables or columns).
LIT - a literal value, such as number or string
VAR - a variable name, such as stored routine or app bind var
COL - a column name, such as in a view or cursor in routine
VIEW - a view declaration, such as a subquery or a cursor
SFUNC - a standard built-in function; one of standard_func
UFUNC - a user defined stored function (routine)standard_func
These are the standard built in functions that can be used as parts of view definitions or code routines like stored procedures or functions or triggers. If any description contains "aggregate", it means the function is an aggregate function (a grouping func, like 'count')
TO_STR - cast arg as string (might take more args)
TO_NUM - cast arg as numeric (might take more args)
TO_INT - cast arg as integer (might take more args)
TO_BOOL - cast arg as boolean (might take more args)
TO_DATE - cast arg as datetime (might take more args)
NOT - a logical 'not', true iif lone arg is false
AND - a logical 'and', true iif every arg is true
OR - a logical 'or', true iif at least one arg is true
XOR - a logical 'xor', true iif 1+ arg true and 1+ arg false
EQ - true if both args are equal (both args cast same tp)
NE - true if both args are unequal (when same data type)
LT - true if first arg is less than second
GT - true if first arg is greater than second
LE - true if first arg is less than or equal to second
GE - true if first arg is greater than or equal to second
IS_NULL - true if only arg is a null value
NVL - returns arg 1 if it is not null, else arg 2
SWITCH - a logical switch-case expr (like Oracle 'decode')
LIKE - true if first arg contains second; args 3,4 are flags
ADD - result of adding all args as numbers
SUB - result of subtracting all subsequent args from first
MUL - result of multiplying all arguments
DIV - result of dividing first argument by second
DIVI - integral division of first arg by second
MOD - modulus of integral division of first arg by second
ROUND - rounds first arg to N dec places; N is second arg or 0
EXP - raises first arg to the power of the second
LOG - logarithm of the first arg on the base of second
MIN - minimum arg value of all arguments
MAX - maximum arg value of all arguments
AVG - average value of all arguments
SCONCAT - L.cstr concat of all arguments
SLENGTH - length of input string in characters
SINDEX - pos in arg 1 of arg 2 if present, start at arg 3
SUBSTR - substr in arg 1 starting pos arg 2 of length arg 3
SREPEAT - L.cstr concat arg 1 to self repeated by arg 2 instances
STRIM - trims leading and trailing whitespace from arg 1
SPAD - lengthens arg 1 to length of arg 2 using arg 3 or space
SPADL - like spad but add filler on left rather than right
LC - lowercases latin chars in a string
UC - uppercases latin chars in a string
GCOUNT - aggregate - count of rows a view/cursor can see
GMIN - aggregate - minimum of values in all records in one view col
GMAX - aggregate - maximum of values in all records in one view col
GSUM - aggregate - sum of values in all records in one view col
GAVG - aggregate - average of values in all records in one view col
GCONCAT - aggregate - L.cstr concat of values in all records in one view col
GEVERY - aggregate - is true when all rec values in one col are true
GANY - aggregate - is true when at least one rec value in one col is true
GSOME - aggregate - is true when some rec values are true
CROWID - unique database-generated row id of returned row (=rowid?)
CROWNUM - row number of returned row, before sorting (eg:Oracle)
CLEVEL - when using st-wi-cn-by, what hierarchy level row is in (eg:Oracle)
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).basic_var_type
These are the basic variable or container types that routine variables or arguments or return types can be, or which functions can take or return, or that intermediate values for expressions are stored in. Some types can contain single distinct values, such as scalars, and some can have multiple distinct values, such as records. The type of a table column is usually just one value.
SCALAR - a single value
RECORD - a list of scalars (can differ in type), keyed by a string/column-name
ARRAY - an ordered list of elements/records, all same type, can be read in any order
CURSOR - an ordered list of elements/records, can only/usually be read in sequential orderroutine_type
These are the basic types that a routine can be, since SQL::SyntaxModel "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.
All routine types (except packages?) can incorporate a series of commands 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.
ANONYMOUS - an anonymous/un-named routine declared in an application context, not persistant
PACKAGE - an Oracle package; a namespace for related funcs and procs
TRIGGER - a trigger on a table
PROCEDURE - a named stored procedure, plain or nested or in package
FUNCTION - a named stored function, plain or nested or in package
LOOP - a loop/iterator context inside a routine, such as a 'foreach'
CONDITION - a conditional context inside a routine, such as an 'if'
This list is probably missing some items.basic_stmt_type
These are the basic statement types that routines can contain. Examples of statements are calls to stored procedures or some kinds of program logic control operations or value assignment operations.
SPROC - a call to a standard built-in procedure; one of standard_proc
UPROC - a call to a user defined stored procedure (routine)
ASSIG - an assignment of an expression value to a variable
LOGIC - some control-flow logic operation ...
To add: any view related operations like select/insert/update/delete.
Where allowed, also to add user related operations or commit/rollback, etc.standard_proc
These are the standard built in procedures that can be called from routines.
ASSIGN - an assignment to a variable of the result of an expression
line types:
ROUTINE,ASSIGNMENT,CALL,RETURN,THROW,DECLARE_CURSOR,USE_CURSOR
routine types:
PLAIN,THROW,TRY,CATCH,IF,ELSEIF,ELSE,SWITCH,CASE,OTHERWISE,FOREACH,
FOR,WHILE,UNTIL,MAP,GREP,FUNCTION,PROCEDURE,TRIGGER,REGEXPcommand_type
These are the basic types of SQL::SyntaxModel Commands that an application can execute against a database or other Result context. Note that in many cases "db" can mean either "database" or "data source"; "ds" may be used instead.
DB_LIST - fetches a list of auto-detectable database instances
DB_INFO - fetches some detailed info about a database instance
DB_VERIFY - compares a provided db spec to an actual database
DB_OPEN - opens a connection between app and a database instance
DB_CLOSE - closes a connection between app and a database instance
DB_PING - verifies that a db connection is still open
DB_CREATE - creates a new database instance
DB_DELETE - deletes a database instance
DB_CLONE - makes a clone of a db instance in another storage loc
DB_MOVE - moves a db inst from one storage location to another
USER_LIST - fetches a list of users in open db instance
USER_INFO - fetches some detailed info about a database user
USER_VERIFY - compares a provided user spec to an actual user
USER_CREATE - creates new db user one can auth DB_OPEN with (if privs)
USER_DELETE - deletes db user (as well as all schema it owns, if applic)
USER_CLONE - creates a clone of an existing user with different name
USER_UPDATE - alters some details for a database user
USER_GRANT - grants a db privilege to a db user
USER_REVOKE - revokes a db privilege from a db user
TABLE_LIST - fetches a list of tables in schema of open db instance
TABLE_INFO - fetches some detailed info about a database instance
TABLE_VERIFY - compares a provided table spec to an actual table
TABLE_CREATE - creates a new table in schema of open db inst
TABLE_DELETE - deletes an existing table in open db inst (if it can)
TABLE_CLONE - creates a clone of an existing table with different name
TABLE_UPDATE - alters schema for an existing table
VIEW_LIST - fetches a list of views in schema of open db instance
VIEW_INFO - fetches some detailed info about a database instance
VIEW_VERIFY - compares a provided view spec to an actual view
VIEW_CREATE - creates a new view in schema of open db inst
VIEW_DELETE - deletes an existing view in open db inst (if it can)
VIEW_CLONE - creates a clone of an existing view with different name
VIEW_UPDATE - alters schema for an existing view
ROUTINE_LIST - fetches a list of routines/procs/funcs of open db instance
ROUTINE_INFO - fetches some detailed info about a database instance
ROUTINE_VERIFY - compares a provided routine spec to an actual routine
ROUTINE_CREATE - creates a new routine in schema of open db inst
ROUTINE_DELETE - deletes an existing routine in open db inst (if it can)
ROUTINE_CLONE - creates a clone of an existing routine with different name
ROUTINE_UPDATE - alters schema for an existing routine
REC_FETCH - fetches records from a table or view of multiple tables
REC_VERIFY - compares a provided set of records to stored ones
REC_INSERT - inserts new recs into a table or view of multiple tables
REC_UPDATE - updates exist recs in a table or view of multiple tables
REC_C_UPDATE - ? updates exist recs with calculation rather than value list
REC_DELETE - deletes exist recs from a table or view of multiple tables
REC_REPLACE - either updates or inserts recs if they exist or not
REC_CLONE - creates a clone of a record in same table/view (if possible)
REC_LOCK - places a lock on recs for a consistant read plus update
REC_UNLOCK - releases an existing lock on recs
TRA_START - starts a new transactional context for later commands
TRA_COMMIT - commits outstanding rec commands, releases locks
TRA_ROLLBACK - rolls back outstanding rec commands, releases locks
CALL_PROC - calls a stored procedure to do some predefined task
CALL_FUNC - calls a stored function to do some predefined task
Note that the above list isn't technically true when certain types of databases are used,
but this exception is something that would be abstracted away by the driver, so isn't part
of the normal RNI. The exception is with certain small RDBMS products which maintain their
database entirely in RAM with periodic flushes to disk, such as with certain small scale
products. What happens is that all parts of the database are stored in a single file on
disk, which is read fully into RAM by the 'DB_OPEN' command, and written fully to disk
either on 'DB_CLOSE' or other times. Most databases by contrast save any changes to their
state to disk immediately. For those databases, 'wd' could be true for any command type.result_type
These are the basic types of Results that can be returned from executing a SQL::SyntaxModel routine/command against a database or other Result context.
ERROR - an error message
CONN - a database connection handle
TRANS - a database transaction handle
CURS - a database record fetch cursor handle
REC - a database row returned by a cursor
VAR - a variable bound to a db command arg or large result
LIT - a literal value (may be multidimensional)NODE TYPES
These are the Node Types that SQL::SyntaxModel knows about; each one corresponds to a SQL::SyntaxModel concept. A Node Ref attribute of a certain type will reference another Node of that Node Type.
Each Node Type below is accompanied with a list of valid attributes for Nodes of that type, including what the attribute means and what major+minor type it is. For each Node Type, its attributes appear in a list; each attribute is described by one (possibly 'wrapped') line, in this format:
<attr-name> - <major-type>.<minor-type>[ - <mandatory-flag>] - <description>In the above usage, the major-type is shortened from its full name like this: "literal" -<gt> "L", "enumerated" -<gt> "E", "node_ref" -<gt> "N". When the major-type is "node_id", then "NODE_ID" appears in place of the combined major and minor types on the line.
The mandatory-flag indicates whether the attribute is mandatory. If it is absent, then the attribute is optional under all local (considering only that same Node and not other Nodes) circumstances. If the flag says "MA", then the attribute must be populated (not be null) in all circumstances; in some cases, it means you explicitely provide a 'default' value such as zero. If the flag says "MC(...)" then the attribute is only mandatory when the other attribute named in the parenthesis is populated, or if the expression in the parenthesis is true.
Many of the Node Type explanations come with a list of example Nodes, formatted like a table, one row per Node, one column per attribute. Starting with SQL::SyntaxModel version 0.10, the order that the Nodes appear in is significant; previously it wasn't, since there were extra attributes (removed in 0.10) which stored important sequence numbers. If these same Nodes were stored in an RDBMS, then extra columns may need to be added to remember their sequence, which an RDBMS does not natively do. Whereas, if the Nodes were stored in XML format, you won't need to add anything, as XML preserves order.
domain
This type of Node describes a named user-defined customization of a base simple_data_type, the latter of which SQL::SyntaxModel recognizes in code. This customization encompases several things: 1. The domain has its own name / identifier that can be referenced by entities such as table column definitions, view column definitions, and routine variables, as their "data type"; 2. The domain 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 hundredths precision); 3. The domain 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 scale 15 and a precision 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::SyntaxModel 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 domain 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 - MA - unique identifier for each Node within this Node Type
name - L.cstr - MA - name of the user customized data type
base_type - E.simple_data_type - MA - name of the base data type this data_type is customized from
num_scale - L.uint - MC(base_type=NUM_INT|NUM_EXA) - maximum size in digits an exact number can be
num_precision - L.uint - MC(base_type=NUM_EXA) - num digits of fractional precision we allowed
num_octets - L.uint - MC(base_type=NUM_APR) - fixed size in octets for an inexact (floating point) num
num_unsigned - L.bool - when base_type is NUM_*, true means only >= 0 may be stored
max_octets - L.uint - MC(base_type=STR_BIT) - maximum size in octets for a binary/bit string
max_chars - L.uint - MC(base_type=STR_CHAR) - 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 - MC(base_type=STR_CHAR) - 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 - MC(base_type=DATETIME) - what calendar the date and time (or date only) is in
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)Unique Constraint List:
primary - [id]
ak_name - [name]Example List:
|id|name |base |ns |np|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 | 3| | | | | | | | | | | | | - an 8-bit precise int btwn -128 and +127
| 8|short |NUM_INT | 5| | | | | | | | | | | | | - a 16-bit precise int btwn -32,768 and +32,767
| 9|int |NUM_INT | 10| | | | | | | | | | | | | - a 32-bit precise int btwn -2,147,483,648 and +2,147,483,647
|10|long |NUM_INT | 19| | | | | | | | | | | | | - a 64-bit precise int btwn -(2^63) and +(2^63)-1
|11|ubyte |NUM_INT | 3| | |1 | | | | | | | | | | - an 8-bit precise integer between 0 and +255
|12|ushort |NUM_INT | 5| | |1 | | | | | | | | | | - a 16-bit precise integer between 0 and +65,535
|13|uint |NUM_INT | 10| | |1 | | | | | | | | | | - a 32-bit precise integer between 0 and +4,294,967,295
|14|ulong |NUM_INT | 19| | |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 precision
|18|dec255 |NUM_EXA |255| | | | | | | | | | | | | - a precise 255-digit num with arbitrary precision
|19|boolean |BOOLEAN | | | | | | | | | | | | | | - a boolean; only 2 values of false or true
|20|datetime|DATETIME| | | | | | | | | | | | |ABS| - a date and time or date, non-calendar specific
|21|dtchines|DATETIME| | | | | | | | | | | | |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 indomain_opt
When a "domain" Node is meant to impose an enumerated list restriction, meaning that only a specific set of values may be stored, then you use a "domain_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 - MA - unique identifier for each Node within this Node Type
domain - N.domain - MA - what "domain" Node this option is associated with
value - L.misc - MA - this is the actual enumerated value that the domain_opt Node representsUnique Constraint List:
primary - [id]
ak_option - [domain,value]Example List:
|id|do|v|
|--|--|-|
| 1|22|M| - "sex" is "Male"
| 2|22|F| - "sex" is "Female"catalog
This type of Node describes a distinct "database" or "data source" or "database instance" or "server" or "host" or "catalog" which contains everything else, both schema and users. This is what an application "connects to" when it will use a database. Often you will have just one "catalog" Node; however, the rationale for being able to describe multiple databases in the same SQL::ObjectModel is, for example, when one database wants to talk directly to another, or an application wants to use several.
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 - MA - unique identifier for each Node within this Node Type
name - L.cstr - corresponds to Oracle "Data Source" (for one db instance querying another)Unique Constraint List:
primary - [id]
ak_name - [name]Example List:
|id|name|
|--|----|
| 1| | - a default database/catalog, usually just used if it is the only database
| 2|site| - a multi-schema genealogy database/catalog that you manage
| 3|dist| - a similar database/catalog that someone else managesuser
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::SyntaxModel 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 explicitely 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::SyntaxModel, 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 - MA - unique identifier for each Node within this Node Type
catalog - N.catalog - MA - the catalog that this user is in
name - L.cstr - the name this user is identified by, and what he authenticates himself with
password - L.cstr - the password this user provides when authenticating themself
default_schema - N.schema - this user's default schema; unqualified object references look hereUnique Constraint List:
primary - [id]
ak_name - [catalog,name]Example List:
|id|ca|name |pass |sc|
|--|--|--------|------|--|
| 1| 1| | | 1| - a default and/or 'public' user, or for db without 'users'
| 2| 2|ronsealy|K34dsD| 2| - Ron Sealy
| 3| 2|joesmith|fdsKJ4| 3| - Joe Smith
| 4| 3|thainuff|9340sd| 4| - Thainuffrole
This type of Node describes a database role, which is a named collection of user privileges. Details to be added later.
privilege
This type of Node describes a specific privilege that is assigned to a role; all users given that role will have this privilege. Details to be added later.
user_role
Each Node of this type indicates that a specific user has been granted a specific role, and the privileges that come with it. Details to be added later.
schema
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::SyntaxModel 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::SyntaxModel 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 - MA - unique identifier for each Node within this Node Type
catalog - N.catalog - MA - the catalog that this schema/namespace is in
name - L.cstr - the name this schema/namespace is identified by
owner - N.user - MA - the user that owns this schemaUnique Constraint List:
primary - [id]
ak_name - [catalog,name]Example List:
|id|ca|name|ow|
|--|--|----|--|
| 1| 1| | 1| - a default schema/namespace, usually just used if it is the only schema/namespace
| 2| 2|gene| 2| - store all the normal app data that constitutes a genealogy database
| 3| 2|dd | 3| - store a data dictionary describing an application for editing gene data
| 4| 3|gene| 4| - in case we want to access genealogy data collected by someone elsesequence
This type of Node describes a simple named sequence number generator, which will produce integers (normal purpose) but not fractional numbers. Apparently sequence generators are not explicitely part of the SQL standard, but many database products have them.
Attribute List:
id - NODE_ID - MA - unique identifier for each Node within this Node Type
schema - N.schema - MA - the schema that this sequence generator is in
name - L.cstr - MA - 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 uniqueUnique Constraint List:
primary - [id]
ak_name - [schema,name]table
This type of Node describes the core (single cardinality) description 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.
Attribute List:
id - NODE_ID - MA - unique identifier for each Node within this Node Type
schema - N.schema - MA - the schema that this table is in
name - L.cstr - MA - the name this table is identified byUnique Constraint List:
primary - [id]
ak_name - [schema,name]Example List:
|id|sc|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_col
This type of Node describes a single table column, and has details that are specific to that column.
Attribute List:
id - NODE_ID - MA - unique identifier for each Node within this Node Type
table - N.table - MA - unique identifier for this table
name - L.cstr - MA - unique identifier for column within table
domain - N.domain - MA - the user-customized data type for this column
mandatory - L.bool - MA - true means col value is mandatory (not null constr), false if not (null)
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 sequenceUnique Constraint List:
primary - [id]
ak_name - [table,name]Example List:
|id|tb|name |do|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_ind
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) ind_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 - MA - unique identifier for each Node within this Node Type
table - N.table - MA - unique identifier for this table
name - L.cstr - MA - unique identifier for index within table
ind_type - E.table_index_type - MA - what type of index this is (eg: UNIQUE, FOREIGN)
f_table - N.table - MC(ind_type=FOREIGN|UFOREIGN) - foreign key constraining tableUnique Constraint List:
primary - [id]
ak_name - [table,name]Example List:
|id|tb|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_ind_col
This type of Node intersects between a table_ind and a table_col; it says which table columns are involved in each table index.
Attribute List:
id - NODE_ID - MA - unique identifier for each Node within this Node Type
table_ind - N.table_ind - MA - unique identifier for index within table
table_col - N.table_col - MA - unique identifier for column within table
f_table_col - N.table_col - when the index type is a foreign key, this is the constraining columnUnique Constraint List:
primary - [id]
ak_table_col - [table_ind,table_col]Example List:
|id|ind|col|fcol|
|--|---|---|----|
| 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 mothertrigger
This type of Node says when a table has an associated trigger, which is a special unnamed routine that executes automatically when certain kinds of DML activity happen to the table. Triggers can be set to run before or after said activity, either once per SQL statement execution or once per each row worked on; they can run on inserts and/or updates and/or deletes. The "routine" Node has the "trigger" Node as its parent.
Attribute List:
id - NODE_ID - MA - unique identifier for each Node within this Node Type
table - N.table - MA - unique identifier for the table to watch
run_before - L.bool - MA - true when the trigger should execute before attempted change is made
run_after - L.bool - MA - true when the trigger should execute after attempted change is made
run_instead - L.bool - MA - true when the trigger should execute instead of the attempted change
on_insert - L.bool - MA - true when trigger should execute when a record insert is attempted
on_update - L.bool - MA - true when trigger should execute when a record update is attempted
on_delete - L.bool - MA - true when trigger should execute when a record delete is attempted
for_each_row - L.bool - MA - true when trigger is re-executed for each row affected; false is once
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.htmUnique Constraint List:
primary - [id]view
This type of Node describes the core (single cardinality) description of a database view or a SQL query; SQL::SyntaxModel 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 just one "view" Node, through the optional match_table attribute. When that attribute is set, the view will exactly match the interface of that table, with no further customization being allowed (except, perhaps, row paging). 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.
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::SyntaxModel 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.
Attribute List:
id - NODE_ID - MA - unique identifier for each Node within this Node Type
view_context - E.view_context - MA - what context this view expects to live in; eg: schema, cursor, application
view_type - E.view_type - MA - what kind of view this is; eg: table, compound, has-subqueries, recursive
schema - N.schema - MC(view_context=SCHEMA) - the schema that this view is in, if any
name - L.cstr - MC(view_context=SCHEMA) - the name this view is identified by, if any
routine - N.routine - MC(view_context=CURSOR|APPLIC) - parent view of this one, if any; empty if this is the root view
p_view - N.view - MC(view_context=INSIDE) - parent view of this one, if any; empty if this is the root view
match_table - N.table - MC(view_type=TABLE) - when set, the view matches exactly the interface for one table
c_merge_type - E.compound_operator - MC(view_type=COMPOUND) - how to merge any child rowsets/views of this one together, if any
may_write - L.bool - MA - true when we should at least try allowing updates through the view
page_rows - L.bool - true when we should page the resulting rows so only some are returned; default false
maxr_lit_val - L.uint - if paging, this literal value says maximum rows to be returned
maxr_routine_var - N.routine_var - if paging, get max rows from this containing routine variable
skipr_lit_val - L.uint - if paging, this literal value says rows to skip before returning any
skipr_routine_var - N.routine_var - if paging, get skip rows from this containing routine variableUnique Constraint List:
primary - [id]
ak_name - [schema,name]Example List:
|id|contex|type |sc|name|match|wr|
|--|------|------|--|----|-----|--|
| 1|APPLIC|SIMPLE| | | |1 | - user - joins the "user_auth" and "user_profile" tables
| 2|APPLIC|SIMPLE| | | |0 | - person_with_parents - joins the "person" table with itself twice
| 3|APPLIC|SIMPLE| | | |0 | - user_theme - shows how many users use each theme pref value
| 4|APPLIC|TABLE | | | 4|1 | - person - simply the "person" tableview_arg
When this view is a named sub-query, declared in the "with" clause of the main query/view, then it can take arguments to define its caller context; each "view_arg" Node you attach to a "view" Node names an argument. This Node type is not used for anything else; it is not like a routine arg that doubles for an app bind var.
view_col
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 - MA - unique identifier for each Node within this Node Type
view - N.view - MA - unique identifier for this view
name - L.cstr - MA - unique identifier for column within view
domain - N.domain - MA - the user-customized data type for this column
sort_priority - L.uint - if valued, priority for final result rows (after rs merges) to be sorted by this column
is_writeable - L.bool - true when enough info is known to write changes to this column
is_required - 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 rowUnique Constraint List:
primary - [id]
ak_name - [view,name]Example List:
|id|vw|col_name |do|so|
|--|--|-------------|--|--|
| 1| 1|user_id | 9| | - unique internal id for user, not changeable
| 2| 1|login_name |23| 1| - who user authenticates into app as
| 3| 1|login_pass |23| | - also used with authentication
| 4| 1|private_name |24| | - real name that app service provider can know
| 5| 1|private_email|24| | - email that service provider can contact user with
| 6| 1|may_login |19| | - true when user may use app at all
| 7| 1|max_sessions | 7| | - max concurrent app logins user may have
| 8| 1|public_name |25| | - public name or pen name or public alias
| 9| 1|public_email |25| | - email address the public may use for contact
|10| 1|web_url |25| | - personal/business website address of user
|11| 1|contact_net |25| | - info like ICQ number or other internet handles
|12| 1|contact_phy |25| | - physical address or city or telephone number
|13| 1|bio |25| | - brief description of the person
|14| 1|plan |25| | - what the person plans to do next
|15| 1|comments |25| | - other comments
|16| 2|self_id | 9| | - unique actual id for person, not changeable
|17| 2|self_name |24| 1| - actual name of the person they are called by
|18| 2|father_id | 9| | - person_id of this person's father, if known
|19| 2|father_name |24| 2| - actual name of the person's father
|20| 2|mother_id | 9| | - person_id of this person's mother, if known
|21| 2|mother_name |24| 3| - actual name of the person's mother
|22| 3|theme_name |27| 2| - name of a user theme
|23| 3|theme_count | 9| 1| - count of users that use this themeview_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 - MA - unique identifier for each Node within this Node Type
view - N.view - MA - unique identifier for view
name - L.cstr - MA - 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)Unique Constraint List:
primary - [id]
ak_name - [view,name]Example List:
|id|vw|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" tableview_src_col
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 - MA - unique identifier for each Node within this Node Type
src - N.view_src - MA - which view source this column is associated with
match_table_col - N.table_col - which column from the source table is represented, if applicable
match_view_col - N.view_col - which column from the source view is represented, if applicableUnique Constraint List:
primary - [id]Example List:
|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"view_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 compound. 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 - MA - unique identifier for each Node within this Node Type
view - N.view - 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_type - E.join_operator - MA - what kind of table/view join is being doneUnique Constraint List:
primary - [id]
ak_join - [lhs_src,rhs_src]
ak_limit_one - [rhs_src]Example List:
|id|vw|lhs|rhs|type|
|--|--|---|---|----|
| 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_col
This type of Node describes a This is an intersection table between view_join and either table_col or view_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 (each a table or a view), both 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 - MA - unique identifier for each Node within this Node Type
join - N.view_join - MA - which view join this column is associated with
lhs_src_col - N.view_src_col - MA - which column from the left source is being matched on
rhs_src_col - N.view_src_col - MA - which column from the right source is being matched onUnique Constraint List:
primary - [id]Example 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_hierarchy
This type of Node describes an n-level hierarchical join to be part of a view compound definition (similar to Oracle's start-with-connect-by feature). Only one is allowed per view compound.
Note: While SQL::SyntaxModel supports the SQL-1999 "with" syntax, that among its other powers can do hierarchical queries, I have provided "view_hierarchy" as a way of invoking Oracle-style "start-with connect-by" tersely.
Attribute List:
id - NODE_ID - MA - unique identifier for each Node within this Node Type
view - N.view - unique identifier for this view
start_src_col - N.view_src_col - MA - which source column is checked against to determine the root of the hierarchy
start_lit_val - L.misc - if start_src_col is being compared to a literal, then store the literal value here
start_routine_var - N.routine_var - if start_src_col is being compared to a containing routine variable
conn_src_col - N.view_src_col - MA - which source column contains a record's own identifier against which children match
p_conn_src_col - N.view_src_col - MA - which source column contains the value of a parent identifierUnique Constraint List:
primary - [id]view_expr
This type of Node is intended to describe most of the parts of a SQL "select" which is the basis for a view compound 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 column list definitions that start a "select", and the "from" (includes join definitions), and any "union" or similar operations. See the other view_* tables for those.
The "order by" described by this table is applied prior to any compound view merge operations; since those operations tend to change the order of rows, it would only make sense to use this when your view only has one source is not a compound view. If this is a compound view, or you only want to sort by output columns, then use the sort_priority field in view_col to sort instead, which has much simpler syntax, and is evaluated after all compound view merging is done.
This type of Node describes a sub-expression that is part of one of several optional main/root expressions in a view compound 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. The "start with" and "connect by" can only have one root expression each.
Attribute List:
id - NODE_ID - MA - unique identifier for each Node within this Node Type
expr_type - E.basic_expr_type - MA - what type of expression this is; eg: literal, variable, column, view, function
p_expr - N.view_expr - parent expression of this one, if any; empty if this is the root expression
view - N.view - unique identifier for this view
view_part - E.view_part - what "part" of a view definition this is defining
view_col - N.view_col - unique identifier for interface/output column within view
lit_val - L.misc - if expr_type is 'lit', then store the literal value here
view_arg - N.view_arg - if expr_type is 'arg', then use this containing routine variable
routine_arg - N.routine_arg - if expr_type is 'arg', then use this containing routine variable
routine_var - N.routine_var - if expr_type is 'var', then use this containing routine variable
src_col - N.view_src_col - if expr_type is 'col', then use this source table/view column
f_view - N.view - if expr_type is 'view', then put the view/subquery id here; subquery is child of cont view
sfunc - E.standard_func - if expr_type is 'sfunc', then put the standard built-in function name/code here
ufunc - N.routine - if expr_type is 'ufunc', then put the user-defined stored function (routine) id hereUnique Constraint List:
primary - [id]Example List:
|id|cl|vw|part |pe|type |lit |blv|cmv |src|fvw|sf |uf|
|--|--|--|------|--|-----|-----|---|--------|---|---|------|--|
| 1| | 1|WHERE | |SFUNC| | | | | |EQ | | - standard function of "eq(a,b)"
| 2| | | | 1|COL | | | | 1| | | | - match column "user_auth"."user_id"
| 3| | | | 1|VAR | | |curr_uid| | | | | - value of bind var "curr_uid"
| 4| | 2|WHERE | |SFUNC| | | | | |AND | | - standard function of "and(a,b)"
| 5| | | | 4|SFUNC| | | | | |LIKE | | - standard function of "like(a,b)"
| 6| | | | 5|COL | | | | 20| | | | - match column "father"."name"
| 7| | | | 5|VAR | | |srchw_fa| | | | | - value of bind var "srchw_fa"
| 8| | | | 4|SFUNC| | | | | |LIKE | | - standard function of "like(a,b)"
| 9| | | | 8|COL | | | | 22| | | | - match column "mother"."name"
|10| | | | 8|VAR | | |srchw_mo| | | | | - value of bind var "srchw_mo"
|11| | 3|WHERE | |SFUNC| | | | | |EQ | | - standard function of "eq(a,b)"
|12| | | |11|COL | | | | 23| | | | - match column "user_pref"."pref_name"
|13| | | |11|LIT |theme| | | | | | | - match literal value of "theme"
|14| | 3|GROUP | |COL | | | | 24| | | | - group by "user_pref"."pref_value"
|15| | 3|HAVING| |SFUNC| | | | | |GT | | - standard function of "gt(a,b)"
|16| | | |15|SFUNC| | | | | |GCOUNT| | - standard function of "gcount(col)"
|17| | | |15|LIT | 1| | | | | | | - match literal value of "theme"
|21| 1| 1|RESULT| |COL | | | | 1| | | |
|22| 2| 1|RESULT| |COL | | | | 2| | | |
|23| 3| 1|RESULT| |COL | | | | 3| | | |
|24| 4| 1|RESULT| |COL | | | | 4| | | |
|25| 5| 1|RESULT| |COL | | | | 5| | | |
|26| 6| 1|RESULT| |COL | | | | 6| | | |
|27| 7| 1|RESULT| |COL | | | | 7| | | |
|28| 8| 1|RESULT| |COL | | | | 9| | | |
|29| 9| 1|RESULT| |COL | | | | 10| | | |
|30|10| 1|RESULT| |COL | | | | 11| | | |
|31|11| 1|RESULT| |COL | | | | 12| | | |
|32|12| 1|RESULT| |COL | | | | 13| | | |
|33|13| 1|RESULT| |COL | | | | 14| | | |
|34|14| 1|RESULT| |COL | | | | 15| | | |
|35|15| 1|RESULT| |COL | | | | 16| | | |
|36|16| 2|RESULT| |COL | | | | 17| | | |
|37|17| 2|RESULT| |COL | | | | 18| | | |
|38|18| 2|RESULT| |COL | | | | 19| | | |
|39|19| 2|RESULT| |COL | | | | 20| | | |
|40|20| 2|RESULT| |COL | | | | 21| | | |
|41|21| 2|RESULT| |COL | | | | 22| | | |
|42|22| 3|RESULT| |COL | | | | 24| | | |
|43|23| 3|RESULT| |SFUNC| | | | | |GCOUNT| |
|44|23| | |43|COL | | | | 24| | | |
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" ("gcount" has no args).
Notes: The example 'routine_var' values are actually incorrect and will be changed to id numbers.Had there been separate Node types for the above based on view part, here are their descriptions:
view_col_def: This type of Node describes a sub-expression that is part of an interface/output column definition in a view; this is an intersection table between view_col and each compound view member; there needs to be one full expression for every view_col/view-compound-member combination in a view. These expressions are used in the first part of the SQL "where" clause, before "from"; these are not intended to affect how many columns or rows the view returns, but only what the exact content of each column is. All of these sub-expressions form a hierarchy where children are "arguments" to their parent expressions; the root expression is supposed to return the same type of value as the domain specified in the view_col record.
view_src_filter: Each Node of this type uniquely corresponds to a sub-expression that is part of a filter definition in a view compound. This filter definition is run against the rows in the view sources just after or while they are being joined, and it is supposed to eliminate most or all of the source rows that we don't want to keep. In other words, the whole expression becomes the "where" clause in the generated SQL, where applicable (it runs prior to the SQL "group by"). All of these sub-expressions form a hierarchy where children are "arguments" to their parent expressions; the root expression is supposed to return a boolean value.
view_grp: When any Nodes of this type exist for a particular view compound, each one matches a source column, and the rows returned by the view compound will be grouped (self-merged) so that each row has a unique permutation of values in the columns being grouped on. Also, when any view_grp records are used, that view compound may only use the columns being grouped on in its output column definition. Certain standard functions can only be used when grouping.
view_grp_filter: This type of Node describes a sub-expression that is part of a filter definition in a view compound. This filter definition is run against the rows after they have been grouped; it is supposed to do additional filtering that could only be done after grouping, such as when the sub-expression uses standard functions that only work in a grouping SQL select. This whole expression becomes the "having" clause in the generated SQL, where applicable. All of these sub-expressions form a hierarchy where children are "arguments" to their parent expressions; the root expression is supposed to return a boolean value.
view_order: This type of Node says what order to return the rows for a view compound or entire view in; there is one record for each solumn that is being sorted by. They correspond to the SQL "order by". Also, when any view_grp records are used, that view compound may only use the columns being grouped on in its output column definition. Certain standard functions can only be used when grouping.
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::SyntaxModel 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 - MA - unique identifier for each Node within this Node Type
routine_type - E.routine_type - MA - what kind of routine this is
schema - N.schema - the schema that this routine is in, if any
name - L.cstr - the name this routine is identified by, if any
trigger - N.trigger - if this routine is the body for a trigger, it is our parent Node
application - N.application - if routine is in the application space, an app is our parent
p_routine - N.routine - parent routine of this one, if any; empty if this is the root routine
return_type - E.domain - if this routine returns a value, this is its data typeUnique Constraint List:
primary - [id]
ak_name - [schema,name]routine_arg
This type of Node describes an input and/or output argument variable to a routine.
routine_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 - MA - unique identifier for each Node within this Node Type
routine - N.routine - MA - what routine this variable is in
name - L.cstr - MA - unique identifier for variable within view
var_type - E.basic_var_type - MA - what basic variable type the variable is
domain - N.domain - if the var type is 'scalar', this is its user customized data type
init_lit_val - L.misc - if var type is 'scalar', an initial value for var can go here
curs_view - N.view - if var type is 'cursor', then put the view defining it here
is_constant - L.bool - if the var type is 'scalar', this says its value may not be changedUnique Constraint List:
primary - [id]
ak_name - [routine,name]command
This type of Node describes a command or statement or line within a routine, which includes such operations as variable assignment or reading a cursor or executing a DML operation. In the case of an assignment, it is the routine_expr record(s) which refer to the routine_stmt record. If this statement is something which opens a new unnamed routine context, the routine record is referred to by the routine_stmt record.
Attribute List:
id - NODE_ID - MA - unique identifier for each Node within this Node Type
routine - N.routine - MA - what routine this statement is in
stmt_type - E.basic_stmt_type - MA - what type of stmt this is; eg: assignment, condit, call, loop, dml, return
dest_arg - N.routine_arg - if the stmt type is 'assign', this 'out' arg is where the expression result goes
dest_var - N.routine_var - if the stmt type is 'assign', this var is where the expression result goes
sproc - E.standard_proc - if expr_type is 'sproc', then this is the standard built-in procedure to call
uproc - N.routine - if expr_type is 'uproc', then this is the user-defined stored function (routine) to call
c_routine - N.routine - if the stmt type starts an unnamed routine context, this is the child routine idUnique Constraint List:
primary - [id]routine_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 command/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 - MA - unique identifier for each Node within this Node Type
expr_type - E.basic_expr_type - MA - what type of expression this is; eg: literal, variable, column, function
command - N.command - unique identifier for routine command/statement this expression is part of
p_expr - N.routine_expr - parent expression of this one, if any; empty if this is the root expression
lit_val - L.cstr - if expr_type is 'lit', then store the literal value here
src_arg - N.routine_arg - if expr_type is 'arg', then use this containing routine argument
src_var - N.routine_var - if expr_type is 'var', then use this containing routine variable
sfunc - E.standard_func - if expr_type is 'sfunc', then put the standard built-in function name/code here
ufunc - N.routine - if expr_type is 'ufunc', then put the user-defined stored function (routine) id hereUnique Constraint List:
primary - [id]application
This type of Node collects information that is specific to a database-using application but is not stored in a database. Details to be added later.
sql_fragment
This type of Node holds literal SQL fragments that you can inject into SQL that is generated from SQL::SyntaxModel Nodes. Details to be added later.
SEE ALSO
perl(1), SQL::SyntaxModel, Rosetta, Rosetta::Framework.