def authenticate(self, session):
     '''
     @see: IAuthenticationService.authenticate
     '''
     olderThan = self.session().query(current_timestamp()).scalar()
     olderThan -= self._sessionTimeOut
     sql = self.session().query(LoginMapped)
     sql = sql.filter(LoginMapped.Session == session)
     sql = sql.filter(LoginMapped.AccessedOn > olderThan)
     try: login = sql.one()
     except NoResultFound: raise InputError(Ref(_('Invalid session'), ref=Login.Session))
     assert isinstance(login, LoginMapped), 'Invalid login %s' % login
     login.AccessedOn = current_timestamp()
     self.session().flush((login,))
     self.session().expunge(login)
     commitNow()
     # We need to fore the commit because if there is an exception while processing the request we need to make
     # sure that the last access has been updated.
     
     userId = str(login.User)
     rights = (right.Name for right in self.userRbacService.getRights(login.User))
     accesses = self.aclAccessService.accessFor(self.aclAccessService.rightsFor(rights))
     allowed = []
     for access in accesses:
         assert isinstance(access, AclAccess), 'Invalid access %s' % access
         for propertyType, mark in access.markers.items():
             assert isinstance(propertyType, TypeProperty), 'Invalid property type %s' % propertyType
             assert isinstance(propertyType.parent, TypeModel)
             if propertyType.parent.clazz == User or issubclass(propertyType.parent.clazz, User):
                 for k in range(len(access.Filter)): access.Filter[k] = access.Filter[k].replace(mark, userId)
         allowed.append(access)
     return allowed

    def authenticate(self, identifier, attributes, arguments):
        '''
        @see: IAuthenticationSupport.authenticate
        '''
        assert isinstance(identifier, str), 'Invalid identifier %s' % identifier
        assert isinstance(attributes, dict), 'Invalid attributes %s' % attributes
        assert isinstance(arguments, dict), 'Invalid arguments %s' % arguments

        olderThan = self.session().query(current_timestamp()).scalar()
        olderThan -= self.sessionTimeOut
        sql = self.session().query(LoginMapped)
        sql = sql.filter(LoginMapped.Session == identifier)
        sql = sql.filter(LoginMapped.AccessedOn > olderThan)
        try: login = sql.one()
        except NoResultFound: return False
        assert isinstance(login, LoginMapped), 'Invalid login %s' % login
        login.AccessedOn = current_timestamp()
        self.session().flush((login,))
        self.session().expunge(login)
        commitNow()
        # We need to fore the commit because if there is an exception while processing the request we need to make
        # sure that the last access has been updated.

        for authType in arguments:
            assert isinstance(authType, Type), 'Invalid type %s' % authType

            if authType == typeFor(User.Id): arguments[authType] = login.User
            else: raise DevelError('Invalid authenticated type %s' % authType)

        return True

def make_changes_table(table, metadata):
    """Create an object log table for an object.
    """
    table_name = table.name
    entity_name = singular(table_name)
    changes_name = "%s_changes" % (entity_name)
    fk_id = "%s_id" % (entity_name)
    changes_table = rdb.Table(changes_name, metadata,
        rdb.Column("change_id", rdb.Integer, primary_key=True),
        # the item_id of the "owning" item being logged !+HEAD_DOCUMENT_ITEM
        rdb.Column("content_id", rdb.Integer, 
            rdb.ForeignKey(table.c[fk_id]),
            nullable=False,
            index=True
        ),
        rdb.Column("action", rdb.Unicode(16)),
        # audit date, exclusively managed by the system
        rdb.Column("date_audit", rdb.DateTime(timezone=False),
            default=functions.current_timestamp(),
            nullable=False
        ),
        # user-modifiable effective date, defaults to same value as audit date;
        # this is the date to be used for all intents and purposes other than 
        # for data auditing
        rdb.Column("date_active", rdb.DateTime(timezone=False),
            default=functions.current_timestamp(),
            nullable=False
        ),
        rdb.Column("description", rdb.UnicodeText),
        rdb.Column("notes", rdb.UnicodeText),
        rdb.Column("user_id", rdb.Integer, rdb.ForeignKey("users.user_id")),
        #!+SA0.7 rdb.Index("%s_changes_cid_idx" % (entity_name), "content_id"),
        useexisting=False
    )
    return changes_table

 def getGateways(self, session):
     '''
     @see: IAuthenticationService.getGateways
     '''
     olderThan = self.session().query(current_timestamp()).scalar()
     olderThan -= self._sessionTimeOut
     sql = self.session().query(LoginMapped)
     sql = sql.filter(LoginMapped.Session == session)
     sql = sql.filter(LoginMapped.AccessedOn > olderThan)
     try: login = sql.one()
     except NoResultFound: return ()
     assert isinstance(login, LoginMapped), 'Invalid login %s' % login
     login.AccessedOn = current_timestamp()
     self.session().flush((login,))
     self.session().expunge(login)
     commitNow()
     
     # We need to fore the commit because if there is an exception while processing the request we need to make
     # sure that the last access has been updated.
     proc = self._processing
     assert isinstance(proc, Processing), 'Invalid processing %s' % proc
     
     solicit = proc.execute(FILL_CLASSES, solicit=proc.ctx.solicit(acl=login.User)).solicit
     assert isinstance(solicit, Solicit), 'Invalid solicit %s' % solicit
     return solicit.gateways or ()

