今まで作ってきた売り上げ管理システムのレコードを一旦クリアーにして実データを入れてみることにします。
売り上げ管理システムのsql文の構築
今まで作ってきたsql文のレコードを含めて構築するには、dumpコマンドを使います。但し、ここでは、レコードをすべてリセットしたいので、schemaコマンドを使います。
- 新しく構築する空のデータベースを作成する。
- sql文をschemaコマンドを使ってテキストファイルとして出力する。
- テキストファイルを修正する。
- readコマンドでテキストファイルのsql文を空のデータベースで実行する。
- レコードを挿入する。
新たにデータベースを作成する
takai@takai-pc ~/db/sales_manage
$ mkdir sales_manage_pro
takai@takai-pc ~/db/sales_manage
$ cd sales_manage_pro
takai@takai-pc ~/db/sales_manage/sales_manage_pro
$ sqlite3 sales_manage_pro.sq3
SQLite version 3.34.0 2020-12-01 16:14:00
Enter ".help" for usage hints.
sqlite> .quitsql文をテキストファイルに書き出す
今まで作成してきたsql文のレコードを除いたものをテキストファイルとして書き出します。ここでは、主に参照されるテーブル作成に係わるものを fixed_sql.txt、書き込みを行うテーブルに係わるテーブル作成のものをnonfixed_sql.txtとして書き込みます。
---- sql文を構築してきたディレクトリに移動する
takai@takai-pc ~/db/sales_manage/sales_manage_pro
$ cd ..
---- データベースを開きます
takai@takai-pc ~/db/sales_manage
$ sqlite3 sales_manage.sq3
SQLite version 3.34.0 2020-12-01 16:14:00.
Enter ".help" for usage hints.
---- 作成されているテーブルを確認する
sqlite> .tables
branch chglog_details details orders
buyer chglog_price goods view_orders
---- 出力先をfixed_sql.txtにする
sqlite> .output ./fixed_sql.txt
---- データベースを格納するテキストファイルの文字コードを調べる
sqlite> pragma encoding;
UTF-8
---- 参照とされるテーブルを出力先に出力する
sqlite> .schema buyer
sqlite> .schema branch
sqlite> .schema goods
sqlite> .schema chglog_price
---- 出力先を標準出力である画面に戻す
sqlite> .output stdout
---- 出力されたファイルの中身を確認する
sqlite> .shell cat fixed_sql.txt
CREATE TABLE buyer (id integer primary key not null, name text not null, del_flag integer -default false check(del_flag=false or del_flag=true));
CREATE INDEX buyer_index on buyer(name);
CREATE TABLE branch (id integer primary key not null, name text not null, del_flag integer default false
check(del_flag=false or del_flag=true));
CREATE TABLE goods (code integer primary key not null, name text not null, price integer not null, del_flag integer default false check(del_flag=false or del_flag=true));
CREATE TRIGGER tri_chg_price after update of price on goods
begin
insert into chglog_price values(
null,
old.code,
old.price,
new.price,
date('now', 'localtime')
);
end;
CREATE TRIGGER tri_goods_del after update of del_flag on goods
when old.del_flag=false and new.del_flag=true
begin
insert into chglog_price values(
null,
old.code,
old.price,
0,
date('now', 'localtime')
);
end;
CREATE TABLE chglog_price (
id integer primary key not null,
goods_id integer not null,
old_price integer,
new_price integer,
created_date text
);
---- 同様に、nonfixed_sql.txtに書き込み中心のテーブルに係わるsql文を出力する
sqlite> .output ./nonfixed_sql.txt
sqlite> .schema orders
sqlite> .schema details
sqlite> .schema chglog_details
sqlite> .schema view_orders
sqlite> .output stdout
---- ファイルの中身を確認する
sqlite> .shell cat nonfixed_sql.txt
CREATE TABLE orders (id integer priamry key not null,
buyer_id integer not null,
publish text,
branch_id integer not null,
amount integer default 0, iud_mark text
check(iud_mark is null or
iud_mark='i' or
iud_mark='u' or
iud_mark='d'
),
foreign key(buyer_id) references buyer(id)
foreign key(branch_id) references branch(id)
);
CREATE TRIGGER tri_publish_chk after insert on orders
begin
select
case
when (select date(new.publish)) is null
then raise(rollback, 'publish date() return null !')
when (select ((strftime('%d', new.publish))='31')) then
case when (select ((strftime('%m', new.publish)) in ('02', '04', '06', '09', '11')))
then raise(rollback, 'publish undefined day=31 set !')
end
when (select ((strftime('%d', new.publish))='30')) then
case when ((select ((strftime('%Y', new.publish)))%4) != 0)
then raise(rollback, 'publish format 02-29 unleap day !')
when ((select ((strftime('%Y', new.publish)))%100) = 0 and (select ((strftime('%Y', new.publish)))%400) != 0)
then raise(rollback, 'publish format 02-29 unleap special day !')
end
end
end;
end;
CREATE TRIGGER tri_publish_chk_u after update of publish on orders
begin
select
case
when (select date(new.publish)) is null
then raise(rollback, 'publish date() return null !')
when (select ((strftime('%d', new.publish))='31')) then
case when (select ((strftime('%m', new.publish)) in ('02', '04', '06', '09', '11')))
then raise(rollback, 'publish undefined day=31 set !')
end
when (select ((strftime('%d', new.publish))='30')) then
case when (select ((strftime('%m', new.publish))) = '02')
then raise(rollback, 'publish undefined day=30 !')
end
when (select ((strftime('%d', new.publish))='29')) then
case when ((select ((strftime('%Y', new.publish)))%4) != 0)
then raise(rollback, 'publish format 02-29 unleap day !')
when ((select ((strftime('%Y', new.publish)))%100) = 0 and (select ((strftime('%Y', new.publish)))%400) != 0)
then raise(rollback, 'publish format 02-29 unleap special day !')
end
end
end;
end;
CREATE TRIGGER tri_orders_del after update of iud_mark on orders when new.iud_mark='d'
begin
update details
set iud_mark =
case when old.publish != date('now', 'localtime')
then raise(rollback, 'can delete only today!')
else
'd+'
end
where orders_id=old.id;
end;
CREATE TRIGGER orders_before_insert before insert on orders
begin
select
case
when
(select id from branch where id=new.branch_id and del_flag is false) is null
then
raise(rollback, 'branch reference mistake or already deleted')
when
(select id from buyer where id=new.buyer_id and del_flag is false) is null
then
raise(rollback, 'buyer reference mistake or aleady deleted')
end;
end;
CREATE TABLE details (
orders_id integer not null,
goods_id integer not null,
quantity integer not null,
c_mark integer check(
c_mark is null or
c_mark=false or
c_mark=true
),
iud_mark text check(
iud_mark is null or
iud_mark='i' or
iud_mark='u' or
iud_mark='d' or
iud_mark='d+'
), subtotal integer,
foreign key(orders_id) references orders(id),
foreign key(goods_id) references goods(id));
CREATE TRIGGER tri_amount after insert on details when new.c_mark is not true and new.iud_mark is null
begin
update details
set subtotal =
(select quantity*goods.price from details inner join goods on goods_id=goods.code where details.rowid=new.rowid)
where rowid=new.rowid;
end;
CREATE TRIGGER tri_amount_chk after insert on details
when new.c_mark is true and new.iud_mark is not 'i'
begin
update details
set subtotal =
(select quantity*goods.price from details inner join goods on goods_id=goods.code where details.rowid=new.rowid)
where details.rowid=new.rowid;
update orders
set iud_mark =
case when
(select sum(subtotal) from details inner join orders on orders.id=details.orders_id where orders.id=new.orders_id and details.iud_mark is not 'd' and details.iud_mark is not 'd+')
=
(select amount from orders where id=new.orders_id)
and
id=new.orders_id
then 'i'
else
null
end
, amount =
(select sum(details.subtotal) from details inner join orders on orders.id=details.orders_id where id=new.orders_id and details.iud_mark is not 'd' and details.iud_mark is not 'd+')
where id=new.orders_id;
end;
CREATE TRIGGER tri_details_append after insert on details when new.iud_mark is 'i'
begin
update details
set subtotal =
case when
(select publish from orders where id=new.orders_id) != date('now', 'localtime')
then raise(rollback, 'can delete only today!')
else
(select new.quantity*goods.price from details inner join goods on goods_id=goods.code where details.rowid=new.rowid)
end
, c_mark = true
where details.rowid=new.rowid;
update orders
set iud_mark = 'u'
, amount = (select sum(details.subtotal) from details inner join orders on orders.id=details.orders_id and id=new.orders_id and details.iud_mark is not 'd' and details.iud_mark is not 'd+')
where id=new.orders_id;
end;
CREATE TRIGGER tri_quantity_chg after update of quantity on details when new.iud_mark is 'u'
begin
update details
set subtotal =
case when (select publish from orders where id=old.orders_id) != date('now', 'localtime')
then raise(rollback, 'can delete only today!')
else
(select quantity*goods.price from details inner join goods on goods_id=goods.code where details.rowid=new.rowid)
end
where rowid=new.rowid;
update orders
set iud_mark = 'u'
, amount =
(select sum(details.subtotal) from details inner join orders on orders.id=details.orders_id where id=new.orders_id and details.iud_mark is not 'd' and details.iud_mark is not 'd+')
where id=new.orders_id;
end;
CREATE TRIGGER tri_detail_del after update of iud_mark on details when new.iud_mark is 'd'
begin
update orders
set iud_mark =
case when
(select publish from orders where id=old.orders_id) != date('now', 'localtime')
then
raise(rollback, 'can delete only today!')
else
'u'
end
, amount = (select sum(details.subtotal) from details inner join orders on orders.id=details.orders_id where id=new.orders_id and details.iud_mark is not 'd' and details.iud_mark is not 'd+')
where id=new.orders_id;
end;
CREATE TRIGGER details_before_insert before insert on details
begin
select
case
when
(select code from goods where code=new.goods_id and del_flag is false) is null
then
raise(rollback, 'goods reference mistake or already deleted')
when
(select id from orders where id=new.orders_id and iud_mark is not 'd') is null
then
raise(rollback, 'orders reference mistake or aleady deleted')
end;
end;
CREATE TRIGGER details_before_update before update on details
begin
select
case
when
(select code from goods where code=new.goods_id and del_flag is false) is null
then
raise(rollback, 'goods reference mistake or already deleted')
when
(select id from orders where id=new.orders_id and iud_mark is not 'd') is null
then
raise(rollback, 'orders reference mistake or aleady deleted')
end;
end;
CREATE TABLE chglog_details (
id integer primary key not null,
orders_id integer,
goods_id integer,
old_quantity integer,
new_quantity integer,
chg_date text
);
CREATE VIEW view_orders
as
select orders.id, orders.publish, orders.amount, buyer.name as buyer_name, branch.name as branch_name
from orders
inner join buyer, branch on
orders.buyer_id = buyer.id
and orders.branch_id = branch.id
and buyer.del_flag=0
and branch.del_flag=0
/* view_orders(id,publish,amount,buyer_name,branch_name) */;
sqlite> .quitテキストファイルを修正する
ここでは、トリガー処理を優先にしたいので、外部キー制約の箇所を削除します。テキストファイルは、データベースの格納エンコード形式（ここではutf-8 改行コードLF）にあわせます。
CREATE TABLE orders (id integer priamry key not null,
buyer_id integer not null,
publish text,
branch_id integer not null,
amount integer default 0, iud_mark text
check(iud_mark is null or
iud_mark='i' or
iud_mark='u' or
iud_mark='d'
),
foreign key(buyer_id) references buyer(id)
foreign key(branch_id) references branch(id) -- 削除する
);
CREATE TABLE details (
orders_id integer not null,
goods_id integer not null,
quantity integer not null,
c_mark integer check(
c_mark is null or
c_mark=false or
c_mark=true
),
iud_mark text check(
iud_mark is null or
iud_mark='i' or
iud_mark='u' or
iud_mark='d' or
iud_mark='d+'
), subtotal integer,
foreign key(orders_id) references orders(id),
foreign key(goods_id) references goods(id)); -- 削除する新しいデータベースにテーブルを再構築する
takai@takai-pc ~/db/sales_manage
$ cd sales_manage_pro
takai@takai-pc ~/db/sales_manage/sales_manage_pro
$ sqlite3 sales_manage_pro.sq3
SQLite version 3.34.0 2020-12-01 16:14:00
Enter ".help" for usage hints.
sqlite> .tables
sqlite> .read ../fixed_sql.txt
sqlite> .tables
branch buyer chglog_price good
sqlite> .read ../nonfixed_sql.txt
sqlite> .tables
branch chglog_details details orders
buyer chglog_price goods view_ordersデータを挿入する
主に参照するテーブルにレコードを挿入する
buyerテーブルへの挿入
sqlite> insert into buyer (name) values
...> ('takai'),
...> ('yamada'),
...> ('kato'),
...> ('nisioka'),
...> ('akiyama'),
...> ('kimura'),
...> ('hayasi');
sqlite> .mode column
sqlite> select * from buyer;
id name del_flag
-- ------- --------
1 takai 0
2 yamada 0
3 kato 0
4 nisioka 0
5 akiyama 0
6 kimura 0
7 hayasi 0branchテーブルにデータを入力する
sqlite> insert into branch (name) values
...> ('山の中'),
...> ('都会'),
...> ('田園'),
...> ('海辺');
sqlite> select * from branch;
id name del_flag
-- ---- --------
1 山の中 0
2 都会 0
3 田園 0
4 海辺 0goodsテーブルにデータをいれる。
あらかじめ表計算ソフトでgoodsテーブルフィールドにあわせた商品リストを作成しておきます。文字コードをROMなしUTF-8、改行LFでcsvファイル（goods_list.csv）で保存しておきます。
---- goods_list.csvの中身を表示する
sqlite> .shell cat goods_list.csv
101,単三乾電池,200,0
102,ボールペン,110,0
103,ノート,150,0
201,アンパン,130,0
202,食パン,200,0
203,カレーパン,170,0
301,おにぎり,160,0
302,幕の内弁当,500,0
303,から揚げ弁当,450,0
404,牛乳,90,0
405,スポーツ飲料,140,0
406,フルーツジュース,190,0
501,日傘,800,0
502,雑誌,1050,0
601,チケット,4000,0
---- csvファイルをgoodsテーブルにインポートする
sqlite> .import --csv ./goods_list.csv goods
sqlite> select * from goods;
code name price del_flag
---- -------- ----- --------
101 単三乾電池 200 0
102 ボールペン 110 0
103 ノート 150 0
201 アンパン 130 0
202 食パン 200 0
203 カレーパン 170 0
301 おにぎり 160 0
302 幕の内弁当 500 0
303 から揚げ弁当 450 0
404 牛乳 90 0
405 スポーツ飲料 140 0
406 フルーツジュース 190 0
501 日傘 800 0
502 雑誌 1050 0
601 チケット 4000 0書き込みを行うテーブルにレコードを挿入する
ordersテーブルにレコードが挿入された後、detailsテーブルにレコードが挿入される手順になることを想定した流れとなります。インポートするデータは、csvファイルとして、カレントディレクトリに格納されているものとします。
インポートするcsvファイル
sqlite> .shell cat orders_date.csv
id,buyer_id,publish,branch_id,amount
24010201,1,2024-01-02,1,2560
24010202,2,2024-01-02,1,3370
24010203,3,2024-01-02,1,8380
24010304,4,2024-01-03,1,4770
24010305,5,2024-01-03,2,12260
24010306,6,2024-01-03,2,6690
24010407,7,2024-01-04,3,4600
24010408,1,2024-02-05,3,1050
24020509,1,2024-02-05,4,12000
24020510,3,2024-02-05,2,14680
24020511,1,2024-02-05,3,1460
sqlite> .shell cat details_date.csv
orders_id,goods_id,quantity,c_mark
24010201,101,4,0
24010201,202,3,0
24010201,103,5,0
24010201,404,1,0
24010201,301,2,1
24010202,102,1,0
24010202,201,5,0
24010202,201,1,0
24010202,303,1,0
24010202,405,7,0
24010202,502,1,1
24010203,601,2,0
24010203,406,2,1
24010304,102,1,0
24010304,202,2,0
24010304,302,1,0
24010304,303,1,0
24010304,405,2,0
24010304,406,2,0
24010304,501,2,0
24010304,502,1,1
24010305,102,1,0
24010305,103,1,0
24010305,601,3,1
24010306,101,10,0
24010306,201,3,0
24010306,301,7,0
24010306,405,5,0
24010306,406,2,0
24010306,502,2,1
24010407,103,3,0
24010407,302,3,0
24010407,303,2,0
24010407,404,5,0
24010407,502,1,1
24010408,201,1,0
24010408,202,1,0
24010408,203,2,0
24010408,406,2,1
24020509,601,3,1
24020510,601,3,0
24020510,203,3,0
24020510,301,5,0
24020510,302,1,0
24020510,303,1,0
24020510,404,1,0
24020510,405,1,0
24020510,406,1,1
24020511,102,2,0
24020511,103,2,0
24020511,405,1,0
24020511,501,1,0csvファイルをインポートする
sqlite> .import --csv --skip 1 ./data/orders_data.csv orders
./data/orders_data.csv:2: expected 6 columns but found 5 - filling the rest with NULL
./data/orders_data.csv:3: expected 6 columns but found 5 - filling the rest with NULL
.
.
qlite> select * from orders;
id buyer_id publish branch_id amount iud_mark
-------- -------- ---------- --------- ------ --------
24010201 1 2024-01-02 1 2560
24010202 2 2024-01-02 1 3370
24010203 3 2024-01-02 1 8380
24010304 4 2024-01-03 1 4770
24010305 5 2024-01-03 2 12260
24010306 6 2024-01-03 2 6690
24010407 7 2024-01-04 3 4600 -- amount値が間違っている
24010408 1 2024-02-05 3 1050
24020509 1 2024-02-05 4 12000
24020510 3 2024-02-05 2 14680
24020511 1 2024-02-05 3 146sqlite> .import --csv --skip 1 ./data/details_data.csv details
./data/details_data.csv:2: expected 6 columns but found 4 - filling the rest with NULL
./data/details_data.csv:3: expected 6 columns but found 4 - filling the rest with NULL
.
.
sqlite> select * from details;
orders_id goods_id quantity c_mark iud_mark subtotal
--------- -------- -------- ------ -------- --------
24010201 101 4 0 800
24010201 202 3 0 600
24010201 103 5 0 750
24010201 404 1 0 90
24010201 301 2 1 320
24010202 102 1 0 110
24010202 201 5 0 650
24010202 201 1 0 130
24010202 303 1 0 450
24010202 405 7 0 980
24010202 502 1 1 1050
24010203 601 2 0 8000
24010203 406 2 1 380
24010304 102 1 0 110
24010304 202 2 0 400
24010304 302 1 0 500
24010304 303 1 0 450
24010304 405 2 0 280
24010304 406 2 0 380
24010304 501 2 0 1600
24010304 502 1 1 1050
24010305 102 1 0 110
24010305 103 1 0 150
24010305 601 3 1 12000
24010306 101 10 0 2000
24010306 201 3 0 390
24010306 301 7 0 1120
24010306 405 5 0 700
24010306 406 2 0 380
24010306 502 2 1 2100
24010407 103 3 0 450
24010407 302 3 0 1500
24010407 303 2 0 900
24010407 404 5 0 450
24010407 502 1 1 1050
24010408 201 1 0 130
24010408 202 1 0 200
24010408 203 2 0 340
24010408 406 2 1 380
24020509 601 3 1 12000
24020510 601 3 0 12000
24020510 203 3 0 510
24020510 301 5 0 800
24020510 302 1 0 500
24020510 303 1 0 450
24020510 404 1 0 90
24020510 405 1 0 140
24020510 406 1 1 190
24020511 102 2 0 220
24020511 103 2 0 300
24020511 405 1 0 140
24020511 501 1 0 800 -- c_markが0にセットされ、最後のレコードではない
sqlite> select * from orders;
id buyer_id publish branch_id amount iud_mark
-------- -------- ---------- --------- ------ --------
24010201 1 2024-01-02 1 2560 i
24010202 2 2024-01-02 1 3370 i
24010203 3 2024-01-02 1 8380 i
24010304 4 2024-01-03 1 4770 i
24010305 5 2024-01-03 2 12260 i
24010306 6 2024-01-03 2 6690 i
24010407 7 2024-01-04 3 4350 -- 間違いが訂正されている
24010408 1 2024-02-05 3 1050 i
24020509 1 2024-02-05 4 12000 i
24020510 3 2024-02-05 2 14680 i
24020511 1 2024-02-05 3 1460 -- 最後のレコードが入力されていない
