projects
/
webpac
/ blobdiff
commit
grep
author
committer
pickaxe
?
search:
re
summary
|
shortlog
|
log
|
commit
|
commitdiff
|
tree
raw
|
inline
| side by side
bugfix: check Isis database error in correct place
[webpac]
/
index_DBI_filter.pm
diff --git
a/index_DBI_filter.pm
b/index_DBI_filter.pm
index
fadeea9
..
566ec76
100644
(file)
--- a/
index_DBI_filter.pm
+++ b/
index_DBI_filter.pm
@@
-60,21
+60,21
@@
sub new {
sub delete_and_create {
my $self = shift;
sub delete_and_create {
my $self = shift;
- my $
index = shift || croak "need index
name!";
+ my $
table = shift || croak "need table
name!";
my $sql = shift || croak "need sql to create table!";
my $sql = shift || croak "need sql to create table!";
- print STDERR "## delete_and_create($
index
)\n" if ($debug);
+ print STDERR "## delete_and_create($
table
)\n" if ($debug);
- my $sql_delete = "delete from $
index
";
- my $sth = $self->{dbh}->prepare($sql_delete)
|| confess "can't prepare: $sql_delete"
;
+ my $sql_delete = "delete from $
table
";
+ my $sth = $self->{dbh}->prepare($sql_delete);
- if ($sth->execute()) {
- print STDERR "## deleted rows from table $
index
\n" if ($debug);
+ 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();
} 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 $
index
\n" if ($debug);
+ print STDERR "## creating table $
table
\n" if ($debug);
$self->{dbh}->begin_work;
}
}
$self->{dbh}->begin_work;
}
}
@@
-119,14
+119,14
@@
sub count {
my $filter = shift;
my $filter = shift;
- my $tables_sql = '
index
';
+ my $tables_sql = '
data
';
my $where_sql = '';
my @sql_args = ( $field, $where );
if ($filter) {
$tables_sql .= ",filters";
$where_sql .= "
my $where_sql = '';
my @sql_args = ( $field, $where );
if ($filter) {
$tables_sql .= ",filters";
$where_sql .= "
- and
index
.ord = filters.ord
+ and
data
.ord = filters.ord
and filter = ?
";
push @sql_args, $filter;
and filter = ?
";
push @sql_args, $filter;
@@
-149,7
+149,7
@@
sub count {
my $sql = qq{
select count(*)
from $tables_sql
my $sql = qq{
select count(*)
from $tables_sql
- where
index
.name = ?
+ where
data
.name = ?
$where_sql
};
$where_sql
};
@@
-178,7
+178,7
@@
sub fetch {
my $from_ord = 0;
my $from_ord = 0;
- my $tables_sql = '
index
';
+ my $tables_sql = '
data
';
my $where_sql = '';
my @sql_args = ( $field, $where );
my $where_sql = '';
my @sql_args = ( $field, $where );
@@
-186,7
+186,7
@@
sub fetch {
if ($filter) {
$tables_sql .= ",filters";
$where_sql .= "
if ($filter) {
$tables_sql .= ",filters";
$where_sql .= "
- and
index
.ord = filters.ord
+ and
data
.ord = filters.ord
and filter = ?
";
push @sql_args, $filter;
and filter = ?
";
push @sql_args, $filter;
@@
-194,7
+194,7
@@
sub fetch {
if ($where) {
my $sql2 = qq{
if ($where) {
my $sql2 = qq{
- select
index
.ord as ord
+ select
data
.ord as ord
from $tables_sql
where name = ? and upper(item) like upper(?)||'%'
$where_sql
from $tables_sql
where name = ? and upper(item) like upper(?)||'%'
$where_sql
@@
-208,7
+208,7
@@
sub fetch {
# if no match is found when searching from beginning
# of word in index, try substring match anywhere
$sql2 = qq{
# if no match is found when searching from beginning
# of word in index, try substring match anywhere
$sql2 = qq{
- select
index
.ord as ord
+ select
data
.ord as ord
from $tables_sql
where name = ? and upper(item) like '% '||upper(?)||'%'
$where_sql
from $tables_sql
where name = ? and upper(item) like '% '||upper(?)||'%'
$where_sql
@@
-225,20
+225,22
@@
sub fetch {
@sql_args = ( $field, $from_ord );
push @sql_args, $filter if ($filter);
@sql_args = ( $field, $from_ord );
push @sql_args, $filter if ($filter);
- push @sql_args, ( $rows, $offset );
my $sql = qq{
my $sql = qq{
- select item,display,
index
.count as count
+ select item,display,
data
.count as count
from $tables_sql
where name = ?
from $tables_sql
where name = ?
- and
index
.ord > ?
+ and
data
.ord > ?
$where_sql
$where_sql
- order by index.ord
- limit ? offset ?
+ 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();
my $sth = $self->{dbh}->prepare($sql) || confess "prepare: $sql; ".$self->{dbh}->errstr();
- $sth->execute(@sql_args) || confess "execute: $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},' <>&"');
my @arr;
while (my $row = $sth->fetchrow_hashref) {
$row->{item} = HTML::Entities::encode($row->{item},' <>&"');
@@
-257,8
+259,8
@@
sub close {
$self->{dbh}->begin_work || confess $self->{dbh}->errstr();
$self->{dbh}->begin_work || confess $self->{dbh}->errstr();
- $self->delete_and_create('
index
', qq{
- create table
index
(
+ $self->delete_and_create('
data
', qq{
+ create table
data
(
name varchar(255),
ord int,
item text,
name varchar(255),
ord int,
item text,
@@
-282,7
+284,7
@@
sub close {
$self->bench("got ".($#items+1)." items, now sorting");
@items = sort @items;
$self->bench("got ".($#items+1)." items, now sorting");
@items = sort @items;
- my $sql = "insert into
index
(name,ord,item,display,count) values (?,?,?,?,?)";
+ 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_index = $self->{dbh}->prepare($sql) || confess "$sql: ".$self->{dbh}->errstr();
$sql = "insert into filters (filter, ord, count) values (?,?,?)";