Re: RDBMS comparison tool

-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160


TL;DR: it's a rat's nest, with both solid reasoning and wild guesses.

Lyle wrote:
> I'm sure there are more things that look a bit odd or out of place. I'm 
> not sure whether there is good reasoning behind them, or whether the DBD 
> developers have been doing a best guess and we might possibly want to 
> consider making things more consistent?

Certainly a best guess for most of the ones in Postgres aka DBD::Pg. 
Patches, corrections, suggestions, complaints certainly welcome. 
It's a fairly unloved bit of the code.

> PostgreSQL BYTEA that's currently in SQL_VARBINARY, would seem a better 
> SQL_VARBINARY...

Yeah, one has to think more in terms of the opposite mapping - if someone 
asks for SQL_VARBINARY, what do we give them? Technically, BYTEA maps 
to both, as Postgres has no (explicit) length limitation. When in doubt, 
we use the more "standard" and/or "common" one. In this case, no LONG.

> Similar situation for PostgreSQL character.
Yep.
> Reviewing the PostgreSQL documentation on CHARACTER it mentions things 
> like short, long and very long character strings, but lacks detail so 
> I've emailed them about it.

Those are internal details about how they are stored and won't affect 
anything as far as an application is concerned.

> MySQL's FLOAT and DOUBLE are linked to several ODBC types, perhaps 
> PostgreSQL could do the same? Or is that bad practice on the 
> MySQL drivers part?

Hard to say, can you be more specific? Keeping in mind that I don't 
actually know a whole lot about the SQL/ODBC specs, and the differences 
therein. :)

> For SQL_DECIMAL PostgreSQL and MySQL seem to disagree on whether FLOAT 
> or DOUBLE should be used.

Well, a postgres float with no precision is really the same as a 
double precision (as you hint at below). The whole thing is quite 
confusing when you start pulling in ODBC, SQL standard, IEEE, decimal 
vs. binary precision, etc. Have a good link to a doc that explains 
exactly what SQL_DECIMAL is meant to be?

> PostgreSQL has SQL_DOUBLE associated with it's INT8 (also called 
> LONGINT) instead of it's FLOAT(25 - 53) or DOUBLE PRECISION which gives 
> double precision.

Not sure about this one. You might want to take some of this up on 
[email protected] or even [email protected].

> It also has SQL_VARCHAR assoicated with TEXT instead of VARCHAR.

Not sure about this one either - if there was a reason for that, 
I don't remember it offhand.

Thanks for doing this work, it's good to see someone poking at 
this. +1 for getting the DBDs somewhat consistent, although it 
may be tricky as some (looking at you, MySQL!) play more fast and 
loose with their data type definitions than others. :)

- -- 
Greg Sabino Mullane [email protected]
End Point Corporation http://www.endpoint.com/
PGP Key: 0x14964AC8 201212312210
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----

iEYEAREDAAYFAlDiVCEACgkQvJuQZxSWSsiqhgCfZkvMm96WnYQTWkL59aYYwNQ6
ZBYAoJ+/mdSGL0xnxPT/+soRzQTQPLBH
=wsJm
-----END PGP SIGNATURE-----