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

Question

I am using DBIx :: Class :: Schema :: Versioned and I want to create a new table view in the database. Setting __PACKAGE__->result_source_instance->is_virtual(1); correctly uses the definition of a view from the schema (without creating a table), but when I set __PACKAGE__->result_source_instance->is_virtual(0); , a table is not created in the database and an attempt to get a result set causes an error "relation does not exist" (which was expected).

I could not find in the documentation any link to how views should be created in DBIx :: Class :: Schema :: Versioned. What happens when I run the difference between the old version that does not contain the view and the new version, the sql/MyProject-Schema-38-PostgreSQL.sql file contains code to create the view:

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

but then the file that contains the difference between version 2 looks empty, so when updating the scheme nothing is done except adding the new version number to dbix_class_schema_versions. This is the content 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 am using postgresql, and the definition is in Schema.pm

 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 much appreciated!

Answer 1

I'm not sure that I understand this question, but here is my approach to it.

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

It should not match the representation in postgres. The “update” of a view with regard to DBIx means using 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 that Schema :: Version has anything to do with it. After all, these are just two different ways of storing a request, aren't they?

If you want to view versions, you may have to look for a completely different route and use something like sqitch to version your sql files, however your schema files must be in version control system.