SQLiteを使ってみよう

SQLiteを使ったWebアプリの作り方が紹介されていたので、おもしろそうなのでやってみました。情報発信者がつかっていたのはPython。PythonでできるのであればNode.jsでもできるんじゃないかと安易に考え、やってみることにしました。

Contents

1 MySQLにはないSQLiteのメリット
2 MySQLにはないSQLiteのデメリット
- 2.1 パスワード設定ができない
- 2.2 書込がダブると書込エラー
3 SQLiteを使ってみる

MySQLにはないSQLiteのメリット

データベースとして有名なものにMySQLがありますが、MySQLにはないSQLiteのメリットはなにでしょうか？

データがファイルひとつだけなのでわかりやすい

まずデータがファイルひとつなので、バックアップが簡単にできることがあります。やっぱりMySQLは難しいという先入観があります。操作をミスしてデータベースを吹っ飛ばしたという失敗もよく聞きます。鳩でもでも分かるC#はワードプレスで作成されているのでMySQLをつかっているわけですが、直接自分でMySQLを操作することはありません。一知半解で適当なことをしてデータベースが消えてしまったなんてことになればしゃれになりません。

しかしSQLiteであればデータがファイルひとつなので、バックアップが簡単にできてしまいます。これは「MySQLは難しそう」という人には大きなメリットとなります。「データはたったひとつのファイルにまとめられている」というのは扱いやすいです。

レンタルサーバMySQLよりも高速に作動

それから多くのレンタルサーバMySQLよりも高速に作動するのも魅力です。鳩でもでも分かるC#はエックスサーバーというレンタルサーバーで使用されています。このサーバーを使用しているのは私一人ではありません。だから使えないコマンドもあります。たしかにそうですよね。他のユーザーもいるのに、私に対してだけroot権限を与える、はっきり言って怖すぎます。それでもSQLiteであればroot権限なんてなくても簡単に使えます。

そして肝心の実行速度もそこそこあります。勘違いがないように言っておくと、MySQLが悪いわけではありません。レンタルサーバーは多くのアカウントを管理しています。MySQもひとつのMySQLサーバーに押し込んで運用しています。これではいくらMySQLが優秀であってもサーバに大きな負担をかかってしまいます。そのためMySQL自体は素晴らしい物でもその動作は劇遅になってしまうのです。

これに対してSQLiteはサーバー上の自分のスペースに置くだけなので快適に使うことができます。データもファイルひとつなので、容易にバックアップも取ることができるのです。

基本的なSQL文はすべて使える

しかもSQL機能的にも問題ありません。基本的なSQL文はすべて使えます。設定も容易です。MySQLを使ってみたが、難しそう。そんな型におすすめです。しかも無料、ライセンスの心配も一切不要です。容量が少なくて済むので、おすすめです。

MySQLにはないSQLiteのデメリット

もちろんデメリットもあります。

パスワード設定ができない

まずパスワードを設定することができません。SQLiteはファイル形式なのでセキュリティ機能はありません。ファイルへのアクセス権限をコントロールしなければなりません。サーバーのドキュメントルート以下に置くのはおすすめできません。しかしドキュメントルートより上に置けばセキュリティも問題は回避できそうです。

書込がダブると書込エラー

書込がダブると書込エラーになるという問題もあります。不特定多数が使うようなものをつくる場合に、最初の人が書込をしている間に、次の人が書込をしようとするとエラーが発生します。そこでその場合の対策が必要です。しかし個人使用である場合は問題にあることはないと考えられます。不特定多数が頻繁に書き込むような環境で使うのは苦手だと思われます。

SQLiteを使ってみる

このような欠点もあるかもしれませんが、それでもSQLiteはおすすめだと思います。そこでSQLiteをつかって掲示板のようなもの（あくまでも掲示板「もどき」です）を作ってみようと思います。

SQLiteをインストール

まずはSQLiteを使ってみることにします。そのためにはSQLiteをインストールすることから始めましょう。

まずブラウザで SQLite の公式ページへアクセスして下さい。

ここでは

sqlite-dll-win64-x64-3360000.zip
sqlite-tools-win32-x86-3360000.zip

をダウンロードします。そしてzipファイルを解凍したら全部で5個のファイルができるので、これを適当な場所に保存しておきましょう。そして保存したパスを登録しておきましょう。そうすればコマンドプロンプトで

sqlite3 -version

と入力すればバージョン情報が表示されるはずです。

データベースを作成する

早速データベースを作成してみましょう。適当なフォルダをつくってcdコマンドでフォルダに移動し、

sqlite3 test.db

とタイプします。

するとコマンドプロンプトの行の先頭に sqlite> が現れます。

テーブルを追加

