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

成果物

GitHub GitHub.Repositories.Database.Create

目的

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

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

絞込み
- ユーザ
- プログラミング言語
- License
全文検索

分析する

総数
- リポジトリ総数
- コードByte数
- 言語ごとのコードByte数
平均
- 開始日からの1日平均作成リポジトリ数
比率
- 書いた言語ごとの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.sqlite3のCountsテーブル

日付

"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.sqlite3のLanguagesテーブル

テーブル

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

親子関係

Repositories
- Counts
- Languages

Repositories(複数ユーザ版)

列名	型	Key	Unique	NotNull	説明
Id	integer	P	-	-	レコードを一意に特定する主キー
AccountId	integer	F	-	-	`GitHub.Accounts.sqlite3`DBの`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 APIのjson結果にあるlanguageキーで取得できる。よって、メイン言語を取得したいだけなら、RepositoriesテーブルにLanguage列を追加するだけでいい。

判断