.dbAddData <- function(conn, sql, bind.data)
{
    dbBeginTransaction(conn)
    dbGetPreparedQuery(conn, sql, bind.data)
    dbCommit(conn)
}

.loadPROVEAN <- function(file)
{
    dat <- read.delim(file, header=TRUE)
    ## Convert strand to character
    strand <- dat$STRAND 
    strand[strand == -1] <- "-"
    strand[strand == 1] <- "+"
    dat$STRAND <- strand
    dat
}
 
.writeMetadata <- function(conn)
{
    rsqlite_version <- installed.packages()['RSQLite', 'Version']
    mat <- 
        matrix(c("Db type", "PROVEANDb",
                 "Data source", "PROVEAN/SIFT",
                 "Genome", "hg19",
                 "Genus and Species", "Homo sapiens",
                 "Resource URL", "http://provean.jcvi.org/index.php",
                 "dbSNP build", "137",
                 "Creation time", svn.time(),
                 "RSQLite version at creation time",
                 installed.packages()['RSQLite', 'Version'],
                 "package", "VariantAnnotation"), ncol=2, byrow=TRUE)
    colnames(mat) <- c("name", "value")
    metadata <- data.frame(name=mat[ , "name"], value=mat[ , "value"],
                           stringsAsFactors=FALSE)
    dbWriteTable(conn, "metadata", metadata, row.names=FALSE)
    cat("done with 'metadata' table \n")
}

.writePROVEAN <- function(conn, file)
{
    dat <- .loadPROVEAN(file)
    sql <- c(
        "CREATE TABLE proveandata (\n",
        " DBSNPID TEXT NOT NULL,\n",
        " VARIANT TEXT,\n",
        " PROTEINID TEXT,\n",
        " LENGTH INTEGER,\n",
        " STRAND TEXT,\n",
        " CODONCHANGE TEXT,\n",
        " POS INTEGER,\n",
        " RESIDUEREF TEXT,\n",
        " RESIDUEALT TEXT,\n",
        " TYPE TEXT,\n",
        " PROVEANSCORE REAL,\n",
        " PROVEANPRED TEXT,\n",
        " PROVEANNUMSEQ INTEGER,\n",
        " PROVEANNUMCLUST TEXT,\n",
        " SIFTSCORE REAL,\n",
        " SIFTPRED TEXT,\n",
        " SIFTMEDIAN REAL,\n",
        " SIFTNUMSEQ INTEGER",
        ")")
    dbGetQuery(conn, paste(sql, collapse=""))
    dbGetQuery(conn, "CREATE INDEX dbsnpid_index on proveandata (DBSNPID)")
 
    qmarks <- paste(rep("?", ncol(dat)), collapse=",")
    sql <- paste("INSERT INTO proveandata VALUES (", qmarks, ")", sep="") 
    .dbAddData(conn, sql, dat)
    cat("done with 'proveandata' table \n")
}

makePROVEANDb <- function(filepath, savepath, dbfile, dbsnpfile, ...) 
{
    if (length(list(...)) != 0L)
        warning("extra args are ignored for now")

    conn <- dbConnect(SQLite(), dbname=dbfile)
    .writePROVEAN(conn, dbsnpfile)
    .writeMetadata(conn)
    dbDisconnect(conn)
    cat(paste("creation of ", basename(dbfile), " complete \n", sep=""))
}

