class SiteModel(db.Model): __tablename__ = 'sites' id = db.Column(db.Integer, primary_key=True) name = db.Column(db.Text, nullable=False) enable_flag = db.Column(db.Boolean, nullable=False, default=True) created_at = db.Column(db.DateTime, nullable=False, default=datetime.now) updated_at = db.Column( db.DateTime, nullable=False, default=datetime.now, onupdate=datetime.now) url_settings = relationship('SiteUrlSettingModel', back_populates='site') def __init__(self, enable_flag: bool = True): self.name = '' self.enable_flag = enable_flag @reconstructor def init_on_load(self): # 有効無効のラベル if self.enable_flag: self.enable_label = '有効' else: self.enable_label = '無効' # ボットが設定されているURL(デフォルト含む)があるか self.have_bot = False for url_setting in self.url_settings: if url_setting.bot_id: self.have_bot = True break
class FaqListModel(db.Model): __tablename__ = 'faq_lists' id = db.Column(db.Integer, primary_key=True) name = db.Column(db.Text, nullable=False) bot_id = db.Column(db.Integer, db.ForeignKey('bots.id'), nullable=False) start_faq_id = db.Column(db.Integer, db.ForeignKey('faqs.id'), nullable=True) not_found_faq_id = db.Column(db.Integer, db.ForeignKey('faqs.id'), nullable=True) created_at = db.Column(db.DateTime, nullable=False, default=datetime.now) updated_at = db.Column(db.DateTime, nullable=False, default=datetime.now, onupdate=datetime.now) faqs = relationship('FaqModel', primaryjoin=id == FaqModel.faq_list_id) bot = relationship('BotModel', back_populates='faq_lists', foreign_keys=[bot_id]) start_faq = relationship('FaqModel', primaryjoin=start_faq_id == FaqModel.id) not_found_faq = relationship('FaqModel', primaryjoin=not_found_faq_id == FaqModel.id) def __init__(self, bot_id: int, name: str = ''): self.name = name self.bot_id = bot_id
class SiteUrlSettingModel(db.Model): __tablename__ = 'site_url_settings' id = db.Column(db.Integer, primary_key=True) url_pattern = db.Column(db.Text, nullable=False) site_id = db.Column(db.Integer, db.ForeignKey('sites.id'), nullable=False) bot_id = db.Column(db.Integer, db.ForeignKey('bots.id'), nullable=True) enable_flag = db.Column(db.Boolean, nullable=False, default=True) created_at = db.Column(db.DateTime, nullable=False, default=datetime.now) updated_at = db.Column( db.DateTime, nullable=False, default=datetime.now, onupdate=datetime.now) site = relationship('SiteModel', back_populates='url_settings') bot = relationship('BotModel') def __init__(self, site_id: int, enable_flag: bool = True): self.site_id = site_id self.url_pattern = '' self.enable_flag = enable_flag @reconstructor def init_on_load(self): # 有効無効のラベル if self.enable_flag: self.enable_label = '有効' else: self.enable_label = '無効' # デフォルトの書き換え if self.url_pattern == URL_PATTERN_DEFALT_ID: self.url_pattern_editable = False else: self.url_pattern_editable = True
class TalkLogModel(db.Model): __tablename__ = 'talk_logs' id = db.Column(db.Integer, primary_key=True) site_id = db.Column(db.Integer) bot_id = db.Column(db.Integer) session_id = db.Column(db.String(255)) talk_type = db.Column(db.String(255)) request_faq_id: db.Column(db.Integer) request_static_answer_name = db.Column(db.Text) request_query = db.Column(db.Text) user_agent = db.Column(db.Text) response_faq_id = db.Column(db.Integer) response_faqs = db.Column(db.Text) score = db.Column(db.Text) error_message = db.Column(db.Text) created_at = db.Column(db.DateTime, nullable=False, default=datetime.now) def __init__(self, request: request, response: TalkResponse = None, top_faq_info_list: list = None, bot_id: int = None, session_id: str = None): self.site_id = int(request.json['site_id']) self.bot_id = bot_id self.session_id = session_id self.talk_type = request.json['type'] self.request_faq_id = request.json[ 'faq_id'] if 'faq_id' in request.json else None self.request_static_answer_name = request.json[ 'name'] if 'name' in request.json else None self.request_query = request.json[ 'query'] if 'query' in request.json else None self.user_agent = request.headers.get('User-Agent') self.response_faq_id = response.answer.id if response.answer else None if response.faqs: faq_questions = [faq.question for faq in response.faqs] self.response_faqs = ','.join(faq_questions) if top_faq_info_list: dump_list = [] for faq_info in top_faq_info_list: dump_list.append({ 'faq_id': faq_info['faq_id'], 'question': faq_info['question'], 'score': faq_info['score'] }) self.score = json.dumps(dump_list, ensure_ascii=False) self.error_message = response.error_message
class BotModel(db.Model): __tablename__ = 'bots' id = db.Column(db.Integer, primary_key=True) name = db.Column(db.Text, nullable=False) fitted_model_path = db.Column(db.Text, nullable=False) created_at = db.Column(db.DateTime, nullable=False, default=datetime.now) updated_at = db.Column(db.DateTime, nullable=False, default=datetime.now, onupdate=datetime.now) fitted_faq_list_id = db.Column(db.Integer, db.ForeignKey('faq_lists.id'), nullable=True) fitted_state = db.Column(db.Integer, nullable=False, default=FITTED_STATE_NO_FIT) last_fitted_at = db.Column(db.DateTime, nullable=True) enable_flag = db.Column(db.Boolean, nullable=False, default=True) fitted_faq_list = relationship('FaqListModel', foreign_keys=fitted_faq_list_id) faq_lists = relationship( 'FaqListModel', back_populates='bot', lazy='joined', primaryjoin='and_(BotModel.id==FaqListModel.bot_id)') def __init__(self, name='', fitted_model_path='', fitted_faq_list_id=None, fitted_state=FITTED_STATE_NO_FIT, enable_flag=True): self.name = name self.fitted_model_path = fitted_model_path self.fitted_faq_list_id = fitted_faq_list_id self.fitted_state = fitted_state self.fitted_state_label = '未学習' self.enable_flag = enable_flag if self.fitted_state == FITTED_STATE_FITTING: self.fitted_state_label = '学習中' elif self.fitted_state == FITTED_STATE_FITTED: self.fitted_state_label = '学習済' @reconstructor def init_on_load(self): # 有効無効のラベル if self.enable_flag: self.enable_label = '有効' else: self.enable_label = '無効' # 学習状態のラベル if self.fitted_state == FITTED_STATE_FITTING: self.fitted_state_label = '学習中' elif self.fitted_state == FITTED_STATE_FITTED: self.fitted_state_label = '学習済' else: self.fitted_state_label = '未学習' # 学習ボタンの活性条件 self.fit_button_enable = True if self.fitted_faq_list_id is None or self.fitted_state == FITTED_STATE_FITTING: self.fit_button_enable = False def get_static_answer(self, name: str) -> FaqModel: if name == STATIC_ANSWER_ID_START: return self.fitted_faq_list.start_faq if name == STATIC_ANSWER_ID_NOT_FOUND: return self.fitted_faq_list.not_found_faq raise Exception('static_answer not found. (name:{})'.format(name))
class FaqModel(db.Model): __tablename__ = 'faqs' id = db.Column(db.Integer, primary_key=True) question = db.Column(db.Text, nullable=False) answer = db.Column(db.Text, nullable=False) question_org = db.Column(db.Text, nullable=False) answer_org = db.Column(db.Text, nullable=False) faq_list_id = db.Column(db.Integer, db.ForeignKey('faq_lists.id'), nullable=False) enable_flag = db.Column(db.Boolean, nullable=False, default=True) fit_flag = db.Column(db.Boolean, nullable=False, default=True) created_at = db.Column(db.DateTime, nullable=False, default=datetime.now) updated_at = db.Column(db.DateTime, nullable=False, default=datetime.now, onupdate=datetime.now) faq_list = relationship('FaqListModel', back_populates='faqs', foreign_keys=[faq_list_id]) related_faqs = relationship( 'FaqModel', secondary=faqs_faqs_table, primaryjoin=id == faqs_faqs_table.c.faq_id, secondaryjoin= 'and_(faqs_faqs.c.faq_list_id==FaqModel.faq_list_id, faqs_faqs.c.question==FaqModel.question)' ) def __init__(self, question, answer, question_org, answer_org, faq_list_id, enable_flag=True, fit_flag=True): self.question = question self.answer = answer self.question_org = question_org self.answer_org = answer_org self.faq_list_id = faq_list_id self.enable_flag = enable_flag self.fit_flag = fit_flag @reconstructor def init_on_load(self): # 有効無効のラベル if self.enable_flag: self.enable_label = '有効' else: self.enable_label = '無効' # 学習対象のラベル if self.fit_flag: self.fit_label = '対象' else: self.fit_label = '非対象' def get_enable_related_faqs(self): return [faq for faq in self.related_faqs if faq.enable_flag]
from datetime import datetime from chatbot.database import db from sqlalchemy.orm import relationship, reconstructor # class 定義せずに many to many 用 tableを定義 # metadata は不要でした # https://flask-sqlalchemy.palletsprojects.com/en/2.x/models/ # 検索するとTable class のコンストラクタ第2引数に metadata を渡しているものが多数出てきたので # 記述していたがうまく行かなかった faqs_faqs_table = db.Table( 'faqs_faqs', db.Column('faq_id', db.Integer, db.ForeignKey('faqs.id')), db.Column('faq_list_id', db.Integer, db.ForeignKey('faq_lists.id')), db.Column('question', db.Text)) class FaqModel(db.Model): __tablename__ = 'faqs' id = db.Column(db.Integer, primary_key=True) question = db.Column(db.Text, nullable=False) answer = db.Column(db.Text, nullable=False) question_org = db.Column(db.Text, nullable=False) answer_org = db.Column(db.Text, nullable=False) faq_list_id = db.Column(db.Integer, db.ForeignKey('faq_lists.id'), nullable=False) enable_flag = db.Column(db.Boolean, nullable=False, default=True) fit_flag = db.Column(db.Boolean, nullable=False, default=True) created_at = db.Column(db.DateTime, nullable=False, default=datetime.now) updated_at = db.Column(db.DateTime, nullable=False,