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

When I played with your CMS and demo project I got errors when trying to add new user:

Error validating new item in schema "users": /plugin: Expected string - got null

Error validating new item in schema "users":  (/created)

How to debug such errors ?

Made a demo repository https://github.com/pavelsr/yancy_passw_auth_problem, please see

The Yancy::Controller::Yancy::Calendar contains actions to display items containing calendar event information:

This controller will have all the actions from Yancy::Controller::Yancy and the following new ones:

• month - A month view of events
  • Takes the following configuration:
    • collection - The collection to view
    • start_field - The field in the item containing the event's start date/time in ISO8601 format
    • end_field - (optional) The field in the item containing the event's end date/time in ISO8601 format
    • year - The year to view (four-digit year)
    • month - The month to view (number, 01-12)
  • Creates the following stash values for templates
    • days - An array of hashes of day information including the following keys:
      • date - A Time::Piece object containing the date information, including year, month, and day
      • items - The events that start, end, or cross this day
• week - A week view of events
  • Takes the following configuration
    • collection - The collection to view
    • start_field - The field in the item containing the event's start date/time in ISO8601 format
    • end_field - (optional) The field in the item containing the event's end date/time in ISO8601 format
    • year - The year to view (four-digit year)
    • month - The month to view (number, 01-12)
    • day - The day to start showing the week of events (number, 00-31)
  • Creates the following stash values for templates
    • hours - An array of hashes of hour information including the following keys:
      • hour - A Time::Piece object containing the date information, including year, month, day, and hour
      • items - The events that start, end, or cross this hour
• day - A day view of events
  • Takes the following configuration
    • collection - The collection to view
    • start_field - The field in the item containing the event's start date/time in ISO8601 format
    • end_field - (optional) The field in the item containing the event's end date/time in ISO8601 format
    • year - The year to view (four-digit year)
    • month - The month to view (number, 01-12)
    • day - The day to view (number, 00-31)
  • Creates the following stash values for templates
    • hours - An array of hashes of hour information including the following keys:
      • hour - A Time::Piece object containing the date information, including year, month, day, and hour
      • items - The events that start, end, or cross this hour

An example application should be added to eg/ to demonstrate this controller.

Is there a way to show specified item's edit section by sending some parameter from url.
for example /yancy/items/1/edit or via hash fragment/yancy/items/1#edit={id}

This can be very helpful feature, like I can just put the yancy edit url from the item details page.

See https://metacpan.org/changes/distribution/JSON-Validator#L14 (FYI: @jhthorsen)

This means Yancy's test suite currently fails:

#   Failed test 'Mojolicious/Plugin/Yancy.pm loaded ok'
#   at t/00-compile.t line 53.
#          got: '512'
#     expected: '0'
Can't locate JSON/Validator/OpenAPI.pm in @INC (you may need to install the JSON::Validator::OpenAPI module) (@INC contains: ... .) at /home/cpansand/.cpan/build/2019010609/Yancy-1.020-1/blib/lib/Mojolicious/Plugin/Yancy.pm line 408.
BEGIN failed--compilation aborted at /home/cpansand/.cpan/build/2019010609/Yancy-1.020-1/blib/lib/Mojolicious/Plugin/Yancy.pm line 408.
Compilation failed in require at -e line 1.
# Looks like you failed 1 test of 10.
t/00-compile.t ............... 
Dubious, test returned 1 (wstat 256, 0x100)
Failed 1/10 subtests 
	(less 1 skipped subtest: 8 okay)
... (etc)...

