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 }
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 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.", )
def do_upgrade(): """Carry out the upgrade.""" op.alter_column(table_name='knwKBRVAL', column_name='id_knwKB', type_=db.MediumInteger(8, unsigned=True), existing_nullable=False, existing_server_default='0')
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
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)
def do_upgrade(): """Implement your upgrades here.""" if not op.has_table("record_json"): op.create_table( 'record_json', sa.Column('id', db.MediumInteger(8, unsigned=True), nullable=False), sa.Column('json', db.JSON, nullable=False), sa.ForeignKeyConstraint(['id'], ['bibrec.id'], ), sa.PrimaryKeyConstraint('id'), mysql_charset='utf8', mysql_engine='MyISAM' ) else: warnings.warn("*** Creation of 'record_json' table 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 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 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 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)
class Record(db.Model): """Represent a record object inside the SQL database.""" __tablename__ = 'bibrec' id = db.Column(db.MediumInteger(8, unsigned=True), primary_key=True, nullable=False, autoincrement=True) creation_date = db.Column(db.DateTime, nullable=False, server_default='1900-01-01 00:00:00', index=True) modification_date = db.Column(db.DateTime, nullable=False, server_default='1900-01-01 00:00:00', index=True) master_format = db.Column(db.String(16), nullable=False, server_default='marc') additional_info = db.Column(db.JSON) # FIXME: remove this from the model and add them to the record class, all? @property def deleted(self): """Return True if record is marked as deleted.""" from .api import get_record dbcollids = [ c.get('primary') for c in get_record(self.id).get('collections') ] # record exists; now check whether it isn't marked as deleted: return ("DELETED" in dbcollids) or \ (current_app.config.get('CFG_CERN_SITE') and "DUMMY" in dbcollids) @staticmethod def _next_merged_recid(recid): """Return the ID of record merged with record with ID = recid.""" from .api import get_record merged_recid = None # FIXME for val in get_record(recid).get("970__d", []): try: merged_recid = int(val) break except ValueError: pass if not merged_recid: return None else: return merged_recid @cached_property def merged_recid(self): """Return record object with which the given record has been merged. :param recID: deleted record recID :return: merged record recID """ return Record._next_merged_recid(self.id) @property def merged_recid_final(self): """Return the last record from hierarchy merged with this one.""" cur_id = self.id next_id = Record._next_merged_recid(cur_id) while next_id: cur_id = next_id next_id = Record._next_merged_recid(cur_id) return cur_id @classmethod def filter_time_interval(cls, datetext, column='c'): """Return filter based on date text and column type.""" column = cls.creation_date if column == 'c' else cls.modification_date parts = datetext.split('->') where = [] if len(parts) == 2: if parts[0] != '': where.append(column >= parts[0]) if parts[1] != '': where.append(column <= parts[1]) else: where.append(column.like(datetext + '%')) return where @classmethod def allids(cls): """Return all existing record ids.""" return intbitset(db.session.query(cls.id).all())
class KnwKB(db.Model): """Represent a KnwKB record.""" KNWKB_TYPES = { 'written_as': 'w', 'dynamic': 'd', 'taxonomy': 't', } __tablename__ = 'knwKB' id = db.Column(db.MediumInteger(8, unsigned=True), nullable=False, primary_key=True, autoincrement=True) _name = db.Column(db.String(255), server_default='', unique=True, name="name") _description = db.Column(db.Text, nullable=False, name="description", default="") _kbtype = db.Column(db.Char(1), nullable=True, default='w', name="kbtype") slug = db.Column(db.String(255), unique=True, nullable=False, default="") # Enable or disable the access from REST API is_api_accessible = db.Column(db.Boolean, default=True, nullable=False) @db.hybrid_property def name(self): """Get name.""" return self._name @name.setter def name(self, value): """Set name and generate the slug.""" self._name = value # generate slug if not self.slug: self.slug = KnwKB.generate_slug(value) @db.hybrid_property def description(self): """Get description.""" return self._description @description.setter def description(self, value): """Set description.""" # TEXT in mysql don't support default value # @see http://bugs.mysql.com/bug.php?id=21532 self._description = value or '' @db.hybrid_property def kbtype(self): """Get kbtype.""" return self._kbtype @kbtype.setter def kbtype(self, value): """Set kbtype.""" if value is None: # set the default value return # or set one of the available values kbtype = value[0] if len(value) > 0 else 'w' if kbtype not in ['t', 'd', 'w']: raise ValueError('unknown type "{value}", please use one of \ following values: "taxonomy", "dynamic" or \ "written_as"'.format(value=value)) self._kbtype = kbtype def is_dynamic(self): """Return true if the type is dynamic.""" return self._kbtype == 'd' def to_dict(self): """Return a dict representation of KnwKB.""" mydict = { 'id': self.id, 'name': self.name, 'description': self.description, 'kbtype': self.kbtype } if self.kbtype == 'd': mydict.update((self.kbdefs.to_dict() if self.kbdefs else {}) or {}) return mydict def get_kbr_items(self, searchkey="", searchvalue="", searchtype='s'): """ Return dicts of 'key' and 'value' from a knowledge base. :param kb_name the name of the knowledge base :param searchkey search using this key :param searchvalue search using this value :param searchtype s=substring, e=exact, sw=startswith :return a list of dictionaries [{'key'=>x, 'value'=>y},..] """ import warnings warnings.warn("The function is deprecated. Please use the " "`KnwKBRVAL.query_kb_mappings()` instead. " "E.g. [kval.to_dict() for kval in " "KnwKBRVAL.query_kb_mappings(kb_id).all()]") if searchtype == 's' and searchkey: searchkey = '%' + searchkey + '%' if searchtype == 's' and searchvalue: searchvalue = '%' + searchvalue + '%' if searchtype == 'sw' and searchvalue: # startswith searchvalue = searchvalue + '%' if not searchvalue: searchvalue = '%' if not searchkey: searchkey = '%' kvals = KnwKBRVAL.query.filter(KnwKBRVAL.id_knwKB.like(self.id), KnwKBRVAL.m_value.like(searchvalue), KnwKBRVAL.m_key.like(searchkey)).all() return [kval.to_dict() for kval in kvals] def get_kbr_values(self, searchkey="", searchvalue="", searchtype='s'): """ Return dicts of 'key' and 'value' from a knowledge base. :param kb_name the name of the knowledge base :param searchkey search using this key :param searchvalue search using this value :param searchtype s=substring, e=exact, sw=startswith :return a list of dictionaries [{'key'=>x, 'value'=>y},..] """ import warnings warnings.warn("The function is deprecated. Please use the " "`KnwKBRVAL.query_kb_mappings()` instead. " "E.g. [(kval.m_value,) for kval in " "KnwKBRVAL.query_kb_mappings(kb_id).all()]") # prepare filters if searchtype == 's': searchkey = '%' + searchkey + '%' if searchtype == 's' and searchvalue: searchvalue = '%' + searchvalue + '%' if searchtype == 'sw' and searchvalue: # startswith searchvalue = searchvalue + '%' if not searchvalue: searchvalue = '%' # execute query return db.session.execute( db.select([KnwKBRVAL.m_value], db.and_(KnwKBRVAL.id_knwKB.like(self.id), KnwKBRVAL.m_value.like(searchvalue), KnwKBRVAL.m_key.like(searchkey)))) @session_manager def set_dyn_config(self, field, expression, collection=None): """Set dynamic configuration.""" if self.kbdefs: # update self.kbdefs.output_tag = field self.kbdefs.search_expression = expression self.kbdefs.collection = collection db.session.merge(self.kbdefs) else: # insert self.kbdefs = KnwKBDDEF(output_tag=field, search_expression=expression, collection=collection) @staticmethod def generate_slug(name): """Generate a slug for the knowledge. :param name: text to slugify :return: slugified text """ slug = slugify(name) i = KnwKB.query.filter( db.or_( KnwKB.slug.like(slug), KnwKB.slug.like(slug + '-%'), )).count() return slug + ('-{0}'.format(i) if i > 0 else '') @staticmethod def exists(kb_name): """Return True if a kb with the given name exists. :param kb_name: the name of the knowledge base :return: True if kb exists """ return KnwKB.query_exists(KnwKB.name.like(kb_name)) @staticmethod def query_exists(filters): """Return True if a kb with the given filters exists. E.g: KnwKB.query_exists(KnwKB.name.like('FAQ')) :param filters: filter for sqlalchemy :return: True if kb exists """ return db.session.query(KnwKB.query.filter(filters).exists()).scalar() def get_filename(self): """Construct the file name for taxonomy knoledge.""" return cfg['CFG_WEBDIR'] + "/kbfiles/" \ + str(self.id) + ".rdf"
class KnwKBRVAL(db.Model): """Represent a KnwKBRVAL record.""" __tablename__ = 'knwKBRVAL' m_key = db.Column(db.String(255), nullable=False, primary_key=True, index=True) m_value = db.Column(db.Text().with_variant(mysql.TEXT(30), 'mysql'), nullable=False) id_knwKB = db.Column(db.MediumInteger(8, unsigned=True), db.ForeignKey(KnwKB.id), nullable=False, server_default='0', primary_key=True) kb = db.relationship( KnwKB, backref=db.backref( 'kbrvals', cascade="all, delete-orphan", collection_class=attribute_mapped_collection("m_key"))) @staticmethod def query_kb_mappings(kbid, sortby="to", key="", value="", match_type="s"): """Return a list of all mappings from the given kb, ordered by key. If key given, give only those with left side (mapFrom) = key. If value given, give only those with right side (mapTo) = value. :param kb_name: knowledge base name. if "", return all :param sortby: the sorting criteria ('from' or 'to') :param key: return only entries where key matches this :param value: return only entries where value matches this :param match_type: s=substring, e=exact, sw=startswith """ # query query = KnwKBRVAL.query.filter(KnwKBRVAL.id_knwKB == kbid) # filter if len(key) > 0: if match_type == "s": key = "%" + key + "%" elif match_type == "sw": key = key + "%" else: key = '%' if len(value) > 0: if match_type == "s": value = "%" + value + "%" elif match_type == "sw": value = value + "%" else: value = '%' query = query.filter(KnwKBRVAL.m_key.like(key), KnwKBRVAL.m_value.like(value)) # order by if sortby == "from": query = query.order_by(KnwKBRVAL.m_key) else: query = query.order_by(KnwKBRVAL.m_value) return query def to_dict(self): """Return a dict representation of KnwKBRVAL.""" # FIXME remove 'id' dependency from invenio modules return { 'id': self.m_key + "_" + str(self.id_knwKB), 'key': self.m_key, 'value': self.m_value, 'kbid': self.kb.id if self.kb else None, 'kbname': self.kb.name if self.kb else None }
class CmtRECORDCOMMENT(db.Model): """Represents a CmtRECORDCOMMENT record.""" __tablename__ = 'cmtRECORDCOMMENT' id = db.Column(db.Integer(15, unsigned=True), nullable=False, primary_key=True, autoincrement=True) id_bibrec = db.Column(db.MediumInteger(8, unsigned=True), db.ForeignKey(Bibrec.id), nullable=False, server_default='0') id_user = db.Column(db.Integer(15, unsigned=True), db.ForeignKey(User.id), nullable=False, server_default='0') title = db.Column(db.String(255), nullable=False, server_default='') body = db.Column(db.Text, nullable=False) date_creation = db.Column(db.DateTime, nullable=False, server_default='1900-01-01 00:00:00') star_score = db.Column(db.TinyInteger(5, unsigned=True), nullable=False, server_default='0') nb_votes_yes = db.Column(db.Integer(10), nullable=False, server_default='0') nb_votes_total = db.Column(db.Integer(10, unsigned=True), nullable=False, server_default='0') nb_abuse_reports = db.Column(db.Integer(10), nullable=False, server_default='0') status = db.Column(db.Char(2), nullable=False, index=True, server_default='ok') round_name = db.Column(db.String(255), nullable=False, server_default='') restriction = db.Column(db.String(50), nullable=False, server_default='') in_reply_to_id_cmtRECORDCOMMENT = db.Column(db.Integer(15, unsigned=True), db.ForeignKey(id), nullable=False, server_default='0') reply_order_cached_data = db.Column(db.Binary, nullable=True) bibrec = db.relationship(Bibrec, backref='recordcomments') user = db.relationship(User, backref='recordcomments') replies = db.relationship('CmtRECORDCOMMENT', backref=db.backref('parent', remote_side=[id], order_by=date_creation)) @property def is_deleted(self): """Check if is deleted.""" return self.status != 'ok' def is_collapsed(self, id_user): """Return true if the comment is collapsed by user.""" return CmtCOLLAPSED.query.filter( db.and_(CmtCOLLAPSED.id_bibrec == self.id_bibrec, CmtCOLLAPSED.id_cmtRECORDCOMMENT == self.id, CmtCOLLAPSED.id_user == id_user)).count() > 0 @session_manager def collapse(self, id_user): """Collapse comment beloging to user.""" c = CmtCOLLAPSED(id_bibrec=self.id_bibrec, id_cmtRECORDCOMMENT=self.id, id_user=id_user) db.session.add(c) db.session.commit() def expand(self, id_user): """Expand comment beloging to user.""" CmtCOLLAPSED.query.filter( db.and_(CmtCOLLAPSED.id_bibrec == self.id_bibrec, CmtCOLLAPSED.id_cmtRECORDCOMMENT == self.id, CmtCOLLAPSED.id_user == id_user)).delete( synchronize_session=False) __table_args__ = (db.Index('cmtRECORDCOMMENT_reply_order_cached_data', reply_order_cached_data, mysql_length=40), db.Model.__table_args__) @classmethod def count(cls, *criteria, **filters): """Count how many comments.""" return cls.query.filter(*criteria).filter_by(**filters).count()