def downgrade():
    # 1. Add primary columns
    op.add_column('userphone', sa.Column('primary', sa.BOOLEAN(), autoincrement=False, nullable=False,
        server_default=expression.false()))
    op.add_column('useremail', sa.Column('primary', sa.BOOLEAN(), autoincrement=False, nullable=False,
        server_default=expression.false()))
    op.alter_column('userphone', 'primary', server_default=None)
    op.alter_column('useremail', 'primary', server_default=None)

    # 2. Update primary flags
    op.execute(sa.DDL('''
        UPDATE useremail SET "primary" = true
        FROM user_useremail_primary WHERE useremail.id = user_useremail_primary.useremail_id;
        '''))
    op.execute(sa.DDL('''
        UPDATE userphone SET "primary" = true
        FROM user_userphone_primary WHERE userphone.id = user_userphone_primary.userphone_id;
        '''))

    # 3. Drop primary tables
    op.execute(sa.DDL('''
        DROP TRIGGER user_userphone_primary_trigger ON user_userphone_primary;
        DROP FUNCTION user_userphone_primary_validate();
        '''))
    op.execute(sa.DDL('''
        DROP TRIGGER user_useremail_primary_trigger ON user_useremail_primary;
        DROP FUNCTION user_useremail_primary_validate();
        '''))
    op.drop_table('user_userphone_primary')
    op.drop_table('user_useremail_primary')

    def tasks_query(self, q, marker=None, offset=None, limit=None,
                    current_user=None, **kwargs):
        session = api_base.get_session()
        query = api_base.model_query(models.Task, session)

        # Filter out tasks or stories that the current user can't see
        query = query.outerjoin(models.Story,
                                models.story_permissions,
                                models.Permission,
                                models.user_permissions,
                                models.User)
        if current_user is not None:
            query = query.filter(
                or_(
                    and_(
                        models.User.id == current_user,
                        models.Story.private == true()
                    ),
                    models.Story.private == false()
                )
            )
        else:
            query = query.filter(models.Story.private == false())

        query = self._build_fulltext_search(models.Task, query, q)
        query = self._apply_pagination(
            models.Task, query, marker, offset, limit)

        return query.all()

def task_build_query(project_group_id, current_user=None, **kwargs):
    # Construct the query
    query = api_base.model_query(models.Task)

    if project_group_id:
        query = query.join(models.Project,
                           models.project_group_mapping,
                           models.ProjectGroup) \
            .filter(models.ProjectGroup.id == project_group_id)

    # Sanity check on input parameters
    query = api_base.apply_query_filters(query=query,
                                         model=models.Task,
                                         **kwargs)

    # Filter out tasks or stories that the current user can't see
    query = query.outerjoin(models.Story,
                            models.story_permissions,
                            models.Permission,
                            models.user_permissions,
                            models.User)
    if current_user is not None:
        query = query.filter(
            or_(
                and_(
                    models.User.id == current_user,
                    models.Story.private == true()
                ),
                models.Story.private == false()
            )
        )
    else:
        query = query.filter(models.Story.private == false())

    return query

def filter_private_stories(query, current_user, story_model=models.Story):
    """Takes a query and filters out stories the user shouldn't see.

    :param query: The query to be filtered.
    :param current_user: The ID of the user requesting the result.
    :param story_model: The database model used for stories in the query.

    """
    # First filter based on users with permissions set directly
    query = query.outerjoin(models.story_permissions,
                            models.Permission,
                            models.user_permissions,
                            models.User)
    if current_user:
        visible_to_users = query.filter(
            or_(
                and_(
                    models.User.id == current_user,
                    story_model.private == true()
                ),
                story_model.private == false(),
                story_model.id.is_(None)
            )
        )
    else:
        visible_to_users = query.filter(
            or_(
                story_model.private == false(),
                story_model.id.is_(None)
            )
        )

    # Now filter based on membership of teams with permissions
    users = aliased(models.User, name="story_users")
    query = query.outerjoin(models.team_permissions,
                            models.Team,
                            models.team_membership,
                            (users,
                             users.id == models.team_membership.c.user_id))
    if current_user:
        visible_to_teams = query.filter(
            or_(
                and_(
                    users.id == current_user,
                    story_model.private == true()
                ),
                story_model.private == false(),
                story_model.id.is_(None)
            )
        )
    else:
        visible_to_teams = query.filter(
            or_(
                story_model.private == false(),
                story_model.id.is_(None)
            )
        )

    return visible_to_users.union(visible_to_teams)

