[Dbix-class] Multi-tennant application/data and dbix-class

[Vladimir Melnik]

Hi,

It seems to be exactly what I has to do to construct a multi-temporal database, when I need to keep each record's history, so I don’t change a row's values (all but `known_till`), creating new records instead of that. So, the `id` is just a row's ID, but I had to add the instance's ID as `<table_name>_id`. :-)

Keeping multiple records ("known" and "forgotten" also) and using tenant's ID let me do something like that:

    my @resource_pieces = $self
        ->mtemporal_filter(as_at => $now, all => { _rest => 0xff })
        ->mtemporal_update(
            as_at               => $now,
            leave_satisfying    => 1,
            spawn_satisfying    => 1,
            yield_satisfying    => 1,
            record              => {
                valid_from
                    => $record_valid_from,
                valid_till
                    => $record_valid_till,
                parent_resource_piece_id
                    => $record->{'parent_resource_piece_id'},
                resource_type_id
                    => $record->{'resource_type_id'},
                resource_set_id
                    => $record->{'resource_set_id'},
                handle
                    => $record->{'handle'},
                description
                    => $record->{'description'}
            },
            filter_matchings => [
                qw(
                    resource_type_id
                    resource_set_id
                    handle
                ),
                $description_strict ? qw(description) : qw()
            ],
            gentle_matchings => [
                qw(
                    valid_from
                    valid_till
                )
            ]

        )
        ->all;

^^^ the sentence above creates a new record with some values, but it does spawn a new tenant only if there are no records with the same `resource_type_id`, `resource_set_id`, `handle` and `description` (if it's been asked to). If something is already present, it clones the last state of this tenant as a new record with the same tenant ID and sets the `known_till` of the "known" rows to the current time.

id | known_from | known_till | valid_from | valid_till | resource_piece_id | other values
--- The initial state is: ---
 1 | 16:20      | FOREVER    | 07:40      | 13:13      | 13                | foo, bar, baz
--- At 19:40 we'd changed of a tenant having certain values (foo & bar are "matchings"): ---
 1 | 16:20      | 19:40      | 07:40      | 13:13      | 13                | foo, bar, baz
 2 | 16:20      | FOREVER    | 07:40      | 13:13      | 13                | foo, bar, xyz
--- ^^^ So, baz had been changed to xyz, but The tenant ID has been kept the same - 13th ---

And, if it's not a secret, what's the goal that you are achieving with tenant IDs, is it same multi-temporal mania? :-)

On Wed, Jan 03, 2018 at 10:37:58PM +0200, Aaron Trevena wrote:
> Hi All,
> 
> I was wondering if anyone can share any success, tips and problems of
> using dbix-class in a multi-tenant application.
> 
> I'm looking at using tenant id in the main tables, and thinking best
> approach will be to add logic to a resultset base class or role to
> hook in forcing queries to restrict by tenant id, but would really
> like to hear what anybody who's done this stuff already did and what
> worked or not and any gotchas,
> 
> Thanks,
> 
> A.
> 
> -- 
> Aaron J Trevena, BSc Hons
> http://www.aarontrevena.co.uk
> LAMP System Integration, Development and Consulting
> 
> _______________________________________________
> List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
> IRC: irc.perl.org#dbix-class
> SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
> Searchable Archive:
> http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk

-- 
V.Melnik