F#: MySQLへのアクセス、XMLの出力

a0002_009631

以前から遊んでいたF#の話。
目標としては、JRA-VAN DataLab.から取得したデータを元にこの形式のページを作ること。

まず、JRA-VAN DataLab.からデータを取得するのはPC-KEIBAでOK。
PC-KEIBAで取得したMySQL上のデータを使って必要な情報を抽出する部分をF#でプログラムして作ろうとしている次第。

まず1つ目のソース。1頭の1レース分の情報を持つクラス。

module SharedHorseInfoPrinter.HorseRaceInfo

type HorseRaceInfo(kaisainengappi,keibajo,name,joken,grade,track,kyori,kishumei,futan,chakujun,ninki,tosu,odds,time,agari3f,shiba,dirt,aite,timesa) =
    member val Kaisainengappi : System.DateTime = kaisainengappi
    member val Keibajo : string = keibajo
    member val Name : string = name
    member val Joken : string = joken
    member val Grade : string = grade
    member val Track : string = track
    member val Kyori : int16 = kyori
    member val Kishumei : string = kishumei
    member val Futan : decimal = futan
    member val Chakujun : int16 = chakujun
    member val Ninki : int16 = ninki
    member val Tosu : int16 = tosu
    member val Odds : decimal = odds
    member val Time : decimal = time
    member val Agari3F : decimal = agari3f
    member val Shiba : string = shiba
    member val Dirt : string = dirt
    member val Aite : string = aite
    member val Timesa : decimal = timesa

次に馬1頭分の情報を持つクラス。

module SharedHorseInfoPrinter.HorseInfo

open SharedHorseInfoPrinter.HorseRaceInfo;

type HorseInfo(kettoTorokuBango,bamei,seibetsu,moshoku,seinengappi,shozoku,chokyoshi,sire,broodmare,bms,race) =
    member val KettoTorokuBango : string = kettoTorokuBango
    member val Bamei : string = bamei
    member val Seibetsu : string = seibetsu
    member val Moshoku : string = moshoku
    member val Seinengappi : System.DateTime = seinengappi
    member val Shozoku : string = shozoku
    member val Chokyoshi : string = chokyoshi
    member val Sire : string = sire
    member val Broodmare : string = broodmare
    member val BMS : string = bms
    member val Race : list<HorseRaceInfo> = race

RDBMSから情報を取得して、上記HorseRaceInfo、HorseInfoを生成するクラス。SQLダラダラなのとreaderからの取得箇所がダサいのがなんとかならないかなぁ…

module SharedHorseInfoPrinter.HorseInfoCreator

open System.Data.Common;
open SharedHorseInfoPrinter.HorseRaceInfo;
open SharedHorseInfoPrinter.HorseInfo;

