DBIC schema loader: how to skip columns

Question

I have a MySQL DB with multiple tables and views on those tables. A view limits what can be seen to a single customer's data (create view ... where customer_id = X). The Catalyst app will be talking to these views, not to the actual tables. The only difference between the view's columns and the underlying tables' ones is that the view lacks the customer_id column (i.e. to the application it seems like the current customer is the only one in the system).

The problem is, I cannot use DBIC Schema Loader to load the schema from the views, as they lack all the relations and keys. I have to load the schema from the base tables and then use it on the views. The problems is, I cannot get rid of that customer_id column. I need to get rid of it, because it is not present in the view that the application will be talking with.

I ended up using the filter_generated_code option to strip the unneeded bits away from the generated code, but then I get the following error during generation:

DBIx::Class::Schema::Loader::make_schema_at(): No such column customer_id
at /opt/merp/perl/lib/perl5/Catalyst/Helper/Model/DBIC/Schema.pm line 635

How can I have the loader skip certain columns at load time?

Answer 1

I'm not sure how you can get the loader to skip columns at load time, but you can remove them after load. For example, you can add something like this to any class which needs a column removed:

__PACKAGE__->remove_column('customer_id');

Answer 2

I ended up just leaving the column for what it is, so it is visible to the application code. The DB views and triggers ensure the application can only insert and select the currently set customer id. The only trick I employed was using filter_generated_code to replace the underlying table name with the view name (just stripping a leading underscore). This way I now have a script that does a show tables, filters out the views, dumps the structure into the DBIC classes, replacing the table name with the view name, looking somewhat like this:

exclude=`mysql -u user -ppassword -D db --execute='show tables' \
--silent --skip-column-names | egrep "^_" | sed "s/^_//g" | \
sed ':a;N;$!ba;s/\n/|/g'`

perl script/proj_create.pl model DB DBIC::Schema Proj::Schema \
create=static components=TimeStamp filter_generated_code=\
'sub { my ($type,$class,$text) = @_; $text =~ s/([<"])_/$1/g; return $text; } ' \
exclude="^($exclude)$" dbi:mysql:db 'user' 'password' quote_names=1 '{AutoCommit => 1}'

Answer 3

I'm not sure if there is an option for that in DBIC::Schema::Loader, the docs will tell you. If there isn't just generate the schema and then remove the column definition.

But besides that you seem to be missing a major feature of DBIC: ResultSet chaining. If you're using e.g. Catalyst you'd have an action that filters your ResultSet on the stash based on the customer id and all chained sub actions would only ever see the allowed rows.