Debian Codegraph @

README

raw
1 NAME
2 DBIx::DBStag - Relational Database to Hierarchical (Stag/XML) Mapping
3
4 SYNOPSIS
5 use DBIx::DBStag;
6 my $dbh = DBIx::DBStag->connect("dbi:Pg:dbname=moviedb");
7 my $sql = q[
8 SELECT
9 studio.*,
10 movie.*,
11 star.*
12 FROM
13 studio NATURAL JOIN
14 movie NATURAL JOIN
15 movie_to_star NATURAL JOIN
16 star
17 WHERE
18 movie.genre = 'sci-fi' AND star.lastname = 'Fisher'
19 USE NESTING
20 (set(studio(movie(star))))
21 ];
22 my $dataset = $dbh->selectall_stag($sql);
23 my @studios = $dataset->get_studio;
24
25 # returns nested data that looks like this -
26 #
27 # (studio
28 # (name "20th C Fox")
29 # (movie
30 # (name "star wars") (genre "sci-fi")
31 # (star
32 # (firstname "Carrie")(lastname "Fisher")))))
33
34 # iterate through result tree -
35 foreach my $studio (@studios) {
36 printf "STUDIO: %s\n", $studio->get_name;
37 my @movies = $studio->get_movie;
38
39 foreach my $movie (@movies) {
40 printf " MOVIE: %s (genre:%s)\n",
41 $movie->get_name, $movie->get_genre;
42 my @stars = $movie->get_star;
43
44 foreach my $star (@stars) {
45 printf " STARRING: %s:%s\n",
46 $star->get_firstname, $star->get_lastname;
47 }
48 }
49 }
50
51 # manipulate data then store it back in the database
52 my @allstars = $dataset->get("movie/studio/star");
53 $_->set_fullname($_->get_firstname.' '.$_->get_lastname)
54 foreach(@allstars);
55
56 $dbh->storenode($dataset);
57
58 Or from the command line:
59
60 unix> selectall_xml -d 'dbi:Pg:dbname=spybase' 'SELECT * FROM studio NATURAL JOIN movie'
61
62 DESCRIPTION
63 This module is for mapping from databases to Stag objects (Structured
64 Tags - see the Data::Stag manpage), which can also be represented as
65 XML. It has two main uses:
66
67 Querying
68 This module can take the results of any SQL query and decompose the
69 flattened results into a tree data structure which reflects the
70 foreign keys in the underlying relational schema. It does this by
71 looking at the SQL query and introspecting the database schema,
72 rather than requiring metadata or an object model.
73
74 In this respect, the module works just like a regular the DBI
75 manpage handle, with some extra methods provided.
76
77 Storing Data
78 DBStag objects can store any tree-like datastructure (such as XML
79 documents) into a database using normalized schema that reflects the
80 structure of the tree being stored. This is done using little or no
81 metadata.
82
83 XML can also be imported, and a relational schema automatically
84 generated.
85
86 For a tutorial on using DBStag to build and query relational databases
87 from XML sources, please see the DBIx::DBStag::Cookbook manpage
88
89 HOW QUERYING WORKS
90
91 This is a general overview of the rules for turning SQL query results
92 into a tree like data structure.
93
94 Relations
95
96 Relations (i.e. tables and views) are elements (nodes) in the tree. The
97 elements have the same name as the relation in the database.
98
99 Columns
100
101 Table and view columns of a relation are sub-elements of the table or
102 view to which they belong. These elements will be data elements (i.e.
103 terminal nodes). Only the columns selected in the SQL query will be
104 present.
105
106 For example, the following query
107
108 SELECT name, job FROM person;
109
110 will return a data structure that looks like this:
111
112 (person
113 (name "fred")
114 (job "forklift driver"))
115 (person
116 (name "joe")
117 (job "steamroller mechanic"))
118
119 The data is shown as a lisp-style S-Expression - it can also be
120 expressed as XML, or manipulated as an object within perl.
121
122 Table aliases
123
124 If an ALIAS is used in the FROM part of the SQL query, the relation
125 element will be nested inside an element with the same name as the
126 alias. For instance, the query
127
128 SELECT name FROM person AS author WHERE job = 'author';
129
130 Will return a data structure like this:
131
132 (author
133 (person
134 (name "Philip K Dick")))
135
136 The underlying assumption is that aliasing is used for a purpose in the
137 original query; for instance, to determine the context of the relation
138 where it may be ambiguous.
139
140 SELECT *
141 FROM person AS employee
142 INNER JOIN
143 person AS boss ON (employee.boss_id = boss.person_id)
144
145 Will generate a nested result structure similar to this -
146
147 (employee
148 (person
149 (person_id "...")
150 (name "...")
151 (foo "...")
152 (boss
153 (person
154 (person_id "...")
155 (name "...")
156 (foo "...")))))
157
158 If we neglected the alias, we would have 'person' directly nested under
159 'person', and the meaning would not be obvious. Note how the contents of
160 the SQL query dynamically modifies the schema/structure of the result
161 tree.
162
163 NOTE ON SQL SYNTAX
164
165 Right now, DBStag is fussy about how you specify aliases; you must use
166 AS - you must say
167
168 SELECT name FROM person AS author;
169
170 instead of
171
172 SELECT name FROM person author;
173
174 Nesting of relations
175
176 The main utility of querying using this module is in retrieving the
177 nested relation elements from the flattened query results. Given a query
178 over relations A, B, C, D,... there are a number of possible tree
179 structures. Not all of the tree structures are meaningful.
180
181 Usually it will make no sense to nest A under B if there is no foreign
182 key relationship linking either A to B, or B to A. This is not always
183 the case - it may be desirable to nest A under B if there is an
184 intermediate linking table that is required at the relational level but
185 not required in the tree structure.
186
187 DBStag will guess a structure/schema based on the ordering of the
188 relations in your FROM clause. However, this guess can be over-ridden at
189 either the SQL level (using DBStag specific SQL extensions) or at the
190 API level.
191
192 The default algorithm is to nest each relation element under the
193 relation element preceeding it in the FROM clause; for instance:
194
195 SELECT * FROM a NATURAL JOIN b NATURAL JOIN c
196
197 If there are appropriately named foreign keys, the following data will
198 be returned (assuming one row in each of a, b and c)
199
200 (set
201 (a
202 (a_foo "...")
203 (b
204 (b_foo "...")
205 (c
206 (c_foo "...")))))
207
208 where 'x_foo' is a column in relation 'x'
209
210 This is not always desirable. If both b and c have foreign keys into
211 table a, DBStag will not detect this - you have to guide it. There are
212 two ways of doing this - you can guide by bracketing your FROM clause
213 like this:
214
215 !!##
216 !!## NOTE - THIS PART IS NOT SET IN STONE - THIS MAY CHANGE
217 !!##
218 SELECT * FROM (a NATURAL JOIN b) NATURAL JOIN c
219
220 This will generate
221
222 (set
223 (a
224 (a_foo "...")
225 (b
226 (b_foo "..."))
227 (c
228 (c_foo "..."))))
229
230 Now b and c are siblings in the tree. The algorithm is similar to
231 before: nest each relation element under the relation element preceeding
232 it; or, if the preceeding item in the FROM clause is a bracketed
233 structure, nest it under the first relational element in the bracketed
234 structure.
235
236 (Note that in MySQL you may not place brackets in the FROM clause in
237 this way)
238
239 Another way to achieve the same thing is to specify the desired tree
240 structure using a DBStag specific SQL extension. The DBStag specific
241 component is removed from the SQL before being presented to the DBMS.
242 The extension is the USE NESTING clause, which should come at the end of
243 the SQL query (and is subsequently removed before processing by the
244 DBMS).
245
246 SELECT *
247 FROM a NATURAL JOIN b NATURAL JOIN c
248 USE NESTING (set (a (b)(c)));
249
250 This will generate the same tree as above (i.e. 'b' and 'c' are
251 siblings). Notice how the nesting in the clause is the same as the
252 nesting in the resulting tree structure.
253
254 Note that 'set' is not a table in the underlying relational schema - the
255 result data tree requires a named top level node to group all the 'a'
256 relations under. You can call this top level element whatever you like.
257
258 If you are using the DBStag API directly, you can pass in the nesting
259 structure as an argument to the select call; for instance:
260
261 my $seq =
262 $dbh->selectall_xml(-sql=>q[SELECT *
263 FROM a NATURAL JOIN b
264 NATURAL JOIN c],
265 -nesting=>'(set (a (b)(c)))');
266
267 or the equivalent -
268
269 my $seq =
270 $dbh->selectall_xml(q[SELECT *
271 FROM a NATURAL JOIN b
272 NATURAL JOIN c],
273 '(set (a (b)(c)))');
274
275 If you like, you can also use XML here (only at the API level, not at
276 the SQL level) -
277
278 my $seq =
279 $dbh->selectall_xml(-sql=>q[SELECT *
280 FROM a NATURAL JOIN b
281 NATURAL JOIN c],
282 -nesting=>q[
283 <set>
284 <a>
285 <b></b>
286 <c></c>
287 </a>
288 </set>
289 ]);
290
291 As you can see, this is a little more verbose.
292
293 Most command line scripts that use this module should allow pass-through
294 via the '-nesting' switch.
295
296 Aliasing of functions and expressions
297
298 If you alias a function or an expression, DBStag needs to know where to
299 put the resulting column; the column must be aliased.
300
301 This is inferred from the first named column in the function or
302 expression; for example, in the SQL below
303
304 SELECT blah.*, foo.*, foo.x - foo.y AS z
305
306 The z element will be nested under the foo element
307
308 You can force different nesting using a double underscore:
309
310 SELECT blah.*, foo.*, foo.x - foo.y AS blah__z
311
312 This will nest the z element under the blah element
313
314 Conformance to DTD/XML-Schema
315
316 DBStag returns the Data::Stag manpage structures that are equivalent to
317 a simplified subset of XML (and also a simplified subset of lisp
318 S-Expressions).
319
320 These structures are examples of semi-structured data - a good reference
321 is this book -
322
323 Data on the Web: From Relations to Semistructured Data and XML
324 Serge Abiteboul, Dan Suciu, Peter Buneman
325 Morgan Kaufmann; 1st edition (January 2000)
326
327 The schema for the resulting Stag structures can be seen to conform to a
328 schema that is dynamically determined at query-time from the underlying
329 relational schema and from the specification of the query itself.
330
331 CLASS METHODS
332 connect
333
334 Usage - $dbh = DBIx::DBStag->connect($DSN);
335 Returns - L<DBIx::DBStag>
336 Args - see the connect() method in L<DBI>
337
338 selectall_stag
339
340 Usage - $stag = $dbh->selectall_stag($sql);
341 $stag = $dbh->selectall_stag($sql, $nesting_clause);
342 $stag = $dbh->selectall_stag(-template=>$template,
343 -bind=>{%variable_bindinfs});
344 Returns - L<Data::Stag>
345 Args - sql string,
346 [nesting string],
347 [bind hashref],
348 [template DBIx::DBStag::SQLTemplate]
349
350 Executes a query and returns a the Data::Stag manpage structure
351
352 An optional nesting expression can be passed in to control how the
353 relation is decomposed into a tree. The nesting expression can be XML or
354 an S-Expression; see above for details
355
356 selectall_xml
357
358 Usage - $xml = $dbh->selectall_xml($sql);
359 Returns - string
360 Args - See selectall_stag()
361
362 As selectall_stag(), but the results are transformed into an XML string
363
364 selectall_sxpr
365
366 Usage - $sxpr = $dbh->selectall_sxpr($sql);
367 Returns - string
368 Args - See selectall_stag()
369
370 As selectall_stag(), but the results are transformed into an
371 S-Expression string; see the Data::Stag manpage for more details.
372
373 selectall_sax
374
375 Usage - $dbh->selectall_sax(-sql=>$sql, -handler=>$sax_handler);
376 Returns - string
377 Args - sql string, [nesting string], handler SAX
378
379 As selectall_stag(), but the results are transformed into SAX events
380
381 [currently this is just a wrapper to selectall_xml but a genuine event
382 generation model will later be used]
383
384 selectall_rows
385
386 Usage - $tbl = $dbh->selectall_rows($sql);
387 Returns - arrayref of arrayref
388 Args - See selectall_stag()
389
390 As selectall_stag(), but the results of the SQL query are left
391 undecomposed and unnested. The resulting structure is just a flat table;
392 the first row is the column headings. This is similar to
393 DBI->selectall_arrayref(). The main reason to use this over the direct
394 DBI method is to take advantage of other stag functionality, such as
395 templates
396
397 prepare_stag SEMI-PRIVATE METHOD
398
399 Usage - $prepare_h = $dbh->prepare_stag(-template=>$template);
400 Returns - hashref (see below)
401 Args - See selectall_stag()
402
403 Returns a hashref
404
405 {
406 sth=>$sth,
407 exec_args=>\@exec_args,
408 cols=>\@cols,
409 col_aliases_ordered=>\@col_aliases_ordered,
410 alias=>$aliasstruct,
411 nesting=>$nesting
412 };
413
414 storenode
415
416 Usage - $dbh->storenode($stag);
417 Returns -
418 Args - L<Data::Stag>
419
420 Recursively stores a tree structure in the database
421
422 SQL TEMPLATES
423 DBStag comes with its own SQL templating system. This allows you to
424 reuse the same canned SQL or similar SQL qeuries in different contexts.
425 See the DBIx::DBStag::SQLTemplate manpage
426
427 find_template
428
429 Usage - $template = $dbh->find_template("my-template-name");
430 Returns - L<DBIx::DBStag::SQLTemplate>
431 Args - str
432
433 Returns an object representing a canned paramterized SQL query. See the
434 DBIx::DBStag::SQLTemplate manpage for documentation on templates
435
436 list_templates
437
438 Usage - $templates = $dbh->list_templates();
439 Returns - Arrayref of L<DBIx::DBStag::SQLTemplate>
440 Args -
441
442 Returns a list of ALL defined templates - See the
443 DBIx::DBStag::SQLTemplate manpage
444
445 find_templates_by_schema
446
447 Usage - $templates = $dbh->find_templates_by_schema($schema_name);
448 Returns - Arrayref of L<DBIx::DBStag::SQLTemplate>
449 Args - str
450
451 Returns a list of templates for a particular schema - See the
452 DBIx::DBStag::SQLTemplate manpage
453
454 find_templates_by_dbname
455
456 Usage - $templates = $dbh->find_templates_by_dbname("mydb");
457 Returns - Arrayref of L<DBIx::DBStag::SQLTemplate>
458 Args - db name
459
460 Returns a list of templates for a particular db
461
462 Requires resources to be set up (see below)
463
464 RESOURCES
465 resources_list
466
467 Usage - $rlist = $dbh->resources_list
468 Returns - arrayref to a hashref
469 Args - none
470
471 Returns a list of resources; each resource is a hash
472
473 {name=>"mydbname",
474 type=>"rdb",
475 schema=>"myschema",
476 }
477
478 SETTING UP RESOURCES
479 The above methods rely on you having a file describing all the
480 relational dbs available to you, and setting the env var
481 DBSTAG_DBIMAP_FILE set (this is a : separated list of paths).
482
483 This is alpha code - not fully documented, API may change
484
485 Currently a resources file is a whitespace delimited text file -
486 XML/Sxpr/IText definitions may be available later
487
488 Here is an example of a resources file:
489
490 # LOCAL
491 mytestdb rdb Pg:mytestdb schema=test
492
493 # SYSTEM
494 worldfactbook rdb Pg:[email protected] schema=wfb
495 employees rdb Pg:[email protected] schema=employees
496
497 The first column is the nickname or logical name of the resource/db.
498 This nickname can be used instead of the full DBI locator path (eg you
499 can just use employees instead of
500 dbi:Pg:dbname=employees;host=db2.mycompany.com
501
502 The second column is the resource type - rdb is for relational database.
503 You can use the same file to track other system datasources available to
504 you, but DBStag is only interested in relational dbs.
505
506 The 3rd column is a way of locating the resource - driver:name@host
507
508 The 4th column is a ; separated list of tag=value pairs; the most
509 important tag is the schema tag. Multiple dbs may share the same schema,
510 and hence share SQL Templates
511
512 COMMAND LINE SCRIPTS
513 DBStag is usable without writing any perl, you can use command line
514 scripts and files that utilise tree structures (XML, S-Expressions)
515
516 selectall_xml.pl
517 selectall_xml.pl -d <DSN> [-n <nestexpr>] <SQL>
518
519 Queries database and writes decomposed relation as XML
520
521 Can also be used with templates:
522
523 selectall_xml.pl -d <DSN> /<templatename> <var1> <var2> ... <varN>
524
525 selectall_html.pl
526 selectall_html.pl -d <DSN> [-n <nestexpr>] <SQL>
527
528 Queries database and writes decomposed relation as HTML with nested
529 tables indicating the nested structures.
530
531 stag-storenode.pl
532 stag-storenode.pl -d <DSN> <file>
533
534 Stores data from a file (Supported formats: XML, Sxpr, IText - see
535 the Data::Stag manpage) in a normalized database. Gets it right most
536 of the time.
537
538 TODO - metadata help
539
540 stag-autoddl.pl
541 stag-autoddl.pl [-l <linktable>]* <file>
542
543 Takes data from a file (Supported formats: XML, Sxpr, IText - see
544 the Data::Stag manpage) and generates a relational schema in the
545 form of SQL CREATE TABLE statements.
546
547 ENVIRONMENT VARIABLES
548 DBSTAG_TRACE
549 setting this environment will cause all SQL statements to be printed
550 on STDERR
551
552 BUGS
553 This is alpha software! Probably several bugs.
554
555 The SQL parsing can be quite particular - sometimes the SQL can be
556 parsed by the DBMS but not by DBStag. The error messages are not always
557 helpful.
558
559 There are probably a few cases the SQL SELECT parsing grammar cannot
560 deal with.
561
562 If you want to select from views, you need to hack DBIx::DBSchema (as of
563 v0.21)
564
565 TODO
566 Use SQL::Translator to make SQL DDL generation less Pg-specific; also
567 for deducing foreign keys (right now foreign keys are guessed by the
568 name of the column, eg table_id)
569
570 Can we cache the grammar so that startup is not so slow?
571
572 Improve algorithm so that events are fired rather than building up
573 entire structure in-memory
574
575 Tie in all DBI attributes accessible by hash, i.e.: $dbh->{...}
576
577 Error handling
578
579 WEBSITE
580 http://stag.sourceforge.net
581
582 AUTHOR
583 Chris Mungall <[email protected]>
584
585 COPYRIGHT
586 Copyright (c) 2004 Chris Mungall
587
588 This module is free software. You may distribute this module under the
589 same terms as perl itself
590