ursa-cli 0.11.0

The ursa command-line tool: SQL over Parquet, CSV, JSON and Arrow files, local or in object storage.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
//! `ursa`: SQL for graph analytics over Parquet, CSV, JSON and Arrow files.
//!
//! ```text
//! ursa -c "SELECT * FROM 'karate_edges.csv' LIMIT 3"
//! ursa --format csv -f query.sql > out.csv
//! echo "SELECT 1" | ursa
//! ursa club.ursa -c "SELECT * FROM degree(karate)"
//! ursa club.ursa          # the interactive shell
//! ```
//!
//! With a database file (`club.ursa`), views, external tables and graphs are
//! kept in it between runs; without one, the session is in memory. With no
//! SQL to run and a terminal on stdin, `ursa` starts the shell ([`shell`],
//! [`repl`]). `-c`, `-f` and stdin run through the shell too, so they may hold
//! dot-commands (`.timer on`, `.mode csv`, `.read FILE`), as in DuckDB.

use std::ffi::OsString;
use std::io::{self, IsTerminal, Read, Write};
use std::path::PathBuf;
use std::process::ExitCode;

use clap::parser::ValueSource;
use clap::{ArgMatches, CommandFactory, FromArgMatches, Parser};
use datafusion::error::DataFusionError;
use datafusion::prelude::SessionContext;

mod interrupt;
mod notices;
mod repl;
mod startup;

use ursa_shell::print::{is_broken_pipe, OutputFormat, DEFAULT_MAX_ROWS};
use ursa_shell::shell::{Flow, Shell};

/// The agent skill for writing Ursa SQL, which `--skill` prints: a copy of
/// `skills/ursa-sql/SKILL.md` kept in the crate, so the published crate has
/// it too (tests/cli.rs checks that the two match).
const SKILL: &str = include_str!("ursa-sql-skill.md");

/// The end of `--help`.
const AFTER_HELP: &str = concat!(
    "Examples:\n",
    "  ursa -c \"SELECT * FROM 'edges.parquet' LIMIT 3\"         # any file is a table\n",
    "  ursa --format json -c \"SELECT * FROM ursa_functions()\"  # JSON, for programs\n",
    "  ursa club.ursa -f query.sql                             # SQL and dot-commands\n",
    "  ursa club.ursa                                          # the shell (.help)\n",
    "\n",
    "An edge table is a graph:\n",
    "  CREATE PROPERTY GRAPH g EDGE TABLES (ties SOURCE KEY (src) DESTINATION KEY (dst));\n",
    "  SELECT * FROM pagerank(g) ORDER BY pagerank DESC LIMIT 5;\n",
    "\n",
    "SELECT * FROM ursa_functions() lists the graph functions and their parameters.\n",
    "ursa --skill prints a guide to writing Ursa SQL, for coding agents.\n",
    "Docs: https://cldixon.github.io/ursa/sql/",
);

#[derive(Debug, Parser)]
#[command(
    name = "ursa",
    version = env!("URSA_VERSION"),
    about = "SQL for graph analytics over Parquet, CSV, JSON and Arrow files, local or in object storage.",
    after_help = AFTER_HELP
)]
struct Args {
    /// A database file (.ursa), created if it does not exist. It keeps the
    /// views, external tables and graphs you define. Without one, ursa works in
    /// memory.
    #[arg(value_name = "DATABASE")]
    database: Option<String>,

    /// Open the database without changing it: statements that define or drop
    /// anything are refused, and other processes may write it meanwhile.
    #[arg(long = "readonly", requires = "database")]
    readonly: bool,

    /// Run SQL and exit. Separate statements with ';'. May be repeated.
    #[arg(short = 'c', long = "command", value_name = "SQL")]
    command: Vec<String>,

    /// Run the SQL and dot-commands in a file and exit. May be repeated.
    #[arg(short = 'f', long = "file", value_name = "PATH")]
    file: Vec<PathBuf>,

