comparison create_sqlite_db.R @ 20:172f0ad5c889 draft

planemo upload for repository https://github.com/computational-metabolomics/mspurity-galaxy commit 04023134d8f28e85927ca293373c506484149ead-dirty

19:deea5e9205d9

add_extra_table_elucidation <- function(name, pth, db_con, filter_Score=NA, filter_Rank=NA){
    if (is.null(pth)){
        return(0)
    }
    index <- 0
    chunkSize <- 5000
    print(pth)
    con <- file(description=pth,open="r")   
    df <- read.table(con, nrows=chunkSize,  header = TRUE, sep='\t', stringsAsFactors = FALSE,  comment.char = "")
    headers = colnames(df)
    print(head(df))
    write_to_table(df, db_con, name, FALSE, filter_Score, filter_Rank)

    repeat {
        index <- index + 1
        print(paste('Processing rows:', index * chunkSize))
 
        if (nrow(df) != chunkSize){
                print('Processed all files!')
                break
        }
       
        df <- read.table(con, nrows=chunkSize, skip=0, header = FALSE, sep='\t', stringsAsFactors = FALSE,  comment.char = "")
        colnames(df) <- headers


	write_to_table(df, db_con, name, TRUE, filter_Score, filter_Rank)

 
        break
    }
   close(con)

}

write_to_table <- function(df, db_con, name, append, filter_Score, filter_Rank){

       df <- df[!df$UID=='UID',]
        print(filter_Score)
        print(filter_Rank)
        print('filter rank and score')

        if (!is.na(filter_Score)){
           df <- df[df$Score>=filter_Score,]
        }      

        if (!is.na(filter_Rank)){
      
            df <- df[df$Rank<=filter_Rank,]
        }

        # get peakid, an scan id
        df_ids <- stringr::str_split_fixed(df$UID, '-', 3)
        colnames(df_ids) <- c('grp_id', 'file_id', 'pid')
        df <- cbind(df_ids, df)

add_extra_table_elucidation('metfrag_results', opt$metfrag_result, con, filter_Score=0.6, filter_Rank=NA)
add_extra_table_elucidation('sirius_csifingerid_results', opt$sirius_csifingerid_result, con, filter_Score=NA, filter_Rank=5)
add_probmetab(opt$probmetab_result, con)




cmd <- paste('SELECT cpg.grpid, cpg.mz, cpg.mzmin, cpg.mzmax, cpg.rt, cpg.rtmin, cpg.rtmax, c_peaks.cid, ',
             'c_peaks.mzmin AS c_peak_mzmin, c_peaks.mzmax AS c_peak_mzmax, ',
             'c_peaks.rtmin AS c_peak_rtmin, c_peaks.rtmax AS c_peak_rtmax, s_peak_meta.*, fileinfo.filename, fileinfo.nm_save ',
             'FROM c_peak_groups AS cpg ',

20:172f0ad5c889

add_extra_table_elucidation <- function(name, pth, db_con, filter_Score=NA, filter_Rank=NA){
    if (is.null(pth)){
        return(0)
    }
    DBI::dbWriteTable(conn=db_con, name=name, value=pth, sep='\t', header=T)


}

write_to_table <- function(df, db_con, name, append){

       df <- df[!df$UID=='UID',]
        print(filter_Score)
        print(filter_Rank)
        print('filter rank and score')


        # get peakid, an scan id
        df_ids <- stringr::str_split_fixed(df$UID, '-', 3)
        colnames(df_ids) <- c('grp_id', 'file_id', 'pid')
        df <- cbind(df_ids, df)

add_extra_table_elucidation('metfrag_results', opt$metfrag_result, con, filter_Score=0.6, filter_Rank=NA)
add_extra_table_elucidation('sirius_csifingerid_results', opt$sirius_csifingerid_result, con, filter_Score=NA, filter_Rank=5)
add_probmetab(opt$probmetab_result, con)

if (is.null(xset)){
  DBI::dbWriteTable(con, name='xset_classes', value=xa@xcmsSet@phenoData, row.names=TRUE)
}else{

  DBI::dbWriteTable(con, name='xset_classes', value=xset@phenoData, row.names=TRUE)

}

cmd <- paste('SELECT cpg.grpid, cpg.mz, cpg.mzmin, cpg.mzmax, cpg.rt, cpg.rtmin, cpg.rtmax, c_peaks.cid, ',
             'c_peaks.mzmin AS c_peak_mzmin, c_peaks.mzmax AS c_peak_mzmax, ',
             'c_peaks.rtmin AS c_peak_rtmin, c_peaks.rtmax AS c_peak_rtmax, s_peak_meta.*, fileinfo.filename, fileinfo.nm_save ',
             'FROM c_peak_groups AS cpg ',