doc/man1/htsn-import.1

   1 .TH htsn-import 1
   2
   3 .SH NAME
   4 htsn-import \- Import XML files from The Sports Network into an RDBMS.
   5
   6 .SH SYNOPSIS
   7
   8 \fBhtsn-import\fR [OPTIONS] [FILES]
   9
  10 .SH DESCRIPTION
  11 .P
  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?
  16 .P
  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
  22 database.
  23 .P
  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
  29 with.
  30 .P
  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.
  37 .P
  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.
  46
  47 .SH SUPPORTED DOCUMENT TYPES
  48 .P
  49 The XML document types obtained from the feed are uniquely identified
  50 by their DTDs. We currently support documents with the following DTDs:
  51 .IP \[bu] 2
  52 AutoRacingResultsXML.dtd
  53 .IP \[bu]
  54 Auto_Racing_Schedule_XML.dtd
  55 .IP \[bu]
  56 Heartbeat.dtd
  57 .IP \[bu]
  58 Injuries_Detail_XML.dtd
  59 .IP \[bu]
  60 injuriesxml.dtd
  61 .IP \[bu]
  62 jfilexml.dtd
  63 .IP \[bu]
  64 newsxml.dtd
  65 .IP \[bu]
  66 Odds_XML.dtd
  67 .IP \[bu]
  68 scoresxml.dtd
  69 .IP \[bu]
  70 weatherxml.dtd
  71 .IP \[bu]
  72 GameInfo
  73 .RS
  74 .IP \[bu]
  75 CBASK_Lineup_XML.dtd
  76 .IP \[bu]
  77 cbaskpreviewxml.dtd
  78 .IP \[bu]
  79 cflpreviewxml.dtd
  80 .IP \[bu]
  81 Matchup_NBA_NHL_XML.dtd
  82 .IP \[bu]
  83 MLB_Fielding_XML.dtd
  84 .IP \[bu]
  85 MLB_Gaming_Matchup_XML.dtd
  86 .IP \[bu]
  87 MLB_Lineup_XML.dtd
  88 .IP \[bu]
  89 MLB_Matchup_XML.dtd
  90 .IP \[bu]
  91 MLS_Preview_XML.dtd
  92 .IP \[bu]
  93 mlbpreviewxml.dtd
  94 .IP \[bu]
  95 NBA_Gaming_Matchup_XML.dtd
  96 .IP \[bu]
  97 NBA_Playoff_Matchup_XML.dtd
  98 .IP \[bu]
  99 NBALineupXML.dtd
 100 .IP \[bu]
 101 nbapreviewxml.dtd
 102 .IP \[bu]
 103 NCAA_FB_Preview_XML.dtd
 104 .IP \[bu]
 105 NFL_NCAA_FB_Matchup_XML.dtd
 106 .IP \[bu]
 107 nflpreviewxml.dtd
 108 .IP \[bu]
 109 nhlpreviewxml.dtd
 110 .IP \[bu]
 111 recapxml.dtd
 112 .IP \[bu]
 113 WorldBaseballPreviewXML.dtd
 114 .RE
 115 .IP \[bu]
 116 SportInfo
 117 .RS
 118 .IP \[bu]
 119 CBASK_3PPctXML.dtd
 120 .IP \[bu]
 121 Cbask_All_Tourn_Teams_XML.dtd
 122 .IP \[bu]
 123 CBASK_AssistsXML.dtd
 124 .IP \[bu]
 125 Cbask_Awards_XML.dtd
 126 .IP \[bu]
 127 CBASK_BlocksXML.dtd
 128 .IP \[bu]
 129 Cbask_Conf_Standings_XML.dtd
 130 .IP \[bu]
 131 Cbask_DivII_III_Indv_Stats_XML.dtd
 132 .IP \[bu]
 133 Cbask_DivII_Team_Stats_XML.dtd
 134 .IP \[bu]
 135 Cbask_DivIII_Team_Stats_XML.dtd
 136 .IP \[bu]
 137 CBASK_FGPctXML.dtd
 138 .IP \[bu]
 139 CBASK_FoulsXML.dtd
 140 .IP \[bu]
 141 CBASK_FTPctXML.dtd
 142 .IP \[bu]
 143 Cbask_Indv_Scoring_XML.dtd
 144 .IP \[bu]
 145 CBASK_MinutesXML.dtd
 146 .IP \[bu]
 147 Cbask_Polls_XML.dtd
 148 .IP \[bu]
 149 CBASK_ReboundsXML.dtd
 150 .IP \[bu]
 151 CBASK_ScoringLeadersXML.dtd
 152 .IP \[bu]
 153 Cbask_Team_ThreePT_Made_XML.dtd
 154 .IP \[bu]
 155 Cbask_Team_ThreePT_PCT_XML.dtd
 156 .IP \[bu]
 157 Cbask_Team_Win_Pct_XML.dtd
 158 .IP \[bu]
 159 Cbask_Top_Twenty_Five_XML.dtd
 160 .IP \[bu]
 161 CBASK_TopTwentyFiveResult_XML.dtd
 162 .IP \[bu]
 163 Cbask_Tourn_Awards_XML.dtd
 164 .IP \[bu]
 165 Cbask_Tourn_Champs_XML.dtd
 166 .IP \[bu]
 167 Cbask_Tourn_Indiv_XML.dtd
 168 .IP \[bu]
 169 Cbask_Tourn_Leaders_XML.dtd
 170 .IP \[bu]
 171 Cbask_Tourn_MVP_XML.dtd
 172 .IP \[bu]
 173 Cbask_Tourn_Records_XML.dtd
 174 .IP \[bu]
 175 LeagueScheduleXML.dtd
 176 .IP \[bu]
 177 minorscoresxml.dtd
 178 .IP \[bu]
 179 Minor_Baseball_League_Leaders_XML.dtd
 180 .IP \[bu]
 181 Minor_Baseball_Standings_XML.dtd
 182 .IP \[bu]
 183 Minor_Baseball_Transactions_XML.dtd
 184 .IP \[bu]
 185 mlbbattingavgxml.dtd
 186 .IP \[bu]
 187 mlbdoublesleadersxml.dtd
 188 .IP \[bu]
 189 MLBGamesPlayedXML.dtd
 190 .IP \[bu]
 191 MLBGIDPXML.dtd
 192 .IP \[bu]
 193 MLBHitByPitchXML.dtd
 194 .IP \[bu]
 195 mlbhitsleadersxml.dtd
 196 .IP \[bu]
 197 mlbhomerunsxml.dtd
 198 .IP \[bu]
 199 MLBHRFreqXML.dtd
 200 .IP \[bu]
 201 MLBIntWalksXML.dtd
 202 .IP \[bu]
 203 MLBKORateXML.dtd
 204 .IP \[bu]
 205 mlbonbasepctxml.dtd
 206 .IP \[bu]
 207 MLBOPSXML.dtd
 208 .IP \[bu]
 209 MLBPlateAppsXML.dtd
 210 .IP \[bu]
 211 mlbrbisxml.dtd
 212 .IP \[bu]
 213 mlbrunsleadersxml.dtd
 214 .IP \[bu]
 215 MLBSacFliesXML.dtd
 216 .IP \[bu]
 217 MLBSacrificesXML.dtd
 218 .IP \[bu]
 219 MLBSBSuccessXML.dtd
 220 .IP \[bu]
 221 mlbsluggingpctxml.dtd
 222 .IP \[bu]
 223 mlbstandxml.dtd
 224 .IP \[bu]
 225 mlbstandxml_preseason.dtd
 226 .IP \[bu]
 227 mlbstolenbasexml.dtd
 228 .IP \[bu]
 229 mlbtotalbasesleadersxml.dtd
 230 .IP \[bu]
 231 mlbtriplesleadersxml.dtd
 232 .IP \[bu]
 233 MLBWalkRateXML.dtd
 234 .IP \[bu]
 235 mlbwalksleadersxml.dtd
 236 .IP \[bu]
 237 MLBXtraBaseHitsXML.dtd
 238 .IP \[bu]
 239 MLB_Pitching_Appearances_Leaders.dtd
 240 .IP \[bu]
 241 MLB_ERA_Leaders.dtd
 242 .IP \[bu]
 243 MLB_Pitching_Balks_Leaders.dtd
 244 .IP \[bu]
 245 MLB_Pitching_CG_Leaders.dtd
 246 .IP \[bu]
 247 MLB_Pitching_ER_Allowed_Leaders.dtd
 248 .IP \[bu]
 249 MLB_Pitching_Hits_Allowed_Leaders.dtd
 250 .IP \[bu]
 251 MLB_Pitching_Hit_Batters_Leaders.dtd
 252 .IP \[bu]
 253 MLB_Pitching_HR_Allowed_Leaders.dtd
 254 .IP \[bu]
 255 MLB_Pitching_IP_Leaders.dtd
 256 .IP \[bu]
 257 MLB_Pitching_Runs_Allowed_Leaders.dtd
 258 .IP \[bu]
 259 MLB_Pitching_Saves_Leaders.dtd
 260 .IP \[bu]
 261 MLB_Pitching_Shut_Outs_Leaders.dtd
 262 .IP \[bu]
 263 MLB_Pitching_Starts_Leaders.dtd
 264 .IP \[bu]
 265 MLB_Pitching_Strike_Outs_Leaders.dtd
 266 .IP \[bu]
 267 MLB_Pitching_Walks_Leaders.dtd
 268 .IP \[bu]
 269 MLB_Pitching_WHIP_Leaders.dtd
 270 .IP \[bu]
 271 MLB_Pitching_Wild_Pitches_Leaders.dtd
 272 .IP \[bu]
 273 MLB_Pitching_Win_Percentage_Leaders.dtd
 274 .IP \[bu]
 275 MLB_Pitching_WL_Leaders.dtd
 276 .IP \[bu]
 277 NBA_Team_Stats_XML.dtd
 278 .IP \[bu]
 279 NBA3PPctXML.dtd
 280 .IP \[bu]
 281 NBAAssistsXML.dtd
 282 .IP \[bu]
 283 NBABlocksXML.dtd
 284 .IP \[bu]
 285 nbaconfrecxml.dtd
 286 .IP \[bu]
 287 nbadaysxml.dtd
 288 .IP \[bu]
 289 nbadivisionsxml.dtd
 290 .IP \[bu]
 291 NBAFGPctXML.dtd
 292 .IP \[bu]
 293 NBAFoulsXML.dtd
 294 .IP \[bu]
 295 NBAFTPctXML.dtd
 296 .IP \[bu]
 297 NBAMinutesXML.dtd
 298 .IP \[bu]
 299 NBAReboundsXML.dtd
 300 .IP \[bu]
 301 NBAScorersXML.dtd
 302 .IP \[bu]
 303 nbastandxml.dtd
 304 .IP \[bu]
 305 NBAStealsXML.dtd
 306 .IP \[bu]
 307 nbateamleadersxml.dtd
 308 .IP \[bu]
 309 nbatripledoublexml.dtd
 310 .IP \[bu]
 311 NBATurnoversXML.dtd
 312 .IP \[bu]
 313 NCAA_Conference_Schedule_XML.dtd
 314 .IP \[bu]
 315 nflfirstdownxml.dtd
 316 .IP \[bu]
 317 NFLFumbleLeaderXML.dtd
 318 .IP \[bu]
 319 NFLGiveTakeXML.dtd
 320 .IP \[bu]
 321 NFLInside20XML.dtd
 322 .IP \[bu]
 323 NFLKickoffsXML.dtd
 324 .IP \[bu]
 325 NFLMondayNightXML.dtd
 326 .IP \[bu]
 327 NFLPassLeadXML.dtd
 328 .IP \[bu]
 329 NFLQBStartsXML.dtd
 330 .IP \[bu]
 331 NFLSackLeadersXML.dtd
 332 .IP \[bu]
 333 nflstandxml.dtd
 334 .IP \[bu]
 335 NFLTeamRankingsXML.dtd
 336 .IP \[bu]
 337 NFLTopPerformanceXML.dtd
 338 .IP \[bu]
 339 NFLTotalYardageXML.dtd
 340 .IP \[bu]
 341 NFL_KickingLeaders_XML.dtd
 342 .IP \[bu]
 343 NFL_NBA_Draft_XML.dtd
 344 .IP \[bu]
 345 NFL_Roster_XML.dtd
 346 .IP \[bu]
 347 NFL_Team_Stats_XML.dtd
 348 .IP \[bu]
 349 Transactions_XML.dtd
 350 .IP \[bu]
 351 Weekly_Sched_XML.dtd
 352 .IP \[bu]
 353 WNBA_Team_Leaders_XML.dtd
 354 .IP \[bu]
 355 WNBA3PPctXML.dtd
 356 .IP \[bu]
 357 WNBAAssistsXML.dtd
 358 .IP \[bu]
 359 WNBABlocksXML.dtd
 360 .IP \[bu]
 361 WNBAFGPctXML.dtd
 362 .IP \[bu]
 363 WNBAFoulsXML.dtd
 364 .IP \[bu]
 365 WNBAFTPctXML.dtd
 366 .IP \[bu]
 367 WNBAMinutesXML.dtd
 368 .IP \[bu]
 369 WNBAReboundsXML.dtd
 370 .IP \[bu]
 371 WNBAScorersXML.dtd
 372 .IP \[bu]
 373 wnbastandxml.dtd
 374 .IP \[bu]
 375 WNBAStealsXML.dtd
 376 .IP \[bu]
 377 WNBATurnoversXML.dtd
 378 .RE
 379 .P
 380 The GameInfo and SportInfo types do not have their own top-level
 381 tables in the database. Instead, their raw XML is stored in either the
 382 \(dqgame_info\(dq or \(dqsport_info\(dq table respectively.
 383
 384 .SH DATABASE SCHEMA
 385 .P
 386 At the top level (with two notable exceptions), we have one table for
 387 each of the XML document types that we import. For example, the
 388 documents corresponding to \fInewsxml.dtd\fR will have a table called
 389 \(dqnews\(dq. All top-level tables contain two important fields,
 390 \(dqxml_file_id\(dq and \(dqtime_stamp\(dq. The former is unique and
 391 prevents us from inserting the same data twice. The time stamp on the
 392 other hand lets us know when the data is old and can be removed. The
 393 database schema make it possible to delete only the outdated top-level
 394 records; all transient children should be removed by triggers.
 395 .P
 396 These top-level tables will often have children. For example, each
 397 news item has zero or more locations associated with it. The child
 398 table will be named <parent>_<children>, which in this case
 399 corresponds to \(dqnews_locations\(dq.
 400 .P
 401 To relate the two, a third table may exist with name
 402 <parent>__<child>. Note the two underscores. This prevents ambiguity
 403 when the child table itself contains underscores. The table joining
 404 \(dqnews\(dq with \(dqnews_locations\(dq is thus called
 405 \(dqnews__news_locations\(dq. This is necessary when the child table
 406 has a unique constraint; we don't want to blindly insert duplicate
 407 records keyed to the parent. Instead we'd like to use the third table
 408 to map an existing child to the new parent.
 409 .P
 410 Where it makes sense, children are kept unique to prevent pointless
 411 duplication. This slows down inserts, and speeds up reads (which are
 412 much more frequent). There is a tradeoff to be made, however. For a
 413 table with a small, fixed upper bound on the number of rows (like
 414 \(dqodds_casinos\(dq), there is great benefit to de-duplication. The
 415 total number of rows stays small, so inserts are still quick, and many
 416 duplicate rows are eliminated.
 417 .P
 418 But, with a table like \(dqodds_games\(dq, the number of games grows
 419 quickly and without bound. It is therefore more beneficial to be able
 420 to delete the old games (through an ON DELETE CASCADE, tied to
 421 \(dqodds\(dq) than it is to eliminate duplication. A table like
 422 \(dqnews_locations\(dq is somewhere in-between. It is hoped that the
 423 unique constraint in the top-level table's \(dqxml_file_id\(dq will
 424 prevent duplication in this case anyway.
 425 .P
 426 The aforementioned exceptions are the \(dqgame_info\(dq and
 427 \(dqsport_info\(dq tables. These tables contain the raw XML for a
 428 number of DTDs that are not handled individually. This is partially
 429 for backwards-compatibility with a legacy implementation, but is
 430 mostly a stopgap due to a lack of resources at the moment. These two
 431 tables (game_info and sport_info) still possess timestamps that allow
 432 us to prune old data.
 433 .P
 434 UML diagrams of the resulting database schema for each XML document
 435 type are provided with the \fBhtsn-import\fR documentation.
 436
 437 .SH XML Schema Oddities
 438 .P
 439 There are a number of problems with the XML on the wire. Even if we
 440 construct the DTDs ourselves, the results are sometimes
 441 inconsistent. Here we document a few of them.
 442
 443 .IP \[bu] 2
 444 Odds_XML.dtd
 445
 446 The <Notes> elements here are supposed to be associated with a set of
 447 <Game> elements, but since the pair
 448 (<Notes>...</Notes><Game>...</Game>) can appear zero or more times,
 449 this leads to ambiguity in parsing. We therefore ignore the notes
 450 entirely (although a hack is employed to facilitate parsing).
 451
 452 .IP \[bu]
 453 weatherxml.dtd
 454
 455 There appear to be two types of weather documents; the first has
 456 <listing> contained within <forecast> and the second has <forecast>
 457 contained within <listing>. While it would be possible to parse both,
 458 it would greatly complicate things. The first form is more common, so
 459 that's all we support for now.
 460
 461 .SH OPTIONS
 462
 463 .IP \fB\-\-backend\fR,\ \fB\-b\fR
 464 The RDBMS backend to use. Valid choices are \fISqlite\fR and
 465 \fIPostgres\fR. Capitalization is important, sorry.
 466
 467 Default: Sqlite
 468
 469 .IP \fB\-\-connection-string\fR,\ \fB\-c\fR
 470 The connection string used for connecting to the database backend
 471 given by the \fB\-\-backend\fR option. The default is appropriate for
 472 the \fISqlite\fR backend.
 473
 474 Default: \(dq:memory:\(dq
 475
 476 .IP \fB\-\-log-file\fR
 477 If you specify a file here, logs will be written to it (possibly in
 478 addition to syslog). Can be either a relative or absolute path. It
 479 will not be auto-rotated; use something like logrotate for that.
 480
 481 Default: none
 482
 483 .IP \fB\-\-log-level\fR
 484 How verbose should the logs be? We log notifications at four levels:
 485 DEBUG, INFO, WARN, and ERROR. Specify the \(dqmost boring\(dq level of
 486 notifications you would like to receive (in all-caps); more
 487 interesting notifications will be logged as well. The debug output is
 488 extremely verbose and will not be written to syslog even if you try.
 489
 490 Default: INFO
 491
 492 .IP \fB\-\-remove\fR,\ \fB\-r\fR
 493 Remove successfully processed files. If you enable this, you can see
 494 at a glance which XML files are not being processed, because they're
 495 all that should be left.
 496
 497 Default: disabled
 498
 499 .IP \fB\-\-syslog\fR,\ \fB\-s\fR
 500 Enable logging to syslog. On Windows this will attempt to communicate
 501 (over UDP) with a syslog daemon on localhost, which will most likely
 502 not work.
 503
 504 Default: disabled
 505
 506 .SH CONFIGURATION FILE
 507 .P
 508 Any of the command-line options mentioned above can be specified in a
 509 configuration file instead. We first look for \(dqhtsn-importrc\(dq in
 510 the system configuration directory. We then look for a file named
 511 \(dq.htsn-importrc\(dq in the user's home directory. The latter will
 512 override the former.
 513 .P
 514 The user's home directory is simply $HOME on Unix; on Windows it's
 515 wherever %APPDATA% points. The system configuration directory is
 516 determined by Cabal; the \(dqsysconfdir\(dq parameter during the
 517 \(dqconfigure\(dq step is used.
 518 .P
 519 The file's syntax is given by examples in the htsn-importrc.example file
 520 (included with \fBhtsn-import\fR).
 521 .P
 522 Options specified on the command-line override those in either
 523 configuration file.
 524
 525 .SH EXAMPLES
 526 .IP \[bu] 2
 527 Import newsxml.xml into a preexisting sqlite database named \(dqfoo.sqlite3\(dq:
 528
 529 .nf
 530 .I $ htsn-import --connection-string='foo.sqlite3' \\\\
 531 .I "              test/xml/newsxml.xml"
 532 Successfully imported test/xml/newsxml.xml.
 533 Imported 1 document(s) total.
 534 .fi
 535 .IP \[bu]
 536 Repeat the previous example, but delete newsxml.xml afterwards:
 537
 538 .nf
 539 .I $ htsn-import --connection-string='foo.sqlite3' \\\\
 540 .I "              --remove test/xml/newsxml.xml"
 541 Successfully imported test/xml/newsxml.xml.
 542 Imported 1 document(s) total.
 543 Removed processed file test/xml/newsxml.xml.
 544 .fi
 545 .IP \[bu]
 546 Use a Postgres database instead of the default Sqlite. This assumes
 547 that you have a database named \(dqhtsn\(dq accessible to user
 548 \(dqpostgres\(dq locally:
 549
 550 .nf
 551 .I $ htsn-import --connection-string='dbname=htsn user=postgres' \\\\
 552 .I "              --backend=Postgres test/xml/newsxml.xml"
 553 Successfully imported test/xml/newsxml.xml.
 554 Imported 1 document(s) total.
 555 .fi
 556
 557 .SH BUGS
 558
 559 .P
 560 Send bugs to michael@orlitzky.com.