Awesome
RedisPlugin for Phalcon (The correspondence of MySQL sharding.)
Composer
{
"require": {
"ienaga/phalcon-redis-plugin": "3.*"
}
}
Version
PHP: 7.0.x, 7.1.x, 7.2.x
Phalcon: 3.x
phpredis and YAML
sudo yum install libyaml libyaml-devel php-pecl-yaml php-pecl-redis
app/config/config.php
@see PhalconConfig
$loader = new Phalcon\Config\Adapter\Yaml\Loader();
return $loader
->setIgnore(["routing"]) // ignore yml names
->setEnvironment("stg") // default dev
->setBasePath(realpath(dirname(__FILE__) . "/../.."))
->load();
app/config/database.yml
prd:
stg:
dev:
database:
dbAdminMaster:
adapter: Mysql
host: 127.0.0.1
port: 3301
username: root
password: XXXXX
dbname: admin
charset: utf8
transaction: true
dbAdminSlave:
adapter: Mysql
host: 127.0.0.1
port: 3311
username: root
password: XXXXX
dbname: admin
charset: utf8
transaction: false
dbCommonMaster:
adapter: Mysql
host: 127.0.0.1
port: 3301
username: root
password: XXXXX
dbname: common
charset: utf8
transaction: false
dbCommonSlave:
adapter: Mysql
host: 127.0.0.1
port: 3311
username: root
password: XXXXX
dbname: common
charset: utf8
transaction: false
dbUser1Master:
adapter: Mysql
host: 127.0.0.1
port: 3306
username: root
password: XXXXX
dbname: user
charset: utf8
transaction: true
dbUser1Slave:
adapter: Mysql
host: 127.0.0.1
port: 3316
username: root
password: XXXXX
dbname: user
charset: utf8
transaction: false
dbUser2Master:
adapter: Mysql
host: 127.0.0.1
port: 3307
username: root
password: XXXXX
dbname: user
charset: utf8
transaction: true
dbUser2Slave:
adapter: Mysql
host: 127.0.0.1
port: 3317
username: root
password: XXXXX
dbname: user
charset: utf8
transaction: false
app/config/redis.yml
prd:
stg:
dev:
redis:
# log出力
logger:
logging: true # logging ON OFF
output: /var/www/project/log/sql.log # output log file path
enabled: true # false => cache off
autoIndex: true # false => auto index off
# 対象のカラムがModelに存在したら使用。上から順に優先。存在が確認できた時点でbreak
prefix:
columns: # e.g. user_id, id, social_id, [account, password]
- user_id
- social_id
- [account, password]
- id
# 共通のマスタがあれば登録「table_」と共有部分だけの記載はtable_*と同義
# common
common:
enabled: false
service:
name: dbCommon
dbs: # e.g. master_, access_log
- mst_
# Sharding設定
shard:
enabled: true # Shardingを使用しない時はfalse
# Shardingのマスタ設定
admin:
service:
name: dbAdmin
# Shardingのマスタ設定
admin:
service:
name: dbAdmin
# ユーザマスタ
# e.g.
# CREATE TABLE IF NOT EXISTS `admin_user` (
# `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
# `social_id` varchar(255) NOT NULL,
# `admin_db_config_id` int(10) unsigned NOT NULL,
# PRIMARY KEY (`id`)
# ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
model: AdminUser # e.g. AdminUser or namespace \Project\AdminUser
column: admin_db_config_id # e.g. admin_db_config_id
# ユーザマスタの登録「table_」と共有部分だけの記載はtable_*と同義
dbs: # e.g. admin_, user_ranking
- admin_
# Shardingをコントロールするテーブルとカラム
#
# e.g.
# CREATE TABLE IF NOT EXISTS `admin_db_config` (
# `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
# `name` varchar(50) NOT NULL,
# `gravity` tinyint(3) unsigned NOT NULL DEFAULT '0',
# PRIMARY KEY (`id`)
# ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
# INSERT INTO `admin_db_config` (`id`, `name`, `gravity`) VALUES
# (1, 'dbUser1', 50),
# (2, 'dbUser2', 50);
# shard config master
control:
model: AdminDbConfig # e.g. AdminConfigDb or namespace \Project\AdminConfigDb
column: name # e.g. name
# schemaをキャッシュ
metadata:
host: 127.0.0.1
port: 6379
select: 0
# servers
server:
dbMaster:
host: 127.0.0.1
port: 6379
select: 0 # redis select [データベースインデックス]
dbSlave:
host: 127.0.0.1
port: 6379
select: 0
dbCommonMaster:
host: 127.0.0.1
port: 6379
select: 0
dbCommonSlave:
host: 127.0.0.1
port: 6379
select: 0
dbUser1Master:
host: 127.0.0.1
port: 6379
select: 0
dbUser1Slave:
host: 127.0.0.1
port: 6379
select: 0
dbUser2Master:
host: 127.0.0.1
port: 6379
select: 0
dbUser2Slave:
host: 127.0.0.1
port: 6379
select: 0
app/config/services.php
/**
* Database connection is created based in the parameters defined in the configuration file
*/
$dbService = new \Phalcon\Mvc\Model\Adapter\Redis\Service();
$dbService->registration();
/**
* If the configuration specify the use of metadata adapter use it or use memory otherwise
*/
$di->setShared('modelsMetadata', function () {
return new \Phalcon\Mvc\Model\Adapter\Redis\Metadata\Redis(
$this->getConfig()
->get("redis")
->get("metadata")
->toArray()
);
});
findFirst
class Robot extends \Phalcon\Mvc\Model\Adapter\Redis\Model {}
# findFirst
$robot = Robot::criteria()
->add('id', $id)
->add('type', $type, Criteria::NOT_EQUAL)
->group('type')
->findFirst();
find
class Robot extends \Phalcon\Mvc\Model\Adapter\Redis\Model {}
$robot = Robot::criteria()
->add('id', array($id), Criteria::IN)
->add('type', array($start, $end), Criteria::BETWEEN)
->limit(10, 5) // limit, offset
->order('type DESC')
->find();
cache Control
class Robot extends \Phalcon\Mvc\Model\Adapter\Redis\Model {}
$robot = Robot::criteria()
->add('id', array($id), Criteria::IN)
->add('type', array($start, $end), Criteria::BETWEEN)
->limit(10, 30)
->order('type DESC')
->cache(false)
->find();
autoIndex Control
class Robot extends \Phalcon\Mvc\Model\Adapter\Redis\Model {}
$robot = Robot::criteria()
->add('id', array($id), Criteria::IN)
->add('type', array($start, $end), Criteria::BETWEEN)
->limit(10, 30)
->order('type DESC')
->autoIndex(false)
->find();
save
class UserItem extends \Phalcon\Mvc\Model\Adapter\Redis\Model {}
$userItem = new UserItem();
$userItem->setId($id);
$userItem->setType($type);
$userItem->save();
update
class Robot extends \Phalcon\Mvc\Model\Adapter\Redis\Model {}
Robot::criteria()
->add("user_status", 1)
->add("power", 100)
->set("status", 2)
->set("name", "robot")
->update();
UPDATE `robot` SET `status` = 2, `name` = "robot" WHERE `user_status` = 1 AND `power` = 100;
delete
class Robot extends \Phalcon\Mvc\Model\Adapter\Redis\Model {}
Robot::criteria()
->add("user_status", 1)
->add("power", 100, Robot::GREATER_EQUAL)
->delete();
DELETE FROM `robot` WHERE `user_status` = 1 AND `power` >= 100;
count
class Robot extends \Phalcon\Mvc\Model\Adapter\Redis\Model {}
$count = Robot::criteria()
->add("user_status", 1)
->add("power", 100)
->add("status", 2)
->count();
sum
class Robot extends \Phalcon\Mvc\Model\Adapter\Redis\Model {}
$sum = Robot::criteria()
->add("user_status", 1)
->sum("price");
autoIndex
e.g. PRIMARY = type, INDEX = id, status ※autoIndexをtrueにする事で、PRIMARYもしくはINDEXに一番マッチするクエリに並び替えて発行。
class Robot extends \Phalcon\Mvc\Model\Adapter\Redis\Model {}
$robot = Robot::criteria()
->limit(10)
->add("type", $type)
->addGroup("type")
->addOrder("id", "DESC")
->add("status", $status)
->add("id", $id)
->find();
SELECT * FROM `robot`
WHERE `id` = :id:
AND `type` = :type:
AND `status` = :status:
GROUP BY `type`
ORDER BY `id` DESC
LIMIT 10
Index Test Mode
テストモードをtrueにしてSQLを発行。 どのIndexにもマッチしない時はErrorを出力。
- 単体
class Robot extends \Phalcon\Mvc\Model\Adapter\Redis\Model {}
$robot = Robot::criteria()
->limit(10)
->add("type", $type)
->addGroup("type")
->addOrder("id", "DESC")
->add("status", $status)
->add("id", $id)
->test(true)
->find();
- 全体
class Robot extends \Phalcon\Mvc\Model\Adapter\Redis\Model {}
\RedisPlugin\Mvc\Model::test(true);
$robot = Robot::criteria()
->limit(10)
->add("type", $type)
->addGroup("type")
->addOrder("id", "DESC")
->add("status", $status)
->add("id", $id)
->find();