    /// Output format.
    #[arg(long = "format", value_enum, default_value_t = OutputFormat::Table)]
    format: OutputFormat,

    /// Show at most N rows of a table: the first and last N/2, and a footer
    /// that counts them. 0 shows every row. The other formats write every
    /// row. Also .maxrows N.
    #[arg(long = "max-rows", value_name = "N", default_value_t = DEFAULT_MAX_ROWS)]
    max_rows: usize,

    /// Draw tables at most N characters wide, cutting long values short and
    /// leaving out middle columns. 0: the terminal's width, or no limit when
    /// the output is not a terminal. Also .maxwidth N.
    #[arg(long = "max-width", value_name = "N", default_value_t = 0)]
    max_width: usize,

    /// Run the SQL and dot-commands in FILE at startup, before the shell's
    /// prompt or the -c, --file or stdin SQL. Replaces ~/.ursarc. Also
    /// spelled -init, as in DuckDB.
    #[arg(long = "init", value_name = "FILE", conflicts_with = "no_init")]
    init: Option<PathBuf>,

    /// Run no init file, not even ~/.ursarc. Also spelled -no-init.
    #[arg(long = "no-init")]
    no_init: bool,

    /// Keep no shell history. Otherwise it is kept in ~/.ursa_history, or in
    /// the file $URSA_HISTORY names (set it empty to keep none).
    #[arg(long = "no-history")]
    no_history: bool,

    /// Print a guide to writing Ursa SQL, for coding agents (an agent skill,
    /// in Markdown), and exit.
    #[arg(long = "skill")]
    skill: bool,

    /// Print no notices on stderr (slow index builds, indexes not stored or
    /// evicted); errors are still printed. As SET ursa.notices = 'off'.
    #[arg(short = 'q', long = "quiet")]
    quiet: bool,
}

/// Where SQL comes from, in the order given on the command line.
enum Source {
    Command(String),
    File(PathBuf),
    Stdin(String),
}

fn main() -> ExitCode {
    let matches = Args::command().get_matches_from(single_dash_longs(std::env::args_os()));
    let args = match Args::from_arg_matches(&matches) {
        Ok(args) => args,
        Err(e) => e.exit(),
    };
    if args.skill {
        // A reader that stops early (`ursa --skill | head`) is not an error.
        let _ = io::stdout().write_all(SKILL.as_bytes());
        return ExitCode::SUCCESS;
    }

    if let Some(database) = &args.database {
        if !database.ends_with(".ursa") {
            return fail(&format!(
                "'{database}' is not a database file (those end in .ursa); to run SQL, use \
                 -c \"<sql>\" or --file <path>"
            ));
        }
    }

    let mut sources = sources_in_order(&args, &matches);
    let interactive = sources.is_empty() && io::stdin().is_terminal();
    if interactive && args.format == OutputFormat::Arrow {
        return fail("the shell cannot show the arrow format; use --format arrow with -c or -f");
    }
    if sources.is_empty() && !interactive {
        let mut sql = String::new();
        if let Err(e) = io::stdin().read_to_string(&mut sql) {
            return fail(&format!("cannot read SQL from stdin: {e}"));
        }
        if !sql.trim().is_empty() {
            sources.push(Source::Stdin(sql));
        }
    }
    let runtime = match tokio::runtime::Builder::new_multi_thread()
        .enable_all()
        .build()
    {
        Ok(runtime) => runtime,
        Err(e) => return fail(&format!("cannot start: {e}")),
    };
    let given = Given {
        format: matches.value_source("format") == Some(ValueSource::CommandLine),
        max_rows: matches.value_source("max_rows") == Some(ValueSource::CommandLine),
        max_width: matches.value_source("max_width") == Some(ValueSource::CommandLine),
    };
    if !interactive {
        // The shell handles Ctrl-C itself: it cancels the statement.
        cancel_on_interrupt();
    }
    let result = runtime.block_on(run(sources, &args, interactive, given));
    // A query cancelled in the shell may still be running on a blocking
    // thread; it has nothing more to say.
    runtime.shutdown_background();
    match result {
        Ok(true) => ExitCode::SUCCESS,
        // The shell has printed the error.
        Ok(false) => ExitCode::FAILURE,
        // A reader that stops early (`ursa … | head`) is not an error.
        Err(e) if is_broken_pipe(&e) => ExitCode::SUCCESS,
        Err(e) => fail(&ursa_engine::user_message(&e)),
    }
}

