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
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-
