dolthub / dolthub/dolt

Use `as of` clause to query `information_schema` history

Open
#5,663 0 comments 0 reactions 1 assignee Claimed by @zachmu View on GitHub
enhancement sql
Dominant language
Go
Stars
24.4k
Forks
873
Avg merge
1d 5h
Merged PRs (30d)
108

Description

Right now there's no way to use SQL to get foreign key/indexes for a revision in a database. It has a `table_schema` column but it only shows the revision database that's currently being used
```sql
mysql> use testtest;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select table_schema, table_name, index_type from information_schema.statistics where table_name="t1";
+--------------+------------+------------+
| TABLE_SCHEMA | TABLE_NAME | INDEX_TYPE |
+--------------+------------+------------+
| testtest | t1 | BTREE |
+--------------+------------+------------+
1 row in set (0.04 sec)

mysql> use `testtest/main`;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select table_schema, table_name, index_type from information_schema.statistics where table_name="t1";
+---------------+------------+------------+
| TABLE_SCHEMA | TABLE_NAME | INDEX_TYPE |
+---------------+------------+------------+
| testtest | t1 | BTREE |
| testtest/main | t1 | BTREE |
+---------------+------------+------------+
2 rows in set (0.04 sec)

mysql> use testtest;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select table_schema, table_name, index_type from information_schema.statistics where table_name="t1" and table_schema="testtest/main";
Empty set (0.04 sec)
```

Would be helpful to use an AS OF clause to query `information_schema` at different database revisions, like:
```
select * from information_schema.statistics as of "testtest/main" where table_name="t1" ;
```

This would be useful for the hosted workbench, where we display foreign key and index information for each branch

Contributor guide

No contributing guide indexed for this repository

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.