import pgdb

import config

from bkmark import Bookmark

import os

def sql_quote(str):

	return pgdb._quote(str)

class MyDbConnexion:

	def __init__(self, name):

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

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

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

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

		self.userid = self.get_user_id(name)

		if self.userid == None:

			self.userid = self.create_user(name)

		else:

			self.userid = self.userid[0]

	def get_user_id(self, name):

		self.crs.execute("""

			SELECT id FROM users

			WHERE name = %s;

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

		return self.crs.fetchone()

	def create_user(self, name):

		id = self.get_next_id('users')

		self.crs.execute("""

			INSERT INTO users(id, name, full_name)

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

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

		self.crs.execute("""

			INSERT INTO keywords(id, userid, name)

			VALUES (0, %d, '--');

			""" %(id,))

		self.crs.execute("""

			INSERT INTO db_sequence(seq_name, userid, nextid)

			VALUES ('keywords', %d, 1);

			""" %(id,))

		self.crs.execute("""

			INSERT INTO db_sequence(seq_name, userid, nextid)

			VALUES ('bookmarks', %d, 0);

			""" %(id,))

		self.crs.execute("""

			INSERT INTO folders(id, keyword, parent, userid)

			VALUES (0, 0, -1, %d);

			""" %(id,))

		self.crs.execute("""

			INSERT INTO db_sequence(seq_name, userid, nextid)

			VALUES ('folders', %d, 1);

			""" %(id,))

		self.crs.execute("""

			INSERT INTO preferences(userid)

			VALUES (%d);

			""" %(id,))

		self.cnx.commit()

		return id

	def delete_user(self, userid):

		self.crs.execute("""

			DELETE FROM users WHERE id = %d;

			DELETE FROM bookmarks_keywords WHERE userid = %d;

			DELETE FROM bookmarks WHERE userid = %d;

			DELETE FROM preferences WHERE userid = %d;

			DELETE FROM keywords WHERE userid = %d;

			DELETE FROM db_sequence WHERE userid = %d;

			""" % (userid,userid,userid,userid,userid))

		self.cnx.commit()

	def get_preferences(self):

		self.crs.execute("""

			SELECT p.keywords_box,

			p.keywords_sort, p.keywords_reverse,

			u.full_name, p.default_view

			FROM preferences p, users u

			WHERE u.id = %d AND p.userid = u.id;

			""" % (self.userid,))

		res = self.crs.fetchone()

		return {

		   'keywords_box' : res[0],

		   'keywords_sort' : res[1],

		   'keywords_reverse': res[2],

		   'fullname': res[3],

		   'default_view': res[4]

		   }

	def set_preferences(self, prefs):

	        if prefs['keywords_reverse']:

			rev = 'TRUE'

		else:

			rev = 'FALSE'

		self.crs.execute("""

			UPDATE preferences

			SET keywords_box = %d,

			keywords_sort = %s,

			keywords_reverse = %s,

			default_view = %d

			WHERE userid = %d;

			""" % (prefs['keywords_box'],

			sql_quote(prefs['keywords_sort']),

			rev, prefs['default_view'], self.userid))

		self.crs.execute("""

			UPDATE users

			SET full_name = %s

			WHERE id = %d;

			""" % (sql_quote(prefs["fullname"]),

				self.userid))

		self.cnx.commit()

	def add_bookmark(self, bk):

		id = self.get_next_id('bookmarks')

		self.crs.execute("""

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

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

			INSERT INTO bookmarks_keywords(bookmark, keyword, userid)

			VALUES (%d, 0, %d);

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

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

			sql_quote(bk.added),

			id, self.userid))

		self.cnx.commit()

		return id

	def get_bookmarks(self, ids):

		qry = """

			SELECT id, url, name, ldesc, added

			FROM bookmarks

			WHERE userid = %d AND (id = %d

			""" % (self.userid, 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 userid = %d AND id = %d;

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

			sql_quote(bk.desc),

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

		self.cnx.commit()

	def update_keywords(self, id, keywords):

		self.crs.execute("""

			DELETE FROM bookmarks_keywords

			WHERE userid = %d AND bookmark = %d AND

			keyword <> 0;""" % (self.userid, id))

		for kw in keywords:

			if kw == 0: continue

			self.crs.execute("""

				INSERT INTO

				bookmarks_keywords(bookmark, keyword, userid)

				VALUES (%d, %d, %d);""" % (id, kw, self.userid))

		self.cnx.commit()	

	def remove_bookmark(self, id):

		self.crs.execute("""

			DELETE FROM bookmarks_keywords

			WHERE userid = %d AND bookmark = %d;

			DELETE FROM bookmarks

			WHERE userid = %d AND id = %d;

			""" % (self.userid, id, self.userid, id))

		self.cnx.commit()

	def add_keyword(self, name):

		id = self.get_next_id('keywords')

		self.crs.execute("""

			INSERT INTO keywords(id, userid, name)

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

			""" % (id, self.userid, sql_quote(name)))

		self.cnx.commit()

		return id

	def get_keyword(self, id):

		self.crs.execute("""

			SELECT name FROM keywords

			WHERE userid = %d AND id = %d;

			""" % (self.userid, id))

		return self.crs.fetchone()[0]

	def get_keywords(self, bk_ids):

		if len(bk_ids) == 0:

			return []

		qry = """

			SELECT DISTINCT k.id, k.name

			FROM keywords k, bookmarks_keywords bk

			WHERE bk.userid = %d

			AND k.userid = %d

			AND k.id = bk.keyword

			AND (

			""" % (self.userid, self.userid)

		for id in bk_ids:

			qry += "bk.bookmark = %d OR " % (id,)

		qry = qry[:-3] + ") ORDER BY k.id;"

		self.crs.execute(qry)

		return self.crs.fetchall()

	def update_keyword(self, id, name):

		self.crs.execute("""

			UPDATE keywords

			SET name = %s

			WHERE userid = %d AND id = %d;

			""" % (sql_quote(name), self.userid, 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 AND userid = %d;

			DELETE FROM keywords

			WHERE id = %d AND userid = %d;

			""" % (id, self.userid, id, self.userid))

		self.cnx.commit()

	def add_keywords(self, add_id, into_id):

		self.crs.execute("""

			INSERT INTO bookmarks_keywords (userid,

			bookmark, keyword)

			SELECT %d, bookmark, %d

			FROM bookmarks_keywords

			WHERE keyword = %d

			AND userid = %d

			AND bookmark NOT IN 

			  (SELECT bookmark FROM bookmarks_keywords

			  WHERE userid = %d AND keyword = %d )

			;

			""" % (self.userid, into_id, add_id, self.userid,

			self.userid, into_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 userid = %d

			AND bookmark NOT IN 

			  (SELECT bookmark FROM bookmarks_keywords

			  WHERE userid = %d AND keyword = %d )

			;

			""" % (into_id, merge_id, self.userid,

			self.userid, into_id))

		self.remove_keyword(merge_id)

	def get_all_keywords(self):

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

		self.crs.execute("""SELECT k.id, k.name

			FROM keywords k

			WHERE k.userid = %d

			ORDER BY k.id;""" % (self.userid,))

		res = self.crs.fetchall()

		return res

	def get_keywords_count(self, bks = []):

		"""Return a list of doublets [id, count].

Does with a count of zero are ignored."""

		qry = """SELECT bk.keyword, COUNT(bk.keyword)

			FROM bookmarks_keywords bk

			WHERE bk.userid = %d""" % (self.userid,)

		if len(bks) > 0:

			qry += " AND ( bk.bookmark = %d" % bks[0]

			for bk in bks[1:]:

				qry += " OR bk.bookmark = %d" % bk

			qry += " )"

		qry += " GROUP BY bk.keyword ORDER BY bk.keyword;"

		self.crs.execute(qry)

		res = self.crs.fetchall()

		if not res:

			res = [[0,0]]

		return res

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

	        """

Return a bookmarks selection query used in at least two function.

"""

		basic_qry = """

		  SELECT bk.bookmark FROM bookmarks_keywords bk

		  WHERE bk.keyword = %d

		  AND bk.userid = %d

		  """

		qry = basic_qry % (has_kw[0], self.userid)

		for kw in has_kw[1:]:

			qry += ("INTERSECT " + basic_qry % (kw, self.userid))

		for kw in except_kw:

			qry += ("EXCEPT " + basic_qry % (kw, self.userid))

		return qry

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

	        """

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

"""

		qry = self._bookmarks_selection(has_kw, except_kw) + ';'

		self.crs.execute(qry)

		return self.crs.fetchall()

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

	        """

Return a count of bookmarks which would have been return on

select_bookmarks qry.

"""

		qry = """

			SELECT keyword, COUNT(keyword)

			FROM bookmarks_keywords

			WHERE userid = %d

			""" % self.userid

		if len(opt_kw) > 0:

			qry += "AND ( "

			qry += " OR ".join(map(lambda e: "keyword = %d" % e, opt_kw))

			qry += ") "

		qry += "AND bookmark IN (" + self._bookmarks_selection(has_kw, except_kw) + ") "

		qry += "GROUP BY keyword ORDER BY keyword;"

		self.crs.execute(qry)

		return self.crs.fetchall()

	def get_folder_keyword(self, id):

		self.crs.execute("""

			SELECT k.id, k.name

			FROM keywords k, folders f

			WHERE

			f.userid = %d AND f.id = %d

			AND k.userid = f.userid

			AND k.id = f.keyword;

			""" %(self.userid, id))

		return self.crs.fetchone()

	def get_subfolders(self, id):

		self.crs.execute("""

			SELECT f.id, k.id, k.name

			FROM folders f, keywords k

			WHERE f.userid = %d

			AND f.parent = %d

			AND k.userid = f.userid

			AND k.id = f.keyword

			ORDER BY k.name;

			""" %(self.userid, id))

		res = self.crs.fetchall()

		return map(lambda e: {

			'id' : e[0],

			'name' : e[2],

			'keyword' : e[1] },

			res)

	def get_folder_parents_and_self(self, id):

		"""Return self and parents in descending order."""

		if id < 0:

			return []

		self.crs.execute("""

			SELECT f.id, k.id, k.name, f.parent

			FROM folders f, keywords k

			WHERE

			f.id = %d AND f.userid = %d

			AND k.userid = f.userid

			AND k.id = f.keyword;

			""" %(id, self.userid))

		cur = self.crs.fetchone()

		res = [{ 'id': cur[0],

		        'name' : cur[2],

			'keyword': cur[1] }]

		return res + self.get_folder_parents_and_self(cur[3])

	def add_folder(self, keyword, parent):

		id = self.get_next_id('folders')

		self.crs.execute("""

			INSERT INTO folders(userid, id, keyword, parent)

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

			""" % (self.userid, id, keyword, parent))

		self.cnx.commit()

	def remove_folders(self, id, commit = 1):

		if id == 0:

			raise "Can't erase base folder!"

		subs = self.get_subfolders(id)

		for sub in subs:

			self.remove_folders(sub['id'], 0)

		self.crs.execute("""

			DELETE FROM folders

			WHERE id = %d AND userid = %d;

			""" % (id, self.userid))	

		if commit:

			self.cnx.commit()

	def get_next_id(self, seq_name):

		if seq_name == 'users':

			userid = -1

		else:

			userid = self.userid

		self.crs.execute("""

			SELECT nextid FROM db_sequence

			WHERE seq_name = '%s'

			AND userid = %d

			FOR UPDATE;

			""" % (seq_name,userid))

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

		self.crs.execute("""

			UPDATE db_sequence SET nextid = %d

			WHERE seq_name = '%s'

			AND userid = %d;

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

		return id

def connect(username):

	return MyDbConnexion(username)