import config

class MyDbConnexion:

	def __init__(self):

		self.cnx = pg.connect(config.database, config.host,

		config.port, None, None, config.user, config.passwd)

	def add_bookmark(self, bk):

		id = self.get_next_id('bookmarks')

		res = self.cnx.query("""

			INSERT INTO bookmarks(id, url, name, ldesc, added)

			VALUES (%d, '%s', '%s', '%s', '%s');

			INSERT INTO bookmarks(bookmark, keyword)

			VALUES (%d, %d);

			""" % (id, bk.url, bk.name, bk.desc, bk.added,

			id, 0))

		return id

	def get_bookmark(self, id):

		bk = Bookmark()

		res = self.cnx.query("""

			SELECT url, name, ldesc, added

			FROM bookmarks

			WHERE id = %d;

			""" % (id,))

		res = res.dictresult()[0]

		bk.url = res['url']

		bk.name = res['name']

		bk.desc = res['ldesc']

		bk.added = res['added']

		bk.id = id

		return bk

	def update_bookmark(self, bk):

		return self.cnx.query("""

			UPDATE bookmarks

			SET url = '%s', name = '%s',

			ldesc = '%s', added = '%s'

			WHERE id = %d;

			""" % (bk.url, bk.name, bk.desc, bk.added, bk.id))

	def remove_bookmark(self, id):

		return self.cnx.query("""

			DELETE FROM bookmarks_keywords

			WHERE bookmark = %d;

			DELETE FROM bookmarks

			WHERE id = %d;

			""" % (id, id))

	def add_keyword(self, name):

		id = self.get_next_id('keywords')

		res = self.cnx.query("""

			INSERT INTO keywords(id, name)

			VALUES (%d, '%s')'

			""" % (id, name))

		return id

	def get_keyword(self, id):

		return self.cnx.query("""

			SELECT name FROM keywords

			WHERE keyword.id = %d;

			""" % (id,)).getresult()[0][0]

	def get_keywords(self, bk_id):

		return self.cnx.query("""

			SELECT id, name FROM keywords, bookmarks_keywords

			WHERE keywords.id = bookmarks_keywords.keyword

			AND bookmarks_keywords.bookmark = %d;

			""" % (bk_id,))

	def update_keyword(self, id, name):

		return self.cnx.query("""

			UPDATE keywords

			SET name = '%s'

			WHERE id = %d;

			""" % (name, id))

	def remove_keyword(self, id):

		if id == 0:

			raise "Can't remove default keyword!"

		return self.cnx.query("""

			DELETE FROM bookmarks_keywords

			WHERE keyword = %d;

			DELETE FROM keywords

			WHERE id = %d;

			""" % (id, id))

	def merge_keywords(self, merge_id, into_id):

		self.cnx.query("""

			UPDATE bookmarks_keywords

			SET keyword = %d

			WHERE keyword = %d;

			""" % (into_id, merge_id))

		self.remove_keyword(merge_id)

	def get_all_keywords(self):

		self.cnx.query("SELECT id, name FROM keywords;"

	def get_bookmarks(self, has_kw, except_kw):

		basic_qry = """

		  SELECT id, name, url FROM bookmarks, bookmarks_keywords

		  WHERE bookmarks.id = bookmarks_keywords.bookmark

		  AND bookmarks_keywords.keyword = %d

		  """

		has_kw.reverse()

		kw = has_kw.pop()

		qry = basic_qry % kw

		has_kw.reverse()

		for kw in has_kw:

			qry = qry + ("INTERCEPT " + basic_qry % kw)

		for kw in except_kw:

			qry = qry + ("EXCEPT " + basic_qry % kw)

		qry = qry + ';'

	def get_next_id(self, seq_name):

		id = self.cnx.query("""

			SELECT nextid FROM db_sequence WHERE

			db_sequence.seq_name = '%s';

			""" % (seq_name)).getresult()[0][0]

		self.cnx.query("""

			UPDATE db_sequence SET nextid = %d

			WHERE seq_name = 'bookmarks';

			""" % (id+1,))

	return MyDbConnexion()