NAME¶
stag-storenode.pl - script is for storing data in database
SYNOPSIS¶
stag-storenode.pl -d "dbi:Pg:dbname=mydb;host=localhost" myfile.xml
DESCRIPTION¶
This script is for storing data (specified in a nested file format such as XML
or S-Expressions) in a database. It assumes a database schema corresponding to
the tags in the input data already exists.
ARGUMENTS¶
-d DBNAME
This is either a DBI locator or the logical name of a database in the
DBSTAG_DBIMAP_FILE config file
-user USER
db user name
-password PASSWORD
db user password
-u UNIT
This is the node/element name on which to load; a database loading event will be
fired every time one of these elements is parsed; this also constitutes a
whole transaction
-c STAGMAPFILE
This is a stag mapping file, indicating which elements are aliases
-p PARSER
Default is xml; can be any stag compatible parser, OR a perl module which will
parse the input file and fire stag events (see Data::Stag::BaseGenerator)
-t TRANSFORMER
This is 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) list of nodes/elements on which no update should
be performed if a unique key is found to be present in the DB
-trust_ids
If this flag is present, the values for primary key values are trusted;
otherwise they are assumed to be surrogate internal IDs that should not be
used. In this case they will be remapped.
-tracenode TABLE/COLUMN
E.g.
-tracenode person/name
Writes out a line on STDERR for every new person inserted/updated
-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.
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
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. You do not have to specify which, they
are sufficiently similar that the loader can accept either.
MAKING DATABASE FROM XML FILES¶
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).
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.
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.
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.
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').
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!
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)
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.
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.
If this works, you should now be able to retreive XML from the database, eg
stag-selectall_xml -d 'dbi:Pg:mydb' 'SELECT * FROM x NATURAL JOIN y'