如何发现MonetDB中给定索引或键的列(How to discover the columns for a given index or key in MonetDB)

MonetDB似乎支持一套相当全面的系统目录视图 ，以便发现数据库的模式结构。不幸的是，我似乎无法找到一个SQL查询，它将获得给定键或索引的一组列 。以下是由Tables表报告的系统表/视图：

模式类型函数args序列依赖关系连接_tables _columns关键字idxs触发器对象表列db_user_info用户user_role auths权限queryhistory callhistory querylog systemfunctions

我尝试了依赖关系，但ID似乎没有匹配。顺便说一句，我曾尝试寻找源代码，但我还没有找到系统视图的创建和维护位置。

MonetDB seems to support a fairly comprehensive set of system catalog views in order to discover the schema structure of the database. Unfortunately, I can't seem to find a SQL query that will obtain the set of columns for a given key or index. Here are the system tables/views are reported by the Tables table:

schemas types functions args sequences dependencies connections _tables _columns keys idxs triggers objects tables columns db_user_info users user_role auths privileges queryhistory callhistory querylog systemfunctions

I tried dependencies, but the IDs don't seem to match up. BTW, I did try looking in the source code, but I haven't yet found where the system views are created and maintained.

最满意答案

两年半之后，因为我对这个问题很感兴趣：你确实可以使用名字很差的“objects”表找到给定键的列。

例如，请考虑下表

CREATE TABLE indextest (a INT, b INT); ALTER TABLE indextest ADD CONSTRAINT indextest_pk PRIMARY KEY (a); ALTER TABLE indextest ADD CONSTRAINT indextest_uq UNIQUE (a, b);

现在我们来看看哪些列属于indextest_uq ：

SELECT idxs.id AS index_id, columns.id AS column_id, tables.name AS table_name, columns.name AS column_name, columns.type AS column_type FROM idxs JOIN objects ON idxs.id=objects.id JOIN tables ON idxs.table_id=tables.id JOIN columns ON idxs.table_id=columns.table_id AND objects.name=columns.name WHERE idxs.name='indextest_uq';

这个查询的结果如下所示：

+----------+-----------+------------+-------------+-------------+ | index_id | column_id | table_name | column_name | column_type | +==========+===========+============+=============+=============+ | 6446 | 6438 | indextest | a | int | | 6446 | 6439 | indextest | b | int | +----------+-----------+------------+-------------+-------------+

很显然，通过扩展查询的SELECT部分，可以包含更多来自列表和tables表的信息。

Two and a half years later, because I was intrigued by the question: You can indeed find the columns for a given key using the poorly named "objects" table.

For example, consider the following table

CREATE TABLE indextest (a INT, b INT); ALTER TABLE indextest ADD CONSTRAINT indextest_pk PRIMARY KEY (a); ALTER TABLE indextest ADD CONSTRAINT indextest_uq UNIQUE (a, b);

Now let's find out which columns belong to indextest_uq:

SELECT idxs.id AS index_id, columns.id AS column_id, tables.name AS table_name, columns.name AS column_name, columns.type AS column_type FROM idxs JOIN objects ON idxs.id=objects.id JOIN tables ON idxs.table_id=tables.id JOIN columns ON idxs.table_id=columns.table_id AND objects.name=columns.name WHERE idxs.name='indextest_uq';

The result of this query looks like this:

+----------+-----------+------------+-------------+-------------+ | index_id | column_id | table_name | column_name | column_type | +==========+===========+============+=============+=============+ | 6446 | 6438 | indextest | a | int | | 6446 | 6439 | indextest | b | int | +----------+-----------+------------+-------------+-------------+

Obviously, more information from the columns and tables tables could be included by extending the SELECT part of the query.

更多推荐