Although Makefile.PL says "MIN_PERL_VERSION" => "5.010", t/lib/Local/Test.pm at line 61 uses a postfix dereference as ->@[ only made stable in v5.24:

{ rows => [ $list->@[1..$#$list] ], total => scalar @$list },

Suggested resolution: Either the line should be rewritten or the minimum Perl version should be v5.24.

Hello Doug,

I'd like to extend Dbic backend class to accept a connected dbic instance (weakend) to bind to. Would that be acceptable for your goals?

Best greets,

Mario

Continued from #38: The types in JSON schema can be an array. Yancy uses this to allow properties to be null. However, OpenAPI does not allow this (either in v2 or v3). In v3, OpenAPI adds a nullable property, but in v2 all properties are assumed to be nullable unless required.

The Yancy collections configuration (and through it, the read_schema schemas) are JSON schemas. The Yancy editor works with OpenAPI schemas, not JSON schemas. To fix this problem, Yancy will need to translated the JSON schemas to OpenAPI schemas when building the editor's OpenAPI schema.

We also need to make sure the documentation is clear: read_schema and collections are JSON schema (and what version we support), openapi and the editor are OpenAPI (and what version we support).

Suggestion: Add a note under "Data Collections" in Yancy.pm that, both for collections and for collection fields, the description values are treated as Markdown text and run through the marked filter for display. An example with unindent would be further enlightening.

On a few of my smokers the test suite fails:

    #   Failed test 'User is not authorized for API spec'
    #   at t/plugin/auth/basic.t line 56.
    #          got: '404'
    #     expected: '401'

...

    #   Failed test 'User is not authorized to delete a user'
    #   at t/plugin/auth/basic.t line 76.
    #          got: '404'
    #     expected: '401'
    # Looks like you failed 6 tests of 21.

#   Failed test 'unauthenticated user cannot admin'
#   at t/plugin/auth/basic.t line 80.
# Looks like you failed 1 test of 4.
t/plugin/auth/basic.t ........ 
Dubious, test returned 1 (wstat 256, 0x100)
Failed 1/4 subtests 

Probably this happens if Mojolicious::Plugin::OpenAPI is too old (i.e. 1.16 seems to be too old). Statistical analysis:

****************************************************************
Regression 'mod:Mojolicious::Plugin::OpenAPI'
****************************************************************
Name           	       Theta	      StdErr	 T-stat
[0='const']    	      0.0000	      0.0000	   0.51
[1='eq_1.16']  	      0.0000	      0.0000	   1.09
[2='eq_1.19']  	      1.0000	      0.0000	3267025968841381.50
[3='eq_1.22']  	      1.0000	      0.0000	4567467586025256.00

R^2= 1.000, N= 46, K= 4
****************************************************************

It might be more honest to say I can't seem to them together. I'm attempting to migrate from Auth::Basic to Auth:Password (you can see my old config in the comment), and I keep getting an error message. I can't / don't want to use read_schema => 1 because it pukes on all of my mysql JSON columns.

My configuration:

plugin Yancy => {
  backend => app->config->{dsn},
  read_schema => 0,
  schema => {
    admins => {
      'x-id-field' => 'username',
      properties => {
        username => { type => 'string' },
        password => { type => 'string' },
      },
    },
  }
};

# app->yancy->plugin('Auth::Basic' => {
#   collection => 'admins',
#   password_digest => { type => 'SHA-512' },
# });

app->yancy->plugin('Auth::Password' => {
  schema => 'admins',
  username_field => 'username',
  password_field => 'password',
  password_digest => {
    type => 'SHA-512',
  },
});

My error message:

Can't load application from file "/home/www/sites/[...]/app/puravida.pl": Can't use an undefined value as an ARRAY reference at /opt/perl5/perls/puravida/lib/site_perl/5.24.3/Yancy/Plugin/Auth/Password.pm line 314.

Is it bug or do I need to make changes to the core Yancy config?

I copied the generated api spec (/yanya/api) into Swagger online editor and go some errors:

Semantic error at definitions.Project.properties.description.type
Schema "type" key must be a string
Jump to line 12
Schema error at info.version
should be string
Jump to line 48
Semantic error at paths./Project.get.parameters.0.$ref
$ref values must be RFC3986-compliant percent-encoded URIs
Jump to line 70
Semantic error at paths./Project.get.parameters.1.$ref
$ref values must be RFC3986-compliant percent-encoded URIs
Jump to line 71
Semantic error at paths./Project.get.parameters.2.$ref
$ref values must be RFC3986-compliant percent-encoded URIs
Jump to line 72

The generated spec looks like this:

basePath: /yancy/api
consumes:
  - application/json
definitions:
  Project:
    properties:
      abbreviation:
        type: string
        x-order: 3
      description:
        type:
          - string
          - 'null'
        x-order: 4
      id:
        type: integer
        x-order: 1
      name:
        type: string
        x-order: 2
      projectnumber:
        type: string
        x-order: 5
    required:
      - name
      - abbreviation
      - projectnumber
    type: object
  _Error:
    properties:
      errors:
        items:
          properties:
            message:
              description: Human readable description of the error
              type: string
            path:
              description: JSON pointer to the input data where the error occur
              type: string
          required:
            - message
        type: array
    title: OpenAPI Error Object
    type: object
host: 'localhost:3000'
info:
  title: Yancy
  version: 1
parameters:
  $limit:
    description: The number of items to return
    in: query
    name: $limit
    type: integer
  $offset:
    description: The index (0-based) to start returning items
    in: query
    name: $offset
    type: integer
  $order_by:
    description: 'How to sort the list. A string containing one of "asc" (to sort in ascending order) or "desc" (to sort in descending order), followed by a ":", followed by the field name to sort by.'
    in: query
    name: $order_by
    pattern: '^(?:asc|desc):[^:,]+$'
    type: string
paths:
  /Project:
    get:
      parameters:
        - $ref: '#/parameters/$limit'
        - $ref: '#/parameters/$offset'
        - $ref: '#/parameters/$order_by'
        - description: 'Filter the list by the id field. By default, looks for rows containing the value anywhere in the column. Use ''*'' anywhere in the value to anchor the match.'
          in: query
          name: id
          type: integer
        - description: 'Filter the list by the projectnumber field. By default, looks for rows containing the value anywhere in the column. Use ''*'' anywhere in the value to anchor the match.'
          in: query
          name: projectnumber
          type: string
        - description: 'Filter the list by the name field. By default, looks for rows containing the value anywhere in the column. Use ''*'' anywhere in the value to anchor the match.'
          in: query
          name: name
          type: string
        - description: 'Filter the list by the description field. By default, looks for rows containing the value anywhere in the column. Use ''*'' anywhere in the value to anchor the match.'
          in: query
          name: description
          type: string
        - description: 'Filter the list by the abbreviation field. By default, looks for rows containing the value anywhere in the column. Use ''*'' anywhere in the value to anchor the match.'
          in: query
          name: abbreviation
          type: string
      responses:
        '200':
          description: List of items
          schema:
            properties:
              items:
                description: This page of items
                items:
                  $ref: '#/definitions/Project'
                type: array
              total:
                description: The total number of items available
                type: integer
            required:
              - items
              - total
            type: object
        '400':
          description: Default response.
          schema:
            $ref: '#/definitions/_Error'
        '401':
          description: Default response.
          schema:
            $ref: '#/definitions/_Error'
        '404':
          description: Default response.
          schema:
            $ref: '#/definitions/_Error'
        '500':
          description: Default response.
          schema:
            $ref: '#/definitions/_Error'
        '501':
          description: Default response.
          schema:
            $ref: '#/definitions/_Error'
        default:
          description: Unexpected error
          schema:
            $ref: '#/definitions/_Error'
      x-mojo-to:
        action: list_items
        collection: Project
        controller: 'Yancy::API'
    post:
      parameters:
        - in: body
          name: newItem
          required: true
          schema:
            $ref: '#/definitions/Project'
      responses:
        '201':
          description: Entry was created
          schema:
            $ref: '#/definitions/Project/properties/id'
        '400':
          description: Default response.
          schema:
            $ref: '#/definitions/_Error'
        '401':
          description: Default response.
          schema:
            $ref: '#/definitions/_Error'
        '404':
          description: Default response.
          schema:
            $ref: '#/definitions/_Error'
        '500':
          description: Default response.
          schema:
            $ref: '#/definitions/_Error'
        '501':
          description: Default response.
          schema:
            $ref: '#/definitions/_Error'
        default:
          description: Unexpected error
          schema:
            $ref: '#/definitions/_Error'
      x-mojo-to:
        action: add_item
        collection: Project
        controller: 'Yancy::API'
  '/Project/{id}':
    delete:
      description: Delete a single item
      responses:
        '204':
          description: Item was deleted
        '400':
          description: Default response.
          schema:
            $ref: '#/definitions/_Error'
        '401':
          description: Default response.
          schema:
            $ref: '#/definitions/_Error'
        '404':
          description: Default response.
          schema:
            $ref: '#/definitions/_Error'
        '500':
          description: Default response.
          schema:
            $ref: '#/definitions/_Error'
        '501':
          description: Default response.
          schema:
            $ref: '#/definitions/_Error'
        default:
          description: Unexpected error
          schema:
            $ref: '#/definitions/_Error'
      x-mojo-to:
        action: delete_item
        collection: Project
        controller: 'Yancy::API'
        id_field: id
    get:
      description: Fetch a single item
      responses:
        '200':
          description: Item details
          schema:
            $ref: '#/definitions/Project'
        '400':
          description: Default response.
          schema:
            $ref: '#/definitions/_Error'
        '401':
          description: Default response.
          schema:
            $ref: '#/definitions/_Error'
        '404':
          description: Default response.
          schema:
            $ref: '#/definitions/_Error'
        '500':
          description: Default response.
          schema:
            $ref: '#/definitions/_Error'
        '501':
          description: Default response.
          schema:
            $ref: '#/definitions/_Error'
        default:
          description: Unexpected error
          schema:
            $ref: '#/definitions/_Error'
      x-mojo-to:
        action: get_item
        collection: Project
        controller: 'Yancy::API'
        id_field: id
    parameters:
      - description: The id of the item
        in: path
        name: id
        required: true
        type: string
        x-mojo-placeholder: '*'
    put:
      description: Update a single item
      parameters:
        - in: body
          name: newItem
          required: true
          schema:
            $ref: '#/definitions/Project'
      responses:
        '200':
          description: Item was updated
          schema:
            $ref: '#/definitions/Project'
        '400':
          description: Default response.
          schema:
            $ref: '#/definitions/_Error'
        '401':
          description: Default response.
          schema:
            $ref: '#/definitions/_Error'
        '404':
          description: Default response.
          schema:
            $ref: '#/definitions/_Error'
        '500':
          description: Default response.
          schema:
            $ref: '#/definitions/_Error'
        '501':
          description: Default response.
          schema:
            $ref: '#/definitions/_Error'
        default:
          description: Unexpected error
          schema:
            $ref: '#/definitions/_Error'
      x-mojo-to:
        action: set_item
        collection: Project
        controller: 'Yancy::API'
        id_field: id
produces:
  - application/json
schemes:
  - http
swagger: '2.0'
x-bundled: {}

Especially the unescaped $limit and thelike looks like a yancy bug?

Hi,

I use Yancy in a Mojolicious::Lite application through Mojolicious::Plugin::Yancy. The SQLIte database schema is :

CREATE TABLE votes (
    id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
    mood INT NOT NULL,
    date DATETIME NOT NULL, 
    active BOOLEAN DEFAULT (1) NOT NULL
);

And the app configuration is :

plugin Yancy => {
    backend => "sqlite:./data/rerb.sqlite",
    read_schema => 1,
    collections => {
        votes => {
            'x-list-columns' => [ 'id', 'mood', 'date', 'active' ],
        }
    }
};

Everything works fine this way, as long the x-list-columns parameters is available: if I remove it, the web page only display a list of id fields, but other fields are not display, despite they are returned by the Yancy API.

I report this, dunno if this is a bug or the expected behavior with maybe a lack of documentation? I assumed read_schema => 1 was able to "auto-list" all the collection fields, so that's why I think this behavior seems strange: I mean, by reading synopsis and various parts of the documentations (and trying different things many times), I was always having this problem and never found a place asking to explicitly set x-list-columns but always use read_schema.

If this is not a bug and that x-list-columns is required, I guess it should be specified more evidently. I volunteer to send a documentation PR.

Mojolicious 8.06
Yancy 1.018
Mojo::SQLite 3.001

Thanks!

I'm trying to install Yancy, but tests keep failing. Every test (it seems) in dbic.t is failing because it's getting 0 as a string, and expects to get 0 as a number.

Perhaps I'm missing a dependency that slipped under the rug?

Hi,looking forward to trying out Yancy but got the following error:

t/plugin/auth/basic.t ........ 1/? 
    #   Failed test 'User is not authorized for API spec'
    #   at t/plugin/auth/basic.t line 119.
    #          got: '500'
    #     expected: '401'

    #   Failed test 'Content-Type is similar'
    #   at t/plugin/auth/basic.t line 119.
    #                   'text/html;charset=UTF-8'
    #     doesn't match '(?^:^application/json)'
    # Looks like you failed 2 tests of 21.

#   Failed test 'unauthenticated user cannot admin'
#   at t/plugin/auth/basic.t line 143.

    #   Failed test 'User is not authorized for API spec'
    #   at t/plugin/auth/basic.t line 272.
    #          got: '500'
    #     expected: '401'

    #   Failed test 'Content-Type is similar'
    #   at t/plugin/auth/basic.t line 272.
    #                   'text/html;charset=UTF-8'
    #     doesn't match '(?^:^application/json)'
    # Looks like you failed 2 tests of 32.
t/plugin/auth/basic.t ........ 6/? 
#   Failed test 'standalone plugin'
#   at t/plugin/auth/basic.t line 306.
# Looks like you failed 2 tests of 6.
t/plugin/auth/basic.t ........ Dubious, test returned 2 (wstat 512, 0x200)
Failed 2/6 subtests 
t/plugin/form/bootstrap4.t ... ok   
t/standalone.t ............... ok   
t/util.t ..................... ok   

Test Summary Report
-------------------
t/plugin/auth/basic.t      (Wstat: 512 Tests: 6 Failed: 2)
  Failed tests:  2, 6
  Non-zero exit status: 2
Files=19, Tests=58, 28 wallclock secs ( 0.22 usr  0.06 sys + 26.18 cusr  1.27 csys = 27.73 CPU)
Result: FAIL
Failed 1/19 test programs. 2/58 subtests failed.
make: *** [test_dynamic] Error 255

I would be happy to try debug this myself if you can point me in the right direction.
Thanks,
Paul

Now that we have a single standard Auth plugin API, we can have a single role-based authorization plugin that covers all authentication.

Create a Yancy::Plugin::Access::Role plugin. This plugin should take the following configuration:

• collection - The collection that stores the role to user mapping
• userid_field - The field that stores the user ID in the role collection
• role_field - The field that stores the role name

Role collections can have other fields, like description or ancillary data. Once data relationships work in the editor, we can have the user ID and role name fields be drop-down boxes linked to the users table and a main roles table (so that users do not mistype role names).

This plugin should add a single helper: yancy.access.require_role. The helper should take an array reference of role names. If the current user does not have one of the given roles, the authorization check should fail and the user should be shown the Unauthorized page.

Then, the editor should get a new configuration: editor.require_role (alongside editor.require_user). This should take an array reference of roles. If this is specified, the user's role should be checked with the yancy.access.require_role helper. If the current user does not have one of the given roles, the authorization check should fail and the user should be shown the Unauthorized page.

In the future we may add other ways to restrict access (Github Org membership, LDAP groups, etc...), so our API should be simple.

I wrote a small example application with Mojo::SQLite, that follows pretty much the Yancy standard receipt:

use Mojolicious::Lite;
use Mojo::SQLite;
use Test::More;
use Test::Mojo;
use Mojo::File qw/tempfile/;

my $db_file = tempfile;

helper sqlite => sub {
  state $sql = Mojo::SQLite->new->from_filename($db_file);
};

plugin Yancy => {
  backend => {
    sqlite => app->sqlite
  },
  collections => {
    people => {
      type => 'object',
      properties => {
        id => {
          type => 'integer',
          readOnly => 1,
        },
        name => {
          type => 'string'
        }
      },
      example => {
        name => 'Philip J. Fry'
      }
    },
  }
};

# Load schema
app->sqlite->migrations->from_string(<<SCHEMA)->migrate;
-- 1 up
CREATE TABLE people (
    id SERIAL,
    name VARCHAR NOT NULL
);
-- 1 down
drop table people;
SCHEMA

get '/' => sub {
  my $c = shift;
  $c->render(inline => <<'TEMPLATE');
% my @people = app->yancy->list('people');
<h1>People!</h1>
<ul>
  % for my $person (@people) {
    <li><%= $person->{name} %></li>
  % }
</ul>
TEMPLATE
};

my $t = Test::Mojo->new;

$t->get_ok('/')
  ->status_is(200)
  ->text_is('h1', 'People!')
  ->element_exists_not('ul > li');

$t->get_ok('/yancy')
  ->status_is(200)
  ->text_is('head > title', 'Yancy CMS');

$t->post_ok('/yancy/api/people' => json => {
  name => 'akron'
})->status_is(200)
  ->content_is('');

$t->get_ok('/')
  ->status_is(200)
  ->element_exists('ul > li')
  ->text_is('ul > li', 'akron');

done_testing;

When posting the object to /yancy/api/people in 0.017 (replicating the editor behaviour), the server status is 500 and the response is {"errors":[{"message":"Expected object - got null.","path":"\/"}],"status":500}. However, the data is correctly stored and can be retrieved.
Am I doing something wrong?

I have some troubles with MySQL tables.
Example collection:

collections => {
	users => {
		properties => {
			username => {
				type => 'string',
			},
			password => {
				type => 'string',
			},
			email => {
				type => [ 'string', 'null' ],
				format => 'email',
			},
			phone => {
				type => 'string',
				#format => 'tel',
			},
			created => {
				type => 'string',
				format => 'date-time',
			},
	},
}

Few problems arise.

1. If there is an empty email field, I get error: OpenAPI >>> GET /yancy/api/users [{"message":"anyOf failed: Does not match email format."..... in console and 500 in browser. The email is not required and type allows null. I'm not sure if this can be solved on Yancy side?
2. Not MySQL related, but as the POD suggests, there is tel format. It seems this is a mistake. I get Format rule for 'tel' is missing at .../app/local/lib/perl5/JSON/Validator.pm line 615. error on the console and the field is not rendered on the web.
3. The created column is DATETIME in MySQL. If I set the format to date-time in Yancy config, I get OpenAPI >>> GET /yancy/api/users [{"message":"Does not match date-time format." ..... in console and 500 in browser. The OpenAPI specification says: the date-time notation as defined by RFC 3339, section 5.6, for example, 2017-07-21T17:32:28Z. I cannot find a way to convert MySQL DATETIME to RFC3339 DATE-TIME. May be a list_items filter would be appropriate for such cases? So I can define filter to convert a DB field to something that OpenAPI will recognize later. As far as I understand, the filters now work only for the create and delete operations, not for select. I'm not sure if I'm missing something here or if this approach is correct.

Any suggestions are welcome. Thanks in advance.

As promised, this is my attempt to spell out the ideas we chatted about on IRC.

Motivation

As also discussed previously, for me this is driven by wanting to be start with an OpenAPI spec, e.g. the OpenAPI spec for https://github.com/gothinkster/realworld with minimal extra extension info (https://github.com/mohawk2/SQL-Translator-Parser-OpenAPI/blob/master/t/06-corpus.json.overlay - the password thing should probably be handled with e.g. your "filter" concept on the password instead).

Then I'd be able to generate a database with SQL::Translator::Parser::OpenAPI. Then hand the spec to Yancy, and get REST API, and even web service with edit functionality.

Current shape of Yancy

My understanding of the current shape of things in Yancy is it works roughly:

backend -> bespoke-spec
     [Yancy::Backend#read_schema], goes into stash's "collections" item
[OR: manually supply bespoke-spec)
bespoke-spec -> OpenAPI
     [Mojolicious::Plugin::Yancy::_build_openapi_spec], [Yancy::Controller::Yancy::API]
bespoke-spec -> form
    [Yancy::Plugin::Form] using the stash's "collections" item

One characteristic of this is that the bespoke-spec features "x-order" values so that parameters are correctly ordered. OpenAPI uses a sequence rather than a map for specifying parameters, so using OpenAPI operations as the driver for the forms eliminates the need for this, as that information would live in the parameters spec of the mutation operation. The form could then use this instead.

Proposed new shape of working

backend -> openapi-spec
[OR: manually supply openapi-spec)
openapi-spec -> bespoke-spec # still needed?
openapi-spec -> form # reads the input parameters for the POST routes

One thing I'd like to change is from having definitions of ${name}Item and ${name}Array to just ${name}, and refer to the arrays "natively" with just the OpenAPI bit for that ({ type => 'array', items => { '$ref' => "/definitions/$name" } } doesn't feel onerous). I don't feel like having the separate definition for array adds anything. This then leaves the "definitions" quite idiomatically similar to your current bespoke-spec concept.

Parameterising forms

You've also mentioned you'd like form plugins to be able to receive a JSON schema as an input so it doesn't need to look things up from the config. Maybe that's compatible with this? Certainly I'd favour parameterising more things!

The possibility of no-code

Another thing I think would add value would be to be able to just specify a backend in the yancy.conf, also openapispec. This then offers the promise of simply being able to start up yancy with the right configuration, and an openapi spec configured in a way that JSON::Validator::OpenAPI likes (which can include URLs), without writing one's own code at all.

Conclusion

Does this make sense? Would you prefer to keep your current "shape" and enable these things via extra plugins?

The test suite started to fail on my smokers. It seems that the newest Mojolicious::Plugin::OpenAPI version (or maybe the newest Mojolicious version) is causing the problem. Statistical analysis:

****************************************************************
(3)
****************************************************************
Regression 'mod:Mojolicious::Plugin::OpenAPI'
****************************************************************
Name           	       Theta	      StdErr	 T-stat
[0='const']    	      1.0000	      0.0000	18541236012653060.00
[1='eq_1.30']  	      0.0000	      0.0000	   1.92
[2='eq_2.00']  	     -1.0000	      0.0000	-16736634826245860.00

R^2= 1.000, N= 120, K= 3
****************************************************************

****************************************************************
(5)
****************************************************************
Regression 'mod:Mojolicious'
****************************************************************
Name           	       Theta	      StdErr	 T-stat
[0='const']    	      1.0000	      0.0930	  10.75
[1='eq_7.88']  	     -0.0000	      0.1019	  -0.00
[2='eq_7.91']  	     -0.0000	      0.1316	  -0.00
[3='eq_7.93']  	     -0.0000	      0.0940	  -0.00
[4='eq_7.94']  	     -0.0000	      0.0951	  -0.00
[5='eq_8.0']   	     -0.0000	      0.1316	  -0.00
[6='eq_8.01']  	     -0.9778	      0.0940	 -10.40

R^2= 0.965, N= 120, K= 7
****************************************************************

It would be nice if the editor could edit the application's templates. To do this, we need to:

1. Create a Yancy::Plugin::Template that adds a new collection (templates) to the existing Yancy backend
   • Database schema for this table should be provided for all supported backends via a named migration
   • The table should have two fields: A name (which is the full path of the template including format [e.g. html] and template handler [e.g. ep]) and a template, which is the code of the template.
2. When the application starts, load the templates so they are available to the application
   • Register a template handler for Yancy and make it the default handler.
     • This has the benefit of allowing some routes to bypass the Yancy templates by explicitly using the ep handler
   • The Yancy template handler should render the template from a cache (use the renderer cache)
     • We will need to copy parts of the EPRenderer to enable helpers and access to the controller
     • If the template doesn't exist in the cache, look for the template in the database. If it doesn't exist in the database, defer to the EPRenderer
     • We need to expire the cache regularly through a timer: Only the one worker that responds to the editor's API request to change the template will get the backend event to fire.
   • If the above does not work well, create a subclass of Mojolicious::Renderer and install that as the app's renderer (keeping the existing app renderer as a fallback). Then Yancy has total control over rendering and can delegate to Mojolicious::Renderer as desired.
3. Add backend events (#52) to update the internal template cache when a template is created, updated, or deleted

Hi ,

We have a use case where DBIx::Class backend DSN needs to be set at
request handling time in a mojo app depending on the tenant selection (based
on Host header currently).

We are registering the Yancy plugin in Mojo app during the application startup
but the backend is not expected to be valid at that point as its determined during
HTTP request handling phase.

Kindly guide how can we use Yancy based on its current features.

related topic #33

regds
Rajesh Mallah.

Possible duplicate/clarification of issue #79

E.g. I have table

CREATE TABLE users (
  `id` int(4) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `username` VARCHAR(255) NOT NULL,
  `password` VARCHAR(40) NOT NULL,
  `created` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

I set read_schema => 1 in Yancy config and try to execute

app->yancy->create( users => { username => "admin", password => "admin" } );

If I not provide created explicitly via Perl, like created => '2019-01-01 14:45:12' there will be an error:

app_1  | Use of uninitialized value in sprintf at /usr/local/share/perl5/site_perl/Mojolicious/Plugin/Yancy.pm line 848.
app_1  | [2020-04-01 23:11:42.25123] [33] [error] Error validating new item in schema "users":  (/created)

Seems like yancy.validate not produce any @errors but for some reason failed.

Is it possible to get verbose description of error ?
Is that issue related to Mojo::mysql or JSON::Validator?

As temporary solution I generate CURRENT_TIMESTAMP via POSIX::strftime(), like print strftime "%Y-%m-%d %H:%M:%S", localtime time, but still, any way to leave handling timestamp for SQL ?

I gather the roadblock for supporting MongoDB is implementing read_schema. If so, I believe the way forward is to have the implementing method check the DB for whether there is a configured schema: https://docs.mongodb.com/manual/core/schema-validation/

If not, die. If so, just read the schema and use that. Does that accord with your thoughts?

Right now, the editor application does only what it was developed to do: Edit content in the Yancy tables. But that's not the only thing one needs to do to administer a website, and it'd be easier for users to have one place to go to do all their work: The Yancy editor.

This ticket is a loose collection of ideas for how to make the Yancy editor into an app that's easy to extend with custom JavaScript / Vue components. These may change slightly as Yancy evolves.

Some work on this has already been completed:

• Users can add custom Vue components to the editor using the yancy.editor.include helper.
• Users can add menus to the sidebar of the editor using the yancy.editor.menu helper. These menus get routed to Vue components added via yancy.editor.include.

This work is a blocker for a v3.0 release, but not the v2.0 release.

Single-Page App with Vue Router

It will improve modularity if the Yancy editor is a single-page application that uses Yancy APIs and the Vue router to move between views.

The edit form / new item form should be its own destination, not a drawer that slides out from a table.

Data Caching with VueX

Different parts of the application are going to need to share data between themselves and the backend. The editor should use VueX to provide an API to get/set/create/delete items in the backend. The VueX state is the source of truth for all the components in the editor application.

Editor global

The editor Vue application should be available from the global Yancy variable. This allows for us to add an API to interact with the editor from custom JavaScript.

Smaller, atomic field components

Rather than having one "edit field" component, the editor should have a set of components for each type of data (input type, not JSON Schema type). There should be a method on the editor global (Yancy) that allows a user to register their own Vue component to handle a given type of input (if this can be done with Vue's "components" feature, more's the better).

Schema customizations

See #61. The new menus/buttons should allow routing to specific Vue components.

Documentation / Examples

There should be documentation on how to add custom panels to the editor, including multi-step workflows.

These are the main features that, when complete, will mark the 2.0 release. These features will be considered EXPERIMENTAL until v2.0 is released.

• Form plugins
  • Form plugins will generate forms for JSON schemas. This allows for easy editing of data by users.
  • Form plugins can generate forms to edit all the fields in an item or only a subset of fields.
  • Form plugins will exist for some popular UX libraries like Bootstrap and Pure.
    • A default plugin with semantic HTML 5 markup not attached to any UX library will exist as well (This is cancelled for now: The default Yancy pages use Bootstrap4, so the default form plugin is Bootstrap4)
  • The form plugin will use the Mojolicious::Validation framework for validating data (This cannot be made to work without extensive enhancement of Mojolicious::Validation to support structured data validation, which can be done in future versions)
  • Yancy uses best practices by default, so form plugins will use Mojolicious's CSRF tokens.
  • Work has begun on Yancy::Plugin::Form::Bootstrap4
• Authentication plugins
  • An authentication plugin API for developing interoperable auth plugins
  • Multiple authentication plugins will be able to be loaded in a single site
    • This will be the Yancy::Plugin::Auth module, which can load other auth modules inside
  • Authentication plugins will exist for some common authentication schemes. Each plugin creates a unique identifier for a user
    • Password-based auth using Perl's Digest modules
    • Future development: LDAP auth (OpenLDAP and Microsoft ActiveDirectory)
    • OAuth2 (via Mojolicious::Plugin::OAuth2)
      • There will be a generic OAuth2 module, and specific ones for Github and other common providers.
    • Future development: OpenID-Connect
  • Authentication plugins will store the information needed to uniquely identify a user
    • Individual auth methods must fetch enough information to identify the user
      • LDAP uses distinguished name (dn)
      • Generic OAuth2 would need to provide a hook to find this information
      • Specific OAuth2 modules, like Github, can provide this information automatically
    • The multiplex Yancy::Plugin::Auth module must store what auth module the user used
  • Password-based auth will allow registration and providing an e-mail address for password resets (sending e-mails like this will be handled by a plugin and may be added to core later)
  • Future development (v2 and beyond):
    • User profiles that should be filled out when authenticating for the first time
      • This is already supported by registration_fields
    • 2FA for password-based auth
    • Role-based authorization plugin
    • Link multiple authentications to a single user profile
      • This is already supported by varying the username_field across multiple auth plugins
    • Authentication plugins may expose other information about the user
      • OAuth2 plugins can ask for "scopes" to get e-mail addresses, names, locations, etc...
      • LDAP plugins can fetch common LDAP fields
      • Plugins will all configure this in the same way, so that the multiplex Yancy::Plugin::Auth will be able to configure plugins correctly
• File upload field
  • Data fields will be able to be a path to a file
  • Files will be able to be uploaded through the JS editor or POST forms (using the form plugins)
  • Files will be stored using a content-addressable folder tree (using Perl's Digest modules)
  • Files which are not linked to data in the database will be cleaned up on a regular basis using a "cleanup" command (scheduled by the administrator)
• Data relationships
  • Properties in the schema can be declared as relationships to other data foreign keys.
    • This is completed in v1.043
  • Additional properties in the schema can be declared as relationships Instead, any x-foreign-key in the schema, or that refers to the schema, is automatically a relationship that can be joined.
    • These relationships can be one-to-one or one-to-many
    • Related data can be expanded by default in the configuration, or on-demand as an argument to the get or list backend methods
    • We do not want to send the entire set of related data every time we save the item, so the related data will be read-only for now
  • The editor will display a link to edit the related data
    • For one-to-many relationships, the link will display a list view
      • The list view will allow for creating/deleting items, ensuring foreign keys are correct
      • The list view will allow for editing existing related items, without editing the foreign key
    • For one-to-one relationships, the link will go directly to an edit form
  • Future development (v2 and beyond):
    • Support many-to-many relationships through a relationship table
    • Support editing related data in the same form as the parent data
    • Read foreign key relationships from the schema for the supported backends
• Pluggable editor
  • The editor menu will have a section for "Plugins".
  • Yancy::Plugins will be able to add themselves to the menu by defining a VueJS component
  • Yancy plugins will be able to add routes under /yancy/plugin/:plugin_name
    • It's not necessary for the editor to control route creation, at this point. Plugins can create whatever routes they want where-ever they want.
  • Plugins can define multiple routes and link between those routes, creating an application that lives in the Yancy editor
    • The editor is a single-page application (SPA) using the Vue.JS app framework. Over time, the JS API will be enhanced to allow for easier extending of the editor.
  • The editor itself will be moved into a plugin as well, see #22
• Internationalization (i18n)
  • A Yancy site may load one or more language lexicons
  • All default Yancy templates and user-facing strings will be internationalized, with the language being decided by visitor preference
    • Yancy can be shipped with contributed translations
  • All internal log messages will be internationalized, with the language of the message being decided by the site's developer
  • Site developers will be able to extend the Yancy lexicons to add their own strings

These are breaking changes that will be made for version 2.0, with a transition period during the 1.x development cycle:

• collections configuration will be renamed to schema
  • This more accurately describes what is being configured
  • Any references to collection will be renamed to schema
  • This also makes it possible for parts of Yancy to accept both the name of a schema in the configuration or a JSON schema hash for ad-hoc schemas
  • During the v1.x development cycle, collections will be an alias for schema in the configuration, and collection will be supported
    • Before the stable v2.0 release, a warning will be generated to remind users to rename collections to schema on app startup
• The Basic auth plugin will be deprecated.
  • This plugin was designed for simple authentication while waiting for the more advanced pluggable authentication module.
  • The route feature of the Basic auth module is hard to use, hard to test, and just plain wrong
    • The pluggable Auth module provides a better way to protect given routes
    • The standalone app will need a way to require authentication for routes
  • Once the Auth plugin is complete and stable, the Basic auth plugin will be removed.

These things are not required for version 2.0, but would be nice to have.

• MongoDB Backend (WIP branch: backend/mongodb)
  • This will require the Yancy editor to understand some more complex data structures, similar to data relationships and packed data fields

Hi,

I was trying to use Yancy in standalone mode. My setup creates a cpanfile declaring Yancy and dependencies, carton installed. I was creating the yancy.conf file in the homedir of the project, but it didn't seem to get loaded. A bit of investigating shows that the yancy.conf is looked up in local/lib/perl5, which I wasn't expecting.

pplu@clamps:~/yancy$ carton exec strace yancy daemon 2>&1 | grep conf
[...]
stat("/home/pplu/yancy/local/lib/perl5/yancy.development.conf", 0x788680) = -1 ENOENT (No such file or directory)
stat("/home/pplu/yancy/local/lib/perl5/yancy.conf", 0x788680) = -1 ENOENT (No such file or directory)
[...]

Any ideas on how to fix this?

When listing a collection with optional unset fields, in SQLite the backend responds with

{"errors":[{"message":"Expected string - got null.","path":"\/items\/0\/xy"}],"status":500}

There is no difference, whether the field is a listed column or not.
Tested with: 1.001
Example app:

use Mojolicious::Lite;
use Mojo::SQLite;
use Test::More;
use Test::Mojo;
use Mojo::File qw/tempfile/;

my $db_file = tempfile;

helper sqlite => sub {
  state $sql = Mojo::SQLite->new->from_filename($db_file);
};

plugin Yancy => {
  backend => {
    sqlite => app->sqlite
  },
  collections => {
    people => {
      required => ['name'],
      'x-list-columns' => [qw/name/], # No difference though
      type => 'object',
      properties => {
        id => {
          type => 'integer',
          readOnly => 1,
        },
        name => {
          type => 'string'
        },
        email => {
          type => 'string'
        }
      },
      example => {
        name => 'Philip J. Fry'
      }
    },
  }
};

# Load schema
app->sqlite->migrations->from_string(<<SCHEMA)->migrate;
-- 1 up
CREATE TABLE people (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name VARCHAR NOT NULL,
    email VARCHAR NULL
);
-- 1 down
drop table people;
SCHEMA

get '/' => sub {
  my $c = shift;
  $c->render(inline => <<'TEMPLATE');
% my @people = app->yancy->list('people');
<h1>People!</h1>
<ul>
  % for my $person (@people) {
    <li><%= $person->{name} %><% if ($person->{email}) { %> (<%= $person->{email} %>)<% } %></li>
  % }
</ul>
TEMPLATE
};

my $t = Test::Mojo->new;

$t->get_ok('/')
  ->status_is(200)
  ->text_is('h1', 'People!')
  ->element_exists_not('ul > li');

$t->get_ok('/yancy')
  ->status_is(200)
  ->text_is('head > title', 'Yancy CMS');

$t->post_ok('/yancy/api/people' => json => {
  name => 'akron'
})->status_is(201)
  ->content_is(1);

$t->post_ok('/yancy/api/people' => json => {
  name => 'frank',
  email => '[email protected]',
})->status_is(201)
  ->content_is(2);

# This fails
$t->get_ok('/yancy/api/people')
  ->status_is(200)
  ->content_is('');

$t->get_ok('/')
  ->status_is(200)
  ->element_exists('ul > li')
  ->text_is('ul > li:nth-of-type(1)', 'akron');

$t->get_ok('/')
  ->status_is(200)
  ->element_exists('ul > li')
  ->text_is('ul > li:nth-of-type(2)', 'frank ([email protected])');

done_testing;

(Sorry for submitting an example app again instead of doing a pull request, but I wasn't able to find a backend specific full integration test in the test suite and had no luck using TEST_YANCY_BACKEND="sqlite:myfile.db" prove -lr t/).

How can I write in another languege?
#!/usr/bin/env perl
use utf8;
use Mojolicious::Lite;
binmode(STDOUT, ":utf8");
binmode(STDIN, ":utf8");
plugin Yancy => {
backend => 'static:.',
read_schema => 1,
schema => {
pages => {
properties => {
# Add an optional 'author' field
author => { type => [ 'string', 'null' ] },
},
},
},
};
get '/*id', {
controller => 'yancy',
action => 'get',
schema => 'pages',
template => 'default',
layout => 'default',
id => 'index',
};
app->start;
DATA
@@ default.html.ep
% title $item->{title};
<%== $item->{html} %>
@@ layouts/default.html.ep

[error] Error setting item with ID "as/ss" in schema "pages": Wide character in syswrite at /home/dagandre/perl5/perlbrew/perls/perl-5.28.2/lib/site_perl/5.28.2/x86_64-linux/IO/Handle.pm line 481.

I try to edit site created by Statocles.

perl ./myapp.pl backend copy static:. pages
Wide character in syswrite at /home/dagandre/perl5/perlbrew/perls/perl-5.28.2/lib/site_perl/5.28.2/x86_64-linux/IO/Handle.pm line 481.

Add distributed collection configuration for dbi backend through a custom method in each result definition. The idea is to allow all openapi database related configuration to happen in one place.

Setting x-list-columns isn't required, but the heuristics that Yancy uses to guess what to display in the list (if you don't set x-list-columns) are crude at best: Right now, it looks for columns named 'id', 'name', 'username', 'title', or 'slug'.

My best idea for how to guess what columns to display is to display the first 4-6 columns that do not have "format": "textarea" or "format": "markdown". These columns are the most likely to break the list view. We should probably also add some CSS: white-space: nowrap; text-overflow: ellipsis would make it so that even long amounts of text would not break the table.

After discussion on #yancy, here are some thoughts. Mojolicious::Plugin::OpenAPI::Security has the basics of support for securityDefinitions (SD), which Mojolicious::Plugin::Yancy could expand on.

OpenAPI spec -> Yancy

If an OpenAPI spec with SD is provided, this could be enabled by a Yancy::Plugin::Auth::DispatchSecurity (only with maybe a better name). Each Yancy::Plugin::Auth::* module would register itself with the dispatcher (having been loaded by Yancy as a plugin), which that would read the SD, and on loading Mojolicious::Plugin::OpenAPI would provide the appropriate callback according to the SD.

Yancy -> OpenAPI spec

Similarly, if there are no SD provided, but a Yancy::Plugin::Auth::* is loaded with the right config, it could register itself (by its return values - perhaps plugins should more widely return things that want to be passed to the OpenAPI plugin and/or spec) and end up being added to the spec's SD.

Is there any option for automatic tables creation according schema config property?
Like manage.py migrate in Django

There should be a way to subscribe to data updates using the Yancy::Controller::Yancy. Add the following actions to the controller:

• get_ws - Get a WebSocket for updates to a single item. Every message will be an update to the given item, which may be the entire item or only those fields which have changed.
  • Required configuration:
    • collection - The collection to work with
    • id - The ID of the item to get updates from
• list_ws - Get a WebSocket for updates to an entire collection.
  • Required configuration:
    • collection - The collection to work with
  • Future enhancements can include filtering which items will get sent

Every update message is a JSON object. The object's keys are item IDs, and the values are either null (if the item has been deleted) or the item changes (which may be the entire item).

The set, create, and delete actions should send a message to any connected WebSockets. Create a new plugin, Yancy::Plugin::Notify available from the helper yancy.notify. This plugin should have three methods:

• subscribe - Used by get_ws and list_ws to subscribe to updates. Takes two arguments: The collection name to subscribe to, and a subref to call to send the message. This subref receives two arguments, the ID of the item and the item to send. The subref decides whether to send the message, so get_ws only sends messages for the one item it wants. Returns the subref given, for later use to unsubscribe.
• unsubscribe - Used by get_ws and list_ws to unsubscribe from updates. Takes two arguments: The collection name to unsubscribe from, and the subref used to subscribe.
• send - Used by set, create, and delete to send updates for an item. Takes three arguments: The collection name, the item ID, and the item data (or undef if the item has been deleted).

In the future, Yancy::Plugin::Notify can support message broker backends like Mercury, Redis, Mojo::Pg, etc... to coordinate messages between forked / distributed daemons.

Hi ,
The code does only ilike search. we can have equality search for integers.
Shall i submit a pull request here? (ie in github)

regds
mallah.

The t/plugin/auth/basic.t test fails on some of my smokers:

    #   Failed test 'User is not authorized for API spec'
    #   at t/plugin/auth/basic.t line 109.
    #          got: '500'
    #     expected: '401'
    # Looks like you failed 1 test of 21.

#   Failed test 'unauthenticated user cannot admin'
#   at t/plugin/auth/basic.t line 133.

    #   Failed test 'User is not authorized for API spec'
    #   at t/plugin/auth/basic.t line 262.
    #          got: '500'
    #     expected: '401'
    # Looks like you failed 1 test of 32.

#   Failed test 'standalone plugin'
#   at t/plugin/auth/basic.t line 296.
# Looks like you failed 2 tests of 6.
t/plugin/auth/basic.t ........ 
Dubious, test returned 2 (wstat 512, 0x200)
Failed 2/6 subtests 

This seems to happen if Mojolicious::Plugin::OpenAPI < 2.00 is installed.

Is there any method to create admin superuser like in Django ?
https://docs.djangoproject.com/en/2.2/intro/tutorial02/#creating-an-admin-user
I haven't found anything in docs.
If there is no such method - it would be nice to add it.

Users who are new to Mojolicious might not know that the mojo command, and by extension the yancy command, isn't very useful and that the primary command to interact with their application is to run their application (myapp.pl). Further, without a yancy.conf file, the standalone daemon is basically no more useful than using mojo daemon.

So, if there is no yancy.conf file in the current directory, the yancy command should die with an error explaining that it's the standalone daemon for working with existing databases and must be configured, but also if you're using Yancy as a plugin, you should invoke your application directly.

When creating new collection items I receive the following output from Morbo:

`[Mon Sep 10 10:34:36 2018] [debug] POST "/yancy/api/vendors" (8622a8f9)

[Mon Sep 10 10:34:36 2018] [debug] Routing to controller "Yancy::Controller::Yancy::API" and action "add_item"

[Mon Sep 10 10:34:36 2018] [debug] 201 Created (0.007491s, 133.494/s)

[Mon Sep 10 10:34:36 2018] [debug] GET "/yancy/api/vendors/undefined" (03f60b91)

[Mon Sep 10 10:34:36 2018] [debug] Routing to controller "Yancy::Controller::Yancy::API" and action "get_item"

[Mon Sep 10 10:34:36 2018] [warn] OpenAPI >>> GET /yancy/api/vendors/undefined [{"message":"Missing property.","path":"/vendor_address"},{"message":"Missing property.","path":"/vendor_name"},{"message":"Missing property.","path":"/vendor_phone"}]

[Mon Sep 10 10:34:36 2018] [debug] 500 Internal Server Error (0.002883s, 346.861/s)
`

I'm using a very minimal test config:

plugin Yancy => { backend => 'mysql://***', read_schema => 1, collections => { vendors => { title => 'Vendors', 'x-list-columns' => [qw(vendor_name vendor_address)], 'x-id-field' => 'vendor_id' } } };
I sincerely apologize if this is a configuration issue, but as far as I can tell, it should just work.

Updating and deleting work fine.

Hi Doug,

Could you please provide an example of usage route config param?

My purpose is to customize URL - set /admin instead of /yancy.

I don't understand how it's possible to do with config - seems like Yancy::Plugin::Editor code expect Mojolicious::Routes::Route object, not a string.

If I set route => 'admin' or route => '/admin' I get Can't locate object method "to" via package ".admin" error

Setting app->routes->any( '/admin' )->name('admin'); gives same error.

Hi there,

When trying to install Yancy using CPAN I get test failures:

#   Failed test 'default id field'
#   at /root/.cpanm/work/1585663571.14668/Yancy-1.046/t/backend/../lib/Local/Test.pm line 777.
Cannot do foreign key with columns that are not the primary ID (x-id-field) on table blog, relationship user (foreign column: username, foreign id: id) at /root/.cpanm/work/1585663571.14668/Yancy-1.046/blib/lib/Yancy/Backend/Role/Relational.pm line 402.

The full output is in a Gist

Output from dpkg -l

ii  libaprutil1-dbd-sqlite3:amd64                            1.5.4-3                           amd64        Apache Portable Runtime Utility Library - SQLite3 Driver
ii  libsqlite0                                               2.8.17-14                         amd64        SQLite 2 shared library
ii  libsqlite3-0:amd64                                       3.16.2-5+deb9u1                   amd64        SQLite 3 shared library
ii  sqlite                                                   2.8.17-14                         amd64        command line interface for SQLite 2
ii  sqlite3                                                  3.16.2-5+deb9u1                   amd64        Command line interface for SQLite 3
ii  libdbd-pg-perl                                           3.10.5-1~pgdg90+1                 amd64        Perl DBI driver for the PostgreSQL database server
ii  libdbd-sqlite3-perl                                      1.54-1                            amd64        Perl DBI driver with a self-contained RDBMS
ii  libdbi-perl                                              1.636-1+b1                        amd64        Perl Database Interface (DBI)
ii  libdbix-class-candy-perl                                 0.005002-1                        all          module providing syntax sugar for DBIx::Class
ii  libdbix-class-cursor-cached-perl                         1.001004-1                        all          cursor object with built-in caching support
ii  libdbix-class-dynamicdefault-perl                        0.04-1                            all          dbix-class extension to automatically set and update fields
ii  libdbix-class-helpers-perl                               2.033002-1                        all          collection of helpers for DBIx::Class
ii  libdbix-class-journal-perl                               0.900200-1                        all          Auditing for tables managed by DBIx::Class
ii  libdbix-class-perl                                       0.082840-3                        all          extensible and flexible object <-> relational mapper
ii  libdbix-class-schema-loader-perl                         0.07046-1                         all          module to automate definition of a DBIx::Class::Schema
ii  libdbix-class-timestamp-perl                             0.14-1                            all          DBIx::Class extension to update and create date and time based fields
ii  libdbix-connector-perl                                   0.56-1                            all          fast and safe DBI connection and transaction management
ii  libdbix-introspector-perl                                0.001005-1                        all          module to detect what database code is connected to

I've I've left out any crucial bit of information please let me know.

We need CORS support to handle remote access onto the api routes.
As Yancy makes use of Mojolicious::Plugin::OpenAPI there is some cors support out of the box, if the api controller is extended as described in Mojolicious::Plugin::OpenAPI::Cors.

Is this something that would be for core Yancy or shall we put that into an custom controller?

If the given DBIx::Class Schema has virtual tables it cannot be imported with read_schema.

Sorry, adding a working test case is beyond my scope, as your test suite is quite complex.

That is what our vitual result classes look like:

package Glue::DB::Schema::Virtual::Result::VirtualContractNextNumber;

use strict;
use warnings;
use base qw(DBIx::Class::Core);

__PACKAGE__->table_class('DBIx::Class::ResultSource::View');

# For the time being this is necessary even for virtual views
__PACKAGE__->table('virtual_contract_next_number');
__PACKAGE__->add_columns(
    next_contract_number => {
        data_type          => 'VARCHAR',
        size               => 15,
    }
);

# do not attempt to deploy() this view
__PACKAGE__->result_source_instance->is_virtual(1);

__PACKAGE__->result_source_instance->view_definition(q[
  SELECT
    CONCAT(
      'V',
      LPAD(
        CAST(
          COALESCE(
            CAST(RIGHT(MAX(number), LENGTH(MAX(number)) - LENGTH('V')) AS INT),
            0
          ) + 1
          AS VARCHAR(2)
        ),
        2,
        '0'
      )
    ) AS next_contract_number
  FROM contract
  WHERE project_id = ?
]);

1;

And this is the error on loading the Yancy plugin in Mojolicious:

Can't load application from file "/home/glue/src/glue-core-backend/app/scripts/glue": Possibly a typo in schema? Could not find "/definitions/VirtualContractNextNumber/properties/id" in "" (#/definitions/VirtualContractNextNumber/properties/id) at /usr/local/share/perl/5.26.1/JSON/Validator.pm line 460.
	JSON::Validator::_resolve_ref(JSON::Validator::OpenAPI::Mojolicious=HASH(0x55a2e5737418), HASH(0x55a2e56f6b10), Mojo::URL=HASH(0x55a2e574b4e8)) called at /usr/local/share/perl/5.26.1/JSON/Validator/OpenAPI/Mojolicious.pm line 295
	JSON::Validator::OpenAPI::Mojolicious::_resolve_ref(JSON::Validator::OpenAPI::Mojolicious=HASH(0x55a2e5737418), HASH(0x55a2e56f6b10), Mojo::URL=HASH(0x55a2e574b4e8)) called at /usr/local/share/perl/5.26.1/JSON/Validator.pm line 422
	JSON::Validator::_resolve(JSON::Validator::OpenAPI::Mojolicious=HASH(0x55a2e5737418), HASH(0x55a2e5739c80)) called at /usr/local/share/perl/5.26.1/JSON/Validator/OpenAPI/Mojolicious.pm line 35
	JSON::Validator::OpenAPI::Mojolicious::load_and_validate_schema(JSON::Validator::OpenAPI::Mojolicious=HASH(0x55a2e5737418), HASH(0x55a2e5739c80), HASH(0x55a2e5737508)) called at /home/glue/src/glue-core-backend/app/lib/Mojolicious/Plugin/OpenAPI.pm line 41
	Mojolicious::Plugin::OpenAPI::register(Mojolicious::Plugin::OpenAPI=HASH(0x55a2e57372b0), Glue=HASH(0x55a2e2a5bad0), HASH(0x55a2e57246d0)) called at /usr/local/share/perl/5.26.1/Mojolicious/Plugins.pm line 46
	Mojolicious::Plugins::register_plugin(Mojolicious::Plugins=HASH(0x55a2e525c6e0), "OpenAPI", Glue=HASH(0x55a2e2a5bad0), HASH(0x55a2e57246d0)) called at /usr/local/share/perl/5.26.1/Mojolicious.pm line 188
	Mojolicious::plugin(Glue=HASH(0x55a2e2a5bad0), "OpenAPI", HASH(0x55a2e57246d0)) called at /usr/local/share/perl/5.26.1/Mojolicious/Plugin/Yancy.pm line 482
	Mojolicious::Plugin::Yancy::register(Mojolicious::Plugin::Yancy=HASH(0x55a2e5632f58), Glue=HASH(0x55a2e2a5bad0), HASH(0x55a2e54da390)) called at /usr/local/share/perl/5.26.1/Mojolicious/Plugins.pm line 46
	Mojolicious::Plugins::register_plugin(Mojolicious::Plugins=HASH(0x55a2e525c6e0), "Yancy", Glue=HASH(0x55a2e2a5bad0), HASH(0x55a2e54da390)) called at /usr/local/share/perl/5.26.1/Mojolicious.pm line 188
	Mojolicious::plugin(Glue=HASH(0x55a2e2a5bad0), "Yancy", HASH(0x55a2e54da390)) called at /home/glue/src/glue-core-backend/app/scripts/../lib/Glue.pm line 61
	Glue::startup(Glue=HASH(0x55a2e2a5bad0)) called at (eval 470) line 16
	Glue::startup(Glue=HASH(0x55a2e2a5bad0)) called at /usr/local/share/perl/5.26.1/Mojolicious.pm line 181
	Mojolicious::new("Glue") called at /usr/local/share/perl/5.26.1/Mojo/Server.pm line 17
	Mojo::Server::build_app(Mojo::Server=HASH(0x55a2e2a5b890), "Glue") called at /usr/local/share/perl/5.26.1/Mojolicious/Commands.pm line 72
	Mojolicious::Commands::start_app("Mojolicious::Commands", "Glue") called at /home/glue/src/glue-core-backend/app/scripts/glue line 11
	require /home/glue/src/glue-core-backend/app/scripts/glue called at (eval 90) line 1
	eval 'package Mojo::Server::Sandbox::71586b68ac7b572aa587893fd1229b54; require $path' called at /usr/local/share/perl/5.26.1/Mojo/Server.pm line 54
	Mojo::Server::load_app(Mojo::Server::Daemon=HASH(0x55a2e0aa77d0), "glue") called at /usr/local/share/perl/5.26.1/Mojo/Server/Morbo.pm line 67
	Mojo::Server::Morbo::_spawn(Mojo::Server::Morbo=HASH(0x55a2e0a9c520)) called at /usr/local/share/perl/5.26.1/Mojo/Server/Morbo.pm line 54
	Mojo::Server::Morbo::_manage(Mojo::Server::Morbo=HASH(0x55a2e0a9c520)) called at /usr/local/share/perl/5.26.1/Mojo/Server/Morbo.pm line 34
	Mojo::Server::Morbo::run(Mojo::Server::Morbo=HASH(0x55a2e0a9c520), "glue") called at /usr/local/bin/morbo line 19
Compilation failed in require at (eval 90) line 1.

One way to solve this is to skip virtual tables in _build_db_schema sub:

    # skip virtual tables
    if ( $source->isa('DBIx::Class::ResultSource::View') && $source->is_virtual) {
      DEBUG && $app->log->debug("OpenAPI - skipping virtual table '$table'");
      next;
    }

Now that the JSON::Validator plugin supports OpenAPI 3.0, we should finish the openapi-3 branch (or start migrating to OpenAPI v3 from scratch). OpenAPI v3.0 provides the following benefits:

• Better security definitions that allow for routes to request one authentication mechanism from a set of them.
  • It would be awesome if Yancy started using something like this to define auth in general (#40)
• Support for Links (which would improve the API definition for data relationships)
• Callbacks. These are not definitions for WebSockets, unfortunately, but still an interesting concept for making it easy to build microservices with just Yancy.

More information: https://swagger.io/blog/news/whats-new-in-openapi-3-0/

Hello Doug,

on using dbi backend, we'd like to define the collections to expose to the API (instead using x-ignore to define which not to expose). We'd like adding a mode switch to allow switching between expose-on-default or ignore-on-default.

Would that be an acceptable extension?

Best greets,

Mario

The editor should be able to configure custom menus, much like x-view-url.

Setting x-menu on a collection should add a set of buttons above the upper-right of the items list table (where the "Add Item" button is now). x-menu should be an array with items matching the following:

• { title: 'Text', url: '...' } - Create a button that goes to the given URL
• { title: 'Text', script: '...' } - Create a button that executes the given JS. The JS context (this) will have an items property corresponding to the items currently shown in the table.
• { title: 'Text', items: [ ... ] } - Create a button with a dropdown menu that can contain the above items. Dropdown menus cannot contain other dropdown menus.

Setting x-menu-item on a collection will add a drop-down menu to each item row. In this case, the script will be given a context of the item itself (so this.id would be the ID of the row whose menu was clicked). Editing the context object edits the item in the table. The context also has delete() and save() methods. The delete() method will prompt the user for confirmation. The save() method takes an optional single argument, an object of fields to update before saving. Item menus cannot contain dropdown menus.

In the future we can add good documentation for the Vue components and other objects and refactor them so that the script enhancements are easier to write. If we ever add a checkbox to the item rows, we can give the selected items to the script as this.selectedItems.

We should document a few ways to make useful enhancements to the editor, including how to add a custom JS file to the editor (which should be as easy as possible) and call a function inside. There should be useful one-liners as well.

Discussion questions:

• Should we turn x-item-menu into a series of buttons in the item row, like x-menu?
• Should we allow x-menu on item properties, adding a gear icon to the right of the value in the column
  • The use-case here is a "status" column that has a drop-down menu for quickly changing between different statuses

In Yancy::Backend::Dbic are two differend approached to get the name of the source during read_schema.

The column property export uses:

        my $result_class = $source->result_class;
        # ; say "Adding class: $result_class ($table)";
        $classes{ $result_class } = $source;

The foreign key export uses:

my $self_table = $source->name;

and

my $foreign_table = $classes{ $foreign_class }->name;

According to the DBIx::Class doc the name method not allways delivers the correct value, instead the source_name method delvers the desired value.

Without that fix we get the schema with camel cased and snake cased table name mixed:

'office_employee' => {
                        'properties' => {}
                      },
'OfficeEmployee' => {
                       'properties' => {
                                         'email' => {
                                                      'type' => 'string',
                                                      'x-order' => 6
                                                    },
                                         'firstname' => {
                                                          'type' => 'string',
                                                          'x-order' => 3
                                                        },
                                         'lastname' => {
                                                         'type' => 'string',
                                                         'x-order' => 4
                                                       },
                                         'archived' => {
                                                         'type' => 'integer',
                                                         'x-order' => 7
                                                       },
                                         'title' => {
                                                      'type' => 'string',
                                                      'x-order' => 2
                                                    },
                                         'id' => {
                                                   'x-order' => 1,
                                                   'readOnly' => 1,
                                                   'type' => 'integer'
                                                 },
                                         'short' => {
                                                      'x-order' => 5,
                                                      'type' => 'string'
                                                    }
                                       },
                       'type' => 'object',
                       'required' => [
                                       'title',
                                       'firstname',
                                       'lastname',
                                       'short',
                                       'email',
                                       'archived'
                                     ]
                     },

t/backend/dbic.t may fail:

Role::Tiny 2.000001+ is required for roles at /home/cpansand/.cpan/build/2018102417/Yancy-1.009-OCdQvu/blib/lib/Yancy/Backend/Role/Sync.pm line 32.
BEGIN failed--compilation aborted at /home/cpansand/.cpan/build/2018102417/Yancy-1.009-OCdQvu/blib/lib/Yancy/Backend/Role/Sync.pm line 32.
Compilation failed in require at /usr/perl5.20.1Dp/lib/site_perl/5.20.1/Role/Tiny.pm line 38.
Compilation failed in require at t/backend/dbic.t line 42.
BEGIN failed--compilation aborted at t/backend/dbic.t line 42.
t/backend/dbic.t ............. 
Dubious, test returned 255 (wstat 65280, 0xff00)
No subtests run 

Every backend should emit events in the following circumstances:

• before_create - Before an item is created so that the event handler can edit it or prevent it (by throwing an exception)
• after_create - After an item is created, given the success/fail of the create and the ID of the item (if successful)
• before_set - Before an item is updated so that the event handler can edit it or prevent it (by throwing an exception)
• after_set - After an item is updated, given the success/fail of the set and the ID of the item
• before_delete - Before an item is deleted so the event handler can prevent it (by throwing an exception)
• after_delete - After an item is deleted, given the success/fail of the delete and the ID of the item

Backends should inherit from Mojo::EventEmitter. Events can be added to the backend by using the standard Mojo::EventEmitter methods. These events should be documented in Yancy::Backend.

The documentation should explain that the events will only be handled by the worker that handles the request. So these events cannot be used, by themselves, to broadcast updates to other clients unless all the clients are connected to the same worker, or some kind of messaging system is used (like Mercury).