SQL System Catalog

SQL System Catalog mk Thu, 03/28/2013 - 17:08

A relational system uses a  system catalog to store store schema information, such as details about tables and columns, and system views to present internal bookkeeping information. The MonetDB SQL system catalog is a collection of regular readonly tables and views. The relations are modified indirectly through a SQL commands. The system views are implemented as table producing functions.

Below you find a synopsis of the tables and views currently provided. In addition, graphical user interfaces, such as SQuirreL, can be used to interactively browse their content.

Schema, table and columns

Schema, table and columns mk Thu, 03/28/2013 - 17:19

All objects belong to a SQL schema, which ensures a conflict-free namespace for accessing them and a focus for user authorization. The schema contains tables and views administered. Statistics on columns can be gathered via the ANALYZE command.

sys.schemas
name type references description
"id" INTEGER   The unique internal identifier for the schema.
"name" VARCHAR   The unique SQL name.
"authorization" INTEGER sys.auths.id The identifier associated with the authorization.
"owner" INTEGER sys.auths.id The reference to the owner of this schema.
"system" BOOLEAN   Is it a system schema.
sys.table_types
name type references description
"table_type_id" SMALLINT   The unique internal identifier for the table type.
"table_type_name" VARCHAR   The unique SQL name of this table type.
sys.tables
name type references description
"id" INTEGER   The unique internal identifier for the table.
"name" VARCHAR   The SQL name.
"schema_id" INTEGER sys.schemas.id The schema identifier.
"query" VARCHAR   The query to populate a view.
"type" SMALLINT sys.table_types. table_type_id The table type identifier.
See sys.table_types for meaning of the identifiers.
"system" BOOLEAN   Is it a MonetDB system table?
"commit_action" SMALLINT   Action on commit for temp tables: 0=Not a temp table, 1=Delete rows, 2=Preserve rows, 3=Drop temp table.
"access" SMALLINT   Table access code: 0 = READWRITE, 1 = READ ONLY, 2 = INSERT ONLY.
"temporary" TINYINT   Retention period indicator; 0=persistent, 1=session.
sys.columns
name type references description
"id" INTEGER   The unique internal identifier for the column.
"name" VARCHAR   The SQL name.
"type" VARCHAR sys.types.sqlname The SQL type name.
"type_digits" INTEGER   The number of digits (radix 2) for numeric types or max length for character/binary strings.
"type_scale" INTEGER   The precision after decimal point. Only applicable for decimal/numeric types.
"table_id" INTEGER sys.tables.id The table this column belongs to.
"default" VARCHAR   The default lexical value.
"null" BOOLEAN   Nulls are allowed constraint.
"number" INTEGER   Column position in the underlying table, starting from 0.
"storage" VARCHAR   Internal storage class identifier.
sys.statistics
name type references description
"column_id" INTEGER sys.columns.id The unique internal identifier for the column.
"type" VARCHAR sys.types.sqlname The SQL type name.
"width" INTEGER   The number of bytes for fixed size data types or number of bytes per character for character/binary strings.
"stamp" TIMESTAMP   Timestamp when the statistics for the column was created or last updated.
"sample" BIGINT   The number of sample items/rows on which the statistic is based.
"count" BIGINT   The number of items/rows on which the statistic is based.
"unique" BIGINT   The number of unique value found for this column.
"nils" BIGINT   The number of NULLs found for this column.
"minval" CLOB   The minimum value found for this column.
"maxval" CLOB   The maximum value found for this column.
"sorted" BOOLEAN   Whether the data in this column is sorted in ascending order.
"revsorted" BOOLEAN   Whether the data in this column is sorted in descending order.

Functions, arguments, types

Functions, arguments, types mk Thu, 03/28/2013 - 20:35

The functions and procedures are collected in the tables below. It provides an roadmap to check availability of operations supported.

