view release on metacpan or  search on metacpan

lib/BusyBird/StatusStorage/SQLite.pm  view on Meta::CPAN

        $dbh->begin_work();
        my $timeline_id = $self->_get_timeline_id($dbh, $timeline) || $self->_create_timeline($dbh, $timeline);
        if(!defined($timeline_id)) {
            die "Internal error: could not create a timeline '$timeline' somehow.";
        }
        my $sth;
        my $total_count = 0;
        my $put_method = "_put_$mode";
        foreach my $status (@$statuses) {
            my $record = _to_status_record($timeline_id, $status);
            my $count;
            ($count, $sth) = $self->$put_method($dbh, $record, $sth);
            if($count > 0) {
                $total_count += $count;
            }
        }
        my $exceeding_delete_count = 0;
        if($mode ne "update" && $total_count > 0) {
            $exceeding_delete_count = $self->_delete_exceeding_statuses($dbh, $timeline_id);
        }
        $dbh->commit();
        if($exceeding_delete_count > 0) {
            $self->_add_to_delete_count($dbh, $exceeding_delete_count);
        }
        return (undef, $total_count);
    } catch {
        my $e = shift;
        if($dbh) {
            $dbh->rollback();
        }
        return ($e);
    };
    @_ = @results;
    goto $callback;
}

sub _get_timeline_id {
    my ($self, $dbh, $timeline_name) = @_;
    my ($sql, @bind) = $self->{maker}->select('timelines', ['timeline_id'], sql_eq(name => $timeline_name));
    my $record = $dbh->selectrow_arrayref($sql, undef, @bind);
    if(!defined($record)) {
        return undef;
    }
    return $record->[0];
}

sub _create_timeline {
    my ($self, $dbh, $timeline_name) = @_;
    my ($sql, @bind) = $self->{maker}->insert('timelines', [name => "$timeline_name"]);
    $dbh->do($sql, undef, @bind);
    return $self->_get_timeline_id($dbh, $timeline_name);
}

sub _to_status_record {
    my ($timeline_id, $status) = @_;
    croak "status ID must be set" if not defined $status->{id};
    croak "timeline_id must be defined" if not defined $timeline_id;
    my $record = {
        timeline_id => $timeline_id,
        status_id => $status->{id},
        level => $status->{busybird}{level} || 0,
    };
    my $acked_at = $status->{busybird}{acked_at};  ## avoid autovivification
    ($record->{utc_acked_at}, $record->{timezone_acked_at}) = _extract_utc_timestamp_and_timezone($acked_at);
    ($record->{utc_created_at}, $record->{timezone_created_at}) = _extract_utc_timestamp_and_timezone($status->{created_at});
    $record->{content} = to_json($status);
    return $record;
}

sub _from_status_record {
    my ($record) = @_;
    my $status = from_json($record->{content});
    $status->{id} = $record->{status_id};
    if($record->{level} != 0 || defined($status->{busybird}{level})) {
        $status->{busybird}{level} = $record->{level};
    }
    my $acked_at_str = _create_bb_timestamp_from_utc_timestamp_and_timezone($record->{utc_acked_at}, $record->{timezone_acked_at});
    if(defined($acked_at_str) || defined($status->{busybird}{acked_at})) {
        $status->{busybird}{acked_at} = $acked_at_str;
    }
    my $created_at_str = _create_bb_timestamp_from_utc_timestamp_and_timezone($record->{utc_created_at}, $record->{timezone_created_at});
    if(defined($created_at_str) || defined($status->{created_at})) {
        $status->{created_at} = $created_at_str;
    }
    return $status;
}

sub _extract_utc_timestamp_and_timezone {
    my ($timestamp_str) = @_;
    if(!defined($timestamp_str) || $timestamp_str eq '') {
        return ($UNDEF_TIMESTAMP, 'UTC');
    }
    my $datetime = BusyBird::DateTime::Format->parse_datetime($timestamp_str);
    croak "Invalid datetime format: $timestamp_str" if not defined $datetime;
    my $timezone_name = $datetime->time_zone->name;
    $datetime->set_time_zone('UTC');
    my $utc_timestamp = _format_datetime($datetime);
    return ($utc_timestamp, $timezone_name);
}

sub _create_bb_timestamp_from_utc_timestamp_and_timezone {
    my ($utc_timestamp_str, $timezone) = @_;
    if($utc_timestamp_str eq $UNDEF_TIMESTAMP) {
        return undef;
    }
    my $dt = _parse_datetime($utc_timestamp_str);
    $dt->set_time_zone($timezone);
    return BusyBird::DateTime::Format->format_datetime($dt);
}

