SQLite(エスキューライト)はC言語で使える軽量コンパクトなデータベース管理用のライブラリ関数である。 インストールも使い方も極めて簡単である。
SQLiteのオフィシャルページから ソースファイルsqlite-amalgamation-3071502.zipおよびDLLファイル sqlite-dll-win32-x86-3071502.zipをダウンロードする。2013年１月13日時点のバージョンは SQLite 3.7.15.2 である。
前者から ヘッダファイル sqlite3.h を取り出し、 コンパイラのincludeディレクトリにコピーする。
後者から sqlite3.dll を取り出し、 パスの通ったところにコピーする。 TCCでコンパイルする場合には、sqlite3.def を libディレクトリにコピーする。 ただし、TCCのdefファイルの規格に合わせるために、先頭行に LIBRARY sqlite3.dll を追加する必要がある。
MSVC で使うには、sqlite3.defから次のようにして sqlite3.lib を作成して、 LIBディレクトリに移す。
c:\mh\mcc>lib /def:sqlite3.def Microsoft (R) Library Manager Version 11.00.60315.1 Copyright (C) Microsoft Corporation. All rights reserved. LINK : warning LNK4068: /MACHINE の指定がありません。X86 を既定とします。 ライブラリ sqlite3.lib とオブジェクト sqlite3.exp を作成中インストール作業はこれで終わりである。
プログラムに組み込んでの使用では使わないが、 コマンドプロンプト画面でデータベース操作をするためには、 sqlite-shell-win32-x86-3071502.zip もダウンロードして、 sqlite3.exe をパスの通ったディレクトリに移して置く。
文献[1]を参考にして、次のテストプログラムを作成した。 MySQLの場合、一つのデータベースに対してたくさんのファイルが生成されるが、 SQLiteは１個だけである（下のプログラムでは sample.db）。
select文の場合、抽出されたレコードに対する処理はコールバック関数で行う。
#include <sqlite3.h>
#include <stdio.h>
#pragma comment(lib, "sqlite3.lib")
// 抽出結果が返るコールバック関数
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");
return 0; // success
}
void main(int argc, char* argv[]) {
sqlite3 *db;
char *zErrMsg;
// データベースファイルを新規生成
int rc = sqlite3_open("sample.db", &db);
// テーブル生成SQL文
char *create_sql
= "create table sample(id integer primary key, worker text not null, age integer, place text)";
// テーブルを生成
rc = sqlite3_exec(db, create_sql, 0, 0, &zErrMsg);
// データ追加
sqlite3_exec(db, "insert into sample values(0,'IKD',30,'Tokyo')", 0, 0, &zErrMsg);
sqlite3_exec(db, "insert into sample values(1,'Maru',null,null)", 0, 0, &zErrMsg);
sqlite3_exec(db, "insert into sample values(2,'Nacky',35,'Hukuoka')", 0, 0, &zErrMsg);
// "sample"テーブルの全項目を列挙
rc = sqlite3_exec(db, "select * from sample", callback, 0, &zErrMsg);
// データベースを閉じる
sqlite3_close(db);
}
TCCでコンパイルする場合、#pragma comment(lib, "sqlite3.lib") は、無視される。-lオプションで、DLL名を指定する必要がある(これにより、sqlite3.defファイルが参照される)。 コンパイル・実行した結果を下に示す。
c:\mh\www\db\sqlite>tcc sqlite01.c -lsqlite3 c:\mh\www\db\sqlite>sqlite01 id = 0 worker = IKD age = 30 place = Tokyo id = 1 worker = Maru age = NULL place = NULL id = 2 worker = Nacky age = 35 place = Hukuoka
データベース作成は sqlite3_open関数を使用する。 第1引数でデータベースファイルのパスを指定する。 存在しなければ、作成し、オープンする。 存在した時は、オープンする。拡張子は何でもよい。
sqlite3 *db;
int rc = sqlite3_open("sample.db", &db);
テーブル作成は sqlite3_exec関数を使用する。
char *zErrMsg; char *sql = "create table sample(id integer primary key, worker text not null, age integer, place text)"; int rc = sqlite3_exec(db, sql, 0, 0, &zErrMsg);
列（カラム）のデータ型を指定せずにテーブルを作成することができる。 この場合、SQLiteでセルに格納されるデータ型は次の５種類である。 同じ列に、異なるデータ型の値を格納することも可能である。
SQLite3で列に指定できるデータ型は「TEXT」「NUMERIC」「INTEGER」「REAL」「NONE」の5つである。 NONE型は入力データの型がそのまま保存される。
| NULL | NULL値 |
|---|---|
| INTEGER | 符号付整数。1, 2, 3, 4, 6, or 8 バイトで格納 |
| REAL | 浮動小数点数。8バイトで格納 |
| TEXT | テキスト。UTF-8, UTF-16BE or UTF-16-LEのいずれかで格納 |
| BLOB | Binary Large OBject。入力データをそのまま格納 |
テーブルcurrentを丸ごとコピーしたテーブルcurrent2012 を作成するには次のようにする。インデックスはコピーされない。
CREATE TABLE current2012 AS SELECT * FROM current;
ビューとは既に作成されたテーブルの中から条件に適合する一部分を取り出して作成する 仮想的なテーブルである。 基本構文は次の通りである。
CREATE VIEW ビュー名 AS SELECT文;
具体例を下に示す。 これは二つの表 current, stkprice から特定の列（コラム）だけを抜き出したものである。 二つの表に同じカラム名がある場合、current.コード のように前に 表名. を付けて区別する。 ユニークな名前の場合、表名を省略してもよい。
create view stklist as
select current.コード, 銘柄, 保有数, 取得単価, 現在値, 年月日, 現時刻
from current, stkprice
where current.コード=stkprice.コード;
update tablename set col1=val1, col2=val2 where col0=val0;
カラムの内容を関数で加工した場合などで AS句による名前付けが行われる。 例えば、次のような月別のデータを記録したテーブル mdata があったとする。 カラムyyyy_mm は text型、カラムvolは integer型とする。
yyyy_mm, mvol 2010-01, 1234 2010-02, 2323 ... 2012-12, 1414
年別に集計して、次のようなデータを得るにはどうするか。 答えは次のようなクエリ（ビュー）を作成・実行すればよい。 substr関数で６文字の年月カラムから年に当たる先頭の4文字を取り出している。 これに year という名前を付けている。 次の sum関数は group by 句とセットで使われる集計関数の一つである。 group by year によって、year の値が同じ行のカラムmvolの値の合計が計算される。 これにより、年間合計が計算される。
select substr(yyyy_mm,1,4) as year, sum(mvol) as yvol from mdata group by year;
仮想テーブル ビュー ydata として登録するには、次の SQL文を実行すればよい。
create view ydata as select substr(yyyy_mm,1,4) as year, sum(mvol) as yvol from mdata group by year;
yyyy, yvol 2010, 23456 2011, 12125 ... 2012, 20202
集計カラムが複数の場合、列方向の集計も求められることが多いだろう。 確か、MS-Accessでは、同時に行方向、列方向の集計を行えたと思うが、 MS-Access独自であり、標準のSQLの仕様にはないと思う。 最近、SQLに遠ざかっているので、確信はないが。
では、列方向の集計はどうするか？ 次に示すように、カラムの足し算を行えば合計を算出できる。
create view ydata as select substr(yyyy_mm,1,4) as year, sum(mvol1) as yvol1, sum(mvol2) as yvol2, sum(mvol1+mvol2) as total from mdata group by year;
yyyy, yvol1, yvol2, total 2010, 23411, 5922, 29333 2011, 12125, 4605, 16730 ... 2012, 20202, 18184, 38384
詳細は SQLite Autoincrement にあるように、SQLiteではテーブルの全ての行がROWIDとよばれるユニークな行識別番号を持つ。 このROWIDはあたかもカラムのように扱える。 SELECT文などで取り出すことができる。また、UPDATE文で行の特定に使うことができる。
テーブルが整数型カラムに主キー(Primary Key)を持つ場合、これが ROWID に当たる。 主キーが無い場合、あるいは主キーをもつセルが整数型でない場合、 システムが自動的に ROWID を割り当てる。
例えば、この ROWID と全カラムを抽出するには次のようにする。
sqlite> select rowid,* from master; 0|0|-|master|1|2|0|0 1|1|USD|usdjpy|162|0|170|2 2|2|EUR|eurjpy|118|0|126|4 sqlite>
このテーブルmasterでは最初の整数型カラムid を主キーとしていたため、 最初のカラムと次のカラムは全く同じものを表す。
表エディタなどでは、行を指定した更新処理が必要となる。 一般には、主キーは最初のカラムとは限らないし、主キーを持たないテーブルもある。 従って、上のようにテーブルのデータを取り出すと、更新処理が容易となる。
更新は、もちろん、 テーブルmasterのカラム名idを使って update文を実行してもよい。
sqlite> update master set id=5 where id=2; sqlite> select rowid,* from master; 0|0|-|master|1|2|0|0 1|1|USD|usdjpy|162|0|170|2 5|5|EUR|eurjpy|118|0|126|4
しかし、次のように、rowid を使うことにすれば、 主キーの名前、あるいは主キーの有無に関わらず、特定の行を更新できる。
sqlite> update master set id=2 where rowid=5; sqlite> select rowid,* from master; 0|0|-|master|1|2|0|0 1|1|USD|usdjpy|162|0|170|2 2|2|EUR|eurjpy|118|0|126|4
整数型セルに主キーを持つテーブルの場合、主キーのカラムを変更すると、 ROWIDの値も変わることに注意を要する。 例えば表エディタで、最初にメモリ上に表データを読み込むとする。 セルデータの修正があった場合、UPDATE文で行の指定(WHERE句)には、 ROWIDを使うことになる。 ある行の主キーのカラムを変更した場合、ROWIDも変わったことになるから、 この行をUPDATE文で再度変更する場合、この行のROWIDの値は最初の変更のときの値ではない。
つまり、一回のSELECT文に対して、UPDATE文を２回以上実行できるようにするには、 主キーをもつテーブルの場合、そのカラム名（またはカラム番号）を知っておかねばならない。
UPDATEを実行する度にその行に対して、SELECT文を実行して、 その行の全カラムのデータを読み込んでおくならば、主キーの有無、カラム名を知る必要はない。
SQLiteの場合、データベースには sqlite_master テーブルが自動的に作られる。 ここに、データベースの構造に関わる情報が格納されている。
例えば、あるデータベースで
select * from sqlite_master where type='table';を実行すると、次のような結果が得られる。
| type | name | tbl_name | rootpage | sql |
|---|---|---|---|---|
| table | log | log | 2 | CREATE TABLE log(id integer primary key, secs integer, ip integer, pid integer) |
| table | path | path | 3 | CREATE TABLE path(id integer primary key, name text) |
| table | daily | daily | 4 | CREATE TABLE daily(days integer primary key, alls integer) |
テーブル名だけを取り出すには select name from sqlite_master where type='table'; とする。 データ型などカラム属性は sql文から解釈しなければならない。
selectではなく "pragma info_table(テーブル名);" を使えば、 テーブル情報が表形式で得られることが分かった。詳細はこの節の末尾に記す。
例えば、どのような CREATE TABLE文でテーブルが生成されたかは、 次のようにして知ることができる。
上のデータベースとは別のデータベースの例を示す。
sqlite> select sql from sqlite_master where type='table' and name='master';
CREATE TABLE 'master' (
id INTEGER PRIMARY KEY DEFAULT '',
tabname TEXT DEFAULT '',
tablename TEXT DEFAULT '',
toprow INTEGER DEFAULT '',
topcol INTEGER DEFAULT '',
currow INTEGER DEFAULT '',
curcol INTEGER DEFAULT ''
)
sqlite> select sql from sqlite_master where type='table' and name='usdjpy';
CREATE TABLE usdjpy(wdate integer primary key, open real, high real, low real, c
lose real)
TkSQLite でCSVファイルをインポートして、テーブル名walkを付加した段階では
sqlite> select sql from sqlite_master where type='table' and name='walk';
CREATE TABLE 'walk' (
'日付' text,
'歩数' text
)
である。TkSQLiteで、データ型を変更し、日付カラムに主キーを付与すると、
sqlite> select sql from sqlite_master where type='table' and name='walk';
CREATE TABLE 'walk' (
'日付' DATETIME PRIMARY KEY,
'歩数' INTEGER
)
になる。
以上から、大文字、小文字の違い、改行の有無は実行した命令に依存するが、 変更は反映されている。 したがって、主キーをもつ整数型カラムを見つけるのは、それほど難しいわけではない。
PRAGMA命令を使えば、 テーブル情報(カラム名、データ型、NotNull、Default Value、Primary Key)が 得られる。
c:\mh\mcc>sqlite3 ../_rdb/accesslog.db SQLite version 3.7.15.2 2013-01-09 11:53:05 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> PRAGMA table_info(log); 0|id|integer|0||1 1|secs|integer|0||0 2|ip|integer|0||0 3|pid|integer|0||0 sqlite>
C言語インタフェースレベルでは、 もっと簡単にテーブルのスキマーを得るsqlite3_table_column_metadata関数が用意されている。
sqliteで管理しているデータをcsvファイルに出力する必要が生じた。 対象テーブルが多いことと、同じことを再び実行する必要があるかも知れないことから、 バッチ処理を行うことにした。
クエリやSQLite3コマンドを書いたファイル query.txt を用意し、 次のようにリダイレクトして実行すればよい。
c:\sys>sqlite3 sample.db < query.txt
今回使用したバッチファイルの内容を下に示す。
[query.txt].header on .mode csv .output c:/ttm/audjpy_d.csv select * from audjpy_d; .output c:/ttm/audjpy_w.csv select * from audjpy_w; .output c:/ttm/audjpy_m.csv select * from audjpy_m; [後略]
ここで .headerコマンドはカラム名行を出力するか否かをコントロールするものである。 デフォルトは off である。 出力される csvファイルの一つ audjpy_d.csv の内容を下に示す。
[audjpy_d.csv]wdate,open,high,low,close 20120703,81.414,82.171,81.272,82.046 20120704,82.06,82.169,81.839,82.037 20120705,82.016,82.347,81.609,82.197 [中略] 20131204,93.614,93.681,91.789,92.391
.mode csv TABLE_NAME # エクスポート SELECT * FROM TABLE_NAME # インポート .import sample.csv TABLE_NAME