def make_changes_table(table, metadata):
    """Create an object log table for an object.
    """
    table_name = table.name
    entity_name = singular(table_name)
    changes_name = "%s_changes" % (entity_name)
    fk_id = "%s_id" % (entity_name)
    changes_table = rdb.Table(changes_name, metadata,
        rdb.Column("change_id", rdb.Integer, primary_key=True),
        rdb.Column("content_id", rdb.Integer, rdb.ForeignKey(table.c[fk_id])),
        rdb.Column("action", rdb.Unicode(16)),
        # audit date, exclusively managed by the system
        rdb.Column("date_audit", rdb.DateTime(timezone=False),
            default=functions.current_timestamp(),
            nullable=False
        ),
        # user-modifiable effective date, defaults to same value as audit date;
        # this is the date to be used for all intents and purposes other than 
        # for data auditing
        rdb.Column("date_active", rdb.DateTime(timezone=False),
            default=functions.current_timestamp(),
            nullable=False
        ),
        rdb.Column("description", rdb.UnicodeText),
        rdb.Column("notes", rdb.UnicodeText),
        rdb.Column("user_id", rdb.Integer, rdb.ForeignKey("users.user_id")),
        useexisting=True # !+ZCA_TESTS(mr, jul-2011) tests break without this
    )
    return changes_table

 def authenticate(self, session):
     '''
     @see: IAuthenticationService.authenticate
     '''
     olderThan = self.session().query(current_timestamp()).scalar()
     olderThan -= self._sessionTimeOut
     sql = self.session().query(LoginMapped)
     sql = sql.filter(LoginMapped.Session == session)
     sql = sql.filter(LoginMapped.AccessedOn > olderThan)
     try: login = sql.one()
     except NoResultFound: raise InputError(Ref(_('Invalid session'), ref=Login.Session))
     assert isinstance(login, LoginMapped), 'Invalid login %s' % login
     login.AccessedOn = current_timestamp()
     self.session().flush((login,))
     self.session().expunge(login)
     commitNow()
     
     # We need to fore the commit because if there is an exception while processing the request we need to make
     # sure that the last access has been updated.
     proc = self._processing
     assert isinstance(proc, Processing), 'Invalid processing %s' % proc
     
     solicitation = proc.ctx.solicitation()
     assert isinstance(solicitation, Solicitation), 'Invalid solicitation %s' % solicitation
     solicitation.userId = login.User
     solicitation.types = self.acl.types
     
     chain = Chain(proc)
     chain.process(**proc.fillIn(solicitation=solicitation, reply=proc.ctx.reply())).doAll()
     
     reply = chain.arg.reply
     assert isinstance(reply, Reply), 'Invalid reply %s' % reply
     if reply.gateways is None: return ()
     
     return sorted(reply.gateways, key=lambda gateway: (gateway.Pattern, gateway.Methods))

