UbuntuにSQLite3のインストール

1. SQLiteについて

1-1. 文書管理やデータセット作成

• SQLiteの基本コマンド

• SQLite公式サイトからの引用

• 以下の内容は、SQLiteのコマンドラインシェルを抜粋・記載したものです。正しい詳細はこちらを確認ください。

• SQLite は、小型で高速、自己完結型、高信頼性、フル機能の SQL データベース エンジンを実装する C 言語ライブラリです。SQLiteは世界で最も使用されているデータベースエンジンです。SQLiteはすべての携帯電話とほとんどのコンピュータに組み込まれており、人々が毎日使用する無数の他のアプリケーションにバンドルされています。

• SQLiteファイル形式は安定しており、クロスプラットフォームであり、下位互換性があり、開発者は2050年までその状態を維持することを約束します。SQLite データベース ファイルは、システム間でリッチ コンテンツを転送するためのコンテナーとして、およびデータの長期的なアーカイブ形式として一般的に使用されます。1兆(1e12)以上のSQLiteデータベースが活発に使用されています。

1-2. 特徴

1. 他のデータベースの様にデータベース自身をインストールする必要はありません。
2. 数万～数千万件のデータへ対応可能です。
3. 同一スペックのPCで利用する場合、OracleやPostgreSQLなどの業務用データベースより高速に動作します。
4. 本格的なSQLにも対応しているので、他のデータベースと同様に複数のテーブルを結合して複雑な集計を行わせることも可能です。
5. 同時に１人しかアクセスできないので、セキュリティは簡単に接続できます。
6. SQLite は１つのファイルをコピーするだけでバックアップや複製ができます。

1-3. SQLiteの制限

1. 複数ユーザー又は複数アプリからの同時アクセス
2. ストアドプロシージャの作成
3. 列名の変更(RENAME COLUMN)
4. 日付／時間の計算：日付／時刻のデータ型が存在しません。但し、’2021-06-01 10:05:03′ というフォーマットの文字列を日付／時刻として扱い、差分を計算する関数は用意されています。

1-4. SQLiteで扱えるデータ型

• NULL NULL型
• REAL 浮動小数点
• INTEGER 整数
• TEXT 任意の文字列
• BLOB 任意のバイナリデータ

1-5. カラムの型

• REA
• INTEGER
• TEXT
• BLOB

1-6. 使用上の注意点

• カラムの型を指定しないと何でも入ってしまいます。
• 型を指定しないと、集計関数が誤動作します。
• トランザクションが無いとInsertが極めて遅いです。

2. Ubuntuの確認

2-1. OSの確認

• cat /etc/os-release

  cat /etc/os-release
    PRETTY_NAME="Ubuntu 22.04 LTS"
    VERSION_CODENAME=jammy
  

2-2. SQLite3のパッケージ確認

• apt-cacheコマンドでパッケージ名sqlite3の確認

  apt-cache search sqlite3 | grep ^sqlite3
  
    sqlite3 - Command line interface for SQLite 3
    sqlite3-doc - SQLite 3 documentation
    sqlite3-pcre - Perl-compatible regular expression support for SQLite
    sqlite3-tools - Command line interface for SQLite 3 (tools)
  

2-3. sqlite3のインストール

• apt-get install

  sudo apt-get install -y sqlite3
    sqlite3 is already the newest version (3.37.3-2).
  確認
  which sqlite3
    /usr/bin/sqlite3
  

3. 動作確認

• データベース接続(新規作成)、テーブル作成、データ挿入、データ閲覧およびSQLiteシェルの終了までの操作です。

1. データベース接続(新規作成)

• 通常の起動

  sqlite3 testdb.sqlite3
  SQLite version 3.37.2 2022-01-06 13:25:41
  Enter ".help" for usage hints.
  

• in-memoryで起動

  sqlite3
    SQLite version 3.37.2 2022-01-06 13:25:41
    Enter ".help" for usage hints.
    Connected to a transient in-memory database.
    Use ".open FILENAME" to reopen on a persistent database.
  sqlite> 
  

2. create,insert,select

   sqlite> create table tbl1(one text, two int);
   sqlite> insert into tbl1 values('hello!',10);
   sqlite> insert into tbl1 values('goodbye', 20);
   sqlite> select * from tbl1;
   hello!|10
   goodbye|20
   

3. sqliteシェルの終了

• .exit

  sqlite> .exit
  

• sqlite3プログラムを終了するには、通常は Control-Dで終わります。

4. 作成されたデータベースファイルの確認

