Migrate Jasper XML to Sqlite3

converter.rb

require 'nokogiri'
require 'sqlite3'

class Converter

  def initialize(file, database_name = "flashcards")
    @file = file
    @database_name = database_name
    @database = open_database()
  end

  def convert
    open_database()
    create_tables()
    import()
  end

  def open_database
    SQLite3::Database.open("#{@database_name}.sqlite")
  end

  def create_tables
    # Users Table
    sql = "DROP TABLE IF EXISTS users"
    @database.execute(sql)

    sql = 'CREATE TABLE users (id INTEGER PRIMARY KEY AUTOINCREMENT, uid VARCHAR(255), email VARCHAR(255), created_at DATETIME, updated_at DATETIME)'
    @database.execute(sql)

    # Decks Table
    sql = "DROP TABLE IF EXISTS decks"
    @database.execute(sql)

    sql = 'CREATE TABLE decks (id INTEGER PRIMARY KEY AUTOINCREMENT, uid VARCHAR(255), user_uid VARCHAR(255), total_known_cards INTEGER, total_unknown_cards INTEGER, total_skipped_cards INTEGER, total_cards INTEGER, status VARCHAR(100), created_at DATETIME, updated_at DATETIME)'
    @database.execute(sql)    

    # Deck Flashcards Status Table
    sql = "DROP TABLE IF EXISTS deck_flashcard_status"
    @database.execute(sql)

    sql = 'CREATE TABLE deck_flashcard_status (id INTEGER PRIMARY KEY AUTOINCREMENT, deck_uid VARCHAR(255), flashcard_uid VARCHAR(255), category_uid VARCHAR(255), status VARCHAR(100), created_at DATETIME, updated_at DATETIME)'
    @database.execute(sql)    


    # Flashcards Table
    sql = "DROP TABLE IF EXISTS flashcards"
    @database.execute(sql)

    sql = 'CREATE TABLE flashcards (id INTEGER PRIMARY KEY AUTOINCREMENT, uid VARCHAR(255), category_uid VARCHAR(255), question TEXT, answer TEXT, status VARCHAR(20) DEFAULT "UNANSWERED", created_at DATETIME, updated_at DATETIME)'
    @database.execute(sql)    

    # Categories Table
    sql = "DROP TABLE IF EXISTS categories"
    @database.execute(sql)

    sql = 'CREATE TABLE categories (id INTEGER PRIMARY KEY AUTOINCREMENT, uid VARCHAR(255), title VARCHAR(255), created_at DATETIME, updated_at DATETIME)'
    @database.execute(sql)        
  end


  def import
    # Read XML
    input = Nokogiri::XML(@file)
    cards = input.root.xpath("//card")
    categories = Hash.new

    cards.each do |card|
      uid = card['id'].gsub("test", "")   
      type = card.xpath('type').children.text
      category = card.xpath('topic').children.text
      category_uid = slugfy(category)
      categories[category_uid] = category unless categories.include?(category_uid)
      # subtopic = card.xpath('subtopic').children.text
      question = clean_html_field(card.xpath('question').children.to_html)
      answer = clean_html_field(card.xpath('answer').children.to_html)
      
      sql = "INSERT INTO flashcards ('uid', 'category_uid', 'question', 'answer', 'created_at', 'updated_at') VALUES ('#{uid}', '#{category_uid}', '#{question}', '#{answer}', date('now'), date('now'))"
      @database.execute(sql)      
      puts "#{uid}: inserted"
      # puts "#{sql};"
    end

    categories.each do |k,v|
      sql = "INSERT INTO categories ('uid', 'title', 'created_at', 'updated_at') VALUES ('#{k}', '#{v}', date('now'), date('now'))"
      @database.execute(sql)
      puts "#{k}: inserted"
      # puts "#{sql};"
    end

    sql = "INSERT INTO users ('uid', 'email', 'created_at', 'updated_at') VALUES ('guest', 'guest@kaplan.com', date('now'), date('now'))"
    @database.execute(sql)
    puts "guest user: inserted"
    # puts "#{sql};"
  end

  def clean_html_field(html)
    html.gsub("\t", "").gsub("\n", "").gsub(/\s\s+/, "").gsub("'", "’").gsub("`", "‘")
  end

  def slugfy(value)
    # Perform transliteration to replace non-ascii characters with an ascii
    # character
    value = value.gsub(/[^\x00-\x7F]/n, '').to_s
    
    # Remove single quotes from input
    value.gsub!(/[']+/, '')

    # Replace any non-word character (\W) with a space
    value.gsub!(/\W+/, ' ')
    
    # Remove any whitespace before and after the string
    value.strip!
    
    # All characters should be downcased
    value.downcase!
    
    # Replace spaces with dashes
    value.gsub!(' ', '-')
    
    # Return the resulting slug    
    value
  end

end