DBIx::DBStag(3pm)

🌈 DBIx::DBStag 💅

okay bestie, sit DOWN, because this is the Perl module that turns your boring old relational database into a cute little nested tree and honestly? it's giving genius. this is the manpage for DBIx::DBStag, living its best life in section 3pm of the User Contributed Perl Documentation. ✨

---

🎀 NAME 🎀

DBIx::DBStag — Relational Database to Hierarchical (Stag/XML) Mapping

like, it takes your SQL relational rows and maps them into a hierarchy. stag. xml. the whole vibe. 🦋

---

✨ SYNOPSIS ✨

okay so here's the whole thing in action, no cap:

use DBIx::DBStag;
my $dbh = DBIx::DBStag->connect("dbi:Pg:dbname=moviedb");
my $sql = q[
            SELECT
             studio.*,
             movie.*,
             star.*
            FROM
             studio NATURAL JOIN
             movie NATURAL JOIN
             movie_to_star NATURAL JOIN
             star
            WHERE
             movie.genre = 'sci-fi' AND star.lastname = 'Fisher'
             USE NESTING
             (set(studio(movie(star))))
           ];
my $dataset = $dbh->selectall_stag($sql);
my @studios = $dataset->get_studio;

# returns nested data that looks like this -
#
# (studio
#  (name "20th C Fox")
#  (movie
#   (name "star wars") (genre "sci-fi")
#   (star
#    (firstname "Carrie")(lastname "Fisher")))))

# iterate through result tree -
foreach my $studio (@studios) {
      printf "STUDIO: %s\n", $studio->get_name;
      my @movies = $studio->get_movie;

      foreach my $movie (@movies) {
          printf "  MOVIE: %s (genre:%s)\n",
            $movie->get_name, $movie->get_genre;
          my @stars = $movie->get_star;

          foreach my $star (@stars) {
              printf "    STARRING: %s:%s\n",
                $star->get_firstname, $star->get_lastname;
          }
      }
}

# manipulate data then store it back in the database
my @allstars = $dataset->get("movie/studio/star");
$_->set_fullname($_->get_firstname.' '.$_->get_lastname)
  foreach(@allstars);

$dbh->storenode($dataset);
exit 0;

Or from the command line:

unix> selectall_xml.pl -d 'dbi:Pg:dbname=moviebase'     \
     'SELECT * FROM studio NATURAL JOIN movie NATURAL   \
        JOIN movie_to_star NATURAL JOIN star            \
        USE NESTING (set(studio(movie(star))))'

Or using a predefined template:

unix> selectall_xml.pl -d moviebase /mdb-movie genre=sci-fi

---

so yeah that's the teaser trailer 💖 the reunion with the rest of the page is coming up next, don't go anywhere.

💖 DESCRIPTION 💖

Okay bestie, gather round, because this module is literally the moment. It's all about mapping between relational databases and Stag objects (Structured Tags — peep Data::Stag if you're curious). Stag objects can also be flexing as XML, because of course they can. The module has two main uses, and both of them ate:

• Querying — This module can take the results of any SQL query and decompose those flat, squished results into a whole tree data structure that reflects the foreign keys in the underlying relational schema. How?? By looking at the SQL query itself and introspecting the database schema, bestie — no metadata required, no object model, none of that. Honestly iconic behavior.

