Memo/PHP/PEAR/SQLite

http://dexlab.net/pukiwiki/index.php?Memo/PHP/PEAR/SQLite
 

Memo/PHP

組込み可能なSQLデータベースエンジン(SQLite)

TIPS

CSVのインポート

  • sqlite2 & 3
    • copyコマンド
      sqlite> copy テーブル名 from 'CSVファイル名' using delimiters ',';
  • sqlite3
    sqlite3 -separator , データベース.db ".import csvファイル名 テーブル名"

日付と時刻関数

  • まだドキュメントがまとまっていないようだが、CSVに関数の説明がある
  • julianday() 日付文字列 -> ユリウス通日変換
    CREATE TABLE schedule(date REAL NOT NULL, task TEXT NOT NULL); 
    INSERT INTO schedule( date, task ) VALUES( julianday( 'now' ), 'Meeting' ); 
    
    SELECT task FROM schedule WHERE date BETWEEN julianday( '2007-04-19' ) AND julianday( '2007-04-20' ); 
    SELECT strftime( '%m-%d %H:%M', date, 'localtime' ) FROM schedule WHERE task='Meeting';
  • datetime() 日付 -> 文字列変換
    select datetime(time, 'unixepoch', 'localtime') from ticket;
    select datetime(time, 'unixepoch', 'localtime') from ticket where date(time, 'unixepoch', 'localtime') >= '2007-01-01';
    select datetime(time, 'unixepoch', 'localtime') from ticket where date(time, 'unixepoch', 'localtime') BETWEEN '2007-01-01' and '2007-06-01';
  • unixタイムスタンプから任意の形式へ
    select strftime('%Y/%m/%d', project.ordertime, 'unixepoch', 'localtime') from project;

2.x -> 3.x。3.x -> 2.xへ移行

  • 2.x -> 3.x
    # sqlite database .dump | sqlite3 database.3
  • 3.x -> 2.x
    # sqlite3 database .dump | sqlite database.2

FOREIGN KEYを使う

破損チェック

  • チェック
    sqlite データベース名.db
    sqlite> pragma integrity_check;
    ok
    sqlite>
  • 修復を試みる
    sqlite データベース名.db .dump >backup.sql 
    sqlite データベース名.db < backup.sql

自動インクリメントカラム(シーケンス)

シーケンス表の替わりとして使える。
nullをINSERTすると、自動インクリメントされる。
が、他のデータベースとの移植性が下がるので、注意。

CREATE TABLE foo ( id INTEGER UNSIGNED PRIMARY KEY )
INSERT INTO foo VALUES (NULL)

テーブル等の情報を得る

$aType = array(
	'master',
	'tables',
	'schema',
	'schemax',
	'schema_x',
	'alter',
);

echo "<pre>";
foreach($aType as $type){
	$data = $db->getListOf($type);
	if (DB::isError($data)) {
	    continue;
	}
	echo "$type => ";
	var_export($data);
	echo "\n";
}

結果:

master => array (
  0 => 'table',
)
tables => array (
  0 => 'foo',
)
schema => array (
  0 => 'CREATE TABLE foo (bar varchar(10))',
)
schemax => array (
)
schema_x => array (
)

データベース情報を得る

データベースファイルの情報を得ます。
「size」を見る事で、データベース作成済みかどうかをチェックする等に使えるはず。

print_r($db->getDbFileStats());

結果:

Array
(
    [0] => 770
    [1] => 3113726
    [2] => 33206
    [3] => 1
    [4] => 48
    [5] => 48
    [6] => -1
    [7] => 0
    [8] => 1135670667
    [9] => 1135670667
    [10] => 1135670667
    [11] => -1
    [12] => -1
    [dev] => 770
    [ino] => 3113726
    [mode] => 33206
    [nlink] => 1
    [uid] => 48
    [gid] => 48
    [rdev] => -1
    [size] => 0
    [atime] => 1135670667
    [mtime] => 1135670667
    [ctime] => 1135670667
    [blksize] => -1
    [blocks] => -1
)