def create_spam_checks():
    "Generate file based spam checks ruleset"
    users_q = Session.query(User).filter(User.spam_checks == false())
    users = windowed_query(users_q, User.id, 100)
    domains = Session.query(Domain).filter(Domain.spam_checks == false()).all()
    kwargs = dict(users=users, domains=domains)
    write_ruleset('spam.checks.rules', kwargs)
    Session.close()

 def test_malformed_range(self):
     # missing the "TO" separator
     clause = lucene_to_sqlalchemy(u'[what]',
             {'fqdn': System.fqdn, 'memory': System.memory},
             [System.fqdn, System.memory])
     self.assert_clause_equals(clause,
             or_(System.fqdn == u'[what]', false()))
     clause = lucene_to_sqlalchemy(u'memory:[1024, 2048]',
             {'fqdn': System.fqdn, 'memory': System.memory},
             [System.fqdn, System.memory])
     self.assert_clause_equals(clause, and_(false()))

 def get_history_datasets(self, trans, history):
     """
     Returns history's datasets.
     """
     query = (trans.sa_session.query(trans.model.HistoryDatasetAssociation)
              .filter(trans.model.HistoryDatasetAssociation.history == history)
              .join("dataset")
              .options(eagerload_all("dataset.actions"))
              .order_by(trans.model.HistoryDatasetAssociation.hid)
              .filter(trans.model.HistoryDatasetAssociation.deleted == expression.false())
              .filter(trans.model.Dataset.purged == expression.false()))
     return query.all()

def get_summary(value_class):
    q = db_session.query(
            func.date(SR_Values.datetime).label("date")
            , func.sum(SR_Values.value).label("daily_value")
        ).filter(SR_Classes.id == SR_Values.value_class_id
        ).filter(SR_Classes.accum_flag == true()
        ).filter(SR_Classes.value_class == value_class
        ).filter(SR_Values.datetime > datetime.datetime(datetime.datetime.now().year, 1, 1)
        ).group_by(SR_Classes.value_class, func.month(SR_Values.datetime)
        ).order_by(SR_Classes.value_class, func.date(SR_Values.datetime))
    print q
    rows = [{     "name": x.date
                , "value": x.daily_value
                } for x in q.all()]
    q = db_session.query(
            func.date(SR_Values.datetime).label("date")
            , func.avg(SR_Values.value).label("daily_value")
        ).filter(SR_Classes.id == SR_Values.value_class_id
        ).filter(SR_Classes.accum_flag == false()
        ).filter(SR_Classes.value_class == value_class
        ).filter(SR_Values.datetime > datetime.datetime(datetime.datetime.now().year, 1, 1)
        ).group_by(SR_Classes.value_class, func.month(SR_Values.datetime)
        ).order_by(SR_Classes.value_class, func.date(SR_Values.datetime))
    rows.extend([{     "name": x.date
                    , "value": x.daily_value
                    } for x in q.all()])
    print rows
    return rows

    def _get_group_project_roles(self, session, groups, project_id,
                                 project_domain_id):
        # NOTE(rodrigods): First, we always include projects with
        # non-inherited assignments
        sql_constraints = sqlalchemy.and_(
            RoleAssignment.type == AssignmentType.GROUP_PROJECT,
            RoleAssignment.inherited == false(),
            RoleAssignment.target_id == project_id)
        if CONF.os_inherit.enabled:
            # Inherited roles from domains
            sql_constraints = sqlalchemy.or_(
                sql_constraints,
                sqlalchemy.and_(
                    RoleAssignment.type == AssignmentType.GROUP_DOMAIN,
                    RoleAssignment.inherited,
                    RoleAssignment.target_id == project_domain_id))

            # Inherited roles from projects
            project_parents = [x['id']
                               for x in self.list_project_parents(project_id)]
            sql_constraints = sqlalchemy.or_(
                sql_constraints,
                sqlalchemy.and_(
                    RoleAssignment.type == AssignmentType.GROUP_PROJECT,
                    RoleAssignment.inherited,
                    RoleAssignment.target_id.in_(project_parents)))
        sql_constraints = sqlalchemy.and_(sql_constraints,
                                          RoleAssignment.actor_id.in_(groups))

        # NOTE(morganfainberg): Only select the columns we actually care about
        # here, in this case role_id.
        query = session.query(RoleAssignment.role_id).filter(
            sql_constraints).distinct()

        return [result.role_id for result in query.all()]

