/[pgestraier]/trunk/README.pod
This is repository of my old source code which isn't updated any more. Go to git.rot13.org for current projects!
ViewVC logotype

Diff of /trunk/README.pod

Parent Directory Parent Directory | Revision Log Revision Log | View Patch Patch

revision 49 by dpavlin, Sat Oct 29 18:54:40 2005 UTC revision 64 by dpavlin, Mon Aug 7 14:32:21 2006 UTC
# Line 1  Line 1 
1  =head1 pgestraier - search Hyper Estraier indexes from PostgreSQL  =head1 pgestraier - PostgreSQL full-text search using Hyper Estraier
2    
3  This package is essentially PostgreSQL C function which calls Hyper Estraier  This package is essentially composed of two different parts:
4  API and returns results in user defined format.  
5    =over 4
6    
7    =item search function
8    
9    PostgreSQL function to search Hyper Estraier full-text index, using
10    full-text queries and attribute filtering to return user-specified
11    table of results.
12    
13    This function can mimic SQL C<LIMIT>, C<OFFSET> and C<ORDER BY>
14    functionality much faster than using those SQL constructs on search
15    results.
16    
17    =item trigger function
18    
19    PostgreSQL trigger function to keep Hyper Estraier in sync with PostgreSQL.
20    It triggers after insert, update or delete and update full-text index
21    accordingly.
22    
23    =back
24    
25    Both functions are written in C, while test framework and supporting
26    utilities are written in perl.
27    
28    You can use just one of those functions. If you want just to search existing
29    Hyper Estraier index or generate it off-line (after nightly batch jobs, for
30    example), just use search function.
31    
32    On the other hand, if you want just to keep your Hyper Estraier index in
33    sync with PostgreSQL data, you can use just trigger function to achieve that.
34    
35  =head1 Why is it written?  =head1 Why is it written?
36    
37  Aside from providing single API to query your RDBMS and full text index  Aside from providing single query language (SQL) to RDBMS and full text index
38  (using any language that has PostgreSQL client libraries), real power is  (using any language that has PostgreSQL client libraries), real power is
39  hidden in ability to join results from full text index and structured data  hidden in ability to join results from full text index and structured data
40  in RDBMS.  in RDBMS.
41    
42    For simple real-life example which address problem
43    C<< WHERE name LIKE '%foo%' OR surname LIKE '%foo%' >>
44    is slow see L<Tutorial>.
45    
46  =head1 How to install  =head1 How to install
47    
48  Installation should be simple. However, you will have to have following  Installation should be simple. However, you will have to have following
49  software already installed before you try this function:  software already installed before you try this functions:
50    
51  =over  =over
52    
53  =item *  =item *
54    
55  PostgreSQL (tested with versions 7.4 and 8.0) with development libraries  PostgreSQL (tested with versions 7.4, 8.0 and 8.1) with development libraries
56    
57    =item *
58    
59    Hyper Estraier (tested with various versions, recommended 1.2.4 or newer)
60    with development headers
61    
62  =item *  =item *
63    
64  Hyper Estraier (tested with 0.5.0-1.0.0+, version newer than 0.9.6 are  working C compiler (tested with gcc)
 recommended)  
