Skip to content

Commit c3d8582

Browse files
jawbjawb
committed
same db diff
1 parent 5695ef1 commit c3d8582

8 files changed

Lines changed: 207 additions & 52 deletions

File tree

‎.dbdiff‎

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1,9 +1,9 @@
11
server1-user: root
2-
server1-password: xxxx
2+
server1-password: youndaime
33
server1-port: 3306
44
server1-host: localhost
55
#server2-user: root
6-
#server2-password: xxxx
6+
#server2-password: youndaime
77
#server2-port: 3306
88
#server2-host: localhost
99
template: simple-db-migrate.tmpl

‎.gitignore‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,6 @@
11
/vendor
22
composer.phar
33
composer.lock
4+
migration
45
.DS_Store
56
Thumbs.db

‎src/DB/DBManager.php‎

Lines changed: 16 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -57,4 +57,20 @@ public function getTables($connection) {
5757
return array_flatten($result);
5858
}
5959

60+
public function getColumns($connection, $table) {
61+
$result = $this->getDB($connection)->select("show columns from $table");
62+
return array_pluck($result, 'Field');
63+
}
64+
65+
public function getKey($connection, $table) {
66+
$keys = $this->getDB($connection)->select("show indexes from $table");
67+
$ukey = [];
68+
foreach ($keys as $key) {
69+
if ($key['Key_name'] === 'PRIMARY') {
70+
$ukey[] = $key['Column_name'];
71+
}
72+
}
73+
return $ukey;
74+
}
75+
6076
}

‎src/DB/Data/ArrayDiff.php‎

Lines changed: 3 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -7,7 +7,7 @@
77

