インターネット技術特論
H:SQLite3
山口 実靖
http://www.ns.kogakuin.ac.jp/~ct13140/inet/ インターネット技術特論H-2SQLite
• オープンソース(フリー)RDBMS実装の1個
• http://www.sqlite.org/
– SQLite 2.x と SQLite 3.x が有名.
• 特徴
– RDBMSサーバプロセスの起動が不要．
– 「1データベース，1ファイル」で格納．．
• つまり「お手軽」なRDBMS．
– SQLiteの業務での使用はまれ．使い方やSQL文法は他の 実装と類似．勉強には(ほとんど)問題ない． – 問題点：型付けが弱い．ユーザが無く,GRANTなどがない. インターネット技術特論H-4起動 と 終了
• www.ns.kogakuin.ac.jp では以下にある
/usr/bin/sqlite3
• 起動方法 (データベースと接続)
sqlite3 DBファイル
指定ファイルにデータ保存．無ければ新規作成．
• 終了方法 (データベースと切断)
sqlite> .exit
インターネット技術特論H-5起動 と 終了
>sqlite3 /home/ct13140/db/sane.db SQLite version 3.6.20Enter ".help" for instructions
Enter SQL statements terminated with a ";" sqlite>
sqlite> .help
.backup ?DB? FILE Backup DB (default "main") to FILE
(略) width: sqlite> .exit > sqlite> というプロンプトが 表示されている間は， SQLite3と接続中． 接続 接続中 切断 インターネット技術特論H-6
操作
• 赤字
がユーザの入力である．
• 以下のような表を作るとする．
id name email 0 fukuda fukuda@kantei.gr.jp 1 abe abe@kantei.gr.jp ↑ Integer型 ↑ text型 ↑ text型 インターネット技術特論H-7復習 (SQL文) 0/6
• データベースの作成
– データベースにつないでいない状態で
sqlite3 データベースファイル名
– ファイルが存在したら「既存DBに接続」，存在
しなかったら「新規DB作成」
– これはSQLite特有のコマンド．
• データベースの削除
– データベースにつないでいない状態で
rm データベースファイル名
– これはUnix一般のファイル削除コマンド．
復習 (SQL文) 1/6
• データベースに接続する．
– データベースにつないでいない状態で
sqlite3 データベースファイル名
– これはSQLite特有のコマンド．
• データベースとの接続を切断する．
– データベースにつないでいる状態で
復習 (SQL文) 2/6
• 存在する表の一覧を表示
– データベースにつないでいる状態で
.tables
– これは，SQLite特有のコマンド．
インターネット技術特論H-10
復習 (SQL文) 3/6
• 表の作成
– データベースにつないでいる状態で
CREATE TABLE...;
– これはRDBMS一般のコマンド．
• 表の削除
– データベースにつないでいる状態で
DROP TABLE テーブル名;
– 表内のデータが全て消えてしまう．
– これはRDBMS一般のコマンド．
インターネット技術特論H-11復習 (SQL文) 4/6
• 表内のデータを得る
– データベースにつないでいる状態で
SELECT 列名 FROM 表名...;
– これはRDBMS一般のコマンド．
• 表にデータを挿入する．
– データベースにつないでいる状態で
INSERT INTO 表名 VALUES (...);
– これはRDBMS一般のコマンド．
インターネット技術特論H-12
復習 (SQL文) 5/6
• 表内のデータを変更する
– データベースにつないでいる状態で
UPDATE 表名 SET 列名=値 WHERE 条件;
– これはRDBMS一般のコマンド．
インターネット技術特論H-13復習 (型)
• Integer型
: 整数型
• Real型
: 浮動小数点型
• TEXT型
: 文字列型
– SQLite に, CHAR型, VARCHAR型はない.
• BLOB型
: Binary Large OBject
– 値をそのままバイナリで格納.
インターネット技術特論H-14
操作 0/21
• PuTTYでwww.ns.kogakuin.ac.jp
にloginする
– 「Host Name (or IP address)」に 「www.ns.kogakuin.ac.jp」と， 「Port」に「22」と入力し「Open」をクリック． – 「Security Alert」が表示されたら「Yes」を押す． – 「login as」に対してユーザ名を，「password」に対して パスワードを入力． • 注意：ここで使用するユーザ名とパスワードは，工学院大学計算機 (Windows)にlogonする時に使用するものです． インターネット技術特論H-15
操作 1/21
• 自分用データベースの作成＆接続．
ct13140@sweb01[100]:sqlite3 ~/db/sane.db SQLite version 3.6.20Enter ".help" for instructions
Enter SQL statements terminated with a ";" sqlite> このように表されれば データベースに接続中． インターネット技術特論H-16
操作 2/21
• データベースとの接続を切断する．
sqlite> .exit ct13140@sweb01[101]: 表示が sqlite> でなくなれば，切断成功． 上の例では，表示が ct13140@sweb01[101]: に代わってい る． インターネット技術特論H-17操作 3/21
• 再度自分用データベースに接続する．
ct13140@sweb01[101]:sqlite3 ~/db/sane.db SQLite version 3.6.20Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite>
このように表されれば データベースに接続中．
インターネット技術特論H-18
操作 4/21
• 存在する表を調べる(データベース接続中)．
sqlite> .tables sqlite> 何も表示されずに プロンプト(sqlite) が表示されたら， 「表はない」ということ． インターネット技術特論H-19操作 5/21
• 表usertblを作る(データベース接続中)．
sqlite> CREATE TABLE usertbl (...> id integer, ...> name txt, ...> email txt ...> ); sqlite> 使用したSQL文は
CREATE TABLE usertbl (id integer, name text, email text); 途中の改行はあってもなくてもよい． 最後の；(セミコロン)を忘れない様に． インターネット技術特論H-20
操作 6/21
• 存在する表を調べる(データベース接続中)．
sqlite> .tables usertbl sqlite> 「操作5」で作成し た表が表示されれ れば成功． インターネット技術特論H-21操作 7/21
• 表にSELECTを実行する(データベース接続中)．
– 現在，表は空のはずである．
sqlite> SELECT * FROM usertbl; sqlite> 何も成功されなければ， 成功 インターネット技術特論H-22
操作 8/21
• 表にデータを入力する(データベース接続中)．
sqlite> INSERT INTO usertbl VALUES (0,'fukuda', 'fukuda@kantei.go.jp'); sqlite> SELECT * FROM usertbl; 0|fukuda|fukuda@kantei.go.jp これがデータ の挿入命令． 表示は特に 変化なし． 途中改行は あってもなく てもよい． SELECTを実行して， 表の中身を確認し てみる． インターネット技術特論H-23
操作 9/21
• 表にデータを入力する(データベース接続中)．
sqlite> INSERT INTO usertbl VALUES (1, 'abe','abe@kantei.go.jp');
sqlite> INSERT INTO usertbl VALUES (2, 'koizumi', 'koizumi@kantei.go.jp'); sqlite> SELECT * FROM usertbl;
0|fukuda|fukuda@kantei.go.jp 1|abe|abe@kantei.go.jp 2|koizumi|koizumi@kantei.go.jp sqlite>
操作 10/21
• 表diarytblを作る(データベース接続中).
sqlite> CREATE TABLE diarytbl (diaryidInteger, userid Integer, year Integer, month Integer, day Integer, sentence txt);
sqlite> .tables diarytbl usertbl sqlite>
操作 11/21
• 表diarytblにデータを挿入する(データベース
接続中)．
sqlite> INSERT INTO diarytbl VALUES (0, 0, 2007, 1, 1, 'gantan deshita');
sqlite> INSERT INTO diarytbl VALUES (1, 0, 2007, 7, 29, 'senkyo deshita');
sqlite> INSERT INTO diarytbl VALUES (2, 2, 2005, 8, 8, 'kaisan shita');
インターネット技術特論H-26
操作 12/21
• 現状の確認(データベース接続中).
sqlite> SELECT * FROM usertbl; 0|fukuda|fukuda@kantei.go.jp 1|abe|abe@kantei.go.jp2|koizumi|koizumi@kantei.go.jp sqlite> SELECT * FROM diarytbl; 0|0|2007|1|1|gantan deshita 1|0|2007|7|29|senkyo deshita 2|2|2005|8|8|kaisan shita sqlite> インターネット技術特論H-27
操作 13/21
• 現状の確認(データベース接続中).
sqlite> SELECT * FROM diarytbl WHERE userid=0; 0|0|2007|1|1|gantan deshita 1|0|2007|7|29|senkyo deshita sqlite> インターネット技術特論H-28
操作 14/21
• 現状の確認(データベース接続中).
sqlite> UPDATE diarytbl SET sentence='ganjitu deshita' WHERE diaryid=0;
sqlite> SELECT * FROM diarytbl; 0|0|2007|1|1|ganjitu deshita 1|0|2007|7|29|senkyo deshita 2|2|2005|8|8|kaisan shita sqlite> 表内の列の順番は全く保証されていないので， 順番はRDBMSが勝手に決めてくる． 必ずしこの順になるとは限らない． インターネット技術特論H-29
操作 15/21
• 内部結合(データベース接続中).
sqlite> SELECT * FROM usertbl CROSS JOIN diarytbl; 0|fukuda|fukuda@kantei.go.jp|0|0|2007|1|1|ganjitu deshita 0|fukuda|fukuda@kantei.go.jp|1|0|2007|7|29|senkyo deshita 0|fukuda|fukuda@kantei.go.jp|2|2|2005|8|8|kaisan shita 1|abe|abe@kantei.go.jp|0|0|2007|1|1|ganjitu deshita 1|abe|abe@kantei.go.jp|1|0|2007|7|29|senkyo deshita 1|abe|abe@kantei.go.jp|2|2|2005|8|8|kaisan shita 2|koizumi|koizumi@kantei.go.jp|0|0|2007|1|1|ganjitu deshita 2|koizumi|koizumi@kantei.go.jp|1|0|2007|7|29|senkyo deshita 2|koizumi|koizumi@kantei.go.jp|2|2|2005|8|8|kaisan shita sqlite> userID = 2 の横に， userID=0 の書込を置いても， 意味が無い． インターネット技術特論H-30操作 16/21
• 内部結合(データベース接続中).
sqlite> SELECT * FROM usertbl INNER JOIN diarytbl ON usertbl.id = diarytbl.userid; 0|fukuda|fukuda@kantei.go.jp|0|0|2007|1|1|ganjitu deshita 0|fukuda|fukuda@kantei.go.jp|1|0|2007|7|29|senkyo deshita 2|koizumi|koizumi@kantei.go.jp|2|2|2005|8|8|kaisan shita sqlite> インターネット技術特論H-31
操作 17/21
• 表内のデータの削除(データベース接続中).
sqlite> DELETE FROM diarytbl WHERE diaryid=0; sqlite> SELECT * FROM diarytbl;1|0|2007|7|29|senkyo deshita 2|2|2005|8|8|kaisan shita sqlite> インターネット技術特論H-32
操作 18/21
• 表の削除(データベース接続中).
sqlite> DROP TABLE diarytbl; sqlite> .tables usertbl sqlite> 表を削除すると， 表内のデータは全て消失するので注意． インターネット技術特論H-33操作 19/21
• データベースとの接続を切断(データベース接続
中).
sqlite> .exit ct13140@sweb01[102]:インターネット技術特論H-34
操作 20/21
• データベースの削除.
ct13140@sweb01[102]:rm ~/db/a.db ct13140@sweb01[103]: データベースを削除すると， データベース内の全てのデータが失われるので注意！！ 頻繁に行う作業ではありません． インターネット技術特論H-35注意事項
• RDBMSに接続中は，
sqlite>
と表示されるのが正常な状態です．
• ...>
などと表示されたのなら「コマンド入力途中」とい
う変な状態です．これを正常に戻すには
;[Ent] などを押すと，(入力文はエラーとな
るが)元に戻せる．
インターネット技術特論H-36注意事項
• DBのデータを読むには，
– DBファイルへの読込権限が必要．
• DBにデータを書き込むには，
– DBのファイルへの書込権限と，
– DBのファイルのディレクトリへの書込権限が必要
• Webなどで使用するときは後者を要注意！Ruby で SQLite3
インターネット技術特論H-38 $LOAD_PATH.push('/home/ct13140/app/sqlite3-ruby-1.3.1/lib/ruby/site_ruby/2.0.0/x86_64-linux/') # ↑これは1行です. require 'sqlite3' db = SQLite3::Database.new("/home/ct13140/db/a.db") # DBと接続する．ここでDBファイル名を指定．res = db.execute('select * from usertbl;')
# この形↑でSQL文を文字列で送る.戻り値は2次元配列 つまり 2次元の表. res.each do | row | puts row.join(" , ") end
sqlite0.rb
これは，工学院大学固有の話です 赤字部は，(変更せずに)そのまま打ち込む． 青字部は，各自 適切な内容に変更する． インターネット技術特論H-39sqlite0.rb 実行方法
•
/home/ct13140/app/ruby2.0.0p247/bin/ruby
-
I/home/ct13140/app/sqlite3-ruby-1.3.1/lib/ruby/site_ruby/2.0.0/
sqlite0.rb
– これ↑は，1行です．
Ruby version 2.0.0 を使用してください。 #!/home/ct13140/app/ruby2.0.0p247/bin/ruby - I/home/ct13140/app/sqlite3-ruby-1.3.1/lib/ruby/site_ruby/2.0.0/ -- #↑これは1行です.これはコメントではありません．消してはいけません. $LOAD_PATH.push('/home/ct13140/app/sqlite3-ruby-1.3.1/lib/ruby/site_ruby/2.0.0/x86_64-linux/') # ↑これは1行です. require 'sqlite3' db = SQLite3::Database.new("/home/ct13140/db/a.db")sqlite1.rb
sqlite1.rb 実行方法
• chmod 755 sqlite1.rb
– 実行権限を付与する．
• ./sqlite1.rb
– 1行目の内容を使ってこのプログラムは動く.
• つまり，これ↓ #!/home/ct13140/app/ruby-2.0.0-p247/bin/ruby -I/home/ct13140/app/sqlite3-ruby-1.3.1/lib/ruby/site_ruby/2.0.0/ --インターネット技術特論H-42
Ruby + CGI + SQLite3
• データベースファイルを作成する
sqlite3 /home/ct13140/db/a.db
SQLite version 3.6.20
Enter ".help" for instructions
Enter SQL statements terminated with a ";" sqlite> .exit
• ファイルとディレクトリに書込権限を与える
chmod 606 /home/ct13140/db/a.db chmod 707 /home/ct13140/db – ディレクトリに与え忘れないように – ディレクトリに書込権限を与えたら，他人がそのディレクトリに書き込めてしまう． 不必要な場所に書込権限を与えないように注意． インターネット技術特論H-43004.cgi
#!/home/ct13140/app/ruby2.0.0p247/bin/ruby - I/home/ct13140/app/sqlite3-ruby-1.3.1/lib/ruby/site_ruby/2.0.0/ -- $LOAD_PATH.push('/home/ct13140/app/sqlite3-ruby-1.3.1/lib/ruby/site_ruby/2.0.0/x86_64-linux/') require 'sqlite3'print "Content-type: text/html¥n¥n"
db = SQLite3::Database.new("/home/ct13140/db/a.db") puts '<table border=5>'
db.execute('select * from person;') do | row | puts '<tr><td>' puts row.join("</td><td>") puts '</td></tr>' end puts '</table>' インターネット技術特論H-44
005.cgi
#!/home/ct13140/app/ruby2.0.0p247/bin/ruby - I/home/ct13140/app/sqlite3-ruby-1.3.1/lib/ruby/site_ruby/2.0.0/ -- $LOAD_PATH.push('/home/ct13140/app/sqlite3-ruby-1.3.1/lib/ruby/site_ruby/2.0.0/x86_64-linux/') require 'sqlite3' require 'cgi' cgi = CGI.new uid = cgi["id"] name = cgi["name"] email = cgi["email"]print "Content-type: text/html¥n¥n" print uid," , ",name," , ",email, "<br>¥n"
インターネット技術特論H-45
005.cgi
begin
db = SQLite3::Database.new("/home/ct13140/db/a.db") db.execute('insert into usertbl (id,name,email)
values ('+uid+',¥''+name+'¥',¥''+email+'¥')') rescue => exp print "ouch!<br>¥n" print exp.class, "<br>¥n" print exp.message, "<br>¥n" end puts 'fin<br>'
puts '<a href="./">back</a>'
IDは本来は「ユーザ入力するもの」ではなく，「システムが自動で割り振るもの」だが， ご愛敬．．．
インターネット技術特論H-46
• NOT NULL制約
– NULL値を入れられなくなる
sqlite> CREATE TABLE tbl0(id integer, name text NOT NULL, email text);
sqlite> INSERT INTO tbl0 VALUES (0,'sane',NULL);
sqlite> INSERT INTO tbl0 VALUES (1,NULL,'sane@sane.com'); Error: constraint failed
フィールド制約:NOT NULL
これは， 正常に実行される． これは， エラーとなる． インターネット技術特論H-47• UNIQUE制約
– 同じ値を複数行に入れられなくなる
sqlite> CREATE TABLE tbl1(id integer UNIQUE, name text, email text);
sqlite> INSERT INTO tbl1 VALUES (0,'sane','sane@sane.com'); sqlite> INSERT INTO tbl1 VALUES
(0,'yasu','yasu@sane.com'); Error: constraint failed
フィールド制約:UNIQUE
これは， 正常に実行される． これは， エラーとなる． インターネット技術特論H-48• DEFAULT制約
– 入力略時には，DEFAULT値が採用される．
sqlite> CREATE TABLE tbl2(id integer, name text, email text DEFAULT 'a@a.com'); sqlite> INSERT INTO tbl2 (id,name) VALUES
(0,'sane');
sqlite> select * from tbl2; 0|sane|a@a.com
フィールド制約:DEFAULT
インターネット技術特論H-49• PRIMARY KEY制約
– 自動的に一意の値が割り振られる．
– ただし，削除してしまった行の値は再利用される．
sqlite> CREATE TABLE tbl3(id integer PRIMARY KEY, name text, email text);
sqlite> INSERT INTO tbl3 VALUES (NULL,'sane', 'sane@sane.com');
sqlite> INSERT INTO tbl3 (name,email) VALUES ('yasu','yasu@sane.com');
sqlite> SELECT * FROM tbl3; 1|sane|sane@sane.com
2|yasu|yasu@sane.com
インターネット技術特論H-50
フィールド制約:ANTOINCREMENT
• 一意なIDの自動割り振り
sqlite> CREATE TABLE usertbl2 (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, email TEXT);
sqlite> .tables usertbl usertbl2
sqlite> INSERT INTO usertbl2 (name, email) VALUES ('sane','sane@sane.com');
sqlite> INSERT INTO usertbl2 (name, email) VALUES ('yasu','yasu@sane.com');
sqlite> INSERT INTO usertbl2 VALUES (NULL, 'hoge', 'hoge@sane.com');
sqlite> SELECT * FROM usertbl2; 1|sane|sane@sane.com
2|yasu|yasu@sane.com 3|hoge|hoge@sane.com sqlite>
PRIMARY KEY かつ AUTOINCREMENT にしておくと，一度削除された値が 再利用されることがない．
インターネット技術特論H-51
• CHECK制約
– 値に制限をかけられる.
sqlite> CREATE TABLE tbl4(id integer CHECK(id>0), name text, email text); sqlite> INSERT INTO tbl4 VALUES (1,'sane',
'sane@sane.com');
sqlite> INSERT INTO tbl4 VALUES (0,'yasu', 'yasu@sane.com');
Error: constraint failed
フィールド制約:CHECK
これは， 正常に実行される． これは， エラーとなる． インターネット技術特論H-52SQLiteコマンド
• (DBではなく) DBMSを制御するコマンド
.help :ヘルプメッセージを表示
.dump ：データベースをダンプ
例:シェルにて sqlite3 a.db .dump | sqlite3 b.db
