connection.mjs

import mysql from 'mysql'
import Logger from './logger.mjs'
import fs from 'node:fs'
import path from 'node:path'
import dotenv from 'dotenv'

const logger = new Logger('buildMaterials')

// 先构造出.env*文件的绝对路径
const appDirectory = fs.realpathSync(process.cwd())
const resolveApp = (relativePath) => path.resolve(appDirectory, relativePath)
const pathsDotenv = resolveApp('.env')
// 加载.env.local
dotenv.config({ path: `${pathsDotenv}.local` })
const { SQL_HOST, SQL_PORT, SQL_USER, SQL_PASSWORD, SQL_DATABASE } = process.env

// 组件表名称
const componentsTableName = 't_component'
// 组件关联到物料资产包的id
const materialHistoryId = 1
// 数据库配置
const mysqlConfig = {
  host: SQL_HOST, // 主机名（服务器地址）
  port: SQL_PORT, // 端口号
  user: SQL_USER, // 用户名
  password: SQL_PASSWORD, // 密码
  database: SQL_DATABASE // 数据库名称
}
class MysqlConnection {
  constructor(config) {
    this.config = config || mysqlConfig
    // 是否连接上了数据库
    this.connected = false
    this.connection = mysql.createConnection(this.config)
  }

  connect() {
    return new Promise((resolve, reject) => {
      this.connection.connect((error) => {
        if (error) {
          logger.warn('unable to connect to the database, please check the database configuration is correct.')
          reject()
        } else {
          logger.success('database connected.')
          this.connected = true
          resolve()
        }
      })
    })
  }

  /**
   * 执行sql语句，更新数据库
   * @param {string} sql sql语句
   * @param {string} componentName 组件名称
   */
  query(sql) {
    return new Promise((resolve, reject) => {
      this.connection.query(sql, (error, result) => {
        if (error) {
          reject(error)
        } else {
          resolve(result)
        }
      })
    })
  }

  /**
   * 组件字段映射
   * @param {string} field 字段名
   * @returns 映射后的字段名
   */
  fieldTransform(field) {
    const fieldMap = {
      docUrl: 'doc_url',
      devMode: 'dev_mode',
      schema: 'schema_fragment'
    }

    return fieldMap[field] || field
  }

