Rand Stats

UpRooted

zef:bbkr

Extract subtrees of data from relational databases in Raku language.

test

DESCRIPTION

This module allows to extract tree of data from relational database and process it. Tree of data may be for example some user account and all records in related tables that belong to him. Useful for cases like:

This module is NOT continuous replication tool (like for example Debezium).

SYNOPSIS

Let's say you have MySQL database and want to save user of id = 1 from users table with all his data from other tables to .sql file.

my $connection = DBIish.connect( 'mysql', host => ..., port => ..., ... );

use UpRooted::Schema::MySQL;
my $schema = UpRooted::Schema::MySQL.new( :$connection );

use UpRooted::Tree;
my $tree = UpRooted::Tree.new( root-table => $schema.table( 'users' ) );

use UpRooted::Reader::MySQL;
my $reader = UpRooted::Reader::MySQL.new( :$connection, :$tree );

use UpRooted::Writer::MySQLFile;
my $writer = UpRooted::Writer::MySQLFile.new;
    
$writer.write( $reader, id => 1 );

Your user will be saved as out.sql file.

DOCKER

If you do not have Raku installed UpRooted is also available as Docker image.

MODULES

This section explains role of every module in UpRooted stack and tells which variants of each module are available.

UpRooted::Schema

UpRooted::Schema describes relation between UpRooted::Tables.

It can be discovered automatically by plugins like:

In rare cases you may need to construct or fine tune UpRooted::Schema manually. For example if you use MySQL MyISAM engine or MySQL partitioning. Or you use PostgreSQL foreign keys relying on unique keys instead of unique constraints. Without proper foreign keys relations between UpRooted::Tables cannot be auto discovered and must be defined by hand. There is separate manual describing this process.

Creating UpRooted::Schema must be done only once per schema.

UpRooted::Tree

UpRooted::Tree knows how to reach each leaf UpRooted::Table from chosen root UpRooted::Table. It also resolves UpRooted::Tables order correctly to satisfy foreign key constraints, which is important for example when writing data tree to online database.

You can derive many UpRooted::Trees from single UpRooted::Schema, depending on which root UpRooted::Table is used.

Creating UpRooted::Tree must be done only once per root UpRooted::Table.

UpRooted::Reader

UpRooted::Reader transforms UpRooted::Tree to series of queries allowing to extract data that belong to given row in root UpRooted::Table. This is always database engine specific.

Available variants:

Creating UpRooted::Reader must be done only once per UpRooted::Tree.

UpRooted::Writer

UpRooted::Writer writes data provided by UpRooted::Reader.

Available variants:

Note that UpRooted::Reader and UpRooted::Writer are independent. You can read from MySQL database and write directly to PostgreSQL database if needed.

Disabling UpRooted::Schema name in Fully Qualified Names in queries may be useful for example when you need to write data to whatever schema is / will be used in connection. To do so provide flag to constructor like this: UpRooted::Writer::MySQL.new( :!use-schema-name ). To find other options accepted by each UpRooted::Writer call p6doc on chosen module.

Note that not every UpRooted::Writer can save every data type provided by UpRooted::Reader. For example MySQL does not support PostgreSQL array types and will save them as joined strings.

CACHING

Creating instances of modules mentioned above are heavy operations, especially on large schemas. You can reuse all of them for great speed improvement.

For example if you need to save multiple users you need to create UpRooted::Schema, UpRooted::Tree, UpRooted::Reader and UpRooted::Writer only once.

my $schema = ...;
my $tree = ...;
my $reader = ...;
my $writer = UpRooted::Writer::MySQLFile.new(
    # name generator to avoid file name conflicts
    file-naming => sub ( $tree, %conditions ) {
        %conditions{ 'id' } ~ '.sql'
    }
);
    
$writer.write( $reader, id => 1 );
$writer.write( $reader, id => 2 );
$writer.write( $reader, id => 3 );

It will create 1.sql, 2.sql, 3.sql files without rediscovering everything every time.

EXTENSIONS

UpRooted is written with extensibility in mind.

Base version will focus on MySQL and PostgreSQL databases, as those are the two most common open source ones.

However if you need for example to create UpRooted::Schema from Red ORM, make UpRooted::Writer to save set of CSV files or even implement whole UpRooted stack to work with MS SQL - do not be afraid to implement it. Interfaces are simple, well documented and checking existing MySQL / PostgreSQL code will give you the idea how little is actually needed to extend UpRooted capabilities.

SCHEMA DESIGN ISSUES

Relational databases are extremly flexible in terms of schema design. However there are few rules you must follow if you want to work with data trees.

If you use UpRooted but do not get expected results please go through this list carefully before creating issue on GitHub.

Data is transformed (extra rows, counters are too high) when written to another database

Cause: There are ON INSERT triggers changing data.

This one is very easy to overlook. Triggers are convenient, relaible and cheap way of managing entangled data state. For example when row is inserted into orders table increase counter in order_stats table by 1.

