make.db.names-methods
Make R/S-Plus identifiers into legal SQL identifiers
Produce legal SQL identifiers from a character vector.
Usage
make.db.names(dbObj, snames, unique=T, allow.keywords=T)
SQLKeywords(dbObj, ...)
isSQLKeyword(dbObj, name, keywords=.SQL92Keywords,
case=c("lower", "upper", "any")[3])
Arguments
- dbObj
- any DBI object (e.g.,
DBIDriver
). - snames
- a character vector of R/S-Plus identifiers (symbols) from which we need to make SQL identifiers.
- name
- a character vector of SQL identifiers we want to check against keywords from the DBMS.
- unique
- logical describing whether the resulting set of SQL names should
be unique. Its default is
TRUE
. Following the SQL 92 standard, uniqueness of SQL identifiers is determined regardless of whether letters are upper or lower case. - allow.keywords
- logical describing whether SQL keywords should be allowed in the
resulting set of SQL names. Its default is
TRUE
- keywords
- a character vector with SQL keywords, by default it's
.SQL92Keywords
defined in theDBI
package. - case
- a character string specifying whether to make the comparison
as lower case, upper case, or any of the two.
it defaults to
any
. - ...
- any other argument are passed to the driver implementation.
Details
The algorithm in make.db.names
first invokes make.names
and then replaces each occurrence of a dot ``.'' by an underscore
``_''. If allow.keywords
is FALSE
and identifiers
collide with SQL keywords, a small integer is appended to the
identifier in the form of "_n"
.
The set of SQL keywords is stored in the character
vector .SQL92Keywords
and reflects the SQL ANSI/ISO
standard as documented
in "X/Open SQL and RDA", 1994, ISBN 1-872630-68-8.
Users can easily override or update this vector.
Value
make.db.names
returns a character vector of legal SQL identifiers corresponding to itssnames
argument.SQLKeywords
returns a character vector of all known keywords for the database-engine associated withdbObj
.isSQLKeyword
returns a logical vector parallel toname
.
synopsis
make.db.names(dbObj, snames, ...) SQLKeywords(dbObj, ...) isSQLKeyword(dbObj, name, ...)
Bugs
The current mapping is not guaranteed to be fully reversible: some
SQL identifiers that get mapped into S identifiers with make.names
and then back to SQL with make.db.names
will not be equal to the original SQL identifiers (e.g., compound
SQL identifiers of the form username.tablename
will
loose the dot ``.'').
References
The set of SQL keywords is stored in the character vector
.SQL92Keywords
and reflects the SQL ANSI/ISO standard as
documented in "X/Open SQL and RDA", 1994, ISBN 1-872630-68-8.
Users can easily override or update this vector.
See the Database Interface definition document
DBI.pdf
in the base directory of this package
or
See Also
dbReadTable
,
dbWriteTable
,
dbExistsTable
,
dbRemoveTable
,
dbListTables
.
Examples
# This example shows how we could export a bunch of data.frames
# into tables on a remote database.
con <- dbConnect("Oracle", user="iptraffic", pass = pwd)
export <- c("trantime.email", "trantime.print", "round.trip.time.email")
tabs <- make.db.names(export, unique = T, allow.keywords = T)
for(i in seq(along = export) )
dbWriteTable(con, name = tabs[i], get(export[i]))
# Oracle's extensions to SQL keywords
oracle.keywords <- c("CLUSTER", "COLUMN", "MINUS", "DBNAME")
isSQLKeyword(nam, c(.SQL92Keywords, oracle.keywords))
[1] T T T F