しかしフォルダのなかを調べてみてもtest.dbは生成されていません。データベースを作るためにはテーブルを追加しなければなりません。そこでここではusersというテーブルを追加します。

テーブルを追加するのですが、ここでは1行ずつ以下を入力します。create table users(を入力してEnterキーをおすと、行の先頭に …> が表示されますが、最後の );を入力すると消えます。

sqlite> create table users(
	...> id integer primary key autoincrement,
	...> name text,
	...> age integer,
	...> email text unique,
	...> status integer default 1
	...> );

sqlite> create table users(

...> id integer primary key autoincrement,

...> name text,

...> age integer,

...> email text unique,

...> status integer default 1

...> );

これでフォルダのなかにtest.dbが生成されているはずです。

最初に　id integer primary key autoincrement, としています。

カラムに PRIMARY KEY 制約を設定すると、そのカラムが主キー(プライマリーキー)になります。主キーはテーブルの中で1つだけ存在します。主キーは重複する値を取ることはできません。またid integerとしているので、idはプライマリーキーであるとともに整数値となります。

またここでは id integer primary keyのあとにautoincrementとしています。 INTEGER PRIMARY KEY を設定する時に AUTOINCREMENT を合わせて設定すると、対象のカラムに今までに格納されたことのある最大の値に1が加えられた値が格納されるようになります。

データは追加されるだけでなく削除されることもあります。このときにAUTOINCREMENTを設定していないと対象のカラムに格納されている最大の値に1を加えた値となります。データの追加と削除を繰り返しているとかつて使われたidが再度使用されることになります。これでは困る（多分）のでINTEGER PRIMARY KEYを設定したらAUTOINCREMENTも同時に設定しています。

それ以外の部分ですが、データ型を指定しています。

格納される値のデータ型は「NULL」「INTEGER」「REAL」「TEXT」「BLOB」の5種類です。NULLはNULL値で、INTEGERは符号つきの整数であり、REALは浮動小数点数です。TEXTはテキストでUTF-8, UTF-16BE or UTF-16-LEのいずれかで格納されます。またBLOBはBinary Large OBjectであり、入力データをそのまま格納します。

テーブルを作成する時にカラムに対してデータ型を指定することができます。SQLite3の場合、カラムに指定できるデータ型は「TEXT」「NUMERIC」「INTEGER」「REAL」「NONE」の5つです。データ型が文字列 BLOB を含む場合や、データ型が指定されなかった場合は NONE 型となります。

再掲すると

sqlite> create table users(
	...> id integer primary key autoincrement,
	...> name text,
	...> age integer,
	...> email text unique,
	...> status integer default 1
	...> );

sqlite> create table users(

...> id integer primary key autoincrement,

...> name text,

...> age integer,

...> email text unique,

...> status integer default 1

...> );

idは整数型、nameはテキスト、ageは符号つきの整数、emailはテキストであるが、他と重複してはならない、statusは符号つきの整数であるが、指定しなければ1が格納されることになります。

データを追加する

ではデータを追加してみましょう。

そのまえにいまは中身は空ですが、どのようなデータが格納されているのか確認してみます。確認するためにはコマンドプロンプトで select * from users; と入力します。もちろんデータはなにもないのでなにも表示されません。

ではデータを追加してみましょう。

コマンドプロンプトに sqlite> と表示されているのを確認して、一行ずつ以下を入力してみます。

insert into users(name, age, email) values('佐藤一郎', 23, 'sato@yahoo.co.jp');
insert into users(name, age, email, status) values('鈴木次郎', 31, 'suzuki@yahoo.co.jp', 100);

1 2	insert into users(name, age, email) values('佐藤一郎', 23, 'sato@yahoo.co.jp'); insert into users(name, age, email, status) values('鈴木次郎', 31, 'suzuki@yahoo.co.jp', 100);

そのあと select * from users; と入力してみましょう。すると

1|佐藤一郎|23|sato@yahoo.co.jp|1
2|鈴木次郎|31|suzuki@yahoo.co.jp|100

と表示されるはずです。’佐藤一郎’はstatusを指定しませんでしたが、status integer default 1の効果で、1と出力されています。

また以下のように入力したらどうなるでしょうか？ ageは整数でなければならないのですが、文字列で’yonju-ichi’として登録しようとしています。また’渡辺四郎’はageを指定しないで登録しようとしています。

insert into users(name, age, email) values('高橋三郎', 'yonju-ichi', 'taka@yahoo.co.jp');
insert into users(name, email) values('渡辺四郎', 'wata@yahoo.co.jp');

1 2	insert into users(name, age, email) values('高橋三郎', 'yonju-ichi', 'taka@yahoo.co.jp'); insert into users(name, email) values('渡辺四郎', 'wata@yahoo.co.jp');

なんと登録されてしまいました。