Skip to content

FEATURES.md

Latest commit

 

History

History
195 lines (127 loc) · 13.2 KB

FEATURES.md

File metadata and controls

195 lines (127 loc) · 13.2 KB

AI-Powered Description Features

This document provides a detailed overview of the four AI-powered description features in SQL Server Script and Document Builder: Describe, Describe with…, Describe missing, and Batch Describe.

All four features require a configured LLM connection (API key, endpoint, and model) before use. If the AI settings are not configured, the application automatically opens the Settings dialog to prompt the user.

1. Describe

Menu Handler: AIDescriptionAssistantToolStripMenuItem_Click Core Method: ColumnDefView.AIAssistant()

Overview

The Describe feature generates AI-powered descriptions for the currently selected database object (table or view), including both the object-level description and all column descriptions. It overwrites any existing descriptions with freshly generated content.

How It Works

  1. AI Settings Check – Verifies that the LLM API key, endpoint, and model are configured. If not, the Settings dialog is shown.

  2. View Optimization (? 20 missing columns) – If the selected object is a View and 20 or more columns are missing descriptions, the system first runs the View Preparation step:

    • The LLM parses the view definition SQL to extract the source base tables and their columns.
    • For each source base table, the system opens the table from the database and inherits any existing column descriptions that match view column names.
    • If the view itself has no description, the LLM generates one based on the view definition.
    • After inheriting descriptions, any remaining undescribed columns are handled by the DescribeMissingCoreAsync flow.

  3. Reference Context Gathering – For tables, the system queries foreign key relationships to build a JSON reference context. This gives the LLM awareness of related tables and columns for more accurate descriptions.

  4. LLM Prompt Construction – The current TableContext (schema, table name, existing description, and all columns with their names, data types, and ordinal positions) is serialized to JSON. The prompt instructs the LLM to:

    • Provide a 1-sentence table description.
    • Fill in ultra-concise column descriptions (e.g., "Primary key", "Event timestamp").
    • Append Reference: [schema].[table].[column] for foreign key columns.
    • Return only valid JSON without modifying names or data types.

  5. Batched Column Processing – Columns are sent to the LLM in batches of 10 to stay within token limits. The table description is only generated from the first batch; subsequent batches preserve it.

  6. UI Update – The table description text box and the column descriptions in the DataGridView are updated with the LLM-generated results. The change-tracking flag is set so the user can save or discard.

  7. Events – AIProcessingStarted fires at the beginning and AIProcessingCompleted fires at the end, toggling the busy/working UI state (animated progress indicator, disabled controls).

Key Characteristics

  • Overwrites all descriptions – Both the table description and every column description are regenerated.
  • Supports optional language setting – Descriptions can be generated in a language other than English based on the AI language setting.
  • Smart view handling – For views with many columns, source table descriptions are inherited first to minimize LLM calls.

2. Describe with…

Menu Handler: DescriptionAssistantPlusToolStripMenuItem_Click Core Method: ColumnDefView.AIAssistant(string additionalInfo)

Overview

Describe with… is an enhanced version of the Describe feature that allows the user to provide additional context or specific instructions to guide the AI output. This is useful when the schema alone doesn't convey the business meaning of columns.

How It Works

  1. AI Settings Check – Same as Describe.

  2. User Input Dialog – An InputBox dialog is displayed with the title "Description Assistant Plus - Additional Information". The user can enter up to 4,000 characters of free-form text describing:

    • Business context (e.g., "This table is part of the HR module")
    • Naming conventions (e.g., "Columns prefixed with 'fl_' are boolean flags")
    • Specific requirements (e.g., "Use formal business terminology")
    • Domain knowledge (e.g., "This is an e-commerce order system")

  3. AI Processing – The user's additional information is passed as the additionalInfo parameter to ColumnDefView.AIAssistant(additionalInfo). The same flow as Describe executes, but the LLM prompt now includes an Extra context: section containing the user's input.

  4. Prompt Enhancement – In the BuildPrompt method, if additionalInfo is non-empty, it is injected into the prompt:

    Extra context: <user's additional information>

    The prompt also instructs the LLM: "If additional information is provided above, use it as extra context for your descriptions."

  5. Result Handling – Identical to the standard Describe flow: batched processing, UI updates, and change tracking.

Key Characteristics

  • User-guided AI output – The additional context lets the user steer description quality and relevance.
  • Same underlying engine – Uses the exact same AIAssistant method as Describe; the only difference is the non-empty additionalInfo string.
  • Optional input – The user can leave the input blank and click OK, which behaves identically to the standard Describe feature.

3. Describe Missing

Menu Handler: DescribeMissingToolStripMenuItem_Click Core Method: ColumnDefView.DescribeMissing(string additionalInfo) ? DescribeMissingCoreAsync(string additionalInfo, bool skipViewPreparation)

Overview

Describe Missing selectively generates descriptions only for columns (and the table) that are currently missing descriptions. Existing descriptions are left untouched. This is ideal for partially documented objects where you want to fill in the gaps without overwriting prior work.

