RFC: Getting rid of getColumnMeta()

I even feel like I have something like this done, so I'll commit it.

https://github.com/…267c647ee549

Post scriptum: That contributor is proposing to remove ‘native_type’ in favor of ‘decl_type’ (to keep in mind in the case it will be approved in future)

I applied your last commit, I'm pretty satisfied with it (also PostgreSQL is happier now :-)

By the way PDO seems to apply a bit of normalization, in particular:

array(6) {
  ["int_fld"]=>
  int(10)
  ["varchar_fld"]=>
  string(3) "ABC"
  ["float_fld"]=>
  string(4) "3.14"
  ["decimal_fld"]=>
  string(6) "123.45"
  ["timestamp_fld"]=>
  string(19) "2021-04-30 00:00:00"
  ["boolean_fld"]=>
  bool(true)
}

In my opinion this is the best compromise:
Integers, strings and boolean are returned with their respective types. These are the hardest to convert without introspecting the database.
Decimals are returned as string and this is a good thing (here just for reference).
Floats are also returned as string, however I still have to see the sense of float in non-scientific computing.

The only one that require a conversion is the timestamp/datetime field.

At this point it would be very convenient to allow for custom normalization

Yeah, for PostgreSQL it behaves fine, for MySQL it unfortunately returns “int_fld” as string.

Yes, later I saw it. Maybe it could be uniformed for all drivers by using ATTR_STRINGIFY_FETCHES (or by adjusting tests for PostgreSQL since I find it very convenient).

By the way, I was playing a bit with MySQL and getColumnMeta(), in the case of arbitrary expressions (not table's fields) it's totally unrealiable (while PostgreSQL never get wrong, but at the expense of a doubling the cost of the query).

For example:

$sth = $pdo->prepare("SELECT DATE('2021-04-04 01:02:03')");

Returns correctly:

["native_type"]=>
string(4) "DATE"

While:

$sth = $pdo->prepare("SELECT DATE_ADD('2021-04-04', INTERVAL 31 DAY)");

Returns:

["native_type"]=>
string(6) "STRING"

More complex date expressions even returned double! Or real expressions returned longint (while giving a floating point result).
I don't know how it works internally for MySQL driver but this is seriously wrong.

Ignoring table's fields and playing a bit directly with values and adding:

	} else if (
		    \DateTime::createFromFormat('Y-m-d',        $value) !== false || // Date
		    \DateTime::createFromFormat('H:i:s',        $value) !== false || // Time
		    \DateTime::createFromFormat('Y-m-d H:i:s',  $value) !== false || // DateTime
		    \DateTime::createFromFormat('Y-m-d H:i:sP', $value) !== false    // DateTime + timezone
	) {
		    $normalizedRow[$key] = new DateTime($value);
	}

works very well and tests are successful.

Regarding table's fields, unfortunately I'm not able to find a nice way to inject Structure inside ResultSet, so I can directly check the column type. Do you have some idea?

Last edited by dsar (2021-05-05 11:28)

Great! :-)

But I still hope that we will find a solution to get rid of getColumnMeta() for the default case.

Nette Database is faster than other ORMs, but it can be even faster…

JiriSlischka wrote:

That means I can create my own “NoNormalizer” to prevent default normalization?

If you don't want to normalize, it is enough $connection->setRowNormalizer(null);

Keep in mind that depending on the driver there is a bit of normalization (but it seems that only MySQL outputs everything as string)

Nette Database is faster than other ORMs, but it can be even faster…

Yes, because it's not ORM.

I know it comes from NotORM, however I used the term in a very generic way