Raku Land

DB::Xoos::MySQL

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<...>, ];