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