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.

For all available options see L<pgest-index> documentation.
1	=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	L<Hyper Estraier\|http://hyperestraier.sourceforge.net/>.
7
8	In less then 10 minutes from installation to fill-text search-able table
9	(speed may vary somewhat, but it's really fast).
10
11	=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
54	For all available options see L<pgest-index> documentation.