Re: column_info()'s COLUMN_DEF values - literal vs function
On Wed, May 27, 2009 at 05:04:04PM +0200, H.Merijn Brand wrote: The ODBC 3.0 spec says http://web.archive.org/web/20070513203826/http://msdn.microsoft.com/library/en-us/odbc/htm/odbcsqlcolumns.asp The default value of the column. The value in this column should be interpreted as a string *if it is enclosed in quotation marks*. If NULL was specified as the default value, then this column is the word NULL, not enclosed in quotation marks. If the default value cannot be represented without truncation, then this column contains TRUNCATED, with no enclosing single quotation marks. If no default value was specified, then this column is NULL. *The value of COLUMN_DEF can be used in generating a new column definition*, except when it contains the value TRUNCATED. (The *emphasis* is mine.) So, people, what does your database driver do for these cases? Are COLUMN_DEF values for literal defaults returned by column_info() enclosed in quotation marks? Unify, and thus DBD::Unify, does not have support for default values in CREATE TABLE statements at all, so just tell me what to do. Ignore or fill with NULL I think If no default value was specified, then this column is NULL fits the bill. Tim.
Re: column_info()`s COLUMN_DEF values - literal vs function
On Wed, May 27, 2009 at 03:10:53PM -, Greg Sabino Mullane wrote: How does your DBI driver represent a default column value in the results returned by the column_info() method? DBD::Pg quotes enough to allow it to work when passed back in verbatim, with parens to indicate functions: $dbh-do(DROP TABLE abc CASCADE); $dbh-do(CREATE TABLE abc( bar1 TEXT DEFAULT 'current_user', bar2 TEXT DEFAULT current_user )); $sth = $dbh-column_info(undef, undef, 'abc', ''); $sth-execute(); for (@{$sth-fetchall_arrayref({})}) { print $_-{COLUMN_NAME} - $_-{COLUMN_DEF}\n; } Outputs: bar1 - 'current_user'::text bar2 - current_user() So Pg does the right thing and goes further by disambiguating it, not just returning the original text. Interesting. [idle aside: I wonder if SQL::Translator knows what to do with that.] If NULL was specified as the default value, then this column is the word NULL, not enclosed in quotation marks. However, we are definitely not doing this, and returning undef instead. If the purpose of this field according to the ODBC spec is to allow new column definitions, then I suppose DBD::Pg should change (although undefined does map to NULL in other contexts, and NULL is the default DEFAULT, so to speak, for most (all?) database systems). Doesn't seem like a big issue. Tim.
Re: column_info()'s COLUMN_DEF values - literal vs function
On Wed, May 27, 2009 at 04:16:48PM +0100, Martin Evans wrote: Tim Bunce wrote: How does your DBI driver represent a default column value in the results returned by the column_info() method? Specifically, does it distinguish between default literal strings and default functions/expressions? I ran the following code to SQL Server via the Easysoft SQL Server ODBC driver and DBD::ODBC: create table martin (a int default NULL, b int default 1, c char(20) default 'fred', d char(30) default current_user); DBI::dump_results($h-column_info(undef, undef, 'martin', undef)); The results are below. If you would like more types of defaults let me know. 'master', 'dbo', 'martin', 'a', '4', 'int', '10', '4', '0', '10', '1', undef, '(NULL)', 'master', 'dbo', 'martin', 'b', '4', 'int', '10', '4', '0', '10', '1', undef, '((1))', 'master', 'dbo', 'martin', 'c', '1', 'char', '20', '20', undef, undef, '1', undef, '('fred')', 'master', 'dbo', 'martin', 'd', '1', 'char', '30', '30', undef, undef, '1', undef, '(user_name())', So ODBC matches the spec and, like Postgres, is reporting an expression rather than the original literal text. (And wrapping it in parens, presumably to avoid precedence issues if used in an expression.) For now I've a draft patch to the DBI docs that looks like: -BCOLUMN_DEF: The default value of the column. +BCOLUMN_DEF: The default value of the column, in a format that can be used +directly in an SQL statement. + +Note that this may be an expression and not simply the text used for the +default value in the original CREATE TABLE statement. For example, given: + +col1 char(30) default current_user +col2 char(30) default 'string' + +where current_user is the name of a function, the corresponding CCOLUMN_DEF +values would be: + +Databasecol1 col2 +Postgres: current_user() 'string'::text +MS SQL: (user_name())('string') + I could in theory run this to around 10 databases via 4 or 5 DBDs but I'd really need ALOT or persuasion that I was helping out big time to to that. I'd be interested in the COLUMN_DEF values for other databases and DBDs but it's not urgent. Hopefully others can fill in the gaps. (Oracle and mysql are two big missing databases at the moment.). Tim.
Re: column_info()'s COLUMN_DEF values - literal vs function
Tim Bunce wrote: On Wed, May 27, 2009 at 04:16:48PM +0100, Martin Evans wrote: Tim Bunce wrote: How does your DBI driver represent a default column value in the results returned by the column_info() method? Specifically, does it distinguish between default literal strings and default functions/expressions? I ran the following code to SQL Server via the Easysoft SQL Server ODBC driver and DBD::ODBC: create table martin (a int default NULL, b int default 1, c char(20) default 'fred', d char(30) default current_user); DBI::dump_results($h-column_info(undef, undef, 'martin', undef)); The results are below. If you would like more types of defaults let me know. 'master', 'dbo', 'martin', 'a', '4', 'int', '10', '4', '0', '10', '1', undef, '(NULL)', 'master', 'dbo', 'martin', 'b', '4', 'int', '10', '4', '0', '10', '1', undef, '((1))', 'master', 'dbo', 'martin', 'c', '1', 'char', '20', '20', undef, undef, '1', undef, '('fred')', 'master', 'dbo', 'martin', 'd', '1', 'char', '30', '30', undef, undef, '1', undef, '(user_name())', So ODBC matches the spec and, like Postgres, is reporting an expression rather than the original literal text. (And wrapping it in parens, presumably to avoid precedence issues if used in an expression.) Of course, this is one particular ODBC driver. DBD::ODBC has the luxury in this one case of not having to do anything but leave it up to the ODBC driver to produce the result-set. We wrote this ODBC driver so obviously it conforms with the spec to the best we can make it. You should also be aware that although the Microsoft SQL Server driver (or at least one of the many many versions of their driver) returns the same as above it for those columns it returns more columns than above - another 6 IIRC. For now I've a draft patch to the DBI docs that looks like: -BCOLUMN_DEF: The default value of the column. +BCOLUMN_DEF: The default value of the column, in a format that can be used +directly in an SQL statement. + +Note that this may be an expression and not simply the text used for the +default value in the original CREATE TABLE statement. For example, given: + +col1 char(30) default current_user +col2 char(30) default 'string' + +where current_user is the name of a function, the corresponding CCOLUMN_DEF +values would be: + +Databasecol1 col2 +Postgres: current_user() 'string'::text +MS SQL: (user_name())('string') + I could in theory run this to around 10 databases via 4 or 5 DBDs but I'd really need ALOT or persuasion that I was helping out big time to to that. I'd be interested in the COLUMN_DEF values for other databases and DBDs but it's not urgent. Hopefully others can fill in the gaps. (Oracle and mysql are two big missing databases at the moment.). Tim. DBD::Oracle to one of our databases with: use DBI; use strict; use warnings; my $h = DBI-connect; eval {$h-do(q{drop table martin})}; my $table = 'EOT'; create table martin (a int default NULL, b int default 1, c char(20) default 'fred', d varchar2(30) default user, e int) EOT $h-do($table); DBI::dump_results($h-column_info(undef, 'XXX', 'MARTIN', undef)); shows: undef, XXX, MARTIN, A, 3, NUMBER, 38, 40, 0, 10, 1, undef, NULL, 3, undef, undef, 1, YES undef, XXX, MARTIN, B, 3, NUMBER, 38, 40, 0, 10, 1, undef, 1, 3, undef, undef, 2, YES undef, XXX, MARTIN, C, 1, CHAR, 20, 20, undef, undef, 1, undef, 'fred', 1, undef, undef, 3, YES undef, XXX, MARTIN, D, 12, VARCHAR2, 30, 30, undef, undef, 1, undef, user, 12, undef, undef, 4, YES undef, XXX, MARTIN, E, 3, NUMBER, 38, 40, 0, 10, 1, undef, undef, 3, undef, undef, 5, YES but from what I remember (October 2001 when Steffen came up with it), it is just a massive SQL statement and probably could be altered without too many problems (for someone else ;-) - I've got way too many problems with DBD::Oracle and JSON::XS and bits of them not working with unicode). Notice I added a column with no default which I missed off the ODBC one to SQL Server so here it is again (different fn and not varchar2 obviously): 'master', 'dbo', 'martin', 'a', '4', 'int', '10', '4', '0', '10', '1', undef, '(NULL)', '4', undef, undef, '1', 'YES', undef, undef, undef, undef, undef, undef, '38' 'master', 'dbo', 'martin', 'b', '4', 'int', '10', '4', '0', '10', '1', undef, '((1))', '4', undef, undef, '2', 'YES', undef, undef, undef, undef, undef, undef, '38' 'master', 'dbo', 'martin', 'c', '1', 'char', '20', '20', undef, undef, '1', undef, '('fred')', '1', undef, '20', '3', 'YES', undef, undef, undef, undef, undef, undef, '39' 'master', 'dbo', 'martin', 'd', '12', 'varchar', '30', '30', undef, undef, '1', undef, '(user_name())', '12', undef, '30', '4', 'YES', undef, undef, undef, undef, undef, undef, '39'
Re: column_info()'s COLUMN_DEF values - literal vs function
Tim Bunce wrote: I'd be interested in the COLUMN_DEF values for other databases and DBDs but it's not urgent. Hopefully others can fill in the gaps. (Oracle and mysql are two big missing databases at the moment.). DBD::Oracle (s/current_user/user) undef, 'TST', 'MARTIN', 'A', '3', 'NUMBER', '38', '40', '0', '10', '1', undef, 'NULL', '3', undef, undef, '1', 'YES' undef, 'TST', 'MARTIN', 'B', '3', 'NUMBER', '38', '40', '0', '10', '1', undef, '1', '3', undef, undef, '2', 'YES' undef, 'TST', 'MARTIN', 'C', '1', 'CHAR', '20', '20', undef, undef, '1', undef, ''fred'', '1', undef, undef, '3', 'YES' undef, 'TST', 'MARTIN', 'D', '1', 'CHAR', '30', '30', undef, undef, '1', undef, 'user DBD::ADO Provider=MSDAORA (s/current_user/user) undef, 'TST', 'MARTIN', 'A', 2, 'NUMBER', 38, undef, 0, undef, 1, undef, undef, 2, undef, undef, '1', 'YES' undef, 'TST', 'MARTIN', 'B', 2, 'NUMBER', 38, undef, 0, undef, 1, undef, undef, 2, undef, undef, '2', 'YES' undef, 'TST', 'MARTIN', 'C', 1, 'CHAR', 20, '20', undef, undef, 1, undef, undef, 1, undef, '20', '3', 'YES' undef, 'TST', 'MARTIN', 'D', 1, 'CHAR', 30, '30', undef, undef, 1, undef, undef, 1, undef, '30', '4', 'YES' Provider=SQLOLEDB 'master', 'dbo', 'martin', 'a', 4, 'int', 10, undef, undef, undef, 1, undef, 'null', 4, undef, undef, '1', 'YES' 'master', 'dbo', 'martin', 'b', 4, 'int', 10, undef, undef, undef, 1, undef, '1', 4, undef, undef, '2', 'YES' 'master', 'dbo', 'martin', 'c', 1, 'char', 20, '20', undef, undef, 1, undef, ''fred'', 1, undef, '20', '3', 'YES' 'master', 'dbo', 'martin', 'd', 1, 'char', 30, '30', undef, undef, 1, undef, 'user_name()', 1, undef, '30', '4', 'YES' Provider=VFPOLEDB undef, undef, 'martin', 'a', 4, 'I', 4, undef, undef, undef, 1, undef, '.NULL.', 4, undef, undef, '1', 'YES' undef, undef, 'martin', 'b', 4, 'I', 4, undef, undef, undef, 1, undef, '1', 4, undef, undef, '2', 'YES' undef, undef, 'martin', 'c', 1, 'C', 20, '20', undef, undef, 1, undef, 'fred', 1, undef, '20', '3', 'YES' undef, undef, 'martin', 'd', 1, 'C', 30, '30', undef, undef, 1, undef, 'current_user', 1, undef, '30', '4', 'YES' Provider=Microsoft.Jet.OLEDB.4.0 undef, undef, 'martin', 'a', 4, 'Long', 10, undef, undef, undef, 1, undef, 'NULL', 4, undef, undef, '1', 'YES' undef, undef, 'martin', 'b', 4, 'Long', 10, undef, undef, undef, 1, undef, '1', 4, undef, undef, '2', 'YES' undef, undef, 'martin', 'c', -8, undef, '20', '40', undef, undef, 1, undef, ''fred'', -8, undef, '40', '3', 'YES' undef, undef, 'martin', 'd', -8, undef, '30', '60', undef, undef, 1, undef, 'current_user', -8, undef, '60', '4', 'YES' Steffen
column_info()'s COLUMN_DEF values - literal vs function
How does your DBI driver represent a default column value in the results returned by the column_info() method? Specifically, does it distinguish between default literal strings and default functions/expressions? Consider the difference between these two column definitions bar1 timestamp not null default 'current_timestamp', bar2 timestamp not null default current_timestamp, or, more generally: foo1 varchar(20) not null default 'current_user', foo2 varchar(20) not null default current_user, This issue has cropped up in relation to a bug Jos has filed against DBIx::Class::Schema::Loader: https://rt.cpan.org/Ticket/Display.html?id=46412 The ODBC 3.0 spec says http://web.archive.org/web/20070513203826/http://msdn.microsoft.com/library/en-us/odbc/htm/odbcsqlcolumns.asp The default value of the column. The value in this column should be interpreted as a string *if it is enclosed in quotation marks*. If NULL was specified as the default value, then this column is the word NULL, not enclosed in quotation marks. If the default value cannot be represented without truncation, then this column contains TRUNCATED, with no enclosing single quotation marks. If no default value was specified, then this column is NULL. *The value of COLUMN_DEF can be used in generating a new column definition*, except when it contains the value TRUNCATED. (The *emphasis* is mine.) So, people, what does your database driver do for these cases? Are COLUMN_DEF values for literal defaults returned by column_info() enclosed in quotation marks? Tim.
Re: column_info()'s COLUMN_DEF values - literal vs function
On Wed, 27 May 2009 15:40:13 +0100, Tim Bunce tim.bu...@pobox.com wrote: How does your DBI driver represent a default column value in the results returned by the column_info() method? Specifically, does it distinguish between default literal strings and default functions/expressions? Consider the difference between these two column definitions bar1 timestamp not null default 'current_timestamp', bar2 timestamp not null default current_timestamp, or, more generally: foo1 varchar(20) not null default 'current_user', foo2 varchar(20) not null default current_user, This issue has cropped up in relation to a bug Jos has filed against DBIx::Class::Schema::Loader: https://rt.cpan.org/Ticket/Display.html?id=46412 The ODBC 3.0 spec says http://web.archive.org/web/20070513203826/http://msdn.microsoft.com/library/en-us/odbc/htm/odbcsqlcolumns.asp The default value of the column. The value in this column should be interpreted as a string *if it is enclosed in quotation marks*. If NULL was specified as the default value, then this column is the word NULL, not enclosed in quotation marks. If the default value cannot be represented without truncation, then this column contains TRUNCATED, with no enclosing single quotation marks. If no default value was specified, then this column is NULL. *The value of COLUMN_DEF can be used in generating a new column definition*, except when it contains the value TRUNCATED. (The *emphasis* is mine.) So, people, what does your database driver do for these cases? Are COLUMN_DEF values for literal defaults returned by column_info() enclosed in quotation marks? Unify, and thus DBD::Unify, does not have support for default values in CREATE TABLE statements at all, so just tell me what to do. Ignore or fill with NULL DBD::CSV is completely depending on SQL::Statement through DBD::File here, and as DBD::CSV has no concept (yet) of a stored DDD, there is no real use for it al all. -- H.Merijn Brand http://tux.nl Perl Monger http://amsterdam.pm.org/ using porting perl 5.6.2, 5.8.x, 5.10.x, 5.11.x on HP-UX 10.20, 11.00, 11.11, 11.23, and 11.31, OpenSuSE 10.3, 11.0, and 11.1, AIX 5.2 and 5.3. http://mirrors.develooper.com/hpux/ http://www.test-smoke.org/ http://qa.perl.org http://www.goldmark.org/jeff/stupid-disclaimers/
Re: column_info()`s COLUMN_DEF values - literal vs function
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 How does your DBI driver represent a default column value in the results returned by the column_info() method? DBD::Pg quotes enough to allow it to work when passed back in verbatim, with parens to indicate functions: $dbh-do(DROP TABLE abc CASCADE); $dbh-do(CREATE TABLE abc( bar1 TEXT DEFAULT 'current_user', bar2 TEXT DEFAULT current_user )); $sth = $dbh-column_info(undef, undef, 'abc', ''); $sth-execute(); for (@{$sth-fetchall_arrayref({})}) { print $_-{COLUMN_NAME} - $_-{COLUMN_DEF}\n; } Outputs: bar1 - 'current_user'::text bar2 - current_user() If NULL was specified as the default value, then this column is the word NULL, not enclosed in quotation marks. However, we are definitely not doing this, and returning undef instead. If the purpose of this field according to the ODBC spec is to allow new column definitions, then I suppose DBD::Pg should change (although undefined does map to NULL in other contexts, and NULL is the default DEFAULT, so to speak, for most (all?) database systems). - -- Greg Sabino Mullane g...@endpoint.com g...@turnstep.com End Point Corporation PGP Key: 0x14964AC8 200607261743 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iEYEAREDAAYFAkodV9kACgkQvJuQZxSWSsgAgACg08w+JwQ5CDn/wo1Xyr8KywEG PIIAoL1kiaYWLDSmdS9+Eu1BfslupFxT =3SqD -END PGP SIGNATURE-
Re: column_info()'s COLUMN_DEF values - literal vs function
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Tim Bunce wrote: How does your DBI driver represent a default column value in the results returned by the column_info() method? Specifically, does it distinguish between default literal strings and default functions/expressions? Consider the difference between these two column definitions bar1 timestamp not null default 'current_timestamp', bar2 timestamp not null default current_timestamp, or, more generally: foo1 varchar(20) not null default 'current_user', foo2 varchar(20) not null default current_user, This issue has cropped up in relation to a bug Jos has filed against DBIx::Class::Schema::Loader: https://rt.cpan.org/Ticket/Display.html?id=46412 The ODBC 3.0 spec says http://web.archive.org/web/20070513203826/http://msdn.microsoft.com/library/en-us/odbc/htm/odbcsqlcolumns.asp The default value of the column. The value in this column should be interpreted as a string *if it is enclosed in quotation marks*. If NULL was specified as the default value, then this column is the word NULL, not enclosed in quotation marks. If the default value cannot be represented without truncation, then this column contains TRUNCATED, with no enclosing single quotation marks. If no default value was specified, then this column is NULL. *The value of COLUMN_DEF can be used in generating a new column definition*, except when it contains the value TRUNCATED. (The *emphasis* is mine.) So, people, what does your database driver do for these cases? Are COLUMN_DEF values for literal defaults returned by column_info() enclosed in quotation marks? Tim. I ran the following code to SQL Server via the Easysoft SQL Server ODBC driver and DBD::ODBC: use DBI; use strict; use warnings; my $h = DBI-connect; eval {$h-do(q{drop table martin})}; my $table = 'EOT'; create table martin (a int default NULL, b int default 1, c char(20) default 'fred', d char(30) default current_user); EOT $h-do($table); DBI::dump_results($h-column_info(undef, undef, 'martin', undef)); The results are below. If you would like more types of defaults let me know. I could in theory run this to around 10 databases via 4 or 5 DBDs but I'd really need ALOT or persuasion that I was helping out big time to to that. 'master', 'dbo', 'martin', 'a', '4', 'int', '10', '4', '0', '10', '1', undef, '(NULL)', '4', undef, undef, '1', 'YES', undef, undef, undef, undef, undef, undef, '38' 'master', 'dbo', 'martin', 'b', '4', 'int', '10', '4', '0', '10', '1', undef, '((1))', '4', undef, undef, '2', 'YES', undef, undef, undef, undef, undef, undef, '38' 'master', 'dbo', 'martin', 'c', '1', 'char', '20', '20', undef, undef, '1', undef, '('fred')', '1', undef, '20', '3', 'YES', undef, undef, undef, undef, undef, undef, '39' 'master', 'dbo', 'martin', 'd', '1', 'char', '30', '30', undef, undef, '1', undef, '(user_name())', '1', undef, '30', '4', 'YES', undef, undef, undef, undef, undef, undef, '39' 4 rows Martin - -- Martin J. Evans Easysoft Limited http://www.easysoft.com -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFKHVlfhd1KVpsamNgRAqdHAJ42KesVvO3uxy0M20X14PfSYYwVigCfVhko PUZrbicrua54kN5bc+XpLK4= =alsQ -END PGP SIGNATURE-