[R-sig-DB] Proposal of dbQuoteIdentifier for DBI specification
tomoakin at kenroku.kanazawa-u.ac.jp
Sat Nov 13 06:31:18 CET 2010
We changed that the SQL identifiers be quoted instead of mangling in
RPostgreSQL. This allows proper distinction of capital and small
and make it possible to use the name as is what is defined in R world.
This was applied uniformly to tablename argument for
dbWriteTable(), dbExistsTable(), dbReadTable(), and dbRemoveTable()
However, this makes difficulty in schema usage, as now '.' (period)
is allowed as a part of tablename and cannot be regarded as a
separator. To allow construction of schema.table structure like
"s.che.ma"."ta.ble", passing a vector c('s.che.ma', 'ta.ble')
instead of string will be perhaps helpful.
Each element of the vector will be quoted and thereafter connected
I am aware that RSQLite had similar discussion for mangling / quoting
about one year ago
but haven't found what the decision and what the future direction is.
In addition while identifier quoting for Postgresql is simple,
that looks different in MySQL that it is quote with back quote'`'
rather than double quote and double quote works only under ANSI_QUOTES
option. So, if quoting rule is different among backends,
perhaps it is worth to define dbQuoteIdentifier(con, name)
as a common way to quote an identifier.
Perl DBI has quote_identifier and have two forms of synopsis
$sql = $dbh->quote_identifier( $name );
$sql = $dbh->quote_identifier( $catalog, $schema, $table, \%attr );
The example given there is interesting that
$id = $dbh->quote_identifier( 'link', 'schema', 'table' );
should produce "schema"."table"@"link" for oracle.
So, my proposal is that dbQuoteIdentifier() is defined similary that
with a single string argument that should be quoted.
For a vector argument two multiple strings, the two strings is
as schema and tablename, and each element are quoted and concatenated
to the backend specification.
For a vector argument with three elements, the elements are
dbname, schema, and tablename. Each element are quoted and
the rule appropriate for the back-end database management system.
I think quote everything is the right way to go,
and quoting should be made easier.
Advanced Science Research Center,
Kanazawa, 920-0934, Japan
More information about the R-sig-DB