sys.functions
name type references description
"id" INTEGER   The unique internal identifier for the function.
"name" VARCHAR   The SQL name.
"func" VARCHAR   MAL function identifier or SQL function definition.
"mod" VARCHAR   MAL function module identifier.
"language" INTEGER sys.function_languages.language_id The programming language id code in which the function is defined, see table sys.function_languages.
"type" INTEGER sys.function_types.function_type_id Type of function, see table sys.function_types.
"side_effect" BOOLEAN   It does (not) lead to updates.
"varres" BOOLEAN   Does the function have varres?
"vararg" BOOLEAN   Can the function be called with  a variable number of arguments?
"schema_id" INTEGER sys.schemas.id The schema identifier.
sys.systemfunctions
name type references description
"function_id" INTEGER sys.functions.id

The unique internal identifier of a system function.
Note: this is not a system controlled foreign key reference, and is not automatically removed when the referred function is dropped from sys.functions.

sys.function_languages
name type references description
"language_id" SMALLINT   The unique internal identifier of a function programming language.
"language_name" VARCHAR   A description of the programming language.
sys.function_types
name type references description
"function_type_id" SMALLINT   The unique internal identifier of a function type.
"function_type_name" VARCHAR   A description of the function type.
sys.args
name type references description
"id" INTEGER   The unique internal identifier for function argument.
"func_id" INTEGER sys.functions.id The corresponding function.
"name" VARCHAR   The SQL name.
"type" VARCHAR sys.types.sqlname The SQL type name.
"type_digits" INTEGER   The number of digits for number types.
"type_scale" INTEGER   The precision after decimal point.
"inout" TINYINT   Whether the argument is usable for input (=0) or output or both (=1).
"number" INTEGER   The function argument position ranging from 1 to n for functions and from 0 to n-1 for procedures (sys.functions.type = 2). For functions a number 0 represents the return arg type.
sys.types
name type references description
"id" INTEGER   The unique internal identifier for the data type.
"systemname" VARCHAR   MAL data type name.
"sqlname" VARCHAR   SQL data type name.
"digits" INTEGER   The number of digits (in the specified radix) for numerical, floating point, time, timestamp and interval types.
"scale" INTEGER   Type of scale. 0=not applicable, 1=decimal, 2=exponential (floating point).
"radix" INTEGER   Radix for the number of digits value: 2 (= binary) or 10 (= decimal).
"eclass" INTEGER   The internal storage class equivalence identifier.
"schema_id" INTEGER sys.schemas.id The schema identifier.

Objects, keys, indices, sequences

Objects, keys, indices, sequences mk Thu, 03/28/2013 - 20:32
sys.objects
name type references description
"id" INTEGER   The internal object identifier. Primary key of this table is the combination of "id" and "nr".
"name" VARCHAR   The SQL name.
"nr" INTEGER   Position within the object list.
sys.keys
name type references description
"id" INTEGER sys.objects.id The unique internal key identifier.
"table_id" INTEGER sys.tables.id The corresponding table identifier.
"type" INTEGER sys.key_types.key_type_id Key type id: 0=Primary Key, 1=Unique Key, 2=Foreign Key, see table sys.key_types.
"name" VARCHAR   The SQL name given to the key.
"rkey" INTEGER   Related key designator. -1 if not applicable.
"action" INTEGER   Cascading properties. -1 if not applicable.
sys.key_types
name type references description
"key_type_id" SMALLINT   The unique internal identifier of a key type.
"key_type_name" VARCHAR   A description of the key type.
sys.idxs
name type references description
"id" INTEGER sys.objects.id The unique internal index identifier.
"table_id" INTEGER sys.tables.id The corresponding table identifier.
"type" INTEGER sys.index_types.index_type_id The corresponding index type identifier, see table sys.index_types.
"name" VARCHAR   The SQL name.
sys.index_types
name type references description
"index_type_id" SMALLINT   The unique internal identifier of an index type.
"index_type_name" VARCHAR   A description of the index type.
sys.sequences
name type references description
"id" INTEGER   The unique internal sequence identifier.
"schema_id" INTEGER sys.schemas.id The corresponding schema identifier.
"name" VARCHAR   The SQL name.
"start" BIGINT   The start value of the sequence.
"minvalue" BIGINT   The minimal value of the sequence.
"maxvalue" BIGINT   The maximal value of the sequence.
"increment" BIGINT   The sequence increment value.
"cacheinc" BIGINT   The increment to ease transaction management.
"cycle" BOOLEAN   The repeat the sequence upon hitting its bounds.

