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!";
- 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();
- print STDERR "## creating table $index\n" if ($debug);
+ print STDERR "## creating table $table\n" if ($debug);
$self->{dbh}->begin_work;
}
}
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 = '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) || 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 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 || 1;
+ return $total || '0';
}
my $offset = shift || 0;
my $rows = shift || 10;
+ my $filter = shift;
+
my $from_ord = 0;
- my @sql_args;
+ my $tables_sql = 'data';
+ 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 data.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 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 = "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 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;
}
}
}
- 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);
+
+ 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},' <>&"');
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 (
+ $self->delete_and_create('data', qq{
+ create table data (
name varchar(255),
ord int,
item text,
$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 (?,?,?)";
}
- $self->{dbh}->commit || die $self->{dbh}->errstr();
+ $self->{dbh}->commit || confess $self->{dbh}->errstr();
$self->bench("vacuuming");
projects / webpac / blobdiff