def test_bans_get(self):
        from datetime import timedelta
        from sqlalchemy.sql import func
        from ..interfaces import IBanQueryService
        from ..models import Ban
        from ..services import BanQueryService, ScopeService
        from ..views.admin import bans_get
        from . import mock_service

        ban1 = self._make(
            Ban(ip_address="10.0.1.0/24", active_until=func.now() + timedelta(hours=1))
        )
        self._make(
            Ban(ip_address="10.0.2.0/24", active_until=func.now() - timedelta(hours=1))
        )
        ban3 = self._make(Ban(ip_address="10.0.3.0/24"))
        self._make(Ban(ip_address="10.0.3.0/24", active=False))
        ban5 = self._make(
            Ban(ip_address="10.0.3.0/24", active_until=func.now() + timedelta(hours=2))
        )
        ban6 = self._make(
            Ban(ip_address="10.0.3.0/24", created_at=func.now() + timedelta(minutes=5))
        )
        self.dbsession.commit()
        request = mock_service(
            self.request,
            {IBanQueryService: BanQueryService(self.dbsession, ScopeService())},
        )
        request.method = "GET"
        response = bans_get(request)
        self.assertEqual(response["bans"], [ban6, ban3, ban5, ban1])

def _process_request(context, organization_id, member, status):
    ''' Cancel a member request or existing membership.
        Delete from database the member request (if existing) and set delete state in member table
    :param member: id of the member
    :type member: string
    '''
    user = context.get("user")

    # Logical delete on table member
    member.state = 'deleted'
    # Fetch the newest member_request associated to this membership (sort by
    # last modified field)
    member_request = model.Session.query(MemberRequest).filter(
        MemberRequest.membership_id == member.id).order_by('request_date desc').limit(1).first()

    # BFW: Create a new instance every time membership status is changed
    message = u'MemberRequest cancelled by own user'
    locale = get_safe_locale()
    mrequest_date = func.now()
    if member_request is not None and member_request.status == status:
        locale = member_request.language
        mrequest_date = member_request.request_date

    member_request = MemberRequest(membership_id=member.id, role=member.capacity, status="cancel", request_date=mrequest_date,
                                   language=locale, handling_date=func.now(), handled_by=c.userobj.name, message=message)
    model.Session.add(member_request)

    revision = model.repo.new_revision()
    revision.author = user
    revision.message = u'Member request deleted by user'

    member.save()
    model.repo.commit()

    return model_dictize.member_dictize(member, context)

    def init(self, auto_create=True):

        # TODO handle if user does not pass in table sqlite://path.db
        uri_splt = self.uri.split(":")
        engine_uri = u":".join(uri_splt[:-1])
        table_name = uri_splt[-1]

        metadata = MetaData()
        postref_table = Table(table_name, metadata,
                              Column('id', Integer, primary_key=True),
                              Column('created_at', DateTime, default=func.now()),
                              Column('updated_at', DateTime, default=func.now(), onupdate=func.current_timestamp()),
                              Column('uuid', String(512)),
                              Column('path', String(512)),
                              Column('revision', Integer, default=0),
                              Column('status', Integer, default=self.PostStatus.DRAFT.value),
                              Column('ref', String(512)),
                              Column('data', LargeBinary))
        self.engine = create_engine(engine_uri, pool_recycle=3600)
        self.session = scoped_session(sessionmaker(bind=self.engine))
        if auto_create:
            postref_table.create(self.engine, checkfirst=True)

        class PostRef(object):
            pass
        mapper(PostRef, postref_table)
        self.PostRef = PostRef

    def test_list_inactive(self):
        from datetime import timedelta
        from sqlalchemy.sql import func
        from ..models import Ban

        self._make(
            Ban(ip_address="10.0.1.0/24", active_until=func.now() + timedelta(hours=1))
        )
        ban2 = self._make(
            Ban(ip_address="10.0.2.0/24", active_until=func.now() - timedelta(hours=1))
        )
        self._make(Ban(ip_address="10.0.3.0/24"))
        ban4 = self._make(Ban(ip_address="10.0.3.0/24", active=False))
        ban5 = self._make(
            Ban(ip_address="10.0.3.0/24", active_until=func.now() - timedelta(hours=2))
        )
        ban6 = self._make(
            Ban(
                ip_address="10.0.3.0/24",
                created_at=func.now() + timedelta(minutes=5),
                active=False,
            )
        )
        self.dbsession.commit()
        ban_query_svc = self._make_one()
        self.assertEqual(ban_query_svc.list_inactive(), [ban6, ban4, ban2, ban5])

    def test_dashboard_get(self):
        from datetime import timedelta
        from sqlalchemy.sql import func
        from ..interfaces import IUserSessionQueryService, IUserLoginService
        from ..models import User, UserSession
        from ..services import UserSessionQueryService, UserLoginService
        from ..views.admin import dashboard_get
        from . import mock_service

        user = self._make(
            User(
                username="foo",
                encrypted_password="none",
                ident="foo",
                ident_type="ident_admin",
                name="Nameless Foo",
            )
        )
        user_session1 = self._make(
            UserSession(
                user=user,
                token="user1_token1",
                ip_address="127.0.0.1",
                last_seen_at=func.now() - timedelta(days=2),
            )
        )
        user_session2 = self._make(
            UserSession(
                user=user,
                token="user1_token2",
                ip_address="127.0.0.1",
                last_seen_at=func.now() - timedelta(days=3),
            )
        )
        user_session3 = self._make(
            UserSession(
                user=user,
                token="user1_token3",
                ip_address="127.0.0.1",
                last_seen_at=func.now() - timedelta(days=1),
            )
        )
        self.dbsession.commit()
        request = mock_service(
            self.request,
            {
                IUserLoginService: UserLoginService(self.dbsession),
                IUserSessionQueryService: UserSessionQueryService(self.dbsession),
            },
        )
        request.method = "GET"
        self.config.testing_securitypolicy(userid="user1_token3")
        response = dashboard_get(request)
        self.assertEqual(response["user"], user)
        self.assertEqual(
            response["sessions"], [user_session3, user_session1, user_session2]
        )

    def create_with_user(self, board_slug, user_id, title, body, ip_address):
        """Creates a topic similar to :meth:`create` but with user ID
        associated to it.

        This method will make the post delegate ident and name from the user
        as well as allow posting in board or topic that are not archived.

        :param board_slug: A slug :type:`str` identifying a board.
        :param user_id: A user ID :type:`int` to post as.
        :param title: A :type:`str` topic title.
        :param body: A :type:`str` topic body.
        :param ip_address: An IP address of the topic creator.
        """
        user = self.user_query_svc.user_from_id(user_id)
        board = self._prepare_c(
            board_slug, allowed_board_status=("open", "restricted", "locked")
        )

        # Create topic

        topic = Topic(
            board=board,
            title=title,
            created_at=func.now(),
            updated_at=func.now(),
            status="open",
        )

        self.dbsession.add(topic)

        # Create topic meta

        topic_meta = TopicMeta(
            topic=topic, post_count=1, posted_at=func.now(), bumped_at=func.now()
        )

        self.dbsession.add(topic_meta)

        # Create post

        ident = user.ident
        ident_type = user.ident_type
        name = user.name

        post = Post(
            topic=topic,
            number=topic_meta.post_count,
            body=body,
            bumped=True,
            name=name,
            ident=ident,
            ident_type=ident_type,
            ip_address=ip_address,
        )

        self.dbsession.add(post)
        return topic

    def create(self, board_slug, title, body, ip_address):
        """Creates a new topic and associate related metadata. Unlike
        ``enqueue``, this method performs the actual creation of the topic.

        :param board_slug: A slug :type:`str` identifying a board.
        :param title: A :type:`str` topic title.
        :param body: A :type:`str` topic body.
        :param ip_address: An IP address of the topic creator.
        """
        board = self._prepare_c(board_slug, allowed_board_status=("open",))

        # Create topic

        topic = Topic(
            board=board,
            title=title,
            created_at=func.now(),
            updated_at=func.now(),
            status="open",
        )

        self.dbsession.add(topic)

        # Create topic meta

        topic_meta = TopicMeta(
            topic=topic, post_count=1, posted_at=func.now(), bumped_at=func.now()
        )

        self.dbsession.add(topic_meta)

        # Create post

        ident = None
        ident_type = "none"
        if board.settings["use_ident"]:
            ident_type = "ident"
            ident = self.identity_svc.identity_with_tz_for(
                self.setting_query_svc.value_from_key("app.time_zone"),
                board=topic.board.slug,
                ip_address=ip_address,
            )

        post = Post(
            topic=topic,
            number=topic_meta.post_count,
            body=body,
            bumped=True,
            name=board.settings["name"],
            ident=ident,
            ident_type=ident_type,
            ip_address=ip_address,
        )

        self.dbsession.add(post)
        return topic

 def vacuum_zones(self, age_days=None):
     """
     Destroy zones older than age_days
     """
     self._begin_op()
     db_session = self.db_session
     db_query_slice = get_numeric_setting('db_query_slice', int)
     age_days_from_config = float(zone_cfg.get_row_exc(db_session,
                                 key='zone_del_age'))
     if age_days_from_config <= 0 and age_days is None:
         age_days = get_numeric_setting('zone_del_off_age', float)
     elif age_days is None:
         age_days = age_days_from_config
     age_days = timedelta(days=age_days)
     count = 0
     # Clear old and nuked zones one by one
     id_query = db_session.query(ZoneSM.id_)\
             .filter(ZoneSM.state == ZSTATE_DELETED)\
             .filter(or_(ZoneSM.deleted_start == None,
                         (func.now() - ZoneSM.deleted_start) > age_days))\
             .filter(ZoneSM.zone_files == False)\
             .yield_per(db_query_slice)
     id_results = []
     for zone_id, in id_query:
         id_results.append(zone_id)
     for zone_id in id_results:
         try:
             zone_sm = db_session.query(ZoneSM)\
                     .filter(ZoneSM.id_ == zone_id).one()
         except NoResultFound:
             continue
         if zone_sm.state != ZSTATE_DELETED:
             # Skip this if a customer has undeleted zone in the mean time..
             continue
         db_session.delete(zone_sm)
         db_session.commit()
         count += 1
                 
     # Finally do zone_sm destroy operation to 
     query = db_session.query(ZoneSM)\
             .filter(ZoneSM.state == ZSTATE_DELETED)\
             .filter(or_(ZoneSM.deleted_start == None,
                 (func.now() - ZoneSM.deleted_start) > age_days))
     for zone_sm in query:
         if zone_sm.state != ZSTATE_DELETED:
             # Skip this if a customer has undeleted zone in the mean time..
             continue
         try:
             exec_zonesm(zone_sm, ZoneSMDoDestroy)
         except ZoneSmFailure:
             continue
         count += 1
     result = {'num_deleted': count}
     self._finish_op()
     return result

 def make_filter(cls, field, ftype, value):
     filter = None
     if ftype == 'IN':
         filter = field.in_([v for v in value if v])
     elif ftype == 'date_gt':
         filter = field >  value
     elif ftype == 'date_gte':
         filter = field >= value
     elif ftype == 'date_gt_now_less':
         qty, granularity = value.split(" ")
         filter = field > func.date_trunc(granularity, func.now() - cast(value, Interval()))
     elif ftype == 'date_lt_now_less':
         qty, granularity = value.split(" ")
         filter = field < func.date_trunc(granularity, func.now() - cast(value, Interval()))
     elif ftype == 'date_x_last_n':
         qty, granularity, count_current_period = value.split(" ")
         filter = (field > func.date_trunc(granularity, func.now() - cast("%s %s" % (qty, granularity), Interval())), field < func.date_trunc(granularity, func.now() - cast('0', Interval())),)
         if count_current_period == 'on':
             filter = filter[0]
     elif ftype == 'date_month_ne':
         filter = extract('month', field) != value
     elif ftype == 'date_month_gt':
         filter = extract('month', field) > value
     elif ftype == 'date_month_lt':
         filter = extract('month', field) < value
     elif ftype == 'date_month_eq':
         filter = extract('month', field) == value
     elif ftype == 'date_hour_ne':
         filter = extract('hour', field) != value
     elif ftype == 'date_hour_gt':
         filter = extract('hour', field) > value
     elif ftype == 'date_hour_lt':
         filter = extract('hour', field) < value
     elif ftype == 'date_hour_eq':
         filter = extract('hour', field) == value
     elif ftype == 'date_lt':
         filter = field <  value
     elif ftype == 'date_lte':
         filter = field <= value
     elif ftype == '=':
         filter = field == value
     elif ftype == '!=':
         filter = field != value
     elif ftype == '>':
         filter = field >  value
     elif ftype == '>=':
         filter = field >= value
     elif ftype == '<':
         filter = field <  value
     elif ftype == '<=':
         filter = field <= value
     elif ftype == 'like':
         filter = field.ilike(value)
     return filter

 def _make_topic(days=0, hours=0, **kwargs):
     topic = self._make(Topic(**kwargs))
     self._make(
         TopicMeta(
             topic=topic,
             post_count=0,
             posted_at=func.now(),
             bumped_at=func.now() - timedelta(days=days, hours=hours),
         )
     )
     return topic

 def update_user_data(self, username, type, new_data):
     new_data = json.dumps(new_data)
     res = self.engine.execute(
         UserStorage.__table__.update((UserStorage.__table__.c.userid == username) & (UserStorage.__table__.c.type == type)),
         data=new_data,
         last_updated=func.now())
     if not res.rowcount:
         self.engine.execute(
             UserStorage.__table__.insert(),
             userid=username,
             type=type,
             data=new_data,
             last_updated=func.now())

