Add introspection of special table and view types.

[Nick Pope]

Originally this ticket was aimed at PostgreSQL:

Looking at the documentation for ​pg_class there are the following types that we might be interested in introspecting for generation of models:

• r — ordinary table — obviously this is already supported.
• v — views — support was implemented in #29004.
• f — foreign table — being addressed by #29719.
• m — materialized views — could be handled much like #29004, mapping m to v.
• p — partitioned table — could be handled mapping p to t, but gets more complicated, read on below...

For partitioned tables, we are unlikely to want to generate models for all of the individual partitions, although this could be supported with an --include-partitions flag.

From ​this link we can see an example of output for partitioned tables:

 relid |      relname       | relsize | relispartition | relkind
-------+--------------------+---------+----------------+---------
 16410 | population         |       0 | f              | p
 16417 | population_s       |    8192 | t              | r
 16424 | population_t       |       0 | t              | p
 16431 | population_t_10_20 |    8192 | t              | r
 16445 | population_t_20_30 |    8192 | t              | r

On PostgreSQL 10+ we need to filter on NOT relispartition to ignore all of the partitions and only include the parent (which is the table that is usually interacted with).

Regarding support for all backends:

Feature support by backend:

	PostgreSQL	MySQL	Oracle	SQLite
Partitions	✔	✔	✔	✘
Foreign Tables	✔	✘	✘	✘
Materialized Views	✔	✘	✔	✘

It looks partitions can be introspected using INFORMATION_SCHEMA.PARTITIONS for ​MySQL and there are other options for ​Oracle.

For introspection of materialized views, I think we could use USER_MVIEWS for ​Oracle.

[Nick Pope]

​PR

[Tim Graham <timograham@…>]

In bf8b625a:

Refs #29722 -- Added introspection of materialized views for PostgreSQL.

[Nick Pope]

​PR for partitions support for PostgreSQL updated.

[Tim Graham <timograham@…>]

In ebd27062:

Refs #29722 -- Added introspection of partitions for PostgreSQL.

[Tim Graham]

The ticket remains open for adding support for databases besides PostgreSQL.

[Mariusz Felisiak]

​PR with introspection of materialized views for Oracle.

[GitHub <noreply@…>]

In f091ea3:

Refs #29722 -- Added introspection of materialized views for Oracle.

Thanks Tim Graham for the review.

[Nick Pope]

The main remaining item to be supported here was partitions for MySQL/MariaDB. In practice this isn't required, however.
This is because they behave differently to partitions in PostgreSQL:

• In PostgreSQL, partitions are "normal" tables and needed to be excluded from introspection by default.
• In MySQL/MariaDB, partitions are only listed in INFORMATION_SCHEMA.PARTITIONS so are already excluded by default.
• In PostgreSQL it is possible to query a partition directly with no special syntax (e.g. SELECT * FROM child) so it may still be helpful to introspect partitions.
• In MySQL/MariaDB you must use special syntax to directly access a partition (e.g. SELECT * FROM parent PARTITION (child)) so it wouldn't be useful to introspect them.

Here is a dump of some SQL written while checking whether this could be supported (for reference):

CREATE TABLE inspectdb_partition_parent (date date NOT NULL)
PARTITION BY LIST (quarter(date)) (
    PARTITION inspectdb_partition_child VALUES IN (1, 2, 3, 4)
);
CREATE VIEW inspectdb_view AS (SELECT * FROM inspectdb_partition_parent);

INSERT INTO inspectdb_partition_parent values (now());
SELECT date FROM inspectdb_partition_child;  -- Table 'db_282816758.inspectdb_partition_child' doesn't exist
SELECT date FROM inspectdb_partition_parent;  -- 2021-01-28
SELECT date FROM inspectdb_partition_parent PARTITION (inspectdb_partition_child);  -- 2021-01-28

-- Implementation of DatabaseIntrospection.get_table_list() to support partitions:

SELECT table_name, CASE WHEN table_type = 'VIEW' THEN 'v' ELSE 't' END
FROM information_schema.tables 
WHERE table_schema NOT IN ('information_schema', 'mysql', 'performance_schema')
UNION
SELECT partition_name, 'p'
FROM information_schema.partitions
WHERE table_schema NOT IN ('information_schema', 'mysql', 'performance_schema') AND partition_name IS NOT NULL;

-- inspectdb_partition_parent   t
-- inspectdb_view               v
-- inspectdb_partition_child    p

Also remaining was support for partitions with Oracle. This looks very complex compared to PostgreSQL/MySQL.
As mentioned above, the main issue was that introspection for PostgreSQL resulted in tables being generated for partitions as partitions are treated as normal tables - this was undesirable.