4 htsn-import \- Import XML files from The Sports Network into an RDBMS.
8 \fBhtsn-import\fR [OPTIONS] [FILES]
12 The Sports Network <http://www.sportsnetwork.com/> offers an XML feed
13 containing various sports news and statistics. Our sister program
14 \fBhtsn\fR is capable of retrieving the feed and saving the individual
15 XML documents contained therein. But what to do with them?
17 The purpose of \fBhtsn-import\fR is to take these XML documents and
18 get them into something we can use, a relational database management
19 system (RDBMS), otherwise known as a SQL database. The structure of
20 relational database, is, well, relational, and the feed XML is not. So
21 there is some work to do before the data can be imported into the
24 First, we must parse the XML. Each supported document type (see below)
25 has a full pickle/unpickle implementation (\(dqpickle\(dq is simply a
26 synonym for \(dqserialize\(dq here). That means that we parse the
27 entire document into a data structure, and if we pickle (serialize)
28 that data structure, we get the exact same XML document tha we started
31 This is important for two reasons. First, it serves as a second level
32 of validation. The first validation is performed by the XML parser,
33 but if that succeeds and unpicking fails, we know that something is
34 fishy. Second, we don't ever want to be surprised by some new element
35 or attribute showing up in the XML. The fact that we can unpickle the
36 whole thing now means that we won't be surprised in the future.
38 The aforementioned feature is especially important because we
39 automatically migrate the database schema every time we import a
40 document. If you attempt to import a \(dqnewsxml.dtd\(dq document, all
41 database objects relating to the news will be created if they do not
42 exist. We don't want the schema to change out from under us without
43 warning, so it's important that no XML be parsed that would result in
44 a different schema than we had previously. Since we can
45 pickle/unpickle everything already, this should be impossible.
47 A list of supported document types is given in the appendix.
49 The GameInfo and SportInfo types do not have their own top-level
50 tables in the database. Instead, their raw XML is stored in either the
51 \(dqgame_info\(dq or \(dqsport_info\(dq table respectively.
55 At the top level (with two notable exceptions), we have one table for
56 each of the XML document types that we import. For example, the
57 documents corresponding to \fInewsxml.dtd\fR will have a table called
58 \(dqnews\(dq. All top-level tables contain two important fields,
59 \(dqxml_file_id\(dq and \(dqtime_stamp\(dq. The former is unique and
60 prevents us from inserting the same data twice. The time stamp on the
61 other hand lets us know when the data is old and can be removed. The
62 database schema make it possible to delete only the outdated top-level
63 records; all transient children should be removed by triggers.
65 These top-level tables will often have children. For example, each
66 news item has zero or more locations associated with it. The child
67 table will be named <parent>_<children>, which in this case
68 corresponds to \(dqnews_locations\(dq.
70 To relate the two, a third table may exist with name
71 <parent>__<child>. Note the two underscores. This prevents ambiguity
72 when the child table itself contains underscores. The table joining
73 \(dqnews\(dq with \(dqnews_locations\(dq is thus called
74 \(dqnews__news_locations\(dq. This is necessary when the child table
75 has a unique constraint; we don't want to blindly insert duplicate
76 records keyed to the parent. Instead we'd like to use the third table
77 to map an existing child to the new parent.
79 Where it makes sense, children are kept unique to prevent pointless
80 duplication. This slows down inserts, and speeds up reads (which are
81 much more frequent). There is a tradeoff to be made, however. For a
82 table with a small, fixed upper bound on the number of rows (like
83 \(dqodds_casinos\(dq), there is great benefit to de-duplication. The
84 total number of rows stays small, so inserts are still quick, and many
85 duplicate rows are eliminated.
87 But, with a table like \(dqodds_games\(dq, the number of games grows
88 quickly and without bound. It is therefore more beneficial to be able
89 to delete the old games (through an ON DELETE CASCADE, tied to
90 \(dqodds\(dq) than it is to eliminate duplication. A table like
91 \(dqnews_locations\(dq is somewhere in-between. It is hoped that the
92 unique constraint in the top-level table's \(dqxml_file_id\(dq will
93 prevent duplication in this case anyway.
95 The aforementioned exceptions are the \(dqgame_info\(dq and
96 \(dqsport_info\(dq tables. These tables contain the raw XML for a
97 number of DTDs that are not handled individually. This is partially
98 for backwards-compatibility with a legacy implementation, but is
99 mostly a stopgap due to a lack of resources at the moment. These two
100 tables (game_info and sport_info) still possess timestamps that allow
101 us to prune old data.
103 UML diagrams of the resulting database schema for each XML document
104 type are provided with the \fBhtsn-import\fR documentation, in the
105 \fIdoc/dbschema\fR directory. These are not authoritative, but it
106 should be considered a bug if they are incorrect. The diagrams are
107 created using the pgModeler <http://www.pgmodeler.com.br/> tool.
111 Normally in a database one makes a distinction between fields that
112 simply don't exist, and those fields that are
113 \(dqempty\(dq. Translating from XML, there is a natural way to
114 determine which one should be used: if an element is present in the
115 XML document but its contents are empty, then an empty string should
116 be inserted into the corresponding field. If on the other hand the
117 element is missing entirely, the corresponding database entry should
118 be NULL to indicate that fact.
120 This sounds well and good, but the XML must be consistent for the
121 database consumer to make any sense of what he sees. The feed XML uses
122 optional and blank elements interchangeably, and without any
123 discernable pattern. To propagate this pattern into the database would
124 only cause confusion.
126 As a result, a policy was adopted: both optional elements and elements
127 whose contents can be empty will be considered nullable in the
128 database. If the element is missing, the corresponding field is
129 NULL. Likewise if the content is simply missing. That means there
130 should never be a (completely) empty string in a database column.
132 .SH XML SCHEMA GENERATION
134 In order to parse XML, you need to know the structure of your
135 documents. Usually this is given in the form of a DTD or schema. The
136 Sports Network does provide DTDs for their XML, but unfortunately many
137 of them do not match the XML found on the feed.
139 We need to construct a database into which to insert the XML. How do
140 we know if <game> should be a column, or if it should have its own
141 table? We need to know how many times it can appear in the
142 document. So we need some form of specification. Since the supplied
143 DTDs are incorrect, we would like to generate them automatically.
145 The process should go something like,
147 Generate a DTD from the first foo.xml file we see. Call it foo.dtd.
149 Validate future foo documents against foo.dtd. If they all validate,
150 great. If one fails, add it to the corpus and update foo.dtd so
151 that both the original and the new foo.xml validate.
153 Repeat until no more failures occur. This can never be perfect:
154 tomorrow we could get a foo.xml that's wildly different from what
155 we've seen in the past. But it's the best we can hope for under
158 Enter XML-Schema-learner
159 <https://github.com/kore/XML-Schema-learner>. This tool can infer a
160 DTD from a set of sample XML files. The top-level \(dqschemagen\(dq
161 folder (in this project) contains a number of subfolders\(emone for
162 each type of document that we want to parse. Contained therein are XML
163 samples for that particular document type. These were hand-picked one
164 at a time according to the procedure above, and the complete set of
165 XML is what we use to generate the DTDs used by htsn-import.
167 To generate them, run `make schema` at the project
168 root. XML-Schema-learner will be invoked on each subfolder of
169 \(dqschemagen\(dq and will output the corresponding DTDs to the
170 \(dqschemagen\(dq folder.
172 Most of the production schemas are generated this way; however, a few
173 needed manual tweaking. The final, believed-to-be-correct schemas for
174 all supported document types can be found in the \(dqschema\(dq folder in
175 the project root. Having the correct DTDs available means you
176 don't need XML-Schema-learner available to install \fBhtsn-import\fR.
178 .SH XML SCHEMA UPDATES
180 If a new tag is added to an XML document type, \fBhtsn-import\fR will
181 most likely refuse to parse it, since the new documents no longer
182 match the existing DTD.
184 The first thing to do in that case is add the unparseable document to
185 the \(dqschemagen\(dq directory, and generate a new DTD that matches
186 both the old and new samples. Once a new, correct DTD has been
187 generated, it should be added to the \(dqschema\(dq directory. Then,
188 the parser can be updated and \fBhtsn-import\fR rebuilt.
190 At this point, \fBhtsn-import\fR should be capable of importing the
191 new document. But the addition of the new tag will most require new
192 fields in the database. Fortunately, easy migrations like this are
193 handled automatically. As an example, at one point, \fIOdds_XML.dtd\fR
194 did not contain the \(dqHStarter\(dq and \(dqAStarter\(dq elements
195 associated with its games. Suppose we parse one of the old documents
196 (without \(dqHStarter\(dq and \(dqAStarter\(dq) using an old version
197 of \fBhtsn-import\fR:
200 .I $ htsn-import --connection-string='foo.sqlite3' \\\\
201 .I " schemagen/Odds_XML/19996433.xml"
202 Migration: CREATE TABLE \(dqodds\(dq ...
203 Successfully imported schemagen/Odds_XML/19996433.xml.
204 Processed 1 document(s) total.
207 At this point, the database schema matches the old documents, i.e. the
208 ones without \fIAStarter\fR and \fIHStarter\fR. If we use a new
209 version of \fBhtsn-import\fR, supporting the new fields, the migration
210 is handled gracefully:
213 .I $ htsn-import --connection-string='foo.sqlite3' \\\\
214 .I " schemagen/Odds_XML/21315768.xml"
215 Migration: ALTER TABLE \(dqodds_games\(dq
216 ADD COLUMN \(dqaway_team_starter_id\(dq INTEGER;
217 Migration: ALTER TABLE \(dqodds_games\(dq
218 ADD COLUMN \(dqaway_team_starter_name\(dq VARCHAR;
219 Migration: ALTER TABLE \(dqodds_games\(dq
220 ADD COLUMN \(dqhome_team_starter_id\(dq INTEGER;
221 Migration: ALTER TABLE \(dqodds_games\(dq
222 ADD COLUMN \(dqhome_team_starter_name\(dq VARCHAR;
223 Successfully imported schemagen/Odds_XML/21315768.xml.
224 Processed 1 document(s) total.
227 If fields are removed from the schema, then manual intervention may be
231 .I $ htsn-import -b Postgres -c 'dbname=htsn user=postgres' \\\\
232 .I " schemagen/Odds_XML/19996433.xml"
233 ERROR: Database migration: manual intervention required.
234 The following actions are considered unsafe:
235 ALTER TABLE \(dqodds_games\(dq DROP COLUMN \(dqaway_team_starter_id\(dq
236 ALTER TABLE \(dqodds_games\(dq DROP COLUMN \(dqaway_team_starter_name\(dq
237 ALTER TABLE \(dqodds_games\(dq DROP COLUMN \(dqhome_team_starter_id\(dq
238 ALTER TABLE \(dqodds_games\(dq DROP COLUMN \(dqhome_team_starter_name\(dq
240 ERROR: Failed to import file schemagen/Odds_XML/19996433.xml.
241 Processed 0 document(s) total.
244 To fix these errors, manually invoke the SQL commands that were
248 .I $ psql -U postgres -d htsn \\\\
249 .I " -c 'ALTER TABLE odds_games DROP COLUMN away_team_starter_id;'"
251 .I $ psql -U postgres -d htsn \\\\
252 .I " -c 'ALTER TABLE odds_games DROP COLUMN away_team_starter_name;'"
254 .I $ psql -U postgres -d htsn \\\\
255 .I " -c 'ALTER TABLE odds_games DROP COLUMN home_team_starter_id;'"
257 .I $ psql -U postgres -d htsn \\\\
258 .I " -c 'ALTER TABLE odds_games DROP COLUMN home_team_starter_name;'"
262 After manually adjusting the schema, the import should succeed.
264 .SH XML SCHEMA ODDITIES
266 There are a number of problems with the XML on the wire. Even if we
267 construct the DTDs ourselves, the results are sometimes
268 inconsistent. Here we document a few of them.
273 The TSN DTD for news (and almost all XML on the wire) suggests that
274 there is a exactly one (possibly-empty) <SMS> element present in each
275 message. However, we have seen an example (XML_File_ID 21232353) where
276 an empty <SMS> followed a non-empty one:
279 <SMS>Odd Man Rush: Snow under pressure to improve Isles quickly</SMS>
283 We don't parse this case at the moment.
288 The <Notes> elements here are supposed to be associated with a set of
289 <Game> elements, but since the pair
290 (<Notes>...</Notes><Game>...</Game>) can appear zero or more times,
291 this leads to ambiguity in parsing. We therefore ignore the notes
292 entirely (although a hack is employed to facilitate parsing). The same
293 thing goes for the newer <League_Name> element.
298 There appear to be two types of weather documents; the first has
299 <listing> contained within <forecast> and the second has <forecast>
300 contained within <listing>. While it would be possible to parse both,
301 it would greatly complicate things. The first form is more common, so
302 that's all we support for now. An example is provided as
303 test/xml/weatherxml-type2.xml.
305 We are however able to identify the second type. When one is
306 encountered, an informational message (that it is unsupported) will be
307 printed. If the \fI\-\-remove\fR flag is used, the file will be
308 deleted. This prevents documents that we know we can't import from
311 Another problem that comes up occasionally is that the home and away
312 team elements appear in the reverse order. As in the other case, we
313 report these as unsupported and then \(dqsucceed\(dq so that the
314 offending document can be removed if desired. An example is provided
315 as test/xml/weatherxml-backwards-teams.xml.
319 When deploying for the first time, the target database will most
320 likely be empty. The schema will be migrated when a new document type
321 is seen, but this has a downside: it can be months before every
322 supported document type has been seen once. This can make it difficult
323 to test the database permissions.
325 Since all of the test XML documents have old timestamps, one easy
326 workaround is the following: simply import all of the test XML
327 documents, and then delete them using whatever script is used to prune
328 old entries. This will force the migration of the schema, after which
329 you can set and test the database permissions.
331 Something as simple as,
334 .I $ find ./test/xml -iname '*.xml' | xargs htsn-import -c foo.sqlite
341 .IP \fB\-\-backend\fR,\ \fB\-b\fR
342 The RDBMS backend to use. Valid choices are \fISqlite\fR and
343 \fIPostgres\fR. Capitalization is important, sorry.
347 .IP \fB\-\-connection-string\fR,\ \fB\-c\fR
348 The connection string used for connecting to the database backend
349 given by the \fB\-\-backend\fR option. The default is appropriate for
350 the \fISqlite\fR backend.
352 Default: \(dq:memory:\(dq
354 .IP \fB\-\-log-file\fR
355 If you specify a file here, logs will be written to it (possibly in
356 addition to syslog). Can be either a relative or absolute path. It
357 will not be auto-rotated; use something like logrotate for that.
361 .IP \fB\-\-log-level\fR
362 How verbose should the logs be? We log notifications at four levels:
363 DEBUG, INFO, WARN, and ERROR. Specify the \(dqmost boring\(dq level of
364 notifications you would like to receive (in all-caps); more
365 interesting notifications will be logged as well. The debug output is
366 extremely verbose and will not be written to syslog even if you try.
370 .IP \fB\-\-remove\fR,\ \fB\-r\fR
371 Remove successfully processed files. If you enable this, you can see
372 at a glance which XML files are not being processed, because they're
373 all that should be left.
377 .IP \fB\-\-syslog\fR,\ \fB\-s\fR
378 Enable logging to syslog. On Windows this will attempt to communicate
379 (over UDP) with a syslog daemon on localhost, which will most likely
384 .SH CONFIGURATION FILE
386 Any of the command-line options mentioned above can be specified in a
387 configuration file instead. We first look for \(dqhtsn-importrc\(dq in
388 the system configuration directory. We then look for a file named
389 \(dq.htsn-importrc\(dq in the user's home directory. The latter will
392 The user's home directory is simply $HOME on Unix; on Windows it's
393 wherever %APPDATA% points. The system configuration directory is
394 determined by Cabal; the \(dqsysconfdir\(dq parameter during the
395 \(dqconfigure\(dq step is used.
397 The file's syntax is given by examples in the htsn-importrc.example file
398 (included with \fBhtsn-import\fR).
400 Options specified on the command-line override those in either
405 Import newsxml.xml into a preexisting sqlite database named \(dqfoo.sqlite3\(dq:
408 .I $ htsn-import --connection-string='foo.sqlite3' \\\\
409 .I " test/xml/newsxml.xml"
410 Successfully imported test/xml/newsxml.xml.
411 Imported 1 document(s) total.
414 Repeat the previous example, but delete newsxml.xml afterwards:
417 .I $ htsn-import --connection-string='foo.sqlite3' \\\\
418 .I " --remove test/xml/newsxml.xml"
419 Successfully imported test/xml/newsxml.xml.
420 Imported 1 document(s) total.
421 Removed processed file test/xml/newsxml.xml.
424 Use a Postgres database instead of the default Sqlite. This assumes
425 that you have a database named \(dqhtsn\(dq accessible to user
426 \(dqpostgres\(dq locally:
429 .I $ htsn-import --connection-string='dbname=htsn user=postgres' \\\\
430 .I " --backend=Postgres test/xml/newsxml.xml"
431 Successfully imported test/xml/newsxml.xml.
432 Imported 1 document(s) total.
438 Send bugs to michael@orlitzky.com.
440 .SH APPENDIX: SUPPORTED DOCUMENT TYPES
442 The XML document types obtained from the feed are uniquely identified
443 by their DTDs. We currently support documents with the following DTDs:
445 AutoRacingResultsXML.dtd
447 Auto_Racing_Schedule_XML.dtd
451 Injuries_Detail_XML.dtd
461 Schedule_Changes_XML.dtd
476 Matchup_NBA_NHL_XML.dtd
480 MLB_Gaming_Matchup_XML.dtd
490 NBA_Gaming_Matchup_XML.dtd
492 NBA_Playoff_Matchup_XML.dtd
498 NCAA_FB_Preview_XML.dtd
500 NFL_NCAA_FB_Matchup_XML.dtd
508 WorldBaseballPreviewXML.dtd
516 Cbask_All_Tourn_Teams_XML.dtd
524 Cbask_Conf_Standings_XML.dtd
526 Cbask_DivII_III_Indv_Stats_XML.dtd
528 Cbask_DivII_Team_Stats_XML.dtd
530 Cbask_DivIII_Team_Stats_XML.dtd
538 Cbask_Indv_Scoring_XML.dtd
544 CBASK_ReboundsXML.dtd
546 CBASK_ScoringLeadersXML.dtd
548 Cbask_Team_ThreePT_Made_XML.dtd
550 Cbask_Team_ThreePT_PCT_XML.dtd
552 Cbask_Team_Win_Pct_XML.dtd
554 Cbask_Top_Twenty_Five_XML.dtd
556 CBASK_TopTwentyFiveResult_XML.dtd
558 Cbask_Tourn_Awards_XML.dtd
560 Cbask_Tourn_Champs_XML.dtd
562 Cbask_Tourn_Indiv_XML.dtd
564 Cbask_Tourn_Leaders_XML.dtd
566 Cbask_Tourn_MVP_XML.dtd
568 Cbask_Tourn_Records_XML.dtd
570 LeagueScheduleXML.dtd
574 Minor_Baseball_League_Leaders_XML.dtd
576 Minor_Baseball_Standings_XML.dtd
578 Minor_Baseball_Transactions_XML.dtd
582 mlbdoublesleadersxml.dtd
584 MLBGamesPlayedXML.dtd
590 mlbhitsleadersxml.dtd
608 mlbrunsleadersxml.dtd
616 mlbsluggingpctxml.dtd
620 mlbstandxml_preseason.dtd
624 mlbtotalbasesleadersxml.dtd
626 mlbtriplesleadersxml.dtd
630 mlbwalksleadersxml.dtd
632 MLBXtraBaseHitsXML.dtd
634 MLB_Pitching_Appearances_Leaders.dtd
638 MLB_Pitching_Balks_Leaders.dtd
640 MLB_Pitching_CG_Leaders.dtd
642 MLB_Pitching_ER_Allowed_Leaders.dtd
644 MLB_Pitching_Hits_Allowed_Leaders.dtd
646 MLB_Pitching_Hit_Batters_Leaders.dtd
648 MLB_Pitching_HR_Allowed_Leaders.dtd
650 MLB_Pitching_IP_Leaders.dtd
652 MLB_Pitching_Runs_Allowed_Leaders.dtd
654 MLB_Pitching_Saves_Leaders.dtd
656 MLB_Pitching_Shut_Outs_Leaders.dtd
658 MLB_Pitching_Starts_Leaders.dtd
660 MLB_Pitching_Strike_Outs_Leaders.dtd
662 MLB_Pitching_Walks_Leaders.dtd
664 MLB_Pitching_WHIP_Leaders.dtd
666 MLB_Pitching_Wild_Pitches_Leaders.dtd
668 MLB_Pitching_Win_Percentage_Leaders.dtd
670 MLB_Pitching_WL_Leaders.dtd
672 NBA_Team_Stats_XML.dtd
702 nbateamleadersxml.dtd
704 nbatripledoublexml.dtd
708 NCAA_Conference_Schedule_XML.dtd
712 NFLFumbleLeaderXML.dtd
720 NFLMondayNightXML.dtd
726 NFLSackLeadersXML.dtd
730 NFLTeamRankingsXML.dtd
732 NFLTopPerformanceXML.dtd
734 NFLTotalYardageXML.dtd
736 NFL_KickingLeaders_XML.dtd
738 NFL_NBA_Draft_XML.dtd
742 NFL_Team_Stats_XML.dtd
748 WNBA_Team_Leaders_XML.dtd