Title: | Connection Class for Package stacomiR |
---|---|
Description: | S4 class wrappers for the 'ODBC' and Pool DBI connection, also provides some utilities to paste small datasets to clipboard, rename columns. It is used by the package 'stacomiR' for connections to the database. Development versions of 'stacomiR' are available in R-forge. |
Authors: | Cedric Briand [aut, cre], Marion Legrand [aut], Beaulaton Laurent [ctb] |
Maintainer: | Cedric Briand <[email protected]> |
License: | GPL (>= 2) |
Version: | 0.6.0.1 |
Built: | 2024-10-28 03:19:30 UTC |
Source: | https://github.com/cran/stacomirtools |
This function replaces the variable names in a data.frame
chnames(object, old_variable_name, new_variable_name)
chnames(object, old_variable_name, new_variable_name)
object |
a data frame |
old_variable_name |
a character vector with old variables names |
new_variable_name |
a character vector with new variables names |
object
Cedric Briand [email protected]
df <- data.frame("var1" = c("blue","red"), "var2" = c("nice","ugly")) colnames(df) # "var1" "var2" df <- chnames(object = df, old_variable_name = c("var1","var2"), "new_variable_name" = c("color","beauty")) colnames(df) # "color" "beauty" # the following will return an error, as the variable wrong_name is not in variable names ## Not run: chnames(object = df, old_variable_name = c("wrong_name"), "new_variable_name" = c("color"))) ## End(Not run)
df <- data.frame("var1" = c("blue","red"), "var2" = c("nice","ugly")) colnames(df) # "var1" "var2" df <- chnames(object = df, old_variable_name = c("var1","var2"), "new_variable_name" = c("color","beauty")) colnames(df) # "color" "beauty" # the following will return an error, as the variable wrong_name is not in variable names ## Not run: chnames(object = df, old_variable_name = c("wrong_name"), "new_variable_name" = c("color"))) ## End(Not run)
generic connect function for baseODBC
connect(object, ...)
connect(object, ...)
object |
an object |
... |
additional arguments passed on to the connect method |
connect method for ConnectionDB class
## S4 method for signature 'ConnectionDB' connect(object, base = NULL)
## S4 method for signature 'ConnectionDB' connect(object, base = NULL)
object |
An object of class ConnectionDB |
base |
a string with values for dbname, host, port, user, password, in this order. |
a connection with slot filled
Cedric Briand [email protected]
## Not run: object <- new("ConnectionDB") object@dbname <- c("bd_contmig_nat","test") object@host <- "localhost" object@port <- "5432" object@user <- "myuser" object@password <- "mypassword" object@silent=FALSE object <- connect(object) pool::dbGetInfo(object@connection) pool::poolClose(object@connection) ## End(Not run)
## Not run: object <- new("ConnectionDB") object@dbname <- c("bd_contmig_nat","test") object@host <- "localhost" object@port <- "5432" object@user <- "myuser" object@password <- "mypassword" object@silent=FALSE object <- connect(object) pool::dbGetInfo(object@connection) pool::poolClose(object@connection) ## End(Not run)
connect method for ConnectionODBC class
## S4 method for signature 'ConnectionODBC' connect(object)
## S4 method for signature 'ConnectionODBC' connect(object)
object |
an object of class ConnectionODBC |
a connection with slot filled
Cedric Briand [email protected]
##this wont be run as the user need to manually configure the connection before using it ## Not run: object=new("ConnectionODBC") object@baseODBC=c("myODBCconnection","myusername","mypassword") object@silent=FALSE object<-connect(object) odbcClose(object@connection) ## End(Not run)
##this wont be run as the user need to manually configure the connection before using it ## Not run: object=new("ConnectionODBC") object@baseODBC=c("myODBCconnection","myusername","mypassword") object@silent=FALSE object<-connect(object) odbcClose(object@connection) ## End(Not run)
connect method loads a request to the database and returns either an error or a data.frame
## S4 method for signature 'RequeteODBC' connect(object)
## S4 method for signature 'RequeteODBC' connect(object)
object |
an object of class RequeteODBC |
An object of class RequeteODBC
assign("showmerequest",1,envir=envir_stacomi) allows to print all queries passing on the class RequeteDB
Cedric Briand [email protected]
showClass("RequeteODBC") ## Not run: object=new("RequeteODBC") object@open=TRUE object@baseODBC=baseODBC object@sql= "select * from t_lot_lot limit 100" object<-connect(object) odbcClose(object@connection) odbcCloseAll() object=new("RequeteODBC") object@open=TRUE ## this will leave the connection open, ## by default it closes after the query is sent ## the following will work only if you have configured and ODBC link object@baseODBC=c("myODBCconnection","myusername","mypassword") object@sql= "select * from mytable limit 100" object<-connect(object) odbcClose(object@connection) envir_stacomi=new.env() ## While testing if you want to see the output of sometimes ## complex queries generated by the program assign("showmerequest",1,envir_stacomi) ## You can assign any values (here 1) ## just tests the existence of "showmerequest" in envir_stacomi object=new("RequeteODBC") object@baseODBC=c("myODBCconnection","myusername","mypassword") object@sql= "select * from mytable limit 100" object<-connect(object) ## the connection is already closed, the query is printed ## End(Not run)
showClass("RequeteODBC") ## Not run: object=new("RequeteODBC") object@open=TRUE object@baseODBC=baseODBC object@sql= "select * from t_lot_lot limit 100" object<-connect(object) odbcClose(object@connection) odbcCloseAll() object=new("RequeteODBC") object@open=TRUE ## this will leave the connection open, ## by default it closes after the query is sent ## the following will work only if you have configured and ODBC link object@baseODBC=c("myODBCconnection","myusername","mypassword") object@sql= "select * from mytable limit 100" object<-connect(object) odbcClose(object@connection) envir_stacomi=new.env() ## While testing if you want to see the output of sometimes ## complex queries generated by the program assign("showmerequest",1,envir_stacomi) ## You can assign any values (here 1) ## just tests the existence of "showmerequest" in envir_stacomi object=new("RequeteODBC") object@baseODBC=c("myODBCconnection","myusername","mypassword") object@sql= "select * from mytable limit 100" object<-connect(object) ## the connection is already closed, the query is printed ## End(Not run)
connect method loads a request to the database and returns either an error or a data.frame
## S4 method for signature 'RequeteODBCwhere' connect(object)
## S4 method for signature 'RequeteODBCwhere' connect(object)
object |
an object of class RequeteODBCwhere |
An object of class RequeteODBCwhere
method modified from v0.2.1240 to use the connect method of the mother class
Cedric Briand [email protected]
## Not run: object<-new("RequeteODBCwhere") object@baseODBC<-baseODBC object@sql<- "select * from t_lot_lot" object@where<-"WHERE lot_tax_code='2038'" object@and<-c("AND lot_std_code='CIV'","AND lot_ope_identifiant<1000") object@order_by<-"ORDER BY lot_identifiant" object<-connect(object) ## End(Not run)
## Not run: object<-new("RequeteODBCwhere") object@baseODBC<-baseODBC object@sql<- "select * from t_lot_lot" object@where<-"WHERE lot_tax_code='2038'" object@and<-c("AND lot_std_code='CIV'","AND lot_ope_identifiant<1000") object@order_by<-"ORDER BY lot_identifiant" object<-connect(object) ## End(Not run)
connect method loads a request to the database and returns either an error or a data.frame
## S4 method for signature 'RequeteODBCwheredate' connect(object)
## S4 method for signature 'RequeteODBCwheredate' connect(object)
object |
an object of class RequeteODBCwheredate |
An object of class RequeteODBCwheredate
method modified from v0.2.1240 to use the connect method of the mother class which in turn will use the method of the mother class
Cedric Briand [email protected]
## Not run: object<-new("RequeteODBCwheredate") object@baseODBC<-get("baseODBC",envir=envir_stacomi) object@select<- "select * from t_operation_ope" object@datedebut=strptime("1996-01-01 00:00:00",format="%Y-%m-%d %H:%M:%S") object@datefin=strptime("2000-01-01 00:00:00",format="%Y-%m-%d %H:%M:%S") object@colonnedebut="ope_date_debut" object@colonnefin="ope_date_fin" object@and<-c("AND ope_dic_identifiant=1","AND ope_dic_identifiant=2") object@order_by<-"ORDER BY ope_identifiant" object@silent=FALSE object<-connect(object) ## End(Not run)
## Not run: object<-new("RequeteODBCwheredate") object@baseODBC<-get("baseODBC",envir=envir_stacomi) object@select<- "select * from t_operation_ope" object@datedebut=strptime("1996-01-01 00:00:00",format="%Y-%m-%d %H:%M:%S") object@datefin=strptime("2000-01-01 00:00:00",format="%Y-%m-%d %H:%M:%S") object@colonnedebut="ope_date_debut" object@colonnefin="ope_date_fin" object@and<-c("AND ope_dic_identifiant=1","AND ope_dic_identifiant=2") object@order_by<-"ORDER BY ope_identifiant" object@silent=FALSE object<-connect(object) ## End(Not run)
ConnectionDB class
connection an S4 object of class connectionDB
dbname
name of the database, length 1
host
host default "localhost", length 1
port
port of the database default "5432", length 1
user
user of the database, length 1
password
password for the datatbase, length 1
silent
A "logical" stating if the program returns output to the user
status
Can be -1 or string
connection
Could be both string or S3
Mother class for connection, opens the connection but does not shut it
Cedric Briand [email protected]
##this wont be run as the user need to manually configure the connection before using it ## Not run: object <- new("ConnectionDB") object@dbname <- c("bd_contmig_nat","test") object@host <- "localhost" object@port <- "5432" object@user <- "myuser" object@password <- "mypassword" object@silent=FALSE object <- connect(object) pool::dbGetInfo(object@connection) pool::poolClose(object@connection) ## End(Not run)
##this wont be run as the user need to manually configure the connection before using it ## Not run: object <- new("ConnectionDB") object@dbname <- c("bd_contmig_nat","test") object@host <- "localhost" object@port <- "5432" object@user <- "myuser" object@password <- "mypassword" object@silent=FALSE object <- connect(object) pool::dbGetInfo(object@connection) pool::poolClose(object@connection) ## End(Not run)
ConnectionODBC class
connectionODBC an S4 object of class connectionODBC
baseODBC
"vector" (of length 3, character)
silent
"logical"
etat
"ANY" # can be -1 or string
connection
"ANY" # could be both string or S3
Mother class for connection, opens the connection but does not shut it
Cedric Briand [email protected]
##this wont be run as the user need to manually configure the connection before using it ## Not run: object=new("ConnectionODBC") object@baseODBC=c("myODBCconnection","myusername","mypassword") object@silent=FALSE object<-connect(object) odbcClose(object@connection) ## End(Not run)
##this wont be run as the user need to manually configure the connection before using it ## Not run: object=new("ConnectionODBC") object@baseODBC=c("myODBCconnection","myusername","mypassword") object@silent=FALSE object<-connect(object) odbcClose(object@connection) ## End(Not run)
ex fonction to write to the clipboard
ex(d = NULL)
ex(d = NULL)
d |
a dataframe |
Cedric Briand [email protected]
see xtable for further description, an xtable is created and printed to html format
funhtml( data, caption = NULL, top = TRUE, outfile = NULL, clipboard = FALSE, append = TRUE, digits = NULL, ... )
funhtml( data, caption = NULL, top = TRUE, outfile = NULL, clipboard = FALSE, append = TRUE, digits = NULL, ... )
data |
a data frame |
caption |
the caption |
top |
a logical, if true the caption is placed on top |
outfile |
the path to the file |
clipboard |
if clipboard TRUE, a copy to the clipboard is made |
append |
is the file appended to the previous one ? |
digits |
the number of digits |
... |
additional parameters to be passed to the function |
an xtable
Cedric Briand [email protected]
generic query function for
getquery(object, ...)
getquery(object, ...)
object |
an object |
... |
additional parameters passed to query |
getquery retreives the result of the query from the object
## S4 method for signature 'RequeteDB' getquery(object)
## S4 method for signature 'RequeteDB' getquery(object)
object |
an object of class RequeteDB |
A data frame
returns the index of values appearing only once in a vector : match(unique(a),a), replicated values are not returned on their second occurence
induk(a)
induk(a)
a |
a vector |
the index unique values within a vector
Cedric Briand [email protected]
induk(c(1,1,2,2,2,3))
induk(c(1,1,2,2,2,3))
is.even function modified from package sma (which did not verify that the entry was indeed an integer)
is.even(x)
is.even(x)
x |
integer |
a logical
Adapted from Henrik Bengtsson
is.even(1) is.even(2)
is.even(1) is.even(2)
id.odd function modified from package sma (which did not verify that the entry was indeed an integer)
is.odd(x)
is.odd(x)
x |
integer |
a logical
Adapted from Henrik Bengtsson
is.odd(1) is.odd(2)
is.odd(1) is.odd(2)
very usefull function used to "kill" the factors, noticeably after loading with 'ODBC'
killfactor(df)
killfactor(df)
df |
a data.frame |
df
Cedric Briand [email protected]
generic query function for
query(object, ...)
query(object, ...)
object |
an object |
... |
additional parameters passed to query |
query method loads a quert to the data and returns either an error or a data.frame
## S4 method for signature 'RequeteDB' query(object, ...)
## S4 method for signature 'RequeteDB' query(object, ...)
object |
an object of class RequeteDB |
... |
further arguments passed to the query method, base will be passed to ConnectionDB to set the connection parameters, it should be a vector with dbname host port user and password (in this order) |
An object of class RequeteDB
assign("showmerequest",1,envir=envir_stacomi) allows to print all queries passing on the class RequeteDB
Cedric Briand [email protected]
showClass("RequeteDB") ## Not run: # this connection require user and password and a working data object=new("RequeteDB",dbname ="bd_contmig_nat", host ="localhost", port="5432", user ="postgres", password="secret") ) object@open=TRUE ## this will leave the connection open, ## by default it closes after the query is sent ## the following will work only if you have configured and ODBC link object@sql= "select * from t_lot_lot limit 100" object<-query(object) envir_stacomi=new.env() ## While testing if you want to see the output of sometimes ## complex queries generated by the program assign("showmerequest",1,envir_stacomi) ## You can assign any values (here 1) ## just tests the existence of "showmerequest" in envir_stacomi object@sql= "select * from mytable limit 100" object<-connect(object) ## the connection is already closed, the query is printed ## End(Not run)
showClass("RequeteDB") ## Not run: # this connection require user and password and a working data object=new("RequeteDB",dbname ="bd_contmig_nat", host ="localhost", port="5432", user ="postgres", password="secret") ) object@open=TRUE ## this will leave the connection open, ## by default it closes after the query is sent ## the following will work only if you have configured and ODBC link object@sql= "select * from t_lot_lot limit 100" object<-query(object) envir_stacomi=new.env() ## While testing if you want to see the output of sometimes ## complex queries generated by the program assign("showmerequest",1,envir_stacomi) ## You can assign any values (here 1) ## just tests the existence of "showmerequest" in envir_stacomi object@sql= "select * from mytable limit 100" object<-connect(object) ## the connection is already closed, the query is printed ## End(Not run)
query method loads a request to the database and returns either an error or a data.frame
## S4 method for signature 'RequeteDBwhere' query(object, ...)
## S4 method for signature 'RequeteDBwhere' query(object, ...)
object |
an object of class RequeteDBwhere |
... |
further arguments passed to the query method, base will be passed to ConnectionDB to set the connection parameters, |
An object of class RequeteODBCwhere
Cedric Briand [email protected]
## Not run: object<-new("RequeteODBCwhere") base=c("bd_contmig_nat","localhost","5432","user", "password") object@sql<- "select * from t_lot_lot" object@where<-"WHERE lot_tax_code='2038'" object@and<-c("AND lot_std_code='CIV'","AND lot_ope_identifiant<1000") object@order_by<-"ORDER BY lot_identifiant" object <- connect(object, base) ## End(Not run)
## Not run: object<-new("RequeteODBCwhere") base=c("bd_contmig_nat","localhost","5432","user", "password") object@sql<- "select * from t_lot_lot" object@where<-"WHERE lot_tax_code='2038'" object@and<-c("AND lot_std_code='CIV'","AND lot_ope_identifiant<1000") object@order_by<-"ORDER BY lot_identifiant" object <- connect(object, base) ## End(Not run)
query method loads a request to the database and returns either an error or a data.frame
## S4 method for signature 'RequeteDBwheredate' query(object, ...)
## S4 method for signature 'RequeteDBwheredate' query(object, ...)
object |
an object of class RequeteDBwheredate |
... |
further arguments passed to the query method, base will be passed to ConnectionDB to set the connection parameters, |
An object of class RequeteDBwheredate
Cedric Briand [email protected]
## Not run: object<-new("RequeteDBwheredate") base=c("bd_contmig_nat","localhost","5432","user", "password") object@select<- "select * from t_operation_ope" object@datedebut=strptime("1996-01-01 00:00:00",format="%Y-%m-%d %H:%M:%S") object@datefin=strptime("2000-01-01 00:00:00",format="%Y-%m-%d %H:%M:%S") object@colonnedebut="ope_date_debut" object@colonnefin="ope_date_fin" object@and<-c("AND ope_dic_identifiant=1","AND ope_dic_identifiant=2") object@order_by<-"ORDER BY ope_identifiant" object@silent=FALSE object<-connect(object, base) ## End(Not run)
## Not run: object<-new("RequeteDBwheredate") base=c("bd_contmig_nat","localhost","5432","user", "password") object@select<- "select * from t_operation_ope" object@datedebut=strptime("1996-01-01 00:00:00",format="%Y-%m-%d %H:%M:%S") object@datefin=strptime("2000-01-01 00:00:00",format="%Y-%m-%d %H:%M:%S") object@colonnedebut="ope_date_debut" object@colonnefin="ope_date_fin" object@and<-c("AND ope_dic_identifiant=1","AND ope_dic_identifiant=2") object@order_by<-"ORDER BY ope_identifiant" object@silent=FALSE object<-connect(object, base) ## End(Not run)
RequeteDB class
sql
"character"
query
"data.frame"
open
logical is the connection left open after the request ?
Inherits from ConnectionDB
Cedric Briand [email protected]
object=new("RequeteDB")
object=new("RequeteDB")
RequeteDBwhere class
select
"character"
where
"character"
and
"vector"
order_by
"character"
Inherits from RequeteDB the syntax is where="WHERE ..." and =vector("AND...","AND...") order_by="ORDER BY.."
Cedric Briand [email protected]
object=new("RequeteDBwhere")
object=new("RequeteDBwhere")
RequeteDBwhere class
datedebut
"POSIXlt"
datefin
"POSIXlt"
colonnedebut
"character" # name of the column containing datedebut
colonnefin
"character" # name of the column containing datefin
Inherits from RequeteDBwhere and uses its connect method with a new SetAs
object=new("RequeteDBwhere")
object=new("RequeteDBwhere")
RequeteODBC class
sql
"character"
query
"data.frame"
open
logical is the connection left open after the request ?
Inherits from ConnectionODBC
Cedric Briand [email protected]
object=new("RequeteODBC")
object=new("RequeteODBC")
RequeteODBCwhere class
select
"character"
where
"character"
and
"vector"
order_by
"character"
Inherits from RequeteODBC the syntax is where="WHERE ..." and =vector("AND...","AND...") order_by="ORDER BY.."
Cedric Briand [email protected]
object=new("RequeteODBCwhere")
object=new("RequeteODBCwhere")
RequeteODBCwhere class
datedebut
"POSIXlt"
datefin
"POSIXlt"
colonnedebut
"character" # name of the column containing datedebut
colonnefin
"character" # name of the column containing datefin
Inherits from RequeteODBCwhere and uses its connect method with a new SetAs
object=new("RequeteODBCwhere")
object=new("RequeteODBCwhere")
Function to transform a ftable into dataframe but just keeping the counts works with ftable of dim 2
tab2df(tab)
tab2df(tab)
tab |
a flat table |
Cedric Briand [email protected]
df <- data.frame("var1" = c("blue","red"), "var2" = c("nice","ugly")) ftdf <- ftable(df) tab2df(ftdf)
df <- data.frame("var1" = c("blue","red"), "var2" = c("nice","ugly")) ftdf <- ftable(df) tab2df(ftdf)
validity function for ConnectionDB class
validity_DB(object)
validity_DB(object)
object |
An object of class ConnectionDB |
Validity method for ODBC class
validity_ODBC(object)
validity_ODBC(object)
object |
an object of class ConnectionODBC |