読者です 読者をやめる 読者になる 読者になる

やってみる

アウトプットすべく己を導くためのブログ。その試行錯誤すらたれ流す。

リポジトリDBを作成する

ワークフロー プログラミング

GitHubリモートリポジトリ用のデータベースを作成する。気にしたことを書きなぐる。

成果物

GitHubGitHub.Repositories.Database.Create

目的

GitHubリポジトリのリンク集を作ること。その他の細かい点はあくまで理想。できなくていい。

リポジトリ一覧を作成する

分析する

  • 総数
  • 平均
  • 比率
    • 書いた言語ごとのByte比率
  • 最近の傾向
    • 作成頻度
    • commit頻度
    • 言語の傾向

データベース

リポジトリ生成APIの結果を登録する。

ユーザ

  • GitHub.Repositories.sqlite3
  • GitHub.Repositories.{username}.sqlite3

GitHubユーザごとにDBを分けるか、複数ユーザのリポジトリを1つのDBに登録するか。

private

privateリポジトリか否かのデータを持たせるかどうか。

  • privateリポジトリの作成は有料である
  • privateリポジトリの作成にはrepo権限が必要である(public_repo権限では作成できない)

publicだけにしたほうが楽そう。

カウント

  • "forks_count": 9,
  • "stargazers_count": 80,
  • "watchers_count": 80,
  • "open_issues_count": 0,

別DB、別テーブルにするか否か。

  • GitHub.Repositories.sqlite3Countsテーブル

日付

  • "pushed_at": "2011-01-26T19:06:43Z",
  • "created_at": "2011-01-26T19:01:12Z",
  • "updated_at": "2011-01-26T19:14:43Z",

GitHubサーバと整合性を保つために必要か。別途、API取得最終日時も必要か。

プログラミング言語とByte数

List languages APIの結果を登録するか否か。別DB、別テーブルにするか否か。

usernameとリポジトリ名が必要。

GET /repos/:owner/:repo/languages

リレーショナルDB形式にするか否か。

{
  "C": 78769,
  "Python": 7769
}
  • GitHub.Repositories.sqlite3Languagesテーブル

テーブル

テーブル名 説明
Repositories リモートリポジトリ情報
Counts フォーク、スター、ウォッチ、イシュー数
Languages プログラミング言語とそのByte数

親子関係

  • Repositories
    • Counts
    • Languages

Repositories(複数ユーザ版)

列名 Key Unique NotNull 説明
Id integer P - - レコードを一意に特定する主キー
AccountId integer F - - GitHub.Accounts.sqlite3DBのAccounts.Id
IdOnGitHub integer - GitHub上でのリポジトリ特定キー
Name text - - リポジトリ
Description text - - - リポジトリ説明
Homepage text - - - リポジトリ関連URL
CreatedAt text - - リポジトリ作成日時
PushedAt text - - 最終Push日時
UpdatedAt text - - 最終更新日時
CheckedAt text - - 本DB最終更新日時

privateか否かの情報は削る(publicリポジトリしか扱わない)

  • privateリポジトリを作成する予定がないため
  • 実装を楽にするため

CheckedAt

APIにて情報取得した日時をyyyy-MM-dd HH:mm:ss形式で記録する。

List your repositories APIを叩いた結果、返却されたupdated_atの日時がCheckedAtより新しい場合、DB情報が古いことになる。そのときはDBを更新する必要がある。DB情報を更新するときは、Counts, Languagesの各種テーブルも同時に行うこと。

ただ、定期的に更新されていないか確認するより、Pushするたびに毎回DBも同時に更新するようにしたほうが楽。GitHubサーバ負荷も少なく済むし、更新確認処理もせずに済む。もし定期的に更新確認しないならCheckedAt列は不要。

はたしてCheckedAt列は必要か。

Repositories(1ユーザ版)

列名 Key Unique NotNull 説明
Id integer P - - レコードを一意に特定する主キー
IdOnGitHub integer - GitHub上でのリポジトリ特定キー
Name text - リポジトリ
Description text - - - リポジトリ説明
Homepage text - - - リポジトリ関連URL
CreatedAt text - - リポジトリ作成日時
PushedAt text - - 最終Push日時
UpdatedAt text - - 最終更新日時
CheckedAt text - - 本DB最終更新日時

1ユーザごとにDBを分ける

  • 普通はユーザ単位で分ける
    • select文の発行が最小限に抑えられる
  • AccountId列を不要にできる
  • Nameでも一意に特定できる

DB接続時にファイル名(ユーザ名)で選択する必要がある。ユーザごとにDBファイルが増えることになる。

Counts

列名 Key Unique NotNull 説明
Id integer P - - レコードを一意に特定する主キー
RepositoryId integer F - Repositories.Id
Forks integer - - フォーク数
Stargazers integer - - スター数
Watchers integer - - 閲覧者数
Issues integer - - イシュー数