How It Works

  1. AI Settings Check – Same as the other features.

  2. View Preparation (? 20 missing columns) – If the object is a View with 20 or more undescribed columns, the system first runs the View Preparation step (same as in Describe):

    • Extracts source base tables from the view definition using the LLM.
    • Inherits column descriptions from source base tables where column names match and the view column has no description.
    • Generates a view-level description if one is missing.
    • Updates the UI grid with inherited descriptions.

  3. Object Type Filter – Only Tables and Views are supported. Other object types (stored procedures, functions, etc.) are skipped.

  4. Missing Column Identification – The system filters _tableContext.Columns to find only those with a blank or whitespace-only Description. A new TableContext is created containing only these missing columns.

  5. Reference Context Gathering – Foreign key relationships are queried to provide the LLM with context about related tables.

  6. LLM Processing – The filtered context (containing only undescribed columns) is sent to the LLM via GenerateTableAndColumnDescriptionsAsync. The same batched processing (batches of 10) and prompt construction is used.

  7. Selective Update – After the LLM responds, only columns that were originally missing descriptions are updated. The system iterates through the generated results and copies descriptions back to the original _tableContext only if the generated description is non-empty.

  8. UI Update – The DataGridView is refreshed with the newly generated descriptions. The change-tracking flag is updated.

Key Characteristics

  • Non-destructive – Never overwrites existing descriptions. Only blank columns are sent to the LLM.
  • Efficient for views – The two-phase approach (inherit from source tables first, then LLM for remainder) minimizes API calls for views with many undescribed columns.
  • Works with the table description too – If the table-level description is missing, it will be generated as part of the first batch.
  • No additional context by default – When invoked from the menu, additionalInfo is passed as an empty string. However, the method signature supports it for internal use (e.g., from Batch Describe).

4. Batch Describe

Menu Handler: BatchDescribeToolStripMenuItem_Click Core Method: Orchestrates ColumnDefView.OpenAsync, ColumnDefView.DescribeMissing, and ColumnDefView.Save in a loop.

Overview

Batch Describe processes multiple selected database objects sequentially in a single operation. For each object, it generates descriptions for missing columns (using the Describe Missing logic), saves the results to the database, and advances to the next object. This enables bulk documentation of entire schemas or selected object groups.

How It Works

  1. Connection Check – Verifies an active database connection exists.

  2. AI Settings Check – Same as the other features.

  3. Object Selection Dialog – Opens the DBObjectsSelectForm dialog, which allows the user to:

    • Browse available database objects (tables, views).
    • Filter by schema or search by name.
    • Select/deselect individual objects or use bulk selection.
    • The dialog returns a List<ObjectName> of the chosen objects.

  4. Additional Context Input – An InputBox dialog prompts the user for optional additional context (same as Describe with…). This context is applied to every object in the batch.

  5. Pre-Batch Save – Any pending edits on the currently displayed object are saved before batch processing begins.

  6. Sequential Processing Loop – For each selected object:

    • Progress Update – The progress bar and status message are updated with the current completion percentage.
    • Open – The object is loaded into the definition panel via definitionPanel.OpenAsync(obj, _currentConnection), which reads the object's schema, columns, and existing descriptions from the database.
    • Describe Missing – definitionPanel.DescribeMissing(additionalInfo) is called, which generates descriptions only for undescribed columns (with view preparation for views having ? 20 missing columns).
    • Save – definitionPanel.Save() persists all generated descriptions back to the database immediately, ensuring no work is lost if the batch is interrupted.

  7. UI State Management – During processing:

    • The UI enters "working mode" (controls disabled, animated status message, wait cursor).
    • The progress bar shows completion percentage.
    • The objects tab control and definition panel are disabled to prevent user interaction.
    • After completion (or if an error occurs), the UI is restored to its normal state.

Key Characteristics

  • Uses Describe Missing internally – Each object in the batch is processed with the non-destructive Describe Missing logic, so existing descriptions are preserved.
  • Saves after each object – Descriptions are persisted to the database after each object is processed, providing incremental safety.
  • Supports additional context – The user can provide batch-wide additional context that applies to all objects.
  • Progress reporting – A progress bar and percentage text keep the user informed throughout the batch operation.
  • Graceful UI handling – Controls are disabled during processing and re-enabled afterward in a try/finally block to ensure the UI is always restored.

Feature Comparison

Feature Scope Overwrites Existing? User Input Saves Automatically?
Describe Single object ? Yes – all descriptions None ? No – user reviews first
Describe with… Single object ? Yes – all descriptions Additional context text ? No – user reviews first
Describe missing Single object ? No – only blank columns None ? No – user reviews first
Batch Describe Multiple objects ? No – only blank columns Object selection + optional context ? Yes – after each object

Technical Architecture

All four features share a common technical foundation:

  • AIHelper.GenerateTableAndColumnDescriptionsAsync – Core method that constructs the LLM prompt, sends requests in batches of 10 columns, parses JSON responses, and updates the TableContext.
  • AIHelper.BuildPrompt – Constructs the LLM prompt with table context JSON, reference data, database description, additional info, and language instructions.
  • AIHelper.CallLLMAsync – Sends HTTP POST requests to the configured LLM endpoint (OpenAI-compatible API) with a system message and user prompt, then extracts the assistant's reply.
  • AIHelper.ExtractViewSourceColumnsAsync – Parses view SQL definitions to identify source base tables and their columns for the view preparation optimization.
  • AIHelper.GenerateObjectDescriptionFromDefinitionAsync – Generates a single-sentence description for an object (view, procedure, etc.) based on its SQL definition.
  • TableContext / ColumnContext – Data models that hold the schema, table/column names, data types, and descriptions. Serialized to/from JSON for LLM communication.
  • ColumnDefView – The UI control that hosts the description editor grid, manages the TableContext, and coordinates AI processing with change tracking and database persistence.