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