class FieldTag(db.Model): """Represent a FieldTag record.""" __tablename__ = 'field_tag' id_field = db.Column(db.MediumInteger(9, unsigned=True), db.ForeignKey('field.id'), nullable=False, primary_key=True) id_tag = db.Column(db.MediumInteger(9, unsigned=True), db.ForeignKey('tag.id'), nullable=False, primary_key=True) score = db.Column(db.TinyInteger(4, unsigned=True), nullable=False, server_default='0') tag = db.relationship(Tag, backref='fields', order_by=score) field = db.relationship(Field, backref='tags', order_by=score) def __init__(self, score=None, tup=None, *args, **kwargs): """Init.""" if score is not None: self.score = score if tup is not None: self.tag = Tag(tup) super(FieldTag, self).__init__(*args, **kwargs) @property def as_tag(self): """Return Tag record directly.""" return self.tag
class CheckerRecord(db.Model): """Connect checks with their executions on records.""" __tablename__ = 'checker_record' rec_id = db.Column( db.MediumInteger(8, unsigned=True), db.ForeignKey(RecordMetadata.id), primary_key=True, nullable=False, autoincrement=True, ) record = db.relationship( RecordMetadata, backref=backref("checker_record", cascade="all, delete-orphan"), doc="The record associated with a task.", ) rule_name = db.Column(db.String(127), db.ForeignKey('checker_rule.name'), nullable=False, index=True, primary_key=True, doc="Name of the task in this associaton.") last_run_version_id = db.Column( db.Integer, nullable=False, doc="Last checked version ID of associated record.", )
class UserQuery(db.Model): """Represent a UserQuery record.""" __tablename__ = 'user_query' id_user = db.Column(db.Integer(15, unsigned=True), db.ForeignKey(User.id), primary_key=True, server_default='0') id_query = db.Column(db.Integer(15, unsigned=True), db.ForeignKey(WebQuery.id), primary_key=True, index=True, server_default='0') hostname = db.Column(db.String(50), nullable=True, server_default='unknown host') date = db.Column(db.DateTime, nullable=True, default=datetime.datetime.now) webquery = db.relationship(WebQuery, backref='executions') @classmethod def log(cls, urlargs=None, id_user=None): """Log.""" id_user = id_user if not None else current_user.get_id() urlargs = urlargs or request.query_string if id_user < 0: return webquery = WebQuery.query.filter_by(urlargs=urlargs).first() if webquery is None: webquery = WebQuery(urlargs=urlargs) db.session.add( cls(id_user=id_user, hostname=request.host, webquery=webquery)) db.session.commit()
class KnwKBDDEF(db.Model): """Represent a KnwKBDDEF record.""" __tablename__ = 'knwKBDDEF' id_knwKB = db.Column(db.MediumInteger(8, unsigned=True), db.ForeignKey(KnwKB.id), nullable=False, primary_key=True) id_collection = db.Column(db.MediumInteger(unsigned=True), db.ForeignKey(Collection.id), nullable=True) output_tag = db.Column(db.Text, nullable=True) search_expression = db.Column(db.Text, nullable=True) kb = db.relationship(KnwKB, backref=db.backref('kbdefs', uselist=False, cascade="all, delete-orphan"), single_parent=True) collection = db.relationship(Collection, backref=db.backref('kbdefs')) def to_dict(self): """Return a dict representation of KnwKBDDEF.""" return { 'field': self.output_tag, 'expression': self.search_expression, 'coll_id': self.id_collection, 'collection': self.collection.name if self.collection else None }
class Session(db.Model): """Represent Session record.""" __tablename__ = 'session' session_key = db.Column(db.String(32), nullable=False, server_default='', primary_key=True) session_expiry = db.Column(db.DateTime, nullable=True, index=True) session_object = db.Column(db.LargeBinary, nullable=True) uid = db.Column(db.Integer(15, unsigned=True), nullable=False, index=True) def get_session(self, name, expired=False): """Return an instance of :class:`Session`.""" where = Session.session_key == name if expired: where = db.and_( where, Session.session_expiry >= db.func.current_timestamp()) return self.query.filter(where).one() def set_session(self, name, value, timeout=None): """Store value in database.""" uid = current_user.get_id() session_expiry = datetime.utcnow() + timeout return Session(session_key=name, session_object=value, session_expiry=session_expiry, uid=uid)
class PidLog(db.Model): """Audit log of actions happening to persistent identifiers. This model is primarily used through PersistentIdentifier.log and rarely created manually. """ __tablename__ = 'pidLOG' __table_args__ = ( db.Index('idx_action', 'action'), ) id = db.Column(db.Integer(15, unsigned=True), primary_key=True) """Id of persistent identifier entry.""" id_pid = db.Column( db.Integer(15, unsigned=True), db.ForeignKey(PersistentIdentifier.id), nullable=True, ) """PID.""" timestamp = db.Column(db.DateTime(), nullable=False, default=datetime.now) """Creation datetime of entry.""" action = db.Column(db.String(10), nullable=False) """Action identifier.""" message = db.Column(db.Text(), nullable=False) """Log message.""" # Relationship pid = db.relationship("PersistentIdentifier", backref="logs")
class WebQuery(db.Model): """Represent a WebQuery record.""" __tablename__ = 'query' id = db.Column(db.Integer(15, unsigned=True), primary_key=True, autoincrement=True) type = db.Column(db.Char(1), nullable=False, server_default='r') urlargs = db.Column(db.Text().with_variant(db.Text(100), 'mysql'), nullable=False)
class Fieldname(db.Model): """Represent a Fieldname record.""" __tablename__ = 'fieldname' id_field = db.Column(db.MediumInteger(9, unsigned=True), db.ForeignKey(Field.id), primary_key=True) ln = db.Column(db.Char(5), primary_key=True, server_default='') type = db.Column(db.Char(3), primary_key=True, server_default='sn') value = db.Column(db.String(255), nullable=False) field = db.relationship(Field, backref='names')
class Fieldvalue(db.Model): """Represent a Fieldvalue record.""" def __init__(self): """Init.""" pass __tablename__ = 'fieldvalue' id = db.Column(db.MediumInteger(9, unsigned=True), primary_key=True, autoincrement=True) name = db.Column(db.String(255), nullable=False) value = db.Column(db.Text, nullable=False)
class WtgTAGRecord(db.Model, Serializable): """Connection between Tag and Record.""" __tablename__ = 'wtgTAG_bibrec' __public__ = set(['id_tag', 'id_bibrec', 'date_added']) # tagTAG.id id_tag = db.Column(db.Integer(15, unsigned=True), db.ForeignKey(WtgTAG.id), nullable=False, primary_key=True) # Record.id id_bibrec = db.Column(db.MediumInteger(8, unsigned=True), db.ForeignKey(Record.id), nullable=False, primary_key=True) # Annotation annotation = db.Column( db.Text(convert_unicode=True), default='') # Creation date date_added = db.Column(db.DateTime, default=datetime.now) # Relationships tag = db.relationship(WtgTAG, backref=db.backref('records_association', cascade='all')) tag_query = db.relationship(WtgTAG, backref=db.backref('records_association_query', cascade='all', lazy='dynamic')) bibrec = db.relationship(Record, backref=db.backref('tags_association', cascade='all')) bibrec_query = db.relationship(Record, backref=db.backref('tags_association_query', cascade='all', lazy='dynamic')) def __init__(self, bibrec=None, **kwargs): """TODO.""" super(WtgTAGRecord, self).__init__(**kwargs) if bibrec is not None: self.bibrec = bibrec
def do_upgrade(): """Upgrade recipe. Adds two new columns (password_salt and password_scheme) and migrates emails to password salt. """ op.add_column( 'user', db.Column('password_salt', db.String(length=255), nullable=True)) op.add_column( 'user', db.Column('password_scheme', db.String(length=50), nullable=False)) # Temporary column needed for data migration op.add_column('user', db.Column('new_password', db.String(length=255))) # Migrate emails to password_salt m = db.MetaData(bind=db.engine) m.reflect() u = m.tables['user'] conn = db.engine.connect() conn.execute( u.update().values(password_salt=u.c.email, password_scheme='invenio_aes_encrypted_email')) # Migrate password blob to password varchar. for row in conn.execute(select([u])): # NOTE: Empty string passwords were stored as empty strings # instead of a hashed version, hence they must be treated differently. legacy_pw = row[u.c.password] or mysql_aes_encrypt(row[u.c.email], "") stmt = u.update().where(u.c.id == row[u.c.id]).values( new_password=hashlib.sha256(legacy_pw).hexdigest()) conn.execute(stmt) # Create index op.create_index(op.f('ix_user_password_scheme'), 'user', ['password_scheme'], unique=False) # Drop old database column and rename new. op.drop_column('user', 'password') op.alter_column( 'user', 'new_password', new_column_name='password', existing_type=mysql.VARCHAR(255), existing_nullable=True, )
class AccARGUMENT(db.Model): """Represent an authorization argument.""" __tablename__ = 'accARGUMENT' id = db.Column(db.Integer(15), primary_key=True, autoincrement=True) keyword = db.Column(db.String(32), nullable=True) value = db.Column(db.String(255), nullable=True) __table_args__ = (db.Index('KEYVAL', keyword, value), db.Model.__table_args__) def __repr__(self): """Repr.""" return "{0.keyword}={0.value}".format(self)
class CmtCOLLAPSED(db.Model): """Represents a CmtCOLLAPSED record.""" __tablename__ = 'cmtCOLLAPSED' id_bibrec = db.Column(db.MediumInteger(8, unsigned=True), db.ForeignKey(Bibrec.id), primary_key=True) id_cmtRECORDCOMMENT = db.Column(db.Integer(15, unsigned=True), db.ForeignKey(CmtRECORDCOMMENT.id), primary_key=True) id_user = db.Column(db.Integer(15, unsigned=True), db.ForeignKey(User.id), primary_key=True)
class AccROLE(db.Model): """Represent an access role.""" __tablename__ = 'accROLE' id = db.Column(db.Integer(15, unsigned=True), primary_key=True, autoincrement=True) name = db.Column(db.String(32), unique=True, nullable=True) description = db.Column(db.String(255), nullable=True) firerole_def_ser = db.Column(db.iBinary, nullable=True) firerole_def_src = db.Column(db.Text, nullable=True) def __repr__(self): """Repr.""" return "{0.name} - {0.description}".format(self)
class UserAccROLE(db.Model): """Represent an user role relationship.""" __tablename__ = 'user_accROLE' id_user = db.Column(db.Integer(15, unsigned=True), db.ForeignKey(User.id), nullable=False, primary_key=True) id_accROLE = db.Column(db.Integer(15, unsigned=True), db.ForeignKey(AccROLE.id), nullable=False, primary_key=True) expiration = db.Column(db.DateTime, nullable=False, server_default='9999-12-31 23:59:59') user = db.relationship(User, backref='roles') role = db.relationship(AccROLE, backref='users')
def do_upgrade(): """Implement your upgrades here.""" # add column "id" in the table op.add_column( 'collection_field_fieldvalue', db.Column('id', db.MediumInteger(9, unsigned=True), nullable=False)) # set all new ids records = run_sql("""SELECT id_collection, id_field, id_fieldvalue, type, score, score_fieldvalue FROM collection_field_fieldvalue AS cff ORDER BY cff.id_collection, id_field, id_fieldvalue, type, score, score_fieldvalue""") for index, rec in enumerate(records): sql = """UPDATE collection_field_fieldvalue SET id = %%s WHERE id_collection = %%s AND id_field = %%s AND type = %%s AND score = %%s AND score_fieldvalue = %%s AND id_fieldvalue %s """ % ('=%s' % (rec[2], ) if rec[2] is not None else 'is NULL', ) run_sql(sql, (index + 1, rec[0], rec[1], rec[3], rec[4], rec[5])) # create new primary key with id op.create_primary_key('pk_collection_field_fieldvalue_id', 'collection_field_fieldvalue', ['id']) # set id as autoincrement op.alter_column('collection_field_fieldvalue', 'id', existing_type=db.MediumInteger(9, unsigned=True), existing_nullable=False, autoincrement=True)
class AccACTION(db.Model): """Represent an access action.""" __tablename__ = 'accACTION' id = db.Column(db.Integer(15, unsigned=True), primary_key=True, autoincrement=True) name = db.Column(db.String(32), unique=True, nullable=True) description = db.Column(db.String(255), nullable=True) allowedkeywords = db.Column(db.String(255), nullable=True) optional = db.Column(db.Enum('yes', 'no', name='yes_no'), nullable=False, server_default='no') def __repr__(self): """Repr.""" return "{0.name}".format(self)
def do_upgrade(): """Add the table with facets configuration.""" if not op.has_table('facet_collection'): op.create_table( 'facet_collection', db.Column('id', mysql.INTEGER(), nullable=False), db.Column('id_collection', mysql.INTEGER(), nullable=False), db.Column('order', mysql.INTEGER(), nullable=False), db.Column('facet_name', db.String(length=80), nullable=False), db.ForeignKeyConstraint(['id_collection'], ['collection.id'], ), db.PrimaryKeyConstraint('id'), mysql_charset='utf8', mysql_engine='MyISAM' ) else: warnings.warn("*** Creation of table 'facet_collection' skipped!")
class RecordMetadata(db.Model): """Represent a json record inside the SQL database.""" __tablename__ = 'record_json' id = db.Column(db.MediumInteger(8, unsigned=True), db.ForeignKey(Record.id), primary_key=True, nullable=False, autoincrement=True) version_id = db.Column(db.Integer, nullable=False) json = db.Column(db.JSON, nullable=False) record = db.relationship(Record, backref='record_json') __mapper_args__ = {"version_id_col": version_id}
class UserEXT(db.Model): """Represent a UserEXT record.""" __tablename__ = 'userEXT' id = db.Column(db.String(255), primary_key=True, nullable=False) method = db.Column(db.String(50), primary_key=True, nullable=False) id_user = db.Column(db.Integer(15, unsigned=True), db.ForeignKey(User.id), nullable=False) user = db.relationship(User, backref="external_identifiers") __table_args__ = (db.Index('userext_id_user_method', id_user, method, unique=True), db.Model.__table_args__)
class CmtSUBSCRIPTION(db.Model): """Represents a CmtSUBSCRIPTION record.""" __tablename__ = 'cmtSUBSCRIPTION' id_bibrec = db.Column(db.MediumInteger(8, unsigned=True), db.ForeignKey(Bibrec.id), nullable=False, primary_key=True) id_user = db.Column(db.Integer(15, unsigned=True), db.ForeignKey(User.id), nullable=False, primary_key=True) creation_time = db.Column(db.DateTime, nullable=False, server_default='1900-01-01 00:00:00') bibrec = db.relationship(Bibrec) user = db.relationship(User, backref='comment_subscriptions')
class Goto(db.Model): """Represents a Goto record.""" __tablename__ = 'goto' label = db.Column(db.String(150), primary_key=True) plugin = db.Column(db.String(150), nullable=False) _parameters = db.Column(db.JSON, nullable=False, default={}, name="parameters") creation_date = db.Column(db.DateTime, default=datetime.datetime.now, nullable=False, index=True) modification_date = db.Column(db.DateTime, default=datetime.datetime.now, onupdate=datetime.datetime.now, nullable=False, index=True) @validates('plugin') def validate_plugin(self, key, plugin): """Validate plugin name.""" if plugin not in redirect_methods: raise ValueError("%s plugin does not exist" % plugin) return plugin @db.hybrid_property def parameters(self): """Get parameters method.""" return self._parameters @parameters.setter def parameters(self, value): """Set parameters method.""" self._parameters = value or {} def to_dict(self): """Return a dict representation of Goto.""" return {'label': self.label, 'plugin': self.plugin, 'parameters': self.parameters, 'creation_date': self.creation_date, 'modification_date': self.modification_date}
class Bibfmt(db.Model): """Represent a Bibfmt record.""" __tablename__ = 'bibfmt' id_bibrec = db.Column( db.MediumInteger(8, unsigned=True), nullable=False, server_default='0', primary_key=True, autoincrement=False) format = db.Column( db.String(10), nullable=False, server_default='', primary_key=True, index=True) kind = db.Column( db.String(10), nullable=False, server_default='', index=True ) last_updated = db.Column( db.DateTime, nullable=False, server_default='1900-01-01 00:00:00', index=True) value = db.Column(db.iLargeBinary) needs_2nd_pass = db.Column(db.TinyInteger(1), server_default='0')
class AccAuthorization(db.Model): """Represent an authorization.""" __tablename__ = 'accROLE_accACTION_accARGUMENT' id = db.Column(db.Integer(15, unsigned=True), primary_key=True, autoincrement=True) id_accROLE = db.Column(db.Integer(15, unsigned=True), db.ForeignKey(AccROLE.id), nullable=True, index=True) id_accACTION = db.Column(db.Integer(15, unsigned=True), db.ForeignKey(AccACTION.id), nullable=True, index=True) _id_accARGUMENT = db.Column(db.Integer(15), nullable=True, name="id_accARGUMENT", index=True) argumentlistid = db.Column(db.MediumInteger(8), nullable=True) role = db.relationship(AccROLE, backref='authorizations') action = db.relationship(AccACTION, backref='authorizations') argument = db.relationship( AccARGUMENT, backref='authorizations', primaryjoin=db.and_( AccARGUMENT.id == _id_accARGUMENT, _id_accARGUMENT != -1, _id_accARGUMENT is not None ), foreign_keys=_id_accARGUMENT, uselist=False, cascade="all, delete", ) @db.hybrid_property def id_accARGUMENT(self): """get id_accARGUMENT.""" return self._id_accARGUMENT @id_accARGUMENT.setter def id_accARGUMENT(self, value): """set id_accARGUMENT.""" self._id_accARGUMENT = value or None
class Field(db.Model): """Represent a Field record.""" def __repr__(self): """Get repr.""" return "%s(%s)" % (self.__class__.__name__, self.id) __tablename__ = 'field' id = db.Column(db.MediumInteger(9, unsigned=True), primary_key=True) name = db.Column(db.String(255), nullable=False) code = db.Column(db.String(255), unique=True, nullable=False) @property def name_ln(self): """Get name ln.""" from .cache import get_field_i18nname return get_field_i18nname(self.name, getattr(g, 'ln', cfg['CFG_SITE_LANG'])) # try: # return db.object_session(self).query(Fieldname).\ # with_parent(self).filter(db.and_(Fieldname.ln==g.ln, # Fieldname.type=='ln')).first().value # except Exception: # return self.name @classmethod def get_field_name(cls, code): """Return field name for given code.""" return cls.query.filter_by(code=code).value(cls.name) @classmethod def get_field_tags(cls, code, tagtype='marc'): """Yield tag values for given field code.""" column = Tag.value if tagtype == 'marc' else Tag.recjson_value tags = cls.query.join(cls.tags).join( FieldTag.tag).filter(cls.code == code).values(column) for tag in tags: for value in tag[0].split(','): yield value.strip()
def do_upgrade(): """Implement your upgrades here.""" try: op.add_column('inspire_prod_records', db.Column('valid', db.Boolean, default=None, nullable=True, index=True) ) op.add_column('inspire_prod_records', db.Column('errors', db.Text(), nullable=True) ) except OperationalError as err: # Columns exist warnings.warn( "*** Error adding columns 'inspire_prod_records.valid' " "and 'inspire_prod_records.errors': {0} ***".format( str(err) ) )
class CheckerReporter(db.Model): """Represent reporters associated with a task. ..note:: These entries are currently not meant to be associated with multiple tasks, because it is assumed that they may be deleted without affecting more than one tasks. """ __tablename__ = 'checker_reporter' plugin = db.Column(db.String(127), primary_key=True, doc="Check associated with this reporter." "") rule_name = db.Column(db.String(127), db.ForeignKey('checker_rule.name', onupdate="CASCADE", ondelete="CASCADE"), index=True, nullable=False, primary_key=True, doc="Task associated with this reporter." "") arguments = db.Column( JsonEncodedDict(1023), default={}, doc="Arguments to be passed to this reporter.", ) @db.hybrid_property def module(self): """Python module of the associated check.""" return reporters_files[self.plugin]
class Tag(db.Model): """Represent a Tag record.""" __tablename__ = 'tag' id = db.Column(db.MediumInteger(9, unsigned=True), primary_key=True) name = db.Column(db.String(255), nullable=False) value = db.Column(db.Char(6), nullable=False, server_default='') recjson_value = db.Column(db.Text, nullable=False) def __init__(self, tup=None, *args, **kwargs): """Init.""" if tup is not None and isinstance(tup, tuple): self.name, self.value = tup super(Tag, self).__init__(*args, **kwargs) else: if tup is None: super(Tag, self).__init__(*args, **kwargs) else: super(Tag, self).__init__(tup, *args, **kwargs) @property def as_tag(self): """Return tupple with name and value.""" return self.name, self.value
def do_upgrade(): """ Implement your upgrades here """ if not op.has_table('remoteACCOUNT'): op.create_table('remoteACCOUNT', db.Column('id', db.Integer(display_width=15), nullable=False), db.Column('user_id', db.Integer(display_width=15), nullable=False), db.Column('client_id', db.String(length=255), nullable=False), db.Column('extra_data', db.JSON, nullable=True), db.ForeignKeyConstraint( ['user_id'], ['user.id'], ), db.PrimaryKeyConstraint('id'), db.UniqueConstraint('user_id', 'client_id'), mysql_charset='utf8', mysql_engine='MyISAM') else: warnings.warn("*** Creation of table 'remoteACCOUNT table skipped!'") if not op.has_table('remoteTOKEN'): op.create_table('remoteTOKEN', db.Column('id_remote_account', db.Integer(display_width=15), nullable=False), db.Column('token_type', db.String(length=40), nullable=False), db.Column('access_token', db.Text(), nullable=False), db.Column('secret', db.Text(), nullable=False), db.ForeignKeyConstraint( ['id_remote_account'], ['remoteACCOUNT.id'], ), db.PrimaryKeyConstraint('id_remote_account', 'token_type'), mysql_charset='utf8', mysql_engine='MyISAM') else: warnings.warn("*** Creation of table 'remoteTOKEN' skipped!'")
class CmtACTIONHISTORY(db.Model): """Represents a CmtACTIONHISTORY record.""" __tablename__ = 'cmtACTIONHISTORY' id_cmtRECORDCOMMENT = db.Column(db.Integer(15, unsigned=True), db.ForeignKey(CmtRECORDCOMMENT.id), nullable=True, primary_key=True) id_bibrec = db.Column(db.MediumInteger(8, unsigned=True), db.ForeignKey(Bibrec.id), nullable=True, primary_key=True) id_user = db.Column(db.Integer(15, unsigned=True), db.ForeignKey(User.id), nullable=True, primary_key=True) client_host = db.Column(db.Integer(10, unsigned=True), nullable=True) action_time = db.Column(db.DateTime, nullable=False, server_default='1900-01-01 00:00:00') action_code = db.Column(db.Char(1), nullable=False, index=True) recordcomment = db.relationship(CmtRECORDCOMMENT, backref='actionhistory') bibrec = db.relationship(Bibrec) user = db.relationship(User)