In this respect, the module works just like a regular DBI handle, but with a few extra methods (because it's that girl).

Queries can also make use of predefined templates ✨

• Storing Data — DBStag objects can store any tree-like data structure (think XML documents) into a database using a normalized schema that reflects the structure of the tree being stored. And it does this with little or no metadata. Lowkey sorcery, but make it database.

XML can also be imported, and a relational schema automatically generated. Slay.

For a tutorial on using DBStag to build and query relational databases from XML sources, please see DBIx::DBStag::Cookbook 📖

🌈 HOW QUERY RESULTS ARE TURNED INTO STAG/XML 🌈

This is the general overview of the rules for turning SQL query results into a tree-like data structure. Now listen — you do not need to understand all these rules to use this module. You can just experiment by using the selectall_xml.pl script that comes with this distribution. Vibes first, theory later 💅

Mapping Relations

Relations (aka tables and views) are elements (nodes) in the tree. These elements have the same name as the relation in the database. Easy.

These nodes are always non-terminal (i.e. they always have child nodes). They're giving parent energy, fr.

Mapping Columns

Table and view columns of a relation are sub-elements of the table or view they belong to. These elements will be data elements (i.e. terminal nodes — the leaves, the end of the line, the final girls). Only the columns selected in the SQL query will be present, so don't expect the whole menu.

For example, this query:

SELECT name, job FROM person;

will return a data structure that looks like this:

(set
 (person
  (name "fred")
  (job "forklift driver"))
 (person
  (name "joe")
  (job "steamroller mechanic")))

The data is shown as a lisp-style S-Expression — it can also be expressed as XML, or manipulated as an object within perl. Choose your own adventure 🦋

Handling table aliases

If an ALIAS is used in the FROM part of the SQL query, the relation element gets nested inside an element with the same name as the alias. For instance, the query:

SELECT name FROM person AS author WHERE job = 'author';

will return a data structure like this:

(set
 (author
  (person
   (name "Philip K Dick"))))

The underlying assumption is that aliasing is used for a purpose in the original query — for instance, to determine the context of the relation where it may be ambiguous.

SELECT *
FROM person AS employee
         INNER JOIN
     person AS boss ON (employee.boss_id = boss.person_id)

Will generate a nested result structure similar to this:

(set
 (employee
  (person
   (person_id "...")
   (name "...")
   (salary  "...")
   (boss
    (person
     (person_id "...")
     (name "...")
     (salary  "..."))))))

If we neglected the alias, we'd have 'person' directly nested under 'person', and the meaning would not be obvious. Note how the contents of the SQL query dynamically modifies the schema/structure of the result tree. No cap, the query is the blueprint.

NOTE ON SQL SYNTAX

Right now, DBStag is fussy about how you specify aliases — you must use AS. You must say:

SELECT name FROM person AS author;

instead of:

SELECT name FROM person author;

Periodt. No shortcuts, bestie.

Nesting of relations

The main utility of querying using this module is in retrieving the nested relation elements from the flattened query results. Given a query over relations A, B, C, D, ..., there are a number of possible tree structures. Not all of the tree structures are meaningful or useful (some are giving chaos energy, frankly 🙈).

Usually it will make no sense to nest A under B if there's no foreign key relationship linking either A to B, or B to A. This is not always the case, though — it may be desirable to nest A under B if there's an intermediate linking table that's required at the relational level but not required in the tree structure.

DBStag will guess a structure/schema based on the ordering of the relations in your FROM clause. However, this guess can be over-ridden at either the SQL level (using DBStag specific SQL extensions) or at the API level.

The default algorithm is to nest each relation element under the relation element preceding it in the FROM clause; for instance:

SELECT * FROM a NATURAL JOIN b NATURAL JOIN c

If there are appropriately named foreign keys, the following data will be returned (assuming one column x_foo in each of a, b and c):

(set
 (a
  (a_foo "...")
  (b
   (b_foo "...")
   (c
    (c_foo "...")))))

where x_foo is a column in relation x.

This is not always desirable. If both b and c have foreign keys into table a, DBStag will not detect this — you have to guide it, like a GPS that needs you to type in the address. There are two ways of doing this. You can guide by bracketing your FROM clause like this:

SELECT * FROM (a NATURAL JOIN b) NATURAL JOIN c

This will generate:

(set
 (a
  (a_foo "...")
  (b
   (b_foo "..."))
  (c
   (c_foo "..."))))

Now b and c are siblings in the tree 🫶 The algorithm is similar to before: nest each relation element under the relation element preceding it; or, if the preceding item in the FROM clause is a bracketed structure, nest it under the first relational element in the bracketed structure.

(Note that in MySQL you may not place brackets in the FROM clause in this way — rip, MySQL girlies.)

Another way to achieve the same thing is to specify the desired tree structure using a DBStag specific SQL extension. The DBStag specific component is removed from the SQL before being presented to the DBMS. The extension is the USE NESTING clause, which should come at the end of the SQL query (and is subsequently removed before processing by the DBMS).

SELECT *
FROM a NATURAL JOIN b NATURAL JOIN c
USE NESTING (set (a (b)(c)));

This will generate the same tree as above (i.e. 'b' and 'c' are siblings). Notice how the nesting in the clause is the same as the nesting in the resulting tree structure. Symmetry, we love to see it ✨

Note that 'set' is not a table in the underlying relational schema — the result data tree requires a named top level node to group all the 'a' relations under. You can call this top level element whatever you like. It's your main character, name it accordingly 👑

If you are using the DBStag API directly, you can pass in the nesting structure as an argument to the select call; for instance:

my $xmlstr =
  $dbh->selectall_xml(-sql=>q[SELECT *
                              FROM a NATURAL JOIN b
                                   NATURAL JOIN c],
                      -nesting=>'(set (a (b)(c)))');

or the equivalent:

my $xmlstr =
  $dbh->selectall_xml(q[SELECT *
                        FROM a NATURAL JOIN b
                             NATURAL JOIN c],
                      '(set (a (b)(c)))');

If you like, you can also use XML here (only at the API level, not at the SQL level):

my $seq =
  $dbh->selectall_xml(-sql=>q[SELECT *
                              FROM a NATURAL JOIN b
                                   NATURAL JOIN c],
                      -nesting=>q[
                                  <set>
                                    <a>
                                      <b></b>
                                      <c></c>
                                    </a>
                                  </set>
                                 ]);

As you can see, this is a little more verbose than the S-Expression. It's giving "I have time today" energy 💅

Most command line scripts that use this module should allow pass-through via the -nesting switch.

Aliasing of functions and expressions

If you alias a function or an expression, DBStag needs to know where to put the resulting column — the column must be aliased. No alias, no vibes, no deal.

This is inferred from the first named column in the function or expression; for example, the SQL below uses the minus function:

SELECT blah.*, foo.*, foo.x-foo.y AS z

The z element will be nested under the foo element.

You can force different nesting using a double underscore:

SELECT blah.*, foo.*, foo.x - foo.y AS blah__z

This will nest the z element under the blah element. Look at you, taking control of your own destiny 🌸

If you would like to override this behaviour and use the alias as the element name, pass in the -aliaspolicy=>'a' arg to the API call. If you wish to use the table names without nesting, use -aliaspolicy=>'t'.

🎀 Conformance to DTD/XML-Schema 🎀

DBStag returns Data::Stag structures that are equivalent to a simplified subset of XML (and also a simplified subset of lisp S-Expressions). Multipurpose icon.

These structures are examples of semi-structured data — a good reference is this book:

Data on the Web: From Relations to Semistructured Data and XML
Serge Abiteboul, Dan Suciu, Peter Buneman
Morgan Kaufmann; 1st edition (January 2000)

The schema for the resulting Stag structures can be seen to conform to a schema that is dynamically determined at query-time from the underlying relational schema and from the specification of the query itself. It's giving bespoke, honestly.

If you need to generate a DTD you can ause the stag-autoschema.pl script, which is part of the Data::Stag distribution 📚

🦋 QUERY METHODS 🦋

okay bestie, this whole little cluster of methods is for using the DBStag API to ask the database stuff 🔍 like, query energy only, storage comes later

---

✨ connect ✨

Usage   - $dbh = DBIx::DBStag->connect($DSN);
Returns - DBIx::DBStag
Args    - see the connect() method in DBI

This is literally the first method you're gonna call to spin up a DBStag object. Your girl is the entry point 💅

The DSN can be a standard DBI DSN, or it can be a DBStag alias. (Yes, aliases, we love a shortcut 🎀)

---

🌸 selectall_stag 🌸

Usage   - $stag = $dbh->selectall_stag($sql);
          $stag = $dbh->selectall_stag($sql, $nesting_clause);
          $stag = $dbh->selectall_stag(-template=>$template,
                                       -bind=>{%variable_bindinfs});
Returns - Data::Stag
Args    - sql string,
          [nesting string],
          [bind hashref],
          [template DBIx::DBStag::SQLTemplate]

Runs a query and hands you back a Data::Stag structure. So cute, so structured ✨

You can pass in an optional nesting expression to control how the relation gets decomposed into a tree — that expression can be XML or an S-Expression, and the deets are up above in the earlier parts 👆 no cap.

---

🎀 selectall_xml 🎀

Usage   - $xml = $dbh->selectall_xml($sql);
Returns - string
Args    - See selectall_stag()

Same as selectall_stag(), except the results get transformed into an XML string. Glow-up but make it text 🦋

---

⭐ selectall_sxpr ⭐

Usage   - $sxpr = $dbh->selectall_sxpr($sql);
Returns - string
Args    - See selectall_stag()

Again, same as selectall_stag(), but this time the results become an S-Expression string. Run to Data::Stag for the extra lore, iykyk 📚

---

🔥 selectall_sax 🔥

Usage   - $dbh->selectall_sax(-sql=>$sql, -handler=>$sax_handler);
Returns - string
Args    - sql string, [nesting string], handler SAX

selectall_stag() again, but now the results get transformed into SAX events.

heads up bestie: right now this is just a wrapper to selectall_xml — a genuine event generation model will later be used. so like, it's giving placeholder, not gonna lie 🙈

---

🫶 selectall_rows 🫶

Usage   - $tbl = $dbh->selectall_rows($sql);
Returns - arrayref of arrayref
Args    - See selectall_stag()

Just like selectall_stag(), except the results of the SQL query are left undecomposed and unnested. What you get is a flat table, and the first row is the column headings. Basically DBI->selectall_arrayref() core 💀

But why use this instead of just calling DBI directly? The reason is so you can take advantage of all the other stag functionality — templates, bestie, templates — periodt.

---

💜 prepare_stag PRIVATE METHOD 💜

Usage   - $prepare_h = $dbh->prepare_stag(-template=>$template);
Returns - hashref (see below)
Args    - See selectall_stag()

Returns a hashref, and here is exactly what's in it, no secrets:

{
 sth=>$sth,
 exec_args=>\@exec_args,
 cols=>\@cols,
 col_aliases_ordered=>\@col_aliases_ordered,
 alias=>$aliasstruct,
 nesting=>$nesting
};

---

👑 STORAGE METHODS 👑

and now for the other half of the story — these methods use the DBStag API to store nested data in a database. putting stuff IN. the reverse of the vibe above 🌈

---

🌸 storenode 🌸

Usage   - $dbh->storenode($stag);
Returns -
Args    - Data::Stag

SEE ALSO: the stag-storenode.pl script ✨

This recursively stores a stag tree structure in the database. Recursively!! it goes all the way down, bestie 🫶

The database schema gets introspected for most of the mapping data, but you can supply your own too (more on that later).

The Stag tree / XML has to be a direct mapping of the relational schema — column and table names must correspond to element names. Elements may be nested. You've got different styles of XML-Relational mapping to choose from: the XORT-style and the more compact Stag-style 👑

🦋 XORT-style mapping 🦋

With a XORT-style mapping, elements corresponding to tables can be nested under elements corresponding to foreign keys.

So like, if the relational schema has a foreign key from table person to table address, then this XML is permissable:

<person>
  <name>..</name>
  <address_id>
    <address>
    </address>
  </address_id>
</person>

The address node will be stored in the database and collapsed to whatever the value of the primary key is. cute little collapse 💅

🎀 Stag-style mapping 🎀

Stag-style is more compact, but sometimes it relies on the presence of a dbstag_metadata element to specify how foreign keys are mapped. so like, sometimes it needs a lil helper node, no big deal 🌸

✨ Operations ✨

Operations are specified as attributes inside elements, and they say whether the nod should be inserted, updated, looked up or stored/forced. Operations are optional — the default is force/store.

<person op="insert">
 <name>fred</name>
 <address_id op="lookup">
  <streetaddr>..</>
  <city>..</>
 </address_id>
</person>

The above will always insert into the person table. And honestly that may be quite dangerous — if an entry with the same unique constraint exists, an error will be thrown 💀. Assuming (streetaddr,city) is a unique constraint for the address table, this will look up the specified address (and not modify the table) and use the returned pk value for the person.address_id foreign key. slay, that's the whole point 🫶

The operations are:

• force (default) — looks up (by unique constraints) first; if it exists, will do an update. if it does not exist, will do an insert
• insert — insert only. DBMS will throw error if row with same UC exists
• update — update only. DBMS will throw error if a row the with the specified UC cannot be found
• lookup — finds the pk value using one of the unique constraints present in the XML node
• delete NOT IMPLEMENTED — deletes row that has matching UC

Operations can be used in either XORT or Stag mode 🦋

🌈 Macros 🌈

Macros can be used with either XORT or Stag style mappings. Macros let you refer to the same node later on in the XML. obsessed with a reusable node, honestly 👑

<person op="lookup" id="joe">
  <name>joe</name>
</person>
<person op="lookup" id="fred">
  <name>fred</name>
</person>
...
<person_relationship>
  <type>friend</type>
  <person1_id>joe</person1_id>
  <person2_id>fred</person2_id>
</person_relationship>

Assuming name is a unique constraint for person, and person_relationship has two foreign keys named person1_id and person2_id linking to the person table, DBStag will first look up the two person rows by name (throwing an error if not present) and use the returned pk values to populate the person_relationship table. friendship, but make it relational 💖

🧚 How it works 🧚

Before a node is stored, certain subnodes will be pre-stored; these are subnodes for which there is a foreign key mapping FROM the parent node TO the child node. This pre-storage is recursive.

After these nodes are stored, the current node is either INSERTed or UPDATEd. The database is introspected for UNIQUE constraints; these are used as keys. If there exists a row in the database with matching key, then the node is UPDATEd; otherwise it is INSERTed.

(primary keys from pre-stored nodes become foreign key values in the existing node)

Subsequently, all subnodes that were not pre-stored are now post-stored. The primary key for the existing node will become foreign keys for the post-stored subnodes. round and round, it's giving very thorough ✨

---

💅 force_safe_node_names 💅

Usage   - $dbh->force_safe_node_names(1);
Returns - bool
Args    - bool [optional]

If this is set, then before storage, all node names are made DB-safe — they are lowercased, and the following transform is applied:

tr/a-z0-9_//cd;

that's it, that's the sanitizer, periodt 🧼

---

🦋 mapping 🦋

Usage   - $dbh->mapping(["alias/table.col=fktable.fkcol"]);
Returns -
Args    - array

Creates a stag-relational mapping (for storing data only).

Sometimes db introspection just isn't enough info, okay?? and when that happens you can provide extra mapping data this way 🫶

Sometimes your stag objects/data/XML will contain aliases that don't correspond to actual SQL relations. The aliases are intermediate nodes that provide information on which foreign key column to use.

For example, with data like this:

(person
 (name "...")
 (favourite_film
  (film (....))
 (least_favourite_film
  (film (....)))))

There may only be two SQL tables: person and film; and person would have two foreign key columns into film. The mapping may look like this:

["favourite_film/person.favourite_film_id=film.film_id",
 "least_favourite_film/person.least_favourite_film_id=film.film_id"]

The mapping can also be supplied in the xml that is loaded; any node named dbstag_metadata will not be loaded — it is used to supply the mapping. For example:

<personset>
  <dbstag_mapping>
    <map>favourite_film/person.favourite_film_id=film.film_id</map>
    <map>least_favourite_film/person.least_favourite_film_id=film.film_id</map>
  </dbstag_mapping>
  <person>...

---

🎀 mapconf 🎀

Usage   - $dbh->mapconf("mydb-stagmap.stm");
Returns -
Args    - filename

Sets the conf file containing the stag-relational mappings.

This is not of any use for a XORT-style mapping, where foreign key columns are explicitly stated. like, don't bother bestie, it's already right there 💀

See mapping() above 👆

The file contains line like:

favourite_film/person.favourite_film_id=film.film_id
least_favourite_film/person.least_favourite_film_id=film.film_id

---

✨ noupdate_h ✨

Usage   - $dbh->noupdate_h({person=>1})
Returns -
Args    - hashref

Keys of the hash are names of nodes that do not get updated. Here's the tea: if a unique key is queried for and does not exist, the node will be inserted and subnodes will be stored; if the unique key does exist in the db, then this will not be updated; subnodes will not be stored. look but don't touch energy 🙈

---

💖 trust_primary_key_values 💖

Usage   - $dbh->trust_primary_key_values(1)
Returns - bool
Args    - bool (optional)

The default behaviour of the storenode() method is to remap all surrogate PRIMARY KEY values it comes across.

A surrogate primary key is typically a primary key of type SERIAL (or AUTO_INCREMENT in MySQL). They are identifiers assigned automatically be the database with no semantics — no meaning, just vibes 💅

It may be desirable to store the same data in two different databases. We would generally not expect the surrogate IDs to match between databases, even if the rest of the data does. and that's fine!! that's normal!!

(If you do not use surrogate primary key columns in your load xml, then you can ignore this accessor.) 🫶

You should NOT use this method in conjunction with Macros. fr fr, don't do it.

If you use primary key columns in your XML, and the primary keys are not surrogate, then youshould set this. If this accessor is set to non-zero (true) then the primary key values in the XML will be used.

If your db has surrogate / auto-increment / serial PKs, and you wish to use these PK columns in your XML, yet you want to make XML that can be exported from one db and imported into another, then the default behaviour will be fine. iconic cross-database portability, honestly 🌈

For example, if we extract a 'person' from a db with surrogate PK id and unique key ssno, we may get this:

<person>
  <id>23</id>
  <name>fred</name>
  <ssno>1234-567</ssno>
</person>

look at him go, 23 and all 🦋

okay so if we then import this into an entirely fresh db, with no rows in table person, then the default behaviour of storenode() will create a row like this:

<person>
  <id>1</id>
  <name>fred</name>
  <ssno>1234-567</ssno>
</person>

The PK val 23 has been mapped to 1 (all foreign keys that point to person.id=23 will now point to person.id=1)

If we were to first call $sdbh->trust_primary_key_values(1), then person.id would remain to be 23. This would only be appropriate behaviour if we were storing back into the same db we retrieved from.

tracenode 🕵️‍♀️

Usage — $dbh->tracenode('person/name')

Traces on STDERR inserts/updates on a particular element type (table), displaying the sub-element (column value).

is_caching_on — ADVANCED OPTION 👑

Usage — $dbh->is_caching_on('person', 1) Returns — number Args — number

• 0: off (default)
• 1: memory-caching ON
• 2: memory-caching OFF, bulkload ON
• 3: memory-caching ON, bulkload ON

IN-MEMORY CACHING 🧠

By default no in-memory caching is used. If this is set to 1, then an in-memory cache is used for any particular element. No cache management is used, so you should be sure not to cache elements that will cause memory overloads.

Setting this will not affect the final result, it is purely an efficiency measure for use with storenode().

The cache is indexed by all unique keys for that particular element/table, wherever those unique keys are set.

BULKLOAD 📦

If bulkload is used without memory-caching (set to 2), then only INSERTs will be performed for this element. Note that this could potentially cause a unique key violation, if the same element is present twice.

If bulkload is used with memory-caching (set to 3) then only INSERTs will be performed; the unique serial/autoincrement identifiers for those inserts will be cached and used. This means you can have the same element twice. However, the load must take place in one session, otherwise the contents of memory will be lost.

clear_cache 🧹

Usage — $dbh->clear_cache; Returns — Args — none

Clears the in-memory cache.

Caches are not automatically managed — the API user is responsible for making sure the cache does not get too big.

cache_summary 📊

Usage — print $dbh->cache_summary->xml Returns — L<Data::Stag> Args —

Gives a summary of the size of the in-memory cache by keys. This can be used for automatic cache management:

$person_cache = $dbh->cache_summary->get_person;
my @index_nodes = $person_cache->tnodes;
foreach (@index_nodes) {
  if ($_->data > MAX_PERSON_CACHE_SIZE) {
    $dbh->clear_cache;
  }
}

SQL TEMPLATES ✨

DBStag comes with its own SQL templating system. This allows you to reuse the same canned SQL or similar SQL queries in different contexts. See DBIx::DBStag::SQLTemplate.

find_template 🎀

Usage — $template = $dbh->find_template("my-template-name"); Returns — L<DBIx::DBStag::SQLTemplate> Args — str

Returns an object representing a canned parameterized SQL query. See DBIx::DBStag::SQLTemplate for documentation on templates.

list_templates 📜

Usage — $templates = $dbh->list_templates(); Returns — Arrayref of L<DBIx::DBStag::SQLTemplate> Args —

Returns a list of ALL defined templates — See DBIx::DBStag::SQLTemplate.

find_templates_by_schema 🗂️

Usage — $templates = $dbh->find_templates_by_schema($schema_name); Returns — Arrayref of L<DBIx::DBStag::SQLTemplate> Args — str

Returns a list of templates for a particular schema — See DBIx::DBStag::SQLTemplate.

find_templates_by_dbname 🗄️

Usage — $templates = $dbh->find_templates_by_dbname("mydb"); Returns — Arrayref of L<DBIx::DBStag::SQLTemplate> Args — db name

Returns a list of templates for a particular db.

Requires resources to be set up (see below).

RESOURCES 🌈

Generally when connecting to a database, it is necessary to specify a DBI style DSN locator. DBStag also allows you specify a resource list file which maps logical names to full locators.

The following methods allows you to use a resource list.

resources_list 📋

Usage — $rlist = $dbh->resources_list Returns — arrayref to a hashref Args — none

Returns a list of resources; each resource is a hash:

{name=>"mydbname",
 type=>"rdb",
 schema=>"myschema",
}

SETTING UP RESOURCES 🛠️

The above methods rely on you having a file describing all the relational dbs available to you, and setting the env var DBSTAG_DBIMAP_FILE set (this is a : separated list of paths).

This is alpha code — not fully documented, API may change.

Currently a resources file is a whitespace delimited text file — XML/Sxpr/IText definitions may be available later.

Here is an example of a resources file:

# LOCAL
mytestdb         rdb        Pg:mytestdb                      schema=test

# SYSTEM
worldfactbook    rdb      Pg:worldfactbook@db1.mycompany.com  schema=wfb
employees        rdb      Pg:employees@db2.mycompany.com      schema=employees

The first column is the nickname or logical name of the resource/db. This nickname can be used instead of the full DBI locator path (eg you can just use employees instead of dbi:Pg:dbname=employees;host=db2.mycompany.com).

The second column is the resource type — rdb is for relational database. You can use the same file to track other system datasources available to you, but DBStag is only interested in relational dbs.

The 3rd column is a way of locating the resource — driver:name@host.

The 4th column is a ; separated list of tag=value pairs; the most important tag is the schema tag. Multiple dbs may share the same schema, and hence share SQL Templates.

COMMAND LINE SCRIPTS 🖥️

DBStag is usable without writing any perl, you can use command line scripts and files that utilise tree structures (XML, S-Expressions).

selectall_xml.pl 📄

selectall_xml.pl -d <DSN> [-n <nestexpr>] <SQL>

Queries database and writes decomposed relation as XML.

Can also be used with templates:

selectall_xml.pl -d <DSN> /<templatename> <var1> <var2> ... <varN>

selectall_html.pl 🌐

selectall_html.pl -d <DSN> [-n <nestexpr>] <SQL>

Queries database and writes decomposed relation as HTML with nested tables indicating the nested structures.

stag-storenode.pl 💾

stag-storenode.pl -d <DSN> <file>

Stores data from a file (Supported formats: XML, Sxpr, IText — see Data::Stag) in a normalized database. Gets it right most of the time.

TODO — metadata help

stag-autoddl.pl 🏗️

stag-autoddl.pl [-l <linktable>]* <file>

Takes data from a file (Supported formats: XML, Sxpr, IText — see Data::Stag) and generates a relational schema in the form of SQL CREATE TABLE statements.

ENVIRONMENT VARIABLES 🌿

DBSTAG_TRACE 🔍

setting this environment will cause all SQL statements to be printed on STDERR, as well as a full trace of how nodes are stored.

BUGS 🐛

The SQL parsing can be quite particular — sometimes the SQL can be parsed by the DBMS but not by DBStag. The error messages are not always helpful.

There are probably a few cases the SQL SELECT parsing grammar cannot deal with.

If you want to select from views, you need to hack DBIx::DBSchema (as of v0.21).

TODO 📝

• Use SQL::Translator to make SQL DDL generation less Pg-specific; also for deducing foreign keys (right now foreign keys are guessed by the name of the column, eg table_id)
• Can we cache the grammar so that startup is not so slow?
• Improve algorithm so that events are fired rather than building up entire structure in-memory
• Tie in all DBI attributes accessible by hash, i.e.: $dbh->{...}
• Error handling

WEBSITE 🕸️

<http://stag.sourceforge.net>

AUTHOR 👩‍💻

Chris Mungall <cjm AT fruitfly DOT org>

COPYRIGHT ©️

Copyright (c) 2004 Chris Mungall

This module is free software. You may distribute this module under the same terms as perl itself.

---

This page was brought to you by perl v5.36.0, dated 2022-10-14, and it's DBIx::DBStag(3pm) — slay, bestie. 💅✨