Chronr / chronr / chronr / model /

"""The application's model objects"""
from datetime import datetime
import hashlib
import logging
import os

import sqlalchemy as sa
from sqlalchemy import orm
from schemabot import SchemaBot, SchemaManager

from colossusmodel import define_model
from chronr.model import meta

# ---- Constants ----


# ---- Globals ----

log = logging.getLogger(__name__)

# ---- Functions ----

def init_model(engine, auto_schema_update=False):
    """Call me before using any of the tables or classes in the model"""
    ## Reflected tables must be defined and mapped here
    #global reflected_table
    #reflected_table = sa.Table("Reflected", meta.metadata, autoload=True,
    #                           autoload_with=engine)
    #orm.mapper(Reflected, reflected_table)
    meta.engine = engine
    schema_mgr = SchemaManager()
    schema_mgr.register(1, upgrade=schema_version_1_upgrade, downgrade=schema_version_1_downgrade)
    schema_mgr.register(2, upgrade=schema_version_2_upgrade, downgrade=schema_version_2_downgrade)
    schemabot = SchemaBot(schema_mgr, engine=engine)
    (model_version, current_db_version) = schemabot.version_check()
    force_version = os.environ.get('SCHEMABOT_FORCE_VERSION')
    if force_version is not None:
        # User override of model_version. This will force an upgrade or downgrade
        #   to the specified model version.
        model_version = int(force_version)
        if model_version != 0 and schema_mgr.get(model_version) is None:
            raise ValueError("Model version %d requested by SCHEMABOT_FORCE_VERSION does not exist." %model_version)
        log.warn("Forcing a DB schema update to version %d" %model_version)
    if model_version != current_db_version:
        if auto_schema_update:
            raise Exception("SchemaBot reports that the model version (%d) does not match the DB schema version (%d). Please run 'paster setup-app config.ini' to update the database schema." %(model_version, current_db_version))

# ---- Model ----

# - Schema version 1 -