def register():
	user_info = {
	'first_name': request.json['first_name'],
	'last_name': request.json['last_name'],
	'email': request.json['email'],
	'password': request.json['password'],
	'password_cnf': request.json['confirm_password']
	}
	user_status = validate_user(user_info)
	if user_status['flag'] == True :
		return jsonify(error = True)
	else :
		current_time = func.now()
		pw_hash=bcrypt.generate_password_hash(request.json['password'])
		user = Users(first_name = request.json['first_name'],last_name = request.json['last_name'],email = request.json['email'],password = pw_hash,created_at = current_time,updated_at = current_time)
		print("user is",user.__dict__)
		session['current_user'] = request.json['first_name'] + request.json['last_name']
		sql_alchemy_session.add(user)
		#Flush() to add the current transaction to the db and get the auto generated user_idfo rthis transaction
		sql_alchemy_session.flush()
		user_id = user.__dict__.get('user_id')
		#commit() commits any pending changes to the db
		sql_alchemy_session.commit()

		return jsonify(error = False,username = session['current_user'],user_id = user_id)

 def vacuum_event_queue(self, age_days=None):
     """
     Destroy events processed more than age_days ago
     """
     self._begin_op()
     db_session = self.db_session
     if age_days is None:
         age_days = float(zone_cfg.get_row_exc(db_session,
                             key='event_max_age'))
     age_days = timedelta(days=age_days)
     count = 0
     # Do a straight SQL DELETE first to speed things along
     # Count events to be deleted
     event_table = sql_data['tables'][Event]
     where_stmt = and_(Event.state.in_(event_processed_states),
                         Event.processed != None,
                         (func.now() - Event.processed) > age_days)
     count_select = select([func.count(event_table.c.get('id'))],
                             where_stmt)
     result = db_session.execute(count_select).fetchall()
     count += result[0][0]
     db_session.execute(event_table.delete().where(where_stmt))
                 
     result = {'num_deleted': count}
     self._finish_op()
     return result

    def _user_session_c(self, token, ip_address):
        """Internal method for querying user session object and cache
        it throughout the request lifecycle.

        :param token: A user login token :type:`str`.
        :param ip_address: IP address of the user.
        """
        if not (token, ip_address) in self.sessions_map:
            user_session = (
                self.dbsession.query(UserSession)
                .options(joinedload(UserSession.user))
                .filter(
                    and_(
                        UserSession.token == token,
                        UserSession.ip_address == ip_address,
                        UserSession.last_seen_at != None,  # noqa: E711
                        or_(
                            UserSession.revoked_at == None,  # noqa: E711
                            UserSession.revoked_at >= func.now(),
                        ),
                    )
                )
                .first()
            )
            self.sessions_map[(token, ip_address)] = user_session
        return self.sessions_map[(token, ip_address)]

 def vacuum_syslog (self, age_days=None):
     """
     Destroy syslog messages received more than age_days ago
     """
     self._begin_op()
     db_session = self.db_session
     if age_days is None:
         age_days = float(zone_cfg.get_row_exc(db_session,
             key='syslog_max_age'))
     age_days = timedelta(days=age_days)
     count = 0
     # Do a straight SQL DELETE first to speed things along
     # Count events to be deleted
     syslog_table = sql_data['tables'][SyslogMsg]
     where_stmt = and_(SyslogMsg.receivedat != None,
                         (func.now() - SyslogMsg.receivedat) > age_days)
     count_select = select([func.count(syslog_table.c.get('id'))],
                             where_stmt)
     result = db_session.execute(count_select).fetchall()
     count += result[0][0]
     db_session.execute(syslog_table.delete().where(where_stmt))
                 
     result = {'num_deleted': count}
     self._finish_op()
     return result

    def test_ban_edit_get(self):
        from datetime import timedelta
        from sqlalchemy.sql import func
        from ..models import Ban
        from ..interfaces import IBanQueryService
        from ..services import BanQueryService, ScopeService
        from ..views.admin import ban_edit_get
        from . import mock_service

        now = func.now()
        ban = self._make(
            Ban(
                ip_address="10.0.0.0/24",
                description="Violation of galactic law",
                active_until=now + timedelta(days=30),
                scope="galaxy_far_away",
                active=True,
                created_at=now,
            )
        )
        self.dbsession.commit()
        request = mock_service(
            self.request,
            {IBanQueryService: BanQueryService(self.dbsession, ScopeService())},
        )
        request.method = "GET"
        request.matchdict["ban"] = str(ban.id)
        response = ban_edit_get(request)
        self.assertEqual(response["ban"], ban)
        self.assertEqual(response["form"].ip_address.data, "10.0.0.0/24")
        self.assertEqual(response["form"].description.data, "Violation of galactic law")
        self.assertEqual(response["form"].duration.data, 30)
        self.assertEqual(response["form"].scope.data, "galaxy_far_away")
        self.assertTrue(response["form"].active.data)

 def ping(self):
     """Use this active session to check the database connectivity."""
     try:
         self.execute(select([func.now()])).first()
     except exc.OperationalError:
         return False
     return True

    def bulk_get(self, queue, message_ids, project):
        if project is None:
            project = ''

        message_ids = [id for id in
                       map(utils.msgid_decode, message_ids)
                       if id is not None]

        statement = sa.sql.select([tables.Messages.c.id,
                                   tables.Messages.c.body,
                                   tables.Messages.c.ttl,
                                   tables.Messages.c.created])

        and_stmt = [tables.Messages.c.id.in_(message_ids),
                    tables.Queues.c.name == queue,
                    tables.Queues.c.project == project,
                    tables.Messages.c.ttl >
                    sfunc.now() - tables.Messages.c.created]

        j = sa.join(tables.Messages, tables.Queues,
                    tables.Messages.c.qid == tables.Queues.c.id)

        statement = statement.select_from(j).where(sa.and_(*and_stmt))

        now = timeutils.utcnow_ts()
        records = self.driver.run(statement)
        for id, body, ttl, created in records:
            yield {
                'id': utils.msgid_encode(id),
                'ttl': ttl,
                'age': now - calendar.timegm(created.timetuple()),
                'body': json.loads(body),
            }

    def test_relations(self):
        from datetime import timedelta
        from sqlalchemy.sql import func
        from ..models import User, UserSession

        user = self._make(
            User(
                username="foo",
                encrypted_password="none",
                ident="fooident",
                name="Nameless Foo",
            )
        )
        session1 = self._make(
            UserSession(
                user=user,
                token="test1",
                ip_address="127.0.0.1",
                created_at=func.now() - timedelta(days=1),
            )
        )
        session2 = self._make(
            UserSession(user=user, token="test2", ip_address="127.0.0.1")
        )
        self.dbsession.commit()
        self.assertEqual(session1.user, user)
        self.assertEqual(session2.user, user)
        self.assertEqual(list(user.sessions), [session2, session1])

    def get(self, queue, message_id, project, count=False):

        if project is None:
            project = ''

        mid = utils.msgid_decode(message_id)

        if mid is None:
            raise errors.MessageDoesNotExist(message_id, queue, project)

        try:
            j = sa.join(tables.Messages, tables.Queues,
                        tables.Messages.c.qid == tables.Queues.c.id)

            sel = sa.sql.select([tables.Messages.c.body,
                                 tables.Messages.c.ttl,
                                 tables.Messages.c.created])

            if count:
                sel = sa.sql.select([sfunc.count(tables.Messages.c.id)])

            sel = sel.select_from(j)
            sel = sel.where(sa.and_(tables.Messages.c.id == mid,
                                    tables.Queues.c.project == project,
                                    tables.Queues.c.name == queue,
                                    tables.Messages.c.ttl >
                                    sfunc.now() - tables.Messages.c.created))

            return self.driver.get(sel)[0]
        except utils.NoResult:
            raise errors.MessageDoesNotExist(message_id, queue, project)