Issues in OracleColumnMeta.php (master) - Issues in master - dvdoug/DB - Measure and Improve Code Quality continuously with Scrutinizer

Issues (38)

src/OracleColumnMeta.php (2 issues)

Labels

Severity

Minor 2

<?php

declare(strict_types=1);
/**
 * Database Access Layer.
 * @author Doug Wright
 */

namespace DVDoug\DB;

use function abs;
use function bccomp;
use Exception;
use function max;
use function sprintf;
use function strpos;

/**
 * Metadata about a database column.
 * @author Doug Wright
 */
class OracleColumnMeta implements ColumnMetaInterface
{
    use DDLGeneration;

    /**
     * Database connection.
     */
    protected DatabaseInterface $connection;

    /**
     * Database name.
     */
    protected string $database;

    /**
     * Table name.
     */
    protected string $table;

    /**
     * Column name.
     */
    protected string $name;

    /**
     * Column type.
     */
    protected string $type;

    /**
     * Column length.
     */
    protected int $length;

    /**
     * Column precision.
     */
    protected ?int $precision;

    /**
     * Column scale.
     */
    protected ?int $scale;

    /**
     * Column nullable?
     */
    protected bool $isNullable;

    /**
     * Column max value.
     */
    protected ?string $maxValue;

    /**
     * Column min value.
     */
    protected ?string $minValue;

    /**
     * Number of distinct values.
     */
    protected int $distinctValues;