But those triggers will get in a way of copying / moving data trees literally between databases, because they will "replay" their logic when data tree is written to another database.

Fix: When designing schema with data tree reading / writing in mind triggers can be only used to verify constraints.

Data tree is not writable to another database without disabling foreign key constraints

Cause 1: There are self-looped tables. Usually implementing tree logic.

By default UpRooted resolves dependencies and whole purpose of UpRooted::Tree is to provide data in correct order. However it may be not possible if table is directly referencing itself.

    +----------+
    | users    |
    +----------+
    | id       |----------------------+
    | login    |                      |
    | password |                      |
    +----------+                      |
                                      |
              +--------------------+  |
              | albums             |  |
              +--------------------+  |
          +---| id                 |  |
          |   | user_id            |>-+
          +--<| parent_album_id    |
              | name               |
              +--------------------+

For example user has album with id = 2 as subcategory of album with id = 1. Then he rearranges his collection, so that the album with id = 2 is on top. In such scenario if database returned data tree rows in primary key order then it will not be possible to insert album with id = 1 because it requires presence of album with id = 2.

Hint 1:

You can check if UpRooted::Tree has loops by running:

my $tree = ...;
say $tree.paths.grep: *.is-looped;

Fix 1: Have separate table that establishes tree hierarchy between rows:

    +----------+
    | users    |
    +----------+
    | id       |----------------------+
    | login    |                      |
    | password |                      |
    +----------+                      |
                                      |
              +--------------------+  |
              | albums             |  |
              +--------------------+  |
      +-+=====| id                 |  |
      | |     | user_id            |>-+
      | |     | name               |
      | |     +--------------------+
      | |
      | |    +------------------+
      | |    | album_hierarchy  |
      | |    +------------------+
      | +---<| parent_album_id  |
      +-----<| child_album_id   |
             +------------------+

Cause 2: Jailbreak. Data from one tree links to data from another tree. Usually implementing relations or activities between users.

      +----------+        +------------+
      | users    |        | blog_posts |
      +----------+        +------------+
    +-| id       |---+    | id         |---+
    | | login    |   +---<| user_id    |   |
    | | password |        | text       |   |
    | +----------+        +------------+   |
    |                                      |
    |    +--------------+                  |
    |    | comments     |                  |
    |    +--------------+                  |
    |    | blog_post_id |>-----------------+
    +---<| user_id      |
         | text         |
         +--------------+

For example user with id = 1 created a blog post that was commented by user with id = 2. Now record from comments table has two owners, one direct (belongs to user with id = 2) and one indirect (belongs to post written by user with id = 1`).

Fix 2: Unfortunately the only way to detach two data trees in such case is to remove one of foreign key constraints.

Some rows from table are missing

Cause: Ambiguity in correct way of reaching rows in table. Only multiple nullable relation paths to this table exist.

                  +----------+
                  | users    |
                  +----------+
    +-------------| id       |-------------+
    |             | login    |             |
    |             | password |             |
    |             +----------+             |
    |                                      |
    |  +-----------+        +-----------+  |
    |  | time      |        | distance  |  |
    |  +-----------+        +-----------+  |
    |  | id        |--+  +--| id        |  |
    +-<| user_id   |  |  |  | user_id   |>-+
       | amount    |  |  |  | amount    |
       +-----------+  |  |  +-----------+
                      |  |
                   (nullable)
                      |  |
                      |  |
             +--------+  +---------+
             |                     |
             |   +-------------+   |
             |   | parts       |   |
             |   +-------------+   |
             +--<| time_id     |   |
                 | distance_id |>--+
                 | name        |
                 +-------------+

This time our product is application that helps you with car maintenance schedule. Our users car has 4 tires that must be replaced after 10 years or 100000km and 4 spark plugs that must be replaced after 100000km. So 4 indistinguishable rows for tires are added to parts table (they reference both time and distance) and 4 indistinguishable rows are added for spark plugs (they reference only distance).

Now to extract to shard we have to find which rows from parts table does he own. By following relations through time table we will get 4 tires. But because this path is nullable at some point we are not sure if we found all records. And indeed, by following relations through distance table we found 4 tires and 4 spark plugs. Since this path is also nullable at some point we are not sure if we found all records. So we must combine result from time and distance paths, which gives us... 8 tires and 4 spark plugs? Well, that looks wrong. Maybe let's group it by time and distance pair, which gives us... 1 tire and 1 spark plug? So depending how you combine indistinguishable rows from many nullable paths to get final row set, you may suffer either data duplication or data loss.

To understand this issue better consider two answers to question How many legs does the horse have?:

Hint:

You can check if UpRooted::Tree has ambiguities by running:

my $tree = ...;
say $tree.paths.grep: *.is-ambiguous;

Fix: Redesign schema so that at least one not nullable relations path leads to every table.