schema_tool.txt

openapi: 3.0.1
info:
  title: SQL Executor for Dual Databases
  description: |
    Execute read-only SQL queries on two specialized databases and return structured JSON results:
    - pubchemlite: Compound basic information database (core table: pubchemlite_exposomics_20251226)
    - invitrodb_v4_3: In vitro toxicity experiment database (core tables: assay, chemical, mc0, assay_component)
    (Adapted for local/server Flask API, only SELECT queries supported, write/modify operations prohibited)
  version: 1.0.0
servers:
  - url: http://192.168.0.179:5000
    description: Main API Server
paths:
  /execute_sql:
    post:
      summary: Execute SELECT query on specified database
      operationId: executeSqlPost
      description: |
        Execute read-only SELECT queries on pubchemlite or invitrodb_v4_3 database.
        Notes:
        1. Only SELECT operation is allowed (INSERT/UPDATE/DELETE/DROP are prohibited);
        2. For fields with special characters (e.g. pred_CCS_A2_[M+H]+ in pubchemlite), escape []/+ with backslash (\) or use backticks (`);
        3. Avoid Python format characters (%, A, s, d) in SQL to prevent parsing errors;
        4. Add LIMIT clause to avoid large result sets (recommended LIMIT 100).
      requestBody:
        required: true
        content:
          application/json:
            schema:
              type: object
              properties:
                db_identifier:
                  type: string
                  description: Target database identifier (case-sensitive)
                  enum: [pubchemlite, invitrodb_v4_3]
                  example: "pubchemlite"
                  x-validation: "Must be one of the specified enum values"
                sql:
                  type: string
                  description: |
                    Valid MySQL SELECT query (only):
                    ## pubchemlite (core table: pubchemlite_exposomics_20251226)
                    Key fields:
                    - Basic info: Identifier, CompoundName, MolecularFormula, SMILES, XLogP
                    - Literature/Patent: PubMed_Count, Patent_Count
                    - Toxicity/Property: SafetyInfo, ToxicityInfo, MonoisotopicMass
                    - Predicted CCS: pred_CCS_A2_[M+H]+, pred_CCS_A2_[M+Na]+, pred_CCS_A2_[M-H]- (escape special chars!)
                    
                    ## invitrodb_v4_3 (core tables)
                    - assay: aid, assay_name, organism, tissue, ncbi_taxon_id
                    - chemical: chid, casn, chnm (chemical name), dsstox_substance_id
                    - mc0: m0id, acid, spid, conc (concentration μM), rval (response value)
                    - assay_component: acid, assay_component_name (toxicity type)
                  examples:
                    pubchemlite_example:
                      summary: Query compound basic info (with escaped special field)
                      value: "SELECT Identifier, CompoundName, `pred_CCS_A2_[M+H]+` FROM pubchemlite_exposomics_20251226 WHERE PubMed_Count > 5 LIMIT 10"
                    invitrodb_example:
                      summary: Query human in vitro toxicity data
                      value: "SELECT c.chnm, a.assay_name, m.conc, m.rval FROM chemical c JOIN mc0 m ON c.dsstox_substance_id = m.dsstox_substance_id JOIN assay a ON m.aid = a.aid WHERE a.ncbi_taxon_id = 9606 LIMIT 10"
              required:
                - db_identifier
                - sql
              x-error-tips: |
                1. Missing required parameters: Ensure both db_identifier and sql are provided;
                2. Invalid db_identifier: Only pubchemlite/invitrodb_v4_3 are allowed;
                3. SQL syntax error: Check field/table names and special character escaping.
      responses:
        '200':
          description: Query executed successfully (returns array of records)
          content:
            application/json:
              schema:
                type: object
                properties:
                  status:
                    type: string
                    enum: [success, error]
                    description: Execution status
                  data:
                    type: array
                    items:
                      type: object
                      description: Query results (key = field name, value = field value)
                      additionalProperties: true
                  message:
                    type: string
                    description: Error message (empty if success)
                examples:
                  pubchemlite_response:
                    summary: pubchemlite query success
                    value:
                      status: "success"
                      data: [
                        {
                          "Identifier": "CID12345",
                          "CompoundName": "Aspirin",
                          "MolecularFormula": "C9H8O4",
                          "pred_CCS_A2_[M+H]+": 181.05
                        }
                      ]
                      message: ""
                  invitrodb_response:
                    summary: invitrodb_v4_3 query success
                    value:
                      status: "success"
                      data: [
                        {
                          "chnm": "Aspirin",
                          "assay_name": "Cell Viability Assay",
                          "conc": 10.0,
                          "rval": 25.8
                        }
                      ]
                      message: ""
        '400':
          description: Bad Request (client-side error)
          content:
            application/json:
              schema:
                type: object
                properties:
                  status:
                    type: string
                    default: "error"
                  message:
                    type: string
                examples:
                  invalid_db_identifier:
                    summary: Incorrect database identifier
                    value:
                      status: "error"
                      message: "Parameter error: db_identifier only supports pubchemlite/invitrodb_v4_3"
                  special_char_error:
                    summary: Unescaped special characters in SQL
                    value:
                      status: "error"
                      message: "SQL parsing error: pred_CCS_A2_[M+H]+ needs escaping (recommended: `pred_CCS_A2_[M+H]+` or pred_CCS_A2_\\[M+H\\]+)"
                  format_char_conflict:
                    summary: Python format character conflict
                    value:
                      status: "error"
                      message: "Request processing failed: unsupported format character 'A' (please avoid %/A/s/d format characters in SQL)"
                  cross_db_field:
                    summary: Cross-database field query
                    value:
                      status: "error"
                      message: "SQL syntax error: CompoundName field does not exist in invitrodb_v4_3 database (this field is only supported in pubchemlite)"
        '500':
          description: Server Error (database/server-side error)
          content:
            application/json:
              schema:
                type: object
                properties:
                  status:
                    type: string
                    default: "error"
                  message:
                    type: string
                examples:
                  db_connection_failed:
                    summary: Database connection timeout
                    value:
                      status: "error"
                      message: "Database connection failed: pubchemlite database connection timeout, please check database configuration"
                  table_not_exist:
                    summary: Table does not exist in target database
                    value:
                      status: "error"
                      message: "Database execution error: assay table does not exist in pubchemlite database (this table is only supported in invitrodb_v4_3)"
                  sql_timeout:
                    summary: SQL query timeout
                    value:
                      status: "error"
                      message: "SQL execution timeout: please add LIMIT to restrict returned rows (recommended LIMIT 100)"
    parameters: []
components:
  schemas: {}