Questions on upgrading tables based upon the system tables.
I am writing code to migrate from one version of our schema to another (such as if we add tables or add columns). The from version is whatever is physically being used, and the new version is compiled into data structures in our code.
I can successfully create or remove tables, and add and drop columns.
We have multiple tables that we then bind to a merge table, so the schemas must be exact. Our tables have a primary key.
For the table and column based changes, querying sys.schemas, sys.tables, and sys.columns gives us everything we need. I can also figure out how to add the primary key on the new table (via alter table commands). However, I can't figure out how to find the set of columns in the primary key, to be able to build up the primary key.
Here is the table definition for our tables that go into the merge table (%04d goes from 0000 to the number of threads we send bulk data with):
CREATE TABLE if not exists data_availability__%04d ( interval_start BIGINT, source INT, PRIMARY KEY(interval_start, source) );
alter table data_availability add table data_availability__%04d;
Looking at system tables gives the following information (before adding this table):
sql>select * from sys.tables where name = 'data_availability'; +------+-------------------+------+------+------+-------+------+-------+-------+ | id | name | sche | quer | type | syste | comm | acces | tempo | : : : ma_i : y : : m : it_a : s : rary : : : : d : : : : ctio : : : : : : : : : : n : : : +======+===================+======+======+======+=======+======+=======+=======+ | 8889 | data_availability | 8800 | null | 3 | false | 0 | 0 | 0 | +------+-------------------+------+------+------+-------+------+-------+-------+ 1 tuple
sql>select * from sys.keys where table_id = 8889; +------+----------+------+----------------------------------------------+------+--------+ | id | table_id | type | name | rkey | action | +======+==========+======+==============================================+======+========+ | 8887 | 8889 | 0 | data_availability_interval_start_source_pkey | -1 | -1 | +------+----------+------+----------------------------------------------+------+--------+ 1 tuple
sql>select * from sys.key_types; +-------------+---------------+ | key_type_id | key_type_name | +=============+===============+ | 0 | Primary Key | | 1 | Unique Key | | 2 | Foreign Key | +-------------+---------------+ 3 tuples
sql>select * from sys.idxs where table_id = 8889; +------+----------+------+----------------------------------------------+ | id | table_id | type | name | +======+==========+======+==============================================+ | 8888 | 8889 | 0 | data_availability_interval_start_source_pkey | +------+----------+------+----------------------------------------------+ 1 tuple
sql>select * from sys.index_types; +---------------+-----------------------+ | index_type_id | index_type_name | +===============+=======================+ | 0 | Hash | | 1 | Join | | 2 | Order preserving hash | | 3 | No-index | | 4 | Imprint | | 5 | Ordered | +---------------+-----------------------+ 6 tuples
sql>select * from sys.columns where table_id = 8889; +------+----------------+--------+-------------+------------+----------+---------+-------+--------+---------+ | id | name | type | type_digits | type_scale | table_id | default | null | number | storage | +======+================+========+=============+============+==========+=========+=======+========+=========+ | 8885 | interval_start | bigint | 64 | 0 | 8889 | null | false | 0 | null | | 8886 | source | int | 32 | 0 | 8889 | null | false | 1 | null | +------+----------------+--------+-------------+------------+----------+---------+-------+--------+---------+ 2 tuples
The above data is for the merge table that we will bind the (new) table to. I can get the name and id of the primary key (which is also an index). I could not find a table that gives a column -> key binding. Is there such a table? If not, how do I get this information from MonetDB?
Thanks,
Dave
I could not find a table that gives a column -> key binding. Is there such a table? If not, how do I get this information from MonetDB?
Yes, use: sql>select * from sys.dependency_columns_on_keys where table_id = 8889;
See also: https://www.monetdb.org/Documentation/SQLcatalog/TriggersDependencies for information and other useful system views to find dependencies.
Tip: in mclient you can get a list of all the (system) tables/views in the current schema quickly using: sql>set schema sys; sql>\dStv SYSTEM TABLE sys._columns SYSTEM TABLE sys._tables SYSTEM TABLE sys.args SYSTEM TABLE sys.auths SYSTEM VIEW sys.columns SYSTEM TABLE sys.comments SYSTEM TABLE sys.db_user_info SYSTEM TABLE sys.dependencies SYSTEM VIEW sys.dependencies_vw SYSTEM VIEW sys.dependency_args_on_types SYSTEM VIEW sys.dependency_columns_on_functions SYSTEM VIEW sys.dependency_columns_on_indexes SYSTEM VIEW sys.dependency_columns_on_keys etc.
or alternatively using SQL query (lists only system tables/views from schema sys): sql>select tt.table_type_name, s.name || '.' || t.name as full_qualified_name from sys.tables t join sys.table_types tt on t.type = tt.table_type_id join sys.schemas s on t.schema_id = s.id where t.system and s.name = 'sys' order by full_qualified_name; +-----------------+----------------------------------------+ | table_type_name | full_qualified_name | +=================+========================================+ | SYSTEM TABLE | sys._columns | | SYSTEM TABLE | sys._tables | | SYSTEM TABLE | sys.args | | SYSTEM TABLE | sys.auths | | SYSTEM VIEW | sys.columns | | SYSTEM TABLE | sys.comments | | SYSTEM TABLE | sys.db_user_info | | SYSTEM TABLE | sys.dependencies | | SYSTEM VIEW | sys.dependencies_vw | | SYSTEM VIEW | sys.dependency_args_on_types | | SYSTEM VIEW | sys.dependency_columns_on_functions | etc.
See also: https://www.monetdb.org/Documentation/SystemCatalog for documentation on all system catalog tables and views.
FYI: for primary key constraints (and unique constraints) MonetDB implicitly creates a system managed hash index to speed up checking uniqueness. You can/do not need to create or drop hash indices yourself. If you drop the primary key (or unique) constraint, the implicitly created hash index will be removed also. As these are internally managed indices, they do not show up when querying sys.dependency_columns_on_indexes
On 07-08-2019 22:22, Gotwisner, Dave wrote:
I am writing code to migrate from one version of our schema to another (such as if we add tables or add columns). The from version is whatever is physically being used, and the new version is compiled into data structures in our code.
I can successfully create or remove tables, and add and drop columns.
We have multiple tables that we then bind to a merge table, so the schemas must be exact. Our tables have a primary key.
For the table and column based changes, querying sys.schemas, sys.tables, and sys.columns gives us everything we need. I can also figure out how to add the primary key on the new table (via alter table commands). However, I can’t figure out how to find the set of columns in the primary key, to be able to build up the primary key.
Here is the table definition for our tables that go into the merge table (%04d goes from 0000 to the number of threads we send bulk data with):
CREATE TABLE if not exists data_availability__%04d (
interval_start BIGINT,
source INT,
PRIMARY KEY(interval_start, source)
);
alter table data_availability add table data_availability__%04d;
Looking at system tables gives the following information (before adding this table):
sql>select * from sys.tables where name = 'data_availability';
+------+-------------------+------+------+------+-------+------+-------+-------+
| id | name | sche | quer | type | syste | comm | acces | tempo |
: : : ma_i : y : : m : it_a : s : rary :
: : : d : : : : ctio : : :
: : : : : : : n : : :
+======+===================+======+======+======+=======+======+=======+=======+
| 8889 | data_availability | 8800 | null | 3 | false | 0 | 0 | 0 |
+------+-------------------+------+------+------+-------+------+-------+-------+
1 tuple
sql>select * from sys.keys where table_id = 8889;
+------+----------+------+----------------------------------------------+------+--------+
| id | table_id | type | name | rkey | action |
+======+==========+======+==============================================+======+========+
| 8887 | 8889 | 0 | data_availability_interval_start_source_pkey | -1 | -1 |
+------+----------+------+----------------------------------------------+------+--------+
1 tuple
sql>select * from sys.key_types;
+-------------+---------------+
| key_type_id | key_type_name |
+=============+===============+
| 0 | Primary Key |
| 1 | Unique Key |
| 2 | Foreign Key |
+-------------+---------------+
3 tuples
sql>select * from sys.idxs where table_id = 8889;
+------+----------+------+----------------------------------------------+
| id | table_id | type | name |
+======+==========+======+==============================================+
| 8888 | 8889 | 0 | data_availability_interval_start_source_pkey |
+------+----------+------+----------------------------------------------+
1 tuple
sql>select * from sys.index_types;
+---------------+-----------------------+
| index_type_id | index_type_name |
+===============+=======================+
| 0 | Hash |
| 1 | Join |
| 2 | Order preserving hash |
| 3 | No-index |
| 4 | Imprint |
| 5 | Ordered |
+---------------+-----------------------+
6 tuples
sql>select * from sys.columns where table_id = 8889;
+------+----------------+--------+-------------+------------+----------+---------+-------+--------+---------+
| id | name | type | type_digits | type_scale | table_id | default | null | number | storage |
+======+================+========+=============+============+==========+=========+=======+========+=========+
| 8885 | interval_start | bigint | 64 | 0 | 8889 | null | false | 0 | null |
| 8886 | source | int | 32 | 0 | 8889 | null | false | 1 | null |
+------+----------------+--------+-------------+------------+----------+---------+-------+--------+---------+
2 tuples
The above data is for the merge table that we will bind the (new) table to. I can get the name and id of the primary key (which is also an index). I could not find a table that gives a column -> key binding. Is there such a table? If not, how do I get this information from MonetDB?
Thanks,
Dave
users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
participants (2)
-
dinther -
Gotwisner, Dave