def last_update_time() -> sa.Column:
    """A timestamp column set to CURRENT_TIMESTAMP on update.

    Return a column containing the time that a record was last updated.

    :return: a SQLAlchemy Column for a datetime with time zone auto-updating
             column
    """
    return sa.Column(
        pg.TIMESTAMP(timezone=True),
        nullable=False,
        server_default=current_timestamp(),
        onupdate=current_timestamp(),
    )

 def update_available_cookies(self, user):
     now = datetime.datetime.now()
     now = datetime.datetime(now.year,now.month,now.day)
     if not user.last_cookie_date or (now - user.last_cookie_date).days > 0:
         user.available_cookies = Config.getint("Alliance","cookies")
         user.last_cookie_date = current_timestamp()
         session.commit()

 def cancel(self, message, user, params):
     id = params.group(1)
     prop = self.load_prop(id)
     if prop is None:
         message.reply("No proposition number %s exists (idiot)."%(id,))
         return
     if not prop.active:
         message.reply("You can't cancel prop %d, it's already expired."%(prop.id,))
         return
     if prop.proposer is not user and not user.is_admin():
         message.reply("Only %s may cancel proposition %d."%(prop.proposer.name,prop.id))
         return
     
     self.recalculate_carebears(prop)
     
     yes, no, veto = self.sum_votes(prop)
     vote_result = "cancel"
     
     reply = self.text_result(vote_result, yes, no, veto)
     reply+= self.text_summary(prop)
     message.reply(reply)
     
     prop.active = False
     prop.closed = current_timestamp()
     prop.vote_result = vote_result
     session.commit()

    def insert(self, post):
        '''
        @see: IPostService.insert
        '''
        assert isinstance(post, Post), 'Invalid post %s' % post
        postDb = PostMapped()
        copy(post, postDb, exclude=COPY_EXCLUDE)
        postDb.typeId = self._typeId(post.Type)

        postDb = self._adjustTexts(postDb)

        if post.CreatedOn is None: postDb.CreatedOn = current_timestamp()
        if not postDb.Author:
            colls = self.session().query(CollaboratorMapped).filter(CollaboratorMapped.User == postDb.Creator).all()
            if not colls:
                coll = CollaboratorMapped()
                coll.User = postDb.Creator
                src = self.session().query(SourceMapped).filter(SourceMapped.Name == PostServiceAlchemy.default_source_name).one()
                coll.Source = src.Id
                self.session().add(coll)
                self.session().flush((coll,))
                colls = (coll,)
            postDb.Author = colls[0].Id

        self.session().add(postDb)
        self.session().flush((postDb,))
        post.Id = postDb.Id
        return post.Id

