NAME

SQL::ObjectModel::DataDictionary - Describe a database suitable for storing SQL::ObjectModels.

COPYRIGHT AND LICENSE

This file is part of the SQL::ObjectModel library (libSOM).

SQL::ObjectModel is Copyright (c) 1999-2003, 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::ObjectModel 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::ObjectModel 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::ObjectModel 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::ObjectModel 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::ObjectModel statically or dynamically with other modules is making a combined work based on SQL::ObjectModel. Thus, the terms and conditions of the GPL cover the whole combination.

As a special exception, the copyright holders of SQL::ObjectModel give you permission to link SQL::ObjectModel 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::ObjectModel (the version of SQL::ObjectModel 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::ObjectModel, and which is fully useable when not linked to SQL::ObjectModel in any form.

Note that people who make modified versions of SQL::ObjectModel 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::ObjectModel would appreciate being informed any time you create a modified version of SQL::ObjectModel that you are willing to distribute, because that is a practical way of suggesting improvements to the standard version.

DESCRIPTION

All concepts in a SQL::ObjectModel can be represented by a data dictionary. Such a data dictionary can be a linked in-memory set of objects (or serialized version thereof), or they can be represented by records in database tables having specific columns and constraints. This documentation will attempt to explain the components of a SQL::ObjectModel data dictionary and how they relate. Since SQL::ObjectModel doesn't actually use any database tables internally, and that it is your application which would map such database tables to SQL::ObjectModel objects, you are free to modify this schema to your liking. However, it is recommended that you mainly just add columns, such as for adding user visible text descriptions of each record, rather than changing or removing the suggested columns.

STRUCTURE

Here is a set of diagrams that show visually how the various tables relate. The structure of the individual tables themselves are explained in other sections further below. All of the relationships are either many-to-one or one to one; most are optional, but for simplicity all lines are solid below.

Data Types

cct_basic_data_type
    |
   /|\
data_type=>---+---cct_str_latin_case
   \|/        |
    |         |
cct_str_enc   cct_datetime_calendar

Databases and Name Spaces

   user
   \|/
    |
 database   view(named)
    |       \|/
   /|\       |
namespace----+-<=block(named)
    |        |
   /|\      /|\
  table   sequence

Tables

 trigger      data_type
   \|/            |
    |            /|\
  table------<=table_col=>----sequence
    |(2)          |(2)
   /|\           /|\
table_ind--<=table_ind_col
   \|/
    |
cct_index_type

Views

 data_type   cct_view_type
     |           |
    /|\         /|\                     cct_view_part;cct_basic_expr_type;view;
 view_col=>----view  cct_rs_merge_type  cct_standard_func;block(user_func)
     |           |     |                   |
    /|\         /|\   /|\                 /|\
view_col_def=>--view_rowset---<=view_part_def;=>---view_src_col
    \|/             \|/         view_hierarchy         |(2)
     |               |                                /|\
view_src_col=>----view_src----<=view_join---<=view_join_col
                    \|/   (2)     |
                     |           /|\
                 table;view  cct_rs_join_type

Sequences

sequence

Blocks

 table   cct_block_type   data_type
   |            |             |
  /|\          /|\           /|\
trigger-------block-----<=block_var=>---cct_basic_var_type
                |   -------|  |
               /|\ /|\       /|\
            block_stmt--<=block_expr=>---cct_basic_expr_type;view;
            \|/    \|/                   cct_standard_func;block(user_func)
             |      ----------|
cct_basic_stmt_type   cct_standard_proc

Users

user---<=privilege

Commands and Results

cct_command_type
      |
     /|\
   command=>---command_list
    |  \|/ (a command can be executed multiple times for multiple results)
   /|\  |  (a result can be used as input or context for multiple commands)
   result
     \|/
      |
cct_result_type

CONSTANT CODE TABLES

These tables correspond to basic concepts that are hard-coded into SQL::ObjectModel and they are intended to be read-only. The table contents are meant for two main uses, one being for foreign key constraints on enumerated type columns in other tables, and the other being for a source of picklist values on forms that users define SQL::ObjectModels with.

The values in these tables are stored in code by the SQL::ObjectModel::Codes class. You can get the values for these tables from that class, and don't have to code them yourself. The other SQL::ObjectModel classes use the Codes class internally for when they need to know this information, and hence are not susceptible to security risks from someone altering the following tables in the database.

cct_basic_var_type

This table enumerates the basic variable or container types that block 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.

Column List:

code - str,req - name of the base data type
is_ref - bool,req - if true, var stores reference to var type otherwise desc here
is_scalar - bool,req - true when variable can only hold a single distinct value at once
is_record - bool,req - true when var holds multiple distinct values, each keyed by a str/name
is_array - bool,req - true when var holds ordered list of sca/rec elems, accessible in any order
is_cursor - bool,req - true when var holds a SQL-select definition; rec only acc sequentially
can_use_in_table - bool,req - true when may be used for a table column

Index List:

primary - unique - [code]

Record List:

|code  |rf|sc|re|ar|cu|ta|
|------|--|--|--|--|--|--|
|scalar|0 |1 |0 |0 |0 |1 | - a single value
|record|0 |0 |1 |0 |0 |0 | - a list of scalars (can differ in type), keyed by a string/column-name
|array |0 |0 |0 |1 |0 |0 | - an ordered list of elements/records, all same type, can be read in any order
|cursor|0 |0 |0 |0 |1 |0 | - an ordered list of elements/records, can only/usually be read in sequential order
|ref   |1 |0 |0 |1 |0 |1 | - reference to a record, such as in object-relational databases

cct_basic_data_type

This table enumerates the basic scalar data value types that table columns or view columns or block variables or arguments can be.

Column List:

code - str,req - name of the base data type
is_storable - bool,req - true when may be used for a table col, false only be intermediate val
size_undefined - bool,req - true when user can't customize space used by data type
size_is_bytes - bool,req - true when can customize type to a size measured in bytes
size_is_chars - bool,req - true when can customize type to a size measured in chars
size_is_fixed - bool - true when customized size is what data always takes up
size_is_max - bool - true when data can take up variable space with size the maximum possible
typ_min_size - uint - suggested typical minimum size to customize to
typ_max_size - uint - suggested typical maximum size to customize to
can_use_as_num - bool,req - true when data can be used in mathematics in its natural form

Index List:

primary - unique - [code]

Record List:

|code    |st|un|by|ch|if|im|min|       max|num|
|--------|--|--|--|--|--|--|---|----------|---|
|bin     |1 |0 |1 |0 |0 |1 |  1|4294967295|0  | - stores anything as binary data
|str     |1 |0 |0 |1 |0 |1 |  1|4294967295|0  | - stores character data
|bool    |1 |1 |0 |0 |  |  |   |          |0  | - a boolean; only 2 values of false or true
|int     |1 |0 |1 |0 |1 |0 |  1|         8|1  | - a precise integer
|float   |1 |0 |1 |0 |1 |0 |  4|         8|1  | - an imprecise fractional number
|dec     |1 |0 |1 |0 |1 |0 |  1|       255|1  | - a precise fractional number as base-10 str
|datetime|1 |1 |0 |0 |  |  |   |          |0  | - a date or time or both (may be split up)
|numeric |0 |  |  |  |  |  |   |          |1  | - a num type for math exprs, can be int or not

Geographical types are expected to be added as well.  Maybe enumerated or set types also.

cct_str_enc

This table enumerates the set of known character string encodings that can be used by character data, with Unicode being the recommended one to use when possible, and with Ascii being the most common legacy encoding. Encodings are only applicable to data which is measured in characters.

Column List:

code - str,req - name of the character encoding
bytes_per_char - int,req - num bytes used to store each character; typically 1
has_latin_chars - bool,req - true when operations like upper/lowercase have any meaning

Index List:

primary - unique - [code]

Record List:

|cod|bpc|lat|
|---|---|---|
|u8 |  1|1  | - 8 bit unicode
|u16|  2|1  | - 16 bit unicode
|u32|  4|1  | - 32 bit unicode
|asc|  1|1  | - ascii
|ebs|  1|1  | - ebsdic

Note that some of these examples may be wrong for practical use and be changed later.

cct_str_latin_case

This table enumerates a set of options for normalizing the case of latin characters in strings, so that they can be matched reliably with case-sensitive (normal) string operations, even if they are conceptually case-insensitive. When this attribute is used with table columns, for example, it may cause data to be upper/lowercased prior to insertion, losing knowledge of the old case.

One place that this is helpful is when dealing with database systems that are both case-insensitive and case-sensitive with the same data depending on how you access it. For example, Oracle table and column names when declared as barewords, the normal SQL way, will match in a case-insensitive fashion with other barewords, but will match case-sensitive full-uppercase only when matching either with double-quoted strings or when looking in the special 'user_tables' et al Oracle views. (As if that paragraph is helpful?)

Note that this table's function may be expanded later (with rename) to handle things like whitespace trimming et al, or maybe it won't.

Column List:

code - str,req - name of the latin case normalization type
uc_latin - bool,req - uppercase everything prior to writing, no change on reading
lc_latin - bool,req - lowercase everything prior to writing, no change on reading

Index List:

primary - unique - [code]

Record List:

|co|uc|lc|
|--|--|--|
|pr|0 |0 | - preserve - everything is case-sensitive, no transformation is done (default)
|uc|1 |0 | - toupper  - uppercase everything prior to writing, no change on reading
|lc|0 |1 | - tolower  - lowercase everything prior to writing, no change on reading

cct_datetime_calendar

This table enumerates the set of known calendars which a date and time 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.

Column List:

code - str,req - name of the calendar

Index List:

primary - unique - [code]

Record List:

|cod|
|---|
|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.

cct_index_type

This table enumerates 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.

Column List:

code - str,req - name of the table index type
is_unique - bool,req - true if index imposes a unique key constraint
is_foreign - bool,req - true if index imposes a foreign key constraint

Index List:

primary - unique - [code]

Record List:

|code    |uk|fk|
|--------|--|--|
|noconstr|0 |0 | - index is simply to make db faster, no constraints are imposed
|unique  |1 |0 | - index imposes a unique key constraint on a set of columns
|foreign |0 |1 | - index imposes a many-to-one foreign key constraint on a set of columns
|uforeign|1 |1 | - index imposes a one-to-one foreign key (assuming target is a unique key)

cct_view_type

This table enumerates the basic types that a view can be, since SQL::ObjectModel "views" have a broader meaning than in classic SQL. There are 4 main types.

First, 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 block; 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.

Column List:

code - str,req - name of the view type
inherit_props - bool,req - true when view declared inside another, inherits parent attrs
can_have_vars - bool,req - true when view can or might have variables

Index List:

primary - unique - [code]

Record List:

|code  |inh|var|
|------|---|---|
|object|0  |0  | - a named schema object, the classic "view"
|caller|0  |1  | - declared by a caller application and doesn't persist
|cursor|0  |1  | - a cursor declared inside a block
|inside|1  |1  | - a subquery inside another view

cct_rs_merge_type

This table enumerates the standard ways that multiple compatible view row sets can be merged into a single row set, where all of the input row sets have the same set of columns as the the output row set, and each single output row matches a distinct input row. A union is the most common such operation.

Column List:

code - str,req - name of the view row set merge type
max_inputs - int,req - number of input sets that this set merge operator can take; 0 = unlimited
keeps_all - bool,req - true if all input rows are always returned, false if some may not be
is_unique - bool,req - true if all returned rows are distinct, false if duplicates may occur

Index List:

primary - unique - [code]

Record List:

|cod|max|all|unq|
|---|---|---|---|
|dis|  1|0  |1  | - distinct - all distinct rows from a single input set, like a "select distinct ..."
|all|  0|1  |0  | - all - all input rows are returned, like a "union all" (the default)
|uni|  0|0  |1  | - union - all distinct rows that are each in at least one input set
|int|  0|0  |1  | - intersect - all distinct rows that are each in every input set
|exc|  0|0  |1  | - exclusive - all distinct rows that are each in exactly one input set (exclusive or)
|min| ?2|0  |1  | - minus - all distinct rows from one input set that are not in the others

cct_rs_join_type

This table enumerates 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.

Column List:

code - str,req - name of the table or view join type
outer_join - bool,req - when true all rows from one source kept even if not matched in the other

Index List:

primary - unique - [code]

Record List:

|code |out|
|-----|---|
|equal|  0| - an equal-join; only rows with related entries in both sources are kept
|left |  1| - 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.
Full outer join may also be added.

cct_view_part

This table enumerates the standard parts of a view definition which exist in the SQL following the "from" part. See "view_part_def" for an explanation.

Column List:

code - str,req - name of the view part

Index List:

primary - unique - [code]

Record List:

|code |
|-----|
|where| - the SQL "where" part
|group| - the SQL "group by" part
|havin| - the SQL "having" part
|order| - the SQL "order by" part

cct_basic_expr_type

This table enumerates the basic expression types that views or blocks 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).

