.matchSpecies <- function(genome) { # {{{
  require(GenomicFeatures)
  GenomicFeatures:::.matchUCSCGenomeToSpecies(genome)
} # }}} 

.writeGrAsTable <- function(conn, GR, tableName) { # {{{
  
  table <- as(unique(GR), 'data.frame')
  names(table) <- gsub('^seqnames$', 'chrom', names(table))
  names(table) <- gsub('^end$', 'chromEnd', names(table))
  names(table) <- gsub('^start$', 'chromStart', names(table))
  table$width <- NULL
  table$name <- rownames(table)
  names(table) <- make.db.names(conn, names(table))
  seqCols <- c( which(names(table) == 'chrom'),
                which(names(table) == 'strand'),
                which(names(table) == 'chromStart'),
                which(names(table) == 'chromEnd') )
  otherCols <- setdiff(seq_along(names(table)), seqCols)
  colOrder <- c(seqCols, otherCols)
  colNames <- names(table)[ otherCols ]
  table <- table[ , colOrder ]
 
  sql1 <- c("CREATE TABLE ",tableName," (\n",
            "  chrom TEXT NOT NULL,\n",
            "  strand TEXT NOT NULL,\n",
            "  chromStart INTEGER NOT NULL,\n",
            "  chromEnd INTEGER NOT NULL,\n") ## always a comma = never done

  ## Add remaining rows (b/c there will ALWAYS be at least one "other" field)
  sql2 <- paste("  ", colNames, " TEXT, ")
  sql <- c(sql1, sql2,")")
  ## remove final comma
  sql[length(sql)-1] <- sub(",","",sql[length(sql)-1])
  GenomicFeatures:::dbEasyQuery(conn, paste(sql, collapse=""))
  ## Fill the  table.
  sqlVals <- paste("$", colNames, ",", sep="")
  sqlVals[length(sqlVals)] <- sub(",","",sqlVals[length(sqlVals)])
  sql <- paste(c("INSERT INTO ",tableName,
               " VALUES ($chrom,$strand,$chromStart,$chromEnd,",
               sqlVals,")"), collapse="")
  GenomicFeatures:::dbEasyPreparedQuery(conn, sql, table)
} # }}}

.writeGrMetadataTable <- function(conn, metadata, tableName) { # {{{
    data_nrow <- GenomicFeatures:::dbEasyQuery(conn, 
                                               paste("SELECT COUNT(*) FROM ",
                                                     tableName,
                                                     collapse=""))[[1L]]    
    thispkg_version <- installed.packages()['GenomicFeatures', 'Version']
    rsqlite_version <- installed.packages()['RSQLite', 'Version']
    mat <- matrix(c(
        GenomicFeatures:::DB_TYPE_NAME, "FeatureDb",
        "Supporting package", "GenomicFeatures",
        "data_nrow", data_nrow,
        "Db created by", "GenomicFeatures package from Bioconductor",
        "Creation time", svn.time(),
        "GenomicFeatures version at creation time", thispkg_version,
        "RSQLite version at creation time", rsqlite_version,
        "DBSCHEMAVERSION", GenomicFeatures:::DB_SCHEMA_VERSION),
        ncol=2, byrow=TRUE
    )
    colnames(mat) <- c("name", "value")
    metadata <- rbind(data.frame(name=mat[ , "name"], value=mat[ , "value"],
                                 stringsAsFactors=FALSE),
                      metadata)
    dbWriteTable(conn, "metadata", metadata, row.names=FALSE)
} # }}}

prepareGrMetadata <- function(GR, URL='none', src=NULL, label=NULL, genus.and.species=NULL, ...){#{{{

  ## GR must have exactly one genome()
  if(any(is.na(genome(GR)))) { # {{{ genome(GR) must not be NA
    stop('You must add a genome() for your GRanges')
  } # }}}
  if(length(unique(genome(GR)))>1) { # {{{ genome(GR) must be consistent 
    stop('Your GRanges object must have exactly one genome()')
  } # }}}

  message("Preparing the 'metadata' data frame ... ", appendLF=FALSE)
  g <- unique(genome(GR))
  if(!exists('genus.and.species')) genus.and.species <- .matchSpecies(g)
  metadata <- data.frame(
        name=c("Data source", "Genome", "Resource", "Genus and Species", "URL"),
        value=c(src, g, label, genus.and.species, URL)
  )
  message("OK")
  metadata

} # }}}

makeFeatureDbFromGR <- function(GR, tableName, metadata, ...) { # {{{
  conn <- dbConnect(SQLite(), dbname="")
  .writeGrAsTable(conn, GR, tableName)
  .writeGrMetadataTable(conn, metadata, tableName)  # must come last!
  GenomicFeatures:::FeatureDb(conn) 
} # }}}

GenomicRangesToFeatureDb <- function(GR, URL='none', tableName=NULL, src=NULL, label=NULL, genus.and.species=NULL, ...){ # {{{

  require(RSQLite)
  require(GenomicFeatures)

  if(is.null(label)) label = as.character(match.call()["GR"])
  if(is.null(src)) src = as.character(match.call()["GR"])
  if(is.null(tableName)) tableName = as.character(match.call()["GR"])
  metadata <- prepareGrMetadata(GR, URL, src, label, genus.and.species, ...)
  makeFeatureDbFromGR(GR, tableName, metadata, ...)

} # }}}
