UD/CRUD
Table of Contents
delete …
今FreeBSDのパッケージになってるrfcを更新してみたら、最新は、rfc9371だっ た。でも、古いものからの積み重ねになってんだよなあ。と言う事で、簡単な データベースを作成。これを肴にする。
sqlite3 test.db create table rfc(id integer,name text); insert into rfc(id,name) values(786,'mail'); insert into rfc(id,name) values(801,'TCP'); insert into rfc(id,name) values(1945,'HTTP');
前回は軽く触れたsqliteの動作ね。潜ってみたい。
#include <stdio.h>
#include "sqlite3.h"
int main(void){
sqlite3 *db;
char *filename="test.db";
int rc; //result codes
sqlite3_stmt *stmt;
rc = sqlite3_open(filename, &db);
rc = sqlite3_prepare_v2(db,"DELETE FROM rfc where id=2",
-1, &stmt, 0);
if(rc != SQLITE_OK){
printf("ERROR(%d) %s\n",rc, sqlite3_errmsg(db));
sqlite3_close(db);
return 2;
}
rc = sqlite3_step(stmt); // exec delete
if(rc != SQLITE_DONE){
printf("ERROR(%d) %s\n",rc,sqlite3_errmsg(db));
}
rc = sqlite3_finalize(stmt);
if(rc != SQLITE_OK){
printf("ERROR(%d) %s\n",rc,sqlite3_errmsg(db));
}
sqlite3_close(db);
return 0;
}
CRUDのうちのdeleteをやる。有り難くコードを利用させて貰う。Uも貴重です よ。普通はCRだけの説明が多いですからね。
cc -g -O0 rfc_del.c -L`pwd`/.libs -lsqlite3
で、こんな風にコンパイルしたら、バイナリーは無事に作成されたけど、ライ ブラリーが見付からないと言う実行時エラー。訳ワカメだったの で、.libs/libsqlite3.so.8.6を、/usr/libに入れてしまった。 ライブラリィーは、/home/sakae/sqlite-autoconf-3410000 か ら、–enable-debug付きで 作ったものだ。
実行の前に説明を聞いておく。
sqlite> explain DELETE FROM rfc where id=2; addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 12 0 0 Start at 12 1 Null 0 1 0 0 r[1]=NULL 2 OpenWrite 0 2 0 2 0 root=2 iDb=0; rfc 3 Explain 3 0 0 SCAN rfc 0 4 Rewind 0 11 0 0 5 Column 0 0 2 0 r[2]= cursor 0 column 0 6 Ne 3 10 2 BINARY-8 84 if r[2]!=r[3] goto 10 7 ReleaseReg 2 1 0 0 release r[2] mask 0 8 Rowid 0 4 0 0 r[4]=rfc.rowid 9 Delete 0 1 0 rfc 2 10 Next 0 5 0 1 11 Halt 0 0 0 0 12 Transaction 0 1 1 0 1 usesStmtJournal=0 13 TableLock 0 2 1 rfc 0 iDb=0 root=2 write=1 14 Integer 2 3 0 0 r[3]=2 15 Goto 0 1 0 0
説明ついでに、manから拾い出したのをのせておく。SQL文って、綺麗に清書し たのを沢山見るけど、一行野郎なんだね。
sqlite3 test.db 'select * from rfc;'
An Introduction To The SQLite C/C++ Interface まず、これを参照して全体 を把握する事だな。
try again in FreeBSD
FreeBSDで失敗して悔しいので再トライ。方針は、sqlite3系をちゃんとインス トールする。そして、禁断の設定をしてターゲットをコンパイル。
./configure --prefix=/home/sakae/MINE --enable-debug --enable-shared
こんな風にsqlite3を用意しる。自前のエリアに入れるんだな。
[sakae@fb ~/MINE/work]$ LD_LIBRARY_PATH=/home/sakae/MINE/lib cc -v -g -O0 rfc_del.c -I/home/sakae/MINE/include -L/home/sakae/MINE/lib -lsqlite3
ターゲットのコンパイル時にライブラリィーのエリアを指定。指定したからと 言って、-Lの指定は省略できない。
[sakae@fb ~/MINE/work]$ ldd a.out
a.out:
libsqlite3.so.0 => /home/sakae/MINE/lib/libsqlite3.so.0 (0x20443000)
libc.so.7 => /lib/libc.so.7 (0x206aa000)
libz.so.6 => /lib/libz.so.6 (0x2087c000)
libm.so.5 => /lib/libm.so.5 (0x20896000)
libthr.so.3 => /lib/libthr.so.3 (0x208d2000)
これで目的のエリアにあるライブラリィーがリンクされた。勿論ちゃんと機能 するよ。
[sakae@fb ~/MINE/work]$ gdb -q a.out
Reading symbols from a.out...
(gdb) b sqlite3_step
Breakpoint 1 at 0x401ad0
(gdb) r
Starting program: /usr/home/sakae/MINE/work/a.out
Breakpoint 1, sqlite3_step (pStmt=0x20966b78) at sqlite3.c:88538
88538 int rc = SQLITE_OK; /* Result from sqlite3Step() */
(gdb) bt 3
#0 sqlite3_step (pStmt=0x20966b78) at sqlite3.c:88538
#1 0x204cfff6 in sqlite3_exec (db=0x20934008,
zSql=0x20969ba8 "SELECT*FROM\"main\".sqlite_master ORDER BY rowid",
xCallback=0x20541820 <sqlite3InitCallback>, pArg=0xffbfdee0, pzErrMsg=0x0)
at sqlite3.c:131919
#2 0x20541655 in sqlite3InitOne (db=0x20934008, iDb=0, pzErrMsg=0xffbfe8e4,
mFlags=0) at sqlite3.c:137529
(More stack frames follow...)
但しemacsからgdbを起動した場合、
(gdb) b sqlite3_step Breakpoint 1 at 0x401ad0 (gdb) r Starting program: /usr/home/sakae/MINE/work/a.out Breakpoint 1, 0x2047e616 in sqlite3_step () from /usr/local/lib/libsqlite3.so.0
と、なってしまう。
[sakae@fb ~/MINE/work]$ export LD_LIBRARY_PATH=/home/sakae/MINE/lib [sakae@fb ~/MINE/work]$ mg rfc_del.c
こんな風に、場所を広告した上でemacsを使っても、その広告は、ターゲット までは届かずに、/usr/local/lib/libsqlte3の方が使われてしまっていた。相 性悪いな。
gud: how to set the LDLIBRARYPATH before run?
むむ、こんなのが有るぞ。
try ArchLinux
普通にemacsからgdbを起動すると、
Reading symbols from sqlite3... This GDB supports auto-downloading debuginfo from the following URLs: <https://debuginfod.archlinux.org> Enable debuginfod for this session? (y or [n]) Please answer y or [n]. : Enable debuginfod for this session? (y or [n]) n Please answer y or [n].
山本リンダ風に、どうにも止まらないになってしまう。こんなの初めての経験。 これを脱するには、
M-x gud-gdb と起動すればいいらしい。
Run gud-gdb (like this): gdb --fullname sqlite3
以下は、そうやった時のセッション。
(gdb) b sqlite3_exec
Breakpoint 1 at 0x1274cf: file sqlite3.c, line 131888.
(gdb) r doctor.db 'select * from Procs;'
Starting program: /home/sakae/sqlite-autoconf-3410000/sqlite3 doctor.db 'select * from Procs;'
This GDB supports auto-downloading debuginfo from the following URLs:
<https://debuginfod.archlinux.org>
Enable debuginfod for this session? (y or [n]) n
Debuginfod has been disabled.
To make this setting permanent, add 'set debuginfod enabled off' to .gdbinit.
[Thread debugging using libthread_db enabled]
Using host libthread_db library "/usr/lib/libthread_db.so.1".
Breakpoint 1, sqlite3_exec (db=0x5555557d9518,
zSql=0x5555557e8068 "SELECT*FROM\"main\".sqlite_master ORDER BY rowid",
xCallback=0x55555568410c <sqlite3InitCallback>, pArg=0x7fffffffbfe0,
pzErrMsg=0x0) at sqlite3.c:131888
(gdb)
案内にもあるように、set debuginfod enabled off を記述しておけとな。
sqlite3 script
.open test.db create table rfc(id integer,name text); insert into rfc(id,name) values(786,'mail'); insert into rfc(id,name) values(801,'TCP'); insert into rfc(id,name) values(1945,'HTTP'); .mode box .print 'orignl data' select * from rfc; .print 'update data for id:801' update rfc set name='TCP/IP' where id=801; .print 'delete data for mail' delete from rfc where id=786; .print 'after UD' select * from rfc;
こんな具合にドット・コマンドとSQL文の混合スクリプトを作成した。
[sakae@fb ~/MINE/work]$ sqlite3 SQLite version 3.40.1 2022-12-28 14:03:47 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> .read ud.sql orignl data ┌──────┬──────┐ │ id │ name │ ├──────┼──────┤ │ 786 │ mail │ │ 801 │ TCP │ │ 1945 │ HTTP │ └──────┴──────┘ update data for id:801 delete data for mail after UD ┌──────┬────────┐ │ id │ name │ ├──────┼────────┤ │ 801 │ TCP/IP │ │ 1945 │ HTTP │ └──────┴────────┘ sqlite>
起動して、中からスクリプトを読み込み(=実行)してみた。出来の悪いアップ デートとデリートの実例。
sqlite> .read ud.sql
Parse error near line 2: table rfc already exists
create table rfc(id integer,name text);
^--- error here
orignl data
┌──────┬────────┐
│ id │ name │
├──────┼────────┤
│ 801 │ TCP/IP │
│ 1945 │ HTTP │
│ 786 │ mail │
│ 801 │ TCP │
│ 1945 │ HTTP │
└──────┴────────┘
:
再度実行すると、既にDBは作成済なので、エラーと言われる。でも、実行でき ちゃった。
そういう難点を解消するには、起動時に、テーブルをdropしてしまえば良い。 そんな設定ファイルを用意して、sqlite3 –init xx.sql のように使えばよい。
Command Line Shell For SQLite に詳しい説明があるぞ。
csv to sqlite3
上のCLIの説明を見ていたら、簡単にCSVからデータを輸入できるようだ。
sqlite> .import --csv --skip 1 --schema temp C:/work/somedata.csv tab1
一応説明を参照しておく。
sqlite> .help import
.import FILE TABLE Import data from FILE into TABLE
Options:
--ascii Use \037 and \036 as column and row separators
--csv Use , and \n as column and row separators
--skip N Skip the first N rows of input
--schema S Target table to be S.TABLE
-v "Verbose" - increase auxiliary output
Notes:
* If TABLE does not exist, it is created. The first row of input
determines the column names.
* If neither --csv or --ascii are used, the input mode is derived
from the ".mode" output mode
* If FILE begins with "|" then it is a command that generates the
input text.
オイラーが貯めこんでいる血圧データから、朝の最高血圧だけを、年月と共に 採取。その冒頭には、ヘッダーをつけておいた。
#! /bin/sh
cat current.csv |
awk -F, \
'BEGIN{printf("yy,mm,hi\n")}
{if ($1 % 100 < 12)
{printf("%s,%s,%s\n", substr($1,1,2), substr($1,3,2), $2)}
}'
これを走らせて、一時ファイルのz.csvなんてのにリダイレクト。それから saliteに輸入しる。
$ sqlite3 my.db SQLite version 3.41.0 2023-02-21 18:09:37 Enter ".help" for usage hints. sqlite> .import --csv z.csv bld sqlite> .mode table sqlite> select * from bld limit 3; +----+----+-----+ | yy | mm | hi | +----+----+-----+ | 12 | 01 | 118 | | 12 | 01 | 128 | | 12 | 01 | 122 | +----+----+-----+
冒頭部分をちょいと確認。
sqlite> select yy, avg(hi) from bld group by yy; +----+------------------+ | yy | avg(hi) | +----+------------------+ | 12 | 119.644444444444 | | 13 | 119.050279329609 | | 14 | 122.433701657459 | :
次は年毎に平均を出してみた。無駄に桁数が多いな。こういう時は丸めればい いのか。ついでに、タイトルもasを使って置き換えてみた。roundは組み込み の関数。avgは組み込みの集計(aggregate)関数に分類されてた。
sqlite> select yy, round(avg(hi),2) as avg from bld group by yy; +----+--------+ | yy | avg | +----+--------+ | 12 | 119.64 | | 13 | 119.05 | | 14 | 122.43 | :
sqlite> select yy,mm,round(avg(hi),2) as avg from bld where yy=23 group by mm; +----+----+--------+ | yy | mm | avg | +----+----+--------+ | 23 | 01 | 135.71 | | 23 | 02 | 136.75 | +----+----+--------+
月毎の平均を求めると、何も指定しなければ、最初の年で終了してしまう。そ んな訳で、年をwhere句で強制指定してみた。
CREATE VIEW yma(yy, mm, avg) AS select L.yy, L.mm, round(avg(L.hi),2) from bld as L, bld as R where L.yy = R.yy and L.mm = R.mm group by L.yy, R.mm;
次は、こんなビューを作成。普通にやるだけで十分なんだけど、SQLパズルの 復習って事で。自分でやってみると、よく原理が分った。 原テーブルに別名のLとRをつける。左のテーブルと右のテーブルだな。where 句で、左右のそれぞれの列が等しいって条件を加味してselectする。 その結果は、合成したようなものだから、VIEWで新しいテーブルとして登録。
結果を確認。
sqlite> select * from yma limit 9,6; +----+----+--------+ | yy | mm | avg | +----+----+--------+ | 12 | 10 | 119.81 | | 12 | 11 | 122.5 | | 12 | 12 | 124.67 | | 13 | 01 | 124.03 | | 13 | 02 | 119.79 | | 13 | 03 | 116.81 | +----+----+--------+
これの答合わせはpythonエリアにお住まいのう北極熊さんにお願いかな。
ああ、.import時に勝手に作成されちゃったテーブルは、どういう形だろ？
CREATE TABLE IF NOT EXISTS "bld"( "yy" TEXT, "mm" TEXT, "hi" TEXT);
思った通りテキスト属性だった。それに対して、平均を計算するなんて、よき に計らえって方式。古くはawkやperlの流れをくんでいるな。pythonみたいに ツンツンしてない、大らかな所が好感だな。
tools
The sqlite3analyzer.exe Utility Program
sqlite3_analyzer.exe my.db /** Disk-Space Utilization Report For my.db Page size in bytes................................ 4096 Pages in the whole file (measured)................ 18 Pages in the whole file (calculated).............. 18 Pages that store data............................. 18 100.0% Pages on the freelist (per header)................ 0 0.0% Pages on the freelist (calculated)................ 0 0.0% Pages of auto-vacuum overhead..................... 0 0.0% Number of tables in the database.................. 2 Number of indices................................. 0 Number of defined indices......................... 0 Number of implied indices......................... 0 Size of the file in bytes......................... 73728 Bytes of user payload stored...................... 44627 60.5% :
このツールはMINGW64に紛れこんでいた。リナとか*BSDには入っていなかった ぞ。ソースは何処にあるのだろう？ Windowsだけが優遇されてるの？
sqlite3_analyzer.exe sqlite3_analyzer.sh こんなのが同梱されてるんだけ
どな。
$ pacman -Qi mingw-w64-x86_64-sqlite3
Name : mingw-w64-x86_64-sqlite3
Version : 3.41.0-1
Description : A C library that implements an SQL database engine (mingw-w64)
Architecture : any
URL : https://www.sqlite.org
Licenses : PublicDomain
Groups : None
Provides : mingw-w64-x86_64-sqlite=3.41.0
mingw-w64-x86_64-sqlite-analyzer=3.41.0
Depends On : mingw-w64-x86_64-gcc-libs mingw-w64-x86_64-readline
mingw-w64-x86_64-tcl mingw-w64-x86_64-zlib
Optional Deps : None
Required By : mingw-w64-x86_64-python
Optional For : None
Conflicts With : mingw-w64-x86_64-sqlite-analyzer
Replaces : mingw-w64-x86_64-sqlite-analyzer
Installed Size : 12.25 MiB
Packager : CI (msys2/msys2-autobuild/91ab3435/4335856811)
Build Date : Sun Mar 5 20:52:15 2023
Install Date : Mon Mar 6 15:12:42 2023
Install Reason : Installed as a dependency for another package
Install Script : No
Validated By : Signature
python君の朋友なのか。その親分はgdbだったりするんだけどね。
etc
Debuginfod service - Arch Linux
Debuginfod service - Arch Linux
(gdb) b main Breakpoint 1 at 0x52434: file shell.c, line 26946. (gdb) r Starting program: /tmp/sqlite-autoconf-3410000/sqlite3 Downloading separate debug info for /lib64/ld-linux-x86-64.so.2 Downloading separate debug info for system-supplied DSO at 0x7ffff7fc8000 Downloading separate debug info for /usr/lib/libedit.so.0 Downloading separate debug info for /usr/lib/libncursesw.so.6 Downloading separate debug info for /usr/lib/libz.so.1 Downloading separate debug info for /usr/lib/libm.so.6 Downloading separate debug info for /usr/lib/libc.so.6 [Thread debugging using libthread_db enabled] Using host libthread_db library "/usr/lib/libthread_db.so.1".
通常は使う事がない、デバッグ情報付きのライブラリィーを自動的にDLしてく れる、小粋なサービス。
[sakae@arch sqlite-autoconf-3410000]$ ldd sqlite3
linux-vdso.so.1 (0x00007ffc76ff8000)
libedit.so.0 => /usr/lib/libedit.so.0 (0x00007fb2fa48a000)
libncursesw.so.6 => /usr/lib/libncursesw.so.6 (0x00007fb2fa416000)
libz.so.1 => /usr/lib/libz.so.1 (0x00007fb2fa3fc000)
libm.so.6 => /usr/lib/libm.so.6 (0x00007fb2fa314000)
libc.so.6 => /usr/lib/libc.so.6 (0x00007fb2fa12d000)
/lib64/ld-linux-x86-64.so.2 => /usr/lib64/ld-linux-x86-64.so.2 (0x00007fb2fa75a000)