R/import_md_test.R

In Gabegit/gmExam: create a testbank from markdown files, make test paper

#' import markdown files into sqlite database
#' @description default dir: mddir = "/Users/gabe/liguomin.teaching/Testbanks/medMicro-md-bank/cn/"
#' 如果是单个文件，isdir=0,如果要导入很多文件，isdir=1，default。
#' @author Gabe Lee
#' @param mdDir: markdown file dir
#' @return importing
#' @export
#' @examples
#'
#' #import_md_test()
#' #import_md_test('31.micro_utility-sqrtxy-base-en.md',isdir=0)
#' #dirs = c("37.micro_essay-wefare-therom-cn.md",
#' # "36.micro_essay-general-equlibrium-cn.md",
#' # "35.micro_producion-cost-en.md",
#' # "34.micro_utility-min-max-en.md",
#' # "33.micro_utiltiy-max-en.md",
#' # "32.micro_utility-tom-food-en.md")
#' # import_md_test(dirs,isdir = 0)
#' # }

import_md_test <- function(mdfile=mddir,isdir=1){
	mddir = "/Users/gabe/liguomin.teaching/Testbanks/medMicro-md-bank/cn/"

	# This connection creates an empty database if it does not exist
	db <- RSQLite::dbConnect(RSQLite::SQLite(), dbname = "/Users/gabe/liguomin.teaching/Testbanks/testbankmd.sqlite")
	RSQLite::initExtension(db)

	# please uncomment below line if no testbankmd.sqlite
	##dbSendQuery(conn = db, "CREATE TABLE testbank(mdid text, title text unique, chapter text,type text,subject text, content text,examyear text default '0',adddate DATETIME('NOW','localtime') DEFAULT CURRENT_TIMESTAMP)")

	# Lookup By Index
	##dbSendQuery(conn = db, "CREATE INDEX idx1 ON testbank(title,subject,chapter)")


	# create trigger
	## add a column
	  # RSQLite::dbSendQuery(conn = db,"ALTER TABLE testbank ADD COLUMN updatetime DATE")

	## RSQLite::dbSendQuery(conn = db,"create trigger testbank_update_trg after update on testbank
# 		begin
# 		  update testbank set updatetime = datetime('NOW','localtime') where rowid = new.rowid;
# 	  end")


	if (isdir==1) {
		# loop all md files
		mds = list.files(path = mddir,include.dirs = TRUE, pattern = ".md$")

	}
	  else mds = mdfile #直接输入要导入的文件名

	insert_md <- function(md){
#      md = "29.micro_glossary-profit-cn.md"
#      md = "3.micro_competition-ls-q-1500-50p-cn.md"
  #  md = "1.micro_competition-100-firm-cn.md"
		subject <- sub("[0-9]+\\.(\\w+)_.+","\\1", md)
		chapter <- sub(".+_(\\w+?)\\-.+","\\1",md)
		title <- sub(".+_(\\w+.+?)\\.md","\\1",md)
		type <- sub(".+\\-(\\w{2,})\\.md","\\1",md)
		mdid <- sub("([0-9]+)\\.\\w+_.+","\\1", md)

		 subject;chapter;title;type;mdid



		#Import text file as single character string
		mddir = "/Users/gabe/liguomin.teaching/Testbanks/medMicro-md-bank/cn/"
		md_file <- paste0(mddir,md)
		cont <- readChar(md_file,nchars=1e6)

		#  将字段值中的单引号改为两个单引号，否则会出错
		contt <- gsub("'","''",cont)
		#cont

		val <- paste("'",mdid,"','",title,"','",chapter,"','",type,"','",subject,"','",contt,"'",sep = "")

		RSQLite::dbSendQuery(conn = db, paste('insert or replace into testbank(mdid,title,chapter,type,subject,content) values(',val,")",sep = ""))

	}

	lapply(mds, insert_md)

	## use the built in replace() function to perform a string replace in a query.
	RSQLite::dbSendQuery(conn = db,"UPDATE testbank SET examyear= replace(examyear,'0,','')")


}