DatabaseActions::isSomeTypeOfField() - Code Metrics - Inspection of "MINOR: support for date fields" - sunnysideup/silverstripe-database-share-clean-up - Measure and Improve Code Quality continuously with Scrutinizer

Passed

Push — master ( c99bae...666e95 )

by Nicolaas

created 2022-11-23 23:31 UTC

DatabaseActions::isSomeTypeOfField() A

↳ Parent: DatabaseActions

Complexity

Conditions	4
Paths	3

Size

Total Lines	13
Code Lines	8

Duplication

Lines	0
Ratio	0 %

Importance

Changes	1
Bugs	0	Features	0

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

<?php

namespace Sunnysideup\DatabaseShareCleanUp\Api;

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

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
    {
        if ('_Versions' === substr($tableName, -9)) {
            $nonVersionedTable = substr($tableName, 0, strlen($tableName) - 9);
            if ($this->hasTable($nonVersionedTable)) {
                $this->truncateTable($tableName);
                $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;
            }
            FlushNow::do_flush('ERROR: could not find: ' . $nonVersionedTable, 'bad');
        }

        return false;
    }

    public function deleteObsoleteTables(string $tableName): bool
    {
        if (0 === strpos($tableName, '_obsolete_')) {
            $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;
        } elseif ($this->isDateField($tableName, $fieldName)) {
            $this->debugFlush('Anonymising ' . $tableName . '.' . $fieldName, 'repaired');
            // $sortStatement = $this->getSortStatement($tableName);
            $date = "01-01-1974";
            $sql = '
                UPDATE "' . $tableName . '"
                SET "' . $fieldName . '" = \''.$date.'\'
                WHERE "' . $fieldName . '" IS NOT NULL AND "' . $fieldName . '" <> \'\'';
            $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
    {
        return 0 === $this->countRows($tableName);
    }

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

    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 {
            FlushNow::do_flush('ERROR: could not find: ' . $tableName . '.' . $fieldName, 'bad');
        }
        return false;
    }

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

        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) {
            FlushNow::do_flush($message, $type);
        }
    }
}


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

sunnysideup / silverstripe-database-share-clean-up

Push — master ( c99bae...666e95 )

DatabaseActions::isSomeTypeOfField() A

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like