#!/usr/local/bin/oldpython
"""
A little hack to sync events between the user's gpe calendar and a remote
one (give its path).

This requires file access to both databases.

WARNING 1: This does not yet sync alarms.  It would be easy to retrofit 
that; either do it yourself or ask the author.

WARNING 2: This will lose the time of an event (always set it to 0 hours
UTC) if mxDateTime is available.  This is due to a bug (probably) in 
gpe-calendar that declares event.start as DATE rather than TIMESTAMP;
if you absolutely need python-mxdatetime on the syncing box, let's think
again.


It depends on python-sqlite for version 2 databases.
For Debian: sudo aptitude install python-sqlite # no longer

Other setup: before sycing for the first time, copy .gpe/calendar from
the initial master device to the initial slave.  during sync itself,
the devices are handled symmetrically; however, new calendars and alarms
are not (yet) supported.


Here is how this works:

From both databases, get deleted events from one, and delete events with
matching ids in the other.

For each database, get mappings from eventid to modification date

For each event that is in one but not the other, copy over all fields

For each event that is in both, compare the modification date.  If
they are different, copy all fields from one to the other.


The way this is done, this will only work if the calendars on both ends are
identically defined.

This program is free software, distributed under the GNU General Public
License Version 3 or (at your option) any later version.  

Copyright 2013 Markus Demleitner, msdemlei@ari.uni-heidelberg.de
"""

import os
import sys

import sqlite
# we don't want to quote strings ourselves, so we get sqlite's guts
from sqlite.main import _quote as quoteForSQLorig


# a central concept here is the event tuple; that's whatever is returned
# when querying for EVENT_COLUMNS (using, e.g., the pre-rendered _EVENT_SQL).

# uid must be the 0th item here
EVENT_COLUMNS = ("uid", "start", "duration", "recur", "rend", "alarm", 
	"calendar",  "eventid", "rcount", "rincrement", "modified", "byday", 
	"rexceptions")
_EVENT_SQL = ",".join(EVENT_COLUMNS)

# uid must be the 0th item here.
CALENDAR_COLUMNS = ("uid", "tag", "value")
_CALENDAR_SQL = ",".join(CALENDAR_COLUMNS)


def quoteForSQL(value):
	if isinstance(value, int):
		return str(value)
	else:
		return quoteForSQLorig(value)


class QuotableList(list):
	"""a list that properly serialized to sqlite.
	"""
	def _quote(aList):
		"""returns a properly quoted SQL literal for a list.
		"""
		return "(%s)"%(",".join(quoteForSQL(s) for s in aList))


