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 $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 .= "
- and index.ord = filters.ord
+ and data.ord = filters.ord
and filter = ?
";
push @sql_args, $filter;
my $sql = qq{
select count(*)
from $tables_sql
- where index.name = ?
+ where data.name = ?
$where_sql
};
my $from_ord = 0;
- my $tables_sql = 'index';
+ my $tables_sql = 'data';
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;
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
# 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
@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
+ select item,display,data.count as count
from $tables_sql
where name = ?
- and index.ord > ?
+ and data.ord > ?
$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();
- $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},' <>&"');
$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 (?,?,?)";