diff options
| -rw-r--r-- | R/serenitasdb.R | 64 |
1 files changed, 35 insertions, 29 deletions
diff --git a/R/serenitasdb.R b/R/serenitasdb.R index 7dcfcd7d..071df331 100644 --- a/R/serenitasdb.R +++ b/R/serenitasdb.R @@ -1,11 +1,12 @@ -library(RPostgreSQL) -drv <- dbDriver("PostgreSQL") -serenitasdb <- dbConnect(drv, dbname="serenitasdb", user="serenitas_user", password="Serenitas1", +library(DBI) +serenitasdb <- dbConnect(RPostgres::Postgres(), + dbname="serenitasdb", + user = "serenitas_user", host="debian") nameToBasketID <- function(name, date=Sys.Date()){ - sqlstr <- "SELECT * from nametobasketid('%s', '%s')" - r <- dbGetQuery(serenitasdb, sprintf(sqlstr, name, date)) + sqlstr <- "SELECT * from nametobasketid($1, $2)" + r <- dbGetQuery(serenitasdb, sqlstr, params=list(name, date)) return(as.integer(r)) } @@ -16,8 +17,10 @@ set.index.desc <- function(index, date=Sys.Date()){ stop("need to pass a credit index") } id <- nameToBasketID(index$name, date) - sqlstr <- "SELECT indexfactor, cumulativeloss, maturity from index_desc where basketid=%s and tenor='%s'" - r <- as.list(dbGetQuery(serenitasdb, sprintf(sqlstr, id, index$tenor))) + sqlstr <- paste("SELECT indexfactor, cumulativeloss, maturity from index_desc", + "WHERE tenor=$1 and basketid=$2") + r <- as.list(dbGetQuery(serenitasdb, sqlstr, params=list(index$tenor, id))) + r$maturity <- as.Date(r$maturity) return(c(index, list(tradedate = date, factor=r$indexfactor/100, loss=r$cumulativeloss/100, maturity=r$maturity))) @@ -38,7 +41,8 @@ arr.convert <- function(arr){ } get.indexquotes <- function(indexname, date=Sys.Date()){ - r <- dbGetQuery(serenitasdb, sprintf("select * from curve_quotes('%s', '%s')", indexname, date)) + r <- dbGetQuery(serenitasdb, "SELECT * from curve_quotes($1, $2)", + params = list(indexname, date)) quotes <- list(tickers=r[,1], spread_curve = arr.convert(r$spread_curve), upfront_curve =arr.convert(r$upfront_curve), @@ -55,9 +59,10 @@ get.tranchequotes <- function(indexname, tenor='5yr', date=Sys.Date()){ temp <- indexsplit(indexname) ## first try the easy way: sqlstr <- paste("select * from tranche_quotes", - "where index='%s' and series=%s and quotedate::date='%s'", - "and tenor = '%s' order by attach asc") - r <- dbGetQuery(serenitasdb, sprintf(sqlstr, temp$index, temp$series, date, tenor)) + "where index=$1 and series=$2 and quotedate::date=$3", + "and tenor = $4 order by attach asc") + r <- dbGetQuery(serenitasdb, sqlstr, + params = list(temp$index, temp$series, date, tenor)) ## check if set is unique and complete ## hy9 and hy10 tranche is gone if(tolower(indexname) %in% c("hy9", "hy10")){ @@ -71,9 +76,10 @@ get.tranchequotes <- function(indexname, tenor='5yr', date=Sys.Date()){ ##else we work harder ##we get the list of distinct quotes sqlstr <- paste("select distinct quotesource, quotedate from tranche_quotes", - "where index='%s' and series=%s and quotedate::date='%s'", - "and tenor = '%s' order by quotedate desc") - distinct.quotes <- dbGetQuery(serenitasdb, sprintf(sqlstr, temp$index, temp$series, date, tenor)) + "where index=$1 and series=$2 and quotedate::date=$3", + "and tenor = $4 order by quotedate desc") + distinct.quotes <- dbGetQuery(serenitasdb, sqlstr, + params = list(temp$index, temp$series, date, tenor)) flag <- FALSE ##we loop through the disctinct quotes until we find a complete set if(nrow(distinct.quotes)==0){ @@ -81,17 +87,18 @@ get.tranchequotes <- function(indexname, tenor='5yr', date=Sys.Date()){ } for(i in 1:nrow(distinct.quotes)){ if(temp$index == 'HY' && temp$series>=15){ ##don't want the tranchelets quoted by CITI - sqlstr <- paste("select * from tranche_quotes where index='%s'", - "and series=%s and tenor = '%s' and quotedate='%s'", - "and detach-attach!=5 and quotesource='%s' order by attach asc") + sqlstr <- paste("select * from tranche_quotes where index=$1", + "and series=$2 and tenor = $3 and quotedate=$4", + "and detach-attach!=5 and quotesource=$5 order by attach asc") }else{ - sqlstr <- paste("select * from tranche_quotes where index='%s'", - "and series=%s and tenor = '%s' and quotedate='%s'", - "and quotesource='%s' order by attach asc") + sqlstr <- paste("select * from tranche_quotes where index=$1", + "and series=$2 and tenor = $3 and quotedate=$4", + "and quotesource=$5 order by attach asc") } - r <- dbGetQuery(serenitasdb, sprintf(sqlstr, temp$index, temp$series, tenor, - distinct.quotes$quotedate[i], - distinct.quotes$quotesource[i])) + r <- dbGetQuery(serenitasdb, sqlstr, + params = list(temp$index, temp$series, tenor, + distinct.quotes$quotedate[i], + distinct.quotes$quotesource[i])) if(all(c(r$attach, 100)==c(lower.attach, r$detach))){#set is complete flag <- TRUE break @@ -105,17 +112,16 @@ get.tranchequotes <- function(indexname, tenor='5yr', date=Sys.Date()){ couponfromindex <- function(indexname, tenor){ temp <- indexsplit(indexname) - sqlstr <- sprintf("select coupon from index_maturity where index='%s' and series=%s and tenor='%s'", - temp$index, temp$series, tenor) - r <- dbGetQuery(serenitasdb, sqlstr) + sqlstr <- "select coupon from index_maturity where index=$1 and series=$2 and tenor=$3" + r <- dbGetQuery(serenitasdb, sqlstr, params = list(temp$index, temp$series, tenor)) r$coupon } getlastdate <- function(indexname, tenor){ temp <- indexsplit(indexname) r <- dbGetQuery(serenitasdb, - sprintf(paste("SELECT max(date)+1 AS date FROM risk_numbers", - "WHERE index='%s' and series='%s' and tenor='%s'"), - temp$index, temp$series, tenor)) + paste("SELECT max(date)+1 AS date FROM risk_numbers", + "WHERE index=$1 and series=$2 and tenor=$3"), + params = list(temp$index, temp$series, tenor)) return(as.Date(r$date, format="%Y-%m-%d")) } |