累積の奧義

在此備忘一下，如何使用 C 語言存取 SQLite，
以下的程式碼，是一個可以讓我們輸入 SQL 語法，
來對 SQLite Database 進行新增，查詢，修改，刪除資料表內容與新增資料表的程式。

#include <stdio.h>
#include <stdlib.h>
#include <sqlite3.h>

static int callback(void *NotUsed, int argc, char **argv, char **azColName){
  int i;
  for (i=0; i<argc; i++) {
    printf("%s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL");
  }
  printf("\n");
  return 0;
}

int main(int argc, char** argv) {
  sqlite3 *db;
  char *zErrMsg = 0;
  int rc; 

  if (argc != 3) {
    fprintf(stderr, "Usage: %s Database SQL-Statment\n", argv[0]);
    exit(1);
 }

  rc = sqlite3_open(argv[1], &db);

  if (rc) {
    fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
    sqlite3_close(db);
    exit(1);
  }

  rc = sqlite3_exec(db, argv[2], callback, 0, &zErrMsg);

  if (rc != SQLITE_OK) {
    fprintf(stderr, "SQL error: %s\n", zErrMsg);
    sqlite3_free(zErrMsg);
  }

  printf("The SQL statement is executed successfully. Please check the database file!\n");
  sqlite3_close(db);

  return 0;
}

編譯方式：
記得加上 " -lsqlite "，若出現如下錯誤，表示沒有安裝 libsqlite3-dev，用apt-get裝一下吧。

[lego@lego-Chen SQLite]$gcc mySqlite.c -lsqlite -o mySqlite
sqlite.c:3:21: fatal error: sqlite3.h: No such file or directory

測試程式：

// 建立資料庫 sqlite.db 與資料表 mytable
[lego@lego-Chen SQLite]$./mySqlite sqlite.db "create table mytable(id int, name char(10));"
The SQL statement is executed successfully. Please check the database file!

// 新增一筆資料
[lego@lego-Chen SQLite]$./mySqlite sqlite.db "insert into mytable values(1, 'lego');"
The SQL statement is executed successfully. Please check the database file!

// 測試資料是否有被新增
[lego@lego-Chen SQLite]$./mySqlite sqlite.db "select * from mytable;"id = 1
name = lego

The SQL statement is executed successfully. Please check the database file!

一直很想玩這玩意，看了一下，果然是個好玩意！！
雖然我對資料庫還很嫩，不過他有幾個吸引我的地方：
1. 可攜性與跨平台，SQLite Database就是一個檔案，只要你建立的SQLitey資料庫檔案，
Copy 到別的支援 SQLite 的平台上，就可以立即享用。
2. 很方便使用 shell 的管線與資料流重導以及 shell 的字串處理指令，來完成多種應用。

——————————————

當我們安裝完 SQLite 後 （Ubuntu：apt-get install sqlite）
我們就可以使用 sqlite 指令，來使用 SQLite 資料庫。

當你單獨執行 sqlite 指令時，便會進入 SQLite 的 shell 模式，
在該 shell 模式中，有兩種指令可以使用：
1. 直接使用 SQL 語法來建立資料表或新增，刪除，修改，查詢資料表的內容，記得結尾要加上 ";" 。
2. SQLite3 專有的指令，皆以 . 開頭的指令。

