|
1
|
|
|
<?php |
|
2
|
|
|
|
|
3
|
|
|
namespace Wabel\Zoho\CRM\Copy; |
|
4
|
|
|
|
|
5
|
|
|
use Doctrine\DBAL\Connection; |
|
6
|
|
|
use Doctrine\DBAL\Schema\Table; |
|
7
|
|
|
use Psr\Log\LoggerInterface; |
|
8
|
|
|
|
|
9
|
|
|
/** |
|
10
|
|
|
* This class is in charge of tracking local files. |
|
11
|
|
|
* To do so, it can add a set of triggers that observe and track changes in tables. |
|
12
|
|
|
*/ |
|
13
|
|
|
class LocalChangesTracker |
|
14
|
|
|
{ |
|
15
|
|
|
/** |
|
16
|
|
|
* @var Connection |
|
17
|
|
|
*/ |
|
18
|
|
|
private $connection; |
|
19
|
|
|
|
|
20
|
|
|
/** |
|
21
|
|
|
* @var LoggerInterface |
|
22
|
|
|
*/ |
|
23
|
|
|
private $logger; |
|
24
|
|
|
|
|
25
|
|
|
/** |
|
26
|
|
|
* @param Connection $connection |
|
27
|
|
|
*/ |
|
28
|
|
|
public function __construct(Connection $connection, LoggerInterface $logger) |
|
29
|
|
|
{ |
|
30
|
|
|
$this->connection = $connection; |
|
31
|
|
|
$this->logger = $logger; |
|
32
|
|
|
} |
|
33
|
|
|
|
|
34
|
|
|
public function createTrackingTables() |
|
35
|
|
|
{ |
|
36
|
|
|
$schema = new \Doctrine\DBAL\Schema\Schema(); |
|
37
|
|
|
|
|
38
|
|
|
$localUpdate = $schema->createTable('local_update'); |
|
39
|
|
|
$localUpdate->addColumn('table_name', 'string', ['length' => 100]); |
|
40
|
|
|
$localUpdate->addColumn('uid', 'string', ['length' => 36]); |
|
41
|
|
|
$localUpdate->addColumn('field_name', 'string', ['length' => 100]); |
|
42
|
|
|
$localUpdate->setPrimaryKey(array('table_name', 'uid', 'field_name')); |
|
43
|
|
|
|
|
44
|
|
|
$localInsert = $schema->createTable('local_insert'); |
|
45
|
|
|
$localInsert->addColumn('table_name', 'string', ['length' => 100]); |
|
46
|
|
|
$localInsert->addColumn('uid', 'string', ['length' => 36]); |
|
47
|
|
|
$localInsert->setPrimaryKey(array('table_name', 'uid')); |
|
48
|
|
|
|
|
49
|
|
|
$localDelete = $schema->createTable('local_delete'); |
|
50
|
|
|
$localDelete->addColumn('table_name', 'string', ['length' => 100]); |
|
51
|
|
|
$localDelete->addColumn('uid', 'string', ['length' => 36]); |
|
52
|
|
|
$localDelete->addColumn('id', 'string', ['length' => 100,'notnull'=>false]); |
|
53
|
|
|
$localDelete->setPrimaryKey(array('table_name', 'uid')); |
|
54
|
|
|
$localDelete->addUniqueIndex(['id', 'table_name']); |
|
55
|
|
|
|
|
56
|
|
|
$dbalTableDiffService = new DbalTableDiffService($this->connection, $this->logger); |
|
57
|
|
|
$dbalTableDiffService->createOrUpdateTable($localUpdate); |
|
58
|
|
|
$dbalTableDiffService->createOrUpdateTable($localInsert); |
|
59
|
|
|
$dbalTableDiffService->createOrUpdateTable($localDelete); |
|
60
|
|
|
} |
|
61
|
|
|
|
|
62
|
|
|
public function createUuidInsertTrigger(Table $table) |
|
63
|
|
|
{ |
|
64
|
|
|
$triggerName = sprintf('TRG_%s_SETUUIDBEFOREINSERT', $table->getName()); |
|
65
|
|
|
|
|
66
|
|
|
//Fix - temporary MySQL 5.7 strict mode |
|
67
|
|
|
$sql = sprintf(' |
|
68
|
|
|
DROP TRIGGER IF EXISTS %s; |
|
69
|
|
|
|
|
70
|
|
|
CREATE TRIGGER %s BEFORE INSERT ON `%s` |
|
71
|
|
|
FOR EACH ROW |
|
72
|
|
|
IF new.uid IS NULL |
|
73
|
|
|
THEN |
|
74
|
|
|
SET @uuidmy = uuid(); |
|
75
|
|
|
SET new.uid = LOWER(CONCAT( |
|
76
|
|
|
SUBSTR(HEX(@uuidmy), 1, 8), \'-\', |
|
77
|
|
|
SUBSTR(HEX(@uuidmy), 9, 4), \'-\', |
|
78
|
|
|
SUBSTR(HEX(@uuidmy), 13, 4), \'-\', |
|
79
|
|
|
SUBSTR(HEX(@uuidmy), 17, 4), \'-\', |
|
80
|
|
|
SUBSTR(HEX(@uuidmy), 21) |
|
81
|
|
|
)); |
|
82
|
|
|
END IF; |
|
83
|
|
|
', $triggerName, $triggerName, $table->getName()); |
|
84
|
|
|
|
|
85
|
|
|
$this->connection->exec($sql); |
|
86
|
|
|
} |
|
87
|
|
|
|
|
88
|
|
View Code Duplication |
public function createInsertTrigger(Table $table) |
|
|
|
|
|
|
89
|
|
|
{ |
|
90
|
|
|
$triggerName = sprintf('TRG_%s_ONINSERT', $table->getName()); |
|
91
|
|
|
|
|
92
|
|
|
$sql = sprintf(' |
|
93
|
|
|
DROP TRIGGER IF EXISTS %s; |
|
94
|
|
|
|
|
95
|
|
|
CREATE TRIGGER %s AFTER INSERT ON `%s` |
|
96
|
|
|
FOR EACH ROW |
|
97
|
|
|
BEGIN |
|
98
|
|
|
IF (NEW.lastActivityTime IS NULL) THEN |
|
99
|
|
|
INSERT INTO local_insert VALUES (%s, NEW.uid); |
|
100
|
|
|
DELETE FROM local_delete WHERE table_name = %s AND uid = NEW.uid; |
|
101
|
|
|
DELETE FROM local_update WHERE table_name = %s AND uid = NEW.uid; |
|
102
|
|
|
END IF; |
|
103
|
|
|
END; |
|
104
|
|
|
|
|
105
|
|
|
', $triggerName, $triggerName, $table->getName(), $this->connection->quote($table->getName()), $this->connection->quote($table->getName()), $this->connection->quote($table->getName())); |
|
106
|
|
|
|
|
107
|
|
|
$this->connection->exec($sql); |
|
108
|
|
|
} |
|
109
|
|
|
|
|
110
|
|
View Code Duplication |
public function createDeleteTrigger(Table $table) |
|
|
|
|
|
|
111
|
|
|
{ |
|
112
|
|
|
$triggerName = sprintf('TRG_%s_ONDELETE', $table->getName()); |
|
113
|
|
|
|
|
114
|
|
|
$sql = sprintf(' |
|
115
|
|
|
DROP TRIGGER IF EXISTS %s; |
|
116
|
|
|
|
|
117
|
|
|
CREATE TRIGGER %s BEFORE DELETE ON `%s` |
|
118
|
|
|
FOR EACH ROW |
|
119
|
|
|
BEGIN |
|
120
|
|
|
IF (OLD.id IS NOT NULL) THEN |
|
121
|
|
|
INSERT INTO local_delete VALUES (%s, OLD.uid, OLD.id); |
|
122
|
|
|
END IF; |
|
123
|
|
|
DELETE FROM local_insert WHERE table_name = %s AND uid = OLD.uid; |
|
124
|
|
|
DELETE FROM local_update WHERE table_name = %s AND uid = OLD.uid; |
|
125
|
|
|
END; |
|
126
|
|
|
|
|
127
|
|
|
', $triggerName, $triggerName, $table->getName(), $this->connection->quote($table->getName()), $this->connection->quote($table->getName()), $this->connection->quote($table->getName())); |
|
128
|
|
|
|
|
129
|
|
|
$this->connection->exec($sql); |
|
130
|
|
|
} |
|
131
|
|
|
|
|
132
|
|
|
public function createUpdateTrigger(Table $table) |
|
133
|
|
|
{ |
|
134
|
|
|
$triggerName = sprintf('TRG_%s_ONUPDATE', $table->getName()); |
|
135
|
|
|
|
|
136
|
|
|
$innerCode = ''; |
|
137
|
|
|
|
|
138
|
|
|
foreach ($table->getColumns() as $column) { |
|
139
|
|
|
if (in_array($column->getName(), ['id', 'uid'])) { |
|
140
|
|
|
continue; |
|
141
|
|
|
} |
|
142
|
|
|
$columnName = $this->connection->quoteIdentifier($column->getName()); |
|
143
|
|
|
$innerCode .= sprintf(' |
|
144
|
|
|
IF NOT(NEW.%s <=> OLD.%s) THEN |
|
145
|
|
|
REPLACE INTO local_update VALUES (%s, NEW.uid, %s); |
|
146
|
|
|
END IF; |
|
147
|
|
|
', $columnName, $columnName, $this->connection->quote($table->getName()), $this->connection->quote($column->getName())); |
|
148
|
|
|
} |
|
149
|
|
|
|
|
150
|
|
|
$sql = sprintf(' |
|
151
|
|
|
DROP TRIGGER IF EXISTS %s; |
|
152
|
|
|
|
|
153
|
|
|
CREATE TRIGGER %s AFTER UPDATE ON `%s` |
|
154
|
|
|
FOR EACH ROW |
|
155
|
|
|
BEGIN |
|
156
|
|
|
IF (NEW.lastActivityTime <=> OLD.lastActivityTime) THEN |
|
157
|
|
|
%s |
|
158
|
|
|
END IF; |
|
159
|
|
|
END; |
|
160
|
|
|
|
|
161
|
|
|
', $triggerName, $triggerName, $table->getName(), $innerCode); |
|
162
|
|
|
|
|
163
|
|
|
$this->connection->exec($sql); |
|
164
|
|
|
} |
|
165
|
|
|
} |
|
166
|
|
|
|
Wabel /
zoho-crm-copy-db
Loading history...
Duplicated code is one of the most pungent code smells. If you need to duplicate the same code in three or more different places, we strongly encourage you to look into extracting the code into a single class or operation.
You can also find more detailed suggestions in the “Code” section of your repository.