pgestraier/trunk/Tutorial.pod

=head1 pgestraier Tutorial

This tutorial will try to show how easy it is to replace SQL
constructs that use C<< where column like '%something%' >>
with external full-text index using
L<Hyper Estraier|http://hyperestraier.sourceforge.net/>.

In less then 10 minutes from installation to fill-text search-able table
(speed may vary somewhat, but it's really fast).

=head2 Installation

  $ sudo apt-get install postgresql-8.1 postgresql-server-dev-8.1 \
        libdbd-pg-perl
  $ sudo cpan Search::Estraier

=head2 Compilation

  $ cd pgestraier
  $ sudo make install

This will just install library at right place without running tests or examples.

=head2 Creating full-text index

Let's assume you have database called C<content> in which you have table
C<new> which has fields title, lead, content, keywords and source which
should be search-able.

You will have to do:

  $ ./bin/pgest-index.pl content --create news > search.sql

That's it. Really. You will even get example SQL query to shoehorn into
your application.

You can now connect to database using psql, edit search string in
C<search.sql> and try your new full-text index.

However, you will notice that we indexed B<every> field in table news,
which is wasteful (because other fields are ids of various things,
booleans and fields which aren't interesting for full-text search).

So, we can do better:

  $ ./bin/pgest-index.pl content --create news \
        --sql="select id,title,lead,content,keywords from news"

We added custom SQL query which will be used to produce full-text index and
triggers so that we touch only columns in which we are really interested.

This time around that it! Enjoy.

1	dpavlin	59	=head1 pgestraier Tutorial
2
3			This tutorial will try to show how easy it is to replace SQL
4			constructs that use C<< where column like '%something%' >>
5			with external full-text index using
6	dpavlin	71	L<Hyper Estraier\|http://hyperestraier.sourceforge.net/>.
7	dpavlin	59
8	dpavlin	71	In less then 10 minutes from installation to fill-text search-able table
9			(speed may vary somewhat, but it's really fast).
10	dpavlin	59
11	dpavlin	71	=head2 Installation
12
13			$ sudo apt-get install postgresql-8.1 postgresql-server-dev-8.1 \
14			libdbd-pg-perl
15			$ sudo cpan Search::Estraier
16
17			=head2 Compilation
18
19			$ cd pgestraier
20			$ sudo make install
21
22			This will just install library at right place without running tests or examples.
23
24			=head2 Creating full-text index
25
26			Let's assume you have database called C<content> in which you have table
27			C<new> which has fields title, lead, content, keywords and source which
28			should be search-able.
29
30			You will have to do:
31
32			$ ./bin/pgest-index.pl content --create news > search.sql
33
34			That's it. Really. You will even get example SQL query to shoehorn into
35			your application.
36
37			You can now connect to database using psql, edit search string in
38			C<search.sql> and try your new full-text index.
39
40			However, you will notice that we indexed B<every> field in table news,
41			which is wasteful (because other fields are ids of various things,
42			booleans and fields which aren't interesting for full-text search).
43
44			So, we can do better:
45
46			$ ./bin/pgest-index.pl content --create news \
47			--sql="select id,title,lead,content,keywords from news"
48
49			We added custom SQL query which will be used to produce full-text index and
50			triggers so that we touch only columns in which we are really interested.
51
52			This time around that it! Enjoy.
53