--- a/Service.pm +++ b/Service.pm @@ -34,46 +34,4 @@ $X->set_primary_key("id"); $X->has_many( service_types => $X, 'parent_id' ); -sub sqlt_deploy_hook { - my( $self, $sqlt_table ) = @_; - - my $sqlt = $sqlt_table->schema; - $sqlt->add_procedure( - name => 'service_level_tree', - parameters => [ - { argmode => 'in', type => 'integer' }, - ], - extra => { - returns => { type => 'table( id int, parent_id int, name text, display text, - definitions => [ - { language => 'sql' }, - { attribute => 'STABLE' }, - { quote => "\$\$\n", body => <<' FUNC' =~ s!^\t!!grm =~ s!;\n!;/**/\n - WITH RECURSIVE service_level_tree (id, parent_id, name, display, depth ) AS ( - SELECT - id, - parent_id, - name, - display, - 1 - FROM service_level - WHERE id = $1 - UNION - SELECT - sl.id, - sl.parent_id, - sl.name, - sl.display, - depth +1 - FROM service_level_tree t - INNER JOIN service_level sl ON sl.id = t.parent_id - WHERE depth < 10 -- Prohibit deep hierarchy - ) - SELECT * FROM service_level_tree; - FUNC - ]}); - -} - - 1; diff --git a/ServiceLevel.pm b/S index 73fa9dea..f7da48c8 100644 --- a/ServiceLevel.pm +++ b/ServiceLevel.pm @@ -34,4 +34,46 @@ $X->add_unique_constraint([ 'parent_id', 'name' ]); $X->has_many( service_levels => $X, 'parent_id' ); + +sub sqlt_deploy_hook { + my( $self, $sqlt_table ) = @_; + + my $sqlt = $sqlt_table->schema; + $sqlt->add_procedure( + name => 'service_level_tree', + parameters => [ + { argmode => 'in', type => 'integer' }, + ], + extra => { + returns => { type => 'table( id int, parent_id int, name text, display text, + definitions => [ + { language => 'sql' }, + { attribute => 'STABLE' }, + { quote => "\$\$\n", body => <<' FUNC' =~ s!^\t!!grm =~ s!;\n!;/**/\n + WITH RECURSIVE service_level_tree (id, parent_id, name, display, depth ) AS ( + SELECT + id, + parent_id, + name, + display, + 1 + FROM service_level + WHERE id = $1 + UNION + SELECT + sl.id, + sl.parent_id, + sl.name, + sl.display, + depth +1 + FROM service_level_tree t + INNER JOIN service_level sl ON sl.id = t.parent_id + WHERE depth < 10 -- Prohibit deep hierarchy + ) + SELECT * FROM service_level_tree; + FUNC + ]}); +} + + 1;