--- trunk/bin/pgest-index.pl	2006/08/07 17:05:07	68
+++ trunk/bin/pgest-index.pl	2006/11/26 21:21:46	82
@@ -15,7 +15,7 @@
 =cut
 
 my $c = {
-	debug => 1,
+	debug => 0,
 };
 
 =head1 SYNOPSIS
@@ -47,9 +47,23 @@
 index. Have in mind that you can't use aliases (as I<something>) in this SQL
 query (or triggers will be created with wrong fields).
 
+If SQL query isn't specified, default one C<< select * from movies >> will
+be created. That will be B<serious performance hit> if all columns are
+not needed for search.
+
 =item --pk id
 
-Specify name of primary key column in SQL query. If not specified, C<id> will be used.
+Specify name of primary key column in SQL query. If you allready have primary
+key on table or unique index and it consists of simgle column
+(compaund keys are not supported) it will be picked up automatically.
+
+If you specify value which is not unique, you will get just last occurence
+of that item in index (which might be what you want). That's because specified
+C<pk> column will be used for C<@uri> in Hyper Estraier.
+
+If name of primary key begins with C<_> it will not be added into text
+indexing (so you won't be able to find prmary key value, but it will still
+be available as attribute value).
 
 =item --user login
 
@@ -60,7 +74,8 @@
 
 =item --debug
 
-Dump debugging output. It may be specified multiple times for more verbose debugging.
+Dump debugging output. It may be specified multiple times for more verbose
+debugging.
 
 =back
 
@@ -100,12 +115,24 @@
 sub drop_triggers {
 	my $table = shift || die "no table?";
 
-	warn "removing triggers from $table\n";
-
-	foreach my $t (qw/UPDATE INSERT DELETE/) {
-		my $lc_t = lc($t);
-		$dbh->do(qq{ DROP TRIGGER pgest_trigger_${lc_t} ON ${table} });
+	my $sth = $dbh->prepare(qq{
+		SELECT relname,tgname
+		FROM pg_trigger JOIN pg_class ON relfilenode = tgrelid
+		WHERE tgname LIKE 'pgest_trigger_%' AND relname = ?
+	}) || $dbh->errstr;
+
+	$sth->execute( $table ) || $sth->errstr();
+
+	warn "there are ", $sth->rows, " triggers instead of just 3, dropping all\n" if ($sth->rows != 3);
+
+	while (my $row = $sth->fetchrow_hashref) {
+		my $sql = sprintf(qq{ DROP TRIGGER %s ON %s }, $row->{tgname}, $row->{relname} );
+		#warn "# $sql\n";
+		$dbh->do( $sql ) || $dbh->errstr;
 	}
+
+	warn "removed ", $sth->rows, " triggers from $table\n" if ($sth->rows);
+
 }
 
 if ($c->{drop}) {
@@ -145,32 +172,36 @@
 
 	warn "# finding primary key for $table\n" if ($c->{debug});
 
-	$c->{pk} = $dbh->selectrow_array(qq{
+	my $index_fmt = qq{
 		SELECT
 			a.attname, t.typname
 		FROM pg_type t, pg_attribute a
 		WHERE t.oid = a.atttypid AND attrelid = (
 			SELECT indexrelid
 			FROM pg_class c, pg_index i
-			WHERE c.relname = '$table'
+			WHERE c.relname = '%s'
 				AND c.oid = i.indrelid
-				AND indisprimary
+				AND %s
 				AND indnatts = 1
 		)
-	});
+	};
+
+	$c->{pk} = $dbh->selectrow_array( sprintf($index_fmt, $table, 'indisprimary') );
+	
+	$c->{pk} ||= $dbh->selectrow_array( sprintf($index_fmt, $table, 'indisunique') );
 
 }
 
 die "$0: can't find single column primary key for table ${table}. Please specify column with --pk\n" unless ($c->{pk});
 
-warn "using primary key $c->{pk}\n";
+warn "using primary key $c->{pk}", $c->{pk} =~ m/^_/ ? " (not indexed)" : "", "\n";
 
 $dbh->begin_work;
 
 $c->{sql} ||= "select * from $table";
 
 my $sth = $dbh->prepare($c->{sql}) || die $dbh->errstr();
-$sth->execute() || die $sth->errstr();
+$sth->execute() || die $sth->errstr;
 
 my @cols = @{ $sth->{NAME} };
 
@@ -184,6 +215,8 @@
 my $t = time();
 my $pk = $c->{pk} || 'id';
 
+warn "indexing existing ",$sth->rows," rows\n";
+
 while (my $row = $sth->fetchrow_hashref() ) {
 
 	warn "# row: ",Dumper($row) if ($c->{debug} >= 3);
@@ -197,7 +230,7 @@
 		die "can't find pk column '$pk' in results\n";
 	}
 
-	printf "%4d ",$i;
+	my $log = sprintf "%4d ",$i;
 
 	while (my ($col,$val) = each %{$row}) {
 
@@ -206,11 +239,11 @@
 			$doc->add_attr($col, $val);
 
 			# add body text to document (make it searchable using full-text index)
-			$doc->add_text($val);
+			$doc->add_text($val) unless ($col =~ m/^_/);
 
-			print "R";
+			$log .= "R";
 		} else {
-			print ".";
+			$log .= ".";
 		}
 
 	}
@@ -219,7 +252,9 @@
 
 	die "error: ", $node->status,"\n" unless (eval { $node->put_doc($doc) });
 
-	printf (" %d%% %.1f/s\n", int(( $i++ / $total) * 100), ( $i / (time() - $t) ) );
+	$log .= sprintf(" %d%% %.1f/s\r", int(( $i++ / $total) * 100), ( $i / (time() - $t) ) );
+
+	print STDERR $log;
 
 }
 
@@ -241,17 +276,27 @@
 
 	#warn "$sql\n";
 
-	$dbh->do( $sql ) || die $dbh->errstr();
+	$dbh->do( $sql ) || die $dbh->errstr;
 
 }
 
+warn "created consistency triggers\n";
+
 $dbh->commit;
 
+=head1 SEARCHING
+
+At end of each run, this script will output example search SQL query on STDOUT.
+
+You can use it to quickly construct queries for your application.
+
+=cut
+
 my $col_names = join(', ', @cols);
 my $col_def = join(', ', map { "$_ text" } @cols);
 
 print "
-## example SQL search query:
+-- example SQL search query:
 
 SELECT $col_names
 FROM pgest(
@@ -266,3 +311,18 @@
 ) as ($col_def);
 
 ";
+
+__END__
+
+=head1 AUTHOR
+
+Dobrica Pavlinusic <dpavlin@rot13.org>
+
+L<http://www.rot13.org/~dpavlin/>
+
+=head1 LICENSE
+
+This product is licensed under GNU Public License (GPL) v2 or later.
+
+=cut
+