やってみる

アウトプットすべく己を導くためのブログ。その試行錯誤すらたれ流す。

📌
トップ > SQLite3 > SQLite3構文　attach/detach

SQLite3構文　attach/detach

SQLite3

　DB接続。

成果物

情報源

attach

attach database 接続文字列 as スキーマ名
detach database スキーマ名

　databaseキーワードを省略することもできる。

attach 接続文字列 as スキーマ名
detach スキーマ名

接続文字列

  • シングルクォート''で囲む
  • ':memory:'でインメモリを指定可
  • 'ファイルパス'で指定DBファイルに接続
  • 'file:...'のようにURIファイル名を指定可

要素の操作

操作/要素 スキーマ テーブル
一覧 .databases .tables, .schema
select * from スキーマ名.sqlite_master;
指定 attach '接続文字列' as 'スキーマ名;'
detach スキーマ名;		 select * from スキーマ名.テーブル名;

特殊スキーマ名

特殊スキーマ名 概要
main sqlite3コマンドなどでオープンしたDB
temp create temp table ...などで作成された一時DB
  • main,tempのスキーマ名は変更できない
  • main,tempはattach as スキーマ名のスキーマ名に使えない
  • main,tempはdetachできない

確認

　DBオープンしたらそのDBはmainスキーマ名である。

.database

:main

　一時テーブルを作るとそれはtempスキーマ名配下に作成される。

create temp table T(A int);
.database

:main
:temp

　指定DBを接続する。指定スキーマ名で。

attach ':memory:' as mem0;
.database

:main
:mem0

　main,temp,attachで各スキーマができる。

attach ':memory:' as mem0;
create temp table T(A int);
.database

:main
:temp
:mem0

　main,tempスキーマは削除できない。

attach ':memory:' as mem0;
create temp table T(A int);
detach main;
detach temp;
detach mem0;
.databases

main:
temp:

　以下のように怒られる。

cannot detach database main
cannot detach database temp

mainスキーマ名

create table T(A int);
select sql from      sqlite_master;
select sql from main.sqlite_master;

insert into      T values(0);
insert into main.T values(1);
select * from T;

tempスキーマ名

create temp table T(A int);
select sql from temp.sqlite_master;
select sql from temp.sqlite_temp_master;

insert into temp.T values(0);
select * from temp.T;
  • 一時テーブル作成: create temp tableかcreate temporary table文で
    • テーブル名の前にスキーマ名を指定できない
      • temporary table name must be unqualifiedエラー
create temp table main.T(A int);

attach ':memory:' as mem;
create temp table mem.T(A int);

temporary table name must be unqualified

任意スキーマ名（attach）

attach ':memory:' as mem0;

create table mem0.T(A int);
select sql from mem0.sqlite_master;

insert into mem0.T values(0);
select * from mem0.T;

create table a.T(A int);

Error: unknown database a

テーブル結合

　異なるスキーマ間におけるテーブル結合。

attach ':memory:' as mem;

create      table     T(A int);
create temp table     T(A int);
create      table mem.T(A int);
insert into main.T values(0);
insert into temp.T values(0);
insert into  mem.T values(0);

.echo on
select * from main.T left join temp.T on main.T.A = temp.T.A;
select * from main.T left join  mem.T on main.T.A =  mem.T.A;
select * from temp.T left join  mem.T on temp.T.A =  mem.T.A;
.echo off

スキーマ名の省略

　スキーマ間でテーブル名が一意なら、スキーマ名を省略できる。

attach ':memory:' as mem;
create      table     T(A int);
create temp table     U(A int);
create      table mem.V(A int);
insert into T values(0);
insert into U values(0);
insert into V values(0);

.echo on
select * from T left join U on T.A = U.A;
select * from T left join V on T.A =  V.A;
select * from U left join V on U.A =  V.A;
.echo off

メモリ→ファイル

  1. データ編集は主にメモリ側で行う
  2. 一区切りついたらファイルへ保存する

　これによりディスクI/Oが減って応答速度の向上や寿命が伸びる。

attach ':memory:' as m;
attach './a.db' as f;

create table m.users(id integer primary key, name text not null);
create table f.users(id integer primary key, name text not null);

insert into m.users(name) values('Yamada');

begin transaction;
insert into f.users(name) select name from m.users;
delete from m.users;
commit;

insert into m.users(name) values('Suzuki');
insert into m.users(name) values('Tanaka');

begin transaction;
insert into f.users(name) select name from m.users;
delete from m.users;
commit;

.echo on
select * from f.users;
select * from m.users;
.echo off

　さらなる要件として、追加・更新・削除したレコードを把握したい。修正内容やその経緯も。

　これらを実装するならリビジョン管理用テーブルなどを作って差分を残しておく必要がある。削除も変更ログとして残すことになる。それにより復元できるようになる。完全にディスクから削除するには別の操作をさせるようにする。

　コードにするまで大変そうなので今回は見送る。

参考

対象環境

$ uname -a
Linux raspberrypi 4.19.42-v7+ #1218 SMP Tue May 14 00:48:17 BST 2019 armv7l GNU/Linux

前回まで