DatabaseActions::tableExists() - Code Metrics - Inspection of "WIP" - sunnysideup/silverstripe-database-share-clean-up - Measure and Improve Code Quality continuously with Scrutinizer

Passed

Push — master ( fcece8...566a2b )

by Nicolaas

created 2023-02-28 04:28 UTC

DatabaseActions::tableExists() A

↳ Parent: DatabaseActions

Complexity

Conditions	2
Paths	2

Size

Total Lines	3
Code Lines	1

Duplication

Lines	0
Ratio	0 %

Importance

Changes

Metric	Value
eloc	1
c	0
b	0
f	0
dl	0
loc	3
rs	10
cc	2
nc	2
nop	1

<?php

namespace Sunnysideup\DatabaseShareCleanUp\Api;

use SilverStripe\ORM\DB;
use Sunnysideup\Flush\FlushNow;
use Sunnysideup\Flush\FlushNowImplementor;

class DatabaseActions
{
    /**
     * @var string[]
     */
    protected const TEXT_FIELDS = [
        'varchar',
        'text',
        'mediumtext',
    ];

    /**
     * @var string[]
     */
    protected const DATE_FIELDS = [
        'date',
    ];

    protected $forReal = false;

    protected $debug = false;

    protected static $tableList = [];

    protected static $fieldsForTable = [];

    public function setForReal(bool $bool)
    {
        $this->forReal = $bool;
    }

    public function setDebug(bool $bool)
    {
        $this->debug = $bool;
    }

    public function emptyVersionedTable(string $tableName, ?bool $leaveLastVersion = false): bool
    {
        $specialCase = in_array($tableName, ['ChangeSet', 'ChangeSetItem', 'ChangeSetItem_ReferencedBy']);
        echo 'CCCC';
        if ('_Versions' === substr((string) $tableName, -9) || $specialCase) {
            echo 'AAA';
            $nonVersionedTable = substr((string) $tableName, 0, strlen( (string) $tableName) - 9);
            echo 'BBB';
            if ($this->hasTable($nonVersionedTable) ||  $specialCase) {
                $this->truncateTable($tableName);
                if ($leaveLastVersion) {
                    $fields = $this->getAllFieldsForOneTable($nonVersionedTable);
                    $fields = array_combine($fields, $fields);
                    foreach ($fields as $fieldName) {
                        if (!($this->hasField($tableName, $fieldName) && $this->hasField($nonVersionedTable, $fieldName))) {
                            unset($fields[$fieldName]);
                        }
                    }
                    $fields['ID'] = 'RecordID';
                    unset($fields['Version']);
                    $fields['VERSION_NUMBER_HERE'] = 'Version';
                    $sql = '
                        INSERT INTO "' . $tableName . '" ("' . implode('", "', $fields) . '")
                        SELECT "' . implode('", "', array_keys($fields)) . '" FROM "' . $nonVersionedTable . '";';
                    $sql = str_replace('"VERSION_NUMBER_HERE"', '1', $sql);
                    $this->debugFlush('Copying unversioned from ' . $nonVersionedTable . ' into ' . $tableName, 'info');
                    $this->executeSql($sql);
                }

                return true;
            }
            FlushNowImplementor::do_flush('ERROR: could not find: ' . $nonVersionedTable, 'bad');
        }

        return false;
    }

    public function deleteObsoleteTables(string $tableName): bool
    {
        if (0 === strpos($tableName, '_obsolete_')) {
            echo 'BBBB' . $tableName;
            $this->deleteTable($tableName);

            return true;
        }

        return false;
    }

    public function deleteTable(string $tableName)
    {
        $this->debugFlush('Deleting ' . $tableName . ' as it is not required', 'deleted');
        $sql = 'DROP TABLE "' . $tableName . '";';
        $this->executeSql($sql);
    }

