AutoSQL

Required Intellij Plugin: Database Tools and SQL

Demo Video: https://www.bilibili.com/video/BV1Ye411h7Qu/

implementation: cc.unitmesh.database.flow.AutoSqlFlow

1. user should connect to DataSource/Database
2. based on user input, AI select the target tables.
3. generate the SQL scripts for the target tables.
   • generate columns of selected tables.
   • generate SQL scripts for selected tables.

Prompt Override

Steps:

• step 1: prompts/genius/sql/sql-gen-clarify.vm
• step 2: prompts/genius/sql/sql-gen-design.vm

Context:

data class AutoSqlContext(
   val requirement: String,
   val databaseVersion: String,
   val schemaName: String,
   val tableNames: List<String>,
   /**
    * Step 2.
    * A list of table names to retrieve the columns from.
    */
   var tableInfos: List<String> = emptyList(),
)

Current Prompt

Clarify:

    You are a professional Database Administrator.
    According to the user's requirements, you should choose the best Tables for the user in List.
    
    — User use database: ${context.databaseVersion}
    - User schema name: ${context.schemaName}
    - User tables: ${context.tableNames}
    
    For example:
    
    - Question(requirements): calculate the average trip length by subscriber type.// User tables: trips, users, subscriber_type
    - You should anwser: [trips, subscriber_type]
    
    ----
    
    Here are the User requirements:
    
    ${context.requirement}
    
    Please choose the best Tables for the user, just return the table names in a list, no explain.

Design:

    You are a professional Database Administrator.
    According to the user's requirements, and Tables info, write SQL for the user.
    
    — User use database: ${context.databaseVersion}
    - User schema name: ${context.schemaName}
    - User tableInfos: ${context.tableInfos}
    
    For example:
    
    - Question(requirements): calculate the average trip length by subscriber type.
    // table `subscriber_type`: average_trip_length: int, subscriber_type: string
    - Answer:

    select average_trip_length from subscriber_type where subscriber_type = 'subscriber'
    
    ----
    
    Here are the requirements:

    ${context.requirement}
    
    Please write your SQL with Markdown syntax, no explanation is needed. :