k-holyのwebメモ

CREATE TABLE profiles
(
    id                 INTEGER      NOT NULL PRIMARY KEY,
    name               VARCHAR(20)  NOT NULL,
    notes              TEXT
);

CREATE TABLE tree_nodes
(
    id         INTEGER      NOT NULL PRIMARY KEY,
    profile_id INTEGER      NOT NULL,
    path       VARCHAR(255) NOT NULL,
    order_no   REAL         NOT NULL,
    FOREIGN KEY(profile_id) REFERENCES profiles(id)
);
CREATE INDEX tree_nodes_idx_path ON tree_nodes (path);

INSERT INTO profiles   (id, name) VALUES ( 1, '三好長之');
INSERT INTO tree_nodes (id, profile_id, path, order_no) VALUES ( 1, 1, '/1/' , 1);

INSERT INTO profiles   (id, name) VALUES ( 2, '三好之長');
INSERT INTO tree_nodes (id, profile_id, path, order_no) VALUES ( 2, 2, '/1/2/', 1);

INSERT INTO profiles   (id, name) VALUES ( 3, '三好勝時');
INSERT INTO tree_nodes (id, profile_id, path, order_no) VALUES ( 3, 3, '/1/3/', 2);

INSERT INTO profiles   (id, name) VALUES ( 4, '三好一秀');
INSERT INTO tree_nodes (id, profile_id, path, order_no) VALUES ( 4, 4, '/1/4/', 3);

INSERT INTO profiles   (id, name) VALUES ( 5, '三好勝宗');
INSERT INTO tree_nodes (id, profile_id, path, order_no) VALUES ( 5, 5, '/1/5/', 4);

INSERT INTO profiles   (id, name) VALUES ( 6, '三好長秀');
INSERT INTO tree_nodes (id, profile_id, path, order_no) VALUES ( 6, 6, '/1/2/6/', 1);

INSERT INTO profiles   (id, name) VALUES ( 7, '三好頼澄');
INSERT INTO tree_nodes (id, profile_id, path, order_no) VALUES ( 7, 7, '/1/2/7/', 2);

INSERT INTO profiles   (id, name) VALUES ( 8, '芥川長光');
INSERT INTO tree_nodes (id, profile_id, path, order_no) VALUES ( 8, 8, '/1/2/8/', 3);

INSERT INTO profiles   (id, name) VALUES ( 9, '三好長則');
INSERT INTO tree_nodes (id, profile_id, path, order_no) VALUES ( 9, 9, '/1/2/9/', 4);

INSERT INTO profiles   (id, name) VALUES (10, '三好新五郎');
INSERT INTO tree_nodes (id, profile_id, path, order_no) VALUES (10,10, '/1/3/10/', 1);

INSERT INTO profiles   (id, name) VALUES (11, '三好勝長');
INSERT INTO tree_nodes (id, profile_id, path, order_no) VALUES (11,11, '/1/3/11/', 2);

INSERT INTO profiles   (id, name) VALUES (12, '三好政長');
INSERT INTO tree_nodes (id, profile_id, path, order_no) VALUES (12,12, '/1/3/12/', 3);

INSERT INTO profiles   (id, name) VALUES (13, '三好元長');
INSERT INTO tree_nodes (id, profile_id, path, order_no) VALUES (13,13, '/1/2/6/13/', 1);

INSERT INTO profiles   (id, name) VALUES (14, '三好康長');
INSERT INTO tree_nodes (id, profile_id, path, order_no) VALUES (14,14, '/1/2/6/14/', 2);

INSERT INTO profiles   (id, name) VALUES (15, '三好政成');
INSERT INTO tree_nodes (id, profile_id, path, order_no) VALUES (15,15, '/1/2/7/15/', 1);

INSERT INTO profiles   (id, name) VALUES (16, '三好政康');
INSERT INTO tree_nodes (id, profile_id, path, order_no) VALUES (16,16, '/1/2/7/16/', 2);

INSERT INTO profiles   (id, name) VALUES (17, '芥川孫十郎');
INSERT INTO tree_nodes (id, profile_id, path, order_no) VALUES (17,17, '/1/2/8/17/', 1);

INSERT INTO profiles   (id, name) VALUES (18, '三好長逸');
INSERT INTO tree_nodes (id, profile_id, path, order_no) VALUES (18,18, '/1/2/9/18/', 1);

INSERT INTO profiles   (id, name) VALUES (19, '三好政勝');
INSERT INTO tree_nodes (id, profile_id, path, order_no) VALUES (19,19, '/1/3/12/19/', 1);