Triggers, dependencies

Triggers, dependencies mk Thu, 03/28/2013 - 20:38
sys.triggers
name type references description
"id" INTEGER   The unique internal trigger identifier.
"name" VARCHAR   Its SQL name.
"table_id" INTEGER sys.tables.id The corresponding triggering table identifier
"time" SMALLINT  

BEFORE/AFTER

"orientation" SMALLINT   FOR EACH ROW/FOR EACH STATEMENT
"event" SMALLINT   Update/insert/delete event.
"old_name" VARCHAR   The correlation SQL name for the previous state.
"new_name" VARCHAR   The correlation SQL name for the new state.
"condition" VARCHAR   The SQL condition.
"statement" VARCHAR   A SQL statement to be executed.
sys.dependency_types
name type references description
dependency_type_id SMALLINT   The unique identifier for the dependency type.
dependency_type_name VARCHAR   The unique name of this dependency type.
sys.dependencies
name type references description
"id" INTEGER sys.*.id The identifier which has a dependency.
"depend_id" INTEGER sys.*.id The identifier reference on which it depends. The combination of "id" and "depend_id" is unique key.
"depend_type" SMALLINT sys.dependency_types. dependency_type_id To which sys.<obj>.id it depends: a table or view, a column, a function, an auth, an object.
See sys.dependency_types for meaning of the identifiers

 

Following table producing convenience functions exist to find specific dependencies more easily:

SELECT * FROM sys.dependencies_schemas_on_users();
SELECT * FROM sys.dependencies_owners_on_schemas();
SELECT * FROM sys.dependencies_tables_on_views();
SELECT * FROM sys.dependencies_tables_on_indexes();
SELECT * FROM sys.dependencies_tables_on_triggers();
SELECT * FROM sys.dependencies_tables_on_foreignkeys();
SELECT * FROM sys.dependencies_tables_on_functions();
SELECT * FROM sys.dependencies_columns_on_views();
SELECT * FROM sys.dependencies_columns_on_keys();
SELECT * FROM sys.dependencies_columns_on_indexes();
SELECT * FROM sys.dependencies_columns_on_functions();
SELECT * FROM sys.dependencies_columns_on_triggers();
SELECT * FROM sys.dependencies_views_on_functions();
SELECT * FROM sys.dependencies_views_on_triggers();
SELECT * FROM sys.dependencies_functions_on_functions();
SELECT * FROM sys.dependencies_functions_os_triggers();
SELECT * FROM sys.dependencies_keys_on_foreignkeys();

Users, roles, privileges, sessions

Users, roles, privileges, sessions mk Thu, 03/28/2013 - 20:37

The user access grants are organized by authorization groups.

sys.auths
name type references description
"id" INTEGER   The authorization identifier.
"name" VARCHAR   The SQL authorization name.
"grantor" INTEGER sys.auths.id when grantor > 0 The corresponding authorization grantor.

 

sys.users   and   sys.db_user_info
name type references description
"name" VARCHAR sys.auths.name The unique user login name.
"fullname" VARCHAR   Full name of user.
"default_schema" INTEGER sys.schemas.id The default schema for this user.

 

sys.user_role
name type references description
"login_id" INTEGER sys.auths.id The corresponding user id.
"role_id" INTEGER sys.auths.id The corresponding role description identifier.

 