• DBファイルのあるディレクトリが対象

  $ ls -l *sqlite3
  -rw-r--r-- 1 xxx xxx 8192 Sep  4 10:17 testdb.sqlite3
  ___no file
  ls: cannot access '*sqlite3': No such file or directory
  

4. エクスポート

4-1. 出力フォーマット

• quote, csv, json, list, markdown, table, columnなど１４種類あります。
• “.mode” dotコマンドを使用して、これらの出力形式を切り替えることができます。デフォルトの出力モードは “list” です。
• 現在の出力モードの確認

  sqlite> .mode
  current output mode: list
  

• “.separator” ドットコマンドを使用して、区切り文字を変更します。たとえば、区切り記号をコンマとスペースに変更するには、次のようにします。

  sqlite>.mode column
  sqlite> select * from tbl1;
  one      two
  -------  ---
  hello!   10 
  goodbye  20 
  

• markdown
• 幅 0 は、列の幅が自動的に選択されることを意味します。指定されていない列幅はゼロになります。したがって、引数を指定せずにコマンド “.width” を実行すると、すべての列幅がゼロにリセットされるため、すべての列幅が自動的に決定されます。

  sqlite>.width
  sqlite>.mode markdown
  sqlite> select * from tbl1;
  |   one   | two |
  |---------|-----|
  | hello!  | 10  |
  | goodbye | 20  |
  

• 便利な出力モードは「挿入」です。挿入モードでは、出力は SQL INSERT ステートメントのように見えるようにフォーマット設定されます。挿入モードを使用して、後で別のデータベースにデータを入力するために使用できるテキストを生成します。

  sqlite>.mode insert new_table
  sqlite> select * from tbl1 where two<50;
  INSERT INTO "new_table" VALUES('hello',10);
  INSERT INTO "new_table" VALUES('goodbye',20);
  

• json

  sqlite> .mode json
  sqlite> select * from tbl1 where two<50;
  [{"one":"hello!","two":10},
  {"one":"goodbye","two":20}]
  

5. データベーススキーマのクエリ

• データベース内のテーブルのリスト

  sqlite>.tables
  tbl1 tbl2
  

• スキーマ

  sqlite> .schema
  create table tbl1(one varchar(10), two smallint)
  CREATE TABLE tbl2 (
    f1 varchar(30) primary key,
    f2 text,
    f3 real
  );
  sqlite>.schema tbl2
  CREATE TABLE tbl2 (
    f1 varchar(30) primary key,
    f2 text,
    f3 real
  );
  

• 接続されているDBのリスト

  sqlite> .databases
  main: /home/shiny/workspace/xxx/xxx/test r/w
  

6. データベースファイルを開く

“.open” コマンドは、以前に開いたデータベースコマンドを最初に閉じた後、新しいデータベース接続を開きます。最も単純な形式では、 “.open"コマンドは引数として指定されたファイルに対してsqlite3_open()を呼び出すだけです。":memory:” という名前を使用して、CLI が終了するか、または “.open” コマンドが再度実行されると消える新しいメモリ内データベースを開きます。

• –new オプションが “.open” に含まれている場合、データベースは開かれる前にリセットされます。以前のデータはすべて破棄されます。これは以前のデータの破壊的な上書きであり、確認は要求されないため、このオプションは慎重に使用してください。

• –readonly オプションは、データベースを読み取り専用モードで開きます。書き込みは禁止されます。

• –deserialize オプションを指定すると、ディスク上のファイルの内容全体がメモリに読み込まれ、sqlite3_deserialize() インタフェースを使用してメモリ内データベースとして開かれます。もちろん、大規模なデータベースがある場合は、大量のメモリが必要になります。また、データベースに加えた変更は、".save" または “.backup” コマンドを使用して明示的に保存しない限り、ディスクに保存されません。

• –append オプションを指定すると、SQLite データベースはスタンドアロン ファイルとして動作するのではなく、既存のファイルに追加されます。詳細については、appendvfs 拡張機能を参照してください

7. I/O のリダイレクト

7-1. ファイルへの結果の書き込み

• デフォルトでは、sqlite3 はクエリ結果を標準出力に送信します。これは、".output" コマンドと “.once” コマンドを使用して変更できます。出力ファイルの名前を引数として.outputに入れるだけで、後続のすべてのクエリ結果がそのファイルに書き込まれます。または、.output の代わりに .once コマンドを使用すると、出力はコンソールに戻る前に 1 つの次のコマンドに対してのみリダイレクトされます。引数を指定せずに .output を使用して、標準出力への書き込みを再び開始します。

  sqlite>.mode list
  sqlite>.separator |
  sqlite>.output test_file_1.txt
  sqlite> select * from tbl1;
  sqlite>.exit
  

