[SQLite]SQLite Shellの設定を起動時に自動的に読み込む

Rails の開発を始めたら開発時のデータベースが SQLite だったので
SQLite のシェルを使っているのだが、
各種設定が起動時にリセットされる(というか保存されていない)ので設定を保存したい。

調べてみると ~/.sqliterc を用意すれば良いらしい。

参考
sqlite3 コマンドラインツールの設定保存 - wicaの日記

.headers ON
.mode column
.nullvalue "(null)"
.timer ON
.tables

これで起動時に各種設定が実行される。
それを逆手に取って起動時にそのデータベースファイルのテーブル一覧を表示するように最後の行を追加した。
これでテーブル名のコピペも楽ちん。本当は Tab 補完して欲しいのだけど。

[SQLearning][SQLite]テーブルの変更と削除

SQL ゼロからはじめるデータベース操作を教材にしてSQLiteを学ぶ
SQL学習連載「SQLearning」の第4回です。

前回: [SQLearning][SQLite]データ型と制約の指定 | DevAchieve
第3回は「テーブルの変更と削除」についてです。

カラムの追加

以下の CREATE 文で生成されるテーブルを元に解説していきます。

CREATE TABLE products (
    id          INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    name        TEXT    NOT NULL UNIQUE,
    description TEXT    NULL,
    price       INTEGER NOT NULL,
    discount    INTEGER NOT NULL DEFAULT 0,
    reg_date    TEXT    NOT NULL DEFAULT CURRENT_DATE
);

このテーブルにカラムを追加するには以下のように記述します。

ALTER TABLE <テーブル名> ADD <カラム名> <型> <制約>;

ALTER TABLE products
    ADD bar_code TEXT NOT NULL DEFAULT '000000000000000';

カラムは常に末尾に追加されます。
CREATE TABLE 構文とは異なり、以下のような制約があります。

• 追加されるカラムは PRIMARY KEY 制約, UNIQUE 制約を持つことができません。
• 追加されるカラムは デフォルト値に固定値以外の値(CURRENT_TIME, CURRENT_DATE, CURRENT_TIMESTAMP, 式)を持つことができません。
• 追加されるカラムに NOT NULL 制約が指定されている場合は NULL 以外のデフォルト値が設定されている必要があります。
• 外部キー制約が有効で、REFERENCES 句とともにカラムが追加された場合、追加されたカラムはデフォルト値に NULL を持たなくてはなりません。

※ CHECK 制約を追加する際は既存の列に対してテストされないことに注意してください。
これは CHECK 制約に違反するデータを含むテーブルになりうることを意味します。
SQLite の今後のバージョンでは追加される CHECK 制約を検証するように変更されるかもしれません。

カラムが追加されたデータベースは SQLite Ver.3.1.3 以前のバージョンで読めなくなります。

テーブル名の変更

テーブル名を変更するには以下のように記述します。

ALTER TABLE <テーブル名> RENAME TO <新しいテーブル名>;

テーブル名を変更した際に外部キー制約が有効ならば
REFERENCES 句に指定されていたテーブル名は新しいテーブル名に自動的にリネームされます。

SQLite Query Language: ALTER TABLE

テーブルの削除

テーブルを削除するには以下のように記述します。

DROP TABLE <テーブル名>;

テーブルに関連付けられたすべてのインデックスとトリガも削除されます。

DROP TABLE IF EXISTS <テーブル名>;

IF EXISTS を記述すれば存在しないテーブルの削除を試みることによるエラーを防ぐことができます。

次回

次回は「SELECT文の基礎」を予定しています。


SQL ゼロからはじめるデータベース操作 (CD-ROM付) (プログラミング学習シリーズ)

[SQLearning][SQLite]データ型と制約の指定

SQL ゼロからはじめるデータベース操作を教材にしてSQLiteを学ぶ
SQL学習連載「SQLearning」の第3回です。

前回: [SQLearning][SQLite]テーブルの作成・確認・削除 | DevAchieve
第3回は「データ型と制約の指定」についてです。

データ型の指定

以下の CREATE 文を例にデータ型と制約の指定について解説します。

CREATE TABLE IF NOT EXISTS products (
    id          INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    name        TEXT    NOT NULL UNIQUE,
    description TEXT    NULL,
    price       INTEGER NOT NULL CHECK(price > 0),
    tax_free    INTEGER NOT NULL DEFAULT 0 CHECK(tax_free IN (0, 1)),
    reg_date    TEXT    NOT NULL DEFAULT CURRENT_DATE
);

SQLite では動的型付けシステムを使用していて、データ型はカラムではなく値自体に関連付けられます。
ストレージクラスとして定義されていますが、これらはほぼデータ型と同じと考えることができます。

NULL

NULL値

INTEGER

符号付き整数。桁数によって1, 2, 3, 4, 6 バイトで保存される。

REAL

浮動小数点数。8バイトで保存される。

TEXT

文字列。データベースのエンコーディング(UTF-8, UTF-16BE もしくは UTF-16LE)で保存される。