判断

Repositoriesテーブルと分離すべきか統合すべきか。

分離すべき
  • メモリ浪費軽減
    • リポジトリへのリンク集を作るだけならCountsの情報は不要
    • 全部ゼロ
      • 各種カウントはゼロになるだろう
        • 他人にとって価値あるリポジトリを作成していない(できない)
      • 各種カウントはゼロだろうから集計などに利用しない
        • 最悪、登録しなくてもいい
        • 優先度が低い列を同一テーブルに含めたくない
  • 更新される契機や頻度が異なる
    • Descriptionなどは作者の都合で変更するが、forks_countなどは作者以外のユーザの都合で変更される
      • 外部の都合により高頻度で更新する情報は別テーブルにしたほうが負荷が少なくて済みそう
統合すべき
  • API結果(json)も統合された状態である
  • 実行速度が遅くなる
    • SQLが2回発行される
      • リポジトリ作成や更新時、いちいち2テーブルに分けて挿入、更新せねばならない
  • ファイルサイズが無駄に増える
    • 2テーブルに分けるため、Idなどのキー情報が重複する

Languages

列名 Key Unique NotNull 説明
Id integer P - - レコードを一意に特定する主キー
RepositoryId integer F - Repositories.Id
Language text - - プログラミング言語
Size integer - - Byte数

複合主キー

本来はRepositoryId, Languageの複合主キーにすべきかもしれない。同一リポジトリで使用した言語名が重複することはないから。つまり、リポジトリIDとプログラミング言語の2つを指定することで、レコードを一意に特定できる形式にすべきかもしれない。

現状、以下のようなレコードを作成できてしまう。

Id RepositoryId Language Size
0 0 python 100
1 0 python 123

リポジトリ0のPython言語におけるByte数が100なのか123なのかわからない。本来はどちらか一つだけのはずである。

本来ならunique制約をつけたいところ。Languageリポジトリ内でuniqueである。しかし、現状では複数リポジトリの言語を格納しているため、uniqueにできない。

リポジトリを個別のテーブルにする方法もある。以下のような列になる。

列名 Key Unique NotNull 説明
Id integer P - -
Language text - プログラミング言語
Size integer - -

これならunique制約を付与できる。しかし、リポジトリ名は動的に生成されるし、数も膨大である。同じ型で、名前が異なる、レコードが1行だけのテーブルが、大量にできてしまう。それはリレーショナルDBの使い方として間違っている気がする。同一の型なら同一テーブル内の1レコードとして扱うもののはず。

よってリポジトリごとにテーブルを作成する形式にはしづらい。その結果、同一リポジトリなのに同一プログラミング言語レコードを複数挿入できてしまうガバガバなテーブル構成になってしまった。ひどすぎる。どう解決すればいいかわからない。

こちらが参考になりそうだが、私には難しい。

WHERE

言語とByte数をRepositoriesテーブルの列に含めて1レコードで表現することも可能である。

列名
Languages python,csharp
Sizes 500,300

Languages, Sizes列の値にそれぞれカンマ区切りで複数データを入れる。

ただしその場合、Size < 100のようなSQL条件式で絞り込むことができない。文字列分解→数値化→比較の経緯をプログラミングで実装する必要がある。

指定したプログラミング言語の有無だけなら、以下のようにLIKE句で絞り込める。

  • (',' || Languages || ',') LIKE '%,Python,%'
  • (',' || Languages || ',') LIKE '%,Python,% AND (',' || Languages || ',') LIKE '%,CSharp,%
  • (',' || Languages || ',') LIKE '%,Python,% OR (',' || Languages || ',') LIKE '%,CSharp,%

ORDER BY

1リポジトリ複数レコードの言語にすれば、order byが楽になる。

ORDER BY Size ASCとすれば、サイズの多いプログラミング言語順に並び替えて取得できる。複数言語がある場合、1番目がメイン言語と想定することができる。

じつは1番目だけなら、List your repositories APIjson結果にあるlanguageキーで取得できる。よって、メイン言語を取得したいだけなら、RepositoriesテーブルにLanguage列を追加するだけでいい。

判断

Repositoriesテーブルと分離すべきか統合すべきか。

  • 統合する:メイン言語のみ(size情報、言語情報を保持しない)
  • 統合する:カンマ区切り(Size < 100等のSQL絞込みできないorプログラミングによる実装)
  • 分離する:複数レコードにする(別テーブルになるのでテーブル結合などSELECT発行が煩雑になる)

結論

GitHubGitHub.Repositories.Database.Createは以下のとおりにした。

  • Repositoriesは1ユーザ版にする
  • Counts, Languagesは別テーブルにする
  • Repositories.CheckedAtは念のため作成しておいた

所感

データベースが難しい。今回は仮に決めておく。

外部キーは今までNotNull制約していなかったが、すべき。