    /**
     * Constructor.
     * @param DatabaseInterface $aConnection connection to database
     * @param string            $aDatabase   database/schema name
     * @param string            $aTable      table name
     * @param string            $aColumnName column name
     */
    public function __construct(DatabaseInterface $aConnection, $aDatabase, $aTable, $aColumnName)
    {
        $this->connection = $aConnection;
        $this->database = $aDatabase;
        $this->table = $aTable;
        $this->name = $aColumnName;

        /*
         * Basic metadata from the schema
         */
        $statement = $this->connection->prepare('SELECT OWNER AS TABLE_SCHEMA,
                                                      TABLE_NAME,
                                                      COLUMN_NAME,
                                                      DATA_TYPE,
                                                      DATA_LENGTH,
                                                      DATA_PRECISION,
                                                      DATA_SCALE,
                                                      NULLABLE,
                                                      CHAR_LENGTH
                                               FROM ALL_TAB_COLUMNS
                                               WHERE OWNER = :owner
                                                     AND TABLE_NAME = :table_name
                                                     AND COLUMN_NAME = :column_name');
        $statement->bindParamToValue(':owner', $this->database);
        $statement->bindParamToValue(':table_name', $this->table);
        $statement->bindParamToValue(':column_name', $this->name);
        $statement->execute();

        $meta = $statement->fetchAssoc(false);

        $this->type = $meta['DATA_TYPE'];
        $this->length = $meta['CHAR_LENGTH'] ?: $meta['DATA_LENGTH'];
        $this->precision = $meta['DATA_PRECISION'];
        $this->scale = $meta['DATA_SCALE'];
        $this->isNullable = ($meta['NULLABLE'] == 'Y');

        /*
         * Metadata from the data stored
         */
        try {
            $query = sprintf('SELECT COUNT(*) AS COUNT FROM (SELECT %s FROM %s.%s GROUP BY %s) distinctvalues',
                $this->connection->quoteIdentifier($this->name),
                $this->connection->quoteIdentifier($this->database),
                $this->connection->quoteIdentifier($this->table),
                $this->connection->quoteIdentifier($this->name));
            $this->distinctValues = $this->connection->query($query)->fetchAssoc(false)['COUNT'];

            $query = sprintf('SELECT MIN(%s) AS ROWMIN, MAX(%s) AS ROWMAX FROM %s.%s WHERE %s IS NOT NULL',
                $this->connection->quoteIdentifier($this->name),
                $this->connection->quoteIdentifier($this->name),
                $this->connection->quoteIdentifier($this->database),
                $this->connection->quoteIdentifier($this->table),
                $this->connection->quoteIdentifier($this->name));
            $data = $this->connection->query($query)->fetchAssoc(false);
            $this->maxValue = $data['ROWMAX'];
            $this->minValue = $data['ROWMIN'];
        } catch (Exception $e) { //LONG column has restrictions on querying, so just get total value count
            if (strpos($e->getMessage(), 'ORA-00997: illegal use of LONG datatype') !== false) {
                $query = sprintf('SELECT COUNT(*) AS COUNT FROM %s.%s WHERE %s IS NOT NULL',
                    $this->connection->quoteIdentifier($this->database),
                    $this->connection->quoteIdentifier($this->table),
                    $this->connection->quoteIdentifier($this->name));
                $this->distinctValues = $this->connection->query($query)->fetchAssoc(false)['COUNT'] ?: 1;
            }
        }
    }

    /**
     * Get column name.
     */
    public function getName(): string
    {
        return $this->name;
    }

    /**
     * Get column type as used by originating database.
     */
    public function getOriginalType(): string
    {
        return $this->type;
    }

    /**
     * Get column type as suitable for MySQL.
     *
     * @throws Exception
     */
    public function getMySQLType(): string
    {
        switch ($this->type) {
            case 'NUMBER':
                if ($this->scale == 0) {
                    if ($this->minValue >= 0) { //unsigned
                        if (bccomp($this->maxValue, '256') === -1) {
                            return 'TINYINT UNSIGNED';
                        } elseif (bccomp($this->maxValue, '65536') === -1) {
                            return 'SMALLINT UNSIGNED';
                        } elseif (bccomp($this->maxValue, '16777216') === -1) {
                            return 'MEDIUMINT UNSIGNED';
                        } elseif (bccomp($this->maxValue, '4294967296') === -1) {
                            return 'INT UNSIGNED';
                        } elseif (bccomp($this->maxValue, '18446744073709551616') === -1) {
                            return 'BIGINT UNSIGNED';
                        } else {
                            return 'NUMERIC';
                        }
                    } else { //signed
                        if (bccomp(max(abs($this->minValue), $this->maxValue), '128') === -1) {
                            return 'TINYINT';
                        } elseif (bccomp(max(abs($this->minValue), $this->maxValue), '32768') === -1) {
                            return 'SMALLINT';
                        } elseif (bccomp(max(abs($this->minValue), $this->maxValue), '8388608') === -1) {
                            return 'MEDIUMINT';
                        } elseif (bccomp(max(abs($this->minValue), $this->maxValue), '2147483648') === -1) {
                            return 'INT';
                        } elseif (bccomp(max(abs($this->minValue), $this->maxValue), '9223372036854775808') === -1) {
                            return 'BIGINT';
                        } else {
                            return 'DECIMAL';
                        }
                    }
                } else {
                    return 'DECIMAL';
                }
                break;

            case 'CHAR':
            case 'NCHAR':
                return 'CHAR';
                break;
switch ($x) {
    case 1:
        return 'foo';
        break; // This break is not necessary and can be left off.
}

            case 'VARCHAR':
            case 'VARCHAR2':
            case 'NVARCHAR':
            case 'NVARCHAR2':
                return 'VARCHAR';
                break;

            case 'TIMESTAMP':
            case 'TIMESTAMP WITH TIME ZONE':
            case 'TIMESTAMP WITH LOCAL TIME ZONE':
                if ($this->minValue >= '1970-01-01 00:00:01' && $this->maxValue <= '2038-01-19 03:14:07') {
                    return 'TIMESTAMP';
                } else {
                    return 'DATETIME';
                }

            // no break
            case 'DATE':
                /*
                 * Work out whether date or datetime
                 */
                $query = sprintf("SELECT COUNT(*) AS COUNT FROM %s.%s WHERE %s IS NOT NULL AND TO_CHAR(%s, 'SSSSS') > 0",
                    $this->connection->quoteIdentifier($this->database),
                    $this->connection->quoteIdentifier($this->table),
                    $this->connection->quoteIdentifier($this->name),
                    $this->connection->quoteIdentifier($this->name));
                $rows = $this->connection->query($query)->fetchAssoc(false);

                if ($rows['COUNT'] > 0) {
                    return 'DATETIME';
                } else {
                    return 'DATE';
                }
                break;

            case 'BINARY_FLOAT':
                return 'FLOAT';

            case 'BINARY_DOUBLE':
                return 'DOUBLE';

            case 'BLOB':
            case 'BFILE':
            case 'LONG RAW':
            case 'RAW':
                return 'LONGBLOB';
                break;

            case 'LONG':
            case 'CLOB':
            case 'NCLOB':
                return 'LONGTEXT';

            case 'ROWID':
                return 'LONGTEXT';

            default:
                throw new Exception("Unknown conversion for column type {$this->type}");
        }
    }

    /**
     * Get column type as suitable for Oracle.
     */
    public function getOracleType(): string
    {
        return $this->type;
    }

    /**
     * Get length of column.
     */
    public function getLength(): int
    {
        switch ($this->getOriginalType()) {
            case 'NUMBER':
            case 'CHAR':
            case 'NCHAR':
            case 'VARCHAR':
            case 'VARCHAR2':
            case 'NVARCHAR':
            case 'NVARCHAR2':
            case 'BINARY_FLOAT':
            case 'BINARY_DOUBLE':
                return $this->length;
                break;
switch ($x) {
    case 1:
        return 'foo';
        break; // This break is not necessary and can be left off.
}
            default:
                return 0;
        }
    }

    /**
     * Get column precision (number of digits).
     * @return int|null int for numeric columns, null for non-numeric
     */
    public function getPrecision(): ?int
    {
        return $this->precision;
    }

    /**
     * Get column scale (number of digits after decimal place).
     * @return int|null int for numeric columns, null for non-numeric
     */
    public function getScale(): ?int
    {
        return $this->scale;
    }

    /**
     * Get whether column is nullable.
     */
    public function isNullable(): bool
    {
        return $this->isNullable;
    }

    /**
     * Get max value.
     */
    public function getMaxValue(): ?string
    {
        return $this->maxValue;
    }

    /**
     * Get min value.
     */
    public function getMinValue(): ?string
    {
        return $this->minValue;
    }

    /**
     * The number of distinct values in this column.
     */
    public function getDistinctValueCount(): int
    {
        return $this->distinctValues;
    }
}


1			<?php
2
3			declare(strict_types=1);
4			/**
5			* Database Access Layer.
6			* @author Doug Wright
7			*/
8
9			namespace DVDoug\DB;
10
11			use function abs;
12			use function bccomp;
13			use Exception;
14			use function max;
15			use function sprintf;
16			use function strpos;
17
18			/**
19			* Metadata about a database column.
20			* @author Doug Wright
21			*/
22			class OracleColumnMeta implements ColumnMetaInterface
23			{
24			use DDLGeneration;
25
26			/**
27			* Database connection.
28			*/
29			protected DatabaseInterface $connection;
30
31			/**
32			* Database name.
33			*/
34			protected string $database;
35
36			/**
37			* Table name.
38			*/
39			protected string $table;
40
41			/**
42			* Column name.
43			*/
44			protected string $name;
45
46			/**
47			* Column type.
48			*/
49			protected string $type;
50
51			/**
52			* Column length.
53			*/
54			protected int $length;
55
56			/**
57			* Column precision.
58			*/
59			protected ?int $precision;
60
61			/**
62			* Column scale.
63			*/
64			protected ?int $scale;
65
66			/**
67			* Column nullable?
68			*/
69			protected bool $isNullable;
70
71			/**
72			* Column max value.
73			*/
74			protected ?string $maxValue;
75
76			/**
77			* Column min value.
78			*/
79			protected ?string $minValue;
80
81			/**
82			* Number of distinct values.
83			*/
84			protected int $distinctValues;
85
86			/**
87			* Constructor.
88			* @param DatabaseInterface $aConnection connection to database
89			* @param string $aDatabase database/schema name
90			* @param string $aTable table name
91			* @param string $aColumnName column name
92			*/
93			public function __construct(DatabaseInterface $aConnection, $aDatabase, $aTable, $aColumnName)
94			{
95			$this->connection = $aConnection;
96			$this->database = $aDatabase;
97			$this->table = $aTable;
98			$this->name = $aColumnName;
99
100			/*
101			* Basic metadata from the schema
102			*/
103			$statement = $this->connection->prepare('SELECT OWNER AS TABLE_SCHEMA,
104			TABLE_NAME,
105			COLUMN_NAME,
106			DATA_TYPE,
107			DATA_LENGTH,
108			DATA_PRECISION,
109			DATA_SCALE,
110			NULLABLE,
111			CHAR_LENGTH
112			FROM ALL_TAB_COLUMNS
113			WHERE OWNER = :owner
114			AND TABLE_NAME = :table_name
115			AND COLUMN_NAME = :column_name');
116			$statement->bindParamToValue(':owner', $this->database);
117			$statement->bindParamToValue(':table_name', $this->table);
118			$statement->bindParamToValue(':column_name', $this->name);
119			$statement->execute();
120
121			$meta = $statement->fetchAssoc(false);
122
123			$this->type = $meta['DATA_TYPE'];
124			$this->length = $meta['CHAR_LENGTH'] ?: $meta['DATA_LENGTH'];
125			$this->precision = $meta['DATA_PRECISION'];
126			$this->scale = $meta['DATA_SCALE'];
127			$this->isNullable = ($meta['NULLABLE'] == 'Y');
128
129			/*
130			* Metadata from the data stored
131			*/
132			try {
133			$query = sprintf('SELECT COUNT(*) AS COUNT FROM (SELECT %s FROM %s.%s GROUP BY %s) distinctvalues',
134			$this->connection->quoteIdentifier($this->name),
135			$this->connection->quoteIdentifier($this->database),
136			$this->connection->quoteIdentifier($this->table),
137			$this->connection->quoteIdentifier($this->name));
138			$this->distinctValues = $this->connection->query($query)->fetchAssoc(false)['COUNT'];
139
140			$query = sprintf('SELECT MIN(%s) AS ROWMIN, MAX(%s) AS ROWMAX FROM %s.%s WHERE %s IS NOT NULL',
141			$this->connection->quoteIdentifier($this->name),
142			$this->connection->quoteIdentifier($this->name),
143			$this->connection->quoteIdentifier($this->database),
144			$this->connection->quoteIdentifier($this->table),
145			$this->connection->quoteIdentifier($this->name));
146			$data = $this->connection->query($query)->fetchAssoc(false);
147			$this->maxValue = $data['ROWMAX'];
148			$this->minValue = $data['ROWMIN'];
149			} catch (Exception $e) { //LONG column has restrictions on querying, so just get total value count
150			if (strpos($e->getMessage(), 'ORA-00997: illegal use of LONG datatype') !== false) {
151			$query = sprintf('SELECT COUNT(*) AS COUNT FROM %s.%s WHERE %s IS NOT NULL',
152			$this->connection->quoteIdentifier($this->database),
153			$this->connection->quoteIdentifier($this->table),
154			$this->connection->quoteIdentifier($this->name));
155			$this->distinctValues = $this->connection->query($query)->fetchAssoc(false)['COUNT'] ?: 1;
156			}
157			}
158			}
159
160			/**
161			* Get column name.
162			*/
163			public function getName(): string
164			{
165			return $this->name;
166			}
167
168			/**
169			* Get column type as used by originating database.
170			*/
171			public function getOriginalType(): string
172			{
173			return $this->type;
174			}
175
176			/**
177			* Get column type as suitable for MySQL.
178			*
179			* @throws Exception
180			*/
181			public function getMySQLType(): string
182			{
183			switch ($this->type) {
184			case 'NUMBER':
185			if ($this->scale == 0) {
186			if ($this->minValue >= 0) { //unsigned
187			if (bccomp($this->maxValue, '256') === -1) {
188			return 'TINYINT UNSIGNED';
189			} elseif (bccomp($this->maxValue, '65536') === -1) {
190			return 'SMALLINT UNSIGNED';
191			} elseif (bccomp($this->maxValue, '16777216') === -1) {
192			return 'MEDIUMINT UNSIGNED';
193			} elseif (bccomp($this->maxValue, '4294967296') === -1) {
194			return 'INT UNSIGNED';
195			} elseif (bccomp($this->maxValue, '18446744073709551616') === -1) {
196			return 'BIGINT UNSIGNED';
197			} else {
198			return 'NUMERIC';
199			}
200			} else { //signed
201			if (bccomp(max(abs($this->minValue), $this->maxValue), '128') === -1) {
202			return 'TINYINT';
203			} elseif (bccomp(max(abs($this->minValue), $this->maxValue), '32768') === -1) {
204			return 'SMALLINT';
205			} elseif (bccomp(max(abs($this->minValue), $this->maxValue), '8388608') === -1) {
206			return 'MEDIUMINT';
207			} elseif (bccomp(max(abs($this->minValue), $this->maxValue), '2147483648') === -1) {
208			return 'INT';
209			} elseif (bccomp(max(abs($this->minValue), $this->maxValue), '9223372036854775808') === -1) {
210			return 'BIGINT';
211			} else {
212			return 'DECIMAL';
213			}
214			}
215			} else {
216			return 'DECIMAL';
217			}
218			break;
219
220			case 'CHAR':
221			case 'NCHAR':
222			return 'CHAR';
223			break;
			0 ignored issues – show Unused Code introduced 2020-01-12 18:45 UTC by Report Bug Copy Issue Report Show Similar Issues like this `break` is not strictly necessary here and could be removed. The `break` statement is not necessary if it is preceded for example by a `return` statement: switch ($x) { case 1: return 'foo'; break; // This break is not necessary and can be left off. } If you would like to keep this construct to be consistent with other `case` statements, you can safely mark this issue as a false-positive. Loading history...
224
225			case 'VARCHAR':
226			case 'VARCHAR2':
227			case 'NVARCHAR':
228			case 'NVARCHAR2':
229			return 'VARCHAR';
230			break;
231
232			case 'TIMESTAMP':
233			case 'TIMESTAMP WITH TIME ZONE':
234			case 'TIMESTAMP WITH LOCAL TIME ZONE':
235			if ($this->minValue >= '1970-01-01 00:00:01' && $this->maxValue <= '2038-01-19 03:14:07') {
236			return 'TIMESTAMP';
237			} else {
238			return 'DATETIME';
239			}
240
241			// no break
242			case 'DATE':
243			/*
244			* Work out whether date or datetime
245			*/
246			$query = sprintf("SELECT COUNT(*) AS COUNT FROM %s.%s WHERE %s IS NOT NULL AND TO_CHAR(%s, 'SSSSS') > 0",
247			$this->connection->quoteIdentifier($this->database),
248			$this->connection->quoteIdentifier($this->table),
249			$this->connection->quoteIdentifier($this->name),
250			$this->connection->quoteIdentifier($this->name));
251			$rows = $this->connection->query($query)->fetchAssoc(false);
252
253			if ($rows['COUNT'] > 0) {
254			return 'DATETIME';
255			} else {
256			return 'DATE';
257			}
258			break;
259
260			case 'BINARY_FLOAT':
261			return 'FLOAT';
262
263			case 'BINARY_DOUBLE':
264			return 'DOUBLE';
265
266			case 'BLOB':
267			case 'BFILE':
268			case 'LONG RAW':
269			case 'RAW':
270			return 'LONGBLOB';
271			break;
272
273			case 'LONG':
274			case 'CLOB':
275			case 'NCLOB':
276			return 'LONGTEXT';
277
278			case 'ROWID':
279			return 'LONGTEXT';
280
281			default:
282			throw new Exception("Unknown conversion for column type {$this->type}");
283			}
284			}
285
286			/**
287			* Get column type as suitable for Oracle.
288			*/
289			public function getOracleType(): string
290			{
291			return $this->type;
292			}
293
294			/**
295			* Get length of column.
296			*/
297			public function getLength(): int
298			{
299			switch ($this->getOriginalType()) {
300			case 'NUMBER':
301			case 'CHAR':
302			case 'NCHAR':
303			case 'VARCHAR':
304			case 'VARCHAR2':
305			case 'NVARCHAR':
306			case 'NVARCHAR2':
307			case 'BINARY_FLOAT':
308			case 'BINARY_DOUBLE':
309			return $this->length;
310			break;
			0 ignored issues – show Unused Code introduced 2020-01-12 18:45 UTC by Report Bug Copy Issue Report Show Similar Issues like this `break` is not strictly necessary here and could be removed. The `break` statement is not necessary if it is preceded for example by a `return` statement: switch ($x) { case 1: return 'foo'; break; // This break is not necessary and can be left off. } If you would like to keep this construct to be consistent with other `case` statements, you can safely mark this issue as a false-positive. Loading history...
311			default:
312			return 0;
313			}
314			}
315
316			/**
317			* Get column precision (number of digits).
318			* @return int\|null int for numeric columns, null for non-numeric
319			*/
320			public function getPrecision(): ?int
321			{
322			return $this->precision;
323			}
324
325			/**
326			* Get column scale (number of digits after decimal place).
327			* @return int\|null int for numeric columns, null for non-numeric
328			*/
329			public function getScale(): ?int
330			{
331			return $this->scale;
332			}
333
334			/**
335			* Get whether column is nullable.
336			*/
337			public function isNullable(): bool
338			{
339			return $this->isNullable;
340			}
341
342			/**
343			* Get max value.
344			*/
345			public function getMaxValue(): ?string
346			{
347			return $this->maxValue;
348			}
349
350			/**
351			* Get min value.
352			*/
353			public function getMinValue(): ?string
354			{
355			return $this->minValue;
356			}
357
358			/**
359			* The number of distinct values in this column.
360			*/
361			public function getDistinctValueCount(): int
362			{
363			return $this->distinctValues;
364			}
365			}
366

dvdoug / DB

Issues (38)

src/OracleColumnMeta.php (2 issues)

Labels

Severity

Introduced By

Duplication Side-by-Side

Filter issues like