Add Materialized Views to the Preview Tables

[strivedi183]

First off, thanks for a great library

One thing that I think would be useful would be to preview any materialized views in /queries/new in the Preview Tables drop down.

I'm not sure off the top of my head if all SQL databases support Materialized Views (we use Postgres) but for the ones that do, I think would be useful. If this is something you would be interested in having in this library, I'd be happy to help out with it anyway I can or create a PR and any suggestions on how you would go about it or at least a starting point would be appreciate.

Thanks again!

[ankane]

Hey @strivedi183, I'd be happy for Blazer to have this feature. Here's where the list of tables and schema are fetched. If you need to write PostgreSQL specific code, you can use the postgresql? method to conditionally add them.

[ttilberg]

Some notes re: implementation for MS SQL Server for anyone tackling this in the future:

The functional equivalent to pg's materialized view in SQL Server is called an Indexed View. It is created by first creating a view, and then adding a clustered index to it. This causes SQL Server to store the results of the view similar to storing a table.

There are a some requirements that must be met, and become imposed when you do this. Specifically some keywords are banned from the select query (like outer joins, count(*), min/max, and a variety of others). The view must also be created WITH SCHEMABINDING so the underlying tables cannot be altered if the schema change would affect the view, until the view itself is modified. I don't know if these types of restrictions are common on the other rdbs, so I wanted to explicitly note them.

From a strict tsql perspective it looks simple enough:

CREATE VIEW dbo.v_dragon_ravage_report WITH SCHEMABINDING
AS
  SELECT dragon, zip_code, sum(damage) as score, population
  FROM dragons d
  JOIN pillaged_vilages v ON d.id = v.dragon_id
  JOIN zip_codes z ON z.zip_code = v.zip_code
  GROUP BY dragon, zip_code, population
GO

CREATE UNIQUE CLUSTERED INDEX IDX_cl_v_dragon_ravage_report1
    ON dbo.v_dragon_ravage_report(dragons, zip_code)
GO

[ankane]

Moving to ideas list in #24