/// Which settings were given on the command line, so they win over an init
/// file's `.mode`, `.maxrows` and `.maxwidth`.
#[derive(Debug, Clone, Copy)]
struct Given {
    format: bool,
    max_rows: bool,
    max_width: bool,
}

/// The exit status after SIGINT (128 + 2), as shells report it.
const INTERRUPTED_STATUS: i32 = 130;

/// On SIGINT (Ctrl-C), cancel the `-c`, `-f` or stdin SQL: stop writing
/// results, say `Error: interrupted` and exit with status 130. Exiting ends
/// the query, wherever it is running, and the operating system releases the
/// database's lock. Indexes still being stored are left unstored; their
/// files are written whole or not at all.
///
/// The signal is waited for on a thread of its own, so a query that keeps
/// every worker busy cannot delay it.
fn cancel_on_interrupt() {
    let waiting = std::thread::Builder::new()
        .name("ursa-sigint".into())
        .spawn(|| {
            let Ok(runtime) = tokio::runtime::Builder::new_current_thread()
                .enable_all()
                .build()
            else {
                return;
            };
            if runtime.block_on(tokio::signal::ctrl_c()).is_ok() {
                interrupt::interrupt();
                let mut err = io::stderr().lock();
                let _ = writeln!(err, "Error: interrupted");
                let _ = err.flush();
                std::process::exit(INTERRUPTED_STATUS);
            }
        });
    // Without the thread, SIGINT still ends the process (its default).
    drop(waiting);
}

/// `-c` and `--file` sources in the order they were given.
fn sources_in_order(args: &Args, matches: &ArgMatches) -> Vec<Source> {
    let commands = matches
        .indices_of("command")
        .into_iter()
        .flatten()
        .zip(args.command.iter().cloned().map(Source::Command));
    let files = matches
        .indices_of("file")
        .into_iter()
        .flatten()
        .zip(args.file.iter().cloned().map(Source::File));
    let mut sources: Vec<(usize, Source)> = commands.chain(files).collect();
    sources.sort_by_key(|(index, _)| *index);
    sources.into_iter().map(|(_, source)| source).collect()
}

/// `-f` runs a file, as in DuckDB and psql. A missing file named like an
/// output format (`ursa -f json`) was most likely meant as `--format`.
fn format_hint(path: &std::path::Path) -> String {
    use clap::ValueEnum;
    let name = path.to_string_lossy();
    let is_format = OutputFormat::value_variants().iter().any(|f| {
        f.to_possible_value()
            .is_some_and(|v| v.matches(&name, true))
    });
    if is_format && !path.exists() {
        format!("; to choose the output format, use --format {name}")
    } else {
        String::new()
    }
}

/// DuckDB spells its long options with one dash (`-init`); clap needs two.
/// Rewrite `-init` and `-no-init`, except as the value of an option.
fn single_dash_longs(args: impl IntoIterator<Item = OsString>) -> Vec<OsString> {
    const TAKE_VALUES: [&str; 8] = [
        "-c",
        "--command",
        "--file",
        "-f",
        "--format",
        "--init",
        "--max-rows",
        "--max-width",
    ];
    let mut out: Vec<OsString> = Vec::new();
    for arg in args {
        let is_value = out.len() > 1
            && out
                .last()
                .is_some_and(|previous| TAKE_VALUES.iter().any(|option| previous == option));
        let rewritten = match arg.to_str() {
            _ if is_value || out.is_empty() => arg,
            Some("-init") => "--init".into(),
            Some("-no-init") => "--no-init".into(),
            Some(a) if a.starts_with("-init=") => format!("-{a}").into(),
            _ => arg,
        };
        out.push(rewritten);
    }
    out
}