BLOB

バイナリデータ(Binary Large OBject)。入力データがそのまま保存される。

Boolean

真偽値を格納する Boolean 型はありません。かわりに INTEGER 型の 0(false), 1(true) として保存します。

Date 型と Time 型

Date 型, Time 型, DateTime 型はありません。
かわりに組み込みのDate And Time Functionsが INTEGER, TEXT, REAL のいずれかとして保存します。
INTEGER: 1970-01-01 00:00:00 UTC からの秒数である Unix Time
TEXT: ISO8601 表記の文字列("YYYY-MM-DD HH:MM:SS.SSS")
REAL: 先発グレゴリオ暦にしたがった紀元前4714年11月24日、
グリニッジ標準時における正午からのユリウス日数(関連: ユリウス通日)
REAL はちょっと意味がわかりませんね。

また、SQLite では他の RDBMS との互換性のために Type Affinity (型の類似性)という概念が導入されています。

• TEXT: ストレージクラスの NULL, BLOB, TEXT のすべてのデータを含みます。
• NUMERIC: ストレージクラスの5つすべてを含みます。データは変換される可能性があります。
• INTEGER: NUMERIC とほぼ同じ振る舞いをします。違いは CAST 方法のみです。
• REAL: NUMERIC とほぼ同じ振る舞いをします。違いは浮動少数表現を強制する点です。
• NONE: 他のストレージクラスへの変換をしません。

Datatypes In SQLite Version 3

制約の指定

NULL と NOT NULL

NULL は NULL値を許容し、NOT NULL は NULL値 を入れることができなくなります。

DEFAULT

デフォルト値を設定することができます。
INSERT時の日時をデフォルト値にすることのできる以下のキーワードを設定することもできます。

CURRENT_TIME	HH:MM:SS 形式
CURRENT_DATE	YYYY-MM-DD 形式
CURRENT_TIMESTAMP	YYYY-MM-DD HH:MM:SS 形式

PRIMARY KEY

主キーを設定することができます。INTEGER と組み合わせることで AUTOINCREMENT を実現できます。
しかし、その場合はデータ削除に再び同じ値が入ります。

AUTOINCREMENT

明示的に指定するとデータ削除後は削除した値にはならず次に大きい値が入ります。

UNIQUE

指定すると unique(一意な)値のみを受け入れます。
INSERT または UPDATE 時に既存の値と重複した値を指定するとエラーになります。
NULL を許容する場合、それぞれの NULL値は異なる値として扱われます。

CHECK(条件式)

条件式を満たす値のみを許容します。条件式にサブクエリを含めることはできません。
INSERT または UPDATE 時に条件式を満たさない値を指定するとエラーになります。
ver. 3.3.0 からサポートされました。ver. 3.3.0 以前では指定しても無視されます。

SQLite Query Language: CREATE TABLE

次回

次回は「テーブルの変更」についてを予定しています。


SQL ゼロからはじめるデータベース操作 (CD-ROM付) (プログラミング学習シリーズ)

[SQLearning][SQLite]テーブルの作成・確認・削除

SQL ゼロからはじめるデータベース操作を教材にしてSQLiteを学ぶ
SQL学習連載「SQLearning」の第2回です。

前回: [SQLearning][SQLite]ログインとデータベースの作成・削除 | DevAchieve
第2回は「テーブルの作成・確認・削除」についてです。

テーブルの作成

CREATE TABLE products (
    id          INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    name        TEXT    NOT NULL,
    description TEXT    NULL,
    price       INTEGER NOT NULL,
    discount    INTEGER NOT NULL DEFAULT 0,
    reg_date    TEXT    NOT NULL
);

すでにテーブルが存在する場合はエラーになりますが IF NOT EXISTS と書くことでエラーを防ぐことができます。

CREATE TABLE IF NOT EXISTS products (
    id          INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    name        TEXT    NOT NULL,
    description TEXT    NULL,
    price       INTEGER NOT NULL,
    discount    INTEGER NOT NULL DEFAULT 0,
    reg_date    TEXT    NOT NULL
);

SQLite Query Language: CREATE TABLE

テーブルの確認

.tables

テーブルの一覧を表示します。

sqlite> .tables

.schema

CREATE TABLE 文を表示します。

sqlite> .schema products

テーブルの削除

DROP TABLE products;

すでにテーブルが存在する場合はエラーになりますが IF EXISTS と書くことでエラーを防ぐことができます。

DROP TABLE IF EXISTS products;

次回

次回は「データ型・制約の指定」についてを予定しています。


SQL ゼロからはじめるデータベース操作 (CD-ROM付) (プログラミング学習シリーズ)

[SQLearning][SQLite]ログインとデータベースの作成・削除

SQL ゼロからはじめるデータベース操作を教材にしてSQLiteを学ぶ
SQL学習連載「SQLearning」を始めます。

準備は以下の記事で済んでいると想定します。
SQL ゼロからはじめるデータベース操作で SQLite を学ぶ | DevAchieve

SQLite コンソールを開く

