[R-sig-DB] Proposal of dbQuoteIdentifier for DBI specification

[Tomoaki NISHIYAMA]

Hi,

Thanks for comments.

> However, I don't like the semantics you have
> proposed for the behavior of dbQuoteIdentifier in terms of the return
> value for vectors based on length.  Such a function should do what it
> says, quote identifiers.  Having a separate function to deal with
> dbname, schema, and tablename concatenation might be useful, but that
> feels like something to handle separately.

Ok, that was just following Perl example. But, we can give different
name like dbConstructTableReference(name) for a polymorphic one.

The utilization is constuction of SQL from name object that is used to
create the table through dbWriteTable(con, name, dataframe)
like paste('SELECT * from', dbConstructTableReference(con, name),  
'LIMIT 10')
dbQuoteIdentifier may still have value for constructing quoted column  
name.

>> dbWriteTable(), dbExistsTable(), dbReadTable(), and dbRemoveTable()

Have just a single name argument, and its difficult to unambiguously
determine the schema if the user wants to specify a schema or the
table name contains periods (which is allowed in PostgreSQL but not  
allowed in MySQL).
So, in fact, I intend to extend these functions to work with vector
for the name argument to specify a set like c('database', 'schema',  
'table').

Surely, there could a different decision to make additional arguments  
like
dbWriteTable(con, name, dataframe, schema='schema', database='dbname')
and
dbConstructTableReference(con, name, schema='schema', database='dbname')

I'm more than happy if you have a better name.
I like it be shorter.  But, the tradition here seems to write in full.

> Care should be taken so that extra layers of
> function calls are not added to every db operation to determine and
> apply quoting (IMO).


I think that the call for table name construction function
internally to dbWriteTable etc. will not significantly impact the  
performance,
as the number executed is much lower compared with actual INSERT/ 
RETRIEVE
operation.
So, I think user should not required to explicitly write these
function when calling dbWriteTable, but better be done automatically
by the library.

For dbSendQuery and dbGetQeury, these function do not automatically  
apply,
and if the programmer can write the name directly, it should work
without the overhead for the quoting.  But, if its constructing from a
variable the content is determined at runtime, perhaps its best
to call the quoting function to ensure that the SQL is not malformed
by any special character within the variable.

-- 
Tomoaki NISHIYAMA

Advanced Science Research Center,
Kanazawa University,
13-1 Takara-machi,
Kanazawa, 920-0934, Japan