• “.output” または “.once” コマンドに “-x” 引数がある場合、出力はコンマ区切り値 (CSV) として一時ファイルに蓄積され、その結果に対して CSV ファイル (通常はスプレッドシート プログラム) を表示するための既定のシステム ユーティリティを呼び出します。これは、クエリの結果をスプレッドシートに送信して簡単に表示できるようにする簡単な方法です。

  sqlite>.once -x
  sqlite> SELECT * FROM tbl1;
  

• “.excel” コマンドは “.once -x” のエイリアスです。それはまったく同じことをします。

• csvへ出力

  sqlite> .mode csv
  sqlite> .output test_file.csv
  sqlite> SELECT * FROM tbl1;
  
  cat test_file.csv
  hello!,10
  goodbye,20
  

• 確認

  sqlite> .mode column
  sqlite> SELECT * FROM tbl1;
  one      two
  -------  ---
  hello!   10 
  goodbye  20 
  

7-2. ファイルからの SQL の読み込み

• 対話モードでは、sqlite3 はキーボードから入力テキスト (SQL ステートメントまたはドット・コマンド) を読み取ります。もちろん、sqlite3を起動するときにファイルから入力をリダイレクトすることもできますが、プログラムと対話することはできません。ファイルに含まれる SQL スクリプトを実行して、コマンド行から他のコマンドを入力すると便利な場合があります。このために、".read" ドットコマンドが提供されています。

• “.read” コマンドは、入力テキストを読み取るファイルの名前である (通常は) 単一の引数を取ります。

  sqlite>.read myscript.sql
  

7-3. ファイルをCSVまたは他のフォーマットとしてインポート

• “.import” コマンドを使用して、CSV (コンマ区切り値) または同様に区切られたデータを SQLite テーブルにインポートします。".import" コマンドは、データの読み取り元のソースと、データの挿入先となる SQLite テーブルの名前である 2 つの引数を取ります。source 引数は、読み取られるファイルの名前であるか、または “|” 文字で始まる場合は、入力データを生成するために実行されるコマンドを指定します。

• “.import” コマンドを実行する前に “mode” を設定することが重要な場合があることに注意してください。これは、コマンド行シェルが入力ファイル・テキストをファイルの構造以外の形式として解釈しようとするのを防ぐために賢明です。–csv または –ascii オプションを使用すると、インポート入力区切り文字が制御されます。それ以外の場合、区切り文字は現在の出力モードで有効な区切り文字です。

• 「メイン」スキーマにないテーブルにインポートするには、–schema オプションを使用して、テーブルが他のスキーマにあることを指定できます。これは、ATTACH されたデータベースや TEMP テーブルにインポートする場合に便利です。

• .import を実行するとき、最初の入力行の処理は、ターゲット表が既に存在するかどうかによって異なります。存在しない場合は、テーブルが自動的に作成され、最初の入力行の内容を使用してテーブル内のすべての列の名前が設定されます。この場合、テーブルデータの内容は、2 行目以降の入力行から取得されます。ターゲット表が既に存在する場合、最初の表を含む入力のすべての行は、実際のデータ・コンテンツと見なされます。入力ファイルに列ラベルの初期行が含まれている場合は、"–skip 1" オプションを使用して、.import コマンドにその最初の行をスキップさせることができます。

• 最初の行に列名を持つCSVファイルから既存の一時テーブルをロードする使用例を次に示します。

  sqlite> .import --csv --skip 1 --schema temp C:/work/somedata.csv tab1
  

7-4. CSVへのエクスポート

• SQLiteテーブル(またはテーブルの一部)をCSVとしてエクスポートするには、単に “モード"を “csv"に設定し、クエリを実行してテーブルの目的の行を抽出します。

  sqlite> .headers on
  sqlite> .mode csv
  sqlite> .once ./csv_files/dataout.csv
  sqlite> SELECT * FROM tab1;
  確認
  cat ./csv_files/dataout.csv
  

8. データベース全体をテキストファイルに変換

• “.dump” コマンドを使用して、データベースの内容全体を 1 つの UTF-8 テキスト ファイルに変換します。このファイルは、sqlite3 にパイプで戻すことで、データベースに変換し直すことができます。
• バックアップは、SQLiteDBのファイルをコピーするだけでよいです。

  //Linuxのshellで実行します。
  sqlite3 test .dump | gzip -c >ex1.dump.gz
  ls
  -rw-rw-r-- 1 xxxx xxxx    194 xxx  4 16:05 ex1.dump.gz