Column List:

code - str,req - name of the basic expression type
is_lit - bool,req - true when expression is a literal value such as a quoted string or number
is_var - bool,req - true when expression is a variable name, such as in a block or app call
is_col - bool,req - true when expression is a column name, such as in a view, or cursor in block
is_view - bool,req - true when expression is a view declaration, such as a subquery or cursor def
is_sfunc - bool,req - true when expression is a call to a standard built-in function
is_ufunc - bool,req - true when expression is a call to a user-defined stored function (block)
can_have_args - bool,req - true when expression might have child expressions

Index List:

primary - unique - [code]

Record List:

|code |li|va|co|vw|sf|uf|ha|
|-----|--|--|--|--|--|--|--|
|lit  |1 |0 |0 |0 |0 |0 |0 | - a literal value, such as number or string
|var  |0 |1 |0 |0 |0 |0 |0 | - a variable name, such as stored block or app bind var
|col  |0 |0 |1 |0 |0 |0 |0 | - a column name, such as in a view or cursor in block
|view |0 |0 |0 |1 |0 |0 |0 | - a view declaration, such as a subquery or a cursor
|sfunc|0 |0 |0 |0 |1 |0 |1 | - a standard built-in function; one of cct_standard_func
|ufunc|0 |0 |0 |0 |0 |1 |1 | - a user defined stored function (block)

cct_standard_func

This table enumerates a list of the standard built in functions that can be used as parts of view definitions or code blocks like stored procedures or functions or triggers.

Column List:

code - str,req - name of the standard function
can_have_args - bool,req - true when function can have any arguments
req_min_args - int,req - minimum num of args function must have, whether unlimited or not
req_arg_mult - int - all args over minimum must be supplied in this multiple (default=1)
unlim_args - bool,req - true when function can take any number of arguments over minimum
req_max_args - int - count of maximum args function can have when not unlimited
can_use_in_column - bool,req - true when func type may be used in view column def, [having/order]
can_use_in_filter - bool,req - true when func type may be used in view filter (where)
can_use_in_block - bool,req - true when func type may be used in a block (eg: stored proc)
is_aggregate_func - bool,req - true when is an aggregate func (a grouping func, like 'count')
data_type - str - basic data type that function returns

Index List:

primary - unique - [code]
fk_data_type - foreign - [data_type]->[cct_basic_data_type]:[code]

Record List:

|code   |ha|mn|ml|ul|mx|cc|cf|cb|ag|datatype|
|-------|--|--|--|--|--|--|--|--|--|--------|
|to_num |1 | 1|  |0 | 1|1 |1 |1 |0 |numeric | - cast arg as numeric (might take more args)
|to_bool|1 | 1|  |0 | 1|1 |1 |1 |0 |bool    | - cast arg as boolean (might take more args)
|to_int |1 | 1|  |0 | 1|1 |1 |1 |0 |int     | - cast arg as integer (might take more args)
|to_str |1 | 1|  |0 | 1|1 |1 |1 |0 |str     | - cast arg as string (might take more args)
|to_date|1 | 1|  |0 | 1|1 |1 |1 |0 |datetime| - cast arg as datetime (might take more args)
|not    |1 | 1|  |0 | 1|1 |1 |1 |0 |bool    | - a logical 'not', true iif lone arg is false
|and    |1 | 2| 1|1 |  |1 |1 |1 |0 |bool    | - a logical 'and', true iif every arg is true
|or     |1 | 2| 1|1 |  |1 |1 |1 |0 |bool    | - a logical 'or', true iif at least one arg is true
|xor    |1 | 2| 1|1 |  |1 |1 |1 |0 |bool    | - a logical 'xor', true iif 1+ arg true and 1+ arg false
|eq     |1 | 2|  |0 | 2|1 |1 |1 |0 |bool    | - true if both args are equal (both args cast same tp)
|ne     |1 | 2|  |0 | 2|1 |1 |1 |0 |bool    | - true if both args are unequal (when same data type)
|lt     |1 | 2|  |0 | 2|1 |1 |1 |0 |bool    | - true if first arg is less than second
|gt     |1 | 2|  |0 | 2|1 |1 |1 |0 |bool    | - true if first arg is greater than second
|le     |1 | 2|  |0 | 2|1 |1 |1 |0 |bool    | - true if first arg is less than or equal to second
|ge     |1 | 2|  |0 | 2|1 |1 |1 |0 |bool    | - true if first arg is greater than or equal to second
|is_null|1 | 1|  |0 | 1|1 |1 |1 |0 |bool    | - true if only arg is a null value
|nvl    |1 | 2|  |0 | 2|1 |1 |1 |0 |        | - returns arg 1 if it is not null, else arg 2
|switch |1 | 2| 2|1 |  |1 |1 |1 |0 |        | - a logical switch-case expr (like Oracle 'decode')
|like   |1 | 2| 1|0 | 4|1 |1 |1 |0 |bool    | - true if first arg contains second; args 3,4 are flags
|add    |1 | 2| 1|1 |  |1 |1 |1 |0 |numeric | - result of adding all args as numbers
|sub    |1 | 2| 1|1 |  |1 |1 |1 |0 |numeric | - result of subtracting all subsequent args from first
|mul    |1 | 2| 1|1 |  |1 |1 |1 |0 |numeric | - result of multiplying all arguments
|div    |1 | 2|  |0 | 2|1 |1 |1 |0 |numeric | - result of dividing first argument by second
|divi   |1 | 2|  |0 | 2|1 |1 |1 |0 |int     | - integral division of first arg by second
|mod    |1 | 2|  |0 | 2|1 |1 |1 |0 |int     | - modulus of integral division of first arg by second
|round  |1 | 1| 1|0 | 2|1 |1 |1 |0 |numeric | - rounds first arg to N dec places; N is second arg or 0
|exp    |1 | 2| 1|0 | 2|1 |1 |1 |0 |numeric | - raises first arg to the power of the second
|log    |1 | 2| 1|0 | 2|1 |1 |1 |0 |numeric | - logarithm of the first arg on the base of second
|min    |1 | 1| 1|1 |  |1 |1 |1 |0 |numeric | - minimum arg value of all arguments
|max    |1 | 1| 1|1 |  |1 |1 |1 |0 |numeric | - maximum arg value of all arguments
|avg    |1 | 1| 1|1 |  |1 |1 |1 |0 |numeric | - average value of all arguments
|sconcat|1 | 1| 1|1 |  |1 |1 |1 |0 |str     | - str concat of all arguments
|slength|1 | 1|  |0 | 1|1 |1 |1 |0 |int     | - length of input string in characters
|sindex |1 | 2| 1|0 | 3|1 |1 |1 |0 |int     | - pos in arg 1 of arg 2 if present, start at arg 3
|substr |1 | 2| 1|0 | 3|1 |1 |1 |0 |str     | - substr in arg 1 starting pos arg 2 of length arg 3
|srepeat|1 | 1| 1|0 | 2|1 |1 |1 |0 |str     | - str concat arg 1 to self repeated by arg 2 instances
|strim  |1 | 1|  |0 | 1|1 |1 |1 |0 |str     | - trims leading and trailing whitespace from arg 1
|spad   |1 | 2| 1|0 | 3|1 |1 |1 |0 |str     | - lengthens arg 1 to length of arg 2 using arg 3 or space
|spadl  |1 | 2| 1|0 | 3|1 |1 |1 |0 |str     | - like spad but add filler on left rather than right
|lc     |1 | 1|  |0 | 1|1 |1 |1 |0 |str     | - lowercases latin chars in a string
|uc     |1 | 1|  |0 | 1|1 |1 |1 |0 |str     | - uppercases latin chars in a string
|gcount |0 | 0|  |0 | 0|1 |0 |0 |1 |int     | - count of rows a view/cursor can see
|gmin   |1 | 1|  |0 | 1|1 |0 |0 |1 |numeric | - minimum of values in all records in one view col
|gmax   |1 | 1|  |0 | 1|1 |0 |0 |1 |numeric | - maximum of values in all records in one view col
|gsum   |1 | 1|  |0 | 1|1 |0 |0 |1 |numeric | - sum of values in all records in one view col
|gavg   |1 | 1|  |0 | 1|1 |0 |0 |1 |numeric | - average of values in all records in one view col
|gconcat|1 | 1|  |0 | 1|1 |0 |0 |1 |str     | - str concat of values in all records in one view col
|gevery |1 | 1|  |0 | 1|1 |0 |0 |1 |bool    | - is true when all rec values in one col are true
|gany   |1 | 1|  |0 | 1|1 |0 |0 |1 |bool    | - is true when at least one rec value in one col is true
|gsome  |1 | 1|  |0 | 1|1 |0 |0 |1 |bool    | - is true when some rec values are true
|crowid |0 | 0|  |0 | 0|1 |0 |0 |0 |str     | - unique database-generated row id of returned row (=rowid?)
|crownum|0 | 0|  |0 | 0|1 |0 |0 |0 |int     | - row number of returned row, before sorting (eg:Oracle)
|clevel |0 | 0|  |0 | 0|1 |0 |0 |0 |int     | - 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).

