SplFileObject + CallbackFilterIterator(PHP5.4) + PDO

callback-filter-iterator-pdo.php

<?php
namespace Acme;

class U {
    public static function H($data, $default = null) {
        if (isset($data)) {
            return htmlspecialchars($data, ENT_QUOTES, 'UTF-8');
        }
        return $default;
    }
}

class FilterableFileIterator extends \SplFileObject
{
    protected $filter = null;

    public function __set($name, $value)
    {
        if (strcmp('filter', $name) === 0) {
            if (!is_callable($value)) {
                throw new \InvalidArgumentException('The filter is not callable.');
            }
            $this->filter = $value;
            return;
        }
        throw new \RuntimeException(sprintf('The field "%s" not defined.', $name));
    }

    public function current()
    {
        $item = parent::current();
        if (isset($this->filter)) {
            $item = call_user_func($this->filter, $item, $this->key());
        }
        return $item;
    }

}

class Column
{
    const TYPE_INT  = 1;
    const TYPE_TEXT = 2;
    public $name;
    public $type;
    public $scale;
    public $max_length;
    public $not_null;
    public $primary_key;
    public $auto_increment;
    public $binary;
    public $default_value;
}

class ColumnsProcessor
{
    public static function get(\PDO $db, $table)
    {
        static $columns;
        if (!is_array($columns)) {
            $columns = array();
        }
        if (!isset($columns[$table])) {
            switch ($db->getAttribute(\PDO::ATTR_DRIVER_NAME)) {
            case 'sqlite':
                $statement = $db->query(sprintf('PRAGMA TABLE_INFO(%s);',
                    $db->quote($table)));
                $columnsList = $statement->fetchAll(\PDO::FETCH_FUNC, function(
                    $cid, $name, $type, $notnull, $dflt_value, $pk
                ) {
                    $max_length = -1;
                    $scale = null;
                    if (preg_match('/^(.+)\((\d+),(\d+)/', $type, $matches)) {
                        $type = $matches[1];
                        $max_length = is_numeric($matches[2]) ? $matches[2] : -1;
                        $scale = is_numeric($matches[3]) ? $matches[3] : -1;
                    } elseif (preg_match('/^(.+)\((\d+)/', $type, $matches)) {
                        $type = $matches[1];
                        $max_length = is_numeric($matches[2]) ? $matches[2] : -1;
                    }
                    $binary = (strcasecmp($type,'BLOB') === 0);
                    $auto_increment = ($pk && strcasecmp($type, 'INTEGER') === 0);
                    $default_value = (!$binary && strcmp($dflt_value, '') !== 0
                        && strcasecmp($dflt_value, 'NULL') !== 0) ? $dflt_value : null;
                    $column = new Column();
                    $column->name = $name;
                    switch ($type) {
                    case 'INTEGER':
                        $column->type = Column::TYPE_INT;
                        break;
                    case 'VARCHAR':
                        $column->type = Column::TYPE_TEXT;
                        break;
                    }
                    $column->scale = (int)$scale;
                    $column->max_length = (int)$max_length;
                    $column->not_null = (bool)$notnull;
                    $column->primary_key = (bool)$pk;
                    $column->auto_increment = (bool)$auto_increment;
                    $column->binary = (bool)$binary;
                    $column->default_value = $default_value;
                    return $column;
                });
            }
            foreach ($columnsList as $column) {
                $columns[$table][$column->name] = $column;
            }
        }
        return $columns[$table];
    }

}

class ColumnValidator
{
    public static function validate(Column $column, $value)
    {
        if ($column->not_null) {
            if (!isset($value) || strlen($value) === 0) {
                return false;
            }
        }
        switch ($column->type) {
        case Column::TYPE_INT:
            if (isset($value) && !ctype_digit($value) && !is_int($value)) {
                return false;
            }
            break;
        case Column::TYPE_TEXT:
            if (isset($column->max_length) && mb_strlen($value) > $column->max_length) {
                return false;
            }
            break;
        }
        return true;
    }
}

interface Model
{
    public function initialize(\PDO $db);
    public function validate();
}

trait ModelAccessor
{
    public function __set($name, $value)
    {
        if (!array_key_exists($name, $this->fields)) {
            throw new \RuntimeException(sprintf('The field "%s" is not defined.', $name));
        }
        $this->fields[$name] = $value;
    }
    public function __get($name)
    {
        if (!array_key_exists($name, $this->fields)) {
            throw new \RuntimeException(sprintf('The field "%s" is not defined.', $name));
        }
        return $this->fields[$name];
    }
    public function getFields()
    {
        return $this->fields;
    }
}

class User implements Model
{
    use ModelAccessor;

    private $columns = array();
    private $fields = array();

    public function __construct(\PDO $db)
    {
        $this->initialize($db);
    }

