DBIx::...okbook(3pm)

💖 NAME 💖

okay bestie, buckle UP, because this is DBIx::DBStag::Cookbook — aka the DBIx::...okbook(3pm) page from the User Contributed Perl Documentation, and it's literally a whole cookbook for building and querying databases from XML. 🎀

✨ SYNOPSIS ✨

stag-autoddl.pl
stag-storenode.pl
selectall_xml.pl

🌈 DESCRIPTION 🌈

so here's the vibe: this page walks you through building a normalised relational database out of XML source data, setting up SQL templates, firing off relational queries that come back hierarchical (as XML or as perl objects), and then autogenerating a whole web query front end for the data. no cap. 🫶

and why would you even want that? because it gives you the full power of the relational model and SQL, plus all the convenience of representations that let you nest data entities. (SQL query results are typically flat relations, which are lowkey inconvenient for complex hierarchical data. it's giving spreadsheet when you wanted a tree. 😭)

the dataset we're using? the CIA World Factbook. iconic.

the web interface should end up looking something like this — <http://www.godatabase.org/cgi-bin/wfb/ubiq.cgi>

---

👑 AUTOGENERATING A RELATIONAL DATABASE 👑

first, download the CIA world factbook in XML format. this was kindly made available by The University of Goettingen as part of their Mondial database project, see <http://www.dbis.informatik.uni-goettingen.de/Mondial/> for details. 💅

the actual XML file lives at <http://www.dbis.informatik.uni-goettingen.de/Mondial/cia.xml>

or you can grab it from <http://www.godatabase.org/wfb/cia.xml>

🌸 Pre-processing 🌸

we need to do some pre-processing on the XML to make it more database-friendly. this is necessitated by the way Stag handles attributes (Stag prefers XML documents that have a simple tree format, periodt). we also wanna turn XXX_id fields into XXX_ciaid, because we prefer to use XXX_id for surrogate keys in the database. yes, we're being picky, it's fine.

stag-mogrify.pl -w xml -r 's/text$/quant/'\
                       -r 's/id$/ciaid/'\
                       -r 's/(.*)\-//'\
                       cia.xml > cia-pp.xml

also see <http://www.godatabase.org/wfb/cia-pp.xml> 💜

🦋 Generating the SQL DDL 🦋

next up we generate the SQL CREATE TABLE statements:

stag-autoddl.pl -t cia-pp2.xml cia-pp.xml > cia-schema.sql

this does further post-processing of the XML, to make it suitable for relational storage; see the file cia-pp2.xml which is generated as a side-effect of running the above script. (surprise file! 🙈)

load the database (the following instructions assume you have postgresql on your localhost; please consult your DBMS manual if this is not the case):

createdb cia
psql -a -e cia < cia-schema.sql >& create.log

(check there are no errors in the log file — seriously bestie, look at the log. 👀)

⭐ LOAD THE DATA ⭐

turn the processed XML into relations:

stag-storenode.pl -d dbi:Pg:dbname=cia cia-pp2.xml >& load.log

---

💀 FETCHING TREE DATA USING SQL 💀

you can issue SQL queries (using optional stag-specific extensions) and get the results back in a hierarchical format such as XML. the way that works? chef's kiss. 👑

🧚 SQL to XML via the command line 🧚

fetch countries nested under continents:

selectall_xml.pl -d dbi:Pg:dbname=cia\
"SELECT * FROM continent INNER JOIN country ON (continent.name=country.continent)"

or, edit a file containing the SQL (the following query fetches data on countries bordering other countries on different continents):

cat > myquery.sql
select c1.*, c2.*
from country AS c1
         inner join borders on (c1.country_id = borders.country_id)
         inner join country AS c2 on (borders.country=c2.ciaid)
where c1.continent != c2.continent
order by c1.name, c2.name
use nesting (set(c1(c2)));

(the final clause is a DBStag SQL extension — it nests country c2 under country c1, slay)

then query for XML:

selectall_xml.pl -d dbi:Pg:dbname=cia -f myquery.sql > myresults.xml

💅 SQL to XML via the Interactive Query Shell 💅

query the data using the stag query shell (qsh). you type in SQL queries, and get results back as XML (or any other tree format, such as indented text or S-Expressions). versatile queen.

the following can be cut and pasted directly onto the unix command line:

simple query rooted at country:

stag-qsh -d dbi:Pg:dbname=cia
\l
SELECT * FROM country INNER JOIN country_coasts USING (country_id)
WHERE country.name = 'France';

(type \q to quit stag-qsh)

or a more advanced query, still rooted at country:

stag-qsh -d dbi:Pg:dbname=cia
\l
SELECT *
FROM country
     LEFT OUTER JOIN religions USING (country_id)
     LEFT OUTER JOIN languages USING (country_id)
     INNER JOIN continent ON (continent.name=country.continent)
WHERE continent.ciaid = 'australia'
USE NESTING (set(country(religions)(languages)(continent)));

see DBIx::DBStag for more details on fetching hierarchical data from relational database 🌈

---

🎀 USING TEMPLATES 🎀

if you have a particular pattern of SQL you execute a lot, you can reuse this SQL by creating templates. main character energy fr fr.

🍓 Creating Templates 🍓

first create a place for your templates:

mkdir ./templates

(do not change directory after this — like, do NOT. 🙅)

the following command specifies a colon-separated path for directories containing templates (all templates must end with .stg):

setenv DBSTAG_TEMPLATE_DIRS ".:templates:/usr/local/share/sql/templates"

auto-generate templates (you can customize these later):

stag-autoschema.pl -w sxpr cia-pp2.xml > cia-stagschema.sxpr
stag-autotemplate.pl -no_pp -s cia -dir ./templates  cia-stagschema.sxpr

the first command creates an S-Expression representation of the Schema; the second generates SQL templates from these. ✨

you may wish to examine a template:

more templates/cia-country.stg

you can hand generate as many templates as you like; see DBIx::DBStag::SQLTemplate for more details 💜

for more example templates for this schema, see <http://www.godatabase.org/cgi-bin/wfb/ubiq.cgi>

🌸 Executing Templates from the Command Line 🌸

now execute a template from the command line:

selectall_xml.pl -d dbi:Pg:dbname=cia /cia-country country_name=Austria

you should get back a tree (rooted in country), that looks similar to this:

<set>
  <country>
    <country_id>3</country_id>
    <government>federal republic</government>
    <population>8023244</population>
    <total_area>83850</total_area>
    <name>Austria</name>
    <inflation>2.3</inflation>
    ...
    <languages>
      <languages_id>1</languages_id>
      <name>German</name>
      <num>100</num>
      <country_id>3</country_id>
    </languages>
    ...

🫶 Executing Templates with the Stag Query Shell 🫶

you can also do this interactively using qsh 💅

first, we need to inform stag-qsh what the schema is. the schema is used to determine which templates are appropriate. later we will discover how to set up a resources file, which will allow stag to infer the schema. foreshadowing! 👀

call qsh from command line:

stag-qsh -d dbi:Pg:dbname=cia -s cia

interactive perl/qsh:

\l
t cia-country
/borders_country=cid-cia-Germany

(do not leave spaces at the beginning of the line — nope, not even one 😭)

the above should fetch all countries bordering Germany 🇩🇪

if we prefer objects over hierarchical formats such as XML, we can do this using perl. for example, to print the religions of spanish speaking countries:

still in qsh (multi-line mode), type the following:

# find all Spanish-speaking countries
$dataset =
  $dbh->selectall_stag(-template=>'cia-country',-bind=>{languages_name=>'Spanish'});
# get country objects from query results
@lcountry = $dataset->get_country;

foreach $country (@lcountry) {
  printf("Country: %s\n  Religions:%s\n",
         $country->sget_name,
         join(' & ',
              map {
                   $_->get_name.' '.$_->get_quant.'%'
              } $country->get_religions))
}
print "\n\nDone!\n";

\q

see Data::Stag for more details on using Stag objects ✨

---

👑 BUILDING A CGI/WEB INTERFACE 👑

we can construct a generic but powerful default cgi interface for our data, using ubiq.cgi, which should come with your distribution. built-in queen behavior.

you may have to modify some of the directories below, depending on your web server set up (we assume Apache here).

we want to create the CGI, and give it access to our templates:

mkdir /usr/local/httpd/cgi-bin/cia
cp templates/*.stg /usr/local/httpd/cgi-bin/cia
cp `which ubiq.cgi` /usr/local/httpd/cgi-bin/cia
chmod +x /usr/local/httpd/cgi-bin/cia/ubiq.cgi
mkdir /usr/local/httpd/cgi-bin/cia/cache
chmod 777 /usr/local/httpd/cgi-bin/cia/cache

set up the environment for the CGI script. it must be able to see the templates and the necessary perl libraries (if not installed system-wide):

cat > /usr/local/httpd/cgi-bin/cia/dbenv.pl
$ENV{DBSTAG_DBIMAP_FILE} = "./resources.conf";
$ENV{DBSTAG_TEMPLATE_DIRS} = ".:./templates:/usr/local/share/sql/templates";
$ENV{STAGLIB} = "/users/me/lib/DBIx-DBStag:/users/me/lib/stag";

we must create a basic resources file, currently containing one db:

cat > /usr/local/httpd/cgi-bin/cia/resources.conf
cia              rdb               Pg:cia        schema=cia

fields are whitespace delimited; do not leave a space before the initial 'cia' 🙈

(note that if you set DBSTAG_DBIMAP_FILE to the avove file on the command line, you can use the shortened name of cia instead of dbi:Pg:dbname=cia)

you should be able to use the interface via http://localhost/cgi-bin/cia/ubiq.cgi

you can customize this by overriding some of the existing display functions;

cat > /usr/local/httpd/cgi-bin/cia/ubiq-customize.pl
# --- CUSTOM SETTINGS
{
 no warnings 'redefine';

 *g_title = sub {
     "U * B * I * Q - CIA World Factbook";
 };
 *short_intro = sub {
     "Demo interface to CIA World Factbook"
 };
 add_initfunc(sub {
                 $dbname = 'cia';
                 $schema = 'cia';
             });
}

from here on you can customise the web interface, create new templates, integrate this with other data. consult DBIx::DBStag and the script ubiq.cgi for further details. go off. 🔥

---

🌈 FURTHER EXPLORATION 🌈

this cookbook has focused on an example with relatively simple XML, with only a few layers of nesting. (baby's first hierarchy, we love her 🥹)

there is a more complex example you can download from the Mondial project site here: <http://www.dbis.informatik.uni-goettingen.de/Mondial/mondial-2.0.xml>

this also integrates data on cities, which increases the depth of the XML tree. deeper. spookier. iconic.

you could use the tutorial above to try and turn this XML into a database. go on, you got this. 💪

🌟 WEBSITE 🌟

<http://stag.sourceforge.net>

💜 AUTHOR 💜

Chris Mungall

cjm at fruitfly dot org

💖 COPYRIGHT 💖

Okay bestie, the legal-ish bit, but make it cute:

• Copyright (c) 2002 Chris Mungall 🫶

This module is free software. You may distribute it under the same terms as perl itself ✨ — so like, go forth, share it, remix it, be iconic with it, just keep the same energy (and the same license terms) when you do 💅

no cap, that's the whole copyright section. short, sweet, legally binding, slay 🎀

---

And that's a wrap on the whole page, bestie!! 🌸 This lil' guide came to you courtesy of perl v5.36.0, dated 2022-10-14, and it lives under the name DBIx::...okbook(3pm) 📖✨ Go forth, read your databases, and be the main character of your own codebase 💖🦋👑