注意点

速度を得たい場合は、トランザクションを利用する

SQLiteは通常、SQL発行毎にBEGIN 〜 END を実行するので、処理が遅い場合がある。
大量のINSERT等を高速に行いたい場合は、以下のようにトランザクション内で実行すること。

  • ネイティブコマンド
    BEGIN;
    大量のINSERT処理...
    END;
  • PEAR::DB
    $db->autoCommit(false);
    $db->query(); // 大量に発行
    $db->commit();

v2.8.xではcurrent_timestampは使えない

dateフィールドに「current_timestamp」を使うと、apacheが「Segmentation fault」で落ちる。 *1
「# pear install sqlite」でインストールすると2.8.xがインストールされるので注意。
よって、datetime()関数を使うと良い。

select datetime('now','localtime')
Apache2.0.51
PHP4.3.11
SQLite2.8.14

インストール

  1. pearからインストール
    # pear install sqlite
  2. 「/etc/php.d/sqlite.ini」を作成、もしくは「/php.ini」に追加
    # vi /etc/php.d/sqlite.ini
    ---------------------------
    ; Enable sqlite extension module
    extension=sqlite.so
    ---------------------------
    +httpdの再起動
    # service httpd restart
    # php -m | grep sqlite
    ---------------------------
    sqlite
    ---------------------------

サンプル

ネイティブメソッド使用

<?php
if ($db = sqlite_open('mysqlitedb', 0666, $sqliteerror)) { 
  sqlite_query($db,'CREATE TABLE foo (bar varchar(10))');
  sqlite_query($db,"INSERT INTO foo VALUES ('fnord')");
  $result = sqlite_query($db,'select bar from foo');
  var_dump(sqlite_fetch_array($result)); 
} else {
  die ($sqliteerror);
}
?>

PEAR::DB使用

<?php
/**
* SQLiteサンプル2
*
* @note
* - PEAR DB使用
* - インストール
* @code
# pear install sqlite
# pear install DB
* @endcode
*/

require_once 'DB.php';

$dsn = 'sqlite:///mysqlite.db?mode=0666'; 

$options = array(
    'debug'       => 2,
    'portability' => DB_PORTABILITY_ALL,
);

$db =& DB::connect($dsn, $options);
if (DB::isError($db)) {
    die($db->getMessage());
}

$aStats = $db->getDbFileStats();
if($aStats["size"] <= 0){
	$result =& $db->query('CREATE TABLE foo (bar varchar(10))');
	if (DB::isError($result)) {
	    die ($result->getMessage());
	}
}

$result =& $db->query("INSERT INTO foo VALUES ('fnord')");
if (DB::isError($result)) {
    die ($result->getMessage());
}

$db->setFetchMode(DB_FETCHMODE_ASSOC);
$res =& $db->query('select bar from foo');
$row =& $res->fetchRow();
var_dump($row);

$db->disconnect();
?>

サポートしている型

http://www.sqlite.org/datatypes.html

BLOB
BOOLEAN
CHARACTER
CLOB
FLOAT
INTEGER
KEY
NATIONAL
NUMERIC
NVARCHAR
PRIMARY
TEXT
TIMESTAMP
UNIQUE
VARCHAR
VARYING
CREATE TABLE ex2(
  a VARCHAR(10),
  b NVARCHAR(15),
  c TEXT,
  d INTEGER,
  e FLOAT,
  f BOOLEAN,
  g CLOB,
  h BLOB,
  i TIMESTAMP,
  j NUMERIC(10,5)
  k VARYING CHARACTER (24),
  l NATIONAL VARYING CHARACTER(16)
);

ODBC

リンク


*1 3.1.0 ALPHAからサポートされた

トップ   編集 凍結 差分 バックアップ 添付 複製 名前変更 リロード   新規 一覧 単語検索 最終更新   ヘルプ   最終更新のRSS
Last-modified: 2010-03-08 (月) 12:45:27 (3055d)