This repository was archived by the owner on Oct 3, 2019. It is now read-only.
-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathSharding.php
More file actions
145 lines (132 loc) · 4.33 KB
/
Sharding.php
File metadata and controls
145 lines (132 loc) · 4.33 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
<?php
/**
* Sharding class file
* Sharding is a yii extension that performs horizontal partitioning of a table
* The purpose of this is to reduce the index size and thus reduces query time
*
* @author Matthew Torres
* @version 1.0 2/12/2014
*/
class Sharding
{
private $currTable, $nextTable, $shardTally, $attr;
/**
* Initializes table name, near max and limit of the table
* @param string $tableName the name of the table to be sharded
* @param integer $limit the limit of the table
* for asynchronous-like sharding
*/
public function __construct($tableName, $limit){
$this->table = $tableName;
$this->limit = $limit;
}
/**
* Checks if table exists in shard table and initializes table names and tally
* @return string method that counts the number of rows of the table to be sharded
*/
public function useTable(){
$this->attr = $this->findTable();
if($this->attr)
{
$lastAttr = count($this->attr) - 1;
$this->shardTally = $this->attr[$lastAttr]->count;
$this->currTable = $this->table . $this->shardTally;
$this->nextTable = $this->table . ($this->shardTally + 1);
}else {
$this->currTable = $this->table;
$this->nextTable = $this->table . 1;
$this->shardTally = 1;
}
return $this->countRow();
}
/**
* Counts the number of rows based on the defined max number of rows
* @return string returns new name if row count is greater than or equal to the row count
* returns current table if it does not
*/
private function countRow(){
$dataReader = $this->queryBuilder('SELECT * FROM ' . $this->currTable);
$rowCount = $dataReader->rowCount;
if($rowCount >= $this->limit)
{
$this->createTable();
if($this->attr){
$model = new Shardtable;
$model->columnName = $this->table;
$this->shardTally = $model->count = $this->shardTally + 1;
$model->save();
$this->mergeTable();
}else {
$model = new Shardtable;
$model->columnName = $this->table;
$model->count = 1;
$model->save();
}
return $this->nextTable;
}else {
return $this->currTable;
}
}
/**
* Creates a new schema copied from the pervious table and changed some names
* to create an empty table
* @return [type] [description]
*/
private function createTable(){
if($this->attr){
$db = Yii::app()->db;
$command = $db->createCommand('SHOW CREATE TABLE' . $db->quoteTableName($this->currTable) . ';')->queryRow();
$sql = $command['Create Table'];
preg_match_all('/(_\d+)/', $sql, $matches);
for($i=0;$i<count($matches[1]);$i++){
$sql = preg_replace('/' . $matches[1][$i] . '/', '_' . ($this->shardTally + 1), $sql);
}
$sql = preg_replace('/`' . $this->currTable .'`/', '`' . $this->nextTable . '`', $sql);
}else {
$db = Yii::app()->db;
$command = $db->createCommand('SHOW CREATE TABLE' . $db->quoteTableName($this->table) . ';')->queryRow();
$sql = $command['Create Table'];
preg_match_all('/T\s`(\w+)`\sF/', $sql, $matches);
for($i=0;$i<count($matches[1]);$i++){
$sql = preg_replace('/' . $matches[1][$i] . '/', $matches[1][$i] . '_' . 1, $sql);
}
$sql = preg_replace('/`' . $this->table .'`/', '`' . $this->nextTable . '`', $sql);
}
$tableExist = Yii::app()->db->schema->getTable($this->nextTable);
$tableExist ? $dataReader = '' : $dataReader = $this->queryBuilder($sql);
}
/**
* Inserts previously sharded table and
* merges it to the main table
*/
private function mergeTable(){
if($this->shardTally >= 2){
$this->shardTally -= 1;
$dropTable = $this->table.$this->shardTally;
Yii::app()->db->createCommand('INSERT INTO ' . $this->table . ' SELECT * FROM ' . $dropTable)->execute();
Yii::app()->db->createCommand()->dropTable($dropTable);
}
}
/**
* Query builder and execution
* @param string $sql retrieves query to be executed
*/
private function queryBuilder($sql){
$conn = Yii::app()->db;
$command = $conn->createCommand($sql);
$dataReader = $command->query();
return $dataReader;
}
/**
* Checks if table name has been stored in the shard table
* @return string attributes of the table name
* returns null if empty
*/
private function findTable(){
$citeria = new CDbCriteria;
$citeria->addCondition(' columnName = "' . $this->table . '"');
$shard = Shardtable::model()->findAll($citeria);
return $shard;
}
}
?>