ctdo-trac/TracRendezVous/tracrendezvous/rendezvous/model.py

851 lines
32 KiB
Python
Raw Permalink Normal View History

2012-03-31 15:45:24 +00:00
# -*- coding: utf-8 -*-
from trac.core import *
from trac.perm import PermissionSystem
from trac.db import Table, Column, Index
from trac.db.util import sql_escape_percent
from datetime import datetime, timedelta
from trac.util.datefmt import utc, to_timestamp
from trac.util.text import to_unicode
from trac.env import IEnvironmentSetupParticipant
from ctdotools.utils import validate_id, gen_wiki_page
from dateutil.rrule import *
from collections import defaultdict
from tracrendezvous.location.model import ItemLocation as RendezVousLocation
from tracrendezvous.rendezvous import api
__all__ = ['RendezVous', 'RendezVousComment', 'RendezVousType', 'RendezVousDate',
'RendezVousVote', 'TypePermission', 'RendezVousModelProvider',
'RendezVousTypePermissionSystem']
class RendezVousVote(object):
def __init__(self, env, vote_id, date_id, user, email, time_created, time_begin, time_end):
self.env = env
self.vote_id = vote_id
self.date_id = date_id
self.user = to_unicode(user)
self.email = to_unicode(email)
self.time_created = time_created
self.time_begin = time_begin
self.time_end = time_end
@staticmethod
def fetch_one(env, vote_id):
db = env.get_db_cnx()
cursor = db.cursor()
cursor.execute("""SELECT *
FROM rendezvous_vote
WHERE vote_id=%s""", (vote_id,))
row = cursor.fetchone()
if not row:
return None
vote_id, date_id, user, email, time_created, time_begin, time_end = row
return RendezVousVote(env, vote_id, date_id, user, email, datetime.utcfromtimestamp(time_created), datetime.utcfromtimestamp(time_begin), datetime.utcfromtimestamp(time_end))
2012-03-31 15:45:24 +00:00
@staticmethod
def exists(env, date_id, user, time_begin, time_end):
db = env.get_db_cnx()
cursor = db.cursor()
cursor.execute("""SELECT COUNT(*) FROM rendezvous_vote
WHERE date_id=%s
and user=%s
and time_begin=%s
and time_end=%s""", (date_id, user, to_timestamp(time_begin), to_timestamp(time_end)))
row = cursor.fetchone()
return row[0] > 0
@staticmethod
def fetch_by_date(env, date_id, userName=None):
db = env.get_db_cnx()
cursor = db.cursor()
if userName == None:
cursor.execute("""SELECT * FROM rendezvous_vote
WHERE date_id=%s
ORDER BY user""", (date_id,))
else:
cursor.execute("""SELECT * FROM rendezvous_vote
WHERE date_id=%s and user=%s
ORDER BY user;""", (date_id, userName))
res = []
for row in cursor:
vote_id, date_id, user, email, time_created, time_begin, time_end = row
res.append(RendezVousVote(env, vote_id, date_id, user, email, datetime.utcfromtimestamp(time_created), datetime.utcfromtimestamp(time_begin), datetime.utcfromtimestamp(time_end)))
2012-03-31 15:45:24 +00:00
return res
def commit(self):
db = self.env.get_db_cnx()
cursor = db.cursor()
cursor.execute("""INSERT INTO rendezvous_vote
(date_id, user, email, time_created, time_begin, time_end)
VALUES(%s, %s, %s, %s, %s, %s)""", (
self.date_id,
self.user,
self.email,
to_timestamp(self.time_created),
to_timestamp(self.time_begin),
to_timestamp(self.time_end)))
db.commit()
self.vote_id = db.get_last_id(cursor, 'rendezvous_vote')
@staticmethod
def delete(env, vote_id):
db = env.get_db_cnx()
cursor = db.cursor()
cursor.execute("""DELETE FROM rendezvous_vote
WHERE vote_id =%s;""", (vote_id,))
db.commit()
@staticmethod
def delete_by_date(env, date_id):
db = env.get_db_cnx()
cursor = db.cursor()
cursor.execute("""DELETE FROM rendezvous_vote
WHERE date_id =%s;""", (date_id,))
db.commit()
def update(self):
db = self.env.get_db_cnx()
cursor = db.cursor()
cursor.execute("""UPDATE rendezvous_vote
SET user=%s,
email=%s,
time_created=%s,
time_begin=%s,
time_end=%s
WHERE vote_id=%s;""", (self.user,
self.email,
to_timestamp(self.time_created),
to_timestamp(self.time_begin),
to_timestamp(self.time_end),
self.vote_id))
db.commit()
def __str__(self):
return "<RendezVousVote: %d, %d, %s, %s, %s, %s, %s" %(self.vote_id, self.date_id, self.user, self.email, self.time_created, self.time_begin, self.time_end)
def timedelta_2_string(t):
return "%d,%d,%d" % (t.days, t.seconds,t.microseconds)
def string2_timedelta(t):
return timedelta(*map(int, "1,2,3".split(",",3)))
class RendezVousDate(object):
def __init__(self, env, date_id, rendezvous_id, author, email, time_created, time_begin, time_end, elected, fetch_votes=False):
self.env = env
self.date_id = date_id
self.rendezvous_id = rendezvous_id
self.author = to_unicode(author)
self.email = to_unicode(email)
self.time_created = time_created
self.time_begin = time_begin
self.time_end = time_end
self.elected = elected
if fetch_votes:
self.votes = RendezVousVote.fetch_by_date(env, self.date_id)
@staticmethod
def fetch_one(env, date_id, fetch_votes=True):
db = env.get_db_cnx()
cursor = db.cursor()
if int(date_id) > 0:
cursor.execute("""SELECT *
FROM rendezvous_date
WHERE date_id=%s""", (date_id,) )
row = cursor.fetchone()
if not row:
return None
date_id, rendezvous_id, author, email, time_created, time_begin, time_end, elected = row
return RendezVousDate(env, date_id, rendezvous_id, author, email, datetime.utcfromtimestamp(time_created), datetime.utcfromtimestamp(time_begin), datetime.utcfromtimestamp(time_end), elected, fetch_votes)
2012-03-31 15:45:24 +00:00
@staticmethod
def exists(env, ts_begin, ts_end):
db = env.get_db_cnx()
cursor = db.cursor()
cursor.execute("SELECT date_id "
"FROM rendezvous_date "
"WHERE time_begin=%s AND time_end = %s",
(to_timestamp(ts_begin),
to_timestamp(ts_end)))
rows = cursor.fetchall()
return bool(rows)
@staticmethod
def fetch_by_rendezvous(env, rendezvous_id, fetch_votes=True):
db = env.get_db_cnx()
cursor = db.cursor()
cursor.execute("""SELECT *
FROM rendezvous_date
WHERE rendezvous_id=%s;""", (rendezvous_id,) )
rows = cursor.fetchall()
res = []
for row in rows:
date_id, rendezvous_id, author, email, time_created, time_begin, time_end, elected= row
res.append(RendezVousDate(env, date_id, rendezvous_id, author, email, datetime.utcfromtimestamp(time_created), datetime.utcfromtimestamp(time_begin), datetime.utcfromtimestamp(time_end), elected, fetch_votes))
2012-03-31 15:45:24 +00:00
return res
@staticmethod
def fetch_all(env, fetch_votes=True):
db = env.get_db_cnx()
cursor = db.cursor()
cursor.execute("SELECT * FROM rendezvous_date;")
rows = cursor.fetchall()
res = []
for row in rows:
date_id, rendezvous_id, author, email, time_created, time_begin, time_end, elected = row
res.append(RendezVousDate(env, date_id, rendezvous_id, author, email, datetime.utcfromtimestamp(time_created), datetime.utcfromtimestamp(time_begin), datetime.utcfromtimestamp(time_end), elected, fetch_votes))
2012-03-31 15:45:24 +00:00
return res
def get_vote_count(self, authname=None):
db = self.env.get_db_cnx()
cursor = db.cursor()
if authname:
cursor.execute("""SELECT COUNT(rendezvous_date.date_id) from rendezvous_date INNER JOIN rendezvous_vote ON
rendezvous_date.date_id=rendezvous_vote.date_id where rendezvous_date.date_id=%s and rendezvous_vote.user=%s;""", (self.date_id, authname))
else:
cursor.execute("SELECT COUNT(rendezvous_date.date_id) from rendezvous_date INNER JOIN rendezvous_vote "
"ON rendezvous_date.date_id=rendezvous_vote.date_id "
"where rendezvous_date.date_id=%s;", (self.date_id,))
row = cursor.fetchone()
if row:
return row[0]
return 0
def commit(self, conn=None):
db = conn and conn or self.env.get_db_cnx()
cursor = db.cursor()
cursor.execute("""INSERT INTO rendezvous_date
(rendezvous_id, author, email, time_created, time_begin, time_end, elected)
VALUES(%s,%s,%s,%s,%s,%s,%s)""", (
self.rendezvous_id,
self.author,
self.email,
to_timestamp(self.time_created),
to_timestamp(self.time_begin),
to_timestamp(self.time_end),
self.elected))
db.commit()
self.date_id = db.get_last_id(cursor, 'rendezvous_date')
@staticmethod
def delete(env, date_id):
db = env.get_db_cnx()
cursor = db.cursor()
cursor.execute("DELETE FROM rendezvous_date WHERE date_id=%s", (date_id,))
db.commit()
@staticmethod
def delete_by_rendezvous(env, rendezvous_id):
db = env.get_db_cnx()
cursor = db.cursor()
cursor.execute("""DELETE FROM rendezvous_date
WHERE rendezvous_id=%s;""", (rendezvous_id,) )
db.commit()
def update(self, conn=None):
db = conn and conn or self.env.get_db_cnx()
cursor = db.cursor()
try:
cursor.execute("""UPDATE rendezvous_date
SET rendezvous_id=%s,
author=%s,
email=%s,
time_created=%s,
time_begin=%s,
time_end=%s,
elected=%s
WHERE date_id=%s""", (self.rendezvous_id,
self.author,
self.email,
to_timestamp(self.time_created),
to_timestamp(self.time_begin),
to_timestamp(self.time_end),
self.elected,
self.date_id))
if not conn:
db.commit()
except Exception:
pass
def __str__(self):
return "<RendezVousDate: %d, %d, %s, %s, %s, %s, %s, %s>" % (self.date_id, self.rendezvous_id, self.author, self.email, str(self.time_created), str(self.time_begin), str(self.time_end), self.elected)
class RendezVousComment(object):
def __init__(self, env, comment_id, rendezvous_id, author, comment, time_created):
self.env = env
self.comment_id = comment_id
self.rendezvous_id = rendezvous_id
self.author = to_unicode(author)
self.comment = to_unicode(comment)
self.time_created = time_created
@staticmethod
def fetch_one(env, comment_id):
db = env.get_db_cnx()
cursor = db.cursor()
if int(comment_id) > 0:
cursor.execute("SELECT * "
"FROM rendezvous_comment "
"WHERE comment_id=%s", (comment_id,) )
row = cursor.fetchone()
if not row:
return None
comment_id, rendezvous_id, author, comment, time_created = row
return RendezVousComment(env, comment_id, rendezvous_id, author, comment, datetime.utcfromtimestamp(time_created))
2012-03-31 15:45:24 +00:00
@staticmethod
def fetch_by_rendezvous(env, rendezvous_id):
db = env.get_db_cnx()
cursor = db.cursor()
cursor.execute("""SELECT *
FROM rendezvous_comment
WHERE rendezvous_id=%s;""", (rendezvous_id,) )
rows = cursor.fetchall()
res = []
for row in rows:
comment_id, rendezvous_id, author, comment, time_created = row
res.append(RendezVousComment(env, comment_id, rendezvous_id, author, comment, datetime.utcfromtimestamp(time_created)))
2012-03-31 15:45:24 +00:00
return res
@staticmethod
def fetch_all(env):
db = env.get_db_cnx()
cursor = db.cursor()
cursor.execute("SELECT * FROM rendezvous_comment;")
rows = cursor.fetchall()
res = []
for row in rows:
comment_id, rendezvous_id, author, comment, time_created = row
res.append(RendezVousComment(env, comment_id, rendezvous_id, author, comment, datetime.utcfromtimestamp(time_created)))
2012-03-31 15:45:24 +00:00
return res
def commit(self):
db = self.env.get_db_cnx()
cursor = db.cursor()
cursor.execute("""INSERT INTO rendezvous_comment
(rendezvous_id, author, comment, time_created)
VALUES(%s,%s,%s,%s)""", (
self.rendezvous_id,
self.author,
self.comment,
to_timestamp(self.time_created)))
db.commit()
self.comment_id = db.get_last_id(cursor, 'rendezvous_comment')
@staticmethod
def delete(env, comment_id):
db = env.get_db_cnx()
RendezVousVote.delete_by_date(env, comment_id)
cursor = db.cursor()
cursor.execute("DELETE FROM rendezvous_comment WHERE comment_id=%s", (comment_id,))
db.commit()
@staticmethod
def delete_by_rendezvous(env, rendezvous_id):
db = env.get_db_cnx()
cursor = db.cursor()
cursor.execute("DELETE FROM rendezvous_comment WHERE rendezvous_id=%s", (rendezvous_id,))
db.commit()
def update(self):
db = self.env.get_db_cnx()
cursor = db.cursor()
try:
cursor.execute("""UPDATE rendezvous_comment
SET rendezvous_id=%s,
author=%s,
comment=%s,
time_created=%s
WHERE comment_id=%s""", (self.rendezvous_id,
self.author,
self.comment,
to_timestamp(self.time_created),
self.comment_id))
db.commit()
except Exception:
pass
def __str__(self):
return "<RendezVousComment: %d, %d, %s, %s, %s>" % (self.comment_id, self.rendezvous_id, self.author, self.comment, str(self.time_created))
class RendezVousType(object):
def __init__(self, env, type_id, name):
self.env = env
self.type_id = type_id
self.name = to_unicode(name)
self.typePermissions = TypePermission.fetch(env, type_id)
@staticmethod
def fetch_one(env, type_id=None, name=None):
db = env.get_db_cnx()
cursor = db.cursor()
if type_id and type_id > 0:
validate_id(int(type_id))
cursor.execute("""SELECT *
FROM rendezvous_type
WHERE type_id=%s""", (type_id,))
else:
cursor.execute("""SELECT *
FROM rendezvous_type
WHERE name=%s""", (name,))
row = cursor.fetchone()
if row:
return RendezVousType(env, row[0], row[1])
return None
@staticmethod
def fetch_all(env):
db = env.get_db_cnx()
cursor = db.cursor()
cursor.execute("SELECT * FROM rendezvous_type")
rows = cursor.fetchall()
if not rows:
return []
res = []
for row in rows:
res.append(RendezVousType(env, row[0], row[1]))
return res
def commit(self):
db = self.env.get_db_cnx()
cursor = db.cursor()
cursor.execute("""INSERT INTO rendezvous_type
(name)
VALUES(%s)""", (self.name,))
db.commit()
self.type_id = db.get_last_id(cursor, 'rendezvous_type')
def has_permission(self, permission):
for i in self.typePermissions:
if i.permission == permission:
return True
return False
def delete(self):
db = self.env.get_db_cnx()
cursor = db.cursor()
TypePermission.delete_by_type(self.env, self.type_id)
cursor.execute("DELETE FROM rendezvous_type WHERE type_id=%s", (self.type_id,))
db.commit()
def update(self):
db = self.env.get_db_cnx()
cursor = db.cursor()
cursor.execute("""UPDATE rendezvous_time
SET name=%s
WHERE type_id=%s""", (self.name, self.type_id))
db.commit()
class TypePermission(object):
def __init__(self, env, type_id, permission):
self.env = env
self.type_id = type_id
self.permission = permission
@staticmethod
def fetch(env, type_id):
db = env.get_db_cnx()
cursor = db.cursor()
cursor.execute("""SELECT *
FROM rendezvous_type_to_permission
WHERE type_id=%s""", (type_id,) )
rows = cursor.fetchall()
if not rows:
return []
res = []
for row in rows:
res.append(TypePermission(env, row[0], row[1]))
return res
@staticmethod
def fetch_one(env, type_id, permission):
db = env.get_db_cnx()
cursor = db.cursor()
if int(type_id) > 0:
cursor.execute("""SELECT *
FROM rendezvous_type_to_permission
WHERE type_id=%s AND permission=%s""", (type_id, permission))
row = cursor.fetchone()
if row:
return TypePermission(env, row[0], row[1])
return None
@staticmethod
def delete_by_type(env, type_id):
db = env.get_db_cnx()
cursor = db.cursor()
cursor.execute("DELETE FROM rendezvous_type_to_permission WHERE type_id=%s", (type_id,))
db.commit()
def commit(self):
db = self.env.get_db_cnx()
cursor = db.cursor()
cursor.execute("""INSERT INTO rendezvous_type_to_permission
(type_id, permission)
VALUES(%s,%s)""", (self.type_id, self.permission))
db.commit()
def delete(self):
db = self.env.get_db_cnx()
cursor = db.cursor()
cursor.execute("DELETE FROM rendezvous_type_to_permission WHERE type_id=%s AND permission=%s", (self.type_id, self.permission))
db.commit()
def __str__(self):
return "<TypePermission: %d %s>" % (self.type_id, self.permission)
class RendezVous(object):
def __init__(self, env, fetch_dates, rendezvous_id, name, author, email, description, time_created, schedule_deadline, min_votes, type_id, status, location_id, is_date_fixed, tags):
self.env = env
self.rendezvous_id = rendezvous_id
self.name = to_unicode(name)
self.author = to_unicode(author)
self.email = to_unicode(email)
self.description = to_unicode(description)
self.time_created = time_created
self.schedule_deadline = schedule_deadline
self.min_votes = min_votes
self.type_id = type_id
t = RendezVousType.fetch_one(self.env, type_id)
self.type_name = t and t.name or None
self.status = to_unicode(status)
self.location_id = location_id
self.is_date_fixed = is_date_fixed
self.dates = []
self.tags = tags
self.elected = 0
if fetch_dates:
self.dates = RendezVousDate.fetch_by_rendezvous(env, self.rendezvous_id)
for i in self.dates:
if i.elected:
self.elected = i.date_id
@staticmethod
def fetch_one(env, rid=None, name=None, fetch_dates=False):
db = env.get_db_cnx()
cursor = db.cursor()
rendezvous_id=0
if rid:
rendezvous_id = int(rid)
validate_id(rendezvous_id)
cursor.execute("""SELECT *
FROM rendezvous
WHERE rendezvous_id=%s""", (rendezvous_id,))
if name:
myname = unicode(name)
cursor.execute("""SELECT *
FROM rendezvous
WHERE name=%s""", name)
row = cursor.fetchone()
if not row:
return None
rendezvous_id, name, author, email, description, time_created, schedule_deadline, min_votes, type_id, status, location_id, is_date_fixed, tags = row
return RendezVous(env, fetch_dates, rendezvous_id, name, author, email, description,
datetime.utcfromtimestamp(time_created), datetime.utcfromtimestamp(schedule_deadline), min_votes, type_id, status, location_id, is_date_fixed, tags)
2012-03-31 15:45:24 +00:00
@staticmethod
def _fetch_some(env, fetch_dates, query, *args):
db = env.get_db_cnx()
cursor = db.cursor()
if args:
cursor.execute(query, args)
else:
cursor.execute(query)
rows = cursor.fetchall()
if not rows:
return []
res = []
for row in rows:
rendezvous_id, name, author, email, description, time_created, schedule_deadline, min_votes, type_id, status, location_id, is_date_fixed, tags = row
res.append(
RendezVous(env, fetch_dates, rendezvous_id, name, author,
email, description, datetime.utcfromtimestamp(time_created), datetime.utcfromtimestamp(schedule_deadline),
2012-03-31 15:45:24 +00:00
min_votes, type_id, status, location_id, is_date_fixed, tags))
return res
def get_date(self, date_id):
for i in self.dates:
if i.date_id == date_id:
return i
raise ValueError("RendezVousDate not found in RendezVous")
@staticmethod
def fetch_all(env, fetch_dates=False, sort=None):
if not sort:
return RendezVous._fetch_some(env, fetch_dates, "SELECT * FROM rendezvous;")
return RendezVous._fetch_some(env, fetch_dates, "SELECT * FROM rendezvous ORDER BY name")
@staticmethod
def my_rendezvous(env, name):
return RendezVous._fetch_some(env, False, "SELECT * FROM rendezvous where author = %s;", name)
@staticmethod
def exists(env, rendezvous_id=0):
db = env.get_db_cnx()
cursor = db.cursor()
if int(rendezvous_id) <= 0:
return False
cursor.execute("""SELECT *
FROM 'rendezvous'
WHERE rendezvous_id=%s""", (rendezvous_id,))
row = cursor.fetchone()
return row != None
def has_voted(self, authname=None):
for date in self.dates:
for vote in date.votes:
if vote.user == authname:
return True
return False
def has_votes(self):
for date in self.dates:
if date.votes:
return True
return False
def commit(self):
db = self.env.get_db_cnx()
t = datetime.now(utc)
cursor = db.cursor()
cursor.execute( "INSERT INTO rendezvous "
"(name,author,email,description,time_created,schedule_deadline,min_votes,type_id,status,location_id,is_date_fixed,tags) "
"VALUES(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)",
(self.name, self.author, self.email, self.description, to_timestamp(t),
to_timestamp(self.schedule_deadline), self.min_votes, self.type_id,
self.status, self.location_id, self.is_date_fixed, self.tags))
db.commit()
self.rendezvous_id = db.get_last_id(cursor, 'rendezvous')
@staticmethod
def delete(env, rendezvous_id):
db = env.get_db_cnx()
cursor = db.cursor()
cursor.execute("DELETE FROM rendezvous WHERE rendezvous_id = %s", (rendezvous_id,))
db.commit()
def update(self):
db = self.env.get_db_cnx()
cursor = db.cursor()
cursor.execute("UPDATE rendezvous " \
"SET name =%s, " \
"author=%s, " \
"email=%s, " \
"description=%s, " \
"time_created=%s, " \
"schedule_deadline=%s, " \
"min_votes=%s, " \
"type_id=%s, " \
"status=%s, " \
"location_id=%s, " \
"is_date_fixed=%s, " \
"tags=%s " \
"WHERE rendezvous_id=%s", (self.name, self.author, self.email,
self.description, to_timestamp(self.time_created), to_timestamp(self.schedule_deadline),
self.min_votes, self.type_id, self.status, self.location_id, self.is_date_fixed, self.tags, self.rendezvous_id))
db.commit()
def __str__(self):
return "<RendezVous: %d, %s, %s, %s, %s>" % (self.rendezvous_id, self.name, self.author, self.email, str(self.time_created))
class RendezVousModelProvider(Component):
implements(IEnvironmentSetupParticipant)
SCHEMA = [
# rendezvous system
Table('rendezvous', key='rendezvous_id')[
Column('rendezvous_id', auto_increment=True),
Column('name'),
Column('author'),
Column('email'),
Column('description'),
Column('time_created', type='int'),
Column('schedule_deadline', type='int'),
Column('min_votes', type='int'),
Column('type_id', type='int'),
Column('status'),
Column('location_id', type='int'),
Column('is_date_fixed', type='int'),
Column('tags'),
Index(['name']),
Index(['status']) ],
Table('rendezvous_comment', key='comment_id')[
Column('comment_id', auto_increment=True),
Column('rendezvous_id', type='int'),
Column('author'),
Column('comment'),
Column('time_created', type='int')],
# an user's spare time frame
Table('rendezvous_date', key='id')[
Column('date_id', auto_increment=True),
Column('rendezvous_id', type='int'),
Column('author'),
Column('email'),
Column('time_created', type='int'),
Column('time_begin', type='int'),
Column('time_end', type='int'),
Column('elected', type='int')],
Table('rendezvous_type', key=["type_id"])[
Column("type_id", auto_increment=True),
Column("name"),
Index(["name"])],
Table('rendezvous_type_to_permission', key=["type_id", "permission"])[
Column("type_id", type="int"),
Column("permission")],
# user's votings for a date with date and length of time frame
Table('rendezvous_vote', key=['vote_id'])[
Column('vote_id', auto_increment=True),
Column('date_id', type='int'),
Column('user'),
Column('email'),
Column('time_created', type='int'),
Column('time_begin', type='int'),
Column('time_end', type='int'),
Index(['time_begin']),
Index(['time_end'])]]
RendezVousDateTrigger = "CREATE TRIGGER fkd_date_rendezvous_id " \
"BEFORE DELETE ON rendezvous " \
"FOR EACH ROW BEGIN " \
"DELETE from rendezvous_date WHERE rendezvous_id = OLD.rendezvous_id; " \
"END;"
RendezVousCommentTrigger = "CREATE TRIGGER fkd_comment_rendezvous_id " \
"BEFORE DELETE ON rendezvous " \
"FOR EACH ROW BEGIN " \
"DELETE from rendezvous_comment WHERE rendezvous_id = OLD.rendezvous_id; " \
"END;"
RendezVousVoteTrigger = "CREATE TRIGGER fkd_vote_date_id " \
"BEFORE DELETE ON rendezvous_date " \
"FOR EACH ROW BEGIN " \
"DELETE from rendezvous_vote WHERE date_id = OLD.date_id; " \
"END;"
TYPE_DATA = (
(u'public',),
(u'admin',),
(u'Offizieller Treff',),
(u'Topic Treff',))
LOCATION_DATA = (
(u"CTDO, Langer August", "N", 51, 31, 39.4, "E", 7, 27, 53.8, 51.527611, 7.464922),
(u"WILA, Langer August", "N", 51, 31, 39.4, "E", 7, 27, 53.8, 51.527611, 7.464922))
TYPE_PERMISSIONS_DATA = (
(1, u'RENDEZVOUS_VIEW'),
(2, u'RENDEZVOUS_ADMIN'))
def environment_created(self):
if not "rendezvous" in self.config.sections():
data = {"graph_size_x" : 1024,
"graph_size_y" : 300,
"max_dates_per_rendezvous" : 99,
"max_description_length" : 1024,
"max_votes_per_date" : 99,
"show_location_map" : True,
"show_vote_graph" : True}
for k, v in data.iteritems():
self.config.set("rendezvous", k, v)
self.config.save()
self._create_models(self.env.get_db_cnx())
def environment_needs_upgrade(self, db):
"""First version - nothing to migrate, but possibly to create.
"""
cursor = db.cursor()
try:
cursor.execute("select count(*) from rendezvous")
cursor.fetchone()
cursor.execute("select count(*) from rendezvous_date")
cursor.fetchone()
cursor.execute("select count(*) from rendezvous_comment")
cursor.fetchone()
cursor.execute("select count(*) from rendezvous_type")
cursor.fetchone()
cursor.execute("select count(*) from rendezvous_type_to_permission")
cursor.fetchone()
cursor.execute("select count(*) from rendezvous_vote")
cursor.fetchone()
return False
except:
db.rollback()
return True
def upgrade_environment(self, db):
""" nothing to do here for now
"""
self._create_models(db)
def _create_models(self, db):
"""Called when a new Trac environment is created."""
db_backend = None
try:
from trac.db import DatabaseManager
db_backend, _ = DatabaseManager(self.env)._get_connector()
except ImportError:
db_backend = self.env.get_db_cnx()
cursor = db.cursor()
for table in self.SCHEMA:
for stmt in db_backend.to_sql(table):
self.env.log.debug(stmt)
try:
cursor.execute(stmt)
db.commit()
except Exception, e:
self.env.log.warning(str(e))
db.rollback()
cursor.execute(self.RendezVousCommentTrigger)
cursor.execute(self.RendezVousDateTrigger)
cursor.execute(self.RendezVousVoteTrigger)
db.commit()
#try:
cursor.executemany("""INSERT INTO 'rendezvous_type'
(name)
VALUES(%s)""", self.TYPE_DATA)
db.commit()
class RendezVousTypePermissionSystem(Component):
def check_user_type_permissions(self, user, type_id=None, name=None):
ps = PermissionSystem(self.env).get_user_permissions(user)
if 'RENDEZVOUS_ADMIN' in ps:
return True
t = RendezVousType.fetch_one(self.env, type_id=type_id, name=name)
if not t:
return False
db = self.env.get_db_cnx()
cursor = db.cursor()
cursor.execute("""SELECT permission
FROM rendezvous_type_to_permission
WHERE type_id=%s""", (t.type_id,))
rows = cursor.fetchall()
for p in rows:
if not ps.has_key(p[0]):
return False
return True