Repository: radiocosmology/alpenhorn

Issue:
Command to show known corrupt files (was: `verify` should show files already known to be corrupt)
The query to get the file list in `client.verify` has a condition "`ar.ArchiveFileCopy.has_file == 'Y')`", so that known corrupt files will not be shown in the output. This is kind of confusing to the user, as there is no insight into corrupt files.

Furthermore, even the files that were only discovered to be corrupt during the `verify`'s run, and so their `has_file` attribute gets changed from 'Y' to 'X', will disappear from the output if `verify` was run again immediately.

Discussion:
rikvl: > `file list --not --corrupt --node=Node`
lists all files which don't have a corrupt copy on node Node (i.e., as the usage says, --not applies to the node selection only).

Wouldn't this example cause a `UsageError` because of the following?
https://github.com/radiocosmology/alpenhorn/blob/38e87f623db174d47fef8fc24106af093f35a9c1/alpenhorn/cli/file/list.py#L172-L173
rikvl: > is --not a too confusing for the flag which negates the node/group constraint?

At first I was going to suggest perhaps using something more verbose, like `--negate-location-contraint`, but now I think it's good the way it is.
rikvl: > Also, the distinction between --not and --not --all might be a little subtle:
   `file list --not --node=Node1 --node=Node2`
lists files that are absent from both Node1 and Node2
   `file list --not --all --node=Node1 --node=Node2`
lists files which are absent from at least one of Node1 or Node2. Does that feel backwards?

It's clearly explained in the documentation. And changing this to the opposite could end up more confusing
ketiltrout: > Wouldn't this example cause a `UsageError` because of the following?

Ah, right, it would.  I think I ended up prohibiting this construct due to it not making a whole lot of sense.
ketiltrout: After sleeping on it, what do you think about this:

Instead of a `-not` flag, just have more options to explicitly look for files not on thing: `--absent-from-node=NODE` and `--absent-from-group=GROUP` with maybe shorter names.

Might both remove ambiguity and make the command more flexible because you can mix absence and presence.  In fact, in with these, the syncability search `--from=NODE --to=GROUP` is essentially just `--node=NODE --absent-from-group=GROUP` (though I don't think I'd get rid of the special-case `--from/--to`)
ketiltrout: NOTE: I've pulled the CLI common-code refactoring out of this PR and made a new one for it over here: https://github.com/radiocosmology/alpenhorn/pull/250 This is now just the command implementation.
rikvl: > After sleeping on it, what do you think about this:
> 
> Instead of a `-not` flag, just have more options to explicitly look for files not on thing: `--absent-from-node=NODE` and `--absent-from-group=GROUP` with maybe shorter names.
> 
> Might both remove ambiguity and make the command more flexible because you can mix absence and presence. In fact, in with these, the syncability search `--from=NODE --to=GROUP` is essentially just `--node=NODE --absent-from-group=GROUP` (though I don't think I'd get rid of the special-case `--from/--to`)

What's currently non-intuitive is that `--not` only applies to location constraints and not to, say, file state constraints. These new options would make that very clear
ketiltrout: Yeah, that's what I'm thinking.  `--not` is a very vague term for a flag.
ketiltrout: OK, I've removed `--not` and in its place added `--absent-node` and `--absent-group` for explicit selection of a negative location constraint.