    public function initialize(\PDO $db)
    {
        $this->columns = ColumnsProcessor::get($db, 'users');
        $this->fields = array_combine(
            array_keys($this->columns),
            array_fill(0, count($this->columns), null)
        );
    }

    public function validate()
    {
        foreach ($this->columns as $name => $column) {
            if (!ColumnValidator::validate($column, $this->{$name})) {
                return false;
            }
        }
        return true;
    }

}


// データ初期化
$db = new \PDO('sqlite::memory:');
$db->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION);
$query = <<< SQL
CREATE TABLE IF NOT EXISTS users
(
    id      INTEGER      NOT NULL PRIMARY KEY,
    name    VARCHAR(10)  NOT NULL,
    comment VARCHAR(30)
);
SQL;
$db->exec($query);
$db->exec('DELETE FROM users;');

// CSVイテレータ定義
$csv_iterator = new FilterableFileIterator('php://memory', 'r+');
$csv_text = <<< CSV_TEXT
1,田中,
a,不正なID,
2,"小林""",コメント
3,長い名前長い名前長い名前,
4,"鈴木,","改行
改行
改行"
5,長井,とても長いコメントとても長いコメントとても長いコメントとても長いコメントとても長いコメント
CSV_TEXT;
$csv_text = mb_convert_encoding(str_replace("\n", "\r\n", $csv_text), 'SJIS', 'UTF-8');
$csv_iterator->fwrite($csv_text);
$csv_iterator->rewind();
$csv_iterator->setFlags(\SplFileObject::READ_CSV);
$csv_iterator->setCsvControl(',', '"');
$csv_iterator->filter = function($item, $key) use ($db) {
    if (!is_array($item)) {
        throw new \RuntimeException(sprintf('Invalid data found at line %d', $key));
    }
    $user = new User($db);
    if (isset($item[0])) {
        $user->id = mb_convert_encoding(trim($item[0]), 'UTF-8', 'SJIS');
    }
    if (isset($item[1])) {
        $user->name = mb_convert_encoding(trim($item[1]), 'UTF-8', 'SJIS');
    }
    if (isset($item[2])) {
        $user->comment = mb_convert_encoding($item[2], 'UTF-8', 'SJIS');
    }
    return $user;
};

// イテレータでバリデーション、妥当なデータのみ登録
$filter_iterator = new \CallbackFilterIterator($csv_iterator, function(Model $model) {
    return $model->validate();
});
foreach ($filter_iterator as $user) {
    $statement = $db->prepare('INSERT INTO users VALUES(:id, :name, :comment);');
    $statement->execute([
        ':id'      => $user->id,
        ':name'    => $user->name,
        ':comment' => $user->comment,
    ]);
}

?>
<!DOCTYPE html>
<html lang="ja">
<head>
<meta charset="UTF-8" />
<style type="text/css">
table {
    width:80%;
    border-collapse:collapse;
    margin:5px 0px;
}
caption {
    text-align:left;
    font-weight:bold;
}
th, td {
    padding:2px;
    border:solid 1px #999999;
}
tr.row1 {
    background-color:#ffffcc;
}
tr.row2 {
    background-color:#cccccc;
}
td.id {
    width:5%;
    text-align:right;
}
td.name {
    width:30%;
    text-align:left;
}
td.comment {
    width:65%;
    text-align:left;
    white-space:pre;
}
</style>
</head>
<body>

<table>
<caption>All Users</caption>
<thead>
    <tr>
        <th>ID</th>
        <th>名前</th>
        <th>コメント</th>
    </tr>
</thead>
<tbody>
<?php foreach ($csv_iterator as $index => $user) : ?>
    <tr class="row<?=(($index & 1) == 0) ? '1' : '2'?>">
        <td class="id"><?=U::H($user->id)?></td>
        <td class="name"><?=U::H($user->name)?></td>
        <td class="comment"><?=U::H($user->comment)?></td>
    </tr>
<?php endforeach ?>
</tbody>
</table>

<table>
<caption>Inserted Users</caption>
<thead>
    <tr>
        <th>ID</th>
        <th>名前</th>
        <th>コメント</th>
    </tr>
</thead>
<tbody>
<?php
$statement = $db->prepare('SELECT id, name, comment FROM users;');
$statement->setFetchMode(\PDO::FETCH_CLASS | \PDO::FETCH_PROPS_LATE, __NAMESPACE__ . '\\User', array($db));
$statement->execute();
?>
<?php foreach ($statement as $index => $user) : ?>
    <tr class="row<?=(($index & 1) == 0) ? '1' : '2'?>">
        <td class="id"><?=U::H($user->id)?></td>
        <td class="name"><?=U::H($user->name)?></td>
        <td class="comment"><?=U::H($user->comment)?></td>
    </tr>
<?php endforeach ?>
</tbody>
</table>

</body>
</html>

もはやCallbackFilterIterator関係ないコードに