def createArticles():

    artService = entityFor(IArticleService)
    slug = 'article-demo'
    #assert isinstance(artService, ArticleServiceAlchemy)
    
    try:
        artService.getBySlug(slug)
        return 
    #    session.query(ArticleMapped.Id).filter(ArticleMapped.Id == '1').one()[0]
    except (NoResultFound, InputError):
        theArticle = ArticleMapped()
        theArticle.CreatedOn = current_timestamp()
        theArticle.Slug = slug
        artService.insert(theArticle)
    
    artCtxService = entityFor(IArticleCtxService)

    assert isinstance(artCtxService, ArticleCtxServiceAlchemy)
    
    for typ in [1,2,3,4]:
        ctx = ArticleCtxMapped()
        ctx.Type = typ
        ctx.Content = DATA['article_content']
        ctx.Article = theArticle.Id
        artCtxService.insert(ctx)

 def insert(self, blog):
     '''
     @see: IBlogService.insert
     '''
     assert isinstance(blog, Blog), 'Invalid blog %s' % blog
     if blog.CreatedOn is None: blog.CreatedOn = current_timestamp()
     return super().insert(blog)

    def insert(self, post):
        '''
        @see: IPostService.insert
        '''
        assert isinstance(post, Post), 'Invalid post %s' % post
        postDb = PostMapped()
        copy(post, postDb, exclude=COPY_EXCLUDE)
        postDb.typeId = self._typeId(post.Type)

        # TODO: implement the proper fix using SQLAlchemy compilation rules
        nohigh = { i: None for i in range(0x10000, 0x110000) }
        if postDb.Meta: postDb.Meta = postDb.Meta.translate(nohigh)
        if postDb.Content: postDb.Content = postDb.Content.translate(nohigh)
        if postDb.ContentPlain: postDb.ContentPlain = postDb.ContentPlain.translate(nohigh)

        if post.CreatedOn is None: postDb.CreatedOn = current_timestamp()
        if not postDb.Author:
            colls = self.session().query(CollaboratorMapped).filter(CollaboratorMapped.User == postDb.Creator).all()
            if not colls:
                coll = CollaboratorMapped()
                coll.User = postDb.Creator
                src = self.session().query(SourceMapped).filter(SourceMapped.Name == PostServiceAlchemy.default_source_name).one()
                coll.Source = src.Id
                self.session().add(coll)
                self.session().flush((coll,))
                colls = (coll,)
            postDb.Author = colls[0].Id

        self.session().add(postDb)
        self.session().flush((postDb,))
        post.Id = postDb.Id
        return post.Id

    def performLogin(self, authentication):
        '''
        @see: IAuthenticationService.performLogin
        '''
        assert isinstance(authentication, Authentication), 'Invalid authentication %s' % authentication

        if authentication.Token is None:
            raise InputError(Ref(_('The login token is required'), ref=Authentication.Token))
        if authentication.HashedToken is None:
            raise InputError(Ref(_('The hashed login token is required'), ref=Authentication.HashedToken))
        if authentication.UserName is None:
            raise InputError(Ref(_('A user name is required for authentication'), ref=Authentication.UserName))

        olderThan = self.session().query(current_timestamp()).scalar()
        olderThan -= self._authenticationTimeOut
        sql = self.session().query(TokenMapped)
        sql = sql.filter(TokenMapped.Token == authentication.Token)
        sql = sql.filter(TokenMapped.requestedOn > olderThan)
        if sql.delete() > 0:
            commitNow()  # We make sure that the delete has been performed

            try: user = self.session().query(UserMapped).filter(UserMapped.Name == authentication.UserName).filter(UserMapped.DeletedOn == None).one()
            except NoResultFound: user = None

            if user is not None:
                assert isinstance(user, UserMapped), 'Invalid user %s' % user

                hashedToken = hmac.new(bytes(user.Name, 'utf8'),
                                       bytes(user.password, 'utf8'), hashlib.sha512).hexdigest()
                hashedToken = hmac.new(bytes(hashedToken, 'utf8'),
                                       bytes(authentication.Token, 'utf8'), hashlib.sha512).hexdigest()

                if authentication.HashedToken == hashedToken:
                    hash = hashlib.sha512()
                    hash.update(urandom(self.authentication_token_size))

                    login = LoginMapped()
                    login.Session = hash.hexdigest()
                    login.User = user.Id
                    login.CreatedOn = login.AccessedOn = current_timestamp()

                    try: self.session().add(login)
                    except SQLAlchemyError as e: handle(e, login)

                    return login

        raise InputError(_('Invalid credentials'))

 def is_updated(self):
     return deferred(
         sa.Column(
             sa.TIMESTAMP, nullable=False, default=datetime.datetime.now,
             server_default=sqlaexp.text('0'),
             onupdate=datetime.datetime.now,
             server_onupdate=sqlafunc.current_timestamp(),
             ))

 def putLive(self, blogId):
     '''
     @see: IBlogService.putLive
     '''
     blog = self.session().query(BlogMapped).get(blogId)
     if not blog: raise InputError(_('Invalid blog or credentials'))
     assert isinstance(blog, Blog), 'Invalid blog %s' % blog
     blog.LiveOn = current_timestamp() if blog.LiveOn is None else None
     self.session().merge(blog)

 def hide(self, blogId):
     '''
     @see: IBlogService.hide
     '''
     blog = self.session().query(BlogMapped).get(blogId)
     if not blog: raise InputError(_('Invalid blog or credentials'))
     assert isinstance(blog, Blog), 'Invalid blog %s' % blog
     blog.DeletedOn = current_timestamp() 
     self.session().merge(blog)   

def updateLastAccessOn(session, groupId):
    sql = session.query(BlogCollaboratorGroupMapped)
    sql = sql.filter(BlogCollaboratorGroupMapped.Id == groupId)

    try: group = sql.one()
    except NoResultFound: raise InputError(Ref(_('No collaborator group'), ref=BlogCollaboratorGroupMapped.Id))
    
    group.LastAccessOn = current_timestamp()
    session.add(group) 
    session.flush((group,))

 def delete(self, id):
     '''
     @see: IUserService.delete
     '''
     userDb = self.session().query(UserMapped).get(id)
     if not userDb or userDb.DeletedOn is not None: return False
     assert isinstance(userDb, UserMapped), 'Invalid user %s' % userDb
     userDb.DeletedOn = current_timestamp()
     self.session().merge(userDb)
     return True

    def delete(self, id):
        '''
        @see: IPostService.delete
        '''
        postDb = self.session().query(PostMapped).get(id)
        if not postDb or postDb.DeletedOn is not None: return False

        postDb.DeletedOn = current_timestamp()
        self.session().flush((postDb,))
        return True