ターミナルで以下のコマンドを実行します。
sqlite3
SQLite にはユーザーの概念がないのでログインする必要がありません。常に管理者です。

データベースの新規作成

ファイル作成先を指定せずに作成
sqlite3 shop.db
/Users/[ユーザー名]/に作成されます。

ファイル作成先を指定して作成
sqlite3 /Users/wada/Docments/shop.db

ファイルが作成されるのは何かしらの操作をしたあとになるようです。

sqlite> .databases

上記のコマンドを実行すると接続しているデータベース名のリストが得られ、
データベースファイルが存在しない場合、ファイルが作成されます。

データベースの削除

SQLite では1データベース1ファイルなのでデータベースの削除はSQLからではなく、ファイルの削除で行います。
rm shop.db

SQLite コンソールからログアウト

sqlite> .exit

または

sqlite> .quit

次回

次回は「デーブルの作成・確認・削除」についてを予定しています。


SQL ゼロからはじめるデータベース操作 (CD-ROM付) (プログラミング学習シリーズ)

SQL ゼロからはじめるデータベース操作で SQLite を学ぶ

SQL ゼロからはじめるデータベース操作 (CD-ROM付) (プログラミング学習シリーズ)

●達人から学ぶ基礎とコツ

本書は、「データベースやSQLがはじめて」という初心者を対象に、
プロのDBエンジニアである著者がSQLの基礎とコツをやさしく丁寧に教える入門書です。

データベース／テーブルの構造から、データを検索したり更新したりする構文、
よく使う関数、テーブルの結合など、
SQLによるデータベース操作の基本を学習していきます。
WHERE句、GROUP BY句、HAVING句やサブクエリなど、
SQLを使いこなすためのポイントとなる機能や、
初心者にとって理解が難しい部分は、豊富な図とサンプルプログラムでかみくだいて解説しています。
また、標準SQLをベースに、各種データベース(Oracle, SQL Server, DB2, PostgreSQL, MySQL)での違いや、
初心者がハマりやすいポイント、基礎ノウハウについてもしっかり解説。

付属CD-ROMには、SQL学習環境としてPostgreSQL、本書のサンプルプログラムを収録。
本書の内容を試しながら学習することで学習効果が高まります。

・何から学習すれば良いかわからない
・きちんとSQLを書けるようになりたい
・現場で通用する基礎を身につけたい

という方におすすめの一冊です。

＿人人人人人人人人人人人＿
＞　SQLiteが入ってない　＜
￣Y^Y^Y^Y^Y^Y^Y^Y￣

iOS や Android では SQLite なので SQLite でも書けるようになっておきたいところです。
すごくわかりやすく、初心者を脱落させない配慮が感じられる本なのでコレをベースに学習して行きます。
勝手に SQLite 対応しちゃいます。

SQlite のインストール

Windows

SQLite Download Pageから sqlite-shell-win32-x86-<varsion>.zip をダウンロードして
インストールしたら使えます。たぶん。

Mac

たぶんもう入ってます。ターミナルで sqlite3 と入力すれば sqlite が使えるはず。
ダメだったら Windows と同じページから Mac 用の zip をダウンロードしてインストールすればいいと思います。

SQLite の起動

sqlite3 とターミナル上で打てば起動します。

他のデータベースは MySQL しか使ったことないからわからないけど SQLite はユーザーという概念がありません。
一つのデータベースは一つのファイルに収められ、ファイルさえあればパスワードなしで開くことができます。

ヘルプを開く

起動直後に表示されているように.helpでヘルプを表示することができます。

SQLite の終了

.exitまたは.quitで終了します。

これで準備は完了したので次回からはデータベースを作成したりテーブルを作成したいと思います。

Lita - GUI の SQLite クライアントアプリケーション

Lita - SQLite Administration Tool

Lita is a free and open source SQLite database administration tool for Windows, MacOSX and Linux.

• Open, create, compact, manage SQLite databases
• Create, rename, delete, and empty tables
• Create, rename and delete columns
• Create, modify and delete records
• Encrypt or reencrypt your databases
• Run, import and export your custom SQL statements
• Create and delete indices

SQLiteでできるほとんどのことができると思われます。
Android や iOS ではデータベースは SQLite を利用しているので SQL のテストをしたい際に便利です。

Android なら端末の以下のパスに .db ファイルがあるらしいです。ROOT権限があれば覗けるとか。

/data/data/[パッケージ名]/database/

iOS なら Mac の以下のパスに .sqlite ファイルがあります。

/Users/[ユーザー名]/Library/Application Support/iPhone Simulator/[ビルド時のiOSのバージョン]/Applications/[アプリのGUID]/Documents/

/Users/[ユーザー名]/Library が隠しファイル設定になっているので TinkerToolで表示させてやりましょう。

SQL ゼロからはじめるデータベース操作が Oracle, SQL Server, DB2, PostgreSQL, MySQL に対応していたので
勉強がてらに SQLite を対応させようと思います。
スマホアプリは SQLiteなのでマスターしておきたいですね。