cct_block_type

This table enumerates the basic types that a blocks can be, since SQL::ObjectModel "blocks" 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 blocks as well.

All block 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 blocks of certain types. As such, blocks arranged in a hierarchy of parents and children, where a root block is one that is not contained in another. All block types can take arguments of some sort, but your choice of type can severely limit what kinds of arguments you can take. Only some block types, functions mainly, can return a value. Only some block types have names.

Column List:

code - str,req - name of the block type
is_unnamed - bool,req - true when block has no name; it is a scope inside a larger block
has_ret_val - bool,req - true when block is like a function and should return a value
is_package - bool,req - true when child blocks are publically visible in schema
is_trigger - bool,req - true when block is the root of a trigger on a table

Index List:

primary - unique - [code]

Record List:

|code|unn|ret|pkg|trg|
|----|---|---|---|---|
|pack|0  |0  |1  |0  | - an Oracle package; a namespace for related funcs and procs
|trig|0  |0  |0  |1  | - a trigger on a table
|proc|0  |0  |0  |0  | - a named stored procedure, plain or nested or in package
|func|0  |1  |0  |0  | - a named stored function, plain or nested or in package
|loop|1  |0  |0  |0  | - a loop/iterator context inside a block, such as a 'foreach'
|cond|1  |0  |0  |0  | - a conditional context inside a block, such as an 'if'

This list is probably missing some items.

cct_basic_stmt_type

This table enumerates the basic statement types that blocks can contain. Examples of statements are calls to stored procedures or some kinds of program logic control operations or value assignment operations.

Column List:

code - str,req - name of the basic statement type
is_sproc - bool,req - true when statement is a procedure call to a standard procedure
is_uproc - bool,req - true when statement is a procedure call to a user-defined procedure
is_assign - bool,req - true when statement involves assigning an expression value to a variable
is_logic - bool,req - true when stmt is logic control like if/else or return or other

Index List:

primary - unique - [code]

Record List:

|code |sp|up|as|lo|
|-----|--|--|--|--|
|sproc|1 |0 |0 |0 | - a call to a standard built-in procedure; one of cct_standard_proc
|uproc|0 |1 |0 |0 | - a call to a user defined stored procedure (block)
|assig|0 |0 |1 |0 | - an assignment of an expression value to a variable
|logic|0 |0 |0 |1 | - 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.

cct_standard_proc

This table enumerates a list of the standard built in procedures that can be called from blocks.

Column List:

code - str,req - name of the standard function
can_have_args - bool,req - true when function can have any arguments
req_min_args - int,req - minimum num of args function must have, whether unlimited or not
req_arg_mult - int - all args over minimum must be supplied in this multiple (default=1)
unlim_args - bool,req - true when function can take any number of arguments over minimum
req_max_args - int - count of maximum args function can have when not unlimited

Index List:

primary - unique - [code]

Record List:

|code   |ha|mn|ml|ul|mx|
|-------|--|--|--|--|--|
|assign |1 | 2|  |0 | 2| - an assignment to a variable of the result of an expression

line types: block,assignment,call,return,throw,declare-cursor,use-cursor

block types: plain,throw,try,catch,if,elseif,else,switch,case,otherwise,foreach,
for,while,until,map,grep,function,procedure,trigger,regexp

cct_command_type

This table enumerates the basic types of SQL::ObjectModel 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.

Column List:

code - str,req - name of the command type
can_rd_db - bool,req - true when command may open/close db connection or confirm its existence
can_wr_db - bool,req - true when command may create/delete/move/copy db (what about expl save?)
can_rd_user - bool,req - true when command may confirm presence/details of database users
can_wr_user - bool,req - true when command may create/remove users or alter privileges
can_rd_sch - bool,req - true when command may confirm presence/details of table/view/block
can_wr_sch - bool,req - true when command may create/alter/delete tables/views/blocks/etc
can_rd_rec - bool,req - true when command may fetch/count table records, by any means
can_wr_rec - bool,req - true when command may insert or update or delete table records

Index List:

primary - unique - [code]

Record List:

|code        |rd|wd|ru|wu|rs|ws|rr|wr|
|------------|--|--|--|--|--|--|--|--|
|db_list     |1 |0 |0 |0 |0 |0 |0 |0 | - fetches a list of auto-detectable database instances
|db_info     |1 |0 |0 |0 |0 |0 |0 |0 | - fetches some detailed info about a database instance
|db_verify   |1 |0 |0 |0 |0 |0 |0 |0 | - compares a provided db spec to an actual database
|db_open     |1 |0 |0 |0 |0 |0 |0 |0 | - opens a connection between app and a database instance
|db_close    |1 |0 |0 |0 |0 |0 |0 |0 | - closes a connection between app and a database instance
|db_ping     |1 |0 |0 |0 |0 |0 |0 |0 | - verifies that a db connection is still open
|db_create   |0 |1 |0 |0 |0 |0 |0 |0 | - creates a new database instance
|db_delete   |0 |1 |0 |0 |0 |0 |0 |0 | - deletes a database instance
|db_clone    |1 |1 |0 |0 |0 |0 |0 |0 | - makes a clone of a db instance in another storage loc
|db_move     |1 |1 |0 |0 |0 |0 |0 |0 | - moves a db inst from one storage location to another
|user_list   |0 |0 |1 |0 |0 |0 |0 |0 | - fetches a list of users in open db instance
|user_info   |0 |0 |1 |0 |0 |0 |0 |0 | - fetches some detailed info about a database user
|user_verify |0 |0 |1 |0 |0 |0 |0 |0 | - compares a provided user spec to an actual user
|user_create |0 |0 |0 |1 |0 |0 |0 |0 | - creates new db user one can auth db_open with (if privs)
|user_delete |0 |0 |0 |1 |0 |0 |0 |0 | - deletes db user (as well as all schema it owns, if applic)
|user_clone  |0 |0 |1 |1 |0 |0 |0 |0 | - creates a clone of an existing user with different name
|user_update |0 |0 |0 |1 |0 |0 |0 |0 | - alters some details for a database user
|user_grant  |0 |0 |0 |1 |0 |0 |0 |0 | - grants a db privilege to a db user
|user_revoke |0 |0 |0 |1 |0 |0 |0 |0 | - revokes a db privilege from a db user
|table_list  |0 |0 |0 |0 |1 |0 |0 |0 | - fetches a list of tables in schema of open db instance
|table_info  |0 |0 |0 |0 |1 |0 |0 |0 | - fetches some detailed info about a database instance
|table_verify|0 |0 |0 |0 |1 |0 |0 |0 | - compares a provided table spec to an actual table
|table_create|0 |0 |0 |0 |0 |1 |0 |0 | - creates a new table in schema of open db inst
|table_delete|0 |0 |0 |0 |0 |1 |0 |0 | - deletes an existing table in open db inst (if it can)
|table_clone |0 |0 |0 |0 |1 |1 |0 |0 | - creates a clone of an existing table with different name
|table_update|0 |0 |0 |0 |0 |1 |0 |0 | - alters schema for an existing table
|view_list   |0 |0 |0 |0 |1 |0 |0 |0 | - fetches a list of views in schema of open db instance
|view_info   |0 |0 |0 |0 |1 |0 |0 |0 | - fetches some detailed info about a database instance
|view_verify |0 |0 |0 |0 |1 |0 |0 |0 | - compares a provided view spec to an actual view
|view_create |0 |0 |0 |0 |0 |1 |0 |0 | - creates a new view in schema of open db inst
|view_delete |0 |0 |0 |0 |0 |1 |0 |0 | - deletes an existing view in open db inst (if it can)
|view_clone  |0 |0 |0 |0 |1 |1 |0 |0 | - creates a clone of an existing view with different name
|view_update |0 |0 |0 |0 |0 |1 |0 |0 | - alters schema for an existing view
|block_list  |0 |0 |0 |0 |1 |0 |0 |0 | - fetches a list of blocks/procs/funcs of open db instance
|block_info  |0 |0 |0 |0 |1 |0 |0 |0 | - fetches some detailed info about a database instance
|block_verify|0 |0 |0 |0 |1 |0 |0 |0 | - compares a provided block spec to an actual block
|block_create|0 |0 |0 |0 |0 |1 |0 |0 | - creates a new block in schema of open db inst
|block_delete|0 |0 |0 |0 |0 |1 |0 |0 | - deletes an existing block in open db inst (if it can)
|block_clone |0 |0 |0 |0 |1 |1 |0 |0 | - creates a clone of an existing block with different name
|block_update|0 |0 |0 |0 |0 |1 |0 |0 | - alters schema for an existing block
|rec_fetch   |0 |0 |0 |0 |0 |0 |1 |0 | - fetches records from a table or view of multiple tables
|rec_verify  |0 |0 |0 |0 |0 |0 |1 |0 | - compares a provided set of records to stored ones
|rec_insert  |0 |0 |0 |0 |0 |0 |0 |1 | - inserts new recs into a table or view of multiple tables
|rec_update  |0 |0 |0 |0 |0 |0 |0 |1 | - updates exist recs in a table or view of multiple tables
|rec_c_update|0 |0 |0 |0 |0 |0 |0 |1 | - updates exist recs with calculation rather than value list
|rec_delete  |0 |0 |0 |0 |0 |0 |0 |1 | - deletes exist recs from a table or view of multiple tables
|rec_replace |0 |0 |0 |0 |0 |0 |0 |1 | - either updates or inserts recs if they exist or not
|rec_clone   |0 |0 |0 |0 |0 |0 |0 |1 | - creates a clone of a record in same table/view (if possible)
|rec_lock    |0 |0 |0 |0 |0 |0 |1 |1 | - places a lock on recs for a consistant read plus update
|rec_unlock  |0 |0 |0 |0 |0 |0 |1 |1 | - releases an existing lock on recs
|tra_start   |0 |0 |0 |0 |0 |0 |0 |1 | - starts a new transactional context for later commands
|tra_commit  |0 |0 |0 |0 |0 |0 |0 |1 | - commits outstanding rec commands, releases locks
|tra_rollback|0 |0 |0 |0 |0 |0 |0 |1 | - rolls back outstanding rec commands, releases locks
|call_proc   |0 |0 |1 |1 |0 |0 |1 |1 | - calls a stored procedure to do some predefined task
|call_func   |0 |0 |1 |1 |0 |0 |1 |1 | - 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.