INSERT INTO profiles   (id, name) VALUES (20, '三好長慶');
INSERT INTO tree_nodes (id, profile_id, path, order_no) VALUES (20,20, '/1/2/6/13/20/', 1);

INSERT INTO profiles   (id, name) VALUES (21, '三好義賢');
INSERT INTO tree_nodes (id, profile_id, path, order_no) VALUES (21,21, '/1/2/6/13/21/', 2);

INSERT INTO profiles   (id, name) VALUES (22, '安宅冬康');
INSERT INTO tree_nodes (id, profile_id, path, order_no) VALUES (22,22, '/1/2/6/13/22/', 3);

INSERT INTO profiles   (id, name) VALUES (23, '十河一存');
INSERT INTO tree_nodes (id, profile_id, path, order_no) VALUES (23,23, '/1/2/6/13/23/', 4);

INSERT INTO profiles   (id, name) VALUES (24, '三好可政');
INSERT INTO tree_nodes (id, profile_id, path, order_no) VALUES (24,24, '/1/3/12/19/24/', 1);

<?php
/**
 * PHP versions 5
 *
 * @copyright  2011 k-holy <k.holy74@gmail.com>
 * @author     k.holy74@gmail.com
 * @license    http://www.opensource.org/licenses/mit-license.php  The MIT License (MIT)
 */
namespace Holy\RedBean;

/**
 * Holy\RedBean\ModelFormatter
 *
 * @author     k.holy74@gmail.com
 */
class ModelFormatter implements \RedBean_IModelFormatter
{
    /** @var string 名前空間 **/
    protected $namespace;

    /** @var string テーブル名に付与する接頭辞 **/
    protected $prefix;

    /** @var string テーブル名に付与する接尾辞 **/
    protected $suffix;

    public function __construct($namespace = null, $prefix = null, $suffix = null)
    {
        if (isset($namespace)) {
            $this->namespace = $namespace;
        }
        if (isset($prefix)) {
            $this->prefix = $prefix;
        }
        if (isset($suffix)) {
            $this->suffix = $suffix;;
        }
    }

    /**
     * テーブル名をモデルクラス名に変換して返します。
     * @param string テーブル名
     * @return string モデルクラス名
     */
    public function formatModel($model)
    {
        $model = self::camelize($model);
        if (isset($this->namespace)) {
            $model = $this->namespace . '\\'. $model;
        }
        if (isset($this->prefix)) {
            $model = $this->prefix . $model;
        }
        if (isset($this->suffix)) {
            $model .= $this->suffix;
        }
        return $model;
    }

    public static function camelize($string)
    {
        return str_replace(' ', '', ucwords(str_replace('_', ' ', $string)));
    }

}

<?php
/**
 * PHP versions 5
 *
 * @copyright  2011 k-holy <k.holy74@gmail.com>
 * @author     k.holy74@gmail.com
 * @license    http://www.opensource.org/licenses/mit-license.php  The MIT License (MIT)
 */
namespace Keizu;

abstract class AbstractModel extends \RedBean_SimpleModel
{
    protected $db;
    protected $table;
    protected $fields = array();

    public function setDb(\RedBean_FacadeHelper $db)
    {
        $this->db = $db;
    }

    public function getFieldNames()
    {
        return array_keys($this->fields);
    }

    public function update()
    {
        $this->validate(__FUNCTION__);
    }

    public function delete()
    {
        $this->validate(__FUNCTION__);
    }

    abstract function validate($method);

}

<?php
/**
 * PHP versions 5
 *
 * @copyright  2011 k-holy <k.holy74@gmail.com>
 * @author     k.holy74@gmail.com
 * @license    http://www.opensource.org/licenses/mit-license.php  The MIT License (MIT)
 */
namespace Keizu;

/**
 * Keizu\InputValidationException
 *
 * @author     k.holy74@gmail.com
 */
class InputValidationException extends \Exception
{

    public function __construct($errors)
    {
        parent::__construct(json_encode($errors));
    }

    public function getErrors()
    {
        return json_decode($this->getMessage());
    }
}

<?php
/**
 * PHP versions 5
 *
 * @copyright  2011 k-holy <k.holy74@gmail.com>
 * @author     k.holy74@gmail.com
 * @license    http://www.opensource.org/licenses/mit-license.php  The MIT License (MIT)
 */
namespace Keizu;

use Keizu\AbstractModel;
use Keizu\InputValidationException;

/**
 * 人物情報モデル
 */
class ProfilesModel extends AbstractModel
{

    protected $table = 'profiles';
    protected $fields = array(
        'name'  => '名前',
        'notes' => '備考',
    );

