X-Git-Url: http://wagnertech.de/git?a=blobdiff_plain;f=SL%2FDB%2FHelper%2FActsAsList.pm;h=7ca63a353fde47542a4a3740076a770fa488190a;hb=08e48f66590f580cbe2c8e3df76883d88b4c0fef;hp=a6450533d32fb9c793eda902c52359962c36ae04;hpb=5fd2cf413e611047a3b8d667378a258a7955941e;p=kivitendo-erp.git diff --git a/SL/DB/Helper/ActsAsList.pm b/SL/DB/Helper/ActsAsList.pm index a6450533d..7ca63a353 100644 --- a/SL/DB/Helper/ActsAsList.pm +++ b/SL/DB/Helper/ActsAsList.pm @@ -3,20 +3,25 @@ package SL::DB::Helper::ActsAsList; use strict; use parent qw(Exporter); -our @EXPORT = qw(move_position_up move_position_down); +our @EXPORT = qw(move_position_up move_position_down add_to_list remove_from_list reorder_list configure_acts_as_list + get_previous_in_list get_next_in_list get_full_list); use Carp; +use SL::X; + +my %list_spec; sub import { my ($class, @params) = @_; my $importing = caller(); + configure_acts_as_list($importing, @params); + $importing->before_save( sub { SL::DB::Helper::ActsAsList::set_position(@_) }); $importing->before_delete(sub { SL::DB::Helper::ActsAsList::remove_position(@_) }); - # Use 'goto' so that Exporter knows which module to import into via - # 'caller()'. - goto &Exporter::import; + # Don't 'goto' to Exporters import, it would try to parse @params + __PACKAGE__->export_to_level(1, $class, @EXPORT); } # @@ -33,18 +38,173 @@ sub move_position_down { do_move($self, 'down'); } +sub remove_from_list { + my ($self) = @_; + + return $self->db->with_transaction(sub { + remove_position($self); + + # Set to -1 manually because $self->update_attributes() would + # trigger the before_save() hook from this very plugin assigning a + # number at the end of the list again. + my $table = $self->meta->table; + my $column = column_name($self); + my $primary_key_col = ($self->meta->primary_key)[0]; + my $sql = <db->dbh->do($sql, undef, $self->$primary_key_col); + $self->$column(undef); + }); +} + +sub add_to_list { + my ($self, %params) = @_; + + croak "Invalid parameter 'position'" unless ($params{position} || '') =~ m/^ (?: before | after | first | last ) $/x; + + my $column = column_name($self); + + $self->remove_from_list if ($self->$column // -1) != -1; + + if ($params{position} eq 'last') { + set_position($self); + $self->save; + return; + } + + my $table = $self->meta->table; + my $primary_key_col = ($self->meta->primary_key)[0]; + my ($group_by, @values) = get_group_by_where($self); + $group_by = " AND ${group_by}" if $group_by; + my $new_position; + + if ($params{position} eq 'first') { + $new_position = 1; + + } else { + # Can only be 'before' or 'after' -- 'last' has been checked above + # already. + + my $reference = $params{reference}; + croak "Missing parameter 'reference'" if !$reference; + + my $reference_pos; + if (ref $reference) { + $reference_pos = $reference->$column; + } else { + ($reference_pos) = $self->db->dbh->selectrow_array(qq|SELECT ${column} FROM ${table} WHERE ${primary_key_col} = ?|, undef, $reference); + } + + $new_position = $params{position} eq 'before' ? $reference_pos : $reference_pos + 1; + } + + my $query = < ?) + ${group_by} +SQL + + return $self->db->with_transaction(sub { + $self->db->dbh->do($query, undef, $new_position - 1, @values); + $self->update_attributes($column => $new_position); + }); +} + +sub get_next_in_list { + my ($self) = @_; + return get_previous_or_next($self, 'next'); +} + +sub get_previous_in_list { + my ($self) = @_; + return get_previous_or_next($self, 'previous'); +} + +sub get_full_list { + my ($self) = @_; + + my $group_by = get_spec(ref $self, 'group_by') || []; + $group_by = [ $group_by ] if $group_by && !ref $group_by; + my @where = map { ($_ => $self->$_) } @{ $group_by }; + + return $self->_get_manager_class->get_all(where => \@where, sort_by => column_name($self) . ' ASC'); +} + +sub reorder_list { + my ($class_or_self, @ids) = @_; + + return 1 unless @ids; + + my $self = ref($class_or_self) ? $class_or_self : $class_or_self->new; + my $column = column_name($self); + my $result = $self->db->with_transaction(sub { + my $query = qq|UPDATE | . $self->meta->table . qq| SET ${column} = ? WHERE id = ?|; + my $sth = $self->db->dbh->prepare($query) || SL::X::DBUtilsError->throw(msg => 'reorder_list error', db_error => $self->db->dbh->errstr); + + foreach my $new_position (1 .. scalar(@ids)) { + $sth->execute($new_position, $ids[$new_position - 1]) || SL::X::DBUtilsError->throw(msg => 'reorder_list error', db_error => $sth->errstr); + } + + $sth->finish; + + 1; + }); + + return $result; +} + +sub configure_acts_as_list { + my ($class, %params) = @_; + + $list_spec{$class} = { + group_by => $params{group_by}, + column_name => $params{column_name}, + }; +} + # # Helper functions # +sub get_group_by_where { + my ($self) = @_; + + my $group_by = get_spec(ref $self, 'group_by') || []; + $group_by = [ $group_by ] if $group_by && !ref $group_by; + + my (@where, @values); + foreach my $column (@{ $group_by }) { + my $value = $self->$column; + push @values, $value if defined $value; + push @where, defined($value) ? "(${column} = ?)" : "(${column} IS NULL)"; + } + + return (join(' AND ', @where), @values); +} + sub set_position { my ($self) = @_; my $column = column_name($self); + my $value = $self->$column; - if (!defined $self->$column) { - my $max_position = $self->db->dbh->selectrow_arrayref(qq|SELECT COALESCE(max(${column}), 0) FROM | . $self->meta->table)->[0]; - $self->$column($max_position + 1); - } + return 1 if defined($value) && ($value != -1); + + my $table = $self->meta->table; + my ($group_by, @values) = get_group_by_where($self); + $group_by = " AND ${group_by}" if $group_by; + my $sql = < -1) + ${group_by} +SQL + + my $max_position = $self->db->dbh->selectrow_arrayref($sql, undef, @values)->[0]; + $self->$column($max_position + 1); return 1; } @@ -54,37 +214,99 @@ sub remove_position { my $column = column_name($self); $self->load; - if (defined $self->$column) { - $self->_get_manager_class->update_all(set => { $column => \"${column} - 1" }, - where => [ $column => { gt => $self->$column } ]); - } + my $value = $self->$column; + return 1 unless defined($value) && ($value != -1); + + my $table = $self->meta->table; + my ($group_by, @values) = get_group_by_where($self); + $group_by = ' AND ' . $group_by if $group_by; + my $sql = < ?) + ${group_by} +SQL + + $self->db->dbh->do($sql, undef, $value, @values); return 1; } sub do_move { my ($self, $direction) = @_; - my $column = column_name($self); croak "Object has not been saved yet" unless $self->id; - croak "No position set yet" unless defined $self->$column; - my ($comp_sql, $comp_rdbo, $min_max, $plus_minus) = $direction eq 'up' ? ('<', 'ge', 'max', '+') : ('>', 'le', 'min', '-'); + my $column = column_name($self); + my $old_position = $self->$column; + croak "No position set yet" unless defined($old_position) && ($old_position != -1); - my $new_position = $self->db->dbh->selectrow_arrayref(qq|SELECT ${min_max}(${column}) FROM | . $self->meta->table . qq| WHERE ${column} ${comp_sql} | . $self->$column)->[0]; + my $table = $self->meta->table; + my ($comp_sel, $comp_upd, $min_max, $plus_minus) = $direction eq 'up' ? ('<', '>=', 'MAX', '+') : ('>', '<=', 'MIN', '-'); + my ($group_by, @values) = get_group_by_where($self); + $group_by = ' AND ' . $group_by if $group_by; + my $sql = < -1) + AND (${column} ${comp_sel} ?) + ${group_by} +SQL + + my $new_position = $self->db->dbh->selectrow_arrayref($sql, undef, $old_position, @values)->[0]; return undef unless defined $new_position; - $self->_get_manager_class->update_all(set => { $column => $self->$column }, - where => [ $column => $new_position ]); + $sql = <db->dbh->do($sql, undef, $old_position, $new_position, @values); + $self->update_attributes($column => $new_position); } +sub get_previous_or_next { + my ($self, $direction) = @_; + + my $asc_desc = $direction eq 'next' ? 'ASC' : 'DESC'; + my $comparator = $direction eq 'next' ? '>' : '<'; + my $table = $self->meta->table; + my $column = column_name($self); + my $primary_key_col = ($self->meta->primary_key)[0]; + my ($group_by, @values) = get_group_by_where($self); + $group_by = " AND ${group_by}" if $group_by; + my $sql = <db->dbh->selectrow_arrayref($sql, undef, $self->$column, @values) || [])->[0]; + + return $id ? $self->_get_manager_class->find_by(id => $id) : undef; +} + sub column_name { my ($self) = @_; + my $column = get_spec(ref $self, 'column_name'); + return $column if $column; return $self->can('sortkey') ? 'sortkey' : 'position'; } +sub get_spec { + my ($class, $key) = @_; + + return undef unless $list_spec{$class}; + return $list_spec{$class}->{$key}; +} + 1; __END__ @@ -95,12 +317,12 @@ __END__ =head1 NAME SL::DB::Helper::ActsAsList - Mixin for managing ordered items by a -column I or I +column =head1 SYNOPSIS package SL::DB::SomeObject; - use SL::DB::Helper::ActsAsList; + use SL::DB::Helper::ActsAsList [ PARAMS ]; package SL::Controller::SomeController; ... @@ -125,7 +347,45 @@ in the table plus one. When the object is deleted all positions greater than the object's old position are decreased by one. -=head1 FUNCTIONS +C will be given to L and can be used to +set the column name. + +=head1 CLASS FUNCTIONS + +=over 4 + +=item C + +Configures the mixin's behaviour. Will get called automatically with the +include parameters. C<%params> can contain the following values: + +=over 2 + +=item C + +The name of the column containing the position. If not set explicitly +then the mixin will use C if the model contains such a column +(only for legacy tables) and C otherwise. + +=item C + +An optional column name (or array reference of column names) by which +to group. If a table contains items for several distinct sets and each +set has its own sorting then this can be used. + +An example would be requirement spec text blocks. They have a column +called C that selects where to output the text blocks +(either before or after the sections). Furthermore these text blocks +each belong to a single requirement spec document. So each combination +of C and C should have its own +set of C values, which can be achieved by configuring this +mixin with C. + +=back + +=back + +=head1 INSTANCE FUNCTIONS =over 4 @@ -139,6 +399,63 @@ regarding their sort order by exchanging their C values. Swaps the object with the object one step below the current one regarding their sort order by exchanging their C values. +=item C + +Adds this item to the list. The parameter C is required and +can be one of C, C, C and C. With C +the item is inserted as the first item in the list and all other +item's positions are shifted up by one. For C the +item is inserted at the end of the list. + +For C and C an additional parameter C is +required. This is either a Rose model instance or the primary key of +one. The current item will then be inserted either before or after the +referenced item by shifting all the appropriate item positions up by +one. + +If C<$self>'s positional column is already set when this function is +called then L will be called first before anything +else is done. + +After this function C<$self>'s positional column has been set and +saved to the database. + +=item C + +Sets this items positional column to C<-1>, saves it and moves all +following items up by 1. + +=item C + +Fetches the previous item in the list. Returns C if C<$self> is +already the first one. + +=item C + +Fetches the next item in the list. Returns C if C<$self> is +already the last one. + +=item C + +Fetches all items in the same list as C<$self> and returns them as an +array reference. + +=item C + +Re-orders the objects given in C<@ids> by their position in C<@ids> by +updating all of their positional columns. Each element in +C<@positions> must be the ID of an object. The new position is the +ID's index inside C<@ids> plus one (meaning the first element's new +position will be 1 and not 0). + +This works by executing SQL "UPDATE" statements directly. + +Returns the result of the whole transaction (trueish in case of +success). + +This method can be called both as a class method or an instance +method. + =back =head1 BUGS