65    
66  =back  =back
67    
68  To run tests you will also need:  If you want to use helper script to create consistency triggers to keep
69    Hyper Estraier in sync with PostgreSQL database, you will also need:
70    
71  =over  =over
72    
# Line 38  working perl installation Line 76  working perl installation
76    
77  =item *  =item *
78    
79  perl modules C<DBI>, C<DBD::Pg>, C<Test::More> and optionally C<HyperEstraier>  perl modules C<Search::Estraier>, C<DBI> and C<DBD::Pg>
80    
81    =back
82    
83    To run tests you will also need:
84    
85    =over
86    
87    =item *
88    
89    perl module C<Test::More>
90    
91  =item *  =item *
92    
# Line 50  PostgreSQL database C<test> with permiss Line 98  PostgreSQL database C<test> with permiss
98    
99  =item *  =item *
100    
101  Hyper Estraier node C<trivia> with permissions for C<admin> user.  Hyper Estraier C<estmaster> running with permissions for C<admin> user
102    to create C<trivia> node.
103    
104  =back  =back
105    
# Line 62  and see sample results. You will be aske Line 111  and see sample results. You will be aske
111  install C<pgest.so> shared library in system-wide location so that PostgreSQL  install C<pgest.so> shared library in system-wide location so that PostgreSQL
112  could access it.  could access it.
113    
114  Next, you will have to create test index. You have two options:  =head2 Create sample index using Hyper Estraier perl bindings
115    
116  =head2 Create index using estcmd  Perl bindings for Hyper Estraier are available at CPAN:
117    
118  This will create temporary files on disk and index them using estcmd gather  L<http://search.cpan.org/~dpavlin/Search-Estraier/>
119    
   cd data  
   make index  
   cd ..  
120    
121  B<Warning:> this method is incomplete and won't create node index needed  After installing C<Search::Estraier> you can create index using following commands:
 to run last examples in C<test.sql> correctly. Solution is simple: either  
 symlink your newly created index to Hyper Estraier C<_node> directory or  
 create node and fill re-create index using C<estcall>.  
   
 =head2 Create index using Hyper Estraier perl bindings  
   
 Perl bindings for Hyper Estraier are available at  
   
 L<http://hyperestraier.sourceforge.net/binding/>  
   
 However, they don't support node API (yet), so you will have to use  
 my modified version which is available at  
 L<http://svn.rot13.org/> in C<hyperestraier_wrappers> repository.  
   
 If you installed bindings as documented in README file, you can use  
 perl binding to create index about three times faster than using C<estcmd>  
 (to be fair, I must say that creation of intermediate files take most time,  
 not indexing).  
   
 However, you will first need to create node I<trivia> using Hyper Estraier's  
 administration interface at L<http://localhost:1978/masterui>. You will also  
 need user C<admin> with password C<admin> because those values are  
 hard-coded in C<indexer.pl>. If you want to use different user on index  
 name, feel free to change script.  
