#!/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;
			""" % (sql_quote(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 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
			AND NOT ( bookmark IN 
			  (SELECT bookmark FROM bookmarks_keywords
			  WHERE keyword = %d ))
			;
			""" % (into_id, merge_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()