cct_result_type

This table enumerates the basic types of Results that SQL::ObjectModel can return from executing a Command against a database or other Result context.

Column List:

code - str,req - name of the command type
is_error - bool,req - true when result is an error condition
is_conn - bool,req - true when result is a database connection handle
is_trans - bool,req - true when result is a database transaction handle
is_curs - bool,req - true when result is a database record fetch cursor handle
is_rec - bool,req - true when result is a database row returned by a cursor
is_var - bool,req - true when result is a variable bound to a db command arg or large result
is_lit - bool,req - true when result is a literal value (may be multidimensional)

Index List:

primary - unique - [code]

Record List:

|code |er|co|tr|cu|re|va|li|
|-----|--|--|--|--|--|--|--|
|error|1 |0 |0 |0 |0 |0 |0 | - an error message
|conn |0 |1 |0 |0 |0 |0 |0 | - a database connection handle
|trans|0 |0 |1 |0 |0 |0 |0 | - a database transaction handle
|curs |0 |0 |0 |1 |0 |0 |0 | - a database record fetch cursor handle
|rec  |0 |0 |0 |0 |1 |0 |0 | - a database row returned by a cursor
|var  |0 |0 |0 |0 |0 |1 |0 | - a variable bound to a db command arg or large result
|lit  |0 |0 |0 |0 |0 |0 |1 | - a literal value (may be multidimensional)

DATA TYPES

This table is for storing "user" definitions of data types. Each of these would correspond to a basic type that is recognized in code by SQL::ObjectModel, and allows the users to customize details such as the type names or in some cases their scale or precision or certain other constraints.

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.

Because all of the records in these tables are user defined, the provided record list is simply some example or "synopsis" data; none of it corresponds to anything in the SQL::ObjectModel code.

data_type

This table describes custom user-defined data types, which can be used in table column definitions, view column definitions, and block variables.

Column List:

name - str,req - name of the user customized data type
base_type - str,req - name of the basic data type this is customized from
size - int - maximum size in bytes data can be, in bytes for binary and nums, in chars for strs
store_fixed - bool - when true then try to reserve max size on disk even if not all used
str_encoding - str - which character encoding to use when data type is a string
str_trim_white - bool - when true then trim lead/trailing whitespace on save into field/var
str_latin_case - str - when str has latin chars do we upper/lowercase them on save
str_pad_char - str - when store_fixed is true then pad actual value with this to max size on save
str_trim_pad - bool - when store_fixed is true then trim padding on read from field/var
int_unsigned - bool - when basic_type is 'int', true means only >= 0 may be stored
dec_precision - int - when basic_type is 'dec', num digits of fractional precision we allowed
dt_calendar - str - when basic_type is 'datetime', what calendar the date and time is in

Undecided Possible Extra Columns:

min_num_val - int - when type is numerical, what the allowed minimum value is
max_num_val - int - when type is numerical, what the allowed maximum value is
min_date_val - datetime - when type is datetime, what the allowed earliest value is
max_date_val - datetime - when type is datetime, what the allowed latest value is

Index List:

primary - unique - [name]
fk_base_type - foreign - [base_type]->[cct_basic_data_type]:[code]
fk_str_encoding - foreign - [str_encoding]->[cct_str_enc]:[code]
fk_str_latin_case - foreign - [str_latin_case]->[cct_str_latin_case]:[code]
fk_dt_calendar - foreign - [dt_calendar]->[cct_datetime_calendar]:[code]

Example Record List:

|name    |base    | size|fx|enc|tw|la|pc|tp|u|p|cal|
|--------|--------|-----|--|---|--|--|--|--|-|-|---|
|bin1k   |bin     | 1000| 0|   |  |  |  |  | | |   | - a hunk of binary data of maximum 1000 bytes
|bin32k  |bin     |32000| 0|   |  |  |  |  | | |   | - a hunk of binary data of maximum 32000 bytes
|str4    |str     |    4| 1|asc| 1|uc|20|1 | | |   | - a fix-w 4 char ascii str, uppercased, spc-pad
|str10   |str     |   10| 1|asc| 1|pr|20|1 | | |   | - a fix-w 10 char ascii str, case-preserve, spc-pad
|str30   |str     |   30| 0|asc| 1|pr|  |  | | |   | - a var-w 30 char ascii str, case-preserving
|str2k   |str     | 2000| 0|u16| 0|pr|  |  | | |   | - a var-w 2000 char unicode str, case-preserving
|boolean |bool    |     |  |   |  |  |  |  | | |   | - a boolean; only 2 values of false or true
|byte    |int     |    1|  |   |  |  |  |  |0| |   | - a precise int btwn           -128 and           +127
|short   |int     |    2|  |   |  |  |  |  |0| |   | - a precise int btwn        -32,768 and        +32,767
|int     |int     |    4|  |   |  |  |  |  |0| |   | - a precise int btwn -2,147,483,648 and +2,147,483,647
|long    |int     |    8|  |   |  |  |  |  |0| |   | - a precise int btwn        -(2^63) and      +(2^63)-1
|ubyte   |int     |    1|  |   |  |  |  |  |1| |   | - a precise integer between 0 and           +255
|ushort  |int     |    2|  |   |  |  |  |  |1| |   | - a precise integer between 0 and        +65,535
|uint    |int     |    4|  |   |  |  |  |  |1| |   | - a precise integer between 0 and +4,294,967,295
|ulong   |int     |    8|  |   |  |  |  |  |1| |   | - a precise integer between 0 and      +(2^64)-1
|float   |float   |    4|  |   |  |  |  |  | | |   | - a 32-bit imprecise fractional number
|double  |float   |    8|  |   |  |  |  |  | | |   | - a 64-bit imprecise fractional number
|dec10p2 |dec     |   10|  |   |  |  |  |  | |2|   | - a precise 10-digit num with hundredths precision
|dec255  |dec     |  255|  |   |  |  |  |  | | |   | - a precise 255-digit num with arbitrary precision
|datetime|datetime|     |  |   |  |  |  |  | | |abs| - a date or time or both, non-calendar specific
|dtchines|datetime|     |  |   |  |  |  |  | | |chi| - a date or time or both, in the Chinese calendar

DATABASES AND NAME SPACES

SQL::ObjectModel data dictionaries can be and are used to describe both things that are stored in a database schema and things which are not stored there, but are used in connection with database schema by applications. Items which are stored in a database can be accessed by other items stored in a database, like named stored procedures or packages, and they can not directly bind to application variables. Items not stored in a database can be bound and customized to application variables, such as normal 'select' SQL. There are two root tables in SQL::ObjectModel data dictioaries, into which almost everything else is grouped. 'database' groups what can be stored in a database, and 'application' groups what can not be. The current version of DataDictionary.pod does not explicitely describe 'application' or what is under it.

database

Each record in this table corresponds to a distinct "database" or "data source" or "database instance" or "server" or "host" which contains everything else, both schema and users. This is what an application "connects to" when it will use a database. Usually this will contain just one record, if you are only using one database. The rationale for being able to describe multiple databases in the same data dictionary is, for example, when one database wants to talk directly to another.

This documentation is not yet complete, or referenced. It may also be dropped if 'namespace' fills its role well enough.

namespace

Each record in this table corresponds to a "name space" into which your schema objects can be collected. Some database products support multiple name spaces (in Oracle each one is called a "Schema" and is associated with a database user), and other databases do not support multiple name spaces (most?). In many cases this table only ever contains one record, where all schema objects share the same (or only) namespace.

SQL::ObjectModel will let you define your schema objects (tables, views, procedures, etc) as if multiple namespaces were supported natively, and if you are deploying to a database that doesn't, then SQL::ObjectModel can put a common name prefix in front of all the schema objects (the 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.

Because all of the records in these tables are user defined, the provided record list is simply some example or "synopsis" data; none of it corresponds to anything in the SQL::ObjectModel code.

Column List:

namespace_id - int,req - unique identifier for this namespace
level_1_name - str - corresponds to Oracle "Schema"
level_2_name - str - corresponds to Oracle "Data Source" (for one db instance querying another)

Index List:

primary - unique - [namespace_id]

Example Record List:

|id|lev1|lev2|
|--|----|----|
| 1|    |    | - a default namespace, usually just used if it is the only namespace
| 2|gene|    | - store all the normal app data that constitutes a genealogy database
| 3|dd  |    | - store a data dictionary describing an application for editing gene data
| 4|gene|dist| - in case we want to access genealogy data collected by someone else

