init_sqlalchemy.py

#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
# @Version     : Python 3.11.4
# @Software    : Sublime Text 4
# @Author      : StudentCWZ
# @Email       : StudentCWZ@outlook.com
# @Date        : 2023/10/28 22:01
# @File        : init_sqlalchemy.py
# @Description :
"""

from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy import text

from application.utils import DatabaseUri

db = SQLAlchemy()


def init_database(app: Flask) -> None:
    """
    Initialize the database extension

    :param app: flask.Flask application instance
    :return: None
    """
    cfg = app.config.Database
    uri = DatabaseUri(
        db_type=cfg.Type,
        username=cfg.Username,
        password=cfg.Password,
        host=cfg.Host,
        port=cfg.Port,
        db=cfg.DB,
        driver=cfg.Driver,
    )
    app.config.setdefault('SQLALCHEMY_DATABASE_URI', uri.create())
    db.app = app
    db.init_app(app)
    with app.app_context():
        db.session.execute(text(f"""
        CREATE TABLE IF NOT EXISTS {cfg.TableName} (
            id SERIAL NOT NULL,
            username VARCHAR(80) NOT NULL,
            email VARCHAR(120) NOT NULL,
            password VARCHAR(255) NOT NULL,
            active BOOLEAN,
            created_at TIMESTAMP WITH TIME ZONE NOT NULL,
            PRIMARY KEY (id, created_at)
        ) PARTITION BY RANGE (created_at);
        """))
        db.session.execute(text("""
        CREATE TABLE IF NOT EXISTS logs (
            id SERIAL NOT NULL,
            date_time TIMESTAMP WITH TIME ZONE NOT NULL,
            uuid varchar(100) DEFAULT NULL,
            mid varchar(50) DEFAULT NULL,
            mid_type varchar(50) DEFAULT NULL,
            mac_wifi varchar(50) DEFAULT NULL,
            mac_voice varchar(50) DEFAULT NULL,
            code smallint DEFAULT NULL,
            query varchar(255) DEFAULT NULL,
            terminal_domain varchar(50) DEFAULT NULL,
            terminal_intent varchar(50) DEFAULT NULL,
            distribution_gree_domain varchar(50) DEFAULT NULL,
            distribution_gree_intent varchar(50) DEFAULT NULL,
            response_text text,
            emotion_class varchar(50) DEFAULT NULL,
            skill_id varchar(100) DEFAULT NULL,
            voice_portal smallint NOT NULL,
            service_nlu varchar(50) DEFAULT NULL,
            service_type smallint NOT NULL,
            slots text,
            yzs_request_id varchar(50) DEFAULT NULL,
            yzs_remote_ip varchar(15) DEFAULT NULL,
            yzs_app_key varchar(50) DEFAULT NULL,
            yzs_ud_id varchar(50) DEFAULT NULL,
            yzs_user_id varchar(50) DEFAULT NULL,
            yzs_intent text,
            yzs_general text,
            yzs_nlu_time varchar(20) DEFAULT NULL,
            get_body_time varchar(20) DEFAULT NULL,
            gree_nlu_time varchar(20) DEFAULT NULL,
            get_homeid_time varchar(20) DEFAULT NULL,
            tencent_nlu_time varchar(20) DEFAULT NULL,
            cost_time varchar(20) DEFAULT NULL,
            PRIMARY KEY (id, date_time)
        ) PARTITION BY RANGE (date_time);
        """))
        db.session.commit()