SQL System Catalog

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

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

Below you find a synopsis of the system 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, ids, dependencies, comments

Triggers, ids, dependencies, comments 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.ids
name type references description
"id" INTEGER sys.*.id The identifier of the object.
"name" VARCHAR   The name of the object.
"schema_id" INTEGER sys.schemas.id If applicable: The schema identifier the object belongs to, else NULL.
"table_id" INTEGER sys.tables.id If applicable: The table identifier the object belongs to, else NULL.
"table_name" VARCHAR sys.tables.name If applicable: The table name the object belongs to, else NULL.
"obj_type" VARCHAR   Object type name.
"sys_table" VARCHAR   Corresponding system table name where details on the object can be queried.
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.ids.id The identifier which has a dependency.
"depend_id" INTEGER sys.ids.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
sys.dependencies_vw
name type references description
"id" INTEGER sys.ids.id The identifier of the object which has a dependency.
"obj_type" VARCHAR   Object type name.
"name" VARCHAR   The name of the object.
"used_by_id" INTEGER sys.ids.id The identifier reference on which it depends. The combination of "id" and "used_by_id" is unique key.
"used_by_obj_type" VARCHAR   Object type name of the used_by_id.
"used_by_name" VARCHAR   The name of the object referenced by the used_by_id.
"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.
"dependency_type_name" VARCHAR sys.dependency_types
.dependency_type_name
The name of the dependency type.

 

sys.comments
name type references description
"id" INTEGER sys.ids.id The identifier of the object which has a comment.
"remark" VARCHAR   The remark comment associated to the object.

 

Following convenience views exist to find specific dependencies more easily:

SELECT * FROM sys.dependency_owners_on_schemas;
SELECT * FROM sys.dependency_schemas_on_users;
SELECT * FROM sys.dependency_tables_on_foreignkeys;
SELECT * FROM sys.dependency_tables_on_functions;
SELECT * FROM sys.dependency_tables_on_indexes;
SELECT * FROM sys.dependency_tables_on_procedures;
SELECT * FROM sys.dependency_tables_on_triggers;
SELECT * FROM sys.dependency_tables_on_views;
SELECT * FROM sys.dependency_views_on_functions;
SELECT * FROM sys.dependency_views_on_procedures;
SELECT * FROM sys.dependency_views_on_views;
SELECT * FROM sys.dependency_columns_on_functions;
SELECT * FROM sys.dependency_columns_on_indexes;
SELECT * FROM sys.dependency_columns_on_keys;
SELECT * FROM sys.dependency_columns_on_procedures;
SELECT * FROM sys.dependency_columns_on_triggers;
SELECT * FROM sys.dependency_columns_on_types;
SELECT * FROM sys.dependency_columns_on_views;
SELECT * FROM sys.dependency_keys_on_foreignkeys;
SELECT * FROM sys.dependency_functions_on_functions;
SELECT * FROM sys.dependency_functions_on_procedures;
SELECT * FROM sys.dependency_functions_on_triggers;
SELECT * FROM sys.dependency_functions_on_types;
SELECT * FROM sys.dependency_functions_on_views;
SELECT * FROM sys.dependency_args_on_types;

Storage of tables and columns

Storage of tables and columns dinther Thu, 08/30/2018 - 18:17

The information on calculated storage of data tables and columns are accessible from the system views below. These views gives the database administrator insight in the actual footprint of the persistent tables and the maximum playground used when indices are introduced upon them.
The actual storage footprint of an existing database can be obtained by querying the view sys.storage (or for specific table via the table producing function storage(schema_name, table_name)). It represents the actual state of affairs, i.e. storage on disk of columns and foreign key indices, and possible temporary hash indices. For strings we take a sample to determine their average length.
By changing the storagemodelinput table directly, the footprint for yet to be loaded databases can be assessed. See also Storage Model and Disk Space

