Add support for database views

[matthewmcgarvey]

Database views are a great way to let Postgres do some of the data manipulation work before pulling the data into the app. There are several things in the way of allowing us to use Avram to interact with views:

• views will not have primary keys (if they do, they are from other tables)
• views will not have timestamp columns (if they do, they are from other tables)
• views are read-only but we always generate Operation logic
• SchemaEnforcer only verifies database tables

Connected:

• SchemaEnforcer doesn't know about views
• Tables with no primary key

[jwoertink]

I'm actually using Materialzed views already with Avram. Even though views don't need a primary key, if you just set one anyway, then the rest sets up like a normal model including the query class.

  class Release < BaseModel
    macro default_columns
      primary_key id : Int32
    end

    table :releases do
      column released : Bool
      column released_at : Time?
    end
end

ReleaseQuery.new.released(false).select_count

The tricky part with views is that generating the migration to create them.

DROP MATERIALIZED VIEW IF EXISTS releases;
CREATE MATERIALIZED VIEW releases AS
   SELECT a bunch of stuff

And then you create a function to refresh the data (which we can do in crystal now)

CREATE FUNCTION public.refreshallmaterializedviewsconcurrently(schema_arg text DEFAULT 'public'::text) RETURNS integer
    LANGUAGE plpgsql SECURITY DEFINER
    AS $$
      DECLARE
        r RECORD;
      BEGIN
        RAISE NOTICE 'Refreshing materialized view in schema %', schema_arg;
        FOR r IN SELECT matviewname FROM pg_matviews WHERE schemaname = schema_arg
        LOOP
          RAISE NOTICE 'Refreshing %.%', schema_arg, r.matviewname;
          EXECUTE 'REFRESH MATERIALIZED VIEW ' || schema_arg || '.' || r.matviewname || ' with data';
        END LOOP;
        RETURN 1;
      END
    $$;

Then somewhere we call something like select refreshallmaterializedviewsconcurrently(); I think, but I forget how we're currently running that.... Anyway, hopefully this gives some more clarity and context for anyone coming across this

[matthewmcgarvey]

I was just thinking regular views for now and then add materialized view support later on. Do you have the schema enforcer turned off or do materialized views show up as a regular table?

It's also a matter of those models are generating the operation class which it should not

[jwoertink]

I have schema enforcer turned off since that doesn't work with views yet.

[paulcsmith]

I think support is super close. You have to add skip_schema_enforcer in the given model to skip enforcement. For timestamps you can use skip_default_columns to skip the timestamp columns being added. I think we can still generate operation code and people can not use it :P.

So maybe we just need ability to skip primary key? Or maybe we document that you can add a fake primary key for a view?

To me MVP might be to document what we already hae since it seems super close. The only weirdness would be having to add a fake primary key 😬

[matthewmcgarvey]

One of the consequences of making Avram have optional primary keys is that it affects how we do joins and save/updates. Without what seems like major changes, we will have to disallow has many associations and operations entirely if a primary key is not supplied.