fn fail(message: &str) -> ExitCode {
    eprintln!("Error: {message}");
    ExitCode::FAILURE
}

/// Run the session. `Ok(false)` when a statement failed (the error is
/// printed).
async fn run(
    sources: Vec<Source>,
    args: &Args,
    interactive: bool,
    given: Given,
) -> Result<bool, DataFusionError> {
    let ctx = ursa_engine::new_session();
    // Before the database opens, so what opening it says is heard.
    if !args.quiet {
        notices::to_stderr(&ctx);
    }
    // Open (or create) the database even with no SQL to run, so `ursa
    // club.ursa` makes the file, as Getting Started says.
    if let Some(path) = &args.database {
        let options = ursa_sql::OpenOptions {
            read_only: args.readonly,
            ..Default::default()
        };
        ursa_sql::open_database(&ctx, path, &options).await?;
    }
    let result = run_session(ctx.clone(), sources, args, interactive, given).await;
    // Finish storing the indexes this process built before it exits.
    ursa_engine::database::flush(&ctx.state());
    if std::env::var_os(INDEX_STATS_VAR).is_some() {
        eprintln!(
            "ursa: graph indexes built {}, loaded {}",
            ursa_engine::graph::index_builds(&ctx),
            ursa_engine::graph::index_loads(&ctx)
        );
    }
    result
}

/// Set to print, on exit, how many graph indexes the process built and how
/// many it loaded from the database (for tests and for checking that a
/// database's stored indexes are used).
const INDEX_STATS_VAR: &str = "URSA_INDEX_STATS";

/// Run the init file, then the shell or the SQL given, on `ctx`.
///
/// Errors in the init file are printed and it goes on, as in the shell.
/// The SQL given (`-c`, `-f`, stdin) is a script: each source is read as if
/// typed, dot-commands included, and the first error stops everything
/// ([`Shell::stop_on_error`]).
async fn run_session(
    ctx: SessionContext,
    sources: Vec<Source>,
    args: &Args,
    interactive: bool,
    given: Given,
) -> Result<bool, DataFusionError> {
    let home = startup::home_dir();
    let init = startup::init_file(
        args.init.as_deref(),
        args.no_init,
        interactive,
        home.as_deref(),
    );
    if !interactive && init.is_none() && sources.is_empty() {
        return Ok(true);
    }
    // The shell flushes after each result and message; a script's output is
    // buffered in between.
    let out: Box<dyn Write> = if interactive {
        Box::new(io::stdout())
    } else {
        // Nothing more reaches stdout once SIGINT has cancelled the run.
        Box::new(io::BufWriter::new(interrupt::Interruptible(io::stdout())))
    };
    let mut shell = Shell::new(ctx, args.format, out, io::stderr());
    shell.set_max_rows(args.max_rows);
    shell.set_max_width(args.max_width);
    shell.set_version(env!("URSA_VERSION"));
    // Colour unless NO_COLOR is set (https://no-color.org) or the terminal
    // says it has none.
    let color = std::env::var_os("NO_COLOR").is_none_or(|v| v.is_empty())
        && std::env::var("TERM").map_or(true, |t| t != "dumb");
    shell.set_error_color(color && io::stderr().is_terminal());
    if io::stdout().is_terminal() {
        // The terminal's width when a table is drawn: it may have been resized.
        let width = || terminal_size::terminal_size().map_or(0, |(w, _)| usize::from(w.0));
        shell.set_screen(Box::new(width), color);
    }
    if interactive {
        println!("{}", banner(args));
    }
    if let Some(init) = init {
        if shell.run_file(&init).await == Flow::Exit {
            return Ok(true);
        }
        // `--format`, `--max-rows` and `--max-width` given on the command
        // line win over a `.mode`, `.maxrows` or `.maxwidth` in the file.
        if given.format {
            shell.set_mode(args.format);
        }
        if given.max_rows {
            shell.set_max_rows(args.max_rows);
        }
        if given.max_width {
            shell.set_max_width(args.max_width);
        }
    }
    if interactive {
        let history = startup::history_file(
            args.no_history,
            std::env::var_os(startup::HISTORY_VAR),
            home.as_deref(),
        );
        repl::run(&mut shell, history, color && io::stdout().is_terminal())
            .await
            .map_err(|e| DataFusionError::External(Box::new(e)))?;
        return Ok(true);
    }

    shell.stop_on_error();
    for source in sources {
        let text = match source {
            Source::Command(sql) | Source::Stdin(sql) => sql,
            Source::File(path) => std::fs::read_to_string(&path).map_err(|e| {
                DataFusionError::Execution(format!(
                    "cannot read '{}': {e}{}",
                    path.display(),
                    format_hint(&path)
                ))
            })?,
        };
        // `.quit` or `.exit` in a script ends it, and the run, without error.
        if shell.run_text(&text).await == Flow::Exit {
            break;
        }
    }
    shell.end_script();
    Ok(!shell.failed())
}

