SQLite3でテーブルを作成：CREATE TABLE

テーブルを作成する

今回は、SQLiteへテーブルを作成する方法をみていきます。
MySQLなどのリレーショナルデータベースに慣れている方にとっては少し取っ付きづらい、「型」や自動連番のID(AUTO INCREMENT)の設定方法についても解説。

• テーブルで使用できる「型」
• 主キーとして使うことの多い「ID」と「AUTO INCREMENT」（自動連番）の設定
• 登録日や更新日を記録する「日付」のローカライズを含む設定
• テーブルが存在するか確認し、存在しない場合のみ作成

今回は次のようなテーブルを作成しながら進めていきます。

データベースへの接続方法については、「SQLite3を使ってをデータベースへ接続」をご覧ください。

扱うことのできる「型」

まずは、SQLite3で扱うことのできる型を確認しましょう。
基本的には次の5種類です。

SQLiteでは論理値の型はなく、INTEGERで「0」か「1」のような形で保存します。
日付についても「DATE」や「DATEITME」、「TIMESTAMP」のような型がないため、TEXTやNUMERICの型として保存します。

IDの連番(AUTO INCREMENT)設定

SQLite3では、「CREATE TABLE」の際に「AUTO INCREMENT」を指定する必要はありません。
もし主キーを連番のIDにしたい場合、INTEGERで「PRIMARY KEY」を指定するようにします。

// テーブル作成SQLの例
$sql = "CREATE TABLE test(
	id INTEGER PRIMARY KEY,
	name TEXT NOT NULL,
	age INTEGER NOT NULL,
	created_datetime TIMESTAMP DEFAULT (datetime(CURRENT_TIMESTAMP,'localtime'))
)";

こちらのSQLでテーブルを作成すると、データが追加される度に「id」が「1、2、3…」と自動連番で振られていきます。

日付のローカライズを含めた設定

データを登録や更新する際に、ほとんどの場合その時点の日時も保存します。
次のSQLにある「created_datetime」のように設定を宣言すると、その時点での日時を自動的に取得して保存してくれるようになります。

// テーブル作成SQLの例
$sql = "CREATE TABLE test(
	id INTEGER PRIMARY KEY,
	name TEXT NOT NULL,
	age INTEGER NOT NULL,
	created_datetime TIMESTAMP DEFAULT (datetime(CURRENT_TIMESTAMP,'localtime'))
)";

このSQLで登録したテーブルにデータを追加すると、「created_datetime」には「yyyy-mm-dd hh:mm:ss」の形式で「2017-07-26 12:10:30」のように日時を自動取得し登録します。
これは「DEFAULT」を使って、初期値をdatetime関数から取得した時刻を入れるように指定しているためです。

datetime関数は第一引数には時刻、第二引数にはオプションを指定することができます。
第一引数には現在時刻が入るように「CURRENT_TIMESTAMP」を指定。

第二引数に「localtime」を指定しなかった場合はUTCの標準時刻が入ります。
日本時間にしたい場合は必ず指定してください。

テーブルを作成する

これまでの内容を踏まえ、実際にテーブルを作成していきましょう。
テーブル作成のSQLは先述したものをそのまま使います。

コード

<?php
// Initialize
$db = null;
$sql = null;
$res = null;

$db = new SQLite3("./sqlite/test.sqlite3");

// テーブルを追加
$sql = "CREATE TABLE test(
	id INTEGER PRIMARY KEY,
	name TEXT NOT NULL,
	age INTEGER NOT NULL,
	created_datetime TIMESTAMP DEFAULT (datetime(CURRENT_TIMESTAMP,'localtime'))
)";
$res = $db->exec($sql);

上記のコードを実行すると、データベース「test.sqlite3」の中に「test」というテーブルが新しく作成されます。

テーブルが存在しない場合のみ作成

最後に、テーブルが存在しない場合のみテーブルを作成するようにします。
特定のテーブルが既に登録されているかを確認したい場合は、次のSQLを実行します。

SELECT count(*) FROM sqlite_master WHERE type="table" AND name="test"'

「sqlite_master」へ、「test」というテーブルが存在するかを問い合わせる内容です。

既に存在した場合は整数の「1」、存在しない場合は「0」が返ってきます。
そこで、次のようにif文を追加することで「存在しない場合のみテーブル作成をする」という処理にしていきましょう。
赤い箇所がコードを追加した部分です。

コード

<?php
// Initialize
$db = null;
$sql = null;
$res = null;

$db = new SQLite3("./sqlite/test.sqlite3");

// テーブルの存在確認
$sql = 'SELECT count(*) FROM sqlite_master WHERE type="table" AND name="test"';

// テーブルが存在しない場合は作成する
if( !$db->querySingle($sql) ) {

	// テーブルを追加
	$sql = "CREATE TABLE test(
		id INTEGER PRIMARY KEY,
		name TEXT NOT NULL,
		age INTEGER NOT NULL,
		created_datetime TIMESTAMP DEFAULT (datetime(CURRENT_TIMESTAMP,'localtime'))
	)";
	$res = $db->exec($sql);
}

以上、SQLite3でのテーブル作成方法でした。