Support for MySQL views

[djjudas21]

Hi there. I have an existing MySQL+Perl command-line app with a complex schema with many relationships (some of the queries have up to 10 JOINs). Roughly speaking, everything the user sees comes from an SQL view rather than the underlying table, and the logic in the code manipulates the tables. I have recently decided to redevelop my app into something web-based (because the CLI app is somewhat hostile) and wish to use Mojolicious+Yancy. I'm OK at Perl and SQL but completely new to web development.

However, Yancy seems to break when I specify an SQL view in the schema definition, such as in this example where CAMERA is a table and choose_camera is a view that joins CAMERA to other tables and makes a pretty list that can be displayed to the user verbatim.

        $self->plugin( 'Yancy', {
                backend => { Mysql => Mojo::mysql->new("mysql://$config->{db_username}:$config->{db_password}\@$config->{db_hostname}/$config->{db_schema}") },
                read_schema => 1,
                schema => {
                        CAMERA => {
                                title => 'Cameras',
                                description => 'Here are some cameras',
                        },
                        choose_camera => {
                                title => 'Choose a Camera',
                                description => 'List of cameras generated from a view',
                                'x-id-field' => 'id',
                        },
                },
        });

Running with that schema throws the following error:

Can't load application from file "/home/jonathan/git/photodb-backend/script/photo_db": ID field missing in properties for schema 'choose_camera', field 'id'. Add x-id-field to configure the correct ID field name, or add x-ignore to ignore this schema. at /usr/local/share/perl5/Mojolicious/Plugin/Yancy.pm line 639.
Compilation failed in require at (eval 95) line 1.

I've set x-id-field as suggested but still no luck, so I guess that means Yancy can't understand SQL views?

I also note in #25 that you're planning relationship support for v2. I think this will go a long way towards solving my problem by avoiding the use of views by modelling the relationships directly, hopefully displaying the tables with the related columns filled in, and also providing some kind of lookup feature when adding new records?

Is what I am trying to achieve too complex for Yancy alone, and should I drop the idea of trying to use the Yancy editor and instead set up my own Mojolicious routes/controllers with Yancy helpers? I'm a bit hesitant before going down this path as I will presumably have to reinvent quite a few wheels along the way.

Thanks, Jonathan

[preaction]

What is the primary key (or some other unique key) on the choose_camera table? The error is saying there is no column named id in that table, so Yancy doesn't know how to get individual rows. Yancy does not yet support unique keys over multiple columns (though that'd be a wonderful feature).

The relationship support will start out with being able to select foreign keys from a dropdown list. As the editor learns how to handle more complex data structures, then yes, I'd like to be able to indicate that related data should be edited from the parent table (and then completely ignore the child table).

[djjudas21]

choose camera is a view so there is no PK as such, but the column id is simply selected from CAMERA.camera_id, which is a PK. Yancy doesn't seem to pick it up either automatically, or when I set 'x-id-field' => 'id'

MariaDB [photography]> desc choose_camera;
+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| id       | int(11)      | NO   |     | 0       |       |
| opt      | varchar(140) | YES  |     | NULL    |       |
| mount_id | int(11)      | YES  |     | NULL    |       |
+----------+--------------+------+-----+---------+-------+
3 rows in set (0.002 sec)

MariaDB [photography]> show create table choose_camera\G
*************************** 1. row ***************************
                View: choose_camera
         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`photography`@`%` SQL SECURITY DEFINER VIEW `choose_camera` AS select `CAMERA`.`camera_id` AS `id`,concat(`MANUFACTURER`.`manufacturer`,' ',`CAMERAMODEL`.`model`,if(`CAMERA`.`serial`,concat(' (#',`CAMERA`.`serial`,')'),'')) AS `opt`,`CAMERAMODEL`.`mount_id` AS `mount_id` from ((`CAMERA` join `CAMERAMODEL` on(`CAMERA`.`cameramodel_id` = `CAMERAMODEL`.`cameramodel_id`)) join `MANUFACTURER` on(`CAMERAMODEL`.`manufacturer_id` = `MANUFACTURER`.`manufacturer_id`)) where `CAMERA`.`own` = 1 order by concat(`MANUFACTURER`.`manufacturer`,' ',`CAMERAMODEL`.`model`) collate utf8mb4_general_ci
character_set_client: utf8mb4
collation_connection: utf8mb4_general_ci
1 row in set (0.001 sec)

[preaction]

Okay. The read_schema parser might be getting confused with the VIEW. It's not exactly robust code :). It looks like the read schema parser would have to detect the view and then either look at the underlying table to determine what the primary key is, or require x-id-field for views.

Setting x-id-field should also be giving a hint to the read schema parser, so that's a bug (well, an oversight). Fixing that would let you get on with building things.