379 lines
8.1 KiB
Perl
379 lines
8.1 KiB
Perl
package II::DB;
|
|
|
|
use SQL::Abstract;
|
|
use DBI;
|
|
use utf8;
|
|
|
|
use Data::Dumper;
|
|
|
|
sub new {
|
|
my $class = shift;
|
|
|
|
my $dbh = DBI->connect( "dbi:SQLite:dbname=ii.sql", "", "" );
|
|
my $sql = SQL::Abstract->new();
|
|
|
|
my $self = {
|
|
_dbh => $dbh,
|
|
_sql => $sql,
|
|
};
|
|
|
|
bless $self, $class;
|
|
return $self;
|
|
}
|
|
|
|
# Check message hash
|
|
sub check_hash {
|
|
my ( $self, $hash, $echo ) = @_;
|
|
my $dbh = $self->{_dbh};
|
|
|
|
my $q = "select hash from echo where hash='$hash' and echo='$echo'";
|
|
my $sth = $dbh->prepare($q);
|
|
$sth->execute();
|
|
|
|
while ( my @h = $sth->fetchrow_array() ) {
|
|
my ($base_hash) = @h;
|
|
if ( $hash eq $base_hash ) {
|
|
return 1;
|
|
} else {
|
|
return 0;
|
|
}
|
|
}
|
|
}
|
|
|
|
# Begin transaction
|
|
sub begin {
|
|
my ($self) = @_;
|
|
my $dbh = $self->{_dbh};
|
|
|
|
# Begin transaction
|
|
$dbh->do('BEGIN');
|
|
}
|
|
|
|
# Commit transaction
|
|
sub commit {
|
|
my ($self) = @_;
|
|
my $dbh = $self->{_dbh};
|
|
|
|
# Commmit transaction
|
|
$dbh->do('COMMIT');
|
|
}
|
|
|
|
sub write_echo {
|
|
my ( $self, %data ) = @_;
|
|
my $dbh = $self->{_dbh};
|
|
my $sql = $self->{_sql};
|
|
|
|
my ( $stmt, @bind ) = $sql->insert( 'echo', \%data );
|
|
|
|
my $sth = $dbh->prepare($stmt);
|
|
$sth->execute(@bind);
|
|
$sth->finish();
|
|
}
|
|
|
|
sub write_out {
|
|
my ( $self, %data ) = @_;
|
|
my $dbh = $self->{_dbh};
|
|
my $sql = $self->{_sql};
|
|
|
|
my ( $stmt, @bind ) = $sql->insert( 'output', \%data );
|
|
|
|
my $sth = $dbh->prepare($stmt);
|
|
$sth->execute(@bind);
|
|
|
|
print "Message writed to DB!\n";
|
|
}
|
|
|
|
sub del_out {
|
|
my ( $self, $hash ) = @_;
|
|
my $dbh = $self->{_dbh};
|
|
|
|
my $q = "delete from output where hash='$hash' and send=0";
|
|
my $sth = $dbh->prepare($q);
|
|
$sth->execute();
|
|
$sth->finish();
|
|
}
|
|
|
|
sub update_out {
|
|
my ( $self, $hash ) = @_;
|
|
my $dbh = $self->{_dbh};
|
|
|
|
my $q = "update output set send=1 where hash='$hash'";
|
|
my $sth = $dbh->prepare($q);
|
|
$sth->execute();
|
|
}
|
|
|
|
sub write {
|
|
my ( $self, %data ) = @_;
|
|
my $dbh = $self->{_dbh};
|
|
my $sql = $self->{_sql};
|
|
|
|
my ( $stmt, @bind ) = $sql->insert( 'messages', \%data );
|
|
|
|
my $sth = $dbh->prepare($stmt);
|
|
$sth->execute(@bind);
|
|
$sth->finish();
|
|
}
|
|
|
|
sub select_out {
|
|
my ($self) = @_;
|
|
my $dbh = $self->{_dbh};
|
|
|
|
my $q
|
|
= "select from_user, to_user, subg, time, echo, post, hash, base64 from output where send=0";
|
|
|
|
my $sth = $dbh->prepare($q);
|
|
$sth->execute();
|
|
|
|
my @posts;
|
|
while ( my @hash = $sth->fetchrow_array() ) {
|
|
my ( $from, $to, $subg, $time, $echo, $post, $h, $base64 ) = @hash;
|
|
my $data = {
|
|
from => "$from",
|
|
to => "$to",
|
|
subg => "$subg",
|
|
time => $time,
|
|
echo => "$echo",
|
|
post => "$post",
|
|
hash => $h,
|
|
base64 => $base64,
|
|
};
|
|
push( @posts, $data );
|
|
}
|
|
|
|
return @posts;
|
|
}
|
|
|
|
sub select_index {
|
|
my ( $self, $limit ) = @_;
|
|
my $dbh = $self->{_dbh};
|
|
|
|
my $q = "select hash from messages order by time desc limit $limit";
|
|
|
|
my $sth = $dbh->prepare($q);
|
|
$sth->execute();
|
|
|
|
my @hashes;
|
|
while ( my @hash = $sth->fetchrow_array() ) {
|
|
my ($h) = @hash;
|
|
push( @hashes, $h );
|
|
}
|
|
|
|
return @hashes;
|
|
}
|
|
|
|
sub select_subg {
|
|
my ( $self, $echo ) = @_;
|
|
|
|
}
|
|
|
|
# Select user messages
|
|
sub select_user {
|
|
my ( $self, $user ) = @_;
|
|
my $dbh = $self->{_dbh};
|
|
|
|
my $q
|
|
= "select from_user, to_user, subg, time, echo, post, hash from messages where from_user='$user' order by time desc";
|
|
|
|
my $sth = $dbh->prepare($q);
|
|
$sth->execute();
|
|
|
|
my @posts;
|
|
while ( my @hash = $sth->fetchrow_array() ) {
|
|
my ( $from, $to, $subg, $time, $echo, $post, $h ) = @hash;
|
|
my $data = {
|
|
from => "$from",
|
|
to => "$to",
|
|
subg => "$subg",
|
|
time => $time,
|
|
echo => "$echo",
|
|
post => "$post",
|
|
hash => $h,
|
|
};
|
|
push( @posts, $data );
|
|
}
|
|
|
|
return @posts;
|
|
}
|
|
|
|
sub from_me {
|
|
my ( $self, $config ) = @_;
|
|
my $dbh = $self->{_dbh};
|
|
my $nick = $config->{nick};
|
|
|
|
# print Dumper($config);
|
|
# print "NICK: $nick\n";
|
|
|
|
my $q
|
|
= "select from_user, to_user, subg, time, echo, post, hash from messages where from_user='$nick'";
|
|
|
|
my $sth = $dbh->prepare($q);
|
|
$sth->execute();
|
|
|
|
my @posts;
|
|
while ( my @hash = $sth->fetchrow_array() ) {
|
|
my ( $from, $to, $subg, $time, $echo, $post, $h ) = @hash;
|
|
my $data = {
|
|
from => "$from",
|
|
to => "$to",
|
|
subg => "$subg",
|
|
time => $time,
|
|
echo => "$echo",
|
|
post => "$post",
|
|
hash => $h,
|
|
};
|
|
push( @posts, $data );
|
|
}
|
|
|
|
return @posts;
|
|
}
|
|
|
|
sub thread {
|
|
my ( $self, $subg, $echo ) = @_;
|
|
my $dbh = $self->{_dbh};
|
|
|
|
my $q
|
|
= "select from_user, to_user, subg, time, echo, post, hash from messages where echo='$echo' and subg like '%$subg%' order by time";
|
|
|
|
my $sth = $dbh->prepare($q);
|
|
$sth->execute();
|
|
|
|
my @posts;
|
|
while ( my @hash = $sth->fetchrow_array() ) {
|
|
my ( $from, $to, $subg, $time, $echo, $post, $h ) = @hash;
|
|
my $data = {
|
|
from => "$from",
|
|
to => "$to",
|
|
subg => "$subg",
|
|
time => $time,
|
|
echo => "$echo",
|
|
post => "$post",
|
|
hash => $h,
|
|
};
|
|
push( @posts, $data );
|
|
}
|
|
|
|
return @posts;
|
|
}
|
|
|
|
sub echoes {
|
|
my ( $self, $echo ) = @_;
|
|
my $dbh = $self->{_dbh};
|
|
|
|
my $q
|
|
= "select from_user, to_user, subg, time, echo, post, hash from messages where echo='$echo' order by time desc";
|
|
|
|
my $sth = $dbh->prepare($q);
|
|
$sth->execute();
|
|
|
|
my @posts;
|
|
while ( my @hash = $sth->fetchrow_array() ) {
|
|
my ( $from, $to, $subg, $time, $echo, $post, $h ) = @hash;
|
|
my $data = {
|
|
from => "$from",
|
|
to => "$to",
|
|
subg => "$subg",
|
|
time => $time,
|
|
echo => "$echo",
|
|
post => "$post",
|
|
hash => $h,
|
|
};
|
|
push( @posts, $data );
|
|
}
|
|
|
|
return @posts;
|
|
}
|
|
|
|
sub to_me {
|
|
my ( $self, $config ) = @_;
|
|
my $dbh = $self->{_dbh};
|
|
my $nick = $config->{nick};
|
|
|
|
# print Dumper($config);
|
|
# print "NICK: $nick\n";
|
|
|
|
my $q
|
|
= "select from_user, to_user, subg, time, echo, post, hash from messages where to_user='$nick' order by time desc";
|
|
|
|
my $sth = $dbh->prepare($q);
|
|
$sth->execute();
|
|
|
|
my @posts;
|
|
while ( my @hash = $sth->fetchrow_array() ) {
|
|
my ( $from, $to, $subg, $time, $echo, $post, $h ) = @hash;
|
|
my $data = {
|
|
from => "$from",
|
|
to => "$to",
|
|
subg => "$subg",
|
|
time => $time,
|
|
echo => "$echo",
|
|
post => "$post",
|
|
hash => "$h",
|
|
};
|
|
push( @posts, $data );
|
|
}
|
|
|
|
return @posts;
|
|
}
|
|
|
|
sub select_new {
|
|
my ( $self, $msg ) = @_;
|
|
my $dbh = $self->{_dbh};
|
|
|
|
my $q
|
|
= "select from_user, to_user, subg, time, echo, post, hash from messages where hash='$msg'";
|
|
|
|
my $sth = $dbh->prepare($q);
|
|
$sth->execute();
|
|
my ( $from, $to, $subg, $time, $echo, $post );
|
|
|
|
while ( my @hash = $sth->fetchrow_array() ) {
|
|
( $from, $to, $subg, $time, $echo, $post, $h ) = @hash;
|
|
}
|
|
|
|
my $data = {
|
|
from => "$from",
|
|
to => "$to",
|
|
subg => "$subg",
|
|
time => $time,
|
|
echo => "$echo",
|
|
post => "$post",
|
|
hash => "$h",
|
|
};
|
|
|
|
return $data;
|
|
}
|
|
|
|
# Search
|
|
sub do_search {
|
|
my ( $self, $query ) = @_;
|
|
my $dbh = $self->{_dbh};
|
|
|
|
my $q = "select from_user, to_user, subg, time, echo, post, hash
|
|
from messages where subg
|
|
like '\%$query\%' COLLATE NOCASE
|
|
order by time";
|
|
|
|
print "SQL: " . $q . "\n";
|
|
my $sth = $dbh->prepare($q);
|
|
$sth->execute();
|
|
|
|
my @posts;
|
|
while ( my @hash = $sth->fetchrow_array() ) {
|
|
my ( $from, $to, $subg, $time, $echo, $post, $h ) = @hash;
|
|
my $data = {
|
|
from => "$from",
|
|
to => "$to",
|
|
subg => "$subg",
|
|
time => $time,
|
|
echo => "$echo",
|
|
post => "$post",
|
|
hash => "$h",
|
|
};
|
|
push( @posts, $data );
|
|
}
|
|
|
|
return @posts;
|
|
}
|
|
|
|
1;
|