SQLite fixes: rename index table to data (index is reserved word),

[webpac] / index_DBI_filter.pm

#
# this file implements index functions using DBI
# and huge amounts of memory for cache speedup
#
# this version doesn't support ident (which sould be location in
# library). But, that functionality is not used anyway...
#

package index_DBI;
use strict qw(vars);
use vars qw($Count);
use HTML::Entities;
use URI::Escape;
use locale;
use Carp;

use DBI;

# bench time
my $bench_time = time();

my $debug = 1;

sub bench {
        my $self = shift;
        my $msg = shift;

        print STDERR "last operation took ",time()-$bench_time," seconds...\n";
        $bench_time=time();
        print STDERR "$msg\n";
}

sub new {
        my $class = shift;
        my $self = {};
        bless($self, $class);

        my $dbd = shift || die "need dbi_dbd= in [global] section of configuration file";
        my $dsn = shift || die "need dbi_dsn= in [global] section of configuration file";
        my $user = shift || die "need dbi_user= in [global] section of configuration file";
        my $passwd = shift || die "need dbi_passwd= in [global] section of configuration file";

        $self->{dbd} = $dbd;

        $self->{dbh} = DBI->connect("DBI:$dbd:$dsn",$user,$passwd) || die $DBI::errstr;
        $Count++;

        $self->bench("connected to $dbd as $user");

        # force SQLite to support binary 0 in data (which shouldn't
        # happend, but it did to me)
        eval {
                no warnings 'all';
                $self->{dbh}->{sqlite_handle_binary_nulls} = 1;
        };

        return $self;
}

sub delete_and_create {
        my $self = shift;

        my $table = shift || croak "need table name!";
        my $sql = shift || croak "need sql to create table!";

        print STDERR "## delete_and_create($table)\n" if ($debug);

        my $sql_delete = "delete from $table";
        my $sth = $self->{dbh}->prepare($sql_delete);

        if ($sth && $sth->execute()) {
                print STDERR "## deleted rows from table $table\n" if ($debug);
        } else {
                # can't delete from table, assume it doesn't exists!
                $self->{dbh}->rollback;
                $self->{dbh}->do($sql) || confess "SQL: $sql ".$self->{dbh}->errstr();
                print STDERR "## creating table $table\n" if ($debug);
                $self->{dbh}->begin_work;
        }
}

sub insert {
        my $self = shift;

        my $field = shift;
        my $index_data = shift || print STDERR "\$index->insert($field,NULL,...)";
        my $display = shift || $index_data;
        my $filter = shift;

        if (! $index_data) {
                print STDERR "\$index->insert() -- no value to insert\n";
                return;
        }

        $index_data =~ s#&(\w)(acute|cedil|circ|grave|ring|slash|tilde|uml);#$1#gi;

        # strip spaces
        $index_data =~ s#^\s+##;
        $index_data =~ s#\s+$##;
        $index_data =~ s#\s\s+# #g;

        my $uc = uc($index_data);

        if (! $self->{c}->{$uc}->{$field}) {
#print stderr "in index: $index_data\n";
                $self->{c}->{$uc}->{$field}->{item} = $index_data;
                $self->{c}->{$uc}->{$field}->{display} = $display;
        }

        $self->{c}->{$uc}->{$field}->{count}++;
        $self->{c}->{$uc}->{$field}->{filter}->{$filter}++ if ($filter);
}

sub count {
        my $self = shift;

        my $field = shift;
        my $where = shift;

        my $filter = shift;

        my $tables_sql = 'data';
        my $where_sql = '';
        my @sql_args = ( $field, $where );

        if ($filter) {
                $tables_sql .= ",filters";
                $where_sql .= "
                        and data.ord = filters.ord
                        and filter = ?
                ";
                push @sql_args, $filter;
        }

        my $sql = qq{
                select count(*)
                from $tables_sql
                where name = ? and upper(item) like upper(?)||'%'
                $where_sql
        };

        my $sth = $self->{dbh}->prepare($sql) || confess $self->{dbh}->errstr();
        $sth->execute(@sql_args) || confess "sql: $sql; ".$self->{dbh}->errstr();

        my ($total) = $sth->fetchrow_array();

        # no results, count all
        if (! $total) {
                my $sql = qq{
                        select count(*)
                        from $tables_sql
                        where data.name = ?
                        $where_sql
                };

                @sql_args = ( $field );
                push @sql_args, $filter if ($filter);

                my $sth = $self->{dbh}->prepare($sql) || confess $self->{dbh}->errstr();
                $sth->execute(@sql_args) || confess "sql: $sql; ".$self->{dbh}->errstr();
                $total = $sth->fetchrow_array();

        }

        return $total || '0';
}


