LocalChangesTracker::createUpdateTrigger() - Code Metrics - Inspection of "Change varchar 255 into text to avoid MySQL limit..." - Wabel/zoho-crm-copy-db - Measure and Improve Code Quality continuously with Scrutinizer

Completed

Push — 3.1.1 ( a3e855...47adc0 )

unknown

created 2019-07-31 13:01 UTC

LocalChangesTracker::createUpdateTrigger() A

↳ Parent: LocalChangesTracker

Complexity

Conditions	3
Paths	3

Size

Total Lines

Duplication

Lines	0
Ratio	0 %

Importance

Changes

Metric	Value
dl	0
loc	40
rs	9.28
c	0
b	0
f	0
cc	3
nc	3
nop	1

<?php

namespace Wabel\Zoho\CRM\Copy;

use Doctrine\DBAL\Connection;
use Doctrine\DBAL\Schema\Table;
use Psr\Log\LoggerInterface;

/**
 * This class is in charge of tracking local files.
 * To do so, it can add a set of triggers that observe and track changes in tables.
 */
class LocalChangesTracker
{
    /**
     * @var Connection
     */
    private $connection;

    /**
     * @var LoggerInterface
     */
    private $logger;

    /**
     * @param Connection $connection
     */
    public function __construct(Connection $connection, LoggerInterface $logger)
    {
        $this->connection = $connection;
        $this->logger = $logger;
    }

    public function createTrackingTables()
    {
        $schema = new \Doctrine\DBAL\Schema\Schema();

        $localUpdate = $schema->createTable('local_update');
        $localUpdate->addColumn('table_name', 'string', ['length' => 100]);
        $localUpdate->addColumn('uid', 'string', ['length' => 36]);
        $localUpdate->addColumn('field_name', 'string', ['length' => 100]);
        $localUpdate->setPrimaryKey(array('table_name', 'uid', 'field_name'));

        $localInsert = $schema->createTable('local_insert');
        $localInsert->addColumn('table_name', 'string', ['length' => 100]);
        $localInsert->addColumn('uid', 'string', ['length' => 36]);
        $localInsert->setPrimaryKey(array('table_name', 'uid'));

        $localDelete = $schema->createTable('local_delete');
        $localDelete->addColumn('table_name', 'string', ['length' => 100]);
        $localDelete->addColumn('uid', 'string', ['length' => 36]);
        $localDelete->addColumn('id', 'string', ['length' => 100,'notnull'=>false]);
        $localDelete->setPrimaryKey(array('table_name', 'uid'));
        $localDelete->addUniqueIndex(['id', 'table_name']);

        $dbalTableDiffService = new DbalTableDiffService($this->connection, $this->logger);
        $dbalTableDiffService->createOrUpdateTable($localUpdate);
        $dbalTableDiffService->createOrUpdateTable($localInsert);
        $dbalTableDiffService->createOrUpdateTable($localDelete);
    }

    public function hasTriggersInsertUpdateDelete(Table $table)
    {
        $triggerInsertName = sprintf('TRG_%s_ONINSERT', $table->getName());
        $triggerUpdateName = sprintf('TRG_%s_ONUPDATE', $table->getName());
        $triggerDeleteName = sprintf('TRG_%s_ONDELETE', $table->getName());
        $nbTriggers = 0;
        $triggers = $this->connection->fetchAll("SHOW TRIGGERS LIKE '{$table->getName()}'");
        foreach ($triggers as $trigger) {
            if (in_array($trigger['Trigger'], [$triggerInsertName, $triggerUpdateName, $triggerDeleteName], true)) {
                $nbTriggers++;
            }
        }
        return $nbTriggers === 3;
    }

    public function hasTriggerInsertUuid(Table $table)
    {
        $triggerInsertName = sprintf('TRG_%s_SETUUIDBEFOREINSERT', $table->getName());
        $triggers = $this->connection->fetchAll("SHOW TRIGGERS LIKE '{$table->getName()}'");
        foreach ($triggers as $trigger) {
            if ($trigger['Trigger'] === $triggerInsertName) {
                return true;
            }
        }
        return false;
    }

    public function createUuidInsertTrigger(Table $table)
    {
        $triggerName = sprintf('TRG_%s_SETUUIDBEFOREINSERT', $table->getName());
        $this->logger->info('Creating ' . $triggerName . ' trigger for table ' . $table->getName() . '...');

        //Fix - temporary MySQL 5.7 strict mode
        $sql = sprintf(
            '
            DROP TRIGGER IF EXISTS %s;
            
            CREATE TRIGGER %s BEFORE INSERT ON `%s` 
            FOR EACH ROW
            IF new.uid IS NULL
              THEN
              	SET @uuidmy = uuid();
                SET new.uid = LOWER(CONCAT(
                SUBSTR(@uuidmy, 1, 8), \'-\',
                SUBSTR(@uuidmy, 10, 4), \'-\',
                SUBSTR(@uuidmy, 15, 4), \'-\',
                SUBSTR(@uuidmy, 20, 4), \'-\',
                SUBSTR(@uuidmy, 25)
              ));
              END IF;
            ', $triggerName, $triggerName, $table->getName()
        );

        $this->connection->exec($sql);
    }

