txt: ## dist_zef-tony-o-DB-Xoos-MySQL.md
  # DB::Xoos::MySQL
  
  DB::Xoos::MySQL is an ORM designed for convenience and ease of use. It is based upon [DB::Xoos](https://github.com/tony-o/p6-xoo), provides roles and enhancements and allows you to connect to postgres for ultimate magic.
  
  [![CircleCI](https://circleci.com/gh/tony-o/db-xoos-mysql/tree/master.svg?style=svg)](https://circleci.com/gh/tony-o/db-xoos-mysql/tree/master)
  
  # Usage
  
  Below is a minimum viable model setup for your app. Xoos does *not* create the table for you, that is up to you.
  
  ## autoloading model
  
  ### lib/app.pm6
  
  ```
  use DB::Xoos::MySQL;
  
  my DB::Xoos::MySQL $d .=new;
  
  $d.connect('mysql://xyyz/example', options => { :dynamic-loading });
  
  my $customer-model = $d.model('Customer');
  my $new-customer   = $customer-model.new-row;
  $new-customer.name('xyz co');
  $new-customer.rate(150);
  $new-customer.update; # runs an insert because this is a new row
  
  my $xyz = $customer-model.search({ name => { 'like' => '%xyz%' } }).first;
  $xyz.rate( $xyz.rate * 2 ); #twice the rate!
  $xyz.update; # UPDATEs the database
  
  my $xyz-orders = $xyz.orders.count;
  
  ```
  
  ## same example with model modules
  
  ### lib/app.pm6
  
  ```
  use DB::Xoos::MySQL;
  
  my DB::Xoos::MySQL $d .=new;
  
  $d.connect('sqlite://xyz.sqlite3');
  
  my $customer-model = $d.model('Customer');
  my $new-customer   = $customer-model.new-row;
  $new-customer.name('xyz co');
  $new-customer.rate(150);
  $new-customer.update; # runs an insert because this is a new row
  
  my $xyz = $customer-model.search({ name => { 'like' => '%xyz%' } }).first;
  $xyz.rate( $xyz.rate * 2 ); #twice the rate!
  $xyz.update; # UPDATEs the database
  
  my $xyz-orders = $xyz.orders.count;
  
  ```
  
  ### lib/Model/Customer.pm6
  
  ```
  use DB::Xoos::Model;
  unit class Model::Customer does DB::Xoos::Model['customer'];
  
  has @.columns = [
    id => {
      type           => 'integer',
      nullable       => False,
      is-primary-key => True,
      auto-increment => 1,
    },
    name => {
      type           => 'text',
    },
    rate => {
      type => 'integer',
    },
  ];
  
  has @.relations = [
    orders => { :has-many, :model<Order>, :relate(id => 'customer_id') },
  ];
  
  ```
  
  # role DB::Xoos::Model
  
  What is a model? A model is essentially a table in your database. Your ::Model::X is pretty barebones, in this module you'll defined `@.columns` and `@.relations` (if there are any relations).
  
  ## Example
  
  ```
  use DB::Xoos::Model;
  # the second argument below is optional and also accepts a type.
  # if the arg is omitted then it attempts to auto load ::Row::Customer
  # if it fails to auto load then it uses an anonymous Row and adds convenience methods to that
  unit class X::Model::Customer does DB::Xoos::Model['customer', 'X::Row::Customer']; 
  
  has @.columns = [
    id => {
      type           => 'integer',
      nullable       => False,
      is-primary-key => True,
      auto-increment => 1,
    },
    name => {
      type           => 'text',
    },
    contact => {
      type => 'text',
    },
    country => {
      type => 'text',
    },
  ];
  
  has @.relations = [
    orders => { :has-many, :model<Order>, :relate(id => 'customer_id') },
    open_orders => { :has-many, :model<Order>, :relate(id => 'customer_id', '+status' => 'open') },
    completed_orders => { :has-many, :model<Order>, :relate(id => 'customer_id', '+status' => 'closed') },
  ];
  
  # down here you can have convenience methods
  
  method delete-all { #never do this in real life
    die '.delete-all disabled in prod or if %*ENV{in-prod} not defined'
      if !defined %*ENV{in-prod} || so %*ENV{in-prod};
    my $s = self.search({ id => { '>' => -1 } });
    $s.delete;
    !so $s.count;
  }
  
  
  ```
  
  In this example we're creating a customer model with columns `id, name, contact, country` and relations with specific filter criteria. You may notice the `+status => 'open'` on the open\_orders relationship, the `+` here indicates it's a filter on the original table.
  
  ### Breakdown
  
  `class :: does DB::Xoos::Model['table-name', 'Optional String or Type'];`
  
  Here you can see the role accepts one or two parameters, the first is the DB table name, the latter is a String or Type of the row you'd like to use for this model. If no row is found then Xoos will create a generic row and add helper methods for you using the model's column data.
  
  `@.columns`
  
  A list of columns in the table. It is highly recommended you have *one* `is-primary-key` or `.update` will have unexpected results.
  
  `@.relations`
  
  This accepts a list of key values, the key defining the accessor name, the later a hash describing the relationship. `:has-one` and `:has-many` are both used to dictate whether a Xoos model returns an inflated object (:has-one) or a filterable object (:has-many).
  
  ## Methods
  
  ### `search(%filter?, %options?)`
  
  Creates a new filterable model and returns that. Every subsequent call to `.search` will *add* to the existing filters and options the best it can.
  
  Example:
  
  ```
  my $customer = $dbo.model('Customer').search({
    name => { like => '%bozo%' }, 
  }, {
    order-by => [ created_date => 'DESC', 'customer_name' ], 
  });
  # later on ...
  my $geo-filtered-customers = $customer.search({ country => 'usa' });
  # $geo-filtered-customers effective filter is:
  #   {
  #      name => { like => '%bozo%' },
  #      country => 'usa',
  #   }
  
  ```
  
  ### `.all(%filter?)`
  
  Returns all rows from query (an array of inflated `::Row::XYZ`). Providing `%filter` is the same as doing `.search(%filter).all` and is provided only for convenience.
  
  ### `.first(%filter?, :$next = False)`
  
  Returns the first row (again, inflated `::Row::XYZ`) and caches the prepared statement (this is destroyed and ignored if $next is falsey)
  
  ### `.next(%filter?)`
  
  Same as calling `.first(%filter, :next)`
  
  ### `.count(%filter?)`
  
  Returns the result of a `select count` for the current filter selection. Providing `%filter` results in `.search(%filter).count`
  
  ### `.delete(%filter?)`
  
  Deletes all rows matching criteria. Providing `%filter` results in `.search(%filter).delete`
  
  ### `.insert(%field-data?)`
  
  Creates a new row with %field-data.
  
  ## Convenience methods
  
  DB::Xoos::Model inheritance allows you to have convenience methods, these methods can act on whatever the current set of filters is.
  
  Consider the following:
  
  Convenience model definition:
  
  ```
  class X::Model::Customer does DB::Xoos::Model['customer'];
  
  # columns and relations
  
  method remove-closed-orders {
    self.closed_orders.delete;
  }
  
  ```
  
  Later in your code:
  
  ```
  my $customers = $dbo.model('Customer');
  
  my $all-customers    = $customers.search({ id => { '>' => -1 } });
  my $single-customers = $customers.search({ id => 5 });
  
  $all-customers.remove-closed-orders;
  # this removes all orders for customers with an id > -1
  $single-customer.remove-closed-orders;
  # this removes all orders for customers with id = 5
  
  ```
  
  # role DB::Xoos::Row
  
  A role to apply to your `::Row::Customer`. If there is no `::Row::Customer` a generic row is created using the column and relationship data specified in the corresponding `Model` and this file is only really necessary if you want to add convenience methods.
  
  When a `class :: does DB::Xoos::Row`, it receives the info from the model and adds the methods for setting/getting field data.
  
  With the model definition above:
  
  ```
  my $invoice-model = $dbo.model('invoice');
  my $invoice = $invoice-model.new-row({
    customer_id => $customer.id,
    amount      => 400,
  });  # this $invoice is NOT in the database until .update
  
  my $old-amount = $invoice.amount; # = 400
  $invoice.amount($invoice.amount * 2);
  my $new-amount = $invoice.amount; # = 800
  
  $invoice.update;
  
  ```
  
  If there is a collision in the naming conventions between your model and the row then you'll need to use `[set|get]-column`
  
  ## Methods
  
  ### `.duplicate`
  
  Duplicates the row omitting the `is-primary-key` field so the subsequent `.save` results in a new row rather than updating
  
  ### `.as-hash`
  
  Returns the current field data for the row as a hash. If there has been unsaved updates to fields then it returns *those* values instead of what is in the database. You can determine whether the row has field-changes with `is-dirty`
  
  ### `.set-column(Str $key, $value)`
  
  Updates the field data for the column (not stored in database until `.update` is called). If you want to `.wrap` a field setter for a certain key, wrap this and filter for the key
  
  ### `.get-column(Str $key)`
  
  Retrieves the value for `$key` with any field changes having priority over data in database, use `.is-dirty`
  
  ### `.get-relation(Str $column, :%spec?)`
  
  It is recommended any Model with a relationship name that conflicts and causes no convenience method to be generated be renamed, but use this if you must. `$customer.orders` is calling essentially `$customer.get-relation('orders')`. Do not provide `%spec` unless you know what you're doing.
  
  ### `.update`
  
  Saves the row in the database. If the field with a positive `is-primary-key` is *set* then it runs and `UPDATE ...` statement, otherwise it `INSERT ...`s and updates the Row's `is-primary-key` field. Ensure you set one field with `is-primary-key`
  
  ## Field validation
  
  It's just this easy:
  
  ```
  has @.columns = [
    qw<...>,
    phone => {
      type     => 'text',
      validate => sub ($new-value) {
        # return Falsey value here for validation to fail
        #   Truthy value will cause validation to succeed
      },
    },
    qw<...>,
  ];
  
  ```
  