YdLuRbMW

1require 'ruby-cheerio'
2require 'mysql'
3
4class MigrationDB
5
6	def initialize domain, user_name, password, db_name
7		begin
8			@db_connection = Mysql.new(domain, user_name, password)
9			@db_name = db_name
10		rescue Exception => e
11			p "Error connecting to DB Server.."
12		end
13		create_and_use_db
14	end
15
16	def create_table table_name, columns
17		execute_query do
18			"CREATE TABLE IF NOT EXISTS #{table_name}(`#{columns.join('` TEXT,`')}` TEXT)"
19		end
20	end
21
22	def update_table table_name, key, value, id_key, id_value
23		execute_query do
24			"UPDATE #{table_name} SET `#{key}` = \"#{escape_string(value)}\""
25		end
26	end
27
28	def insert_table table_name, i_hash
29		execute_query do
30			"INSERT #{table_name}(`#{i_hash.keys.join('`,`')}`) VALUES(\"#{i_hash.values.map{|p| escape_string(p)}.join('","')}\")"
31		end
32	end
33
34	def create_and_insert_table table_name, a_hash
35		column_names = a_hash.map{|h| h.keys}.flatten.uniq
36		create_table table_name, column_names
37		a_hash.each do |i_hash|
38			insert_table table_name, i_hash  
39		end
40	end
41
42	private
43
44	def create_and_use_db
45		execute_query do
46			"CREATE DATABASE IF NOT EXISTS #{@db_name}"
47		end
48
49		execute_query do
50			"USE #{@db_name}"
51		end
52	end
53
54	def escape_string str
55		str.gsub!(/\"/,'\'') if str.include? '"'
56		str.gsub!(/\'/,'\'') if str.include? "'"
57		str
58	end
59
60	def execute_query
61		begin
62			@db_connection.query(yield) if block_given?
63		rescue Exception => e
64			p e
65		end
66	end
67
68end
69
70
71# change the customer name, DB details here.
72$customer_name = "testing"
73$connection = MigrationDB.new "localhost", "root", "", $customer_name
74
75def merge_read_files directory_path
76	files = Dir[File.join("#{directory_path}","*.xml")]
77	r_file = ""
78	files.each do |file|
79		r_file = r_file + ( File.read file ).gsub('<?xml version="1.0" encoding="UTF-8"?>','')
80	end
81	"<file>"+r_file+"</file>"
82end
83
84def get_solutions directory_path
85	doc = RubyCheerio.new(merge_read_files directory_path)
86	categories = Array.new
87	folders = Array.new
88	articles = Array.new
89	doc.find('solution-category').each do |scategory|
90		categories << { created_at: scategory.find('created-at')[0].text, id: scategory.find('id')[0].text, updated_at: scategory.find('updated-at')[0].text, description: scategory.find('description')[0].text, name: scategory.find('name')[0].text } 
91		solution_folders = scategory.find('solution-folder')
92		solution_folders.each do |sfolder|
93			folders << { id: sfolder.find('id')[0].text, category_id: sfolder.find('category-id')[0].text, description: sfolder.find('description')[0].text, name: sfolder.find('name')[0].text, updated_at: sfolder.find('updated-at')[0].text, visibility: sfolder.find('visibility')[0].text }
94			solution_article = sfolder.find('solution-article')
95			solution_article.each do |sarticle|
96				articles << {folder_id: sfolder.find('id')[0].text, description: sarticle.find('description')[0].text, created_at: sarticle.find('created-at')[0].text, art_type: sarticle.find('art-type')[0].text, user_id: sarticle.find('user-id')[0].text }
97			end
98		end
99	end
100	$connection.create_and_insert_table 'categories', categories
101	$connection.create_and_insert_table 'folders', folders
102	$connection.create_and_insert_table 'articles', articles
103end
104
105# change the directory containing xmls here.
106get_solutions "xmls"