Note: it may not be practical in real life to have databases directly querying each other, but for when you do want to, some database engines do support it.

TABLES

These tables are for storing user definitions of database tables, which are what actually store their data. Each table has one or more columns and one or more indexes or constraints.

All tables are uniquely identified here by an "id", which is different from the table name that is stored in the database schema; the actual name in the schema may change depending on what the implementing database is.

Because all of the records in these tables are user defined, the provided record list is simply some example or "synopsis" data; none of it corresponds to anything in the SQL::ObjectModel code.

table

Each record in this table uniquely corresponds to one database table, and has some core details.

Column List:

table_id - int,req - unique identifier for this table
namespace_id - int,req - the namespace that this table is in
table_name - str,req - the name this table is identified by
table_order - int,req - to explicitely define what order tables are declared in
public_syn - str - a name for the table outside of any namespace; Oracle public synonym
storage_file - str - where applicable, explicitely say where to store table (aka, "tablespace")

Undecided Possible Extra Column:

is_temporary - bool - does table even go in the schema, or just a connection-specific place

Index List:

primary - unique - [table_id]
ak_name - unique - [namespace_id,table_name]
ak_order - unique - [namespace_id,table_order]
ak_public - unique - [public_syn]
fk_namespace - foreign - [namespace_id]->[namespace]:[namespace_id]

Example Record List:

|id|ns|name        |ord|public_syn  |file  |
|--|--|------------|---|------------|------|
| 1| 1|user_auth   |  1|user_auth   |user  | - authentication related details for application users
| 2| 1|user_profile|  2|user_profile|user  | - public personal details for application users
| 3| 1|user_pref   |  3|user_pref   |user  | - application preference values for users
| 4| 1|person      |  4|person      |person| - core data node in a genealogical program

table_col

Each record in this table uniquely corresponds to a single table column, and has details that are specific to that column.

Column List:

table_id - int,req - unique identifier for this table
col_name - str,req - unique identifier for column within table
col_order - int,req - what order columns are declared in (and by default returned in)
data_type - str,req - the user-customized data type for this column
required_val - bool,req - true when col value is required (not null), false when not (null)
default_val - str - an optional default value for the column, or starting val for auto_inc
auto_inc - bool - when true, col val defaults from a special auto-incrementing sequence

Undecided Possible Extra Columns:

default_seq - int - when set, the column defaults to a value from this user defined sequence

Index List:

primary - unique - [table_id,col_name]
ak_order - unique - [table_id,col_order]
fk_table - foreign - [table_id]->[table]:[table_id]
fk_data_type - foreign - [data_type]->[data_type]:[name]

Undecided Possible Extra Indexes:

fk_sequence - foreign - [default_seq]->[sequence]:[sequence_id]

Example Record List:

|tbl|col_name     |ord|type    |req|def|inc|
|---|-------------|---|--------|---|---|---|
|  1|user_id      |  1|int     |1  |1  |1  | - unique internal id for user, not changeable
|  1|login_name   |  2|str20   |1  |   |   | - who user authenticates into app as
|  1|login_pass   |  3|str20   |1  |   |   | - also used with authentication
|  1|private_name |  4|str100  |1  |   |   | - real name that app service provider can know
|  1|private_email|  5|str100  |1  |   |   | - email that service provider can contact user with
|  1|may_login    |  6|boolean |1  |   |   | - true when user may use app at all
|  1|max_sessions |  7|byte    |1  |3  |   | - max concurrent app logins user may have
|  2|user_id      |  1|int     |1  |   |   | - unique internal id for user, not changeable
|  2|public_name  |  2|str250  |1  |   |   | - public name or pen name or public alias
|  2|public_email |  3|str250  |0  |   |   | - email address the public may use for contact
|  2|web_url      |  4|str250  |0  |   |   | - personal/business website address of user
|  2|contact_net  |  5|str250  |0  |   |   | - info like ICQ number or other internet handles
|  2|contact_phy  |  6|str250  |0  |   |   | - physical address or city or telephone number
|  2|bio          |  7|str250  |0  |   |   | - brief description of the person
|  2|plan         |  8|str250  |0  |   |   | - what the person plans to do next
|  2|comments     |  9|str250  |0  |   |   | - other comments
|  3|user_id      |  1|int     |1  |   |   | - unique internal id for user, not changeable
|  3|pref_name    |  2|entitynm|1  |   |   | - app-specific preference name
|  3|pref_value   |  3|generic |0  |   |   | - preference value for this user
|  4|person_id    |  1|int     |1  |1  |1  | - unique actual id for person, not changeable
|  4|alternate_id |  2|str20   |0  |   |   | - user's own way of ident people, if wanted
|  4|name         |  3|str100  |1  |   |   | - actual name of the person they are called by
|  4|sex          |  4|str1    |0  |   |   | - sex of the person, if known
|  4|father_id    |  5|int     |0  |   |   | - person_id of this person's father, if known
|  4|mother_id    |  6|int     |0  |   |   | - person_id of this person's mother, if known

table_ind

Each record in this table uniquely corresponds to 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 order) unique index where all of the index columns are required. If no table indexes qualify then the table has no primary key.

Note that this data dictionary design assumes that database products will only enforce foreign key constraints if the columns in question are populated. That is, a constrained column may be empty even if the foreign column never is.

Column List:

table_id - int,req - unique identifier for this table
ind_name - str,req - unique identifier for index within table
ind_order - int,req - what order indexes are declared in
ind_type - str,req - what type of index this is (eg: unique, foreign)
f_table_id - int - when the index type is a foreign key, this is the constraining table

Index List:

primary - unique - [table_id,ind_name]
ak_order - unique - [table_id,ind_order]
fk_table - foreign - [table_id]->[table]:[table_id]
fk_ind_type - foreign - [ind_type]->[cct_index_type]:[code]
fk_f_table - foreign - [f_table_id]->[table]:[table_id]

Example Record List:

|tbl|ind_name        |ord|type    |ftbl|
|---|----------------|---|--------|----|
|  1|primary         |  1|unique  |    | - ensure all [user_id] are unique
|  1|ak_login_name   |  2|unique  |    | - ensure all [login_name] are unique
|  1|ak_private_email|  3|unique  |    | - ensure all [private_email] are unique
|  2|primary         |  1|unique  |    | - ensure all [user_id] are unique
|  2|ak_public_name  |  2|unique  |    | - ensure all [public_name] are unique
|  2|fk_user         |  3|foreign |   1| - ensure user_profile matches user_auth
|  3|primary         |  1|unique  |    | - ensure all [user_id,pref_name] are unique
|  3|fk_user         |  2|foreign |   1| - ensure user_pref matches user_auth
|  4|primary         |  1|unique  |    | - ensure all [person_id] are unique
|  4|ak_alternate_id |  2|unique  |    | - ensure all [alternate_id] are unique
|  4|fk_father       |  3|foreign |   4| - link child to father
|  4|fk_mother       |  4|foreign |   4| - link child to mother

table_ind_col

This is an intersection table between table_ind and table_col; it says which table columns are involved in each table index.

Column List:

table_id - int,req - unique identifier for this table
ind_name - str,req - unique identifier for index within table
col_name - str,req - unique identifier for column within table
table_ind_col_order - int,req - order of the columns within the index
f_table_id - int - when the index type is a foreign key, this is the constraining table
f_col_name - str - when the index type is a foreign key, this is the constraining column

Index List:

primary - unique - [table_id,ind_name,col_name]
ak_order - unique - [table_id,ind_name,table_ind_col_order]
fk_table_ind - foreign - [table_id,ind_name]->[table_ind]:[table_id,ind_name]
fk_table_col - foreign - [table_id,col_name]->[table_col]:[table_id,col_name]
fk_f_table_col - foreign - [f_table_id,f_col_name]->[table_col]:[table_id,col_name]

Example Record List:

|tbl|ind_name        |col_name     |ord|ftbl|fcol     |
|---|----------------|-------------|---|----|---------|
|  1|primary         |user_id      |  1|    |         | - ensure all [user_id] are unique
|  1|ak_login_name   |login_name   |  1|    |         | - ensure all [login_name] are unique
|  1|ak_private_email|private_email|  1|    |         | - ensure all [private_email] are unique
|  2|primary         |user_id      |  1|    |         | - ensure all [user_id] are unique
|  2|ak_public_name  |public_name  |  1|    |         | - ensure all [login_name] are unique
|  2|fk_user         |user_id      |  1|   1|user_id  | - ensure user_profile matches user_auth
|  3|primary         |user_id      |  1|    |         | - ensure all [user_id,pref_name] are unique
|  3|primary         |pref_name    |  2|    |         | - ensure all [user_id,pref_name] are unique
|  3|fk_user         |user_id      |  1|   1|user_id  | - ensure user_pref matches user_auth
|  4|primary         |person_id    |  1|    |         | - ensure all [person_id] are unique
|  4|ak_alternate_id |alternate_id |  1|    |         | - ensure all [alternate_id] are unique
|  4|fk_father       |father_id    |  1|   4|person_id| - link child to father
|  4|fk_mother       |mother_id    |  1|   4|person_id| - link child to mother

VIEWS

These tables are for storing user definitions of database views, which 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. SQL::ObjectModel "views" are used for more types of things than SQL views are; see the cct_view_type table description above for details.

All views are uniquely identified here by an "id", which is different from the view name that is stored in the database schema (where applicable); the actual name in the schema may change depending on what the implementing database is.

Because all of the records in these tables are user defined, the provided record list is simply some example or "synopsis" data; none of it corresponds to anything in the SQL::ObjectModel code.

view

Each record in this table uniquely corresponds to one database view, and has some core details.

Views are often fully defined using a set of multiple records in various view/_* tables, which makes them fully customizable. However, a short-cut is available which allows a view to be defined entirely with just one record in "view", through the optional match_table field. When this is populated with a table id, 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::ObjectModel 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.

Column List:

view_id - int,req - unique identifier for this view
view_type - str,req - what kind of view this is; eg: object, caller, cursor, inside
namespace_id - int - the namespace that this view is in, if any
view_name - str - the name this view is identified by, if any
view_order - int - to explicitely define what order views are declared in, if named
public_syn - str - a name for the view outside of any namespace, if named; Oracle public synonym
may_write - bool,req - true when we should at least try allowing updates through the view
match_table - int - when set, the view matches exactly the interface for one table