    public function validate($method)
    {
        $errors = array();
        foreach ($this->fields as $field => $label) {
            switch ($field) {
            case 'name':
                if (!isset($this->{$field}) || 0 === strlen($this->{$field})) {
                    $errors[$field] = sprintf('%sを入力してください', $label);
                } elseif (20 < mb_strlen($this->{$field}, 'UTF-8')) {
                    $errors[$field] = sprintf('%sを20文字以内で入力してください', $label);
                }
                break;
            }
        }
        if (!empty($errors)) {
            throw new InputValidationException($errors);
        }
        return true;
    }

}

<?php
/**
 * PHP versions 5
 *
 * @copyright  2011 k-holy <k.holy74@gmail.com>
 * @author     k.holy74@gmail.com
 * @license    http://www.opensource.org/licenses/mit-license.php  The MIT License (MIT)
 */
namespace Keizu;

use Keizu\AbstractModel;
use Keizu\InputValidationException;

/**
 * 系図構造モデル
 */
class TreeNodesModel extends AbstractModel
{

    protected $table = 'tree_nodes';
    protected $fields = array(
        'profile_id' => '人物ID',
        'path'       => 'パス',
        'order_no'   => '並び順',
    );

    public function validate($method)
    {
        if (!isset($this->db)) {
            throw new \RuntimeException('RedBean_FacadeHelper is not set.');
        }
        $errors = array();
        foreach ($this->fields as $field => $label) {
            switch ($field) {
            case 'profile_id':
                if (!isset($this->{$field})) {
                    $errors[$field] = sprintf('%sを入力してください', $label);
                } elseif (!ctype_digit($this->{$field})) {
                    $errors[$field] = sprintf('%sを数値で入力してください', $label);
                } else {
                    $profiles = $this->db->find('profiles', ' id = :id',
                        array('id' => sprintf('%d', $this->{$field})));
                    if (empty($profiles)) {
                        $errors[$field] = sprintf('無効な%sが指定されています', $label);
                    }
                }
                break;
            case 'path':
                if (isset($this->{$field}) && 0 < strlen($this->{$field}) &&
                    !preg_match('~\A/[0-9/]+/\z~', $this->{$field}))
                {
                    $errors[$field] = sprintf('無効な%sが指定されています', $label);
                }
                break;
            case 'order_no':
                if (!isset($this->{$field})) {
                    $errors[$field] = sprintf('%sを入力してください', $label);
                } elseif (!ctype_digit($this->{$field})) {
                    $errors[$field] = sprintf('%sを数値で入力してください', $label);
                }
                break;
            }
        }
        if (!empty($errors)) {
            throw new InputValidationException($errors);
        }
        return true;
    }

    public function getTree()
    {
        if (!isset($this->db)) {
            throw new \RuntimeException('RedBean_FacadeHelper is not set.');
        }
        // ツリー情報を取得
        $query = <<< SQL
SELECT
    PT.id
,   PP.name
,   PPT.id AS parent_id
,   COUNT(CT.id) AS child_count
FROM
    tree_nodes PT
LEFT OUTER JOIN
    profiles PP ON PT.profile_id = PP.id
LEFT OUTER JOIN
    tree_nodes CT ON PT.path =
        (SELECT MAX(path) FROM tree_nodes WHERE CT.path LIKE path || '_%')
LEFT OUTER JOIN
    tree_nodes PPT ON PPT.path =
        (SELECT MAX(path) FROM tree_nodes WHERE PT.path LIKE path || '_%')
GROUP BY PT.id, PP.name, PPT.id
ORDER BY PT.path
;
SQL;
        $id = (!empty($this->id)) ? $this->id : null;
        // jQuery plugin:Treeviewの仕様に合わせてツリーを構成
        // http://bassistance.de/jquery-plugins/jquery-plugin-treeview/
        $tree = call_user_func(function($items) use ($id) {
            $tree = array();
            $node = array();
            foreach ($items as $item){
                if (isset($node[$item['id']]['children'])) {
                    $item['children'] = $node[$item['id']]['children'];
                }
                $node[$item['id']] = array(
                    'id'   => $item['id'],
                    'text' => ((int)$item['id'] === (int)$id)
                        ? sprintf('<span class="selected">%d:%s</span>(%d)',
                            $item['id'], $item['name'], $item['child_count'])
                        : sprintf('<a href="/nodes/%d/">%d:%s<a>(%d)',
                            $item['id'], $item['id'], $item['name'], $item['child_count']),
                    'children' => array(),
                    'expanded' => true,
                );
                if (is_null($item['parent_id'])) {
                    $tree[] = &$node[$item['id']];
                } else {
                    $node[$item['parent_id']]['children'][] = &$node[$item['id']];
                }
            }
            return $tree;
        }, $this->db->getAll($query));
        return $tree;
    }

