ursa-cli 0.7.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
//! `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 input;
mod print;
mod repl;
mod shell;
mod startup;

use print::{is_broken_pipe, OutputFormat};
use 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,

    /// 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,
}

/// 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 format_given = matches.value_source("format") == Some(ValueSource::CommandLine);
    match runtime.block_on(run(sources, &args, interactive, format_given)) {
        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)),
    }
}

/// `-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; 6] = ["-c", "--command", "--file", "-f", "--format", "--init"];
    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,
    format_given: bool,
) -> Result<bool, DataFusionError> {
    // Open (or create) the database even with no SQL to run, so `ursa
    // club.ursa` makes the file, as Getting Started says.
    let ctx = match &args.database {
        Some(path) => {
            let options = ursa_sql::OpenOptions {
                read_only: args.readonly,
                ..Default::default()
            };
            ursa_sql::open_session(path, &options).await?
        }
        None => ursa_engine::new_session(),
    };
    let result = run_session(ctx.clone(), sources, args, interactive, format_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,
    format_given: bool,
) -> 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 {
        Box::new(io::BufWriter::new(io::stdout()))
    };
    let mut shell = Shell::new(ctx, args.format, out, io::stderr());
    if interactive {
        println!("{}", banner(args));
    }
    if let Some(init) = init {
        if shell.run_file(&init).await == Flow::Exit {
            return Ok(true);
        }
        // `-f` given on the command line wins over a `.mode` in the file.
        if format_given {
            shell.set_mode(args.format);
        }
    }
    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)
            .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;
        }
    }
    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)
        );
    }
}