Thought Log

航空宇宙エンジニアの卵、もうすぐ就職

Pythonで家計簿アプリを作る その3の脇道、SQLとデータベースの勉強

今回はデータベース設計について書きたかったのですが、SQLiteSQLについて学んだまとめが肥大化したので外伝的な形で先に載せようと思います。

RDB(Relational Database)とSQL

SQLという名前は耳にしたことがあるものの、①DBを操作するための言語であること、②HTMLと同様に多くのエンジニアはそれができてもプログラマと見做さない傾向があること、位の認識でした。今回わざわざSQLiteを使った理由としてSQLやDBの理解も挙げているので、ここでその成果を書きたいと思います。英語版Wikiを参照しました。

  • SQLはStructured Query Languageの略(クエリとは問合せのこと)
  • 元々SEQUEL(Structured English Query Language)だったこともあり、エスキューエルではなくシークェルと発音する人も多い、というか本家本元?
  • Relational Database Management (RDBM)の言語で、基本的には構造が定まったデータを扱うのに適している
  • 具体的にはCREATE、INSERT、SELECT、DELETEなどのSyntaxを使ってデータに対して①定義②操作(追加/変更/削除)③制御(アクセス権)、④クエリが行える言語のこと
  • Relational Database (RDB)とはrelational modelという情報科学理論に基づいたデータ形式で、恐ろしくデフォルメすると二次元の表形式で表せるデータベースのこと
  • E. F. Coddさんという人が70年代にrelational modelとその応用性を提唱して、それをゆるーく原作として作られたのがSQLおよびSQLが対象とするDB
  • SQLという言語は一つではなく、その操作対象となるRDBMシステムのベンダがそれぞれのバージョンを持っていて、若干の違いがあるため相互に互換性はない。例えばOraclePL/SQLMySQLSQL/PSMなど

どうやらこのE. F. Coddさんは界隈では超有名人っぽいです。流体力学におけるL. Prandtlみたいなもんですかね。彼の元論文であるA Relational Model of Data for Large Data Banksはネットに落ちていました。数弱の工学徒として代数学は履修していませんが、なんとなく意味はわかる程度には例などを沢山盛り込んでくれています。先にSQLの説明などを読んでforeign keysなどでテーブルとテーブルが繋がってるのがRDBのrelationなのかと思いきや、彼がrelationと呼んでいるのはテーブルの各列間の関係です。例えば

Company Contact Country
Alfreds Futterkiste Maria Anders Germany
Centro comercial Moctezuma Francisco Chang Mexico
Ernst Handel Roland Mendel Austria
Island Trading Helen Bennett UK
Laughing Bacchus Winecellars Yoshi Tannamuri Canada
Magazzini Alimentari Riuniti Giovanni Rovelli Italy

のようなデータベースがあったとすると、Company集合における要素Alfreds Futterkisteと、Contact集合における要素Maria Anders、そしてCountry集合における要素Germanyが”繋がって”、あるいは関係を持って1つのタプル(データ、表の1行目)を構成している訳ですが、このタプルの集合(各行の集まり)をrelationと呼んでいるみたいです。そのように表データを考えたことがなかったので目から鱗でした。他にも一般的には木状に表すようなデータを表で表すdata normalizationなど、このようなコンセプトを全く知らない門外漢にはわからないなりに発見が沢山ある論文でした。

f:id:Nullius_in_verba:20220224170715p:plain

木状に表されているデータの正規化例。例では*historyとなっているデータが親に対して複数個ある1対多データとなっている。(Codd 1970)

同時に、論文に書かれてあることはMicrosoftがデータベースデザインの基礎を指南しているこのページに書かれてあることと似ていて、彼の理論の影響力を思い知らされます。またデータベースはRDBだけでなくネットワーク型やキャッシュ型など色々あるみたいです。色々なデータベースの形に関してはこのYoutube動画が面白かったです。

 

www.youtube.com

データベース設計におけるSQLiteの特性