Undecided Possible Extra Columns:

page_rows - bool,req - true when we should page the resulting rows so only some are returned
maxr_lit_val - int - if paging, this literal value says maximum rows to be returned
maxr_var_name - str - if paging, get max rows from the caller bind variable (?) named here
skipr_lit_val - int - if paging, this literal value says rows to skip before returning any
skipr_var_name - str - if paging, get skip rows from the caller bind variable (?) named here

Index List:

primary - unique - [view_id]
ak_name - unique - [namespace_id,view_name]
ak_order - unique - [namespace_id,view_order]
ak_public - unique - [public_syn]
fk_view_type - foreign - [view_type]->[cct_view_type]:[code]
fk_namespace - foreign - [namespace_id]->[namespace]:[namespace_id]
fk_match_table - foreign - [match_table]->[table]:[table_id]

Example Record List:

|id|type  |ns|name|ord|pub|wr|match|
|--|------|--|----|---|---|--|-----|
| 1|caller|  |    |   |   |1 |     | - user - joins the "user_auth" and "user_profile" tables
| 2|caller|  |    |   |   |0 |     | - person_with_parents - joins the "person" table with itself twice
| 3|caller|  |    |   |   |0 |     | - user_theme - shows how many users use each theme pref value
| 4|caller|  |    |   |   |1 |    4| - person - simply the "person" table

view_col

Each record in this table uniquely corresponds to 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.

Column List:

view_id - int,req - unique identifier for this view
col_name - str,req - unique identifier for column within view
col_order - int,req - what order columns are declared in (and by default returned in)
data_type - str,req - the user-customized data type for this column
sort_priority - int - if valued, priority for final result rows (after rs merges) to be sorted by this column

Undecided Possible Extra Columns:

is_writeable - bool - true when enough info is known to write changes to this column
is_required - bool - true when corresponding value must always be populated
is_key - bool - true when this column is a part of unique id for source table or for view row

Index List:

primary - unique - [view_id,col_name]
ak_order - unique - [view_id,col_order]
fk_view - foreign - [view_id]->[view]:[view_id]
fk_data_type - foreign - [data_type]->[data_type]:[name]

Example Record List:

|vw|col_name     |ord|type   |so|
|--|-------------|---|-------|--|
| 1|user_id      |  1|int    |  | - unique internal id for user, not changeable
| 1|login_name   |  2|str20  | 1| - who user authenticates into app as
| 1|login_pass   |  3|str20  |  | - also used with authentication
| 1|private_name |  4|str100 |  | - real name that app service provider can know
| 1|private_email|  5|str100 |  | - email that service provider can contact user with
| 1|may_login    |  6|boolean|  | - true when user may use app at all
| 1|max_sessions |  7|byte   |  | - max concurrent app logins user may have
| 1|public_name  |  8|str250 |  | - public name or pen name or public alias
| 1|public_email |  9|str250 |  | - email address the public may use for contact
| 1|web_url      | 10|str250 |  | - personal/business website address of user
| 1|contact_net  | 11|str250 |  | - info like ICQ number or other internet handles
| 1|contact_phy  | 12|str250 |  | - physical address or city or telephone number
| 1|bio          | 13|str250 |  | - brief description of the person
| 1|plan         | 14|str250 |  | - what the person plans to do next
| 1|comments     | 15|str250 |  | - other comments
| 2|self_id      |  1|int    |  | - unique actual id for person, not changeable
| 2|self_name    |  2|str100 | 1| - actual name of the person they are called by
| 2|father_id    |  3|int    |  | - person_id of this person's father, if known
| 2|father_name  |  4|str100 | 2| - actual name of the person's father
| 2|mother_id    |  5|int    |  | - person_id of this person's mother, if known
| 2|mother_name  |  6|str100 | 3| - actual name of the person's mother
| 3|theme_name   |  2|generic| 2| - name of a user theme
| 3|theme_count  |  3|int    | 1| - count of users that use this theme

view_rowset

Each record in this table uniquely corresponds to a rowset definition which is part of a view definition. Normally there is only one rowset in a view, but if the view contains operations like "union" or "intersect" then there would be more than one rowset which are, for example, being unioned to produce the final result set. Each rowset in a view has its own view definition, except that all rowsets in a view share the same view_col records, and possibly view_order.

The view_rowset records are arranged into a hierarchy whose root is the view, so that the merging operations (eg: the unions) are done in the correct order.

Column List:

view_id - int,req - unique identifier for this view
rowset_num - int,req - unique identifier for rowset within view
p_rowset_num - int - parent rowset of this one, if any; empty if this is the root rowset
p_rowset_order - int,req - order of this rowset among its siblings; 1 if no siblings
c_merge_type - str - how to merge any child rowsets of this one together, if any

Index List:

primary - unique - [view_id,rowset_num]
ak_order - unique - [view_id,p_rowset_num,p_rowset_order]
fk_view - foreign - [view_id]->[view]:[view_id]
fk_merge_type - foreign - [c_merge_type]->[cct_rs_merge_type]:[code]

Example Record List:

|vw|rn|prn|pro|mtp|
|--|--|---|---|---|
| 1| 1|   |  1|   | - user
| 2| 1|   |  1|   | - person_with_parents
| 3| 1|   |  1|   | - user_theme

view_src

Each record in this table uniquely corresponds to a source table or view that is used by this view (one copy per view rowset if applicable). Specifically it applies only to the "from" clause in the generated SQL, where applicable.

Column List:

view_id - int,req - unique identifier for this view
rowset_num - int,req - unique identifier for rowset within view
src_name - str,req - unique "alias" identifier for source within view rowset
src_order - int,req - what order sources are declared in (and by default joined)
match_table - int - if set, the source is a table (and match_view must not be set)
match_view - int - if set, the source is another view (and match_table must not be set)

Index List:

primary - unique - [view_id,rowset_num,src_name]
ak_order - unique - [view_id,rowset_num,src_order]
fk_rowset - foreign - [view_id,rowset_num]->[view_rowset]:[view_id,rowset_num]
fk_match_table - foreign - [match_table]->[table]:[table_id]
fk_match_view - foreign - [match_view]->[view]:[view_id]

Example Record List:

|vw|rn|name        |ord|mtb|mvw|
|--|--|------------|---|---|---|
| 1| 1|user_auth   |  1|  1|   | - match "user_auth" table
| 1| 1|user_profile|  2|  2|   | - match "user_profile" table
| 2| 1|self        |  1|  4|   | - match "person" table
| 2| 1|father      |  2|  4|   | - match "person" table
| 2| 1|mother      |  3|  4|   | - match "person" table
| 3| 1|user_pref   |  1|  3|   | - match "user_pref" table

view_src_col

Each record in this table uniquely represents a column in a source table or view that is used by this view, as specified in the view_src table.

Note that the "Virtual Index List" describes a constraint that is complicated enough such that it can only be enforced with a trigger or some other means that supports multiple levels of indirection (constraints with subqueries?).

Column List:

view_id - int,req - unique identifier for this view
rowset_num - int,req - unique identifier for rowset within view
src_name - str,req - which view source this column is associated with
src_col - str,req - which column from the source table or view is represented

Index List:

primary - unique - [view_id,rowset_num,src_name,src_col]
fk_view_src - foreign - [view_id,rowset_num,src_name]->[view_src]:[view_id,rowset_num,src_name]

Virtual Index List:

given $src_table = fk_view_src:[match_table]
given $src_view = fk_view_src:[match_view]
if $src_table is set then
	fk_src_col - foreign - [$src_table,src_col]->[table]:[table_id,col_name]
end if
if $src_view is set then
	fk_src_col - foreign - [$src_view,src_col]->[view]:[view_id,col_name]
end if

Example Record List:

|vw|rn|src_name    |src_col      |
|--|--|------------|-------------|
| 1| 1|user_auth   |user_id      |
| 1| 1|user_auth   |login_name   |
| 1| 1|user_auth   |login_pass   |
| 1| 1|user_auth   |private_name |
| 1| 1|user_auth   |private_email|
| 1| 1|user_auth   |may_login    |
| 1| 1|user_auth   |max_sessions |
| 1| 1|user_profile|user_id      |
| 1| 1|user_profile|public_name  |
| 1| 1|user_profile|public_email |
| 1| 1|user_profile|web_url      |
| 1| 1|user_profile|contact_net  |
| 1| 1|user_profile|contact_phy  |
| 1| 1|user_profile|bio          |
| 1| 1|user_profile|plan         |
| 1| 1|user_profile|comments     |
| 2| 1|self        |person_id    |
| 2| 1|self        |name         |
| 2| 1|father      |person_id    |
| 2| 1|father      |name         |
| 2| 1|mother      |person_id    |
| 2| 1|mother      |name         |
| 3| 1|user_pref   |pref_name    |
| 3| 1|user_pref   |pref_value   |

view_join

Each record in this table uniquely corresponds to a join operation being done between two sources (each a table or a view), both in the same view rowset. 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 (?). The fact that there is no "right" join in the suggested data dictionary should help prevent the same source from being outer joined to more than one other source, which in many databases is illegal.

Column List:

view_id - int,req - unique identifier for this view
rowset_num - int,req - unique identifier for rowset within view
lhs_src_name - str,req - which view source is on the left side of the join
rhs_src_name - str,req - which view source is on the right side of the join
join_type - str,req - what kind of table/view join is being done

Index List:

primary - unique - [view_id,rowset_num,lhs_src_name,rhs_src_name]
ak_limit_one - unique - [view_id,rowset_num,rhs_src_name]
fk_lhs_src - foreign - [view_id,rowset_num,lhs_src_name]->[view_src]:[view_id,rowset_num,src_name]
fk_rhs_src - foreign - [view_id,rowset_num,rhs_src_name]->[view_src]:[view_id,rowset_num,src_name]
fk_join_type - foreign - [join_type]->[cct_rs_join_type]:[code]

Example Record List:

|vw|rn|lhs_src  |rhs_src     |type |
|--|--|---------|------------|-----|
| 1| 1|user_auth|user_profile|left | - "user_auth" left join "user_profile"
| 2| 1|self     |father      |left | - "self" left join "father"
| 2| 1|self     |mother      |left | - "self" left join "mother"