sys.privileges
name type references description
"obj_id" INTEGER   The internal privileges identifier.
"auth_id" INTEGER sys.auths.id The id of the authorizer.
"privileges" INTEGER sys.privilege_codes.privilege_code_id Privileges classification code, see table sys.privilege_codes.
"grantor" INTEGER sys.auths.id when grantor > 0 The corresponding authorization identifier.
"grantable" INTEGER   Permission to pass the rights onward.
sys.privilege_codes
name type references description
"privilege_code_id" INTEGER   The unique internal code of a privilege or combination of privileges.
"privilege_code_name" VARCHAR   A description of the privilege(s) associated with this code.

 

sys.sessions
name type references description
"user" VARCHAR sys.users.name The user who created the session.
"login" TIMESTAMP   The login start timestamp of the session.
"sessiontimeout" BIGINT   The session timeout time in seconds, 0 means timeout is disabled.
"lastcommand" TIMESTAMP   The start timestamp of the last issued command.
"querytimeout" BIGINT   The query execution timeout time in seconds, 0 means timeout is disabled.
"active" BOOLEAN   Whether the session is active or not.

QueryLog catalog, calls, history, queue

QueryLog catalog, calls, history, queue mk Fri, 03/29/2013 - 14:12

The tables below provide insight into queries compiled and executed. See also: Documentation/Cookbooks/SQLrecipes/QueryHistory

sys.querylog_catalog
name type references description
id oid   The internal query identifier.
owner string sys.users.name The user defining it.
defined timestamp   Time when the query was added to the catalog.
query string   The complete SQL query statement.  
pipe string sys.optimizers.name The MAL optimizer pipeline.
plan string   The MAL execution plan.
mal int   Size of MAL plan
optimize bigint   Optimization time in micro-seconds.
sys.querylog_calls
name type references description
id oid sys.querylog_catalog.id Reference to the querylog definition.
"start" timestamp   Time the statement was started.
"stop" timestamp   Time the statement was completely finished.
arguments string   The actual call structure.
tuples bigint   The number of tuples in the result set
run bigint   The time spent (in usec) until the result export.
ship bigint   The time spent (in usec) to ship the result set.
cpu int   The average cpu load percentage during execution.
io int   The percentage time waiting for IO to finish.
sys.querylog_history
name type references description
id oid sys.querylog_catalog.id Reference to the querylog definition.
owner string sys.users.name The user defining it.
defined timestamp   Time when the query was added to the catalog.
query string   The complete SQL query statement.  
pipe string sys.optimizers.name The MAL optimizer pipeline.
plan string   The MAL execution plan.
mal int   Size of MAL plan
optimize bigint   Optimization time in micro-seconds.
"start" timestamp   Time the statement was started.
"stop" timestamp   Time the statement was completely finished.
arguments string   The actual call structure.
tuples bigint   The number of tuples in the result set
run bigint   The time spent (in usec) until the result export.
ship bigint   The time spent (in usec) to ship the result set.
cpu int   The average cpu load percentage during execution.
io int   The percentage time waiting for IO to finish.

 

sys.queue
name type references description
qtag bigint   Unique internal query call identifier.
"user" string sys.users.name The user name responsible for the call.
started timestamp   The time the query was started.
estimate timestamp   The final/estimated time of completion.
progress int   Percentage of completion based on history.
status string   Running/paused.
tag oid sys.queue.qtag when > 0 The reference to the querylog catalog.
query string   The query itself.

Optimizer pipelines

Optimizer pipelines mk Fri, 03/29/2013 - 15:30

See also: Documentation/Cookbooks/SQLrecipes/OptimizerPipelines

sys.optimizers
name type references description
"name" VARCHAR   The unique optimizer pipeline name.
"def" VARCHAR   The optimizer pipeline definition.
"status" VARCHAR   Status: stable or experimental.

Environment variables

Environment variables mk Fri, 03/29/2013 - 15:30

The system keeps a small table of global variables, initialized upon system restart, session restart, or declared explicitly by the user.

sys.environment
name type references description
"name" VARCHAR   The unique identifying name of the global environment variable, such as monet_version and monet_release.
"value" VARCHAR   The value (as string) for the environment variable of the current instance of MonetDB.
sys.keywords
name type references description
"keyword" VARCHAR   The reserved SQL keyword.