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;

• http://www.tuyudaku.net/sqlite/date.html
• http://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions

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を使う †

• 専用のTRIGGERを登録する必要があるようです。
  http://www.sqlite.org/cvstrac/wiki?p=ForeignKeyTriggers
  SQLiteでの外部結合

破損チェック †

• チェック

  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();

• 生まれ変わるPHP - Zend Engine 2、SQLiteの実力は?

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

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

select datetime('now','localtime')

インストール †

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

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 †

• SQLite ODBC Driver

リンク †

• データ型
• 構文
• 変更履歴

Software

• 自作ソフト
• wiki自作プラグイン
• 利用規定
• ランキング
• 雑誌等掲載履歴

Programming

Game

雑記

連絡先

• MenuBar
• RightBar
• :admin
  

人気の10件

最新の10件