Re: Maintaining DBD::mysql
On Mon, Mar 24, 2003 at 12:04:13AM -0500, Rudy Lippan wrote:
On Fri, 14 Mar 2003, Tim Bunce wrote:
get_info needs implementing. DBI::DBD::Metadata is best for that.
Very Nice. :) Though, it looks like the output needs quite a bit of help
in the way of picking off of the mysql struct and such :(
quote_identifier needs implementing. (Or you could just implement
get_info for values 29 and 41 and the DBI's default quote_identifier
method should then do the right thing.)
1. How do you see $dbh-quote_identifier($catalog, $schema, $table) being
used with mysql, where it seems that $catalog.$table is what mysql
expects. And $catalog..$table does not work.
$catalog is unused for most databases, including MySQL. They use $schema.
Just copying the DBI code should be fine.
2. ` is the quoting char (In non-ansi mode); however, `` is not the
escaped quote,
[Groan] Okay, copy the DBI's quote_identifier code and tweak to use
backslash escaping.
and '/', '\', and '.' can not be used in a
quoted identifier. Should quote_identifier() thow an error if it gets
data that it cannot quote? -- I think it should, but I just wanted to
some feed back.
It's generally best not to second-guess the server...
mysql create table foo (`foo.bar` int);
Query OK, 0 rows affected (0.09 sec)
mysql desc foo;
+-+-+--+-+-+---+
| Field | Type| Null | Key | Default | Extra |
+-+-+--+-+-+---+
| foo.bar | int(11) | YES | | NULL| |
+-+-+--+-+-+---+
For table names (which can't contain '.' etc) the server will
generate an error if and when apprioriate.
A couple of other items I've remembered recently...
- I'd really like to see mysql_client_found_rows=1 being the default.
With mysql_client_found_rows off, normal application code can't
tell the difference between an update that failed to match a row
and one that matched a row but didn't actually change anything.
It's a significant inconsistency with other drivers. I don't know
of any other driver that return a rows *physically changed* count.
This is going to be an issue with future versions of Class::DBI
because it will treat $obj-update() updating 0 rows as an error.
- The connect settings passed in the DSN string should be extracted
and set as standard attributes. Currently you can do:
DBI-connect(dbi:mysql:foo;mysql_compression=1, ...)
but not
DBI-connect(dbi:mysql:foo, ..., { mysql_compression=1 })
or
DBI-connect(dbi:mysql(mysql_compression=1):foo, ...)
but want's to set certain attributes, like mysql_client_found_rows
for mysql. Currently the code has to edit the $DSN value. Not good.
An easy way to implement the change is to use dbd_db_login6
instead of dbd_db_login. Then the \%attr is passed in. The code can
then *delete* from attr any attributes it handles *at connect time*.
The DBI will call $dbh-STORE for any *remaining* attributes after
the connection has been established, before DBI-connect() returns.
Tim.
Re: Maintaining DBD::mysql
On Mon, 24 Mar 2003, Paul DuBois wrote:
At 12:52 + 3/24/03, Tim Bunce wrote:
On Mon, Mar 24, 2003 at 12:04:13AM -0500, Rudy Lippan wrote:
and '/', '\', and '.' can not be used in a
quoted identifier. Should quote_identifier() thow an error if it gets
data that it cannot quote? -- I think it should, but I just wanted to
some feed back.
It's generally best not to second-guess the server...
Yeah, I do not know what I was thinking when I wrote that -- Must have
been late or something :) I agree that the driver should not care about
the what is in a quoted identifier. I think I conflated two issues and
confused myself. The question I should have asked is: do we throw an error
if the client sends up something that cannot be escaped... See below.
For table names (which can't contain '.' etc) the server will
generate an error if and when appropriate.
The exact rules for MySQL identifiers are:
Database and table names cannot contain '\', '/', or '.' characters.
Column and index names can.
What are the rules for escaping the quoting character?
create table `thisthat` (foo int); -- confuses the mysql client.
create table asdf (`foo'bnar` int); -- confuses mysql client.
create table `asdf``asdf` (foo int); -- throws an error
And
mysql create table asdf (`foo\`bnar` int);
ERROR:
Unknown command '\`'.
ERROR 1064: You have an error in your SQL syntax. Check the manual that
corresponds to your MySQL server version for the right syntax to use near
'bnar` int)
And here without the client:
use DBI;
$dbh = DBI-connect(dbi:mysql:dbname=rlippan,root,, {RaiseError=1,
PrintE
rror=0});
my @names = (
q{`foobar`}, q{`Foo'bar`}, q{`foobar`}, q{`foo``bar`}, q{`foo\`bar`},
q{`foo`bar}
);
for (@names) { eval {$dbh-do(qq{ DROP TABLE $_}) } }
for (@names) {
eval {
$dbh-do(qq{
CREATE TABLE $_ (baz int)
});
}; if (my $e = $@) {
print $_ causes: $e\n;
} else {
print $_ Works\n;
}
}
And its output is:
`foobar` Works
`Foo'bar` Works
`foobar` Works
`foo``bar` causes: DBD::mysql::db do failed: You have an error in your SQL
syntax. Check the manual that corresponds to your MySQL server version
for the right syntax to use near 'bar` (baz int)' at line 1 at quoting.pl
line 14.
`foo\`bar` causes: DBD::mysql::db do failed: You have an error in your SQL
syntax. Check the manual that corresponds to your MySQL server version
for the right syntax to use near 'bar` (baz int)' at line 1 at quoting.pl
line 14.
`foo\\`bar causes: DBD::mysql::db do failed: You have an error in your SQL
syntax. Check the manual that corresponds to your MySQL server version
for the right syntax to use near 'bar (baz int)' at line 1 at quoting.pl
line 14.
=
What am I missing?
A couple of other items I've remembered recently...
- I'd really like to see mysql_client_found_rows=1 being the default.
With mysql_client_found_rows off, normal application code can't
tell the difference between an update that failed to match a row
and one that matched a row but didn't actually change anything.
D'oh. I just wrote something that did that :( I think you just saved me
from having to track down a bug when I start testing that function. Thanks
Tim :)
It looks like the only question is one of backwards compatibility. Maybe
we throw a warning in the next version or two, and change after that?
Rudy.
Re: Maintaining DBD::mysql
At 11:50 -0500 3/24/03, Rudy Lippan wrote: On Mon, 24 Mar 2003, Paul DuBois wrote: For table names (which can't contain '.' etc) the server will generate an error if and when appropriate. The exact rules for MySQL identifiers are: Database and table names cannot contain '\', '/', or '.' characters. Column and index names can. What are the rules for escaping the quoting character? create table `thisthat` (foo int); -- confuses the mysql client. create table asdf (`foo'bnar` int); -- confuses mysql client. create table `asdf``asdf` (foo int); -- throws an error The third one now works in 4.1 and creates a table named asdf`asdf. The first two still fail. I'll ask the devs about it. And mysql create table asdf (`foo\`bnar` int); ERROR: Unknown command '\`'. ERROR 1064: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'bnar` int) This still fails in 4.1, too. I'll ask about it. There have been several improvements made to the mysql parser in 4.1, but you've identified some cases that still cause failure.