type HorseInfoCreator(conn : DbConnection) =
    let getCommand (conn : DbConnection) sql paramName =
        let command = conn.CreateCommand()
        command.CommandText <- sql
        let param = command.CreateParameter()
        param.ParameterName <- paramName
        ignore (command.Parameters.Add(param))
        command.Prepare()
        command

    let paramName = @"KettoTorokuBango"

    let sqlHorse =
        @"select ketto_toroku_bango,bamei,b.content,c.content," +
        @"seinengappi,d.content,chokyoshimei_ryakusho,ketto1_bamei," +
        @"ketto2_bamei,ketto5_bamei " +
        @"from jvd_kyosoba_master a,jvd_seibetsu_code b," +
        @"jvd_moshoku_code c,jvd_tozai_shozoku_code d " +
        @"where ketto_toroku_bango=@KettoTorokuBango " +
        @"and a.seibetsu_code=b.code " +
        @"and a.moshoku_code=c.code " +
        @"and a.tozai_shozoku_code=d.code"

    let sqlRace =
        @"select a.kaisai_nengappi,b.content,c.kyosomei_ryakusho_6," +
        @"d.content,e.content,f.content,c.kyori,a.kishumei_ryakusho," +
        @"a.futan_juryo,a.kakutei_chakujun,a.tansho_ninkijun,c.shusso_tosu," +
        @"a.tansho_odds,a.soha_time,a.kohan_3f,g.content,h.content," +
        @"a.aite1_bamei,a.time_sa " +
        @"from jvd_umagoto_race_joho a,jvd_keibajo_code b," +
        @"jvd_race_shosai c,jvd_kyoso_joken_code d,jvd_grade_code e," +
        @"jvd_track_code f,jvd_babajotai_code g,jvd_babajotai_code h " +
        @"where ketto_toroku_bango=@KettoTorokuBango " +
        @"and c.data_kubun in ('7','A','B') " +
        @"and a.keibajo_code=b.code " +
        @"and a.race_code=c.race_code " +
        @"and c.kyoso_joken_code_saijakunen=d.code " +
        @"and c.grade_code=e.code " +
        @"and c.track_code=f.code " +
        @"and c.shiba_babajotai_code=g.code " +
        @"and c.dirt_babajotai_code=h.code " +
        @"order by kaisai_nengappi desc"

    let commandHorse = getCommand conn sqlHorse paramName
    let commandRace = getCommand conn sqlRace paramName

    let rec createRaceInfoList (reader : DbDataReader) =
        if reader.Read() then
            HorseRaceInfo(reader.GetDateTime(0),reader.GetString(1),
                reader.GetString(2),reader.GetString(3),reader.GetString(4),
                reader.GetString(5),reader.GetInt16(6),reader.GetString(7),
                reader.GetDecimal(8),reader.GetInt16(9),reader.GetInt16(10),
                reader.GetInt16(11),reader.GetDecimal(12),
                reader.GetDecimal(13),reader.GetDecimal(14),
                reader.GetString(15),reader.GetString(16),
                reader.GetString(17),reader.GetDecimal(18))
                :: createRaceInfoList reader
        else
            []

    let createRaceInfoList kettoTorokuBango =
        commandRace.Parameters.Item(paramName).Value <- kettoTorokuBango
        use reader = commandRace.ExecuteReader()
        createRaceInfoList reader

    member HorseInfoCreator.createHorseInfo kettoTorokuBango =
        let raceInfoList = createRaceInfoList kettoTorokuBango
        commandHorse.Parameters.Item(paramName).Value <- kettoTorokuBango
        use reader = commandHorse.ExecuteReader()
        if reader.Read()
        then
            (Some (
                HorseInfo(
                    reader.GetString(0),reader.GetString(1),reader.GetString(2),
                    reader.GetString(3),reader.GetDateTime(4),reader.GetString(5),
                    reader.GetString(6),reader.GetString(7),reader.GetString(8),
                    reader.GetString(9),raceInfoList)))
        else
            None

最後にエントリポイントとXML出力部分。
※このコードを使うと妙なエンコーディングで出力されるので、この記事にて修正版ソースを記載しています。

module SharedHorseInfoPrinter.Main

open System.Xml;

open SharedHorseInfoPrinter.HorseRaceInfo;
open SharedHorseInfoPrinter.HorseInfo;
open SharedHorseInfoPrinter.HorseInfoCreator;

let getHorseInfoArray conn kettoTorokuBango =
    let creator = HorseInfoCreator conn
    Seq.map creator.createHorseInfo kettoTorokuBango

let getDateString (x : System.DateTime) = x.ToString(@"yyyy-MM-dd")

let getHorseInfoXml horseInfoArray =
    use strWriter = new System.IO.StringWriter()
    use writer = new XmlTextWriter(strWriter)
    writer.Formatting <- Formatting.Indented
    writer.WriteStartDocument()
    writer.WriteStartElement(@"HorseList")
    Seq.iter (fun (x : HorseInfo option) ->
        match x with
        | Some y ->
            writer.WriteStartElement(@"Horse")
            writer.WriteAttributeString(@"KettoTorokuBango", y.KettoTorokuBango)
            writer.WriteAttributeString(@"Bamei", y.Bamei)
            writer.WriteAttributeString(@"Seibetsu", y.Seibetsu)
            writer.WriteAttributeString(@"Moshoku", y.Moshoku)
            writer.WriteAttributeString(@"Seinengappi", getDateString y.Seinengappi)
            writer.WriteAttributeString(@"Shozoku", y.Shozoku)
            writer.WriteAttributeString(@"Chokyoshi", y.Chokyoshi)
            writer.WriteAttributeString(@"Sire", y.Sire)
            writer.WriteAttributeString(@"Broodmare", y.Broodmare)
            writer.WriteAttributeString(@"BMS", y.BMS)
            writer.WriteStartElement(@"Races")
            Seq.iter (fun (z : HorseRaceInfo) ->
                writer.WriteStartElement(@"Race")
                writer.WriteAttributeString(@"Kaisainengappi", getDateString z.Kaisainengappi)
                writer.WriteAttributeString(@"Keibajo", z.Keibajo)
                writer.WriteAttributeString(@"Name", z.Name)
                writer.WriteAttributeString(@"Joken", z.Joken)
                writer.WriteAttributeString(@"Grade", z.Grade)
                writer.WriteAttributeString(@"Track", z.Track)
                writer.WriteAttributeString(@"Kyori", z.Kyori.ToString())
                writer.WriteAttributeString(@"Kishumei", z.Kishumei)
                writer.WriteAttributeString(@"Futan", z.Futan.ToString())
                writer.WriteAttributeString(@"Chakujun", z.Chakujun.ToString())
                writer.WriteAttributeString(@"Ninki", z.Ninki.ToString())
                writer.WriteAttributeString(@"Tosu", z.Tosu.ToString())
                writer.WriteAttributeString(@"Odds", z.Odds.ToString())
                writer.WriteAttributeString(@"Time", z.Time.ToString())
                writer.WriteAttributeString(@"Agari3F", z.Agari3F.ToString())
                writer.WriteAttributeString(@"Shiba", z.Shiba)
                writer.WriteAttributeString(@"Dirt", z.Dirt)
                writer.WriteAttributeString(@"Aite", z.Aite)
                writer.WriteAttributeString(@"Timesa", z.Timesa.ToString())
                writer.WriteEndElement()
                ) y.Race
            writer.WriteEndElement()
            writer.WriteEndElement()
        | None -> ()
    ) horseInfoArray
    writer.WriteEndDocument()
    strWriter.ToString()

