Re: RDBMS comparison tool

On 30/12/2012 04:19, Darren Duncan wrote:
> Yes, that is useful.
>
> I think you should add a column such that your leftmost column is some 
> canonical type name you made for the report, and have the SQL standard 
> name(s) in a separate column like the ODBC standard names are.

As far as I understand thus far, and one of the DBI devs may correct me 
here, the ODBC and SQL standard names are the same for all those types 
with codes >= 1. Although I agree having some distinction between them 
other than the numbers would be useful. The column marked ODBC is SQL 
Server, it's just using the ODBC driver, I'll need to correct that in 
the next version to make it more clear.

I'm not sure what types names I could create in a new left column that 
wouldn't match the current left column. I'm open to suggestions if you 
want to send me some ideas.

> This works best when no one list is a superset of the others, which is 
> surely the case, then you don't have say the confusion about which 
> things in the first column are SQL standard actual vs some placeholder 
> you added from ODBC/etc.

The more I look at this, the more I find things that seem a little out 
of place. For example:
The MySQL driver is declaring it has type SQL_BIT, but that's actually 
it's CHAR(1) which is a byte, not a bit (at least in LATIN1). According 
to MySQL it does have a proper BIT. It also has a BOOL, but that's just 
a synonym for TINYINT(1).

PostgreSQL BYTEA that's currently in SQL_VARBINARY, would seem a better 
fix for SQL_LONGVARBINARY... But then what would you have in 
SQL_VARBINARY... Similar situation for PostgreSQL character. It's like 
it fits into both categories really. The standard itself doesn't specify 
lengths for data types, extra names like LONGVARBINARY were created by 
the implementations when they wanted to offer larger objects than they 
had before. 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. 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?

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

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. It also has SQL_VARCHAR assoicated with TEXT instead 
of VARCHAR.

I have a feeling that MySQL's DATETIME could, or maybe should be 
associated with SQL_TYPE_TIMESTAMP.

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?


Lyle

>
> -- Darren Duncan
>
> Lyle wrote:
>> Hi All,
>>   Whilst working on another project it made sense to write a tool for 
>> comparing the various RDBMSs. I'm talking about the database 
>> management systems themselves, not databases within them. So far I've 
>> done parts that use $dbh->type_info_all() to compare what types SQL 
>> Server, Postgres, Oracle and MySQL have available and their details. 
>> Generating reports like:
>> http://cosmicperl.com/rdbms/compare_types.html
>> http://cosmicperl.com/rdbms/compare_type_details.html
>>
>> I'm not yet sure as to whether the mapping from the RDBMSs local sql 
>> type to the ones the DBI recognises is done by the DBD driver, or 
>> whether this is already predetermined by the RDBMS...
>>
>> Let me know if this isn't interesting to you all and I'll keep it off 
>> list.
>>
>> Lyle
>>
>>
>
>