#!/usr/bin/python

import pg
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,))

def connect():
	return MyDbConnexion()