    public function createInsertTrigger(Table $table)

    {
        $triggerName = sprintf('TRG_%s_ONINSERT', $table->getName());
        $this->logger->info('Creating ' . $triggerName . ' trigger for table ' . $table->getName() . '...');

        $tableNameQuoted = $this->connection->quote($table->getName());

        $sql = sprintf(
            '
            DROP TRIGGER IF EXISTS %s;
            
            CREATE TRIGGER %s AFTER INSERT ON `%s` 
            FOR EACH ROW
            BEGIN
              IF (NEW.id IS NULL AND NEW.createdTime IS NULL ) THEN
                INSERT INTO local_insert VALUES (%s, NEW.uid);
                DELETE FROM local_delete WHERE table_name = %s AND uid = NEW.uid;
                DELETE FROM local_update WHERE table_name = %s AND uid = NEW.uid;
              END IF;
            END;
            
            ', $triggerName, $triggerName, $table->getName(), $tableNameQuoted, $tableNameQuoted, $tableNameQuoted
        );

        $this->connection->exec($sql);
    }

    public function createDeleteTrigger(Table $table)

    {
        $triggerName = sprintf('TRG_%s_ONDELETE', $table->getName());
        $this->logger->info('Creating ' . $triggerName . ' trigger for table ' . $table->getName() . '...');

        $tableNameQuoted = $this->connection->quote($table->getName());

        $sql = sprintf(
            '
            DROP TRIGGER IF EXISTS %s;
            
            CREATE TRIGGER %s BEFORE DELETE ON `%s` 
            FOR EACH ROW
            BEGIN
              IF (OLD.id IS NOT NULL) THEN
                INSERT INTO local_delete VALUES (%s, OLD.uid, OLD.id);
              END IF;
              DELETE FROM local_insert WHERE table_name = %s AND uid = OLD.uid;
              DELETE FROM local_update WHERE table_name = %s AND uid = OLD.uid;
            END;
            
            ', $triggerName, $triggerName, $table->getName(), $tableNameQuoted, $tableNameQuoted, $tableNameQuoted
        );

        $this->connection->exec($sql);
    }

    public function createUpdateTrigger(Table $table)
    {
        $triggerName = sprintf('TRG_%s_ONUPDATE', $table->getName());
        $this->logger->info('Creating ' . $triggerName . ' trigger for table ' . $table->getName() . '...');

        $innerCode = '';

        $tableNameQuoted = $this->connection->quote($table->getName());

        foreach ($table->getColumns() as $column) {
            if (in_array($column->getName(), ['id', 'uid'])) {
                continue;
            }
            $columnName = $this->connection->quoteIdentifier($column->getName());
            $innerCode .= sprintf(
                '
                IF NOT(NEW.%s <=> OLD.%s) THEN
                  REPLACE INTO local_update VALUES (%s, NEW.uid, %s);
                END IF;
            ', $columnName, $columnName, $tableNameQuoted, $this->connection->quote($column->getName())
            );
        }

        $sql = sprintf(
            '
            DROP TRIGGER IF EXISTS %s;
            
            CREATE TRIGGER %s AFTER UPDATE ON `%s` 
            FOR EACH ROW
            BEGIN
              IF (NEW.modifiedTime <=> OLD.modifiedTime) THEN
            %s
              END IF;
            END;
            
            ', $triggerName, $triggerName, $table->getName(), $innerCode
        );

        $this->connection->exec($sql);
    }
}


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 hasTriggersInsertUpdateDelete(Table $table)
63		{
64		$triggerInsertName = sprintf('TRG_%s_ONINSERT', $table->getName());
65		$triggerUpdateName = sprintf('TRG_%s_ONUPDATE', $table->getName());
66		$triggerDeleteName = sprintf('TRG_%s_ONDELETE', $table->getName());
67		$nbTriggers = 0;
68		$triggers = $this->connection->fetchAll("SHOW TRIGGERS LIKE '{$table->getName()}'");
69		foreach ($triggers as $trigger) {
70		if (in_array($trigger['Trigger'], [$triggerInsertName, $triggerUpdateName, $triggerDeleteName], true)) {
71		$nbTriggers++;
72		}
73		}
74		return $nbTriggers === 3;
75		}
76
77		public function hasTriggerInsertUuid(Table $table)
78		{
79		$triggerInsertName = sprintf('TRG_%s_SETUUIDBEFOREINSERT', $table->getName());
80		$triggers = $this->connection->fetchAll("SHOW TRIGGERS LIKE '{$table->getName()}'");
81		foreach ($triggers as $trigger) {
82		if ($trigger['Trigger'] === $triggerInsertName) {
83		return true;
84		}
85		}
86		return false;
87		}
88
89		public function createUuidInsertTrigger(Table $table)
90		{
91		$triggerName = sprintf('TRG_%s_SETUUIDBEFOREINSERT', $table->getName());
92		$this->logger->info('Creating ' . $triggerName . ' trigger for table ' . $table->getName() . '...');
93
94		//Fix - temporary MySQL 5.7 strict mode
95		$sql = sprintf(
96		'
97		DROP TRIGGER IF EXISTS %s;
98
99		CREATE TRIGGER %s BEFORE INSERT ON `%s`
100		FOR EACH ROW
101		IF new.uid IS NULL
102		THEN
103		SET @uuidmy = uuid();
104		SET new.uid = LOWER(CONCAT(
105		SUBSTR(@uuidmy, 1, 8), \'-\',
106		SUBSTR(@uuidmy, 10, 4), \'-\',
107		SUBSTR(@uuidmy, 15, 4), \'-\',
108		SUBSTR(@uuidmy, 20, 4), \'-\',
109		SUBSTR(@uuidmy, 25)
110		));
111		END IF;
112		', $triggerName, $triggerName, $table->getName()
113		);
114
115		$this->connection->exec($sql);
116		}
117
118	View Code Duplication	public function createInsertTrigger(Table $table)
		0 ignored issues – show Duplication introduced 2017-12-04 17:38 UTC by Report Bug Copy Issue Report This method seems to be duplicated in your project. 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. Loading history...
