From 2049b91d89e249690c2dc26be644e697a8c066fb Mon Sep 17 00:00:00 2001
From: Joe Robinson <joe@lc8n.com>
Date: Tue, 23 Sep 2014 22:54:20 +0100
Subject: Added versioning to DB in convert script

---
 convert.py | 109 +++++++++++++++++++++++++++++++++++++++++++++++++++++++++----
 1 file changed, 103 insertions(+), 6 deletions(-)

diff --git a/convert.py b/convert.py
index 1a07fb2..2be3d28 100755
--- a/convert.py
+++ b/convert.py
@@ -5,6 +5,28 @@ import MySQLdb as mysql
 import sqlite3
 import sys
 
+class Definition(object):
+
+	#ID is relative to the word type, eg noun 1, noun 2, verb 1, verb 2, not to the entire list
+	id = 0
+	word = ""
+	dictionary = ""
+	word_type = ""
+	definition = ""
+	uses = []
+	synonyms = []
+	antonyms = []
+
+	def __init__(self, word, id, dictionary, word_type, definition, uses, synonyms, antonyms):
+		self.word = word
+		self.id = id
+		self.dictionary = dictionary
+		self.word_type = word_type
+		self.definition = definition
+		self.uses = uses
+		self.synonyms = synonyms
+		self.antonyms = antonyms
+
 def create():
 	try:
 		con = sqlite3.connect('dictionaries/wordnet.db');
@@ -18,12 +40,14 @@ def create():
 			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)")
+			cur.execute("CREATE TABLE info (id integer primary key not null , key text, value text)")
+			cur.execute("INSERT INTO info (key, value) VALUES('version', '1')")
 
-	except mysql.Error, e:
+	except sqlite3.Error, e:
 		print "Database Error %d: %s" % (e.args[0],e.args[1])
 		sys.exit(1)
 
-def select():
+def select_wn():
 	try:
 		con = mysql.connect('localhost', 'wordnet', 'words', 'wordnet');
 
@@ -38,7 +62,7 @@ def select():
 	
 	return rows
 
-def insert(rows):
+def insert_wn(rows):
 	try:
 		con = sqlite3.connect('dictionaries/wordnet.db');
 
@@ -69,10 +93,83 @@ def insert(rows):
 		print "Database Error %s" % (e.args[0])
 		sys.exit(1)
 
+def get_db_version():
+	try:
+		con = sqlite3.connect('dictionaries/wordnet.db');
+
+		with con:
+			cur = con.cursor()
+
+			# Check if info table exists at all, if not then version 0
+			cur.execute("SELECT count(name) FROM sqlite_master WHERE type='table' AND name='info'")
+			row = cur.fetchone()
+			if row[0] == 0:
+				return 0
+			else:
+				cur.execute("SELECT value FROM info WHERE key = 'version'")
+				row = cur.fetchone()
+				return row[0]
+
+	except sqlite3.Error, e:
+		print "Database Error %s" % (e.args[0])
+		sys.exit(1)
+
+def update_db(version):
+		try:
+			con = sqlite3.connect('dictionaries/wordnet.db');
+			with con:
+				cur = con.cursor()
+
+				if version < 1:
+					cur.execute("CREATE TABLE info (id integer primary key not null , key text, value text)")
+					cur.execute("INSERT INTO info (key, value) VALUES('version', '1')")
+
+				if version < 2:
+					cur = con.cursor()
+					cur.execute("INSERT INTO types (type, abbreviation) VALUES('urban', 'urb.')")
+					cur.execute("ALTER TABLE definitions ADD COLUMN dictionary_id int")
+					cur.execute("CREATE TABLE categories (id integer primary key not null, category text)")
+					cur.execute("CREATE TABLE dictionaries (id integer primary key not null, name text, abbreviation text)")
+					cur.execute("INSERT INTO dictionaries (name, abbreviation) VALUES('WordNet', 'wn')")
+					cur.execute("INSERT INTO dictionaries (name, abbreviation) VALUES('Urban Dictionary', 'urban')")
+					cur.execute("CREATE TABLE definition_categories (id integer primary key not null, definition_id int category_id int)")
+					cur.execute("UPDATE info set value = 2 where key = 'version'")
+
+		except sqlite3.Error, e:
+			print "Database Error %s" % (e.args[0])
+			sys.exit(1)
+
+
+def update_db_1():
+	try:
+		con = sqlite3.connect('dictionaries/wordnet.db');
+
+		with con:
+			cur = con.cursor()
+			cur.execute("INSERT INTO types (type, abbreviation) VALUES('urban', 'urb.')")
+			cur.execute("ALTER TABLE definitions ADD COLUMN dictionary_id int")
+			cur.execute("CREATE TABLE categories (id integer primary key not null, category text)")
+			cur.execute("CREATE TABLE dictionaries (id integer primary key not null, name text, abbreviation text")
+			cur.execute("INSERT INTO dictionaries (name, abbreviation) VALUES('WordNet', 'wn')")
+			cur.execute("INSERT INTO dictionaries (name, abbreviation) VALUES('Urban Dictionary', 'urban')")
+			cur.execute("CREATE TABLE definition_categories (id integer primary key not null, definition_id int category_id int)")
+			cur.execute("UPDATE info set value = 2 where key = 'version'")
+
+	except sqlite3.Error, e:
+		print "Database Error %d: %s" % (e.args[0],e.args[1])
+		sys.exit(1)
+
 def main():
-	create()
-	items = select()
-	insert(items)
+
+	version = get_db_version()
+
+	if version == 0:
+		create()
+		items = select()
+		insert(items)
+
+	update_db(version)
+
 
 if __name__ == "__main__":
 	main()
-- 
cgit v1.2.3