    public function getRoute()
    {
        if (!isset($this->db)) {
            throw new \RuntimeException('RedBean_FacadeHelper is not set.');
        }
        // ルートからの経路を取得
        $query = <<< SQL
SELECT
    CT.*
,   CP.name
FROM
    tree_nodes CT
,   tree_nodes PT
LEFT OUTER JOIN
    profiles CP ON CT.profile_id = CP.id
WHERE
    CT.path LIKE '/1/%'
AND PT.path LIKE CT.path || '%'
AND PT.id = :id
ORDER BY CT.path
;
SQL;
        return $this->db->getAll($query, array('id' => $this->id));
    }

}

<?php
/**
 * PHP versions 5
 *
 * @copyright  2011 k-holy <k.holy74@gmail.com>
 * @author     k.holy74@gmail.com
 * @license    http://www.opensource.org/licenses/mit-license.php  The MIT License (MIT)
 */
namespace Keizu;

require_once realpath(__DIR__ . '/silex.phar');

const INTERNAL_ENCODING = 'UTF-8';
const CSRF_TOKEN_NAME   = 'AEg9GE5#4ZyPY$fd';

use Silex\Application;
use Silex\Provider\SessionServiceProvider;
use Symfony\Component\HttpFoundation\Request;
use Symfony\Component\HttpFoundation\Response;

use Holy\Silex\Provider\PhpTalServiceProvider;
use Holy\Silex\Provider\RedBeanServiceProvider;
use Holy\RedBean\ModelFormatter;

use Keizu\InputValidationException;

$app = new Application();
$app['debug'] = true;

$app['autoloader']->registerNamespace('Holy' ,
    realpath(__DIR__ . '/vendor/k-holy/src'));

$app['autoloader']->registerNamespace('Keizu',
    realpath(__DIR__ . '/app/src'));

$app->register(new SessionServiceProvider());
$app->register(new PhpTalServiceProvider(), array(
    'phptal.class_path' => realpath(__DIR__ . '/vendor/phptal'),
    'phptal.options' => array(
        'templateRepository' => realpath(__DIR__ . '/views'),
    ),
));
$app->register(new RedBeanServiceProvider(), array(
    'db.redbean.class_path' => realpath(__DIR__ . '/vendor/redbean'),
    'db.options' => array(
        'dsn' => sprintf('sqlite:%s', realpath(__DIR__ . '/app/data/keizu.sqlite')),
    ),
));
$app['db']->debug(true);

// RedBeanのModelFormatterを設定
\RedBean_ModelHelper::setModelFormatter(new ModelFormatter('Keizu', null, 'Model'));

// Error handler
$app->error(function(\Exception $e, $code) use ($app) {
    $message = 'Internal Server Error';
    if ($e instanceof \Symfony\Component\HttpKernel\Exception\HttpExceptionInterface) {
        switch ($code) {
        case 403:
            $message = 'Forbidden';
            break;
        case 404:
            $message = 'NotFound';
            break;
        case 405:
            $message = 'Method Not Allowed';
            break;
        }
    }
    $app['phptal']->set('message', $message);
    $app['phptal']->set('error', ($app['debug']) ? $e->__toString() : null);
    $app['phptal']->set('title', 'エラー');
    return new Response($app['phptal']->setTemplate('error.html')->execute(),
        (isset($code)) ? $code : 500);
});

// Before filter
$app->before(function(Request $request) use($app) {
    $app['phptal']->set('REQUEST_URI', $request->getRequestUri());
    if ($app['request']->query->has('back')) {
        $app['phptal']->set('BACK_URI', $app['request']->query->get('back'));
    }
    if ($app['session']->hasFlash('message')) {
        $app['phptal']->set('message', $app['session']->getFlash('message'));
        $app['session']->removeFlash('message');
    }
});

//----------------------------------------------------------------------------
// トップページ
//----------------------------------------------------------------------------
$app->get('/', function() use ($app) {
    $app['phptal']->set('title', 'トップページ');
    return $app['phptal']->setTemplate('index.html')->execute();
});

//----------------------------------------------------------------------------
// 人物一覧
//----------------------------------------------------------------------------
$app->get('/profiles/', function() use ($app) {
    $app['phptal']->set('items', $app['db']->find('profiles', ' 1 ORDER BY id'));
    $app['phptal']->set('title', '人物一覧');
    return $app['phptal']->setTemplate('profiles/index.html')->execute();
});

