# Xoos
Xoos is an ORM designed for convenience and ease of use, it is modeled after DBIx::\* if you're into that kind of thing already (note: some concepts and names have deviated).
(This module was originally named Koos until my friends in Israel let me know that that's a vulgar word in Arabic)
[](https://circleci.com/gh/tony-o/perl6-xoo)
## what works
* relationships
* row object inflation (calling .first on a query returns a Xoos::Row)
* row objects inherit from the model::@columns
* model level convenience methods
* row level convenience methods
* basic handling compounded primary keys
* column validation hooks
* YAML models (auto composed)
* decouple SQL generation from Xoos::Searchable (this includes decoupling the SQL generation from the DB layer) - DB::Pg is intended to be used but epoll is not ported to OSX
## todo
* soft validation of model/table/relationships when model loads
* prefetch relationships option (currently everything is prefetched)
# Usage
Below is a minimum viable model setup for your app. Xoos does _not_ create the table for you, that is up to you.
### lib/app.pm6
```perl6
use DB::Xoos::SQLite;
my DB::Xoos::SQLite $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
```perl6
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
```perl6
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`
### `.new-row(%field-data?)`
Creates a new row with %field-data.
## Convenience methods
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:
```perl6
class X::Model::Customer does Xoos::Model['customer'];
# columns and relations
method remove-closed-orders {
self.closed_orders.delete;
}
```
Later in your code:
```perl6
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 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 Xoos::Row`, it receives the info from the model and adds the methods for setting/getting field data.
With the model definition above:
```perl6
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:
```perl6
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<...>,
];
```