def get_schema_by_name(name:str, deleted:bool=False): query = session.query(Schema).filter(Schema.name == name.strip()) if not deleted: query = query.filter(Schema.deleted == False) return query.first()
def get_field(schema_name, field_name, deleted=False): schema = get_schema_by_name(schema_name) if not schema: raise ValueError('{} is not a Tablename'.format(schema_name)) query = session.query(Field).filter((Field.schema_id == schema.id) & (Field.name == field_name)) if not deleted: query = query.filter(Field.deleted == False) return query.first()
def iter_entities(schema_id, patch=100): page = 1 while True: query = session.query(Entity).filter((Entity.schema_id == schema_id) & (Entity.deleted == False)) result = query.limit(patch).offset(patch*(page-1)).all() if not result: return None yield from result page += 1
def list_schema(page:int=1, size:int=20, deleted:bool=False): try: query = session.query(Schema) if not deleted: query = query.filter(Schema.deleted == deleted) return pagination(page, size, query) except Exception as e: logger.error('not found. Error:{}'.format(e))
def list_schema_bak(page: int, size: int, deleted: bool = False): try: query = session.query(Schema) if not deleted: query = query.filter(Schema.deleted == False) page = page if page > 0 else 1 size = size if 0 < size < 101 else 20 count = query.count() pages = math.ceil(count / size) result = query.limit(size).offset(size * (page - 1)).all() return result, (page, size, count, pages) except Exception as e: logger.error()
def add_field(schema_name, name, meta): schema = get_schema_by_name(schema_name) if not schema: raise ValueError('{} is not a Tablename'.format(schema_name)) # 解析meta,from ..models import FieldMeta meta_data = FieldMeta(meta) field = Field() field.name = name.strip() field.schema_id = schema.id field.meta = meta # 能解析成功说明符合格式要求 # ref_id 引用 if meta_data.reference: ref = get_field(meta_data.reference.schema, meta_data.reference.field) if not ref: raise TypeError('Wrong Reference {}.{}'.format(meta_data.reference.schema, meta_data.reference.field)) field.ref_id = ref.id # 判断字段是否已经使用 if not table_used(schema.id): # 未使用的逻辑表,直接加字段 return _add_field(field) # 已使用的逻辑表 if meta_data.nullable: # 可以为空,直接加字段 return _add_field(field) # 到这里已经有一个隐含条件即不可为空 if meta_data.unique: # 必须唯一 # 当前的条件是 对一个正在使用的逻辑表加字段不可以为空又要唯一,做不到 raise TypeError('This field is required an unique.') # 到这里的隐含条件是,不可以为空,但可以不唯一 if not meta_data.default: # 没有缺省值 raise TypeError('This field requires a default value.') else: # 为逻辑表所有记录增加字段,操作entity表 entities = session.query(Entity).filter((Entity.schema_id == schema.id) & (Entity.deleted == False)).all() for entity in entities: # value表新增记录 value = Value() value.entity_id = entity.id value.field = field value.value = meta_data.default session.add(value) return _add_field(field) # 到这里的隐含条件是,不可以为空,但可以不唯一 if not meta_data.default: # 没有缺省值 raise TypeError('This field requires a default value.') else: # 为逻辑表所有记录增加字段,操作entity表 for entity in iter_entities(schema.id): # value表新增记录 value = Value() value.entity_id = entity.id value.field = field value.value = meta_data.default session.add(value) return _add_field(field)
def delete_schema(id:int): try: schema = session.query(Schema).filter((Schema.id == id) & (Schema.deleted == False)) if schema: schema.deleted = True session.add(schema) try: session.commit() return schema except Exception as e: session.rollback() raise e else: raise ValueError('Wrong ID {}'.format(id)) except Exception as e: logger.error('Fail to del a schema.id = {}; error:{}'.format(id, e))
def iter_entities(schema_id, patch=100): page = 1 while True: query = session.query(Entity).filter((Entity.schema_id == schema_id) & (Entity.deleted == False)) result = query.limit(patch).offset((page - 1) * patch).all() if not result: return None yield from result page += 1 #总结 # 本项目通过mysql数据库设计,实现一个复杂的cmdb # 目前的这个cmdb功能还是比较简陋的,很多的功能还没有实现锁的机制等 # 但是目前为止,基本功能已经可心实现和完成了,其实生产环境中真正的功能也就是现在这样了 # 及时花了很大的功夫,把更加复杂的功能实现了,也未能有用户使用 # 本项目学习目的 # 学习数据库设计 # 进一步巩固SQLAlchemy使用 # 学习Service层的写法 # 巩固日志的使用 # 学习插件化开发思想 # 学习复杂的逻辑设计,开发,锻炼严谨的思维能力,应用到项目开发中
def table_used(schema_id, deleted=False): query = session.query(Entity).filter(Entity.schema_id == schema_id) if not deleted: query = query.filter(Entity.deleted == False) return query.first() is not None
def list_schema(page: int = 1, size: int = 20, deleted: bool = False): query = session.query(Schema) if not deleted: query = query.filter(Schema.deleted == False) return paginate(page, size, query)