88
class ArrayDiff {
99

10-
const SIZE = 1;
10+
public static $size = 1000;
1111

1212
function __construct($key, $dbiterator1, $dbiterator2) {
1313
$this->key = $key;
@@ -26,9 +26,9 @@ public function getDiff() {
2626
}
2727

2828
public function iterate() {
29-
$data1 = $this->dbiterator1->next(ArrayDiff::SIZE);
29+
$data1 = $this->dbiterator1->next(ArrayDiff::$size);
3030
$this->sourceBucket = array_merge($this->sourceBucket, $data1);
31-
$data2 = $this->dbiterator2->next(ArrayDiff::SIZE);
31+
$data2 = $this->dbiterator2->next(ArrayDiff::$size);
3232
$this->targetBucket = array_merge($this->targetBucket, $data2);
3333
$this->tag();
3434
}

‎src/DB/Data/DistTableData.php‎

Lines changed: 50 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,50 @@
1+
<?php namespace DBDiff\DB\Data;
2+
3+
use DBDiff\Diff\InsertData;
4+
use DBDiff\Diff\UpdateData;
5+
use DBDiff\Diff\DeleteData;
6+
use DBDiff\Exceptions\DataException;
7+
use DBDiff\Logger;
8+
9+
10+
class DistTableData {
11+
12+
function __construct($manager) {
13+
$this->manager = $manager;
14+
$this->source = $this->manager->getDB('source');
15+
$this->target = $this->manager->getDB('target');
16+
}
17+
18+
public function getIterator($connection, $table) {
19+
return new TableIterator($this->{$connection}, $table);
20+
}
21+
22+
public function getDataDiff($table, $key) {
23+
$sourceIterator = $this->getIterator('source', $table);
24+
$targetIterator = $this->getIterator('target', $table);
25+
$differ = new ArrayDiff($key, $sourceIterator, $targetIterator);
26+
return $differ->getDiff();
27+
}
28+
29+
public function getDiff($table, $key) {
30+
Logger::info("Now calculating data diff for table `$table`");
31+
$starTime = time();
32+
$diffs = $this->getDataDiff($table, $key);
33+
$diffSequence = [];
34+
foreach ($diffs as $name => $diff) {
35+
if ($diff['diff'] instanceof \Diff\DiffOp\DiffOpRemove) {
36+
$diffSequence[] = new DeleteData($table, $diff);
37+
} else if (is_array($diff['diff'])) {
38+
$diffSequence[] = new UpdateData($table, $diff);
39+
} else if ($diff['diff'] instanceof \Diff\DiffOp\DiffOpAdd) {
40+
$diffSequence[] = new InsertData($table, $diff);
41+
}
42+
}
43+
$endTime = time();
44+
$time = $endTime - $starTime;
45+
Logger::info("Time {$time}s");
46+
47+
return $diffSequence;
48+
}
49+
50+
}

‎src/DB/Data/LocalTableData.php‎

Lines changed: 106 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,106 @@
1+
<?php namespace DBDiff\DB\Data;
2+
3+
use DBDiff\Diff\InsertData;
4+
use DBDiff\Diff\UpdateData;
5+
use DBDiff\Diff\DeleteData;
6+
use DBDiff\Exceptions\DataException;
7+
use DBDiff\Logger;
8+
9+
10+
class LocalTableData {
11+
12+
function __construct($manager) {
13+
$this->manager = $manager;
14+
$this->source = $this->manager->getDB('source');
15+
$this->target = $this->manager->getDB('target');
16+
}
17+
18+
public function getDiff($table, $key) {
19+
Logger::info("Now calculating data diff for table `$table`");
20+
$diffSequence1 = $this->getOldNewDiff($table, $key);
21+
$diffSequence2 = $this->getChangeDiff($table, $key);
22+
23+
return $diffSequence;
24+
}
25+
26+
public function getOldNewDiff($table, $key) {
27+
$diffSequence = [];
28+
29+
$keyCols = implode(',', $key);
30+
$db1 = $this->source->getDatabaseName();
31+
$db2 = $this->target->getDatabaseName();
32+
$result = $this->source->select("SELECT * FROM (
33+
SELECT *,'source' AS _connection FROM {$db1}.{$table}
34+
UNION ALL
35+
SELECT *,'target' AS _connection FROM {$db2}.{$table}
36+
) tbl
37+
GROUP BY $keyCols
38+
HAVING count(*) = 1;");
39+
40+
foreach ($result as $row) {
41+
if ($row['_connection'] == 'source') {
42+
$diffSequence[] = new InsertData($table, [
43+
'keys' => array_only($row, $key),
44+
'diff' => new \Diff\DiffOp\DiffOpAdd(array_except($row, '_connection'))
45+
]);
46+
} else if ($row['_connection'] == 'target') {
47+
$diffSequence[] = new DeleteData($table, [
48+
'keys' => array_only($row, $key),
49+
'diff' => new \Diff\DiffOp\DiffOpRemove(array_except($row, '_connection'))
50+
]);
51+
}
52+
}
53+
return $diffSequence;
54+
}
55+
56+
public function getChangeDiff($table, $key) {
57+
$diffSequence = [];
58+
59+
$db1 = $this->source->getDatabaseName();
60+
$db2 = $this->target->getDatabaseName();
61+
62+
$columns1 = $this->manager->getColumns('source', $table);
63+
$columns2 = $this->manager->getColumns('target', $table);
64+
65+
$wrap = function($arr) {
66+
return array_map(function($el) {
67+
return "`$el`";
68+
}, $arr);
69+
};
70+
71+
$columns1 = implode(',', $wrap($columns1));
72+
$columns2 = implode(',', $wrap($columns2));
73+
74+
$keyCols = implode(' AND ', array_map(function($el) {
75+
return "t1.{$el} = t2.{$el}";
76+
}, $key));
77+
78+
$this->source->setFetchMode(\PDO::FETCH_NAMED);
79+
$result = $this->source->select("SELECT * FROM
80+
(SELECT $columns1, MD5(concat($columns1)) AS hash FROM {$db1}.{$table}) t1
81+
INNER JOIN
82+
(SELECT $columns2, MD5(concat($columns2)) AS hash FROM {$db2}.{$table}) t2
83+
ON $keyCols AND t1.hash != t2.hash;");
84+
$this->source->setFetchMode(\PDO::FETCH_ASSOC);
85+
86+
foreach ($result as $row) {
87+
$diff = [];
88+
$keys = [];
89+
$row = array_except($row, 'hash');
90+
foreach ($row as $k => $value) {
91+
if (in_array($k, $key)) {
92+
$keys[$k] = $value[1];
93+
}
94+
$diff[$k] = new \Diff\DiffOp\DiffOpChange($value[1], $value[0]);
95+
}
96+
$diffSequence[] = new UpdateData($table, [
97+
'keys' => $keys,
98+
'diff' => $diff
99+
]);
100+
}
101+
102+
return $diffSequence;
103+
}
104+
105+
106+
}

‎src/DB/Data/TableData.php‎

Lines changed: 25 additions & 46 deletions
Original file line numberDiff line numberDiff line change
@@ -13,47 +13,21 @@ function __construct($manager) {
1313
$this->manager = $manager;
1414
$this->source = $this->manager->getDB('source');
1515
$this->target = $this->manager->getDB('target');
16-
}
17-
18-
public function getKey($connection, $table) {
19-
$keys = $this->{$connection}->select("show indexes from $table");
20-
$ukey = [];
21-
foreach ($keys as $key) {
22-
if ($key['Key_name'] === 'PRIMARY') {
23-
$ukey[] = $key['Column_name'];
24-
}
25-
}
26-
return $ukey;
27-
}
28-
29-
public function checkKeys($table, $sourceKey, $targetKey) {
30-
if (empty($sourceKey) || empty($targetKey)) {
31-
throw new DataException("No primary key found in table `$table`");
32-
}
33-
if ($sourceKey != $targetKey) {
34-
throw new DataException("Unmatched primary keys in table `$table`");
35-
}
36-
return true;
16+
$this->distTableData = new DistTableData($manager);
17+
$this->localTableData = new LocalTableData($manager);
3718
}
3819

3920
public function getIterator($connection, $table) {
4021
return new TableIterator($this->{$connection}, $table);
4122
}
4223

43-
public function getDataDiff($key, $table) {
44-
$sourceIterator = $this->getIterator('source', $table);
45-
$targetIterator = $this->getIterator('target', $table);
46-
$differ = new ArrayDiff($key, $sourceIterator, $targetIterator);
47-
return $differ->getDiff();
48-
}
49-
5024
public function getNewData($table) {
5125
Logger::info("Now getting new data from table `$table`");
5226
$diffSequence = [];
5327
$iterator = $this->getIterator('source', $table);
54-
$key = $this->getKey('source', $table);
28+
$key = $this->manager->getKey('source', $table);
5529
while ($iterator->hasNext()) {
56-
$data = $iterator->next(ArrayDiff::SIZE);
30+
$data = $iterator->next(ArrayDiff::$size);
5731
foreach ($data as $entry) {
5832
$diffSequence[] = new InsertData($table, [
5933
'keys' => array_only($entry, $key),
@@ -68,9 +42,9 @@ public function getOldData($table) {
6842
Logger::info("Now getting old data from table `$table`");
6943
$diffSequence = [];
7044
$iterator = $this->getIterator('target', $table);
71-
$key = $this->getKey('target', $table);
45+
$key = $this->manager->getKey('target', $table);
7246
while ($iterator->hasNext()) {
73-
$data = $iterator->next(ArrayDiff::SIZE);
47+
$data = $iterator->next(ArrayDiff::$size);
7448
foreach ($data as $entry) {
7549
$diffSequence[] = new DeleteData($table, [
7650
'keys' => array_only($entry, $key),
@@ -82,22 +56,27 @@ public function getOldData($table) {
8256
}
8357

8458
public function getDiff($table) {
85-
Logger::info("Now calculating data diff for table `$table`");
86-
$sourceKey = $this->getKey('source', $table);
87-
$targetKey = $this->getKey('target', $table);
59+
$server1 = $this->source->getConfig('host').':'.$this->source->getConfig('port');
60+
$server2 = $this->target->getConfig('host').':'.$this->target->getConfig('port');
61+
$sourceKey = $this->manager->getKey('source', $table);
62+
$targetKey = $this->manager->getKey('target', $table);
8863
$this->checkKeys($table, $sourceKey, $targetKey);
89-
$diffs = $this->getDataDiff($sourceKey, $table);
90-
$diffSequence = [];
91-
foreach ($diffs as $name => $diff) {
92-
if ($diff['diff'] instanceof \Diff\DiffOp\DiffOpRemove) {
93-
$diffSequence[] = new DeleteData($table, $diff);
94-
} else if (is_array($diff['diff'])) {
95-
$diffSequence[] = new UpdateData($table, $diff);
96-
} else if ($diff['diff'] instanceof \Diff\DiffOp\DiffOpAdd) {
97-
$diffSequence[] = new InsertData($table, $diff);
98-
}
64+
65+
if ($server1 == $server2) {
66+
return $this->localTableData->getDiff($table, $sourceKey);
67+
} else {
68+
return $this->distTableData->getDiff($table, $sourceKey);
9969
}
100-
return $diffSequence;
70+
}
71+
72+
private function checkKeys($table, $sourceKey, $targetKey) {
73+
if (empty($sourceKey) || empty($targetKey)) {
74+
throw new DataException("No primary key found in table `$table`");
75+
}
76+
if ($sourceKey != $targetKey) {
77+
throw new DataException("Unmatched primary keys in table `$table`");
78+
}
79+
return true;
10180
}
10281

10382
}

‎src/DBDiff.php‎

Lines changed: 4 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -11,7 +11,10 @@
1111
class DBDiff {
1212

1313
public function run() {
14-
14+
15+
// Increase memory limit
16+
ini_set('memory_limit', '512M');
17+
1518
try {
1619
// Params
1720
$paramsFactory = new ParamsFactory;

0 commit comments

Comments
 (0)