122    
123    cd data    cd data
124    make perl    make index
125    cd ..    cd ..
126    
127  To run tests (which require that you have estcmd in your $PATH) issue  To run tests (which require that you have estcmd in your $PATH) issue
128    
129    make test    make test
130    
131  See also included file test.sql for more examples of usage.  See also included file C<test.sql> for more examples of usage.
132    
133  =head1 Usage of pgest from SQL  =head1 Usage of search function pgest from SQL
134    
135  C<pgest> PostgreSQL function has two different prototypes (number of arguments) depending on usage.  C<pgest> PostgreSQL function tries to mimic usage of normal database tables (with support for attribute filtering, limit and offset) in following way:
136    
137          SELECT          SELECT
138                  -- columns to return (defined later)                  -- columns to return (defined later)
139                  id,title,size                  id,title,size
140          FROM pgest(          FROM pgest(
141                  -- path to index OR URL to node, user-name and password                  -- node URI, login, password and depth of search
                 -- you will need JUST ONE of following two lines, depending  
                 -- on your usage described below, for direct access  
                 '/full/path/to/casket',  
                 -- or for node API specify node URI, login, password  
                 -- and depth of search  
142                  'http://localhost:1978/node/trivia', 'admin', 'admin', 42,                  'http://localhost:1978/node/trivia', 'admin', 'admin', 42,
143                  -- query                  -- query
144                  'blade runner',                  'blade runner',
# Line 141  C<pgest> PostgreSQL function has two dif Line 158  C<pgest> PostgreSQL function has two dif
158                  id text, title text, size text                  id text, title text, size text
159          );          );
160    
161    You should note that Hyper Estraier uses UTF-8 encoding, while your
162    PostgreSQL installation might use different encoding. To fix that, use
163    C<convert> function in PostgreSQL to convert encodings.
164    
165    =head2 Using index via C<estmaster> server process
166    
167    This is default and recommended way to use C<pgest> functionality. In this
168    case, C<pgest> will use node API and access index through C<estmaster>
169    process which should be running on (local or remote) machine.
170    
171    This will remove database opening overhead, at a cost of (small) additional network
172    traffic. However, you can have Hyper Estraier C<estmaster> process running on
173    different machine or update index while doing searches, so benefits of this
174    approach are obvious.
175    
176  =head2 Accessing database directly  =head2 Accessing database directly
177    
178  If you want to access database directly (without running C<estmaster> process), first argument is full path to database file.  B<Please note that direct access to database is depreciated.> As such, it's
179    not stated in example, and it's kept just for backward compatibility, but it
180    will probably be removed in future versions of C<pgest>.
181    
182    If you want to access database directly (without running C<estmaster> process), you
183    have to replace node URI, login, password and depth with full path to database file.
184    
185  Have in mind that C<postgres> user under which PostgreSQL is running must  Have in mind that C<postgres> user under which PostgreSQL is running must
186  have read permission on Hyper Estraier database files.  have read permission on Hyper Estraier database files.
# Line 152  This will work a bit faster on really sm Line 189  This will work a bit faster on really sm
189  index grows bigger, you might consider using node API to remove overhead of  index grows bigger, you might consider using node API to remove overhead of
190  database opening on each query.  database opening on each query.
191    
192  =head2 Using index via C<estmaster> server process  =head1 Usage of trigger function pgest_trigger from SQL
193    
194  If first argument is URL to node (like C<http://localhost:1978/node/trivia>)  Let's first say that I really suggest that you use C<dbi-index.pl> helper script to
195  and there are two additional parameters (user-name and password) after it,  create triggers because it already supports following steps automatically:
 C<pgest> will use node API and access index through C<estmaster> process which should be running on (local or remote) machine.  
196    
197  This will remove database opening overhead, at a cost of additional network  =over
198  traffic. However, you can have Hyper Estraier C<estmaster> process running on  
199  different machine or update index while doing searches, so benefits of this  =item begin transaction
200  approach are obvious.  
201    Transaction is needed to catch updates which might happen while creation
202    of full-text index is in progress (and on huge collections this can take a while,
203    just like normal index creation in PostgreSQL).
204    
205    =item insert all existing data in full-text index
206    
207    This will be done directly from PostgreSQL database to Hyper Estraier index.
208    This is somewhat faster than waiting for trigger to fire for each existing
209    row.
210    
211    =item create insert, update and delete triggers
212    
213    Which will keep data in sync later
214    
215    =item commit transaction
216    
217    =back
218    
219    If you still want to do that manually, you will need to know format of
220    C<pgest_trigger> function:
221    
222            CREATE TRIGGER pgest_trigger_insert AFTER INSERT
223                    ON table FOR EACH ROW
224                    EXECUTE PROCEDURE pgest_trigger(
225                            -- node URI, login and password
226                            'http://localhost:1978/node/trivia', 'admin', 'admin',
227                            -- name of primary key column
228                            'id',
229                            -- names of all other columns to index (one or more)
230                            'column', 'another_one', 'and_another'
231                    )
232    
233    You have to create triggers for C<UPDATE> and C<DELETE> in similar way.
234    
235  =head1 Who wrote this?  =head1 Who wrote this?
236    
237  Hyper Estraier is written by Mikio Hirabayashi.  Hyper Estraier is written by Mikio Hirabayashi.
238    
 Perl bindings for Hyper Estraier are written by MATSUNO Tokuhiro.  
   
239  PostgreSQL is written by hackers calling themselves PostgreSQL Global  PostgreSQL is written by hackers calling themselves PostgreSQL Global
240  Development Group.  Development Group.
241    
242  This small C function is written by Dobrica Pavlinusic, dpavlin@rot13.org.  This small C function is written by L<Dobrica Pavlinusic|http://www.rot13.org/~dpavlin/>, dpavlin@rot13.org.
Legend:
Removed from v.49  
changed lines
  Added in v.64
  ViewVC Help
Powered by ViewVC 1.1.26