System Catalog¶
You can easily get various schema information from the SQL statement by using the system catalog virtual class. For example, you can get the following schema information by using the catalog virtual class.
-- Classes that refer to the 'b_user' class
SELECT class_name
FROM db_attribute
WHERE domain_class_name = 'db_user';
-- The number of classes that the current user can access
SELECT COUNT(*)
FROM db_class;
-- Attribute of the 'db_user' class
SELECT attr_name, data_type
FROM db_attribute
WHERE class_name = 'db_user';
System Catalog Classes¶
To define a catalog virtual class, define a catalog class first. The figure below shows catalog classes to be added and their relationships. The arrows represent the reference relationship between classes, and the classes that start with an underline (_) are catalog classes.
Added catalog classes represent information about all classes, attributes and methods in the database. Catalog classes are made up of class composition hierarchy and designed to have OIDs of catalog class instances for cross reference.
_db_class¶
Represents class information. An index for class_name is created.
| Attribute Name | Data Type | Description |
|---|---|---|
| class_of | object | A class object. Represents a meta information object for the class stored in the system. |
| class_name | VARCHAR(255) | Class name |
| class_type | INTEGER | 0 for a class, and 1 for a virtual class |
| is_system_class | INTEGER | 0 for a user-defined class, and 1 for a system class |
| owner | db_user | Class owner |
| inst_attr_count | INTEGER | The number of instance attributes |
| class_attr_count | INTEGER | The number of class attributes |
| shard_attr_count | INTEGER | The number of shared attributes |
| inst_meth_count | INTEGER | The number of instance methods |
| calss_meth_count | INTEGER | The number of class methods |
| sub_classes | SEQUENCE OF _db_class | Class one level down |
| super_classes | SEQUENCE OF _db_class | Class one level up |
| inst_attrs | SEQUENCE OF _db_attribute | Instance attribute |
| class_attrs | SEQUENCE OF _db_attribute | Class attribute |
| shared_attrs | SEQUENCE OF _db_attribute | Shared attribute |
| inst_meths | SEQUENCE OF _db_method | Instance method |
| class_meths | SEQUENCE OF _db_method | Class method |
| meth_files | SEQUENCE OF _db_methfile | File path in which the function for the method is located |
| query_specs | SEQUENCE OF _db_queryspec | SQL definition statement for a virtual class |
| indexes | SEQUENCE OF _db_index | Index created in the class |
The following example shows how to retrieve all sub classes under the class owned by user 'PUBLIC' (for the child class female_event in the result, see the example in ADD SUPERCLASS Clause).
SELECT class_name, SEQUENCE(SELECT class_name FROM _db_class s WHERE s IN c.sub_classes)
FROM _db_class c
WHERE c.owner.name = 'PUBLIC' AND c.sub_classes IS NOT NULL;
class_name sequence((select class_name from _db_class s where s in c.sub_classes))
============================================
'event' {'female_event'}
Note
All examples of system catalog classes have been written in the csql utility. In this example, --no-auto-commit (inactive mode of auto-commit) and -u (specifying user DBA) options are used.
% csql --no-auto-commit -u dba demodb
_db_attribute¶
Represents attribute information. Indexes for class_of and attr_name are created.
| Attribute Name | Data Type | Description |
|---|---|---|
| class_of | _db_class | Class to which the attribute belongs |
| attr_name | VARCHAR(255) | Attribute name |
| attr_type | INTEGER | Type defined for the attribute. 0 for an instance attribute, 1 for a class attribute, and 2 for a shared attribute. |
| from_class_of | _db_class | If the attribute is inherited, the super class in which the attribute is defined is specified. Otherwise, NULL is specified. |
| from_attr_name | VARCHAR(255) | Inherited attribute. If an attribute name has changed to resolve a name conflict, the original name define in the super class is specified. Otherwise, NULL is specified. |
| def_order | INTEGER | Order of attributes in the class. Begins with 0. If the attribute is inherited, the order is the one defined in the super class. For example, if class y inherits attribute a from class x and a was first defined in x, def_order becomes 0. |
| data_type | INTEGER | Data type of the attribute. One of the values specified in the "Data Types Supported by CUBRID" table below. |
| default_value | VARCHAR(255) | Default value. Stores as a character string regardless of data types. If there is no default value, NULL. If the default value is NULL, NULL is used. If the data type is an object, 'volume id | page id | slot id' is used. If the data type is a collection, '{element 1, element 2, ... is used. |
| domains | SEQUENCE OF _db_domain | Domain information of the data type |
| is_nullable | INTEGER | 0 if a not null constraint is configured, and 1 otherwise. |
Data Types Supported by CUBRID
| Value | Meaning | Value | Meaning |
|---|---|---|---|
| 1 | INTEGER | 20 | OID |
| 2 | FLOAT | 22 | NUMERIC |
| 3 | DOUBLE | 23 | BIT |
| 4 | STRING | 24 | VARBIT |
| 5 | OBJECT | 25 | CHAR |
| 6 | SET | 31 | BIGINT |
| 7 | MULTISET | 32 | DATETIME |
| 8 | SEQUENCE | 33 | BLOB |
| 9 | ELO | 34 | CLOB |
| 10 | TIME | 35 | ENUM |
| 11 | TIMESTAMP | ||
| 12 | DATE | ||
| 18 | SHORT |
Character Sets Supported by CUBRID
| Value | Meaning |
|---|---|
| 0 | US English - ASCII encoding |
| 3 | Latin 1 - ISO 8859 encoding |
| 4 | KSC 5601 1990 - EUC encoding |
The following example shows how to retrieve user classes (from_class_of.is_system_class = 0) among the ones owned by user 'PUBLIC'.'
SELECT class_of.class_name, attr_name
FROM _db_attribute
WHERE class_of.owner.name = 'PUBLIC' AND from_class_of.is_system_class = 0
ORDER BY 1, def_order;
class_of.class_name attr_name
============================================
'female_event' 'code'
'female_event' 'sports'
'female_event' 'name'
'female_event' 'gender'
'female_event' 'players'
_db_domain¶
Represents domain information. An index for object_of is created.
| Attribute Name | Data Type | Description |
|---|---|---|
| object_of | object | Attribute that refers to the domain, which can be a method parameter or domain |
| data_type | INTEGER | Data type of the domain (a value in the "Value" column of the "Data Types Supported by CUBRID" table in _db_attribute) |
| prec | INTEGER | Precision of the data type. 0 is used if the precision is not specified. |
| scale | INTEGER | Scale of the data type. 0 is used if the scale is not specified. |
| class_of | _db_class | Domain class if the data type is an object, NULL otherwise. |
| code_set | INTEGER | Character set (value of table "character sets supported by CUBRID" in _db_attribute) if it is character data type. 0 otherwise. |
| set_domains | SEQUENCE OF _db_domain | Domain information about the data type of collection element if it is collection data type. NULL otherwise. |
_db_method¶
Represents method information. Indexes for class_of and meth_name are created.
| Attribute Name | Data Type | Description |
|---|---|---|
| class_of | _db_class | Class to which the method belongs |
| meth_type | INTEGER | Type of the method defined in the class. 0 for an instance method, and 1 for a class method. |
| from_class_of | _db_class | If the method is inherited, the super class in which it is defined is used otherwise NULL |
| from_meth_name | VARCHAR(255) | If the method is inherited and its name is changed to resolve a name conflict, the original name defined in the super class is used otherwise NULL |
| meth_name | VARCHAR(255) | Method name |
| signatures | SEQUENCE OF _db_meth_sig | C function executed when the method is called |
The following example shows how to retrieve class methods of the class with a class method (c.class_meth_count > 0), among classes owned by user 'DBA.'
SELECT class_name, SEQUENCE(SELECT meth_name
FROM _db_method m
WHERE m in c.class_meths)
FROM _db_class c
WHERE c.owner.name = 'DBA' AND c.class_meth_count > 0
ORDER BY 1;
class_name sequence((select meth_name from _db_method m where m in c.class_meths))
============================================
'db_serial' {'change_serial_owner'}
'db_authorizations' {'add_user', 'drop_user', 'find_user', 'print_authorizations', 'info', 'change_owner', 'change_trigg
r_owner', 'get_owner'}
'db_authorization' {'check_authorization'}
'db_user' {'add_user', 'drop_user', 'find_user', 'login'}
'db_root' {'add_user', 'drop_user', 'find_user', 'print_authorizations', 'info', 'change_owner', 'change_trigg
r_owner', 'get_owner', 'change_sp_owner'}
_db_meth_sig¶
Represents configuration information of C functions on the method. An index for meth_of is created.
| Attribute Name | Data Type | Description |
|---|---|---|
| meth_of | _db_method | Method for the function information |
| arg_count | INTEGER | The number of input arguments of the function |
| func_name | VARCHAR(255) | Function name |
| return_value | SEQUENCE OF _db_meth_arg | Return value of the function |
| arguments | SEQUENCE OF _db_meth_arg | Input arguments of the function |
_db_meth_arg¶
Represents method argument information. An index for meth_sig_of is created.
| Attribute Name | Data Type | Description |
|---|---|---|
| meth_sig_of | _db_meth_sig | Information of the function to which the argument belongs |
| data_type | INTEGER | Data type of the argument (a value in the "Value" column of the "Data Types Supported by CUBRID" in _db_attribute) |
| index_of | INTEGER | Order of the argument listed in the function definition. Begins with 0 if it is a return value, and 1 if it is an input argument. |
| domains | SEQUENCE OF _db_domain | Domain of the argument |
_db_meth_file¶
Represents information of a file in which a function is defined. An index for class_of is created.
| Attribute Name | Data Type | Description |
|---|---|---|
| class_of | _db_class | Class to which the method file information belongs |
| from_class_of | _db_class | If the file information is inherited, the super class in which it is defined is used otherwise, NULL |
| path_name | VARCHAR(255) | File path in which the method is located |
_db_query_spec¶
Represents the SQL statement of a virtual class. An index for class_of is created.
| Attribute Name | Data Type | Description |
|---|---|---|
| class_of | _db_class | Class information of the virtual class |
| spec | VARCHAR(4096) | SQL definition statement of the virtual class |
_db_index¶
Represents index information. An index for class_of is created.
| Attribute Name | Data Type | Description |
|---|---|---|
| class_of | _db_class | Class to which to index belongs |
| index_name | varchar(255) | Index name |
| is_unique | INTEGER | 1 if the index is unique, and 0 otherwise. |
| key_count | INTEGER | The number of attributes that comprise the key |
| key_attrs | SEQUENCE OF _db_index_key | Attributes that comprise the key |
| is_reverse | INTEGER | 1 for a reverse index, and 0 otherwise. |
| is_primary_key | INTEGER | 1 for a primary key, and 0 otherwise. |
| is_foreign_key | INTEGER | 1 for a foreign key, and 0 otherwise. |
| filter_expression | VARCHAR(255) | The conditions of filtered indexes |
| have_function | INTEGER | 1 for a foreign key, and 0 otherwise. |
The following example shows how to retrieve names of indexes that belong to the class.
SELECT class_of.class_name, index_name
FROM _db_index
ORDER BY 1;
class_of.class_name index_name
============================================
'_db_attribute' 'i__db_attribute_class_of_attr_name'
'_db_auth' 'i__db_auth_grantee'
'_db_class' 'i__db_class_class_name'
'_db_domain' 'i__db_domain_object_of'
'_db_index' 'i__db_index_class_of'
'_db_index_key' 'i__db_index_key_index_of'
'_db_meth_arg' 'i__db_meth_arg_meth_sig_of'
'_db_meth_file' 'i__db_meth_file_class_of'
'_db_meth_sig' 'i__db_meth_sig_meth_of'
'_db_method' 'i__db_method_class_of_meth_name'
'_db_partition' 'i__db_partition_class_of_pname'
'_db_query_spec' 'i__db_query_spec_class_of'
'_db_stored_procedure' 'u__db_stored_procedure_sp_name'
'_db_stored_procedure_args' 'i__db_stored_procedure_args_sp_name'
'athlete' 'pk_athlete_code'
'db_serial' 'pk_db_serial_name'
'db_user' 'i_db_user_name'
'event' 'pk_event_code'
'game' 'pk_game_host_year_event_code_athlete_code'
'game' 'fk_game_event_code'
'game' 'fk_game_athlete_code'
'history' 'pk_history_event_code_athlete'
'nation' 'pk_nation_code'
'olympic' 'pk_olympic_host_year'
'participant' 'pk_participant_host_year_nation_code'
'participant' 'fk_participant_host_year'
'participant' 'fk_participant_nation_code'
'record' 'pk_record_host_year_event_code_athlete_code_medal'
'stadium' 'pk_stadium_code'
_db_index_key¶
Represents key information on an index. An index for index_of is created.
| Attribute Name | Data Type | Description |
|---|---|---|
| index_of | _db_index | Index to which the key attribute belongs |
| key_attr_name | VARCHAR(255) | Name of the attribute that comprises the key |
| key_order | INTEGER | Order of the attribute in the key. Begins with 0. |
| asc_desc | INTEGER | 1 if the order of attribute values is descending, and 0 otherwise. |
| key_prefix_length | INTEGER | Length of prefix to be used as a key |
| func | VARCHAR(255) | Functional expression of function based index |
The following example shows how to retrieve the names of index that belongs to the class.
SELECT class_of.class_name, SEQUENCE(SELECT key_attr_name
FROM _db_index_key k
WHERE k in i.key_attrs)
FROM _db_index i
WHERE key_count >= 2;
class_of.class_name sequence((select key_attr_name from _db_index_key k where k in
i.key_attrs))
============================================
'_db_partition' {'class_of', 'pname'}
'_db_method' {'class_of', 'meth_name'}
'_db_attribute' {'class_of', 'attr_name'}
'participant' {'host_year', 'nation_code'}
'game' {'host_year', 'event_code', 'athlete_code'}
'record' {'host_year', 'event_code', 'athlete_code', 'medal'}
'history' {'event_code', 'athlete'}
_db_auth¶
Represents user authorization information of the class. An index for the grantee is created.
| Attribute Name | Data Type | Description |
|---|---|---|
| grantor | db_user | Authorization grantor |
| grantee | db_user | Authorization grantee |
| class_of | _db_class | Class object to which authorization is to be granted |
| auth_type | VARCHAR(7) | Type name of the authorization granted |
| is_grantable | INTEGER | 1 if authorization for the class can be granted to other users, and 0 otherwise. |
Authorization types supported by CUBRID are as follows:
- SELECT
- INSERT
- UPDATE
- DELETE
- ALTER
- INDEX
- EXECUTE
The following example shows how to retrieve authorization information defined in the class db_trig.
SELECT grantor.name, grantee.name, auth_type
FROM _db_auth
WHERE class_of.class_name = 'db_trig';
grantor.name grantee.name auth_type
==================================================================
'DBA' 'PUBLIC' 'SELECT'
_db_data_type¶
Represents the data type supported by CUBRID (see the "Data Types Supported by CUBRID" table in _db_attribute).
| Attribute Name | Data Type | Description |
|---|---|---|
| type_id | INTEGER | Data type identifier. Corresponds to the "Value" column in the "Data Types Supported by CUBRID" table. |
| type_name | VARCHAR(9) | Data type name. Corresponds to the "Meaning" column in the "Data Types Supported by CUBRID" table. |
The following example shows how to retrieve attributes and type names of the event class.
SELECT a.attr_name, t.type_name
FROM _db_attribute a join _db_data_type t ON a.data_type = t.type_id
WHERE class_of.class_name = 'event'
ORDER BY a.def_order;
attr_name type_name
============================================
'code' 'INTEGER'
'sports' 'STRING'
'name' 'STRING'
'gender' 'CHAR'
'players' 'INTEGER'
_db_partition¶
Represents partition information. Indexes for class_of and pname are created.
| Attribute Name | Data Type | Description |
|---|---|---|
| class_of | _db_class | OID of the parent class |
| pname | VARCHAR(255) | Parent - NULL |
| ptype | INTEGER | 0 - HASH 1 - RANGE 2 - LIST |
| pexpr | VARCHAR(255) | Parent only |
| pvalues | SEQUENCE OF | Parent - Column name, Hash size RANGE - MIN/MAX value : - Infinite MIN/MAX is stored as NULL LIST - value list |
_db_stored_procedure¶
Represents Java stored procedure information. An index for sp_name is created.
| Attribute Name | Data Type | Description |
|---|---|---|
| sp_name | VARCHAR(255) | Stored procedure name |
| sp_type | INTEGER | Stored procedure type (function or procedure) |
| return_type | INTEGER | Return value type |
| arg_count | INTEGER | The number of arguments |
| args | SEQUENCE OF _db_stored_procedure_args | Argument list |
| lang | INTEGER | Implementation language (currently, Java) |
| target | VARCHAR(4096) | Name of the Java method to be executed |
| owner | db_user | Owner |
_db_stored_procedure_args¶
Represents Java stored procedure argument information. An index for sp_name is created.
| Attribute Name | Data Type | Description |
|---|---|---|
| sp_name | VARCHAR(255) | Stored procedure name |
| index_of | INTEGER | Order of the arguments |
| arg_name | VARCHAR(255) | Argument name |
| data_type | INTEGER | Data type of the argument |
| mode | INTEGER | Mode (IN, OUT, INOUT) |
_db_collation¶
The information on collation.
| Attribute Name | Data Type | Description |
|---|---|---|
| coll_id | INTEGER | Collation ID |
| coll_name | VARCHAR(32) | Collation name |
| charset_id | INTEGER | Charset ID |
| built_in | INTEGER | Built-in or not while installing the product (0: Not built-in, 1: Built-in) |
| expansions | INTEGER | Expansion support (0: Not supported, 1: Supported) |
| contractions | INTEGER | Contraction support (0: Not supported, 1: Supported) |
| uca_strength | INTEGER | Weight strength |
| checksum | VARCHAR(32) | Checksum of a collation file |
db_user¶
| Attribute Name | Data Type | Description |
|---|---|---|
| name | VARCHAR(1073741823) | User name |
| id | INTEGER | User identifier |
| password | db_password | User password. Not displayed to the user. |
| direct_groups | SET OF db_user | Groups to which the user belongs directly |
| groups | SET OF db_user | Groups to which the user belongs directly or indirectly |
| authorization | db_authorization | Information of the authorization owned by the user |
| triggers | SEQUENCE OF object | Triggers that occur due to user actions |
Function Names
- set_password ()
- set_password_encoded ()
- add_member ()
- drop_member ()
- print_authorizations ()
- add_user ()
- drop_user ()
- find_user ()
- login ()
db_authorization¶
| Attribute Name | Data Type | Description |
|---|---|---|
| owner | db_user | User information |
| grants | SEQUENCE OF object | Sequence of {object for which the user has authorization, authorization grantor of the object, authorization type} |
Method Name
- check_authorization (varchar(255), integer)
db_trigger¶
| Attribute Name | Data Type | Description |
|---|---|---|
| owner | db_user | Trigger owner |
| name | VARCHAR(1073741823) | Trigger name |
| status | INTEGER | 1 for INACTIVE, and 2 for ACTIVE. The default value is 2. |
| priority | DOUBLE | Execution priority between triggers. The default value is 0. |
| event | INTEGER | 0 is set for UPDATE, 1 for UPDATE STATEMENT, 2 for DELETE, 3 for DELETE STATEMENT, 4 for INSERT, 5 for INSERT STATEMENT, 8 for COMMIT, and 9 for ROLLBACK. |
| target_class | object | Class object for the trigger target class |
| target_attribute | VARCHAR(1073741823) | Trigger target attribute name. If the target attribute is not specified, NULL* is used. |
| target_class_attribute | INTEGER | If the target attribute is an instance attribute, 0 is used. If it is a class attribute, 1 is used. The default value is 0. |
| condition_type | INTEGER | If a condition exist, 1; otherwise NULL. |
| condition | VARCHAR(1073741823) | Action condition specified in the IF statement |
| condition_time | INTEGER | 1 for BEFORE, 2 for AFTER, and 3 for DEFERRED if a condition exists; NULL, otherwise. |
| action_type | INTEGER | 1 for one of INSERT, UPDATE, DELETE, and CALL, 2 for REJECT, 3 for INVALIDATE_TRANSACTION, and 4 for PRINT. |
| action_definition | VARCHAR(1073741823) | Execution statement to be triggered |
| action_time | INTEGER | 1 for BEFORE, 2 for AFTER, and 3 for DEFERRED. |
db_ha_apply_info¶
A table that stores the progress status every time the applylogdb utility applies replication logs. This table is updated at every point the applylogdb utility commits, and the accumulative count of operations are stored in the *_counter column. The meaning of each column is as follows:
| Column Name | Column Type | Description |
|---|---|---|
| db_name | VARCHAR(255) | Name of the database stored in the log |
| db_creation_time | DATETIME | Creation time of the source database for the log to be applied |
| copied_log_path | VARCHAR(4096) | Path to the log file to be applied |
| committed_lsa_pageid | BIGINT | The page id of commit log lsa reflected last. Although applylogdb is restarted, the logs before last_committed_lsa are not reflected again. |
| committed_lsa_offset | INTEGER | The offset of commit log lsa reflected last. Although applylogdb is restarted, the logs before last_committed_lsa are not reflected again. |
| committed_rep_pageid | BIGINT | The page id of the replication log lsa reflected last. Check whether the reflection of replication has been delayed or not. |
| committed_rep_offset | INTEGER | The offset of the replication log lsa reflected last. Check whether the reflection of replication has been delayed or not. |
| append_lsa_page_id | BIGINT | The page id of the last replication log lsa at the last reflection. Saves append_lsa of the replication log header that is being processed by applylogdb at the time of reflecting the replication. Checks whether the reflection of replication has been delayed or not at the time of reflecting the replication log. |
| append_lsa_offset | INTEGER | The offset of the last replication log lsa at the last reflection. Saves append_lsa of the replication log header that is being processed by applylogdb at the time of reflecting the replication. Checks whether the reflection of replication has been delayed or not at the time of reflecting the replication log. |
| eof_lsa_page_id | BIGINT | The page id of the replication log EOF lsa at the last reflection. Saves eof_lsa of the replication log header that is being processed by applylogdb at the time of reflecting the replication. Checks whether the reflection of replication has been delayed or not at the time of reflecting the replication log. |
| eof_lsa_offset | INTEGER | The offset of the replication log EOF lsa at the last reflection. Saves eof_lsa of the replication log header that is being processed by applylogdb at the time of reflecting the replication. Checks whether the reflection of replication has been delayed or not at the time of reflecting the replication log. |
| final_lsa_pageid | BIGINT | The pageid of replication log lsa processed last by applylogdb. Checks whether the reflection of replication has been delayed or not. |
| final_lsa_offset | INTEGER | The offset of replication log lsa processed last by applylogdb. Checks whether the reflection of replication has been delayed or not. |
| required_page_id | BIGINT | The smallest page which should not be deleted by the log_max_archives parameter. The log page number from which the replication will be reflected. |
| required_page_offset | INTEGER | The offset of the log page from which the replication will be reflected. |
| log_record_time | DATETIME | Timestamp included in replication log committed in the slave database, i.e. the creation time of the log |
| log_commit_time | DATETIME | The time of reflecting the last commit log |
| last_access_time | DATETIME | The final update time of the db_ha_apply_info catalog |
| status | INTEGER | Progress status (0: IDLE, 1: BUSY) |
| insert_counter | BIGINT | Number of times that applylogdb was inserted |
| update_counter | BIGINT | Number of times that applylogdb was updated |
| delete_counter | BIGINT | Number of times that applylogdb was deleted |
| schema_counter | BIGINT | Number of times that applylogdb changed the schema |
| commit_counter | BIGINT | Number of times that applylogdb was committed |
| fail_counter | BIGINT | Number of times that applylogdb failed to be inserted/updated/deleted/committed and to change the schema |
| start_time | DATETIME | Time when the applylogdb process accessed the slave database |
System Catalog Virtual Class¶
General users can only see information of classes for which they have authorization through system catalog virtual classes. This section explains which information each system catalog virtual class represents, and virtual class definition statements.
DB_CLASS¶
Represents information of classes for which the current user has access authorization to a database.
| Attribute Name | Data Type | Description |
|---|---|---|
| class_name | VARCHAR(255) | Class name |
| owner_name | VARCHAR(255) | Name of class owner |
| class_type | VARCHAR(6) | 'CLASS' for a class, and 'VCLASS' for a virtual class |
| is_system_class | VARCHAR(3) | 'YES' for a system class, and 'NO' otherwise. |
| partitioned | VARCHAR(3) | 'YES' for a partitioned group class, and 'NO' otherwise. |
| is_reuse_oid_class | VARCHAR(3) | 'YES' for a REUSE_OID class, and 'NO' otherwise. |
The following example shows how to retrieve classes owned by the current user.
SELECT class_name
FROM db_class
WHERE owner_name = CURRENT_USER;
class_name
======================
'stadium'
'code'
'nation'
'event'
'athlete'
'participant'
'olympic'
'game'
'record'
'history'
'female_event'
The following example shows how to retrieve virtual classes that can be accessed by the current user.
SELECT class_name
FROM db_class
WHERE class_type = 'VCLASS';
class_name
======================
'db_stored_procedure_args'
'db_stored_procedure'
'db_partition'
'db_trig'
'db_auth'
'db_index_key'
'db_index'
'db_meth_file'
'db_meth_arg_setdomain_elm'
'db_meth_arg'
'db_method'
'db_attr_setdomain_elm'
'db_attribute'
'db_vclass'
'db_direct_super_class'
'db_class'
The following example shows how to retrieve system classes that can be accessed by the current user (PUBLIC user).
SELECT class_name
FROM db_class
WHERE is_system_class = 'YES' AND class_type = 'CLASS'
ORDER BY 1;
class_name
======================
'db_authorization'
'db_authorizations'
'db_root'
'db_serial'
'db_user'
DB_DIRECT_SUPER_CLASS¶
Represents the names of super classes (if any) of the class for which the current user has access authorization to a database.
| Attribute Name | Data Type | Description |
|---|---|---|
| class_name | VARCHAR(255) | Class name |
| super_class_name | VARCHAR(255) | super class name |
The following example shows how to retrieve super classes of the female_event class (see ADD SUPERCLASS Clause).
SELECT super_class_name
FROM db_direct_super_class
WHERE class_name = 'female_event';
super_class_name
======================
'event'
The following example shows how to retrieve super classes of the class owned by the current user (PUBLIC user).
SELECT c.class_name, s.super_class_name
FROM db_class c, db_direct_super_class s
WHERE c.class_name = s.class_name AND c.owner_name = user
ORDER BY 1;
class_name super_class_name
============================================
'female_event' 'event'
DB_VCLASS¶
Represents SQL definition statements of virtual classes for which the current user has access authorization to a database.
| Attribute Name | Data Type | Description |
|---|---|---|
| vclass_name | VARCHAR(255) | Virtual class name |
| vclass_def | VARCHAR(4096) | SQL definition statement of the virtual class |
The following example shows how to retrieve SQL definition statements of the db_class virtual class.
SELECT vclass_def
FROM db_vclass
WHERE vclass_name = 'db_class';
vclass_def
======================
'SELECT [c].[class_name], CAST([c].[owner].[name] AS VARCHAR(255)), CASE [c].[class_type] WHEN 0 THEN 'CLASS' WHEN 1 THEN 'VCLASS' ELSE 'UNKNOW' END, CASE WHEN MOD([c].[is_system_class], 2) = 1 THEN 'YES' ELSE 'NO' END, CASE WHEN [c].[sub_classes] IS NULL THEN 'NO' ELSE NVL((SELECT 'YES' FROM [_db_partition] [p] WHERE [p].[class_of] = [c] and [p].[pname] IS NULL), 'NO') END, CASE WHEN MOD([c].[is_system_class] / 8, 2) = 1 THEN 'YES' ELSE 'NO' END FROM [_db_class] [c] WHERE CURRENT_USER = 'DBA' OR {[c].[owner].[name]} SUBSETEQ ( SELECT SET{CURRENT_USER} + COALESCE(SUM(SET{[t].[g].[name]}), SET{}) FROM [db_user] [u], TABLE([groups]) AS [t]([g]) WHERE [u].[name] = CURRENT_USER) OR {[c]} SUBSETEQ ( SELECT SUM(SET{[au].[class_of]}) FROM [_db_auth] [au] WHERE {[au].[grantee].[name]} SUBSETEQ ( SELECT SET{CURRENT_USER} + COALESCE(SUM(SET{[t].[g].[name]}), SET{}) FROM [db_user] [u], TABLE([groups]) AS [t]([g]) WHERE [u].[name] = CURRENT_USER) AND [au].[auth_type] = 'SELECT')'
DB_ATTRIBUTE¶
Represents the attribute information of a class for which the current user has access authorization in the database.
| Attribute Name | Data Type | Description |
|---|---|---|
| attr_name | VARCHAR(255) | Attribute name |
| class_name | VARCHAR(255) | Name of the class to which the attribute belongs |
| attr_type | VARCHAR(8) | 'INSTANCE' for an instance attribute, 'CLASS' for a class attribute, and 'SHARED' for a shared attribute. |
| def_order | INTEGER | Order of attributes in the class. Begins with 0. If the attribute is inherited, the order is the one defined in the super class. |
| from_class_name | VARCHAR(255) | If the attribute is inherited, the super class in which it is defined is used. Otherwise, NULL. |
| from_attr_name | VARCHAR(255) | If the attribute is inherited and its name is changed to resolve a name conflict, the original name defined in the super class is used. Otherwise, NULL. |
| data_type | VARCHAR(9) | Data type of the attribute (one in the "Meaning" column of the "Data Types Supported by CUBRID" table in _db_attribute) |
| prec | INTEGER | Precision of the data type. 0 is used if the precision is not specified. |
| scale | INTEGER | Scale of the data type. 0 is used if the scale is not specified. |
| code_set | INTEGER | Character set (value of table "character sets supported by CUBRID" in _db_attribute) if it is string type. 0 otherwise. |
| domain_class_name | VARCHAR(255) | Domain class name if the data type is an object. NULL otherwise. |
| default_value | VARCHAR(255) | Saved as a character string by default, regardless of data types. If no default value is specified, NULL is stored. If a default value is NULL, it is displayed as 'NULL'. An object data type is represented as 'volume id | page id | slot id' while a set data type is represented as '{element 1, element 2, ... }'. |
| is_nullable | VARCHAR(3) | 'NO' if a not null constraint is set, and 'YES' otherwise. |
The following example shows how to retrieve attributes and data types of the event class.
SELECT attr_name, data_type, domain_class_name
FROM db_attribute
WHERE class_name = 'event'
ORDER BY def_order;
attr_name data_type domain_class_name
==================================================================
'code' 'INTEGER' NULL
'sports' 'STRING' NULL
'name' 'STRING' NULL
'gender' 'CHAR' NULL
'players' 'INTEGER' NULL
The following example shows how to retrieve attributes of the female_event class and its super class.
SELECT attr_name, from_class_name
FROM db_attribute
WHERE class_name = 'female_event'
ORDER BY def_order;
attr_name from_class_name
============================================
'code' 'event'
'sports' 'event'
'name' 'event'
'gender' 'event'
'players' 'event'
The following example shows how to retrieve classes whose attribute names are similar to name, among the ones owned by the current user. (The user is PUBLIC.)
SELECT a.class_name, a.attr_name
FROM db_class c join db_attribute a ON c.class_name = a.class_name
WHERE c.owner_name = CURRENT_USER AND attr_name like '%name%'
ORDER BY 1;
class_name attr_name
============================================
'athlete' 'name'
'code' 'f_name'
'code' 's_name'
'event' 'name'
'female_event' 'name'
'nation' 'name'
'stadium' 'name'
DB_ATTR_SETDOMAIN_ELM¶
Among attributes of the class to which the current user has access authorization in the database, if an attribute's data type is a collection (SET, MULTISET, SEQUENCE), this macro represents the data type of the element of the collection.
| Attribute Name | Data Type | Description |
|---|---|---|
| attr_name | VARCHAR(255) | Attribute name |
| class_name | VARCHAR(255) | Name of the class to which the attribute belongs |
| attr_type | VARCHAR(8) | 'INSTANCE' for an instance attribute, 'CLASS' for a class attribute, and 'SHARED' for a shared attribute. |
| data_type | VARCHAR(9) | Data type of the element |
| prec | INTEGER | Precision of the data type of the element |
| scale | INTEGER | Scale of the data type of the element |
| code_set | INTEGER | Character set if the data type of the element is a character |
| domain_class_name | VARCHAR(255) | Domain class name if the data type of the element is an object |
If the set_attr attribute of class D is of a SET (A, B, C) type, the following three records exist.
| Attr_name | Class_name | Attr_type | Data_type | Prec | Scale | Code_set | Domain_class_name |
|---|---|---|---|---|---|---|---|
| 'set_attr' | 'D' | 'INSTANCE' | 'SET' | 0 | 0 | 0 | 'A' |
| 'set_attr' | 'D' | 'INSTANCE' | 'SET' | 0 | 0 | 0 | 'B' |
| 'set_attr' | 'D' | 'INSTANCE' | 'SET' | 0 | 0 | 0 | 'C' |
The following example shows how to retrieve collection type attributes and data types of the city class (the city table defined in Containment Operators is created).
SELECT attr_name, attr_type, data_type, domain_class_name
FROM db_attr_setdomain_elm
WHERE class_name = 'city';
attr_name attr_type data_type domain_class_name
==============================================================================
'sports' 'INSTANCE' 'STRING' NULL
DB_METHOD¶
Represents method information of a class for which the current user has access authorization to a database.
| Attribute Name | Data Type | Description |
|---|---|---|
| meth_name | VARCHAR(255) | Method name |
| class_name | VARCHAR(255) | Name of the class to which the method belongs |
| meth_type | VARCHAR(8) | 'INSTANCE' for an instance method, and 'CLASS' for a class method. |
| from_class_name | VARCHAR(255) | If the method is inherited, the super class in which it is defined is used otherwise NULL |
| from_meth_name | VARCHAR(255) | If the method is inherited and its name is changed to resolve a name conflict, the original name defined in the super class is used otherwise NULL |
| func_name | VARCHAR(255) | Name of the C function for the method |
The following example shows how to retrieve methods of the db_user class.
SELECT meth_name, meth_type, func_name
FROM db_method
WHERE class_name = 'db_user'
ORDER BY meth_type, meth_name;
meth_name meth_type func_name
==================================================================
'add_user' 'CLASS' 'au_add_user_method'
'drop_user' 'CLASS' 'au_drop_user_method'
'find_user' 'CLASS' 'au_find_user_method'
'login' 'CLASS' 'au_login_method'
'add_member' 'INSTANCE' 'au_add_member_method'
'drop_member' 'INSTANCE' 'au_drop_member_method'
'print_authorizations' 'INSTANCE' 'au_describe_user_method'
'set_password' 'INSTANCE' 'au_set_password_method'
'set_password_encoded' 'INSTANCE' 'au_set_password_encoded_method'
'set_password_encoded_sha1' 'INSTANCE' 'au_set_password_encoded_sha1_method'
DB_METH_ARG¶
Represents the input/output argument information of the method of the class for which the current user has access authorization to a database.
| Attribute Name | Data Type | Description |
|---|---|---|
| meth_name | VARCHAR(255) | Method name |
| class_name | VARCHAR(255) | Name of the class to which the method belongs |
| meth_type | VARCHAR(8) | 'INSTANCE' for an instance method, and 'CLASS' for a class method. |
| index_of | INTEGER | Order in which arguments are listed in the function definition. Begins with 0 if it is a return value, and 1 if it is an input argument. |
| data_type | VARCHAR(9) | Data type of the argument |
| prec | INTEGER | Precision of the argument |
| scale | INTEGER | Scale of the argument |
| code_set | INTEGER | Character set if the data type of the argument is a character. |
| domain_class_name | VARCHAR(255) | Domain class name if the data type of the argument is an object. |
The following example shows how to retrieve input arguments of the method of the db_user class.
SELECT meth_name, data_type, prec
FROM db_meth_arg
WHERE class_name = 'db_user';
meth_name data_type prec
=========================================================
'append_data' 'STRING' 1073741823
DB_METH_ARG_SETDOMAIN_ELM¶
If the data type of the input/output argument of the method of the class is a set, for which the current user has access authorization in the database, this macro represents the data type of the element of the set.
| Attribute Name | Data Type | Description |
|---|---|---|
| meth_name | VARCHAR(255) | Method name |
| class_name | VARCHAR(255) | Name of the class to which the method belongs |
| meth_type | VARCHAR(8) | 'INSTANCE' for an instance method, and 'CLASS' for a class method. |
| index_of | INTEGER | Order of arguments listed in the function definition. Begins with 0 if it is a return value, and 1 if it is an input argument. |
| data_type | VARCHAR(9) | Data type of the element |
| prec | INTEGER | Precision of the element |
| scale | INTEGER | Scale of the element |
| code_set | INTEGER | Character set if the data type of the element is a character |
| domain_class_name | VARCHAR(255) | Domain class name if the data type of the element is an object |
DB_METH_FILE¶
Represents information of a file in which the method of the class for which the current user has access authorization in the database is defined.
| Attribute Name | Data Type | Description |
|---|---|---|
| class_name | VARCHAR(255) | Name of the class to which the method file belongs |
| path_name | VARCHAR(255) | File path in which the C function is defined |
| from_class_name | VARCHAR(255) | Name of the super class in which the method file is defined if the method is inherited, and otherwise NULL |
DB_INDEX¶
Represents information of indexes created for the class for which the current user has access authorization to a database.
| Attribute Name | Data Type | Description |
|---|---|---|
| index_name | VARCHAR(255) | Index name |
| is_unique | VARCHAR(3) | 'YES' for a unique index, and 'NO' otherwise. |
| is_reverse | VARCHAR(3) | 'YES' for a reversed index, and 'NO' otherwise. |
| class_name | VARCHAR(255) | Name of the class to which the index belongs |
| key_count | INTEGER | The number of attributes that comprise the key |
| is_primary_key | VARCHAR(3) | 'YES' for a primary key, and 'NO' otherwise. |
| is_foreign_key | VARCHAR(3) | 'YES' for a foreign key, and 'NO' otherwise. |
| filter_expression | VARCHAR(255) | Conditions of filtered indexes |
| have_function | VARCHAR(3) | 'YES' for function based and 'NO' otherwise. |
The following example shows how to retrieve index information of the class.
SELECT class_name, index_name, is_unique
FROM db_index
ORDER BY 1;
class_name index_name is_unique
==================================================================
'athlete' 'pk_athlete_code' 'YES'
'city' 'pk_city_city_name' 'YES'
'db_serial' 'pk_db_serial_name' 'YES'
'db_user' 'i_db_user_name' 'NO'
'event' 'pk_event_code' 'YES'
'female_event' 'pk_event_code' 'YES'
'game' 'pk_game_host_year_event_code_athlete_code' 'YES'
'game' 'fk_game_event_code' 'NO'
'game' 'fk_game_athlete_code' 'NO'
'history' 'pk_history_event_code_athlete' 'YES'
'nation' 'pk_nation_code' 'YES'
'olympic' 'pk_olympic_host_year' 'YES'
'participant' 'pk_participant_host_year_nation_code' 'YES'
'participant' 'fk_participant_host_year' 'NO'
'participant' 'fk_participant_nation_code' 'NO'
'record' 'pk_record_host_year_event_code_athlete_code_medal' 'YES'
'stadium' 'pk_stadium_code' 'YES'
...
DB_INDEX_KEY¶
Represents the key information of indexes created for the class for which the current user has access authorization to a database.
| Attribute Name | Data Type | Description |
|---|---|---|
| index_name | VARCHAR(255) | Index name |
| class_name | VARCHAR(255) | Name of the class to which the index belongs |
| key_attr_name | VARCHAR(255) | Name of attributes that comprise the key |
| key_order | INTEGER | Order of attributes in the key. Begins with 0. |
| asc_desc | VARCHAR(4) | 'DESC' if the order of attribute values is descending, and 'ASC' otherwise. |
| key_prefix_length | INTEGER | The length of prefix to be used as a key |
| func | VARCHAR(255) | Functional expression of function based index |
The following example shows how to retrieve index key information of the class.
SELECT class_name, key_attr_name, index_name
FROM db_index_key
ORDER BY class_name, key_order;
'athlete' 'code' 'pk_athlete_code'
'city' 'city_name' 'pk_city_city_name'
'db_serial' 'name' 'pk_db_serial_name'
'db_user' 'name' 'i_db_user_name'
'event' 'code' 'pk_event_code'
'female_event' 'code' 'pk_event_code'
'game' 'host_year' 'pk_game_host_year_event_code_athlete_code'
'game' 'event_code' 'fk_game_event_code'
'game' 'athlete_code' 'fk_game_athlete_code'
...
DB_AUTH¶
Represents authorization information of classes for which the current user has access authorization to a database.
| Attribute Name | Data Type | Description |
|---|---|---|
| grantor_name | VARCHAR(255) | Name of the user who grants authorization |
| grantee_name | VARCHAR(255) | Name of the user who is granted authorization |
| class_name | VARCHAR(255) | Name of the class for which authorization is to be granted |
| auth_type | VARCHAR(7) | Name of the authorization type granted |
| is_grantable | VARCHAR(3) | 'YES' if authorization for the class can be granted to other users, and 'NO' otherwise. |
The following example how to retrieve authorization information of the classes whose names begin with db_a.
SELECT class_name, auth_type, grantor_name
FROM db_auth
WHERE class_name like 'db_a%'
ORDER BY 1;
class_name auth_type grantor_name
==================================================================
'db_attr_setdomain_elm' 'SELECT' 'DBA'
'db_attribute' 'SELECT' 'DBA'
'db_auth' 'SELECT' 'DBA'
'db_authorization' 'EXECUTE' 'DBA'
'db_authorization' 'SELECT' 'DBA'
'db_authorizations' 'EXECUTE' 'DBA'
'db_authorizations' 'SELECT' 'DBA'
DB_TRIG¶
Represents information of a trigger that has the class for which the current user has access authorization to a database, or its attribute as the target.
| Attribute Name | Data Type | Description |
|---|---|---|
| trigger_name | VARCHAR(255) | Trigger name |
| target_class_name | VARCHAR(255) | Target class |
| target_attr_name | VARCHAR(255) | Target attribute. If not specified in the trigger, NULL |
| target_attr_type | VARCHAR(8) | Target attribute type. If specified, 'INSTANCE' is used for an instance attribute, and 'CLASS' is used for a class attribute. |
| action_type | INTEGER | 1 for one of INSERT, UPDATE, DELETE, and CALL, 2 for REJECT, 3 for INVALIDATE_TRANSACTION, and 4 for PRINT. |
| action_time | INTEGER | 1 for BEFORE, 2 for AFTER, and 3 for DEFERRED. |
DB_PARTITION¶
Represents information of partitioned classes for which the current user has access authorization to a database.
| Attribute Name | Data Type | Description |
|---|---|---|
| class_name | VARCHAR(255) | Class name |
| partition_name | VARCHAR(255) | Partition name |
| partition_class_name | VARCHAR(255) | Partitioned class name |
| partition_type | VARCHAR(32) | Partition type (HASH, RANGE, LIST) |
| partition_expr | VARCHAR(255) | Partition expression |
| partition_values | SEQUENCE OF | RANGE - MIN/MAX value - For infinite MIN/MAX, NULL LIST - value list |
The following example shows how to retrieve the partition information currently configured for the participant2 class.
SELECT * from db_partition where class_name = 'participant2';
class_name partition_name partition_class_name partition_type partition_expr partition_values
====================================================================================================================================
'participant2' 'before_2000' 'participant2__p__before_2000' 'RANGE' 'host_year' {NULL, 2000}
'participant2' 'before_2008' 'participant2__p__before_2008' 'RANGE' 'host_year' {2000, 2008}
DB_STORED_PROCEDURE¶
Represents information of Java stored procedure for which the current user has access authorization to a database.
| Attribute Name | Data Type | Description |
|---|---|---|
| sp_name | VARCHAR(255) | Stored procedure name |
| sp_type | VARCHAR(16) | Stored procedure type (function or procedure) |
| return_type | VARCHAR(16) | Return value type |
| arg_count | INTEGER | The number of arguments |
| lang | VARCHAR(16) | Implementing language (currently, Java) |
| target | VARCHAR(4096) | Name of the Java method to be executed |
| owner | VARCHAR(256) | Owner |
The following example shows how to retrieve Java stored procedures owned by the current user.
SELECT sp_name, target from db_stored_procedure
WHERE sp_type = 'FUNCTION' AND owner = CURRENT_USER;
sp_name target
============================================
'hello' 'SpCubrid.HelloCubrid() return java.lang.String'
'sp_int' 'SpCubrid.SpInt(int) return int'
DB_STORED_PROCEDURE_ARGS¶
Represents argument information of Java stored procedure for which the current user has access authorization to a database.
| Attribute Name | Data Type | Description |
|---|---|---|
| sp_name | VARCHAR(255) | Stored procedure name |
| index_of | INTEGER | Order of the arguments |
| arg_name | VARCHAR(256) | Argument name |
| data_type | VARCHAR(16) | Data type of the argument |
| mode | VARCHAR(6) | Mode (IN, OUT, INOUT) |
The following example shows how to retrieve arguments the 'phone_info' Java stored procedure in the order of the arguments.
SELECT index_of, arg_name, data_type, mode
FROM db_stored_procedure_args
WHERE sp_name = 'phone_info'
ORDER BY index_of;
index_of arg_name data_type mode
===============================================================
0 'name' 'STRING' 'IN'
1 'phoneno' 'STRING' 'IN'
DB_COLLATION¶
The information on collation.
| Attribute Name | Data Type | Description |
|---|---|---|
| coll_id | INTEGER | Collation ID |
| coll_name | VARCHAR(255) | Collation name |
| charset_name | VARCHAR(255) | Charset name |
| is_builtin | VARCHAR(3) | Built-in or not while installing the product(Yes, No) |
| has_expansions | VARCHAR(3) | Having expansion or not(Yes, No) |
| contractions | INTEGER | Whether to include abbreviation |
| uca_strength | VARCHAR(255) | Weight strength (Not applicable, Primary, Secondary, Tertiary, Quaternary, Identity, Unknown) |
Catalog Class/Virtual Class Authorization¶
Catalog classes are created to be owned by dba. However, dba can only execute SELECT operations. If dba executes operations such as UPDATE / DELETE, an authorization failure error occurs. General users cannot execute queries on system catalog classes.
Although catalog virtual classes are created to be owned by dba, all users can perform the SELECT statement on catalog virtual classes. Of course, UPDATE / DELETE operations on catalog virtual classes are not allowed.
Updating catalog classes/virtual classes is automatically performed by the system when users execute a DDL statement that creates/modifies/deletes a class/attribute/index/user/authorization.
Consistency of Catalog Information¶
Catalog information is represented by the instance of a catalog class/virtual class. If such information is accessed at the READ UNCOMMITTED INSTANCES (TRAN_REP_CLASS_UNCOMMIT_INSTANCE or TRAN_COMMIT_CLASS_UNCOMMIT_INSTANCE) isolation level, incorrect values (values being changed) can be read. Therefore, to get correct catalog information, you must use the SELECT query on the catalog class/virtual class at the READ COMMITTED INSTANCES isolation level or higher.
Querying on Catalog¶
To query on catalog classes, you must convert identifiers such as class, virtual class, attribute, trigger, method and index names to lowercases, and create them. Therefore, you must use lowercases when querying on catalog classes. But, DB user name is changed as uppercases and stored into db_user system catalog table.
CREATE TABLE Foo(name varchar(255));
SELECT class_name, partitioned FROM db_class WHERE class_name = 'Foo';
There are no results.
SELECT class_name, partitioned FROM db_class WHERE class_name = 'foo';
class_name partitioned
============================
'foo' 'NO'
CREATE USER tester PASSWORD 'testpwd';
SELECT name, password FROM db_user;
name password
============================================
'DBA' NULL
'PUBLIC' NULL
'TESTER' db_password