インターネット技術特論 H:SQLite3 山口 実靖

Presentation on theme: "インターネット技術特論 H:SQLite3 山口 実靖"— Presentation transcript:

1 インターネット技術特論 H:SQLite3 山口 実靖

2 SQLite オープンソース(フリー)RDBMS実装の1個 http://www.sqlite.org/ 特徴
SQLite 2.x と SQLite 3.x が有名. 特徴 RDBMSサーバプロセスの起動が不要． 「1データベース，1ファイル」で格納．． つまり「お手軽」なRDBMS． SQLiteの業務での使用はまれ．使い方やSQL文法は他の実装と類似．勉強には(ほとんど)問題ない． 問題点：型付けが弱い．ユーザが無く,GRANTなどがない.

3 起動 と 終了 www.ns.kogakuin.ac.jp では以下にある /home/ct13140/bin/sqlite3
起動方法 (データベースと接続) /home/ct13140/bin/sqlite3 DBファイル 指定ファイルにデータ保存．無ければ新規作成． 終了方法 (データベースと切断) sqlite> .exit 上記は，山口が個人的にインストールしたものです． 年度末までは，存在することを保証します． それ以降は，予告無く削除することもありえます．

4 起動 と 終了 www.ns.kogakuin.ac.jp では以下にある /usr/bin/sqlite3
起動方法 (データベースと接続) sqlite3 DBファイル 指定ファイルにデータ保存．無ければ新規作成． 終了方法 (データベースと切断) sqlite> .exit

5 起動 と 終了 >sqlite3 /home/ct13140/db/sane.db SQLite version 3.6.20
Enter ".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と接続中．

6 操作 赤字がユーザの入力である． 以下のような表を作るとする． id name email fukuda
fukuda 1 abe ↑ Integer型 ↑ text型 ↑ text型

7 復習 (SQL文) 0/6 データベースの作成 データベースの削除 データベースにつないでいない状態で
sqlite3 データベースファイル名 ファイルが存在したら「既存DBに接続」，存在しなかったら「新規DB作成」 これはSQLite特有のコマンド． データベースの削除 rm データベースファイル名 これはUnix一般のファイル削除コマンド．

8 復習 (SQL文) 1/6 データベースに接続する． データベースとの接続を切断する． データベースにつないでいない状態で
sqlite3 データベースファイル名 これはSQLite特有のコマンド． データベースとの接続を切断する． データベースにつないでいる状態で .exit これは，SQLite特有のコマンド．

9 復習 (SQL文) 2/6 存在する表の一覧を表示 データベースにつないでいる状態で .tables これは，SQLite特有のコマンド．

10 復習 (SQL文) 3/6 表の作成 表の削除 データベースにつないでいる状態で CREATE TABLE...;
これはRDBMS一般のコマンド． 表の削除 DROP TABLE テーブル名; 表内のデータが全て消えてしまう．

11 復習 (SQL文) 4/6 表内のデータを得る 表にデータを挿入する． データベースにつないでいる状態で
SELECT 列名 FROM 表名...; これはRDBMS一般のコマンド． 表にデータを挿入する． INSERT INTO 表名 VALUES (...);

12 復習 (SQL文) 5/6 表内のデータを変更する データベースにつないでいる状態で
UPDATE 表名 SET 列名=値 WHERE 条件; これはRDBMS一般のコマンド．

13 復習 (型) Integer型 : 整数型 Real型 : 浮動小数点型 TEXT型 : 文字列型
SQLite に, CHAR型, VARCHAR型はない. BLOB型 : Binary Large OBject 値をそのままバイナリで格納.

14 操作 0/21 PuTTYでwww.ns.kogakuin.ac.jp にloginする
「Host Name (or IP address)」に「 「Port」に「22」と入力し「Open」をクリック． 「Security Alert」が表示されたら「Yes」を押す． 「login as」に対してユーザ名を，「password」に対してパスワードを入力． 注意：ここで使用するユーザ名とパスワードは，工学院大学計算機(Windows)にlogonする時に使用するものです．

15 操作 1/21 自分用データベースの作成＆接続． ct13140@sweb01[100]:sqlite3 ~/db/sane.db
SQLite version Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> このように表されれば データベースに接続中．

16 操作 2/21 データベースとの接続を切断する． sqlite> .exit ct13140@sweb01[101]:

17 操作 3/21 再度自分用データベースに接続する． ct13140@sweb01[101]:sqlite3 ~/db/sane.db
SQLite version Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> このように表されれば データベースに接続中．

18 操作 4/21 存在する表を調べる(データベース接続中)． sqlite> .tables sqlite> 何も表示されずに
が表示されたら， 「表はない」ということ．

