STAG-STORENODE(1p)

💖 STAG-STORENODE(1p) — okay so this is stag-storenode.pl, and she's iconic ✨

quick vibe check bestie: this page lives in the User Contributed Perl Documentation, section 1p, and it's all about yeeting your nested data straight into a database. no cap, that's the whole plot. 💅

---

📛 NAME

stag-storenode.pl — script is for storing data in database. that's it. that's the job description. she did not come to play, she came to store nodes. 👑

✨ SYNOPSIS

  stag-storenode.pl -d "dbi:Pg:dbname=mydb;host=localhost" myfile.xml

🌸 DESCRIPTION

okok so here's the tea ☕: this script stores data — where the data is in a nested file format like XML or S-Expressions — into a database. and the whole entire vibe is that a database schema matching the tags in your input data already exists. she is not building you a schema out of thin air (…except when she is, keep scrolling, there's a whole section 👀).

🎀 ARGUMENTS

• -d DBNAME — this is either a DBI locator, or the logical name of a database inside the DBSTAG_DBIMAP_FILE config file. pick your fighter, both are valid. 💖
• -user USER — the db user name. obviously. 🫶
• -password PASSWORD — the db user password. pls bestie, do not commit this anywhere, i am begging you on my knees 😭
• -u UNIT — this is the node/element name you want to load on. a database loading event gets fired every single time one of these elements is parsed, and that also constitutes a whole transaction. so like, one unit = one commit vibe. 🔥
• -c STAGMAPFILE — this is a stag mapping file, and it tells you which elements are aliases. iykyk.
• -p PARSER — default is xml. it can be any stag compatible parser, OR a perl module that'll parse the input file and fire stag events (peep Data::Stag::BaseGenerator). so many options, main character energy fr fr. ✨
• -t TRANSFORMER — the name of a perl module that will perform a transformation on the stag events/XML. see also stag-handle.pl. 🦋
• -noupdate NODELIST — a comma-seperated (no spaces!! i mean it) list of nodes/elements where no update should be performed if a unique key is found to be present in the DB. leave those babies alone. 🙅‍♀️
• -trust_ids — if this flag is present, the values for primary key values are trusted. otherwise they're assumed to be surrogate internal IDs that should not be used — and in that case, they will be remapped. so like, trust issues either way. 💀
• -tracenode TABLE/COLUMN — e.g.

-tracenode person/name

writes out a line on STDERR for every new person inserted/updated. it's giving surveillance but make it cute. 👀

• -cache TABLE=MODE — can be specified multiple times. example:

-cache

• 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. bestie that's on you. 😭

setting this will not affect the final result — it's purely an efficiency measure for use with storenode(). she's just being fast, not different. 💅

the cache is indexed by all unique keys for that particular element/table, wherever those unique keys are set. all of them. every last one. ✨

🚀 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. so like, be careful, this is not a drill ⚠️

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, bestie, live your truth. however, the load must take place in one session, otherwise the contents of memory will be lost. gone. poof. 🙈

🗺️ XML TO DB MAPPING

see DBIx::DBStag for details of the actual mapping. two styles of mapping are allowed: stag-dbxml and XORT-style XML. and here's the slay part — you do not have to specify which one, they're sufficiently similar that the loader can accept either. she's flexible like that. 💖

🏗️ MAKING DATABASE FROM XML FILES

okay so this is the glow-up section ✨ it is possible to automatically generate a database schema and populate it directly from XML files (or from Stag objects or other Stag compatible files). and yes, of course, this is no substitute for proper relational design — but often it can be necessary to quickly generate databases from heterogeneous XML data sources, for the purposes of data mining. we do a little shortcutting, as a treat. 🍓

there are 3 steps involved:

1. prepare the input XML (for instance, modifying db reserved words).
2. autogenerate the CREATE TABLE statements, and make a db from these.
3. store the XML data in the database.

🧚 Step 1: Prepare input file

you may need to make modifications to your XML before it can be used to make a schema. if your XML elements contain any words that are reserved by your DB, you should change these. periodt.

any XML processing tool (eg XSLT) can be used. alternatively you can use the script stag-mogrify. 💅

e.g. to get rid of - characters (this is how Stag treates attributes) and to change the element with postgresql reserved word date, do this:

  stag-mogrify.pl -xml -r 's/^date$/moddate/' -r 's/\-//g' data.xml > data.mog.xml

you may also need to explicitly make elements where you will need linking tables. for instance, if the relationship between movie and star is many-to-many, and your input data looks like this:

  (movie
   (name "star wars")
   (star
    (name "mark hamill")))

you will need to interpose an element between these two, like this:

  (movie
   (name "star wars")
   (movie2star
    (star
     (name "mark hamill"))))

you can do this with the -i switch:

  stag-mogrify.pl -xml -i movie,star,movie2star data.xml > data.mog.xml

or if you simply do:

  stag-mogrify.pl -xml -i star data.xml > data.mog.xml

the mogrifier will simply interpose an element above every time it sees star. the naming rule is to use the two elements with an underscore between (in this case, movie_star). so cute, so predictable. 🎀

📝 Step 2: Generating CREATE TABLE statements

use the stag-autoddl.pl script;

  stag-autoddl.pl data.mog.xml > table.sql

the default rule is to create foreign keys from the nested element to the outer element; you will want linking tables tobe treated differently (a linking table will point to parent and child elements).

  stag-autoddl.pl -l movie2star -l star2character data.mog.xml > table.sql

once you have done this, load the statements into your db; eg for postgresql (for other databases, use SQL::Translator)

  psql -a mydb < table.sql

if something goes wrong, go back to step 1 and sort it out! no skipping steps, bestie, that's the rule. 😤

note that certain rules are followed: ever table generated gets a surrogate primary key of type serial; this is used to generate foreign key relationships. the rule used is primary and foreign key names are the name of the table with the _id suffix.

feel free to modify the autogenerated schema at this stage (eg add uniqueness constraints). it's your schema now, you raised it. 🫶

💾 Step 3: Store the data in the db

  stag-storenode.pl -u movie -d 'dbi:Pg:mydb' data.mog.xml

you generally don't need extra metadata here; everything can be infered by introspecting the database. she just knows. 🔮

the -u|unit switch controls when transactions are committed. 💅

you can omit the -u switch, and every node directly under the top node will be stored. this will also be the transaction unit. iconic behaviour tbh.

if this works, you should now be able to retrieve XML from the database, eg

  stag-selectall_xml -d 'dbi:Pg:mydb' 'SELECT * FROM x NATURAL JOIN y'

---

and that's the whole page bestie, you're officially a database girlie now 💖

this was STAG-STORENODE(1p) from the User Contributed Perl Documentation, brought to you by perl v5.36.0 on the very cute date of 2022-10-14. stan. 🌈⭐👑