    public function truncateTable(string $tableName)
    {
        $this->debugFlush('Emptying ' . $tableName, 'changed');
        $sql = 'TRUNCATE TABLE "' . $tableName . '"; ';
        $this->executeSql($sql);
    }

    public function truncateField(string $tableName, string $fieldName, ?int $limit = 99999999, ?bool $silent = false): bool
    {
        if ($this->isTextField($tableName, $fieldName) || $this->isDateField($tableName, $fieldName)) {
            if (false === $silent) {
                $this->debugFlush('Emptying ' . $tableName . '.' . $fieldName, 'obsolete');
            }
            $sortStatement = $this->getSortStatement($tableName);
            $sql = '
                UPDATE "' . $tableName . '"
                SET "' . $fieldName . '" = \'\'
                ' . $sortStatement . '
                LIMIT ' . $limit;
            $this->executeSql($sql);

            return true;
        }
        $this->debugFlush('Skipping emptying ' . $tableName . '.' . $fieldName . ' as this is not a text field', 'info');

        return false;
    }

    public function anonymiseField(string $tableName, string $fieldName): bool
    {
        if ($this->isTextField($tableName, $fieldName)) {
            $this->debugFlush('Anonymising ' . $tableName . '.' . $fieldName, 'repaired');
            // $sortStatement = $this->getSortStatement($tableName);
            $r = "SUBSTR('0123456789abcdefghihjlmnopqrstuvwxyz',(RAND()*35)+1,1)";
            $sql = '
                UPDATE "' . $tableName . '"
                SET "' . $fieldName . '" = CONCAT(' . $r . ', ' . $r . ', ' . $r . ", '@', " . $r . ', ' . $r . ", '.', " . $r . ')
                WHERE "' . $fieldName . '" IS NOT NULL AND "' . $fieldName . '" <> \'\'';
            $this->executeSql($sql);

            return true;
        }
        if ($this->isDateField($tableName, $fieldName)) {
            $this->debugFlush('Anonymising ' . $tableName . '.' . $fieldName, 'repaired');
            // $sortStatement = $this->getSortStatement($tableName);
            // randomise by three years
            $sql = '
                UPDATE "' . $tableName . '"
                SET "' . $fieldName . '" = DATE_ADD("' . $fieldName . '", INTERVAL ((1 - ROUND((RAND()))*2)*999) DAY)
                WHERE "' . $fieldName . '" IS NOT NULL';
            $this->executeSql($sql);

            return true;
        }
        $this->debugFlush('Skipping anonymising ' . $tableName . '.' . $fieldName . ' as this is not a text field', 'info');

        return false;
    }

    public function removeOldRowsFromTable(string $tableName, float $percentageToKeep)
    {
        $this->debugFlush('Deleting ' . (100 - round($percentageToKeep * 100, 2)) . '% of the Rows in ' . $tableName, 'obsolete');
        $limit = $this->turnPercentageIntoLimit($tableName, $percentageToKeep);
        $sortStatement = $this->getSortStatement($tableName);
        $sql = '
            DELETE FROM "' . $tableName . '"
            ' . $sortStatement . '
            LIMIT ' . $limit;
        $this->executeSql($sql);
    }

    public function removeOldColumnsFromTable(string $tableName, string $fieldName, float $percentageToKeep): bool
    {
        $this->debugFlush('Emptying ' . (100 - round($percentageToKeep * 100, 2)) . '% from ' . $tableName . '.' . $fieldName, 'obsolete');
        $limit = $this->turnPercentageIntoLimit($tableName, $percentageToKeep);

        return $this->truncateField($tableName, $fieldName, $limit, $silent = true);
    }

    public function getAllTables(?bool $fresh = true): array
    {
        if ($fresh || 0 === count(self::$tableList)) {
            self::$tableList = DB::table_list();
        }

        return self::$tableList;
    }

    public function getAllFieldsForOneTable(string $tableName): array
    {
        return array_keys($this->getAllFieldsForOneTableDetails($tableName));
    }

