#!/usr/bin/python

import pgdb

import config

from bkmark import Bookmark

def sql_quote(str):

	return pgdb._quote(str)

class MyDbConnexion:

	def __init__(self):

		self.cnx = pgdb.connect(database = config.database,

			host = config.host+':%d'%config.port,

			user = config.user, password = config.passwd)

		self.crs = self.cnx.cursor();

	def add_bookmark(self, bk):

		id = self.get_next_id('bookmarks')

		self.crs.execute("""

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

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

			INSERT INTO bookmarks_keywords(bookmark, keyword)

			VALUES (%d, %d);

			""" % (id, sql_quote(bk.url),

			sql_quote(bk.name), sql_quote(bk.desc),

			sql_quote(bk.added), id, 0))

		self.cnx.commit()

		return id

	def get_bookmarks(self, ids):

		qry = """

			SELECT id, url, name, ldesc, added

			FROM bookmarks

			WHERE id = %d

			""" % (ids.pop(),)

		for id in ids:

			qry = qry + " OR id = %d" % (id,)

		qry = qry + ";"

		self.crs.execute(qry)

		bks = []

		list = self.crs.fetchall()

		for res in list:

			bk = Bookmark()

			bk.id = res[0]

			bk.url = res[1]

			bk.name = res[2]

			bk.desc = res[3]

			bk.added = res[4]

			bks.append(bk)

		return bks

	def update_bookmark(self, bk):

		self.crs.execute("""

			UPDATE bookmarks

			SET url = %s, name = %s,

			ldesc = %s, added = %s

			WHERE id = %d;

			""" % (bk.url, sql_quote(bk.name),

			sql_quote(bk.desc),

			sql_quote(bk.added), bk.id))

		self.cnx.commit()

	def update_keywords(self, id, keywords):

		self.crs.execute("""

			DELETE FROM bookmarks_keywords

			WHERE bookmark = %d AND

			keyword <> 0;""" % (id,))

		for kw in keywords:

			if kw == 0: continue

			self.crs.execute("""

				INSERT INTO

				bookmarks_keywords(bookmark, keyword)

				VALUES (%d, %d);""" % (id, kw))

		self.cnx.commit()	

	def remove_bookmark(self, id):

		self.crs.execute("""

			DELETE FROM bookmarks_keywords

			WHERE bookmark = %d;

			DELETE FROM bookmarks

			WHERE id = %d;

			""" % (id, id))

		self.cnx.commit()

	def add_keyword(self, name):

		id = self.get_next_id('keywords')

		self.crs.execute("""

			INSERT INTO keywords(id, name)

			VALUES (%d, %s);

			""" % (id, sql_quote(name)))

		self.cnx.commit()

		return id

	def get_keyword(self, id):

		self.crs.execute("""

			SELECT name FROM keywords

			WHERE keyword.id = %d;

			""" % (id,))

		return self.crs.fetchone()[0]

	def get_keywords(self, bk_id):

		self.crs.execute("""

			SELECT id, name FROM keywords, bookmarks_keywords

			WHERE keywords.id = bookmarks_keywords.keyword

			AND bookmarks_keywords.bookmark = %d

			ORDER BY id;

			""" % (bk_id,))

		return self.crs.fetchall()

	def update_keyword(self, id, name):

		self.crs.execute("""

			UPDATE keywords

			SET name = %s

			WHERE id = %d;

			""" % (sql_quote(name), id))

		self.cnx.commit()

	def remove_keyword(self, id):

		if id == 0:

			raise "Can't remove default keyword!"

		self.crs.execute("""

			DELETE FROM bookmarks_keywords

			WHERE keyword = %d;

			DELETE FROM keywords

			WHERE id = %d;

			""" % (id, id))

		self.cnx.commit()

	def merge_keywords(self, merge_id, into_id):

		self.crs.execute("""

			UPDATE bookmarks_keywords

			SET keyword = %d

			WHERE keyword = %d;

			""" % (into_id, merge_id))

		self.remove_keyword(merge_id)

	def get_all_keywords(self):

		"""Return a list of triplets [id, keyword, count]."""

		self.crs.execute("""SELECT k.id, k.name, COUNT(bk.keyword)

			FROM keywords k, bookmarks_keywords bk

			WHERE k.id = bk.keyword

			GROUP BY k.id, k.name

			ORDER BY k.id;""")

		return self.crs.fetchall()

	def select_bookmarks(self, has_kw = [0], except_kw = []):

	        """

Return a list of id bookmarks who has /has_kw/ set but not /except_kw/.

"""

		basic_qry = """

		  SELECT id FROM bookmarks, bookmarks_keywords

		  WHERE bookmarks.id = bookmarks_keywords.bookmark

		  AND bookmarks_keywords.keyword = %d

		  """

		qry = basic_qry % has_kw[0]

		for kw in has_kw[1:]:

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

		for kw in except_kw:

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

		qry = qry + ';'

		self.crs.execute(qry)

		return self.crs.fetchall()

	def get_next_id(self, seq_name):

		self.crs.execute("""

			SELECT nextid FROM db_sequence WHERE

			db_sequence.seq_name = '%s' FOR UPDATE;

			""" % (seq_name,))

		id = self.crs.fetchone()[0]

		self.crs.execute("""

			UPDATE db_sequence SET nextid = %d

			WHERE seq_name = '%s';

			""" % (id+1, seq_name))

		return id

def connect():

	return MyDbConnexion()