以下說明一下，怎麼使用 sqlite 指令來建立資料庫，建立資料表，並新增，刪除，修改，查詢資料表的內容
為了方便說明，亦會穿插一些sqlite內建管理指令(以 . 開頭）。

//  <--  建立資料庫  -->
// 單獨執行 sqlite 指令，便會進入 SQLite 的 shell 模式
// 後面的 SQLite.db 為資料庫檔案的名稱，
// 若該資料庫檔案已存在的話，表示你要使用該資料庫檔案，對該資料庫檔案進行操作。
// 若該資料庫檔案不存在的話，則會在你為該資料庫建立第一張表的時候，
// sqlite 會自動為你建立該資料庫檔案。
// 在此我尚未建立過 SQLite.db 這個資料庫檔案
[lego@lego-Chen SQLite]$sqlite SQLite.db
SQLite version 2.8.17
Enter ".help" for instructions
sqlite>

//  <--  建立第一張資料表  -->
sqlite> create table mytable (id int, name char(10), age int, sex char(5));

// 驗證資料表是否正確被建立
// .tables 是用來看該資料庫中有幾個資料表
sqlite> .tables
mytable

// .schema 是用來看該資料庫中資料表的結構
// 後面可以指定你要看哪一個資料表的結構(ex: .scheam mytable)，
// 不加參數的話就是看所有資料表的結構。
sqlite> .schema
create table mytable (id int, name char(10), age int, sex char(5));

//  <--  新增3筆資料  -->
sqlite> insert into mytable values(1, '王小明', 18, '男');
sqlite> insert into mytable values(1, '李小四', 32, '男');
sqlite> insert into mytable values(2, '黃小仙', 28, '女');

//  <--  查詢所有資料表的內容  -->
sqlite> select * from mytable;
1|王小明|18|男
1|李小四|32|男
2|黃小仙|28|女

//  <--  查詢資料表中 id == 1 的內容  -->
sqlite> select * from mytable where id = 1;
1|王小明|18|男
1|李小四|32|男

//  <--  修改資料中的內容，將黃小仙的年紀改為 18  -->
sqlite> update mytable set age = 18 where name = '黃小仙';
// 看看是否修改成功
sqlite> select * from mytable where name = '黃小仙';
2|黃小仙|18|女
sqlite> select age from mytable where name = '黃小仙';
18

//  <--  刪除一筆資料，刪除名字為李小四的資料  -->
sqlite> delete from mytable where name = '李小四';

// 驗證刪除的結果是否正確
sqlite> select * from mytable where name = '李小四';
sqlite> select * from mytable where id = 1;
1|王小明|18|男

經由以上一連串的語法，應該可以掌握基本的使用方式了。

接下來就備忘一下幾個 SQLite 內建指令的功能，在此依然延用上面所建立的資料庫：
(其中.tables與.schema上面已經有示例)

.mode 指令：可調整輸出格式，有下列幾種格式，預設為 list 模式：
"line(s)", "column(s)", "insert", "list", or "html"
測試如下：

//  <--  list mode  -->

sqlite> .mode list
sqlite> select * from mytable;
1|王小明|18|男
2|黃小仙|18|女

//  <--  html mode  -->

// 輸出 html 格式，可搭配 CGI 做應用
sqlite> .mode html
sqlite> select * from mytable;
<TR><TD>1</TD>
<TD>王小明</TD>
<TD>18</TD>
<TD>男</TD>
</TR>
<TR><TD>2</TD>
<TD>黃小仙</TD>
<TD>18</TD>
<TD>女</TD>
</TR>

//  <--  insert mode  -->

// 可以看該資料表的插入語法結構
sqlite> select * from mytable;
INSERT INTO table VALUES(1,'王小明',18,'男');
INSERT INTO table VALUES(2,'黃小仙',18,'女');

//  <--  column mode  -->

// 此模式的輸出，可讀性是最好的，
// 還可以藉由  .header ON|OFF 來選擇開啟表頭欄位。
//           .width 來調整每一個欄位的顯示寬度。
sqlite> .mode column
sqlite> select * from mytable;
1           王小明   18          男
2           黃小仙   18          女

// 啟用 header
sqlite> .header on
sqlite> select * from mytable;
id          name        age         sex
----------  ----------  ----------  ----------
1           王小明   18          男
2           黃小仙   18          女

// 調整欄位寬度

sqlite> .width 2 4 3 3
sqlite> select * from mytable;
id  name  age  sex
--  ----  ---  ---
1   王   18   男
2   黃   18   女

.output：設定輸出結果的流向，預設是 stdout，可以將資料流輸出至檔案

// 將輸出流導入 log 檔案
sqlite> .output log

// 執行完該 SQL 語法後，會發現畫面中沒有任何輸出訊息
sqlite> select * from mytable;

// 退出 sqlite shell
sqlite> .exit

// 會看到本目錄下產生了一個 log file，其內容則為剛剛 SQL 語句輸出內容
[lego@lego-Chen SQLite]$cat log
id  name  age  sex
--  ----  ---  ---
1   王   18   男
2   黃   18   女

// 若要改回螢幕輸出 .output stdout

.dump：可用來備份資料庫 （重要）

// 首先將輸出流設定為你要備份的檔名(ex: SQLite_backup.dump)
sqlite> .output SQLite_backup.dump

// 備份，將指令語法輸出至 SQLite_backup.dump
sqlite> .dump

// 離開 SQLite shell, 並檢查 dump file
sqlite> .exit
[lego@lego-Chen SQLite]$cat SQLite_backup.dump
BEGIN TRANSACTION;
create table mytable (id int, name char(10), age int, sex char(5));
INSERT INTO mytable VALUES(1,'王小明',18,'男');
INSERT INTO mytable VALUES(2,'黃小仙',18,'女');
create table mytable2 (id int, name char(10), age int, sex char(5));
COMMIT;

—————————————

sqlite 指令的其他用法，不進入 SQLite shell，直接進行操作，指令格式如下：
sqlite {參數一：資料庫檔案} {參數二：sqlite內建指令 or SQL語法}

舉例1 ：

[lego@lego-Chen SQLite]$sqlite SQLite.db "select * from mytable;"
1|王小明|18|男
2|黃小仙|18|女

舉例2 :

[lego@lego-Chen SQLite]$sqlite SQLite.db ".schema mytable"
create table mytable (id int, name char(10), age int, sex char(5));

舉例3 : 備份資料庫 （之前在shell裡備份的方法，有點蠢）

[lego@lego-Chen SQLite]$sqlite SQLite.db ".dump" > SQLite_backup.dump

由上可知，sqlite指令，可以直接在參數中，下達SQL語法與其內建指令，
因此方便我們用 shell script, awk, sed …等 Linux 字串處理指令，
來進一步處理這些資料，非常方便。

練習一下怎麼用 Perl 來存取 SQLite，在此紀錄一下實作過程中的版本對決的問題。

使用之前先來建立一個 SQLite 資料庫：

// 進入SQLite的shell模式
[lego@lego-Chen SQLite]$sqlite lego.db
SQLite version 2.8.17
Enter ".help" for instructions
// 建立一個資料表 t
sqlite> create table t (id int, name char(10));
// 插入兩筆資料 1. id = 5, name = lego 2. id = 2, name = kenis
sqlite> insert into t values(5, 'lego');
sqlite> insert into t values(2, 'kenis');
// 檢查資料表內容是否如我們預期
sqlite> select * from t;
5|lego
2|kenis
// 退出 SQLite shell 模式
sqlite> .exit
[lego@lego-Chen SQLite]$

下面的 perl script 可檢查你的 perl 是否有支援 SQLite

#!/usr/bin/perl

use DBI;
my @drvnames=();
@drvnames = DBI->available_drivers();
print "drivers installed:\n";

foreach my $dn (@drvnames) {
	print "$dn\n";
}

然而我在運行上述 Perl Script 時，出現了如下錯誤：（表示沒有安裝libapache-dbi-perl套件）

Can't locate DBI.pm in @INC (@INC contains: /etc/perl /usr/local/lib/perl/5.10.1 /usr/local/share/perl/5.10.1 /usr/lib/perl5 /usr/share/perl5 /usr/lib/perl/5.10 /usr/share/perl/5.10 /usr/local/lib/site_perl .) at /opt/legohome//DBI_Check.pl line 3.
BEGIN failed--compilation aborted at /opt/legohome//DBI_Check.pl line 3.

使用 apt-get install libapache-dbi-perl 後，即可運行上述的 perl script，
結果如下：

drivers installed:
DBM
ExampleP
File
Gofer
Proxy
Sponge

～～～沒有看到 SQLite，表示目前的 perl 不支援，
因此 apt-get install libdbd-sqlite3-perl 裝一下吧，
安裝完後在 Run 一次 perl-sqlite_check.pl：

[lego@lego-Chen ~]$ perl-sqlite.pl
drivers installed:
AnyData
DBM
ExampleP
File
Gofer
Proxy
SQLite
Sponge

用一個 perl script 來 insert 一筆資料到剛剛建立的 SQLite Database (lego.db)

#!/usr/bin/perl

use DBI;
#use strict;

my $dbargs = {AutoCommit => 0,
              PrintError => 1};
my $dbh = DBI->connect("DBI:SQLite:dbname=test.db", "", "", $dbargs);
#插入一筆資料 id = 1, name = ptrancer
$dbh->do("INSERT INTO t VALUES (1, 'ptrancer')");

if ($dbh->err()) {
  die "$DBI:errstr\n";
}
$dbh->commit();
$dbh->disconnect();
print "I have inserted one record\n";
exit;

若出現如下錯誤，表示你安裝的 sqlite 版本跟你 perl 的 sqlite 模組版本不匹配

[lego@lego-Chen SQLite]$./basic_demo.pl
DBD::SQLite::db do failed: file is encrypted or is not a database at ./basic_demo.pl line 10.
:errstr

基本上，在你安裝 perl 的 sqlite 模組時，由套件的檔名就可以知道你的sqlite模組版本，
ex: libdbd-sqlite3-perl，就是 SQLite3。
而你系統上安裝的 SQLite 版本，則可以直接輸入SQLite指令去查看：

[lego@lego-Chen SQLite]$sqlite
SQLite version 2.8.17   （就在這）
Enter ".help" for instructions
sqlite>

由結果可知我 perl 的 SQLite module 是 第三版，系統的 SQLite 是第二版，
因此會出現上述問題，所以就更新一下系統的 SQLite 版本吧（apt-get install sqlite3）。
在執行一次 basic_demo.pl

DBD::SQLite::db do failed: file is encrypted or is not a database at ./basic_demo.pl line 10.
:errstr

很遺憾，發現問題依舊～XD…
原來是因為，我們之前建立lego.db時，系統的SQLite版本還是Ver.2，
因此我們要將 lego.db 由 2版 轉到 3版，指令如下（參考網址）：
小記一下：SQLite3的指令是 /usr/bin/sqlite3, 原本的 /usr/bin/sqlite 依然還是 2.x版的，
因此往後建立 Database 時，要記得用 sqlite3 指令。

// 首先用 Ver.2 的 sqlite 指令備份 lego.db
[lego@lego-Chen SQLite]$sqlite lego.db .dump > dump_lego.db
// 把原本的 ver.2 lego.db 砍掉
[lego@lego-Chen SQLite]$rm -rf lego.db
// 用 Ver.3 的 sqlite3 指令，將剛剛備份的資料庫內容，重新建立一個 lego.db
[lego@lego-Chen SQLite]$sqlite3 lego.db < dump_lego.db  

在執行一次 basic_demo.pl

// 先確認目前資料表 t 的內容
[lego@lego-Chen SQLite]$sqlite3 lego.db "select * from t;"
 5|lego
 2|kenis
// 執行 ./basic_demo.pl
[lego@lego-Chen SQLite]$./basic_demo.pl
I have inserted one record
// 再看一次資料表 t 的內容
[lego@lego-Chen SQLite]$sqlite3 lego.db "select * from t;"
 5|lego
 2|kenis
 1|ptrancer   // 新增成功

用一個 perl script 來 select lego.db 的內容

#!/usr/bin/perl

use DBI;

my $dbargs = {AutoCommit => 0,
              PrintError => 1};
// 連線資料庫
my $dbh = DBI->connect("dbi:SQLite:dbname=lego.db", "", "", $dbargs);

if ($dbh->err()) {
  die "$DBI:errstr\n";
}

// 宣告兩個變數 id, name
my $id;
my $name;
my $sql = qq{ select * from t};
my $sth = $dbh->prepare($sql);
$sth->execute();

// 將取得的id欄位資料放到id變數中, name欄位資料放到name變數中
$sth->bind_columns(undef, \$id, \$name);

// 一次取得一筆資料
while ($sth->fetch()) {
  print "id: $id name: $name\n";
}
exit;

執行結果：

[lego@lego-Chen SQLite]$./basic_demo2.pl
id: 5 name: lego
id: 2 name: kenis
id: 1 name: ptrancer

//———————————————————–//
總結一下上述的本版問題：
其實用 perl 存取 SQLite DB 的時候，可以直接在 perl 中建立資料庫，建立資料表，新增，查詢， 修改，
刪除資料表每一筆資料的內容。
此時就不需要動用到系統上的 sqlite 指令，因此也就不會有這個所謂的版本衝突存在。
這次會遇到的版本問題，是因為我用系統的 sqlite 指令，來建立 SQLite DB，
且系統上面安裝的 SQLite 與 perl 使用的 SQLite模組，版本不同所導致，
不過也因為這樣子胡搞瞎搞，學到了一些經驗，但為了一致性，避免麻煩的話，
全都在 Perl 裡做，還是比較好的方法。

關於在 perl 裡，建立資料庫，建立資料表，新增，查詢，修改，刪除資料表每一筆資料的內容可參考下列程式碼：
該程式碼引用至：http://szabgab.com/talks/fundamentals_of_perl/database-using-dbi.html

#!/usr/bin/perl use strict; use warnings; use Getopt::Long qw(GetOptions);
use DBI qw();
my $action; GetOptions("action=s" => \$action);
if (not $action or $action !~ /^(create|insert|selecta|selecth)$/) {
    print <connect("dbi:SQLite:dbname=$dbfile","","", {});
if ($action eq "insert") {
    insert();
}
if ($action eq "selecta") {
    fetch_arrays();
}
if ($action eq "selecth") {
    fetch_hashref();
}

sub create {
    unlink $dbfile if -e $dbfile;
    my $dbh = DBI->connect("dbi:SQLite:dbname=$dbfile","","", {});
    $dbh->do("CREATE TABLE people (id INTEGER PRIMARY KEY, fname VARCHAR(100), lname VARCHAR(100))");
    return;
}

sub insert {
    $dbh->do("INSERT INTO people (id, fname, lname) VALUES(?, ?, ?)", undef, 1, "Gabor", "Szabo");
    $dbh->do("INSERT INTO people (id, fname, lname) VALUES(?, ?, ?)", undef, 2, "Josef", "Kiss");
    return;
}

sub fetch_arrays {
    my $sth = $dbh->prepare("SELECT lname, fname FROM people WHERE id = ?");
    $sth->execute(1);
    while (my @result = $sth->fetchrow_array()) {
        print "lname: $result[0], fname: $result[1]\n";
    }
    $sth->finish;
    return;
}

sub fetch_hashref {
    my $sth = $dbh->prepare("SELECT lname, fname FROM people WHERE id = ?");
    $sth->execute(1);
    while (my $result = $sth->fetchrow_hashref("NAME_lc")) {
        print "lname: $result->{lname}, fname: $result->{fname}\n";
    }
    $sth->finish;
    return;
}

不錯的範例參考網站：

http://szabgab.com/talks/fundamentals_of_perl/database-using-dbi.html

http://quickies.andreaolivato.net/post/123847804/perl-and-sqlite3-quick-start-example-guide

http://search.cpan.org/~adamk/DBD-SQLite-1.35/lib/DBD/SQLite.pm