    public function getAllFieldsForOneTableDetails(string $tableName): array
    {
        if (!isset(self::$fieldsForTable[$tableName])) {
            self::$fieldsForTable[$tableName] = [];
            if ($this->hasTable($tableName)) {
                self::$fieldsForTable[$tableName] = DB::field_list($tableName);
            }
        }

        return self::$fieldsForTable[$tableName];
    }

    public function isEmptyTable(string $tableName): bool
    {
        if ($this->tableExists($tableName)) {
            return 0 === $this->countRows($tableName);
        }
        return true;
    }

    public function countRows(string $tableName): int
    {
        return (int) DB::query('SELECT COUNT(*) FROM "' . $tableName . '";')->value();
    }

    public function tableExists(string $tableName): bool
    {
        return DB::query('SHOW TABLES LIKE \'' . $tableName . '\';')->value() ? true : false;
    }

    public function getTableSizeInMegaBytes(string $tableName): float
    {
        return floatval(DB::query('
            SELECT  round(((data_length + index_length ) / 1024 / 1024), 2) as C
            FROM information_schema.TABLES
            WHERE
                table_schema = \'' . DB::get_conn()->getSelectedDatabase() . '\'
                AND table_name = \'' . $tableName . '\';
        ')->value());
    }

    public function getColumnSizeInMegabytes(string $tableName, string $fieldName): float
    {
        return floatval(DB::query('
            SELECT round(sum(char_length("' . $fieldName . '")) / 1024 / 1024)
            FROM "' . $tableName . '";
        ')->value());
    }

    protected function isTextField(string $tableName, string $fieldName): bool
    {
        return $this->isSomeTypeOfField($tableName, $fieldName, self::TEXT_FIELDS);
    }

    protected function isDateField(string $tableName, string $fieldName): bool
    {
        return $this->isSomeTypeOfField($tableName, $fieldName, self::DATE_FIELDS);
    }

    protected function isSomeTypeOfField(string $tableName, string $fieldName, array $typeStrings): bool
    {
        $details = $this->getAllFieldsForOneTableDetails($tableName);
        if (isset($details[$fieldName])) {
            foreach ($typeStrings as $test) {
                if (0 === stripos(strtolower($details[$fieldName]), $test)) {
                    return true;
                }
            }
        } else {
            FlushNowImplementor::do_flush('ERROR: could not find: ' . $tableName . '.' . $fieldName, 'bad');
        }

        return false;
    }

    protected function turnPercentageIntoLimit(string $tableName, float $percentageToKeep): int
    {
        $count = DB::query('SELECT COUNT("ID") FROM "' . $tableName . '"')->value();
        $count = intval($count);

        return (int) round($percentageToKeep * $count);
    }

    protected function executeSql(string $sql)
    {
        $this->debugFlush('Running <pre>' . $sql . '</pre>', 'info');
        if ($this->forReal) {
            DB::query($sql);
            $this->debugFlush(' ... done', 'green');
        } else {
            $this->debugFlush(' ... not exectuted!', 'info');
        }
    }

    protected function getSortStatement(string $tableName): string
    {
        if ($this->hasField($tableName, 'ID')) {
            return 'ORDER BY "ID" ASC';
        }

        return '';
    }

    protected function hasField(string $tableName, string $fieldName): bool
    {
        return (bool) DB::get_schema()->hasField($tableName, $fieldName);
    }

    protected function hasTable(string $tableName): bool
    {
        return (bool) DB::get_schema()->hasTable($tableName);
    }

    protected function debugFlush(string $message, string $type)
    {
        if ($this->debug) {
            FlushNowImplementor::do_flush($message, $type);
        }
    }
}


1			<?php
2
3			namespace Sunnysideup\DatabaseShareCleanUp\Api;
4
5			use SilverStripe\ORM\DB;
6			use Sunnysideup\Flush\FlushNow;
7			use Sunnysideup\Flush\FlushNowImplementor;
8
9			class DatabaseActions
10			{
11			/**
12			* @var string[]
13			*/
14			protected const TEXT_FIELDS = [
15			'varchar',
16			'text',
17			'mediumtext',
18			];
19
20			/**
21			* @var string[]
22			*/
23			protected const DATE_FIELDS = [
24			'date',
25			];
26
27			protected $forReal = false;
28
29			protected $debug = false;
30
31			protected static $tableList = [];
32
33			protected static $fieldsForTable = [];
34
35			public function setForReal(bool $bool)
36			{
37			$this->forReal = $bool;
38			}
39
40			public function setDebug(bool $bool)
41			{
42			$this->debug = $bool;
43			}
44
45			public function emptyVersionedTable(string $tableName, ?bool $leaveLastVersion = false): bool
46			{
47			$specialCase = in_array($tableName, ['ChangeSet', 'ChangeSetItem', 'ChangeSetItem_ReferencedBy']);
48			echo 'CCCC';
49			if ('_Versions' === substr((string) $tableName, -9) \|\| $specialCase) {
50			echo 'AAA';
51			$nonVersionedTable = substr((string) $tableName, 0, strlen( (string) $tableName) - 9);
52			echo 'BBB';
53			if ($this->hasTable($nonVersionedTable) \|\| $specialCase) {
54			$this->truncateTable($tableName);
55			if ($leaveLastVersion) {
56			$fields = $this->getAllFieldsForOneTable($nonVersionedTable);
57			$fields = array_combine($fields, $fields);
58			foreach ($fields as $fieldName) {
59			if (!($this->hasField($tableName, $fieldName) && $this->hasField($nonVersionedTable, $fieldName))) {
60			unset($fields[$fieldName]);
61			}
62			}
63			$fields['ID'] = 'RecordID';
64			unset($fields['Version']);
65			$fields['VERSION_NUMBER_HERE'] = 'Version';
66			$sql = '
67			INSERT INTO "' . $tableName . '" ("' . implode('", "', $fields) . '")
68			SELECT "' . implode('", "', array_keys($fields)) . '" FROM "' . $nonVersionedTable . '";';
69			$sql = str_replace('"VERSION_NUMBER_HERE"', '1', $sql);
70			$this->debugFlush('Copying unversioned from ' . $nonVersionedTable . ' into ' . $tableName, 'info');
71			$this->executeSql($sql);
72			}
73
74			return true;
75			}
76			FlushNowImplementor::do_flush('ERROR: could not find: ' . $nonVersionedTable, 'bad');
77			}
78
79			return false;
80			}
81
82			public function deleteObsoleteTables(string $tableName): bool
83			{
84			if (0 === strpos($tableName, '_obsolete_')) {
85			echo 'BBBB' . $tableName;
86			$this->deleteTable($tableName);
87
88			return true;
89			}
90
91			return false;
92			}
93
94			public function deleteTable(string $tableName)
95			{
96			$this->debugFlush('Deleting ' . $tableName . ' as it is not required', 'deleted');
97			$sql = 'DROP TABLE "' . $tableName . '";';
98			$this->executeSql($sql);
99			}
100
101			public function truncateTable(string $tableName)
102			{
103			$this->debugFlush('Emptying ' . $tableName, 'changed');
104			$sql = 'TRUNCATE TABLE "' . $tableName . '"; ';
105			$this->executeSql($sql);
106			}
107
108			public function truncateField(string $tableName, string $fieldName, ?int $limit = 99999999, ?bool $silent = false): bool
109			{
110			if ($this->isTextField($tableName, $fieldName) \|\| $this->isDateField($tableName, $fieldName)) {
111			if (false === $silent) {
112			$this->debugFlush('Emptying ' . $tableName . '.' . $fieldName, 'obsolete');
113			}
114			$sortStatement = $this->getSortStatement($tableName);
115			$sql = '
116			UPDATE "' . $tableName . '"
117			SET "' . $fieldName . '" = \'\'
118			' . $sortStatement . '
119			LIMIT ' . $limit;
120			$this->executeSql($sql);
121
122			return true;
123			}
124			$this->debugFlush('Skipping emptying ' . $tableName . '.' . $fieldName . ' as this is not a text field', 'info');
125
126			return false;
127			}
128
129			public function anonymiseField(string $tableName, string $fieldName): bool
130			{
131			if ($this->isTextField($tableName, $fieldName)) {
132			$this->debugFlush('Anonymising ' . $tableName . '.' . $fieldName, 'repaired');
133			// $sortStatement = $this->getSortStatement($tableName);
134			$r = "SUBSTR('0123456789abcdefghihjlmnopqrstuvwxyz',(RAND()*35)+1,1)";
135			$sql = '
136			UPDATE "' . $tableName . '"
137			SET "' . $fieldName . '" = CONCAT(' . $r . ', ' . $r . ', ' . $r . ", '@', " . $r . ', ' . $r . ", '.', " . $r . ')
138			WHERE "' . $fieldName . '" IS NOT NULL AND "' . $fieldName . '" <> \'\'';
139			$this->executeSql($sql);
140
141			return true;
142			}
143			if ($this->isDateField($tableName, $fieldName)) {
144			$this->debugFlush('Anonymising ' . $tableName . '.' . $fieldName, 'repaired');
145			// $sortStatement = $this->getSortStatement($tableName);
146			// randomise by three years
147			$sql = '
148			UPDATE "' . $tableName . '"
149			SET "' . $fieldName . '" = DATE_ADD("' . $fieldName . '", INTERVAL ((1 - ROUND((RAND()))2)999) DAY)
150			WHERE "' . $fieldName . '" IS NOT NULL';
151			$this->executeSql($sql);
152
153			return true;
154			}
155			$this->debugFlush('Skipping anonymising ' . $tableName . '.' . $fieldName . ' as this is not a text field', 'info');
156
157			return false;
158			}
159
160			public function removeOldRowsFromTable(string $tableName, float $percentageToKeep)
161			{
162			$this->debugFlush('Deleting ' . (100 - round($percentageToKeep * 100, 2)) . '% of the Rows in ' . $tableName, 'obsolete');
163			$limit = $this->turnPercentageIntoLimit($tableName, $percentageToKeep);
164			$sortStatement = $this->getSortStatement($tableName);
165			$sql = '
166			DELETE FROM "' . $tableName . '"
167			' . $sortStatement . '
168			LIMIT ' . $limit;
169			$this->executeSql($sql);
170			}
171
172			public function removeOldColumnsFromTable(string $tableName, string $fieldName, float $percentageToKeep): bool
173			{
174			$this->debugFlush('Emptying ' . (100 - round($percentageToKeep * 100, 2)) . '% from ' . $tableName . '.' . $fieldName, 'obsolete');
175			$limit = $this->turnPercentageIntoLimit($tableName, $percentageToKeep);
176
177			return $this->truncateField($tableName, $fieldName, $limit, $silent = true);
178			}
179
180			public function getAllTables(?bool $fresh = true): array
181			{
182			if ($fresh \|\| 0 === count(self::$tableList)) {
183			self::$tableList = DB::table_list();
184			}
185
186			return self::$tableList;
187			}
188
189			public function getAllFieldsForOneTable(string $tableName): array
190			{
191			return array_keys($this->getAllFieldsForOneTableDetails($tableName));
192			}
193
194			public function getAllFieldsForOneTableDetails(string $tableName): array
195			{
196			if (!isset(self::$fieldsForTable[$tableName])) {
197			self::$fieldsForTable[$tableName] = [];
198			if ($this->hasTable($tableName)) {
199			self::$fieldsForTable[$tableName] = DB::field_list($tableName);
200			}
201			}
202
203			return self::$fieldsForTable[$tableName];
204			}
205
206			public function isEmptyTable(string $tableName): bool
207			{
208			if ($this->tableExists($tableName)) {
209			return 0 === $this->countRows($tableName);
210			}
211			return true;
212			}
213
214			public function countRows(string $tableName): int
215			{
216			return (int) DB::query('SELECT COUNT(*) FROM "' . $tableName . '";')->value();
217			}
218
219			public function tableExists(string $tableName): bool
220			{
221			return DB::query('SHOW TABLES LIKE \'' . $tableName . '\';')->value() ? true : false;
222			}
223
224			public function getTableSizeInMegaBytes(string $tableName): float
225			{
226			return floatval(DB::query('
227			SELECT round(((data_length + index_length ) / 1024 / 1024), 2) as C
228			FROM information_schema.TABLES
229			WHERE
230			table_schema = \'' . DB::get_conn()->getSelectedDatabase() . '\'
231			AND table_name = \'' . $tableName . '\';
232			')->value());
233			}
234
235			public function getColumnSizeInMegabytes(string $tableName, string $fieldName): float
236			{
237			return floatval(DB::query('
238			SELECT round(sum(char_length("' . $fieldName . '")) / 1024 / 1024)
239			FROM "' . $tableName . '";
240			')->value());
241			}
242
243			protected function isTextField(string $tableName, string $fieldName): bool
244			{
245			return $this->isSomeTypeOfField($tableName, $fieldName, self::TEXT_FIELDS);
246			}
247
248			protected function isDateField(string $tableName, string $fieldName): bool
249			{
250			return $this->isSomeTypeOfField($tableName, $fieldName, self::DATE_FIELDS);
251			}
252
253			protected function isSomeTypeOfField(string $tableName, string $fieldName, array $typeStrings): bool
254			{
255			$details = $this->getAllFieldsForOneTableDetails($tableName);
256			if (isset($details[$fieldName])) {
257			foreach ($typeStrings as $test) {
258			if (0 === stripos(strtolower($details[$fieldName]), $test)) {
259			return true;
260			}
261			}
262			} else {
263			FlushNowImplementor::do_flush('ERROR: could not find: ' . $tableName . '.' . $fieldName, 'bad');
264			}
265
266			return false;
267			}
268
269			protected function turnPercentageIntoLimit(string $tableName, float $percentageToKeep): int
270			{
271			$count = DB::query('SELECT COUNT("ID") FROM "' . $tableName . '"')->value();
272			$count = intval($count);
273
274			return (int) round($percentageToKeep * $count);
275			}
276
277			protected function executeSql(string $sql)
278			{
279			$this->debugFlush('Running <pre>' . $sql . '</pre>', 'info');
280			if ($this->forReal) {
281			DB::query($sql);
282			$this->debugFlush(' ... done', 'green');
283			} else {
284			$this->debugFlush(' ... not exectuted!', 'info');
285			}
286			}
287
288			protected function getSortStatement(string $tableName): string
289			{
290			if ($this->hasField($tableName, 'ID')) {
291			return 'ORDER BY "ID" ASC';
292			}
293
294			return '';
295			}
296
297			protected function hasField(string $tableName, string $fieldName): bool
298			{
299			return (bool) DB::get_schema()->hasField($tableName, $fieldName);
300			}
301
302			protected function hasTable(string $tableName): bool
303			{
304			return (bool) DB::get_schema()->hasTable($tableName);
305			}
306
307			protected function debugFlush(string $message, string $type)
308			{
309			if ($this->debug) {
310			FlushNowImplementor::do_flush($message, $type);
311			}
312			}
313			}
314

sunnysideup / silverstripe-database-share-clean-up

Push — master ( fcece8...566a2b )

DatabaseActions::tableExists() A

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like