Skip to content
No description, website, or topics provided.
Perl 6
Branch: master
Clone or download
Fetching latest commit…
Cannot retrieve the latest commit at this time.
Permalink
Type Name Latest commit message Commit time
Failed to load latest commit information.
.circleci
lib/DB/Xoos
sql
t
.gitignore
META6.json
README.md

README.md

DB::Xoos::MySQL

DB::Xoos::MySQL is an ORM designed for convenience and ease of use. It is based upon DB::Xoos, provides roles and enhancements and allows you to connect to postgres for ultimate magic.

CircleCI

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<...>,
];
You can’t perform that action at this time.