view_join_col

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 record in this table uniquely corresponds to part of a join operation being done between two sources (each a table or a view), both in the same view rowset. There is one record for each pair of source columns (one from each source) that are being matched.

Column List:

view_id - int,req - unique identifier for this view
rowset_num - int,req - unique identifier for rowset within view
lhs_src_name - str,req - which view source is on the left side of the join
rhs_src_name - str,req - which view source is on the right side of the join
lhs_src_col - str,req - which column from the left source is being matched on
rhs_src_col - str,req - which column from the right source is being matched on

Index List:

primary - unique - [view_id,rowset_num,lhs_src_name,rhs_src_name,lhs_src_col]
fk_view_join - foreign - [view_id,rowset_num,lhs_src_name,rhs_src_name]->[view_join]:[view_id,rowset_num,lhs_src_name,rhs_src_name]
fk_lhs_src_col - foreign - [view_id,rowset_num,lhs_src_name,rhs_src_col]->[view_src_col]:[view_id,rowset_num,src_name,src_col]
fk_rhs_src_col - foreign - [view_id,rowset_num,rhs_src_name,rhs_src_col]->[view_src_col]:[view_id,rowset_num,src_name,src_col]

Example Record List:

|vw|rn|lhs_src  |rhs_src     |lhs_col  |rhs_col  |
|--|--|---------|------------|---------|---------|
| 1| 1|user_auth|user_profile|user_id  |user_id  | - on "user_profile"."user_id" = "user_auth"."user_id"
| 2| 1|self     |father      |father_id|person_id| - on "father"."person_id" = "self"."father_id"
| 2| 1|self     |mother      |mother_id|person_id| - on "mother"."person_id" = "self"."mother_id"

view_hierarchy

Each record in this table can define an n-level hierarchical self-join-union to be part of a view rowset definition (similar to Oracle's start-with-connect-by feature). Only one is allowed per view rowset, and it is probably evaluated between the SQL "where" and "group by", where applicable.

Note: Apparently SQL99 allows recursive queries with "WITH clause" which is a standard way of doing the same thing as Oracle's CONNECT BY.

Column List:

view_id - int,req - unique identifier for this view
rowset_num - int,req - unique identifier for rowset within view
src_name - str,req - which view source is being hierarchically joined to itself
start_src_col - str,req - which source column is checked against to determine the root of the hierarchy
start_lit_val - str - if start_src_col is being compared to a literal, then store the literal value here
start_var_name - str - if start_src_col is being compared to a variable, then put the caller bind variable (?) name here
conn_src_col - str,req - which source column contains a record's own identifier against which children match
p_conn_src_col - str,req - which source column contains the value of a parent identifier

Index List:

primary - unique - [view_id,rowset_num]
fk_start_src_col - foreign - [view_id,rowset_num,src_name,start_src_col]->[view_src_col]:[view_id,rowset_num,src_name,src_col]
fk_conn_src_col - foreign - [view_id,rowset_num,src_name,conn_src_col]->[view_src_col]:[view_id,rowset_num,src_name,src_col]
fk_p_conn_src_col - foreign - [view_id,rowset_num,src_name,p_conn_src_col]->[view_src_col]:[view_id,rowset_num,src_name,src_col]

Note that the current structure only allows queries like "... start with [start_src_col] = [start_lit_val/src_name] connect by prior [conn_src_col] = [p_conn_src_col] ...", but this probably isn't adequate for all uses, and will be improved. We may eliminate this table in favor of view_part_def.

view_col_def

Each record in this table uniquely corresponds to a sub-expression that is part of an interface/output column definition in a view; this is an intersection table between view_col and view_rowset; there needs to be one full expression for every view_col/view_rowset record 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 data_type specified in the view_col record.

Column List:

view_id - int,req - unique identifier for this view
col_name - str,req - unique identifier for interface/output column within view
rowset_num - int,req - unique identifier for rowset within view
expr_num - int,req - unique identifier for sub-expression within view column def
p_expr_num - int - parent expression of this one, if any; empty if this is the root expression
p_expr_order - int,req - order of this expression among its siblings; 1 if no siblings
expr_type - str,req - what type of expression this is; eg: literal, variable, column, view, function
lit_val - str - if expr_type is 'lit', then store the literal value here
var_name - str - if expr_type is 'var', then put the caller bind variable (?) name here
src_name - str - if expr_type is 'col', then put the column source/alias name here
src_col - str - if expr_type is 'col', then put the source table/view column name here
f_view_id - int - if expr_type is 'view', then put the view/subquery id here
sfunc_code - str - if expr_type is 'sfunc', then put the standard built-in function name/code here
ufunc_id - int - if expr_type is 'ufunc', then put the user-defined stored function (block) id here

Index List:

primary - unique - [view_id,col_name,rowset_num,expr_num]
ak_order - unique - [view_id,col_name,rowset_num,p_expr_num,p_expr_order]
fk_view_col - foreign - [view_id,col_name]->[view_col]:[view_id,col_name]
fk_rowset - foreign - [view_id,rowset_num]->[view_rowset]:[view_id,rowset_num]
fk_expr_type - foreign - [c_expr_type]->[cct_basic_expr_type]:[code]
fk_src_col - foreign - [view_id,rowset_num,src_name,src_col]->[view_src_col]:[view_id,rowset_num,src_name,col_name]
fk_f_view - foreign - [f_view_id]->[view]:[view_id]
fk_sfunc_code - foreign - [sfunc_code]->[cct_standard_func]:[code]
fk_ufunc - foreign - [ufunc_id]->[block]:[block_id]

Example Record List:

|vw|col_name     |rn|en|pen|peo|type |lit|var|src_name    |src_col      |fvw|sf    |uf|
|--|-------------|--|--|---|---|-----|---|---|------------|-------------|---|------|--|
| 1|user_id      | 1| 1|   |  1|col  |   |   |user_auth   |user_id      |   |      |  |
| 1|login_name   | 1| 1|   |  1|col  |   |   |user_auth   |login_name   |   |      |  |
| 1|login_pass   | 1| 1|   |  1|col  |   |   |user_auth   |login_pass   |   |      |  |
| 1|private_name | 1| 1|   |  1|col  |   |   |user_auth   |private_name |   |      |  |
| 1|private_email| 1| 1|   |  1|col  |   |   |user_auth   |private_email|   |      |  |
| 1|may_login    | 1| 1|   |  1|col  |   |   |user_auth   |may_login    |   |      |  |
| 1|max_sessions | 1| 1|   |  1|col  |   |   |user_auth   |max_sessions |   |      |  |
| 1|public_name  | 1| 1|   |  1|col  |   |   |user_profile|public_name  |   |      |  |
| 1|public_email | 1| 1|   |  1|col  |   |   |user_profile|public_email |   |      |  |
| 1|web_url      | 1| 1|   |  1|col  |   |   |user_profile|web_url      |   |      |  |
| 1|contact_net  | 1| 1|   |  1|col  |   |   |user_profile|contact_net  |   |      |  |
| 1|contact_phy  | 1| 1|   |  1|col  |   |   |user_profile|contact_phy  |   |      |  |
| 1|bio          | 1| 1|   |  1|col  |   |   |user_profile|bio          |   |      |  |
| 1|plan         | 1| 1|   |  1|col  |   |   |user_profile|plan         |   |      |  |
| 1|comments     | 1| 1|   |  1|col  |   |   |user_profile|comments     |   |      |  |
| 2|self_id      | 1| 1|   |  1|col  |   |   |self        |person_id    |   |      |  |
| 2|self_name    | 1| 1|   |  1|col  |   |   |self        |name         |   |      |  |
| 2|father_id    | 1| 1|   |  1|col  |   |   |father      |person_id    |   |      |  |
| 2|father_name  | 1| 1|   |  1|col  |   |   |father      |name         |   |      |  |
| 2|mother_id    | 1| 1|   |  1|col  |   |   |mother      |person_id    |   |      |  |
| 2|mother_name  | 1| 1|   |  1|col  |   |   |mother      |name         |   |      |  |
| 3|theme_name   | 1| 1|   |  1|col  |   |   |user_pref   |pref_value   |   |      |  |
| 3|theme_count  | 1| 1|   |  1|sfunc|   |   |            |             |   |gcount|  |
| 3|theme_count  | 1| 2|  1|  1|col  |   |   |user_pref   |pref_value   |   |      |  |

view_part_def

The records in this table are intended to describe most of the parts of a SQL "select" which is the basis for a view rowset definition. The parts described here correspond to these SQL terms: "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 rowset 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 rowset. If you have multiple rowsets, 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 rowset merging is done.

Each record in this table uniquely corresponds to a sub-expression that is part of one of several optional main/root expressions in a view rowset 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.

Column List:

view_id - int,req - unique identifier for this view
rowset_num - int,req - unique identifier for rowset within view
view_part - str,req - what "part" of a view definition this is defining
expr_num - int,req - unique identifier for sub-expression within view rowset
p_expr_num - int - parent expression of this one, if any; empty if this is the root expression
p_expr_order - int,req - order of this expression among its siblings; 1 if no siblings
expr_type - str,req - what type of expression this is; eg: literal, variable, column, view, function
lit_val - str - if expr_type is 'lit', then store the literal value here
var_name - str - if expr_type is 'var', then put the caller bind variable (?) name here
src_name - str - if expr_type is 'col', then put the column source/alias name here
src_col - str - if expr_type is 'col', then put the source table/view column name here
f_view_id - int - if expr_type is 'view', then put the view/subquery id here
sfunc_code - str - if expr_type is 'sfunc', then put the standard built-in function name/code here
ufunc_id - int - if expr_type is 'ufunc', then put the user-defined stored function (block) id here

Index List:

primary - unique - [view_id,rowset_num,view_part,expr_num]
ak_order - unique - [view_id,rowset_num,view_part,p_expr_num,p_expr_order]
fk_rowset - foreign - [view_id,rowset_num]->[view_rowset]:[view_id,rowset_num]
fk_view_part - foreign - [view_part]->[cct_view_part]:[code]
fk_expr_type - foreign - [c_expr_type]->[cct_basic_expr_type]:[code]
fk_src_col - foreign - [view_id,rowset_num,src_name,src_col]->[view_src_col]:[view_id,rowset_num,src_name,src_col]
fk_f_view - foreign - [f_view_id]->[view]:[view_id]
fk_sfunc_code - foreign - [sfunc_code]->[cct_standard_func]:[code]
fk_ufunc - foreign - [ufunc_id]->[block]:[block_id]

Example Record List:

|vw|rn|part |en|pen|peo|type |lit  |var     |src_name |src_col   |fvw|sf    |uf|
|--|--|-----|--|---|---|-----|-----|--------|---------|----------|---|------|--|
| 1| 1|where| 1|   |  1|sfunc|     |        |         |          |   |eq    |  | - standard function of "eq(a,b)"
| 1| 1|where| 2|  1|  1|col  |     |        |user_auth|user_id   |   |      |  | - match column "user_auth"."user_id"
| 1| 1|where| 3|  1|  2|var  |     |curr_uid|         |          |   |      |  | - value of bind var "curr_uid"
| 2| 1|where| 1|   |  1|sfunc|     |        |         |          |   |and   |  | - standard function of "and(a,b)"
| 2| 1|where| 2|  1|  1|sfunc|     |        |         |          |   |like  |  | - standard function of "like(a,b)"
| 2| 1|where| 3|  2|  1|col  |     |        |father   |name      |   |      |  | - match column "father"."name"
| 2| 1|where| 4|  2|  2|var  |     |srchw_fa|         |          |   |      |  | - value of bind var "srchw_fa"
| 2| 1|where| 5|  1|  1|sfunc|     |        |         |          |   |like  |  | - standard function of "like(a,b)"
| 2| 1|where| 6|  5|  1|col  |     |        |mother   |name      |   |      |  | - match column "mother"."name"
| 2| 1|where| 7|  5|  2|var  |     |srchw_mo|         |          |   |      |  | - value of bind var "srchw_mo"
| 3| 1|where| 1|   |  1|sfunc|     |        |         |          |   |eq    |  | - standard function of "eq(a,b)"
| 3| 1|where| 2|  1|  1|col  |     |        |user_pref|pref_name |   |      |  | - match column "user_pref"."pref_name"
| 3| 1|where| 3|  1|  2|lit  |theme|        |         |          |   |      |  | - match literal value of "theme"
| 3| 1|group| 1|   |  1|col  |     |        |user_pref|pref_value|   |      |  | - group by "user_pref"."pref_value"
| 3| 1|havin| 1|   |  1|sfunc|     |        |         |          |   |gt    |  | - standard function of "gt(a,b)"
| 3| 1|havin| 2|  1|  1|sfunc|     |        |         |          |   |gcount|  | - standard function of "gcount(col)"
| 3| 1|havin| 4|  1|  2|lit  |    1|        |         |          |   |      |  | - match literal value of "theme"

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).