sys.storage
name type references description
"schema" VARCHAR sys.schemas.name The schema name.
"table" VARCHAR sys.tables.name The table name.
"column" VARCHAR sys.columns.name The column name.
"type" VARCHAR sys.types.sqlname The data type name.
"mode" VARCHAR   State of the column, such as writable.
"location" VARCHAR   The location (relative path and file name) of the persistent column data file.
"count" BIGINT   The number of data values in the column.
"typewidth" INTEGER   Maximum width of the data in bytes for the column. Large objects without a maximum specified will have 0.
"columnsize" BIGINT   The total size in bytes of the column.
"heapsize" BIGINT   The total size in bytes of the heap of the column if existing such as when the column is CHAR(n) or VARCHAR(n) or CLOB(n) or BLOB(n).
"hashes" BIGINT   The total size in bytes of the hash of a column data if existing.
"phash" BOOLEAN   Whether it has a primary key hash?
"imprints" BIGINT   The total size in bytes of the imprint of a column data if existing.
"sorted" BOOLEAN   Whether the column data is sorted?
"revsorted" BOOLEAN   Whether the column data is reverse sorted?
"unique" BOOLEAN   Whether all values in the column data are distinct from each other?
"orderidx" BIGINT   The total size in bytes of the ordered index of a column data if existing.

 

sys.storagemodel
name type references description
"schema" VARCHAR sys.schemas.name The schema name.
"table" VARCHAR sys.tables.name The table name.
"column" VARCHAR sys.columns.name The column name.
"type" VARCHAR sys.types.sqlname The data type name.
"count" BIGINT   The number of data values in the column.
"columnsize" BIGINT   The total size in bytes of the column.
"heapsize" BIGINT   The total size in bytes of the heap of the column if existing such as when the column is CHAR(n) or VARCHAR(n) or CLOB(n) or BLOB(n).
"hashes" BIGINT   The total size in bytes of the hash of a column data if existing.
"imprints" BIGINT   The total size in bytes of the imprint of a column data if existing.
"sorted" BOOLEAN   Whether the column data is sorted?
"revsorted" BOOLEAN   Whether the column data is reverse sorted?
"unique" BOOLEAN   Whether all values in the column data are distinct from each other?
"orderidx" BIGINT   The total size in bytes of the ordered index of a column data if existing.

 

sys.storagemodelinput
name type references description
"schema" VARCHAR sys.schemas.name The schema name.
"table" VARCHAR sys.tables.name The table name.
"column" VARCHAR sys.columns.name The column name.
"type" VARCHAR sys.types.sqlname The data type name.
"typewidth" INTEGER   Maximum width of the data in bytes for the column. Large objects without a maximum specified will have 0.
"count" BIGINT   The estimated number of tuples.
"distinct" BIGINT   Indication of distinct number of strings.
"atomwidth" INTEGER   Average width of strings or clob.
"reference" BOOLEAN   Whether the column is used as foreign key reference?
"sorted" BOOLEAN   Whether the column data is sorted? If set there is no need for an index.
"revsorted" BOOLEAN   Whether the column data is reverse sorted?
"unique" BOOLEAN   Whether all values in the column data are distinct from each other?
"orderidx" BIGINT   The total size in bytes of the ordered index of a column.

 

sys.tablestoragemodel
name type references description
"schema" VARCHAR sys.schemas.name The schema name.
"table" VARCHAR sys.tables.name The table name.
"count" BIGINT   The number of data rows in the table.
"columnsize" BIGINT   The total size in bytes of all columns in this table.
"heapsize" BIGINT   The total size in bytes of the heaps of all columns in this table.
"hashes" BIGINT   The total size in bytes of the hashes of all columns in this table.
"imprints" BIGINT   The total size in bytes of the imprints of all columns in this table.
"auxiliary" BIGINT   The maximum size in bytes if all non-sorted columns would be augmented with a hash (rare situation).

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 unique authorization identifier.
"name" VARCHAR   The SQL authorization name of the user or role.
"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.roles
name type references description
"id" INTEGER   The unique role identifier.
"name" VARCHAR   The role name.
"grantor" INTEGER sys.auths.id when grantor > 0 The corresponding authorization grantor.

 

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

 

sys.privileges
name type references description
"obj_id" INTEGER sys.ids.id The identifier of the object for which privilege(s) are given to a certain user or role.
"auth_id" INTEGER sys.auths.id The id of the user or role.
"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 grantor authorization identifier.
"grantable" INTEGER   Permission to pass privilege rights to other users or roles.
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.var_values
name type references description
"var_name" VARCHAR   The unique identifying name of the system session variable, such as current_schema, optimizer or last_id.
"value" VARCHAR   The value (as string) for the system variable of the current session.
sys.keywords
name type references description
"keyword" VARCHAR   The reserved SQL keyword.