--- trunk/bin/pgest-index.pl	2006/08/07 16:38:24	67
+++ trunk/bin/pgest-index.pl	2006/08/07 21:08:41	70
@@ -15,12 +15,6 @@
 =cut
 
 my $c = {
-	name => 'imenik',
-	dbi => 'Pg:dbname=vip',
-	sql => qq{
-		select ime,tel from imenik
-	},
-	pk => 'tel',
 	debug => 1,
 };
 
@@ -53,9 +47,19 @@
 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, 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.
 
 =item --user login
 
@@ -66,17 +70,24 @@
 
 =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
 
 =cut
 
+my $usage = "$0 database_name (--create|--drop) name [--sql='select id,foo,bar from table'] [--pk=id]\n";
+
 GetOptions($c, qw/create=s drop=s node_url=s sql=s pk=s user=s passwd=s debug+/);
 
+my $dbname = shift @ARGV || die $usage;
+
+$c->{dbi} = 'Pg:dbname=' . $dbname;
+
 warn "# c: ", Dumper($c) if ($c->{debug});
 
-my $table = $c->{create} || $c->{drop} || die "$0 (--create|--drop) name [--sql='select id,foo,bar from table']\n";
+my $table = $c->{create} || $c->{drop} || die $usage;
 
 $c->{node_url} = 'http://localhost:1978/node/' . $table;
 
@@ -141,13 +152,41 @@
 
 drop_triggers( $table );
 
+if (! $c->{pk}) {
+
+	warn "# finding primary key for $table\n" if ($c->{debug});
+
+	$c->{pk} = $dbh->selectrow_array(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'
+				AND c.oid = i.indrelid
+				AND indisprimary
+				AND indnatts = 1
+		)
+	});
+
+}
+
+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";
+
 $dbh->begin_work;
 
+$c->{sql} ||= "select * from $table";
+
 my $sth = $dbh->prepare($c->{sql}) || die $dbh->errstr();
 $sth->execute() || die $sth->errstr();
 
 my @cols = @{ $sth->{NAME} };
 
+die "SQL '$c->{sql}' didn't include primary key $c->{pk}\n" unless grep(/^\Q$c->{pk}\E$/, @cols);
+
 warn "# columns: ",join(",", @cols),"\n" if ($c->{debug});
 
 my $total = $sth->rows;
@@ -156,6 +195,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);
@@ -169,7 +210,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}) {
 
@@ -180,9 +221,9 @@
 			# add body text to document (make it searchable using full-text index)
 			$doc->add_text($val);
 
-			print "R";
+			$log .= "R";
 		} else {
-			print ".";
+			$log .= ".";
 		}
 
 	}
@@ -191,7 +232,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;
 
 }
 
@@ -219,11 +262,19 @@
 
 $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(
@@ -238,3 +289,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
+