#!/usr/bin/python2
# -*- coding: utf-8 -*-

import MySQLdb as mysql
import sqlite3
import sys

def create():
	try:
		con = sqlite3.connect('dictionaries/wordnet.db');

		with con:
			cur = con.cursor()
			cur.execute("CREATE TABLE types (id integer primary key not null , type text, abbreviation text)")
			cur.execute("INSERT INTO types (type, abbreviation) VALUES('noun', 'n')")
			cur.execute("INSERT INTO types (type, abbreviation) VALUES('verb', 'v')")
			cur.execute("INSERT INTO types (type, abbreviation) VALUES('adjective', 'adj')")
			cur.execute("INSERT INTO types (type, abbreviation) VALUES('adverb', 'adv')")
			cur.execute("CREATE TABLE definitions (id integer primary key not null , word text, type_id int, sub_id int, synset_id bigint, definition text)")
			cur.execute("CREATE TABLE uses (id integer primary key not null, definition_id bigint, quote text)")

	except mysql.Error, e:
		print "Database Error %d: %s" % (e.args[0],e.args[1])
		sys.exit(1)

def select():
	try:
		con = mysql.connect('localhost', 'wordnet', 'words', 'wordnet');

		with con:
			cur = con.cursor(mysql.cursors.DictCursor)
			cur.execute("SELECT lemma, pos, sensenum, synsetid, definition, sampleset from dict")
			rows = cur.fetchall()
			print len(rows)
	except mysql.Error, e:
		print "Database Error %d: %s" % (e.args[0],e.args[1])
		sys.exit(1)
	
	return rows

def insert(rows):
	try:
		con = sqlite3.connect('dictionaries/wordnet.db');

		with con:
			cur = con.cursor()

			for row in rows:
				if row['pos'] == 'n':
					type_id = 1
				elif row['pos'] == 'v':
					type_id = 2
				elif row['pos'] == 'a' or row['pos'] == 's':
					type_id = 3
				elif row['pos'] == 'r':
					type_id = 4

				cur.execute("INSERT INTO definitions(word, type_id, sub_id, synset_id, definition) values(?, ?, ?, ?, ?)", [row['lemma'], type_id, row['sensenum'], row['synsetid'], row['definition']])

				row_id = cur.lastrowid

				if row['sampleset'] is not None:
					uses = row['sampleset'].split("|")

					for use in uses:
						cur.execute("INSERT INTO uses(definition_id, quote) values(?, ?)", [row_id, use])

	except sqlite3.Error, e:
		print "Database Error %s" % (e.args[0])
		sys.exit(1)

def main():
	create()
	items = select()
	insert(items)

if __name__ == "__main__":
	main()