def upgrade():
    create_table('users',
        Column('id', Integer, primary_key=True),
        Column('screen_name', String, nullable=False, unique=True),
        Column('twitter_user_id', BigInteger, nullable=False),
        Column('twitter_oauth_token', String, nullable=False),
        Column('twitter_oauth_token_secret', String, nullable=False),
        Column('admin', Boolean, server_default=false(), nullable=False),
        Column('created_at', DateTime(timezone=True), nullable=False),
    )
    create_table('canvases',
        Column('id', Integer, primary_key=True),
        Column('artist_id', Integer, ForeignKey('users.id'), nullable=False),
        Column('title', UnicodeText),
        Column('description', UnicodeText),
        Column('strokes', JSON),
        Column('width', Integer, nullable=False),
        Column('height', Integer, nullable=False),
        Column('broadcast', Boolean, nullable=False),
        Column('replay', Boolean, nullable=False),
        Column('created_at', DateTime(timezone=True), nullable=False),
    )
    create_table('collaborations',
        Column('canvas_id', Integer, ForeignKey('canvases.id'), primary_key=True),
        Column('artist_id', Integer, ForeignKey('users.id'), primary_key=True),
        Column('created_at', DateTime(timezone=True), nullable=False),
    )

 def manage_users_and_groups_for_quota( self, trans, **kwd ):
     quota, params = self._quota_op( trans, 'quota_members_edit_button', self._manage_users_and_groups_for_quota, kwd )
     if not quota:
         return
     in_users = []
     out_users = []
     in_groups = []
     out_groups = []
     for user in trans.sa_session.query( trans.app.model.User ) \
                                 .filter( trans.app.model.User.table.c.deleted == expression.false() ) \
                                 .order_by( trans.app.model.User.table.c.email ):
         if user in [ x.user for x in quota.users ]:
             in_users.append( ( user.id, user.email ) )
         else:
             out_users.append( ( user.id, user.email ) )
     for group in trans.sa_session.query( trans.app.model.Group ) \
                                  .filter( trans.app.model.Group.table.c.deleted == expression.false()) \
                                  .order_by( trans.app.model.Group.table.c.name ):
         if group in [ x.group for x in quota.groups ]:
             in_groups.append( ( group.id, group.name ) )
         else:
             out_groups.append( ( group.id, group.name ) )
     return trans.fill_template( '/admin/quota/quota.mako',
                                 id=params.id,
                                 name=quota.name,
                                 in_users=in_users,
                                 out_users=out_users,
                                 in_groups=in_groups,
                                 out_groups=out_groups,
                                 webapp=params.webapp,
                                 message=params.message,
                                 status=params.status )

    def test_assigns_boolean_server_defaults(self):
        is_admin = self.columns.is_admin
        is_active = self.columns.is_active
        assert is_admin.default.arg is True

        assert is_admin.server_default.arg.__class__ == true().__class__
        assert is_active.server_default.arg.__class__ == false().__class__

    def read(self):
        """Mark badge notifications from all activities read up to a timestamp.

        Takes in an unixtime timestamp.

        Fetches the IDs for all all unread badge notifications for the current
        user, which come from activities created at up to the passed in
        timestamp.

        Marks those notifications as read.
        """
        timestamp = self.request.get('timestamp')
        if not timestamp:
            raise Exception('Missing parameter `timestamp`')
        timestamp = int(timestamp)
        timestamp = datetime.fromtimestamp(int(timestamp), pytz.UTC)
        userid = api.user.get_current().getId()
        notifications = (
            Notification.query
            .join(Activity)
            .filter(
                Notification.userid == userid,
                Notification.is_badge == true(),
                Notification.is_read == false(),
                Activity.created < timestamp,
            )
            .all()
        )
        notification_ids = [n.notification_id for n in notifications]
        return notification_center().mark_notifications_as_read(
            notification_ids)

 def share(self, trans, id, email="", use_panels=False):
     msg = mtype = None
     # Load workflow from database
     stored = self.get_stored_workflow(trans, id)
     if email:
         other = trans.sa_session.query(model.User) \
                                 .filter(and_(model.User.table.c.email == email,
                                              model.User.table.c.deleted == expression.false())) \
                                 .first()
         if not other:
             mtype = "error"
             msg = ("User '%s' does not exist" % escape(email))
         elif other == trans.get_user():
             mtype = "error"
             msg = ("You cannot share a workflow with yourself")
         elif trans.sa_session.query(model.StoredWorkflowUserShareAssociation) \
                 .filter_by(user=other, stored_workflow=stored).count() > 0:
             mtype = "error"
             msg = ("Workflow already shared with '%s'" % escape(email))
         else:
             share = model.StoredWorkflowUserShareAssociation()
             share.stored_workflow = stored
             share.user = other
             session = trans.sa_session
             session.add(share)
             session.flush()
             trans.set_message("Workflow '%s' shared with user '%s'" % (escape(stored.name), escape(other.email)))
             return trans.response.send_redirect(url_for(controller='workflow', action='sharing', id=id))
     return trans.fill_template("/ind_share_base.mako",
                                message=msg,
                                messagetype=mtype,
                                item=stored,
                                email=email,
                                use_panels=use_panels)

    def test_assigns_boolean_server_defaults(self, User):
        is_admin = User.__table__.c.is_admin
        is_active = User.__table__.c.is_active
        assert is_admin.default.arg is True

        assert is_admin.server_default.arg.__class__ == true().__class__
        assert is_active.server_default.arg.__class__ == false().__class__

