public function run()
{
$conf = Config::get('sharding');
$shard = new Adapters\MysqlShard($conf);
$strategy = new Strategy\LinearStrategy(null, self::name, $shard->getConfig());
$name = self::name;
$pos = strlen(self::name) + 2;
$sql = "SELECT substr( TABLE_NAME, {$pos}) AS num, TABLE_ROWS as `count` \n FROM information_schema.TABLES \n WHERE TABLE_SCHEMA \n LIKE '{$name}_%' \n AND TABLE_NAME REGEXP '^({$name}_)[0-9]+\$'";
$tables = [];
for ($cnn_id = 0; $cnn_id < $shard->getInstanceCount(); $cnn_id++) {
try {
$mysql = $shard->getConnectionById($cnn_id);
} catch (Exception $e) {
echo $e->getMessage();
}
$res = $mysql->query($sql);
while (($row = $res->fetch_assoc()) != null) {
$tables[$row['num']] = $row['count'];
}
}
$sum = count($tables);
$fillTables = 0;
$startTabId = 0;
foreach ($tables as $tab_id => $count) {
if ($count > 0) {
$fillTables++;
}
if ($tab_id > $startTabId) {
$startTabId = (int) $tab_id;
}
}
if ($sum - $fillTables > self::emptyTables) {
$out = sprintf('fulling tables %d from %d %s', $fillTables, $sum, date('Y-m-d'));
echo $out, PHP_EOL;
exit;
}
$out = sprintf('generate: full tables %d from %d', $fillTables, $sum);
$startTabId++;
echo 'generate tables:', PHP_EOL;
// $shard->setNoExec();
$table_names = '';
for ($i = $startTabId; $i < $startTabId + self::count; $i++) {
$table_names .= self::name . '_' . $i . PHP_EOL;
$strategy->setId(Strategy\LinearStrategy::MAX_RECORD_COUNT * $i + 1);
$table = Generator\LinesGenerator::getCreateTableSQL($i);
$shard->query($table, $strategy);
// echo $shard->getQuery(), PHP_EOL,PHP_EOL;
}
echo $table_names, PHP_EOL;
}
/**
* данный тест пишет 120 кликов начиная с id = 799999890
* клики должны лечь в таблицы lines_7, lines_8
* после этого считываются все данные из этих таблиц
* и проверяются
* по окончанию теста - данные зачищаются
*
*/
public function testQueryRealExec()
{
// clear tables
$this->dbInit();
/**
* set shard to seven
*/
$time = time();
$expire = $time + 100;
$id = 899999890;
$sql = "INSERT INTO %db.lines_%t (id, ts,ua_id) VALUES({$id}, {$time}, 1777)";
$strategy = new LinearStrategy($id, 'lines', $this->sharding->getConfig());
$strategy->setTabIdToCache(8);
$this->sharding->query($sql, $strategy);
// test on insert record
$sql = "SELECT * FROM %db.lines_%t";
$recordSet = $this->sharding->query($sql, $strategy);
$this->assertNotNull($recordSet);
$row = $recordSet->fetch_array();
$this->assertNotNull($row);
$this->assertEquals($time, $row[1]);
$this->assertEquals($id, $row[0]);
$this->assertEquals(1777, $row[2]);
// $this->dbInit();
//------- begin test for change shard -------
$strategy = new LinearStrategy(null, 'lines', $this->sharding->getConfig());
$tab_id = 7;
for ($i = 0; $i < 20; $i++) {
$sql = "INSERT INTO %db.lines_%t (ts,ua_id) VALUES({$time}, {$tab_id})";
$tab_id = $strategy->getTabId();
$this->sharding->query($sql, $strategy);
$this->assertEquals(0, $this->sharding->getErrorCode());
$this->assertEquals($tab_id, (int) ($this->sharding->getLastId() / LinearStrategy::MAX_RECORD_COUNT));
}
// check data into shard 8
$id = 899999890;
$strategy->setId($id);
$sql = "SELECT * FROM %db.lines_%t ORDER BY id";
$res = $this->sharding->query($sql, $strategy);
$offer_id = 1;
$this->assertNotNull($res);
$counter = 0;
while (($row = $res->fetch_assoc()) != null) {
$this->assertEquals($time, $row['ts']);
$this->assertEquals($id, $row['id']);
$id++;
$counter++;
}
// теперь проверяем вставку оставшихся данных на шарде 8
// сквозной счетчик на поле offer_id
$id = 9 * LinearStrategy::MAX_RECORD_COUNT;
$strategy->setId($id);
$sql = "SELECT * FROM %db.lines_%t ORDER BY id";
$res = $this->sharding->query($sql, $strategy);
while (($row = $res->fetch_array()) != null) {
$this->assertEquals($time, $row['ts']);
$counter++;
$id++;
}
$this->assertEquals(21, $counter);
// clean data
$this->dbInit();
}
<?php
require '../src/autoload.php';
use MysqlShard\Adapters\MysqlShard;
use MysqlShard\Tools\Config;
use MysqlShard\Strategy\LinearStrategy;
use MysqlShard\Generator\LinesGenerator;
$shard = new MysqlShard();
$strategy = new LinearStrategy(null, 'lines', $shard->getConfig());
// create 10 tables for shards 0..9
for ($i = 0; $i < 10; $i++) {
$query = LinesGenerator::getCreateTableSQL($i);
$strategy->setId($i * LinearStrategy::MAX_RECORD_COUNT + 1);
$shard->query($query, $strategy);
}
