Schema.md

UpRooted::Schema

Guide for:

• People who want to write UpRooted::Schema auto-discovery plugins for various database technologies.
• Cases when auto discovery failed and UpRooted::Schema must be manually tuned.

SYNOPSIS

Load required modules:

use UpRooted::Schema;
use UpRooted::Table;
use UpRooted::Column;
use UpRooted::Relation;

Define UpRooted::Schema:

my $library = UpRooted::Schema.new( name => 'library' );

Add UpRooted::Tables to UpRooted::Schema:

UpRooted::Table.new( :$schema, name => 'authors' );
UpRooted::Table.new( :$schema, name => 'books' );

Add UpRooted::Columns to UpRooted::Tables:

with $library.table( 'authors' ) -> $table {
    UpRooted::Column.new( :$table, name => 'id', type => 'bigint', :!is-nullable );
    UpRooted::Column.new( :$table, name => 'first_name', :is-nullable );
    UpRooted::Column.new( :$table, name => 'last_name', :!is-nullable );
}

with $library.table( 'books' ) -> $table {
    UpRooted::Column.new( :$table, name => 'id', type => 'bigint', :!is-nullable );
    UpRooted::Column.new( :$table, name => 'author_id', type => 'bigint', :!is-nullable );
    UpRooted::Column.new( :$table, name => 'title', :is-nullable );
}

Note that for UpRooted::Column:

• Nullability is super important for proper UpRooted::Tree construction later.
• You may provide original database type to help UpRooted::Writer make decision how to store data.

Connect UpRooted::Tables with UpRooted::Relations:

UpRooted::Relation.new(
    name => 'who-wrote-what',
    parent-columns => $library.table( 'authors' ).columns( 'id' ),
    child-columns => $library.table( 'books' ).columns( 'author_id' )
);

RULES

It is not meant to be user-friendly ORM. Limited interface is specifically designed to define and analyze database layout for further data extraction.

Bottom-up composition logic is used. For example you do not tell UpRooted::Schema that it has some UpRooted::Table but instead you define UpRooted::Table that claims to be in UpRooted::Schema. This makes it easier to bulk-load data from denormalized information_schemas in various databases.

Top-down breacrumb trails like $schema.table( 'x' ).column( 'y' ) will become automatically available and you can use them during further composition. Call p6doc UpRooted::Table for example to find out all available attributes.

Never add to UpRooted::Schema any virtual objects, such as views or materialized columns.

Composition will die on slightest sign of inconsistency, for example requesting unknown UpRooted::Column name from UpRooted::Table.

ADVANCED

Multi column relations

Just select multiple UpRooted::Columns from the same UpRooted::Table when defining UpRooted::Relation.

UpRooted::Relation.new(
    name => 'compatibility',
    parent-columns => $library.table( 'cars' ).columns( 'model', 'year' ),
    child-columns => $library.table( 'parts' ).columns( 'car_model', 'car_year' )
);

Arity must be the same.

Blocking relations

Sometimes one UpRooted::Relation between two UpRooted::Tables is less efficient to join these tables than another UpRooted::Relation. To block single UpRooted::Relation between two UpRooted::Tables set is-blocked attribute:

$parent-table.child-relation( 'some-relation-name' ).is-blocked = True;

Sometimes there is UpRooted::Table that should not be included in UpRooted::Tree, for example referral connections causing jailbreak or not important large logs. To exclude UpRooted::Table from UpRooted::Tree block all UpRooted::Relations leading to this table:

for $schema.tables -> $table {
    for table.children-relations -> $relation {
        $relation.is-blocked = True if $relation.child-columns.first.table.name eq 'some-table-name';
    }
}

Blocking must be applied before UpRooted::Tree is derived.

Cross schema relations

Just define two UpRooted::Schemas with UpRooted::Tables and UpRooted::Columns in them. Then connect UpRooted::Tables from different UpRooted::Schemas using UpRooted::Relations.