119		{
120		$triggerName = sprintf('TRG_%s_ONINSERT', $table->getName());
121		$this->logger->info('Creating ' . $triggerName . ' trigger for table ' . $table->getName() . '...');
122
123		$tableNameQuoted = $this->connection->quote($table->getName());
124
125		$sql = sprintf(
126		'
127		DROP TRIGGER IF EXISTS %s;
128
129		CREATE TRIGGER %s AFTER INSERT ON `%s`
130		FOR EACH ROW
131		BEGIN
132		IF (NEW.id IS NULL AND NEW.createdTime IS NULL ) THEN
133		INSERT INTO local_insert VALUES (%s, NEW.uid);
134		DELETE FROM local_delete WHERE table_name = %s AND uid = NEW.uid;
135		DELETE FROM local_update WHERE table_name = %s AND uid = NEW.uid;
136		END IF;
137		END;
138
139		', $triggerName, $triggerName, $table->getName(), $tableNameQuoted, $tableNameQuoted, $tableNameQuoted
140		);
141
142		$this->connection->exec($sql);
143		}
144
145	View Code Duplication	public function createDeleteTrigger(Table $table)
		0 ignored issues – show Duplication introduced 2017-12-04 17:38 UTC by Report Bug Copy Issue Report This method seems to be duplicated in your project. 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. Loading history...
146		{
147		$triggerName = sprintf('TRG_%s_ONDELETE', $table->getName());
148		$this->logger->info('Creating ' . $triggerName . ' trigger for table ' . $table->getName() . '...');
149
150		$tableNameQuoted = $this->connection->quote($table->getName());
151
152		$sql = sprintf(
153		'
154		DROP TRIGGER IF EXISTS %s;
155
156		CREATE TRIGGER %s BEFORE DELETE ON `%s`
157		FOR EACH ROW
158		BEGIN
159		IF (OLD.id IS NOT NULL) THEN
160		INSERT INTO local_delete VALUES (%s, OLD.uid, OLD.id);
161		END IF;
162		DELETE FROM local_insert WHERE table_name = %s AND uid = OLD.uid;
163		DELETE FROM local_update WHERE table_name = %s AND uid = OLD.uid;
164		END;
165
166		', $triggerName, $triggerName, $table->getName(), $tableNameQuoted, $tableNameQuoted, $tableNameQuoted
167		);
168
169		$this->connection->exec($sql);
170		}
171
172		public function createUpdateTrigger(Table $table)
173		{
174		$triggerName = sprintf('TRG_%s_ONUPDATE', $table->getName());
175		$this->logger->info('Creating ' . $triggerName . ' trigger for table ' . $table->getName() . '...');
176
177		$innerCode = '';
178
179		$tableNameQuoted = $this->connection->quote($table->getName());
180
181		foreach ($table->getColumns() as $column) {
182		if (in_array($column->getName(), ['id', 'uid'])) {
183		continue;
184		}
185		$columnName = $this->connection->quoteIdentifier($column->getName());
186		$innerCode .= sprintf(
187		'
188		IF NOT(NEW.%s <=> OLD.%s) THEN
189		REPLACE INTO local_update VALUES (%s, NEW.uid, %s);
190		END IF;
191		', $columnName, $columnName, $tableNameQuoted, $this->connection->quote($column->getName())
192		);
193		}
194
195		$sql = sprintf(
196		'
197		DROP TRIGGER IF EXISTS %s;
198
199		CREATE TRIGGER %s AFTER UPDATE ON `%s`
200		FOR EACH ROW
201		BEGIN
202		IF (NEW.modifiedTime <=> OLD.modifiedTime) THEN
203		%s
204		END IF;
205		END;
206
207		', $triggerName, $triggerName, $table->getName(), $innerCode
208		);
209
210		$this->connection->exec($sql);
211		}
212		}
213

Wabel / zoho-crm-copy-db

Push — 3.1.1 ( a3e855...47adc0 )

LocalChangesTracker::createUpdateTrigger() A

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like