1AnyData(3) User Contributed Perl Documentation AnyData(3)
2
6 AnyData -- easy access to data in many formats
7
9 $table = adTie( 'CSV','my_db.csv','o', # create a table
10 {col_names=>'name,country,sex'}
11 );
12 $table->{Sue} = {country=>'de',sex=>'f'}; # insert a row
13 delete $table->{Tom}; # delete a single row
14 $str = $table->{Sue}->{country}; # select a single value
15 while ( my $row = each %$table ) { # loop through table
16 print $row->{name} if $row->{sex} eq 'f';
17 }
18 $rows = $table->{{age=>'> 25'}} # select multiple rows
19 delete $table->{{country=>qr/us⎪mx⎪ca/}}; # delete multiple rows
20 $table->{{country=>'Nz'}}={country=>'nz'}; # update multiple rows
21 my $num = adRows( $table, age=>'< 25' ); # count matching rows
22 my @names = adNames( $table ); # get column names
23 my @cars = adColumn( $table, 'cars' ); # group a column
24 my @formats = adFormats(); # list available parsers
25 adExport( $table, $format, $file, $flags ); # save in specified format
26 print adExport( $table, $format, $flags ); # print to screen in format
27 print adDump($table); # dump table to screen
28 undef $table; # close the table
29 adConvert( $format1, $file1, $format2, $file2 ); # convert btwn formats
31 print adConvert( $format1, $file1, $format2 ); # convert to screen
32
34 The rather wacky idea behind this module and its sister module
35 DBD::AnyData is that any data, regardless of source or format should be
36 accessable and modifiable with the same simple set of methods. This
37 module provides a multi-dimensional tied hash interface to data in a
38 dozen different formats. The DBD::AnyData module adds a DBI/SQL inter‐
39 face for those same formats.
40 Both modules provide built-in protections including appropriate flock‐
42 ing() for all I/O and (in most cases) record-at-a-time access to files
43 rather than slurping of entire files.
44 Currently supported formats include general format flatfiles (CSV,
46 Fixed Length, etc.), specific formats (passwd files, httpd logs, etc.),
47 and a variety of other kinds of formats (XML, Mp3, HTML tables). The
48 number of supported formats will continue to grow rapidly since there
49 is an open API making it easy for any author to create additional for‐
50 mat parsers which can be plugged in to AnyData itself and thereby be
51 accessible by either the tiedhash or DBI/SQL interface.
52
54 The AnyData.pm module itself is pure Perl and does not depend on any‐
55 thing other than modules that come standard with Perl. Some formats
56 and some advanced features require additional modules: to use the
57 remote ftp/http features, you must have the LWP bundle installed; to
58 use the XML format, you must have XML::Parser and XML::Twig installed;
59 to use the HTMLtable format for reading, you must have HTML::Parser and
60 HTML::TableExtract installed but you can use the HTMLtable for writing
61 with just the standard CGI module. To use DBI/SQL commands, you must
62 have DBI, DBD::AnyData, SQL::Statement and DBD::File installed.
63
65 The AnyData module imports eight methods (functions):
66 adTie() -- create a new table or open an existing table
68 adExport() -- save an existing table in a specified format
69 adConvert() -- convert data in one format into another format
70 adFormats() -- list available formats
71 adNames() -- get the column names of a table
72 adRows() -- get the number of rows in a table or query
73 adDump() -- display the data formatted as an array of rows
74 adColumn() -- group values in a single column
75 The adTie() command returns a special tied hash. The tied hash can
77 then be used to access and/or modify data. See below for details
78 With the exception of the XML, HTMLtable, and ARRAY formats, the
80 adTie() command saves all modifications of the data directly to file
81 as they are made. With XML and HTMLtable, you must make your
82 modifications in memory and then explicitly save them to file with
83 adExport().
84 adTie()
86 my $table = adTie( $format, $data, $open_mode, $flags );
88 The adTie() command creates a reference to a multi-dimensional tied
90 hash. In its simplest form, it simply reads a file in a specified for‐
91 mat into the tied hash:
92 my $table = adTie( $format, $file );
94 $format is the name of any supported format 'CSV','Fixed','Passwd', etc.
96 $file is the name of a relative or absolute path to a local file
97 e.g. my $table = adTie( 'CSV', '/usr/me/myfile.csv' );
99 this creates a tied hash called $table by reading data in the
101 CSV (comma separated values) format from the file 'myfile.csv'.
102 The hash reference resulting from adTie() can be accessed and modified
104 as follows:
105 use AnyData;
107 my $table = adTie( $format, $file );
108 $table->{$key}->{$column} # select a value
109 $table->{$key} = {$col1=>$val1,$col2=>$val2...} # update a row
110 delete $table->{$key} # delete a row
111 while(my $row = each %$table) { # loop through rows
112 print $row->{$col1} if $row->{$col2} ne 'baz';
113 }
114 The thing returned by adTie ($table in the example) is not an object,
116 it is a reference to a tied hash. This means that hash operations such
117 as exists, values, keys, may be used, keeping in mind that this is a
118 *reference* to a tied hash so the syntax would be
119 for( keys %$table ) {...}
121 for( values %$table ) {...}
122 Also keep in mind that if the table is really large, you probably do
124 not want to use keys and values because they create arrays in memory
125 containng data from every row in the table. Instead use 'each' as
126 shown above since that cycles through the file one record at a time and
127 never puts the entire table into memory.
128 It is also possible to use more advanced searching on the hash, see
130 "Multiple Row Operations" below.
131 In addition to the simple adTie($format,$file), there are other ways to
133 specify additional information in the adTie() command. The full syntax
134 is:
135 my $table = adTie( $format, $data, $open_mode, $flags );
137 The $data parameter allows you to read data from remote files accessible by
139 http or ftp, see "Using Remote Files" below. It also allows you to treat
140 strings and arrays as data sources without needing a file at all, see
141 "Working with Strings and Arrays" below.
142 The optional $mode parameter defaults to 'r' if none is supplied or
144 must be one of
145 'r' read # read only access
147 'u' update # read/write access
148 'c' create # create a new file unless it already exists
149 'o' overwrite # create a new file, overwriting any that already exist
150 The $flags parameter allows you to specify additional information such
152 as column names. See the sections in "Further Details" below.
153 With the exception of the XML, HTMLtable, and ARRAY formats, the
155 adTie() command saves all modifications of the data directly to file as
156 they are made. With XML and HTMLtable, you must make your modifica‐
157 tions in memory and then explicitly save them to file with adExport().
158 adConvert()
160 adConvert( $format1, $data1, $format2, $file2, $flags1, $flags2 );
162 or
164 print adConvert( $format1, $data1, $format2, undef, $flags1, $flags2 );
166 or
168 my $aryref = adConvert( $format1, $data1, 'ARRAY', undef, $flags1 );
170 This method converts data in any supported format into any other supported
172 format. The resulting data may either be saved to a file (if $file2 is
173 supplied as a parameter) or sent back as a string to e.g. print the data
174 to the screen in the new format (if no $file2 is supplied), or sent back
175 as an array reference if $format2 is 'ARRAY'.
176 Some examples:
178 # convert a CSV file into an XML file
180 #
181 adConvert('CSV','foo.csv','XML','foo.xml');
182 # convert a CSV file into an HTML table and print it to the screen
184 #
185 print adConvert('CSV','foo.csv','HTMLtable');
186 # convert an XML string into a CSV file
188 #
189 adConvert('XML', ["<x><motto id='perl'>TIMTOWTDI</motto></x>"],
190 'CSV','foo.csv'
191 );
192 # convert an array reference into an XML file
194 #
195 adConvert('ARRAY', [['id','motto'],['perl','TIMTOWTDI']],
196 'XML','foo.xml'
197 );
198 # convert an XML file into an array reference
200 #
201 my $aryref = adConvert('XML','foo.xml','ARRAY');
202 See section below "Using strings and arrays" for details.
204 adExport()
206 adExport( $table, $format, $file, $flags );
208 or
210 print adExport( $table, $format );
212 or
214 my $aryref = adExport( $table, 'ARRAY' );
216 This method converts an existing tied hash into another format and/or
218 saves the tied hash as a file in the specified format.
219 Some examples:
221 all assume a previous call to my $table= adTie(...);
223 # export table to an XML file
225 #
226 adExport($table','XML','foo.xml');
227 # export table to an HTML string and print it to the screen
229 #
230 print adExport($table,'HTMLtable');
231 # export the table to an array reference
233 #
234 my $aryref = adExport($table,'ARRAY');
235 See section below "Using strings and arrays" for details.
237 adNames()
239 my $table = adTie(...);
241 my @column_names = adNames($table);
242 This method returns an array of the column names for the specified ta‐
244 ble.
245 adRows()
247 my $table = adTie(...);
249 adRows( $table, %search_hash );
250 This method takes an AnyData tied hash created with adTie() and counts
252 the rows in the table that match the search hash.
253 For example, this snippet returns a count of the rows in the file that
255 contain the specified page in the request column
256 my $hits = adTie( 'Weblog', 'access.log');
258 print adRows( $hits , request => 'mypage.html' );
259 The search hash may contain multiple search criteria, see the section
261 on mltiple row operations below.
262 If the search_hash is omitted, it returns a count of all rows.
264 adColumn()
266 my @col_vals = adColumn( $table, $column_name, $distinct_flag );
268 This method returns an array of values taken from the specified column.
270 If there is a distinct_flag parameter, duplicates will be eliminated
271 from the list.
272 For example, this snippet returns a unique list of the values in the
274 'player' column of the table.
275 my $game = adTie( 'Pipe','games.db' );
277 my @players = adColumn( $game, 'player', 1 );
278 adDump()
280 my $table = adTie(...);
282 print adDump($table);
283 This method prints the raw data in the table. Column names are printed
285 inside angle brackets and separated by colons on the first line, then
286 each row is printed as a list of values inside sqaure brackets.
287 adFormats()
289 print "$_\n for adFormats();
291 This method shows the available format parsers, e.g. 'CSV', 'XML', etc.
293 It looks in your @INC for the .../AnyData/Format directory and prints
294 the names of format parsing files there. If the parser requires fur‐
295 ther modules (e.g. XML requires XML::Parser) and you do not have the
296 additonal modules installed, the format will not work even if listed by
297 this command. Otherwise, all formats should work as described in this
298 documentation.
299
301 Column Names
302 Column names may be assigned in three ways:
304 * pre -- The format parser pre-assigns column
306 names (e.g. Passwd files automatically have
307 columns named 'username', 'homedir', 'GID', etc.).
308 * user -- The user specifies the column names as a comma
310 separated string associated with the key 'cols':
311 my $table = adTie( $format,
313 $file,
314 $mode,
315 {cols=>'name,age,gender'}
316 );
317 * auto -- If there is no pre-assigned list of column names
319 and none defined by the user, the first line of
320 the file is treated as a list of column names;
321 the line is parsed according to the specific
322 format (e.g. CSV column names are a comma-separated
323 list, Tab column names are a tab separated list);
324 When creating a new file in a format that does not pre-assign column
326 names, the user *must* manually assign them as shown above.
327 Some formats have special rules for assigning column names
329 (XML,Fixed,HTMLtable), see the sections below on those formats.
330 Key Columns
332 The AnyData modules support tables that have a single key column that
334 uniquely identifies each row as well as tables that do not have such
335 keys. For tables where there is a unique key, that key may be assigned
336 in three ways:
337 * pre -- The format parser automatically pre-assigns the
339 key column name e.g. Passwd files automatically
340 have 'username' as the key column.
341 * user -- The user specifies the key column name:
343 my $table = adTie( $format,
345 $file,
346 $mode,
347 {key=>'country'}
348 );
349 * auto If there is no pre-assigned key column and the user
351 does not define one, the first column becomes the
352 default key column
353 Format Specific Details
355 For full details, see the documentation for AnyData::Format::Foo
357 where Foo is any of the formats listed in the adFormats() command
358 e.g. 'CSV', 'XML', etc.
359 Included below are only some of the more important details of the
361 specific parsers.
362 Fixed Format
364 When using the Fixed format for fixed length records you must
365 always specify a pattern indicating the lengths of the fields.
366 This should be a string as would be passed to the unpack() function
367 to unpack the records in your Fixed length definition:
368 my $t = adTie( 'Fixed', $file, 'r', {pattern=>'A3 A7 A9'} );
370 If you want the column names to appear on the first line of a Fixed
372 file, they should be in comma-separated format, not in Fixed for‐
373 mat. This is different from other formats which use their own for‐
374 mat to display the column names on the first line. This is neces‐
375 sary because the name of the column might be longer than the length
376 of the column.
377 XML Format
379 The XML format does not allow you to specify column names as a flag,
380 rather you specify a "record_tag" and the column names are determined
381 from the contents of the tag. If no record_tag is specified, the
382 record tag will be assumed to be the first child of the root of the
383 XML tree. That child and its structure will be determined from the
384 DTD if there is one, or from the first occurring record if there is
385 no DTD.
386 For simple XML, no flags are necessary:
388 <table>
390 <row row_id="1"><name>Joe</name><location>Seattle</location></row>
391 <row row_id="2"><name>Sue</name><location>Portland</location></row>
392 </table>
393 The record_tag will default to the first child, namely "row". The
395 column names will be generated from the attributes of the record
396 tag and all of the tags included under the record tag, so the col‐
397 umn names in this example will be "row_id","name","location".
398 If the record_tag is not the first child, you will need to specify
400 it. For example:
401 <db>
403 <table table_id="1">
404 <row row_id="1"><name>Joe</name><location>Seattle</location></row>
405 <row row_id="2"><name>Sue</name><location>Portland</location></row>
406 </table>
407 <table table_id="2">
408 <row row_id="1"><name>Bob</name><location>Boise</location></row>
409 <row row_id="2"><name>Bev</name><location>Billings</location></row>
410 </table>
411 </db>
412 In this case you will need to specify "row" as the record_tag since
414 it is not the first child of the tree. The column names will be
415 generated from the attributes of row's parent (if the parent is not
416 the root), from row's attributes and sub tags, i.e. "ta‐
417 ble_id","row_id","name","location".
418 When exporting XML, you can specify a DTD to control the output.
420 For example, if you import a table from CSV or from an Array, you
421 can output as XML and specify which of the columns become tags and
422 which become attributes and also specify the nesting of the tags in
423 your DTD.
424 The XML format parser is built on top of Michel Rodriguez's excel‐
426 lent XML::Twig which is itslef based on XML::Parser. Parameters to
427 either of those modules may be passed in the flags for adTie() and
428 the other commands including the "prettyPrint" flag to specify how
429 the output XML is displayed and things like ProtocolEncoding. Pro‐
430 tocolEncoding defaults to 'ISO-8859-1', all other flags keep the
431 defaults of XML::Twig and XML::Parser. See the documentation of
432 those modules for details;
433 CAUTION: Unlike other formats, the XML format does not save changes to
435 the file as they are entered, but only saves the changes when you explicitly
436 request them to be saved with the adExport() command.
437 HTMLtable Format
439 This format is based on Matt Sisk's excelletn HTML::TableExtract.
440 It can be used to read an existing table from an html page, or to
442 create a new HTML table from any data source.
443 You may control which table in an HTML page is used with the column_names,
445 depth and count flags.
446 If a column_names flag is passed, the first table that contains those names
448 as the cells in a row will be selected.
449 If depth and or count parameters are passed, it will look for tables as
451 specified in the HTML::TableExtract documentation.
452 If none of column_names, depth, or count flags are passed, the first table
454 encountered in the file will be the table selected and its first row will
455 be used to determine the column names for the table.
456 When exporting to an HTMLtable, you may pass flags to specify properties
458 of the whole table (table_flags), the top row containing the column names
459 (top_row_flags), and the data rows (data_row_flags). These flags follow
460 the syntax of CGI.pm table constructors, e.g.:
461 print adExport( $table, 'HTMLtable', {
463 table_flags => {Border=>3,bgColor=>'blue'};
464 top_row_flags => {bgColor=>'red'};
465 data_row_flags => {valign='top'};
466 });
467 The table_flags will default to {Border=>1,bgColor=>'white'} if none
469 are specified.
470 The top_row_flags will default to {bgColor=>'#c0c0c0'} if none are
472 specified;
473 The data_row_flags will be empty if none are specified.
475 In other words, if no flags are specified the table will print out with
477 a border of 1, the column headings in gray, and the data rows in white.
478 CAUTION: This module will *not* preserve anything in the html file except
480 the selected table so if your file contains more than the selected table,
481 you will want to use adTie() to read the table and then adExport() to write
482 the table to a different file. When using the HTMLtable format, this is the
483 only way to preserve changes to the data, the adTie() command will *not*
484 write to a file.
485 Multiple Row Operations
487 The AnyData hash returned by adTie() may use either single values as
489 keys, or a reference to a hash of comparisons as a key. If the key to
490 the hash is a single value, the hash operates on a single row but if
491 the key to the hash is itself a hash reference, the hash operates on a
492 group of rows.
493 my $num_deleted = delete $table->{Sue};
495 This example deletes a single row where the key column has the value
497 'Sue'. If multiple rows have the value 'Sue' in that column, only the
498 first is deleted. It uses a simple string as a key, therefore it oper‐
499 ates on only a single row.
500 my $num_deleted = delete $table->{ {name=>'Sue'} };
502 This example deletes all rows where the column 'name' is equal to
504 'Sue'. It uses a hashref as a key and therefore operates on multiple
505 rows.
506 The hashref used in this example is a single column comparison but the
508 hashref could also include multiple column comparisons. This deletes
509 all rows where the the values listed for the country, gender, and age
510 columns are equal to those specified:
511 my $num_deleted = delete $table->{{ country => 'us',
513 gender => 'm',
514 age => '25'
515 }}
516 In addition to simple strings, the values may be specified as regular
518 expressions or as numeric or alphabetic comparisons. This will delete
519 all North American males under the age of 25:
520 my $num_deleted = delete $table->{{ country => qr/mx⎪us⎪ca/,
522 gender => 'm',
523 age => '< 25'
524 }}
525 If numeric or alphabetic comparisons are used, they should be a string
527 with the comparison operator separated from the value by a space, e.g.
528 '> 4' or 'lt b'.
529 This kind of search hashref can be used not only to delete multiple
531 rows, but also to update rows. In fact you *must* use a hashref key in
532 order to update your table. Updating is the only operation that can
533 not be done with a single string key.
534 The search hashref can be used with a select statement, in which case
536 it returns a reference to an array of rows matching the criteria:
537 my $male_players = $table->{{gender=>'m'}};
539 for my $player( @$male_players ) { print $player->{name},"\n" }
540 This should be used with caution with a large table since it gathers
542 all of the selected rows into an array in memory. Again, 'each' is a
543 much better way for large tables. This accomplishes the same thing as
544 the example above, but without ever pulling more than a row into memory
545 at a time:
546 while( my $row= each %$table ) {
548 print $row->{name}, "\n" if $row->{gender}=>'m';
549 }
550 Search criteria for multiple rows can also be used with the adRows()
552 function:
553 my $num_of_women = adRows( $table, gender => 'w' );
555 That does *not* pull the entire table into memory, it counts the rows a
557 record at a time.
558 Using Remote Files
560 If the first file parameter of adTie() or adConvert() begins with
562 "http://" or "ftp://", the file is treated as a remote URL and the LWP
563 module is called behind the scenes to fetch the file. If the files are
564 in an area that requires authentication, that may be supplied in the
565 $flags parameter.
566 For example:
568 # read a remote file and access it via a tied hash
570 #
571 my $table = adTie( 'XML', 'http://www.foo.edu/bar.xml' );
572 # same with username/password
574 #
575 my $table = ( 'XML', 'ftp://www.foo.edu/pub/bar.xml', 'r'
576 { user => 'me', pass => 'x7dy4'
577 );
578 # read a remote file, convert it to an HTML table, and print it
580 #
581 print adConvert( 'XML', 'ftp://www.foo.edu/pub/bar.xml', 'HTMLtable' );
582 Using Strings and Arrays
584 Strings and arrays may be used as either the source of data input or as
586 the target of data output. Strings should be passed as the only ele‐
587 ment of an array reference (in other words, insdie square brackets).
588 Arrays should be a reference to an array whose first element is a ref‐
589 erence to an array of column names and whose succeeding elements are
590 references to arrays of row values.
591 For example:
593 my $table = adTie( 'XML', ["<x><motto id='perl'>TIMTOWTDI</motto></x>"] );
595 This uses the XML format to parse the supplied string and returns a tied
597 hash to the resulting table.
598 my $table = adTie( 'ARRAY', [['id','motto'],['perl','TIMTOWTDI']] );
600 This uses the column names "id" and "motto" and the supplied row values
602 and returns a tied hash to the resulting table.
603 It is also possible to use an empty array to create a new empty tied
605 hash in any format, for example:
606 my $table = adTie('XML',[],'c');
608 creates a new empty tied hash;
610 See adConvert() and adExport() for further examples of using strings
612 and arrays.
613 Ties, Flocks, I/O, and Atomicity
615 AnyData provides flocking which works under the limitations of flock --
617 that it only works if other processes accessing the files are also
618 using flock and only on platforms that support flock. See the flock()
619 man page for details.
620 Here is what the user supplied open modes actually do:
622 r = read only (LOCK_SH) O_RDONLY
624 u = update (LOCK_EX) O_RDWR
625 c = create (LOCK_EX) O_CREAT ⎪ O_RDWR ⎪ O_EXCL
626 o = overwrite (LOCK_EX) O_CREAT ⎪ O_RDWR ⎪ O_TRUNC
627 When you use something like "my $table = adTie(...)", it opens the file
629 with a lock and leaves the file and lock open until 1) the hash vari‐
630 able ($table) goes out of scope or 2) the hash is undefined (e.g.
631 "undef $table") or 3) the hash is re-assigned to another tie. In all
632 cases the file is closed and the lock released.
633 If adTie is called without creating a tied hash variable, the file is
635 closed and the lock released immediately after the call to adTie.
636 For example: print adTie('XML','foo.xml')->{main_office}->{phone}.
638 That obtains a shared lock, opens the file, retrieves the one value
640 requested, closes the file and releases the lock.
641 These two examples accomplish the same thing but the first example
643 opens the file once, does all of the deletions, keeping the exclusive
644 lock in place until they are all done, then closes the file. The sec‐
645 ond example opens and closes the file three times, once for each dele‐
646 tion and releases the exclusive lock between each deletion:
647 1. my $t = adTie('Pipe','games.db','u');
649 delete $t->{"user$_"} for (0..3);
650 undef $t; # closes file and releases lock
651 2. delete adTie('Pipe','games.db','u')->{"user$_"} for (0..3);
653 # no undef needed since no hash variable created
654 Deletions and Packing
656 In order to save time and to prevent having to do writes anywhere
658 except at the end of the file, deletions and updates are *not* done at
659 the time of issuing a delete command. Rather when the user does a
660 delete, the position of the deleted record is stored in a hash and when
661 the file is saved to disk, the deletions are only then physically
662 removed by packing the entire database. Updates are done by inserting
663 the new record at the end of the file and marking the old record for
664 deletion. In the normal course of events, all of this should be trans‐
665 parent and you'll never need to worry about it. However, if your
666 server goes down after you've made updates or deletions but before
667 you've saved the file, then the deleted rows will remain in the data‐
668 base and for updates there will be duplicate rows -- the old unpdated
669 row and the new updated row. If you are worried about this kind of
670 event, then use atomic deletes and updates as shown in the section
671 above. There's still a very small possiblity of a crash in between the
672 deletion and the save, but in this case it should impact at most a sin‐
673 gle row. (BIG thanks to Matthew Wickline for suggestions on handling
674 deletes)
675
677 See the README file and the test.pl included with the module for fur‐
678 ther examples.
679 See the AnyData/Format/*.pm PODs for further details of specific for‐
681 mats.
682 For further support, please use comp.lang.perl.modules
684
686 Special thanks to Andy Duncan, Tom Lowery, Randal Schwartz, Michel
687 Rodriguez, Jochen Wiedmann, Tim Bunce, Aligator Descartes, Mathew Per‐
688 sico, Chris Nandor, Malcom Cook and to many others on the DBI mailing
689 lists and the clp* newsgroups.
690
692 Jeff Zucker <jeff@vpservices.com>
693 This module is copyright (c), 2000 by Jeff Zucker.
695 It may be freely distributed under the same terms as Perl itself.
696perl v5.8.8 2001-07-17 AnyData(3)