class Calendar(object):
	"""a gpe calendar.

	This is constructed with the path to the database file.

	These manage their connections themselves.  You need to commit manually.
	"""
	def __init__(self, dbPath):
		self.conn = sqlite.connect(dbPath)
		self.cursor = self.conn.cursor()
		self.eventIdColumn = EVENT_COLUMNS.index("eventid")

	def commit(self):
		self.conn.commit()

	def getDeletedEvents(self):
		"""returns a list of (eventid, calendar) pairs for deleted events.
		"""
		self.cursor.execute("select uid, eventid, calendar from events_deleted")
		return list(tuple(t) for t in self.cursor)
	
	def deleteEvents(self, eventList):
		"""deletes all events with ids mentioned in eventList.

		eventList must be of the format returned by getDeletedEvents.
		"""
		toDelete = set(eventList)-set(self.getDeletedEvents())
		self.cursor.execute("delete from events where eventid in %s",
			(QuotableList(evid for _, evid, _ in toDelete)))
		self.cursor.execute("delete from calendar where uid in %s",
			(QuotableList(uid for uid, _, _ in toDelete)))
		self.cursor.executemany(
			"insert into events_deleted (uid, eventid, calendar) VALUES (%s, %s, %s)",
			toDelete)

	def getModificationDates(self):
		"""returns a dictionary mapping eventIds to their modification dates.
		"""
		self.cursor.execute("select eventid, modified from events")
		return dict(self.cursor)

	def getEvent(self, evId):
		"""returns the a pair of (event tuple, calendar rows) for evId.

		If evId does not exist, this will raise a KeyError with the evId.
		"""
		self.cursor.execute("select %s from events where eventid=%%s"%
			_EVENT_SQL, (evId,))
		evTuples = list(self.cursor)
		if len(evTuples)==0:
			raise KeyError(evId)
		assert len(evTuples)==1
		evTuple = evTuples[0]
		
		uid = evTuple[0]
		self.cursor.execute("select %s from calendar where uid=%%s"%
			_CALENDAR_SQL, (uid,))
		calendarTuples = list(self.cursor)

		return evTuple, calendarTuples

	def getUidFor(self, eventId):
		"""returns the uid of eventId within self.

		The uid is a database-local property of an event; the same event
		will have different uids in different DBs.

		This will raise an IndexError if eventId does not exist within self's
		database (and an AssertionError if there's more than one, which should
		never happen unless there's a problem in sqlite).
		"""
		self.cursor.execute("SELECT uid FROM events WHERE eventid=%s",
			(eventId,))
		uids = list(self.cursor)
		assert len(uids)==1
		return uids[0][0]

	def insertEvent(self, evTuple, calendarTuples):
		"""inserts an event into the events and calendar tables.
		"""
		self.cursor.execute("insert into events (%s) VALUES (%s)"%(
			",".join(EVENT_COLUMNS[1:]),
			",".join('%%(%s)s'%n for n in EVENT_COLUMNS[1:])),
			evTuple[1:])

		uid = self.getUidFor(evTuple[self.eventIdColumn])
		self.cursor.executemany("insert into calendar (%s) VALUES (%s)"%(
			_CALENDAR_SQL, ",".join('%s' for n in CALENDAR_COLUMNS)),
			[(uid,)+tuple(t[1:]) for t in calendarTuples])

	def updateEvent(self, evId, evTuple, calendarTuples):
		"""updates the event with evId with evTuple.

		Program logic must make sure that evTuple actually has evId.
		"""
		self.cursor.execute("update events set %s where eventid=%%s"%(
			",".join("%s=%%s"%n for n in EVENT_COLUMNS[1:])),
			tuple(evTuple[1:])+(evId,))

		uid = self.getUidFor(evTuple[self.eventIdColumn])
		self.cursor.execute("delete from calendar where uid=%s",
			(uid,))
		self.cursor.executemany("insert into calendar (%s) VALUES (%s)"%(
			_CALENDAR_SQL, ",".join('%s' for n in CALENDAR_COLUMNS)),
			[(uid,)+tuple(t[1:]) for t in calendarTuples])


def syncActiveEvents(cal1, cal2):
	"""tries to synchronized the active events between cal1 and cal2.

	Missing events are copied to where they are missing, updated events
	are replaced where the older copy is.
	"""
	evs1 = cal1.getModificationDates()
	evs2 = cal2.getModificationDates()

	for evId in set(evs1)-set(evs2):
		print("Syncing up: %s"%evId)
		cal2.insertEvent(*cal1.getEvent(evId))

	for evId in set(evs2)-set(evs1):
		print("Syncing down: %s"%evId)
		cal1.insertEvent(*cal2.getEvent(evId))
	
	for evId in set(evs1)&set(evs2):
		if evs1[evId]<evs2[evId]:
			print("Updating down: %s"%evId)
			cal1.updateEvent(evId, *cal2.getEvent(evId))
		elif evs1[evId]>evs2[evId]:
			print("Updating up: %s"%evId)
			cal2.updateEvent(evId, *cal1.getEvent(evId))
		# else both are up to date, fall through.


def propagateDeletions(cal1, cal2):
	"""propagates deletions between the two calendars.
	"""
	cal1.deleteEvents(cal2.getDeletedEvents())
	cal2.deleteEvents(cal1.getDeletedEvents())


def main():
	if len(sys.argv)!=2:
		sys.exit("Usage: %s <remote gpe calendar db>"%sys.argv[0])

	cal1 = Calendar(os.path.expanduser("~/.gpe/calendar"))
	if not os.path.exists(sys.argv[1]):
		sys.exit("No such file: %s"%sys.argv[1])
	try:
		cal2 = Calendar(sys.argv[1])
	except sqlite.Error:
		sys.exit("Could not open %s as gpe calendar."%sys.argv[1])

	propagateDeletions(cal1, cal2)
	syncActiveEvents(cal1, cal2)
	cal1.commit()
	cal2.commit()


if __name__=="__main__":
	main()