DatabaseActions::countRows() - Code Metrics - sunnysideup/silverstripe-database-share-clean-up - Measure and Improve Code Quality continuously with Scrutinizer

DatabaseActions::countRows() A
last analyzed 2024-08-26 02:29 UTC

↳ Parent: DatabaseActions

Complexity

Conditions	1
Paths	1

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

sunnysideup / silverstripe-database-share-clean-up

DatabaseActions::countRows() A last analyzed 2024-08-26 02:29 UTC

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like

DatabaseActions::countRows() A
last analyzed 2024-08-26 02:29 UTC