I think these are the results:
* Improved the flexibility of the command, which is good, since you can now mix positive and negative constraints instead of having to choose one or the other (though there's still only one `--all` for both types)
* I think the code is clearer on how the selections are made and combined, which is also good
* Might be more intuitive for users, but I suspect negative constraints are going to cause confusion however they work.

Given the descriptions of the options, and the realisation that a file is in a group if it is on _at least one_ node in the group, and after some mental Venn diagramming, it's likely a user will figure out why, if StorageGroup `Group` contains `Node` and `Node2`, the positive constraint `--group=Group` is the same as `--node=Node1 --node=Node2`, but you need to add `--all` to convert the negative constraint `--absent-group=Group` into an equivalent node-based one, i.e. `--all --absent-node=Node1 --absent-node=Node2`, but I don't know if I'd call it intuitive.

Is it an improvement?
rikvl: I.e., while in general there are ORs between individual constraints (which can be turned into an AND by `--all`), there's always an implicit AND between the positive constraints and the negative constraints.
ketiltrout: OK, `--all` now also applies to how positive and negative constraints are combined.  This has made the `--from --to` files a bit special, because that is always AND-ed with any other location constraint (ignoring whether `--all` was used).

Patch:
diff --git a/alpenhorn/cli/file/__init__.py b/alpenhorn/cli/file/__init__.py
index cf823d3..de08aea 100644
--- a/alpenhorn/cli/file/__init__.py
+++ b/alpenhorn/cli/file/__init__.py
@@ -6,6 +6,7 @@ import peewee as pw
 from .clean import clean
 from .create import create
 from .import_ import import_
+from .list import list_
 from .modify import modify
 from .show import show
 from .sync import sync
@@ -19,6 +20,7 @@ def cli():
 cli.add_command(clean, "clean")
 cli.add_command(create, "create")
 cli.add_command(import_, "import")
+cli.add_command(list_, "list")
 cli.add_command(modify, "modify")
 cli.add_command(show, "show")
 cli.add_command(sync, "sync")
diff --git a/alpenhorn/cli/file/list.py b/alpenhorn/cli/file/list.py
new file mode 100644
index 0000000..28598d9
--- /dev/null
+++ b/alpenhorn/cli/file/list.py
@@ -0,0 +1,381 @@
+"""alpenhorn file list command."""
+
+import click
+import peewee as pw
+from tabulate import tabulate
+
+from ...common.util import pretty_bytes
+from ...db import (
+    ArchiveAcq,
+    ArchiveFile,
+    ArchiveFileCopy,
+    ArchiveFileCopyRequest,
+    StorageGroup,
+    StorageNode,
+)
+from ..cli import echo, pretty_time
+from ..options import (
+    cli_option,
+    not_both,
+    both_or_neither,
+    files_in_nodes,
+    files_in_groups,
+    resolve_acqs,
+    resolve_group,
+    resolve_node,
+    state_constraint,
+)
+
+
+def _state_flag_help(name):
+    """Returns help for one of the state flags"""
+
+    return "Limit to " + name + "files.  Must be accompanied by at least one "
+    "--node or --group to provide a place to probe file state.",
+
+
+@click.command()
+@click.option(
+    "--absent-group",
+    metavar="GROUP",
+    multiple=True,
+    help="Limit to files absent from GROUP.  May be specified multiple times.",
+)
+@click.option(
+    "--absent-node",
+    metavar="NODE",
+    multiple=True,
+    help="Limit to files absent from NODE.  May be specified multiple times.",
+)
+@cli_option("acq")
+@cli_option(
+    "all_",
+    help="Limit to files that statisfy all --absent-group, "
+    "--absent-node, --node, and --group constraints (instead of just one).",
+)
+@click.option("--corrupt", is_flag=True, help=_state_flag_help("corrupt"))
+@click.option("--details", is_flag=True, help="Show details for listed files.")
+@click.option(
+    "from_",
+    "--from",
+    metavar="SRC",
+    help="Must be specified with --to=DEST: limit to files that can be synced "
+    "from Node SRC to Group DEST",
+)
+@click.option(
+    "--group",
+    metavar="GROUP",
+    multiple=True,
+    help="Limit to files present in GROUP.  May be specified multiple times.",
+)
+@click.option("--healthy", is_flag=True, help=_state_flag_help("healthy"))
+@click.option("--missing", is_flag=True, help=_state_flag_help("missing"))
+@click.option(
+    "--node",
+    metavar="NODE",
+    multiple=True,
+    help="Limit to files present on NODE.  May be specified multiple times.",
+)
+@click.option("--suspect", is_flag=True, help=_state_flag_help("suspect"))
+@click.option(
+    "--to",
+    metavar="DEST",
+    help="Must be specified with --from=SRC: limit to files that can be synced "
+    "from Node SRC to Group DEST",
+)
+@click.pass_context
+def list_(
+    ctx,
+    absent_group,
+    absent_node,
+    acq,
+    all_,
+    corrupt,
+    details,
+    from_,
+    group,
+    healthy,
+    missing,
+    node,
+    suspect,
+    to,
+):
+    """List Files.
+
+    Without options, lists all Files registered to the Data Index.
+    There are several ways to limit the list of files returned:
+
+    \b
+    Limit by acqusition
+    -------------------
+
+    It is always possible to limit the list to only files in acqusition(s)
+    specified in the --acq option.
+
+    \b
+    Limit by location
+    -----------------
+
+    To limit the list based on where a file is, or is not, present, use the
+    --absent-node=NODE, --absent-group=GROUP, --node=NODE and/or --group=GROUP
+    options to list places to search.
+
+    The first two of these options are negative constraints (indicating nodes or
+    groups which must not have the file) and the last two are positive constraints
+    (nodes or groups which must have the file).
+
+    Normally, only one of the location constraints given need be satisfied for a
+    file to be listed, but if you also use "--all", then a file will be listed only
+    if all location constraints are satisfied.
+
+    \b
+    Limit by file state
+    -------------------
+
+    To limit the list based on file state, you can use the state flags:
+    --missing, --corrupt, --suspect, --healthy.  In this case, you must specify
+    at least one location (with --node or --group) to indicate where the file
+    state should be checked.  If multiple state flags are used, files with any
+    one of the specified state will be listed.
+
+    Note: the "missing" state does not mean simply "absent".  The state "missing"
+    is a special state tracked by alpenhorn in the data index for files which are
+    absent from a node/group when they are expected to be present (i.e they haven't
+    been released).  So, these two commands produce (potentially) different results:
+
+    \b
+    group list --absent-node=NODE
+    group list --missing --node=NODE
+
+    The first lists all files absent from NODE.  The second lists only files which are
+    absent but not released from the NODE (i.e. files which have been deleted by a
+    third-party without alpenhorn's knowledge).
+
+    \b
+    Limit based on syncability
+    --------------------------
+
+    Using both "--from=SRC" and "--to=DEST" will limit the list to files which
+    can be synced (copied) from the Node SRC to the Group DEST.  Specifically,
+    this restricts the list to files which are healthy on SRC and absent from
+    DEST.
+
+    You cannot simultaneously limit both by file state and syncability.
+    """
+
+    # Usage checks.
+
+    # Find a state flag to complain about, if any
+    if corrupt:
+        state_flag = "corrupt"
+    elif missing:
+        state_flag = "missing"
+    elif healthy:
+        state_flag = "healthy"
+    elif suspect:
+        state_flag = "suspect"
+    else:
+        state_flag = None
+
+    # Must use --to and --from together
+    both_or_neither(from_, "from", to, "to")
+    # Can't use a state with --from or --to
+    # (checking --from is enough, given the previous test)
+    not_both(from_, "from", state_flag, state_flag)
+
+    # Can't use --all if we haven't used a location constraint
+    if all_:
+        if not node and not group and not absent_group and not absent_node:
+            raise click.UsageError(
+                "--all cannot be used without a location constraint "
+                "(one or more of --absent-group, --absent-node, --group, --node)"
+            )
+
+    # Figure out the state constraint from the flags
+    state_expr = state_constraint(
+        corrupt=corrupt, healthy=healthy, missing=missing, suspect=suspect
+    )
+
+    # Resolve location constraints.
+    #
+    # The "in_any" flag is "all_" for the negative constraints (This comes
+    # from DeMorgan's rule), and "not all_", as expected, for the positive ones.
+    if absent_node:
+        absent_nodes = resolve_node(absent_node)
+        absent_node_files = files_in_nodes(absent_nodes, None, in_any=all_)
+    if absent_group:
+        absent_groups = resolve_group(absent_group)
+        absent_group_files = files_in_groups(absent_groups, None, in_any=all_)
+    if node:
+        nodes = resolve_node(node)
+        node_files = files_in_nodes(nodes, state_expr, in_any=not all_)
+    if group:
+        groups = resolve_group(group)
+        group_files = files_in_groups(groups, state_expr, in_any=not all_)
+
+    # In --details mode, extra node or group details are provided in very
+    # restricted circumstances:
+    #  1. exactly one --group or exactly one --node was specified
+    #  2. not restricted by syncability (i.e. no --from or --to)
+    detail_node = None
+    detail_group = None
+    if details and not from_:
+        if node and not group and len(nodes) == 1:
+            detail_node = nodes[0]
+        elif group and not node and len(groups) == 1:
+            detail_group = groups[0]
+
+    # The negative selection list
+    if absent_node and absent_group:
+        # Negative selection: all_ is a union and not all_ an intersect (DeMorgan again)
+        if all_:
+            omitted_files = absent_node_files | absent_group_files
+        else:
+            omitted_files = absent_node_files & absent_group_files
+    elif absent_node:
+        omitted_files = absent_node_files
+    elif absent_group:
+        omitted_files = absent_group_files
+    else:
+        omitted_files = None
+
+    # If the negative selection is empty, we can just drop it
+    if omitted_files is not None and len(omitted_files) == 0:
+        omitted_files = None
+
+    # The positive selection list
+    if node and group:
+        # Positive selection: all_ is an intersection and not all_ a union
+        if all_:
+            selected_files = node_files & group_files
+        else:
+            selected_files = node_files | group_files
+    elif node:
+        selected_files = node_files
+    elif group:
+        selected_files = group_files
+    else:
+        selected_files = None
+
+    # Apply syncability limit.  This is just a special kind of
+    # location selection where we find files in --from but not in --to.
+    if from_:
+        syncable_files = files_in_nodes([resolve_node(from_)]) - files_in_groups(
+            [resolve_group(to)]
+        )
+
+        # If we're in --all mode or we don't have a negative constraint, we can
+        # combine with positive constraint now.
+        #
+        # In other cases we have to keep syncable_files separate from selected_files
+        # because they combine differently with omitted_files
+        if all_ or omitted_files is None:
+            if selected_files is not None:
+                selected_files &= syncable_files
+            else:
+                selected_files = syncable_files
+            syncable_files = None
+    else:
+        syncable_files = None
+
+    # In --all mode, if we have both a positive and negative selection,
+    # we can combine them now by differencing to simplify things
+    if all_ and omitted_files is not None and selected_files is not None:
+        selected_files -= omitted_files
+        omitted_files = None
+
+    # Handle no matching files: if we have an empty positive selection
+    # (either one) there's nothing to list
+    if selected_files is not None and len(selected_files) == 0:
+        ctx.exit()
+    if syncable_files is not None and len(syncable_files) == 0:
+        ctx.exit()
+
+    # The base query
+    query = (
+        ArchiveFile.select()
+        .join(ArchiveAcq)
+        .order_by(ArchiveAcq.name, ArchiveFile.name)
+    )
+
+    # Apply the --acq limit, if given
+    if acq:
+        query = query.where(ArchiveFile.acq << resolve_acqs(acq))
+
+    # Apply syncability, if present
+    if syncable_files is not None:
+        query = query.where(ArchiveFile.id << syncable_files)
+
+    # Apply file selection, if any
+    if selected_files is not None and omitted_files is not None:
+        # This is a non-all positive and negative slection.  We
+        # need to "or" them together here
+        query = query.where(
+            (ArchiveFile.id << selected_files) | ArchiveFile.id.not_in(omitted_files)
+        )
+    elif selected_files is not None:
+        query = query.where(ArchiveFile.id << selected_files)
+    elif omitted_files is not None:
+        query = query.where(ArchiveFile.id.not_in(omitted_files))
+
+    if details:
+        # Headers
+        headers = ["File", "Size", "MD5 Hash", "Registration Time"]
+
+        # Data
+        file_data = {}
+        for file in query.execute():
+            file_data[file] = (
+                file.acq.name + "/" + file.name,
+                pretty_bytes(file.size_b),
+                "-" if file.md5sum is None else file.md5sum.lower(),
+                pretty_time(file.registered),
+            )
+
+        # Add extra details, if possible
+        if detail_group:
+            headers += ["State", "Node"]
+            data = []
+            for file in file_data:
+                state_name = {
+                    "Y": "Present",
+                    "M": "Suspect",
+                    "X": "Corrupt",
+                    "N": "Absent",
+                }
+                state, node = detail_group.state_on_node(file)
+                data.append(
+                    (
+                        *file_data[file],
+                        state_name[state],
+                        "-" if node is None else node.name,
+                    )
+                )
+        elif detail_node:
+            headers += ["State", "Size on Node"]
+
+            # Get all the ArchiveFileCopy data in a single query
+            copies = {
+                copy.file: copy
+                for copy in ArchiveFileCopy.select()
+                .where(ArchiveFileCopy.file << [file.id for file in file_data])
+                .execute()
+            }
+
+            data = []
+            for file in file_data:
+                copy = copies[file]
+                data.append(
+                    (
+                        *file_data[file],
+                        copies[file].state,
+                        pretty_bytes(copies[file].size_b),
+                    )
+                )
+        else:
+            data = file_data.values()
+
+        if data:
+            echo(tabulate(data, headers=headers))
+    else:
+        echo("\n".join(file.acq.name + "/" + file.name for file in query.execute()))


Test patch:
diff --git a/tests/cli/file/test_list.py b/tests/cli/file/test_list.py
new file mode 100644
index 0000000..eb08995
--- /dev/null
+++ b/tests/cli/file/test_list.py
@@ -0,0 +1,771 @@
+"""Test CLI: alpenhorn file show"""
+
+from datetime import datetime
+
+from alpenhorn.db import (
+    StorageGroup,
+    StorageNode,
+    ArchiveAcq,
+    ArchiveFile,
+    ArchiveFileCopy,
+    ArchiveFileCopyRequest,
+)
+
+
+def test_only_from_to(clidb, cli):
+    """Can't only use --from or --to"""
+
+    group = StorageGroup.create(name="Group")
+    StorageNode.create(name="Node", group=group)
+
+    cli(2, ["file", "list", "--from=Node"])
+    cli(2, ["file", "list", "--to=Group"])
+
+
+def test_state_to_from(clidb, cli):
+    """state flags can't be used with --to or --from."""
+
+    group = StorageGroup.create(name="Group")
+    StorageNode.create(name="Node", group=group)
+
+    for state_flag in ["--corrupt", "--missing", "--healthy", "--suspect"]:
+        cli(2, ["file", "list", state_flag, "--to=Group", "--from=Node"])
+
+
+def test_bare_all(clidb, cli):
+    """--all must accompany a location constraint"""
+
+    group = StorageGroup.create(name="Group")
+    StorageNode.create(name="Node", group=group)
+
+    cli(2, ["file", "list", "--all"])
+
+    # But any of these are fine
+
+    cli(0, ["file", "list", "--all", "--absent-node=Node"])
+    cli(0, ["file", "list", "--all", "--absent-group=Group"])
+    cli(0, ["file", "list", "--all", "--node=Node"])
+    cli(0, ["file", "list", "--all", "--group=Group"])
+
+
+def test_bad_acq(clidb, cli):
+    """Test a non-existent --acq"""
+
+    cli(1, ["file", "list", "--acq=MISSING"])
+
+
+def test_bad_node(clidb, cli):
+    """Test a non-existent --node"""
+
+    cli(1, ["file", "list", "--node=MISSING"])
+
+
+def test_bad_group(clidb, cli):
+    """Test a non-existent --group"""
+
+    cli(1, ["file", "list", "--group=MISSING"])
+
+
+def test_bad_from(clidb, cli):
+    """Test a non-existent --from target"""
+
+    StorageGroup.create(name="Group")
+
+    cli(1, ["file", "list", "--to=Group", "--from=MISSING"])
+
+
+def test_bad_to(clidb, cli):
+    """Test a non-existent --to target"""
+
+    group = StorageGroup.create(name="Group")
+    StorageNode.create(name="Node", group=group)
+
+    cli(1, ["file", "list", "--to=MISSING", "--from=Node"])
+
+
+def test_list(clidb, cli):
+    """Test listing everything."""
+
+    acq = ArchiveAcq.create(name="acq1")
+    ArchiveFile.create(name="file1", acq=acq)
+    ArchiveFile.create(name="file2", acq=acq)
+    acq = ArchiveAcq.create(name="acq2")
+    ArchiveFile.create(name="file3", acq=acq)
+    ArchiveFile.create(name="file4", acq=acq)
+
+    result = cli(0, ["file", "list"])
+
+    assert "acq1/file1" in result.output
+    assert "acq1/file2" in result.output
+    assert "acq2/file3" in result.output
+    assert "acq2/file4" in result.output
+
+
+def test_list_acq(clidb, cli):
+    """Test limiting by --acq."""
+
+    acq = ArchiveAcq.create(name="acq1")
+    ArchiveFile.create(name="file1", acq=acq)
+    ArchiveFile.create(name="file2", acq=acq)
+    acq = ArchiveAcq.create(name="acq2")
+    ArchiveFile.create(name="file3", acq=acq)
+    ArchiveFile.create(name="file4", acq=acq)
+
+    result = cli(0, ["file", "list", "--acq=acq1"])
+
+    assert "acq1/file1" in result.output
+    assert "acq1/file2" in result.output
+    assert "acq2" not in result.output
+
+
+def test_list_details(clidb, cli, assert_row_present):
+    """Test listing details without node/group."""
+
+    acq = ArchiveAcq.create(name="acq1")
+    ArchiveFile.create(
+        name="file1",
+        acq=acq,
+        size_b=3456,
+        md5sum="d41d8cd98f00b204e9800998ecf8427e",
+        registered=datetime(2001, 1, 1, 1, 1, 1, 1),
+    )
+    ArchiveFile.create(
+        name="file2",
+        acq=acq,
+        size_b=4567,
+        md5sum="68b329da9893e34099c7d8ad5cb9c940",
+        registered=datetime(2002, 3, 2, 2, 2, 2, 2),
+    )
+    acq = ArchiveAcq.create(name="acq2")
+    ArchiveFile.create(
+        name="file3",
+        acq=acq,
+        size_b=5678,
+        md5sum="7309AF63395717D5B9F8AA6619301937",
+        registered=datetime(2003, 3, 3, 3, 3, 3, 3),
+    )
+    ArchiveFile.create(name="file4", acq=acq, registered=0)
+
+    result = cli(0, ["file", "list", "--details"])
+
+    assert_row_present(
+        result.output,
+        "acq1/file1",
+        "3.375 kiB",
+        "d41d8cd98f00b204e9800998ecf8427e",
+        "Mon Jan  1 01:01:01 2001 UTC",
+    )
+    assert_row_present(
+        result.output,
+        "acq1/file2",
+        "4.460 kiB",
+        "68b329da9893e34099c7d8ad5cb9c940",
+        "Sat Mar  2 02:02:02 2002 UTC",
+    )
+    assert_row_present(
+        result.output,
+        "acq2/file3",
+        "5.545 kiB",
+        "7309af63395717d5b9f8aa6619301937",
+        "Mon Mar  3 03:03:03 2003 UTC",
+    )
+    assert_row_present(result.output, "acq2/file4", "-", "-", "-")
+
+
+def test_list_node(clidb, cli):
+    """Test --node."""
+
+    group = StorageGroup.create(name="Group")
+    acq = ArchiveAcq.create(name="acq")
+
+    node1 = StorageNode.create(name="Node1", group=group)
+    node2 = StorageNode.create(name="Node2", group=group)
+    node3 = StorageNode.create(name="Node3", group=group)
+
+    file = ArchiveFile.create(name="file1", acq=acq)
+    ArchiveFileCopy.create(file=file, node=node1, has_file="Y", wants_file="Y")
+    ArchiveFileCopy.create(file=file, node=node2, has_file="Y", wants_file="Y")
+
+    file = ArchiveFile.create(name="file2", acq=acq)
+    ArchiveFileCopy.create(file=file, node=node1, has_file="Y", wants_file="Y")
+    ArchiveFileCopy.create(file=file, node=node2, has_file="N", wants_file="N")
+
+    file = ArchiveFile.create(name="file3", acq=acq)
+    ArchiveFileCopy.create(file=file, node=node2, has_file="Y", wants_file="Y")
+    ArchiveFileCopy.create(file=file, node=node3, has_file="Y", wants_file="Y")
+
+    file = ArchiveFile.create(name="file4", acq=acq)
+    ArchiveFileCopy.create(file=file, node=node1, has_file="X", wants_file="Y")
+    ArchiveFileCopy.create(file=file, node=node3, has_file="Y", wants_file="Y")
+
+    result = cli(0, ["file", "list", "--node=Node1", "--node=Node2"])
+
+    assert "acq/file1" in result.output
+    assert "acq/file2" in result.output
+    assert "acq/file3" in result.output
+    assert "acq/file4" not in result.output
+
+
+def test_list_group(clidb, cli):
+    """Test --group."""
+
+    acq = ArchiveAcq.create(name="acq")
+
+    group1 = StorageGroup.create(name="Group1")
+    node1 = StorageNode.create(name="Node1", group=group1)
+
+    group2 = StorageGroup.create(name="Group2")
+    node2 = StorageNode.create(name="Node2", group=group2)
+
+    group3 = StorageGroup.create(name="Group3")
+    node3 = StorageNode.create(name="Node3", group=group3)
+
+    file = ArchiveFile.create(name="file1", acq=acq)
+    ArchiveFileCopy.create(file=file, node=node1, has_file="Y", wants_file="Y")
+    ArchiveFileCopy.create(file=file, node=node2, has_file="Y", wants_file="Y")
+
+    file = ArchiveFile.create(name="file2", acq=acq)
+    ArchiveFileCopy.create(file=file, node=node1, has_file="Y", wants_file="Y")
+    ArchiveFileCopy.create(file=file, node=node2, has_file="N", wants_file="N")
+
+    file = ArchiveFile.create(name="file3", acq=acq)
+    ArchiveFileCopy.create(file=file, node=node2, has_file="Y", wants_file="Y")
+    ArchiveFileCopy.create(file=file, node=node3, has_file="Y", wants_file="Y")
+
+    file = ArchiveFile.create(name="file4", acq=acq)
+    ArchiveFileCopy.create(file=file, node=node1, has_file="X", wants_file="Y")
+    ArchiveFileCopy.create(file=file, node=node3, has_file="Y", wants_file="Y")
+
+    result = cli(0, ["file", "list", "--group=Group1", "--group=Group2"])
+
+    assert "acq/file1" in result.output
+    assert "acq/file2" in result.output
+    assert "acq/file3" in result.output
+    assert "acq/file4" not in result.output
+
+
+def test_list_node_group(clidb, cli):
+    """Test --node and --group together."""
+
+    acq = ArchiveAcq.create(name="acq")
+
+    group1 = StorageGroup.create(name="Group1")
+    node1 = StorageNode.create(name="Node1", group=group1)
+
+    group2 = StorageGroup.create(name="Group2")
+    node2 = StorageNode.create(name="Node2", group=group2)
+
+    group3 = StorageGroup.create(name="Group3")
+    node3 = StorageNode.create(name="Node3", group=group3)
+
+    file = ArchiveFile.create(name="file1", acq=acq)
+    ArchiveFileCopy.create(file=file, node=node1, has_file="Y", wants_file="Y")
+    ArchiveFileCopy.create(file=file, node=node2, has_file="Y", wants_file="Y")
+
+    file = ArchiveFile.create(name="file2", acq=acq)
+    ArchiveFileCopy.create(file=file, node=node1, has_file="Y", wants_file="Y")
+    ArchiveFileCopy.create(file=file, node=node2, has_file="N", wants_file="N")
+
+    file = ArchiveFile.create(name="file3", acq=acq)
+    ArchiveFileCopy.create(file=file, node=node2, has_file="Y", wants_file="Y")
+    ArchiveFileCopy.create(file=file, node=node3, has_file="Y", wants_file="Y")
+
+    file = ArchiveFile.create(name="file4", acq=acq)
+    ArchiveFileCopy.create(file=file, node=node1, has_file="X", wants_file="Y")
+    ArchiveFileCopy.create(file=file, node=node3, has_file="Y", wants_file="Y")
+
+    result = cli(0, ["file", "list", "--group=Group1", "--node=Node1", "--node=Node2"])
+
+    assert "acq/file1" in result.output
+    assert "acq/file2" in result.output
+    assert "acq/file3" in result.output
+    assert "acq/file4" not in result.output
+
+    # i.e. no double listing
+    assert result.output.count("acq") == 3
+
+
+def test_list_all(clidb, cli):
+    """Test --all."""
+
+    group = StorageGroup.create(name="Group")
+    acq = ArchiveAcq.create(name="acq")
+
+    node1 = StorageNode.create(name="Node1", group=group)
+    node2 = StorageNode.create(name="Node2", group=group)
+    node3 = StorageNode.create(name="Node3", group=group)
+
+    file = ArchiveFile.create(name="file1", acq=acq)
+    ArchiveFileCopy.create(file=file, node=node1, has_file="Y", wants_file="Y")
+    ArchiveFileCopy.create(file=file, node=node2, has_file="Y", wants_file="Y")
+
+    file = ArchiveFile.create(name="file2", acq=acq)
+    ArchiveFileCopy.create(file=file, node=node1, has_file="Y", wants_file="Y")
+    ArchiveFileCopy.create(file=file, node=node2, has_file="N", wants_file="N")
+
+    file = ArchiveFile.create(name="file3", acq=acq)
+    ArchiveFileCopy.create(file=file, node=node2, has_file="Y", wants_file="Y")
+    ArchiveFileCopy.create(file=file, node=node3, has_file="Y", wants_file="Y")
+
+    file = ArchiveFile.create(name="file4", acq=acq)
+    ArchiveFileCopy.create(file=file, node=node1, has_file="X", wants_file="Y")
+    ArchiveFileCopy.create(file=file, node=node3, has_file="Y", wants_file="Y")
+
+    result = cli(0, ["file", "list", "--node=Node1", "--node=Node2", "--all"])
+
+    assert "acq/file1" in result.output
+    assert "acq/file2" not in result.output
+    assert "acq/file3" not in result.output
+    assert "acq/file4" not in result.output
+
+
+def test_state(clidb, cli):
+    """Test the state flags."""
+
+    acq = ArchiveAcq.create(name="acq")
+    group = StorageGroup.create(name="Group")
+    node = StorageNode.create(name="Node", group=group)
+
+    for has in ["Y", "M", "X", "N"]:
+        for wants in ["Y", "M", "N"]:
+            file = ArchiveFile.create(name="File" + has + wants, acq=acq)
+            ArchiveFileCopy.create(file=file, node=node, has_file=has, wants_file=wants)
+
+    # The files we want to find for each state
+    state_files = {
+        "corrupt": ["FileXY", "FileXM"],
+        "healthy": ["FileYY", "FileYM"],
+        "suspect": ["FileMY", "FileMM"],
+        "missing": ["FileNY"],
+    }
+
+    # Run through all the state combinations.  There are 2**4 of these:
+    for num in range(16):
+        corrupt = num & 1 == 0
+        healthy = num & 2 == 0
+        suspect = num & 4 == 0
+        missing = num & 8 == 0
+
+        # Build command and resultant file list
+        command = ["file", "list", "--node=Node"]
+        files = []
+        if corrupt:
+            command.append("--corrupt")
+            files += state_files["corrupt"]
+        if suspect:
+            command.append("--suspect")
+            files += state_files["suspect"]
+        if missing:
+            command.append("--missing")
+            files += state_files["missing"]
+        if healthy:
+            command.append("--healthy")
+            files += state_files["healthy"]
+        # The default when no state is chosen
+        if not files:
+            files = state_files["healthy"]
+
+        # Execute
+        result = cli(0, command)
+
+        # Check files
+        for file in files:
+            assert "acq/" + file in result.output
+        # Count files listed to make sure there aren't extras
+        assert result.output.count("acq") == len(files)
+
+
+def test_sync(clidb, cli):
+    """Test --from --to."""
+
+    group1 = StorageGroup.create(name="Group1")
+    node1 = StorageNode.create(name="Node1", group=group1)
+
+    group2 = StorageGroup.create(name="Group2")
+    node2 = StorageNode.create(name="Node2", group=group2)
+
+    acq = ArchiveAcq.create(name="acq")
+    file = ArchiveFile.create(name="file1", acq=acq)
+    ArchiveFileCopy.create(file=file, node=node1, has_file="Y", wants_file="Y")
+
+    file = ArchiveFile.create(name="file2", acq=acq)
+    ArchiveFileCopy.create(file=file, node=node1, has_file="Y", wants_file="Y")
+    ArchiveFileCopy.create(file=file, node=node2, has_file="Y", wants_file="Y")
+
+    file = ArchiveFile.create(name="file3", acq=acq)
+    ArchiveFileCopy.create(file=file, node=node2, has_file="Y", wants_file="Y")
+
+    file = ArchiveFile.create(name="file4", acq=acq)
+    ArchiveFileCopy.create(file=file, node=node1, has_file="Y", wants_file="M")
+    ArchiveFileCopy.create(file=file, node=node2, has_file="N", wants_file="Y")
+
+    result = cli(0, ["file", "list", "--from=Node1", "--to=Group2"])
+
+    assert "file1" in result.output
+    assert "file2" not in result.output
+    assert "file3" not in result.output
+    assert "file4" in result.output
+
+
+def test_sync_select(clidb, cli):
+    """Test --from --to with selection"""
+
+    group1 = StorageGroup.create(name="Group1")
+    node1 = StorageNode.create(name="Node1", group=group1)
+
+    group2 = StorageGroup.create(name="Group2")
+    node2 = StorageNode.create(name="Node2", group=group2)
+
+    group3 = StorageGroup.create(name="Group3")
+    node3 = StorageNode.create(name="Node3", group=group3)
+
+    acq = ArchiveAcq.create(name="acq")
+    file = ArchiveFile.create(name="file1", acq=acq)
+    ArchiveFileCopy.create(file=file, node=node1, has_file="Y", wants_file="Y")
+
+    file = ArchiveFile.create(name="file2", acq=acq)
+    ArchiveFileCopy.create(file=file, node=node1, has_file="Y", wants_file="Y")
+    ArchiveFileCopy.create(file=file, node=node2, has_file="Y", wants_file="Y")
+
+    file = ArchiveFile.create(name="file3", acq=acq)
+    ArchiveFileCopy.create(file=file, node=node2, has_file="Y", wants_file="Y")
+
+    file = ArchiveFile.create(name="file4", acq=acq)
+    ArchiveFileCopy.create(file=file, node=node1, has_file="Y", wants_file="M")
+    ArchiveFileCopy.create(file=file, node=node2, has_file="N", wants_file="Y")
+    ArchiveFileCopy.create(file=file, node=node3, has_file="Y", wants_file="Y")
+
+    result = cli(0, ["file", "list", "--from=Node1", "--to=Group2", "--node=Node3"])
+
+    assert "file1" not in result.output
+    assert "file2" not in result.output
+    assert "file3" not in result.output
+    assert "file4" in result.output
+
+    result = cli(
+        0, ["file", "list", "--from=Node1", "--to=Group2", "--absent-node=Node3"]
+    )
+
+    assert "file1" in result.output
+    assert "file2" not in result.output
+    assert "file3" not in result.output
+    assert "file4" not in result.output
+
+
+def test_detail_node(clidb, cli, assert_row_present):
+    """Test --details with extra details"""
+
+    group = StorageGroup.create(name="group")
+    node = StorageNode.create(name="node", group=group)
+
+    acq = ArchiveAcq.create(name="acq")
+
+    file = ArchiveFile.create(
+        name="file1",
+        acq=acq,
+        size_b=1234,
+        md5sum="d41d8cd98f00b204e9800998ecf8427e",
+        registered=0,
+    )
+    ArchiveFileCopy.create(
+        file=file, node=node, size_b=2345, has_file="Y", wants_file="M"
+    )
+
+    file = ArchiveFile.create(
+        name="file2",
+        acq=acq,
+        size_b=3456,
+        md5sum="68b329da9893e34099c7d8ad5cb9c940",
+        registered=0,
+    )
+    ArchiveFileCopy.create(
+        file=file, node=node, size_b=4567, has_file="M", wants_file="Y"
+    )
+
+    file = ArchiveFile.create(
+        name="file3",
+        acq=acq,
+        size_b=5678,
+        md5sum="7309AF63395717D5B9F8AA6619301937",
+        registered=0,
+    )
+    ArchiveFileCopy.create(
+        file=file, node=node, size_b=6789, has_file="N", wants_file="Y"
+    )
+
+    result = cli(
+        0,
+        [
+            "file",
+            "list",
+            "--node=node",
+            "--healthy",
+            "--missing",
+            "--suspect",
+            "--details",
+        ],
+    )
+
+    assert_row_present(
+        result.output,
+        "acq/file1",
+        "1.205 kiB",
+        "d41d8cd98f00b204e9800998ecf8427e",
+        "-",
+        "Removable",
+        "2.290 kiB",
+    )
+    assert_row_present(
+        result.output,
+        "acq/file2",
+        "3.375 kiB",
+        "68b329da9893e34099c7d8ad5cb9c940",
+        "-",
+        "Suspect",
+        "4.460 kiB",
+    )
+    assert_row_present(
+        result.output,
+        "acq/file3",
+        "5.545 kiB",
+        "7309af63395717d5b9f8aa6619301937",
+        "-",
+        "Missing",
+        "6.630 kiB",
+    )
+
+    result = cli(
+        0,
+        [
+            "file",
+            "list",
+            "--group=group",
+            "--healthy",
+            "--missing",
+            "--suspect",
+            "--details",
+        ],
+    )
+
+    assert_row_present(
+        result.output,
+        "acq/file1",
+        "1.205 kiB",
+        "d41d8cd98f00b204e9800998ecf8427e",
+        "-",
+        "Present",
+        "node",
+    )
+    assert_row_present(
+        result.output,
+        "acq/file2",
+        "3.375 kiB",
+        "68b329da9893e34099c7d8ad5cb9c940",
+        "-",
+        "Suspect",
+        "node",
+    )
+    assert_row_present(
+        result.output,
+        "acq/file3",
+        "5.545 kiB",
+        "7309af63395717d5b9f8aa6619301937",
+        "-",
+        "Absent",
+        "-",
+    )
+
+
+def test_absent(clidb, cli):
+    """Test --absent-node and --absent-group."""
+
+    group1 = StorageGroup.create(name="Group1")
+    node1 = StorageNode.create(name="Node1", group=group1)
+    node2 = StorageNode.create(name="Node2", group=group1)
+    group2 = StorageGroup.create(name="Group2")
+    node3 = StorageNode.create(name="Node3", group=group2)
+
+    acq = ArchiveAcq.create(name="acq")
+    file = ArchiveFile.create(name="file12", acq=acq)
+    ArchiveFileCopy.create(file=file, node=node1, has_file="Y", wants_file="Y")
+    ArchiveFileCopy.create(file=file, node=node2, has_file="Y", wants_file="Y")
+
+    file = ArchiveFile.create(name="file13", acq=acq)
+    ArchiveFileCopy.create(file=file, node=node1, has_file="Y", wants_file="Y")
+    ArchiveFileCopy.create(file=file, node=node3, has_file="Y", wants_file="Y")
+
+    file = ArchiveFile.create(name="file23", acq=acq)
+    ArchiveFileCopy.create(file=file, node=node2, has_file="Y", wants_file="Y")
+    ArchiveFileCopy.create(file=file, node=node3, has_file="Y", wants_file="Y")
+
+    file = ArchiveFile.create(name="file1", acq=acq)
+    ArchiveFileCopy.create(file=file, node=node1, has_file="Y", wants_file="Y")
+
+    file = ArchiveFile.create(name="file2", acq=acq)
+    ArchiveFileCopy.create(file=file, node=node2, has_file="Y", wants_file="Y")
+
+    file = ArchiveFile.create(name="file3", acq=acq)
+    ArchiveFileCopy.create(file=file, node=node3, has_file="Y", wants_file="Y")
+
+    result = cli(0, ["file", "list", "--absent-node=Node1"])
+
+    assert "acq/file2" in result.output
+    assert "acq/file23" in result.output
+    assert "acq/file3" in result.output
+    assert result.output.count("acq") == 3
+
+    result = cli(0, ["file", "list", "--absent-node=Node1", "--absent-node=Node2"])
+
+    assert "acq/file13" in result.output
+    assert "acq/file23" in result.output
+    assert "acq/file1" in result.output
+    assert "acq/file2" in result.output
+    assert "acq/file3" in result.output
+    assert result.output.count("acq") == 5
+
+    result = cli(0, ["file", "list", "--absent-group=Group1"])
+
+    assert "acq/file3" in result.output
+    assert result.output.count("acq") == 1
+
+    result = cli(0, ["file", "list", "--absent-node=Node1", "--absent-group=Group2"])
+
+    assert "acq/file12" in result.output
+    assert "acq/file23" in result.output
+    assert "acq/file1" in result.output
+    assert "acq/file2" in result.output
+    assert "acq/file3" in result.output
+    assert result.output.count("acq") == 5
+
+
+def test_positive_negative(clidb, cli):
+    """Test --node and --absent-node together."""
+
+    group1 = StorageGroup.create(name="Group1")
+    node1 = StorageNode.create(name="Node1", group=group1)
+    node2 = StorageNode.create(name="Node2", group=group1)
+    group2 = StorageGroup.create(name="Group2")
+    node3 = StorageNode.create(name="Node3", group=group2)
+
+    acq = ArchiveAcq.create(name="acq")
+    file = ArchiveFile.create(name="file12", acq=acq)
+    ArchiveFileCopy.create(file=file, node=node1, has_file="Y", wants_file="Y")
+    ArchiveFileCopy.create(file=file, node=node2, has_file="Y", wants_file="Y")
+
+    file = ArchiveFile.create(name="file13", acq=acq)
+    ArchiveFileCopy.create(file=file, node=node1, has_file="Y", wants_file="Y")
+    ArchiveFileCopy.create(file=file, node=node3, has_file="Y", wants_file="Y")
+
+    file = ArchiveFile.create(name="file23", acq=acq)
+    ArchiveFileCopy.create(file=file, node=node2, has_file="Y", wants_file="Y")
+    ArchiveFileCopy.create(file=file, node=node3, has_file="Y", wants_file="Y")
+
+    file = ArchiveFile.create(name="file1", acq=acq)
+    ArchiveFileCopy.create(file=file, node=node1, has_file="Y", wants_file="Y")
+
+    file = ArchiveFile.create(name="file2", acq=acq)
+    ArchiveFileCopy.create(file=file, node=node2, has_file="Y", wants_file="Y")
+
+    file = ArchiveFile.create(name="file3", acq=acq)
+    ArchiveFileCopy.create(file=file, node=node3, has_file="Y", wants_file="Y")
+
+    result = cli(0, ["file", "list", "--node=Node1", "--absent-node=Node2"])
+
+    assert "acq/file1" in result.output
+    assert "acq/file12" in result.output
+    assert "acq/file13" in result.output
+    assert "acq/file3" in result.output
+    assert result.output.count("acq") == 4
+
+    result = cli(0, ["file", "list", "--node=Node1", "--absent-node=Node2", "--all"])
+
+    assert "acq/file1" in result.output
+    assert "acq/file13" in result.output
+    assert result.output.count("acq") == 2
+
+
+def test_all_absent(clidb, cli):
+    """Test --absent-node and --absent-group with --all."""
+
+    group1 = StorageGroup.create(name="Group1")
+    node1 = StorageNode.create(name="Node1", group=group1)
+    node2 = StorageNode.create(name="Node2", group=group1)
+    group2 = StorageGroup.create(name="Group2")
+    node3 = StorageNode.create(name="Node3", group=group2)
+
+    acq = ArchiveAcq.create(name="acq")
+    file = ArchiveFile.create(name="file12", acq=acq)
+    ArchiveFileCopy.create(file=file, node=node1, has_file="Y", wants_file="Y")
+    ArchiveFileCopy.create(file=file, node=node2, has_file="Y", wants_file="Y")
+
+    file = ArchiveFile.create(name="file13", acq=acq)
+    ArchiveFileCopy.create(file=file, node=node1, has_file="Y", wants_file="Y")
+    ArchiveFileCopy.create(file=file, node=node3, has_file="Y", wants_file="Y")
+
+    file = ArchiveFile.create(name="file23", acq=acq)
+    ArchiveFileCopy.create(file=file, node=node2, has_file="Y", wants_file="Y")
+    ArchiveFileCopy.create(file=file, node=node3, has_file="Y", wants_file="Y")
+
+    file = ArchiveFile.create(name="file1", acq=acq)
+    ArchiveFileCopy.create(file=file, node=node1, has_file="Y", wants_file="Y")
+
+    file = ArchiveFile.create(name="file2", acq=acq)
+    ArchiveFileCopy.create(file=file, node=node2, has_file="Y", wants_file="Y")
+
+    file = ArchiveFile.create(name="file3", acq=acq)
+    ArchiveFileCopy.create(file=file, node=node3, has_file="Y", wants_file="Y")
+
+    result = cli(
+        0, ["file", "list", "--absent-node=Node1", "--absent-node=Node2", "--all"]
+    )
+
+    assert "acq/file3" in result.output
+    assert result.output.count("acq") == 1
+
+    result = cli(
+        0, ["file", "list", "--absent-node=Node1", "--absent-group=Group2", "--all"]
+    )
+
+    assert "acq/file2" in result.output
+    assert result.output.count("acq") == 1
+
+
+def test_state_absent(clidb, cli):
+    """State flags don't apply to negative location constraints"""
+
+    group = StorageGroup.create(name="Group")
+    node1 = StorageNode.create(name="Node1", group=group)
+    node2 = StorageNode.create(name="Node2", group=group)
+
+    acq = ArchiveAcq.create(name="acq")
+    file = ArchiveFile.create(name="file1", acq=acq)
+    ArchiveFileCopy.create(file=file, node=node1, has_file="Y", wants_file="Y")
+    ArchiveFileCopy.create(file=file, node=node2, has_file="Y", wants_file="Y")
+
+    file = ArchiveFile.create(name="file2", acq=acq)
+    ArchiveFileCopy.create(file=file, node=node1, has_file="X", wants_file="Y")
+    ArchiveFileCopy.create(file=file, node=node2, has_file="Y", wants_file="Y")
+
+    file = ArchiveFile.create(name="file3", acq=acq)
+    ArchiveFileCopy.create(file=file, node=node1, has_file="Y", wants_file="Y")
+    ArchiveFileCopy.create(file=file, node=node2, has_file="X", wants_file="Y")
+
+    file = ArchiveFile.create(name="file4", acq=acq)
+    ArchiveFileCopy.create(file=file, node=node1, has_file="X", wants_file="Y")
+    ArchiveFileCopy.create(file=file, node=node2, has_file="X", wants_file="Y")
+
+    result = cli(
+        0, ["file", "list", "--corrupt", "--node=Node1", "--absent-node=Node2"]
+    )
+
+    assert "acq/file2" in result.output
+    assert "acq/file3" in result.output
+    assert "acq/file4" in result.output
+    assert result.output.count("acq") == 3
+
+    result = cli(
+        0, ["file", "list", "--corrupt", "--node=Node1", "--absent-node=Node2", "--all"]
+    )
+
+    assert "acq/file4" in result.output
+    assert result.output.count("acq") == 1