Re: RDBMS comparison tool
On 01/01/2013 03:12, Greg Sabino Mullane wrote: 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. :) BTW, I looked into the MySQL type issues further an ended up submitting a bug with patch: http://bugs.mysql.com/bug.php?id=68266 Although SQL_DECIMAL says it's mapped to DOUBLE it's actually correctly mapped to MYSQL_TYPE_DECIMAL which I presume is exact numeric (it certainly looks like it form my testing). SQL_BIT's mapping on the other hand was just plain wrong, and it's not able to auto_increment. DBD::Pg makes no effort to check the DB version and report more accurate type lists. I don't know how far I'll be able to get with that, but I'll start a conversation on the DBD::Pg mailing list as you suggested. Lyle
Re: RDBMS comparison tool
On 07/01/2013 13:08, Lyle wrote:
On 07/01/2013 10:43, H.Merijn Brand wrote:
On Sun, 30 Dec 2012 02:53:33 +, Lyle webmas...@cosmicperl.com
wrote:
Hi All,
Whilst working on another project it made sense to write a tool for
comparing the various RDBMSs...
Very useful indeed, but I'm afraid that it also depends on the version
of the database(s). I've done several talks about the deficiencies of
RDBMS's and basically, there is no perfect database: they all have
their hatred areas *). Choose the one that best fits your current needs
(even if that is a flat-file solution).
What would you need (as perl-script output) to extend that info for
DBD::Unify (which I still have access to) and DBD::CSV, DBD::SQLite
I've got a tight deadline for this project submission, and I'm only
focusing on those four databases. If you want to wait four months
until after my deadline then I'll take a look, otherwise it's probably
easiest to grab a copy off github:
https://github.com/cosmicnet/CompareRDBMS
I couldn't resist having a quick look at SQLite, turns out they haven't
implemented type_info so it doesn't return anything.
sub type_info_all {
return; # XXX code just copied from DBD::Oracle, not yet thought about
DBD::CSV does return type info, for some reason it returns two identical
BLOBs?
I've updated the reports:
http://www.cosmicperl.com/rdbms/compare_types.html
http://www.cosmicperl.com/rdbms/compare_type_details.html
Lyle
Re: RDBMS comparison tool
On 05/01/2013 20:49, Darren Duncan wrote: On 2013.01.05 5:39 AM, Lyle wrote: I'm not overly familiar with Perl's internal handling of number. I guess if you have DECIMAL from a character string Perl will switch it out to an approximate the moment you do a calculation on it. Furthermore if the DBI (or the DBDs, I'm not sure where the distinction lies) is already putting it into a Perl decimal which is floating point, then the battle has already been lost before it gets to me. ... As for what DBDs actually do, well that's a different matter; but I'm talking about what *could* be done in the Perl somewhere, and typically I'd expect the DBD to make that decision on the Perl's behalf. The DBI docs have: Most data is returned to the Perl script as strings. (Null values are returned as |undef|.) This allows arbitrary precision numeric data to be handled without loss of accuracy. Beware that Perl may not preserve the same accuracy when the string is used as a number. So as long as the DBD isn't breaking this, I should be getting decimal numbers back as strings. Lyle
Re: RDBMS comparison tool
On 08/01/13 12:17, Lyle wrote:
On 05/01/2013 20:49, Darren Duncan wrote:
On 2013.01.05 5:39 AM, Lyle wrote:
I'm not overly familiar with Perl's internal handling of number. I guess if you
have DECIMAL from a character string Perl will switch it out to an approximate
the moment you do a calculation on it. Furthermore if the DBI (or the DBDs, I'm
not sure where the distinction lies) is already putting it into a Perl decimal
which is floating point, then the battle has already been lost before it gets to
me.
...
As for what DBDs actually do, well that's a different matter; but I'm talking
about what *could* be done in the Perl somewhere, and typically I'd expect the
DBD to make that decision on the Perl's behalf.
The DBI docs have:
Most data is returned to the Perl script as strings. (Null values are returned as
|undef|.) This allows arbitrary precision numeric data to be handled without loss of
accuracy. Beware that Perl may not preserve the same accuracy when the string is used as
a number.
So as long as the DBD isn't breaking this, I should be getting decimal numbers
back as strings.
Lyle
FYI
In ODBC a value may be bound as a numeric and the numeric returned is then a
structure:
typedef struct tagSQL_NUMERIC_STRUCT
{
SQLCHAR precision;
SQLSCHARscale;
SQLCHAR sign; /* 1=pos 0=neg */
SQLCHAR val[SQL_MAX_NUMERIC_LEN];
} SQL_NUMERIC_STRUCT;
DBD::ODBC never actually binds numerics like this - it always binds as a string
and lets the driver do the conversion for it.
There are similar structures for GUIDs, dates, times and timestamps which
DBD::ODBC also does not use as it would be responsible for any conversion.
More recently, DBD::ODBC binds integer columns as integers instead of strings
as it is more efficient.
Also, be careful just saying this is what MS SQL Server does in your table -
your specific SQL Server returns those results, many will not. You obviously
have a fairly recent version as I can see sql_variant.
I was unsure why you'd include SQL_ALL_TYPES in the table - it is not a type.
Martin
--
Martin J. Evans
Easysoft Limited
http://www.easysoft.com
Re: RDBMS comparison tool
On 08/01/2013 13:19, Martin J. Evans wrote:
FYI
In ODBC a value may be bound as a numeric and the numeric returned is
then a structure:
typedef struct tagSQL_NUMERIC_STRUCT
{
SQLCHAR precision;
SQLSCHARscale;
SQLCHAR sign; /* 1=pos 0=neg */
SQLCHAR val[SQL_MAX_NUMERIC_LEN];
} SQL_NUMERIC_STRUCT;
DBD::ODBC never actually binds numerics like this - it always binds as
a string and lets the driver do the conversion for it.
Good to know.
Also, be careful just saying this is what MS SQL Server does in your
table - your specific SQL Server returns those results, many will not.
You obviously have a fairly recent version as I can see sql_variant.
Good point, I was going to add this in my write up, but as I'm posting
it here I really should make it clear to everyone else.
Other drivers like DBD::Pg appear to return the same type list
regardless of version.
I was unsure why you'd include SQL_ALL_TYPES in the table - it is not
a type.
It's just pull out the type list from DBI Constants:
http://search.cpan.org/~timb/DBI-1.623/DBI.pm#DBI_Constants
Your right, I should filter out SQL_ALL_TYPES.
So if I know understand correctly. DBI's type_info and type_info_all is
an emulation of SQLGetTypeInfo from ODBC, to describe to users what
types should be available for the database in question. It's not
actually used internally? Internally the DBD will decide how to map a
columns reported type to Perl types, be that integer or character.
Lyle
Re: RDBMS comparison tool
On 08/01/13 16:58, Lyle wrote:
On 08/01/2013 13:19, Martin J. Evans wrote:
FYI
In ODBC a value may be bound as a numeric and the numeric returned is then a
structure:
typedef struct tagSQL_NUMERIC_STRUCT
{
SQLCHAR precision;
SQLSCHARscale;
SQLCHAR sign; /* 1=pos 0=neg */
SQLCHAR val[SQL_MAX_NUMERIC_LEN];
} SQL_NUMERIC_STRUCT;
DBD::ODBC never actually binds numerics like this - it always binds as a string
and lets the driver do the conversion for it.
Good to know.
Also, be careful just saying this is what MS SQL Server does in your table -
your specific SQL Server returns those results, many will not. You obviously
have a fairly recent version as I can see sql_variant.
Good point, I was going to add this in my write up, but as I'm posting it here
I really should make it clear to everyone else.
Other drivers like DBD::Pg appear to return the same type list regardless of
version.
I was unsure why you'd include SQL_ALL_TYPES in the table - it is not a type.
It's just pull out the type list from DBI Constants:
http://search.cpan.org/~timb/DBI-1.623/DBI.pm#DBI_Constants
Your right, I should filter out SQL_ALL_TYPES.
So if I know understand correctly. DBI's type_info and type_info_all is an
emulation of SQLGetTypeInfo from ODBC, to describe to users what types should
be available for the database in question. It's not actually used internally?
Internally the DBD will decide how to map a columns reported type to Perl
types, be that integer or character.
Well I don't think I was around when type_info and type_info_all were added to DBI but I
believe it is based on ODBC's SQLGetTypeInfo. You might want to look at Generating
the type_info method in DBI::DBD. You need the ODBC driver for the database you
want to generate it for which is an even stronger suggestion it was based on ODBC.
Martin
--
Martin J. Evans
Easysoft Limited
http://www.easysoft.com
Re: RDBMS comparison tool
On Sun, 30 Dec 2012 02:53:33 +, Lyle webmas...@cosmicperl.com 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 Very useful indeed, but I'm afraid that it also depends on the version of the database(s). I've done several talks about the deficiencies of RDBMS's and basically, there is no perfect database: they all have their hatred areas *). Choose the one that best fits your current needs (even if that is a flat-file solution). What would you need (as perl-script output) to extend that info for DBD::Unify (which I still have access to) and DBD::CSV, DBD::SQLite *) http://tux.nl/Talks/DBDc/quo1.html http://tux.nl/Talks/DBDc/name.html http://tux.nl/Talks/DBDc/null.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 -- H.Merijn Brand http://tux.nl Perl Monger http://amsterdam.pm.org/ using perl5.00307 .. 5.17 porting perl5 on HP-UX, AIX, and openSUSE http://mirrors.develooper.com/hpux/http://www.test-smoke.org/ http://qa.perl.org http://www.goldmark.org/jeff/stupid-disclaimers/
Re: RDBMS comparison tool
On 2013.01.04 4:17 PM, Lyle wrote: On 01/01/2013 03:12, Greg Sabino Mullane wrote: Lyle wrote: 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. I thought it might be useful to know strings below a certain length are stored uncompressed and so a little faster. Likewise very long strings have a different storage mechanism one might want to avoid. Although I've only just had a reply to my post asking for specifics, and haven't had chance to look into it further. My understanding about Postgres either compressing strings or using toast segments for longer ones is that this is just an internal implementation detail and that user-facing concepts like data types should be ignoring these differences. Character data is just text, a sequence of characters of arbitrary length, and that's all there is to 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? 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? I've got a copy of the standard, but I'm pretty sure I'd be breaking some law if I copied and pasted bits. DECIMAL is supposed to be an exact numeric. Whereas FLOAT, REAL, DOUBLE PRECISION are approximate numeric. So I guess DOUBLE is a better fit as it's supposed to be more accurate. But neither are a good match really. Here's the thing. The most important difference is exact numeric versus approximate numeric. Your type list should clearly and explicitly separate these into separate rows from each other, and never cross types between them. Things like integers or rationals or DECIMAL etc are exact numeric. Things like FLOAT and DOUBLE are analogous to IEEE floats which are inexact and are defined by using a certain number of bits to store every value of their type. I don't recall which of the above REAL goes in. If different DBMSs use the same FOO to be exact in one or approximate in another, still keep them in different rows. Since the SQL standard, as well as some other programming languages, define decimal as being exact numeric, then it is absolutely wrong to map them to either FLOAT or DOUBLE. In fact, in Perl 5 the only native type that can map to a DECIMAL is a character string of numeric digits. Don't shoehorn this. There is no good reason to map DECIMAL to a floating point type. Likewise, under no circumstance, map integers to floating types. 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. :) Yes, the hard part is certainly trying to find consistency, or a way of making them act/emulate some consistency. Since in Postgres a VARCHAR is just a restricted TEXT, map length-restricted character types to VARCHAR and unrestricted ones like Perl strings to TEXT, that would carry the best semantics. -- Darren Duncan
Re: RDBMS comparison tool
On 05/01/2013 10:41, Darren Duncan wrote: My understanding about Postgres either compressing strings or using toast segments for longer ones is that this is just an internal implementation detail and that user-facing concepts like data types should be ignoring these differences. Character data is just text, a sequence of characters of arbitrary length, and that's all there is to it. It does indeed: http://www.postgresql.org/docs/9.2/static/storage-toast.html So the PostgreSQL idea of char, varchar, text, they can all store up to around 1gb. I'm currently trying to find some sort of equivalence between the RDBMS types. For PostgreSQL, the distinction between what they might call char and clob are hidden from the user, but they do kind of exist internally. I've got a copy of the standard, but I'm pretty sure I'd be breaking some law if I copied and pasted bits. DECIMAL is supposed to be an exact numeric. Whereas FLOAT, REAL, DOUBLE PRECISION are approximate numeric. So I guess DOUBLE is a better fit as it's supposed to be more accurate. But neither are a good match really. Here's the thing. The most important difference is exact numeric versus approximate numeric. Your type list should clearly and explicitly separate these into separate rows from each other, and never cross types between them. Yes, there is good argument for this. I guess it would help to know exactly what the DBD type_info is used for. I'm not sure if it has some internal use, or whether it's just something to give to users who are asking for particular types. Things like integers or rationals or DECIMAL etc are exact numeric. Things like FLOAT and DOUBLE are analogous to IEEE floats which are inexact and are defined by using a certain number of bits to store every value of their type. I don't recall which of the above REAL goes in. REAL is approximate, inexact. If different DBMSs use the same FOO to be exact in one or approximate in another, still keep them in different rows. Since the SQL standard, as well as some other programming languages, define decimal as being exact numeric, then it is absolutely wrong to map them to either FLOAT or DOUBLE. In fact, in Perl 5 the only native type that can map to a DECIMAL is a character string of numeric digits. Don't shoehorn this. There is no good reason to map DECIMAL to a floating point type. I'm not overly familiar with Perl's internal handling of number. I guess if you have DECIMAL from a character string Perl will switch it out to an approximate the moment you do a calculation on it. Furthermore if the DBI (or the DBDs, I'm not sure where the distinction lies) is already putting it into a Perl decimal which is floating point, then the battle has already been lost before it gets to me. Likewise, under no circumstance, map integers to floating types. 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. :) Yes, the hard part is certainly trying to find consistency, or a way of making them act/emulate some consistency. Since in Postgres a VARCHAR is just a restricted TEXT, map length-restricted character types to VARCHAR and unrestricted ones like Perl strings to TEXT, that would carry the best semantics. PostgreSQL do recommend using VARCHAR instead of CHAR. Due to the way they are implemented, VARCHAR is usually more efficient. Lyle
Re: RDBMS comparison tool
On 2013.01.05 5:39 AM, Lyle wrote: On 05/01/2013 10:41, Darren Duncan wrote: Since the SQL standard, as well as some other programming languages, define decimal as being exact numeric, then it is absolutely wrong to map them to either FLOAT or DOUBLE. In fact, in Perl 5 the only native type that can map to a DECIMAL is a character string of numeric digits. Don't shoehorn this. There is no good reason to map DECIMAL to a floating point type. I'm not overly familiar with Perl's internal handling of number. I guess if you have DECIMAL from a character string Perl will switch it out to an approximate the moment you do a calculation on it. Furthermore if the DBI (or the DBDs, I'm not sure where the distinction lies) is already putting it into a Perl decimal which is floating point, then the battle has already been lost before it gets to me. My understanding is that Perl 5 has 3 internal data types of relevance here, which are integers, floats, and strings. A DBMS integer can be represented perfectly by a Perl integer as long as it is within the Perl integer range, which AFAIK these days is typically a 64-bit machine int but used to be 32 bits, either depending on your configure settings at compile time (perl -V can tell you). If the integer is larger than that, eg some DBMSs support 128 bit ints, you'd need to use a Perl string to represent it perfectly. A DBMS float might be exactly representable by a Perl float, maybe, depending on their details, or there might be a round-off in the conversion; now while that loss might not matter, it means round-tripping may not produce an identical value, which is a larger concern in my mind. A DBMS DECIMAL has no native Perl equivalent and you'd have to use a string in the general case. As for what DBDs actually do, well that's a different matter; but I'm talking about what *could* be done in the Perl somewhere, and typically I'd expect the DBD to make that decision on the Perl's behalf. Yes, doing naive calculation on numbers-as-strings could cause loss, but at least having it as strings initially gives the Perl code the choice for what to do because it has all the detail. Now Perl does also have BigInt, BigRat, BigFloat etc which could be utilized for exact transference, but AFAIK no DBD does that as it would hurt performance or add further dependencies. -- Darren Duncan
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 dbdpg...@perl.org or even pgsql-gene...@postgresql.org. 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 g...@turnstep.com 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-
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
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. 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. As far as I understand thus far, 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. Lyle
Re: RDBMS comparison tool
I'm sorry. Me and Darren are having this conversation on two separate lists (this one and TTM) that we are both part of, but have a substantially different subscriber base. This response was supposed to have gone to TTM. Lyle On 30/12/2012 16:45, Lyle wrote: 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. 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. As far as I understand thus far, 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. Lyle
Re: RDBMS comparison tool
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. 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. -- 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
