Make "Copy SQL to Clipboard" active all the time

[richb-hanover]

I would like to see the "Copy SQL to Clipboard" icon be active whenever a .prql file has focus. It wouldn't depend on the state of the SQL Preview pane.

Justification: I'm using the VS Code extension (very successfully!) to create various SQL queries to paste into my database. An added advantage of this workflow is that all these PRQL files are in my git repo, so I can track the changes as the queries evolve.

The PRQL language has got to the state that I hardly ever look at the generated SQL (at least not in VS Code), so I don't need to look at the SQL Preview pane or save any SQL files.

Thanks!

[RandomFractals]

It's definitely a usage scenario we did not consider when adding Copy Sql to Clipboard feature for an open SQL Preview, and would be a good new enhancement to add in the next PRQL vscode ext. release.

While we don't have the time to conribute those changes, the relevant code to make this work is in commands.ts that contains copy sql to clipboard command registration and implementation:

https://github.com/PRQL/prql-vscode/blob/main/src/commands.ts#L54

  registerCommand(context, constants.CopySqlToClipboard, () => {

    // get last generated prql sql content from workspace state
    let sql: string | undefined = context.workspaceState.get('prql.sql');

    let sqlFileName = 'SQL';
    if (SqlPreview.currentView) {
      // get sql filename and content fromn sql preview
      sqlFileName = `prql://${path.basename(SqlPreview.currentView.documentUri.path, '.prql')}.sql`;
      sql = SqlPreview.currentView.lastCompilationResult?.sql;
    }

    if (sql !== undefined) {
      // write the last active sql preview sql code to vscode clipboard
      env.clipboard.writeText(sql);
      window.showInformationMessage(`Copied ${sqlFileName} to Clipboard.`);
    }
  });
}

The required changes are rather simple:

• Copy Sql to Clipboard command implementation needs to change and use prql from the active editor to generate the corresponding sql based on PRQL settings when there is no Sql Preview open for the active PRQL document
• Also, make sure to update when clauses for the prql.copySqlToClipboard context menues: https://github.com/PRQL/prql-vscode/blob/main/package.json#L84 to show them for any prql doc, even when there is no active/open sql preview, i.e. change this:

   "when": "prql.sqlPreviewActive || resourceLangId == prql",

to:

   "when": "resourceLangId == prql",

That should get any dev willing to PR this feature started on this implementation.

You can see how to generate sql from active prql document uri in generateSqlFile(), some refactoring might be required to skip file generation and just produce sql to copy to clipboard :)

https://github.com/PRQL/prql-vscode/blob/main/src/commands.ts#L122

async function generateSqlFile(prqlDocumentUri: Uri, prqlCode: string) {

  // compile given prql source code
  const sqlCode = compile(prqlCode);
...

[richb-hanover]

Thanks for producing these "hints". I'm hopeful someone with some "Javascript chops" can jump in.

[RandomFractals]

@richb-hanover on a related note, you'll probably like this feature in the new Data Notebook extension with PRQL support we are working on. It will let you view and copy generated SQL from the PRQL notebook document query cell output: https://twitter.com/TarasNovak/status/1643568855887781890

[richb-hanover]

@RandomFractals Thanks for this update. Your project looks good, but I have trouble grokking the entire description from the tweets.

1. Do you have a website that gives an overview of the project?
2. I have a workflow that dumps data into a SQLite database. Would the Data Notebook extension be able to work with that data yet?

Thanks again

[RandomFractals]

@richb-hanover That Data Notebook extension will be released later this year to our Pro sponsors on github, with all the docs and examples from our daily shares on twitter in one place.

It does support SQLite data connections and PRQL or SQL queries loaded in a notebook view in VSCode. I am wrapping up DuckDB wasm support. Other supported databases include PostgreSQL, MSSql and MySql.

[richb-hanover]

@RandomFractals Terrific! I will watch for the announcement!