SQLite

メインページ > 工学 > 情報技術 > プログラミング > SQLite
SQLiteは、MySQLのようなサーバー・クライアント方式ではなく、アプリケーションに直接組み込んで使用することができる関係データベース管理システム (RDBMS) です。 身近なところでは、Google ChromeやMozilla Firefoxなどの多くのアプリケーションで使用されており、Google AndroidではOSに組み込まれています。 これにより、SQLiteは世界で最もインストールベースの多いRDBMSの1つとして認知されています。
SQLiteは、高速かつ軽量なリレーショナルデータベース管理システムです。SQLiteは、コンパクトなライブラリとして提供され、モバイルデバイス、デスクトップアプリケーション、Webブラウザ、組み込みシステムなど、あらゆるプラットフォームで使用されています。
SQLiteは、標準のSQLをサポートし、トランザクション処理、データの完全性維持、およびACID(原子性、一貫性、分離性、耐久性)の保証を提供します。また、多くの主要なプログラミング言語(C、Java、Python、PHPなど)で利用可能であり、簡単に統合することができます。
SQLiteの最も重要な特徴の一つは、ファイルベースのデータベースであることです。つまり、データはファイルに保存され、ファイルを別の場所にコピーするだけで、データを移行したりバックアップしたりすることができます。また、複数のプロセスが同時にデータベースにアクセスすることができるため、アプリケーションのパフォーマンスが向上します。
SQLiteは、他のリレーショナルデータベース管理システムと比較して、データベースが比較的小規模である場合に最適です。大量のデータを処理する必要がある場合や、高い同時アクセスが必要な場合には、より高機能なデータベースシステムを検討する必要があります。
ただし、SQLiteは非常に高速であるため、多くの場合、大量のデータを処理する必要がある場合でも、十分なパフォーマンスを発揮します。SQLiteはまた、カスタム拡張機能を提供することができ、標準のSQLコマンド以外の機能を簡単に実装することができます。
SQLiteは、プログラマにとって非常に使いやすく、学びやすいです。SQLの基本構文を理解しているだけで、すぐにSQLiteを利用することができます。また、SQLiteは非常に広く利用されているため、オンラインで豊富な情報を見つけることができます。
本書では、SQLite version3の保守管理用のコマンドラインインターフェース sqlite3の操作を中心に、SQLiteの機能の解説を行いますので、まずsqlite3が、ご自身の環境にインストールされているか確認します。
SQLite公式(ダウンロードページ)の Precompiled Binaries for Windows にある sqlite-tools-win-x64-3450000.zip(4.77 MiB)をダウンロードします(3450000の部分はバージョンによって変わります。この場合は、3.42.0)。
sqlite-tools-win-x64-3450000.zipは、ZIP書庫で
2022年11月現在サポートが継続されている macOS には、最初からインストールされています。 Terminalを起動してバージョンを確認してください。
ports/pkg の databases/sqlite3 にあります。
GNU/Linuxの場合は、ディストーションやパッケージマネージャーによってパッケージの名前やインストール方法が異なるので以下は一例です。
Chromebrewは、sqlite の名前で SQLite のパッケージが用意されています。
SQLiteは、アプリケーションプログラムにライブラリーとしてリンクされ、単一のファイルにデーターベースを構築します。 このため、MySQLのようにサーバー・クライアントモデルで必要な、クライアントからのデーターベース・サーバーへの接続・認証は必要なく、データーベースファイルをオープンし使用を開始します。
前述の通り、SQLiteはアプリケーションプログラムに組込まれて使用されますが、データーベースの保守管理は必要なので、その用途にコマンドラインインターフェースアプリケーション sqlite3が用意されています。
個別のコマンドの説明を列挙して解説する前に、実際に sqlite3 を使って
などの操作を script(1) を使って記録して様子を掲載します。
SQLiteのデーターベースは、単一のファイルです。 なのでデーターベースへは、S/Cモデルのように接続するのではなくデーターベースファイルを開きます。
sqlite3 の起動時パラメーターにデーターベースファイルを与えると、データーベースがオープンした状態で起動します。
sqlite3をパラメータなしで起動したあと、.openコマンドでデーターベースファイルを開きます。
.open や .databases は、SQLではなくsqlite3のコマンドで、SQLと異なり大文字小文字を区別します。
sqliteのオンラインヘルプコマンドは.helpです。下記に3.40.0の.helpを示します(なるべく解説へのリンクにしました)
SQLiteのデーターベースファイルの拡張子には、.db、.sqliteや.sqlite3が用いられます。 SQLite自身は、拡張子によって動作を変えるようなことはしていないので、どのような拡張子でも構いませんが、オペレーションシステムの拡張子とファイルタイプの関連付けと重複しないように注意が必要です。
ちなみにSQLiteバージョン3のデーターベースファイルのIANAメディアタイプは、application/vnd.sqlite3です。
sqlite3は、.exit、.quitまたは^D(EOF)で終了します。
タイプミスなどで複数行入力モードに入ることがあります
このほか、文字列の ” や ’ が閉じられていない場合も考えられます。
テーブルを定義する場合は CREATE TABLE コマンドを使います。
テーブルを削除する場合は DROP TABLE コマンドを使います。
[TODO]
SQLiteの型システムは、標準SQLとはポリシーが異なっており、カラムに定義された型と異なる型の値が与えられると、可能な限り情報を落とさないように、場合によっては型を変えて保存します。
これは、テーブル定義と異なる型の値がカラムに与えられた場合、SQLiteが情報を落としてしまうと、SQLiteを利用するアプリケーションプログラムが、適切な判断を行う手段を失ってしまうからです。
このような設計上に選択にも、SQLiteが単体のデーターベースシステムではなく、アプリケーションプログラムに組込まれるRDBMSエンジンであるという立ち位置の特徴が現れています。
ではさっそく、1番目の水素のデーターをつけたしてみましょう。
SELECTコマンドの結果にカラムの名前を含めるか含めないかは、 .headerコマンドで切替えできます。
レコード(行)を挿入するとき、何らかの理由で全カラムを埋めることができなかった場合は、一部のカラムだけからなるレコードを挿入することもできます。 また、テーブル定義でカラムをNOT NULL制約することでレコードを挿入するときに必須のカラムを設け、入力がない場合をエラーとし挿入を拒絶することもできあます。
行の削除は、DELETEコマンドとWHERE演算子を併用します。
ヘリウムのnameデーター ”Helium” の行(レコード)を削除
.schema コマンド(Show the CREATE statements matching PATTERN)で、CREATEのフォーマットでテーブル定義を表示できます。
.tables コマンドでテーブルの一覧を表示できます。
SQLiteのデータベースは、1つの通常のファイルなので、バックアップはファイルをコピーするだけです。 また、sqlite3のコマンドに、.backup コマンドと .restore が用意されています。
バックアップはファイルレベルのコピーでできますが、障害発生に備えてのバックアップとしては、テキストファイルへのダンプが有用です。
ダンプは、SQLiteのコマンドライン管理インターフェース sqlite3を使います。
操作と結果概観で作成したデータベース periodic.dbの テーブル elementsをテキストファイルperiodic.txt にダンプする例です。
.dumpコマンドでダンプしたデーターを現在のデーターベースにリストアするには .read コマンドを使います。
SQLiteでは、テーブルにデータが追加されると、データベースファイルのサイズは徐々に大きくなるが、テーブルからデータを削除しても、データベースファイルのサイズはすぐには小さくなりません。
sqlite3では、VACUUMコマンドで空き領域の開放を行います。
SQLiteはアプリケーションに組込まれ使われますが、ここでは Python からの利用の例を紹介します。
Pythonでは、PEP 249 -- Python Database API Specification v2.0 でデーターベースAPIが標準化されており、sqlite3 モジュールでSQLite3をサポートしています。
SQLiteはアプリケーションに組込まれ使われますが、ここでは PHP からの利用の例を紹介します。
PHPでは、PDOでデーターベースAPIが抽象化/標準化されており、SQLiteもPDO経由で使用できます。
PDOとは別に、SQLite専用のSQLite3クラスも用意されています。