/// The shell's first line: the version, and what the session is.
fn banner(args: &Args) -> String {
    let session = match &args.database {
        Some(path) if args.readonly => format!("database {path} (read-only)"),
        Some(path) => format!("database {path}"),
        None => "in memory".to_string(),
    };
    format!(
        "ursa {}, {session}. Enter .help for commands, .quit to exit.",
        env!("URSA_VERSION")
    )
}

#[cfg(test)]
mod tests {
    use super::*;

    fn rewrite(args: &[&str]) -> Vec<String> {
        single_dash_longs(args.iter().map(OsString::from))
            .into_iter()
            .map(|a| a.into_string().unwrap())
            .collect()
    }

    #[test]
    fn single_dash_init_is_rewritten() {
        assert_eq!(
            rewrite(&["ursa", "-init", "a.sql", "club.ursa"]),
            ["ursa", "--init", "a.sql", "club.ursa"]
        );
        assert_eq!(rewrite(&["ursa", "-no-init"]), ["ursa", "--no-init"]);
        assert_eq!(rewrite(&["ursa", "-init=a.sql"]), ["ursa", "--init=a.sql"]);
        // Not as the value of an option.
        assert_eq!(
            rewrite(&["ursa", "-c", "-init", "--init", "-no-init"]),
            ["ursa", "-c", "-init", "--init", "-no-init"]
        );
        // The program name is left alone.
        assert_eq!(rewrite(&["-init"]), ["-init"]);
    }

    #[test]
    fn flags_parse() {
        let args =
            Args::try_parse_from(rewrite(&["ursa", "-init", "a.sql", "--no-history"])).unwrap();
        assert_eq!(args.init, Some(PathBuf::from("a.sql")));
        assert!(args.no_history && !args.no_init);
        assert!(Args::try_parse_from(["ursa", "--init", "a.sql", "--no-init"]).is_err());
    }

    #[test]
    fn the_banner_names_the_session() {
        let version = env!("URSA_VERSION");
        let args = Args::try_parse_from(["ursa"]).unwrap();
        assert_eq!(
            banner(&args),
            format!("ursa {version}, in memory. Enter .help for commands, .quit to exit.")
        );
        let args = Args::try_parse_from(["ursa", "club.ursa", "--readonly"]).unwrap();
        assert!(
            banner(&args).starts_with(&format!("ursa {version}, database club.ursa (read-only).")),
            "{}",
            banner(&args)
        );
    }
}