■

ひょんなことから自宅のデータベースに日本株の全銘柄をつっこんで色々いじくる必要がでてきました。

RubyのActiveRecordで瞬殺しようかとも思ったのですが、我が家の超低スペックサーバーでは富豪的にどうこうするのは難しかろうと考え、OCamlで清貧にやってみることに。

で、DB周りのライブラリを探したのだけど、なかなか見つからん…

どうも最近はPG'OCamlというのが良さそうだけど、MySQLを使いたいので却下。

OCaml-DBIはaptで入らない（このためにGODIを入れるのも…）のと、メンテナの人（Richard Jones、鯉のBlogの人？メールクライアントにMuttを使っているっぽいのが素晴らしい）の飽きちゃってる感が辛いので却下。

というわけで、泣く泣くOCaml-MySQLで地道にやる方針に決定しました。

とりあえず、CSVファイルからDBに突っ込むものをやっつけで作ったので、Web上のサンプルに（なんか丁度良いのが全然見つからなかったので）なれば幸いと以下に張り付け。

open Printf

let host = ref ""
let port = ref 3306
let user = ref ""
let password = ref ""
let database = ref ""
let csv_dir = ref ""
let command = ref ""

let connect_db () =
  Mysql.quick_connect
    ~host:!host ~port:!port ~user:!user
    ~password:!password ~database:!database ()

let find_or_create_stock db code =
  let result =
    Mysql.exec db
      (sprintf "SELECT id FROM stocks WHERE code = %d" code)
  in
  match Mysql.fetch result with
  | Some str_opt_arr -> (
      match Mysql.column result ~key:"id" ~row:str_opt_arr with
      | Some s -> int_of_string s
      | None -> failwith "find_or_create_stock: no id"
  )
  | None ->
      ignore (
        Mysql.exec db
          (sprintf "INSERT INTO stocks (code) VALUES (%d)" code)
      );
      int_of_string (Mysql.ml642int (Mysql.insert_id db))

let update_or_insert_price db stock_id y m d o t b c v =
  let traded_on = "'" ^ Mysql.ml2date (y, m, d) ^ "'" in
  let result =
    Mysql.exec db
      (sprintf
        "SELECT id, open, top, bottom, close, volume
         FROM prices WHERE stock_id = %d AND traded_on = %s"
        stock_id traded_on)
  in
  match Mysql.fetch result with
  | Some str_opt_arr -> (
    if List.for_all (
      fun (k, v) ->
        match Mysql.column result ~key:k ~row:str_opt_arr with
        | Some x -> int_of_string x = v
        | None -> false
    ) [("open", o); ("top", t); ("bottom", b); ("close", c); ("volume" ,v)]
    then ()
    else
      ignore (
        Mysql.exec db
        (sprintf
        "UPDATE prices
         SET open = %d AND top = %d AND bottom = %d
         AND close = %d AND volume = %d
         WHERE stock_id = %d AND traded_on = %s" o t b c v stock_id traded_on)
      )
  )
  | None ->
      ignore (
        Mysql.exec db
        (sprintf
        "INSERT INTO prices
           (stock_id, traded_on, open, top, bottom, close, volume)
         VALUES (%d, %s, %d, %d, %d, %d, %d)" stock_id traded_on o t b c v)
      )

let import_prices_from_channel db ic stock_id =
  let rec loop () =
    try
      let y, m, d, o, t, b, c, v =
        Scanf.fscanf ic "%d,%d,%d,%d,%d,%d,%d,%d,%s\n"
          (fun y m d o t b c v dummy -> (y, m, d, o, t, b, c, v))
      in
      update_or_insert_price db stock_id y m d o t b c v;
      loop ()
    with End_of_file -> ()
  in
  ignore (Mysql.exec db "START TRANSACTION");
  loop ();
  ignore (Mysql.exec db "COMMIT")

let import db csv_dir =
  let rec loop i n =
    if i > n then ()
    else
      let path = sprintf "%s/%04d.csv" csv_dir i in
      ignore (
        try
          let ic = open_in path in
          let stock_id = find_or_create_stock db i in
          import_prices_from_channel db ic stock_id;
          close_in ic
        with Sys_error e -> ()
      );
      loop (i + 1) n
  in
  loop 1000 9999

let _ =
  let arg_spec =
    ["-h", Arg.String (fun s -> host := s), "Database host";
     "--port", Arg.Int (fun i -> port := i), "Database port";
     "-u", Arg.String (fun s -> user := s), "Database user";
     "-p", Arg.String (fun s -> password := s), "Database password";
     "-d", Arg.String (fun s -> database := s), "Database";
     "--csvdir", Arg.String (fun s -> csv_dir := s), "CSV files dir"
    ]
  in
  let usage =
    "Usage: jp_stock [options]\nOptions are:" in
  Arg.parse arg_spec (fun _ -> ()) usage;
  let db = connect_db () in
  import db !csv_dir

正直、Mysql.ml642intという関数名にはちょっとひるんだ。

あ、DBのテーブルはこんな感じで。

CREATE TABLE stocks (
  id INT(11) NOT NULL AUTO_INCREMENT,
  code VARCHAR(10) NOT NULL,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE idx_stocks_code (code)
) TYPE = InnoDB;

CREATE TABLE prices (
  id INT(11) NOT NULL AUTO_INCREMENT,
  stock_id INT(11) NOT NULL,
  traded_on date,
  open INT(11),
  close INT(11),
  top INT(11),
  bottom INT(11),
  volume INT(15),
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  FOREIGN KEY (stock_id) REFERENCES stocks (id) ON DELETE CASCADE
) TYPE = InnoDB;