19 操作 5/21 表usertblを作る(データベース接続中)． sqlite> CREATE TABLE usertbl (
...> id integer, ...> name txt, ...> txt ...> ); sqlite> 使用したSQL文は CREATE TABLE usertbl (id integer, name text, text); 途中の改行はあってもなくてもよい． 最後の；(セミコロン)を忘れない様に．

20 「操作5」で作成した表が表示されれれば成功．
操作 6/21 存在する表を調べる(データベース接続中)． sqlite> .tables usertbl sqlite> 「操作5」で作成した表が表示されれれば成功．

21 操作 7/21 表にSELECTを実行する(データベース接続中)． 現在，表は空のはずである．
sqlite> SELECT * FROM usertbl; sqlite> 何も成功されなければ， 成功

22 操作 8/21 表にデータを入力する(データベース接続中)．
sqlite> INSERT INTO usertbl VALUES (0, 'fukuda', sqlite> SELECT * FROM usertbl; これがデータの挿入命令． 表示は特に変化なし． 途中改行はあってもなくてもよい． SELECTを実行して， 表の中身を確認してみる．

23 操作 9/21 表にデータを入力する(データベース接続中)．
sqlite> INSERT INTO usertbl VALUES (1, 'abe', sqlite> INSERT INTO usertbl VALUES (2, 'koizumi', sqlite> SELECT * FROM usertbl; sqlite>

24 操作 10/21 表diarytblを作る(データベース接続中).
sqlite> CREATE TABLE diarytbl (diaryid Integer, userid Integer, year Integer, month Integer, day Integer, sentence txt); sqlite> .tables diarytbl usertbl sqlite> 日記記事1個ごとに1行の表とする． diaryid:日記記事の通し番号 Userid:日記を書いたユーザのID year,month,day:日記の日付 sentence:日記本文

25 操作 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'); 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>

26 操作 12/21 現状の確認(データベース接続中). sqlite> SELECT * FROM usertbl;
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>

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>

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が勝手に決めてくる． 必ずしこの順になるとは限らない．

29 操作 15/21 内部結合(データベース接続中). sqlite> SELECT * FROM usertbl CROSS JOIN diarytbl; deshita deshita shita deshita deshita shita deshita deshita shita sqlite> userID = 2 の横に， userID=0 の書込を置いても， 意味が無い．

30 操作 16/21 内部結合(データベース接続中). sqlite> SELECT * FROM usertbl INNER JOIN diarytbl ON usertbl.id = diarytbl.userid; deshita deshita shita sqlite>

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>

32 操作 18/21 表の削除(データベース接続中). sqlite> DROP TABLE diarytbl;
sqlite> .tables usertbl sqlite> 表を削除すると， 表内のデータは全て消失するので注意．

33 操作 19/21 データベースとの接続を切断(データベース接続中). sqlite> .exit

34 データベース内の全てのデータが失われるので注意！！
操作 20/21 データベースの削除. ~/db/a.db データベースを削除すると， データベース内の全てのデータが失われるので注意！！ 頻繁に行う作業ではありません．

35 注意事項 RDBMSに接続中は， sqlite> と表示されるのが正常な状態です． ...>
などと表示されたのなら「コマンド入力途中」という変な状態です．これを正常に戻すには ;[Ent] などを押すと，(入力文はエラーとなるが)元に戻せる．

36 注意事項 DBのデータを読むには， DBにデータを書き込むには， DBファイルへの読込権限が必要． DBのファイルへの書込権限と，
Webなどで使用するときは後者を要注意！

37 Ruby で SQLite3

38 sqlite0.rb $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 赤字部は，(変更せずに)そのまま打ち込む． 青字部は，各自 適切な内容に変更する． これは，工学院大学固有の話です

39 sqlite0.rb 実行方法 /home/ct13140/app/ruby p247/bin/ruby -I/home/ct13140/app/sqlite3-ruby-1.3.1/lib/ruby/site_ruby/2.0.0/ sqlite0.rb これ↑は，1行です． /home/ct13140/app/ruby p247/bin//ruby -I/home/ct13140/app/sqlite3-ruby-1.3.1/lib/ruby/site_ruby/2.0.0/ ./sqlite0.rb Ruby version を使用してください。

40 sqlite1.rb #!/home/ct13140/app/ruby p247/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") # DBと接続する．ここでDBファイル名を指定． db.execute('select * from usertbl;') do | row | # この形↑でSQL文を文字列で送る.戻り値は2次元配列 つまり 2次元の表. puts row.join(" , ") end これは，工学院大学固有の話です

41 sqlite1.rb 実行方法 chmod 755 sqlite1.rb ./sqlite1.rb 実行権限を付与する．
1行目の内容を使ってこのプログラムは動く. つまり，これ↓ #!/home/ct13140/app/ruby p247/bin/ruby -I/home/ct13140/app/sqlite3-ruby-1.3.1/lib/ruby/site_ruby/2.0.0/ --

42 Ruby + CGI + SQLite3 データベースファイルを作成する ファイルとディレクトリに書込権限を与える
sqlite3 /home/ct13140/db/a.db SQLite version Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> .exit ファイルとディレクトリに書込権限を与える chmod 606 /home/ct13140/db/a.db chmod 707 /home/ct13140/db ディレクトリに与え忘れないように ディレクトリに書込権限を与えたら，他人がそのディレクトリに書き込めてしまう． 不必要な場所に書込権限を与えないように注意．

43 004.cgi #!/home/ct13140/app/ruby p247/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>'

44 005.cgi #!/home/ct13140/app/ruby p247/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"] = cgi[" "] print "Content-type: text/html\n\n" print uid," , ",name," , ", , "<br>\n"

45 005.cgi begin db = SQLite3::Database.new("/home/ct13140/db/a.db")
db.execute('insert into usertbl (id,name, ) values ('+uid+',\''+name+'\',\''+ +'\')') 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は本来は「ユーザ入力するもの」ではなく，「システムが自動で割り振るもの」だが， ご愛敬．．．

46 フィールド制約:NOT NULL NOT NULL制約 NULL値を入れられなくなる
sqlite> CREATE TABLE tbl0(id integer, name text NOT NULL, text); sqlite> INSERT INTO tbl0 VALUES (0,'sane',NULL); sqlite> INSERT INTO tbl0 VALUES Error: constraint failed これは， 正常に実行される． これは， エラーとなる．

47 フィールド制約:UNIQUE UNIQUE制約 同じ値を複数行に入れられなくなる
sqlite> CREATE TABLE tbl1(id integer UNIQUE, name text, text); sqlite> INSERT INTO tbl1 VALUES sqlite> INSERT INTO tbl1 VALUES Error: constraint failed これは， 正常に実行される． これは， エラーとなる．

48 フィールド制約:DEFAULT DEFAULT制約 入力略時には，DEFAULT値が採用される．
sqlite> CREATE TABLE tbl2(id integer, name text, text DEFAULT sqlite> INSERT INTO tbl2 (id,name) VALUES (0,'sane'); sqlite> select * from tbl2;

49 フィールド制約:PRIMARY KEY PRIMARY KEY制約 自動的に一意の値が割り振られる．
ただし，削除してしまった行の値は再利用される． sqlite> CREATE TABLE tbl3(id integer PRIMARY KEY, name text, text); sqlite> INSERT INTO tbl3 VALUES (NULL,'sane', sqlite> INSERT INTO tbl3 (name, ) VALUES sqlite> SELECT * FROM tbl3;

50 フィールド制約:ANTOINCREMENT
一意なIDの自動割り振り sqlite> CREATE TABLE usertbl2 (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, TEXT); sqlite> .tables usertbl usertbl2 sqlite> INSERT INTO usertbl2 (name, ) VALUES sqlite> INSERT INTO usertbl2 (name, ) VALUES sqlite> INSERT INTO usertbl2 VALUES (NULL, 'hoge', sqlite> SELECT * FROM usertbl2; sqlite> PRIMARY KEY かつ AUTOINCREMENT にしておくと，一度削除された値が 再利用されることがない．

51 フィールド制約:CHECK CHECK制約 値に制限をかけられる.
sqlite> CREATE TABLE tbl4(id integer CHECK(id>0), name text, text); sqlite> INSERT INTO tbl4 VALUES (1,'sane', sqlite> INSERT INTO tbl4 VALUES (0,'yasu', Error: constraint failed これは， 正常に実行される． これは， エラーとなる．

52 SQLiteコマンド (DBではなく) DBMSを制御するコマンド .help :ヘルプメッセージを表示 .dump ：データベースをダンプ
例:シェルにて sqlite3 a.db .dump | sqlite3 b.db .quit :SQLiteを終了 .tables :表の一覧を表示 例:「.tables」，「.tables us%」 .databases :データベース一覧を表示 .schema :表のスキーマ(型など)を表示.

53 無料でRDBMSの勉強をするには 講義ツールを使用(本学サーバ+SQLite3)
本学14階情報処理演習室 Microsoft Access． おすすめしない．AccessはSQLを使いづらい. 自分のWindows機に無料RDBMSをinstall 例:Windows版MySQL，Windows版PostgreSQL 自分のPCに無料OSと無料RDBMSをinstall 例:Linux+MySQL，Linux+PostgreSQL