Cannot create non-virtual view using DBIx::Class::Schema::Versioned

Question

I'm using DBIx::Class::Schema::Versioned and I want to create a new view as a table in the database. Setting __PACKAGE__->result_source_instance->is_virtual(1); makes correct use of the view definition from the schema (not creating the table), but when I set __PACKAGE__->result_source_instance->is_virtual(0); no table is created in the database and attempting to get the resultset throws a "relation does not exist" error (which was expected).

I couldn't find in the documentation any reference to how views should be created in DBIx::Class::Schema::Versioned. What happens is when I run the diffing between the old version that does not contain the view and the new version, the file sql/MyProject-Schema-38-PostgreSQL.sql contains the code for creating the view:

-- View: unlocked_pages
DROP VIEW unlocked_pages;
CREATE VIEW unlocked_pages ( page_id, username ) AS ...

but then the file which contains the difference between the 2 version appears empty and therefore when upgrading the schema nothing is done except adding a new version number in dbix_class_schema_versions. Those are the contents of sql/MyProject-Schema-37-38-PostgreSQL.sql:

-- Convert schema 'sql/MyProject-Schema-37-PostgreSQL.sql' to 'sql/MyProject-Schema-38-PostgreSQL.sql':;

-- No differences found;

I'm using postgresql and the definition in Schema.pm is

package MyProject::Schema;

# based on the DBIx::Class Schema base class
use base qw/DBIx::Class::Schema/;

use strict;
use warnings;

our $VERSION = 38;

# This will load any classes within
# MyProject::Schema::Result and MyProject::Schema::ResultSet (if any)
__PACKAGE__->load_namespaces();

__PACKAGE__->load_components(qw/Schema::Versioned/);

__PACKAGE__->upgrade_directory('../script/sql/');

1;

Any help is greatly appreciated!

Answer 1

I'm not certain I understand the question, but here is my take on it.

Creating a view is done for specific queries, hard coded SQL to achieve something you feel DBIx::Class does not provide, and make the results of those queries available to DBIx, and be able to use the usual result (set) classes.

It is not meant to correspond to a view in postgres. 'Updating' the view as far as DBIx is concerned means using the updated SQL in your view class - there is no need to store it in the database.

If you create a view in postgres that you want to use, you can, but I do not believe Schema::Version has anything to do with it. After all, those are simply two different ways of storing a query, aren't they?

If you want to version views, you may have to look at an entirely different route and use something like sqitch to version your sql files, however, your schema files should be in a version control system anyway.