user_table = sa.Table('user', meta.metadata,
    sa.Column('id', sa.Integer, primary_key=True),
    sa.Column('user_name', sa.Unicode(USERNAME_SIZE), unique=True),
    sa.Column('email_address', sa.Unicode(255), unique=True),
    sa.Column('display_name', sa.Unicode(255)),
    sa.Column('password', sa.String(128)),          # room for SHA-512 hashed passwords (hexdigest)
    sa.Column('created', sa.DateTime,,
    sa.Column('activated', sa.Boolean, nullable=False, default=False),

user_activation_table = sa.Table('user_activation', meta.metadata,
    sa.Column('id', sa.Integer, primary_key=True),
    sa.Column('user_id', sa.Integer, sa.ForeignKey("")),
    sa.Column('key', sa.Unicode(128), nullable=False),
    sa.Column('created', sa.DateTime,,

# Insert a default user "admin" with password "admin"
admin_password_hash = hashlib.sha512('admin').hexdigest()
insert_default_user = 'INSERT INTO "user" '                         \
        "(id, user_name, display_name, password, activated) VALUES "           \
        "(0, 'admin', 'Admin User', '%s', true)"%admin_password_hash
schema_version_1_upgrade = [user_table, user_activation_table, insert_default_user]
schema_version_1_downgrade = [user_table, user_activation_table]

# - Schema version 2 -

events_table = sa.Table('events', meta.metadata,
    sa.Column('id', sa.Integer, primary_key=True),
    sa.Column('user_id', sa.Integer, sa.ForeignKey(''), nullable=False),
    sa.Column('title', sa.Unicode(EVENT_TITLE_SIZE), nullable=False),
    sa.Column('duedate', sa.DateTime(timezone=False), nullable=False),    # date/time (UTC)
    sa.Column('timezone', sa.Unicode(256)),
    sa.Column('duration', sa.Integer),                    # minutes
    sa.Column('allday', sa.Boolean),
    sa.Column('description', sa.Unicode),
    sa.Column('private', sa.Boolean),

following_table = sa.Table('following', meta.metadata,
    sa.Column('user_id', sa.Integer, sa.ForeignKey('',
        onupdate='CASCADE', ondelete='CASCADE')),
    sa.Column('event_id', sa.Integer, sa.ForeignKey('',
        onupdate='CASCADE', ondelete='CASCADE'))

tags_table = sa.Table('tags', meta.metadata,
    sa.Column('id', sa.Integer, primary_key=True),
    sa.Column('name', sa.Unicode(32), index=True, nullable=False, unique=True),

# many_to_many(tags/events/user)
tag_event_user_table = sa.Table('tag_event_user', meta.metadata,
    sa.Column('tag_id', sa.Integer, sa.ForeignKey('')),
    sa.Column('user_id', sa.Integer, sa.ForeignKey('')),
    sa.Column('event_id', sa.Integer, sa.ForeignKey('')),
    sa.UniqueConstraint('tag_id', 'user_id', 'event_id'),

schema_version_2_upgrade = [events_table, following_table, tags_table, tag_event_user_table]
schema_version_2_downgrade = [events_table, following_table, tags_table, tag_event_user_table]

# ---- ORM Mapped Classes ----

class User(object):
    def __init__(self, **kwargs):
        for key,value in kwargs.items():
            if key == 'password':
                # Hash password
                setattr(self, key, value)
    def by_email_address(cls, email):
        A class method that can be used to search users
        based on their email addresses since it is unique.
        return cls.query.filter_by(email_address=email).first()
    def by_user_name(cls, username):
        A class method that permits to search users
        based on their user_name attribute.
        return cls.query.filter_by(user_name=username).first()
    def isfollowing(self, event_id):
        """Return True if user is following event_id.
        if event_id in self.following:
            return True
        return False
    def num_following(self):
        """Return the number of events the user is following."""
        return len(self.following)
    def get_by_id(cls, user_id):
        """Returns a User object representing the user
        record for `user_id`.
        query = meta.Session().query(User)
        u = query.get(user_id)
        return u
    def validate_password(self, password):
        """The given password is hashed and compared against the one
        stored in the database.  Returns True if they are equal AND the
        account is flagged as activated, else False.
        This method is called by
        # hashed_password = hashlib.sha512(password).hexdigest()
        hashed_password = self._hash_password(password)
        return self.password == hashed_password and self.activated
    def set_password(self, raw_pass):
        """Set a new password for the account.  The raw password
        will be stored in hashed form and will not be reversible.
        self.password = self._hash_password(raw_pass)
    def _hash_password(self, raw_pass):
        return hashlib.sha512(raw_pass).hexdigest()

class UserActivation(object):

class Event(object):
    """An event with a due date and various other useful info.
    ORM mapped to the events table.
    The core data type of the chronr app.
    def add_tags_for_user(self, tags, user_id):
        """Tag the event with all strings in the list provided by
        `tags` for the user specified by `user_id`.
        for tag in tags:
            t = Tag.get_existing_or_new(tag)
            u = User.get_by_id(user_id)
            # associate tag with the event
            assoc = TagAssociation()
            assoc.tag = t
            assoc.user = u
    def local_duedate(self):
        """Return the due date in the local timezone (rather than UTC).
        return utc_to_tz(self.duedate, self.timezone)
    def get_next(self, n=10, username=None, qfilter=None, following=False):
        if following and username is None:
            raise ValueError("username must be specified when following is True")
        query = meta.Session().query(Event)
        if following and username:
            # Filter to events that are being followed by user
            user = User.by_user_name(username)
            if user is None:
                raise ValueError("No such username '%s'" %username)
            query = query.filter_by(followers=user)
        elif username is not None:
            # Filter to events created by user
            query = query.join('user').filter_by(user_name=username)
        if qfilter:
            # Add custom query filter
            query = query.filter(qfilter)
        query = query.filter(Event.duedate >= datetime.utcnow()).order_by(Event.duedate).limit(n)
        return query

class Tag(object):
    """A text string used to classify events.  Each tag for an event
    is linked to the user who tagged it.
    def get_by_name(cls, name):
        """Return Tag object matching `name`.  If it doesn't
        exist then None is returned
        query = meta.Session().query(Tag)
            # Fetch existing tag, if it exists
            t = query.filter( == name).one()
        except InvalidRequestError:
            t = None
        return t
    def get_existing_or_new(cls, tag):
        """Return Tag object matching `tag`.  If it doesn't
        exist then create a new row in DB and return the
        corresponding Tag object.
        session = meta.Session()
        query = session.query(Tag)
            # Fetch existing tag, if it exists
            t = query.filter( == tag).one()
        except InvalidRequestError:
            # Create new tag
            t = Tag(name=tag)
        return t
    def remove_for_user_and_event(cls, user_id, event_id):
        query = meta.Session().query(Event)
        event = query.get(event_id)
        if event is None:
            raise ValueError
        for assoc in event.tags:
            if assoc.user_id == user_id:
    def tags_for_event(cls, event_id):
        """Return list of all Tag objects related to Event specified
        by `event_id`.
        query = meta.Session().query(Tag).filter(TagAssociation.c.tag_id == == event_id)
        return query.all()
    def tags_for_user(cls, user_id):
        """Return list of all Tag objects related to User specified
        by `user_id`.
        query = meta.Session().query(Tag).filter(TagAssociation.c.tag_id == == user_id)
        return query.all()

class TagAssociation(object):

orm.mapper(User, user_table,
    properties = dict(    
        activation = sa.orm.relation(UserActivation, backref='user', cascade="all, delete, delete-orphan"),

orm.mapper(UserActivation, user_activation_table)

orm.mapper(Event, events_table,
    properties = dict(
        tags = sa.orm.relation(TagAssociation, lazy=False, cascade="all, delete-orphan"),
        user = sa.orm.relation(User, backref='events'),
        followers = sa.orm.relation(User, secondary=following_table, backref='following')

orm.mapper(Tag, tags_table,
    # properties = dict(
    #     # users = relation(User, secondary=tag_event_user_table, backref='tags'),
    #     # events = relation(Event, secondary=tag_event_user_table, backref='tags'),
    # )

# Associate an Event with a (Tag, User) pair.
orm.mapper(TagAssociation, tag_event_user_table,
    properties = dict(
        tag = sa.orm.relation(Tag, lazy=False),
        user = sa.orm.relation(User, lazy=False, backref='tags'),

## Classes for reflected tables may be defined here, but the table and
## mapping itself must be done in the init_model function
#reflected_table = None
#class Reflected(object):
#    pass