def category(name):
    random = db.session.query(db.Books).filter(false())
    if name != "all":
        entries = db.session.query(db.Books).filter(db.Books.tags.any(db.Tags.name.like("%" +name + "%" ))).order_by(db.Books.last_modified.desc()).all()
    else:
        entries = db.session.query(db.Books).all()
    return render_template('index.html', random=random, entries=entries, title="Category: %s" % name)

 def test_integer_column(self):
     clause = lucene_to_sqlalchemy(u'memory:1024',
             {'memory': System.memory}, [System.memory])
     self.assert_clause_equals(clause, System.memory == 1024)
     # searching invalid numbers against a numeric column is just False
     clause = lucene_to_sqlalchemy(u'memory:much',
             {'memory': System.memory}, [System.memory])
     self.assert_clause_equals(clause, and_(false()))

    def get_users_notifications(self, userid, only_unread=False, limit=None):
        query = Notification.query.by_user(userid)
        if only_unread:
            query = query.filter(Notification.is_read == false())

        query = query.join(
            Notification.activity).order_by(desc(Activity.created))
        return query.limit(limit).all()

def upgrade():
    ### commands auto generated by Alembic - please adjust! ###
    op.add_column('vacancies', sa.Column('deleted', sa.Boolean(), nullable=False,
                                         server_default=sa.DefaultClause(false())))
    op.add_column('vacancies', sa.Column('updated_at', sa.DateTime(), nullable=True,
                                         server_default=sa.DefaultClause(str(datetime.datetime.now()))))
    op.add_column('vacancies', sa.Column('user_id', sa.Integer(), nullable=True))
    op.create_foreign_key(None, 'vacancies', 'users', ['user_id'], ['id'])

 def all_by_account(cls, account):
     """ Query available datasets based on dataset visibility. """
     criteria = [cls.private == false()]
     if account is not None:
         criteria += ["1=1" if account.admin else "1=2", cls.managers.any(type(account).id == account.id)]
     q = db.session.query(cls).filter(db.or_(*criteria))
     q = q.order_by(cls.label.asc())
     return q