Had there been separate tables for the above, here are their descriptions

view_src_filter: Each record in this table uniquely corresponds to a sub-expression that is part of a filter definition in a view rowset. 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 records in this table exist for a particular view rowset, each one matches a source column, and the rows returned by the view rowset 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 rowset 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: Each record in this table uniquely corresponds to a sub-expression that is part of a filter definition in a view rowset. 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: The records in this table say what order to return the rows for a view rowset 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 rowset may only use the columns being grouped on in its output column definition. Certain standard functions can only be used when grouping.

SEQUENCES

This section still needs to be written.

Because all of the records in these tables are user defined, the provided record list is simply some example or "synopsis" data; none of it corresponds to anything in the SQL::ObjectModel code.

To be added: sequence.

BLOCKS

These tables are for storing user definitions of code blocks, which are composed of a series of commands plus conditionals and iterations. Types of blocks include 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 blocks as well.

All blocks are uniquely identified here by an "id", which is different from the view name that is stored in the database schema (where applicable); the actual name in the schema may change depending on what the implementing database is.

Because all of the records in these tables are user defined, the provided record list is simply some example or "synopsis" data; none of it corresponds to anything in the SQL::ObjectModel code.

block

Each record in this table uniquely corresponds to one database block, and has some core details.

Column List:

block_id - int,req - unique identifier for this block
p_block_id - int - parent block of this one, if any; empty if this is the root block
p_block_order - int,req - order of this block among its siblings; 1 if no siblings
block_type - str,req - what kind of block this is; eg: object, caller, cursor, inside
namespace_id - int - the namespace that this block is in, if any
block_name - str - the name this block is identified by, if any
block_order - int - to explicitely define what order blocks are declared in, if named
public_syn - str - a name for the block outside of any namespace, if named; Oracle public synonym

Undecided Possible Extra Columns:

return_type - str,req - if this block returns a value, this is its data type

Index List:

primary - unique - [block_id]
ak_name - unique - [namespace_id,block_name]
ak_order - unique - [namespace_id,block_order]
ak_public - unique - [public_syn]
fk_block_type - foreign - [block_type]->[cct_block_type]:[code]
fk_namespace - foreign - [namespace_id]->[namespace]:[namespace_id]

Undecided Possible Extra Indexes:

fk_return_type - foreign - [return_type]->[data_type]:[name]

block_var

Each record in this table is for declaring a variable within the scope of a block; it can be used by that block and by any child blocks, where applicable. If the current block is a package, these are package-level variables.

A variable could also be an input and/or output argument to the block, which is determined by its type.

Column List:

block_id - int,req - what block this variable is in
var_name - str,req - unique identifier for variable within view
var_order - int,req - what order variables are declared in
var_type - str,req - what basic variable type the variable is
is_argument - bool,req - true if this variable is a block argument (needs fleshing out)
data_type - str - if the var type is 'scalar', this is its user customized data type
init_lit_val - str - if var type is 'scalar', an initial value for var can go here
view_id - int - if var type is 'cursor', then put the view defining it here

Undecided Possible Extra Columns:

is_constant - bool - if the var type is 'scalar', this says its value may not be changed

Index List:

primary - unique - [block_id,var_name]
ak_order - unique - [block_id,var_order]
fk_block - foreign - [block_id]->[block]:[block_id]
fk_var_type - foreign - [var_type]->[cct_basic_var_type]:[code]
fk_data_type - foreign - [data_type]->[data_type]:[name]
fk_view - foreign - [view_id]->[view]:[view_id]

block_stmt

Each record in this table represents a command or statement or line within a block, 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 block_expr record(s) which refer to the block_stmt record. If this statement is something which opens a new unnamed block context, the block record is referred to by the block_stmt record.

Column List:

block_id - int,req - what block this statement is in
stmt_num - int,req - unique identifier for stmt within block, and what order it executes in
stmt_type - str,req - what type of stmt this is; eg: assignment, condit, call, loop, dml, return
dest_var_name - str - if the stmt type is 'assign', this var is where the expression result goes
c_block_id - int - if the stmt type starts an unnamed block context, this is the child block id

Index List:

primary - unique - [block_id,stmt_num]
fk_block - foreign - [block_id]->[block]:[block_id]
fk_dest_var - foreign - [block_id,dest_var_name]->[block_var]:[block_id,var_name]
fk_c_block - foreign - [c_block_id]->[block]:[block_id]

block_expr

Each record in this table uniquely corresponds to a sub-expression that is part of a main/root expression which is the right hand side of a variable assignment statement in a block. 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 block variable on the left side of the statement.

Column List:

block_id - int,req - unique identifier for this block
stmt_num - int,req - unique identifier for block statement this expression is part of
expr_num - int,req - unique identifier for sub-expression within block statement
p_expr_num - int - parent expression of this one, if any; empty if this is the root expression
p_expr_order - int,req - order of this expression among its siblings; 1 if no siblings
expr_type - str,req - what type of expression this is; eg: literal, variable, column, function
lit_val - str - if expr_type is 'lit', then store the literal value here
src_var_name - str - if expr_type is 'var', then put the block variable name here
sfunc_code - str - if expr_type is 'sfunc', then put the standard built-in function name/code here
ufunc_id - int - if expr_type is 'ufunc', then put the user-defined stored function (block) id here

Index List:

primary - unique - [block_id,stmt_num,expr_num]
ak_order - unique - [block_id,stmt_num,p_expr_num,p_expr_order]
fk_stmt - foreign - [block_id,stmt_num]->[block_stmt]:[block_id,stmt_num]
fk_expr_type - foreign - [c_expr_type]->[cct_basic_expr_type]:[code]
fk_src_var_name - foreign - [block_id,src_var_name]->[block_var]:[block_id,var_name]
fk_sfunc_code - foreign - [sfunc_code]->[cct_standard_func]:[code]
fk_ufunc - foreign - [ufunc_id]->[block]:[block_id]

trigger

This table is basically an intersection table between a table and a block, except that each block can only be used in one trigger. Each record in this table uniquely corresponds to a single trigger, which is a special unnamed block that is associated with a table and which 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.

Column List:

table_id - int,req - unique identifier for the table to watch
block_id - int,req - unique identifier for the block to execute, only 1 trig can use a block
run_before - bool,req - true when the block should execute before attempted change is made
run_after - bool,req - true when the block should execute after attempted change is made
on_insert - bool,req - true when block should execute when a record insert is attempted
on_update - bool,req - true when block should execute when a record update is attempted
on_delete - bool,req - true when block should execute when a record delete is attempted
for_each_row - bool,req - true when block is re-executed for each row affected; false is once

Index List:

primary - unique - [table_id,block_id]
ak_block - unique - [block_id]
fk_table - foreign - [table_id]->[table]:[table_id]
fk_block - foreign - [block_id]->[block]:[block_id]

USERS

This section still needs to be written.

Because all of the records in these tables are user defined, the provided record list is simply some example or "synopsis" data; none of it corresponds to anything in the SQL::ObjectModel code.

To be added: user, privilege.

COMMANDS AND RESULTS

This section still needs to be written.

Because all of the records in these tables are user defined, the provided record list is simply some example or "synopsis" data; none of it corresponds to anything in the SQL::ObjectModel code.

To be added: command, result.

SEE ALSO

perl(1), SQL::ObjectModel, Rosetta, Rosetta::Framework.

cpanm Rosetta

perl -MCPAN -e shell
install Rosetta