From 5ae96931ea9968c48fb2854c30c085cfe5ba7a71 Mon Sep 17 00:00:00 2001 From: Dobrica Pavlinusic Date: Sun, 23 Jan 2005 15:18:03 +0000 Subject: [PATCH] add filtering to index (using parameter filter, for now single) git-svn-id: file:///home/dpavlin/private/svn/webpac/trunk@643 13eb9ef6-21d5-0310-b721-a9d68796d827 --- WebPac.pm | 8 +-- all2xml.conf | 14 +++--- all2xml.pl | 1 + index_DBI_filter.pm | 115 +++++++++++++++++++++++++++++++++----------- 4 files changed, 100 insertions(+), 38 deletions(-) diff --git a/WebPac.pm b/WebPac.pm index 5cc5f89..4afc2c2 100644 --- a/WebPac.pm +++ b/WebPac.pm @@ -438,6 +438,8 @@ sub show_index { my $field = $q->param("f$i"); my $limit = $q->param("v$i"); + my $filter = $q->param("filter"); + my $html; my $index = new index_DBI( @@ -447,9 +449,9 @@ sub show_index { $cfg_global->val('global', 'dbi_passwd') || '' ); - my $total = $index->count($field,$limit); + my $total = $index->count($field,$limit,$filter); - if (! $total) { + if (! defined($total)) { my $tmpl = $self->load_tmpl(url_ex($q,'no_index.html')); $tmpl->param('field',$field); $html = $tmpl->output; @@ -482,7 +484,7 @@ sub show_index { make_pager($q, $tmpl, $pager); make_pager_vars($q, $tmpl, @persist_vars); - my @pager_data_list = $index->fetch($field,$limit, $pager->first - 1, $pager->entries_on_this_page); + my @pager_data_list = $index->fetch($field,$limit, $pager->first - 1, $pager->entries_on_this_page, $filter); $tmpl->param('PAGER_DATA_LIST', \@pager_data_list); return in_template($q,$tmpl->output); diff --git a/all2xml.conf b/all2xml.conf index f47c8b5..f47d69b 100644 --- a/all2xml.conf +++ b/all2xml.conf @@ -246,13 +246,13 @@ library=Psihologija library_url=http://knjiznice.ffzg.hr/info.html#psiho -#[ps-peri] -# isis_db=/backup/isis_backup/sunce2/isisdata/latest/PERI/PERI -# type=isis -# materialtype=Èasopis -# library=Psihologija -# library_url=http://knjiznice.ffzg.hr/info.html#psiho -# +[ps-peri] + isis_db=/data/isis_data/ps/PERI/PERI + type=isis + materialtype=Èasopis + library=Psihologija + library_url=http://knjiznice.ffzg.hr/info.html#psiho + #[pu-libri] # isis_db=/backup/isis_backup/C124-2A/ISISDATA/latest/LIBRI/LIBRI # type=isis diff --git a/all2xml.pl b/all2xml.pl index ee41297..e327b7a 100755 --- a/all2xml.pl +++ b/all2xml.pl @@ -739,6 +739,7 @@ print STDERR "using: $type...\n"; my $db = new Biblio::Isis( isisdb => $isis_db ); my $max_rowid = $db->count || die "can't find maxmfn"; +$max_rowid = 200; # XXX print STDERR "Reading database: $isis_db [$max_rowid rows]\n"; diff --git a/index_DBI_filter.pm b/index_DBI_filter.pm index 5ac3208..fadeea9 100644 --- a/index_DBI_filter.pm +++ b/index_DBI_filter.pm @@ -117,24 +117,52 @@ sub count { my $field = shift; my $where = shift; - my $sql = "select count(*) from index where name = ? and upper(item) like upper(?)||'%'"; + my $filter = shift; + + my $tables_sql = 'index'; + my $where_sql = ''; + my @sql_args = ( $field, $where ); + + if ($filter) { + $tables_sql .= ",filters"; + $where_sql .= " + and index.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) || die $self->{dbh}->errstr(); - $sth->execute($field,$where) || die "sql: $sql; ".$self->{dbh}->errstr(); + 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 = "select count(*) from index wheere name = ?"; - - my $sth = $self->{dbh}->prepare($sql) || die $self->{dbh}->errstr(); - $sth->execute($field) || die "sql: $sql; ".$self->{dbh}->errstr(); + my $sql = qq{ + select count(*) + from $tables_sql + where index.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 || 1; + return $total || '0'; } @@ -146,40 +174,71 @@ sub fetch { my $offset = shift || 0; my $rows = shift || 10; + my $filter = shift; + my $from_ord = 0; - my @sql_args; + my $tables_sql = 'index'; + my $where_sql = ''; - my $sql = qq{ - select item,display,count - from index - where name = ? - and ord > ? - order by ord - limit ? offset ? - }; + my @sql_args = ( $field, $where ); - if ($where) { - my $sql2 = "select ord from index where name = ? and upper(item) like upper(?)||'%'"; - my $sth = $self->{dbh}->prepare($sql2) || die "sql2: $sql2; ".$self->{dbh}->errstr(); + if ($filter) { + $tables_sql .= ",filters"; + $where_sql .= " + and index.ord = filters.ord + and filter = ? + "; + push @sql_args, $filter; + } - $sth->execute($field, $where) || die "sql2: $sql2; ".$self->{dbh}->errstr(); + if ($where) { + my $sql2 = qq{ + select index.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 = "select ord from index where name = ? and upper(item) like '% '||upper(?)||'%'"; - $sth = $self->{dbh}->prepare($sql2) || die "sql2: $sql2; ".$self->{dbh}->errstr(); - $sth->execute($field, $where) || die "sql2: $sql2; ".$self->{dbh}->errstr(); + $sql2 = qq{ + select index.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; } } } - my $sth = $self->{dbh}->prepare($sql) || die "prepare: $sql; ".$self->{dbh}->errstr(); - $sth->execute($field,$from_ord,$rows,$offset) || die "execute: $sql; ".$self->{dbh}->errstr(); + @sql_args = ( $field, $from_ord ); + push @sql_args, $filter if ($filter); + push @sql_args, ( $rows, $offset ); + + my $sql = qq{ + select item,display,index.count as count + from $tables_sql + where name = ? + and index.ord > ? + $where_sql + order by index.ord + limit ? offset ? + }; + + my $sth = $self->{dbh}->prepare($sql) || confess "prepare: $sql; ".$self->{dbh}->errstr(); + $sth->execute(@sql_args) || confess "execute: $sql; ".$self->{dbh}->errstr(); my @arr; while (my $row = $sth->fetchrow_hashref) { $row->{item} = HTML::Entities::encode($row->{item},' <>&"'); @@ -196,7 +255,7 @@ sub close { return if (! $self->{dbh}); - $self->{dbh}->begin_work || die $self->{dbh}->errstr(); + $self->{dbh}->begin_work || confess $self->{dbh}->errstr(); $self->delete_and_create('index', qq{ create table index ( @@ -253,7 +312,7 @@ sub close { } - $self->{dbh}->commit || die $self->{dbh}->errstr(); + $self->{dbh}->commit || confess $self->{dbh}->errstr(); $self->bench("vacuuming"); -- 2.20.1