SearchPage/Tutorial.pm

filter: Statementstmt 100% 8 / 8 Branchbran n/a Conditioncond n/a MC/DCmcdc n/a Subroutinesub 100% 2 / 2 Podpod n/a totaltotal 100% 10 / 10 CC 1 SCAR 0.0
CC
1
Cov
100%
BEGIN
1.0
BEGIN
1.0
CRAP 1.0
« SearchPage/Files.pm ↑ Summary
1 package HTML::SearchPage::Tutorial;
2  
3 1 our $VERSION = '0.05';
4  
5 # $Id: Tutorial.pm,v 1.10 2007/09/19 21:30:18 canaran Exp $
6  
7 1 ▶use warnings;
Subroutine BEGIN: called CC=1 SCAR=0.0
8 1 ▶use strict;
Subroutine BEGIN: called CC=1 SCAR=0.0
9  
10 =head1 NAME
11  
12 HTML::SearchPage::Tutorial - HTML::SearchPage distribution tutorial
13  
14 =head1 DESCRIPTION
15  
16 This is the tutorial for HTML::SearchPage module.
17  
18 =head1 INTRODUCTION
19  
20 HTML::SearchPage is a generic distribution for developing web-based search pages that run on top of a database. The search pages can be constructed independent of the schema of the database.
21  
22 For example, let's say that you have scientific experiment data spread across a number of tables in a relational database. For one case, you one table that contains experiment data results in a way that each row contains a single experiment, the researcher that performed the particular experiment, a number of experimental conditions for that particular iteration and the result of the experiment. The experiment table contains a foreign key to another table that contains name and contact information for each researcher. Using HTML::SearchPage, you can easily set up a web page that allows users to query the database for experiment results, filtering results by experimental conditions and generating result tables and formatting result sets as comma/tab-separated text or Excel files.
23  
24 HTML::SearchPage generates the HTML code for the search form, parses CGI params returned in response to using the search form, generates the SQL statement based on parsed parameters, executes them and renders the HTML table that contains the results. It also implements the pagination of the result set, code for generating result dumps and sortable fields.
25  
26 =head1 INSTALLATION AND COMPONENTS
27  
28 HTML::SearchPage distribution can be installed as any other CPAN distribution.
29  
30 The distribution contains the following modules:
31  
32 HTML::SearchPage - Core module that represents a search page.
33 HTML::SearchPage::Param - Side module that represents a parameter
34 field in a search page.
35 HTML::SearchPage::Files - A container to store image and CSS files.
36 This file is not intended to be used directly.
37 It is used by HTML::SearchPage to create temp
38 copies of these non-Perl files.
39 HTML::SearchPage::Tutorial - Tutorial.
40  
41 =head1 REQUIREMENTS
42  
43 =head2 Environment
44  
45 This code was developed for a Linux environment, running Apache as the web server and MySQL as the back-end database server. Running in other compatible environments may require modifications.
46  
47 =head2 Web Server (Apache)
48  
49 When you write your Perl script that uses HTML::SearchPage, you will need to place it in a CGI-executable directory in your web server. In addition to this, you will need a directory which is writable by the user that your web server runs under. This directory will be used to store temp files (few images and a CSS file). This directory needs to be able to be accessible through the web.
50  
51 =head1 DEMO
52  
53 HTML::SearchPage is typically used for making interactive search pages that query a database. In addition, simpler pages that display a single row from a table in a vertical orientation can be generated. Also, results retrieved from the database can be post-processed by external project-specific custom modules.
54  
55 Let's begin by building the sample database described earlier.
56  
57 First table: Each row contains information about a single experiment and a foreign key to a person record in the second table.
58  
59 mysql> describe html_searchpage_experiment;
60 +---------------+--------------+------+-----+---------+----------------+
61 | Field | Type | Null | Key | Default | Extra |
62 +---------------+--------------+------+-----+---------+----------------+
63 | experiment_id | int(11) | NO | PRI | NULL | auto_increment |
64 | person_id | int(11) | YES | | NULL | |
65 | temperature | int(3) | YES | MUL | NULL | |
66 | sample_size | int(3) | YES | MUL | NULL | |
67 | attribute | varchar(30) | YES | MUL | NULL | |
68 | result | varchar(30) | YES | MUL | NULL | |
69 | comments | varchar(100) | YES | | NULL | |
70 +---------------+--------------+------+-----+---------+----------------+
71  
72 experiment_id : the primary key
73 person_id : foreign key to person table
74 temperature : temperature (experimental condition)
75 sample_size : sample_size (experimental condition)
76 attribute : attribute (experimental condition)
77 result : result (experimental condition)
78 comments : additional comments
79  
80 First 10 rows in the table look like this:
81  
82 mysql> select * from html_searchpage_experiment limit 10;
83 +---------------+-----------+-------------+-------------+-----------+--------+---------------+
84 | experiment_id | person_id | temperature | sample_size | attribute | result | comments |
85 +---------------+-----------+-------------+-------------+-----------+--------+---------------+
86 | 1 | 2 | 143 | 346 | color | high | |
87 | 2 | 3 | 154 | 220 | weight | normal | |
88 | 3 | 5 | 70 | 672 | color | high | comments here |
89 | 4 | 4 | 95 | 997 | height | high | comments here |
90 | 5 | 3 | 74 | 598 | height | normal | |
91 | 6 | 3 | 62 | 672 | height | normal | comments here |
92 | 7 | 4 | 109 | 1061 | color | high | comments here |
93 | 8 | 3 | 27 | 969 | weight | normal | comments here |
94 | 9 | 4 | 147 | 756 | weight | low | |
95 | 10 | 2 | 154 | 955 | height | high | comments here |
96 +---------------+-----------+-------------+-------------+-----------+--------+---------------+
97  
98 Second table: Each row contains information on a single researcher.
99  
100 mysql> describe html_searchpage_person;
101 +--------------+--------------+------+-----+---------+----------------+
102 | Field | Type | Null | Key | Default | Extra |
103 +--------------+--------------+------+-----+---------+----------------+
104 | person_id | int(11) | NO | PRI | NULL | auto_increment |
105 | name | varchar(100) | YES | | NULL | |
106 | organization | varchar(100) | YES | | NULL | |
107 | email | varchar(100) | YES | | NULL | |
108 | address | varchar(200) | YES | | NULL | |
109 | comments | varchar(100) | YES | | NULL | |
110 +--------------+--------------+------+-----+---------+----------------+
111  
112 person_id : the primary key
113 name : name of researcher
114 organization : organization
115 email : email
116 address : address
117 comments : additional comments
118  
119 Rows of the table look like this:
120  
121 mysql> select * from html_searchpage_person;
122 +-----------+---------------+----------------+------------------------------+--------------------------+---------------------------------+
123 | person_id | name | organization | email | address | comments |
124 +-----------+---------------+----------------+------------------------------+--------------------------+---------------------------------+
125 | 1 | Researcher #1 | Research Lab A | researcher at research_lab_a | 123 Main St, City, State | comments here for first person |
126 | 2 | Reseracher #2 | Research Lab B | researcher at research_lab_b | 456 Main St, City, State | comments here for second person |
127 | 3 | Researcher #3 | Research Lab C | researcher at research_lab_c | 789 Main St, City, State | comments here for third person |
128 | 4 | Researcher #4 | Research Lab D | researcher at research_lab_d | 234 Main St, City, State | comments here for fourth person |
129 | 5 | Researcher #5 | Research Lab E | researcher at research_lab_e | 567 Main St, City, State | comments here for fifth person |
130 +-----------+---------------+----------------+------------------------------+--------------------------+---------------------------------+
131  
132 The following examples demonstrate main functionality provided by the distribution.
133  
134 =head2 Example 1 - An Interactive Web Page to Query Experiment Data
135  
136 The following script builds a page that allows the user to query experiment results on a join of the two tables with ability to filter on a number of fields.
137  
138 #!/usr/bin/perl
139  
140 use warnings;
141 use strict;
142  
143 use HTML::SearchPage;
144 use HTML::SearchPage::Param;
145  
146 my $sp = HTML::SearchPage->new(
147 page_title => 'HTML::SearchPage Demo',
148 header => '[Placeholder for header]',
149 css => '',
150 temp_dir => qq[/usr/local/demo/html/demo/tmp],
151 temp_dir_eq => qq[http://localhost:8080/demo/tmp],
152 instructions => '[Placeholder for instructions]',
153 footer => '[Placeholder for footer]',
154 db_access_params =>
155 ['DBI:mysql:database=temp;host=localhost;port=3306', 'test', 'test'],
156 base_sql_table => qq[
157 html_searchpage_experiment hse
158 JOIN html_searchpage_person hsp ON (hsp.person_id = hse.person_id)
159 ],
160 base_sql_fields => [
161 'hsp.name',
162 'hse.temperature',
163 'hse.sample_size',
164 'hse.attribute',
165 'hse.result',
166 'hse.comments',
167 ],
168 base_output_headers => [
169 'Researcher:hsp.name',
170 'Temperature (F):hse.temperature',
171 'Sample Size:hse.sample_size',
172 'Attribute:hse.attribute',
173 'Result:hse.result',
174 'Comments',
175 ],
176 sort_fields => 2,
177 method => 'GET',
178 page_size => 20,
179 debug_level => 1,
180 ); # Displays error page if fails
181  
182 my $pf;
183  
184 $pf = HTML::SearchPage::Param->new(
185 -label => 'Temperature (F):',
186 -sql_column => 'hse.temperature',
187 -form_name => 'temperature',
188 -operator_list => ['<:less than', '=:equals', '>:greater than'],
189 -operator_default => '<',
190 -param_type => 'text:12',
191 ) or $sp->display_error_page($@);
192  
193 $sp->param_field('temperature', $pf);
194  
195 $pf = HTML::SearchPage::Param->new(
196 -label => 'Sample Size:',
197 -sql_column => 'hse.sample_size',
198 -form_name => 'sample_size',
199 -operator_list => ['<:less than', '=:equals', '>:greater than'],
200 -operator_default => '>',
201 -param_type => 'text:12',
202 ) or $sp->display_error_page($@);
203  
204 $sp->param_field('sample_size', $pf);
205  
206 $pf = HTML::SearchPage::Param->new(
207 -label => 'Attribute:',
208 -sql_column => 'hse.attribute',
209 -form_name => 'attribute',
210 -operator_list => ['=:equals'],
211 -param_type => 'drop_down',
212 -param_list =>
213 ['DISTINCT:SELECT DISTINCT attribute FROM html_searchpage_experiment'],
214 -auto_all => 1,
215 -auto_null => 1,
216 ) or $sp->display_error_page($@);
217  
218 $sp->param_field('attribute', $pf);
219  
220 $pf = HTML::SearchPage::Param->new(
221 -label => 'Result:',
222 -sql_column => 'hse.result',
223 -form_name => 'result',
224 -operator_list => ['=:equals'],
225 -param_type => 'drop_down',
226 -param_list =>
227 ['DISTINCT:SELECT DISTINCT result FROM html_searchpage_experiment'],
228 -auto_all => 1,
229 -auto_null => 1,
230 ) or $sp->display_error_page($@);
231  
232 $sp->param_field('result', $pf);
233  
234 $sp->display_page;
235  
236 The script consists of 3 main sections:
237  
238 - Instantiating a HTML::SearchPage object (this represents the
239 search page)
240 - Instantiating multiple HTML::SearchPage::Param objects (these represent
241 the parameter fields used to filter results) and adding them to the
242 search page object
243 - Calling the "display_page" method on the search page object
244  
245 Let's begin with the HTML::SearchPage object and the parameters passed on to its constructor:
246  
247 I<page_title, header, footer, css & instructions>
248  
249 You can customize the pages with your page title, header and footer. "page_title" is the title of the page that is displayed on the title bar of the browser and top of the page. "header" and "footer" contain the HTML content that make up the header and footer. The module contains a default CSS but an additional CSS file can be added using the "css" parameter. Also, you can include instructions to be displayed using the "instructions" param. For these parameters, you can simply include the HTML content or you can place content of a file, retrieve it dynamically from a URL or insert output from a script. Please see HTML::SearchPage documentation for implementation details.
250  
251 I<temp_dir & temp_dir_eq>
252  
253 The package uses a temp directory to store temp copies of a few images and a CSS file. This directory needs to be writable by the user which Apache runs under. Also, it needs to be accessible through the web.
254  
255 "temp_dir" is the full path to the location of the directory. "temp_dir_eq" is the URL-equivalent of this directory.
256  
257 For example, if the DOCUMENT_ROOT of the web site that hosts the page is "/usr/local/demo/html" and temp_dir is "/usr/local/demo/html/demo/tmp", the temp_dir_eq would be "http://<domain_name>/demo/tmp".
258  
259 I<db_access_params>
260  
261 The data presented on the page is stored on a MySQL database. "db_access_params" specifies the database access parameters. It is formatted as and array ref containing the datasource, username and password. The datasource is formatted for DBI (please refer to DBI documentation if you need details on the format).
262  
263 [$password, $username, $password]
264  
265 You can specify multiple databases for which a drop-down database selector is generated. Please see HTML::SearchPage documentation for format.
266  
267 I<base_sql_table, base_sql_fields & base_output_headers>
268  
269 These three parameters describe how the data is retrieved from the database.
270  
271 "base_sql_table" describes the table from which data will be retrieved. This can be name of a single table or a clause consisting of JOINs.
272  
273 "base_sql_fields" describes the fields that are retrieved from the database. In this example, we specify 5 fields for retrieval. This parameter is passed as an array ref.
274  
275 For each column name passed by "base_sql_fields", a header is provided by the "base_output_headers" parameter. These headers are used in the display. This parameter is passed as an array ref. For some of the fields, the "base_output_header" is specified in the format:
276  
277 <header>:<sort_field>
278  
279 If a <sort_field> is specified after the header, the corresponding field becomes sortable using <sort_field> as basis for sorting. When a field becomes sortable, a small "S" icon is displayed on its header and it's name appears in the sorting drop-down list.
280  
281 I<sort_fields>
282  
283 A number of sorting drop-downs are provided on the interface. The results can be sorted by multiple fields (e.g. sort by field 1, then by field 2)
284 "sort_fields" specifies the number of sorting drop-downs to display.
285  
286 I<method>
287  
288 This specifies the method used by the form submission, which can be one of 'GET' or 'POST'.
289  
290 I<page_size>
291  
292 The results retrieved from the database are paginated. This parameter defines the number of records to display in each page.
293  
294 I<debug_level>
295  
296 The package provides debugging information on each page. This parameter defines the level of debugging information. Please refer to HTML::SearchPage documentation for details on different debug levels.
297  
298 Now we have a search page object and we are ready to add parameter fields that will be used to query the database. For each field, we create a HTML::SearchPage::Param object and add it to the search page object using search page object's "param_field" method.
299  
300 In this example, 4 parameter fields are added. This means that there will be 4 fields displayed on the top of the page that allow the user to specify constraints in querying the database.
301  
302 Each parameter field, acts on a SQL column. There may be one operator or multiple operators that a user can select for each parameter field. When there is only one operator you can hide it from display.
303  
304 The following are parameters we use with the HTML::SearchPage::Param constructor.
305  
306 I<label>
307  
308 Label of the parameter as displayed on the drop-down.
309  
310 I<sql_column>
311  
312 SQL column that the parameter acts on.
313  
314 I<form_name>
315  
316 Defines the name, which the parameter is represented as in the HTML form.
317  
318 I<operator_list>
319  
320 Operators that are listed for the parameter field. This parameter is passed as an array ref.
321  
322 The following operators are supported:
323  
324 - = : Equal to
325 - like_m : Matches pattern (like; * converted to %; ? converted to _)
326 - like_c : Contains (like; %pattern%)
327 - > : Greater than
328 - >= : Greater than or equal to
329 - < : Less than
330 - <= : Less than or equal to
331 - <> : Not equal to
332 - not_like_m : Not <like_m>
333 - not_like_c : Not <like_c>
334  
335 To display an alternate text for a particular operator, you can use the "<operator>:<display>" format.
336  
337 I<operator_default>
338  
339 The default operator (one that will be displayed when page is first loaded) is specified by this parameter.
340  
341 I<param_type>
342  
343 This parameter specifies the type of the parameter field.
344  
345 The following parameter field types are supported:
346  
347 - text:<length> : Text field, <length> characters long
348 - drop_down : Drop-down list from which a single parameter
349 can be selected
350 - scrolling_list:<size> : Drop-down list from which multiple parameters
351 can be selected
352  
353 I<param_list>
354  
355 For "drop_down" and "scrolling_list" parameter types, multiple parameter values are provided as an array ref. For "text" parameter type, a scalar is needed (if an array ref is provided, all but the first element is ignored).
356  
357 =head2 Example 2 - A Non-interactive Page to Display a Single Row
358  
359 The module also supports simple non-interactive displays. For example, the following implementation is designed to retrieve one record from the person table and present information on the person in a single page. When the script is set up, it can be called by a URL that contains the identifier for the record.
360  
361 #!/usr/bin/perl
362  
363 our $VERSION = '0.01';
364  
365 # $Id: Tutorial.pm,v 1.10 2007/09/19 21:30:18 canaran Exp $
366  
367 use warnings;
368 use strict;
369  
370 use HTML::SearchPage;
371 use HTML::SearchPage::Param;
372  
373 my $sp = HTML::SearchPage->new(
374 page_title => 'HTML::SearchPage Demo',
375 header => '[Placeholder for header]',
376 css => '',
377 temp_dir => qq[/usr/local/demo/html/demo/tmp],
378 temp_dir_eq => qq[http://localhost:8080/demo/tmp],
379 instructions => '',
380 footer => '[Placeholder for footer]',
381 base_sql_table => qq[html_searchpage_person hsp],
382 base_sql_fields => [
383 'hsp.name',
384 'hsp.organization',
385 'hsp.email',
386 'hsp.address',
387 'hsp.comments',
388 ],
389 base_output_headers => [
390 'Name',
391 'Organization',
392 'E-mail',
393 'Address',
394 'Comments',
395 ],
396 base_identifier => 'hsp.person_id',
397 method => 'GET',
398 db_access_params =>
399 ['DBI:mysql:database=temp;host=localhost;port=3306', 'test', 'test'],
400 debug_level => 1,
401 ); # Displays error page if fails
402  
403 $sp->display_info;
404  
405 All the parameters serve the same functions as in the earlier example.
406  
407 The only new parameter is "base_identifier" which defines the unique column which the module will query for.
408  
409 In this particular example, the URL to call this page is constructed as:
410  
411 http://<domain>/<script>?identifier=1
412  
413 The word "identifier" is reserved and must be used as is.
414  
415 Since the "base_identifier" is specified as "hsp.person_id", the module queries the html_searchpage_person table (which is specified by the base_sql_table) for [person_id = "1"].
416  
417 Please note that, this functionality is intended to query for unique identifiers. If multiple records are retrieved as a result of the query an exception is raised and the error page is displayed. Similarly, an exception is raised if no record that match the criteria is found. You might want to perform checks before linking in this manner.
418  
419 =head1 REMARKS
420  
421 Please refer to HTML::SearchPage for a list of all parameters.
422  
423 =head1 TO-DO
424  
425 - add docs on modifiers
426  
427 =head1 AUTHOR
428  
429 Payan Canaran <pcanaran@cpan.org>
430  
431 =head1 BUGS
432  
433 Please report them.
434  
435 =head1 VERSION
436  
437 Version 0.05
438  
439 =head1 ACKNOWLEDGEMENTS
440  
441 =head1 COPYRIGHT & LICENSE
442  
443 Copyright (c) 2005-2007 Cold Spring Harbor Laboratory
444  
445 This program is free software; you can redistribute it and/or modify it
446 under the same terms as Perl itself. See DISCLAIMER.txt for
447 disclaimers of warranty.
448  
449 =cut
450  
451 1 1;
« SearchPage/Files.pm ↑ Summary