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