sqlmon/summarize_sql.js

// summarize_sql.js
// Usage: node summarize_sql.js <inputfile.sql>
// Requires: OPENAI_API_KEY in environment

require('dotenv').config();

const fs = require('fs');
const path = require('path');
const { OpenAI } = require('openai');

if (!process.env.OPENAI_API_KEY) {
  console.error('Error: OPENAI_API_KEY environment variable is required.');
  console.error('Please create a .env file with your OpenAI API key or set the environment variable.');
  process.exit(1);
}

const openai = new OpenAI({
  apiKey: process.env.OPENAI_API_KEY,
});

if (process.argv.length < 3) {
  console.error('Usage: node summarize_sql.js <inputfile.sql>');
  process.exit(1);
}

const inputFile = process.argv[2];
const inputDir = path.dirname(inputFile);
const baseName = path.basename(inputFile, '.sql');
const summaryFile = path.join(inputDir, baseName + '.summary.txt');

function findTableOrViewFile(schemaDotName) {
  // e.g. "Amazon.tVcsLiteUploadQueue" => tables/Amazon.tVcsLiteUploadQueue.sql
  const tablesDir = path.join(__dirname, 'tables');
  const file = path.join(tablesDir, schemaDotName + '.sql');
  if (fs.existsSync(file)) {
    return file;
  }
  return null;
}

function readTableOrView(schemaDotName) {
  const file = findTableOrViewFile(schemaDotName);
  if (!file) return `-- Definition for ${schemaDotName} not found.`;
  return fs.readFileSync(file, 'utf8');
}

const SYSTEM_PROMPT = `
You are a SQL expert assistant. Your task is to help the user understand the definition and logic of a given SQL stored procedure or view.
- Focus on explaining the purpose, main logic, and important details of the procedure/view.
- If you need to look up the definition of a table or view referenced in the code, you can call the function read_table_or_view(schemaDotName) (e.g. read_table_or_view('Amazon.tVcsLiteUploadQueue')) and you will receive the full CREATE statement for that object.
- If you need more than one table/view definition, call read_table_or_view multiple times.
- Be concise but thorough. Output your summary in clear, readable language.
`;

async function main() {
  const sqlText = fs.readFileSync(inputFile, 'utf8');
  let messages = [
    { role: 'system', content: SYSTEM_PROMPT },
    { role: 'user', content: `Please summarize the following SQL stored procedure or view:\n\n${sqlText}` }
  ];

  // Function tool definition for OpenAI function-calling
  const functions = [
    {
      name: "read_table_or_view",
      description: "Get the CREATE statement for a table or view by schema.name",
      parameters: {
        type: "object",
        properties: {
          schemaDotName: {
            type: "string",
            description: "The schema and name, e.g. 'Amazon.tVcsLiteUploadQueue'"
          }
        },
        required: ["schemaDotName"]
      }
    }
  ];

  let summary = null;
  let maxToolCalls = 5; // Prevent infinite loops

  while (maxToolCalls-- > 0) {
    // Log model call
    console.log('\n--- Model Call ---');
    console.log('Model:', "o4-mini");
    console.log('Messages:', JSON.stringify(messages, null, 2));
    console.log('Functions:', JSON.stringify(functions, null, 2));

    const response = await openai.chat.completions.create({
      model: "o4-mini", // or "gpt-3.5-turbo-1106" if you don't have access to gpt-4o
      messages,
      service_tier: "flex",
      functions,
      function_call: "auto"
    });

    const msg = response.choices[0].message;

    if (msg.content) {
      summary = msg.content;
      break;
    } else if (msg.function_call) {
      // LLM is calling our tool
      const { name, arguments: argsJson } = msg.function_call;
      console.log('\n--- Tool Call ---');
      console.log('Function:', name);
      console.log('Arguments:', argsJson);
      if (name === "read_table_or_view") {
        let args;
        try {
          args = JSON.parse(argsJson);
        } catch (e) {
          messages.push({ role: 'assistant', content: "Error: Invalid function call arguments." });
          continue;
        }
        const def = readTableOrView(args.schemaDotName);
        // Log tool result (truncate if large)
        const defPreview = def.length > 500 ? def.slice(0, 500) + '...[truncated]' : def;
        console.log('Result:', defPreview);
        messages.push({
          role: 'function',
          name: 'read_table_or_view',
          content: def
        });
      } else {
        messages.push({ role: 'assistant', content: `Error: Unknown function ${name}` });
      }
    } else {
      messages.push({ role: 'assistant', content: "Error: No content or function call in response." });
    }
  }

  if (!summary) {
    summary = "Error: Could not generate summary after several tool calls.";
  }

  fs.writeFileSync(summaryFile, summary, 'utf8');
  console.log(`Summary written to ${summaryFile}`);
}

main().catch(e => {
  console.error(e);
  process.exit(1);
});
hi