//----------------------------------------------------------------------------
// 人物登録
//----------------------------------------------------------------------------
$app->match('/profiles/register', function() use ($app) {
    $bean = $app['db']->dispense('profiles');
    switch($app['request']->getMethod()) {
    case 'POST':
        $bean->import($app['request']->request->all(), $bean->getFieldNames());
        if ($app['request']->get(CSRF_TOKEN_NAME) === $app['session']->getId()) {
            try {
                $app['db']->store($bean);
            } catch (InputValidationException $ex) {
                $app['phptal']->set('errors', $ex->getErrors());
                break;
            }
            $app['session']->setFlash('message', '人物情報を登録しました。');
            return $app->redirect('/profiles/', 302);
        }
        break;
    }
    $app['phptal']->set('token', array(
        'name'  => CSRF_TOKEN_NAME,
        'value' => $app['session']->getId(),
    ));
    $app['phptal']->set('data' , $bean);
    $app['phptal']->set('title', '人物登録');
    return $app['phptal']->setTemplate('profiles/edit.html')->execute();
});

//----------------------------------------------------------------------------
// 人物編集
//----------------------------------------------------------------------------
$app->match('/profiles/{id}/modify', function($id) use ($app) {
    $bean = $app['db']->load('profiles', $id);
    switch($app['request']->getMethod()) {
    case 'POST':
        $bean->import($app['request']->request->all(), $bean->getFieldNames());
        if ($app['request']->get(CSRF_TOKEN_NAME) === $app['session']->getId()) {
            try {
                $app['db']->store($bean);
            } catch (InputValidationException $ex) {
                $app['phptal']->set('errors', $ex->getErrors());
                break;
            }
            $app['session']->setFlash('message', '人物情報を更新しました。');
            return $app->redirect('/profiles/', 302);
        }
        break;
    }
    $app['phptal']->set('token', array(
        'name'  => CSRF_TOKEN_NAME,
        'value' => $app['session']->getId(),
    ));
    $app['phptal']->set('data' , $bean);
    $app['phptal']->set('title', '人物編集');
    return $app['phptal']->setTemplate('profiles/edit.html')->execute();
})->assert('id', '\d+');

//----------------------------------------------------------------------------
// 人物削除
//----------------------------------------------------------------------------
$app->match('/profiles/{id}/delete', function($id) use ($app) {
    $bean = $app['db']->load('profiles', $id);
    switch($app['request']->getMethod()) {
    case 'POST':
        if ($app['request']->get(CSRF_TOKEN_NAME) === $app['session']->getId()) {
            try {
                $app['db']->trash($bean);
            } catch (InputValidationException $ex) {
                $app['phptal']->set('errors', $ex->getErrors());
                break;
            }
            $app['session']->setFlash('message', '人物情報を削除しました。');
            return $app->redirect('/profiles/', 302);
        }
        break;
    }
    $app['phptal']->set('token', array(
        'name'  => CSRF_TOKEN_NAME,
        'value' => $app['session']->getId(),
    ));
    $app['phptal']->set('data' , $bean);
    $app['phptal']->set('title', '人物削除');
    return $app['phptal']->setTemplate('profiles/delete.html')->execute();
})->assert('id', '\d+');

//----------------------------------------------------------------------------
// ルートノードに転送
//----------------------------------------------------------------------------
$app->get('/nodes/', function() use ($app) {
    return $app->redirect('/nodes/1/', 301);
});

//----------------------------------------------------------------------------
// 人物詳細＋ツリー表示
//----------------------------------------------------------------------------
$app->get('/nodes/{id}/', function($id) use ($app) {
    $node = $app['db']->load('tree_nodes', $id);
    $profile = $app['db']->load('profiles', $node->profile_id);
    $node->setDb($app['db']);
    $app['phptal']->set('profile', $profile);
    $app['phptal']->set('node'   , $node);
    $app['phptal']->set('route'  , $node->getRoute());
    $app['phptal']->set('title'  , 'ノード詳細');
    return $app['phptal']->setTemplate('nodes/detail.html')->execute();
})->assert('id', '\d+');

//----------------------------------------------------------------------------
// ツリーをJSONで返す
//----------------------------------------------------------------------------
$app->get('/tree.{id}.json', function($id) use ($app) {
    $app['db']->debug(false);
    $node = $app['db']->load('tree_nodes', $id);
    $node->setDb($app['db']);
    return new Response(json_encode($node->getTree()), 200,
        array('Content-Type' => 'application/json'));
})->assert('id', '\d+');

$app->run();