sub get_statuses {
    my ($self, %args) = @_;
    my $timeline = $args{timeline};
    croak "timeline parameter is mandatory" if not defined $timeline;
    my $callback = $args{callback};
    croak "callback parameter is mandatory" if not defined $callback;
    croak "callback parameter must be a CODEREF" if ref($callback) ne "CODE";
    my $ack_state = defined($args{ack_state}) ? $args{ack_state} : "any";
    if($ack_state ne "any" && $ack_state ne "unacked" && $ack_state ne "acked") {
        croak "ack_state parameter must be either 'any' or 'acked' or 'unacked'";
    }
    my $max_id = $args{max_id};
    my $count = defined($args{count}) ? $args{count} : 'all';
    if($count ne 'all' && !looks_like_number($count)) {
        croak "count parameter must be either 'all' or number";
    }
    my @results = try {
        my $dbh = $self->_get_my_dbh();
        my $timeline_id = $self->_get_timeline_id($dbh, $timeline);
        if(!defined($timeline_id)) {
            return (undef, []);
        }
        my $cond = $self->_create_base_condition($timeline_id, $ack_state);
        if(defined($max_id)) {
            my $max_id_cond = $self->_create_max_id_condition($dbh, $timeline_id, $max_id, $ack_state);
            if(!defined($max_id_cond)) {
                return (undef, []);
            }
            $cond = sql_and([$cond, $max_id_cond]);

lib/BusyBird/StatusStorage/SQLite.pm  view on Meta::CPAN

                next;
            }
            my ($sql, @bind) = $self->{maker}->select(
                'statuses', ['timeline_id', 'status_id'],
                sql_and([sql_eq(timeline_id => $timeline_id), sql_eq(status_id => $status_id)])
            );
            if(!$sth) {
                $sth = $dbh->prepare($sql);
            }
            $sth->execute(@bind);
            my $result = $sth->fetchall_arrayref();
            if(!defined($result)) {
                confess "Statement handle is inactive. Something is wrong.";
            }
            if(@$result) {
                push @ret_contained, $query_elem;
            }else {
                push @ret_not_contained, $query_elem;
            }
        }
        return (undef, \@ret_contained, \@ret_not_contained);
    }catch {
        my $e = shift;
        return ($e);
    };
    @_ = @method_result;
    goto $callback;
}

sub get_timeline_names {
    my ($self) = @_;
    my $dbh = $self->_get_my_dbh();
    my ($sql, @bind) = $self->{maker}->select(
        'timelines', ['name']
    );
    my $result = $dbh->selectall_arrayref($sql, undef, @bind);
    my @return = map { $_->[0] } @$result;
    return @return;
}

1;

__END__

=pod

=head1 NAME

BusyBird::StatusStorage::SQLite - status storage in SQLite database

=head1 SYNOPSIS

    use BusyBird;
    use BusyBird::StatusStorage::SQLite;
    
    my $storage = BusyBird::StatusStorage::SQLite->new(
        path => 'path/to/storage.sqlite3',
        max_status_num => 5000
    );
    
    busybird->set_config(
        default_status_storage => $storage
    );

=head1 DESCRIPTION

This is an implementation of L<BusyBird::StatusStorage> interface.
It stores statuses in an SQLite database.

This storage is synchronous, i.e., all operations block the thread
and the callback is called before the method returns.

=head1 CLASS METHOD

=head2 $storage = BusyBird::StatusStorage::SQLite->new(%args)

The constructor.

Fields in C<%args> are:

=over

=item C<path> => FILE_PATH (mandatory)

Path string to the SQLite database file.
If C<':memory:'> is specified, it creates a temporary in-memory storage.

=item C<max_status_num> => INT (optional, default: 2000)

The maximum number of statuses the storage guarantees to store per timeline.
You cannot expect a timeline to keep more statuses than this number.

=item C<hard_max_status_num> => INT (optional, default: 120% of max_status_num)

The hard limit max number of statuses the storage is able to store per timeline.
When the number of statuses in a timeline exceeds this number,
it deletes old statuses from the timeline so that the timeline has C<max_status_num> statuses.

=item C<vacuum_on_delete> => INT (optional, default: 200% of max_status_num)

The status storage automatically executes C<VACUUM> every time this number of statuses are
deleted from the storage. B<The number is for the whole storage, not per timeline>.

If you set this option less than or equal to 0, it never C<VACUUM> itself.


=back

=head1 OBJECT METHODS

L<BusyBird::StatusStorage::SQLite> implements all object methods in L<BusyBird::StatusStorage>.
In addition to it, it has the following methods.

=head2 $storage->vacuum()

Executes SQL C<VACUUM> on the database.

=head2 @timeline_names = $storage->get_timeline_names()

Returns all timeline names in the C<$storage>.