sub fetch {
        my $self = shift;

        my $field = shift;
        my $where = shift;

        my $offset = shift || 0;
        my $rows = shift || 10;
        my $filter = shift;

        my $from_ord = 0;

        my $tables_sql = 'data';
        my $where_sql = '';

        my @sql_args = ( $field, $where );

        if ($filter) {
                $tables_sql .= ",filters";
                $where_sql .= "
                        and data.ord = filters.ord
                        and filter = ?
                ";
                push @sql_args, $filter;
        }

        if ($where) {
                my $sql2 = qq{
                        select data.ord as ord
                        from $tables_sql
                        where name = ? and upper(item) like upper(?)||'%'
                        $where_sql
                };
                my $sth = $self->{dbh}->prepare($sql2) || confess "sql2: $sql2; ".$self->{dbh}->errstr();

                $sth->execute(@sql_args) || confess "sql2: $sql2; ".$self->{dbh}->errstr();
                if (my $row = $sth->fetchrow_hashref) {
                        $from_ord += $row->{ord} - 1;
                } else {
                        # if no match is found when searching from beginning
                        # of word in index, try substring match anywhere
                        $sql2 = qq{
                                select data.ord as ord
                                from $tables_sql
                                where name = ? and upper(item) like '% '||upper(?)||'%'
                                $where_sql
                        };
        
                        $sth = $self->{dbh}->prepare($sql2) || confess "sql2: $sql2; ".$self->{dbh}->errstr();
                        $sth->execute(@sql_args) || confess "sql2: $sql2; ".$self->{dbh}->errstr();

                        if (my $row = $sth->fetchrow_hashref) {
                                $from_ord += $row->{ord} - 1;
                        }
                }
        }

        @sql_args = ( $field, $from_ord );
        push @sql_args, $filter if ($filter);

        my $sql = qq{
                select item,display,data.count as count
                from $tables_sql
                where name = ?
                        and data.ord > ?
                $where_sql
                        order by data.ord
        };

        # fix SQLite problem which doesn't allow placeholders in limit and offset
        # http://thread.gmane.org/gmane.comp.db.sqlite.general/9707
        $sql .= "limit $rows offset $offset";

        my $sth = $self->{dbh}->prepare($sql) || confess "prepare: $sql; ".$self->{dbh}->errstr();
        $sth->execute(@sql_args) || confess "execute: $sql; ".join("|",@sql_args)." ".$self->{dbh}->errstr();
        my @arr;
        while (my $row = $sth->fetchrow_hashref) {
                $row->{item} = HTML::Entities::encode($row->{item},' <>&"');
                $row->{display} = HTML::Entities::encode($row->{display},'<>&"');
                $row->{item} =~ s#&amp;(\w)(acute|cedil|circ|grave|ring|slash|tilde|uml);#$1#gi;
                $row->{display} =~ s#&amp;(\w)(acute|cedil|circ|grave|ring|slash|tilde|uml);#&$1$2;#gi;
                push @arr,$row;
        }
        return @arr;
}

sub close {
        my $self = shift;

        return if (! $self->{dbh});

        $self->{dbh}->begin_work || confess $self->{dbh}->errstr();

        $self->delete_and_create('data', qq{
                create table data (
                        name varchar(255),
                        ord int,
                        item text,
                        display text,
                        count int,
                        primary key (name,ord)
                );
        });

        $self->delete_and_create('filters', qq{
                create table filters (
                        filter varchar(255),
                        ord int,
                        count int,
                        primary key (filter,ord)
                );
        });

        $self->bench("getting all entries");
        my @items = keys %{$self->{c}};
        $self->bench("got ".($#items+1)." items, now sorting");
        @items = sort @items;

        my $sql = "insert into data (name,ord,item,display,count) values (?,?,?,?,?)";
        my $sth_index = $self->{dbh}->prepare($sql) || confess "$sql: ".$self->{dbh}->errstr();

        $sql = "insert into filters (filter, ord, count) values (?,?,?)";
        my $sth_filter = $self->{dbh}->prepare($sql) || confess "$sql: ".$self->{dbh}->errstr();

        my $ord = 0;
        foreach my $key (@items) {

                foreach my $field (keys %{$self->{c}->{$key}}) {
                        # store items
                        $sth_index->execute(
                                $field,
                                ++$ord,
                                $self->{c}->{$key}->{$field}->{item},
                                $self->{c}->{$key}->{$field}->{display},
                                $self->{c}->{$key}->{$field}->{count},
                        );

                        # store filters
                        next unless ($self->{c}->{$key}->{$field}->{filter});

                        foreach my $filter (keys %{$self->{c}->{$key}->{$field}->{filter}}) {
                                $sth_filter->execute( $filter, $ord, $self->{c}->{$key}->{$field}->{filter}->{$filter} );
                        }
                }


        }

        $self->{dbh}->commit || confess $self->{dbh}->errstr();

        $self->bench("vacuuming");

        if ($self->{dbd} =~ m/(Pg|SQLite)/) {
                $self->{dbh}->do(qq{vacuum}) || carp "vacumming failed. It shouldn't if you are using PostgreSQL or SQLite: ".$self->{dbh}->errstr();
        }

        $self->bench("disconnecting from database");

        $self->{dbh}->disconnect;
        undef $self->{dbh};
}

END {
        $Count--;
        print STDERR "index_DBI fatal error: \$index->close() not called... $Count references left!\n" if ($Count > 0);
        # FIX: debug output
#       print STDERR "usage\ttable\n";
#       foreach (keys %Table) {
#               print STDERR $Table{$_},"\t$_\n";
#       }
}

1;