1AnyData(3) User Contributed Perl Documentation AnyData(3)
2
6 AnyData - (DEPRECATED) easy access to data in many formats
7
9 use AnyData;
10 my $table = adTie( 'CSV','my_db.csv','o', # create a table
11 {col_names=>'name,country,sex'}
12 );
13 $table->{Sue} = {country=>'de',sex=>'f'}; # insert a row
14 delete $table->{Tom}; # delete a single row
15 $str = $table->{Sue}->{country}; # select a single value
16 while ( my $row = each %$table ) { # loop through table
17 print $row->{name} if $row->{sex} eq 'f';
18 }
19 $rows = $table->{{age=>'> 25'}}; # select multiple rows
20 delete $table->{{country=>qr/us|mx|ca/}}; # delete multiple rows
21 $table->{{country=>'Nz'}}={country=>'nz'}; # update multiple rows
22 my $num = adRows( $table, age=>'< 25' ); # count matching rows
23 my @names = adNames( $table ); # get column names
24 my @cars = adColumn( $table, 'cars' ); # group a column
25 my @formats = adFormats(); # list available parsers
26 adExport( $table, $format, $file, $flags ); # save in specified format
27 print adExport( $table, $format, $flags ); # print to screen in format
28 print adDump($table); # dump table to screen
29 undef $table; # close the table
30 #adConvert( $format1, $file1, $format2, $file2 ); # convert btwn formats
32 #print adConvert( $format1, $file1, $format2 ); # convert to screen
33
35 The rather wacky idea behind this module and its sister module
36 DBD::AnyData is that any data, regardless of source or format should be
37 accessible and modifiable with the same simple set of methods. This
38 module provides a multidimensional tied hash interface to data in a
39 dozen different formats. The DBD::AnyData module adds a DBI/SQL
40 interface for those same formats.
41 Both modules provide built-in protections including appropriate
43 flocking() for all I/O and (in most cases) record-at-a-time access to
44 files rather than slurping of entire files.
45 Currently supported formats include general format flat files (CSV,
47 Fixed Length, etc.), specific formats (passwd files, httpd logs, etc.),
48 and a variety of other kinds of formats (XML, Mp3, HTML tables). The
49 number of supported formats will continue to grow rapidly since there
50 is an open API making it easy for any author to create additional
51 format parsers which can be plugged in to AnyData itself and thereby be
52 accessible by either the tiedhash or DBI/SQL interface.
53
55 The AnyData.pm module itself is pure Perl and does not depend on
56 anything other than modules that come standard with Perl. Some formats
57 and some advanced features require additional modules: to use the
58 remote ftp/http features, you must have the LWP bundle installed; to
59 use the XML format, you must have XML::Parser and XML::Twig installed;
60 to use the HTMLtable format for reading, you must have HTML::Parser and
61 HTML::TableExtract installed but you can use the HTMLtable for writing
62 with just the standard CGI module. To use DBI/SQL commands, you must
63 have DBI, DBD::AnyData, SQL::Statement and DBD::File installed.
64
66 The AnyData module imports eight methods (functions):
67 adTie() -- create a new table or open an existing table
69 adExport() -- save an existing table in a specified format
70 adConvert() -- convert data in one format into another format
71 adFormats() -- list available formats
72 adNames() -- get the column names of a table
73 adRows() -- get the number of rows in a table or query
74 adDump() -- display the data formatted as an array of rows
75 adColumn() -- group values in a single column
76 The adTie() command returns a special tied hash. The tied hash can
78 then be used to access and/or modify data. See below for details
79 With the exception of the XML, HTMLtable, and ARRAY formats, the
81 adTie() command saves all modifications of the data directly to file as
82 they are made. With XML and HTMLtable, you must make your
83 modifications in memory and then explicitly save them to file with
84 adExport().
85 adTie()
87 my $table = adTie( $format, $data, $open_mode, $flags );
88 The adTie() command creates a reference to a multidimensional tied
90 hash. In its simplest form, it simply reads a file in a specified
91 format into the tied hash:
92 my $table = adTie( $format, $file );
94 $format is the name of any supported format 'CSV','Fixed','Passwd',
96 etc. $file is the name of a relative or absolute path to a local file
97 e.g.
99 my $table = adTie( 'CSV', '/usr/me/myfile.csv' );
100 this creates a tied hash called $table by reading data in the CSV
102 (comma separated values) format from the file 'myfile.csv'.
103 The hash reference resulting from adTie() can be accessed and modified
105 as follows:
106 use AnyData;
108 my $table = adTie( $format, $file );
109 $table->{$key}->{$column}; # select a value
110 $table->{$key} = {$col1=>$val1,$col2=>$val2...}; # update a row
111 delete $table->{$key}; # delete a row
112 while(my $row = each %$table) { # loop through rows
113 print $row->{$col1} if $row->{$col2} ne 'baz';
114 }
115 The thing returned by adTie ($table in the example) is not an object,
117 it is a reference to a tied hash. This means that hash operations such
118 as exists, values, keys, may be used, keeping in mind that this is a
119 *reference* to a tied hash so the syntax would be
120 for( keys %$table ) {...}
122 for( values %$table ) {...}
123 Also keep in mind that if the table is really large, you probably do
125 not want to use keys and values because they create arrays in memory
126 containing data from every row in the table. Instead use 'each' as
127 shown above since that cycles through the file one record at a time and
128 never puts the entire table into memory.
129 It is also possible to use more advanced searching on the hash, see
131 "Multiple Row Operations" below.
132 In addition to the simple adTie($format,$file), there are other ways to
134 specify additional information in the adTie() command. The full syntax
135 is:
136 my $table = adTie( $format, $data, $open_mode, $flags );
138 The $data parameter allows you to read data from remote files accessible by
140 http or ftp, see "Using Remote Files" below. It also allows you to treat
141 strings and arrays as data sources without needing a file at all, see
142 "Working with Strings and Arrays" below.
143 The optional $mode parameter defaults to 'r' if none is supplied or
145 must be one of
146 'r' read # read only access
148 'u' update # read/write access
149 'c' create # create a new file unless it already exists
150 'o' overwrite # create a new file, overwriting any that already exist
151 The $flags parameter allows you to specify additional information such
153 as column names. See the sections in "Further Details" below.
154 With the exception of the XML, HTMLtable, and ARRAY formats, the
156 adTie() command saves all modifications of the data directly to file as
157 they are made. With XML and HTMLtable, you must make your
158 modifications in memory and then explicitly save them to file with
159 adExport().
160 adConvert()
162 adConvert( $format1, $data1, $format2, $file2, $flags1, $flags2 );
163 or
165 print adConvert( $format1, $data1, $format2, undef, $flags1, $flags2 );
167 or
169 my $aryref = adConvert( $format1, $data1, 'ARRAY', undef, $flags1 );
171 This method converts data in any supported format into any other supported
173 format. The resulting data may either be saved to a file (if $file2 is
174 supplied as a parameter) or sent back as a string to e.g. print the data
175 to the screen in the new format (if no $file2 is supplied), or sent back
176 as an array reference if $format2 is 'ARRAY'.
177 Some examples:
179 # convert a CSV file into an XML file
181 #
182 adConvert('CSV','foo.csv','XML','foo.xml');
183 # convert a CSV file into an HTML table and print it to the screen
185 #
186 print adConvert('CSV','foo.csv','HTMLtable');
187 # convert an XML string into a CSV file
189 #
190 adConvert('XML', ["<x><motto id='perl'>TIMTOWTDI</motto></x>"],
191 'CSV','foo.csv'
192 );
193 # convert an array reference into an XML file
195 #
196 adConvert('ARRAY', [['id','motto'],['perl','TIMTOWTDI']],
197 'XML','foo.xml'
198 );
199 # convert an XML file into an array reference
201 #
202 my $aryref = adConvert('XML','foo.xml','ARRAY');
203 See section below "Using strings and arrays" for details.
205 adExport()
207 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(...);
240 my @column_names = adNames($table);
241 This method returns an array of the column names for the specified
243 table.
244 adRows()
246 my $table = adTie(...);
247 adRows( $table, %search_hash );
248 This method takes an AnyData tied hash created with adTie() and counts
250 the rows in the table that match the search hash.
251 For example, this snippet returns a count of the rows in the file that
253 contain the specified page in the request column
254 my $hits = adTie( 'Weblog', 'access.log');
256 print adRows( $hits , request => 'mypage.html' );
257 The search hash may contain multiple search criteria, see the section
259 on multiple row operations below.
260 If the search_hash is omitted, it returns a count of all rows.
262 adColumn()
264 my @col_vals = adColumn( $table, $column_name, $distinct_flag );
265 This method returns an array of values taken from the specified column.
267 If there is a distinct_flag parameter, duplicates will be eliminated
268 from the list.
269 For example, this snippet returns a unique list of the values in the
271 'player' column of the table.
272 my $game = adTie( 'Pipe','games.db' );
274 my @players = adColumn( $game, 'player', 1 );
275 adDump()
277 my $table = adTie(...);
278 print adDump($table);
279 This method prints the raw data in the table. Column names are printed
281 inside angle brackets and separated by colons on the first line, then
282 each row is printed as a list of values inside square brackets.
283 adFormats()
285 print "$_\n for adFormats();
286 This method shows the available format parsers, e.g. 'CSV', 'XML', etc.
288 It looks in your @INC for the .../AnyData/Format directory and prints
289 the names of format parsing files there. If the parser requires
290 further modules (e.g. XML requires XML::Parser) and you do not have the
291 additional modules installed, the format will not work even if listed
292 by this command. Otherwise, all formats should work as described in
293 this documentation.
294
296 Column Names
297 Column names may be assigned in three ways:
298 * pre -- The format parser preassigns column
300 names (e.g. Passwd files automatically have
301 columns named 'username', 'homedir', 'GID', etc.).
302 * user -- The user specifies the column names as a comma
304 separated string associated with the key 'cols':
305 my $table = adTie( $format,
307 $file,
308 $mode,
309 {cols=>'name,age,gender'}
310 );
311 * auto -- If there is no preassigned list of column names
313 and none defined by the user, the first line of
314 the file is treated as a list of column names;
315 the line is parsed according to the specific
316 format (e.g. CSV column names are a comma-separated
317 list, Tab column names are a tab separated list);
318 When creating a new file in a format that does not preassign column
320 names, the user *must* manually assign them as shown above.
321 Some formats have special rules for assigning column names
323 (XML,Fixed,HTMLtable), see the sections below on those formats.
324 Key Columns
326 The AnyData modules support tables that have a single key column that
327 uniquely identifies each row as well as tables that do not have such
328 keys. For tables where there is a unique key, that key may be assigned
329 in three ways:
330 * pre -- The format parser automatically preassigns the
332 key column name e.g. Passwd files automatically
333 have 'username' as the key column.
334 * user -- The user specifies the key column name:
336 my $table = adTie( $format,
338 $file,
339 $mode,
340 {key=>'country'}
341 );
342 * auto If there is no preassigned key column and the user
344 does not define one, the first column becomes the
345 default key column
346 Format Specific Details
348 For full details, see the documentation for AnyData::Format::Foo
349 where Foo is any of the formats listed in the adFormats() command
350 e.g. 'CSV', 'XML', etc.
351 Included below are only some of the more important details of the
353 specific parsers.
354 Fixed Format
356 When using the Fixed format for fixed length records you must
357 always specify a pattern indicating the lengths of the fields.
358 This should be a string as would be passed to the unpack() function
359 to unpack the records in your Fixed length definition:
360 my $t = adTie( 'Fixed', $file, 'r', {pattern=>'A3 A7 A9'} );
362 If you want the column names to appear on the first line of a Fixed
364 file, they should be in comma-separated format, not in Fixed
365 format. This is different from other formats which use their own
366 format to display the column names on the first line. This is
367 necessary because the name of the column might be longer than the
368 length of the column.
369 XML Format
371 The XML format does not allow you to specify column names as a flag,
372 rather you specify a "record_tag" and the column names are determined
373 from the contents of the tag. If no record_tag is specified, the
374 record tag will be assumed to be the first child of the root of the
375 XML tree. That child and its structure will be determined from the
376 DTD if there is one, or from the first occurring record if there is
377 no DTD.
378 For simple XML, no flags are necessary:
380 <table>
382 <row row_id="1"><name>Joe</name><location>Seattle</location></row>
383 <row row_id="2"><name>Sue</name><location>Portland</location></row>
384 </table>
385 The record_tag will default to the first child, namely "row". The
387 column names will be generated from the attributes of the record
388 tag and all of the tags included under the record tag, so the
389 column names in this example will be "row_id","name","location".
390 If the record_tag is not the first child, you will need to specify
392 it. For example:
393 <db>
395 <table table_id="1">
396 <row row_id="1"><name>Joe</name><location>Seattle</location></row>
397 <row row_id="2"><name>Sue</name><location>Portland</location></row>
398 </table>
399 <table table_id="2">
400 <row row_id="1"><name>Bob</name><location>Boise</location></row>
401 <row row_id="2"><name>Bev</name><location>Billings</location></row>
402 </table>
403 </db>
404 In this case you will need to specify "row" as the record_tag since
406 it is not the first child of the tree. The column names will be
407 generated from the attributes of row's parent (if the parent is not
408 the root), from row's attributes and sub tags, i.e.
409 "table_id","row_id","name","location".
410 When exporting XML, you can specify a DTD to control the output.
412 For example, if you import a table from CSV or from an Array, you
413 can output as XML and specify which of the columns become tags and
414 which become attributes and also specify the nesting of the tags in
415 your DTD.
416 The XML format parser is built on top of Michel Rodriguez's
418 excellent XML::Twig which is itself based on XML::Parser.
419 Parameters to either of those modules may be passed in the flags
420 for adTie() and the other commands including the "prettyPrint" flag
421 to specify how the output XML is displayed and things like
422 ProtocolEncoding. ProtocolEncoding defaults to 'ISO-8859-1', all
423 other flags keep the defaults of XML::Twig and XML::Parser. See
424 the documentation of those modules for details;
425 CAUTION: Unlike other formats, the XML format does not save changes to
427 the file as they are entered, but only saves the changes when you explicitly
428 request them to be saved with the adExport() command.
429 HTMLtable Format
431 This format is based on Matt Sisk's excelletn HTML::TableExtract.
432 It can be used to read an existing table from an html page, or to
434 create a new HTML table from any data source.
435 You may control which table in an HTML page is used with the column_names,
437 depth and count flags.
438 If a column_names flag is passed, the first table that contains those names
440 as the cells in a row will be selected.
441 If depth and or count parameters are passed, it will look for tables as
443 specified in the HTML::TableExtract documentation.
444 If none of column_names, depth, or count flags are passed, the first table
446 encountered in the file will be the table selected and its first row will
447 be used to determine the column names for the table.
448 When exporting to an HTMLtable, you may pass flags to specify properties
450 of the whole table (table_flags), the top row containing the column names
451 (top_row_flags), and the data rows (data_row_flags). These flags follow
452 the syntax of CGI.pm table constructors, e.g.:
453 print adExport( $table, 'HTMLtable', {
455 table_flags => {Border=>3,bgColor=>'blue'};
456 top_row_flags => {bgColor=>'red'};
457 data_row_flags => {valign='top'};
458 });
459 The table_flags will default to {Border=>1,bgColor=>'white'} if none
461 are specified.
462 The top_row_flags will default to {bgColor=>'#c0c0c0'} if none are
464 specified;
465 The data_row_flags will be empty if none are specified.
467 In other words, if no flags are specified the table will print out with
469 a border of 1, the column headings in gray, and the data rows in white.
470 CAUTION: This module will *not* preserve anything in the html file except
472 the selected table so if your file contains more than the selected table,
473 you will want to use adTie() to read the table and then adExport() to write
474 the table to a different file. When using the HTMLtable format, this is the
475 only way to preserve changes to the data, the adTie() command will *not*
476 write to a file.
477 Multiple Row Operations
479 The AnyData hash returned by adTie() may use either single values as
480 keys, or a reference to a hash of comparisons as a key. If the key to
481 the hash is a single value, the hash operates on a single row but if
482 the key to the hash is itself a hash reference, the hash operates on a
483 group of rows.
484 my $num_deleted = delete $table->{Sue};
486 This example deletes a single row where the key column has the value
488 'Sue'. If multiple rows have the value 'Sue' in that column, only the
489 first is deleted. It uses a simple string as a key, therefore it
490 operates on only a single row.
491 my $num_deleted = delete $table->{ {name=>'Sue'} };
493 This example deletes all rows where the column 'name' is equal to
495 'Sue'. It uses a hashref as a key and therefore operates on multiple
496 rows.
497 The hashref used in this example is a single column comparison but the
499 hashref could also include multiple column comparisons. This deletes
500 all rows where the the values listed for the country, gender, and age
501 columns are equal to those specified:
502 my $num_deleted = delete $table->{{ country => 'us',
504 gender => 'm',
505 age => '25'
506 }}
507 In addition to simple strings, the values may be specified as regular
509 expressions or as numeric or alphabetic comparisons. This will delete
510 all North American males under the age of 25:
511 my $num_deleted = delete $table->{{ country => qr/mx|us|ca/,
513 gender => 'm',
514 age => '< 25'
515 }}
516 If numeric or alphabetic comparisons are used, they should be a string
518 with the comparison operator separated from the value by a space, e.g.
519 '> 4' or 'lt b'.
520 This kind of search hashref can be used not only to delete multiple
522 rows, but also to update rows. In fact you *must* use a hashref key in
523 order to update your table. Updating is the only operation that can
524 not be done with a single string key.
525 The search hashref can be used with a select statement, in which case
527 it returns a reference to an array of rows matching the criteria:
528 my $male_players = $table->{{gender=>'m'}};
530 for my $player( @$male_players ) { print $player->{name},"\n" }
531 This should be used with caution with a large table since it gathers
533 all of the selected rows into an array in memory. Again, 'each' is a
534 much better way for large tables. This accomplishes the same thing as
535 the example above, but without ever pulling more than a row into memory
536 at a time:
537 while( my $row= each %$table ) {
539 print $row->{name}, "\n" if $row->{gender}=>'m';
540 }
541 Search criteria for multiple rows can also be used with the adRows()
543 function:
544 my $num_of_women = adRows( $table, gender => 'w' );
546 That does *not* pull the entire table into memory, it counts the rows a
548 record at a time.
549 Using Remote Files
551 If the first file parameter of adTie() or adConvert() begins with
552 "http://" or "ftp://", the file is treated as a remote URL and the LWP
553 module is called behind the scenes to fetch the file. If the files are
554 in an area that requires authentication, that may be supplied in the
555 $flags parameter.
556 For example:
558 # read a remote file and access it via a tied hash
560 #
561 my $table = adTie( 'XML', 'http://www.foo.edu/bar.xml' );
562 # same with username/password
564 #
565 my $table = ( 'XML', 'ftp://www.foo.edu/pub/bar.xml', 'r'
566 { user => 'me', pass => 'x7dy4'
567 );
568 # read a remote file, convert it to an HTML table, and print it
570 #
571 print adConvert( 'XML', 'ftp://www.foo.edu/pub/bar.xml', 'HTMLtable' );
572 Using Strings and Arrays
574 Strings and arrays may be used as either the source of data input or as
575 the target of data output. Strings should be passed as the only
576 element of an array reference (in other words, inside square brackets).
577 Arrays should be a reference to an array whose first element is a
578 reference to an array of column names and whose succeeding elements are
579 references to arrays of row values.
580 For example:
582 my $table = adTie( 'XML', ["<x><motto id='perl'>TIMTOWTDI</motto></x>"] );
584 This uses the XML format to parse the supplied string and returns a tied
586 hash to the resulting table.
587 my $table = adTie( 'ARRAY', [['id','motto'],['perl','TIMTOWTDI']] );
590 This uses the column names "id" and "motto" and the supplied row values
592 and returns a tied hash to the resulting table.
593 It is also possible to use an empty array to create a new empty tied
595 hash in any format, for example:
596 my $table = adTie('XML',[],'c');
598 creates a new empty tied hash;
600 See adConvert() and adExport() for further examples of using strings
602 and arrays.
603 Ties, Flocks, I/O, and Atomicity
605 AnyData provides flocking which works under the limitations of flock --
606 that it only works if other processes accessing the files are also
607 using flock and only on platforms that support flock. See the flock()
608 man page for details.
609 Here is what the user supplied open modes actually do:
611 r = read only (LOCK_SH) O_RDONLY
613 u = update (LOCK_EX) O_RDWR
614 c = create (LOCK_EX) O_CREAT | O_RDWR | O_EXCL
615 o = overwrite (LOCK_EX) O_CREAT | O_RDWR | O_TRUNC
616 When you use something like "my $table = adTie(...)", it opens the file
618 with a lock and leaves the file and lock open until 1) the hash
619 variable ($table) goes out of scope or 2) the hash is undefined (e.g.
620 "undef $table") or 3) the hash is re-assigned to another tie. In all
621 cases the file is closed and the lock released.
622 If adTie is called without creating a tied hash variable, the file is
624 closed and the lock released immediately after the call to adTie.
625 For example: print adTie('XML','foo.xml')->{main_office}->{phone}.
627 That obtains a shared lock, opens the file, retrieves the one value
629 requested, closes the file and releases the lock.
630 These two examples accomplish the same thing but the first example
632 opens the file once, does all of the deletions, keeping the exclusive
633 lock in place until they are all done, then closes the file. The
634 second example opens and closes the file three times, once for each
635 deletion and releases the exclusive lock between each deletion:
636 1. my $t = adTie('Pipe','games.db','u');
638 delete $t->{"user$_"} for (0..3);
639 undef $t; # closes file and releases lock
640 2. delete adTie('Pipe','games.db','u')->{"user$_"} for (0..3);
642 # no undef needed since no hash variable created
643 Deletions and Packing
645 In order to save time and to prevent having to do writes anywhere
646 except at the end of the file, deletions and updates are *not* done at
647 the time of issuing a delete command. Rather when the user does a
648 delete, the position of the deleted record is stored in a hash and when
649 the file is saved to disk, the deletions are only then physically
650 removed by packing the entire database. Updates are done by inserting
651 the new record at the end of the file and marking the old record for
652 deletion. In the normal course of events, all of this should be
653 transparent and you'll never need to worry about it. However, if your
654 server goes down after you've made updates or deletions but before
655 you've saved the file, then the deleted rows will remain in the
656 database and for updates there will be duplicate rows -- the old non
657 updated row and the new updated row. If you are worried about this
658 kind of event, then use atomic deletes and updates as shown in the
659 section above. There's still a very small possibility of a crash in
660 between the deletion and the save, but in this case it should impact at
661 most a single row. (BIG thanks to Matthew Wickline for suggestions on
662 handling deletes)
663
665 See the README file and the test.pl included with the module for
666 further examples.
667 See the AnyData/Format/*.pm PODs for further details of specific
669 formats.
670 For further support, please use comp.lang.perl.modules
672
674 Special thanks to Andy Duncan, Tom Lowery, Randal Schwartz, Michel
675 Rodriguez, Jochen Wiedmann, Tim Bunce, Alligator Descartes, Mathew
676 Persico, Chris Nandor, Malcom Cook and to many others on the DBI
677 mailing lists and the clp* newsgroups.
678
680 Jeff Zucker <jeff@vpservices.com>
681 This module is copyright (c), 2000 by Jeff Zucker.
683 Some changes (c) 2012 Sven Dowideit L<mailto:SvenDowideit@fosiki.com>
684 It may be freely distributed under the same terms as Perl itself.
685perl v5.32.0 2020-07-28 AnyData(3)