[<EntryPoint>]
let main argv =
    use conn = new MySql.Data.MySqlClient.MySqlConnection
                    @"userid=XXX;password=XXX;database=XXX;Host=XXX"
    conn.Open()
    getHorseInfoArray conn argv |> getHorseInfoXml |> printfn "%s"

    0 // 整数の終了コードを返します

これを使うとこんな感じの出力が出ます。

<?xml version="1.0" encoding="utf-16"?>
<HorseList>
  <Horse KettoTorokuBango="2012104639" Bamei="ヴァンガードシチー" Seibetsu="牡" Moshoku="黒鹿毛" Seinengappi="2012-02-16" Shozoku="美浦" Chokyoshi="古賀慎明" Sire="シンボリクリスエス" Broodmare="シャイニングスルー" BMS="スペシャルウィーク">
    <Races>
      <Race Kaisainengappi="2015-03-22" Keibajo="中山" Name="" Joken="未勝利" Grade="" Track="芝・右" Kyori="2000" Kishumei="北村宏司" Futan="56.0" Chakujun="2" Ninki="4" Tosu="16" Odds="4.9" Time="121.2" Agari3F="35.9" Shiba="良" Dirt="" Aite="ウイングチップ" Timesa="0.2" />
      <Race Kaisainengappi="2015-02-21" Keibajo="東京" Name="" Joken="未勝利" Grade="" Track="芝・左" Kyori="1800" Kishumei="後藤浩輝" Futan="56.0" Chakujun="4" Ninki="9" Tosu="16" Odds="15.9" Time="110.8" Agari3F="34.6" Shiba="良" Dirt="" Aite="メローハーモニー" Timesa="0.6" />
      <Race Kaisainengappi="2014-11-29" Keibajo="東京" Name="" Joken="未勝利" Grade="" Track="芝・左" Kyori="2000" Kishumei="後藤浩輝" Futan="55.0" Chakujun="12" Ninki="3" Tosu="16" Odds="7.4" Time="123.5" Agari3F="36.9" Shiba="良" Dirt="" Aite="タンタアレグリア" Timesa="1.5" />
      <Race Kaisainengappi="2014-11-23" Keibajo="東京" Name="" Joken="未勝利" Grade="" Track="芝・左" Kyori="1800" Kishumei="後藤浩輝" Futan="55.0" Chakujun="2" Ninki="1" Tosu="18" Odds="2.2" Time="110.5" Agari3F="34.6" Shiba="良" Dirt="" Aite="キングイマデショウ" Timesa="0.1" />
      <Race Kaisainengappi="2014-10-25" Keibajo="東京" Name="" Joken="新馬" Grade="" Track="芝・左" Kyori="1800" Kishumei="北村宏司" Futan="55.0" Chakujun="3" Ninki="4" Tosu="16" Odds="12.9" Time="110.5" Agari3F="34.4" Shiba="良" Dirt="" Aite="サトノクラウン" Timesa="0.5" />
    </Races>
  </Horse>
  <Horse KettoTorokuBango="2012104647" Bamei="アルギュロス" Seibetsu="牝" Moshoku="黒鹿毛" Seinengappi="2012-03-26" Shozoku="栗東" Chokyoshi="池江泰寿" Sire="マンハッタンカフェ" Broodmare="シルヴァースカヤ" BMS="Silver Hawk">
    <Races />
  </Horse>
</HorseList>

これをXSLTでHTML化する予定。

スポンサーリンク

フォローする

スポンサーリンク