さてごちゃごちゃとSQL自体についてまとめてみましたが、より実践的に今SQLiteの操作方法を見ていきます。公式のドキュメンテーションがわかりやすかったです。

SQLiteの位置付け

前述の通り大企業がOracleなどを通してDB管理をSQLで行ったりもするのですが、SQLiteはある意味その真逆を目指していて、ローカルで(サーバレス)データをRDBで管理するためのツールとして作られています。公式にも"SQLiteはクライエント/サーバDBと競合するものではありません、競合相手はfopen()です。"との記述があります。今回の家計簿アプリはスタンダロン形式なのでピッタリでした。

データの定義(参考)

エクセルで表を作成する時、①表の名前をつけて②列に名前をつけて③1行づつデータ入力とやっていくと思いますが、SQLiteでも似ていて、①テーブルを定義して②フィールド(行)を定義して③1行づつデータ入力という流れになります。注意点として

  • Primary Key
  • データの型
  • 制約
  • Foreign Key

を気にする必要があります。

Primary Key

Primary Keyとはそのデータを一意的に特定するためのIDで、1つまたは複数の列をPrimary Keyと指定することができます。言わずもがなですが、その列に入るデータが重複するようなものだとダメですね。家計簿の明細を例にするとカテゴリ、日付、金額等はそれぞれ重複する可能性があるので単一ではPrimary Keyの列となり得ません。新たにID列を設定して1、2、3と入力順に数字打った方が簡単そうですね。と思ったらSQLiteはデフォルトで新たなテーブルを定義するとrowidというまさにそれをするPrimary Key列を作ってくれるらしいです。

データの型(参考

SQLiteは他のSQLとは違い、dynamic typing対応のようです。でもSQLiteの勉強というよりはSQLの勉強をしたいので、静的な感じでTypeを使おうと思います。SQLiteの中ではStorage Classというものがあり、以下の5つの種類があるようです。

  • NULL
  • INTEGER
  • REAL
  • TEXT
  • BLOB

まぁほとんど見た通りなので説明は省くとして、BLOBは入力されたデータをそのまま保管するという意味らしいです。バイナリとかも入れれるのかな?家計簿に関連するものでは日付型がないですが、これはTEXTで”YYYY-MM-DD”と入れることでなんとかなるっぽいです。

制約

それぞれの列に入るデータに制約をかけることもできます。今回使いそうなのは空欄にしてはいけないというNOT NULL制約ですね。金額とかに使えそうです。他にUNIQUEという、Primary Key以外の列で重複を避けるための制約もありますが、これはカテゴリ名称などの列に使えそうです。foreign keysも制約の一種ですが、これは次に説明します。

Foreign Key

SQLではテーブル間でデータをつなげる時に、つなげる先の相手のPrimary Keyを使い、これをForeign Keyと呼びます。詳しくはこのページがわかりやすいです。家計簿の例でいうとカテゴリー欄はある特定の値、例えば[家賃、食費、日用品]だけを許容するとします。このときにDBにこれを強制させる方法として、

カテゴリID カテゴリ名
01 家賃
02 食費
03 日用品

というカテゴリID欄をprimary keyとする新たなテーブルを作ってしまい、メインのテーブルのカテゴリ欄にカテゴリID欄(メインから見るとForeign key)だけが登録できるように制約をかければ、メインのテーブルには[家賃、食費、日用品]しか入力できない状態が達成できます。ここで注意なのは、Foreign Keyしか入力できないという制約はデフォルトではOFFになっているので、

PRAGMA foreign_keys = 1;

によってコネクション毎に制約をONにしなければいけません。

以上、SQLについて、SQLiteとデータ定義についてでした。次回はこの知識を用いて家計簿DBの設計と実装を紹介したいと思います。

 

次回:Pythonで家計簿アプリを作る その3、データベース設計と実装 - Thought Log

前回:Pythonで家計簿アプリを作る その2、要件定義など - Thought Log

 

その他:Pythonで家計簿を作る、その3の備忘録 Hatenaブログでのコード表示 - Thought Log