ライセンスのマスターテーブル。

成果物

GitHub GitHub.Licenses.Database.Create.201703140852

開発環境

Linux Mint 17.3 MATE 32bit
SQLite 3.8.2

前回まで

http://ytyaru.hatenablog.com/entry/2017/08/15/000000
http://ytyaru.hatenablog.com/entry/2017/08/16/000000
http://ytyaru.hatenablog.com/entry/2017/08/21/000000

ライセンスでのリポジトリ検索や、ライセンス管理の自動化ができるかもしれない。その足がかりとしてDBを作る。

項目を調べる

ライセンスAPIで取得できるJSONデータをSQLite3に登録する。その項目を調べる。

全ライセンス取得

Licenses | GitHub Developer Guide

GET /licenses

 [ 
   { 
     "key" :   "mit" , 
     "name" :   "MIT License" , 
     "spdx_id" :   "MIT" , 
     "url" :   "https://api.github.com/licenses/mit" , 
     "featured" :   true 
   }, 
   { 
     "key" :   "lgpl-3.0" , 
     "name" :   "GNU Lesser General Public License v3.0" , 
     "spdx_id" :   "LGPL-3.0" , 
     "url" :   "https://api.github.com/licenses/lgpl-3.0" , 
     "featured" :   false 
   }, 
   ...

100件以下ならページネーションは不要のはず。はたして何件あるのか。

各ライセンス詳細

GET /licenses/:license

{ 
   "key" :   "mit" , 
   "name" :   "MIT License" , 
   "spdx_id" :   "MIT" , 
   "url" :   "https://api.github.com/licenses/mit" , 
   "html_url" :   "http://choosealicense.com/licenses/mit/" , 
   "featured" :   true , 
   "description" :   "A permissive license that is short and to the point. It lets people do anything with your code with proper attribution and without warranty." , 
   "implementation" :   "Create a text file (typically named LICENSE or LICENSE.txt) in the root of your source code and copy the text of the license into the file. Replace [year] with the current year and [fullname] with the name (or names) of the copyright holders." , 
   "permissions" :   [ 
     "commercial-use" , 
     "modifications" , 
     "distribution" , 
     "sublicense" , 
     "private-use" 
   ], 
   "conditions" :   [ 
     "include-copyright" 
   ], 
   "limitations" :   [ 
     "no-liability" 
   ], 
   "body" :   "\n\nThe MIT License (MIT)\n\nCopyright (c) [year] [fullname]\n\nPermission is hereby granted, free of charge, to any person obtaining a copy\nof this software and associated documentation files (the \"Software\"), to deal\nin the Software without restriction, including without limitation the rights\nto use, copy, modify, merge, publish, distribute, sublicense, and/or sell\ncopies of the Software, and to permit persons to whom the Software is\nfurnished to do so, subject to the following conditions:\n\nThe above copyright notice and this permission notice shall be included in all\ncopies or substantial portions of the Software.\n\nTHE SOFTWARE IS PROVIDED \"AS IS\", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR\nIMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,\nFITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE\nAUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER\nLIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,\nOUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE\nSOFTWARE.\n" 
 }

bodyの値が文字列エスケープされている。

改行コードが\nとして表記されている
"が\"になっている

また、値を変更すべきものが含まれている。

[year]
[fullname]

リポジトリ毎のライセンス

GET /repos/:owner/:repo

 { 
   "id" :   23022377 , 
   "name" :   "licensee" , 
   "full_name" :   "benbalter/licensee" , 
   "owner" :   { 
     "login" :   "benbalter" , 
     "id" :   282759 , 
     "avatar_url" :   "https://avatars.githubusercontent.com/u/282759?v=3" , 
     "gravatar_id" :   "" , 
     "url" :   "https://api.github.com/users/benbalter" , 
     "html_url" :   "https://github.com/benbalter" , 
     "followers_url" :   "https://api.github.com/users/benbalter/followers" , 
     "following_url" :   "https://api.github.com/users/benbalter/following{/other_user}" , 
     "gists_url" :   "https://api.github.com/users/benbalter/gists{/gist_id}" , 
     "starred_url" :   "https://api.github.com/users/benbalter/starred{/owner}{/repo}" , 
     "subscriptions_url" :   "https://api.github.com/users/benbalter/subscriptions" , 
     "organizations_url" :   "https://api.github.com/users/benbalter/orgs" , 
     "repos_url" :   "https://api.github.com/users/benbalter/repos" , 
     "events_url" :   "https://api.github.com/users/benbalter/events{/privacy}" , 
     "received_events_url" :   "https://api.github.com/users/benbalter/received_events" , 
     "type" :   "User" , 
     "site_admin" :   true 
   }, 
   "private" :   false , 
   "html_url" :   "https://github.com/benbalter/licensee" , 
   "description" :   "A Ruby Gem to detect under what license a project is distributed." , 
   "fork" :   false , 
   "url" :   "https://api.github.com/repos/benbalter/licensee" , 
   "forks_url" :   "https://api.github.com/repos/benbalter/licensee/forks" , 
   "keys_url" :   "https://api.github.com/repos/benbalter/licensee/keys{/key_id}" , 
   "collaborators_url" :   "https://api.github.com/repos/benbalter/licensee/collaborators{/collaborator}" , 
   "teams_url" :   "https://api.github.com/repos/benbalter/licensee/teams" , 
   "hooks_url" :   "https://api.github.com/repos/benbalter/licensee/hooks" , 
   "issue_events_url" :   "https://api.github.com/repos/benbalter/licensee/issues/events{/number}" , 
   "events_url" :   "https://api.github.com/repos/benbalter/licensee/events" , 
   "assignees_url" :   "https://api.github.com/repos/benbalter/licensee/assignees{/user}" , 
   "branches_url" :   "https://api.github.com/repos/benbalter/licensee/branches{/branch}" , 
   "tags_url" :   "https://api.github.com/repos/benbalter/licensee/tags" , 
   "blobs_url" :   "https://api.github.com/repos/benbalter/licensee/git/blobs{/sha}" , 
   "git_tags_url" :   "https://api.github.com/repos/benbalter/licensee/git/tags{/sha}" , 
   "git_refs_url" :   "https://api.github.com/repos/benbalter/licensee/git/refs{/sha}" , 
   "trees_url" :   "https://api.github.com/repos/benbalter/licensee/git/trees{/sha}" , 
   "statuses_url" :   "https://api.github.com/repos/benbalter/licensee/statuses/{sha}" , 
   "languages_url" :   "https://api.github.com/repos/benbalter/licensee/languages" , 
   "stargazers_url" :   "https://api.github.com/repos/benbalter/licensee/stargazers" , 
   "contributors_url" :   "https://api.github.com/repos/benbalter/licensee/contributors" , 
   "subscribers_url" :   "https://api.github.com/repos/benbalter/licensee/subscribers" , 
   "subscription_url" :   "https://api.github.com/repos/benbalter/licensee/subscription" , 
   "commits_url" :   "https://api.github.com/repos/benbalter/licensee/commits{/sha}" , 
   "git_commits_url" :   "https://api.github.com/repos/benbalter/licensee/git/commits{/sha}" , 
   "comments_url" :   "https://api.github.com/repos/benbalter/licensee/comments{/number}" , 
   "issue_comment_url" :   "https://api.github.com/repos/benbalter/licensee/issues/comments{/number}" , 
   "contents_url" :   "https://api.github.com/repos/benbalter/licensee/contents/{+path}" , 
   "compare_url" :   "https://api.github.com/repos/benbalter/licensee/compare/{base}...{head}" , 
   "merges_url" :   "https://api.github.com/repos/benbalter/licensee/merges" , 
   "archive_url" :   "https://api.github.com/repos/benbalter/licensee/{archive_format}{/ref}" , 
   "downloads_url" :   "https://api.github.com/repos/benbalter/licensee/downloads" , 
   "issues_url" :   "https://api.github.com/repos/benbalter/licensee/issues{/number}" , 
   "pulls_url" :   "https://api.github.com/repos/benbalter/licensee/pulls{/number}" , 
   "milestones_url" :   "https://api.github.com/repos/benbalter/licensee/milestones{/number}" , 
   "notifications_url" :   "https://api.github.com/repos/benbalter/licensee/notifications{?since,all,participating}" , 
   "labels_url" :   "https://api.github.com/repos/benbalter/licensee/labels{/name}" , 
   "releases_url" :   "https://api.github.com/repos/benbalter/licensee/releases{/id}" , 
   "created_at" :   "2014-08-16T16:39:56Z" , 
   "updated_at" :   "2015-02-26T18:58:36Z" , 
   "pushed_at" :   "2015-02-26T19:09:18Z" , 
   "git_url" :   "git://github.com/benbalter/licensee.git" , 
   "ssh_url" :   "git@github.com:benbalter/licensee.git" , 
   "clone_url" :   "https://github.com/benbalter/licensee.git" , 
   "svn_url" :   "https://github.com/benbalter/licensee" , 
   "homepage" :   "" , 
   "size" :   687 , 
   "stargazers_count" :   20 , 
   "watchers_count" :   20 , 
   "language" :   "Ruby" , 
   "has_issues" :   true , 
   "has_downloads" :   true , 
   "has_wiki" :   false , 
   "has_pages" :   false , 
   "forks_count" :   6 , 
   "mirror_url" :   null , 
   "open_issues_count" :   2 , 
   "forks" :   6 , 
   "open_issues" :   2 , 
   "watchers" :   20 , 
   "default_branch" :   "master" , 
   "master_branch" :   "master" , 
   "license" :   { 
     "key" :   "mit" , 
     "name" :   "MIT License" , 
     "spdx_id" :   "MIT" , 
     "url" :   "https://api.github.com/licenses/mit" 
   }, 
   "network_count" :   6 , 
   "subscribers_count" :   6 
 }

下の方にある"license" : { ...がライセンス情報。

以下のリポジトリ取得APIにライセンス情報が足されたものに見える。

Repositories | GitHub Developer Guide

リポジトリにライセンスが適用されていない場合

"license": nullになる

GitHubが管理している以外のライセンスの場合

"license":{"key":"other","name":"Other","spdx_id":null,"url":null,"featured":false}になる

DB

GitHub.Licenses.sqlite3ファイルを新規作成する
以下のテーブルを追加する

create table Licenses(
    Id                  integer primary key,
    Key                 text unique not null,
    Name                text not null,
    SpdxId              text,
    Url                 text,
    HtmlUrl             text,
    Featured            integer default 0 check(Featured = 0 or Featured = 1),
    Description         text,
    Implementation      text,
    Permissions         text,
    Conditions          text,
    Limitations         text,
    Body                text
);

"license":{"key":"other","name":"Other","spdx_id":null,"url":null,"featured":false}の場合にも適用できるようにSpdxIdはnot nullやuniqueにしない
Permissions,Conditions,Limitationsはカンマ区切りの文字列で登録する

所感

次回はこれを一括取得してDBに挿入するスクリプトを書く。

やってみる

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

GitHubのライセンスDBを作る

成果物