  /**
   * 格式化单引号
   * @param {string} str 待格式化的字符串
   * @returns 格式化后的字符串
   */
  formatSingleQuoteValue(str) {
    if (typeof str !== 'string') {
      return str
    }

    return str.replace(/'/g, "\\'")
  }

  /**
   * 校验组件数据是否有效
   * @param {object} component 组件数据
   * @returns boolean 校验组件字段是否失败，false-有字段出错
   */
  isValid(component, file) {
    const longTextFields = ['name', 'npm', 'snippets', 'schema_fragment', 'configure', 'component_metadata']

    return Object.entries(component).every(([key, value]) => {
      if (longTextFields.includes(key) && value !== null && typeof value !== 'object') {
        logger.error(`the value of "${key}" is not valid JSON at ${file}.`)

        return false
      }

      return true
    })
  }

  /**
   * 生成更新组件的sql语句
   * @param {object} component 组件数据
   * @returns 更新组件的sql语句
   */
  updateComponent(component, file) {
    const valid = this.isValid(component, file)

    if (!valid) {
      return
    }

    const values = []
    let sqlContent = `update ${componentsTableName} set `

    Object.keys(component).forEach((key) => {
      const { [key]: value } = component
      const fields = [
        'version',
        'name',
        'component',
        'icon',
        'description',
        'docUrl',
        'screenshot',
        'tags',
        'keywords',
        'devMode',
        'npm',
        'group',
        'category',
        'priority',
        'snippets',
        'schema',
        'configure',
        'public',
        'framework',
        'isOfficial',
        'isDefault',
        'tiny_reserved',
        'tenant',
        'createBy',
        'updatedBy'
      ]

      if (!fields.includes(key)) {
        return
      }

      const field = this.fieldTransform(key)
      let updateContent = ''

      if (['id', 'component'].includes(field)) {
        return
      }

      if (value === void 0) {
        return
      }

      if (typeof value === 'string') {
        const formatValue = this.formatSingleQuoteValue(value)

        updateContent = `\`${field}\` = '${formatValue}'`
      } else if (typeof field === 'number' || field === null) {
        updateContent = `\`${field}\` = ${value}`
      } else {
        const formatValue = this.formatSingleQuoteValue(JSON.stringify(value))

        updateContent = `\`${field}\` = '${formatValue}'`
      }

      values.push(updateContent)
    })

    sqlContent += values.join()
    sqlContent += ` where component = '${component.component}';`

    this.query(sqlContent, component.component)
      .then(() => {
        logger.success(`${component.component} updated.`)
      })
      .catch((error) => {
        logger.error(`failed to update ${component.component}: ${error}.`)
      })
  }

  /**
   * 新建的组件关联物料资产包
   * @deprecated 物料资产包已废弃，使用relationMaterialHistory替代
   * @param {number} id 新建的组件id
   */
  relationMaterialBlockHistory(id) {
    const uniqSql = `SELECT * FROM \`material_histories_components__user_components_mhs\` WHERE \`material-history_id\`=${materialHistoryId} AND \`user-component_id\`=${id}`
    this.query(uniqSql).then((result) => {
      if (!result.length) {
        const sqlContent = `INSERT INTO \`material_histories_components__user_components_mhs\` (\`material-history_id\`, \`user-component_id\`) VALUES (${materialHistoryId}, ${id})`

        this.query(sqlContent)
      }
    })
  }

   /**
   * 新建的组件关联物料资产包
   * @param {number} id 新建的组件id
   */
   relationMaterialHistory(id) {
    const uniqSql = `SELECT * FROM \`r_material_history_component\` WHERE \`material_history_id\`=${materialHistoryId} AND \`component_id\`=${id}`
    this.query(uniqSql).then((result) => {
      if (!result.length) {
        const sqlContent = `INSERT INTO \`r_material_history_component\` (\`material_history_id\`, \`component_id\`) VALUES (${materialHistoryId}, ${id})`
        this.query(sqlContent)
      }
    })
  }


  /**
   * 生成新增组件的sql语句
   * @param {object} component 组件数据
   * @returns 新增组件的sql语句
   */
  insertComponent(component, file) {
    const valid = this.isValid(component, file)

    if (!valid) {
      return
    }

    const defaultName = {
      zh_CN: component.component
    }
    const defaultNpm = {
      package: '',
      exportName: '',
      version: '1.0.0',
      destructuring: true
    }
    const defaultConfigure = {
      loop: true,
      condition: true,
      styles: true,
      isContainer: true,
      isModal: false,
      nestingRule: {
        childWhiteList: '',
        parentWhiteList: '',
        descendantBlacklist: '',
        ancestorWhitelist: ''
      },
      isNullNode: false,
      isLayout: false,
      rootSelector: '',
      shortcuts: {
        properties: ['value', 'disabled']
      },
      contextMenu: {
        actions: ['create symbol'],
        disable: ['copy', 'remove']
      }
    }
    const {
      version = '1.0.0',
      name = defaultName,
      component: componentName,
      icon,
      description,
      docUrl,
      screenshot,
      tags,
      keywords,
      devMode = 'proCode',
      npm = defaultNpm,
      group,
      category = 'general',
      priority = 1,
      snippets = [{}],
      schema = {},
      configure = defaultConfigure,
      public: publicRight = 0,
      framework = 'vue',
      isOfficial = 0,
      isDefault = 0,
      tiny_reserved = 0,
      component_metadata = null,
      library_id = 1,
      tenant_id = 1,
      renter_id = 1,
      site_id = 1,
      created_by = 1,
      last_updated_by = 1
    } = component
    const values = `('${version}',
    '${this.formatSingleQuoteValue(JSON.stringify(name))}',
    '${componentName}',
    '${icon}',
    '${this.formatSingleQuoteValue(description)}',
    '${docUrl}',
    '${screenshot}',
    '${tags}',
    '${keywords}',
    '${devMode}',
    '${this.formatSingleQuoteValue(JSON.stringify(npm))}',
    '${group}',
    '${category}',
    '${priority}',
    '${this.formatSingleQuoteValue(JSON.stringify(snippets))}',
    '${this.formatSingleQuoteValue(JSON.stringify(schema))}',
    '${this.formatSingleQuoteValue(JSON.stringify(configure))}',
    '${publicRight}',
    '${framework}',
    '${isOfficial}',
    '${isDefault}',
    '${tiny_reserved}',
    '${component_metadata}',
    '${library_id}',
    '${tenant_id}',
    '${renter_id}',
    '${site_id}',
    '${created_by}',
    '${last_updated_by}'
  );`

    const sqlContent = `INSERT INTO ${componentsTableName} (version, name, name_en, icon, description, doc_url,
       screenshot, tags, keywords, dev_mode, npm, \`group\`, \`category\`, priority, snippets,
        schema_fragment, configure, \`public\`, framework, is_official, is_default, tiny_reserved,component_metadata,
         library_id, tenant_id,renter_id,site_id, created_by, last_updated_by) VALUES ${values}`.replace(/\n/g, '')

    this.query(sqlContent, componentName)
      .then((result) => {
        const id = result.insertId

        logger.success(`${component.component} added.`)
        this.relationMaterialHistory(id)
      })
      .catch((error) => {
        logger.error(`add ${component.component} failed：${error}.`)
      })
  }

  /**
   * 初始化数据库数据，判断是否已存在组件，不存在时执行新增组件
   * @param {object} component 组件数据
   */
  initDB(component) {
    const selectSqlContent = `SELECT * FROM ${this.config.database}.${componentsTableName} WHERE name_en = '${component.component}'`

    this.query(selectSqlContent)
      .then((result) => {
        if (!result.length) {
          this.insertComponent(component)
        }
      })
      .catch((error) => {
        logger.error(`query ${component.component} failed：${error}.`)
      })
  }

  /**
   * 创建组件表
   * @returns promise
   */
  createUserComponentsTable() {
    const sqlContent = `
      CREATE TABLE ${componentsTableName}  (
        id int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
        version varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
        name longtext CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
        component varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
        icon varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
        description varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
        doc_url varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
        screenshot varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
        tags varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
        keywords varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
        dev_mode varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
        npm longtext CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
        \`group\` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
        category varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
        priority int(11) NULL DEFAULT NULL,
        snippets longtext CHARACTER SET utf8 COLLATE utf8_general_ci NULL,
        schema_fragment longtext CHARACTER SET utf8 COLLATE utf8_general_ci NULL,
        configure longtext CHARACTER SET utf8 COLLATE utf8_general_ci NULL,
        createdBy int(11) NULL DEFAULT NULL,
        updatedBy int(11) NULL DEFAULT NULL,
        created_by int(11) NULL DEFAULT NULL,
        updated_by int(11) NULL DEFAULT NULL,
        created_at timestamp NULL DEFAULT CURRENT_TIMESTAMP,
        updated_at timestamp NULL DEFAULT CURRENT_TIMESTAMP,
        public int(11) NULL DEFAULT NULL,
        framework varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
        isOfficial tinyint(1) NULL DEFAULT NULL,
        isDefault tinyint(1) NULL DEFAULT NULL,
        tiny_reserved tinyint(1) NULL DEFAULT NULL,
        tenant int(11) NULL DEFAULT NULL,
        component_metadata longtext CHARACTER SET utf8 COLLATE utf8_general_ci NULL,
        library int(11) NULL DEFAULT NULL,
        PRIMARY KEY (id) USING BTREE,
        UNIQUE INDEX unique_component(createdBy, framework, component, version) USING BTREE
      ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = DYNAMIC;
    `.replace(/\n/g, '')

    return new Promise((resolve, reject) => {
      this.query(sqlContent)
        .then((result) => {
          logger.success(`table ${componentsTableName} created.`)
          resolve(result)
        })
        .catch((error) => {
          logger.error(`create table ${componentsTableName} failed：${error}.`)
          reject(error)
        })
    })
  }

  /**
   * 初始化数据库的组件表
   * @returns promise
   */
  initUserComponentsTable() {
    return new Promise((resolve, reject) => {
      // 查询是否已存在表
      this.query(`SHOW TABLES LIKE '${componentsTableName}'`)
        .then((result) => {
          if (result.length) {
            // 已存在
            resolve()
          } else {
            this.createUserComponentsTable()
              .then(() => {
                resolve()
              })
              .catch((err) => {
                reject(err)
              })
          }
        })
        .catch((error) => {
          reject(error)
        })
    })
  }
}

export default MysqlConnection