Repository: radiocosmology/alpenhorn

Issue:
Handle DB connection failure gracefully in CLI
Instead of, say, this:
```
$ alpenhorn acq list 
Traceback (most recent call last):
  File "/Users/dvw/venv312/bin/alpenhorn", line 8, in <module>
    sys.exit(entry())
             ^^^^^^^
  File "/Users/dvw/venv312/lib/python3.12/site-packages/click/core.py", line 1157, in __call__
    return self.main(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/dvw/venv312/lib/python3.12/site-packages/click/core.py", line 1078, in main
    rv = self.invoke(ctx)
         ^^^^^^^^^^^^^^^^
  File "/Users/dvw/venv312/lib/python3.12/site-packages/click/core.py", line 1688, in invoke
    return _process_result(sub_ctx.command.invoke(sub_ctx))
                           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/dvw/venv312/lib/python3.12/site-packages/click/core.py", line 1688, in invoke
    return _process_result(sub_ctx.command.invoke(sub_ctx))
                           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/dvw/venv312/lib/python3.12/site-packages/click/core.py", line 1434, in invoke
    return ctx.invoke(self.callback, **ctx.params)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/dvw/venv312/lib/python3.12/site-packages/click/core.py", line 783, in invoke
    return __callback(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/dvw/git/alpenhorn2/alpenhorn/cli/acq/list.py", line 59, in list_
    if data:
       ^^^^
  File "/Users/dvw/venv312/lib/python3.12/site-packages/peewee.py", line 2137, in __len__
    self._ensure_execution()
  File "/Users/dvw/venv312/lib/python3.12/site-packages/peewee.py", line 2119, in _ensure_execution
    self.execute()
  File "/Users/dvw/venv312/lib/python3.12/site-packages/peewee.py", line 2036, in inner
    return method(self, database, *args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/dvw/venv312/lib/python3.12/site-packages/peewee.py", line 2107, in execute
    return self._execute(database)
           ^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/dvw/venv312/lib/python3.12/site-packages/peewee.py", line 2280, in _execute
    cursor = database.execute(self)
             ^^^^^^^^^^^^^^^^
  File "/Users/dvw/venv312/lib/python3.12/site-packages/peewee.py", line 490, in __getattr__
    raise AttributeError('Cannot use uninitialized Proxy.')
AttributeError: Cannot use uninitialized Proxy.
```

Discussion:

Solution patch:
diff --git a/alpenhorn/cli/acq/__init__.py b/alpenhorn/cli/acq/__init__.py
index 9e1748420..6e02bbe22 100644
--- a/alpenhorn/cli/acq/__init__.py
+++ b/alpenhorn/cli/acq/__init__.py
@@ -5,6 +5,7 @@
 import click
 import peewee as pw
 
+from ..cli import dbconnect
 from .create import create
 from .files import files
 from .list import list_
@@ -15,6 +16,8 @@
 def cli():
     """Manage Acquisitions."""
 
+    dbconnect()
+
 
 cli.add_command(create, "create")
 cli.add_command(files, "files")
diff --git a/alpenhorn/cli/cli.py b/alpenhorn/cli/cli.py
index ea27dfa2b..ba586b623 100644
--- a/alpenhorn/cli/cli.py
+++ b/alpenhorn/cli/cli.py
@@ -9,6 +9,7 @@
 
 from ..common.logger import echo as echo
 from ..db import (
+    connect as dbconnect,
     ArchiveAcq,
     ArchiveFile,
     ArchiveFileCopy,
diff --git a/alpenhorn/cli/db/__init__.py b/alpenhorn/cli/db/__init__.py
index 1ca365053..9daf6e9aa 100644
--- a/alpenhorn/cli/db/__init__.py
+++ b/alpenhorn/cli/db/__init__.py
@@ -2,6 +2,7 @@
 
 import click
 
+from ..cli import dbconnect
 from .init import init
 
 
@@ -9,5 +10,7 @@
 def cli():
     """Manage the Data Index."""
 
+    dbconnect()
+
 
 cli.add_command(init, "init")
diff --git a/alpenhorn/cli/entry.py b/alpenhorn/cli/entry.py
index 2232cf1a0..b850c7145 100644
--- a/alpenhorn/cli/entry.py
+++ b/alpenhorn/cli/entry.py
@@ -4,7 +4,6 @@
 
 import click
 
-from .. import db
 from ..common.util import help_config_option, start_alpenhorn, version_option
 
 from . import acq, db, file, group, node
diff --git a/alpenhorn/cli/file/__init__.py b/alpenhorn/cli/file/__init__.py
index de08aea59..f80a2f561 100644
--- a/alpenhorn/cli/file/__init__.py
+++ b/alpenhorn/cli/file/__init__.py
@@ -3,8 +3,10 @@
 import click
 import peewee as pw
 
+from ..cli import dbconnect
 from .clean import clean
 from .create import create
+from .find import find
 from .import_ import import_
 from .list import list_
 from .modify import modify
@@ -16,9 +18,12 @@
 def cli():
     """Manage Files."""
 
+    dbconnect()
+
 
 cli.add_command(clean, "clean")
 cli.add_command(create, "create")
+cli.add_command(find, "find")
 cli.add_command(import_, "import")
 cli.add_command(list_, "list")
 cli.add_command(modify, "modify")
diff --git a/alpenhorn/cli/file/create.py b/alpenhorn/cli/file/create.py
index a54ee6435..8d0f2eb9d 100644
--- a/alpenhorn/cli/file/create.py
+++ b/alpenhorn/cli/file/create.py
@@ -16,7 +16,7 @@
     cli_option,
     not_both,
     requires_other,
-    resolve_acqs,
+    resolve_acq,
     validate_md5,
 )
 
@@ -106,7 +106,7 @@ def create(name, acq_name, from_file, md5, prefix, size):
         # database consistency).
         with database_proxy.atomic():
             # Resolve acq
-            acq = resolve_acqs([acq_name])[0]
+            acq = resolve_acq(acq_name)
 
             # Check that "name" isn't already a file in acq
             try:
@@ -149,7 +149,7 @@ def create(name, acq_name, from_file, md5, prefix, size):
     # Create the ArchiveFile
     with database_proxy.atomic():
         # Resolve acq
-        acq = resolve_acqs([acq_name])[0]
+        acq = resolve_acq(acq_name)
 
         # Check that "name" isn't already a file in acq.  We _may_ have already
         # done this once, but we need to do it again inside this transaction.
diff --git a/alpenhorn/cli/file/find.py b/alpenhorn/cli/file/find.py
new file mode 100644
index 000000000..1f5c70821
--- /dev/null
+++ b/alpenhorn/cli/file/find.py
@@ -0,0 +1,91 @@
+"""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_acq,
+    resolve_group,
+    resolve_node,
+    state_constraint,
+)
+
+
+@click.command()
+@cli_option("acq")
+@click.option("--corrupt", is_flag=True, help="Find corrupt files.")
+@click.option(
+    "--group",
+    metavar="GROUP",
+    multiple=True,
+    help="May be specified multiple times.  Limit search to GROUP(s).",
+)
+@click.option("--healthy", is_flag=True, help="Find healthy files.")
+@click.option("--missing", is_flag=True, help="Find missing files.")
+@click.option(
+    "--node",
+    metavar="NODE",
+    multiple=True,
+    help="May be specified multiple times.  Limit search to NODE(s).",
+)
+@click.option("--suspect", is_flag=True, help="Find suspect files.")
+@click.pass_context
+def find(ctx, acq, corrupt, group, healthy, missing, node, suspect):
+    """Find Files on Nodes.
+
+    Without options, lists every healthy File on every Node in the
+    Data Index.  Options can be used to change and limit the list.
+    """
+
+    nodes = resolve_node(node)
+    groups = resolve_group(group)
+    acqs = resolve_acq(acq)
+
+    state_expr = state_constraint(
+        corrupt=corrupt, healthy=healthy, missing=missing, suspect=suspect
+    )
+    if state_expr is None:
+        state_expr = state_constraint(healthy=True)
+
+    # Query
+    query = ArchiveFileCopy.select().join(ArchiveFile).where(state_expr)
+
+    # Add limits, when given
+    if acqs:
+        query = query.where(ArchiveFile.acq << acqs)
+    if groups:
+        query = query.switch(ArchiveFileCopy).join(StorageNode)
+        # Groups and Nodes need to be or'd together if both provided
+        if nodes:
+            query = query.where(
+                (ArchiveFileCopy.node << nodes) | (StorageNode.group << groups)
+            )
+        else:
+            query = query.where(StorageNode.group << groups)
+    elif nodes:
+        query = query.where(ArchiveFileCopy.node << nodes)
+
+    data = []
+    for copy in query.execute():
+        data.append((copy.file.path, copy.node.name, copy.state))
+
+    if data:
+        echo(tabulate(sorted(data), headers=["File", "Node", "State"]))
+    else:
+        echo("No match.")
diff --git a/alpenhorn/cli/file/list.py b/alpenhorn/cli/file/list.py
index 28598d9c9..64e02fc19 100644
--- a/alpenhorn/cli/file/list.py
+++ b/alpenhorn/cli/file/list.py
@@ -20,7 +20,7 @@
     both_or_neither,
     files_in_nodes,
     files_in_groups,
-    resolve_acqs,
+    resolve_acq,
     resolve_group,
     resolve_node,
     state_constraint,
@@ -221,9 +221,9 @@ def list_(
     detail_group = None
     if details and not from_:
         if node and not group and len(nodes) == 1:
-            detail_node = nodes[0]
+            detail_node = nodes.pop()
         elif group and not node and len(groups) == 1:
-            detail_group = groups[0]
+            detail_group = groups.pop()
 
     # The negative selection list
     if absent_node and absent_group:
@@ -300,7 +300,7 @@ def list_(
 
     # Apply the --acq limit, if given
     if acq:
-        query = query.where(ArchiveFile.acq << resolve_acqs(acq))
+        query = query.where(ArchiveFile.acq << resolve_acq(acq))
 
     # Apply syncability, if present
     if syncable_files is not None:
diff --git a/alpenhorn/cli/group/__init__.py b/alpenhorn/cli/group/__init__.py
index 634acd896..0b64629ac 100644
--- a/alpenhorn/cli/group/__init__.py
+++ b/alpenhorn/cli/group/__init__.py
@@ -3,8 +3,7 @@
 import click
 import peewee as pw
 
-from ...db import StorageGroup, StorageNode
-
+from ..cli import dbconnect
 from .autosync import autosync
 from .create import create
 from .list import list_
@@ -18,6 +17,8 @@
 def cli():
     """Manage Storage Groups."""
 
+    dbconnect()
+
 
 cli.add_command(autosync, "autosync")
 cli.add_command(create, "create")
diff --git a/alpenhorn/cli/group/sync.py b/alpenhorn/cli/group/sync.py
index 3f7aee2db..5cad9fbf9 100644
--- a/alpenhorn/cli/group/sync.py
+++ b/alpenhorn/cli/group/sync.py
@@ -22,7 +22,7 @@
     files_in_groups,
     not_both,
     requires_other,
-    resolve_acqs,
+    resolve_acq,
     resolve_group,
 )
 
@@ -383,7 +383,7 @@ def run_query(
             raise click.ClickException("No such group: " + node_name)
 
     # Resolve acqs
-    acqs = resolve_acqs(acq)
+    acqs = resolve_acq(acq)
 
     # Cancel and sync are different enough that they've been broken up into two functions:
     if cancel:
diff --git a/alpenhorn/cli/node/__init__.py b/alpenhorn/cli/node/__init__.py
index 8b8651d56..63ba9732b 100644
--- a/alpenhorn/cli/node/__init__.py
+++ b/alpenhorn/cli/node/__init__.py
@@ -2,6 +2,7 @@
 
 import click
 
+from ..cli import dbconnect
 from .activate import activate
 from .autoclean import autoclean
 from .clean import clean
@@ -22,6 +23,8 @@
 def cli():
     """Manage Storage Nodes."""
 
+    dbconnect()
+
 
 cli.add_command(activate, "activate")
 cli.add_command(autoclean, "autoclean")
diff --git a/alpenhorn/cli/node/clean.py b/alpenhorn/cli/node/clean.py
index af1a23722..084cb0d65 100644
--- a/alpenhorn/cli/node/clean.py
+++ b/alpenhorn/cli/node/clean.py
@@ -19,7 +19,7 @@
     cli_option,
     files_in_groups,
     not_both,
-    resolve_acqs,
+    resolve_acq,
     resolve_node,
     resolve_group,
 )
@@ -97,7 +97,7 @@ def _run_query(
             ctx.exit()
 
         # Resolve acqs
-        acqs = resolve_acqs(acq)
+        acqs = resolve_acq(acq)
 
         # Find all candidate file copies
         query = ArchiveFileCopy.select().where(ArchiveFileCopy.node == node, has_file)
diff --git a/alpenhorn/cli/node/verify.py b/alpenhorn/cli/node/verify.py
index c0ec24e22..e91f66fd4 100644
--- a/alpenhorn/cli/node/verify.py
+++ b/alpenhorn/cli/node/verify.py
@@ -7,7 +7,7 @@
 
 from ...common.util import pretty_bytes
 from ...db import ArchiveAcq, ArchiveFile, ArchiveFileCopy, database_proxy
-from ..options import cli_option, not_both, resolve_acqs, resolve_node, state_constraint
+from ..options import cli_option, not_both, resolve_acq, resolve_node, state_constraint
 from ..cli import check_then_update, echo
 
 
@@ -48,7 +48,7 @@ def _run_query(
         node = resolve_node(name)
 
         # Resolve acqs
-        acqs = resolve_acqs(acq)
+        acqs = resolve_acq(acq)
 
         # Find all candidate file copies
         query = ArchiveFileCopy.select(ArchiveFileCopy.id).where(
diff --git a/alpenhorn/cli/options.py b/alpenhorn/cli/options.py
index 144d1e9c4..5d8ead45f 100644
--- a/alpenhorn/cli/options.py
+++ b/alpenhorn/cli/options.py
@@ -262,12 +262,12 @@ def requires_other(
         raise click.UsageError(f"--{opt1_name} may only be used with --{opt2_name}")
 
 
-def resolve_group(group: str | list[str]) -> StorageGroup | list[StorageGroup]:
+def resolve_group(group: str | list[str]) -> StorageGroup | set[StorageGroup]:
     """Convert group name(s) into StorageGroup(s).
 
     If given a single `str`, returns a single `StorageGroup`.
     Otherwise, should be given a list of str and will return a
-    list of StorageGroups.
+    set of StorageGroups.
 
     If any name can't be resolved, raises ClickException.
     """
@@ -275,24 +275,24 @@ def resolve_group(group: str | list[str]) -> StorageGroup | list[StorageGroup]:
     if one_group:
         group = [group]
 
-    groups = []
+    groups = set()
     for name in group:
         try:
-            groups.append(StorageGroup.get(name=name))
+            groups.add(StorageGroup.get(name=name))
         except pw.DoesNotExist:
             raise click.ClickException("no such group: " + name)
 
     if one_group:
-        return groups[0]
+        return groups.pop()
     return groups
 
 
-def resolve_node(node: str | list[str]) -> StorageNode | list[StorageNode]:
+def resolve_node(node: str | list[str]) -> StorageNode | set[StorageNode]:
     """Convert node name(s) into StorageNode(s).
 
     If given a single `str`, returns a single `StorageNode`.
     Otherwise, should be given a list of str and will return a
-    list of StorageNodes.
+    set of StorageNodes.
 
     If any name can't be resolved, raises ClickException.
     """
@@ -300,32 +300,42 @@ def resolve_node(node: str | list[str]) -> StorageNode | list[StorageNode]:
     if one_node:
         node = [node]
 
-    nodes = []
+    nodes = set()
     for name in node:
         try:
-            nodes.append(StorageNode.get(name=name))
+            nodes.add(StorageNode.get(name=name))
         except pw.DoesNotExist:
             raise click.ClickException("no such node: " + name)
 
     if one_node:
-        return nodes[0]
+        return nodes.pop()
     return nodes
 
 
-def resolve_acqs(acq: list[str]) -> list[ArchiveAcq]:
+def resolve_acq(acq: str | list[str]) -> ArchiveAcq | set[ArchiveAcq]:
     """Convert --acq list to ArchiveAcq list.
 
-    If the input list is empty, so is the output list.
+    If given a single `str`, returns a single `ArchiveAcq`.
+    Otherwise, should be given a list of str and will return a
+    set of ArchiveAcqs.
 
     Raises `click.ClickException` if a non-existent acqusition was
     provided.
     """
-    acqs = []
+
+    one_acq = isinstance(acq, str)
+    if one_acq:
+        acq = [acq]
+
+    acqs = set()
     for acqname in acq:
         try:
-            acqs.append(ArchiveAcq.get(name=acqname))
+            acqs.add(ArchiveAcq.get(name=acqname))
         except pw.DoesNotExist:
             raise click.ClickException("No such acquisition: " + acqname)
+
+    if one_acq:
+        return acqs.pop()
     return acqs
 
 
diff --git a/alpenhorn/db/_base.py b/alpenhorn/db/_base.py
index ed04d5aee..31fa1f760 100644
--- a/alpenhorn/db/_base.py
+++ b/alpenhorn/db/_base.py
@@ -3,6 +3,7 @@
 from __future__ import annotations
 from typing import Any
 
+import click
 import logging
 import peewee as pw
 from playhouse import db_url
@@ -92,7 +93,15 @@ def connect() -> None:
     func = _capability("connect")
     if func is None:
         func = _connect
-    db = func(config=database_config)
+
+    # On connection error, raise click.ClickException
+    try:
+        db = func(config=database_config)
+    except pw.OperationalError as e:
+        raise click.ClickException(
+            f"Unable to connect to the database: {e}.\n"
+            "See --help-config for more details."
+        ) from e
 
     database_proxy.initialize(db)
 
@@ -118,7 +127,9 @@ def _connect(config: dict) -> pw.Database:
     """
 
     try:
-        db = db_url.connect(config.get("url", "sqlite:///:memory:"))
+        db = db_url.connect(config["url"])
+    except KeyError as e:
+        raise pw.OperationalError("No database configured") from e
     except RuntimeError as e:
         raise pw.OperationalError("Database connect failed") from e
 


Test patch:
diff --git a/tests/cli/file/test_find.py b/tests/cli/file/test_find.py
new file mode 100644
index 000000000..49444699b
--- /dev/null
+++ b/tests/cli/file/test_find.py
@@ -0,0 +1,248 @@
+"""Test CLI: alpenhorn file find"""
+
+from alpenhorn.db import (
+    StorageGroup,
+    StorageNode,
+    ArchiveAcq,
+    ArchiveFile,
+    ArchiveFileCopy,
+)
+
+
+def test_bad_acq(clidb, cli):
+    """Test a non-existent --acq"""
+
+    cli(1, ["file", "find", "--acq=MISSING"])
+
+
+def test_bad_node(clidb, cli):
+    """Test a non-existent --node"""
+
+    cli(1, ["file", "find", "--node=MISSING"])
+
+
+def test_bad_group(clidb, cli):
+    """Test a non-existent --group"""
+
+    cli(1, ["file", "find", "--group=MISSING"])
+
+
+def test_everything(clidb, cli, assert_row_present):
+    """Test listing everything."""
+
+    group = StorageGroup.create(name="Group")
+    node1 = StorageNode.create(name="Node1", group=group)
+    node2 = StorageNode.create(name="Node2", group=group)
+
+    acq = ArchiveAcq.create(name="acq1")
+    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="X", wants_file="Y")
+    file = ArchiveFile.create(name="file2", acq=acq)
+    ArchiveFileCopy.create(file=file, node=node1, has_file="M", wants_file="Y")
+    ArchiveFileCopy.create(file=file, node=node2, has_file="N", wants_file="Y")
+    acq = ArchiveAcq.create(name="acq2")
+    file = ArchiveFile.create(name="file3", acq=acq)
+    ArchiveFileCopy.create(file=file, node=node1, has_file="Y", wants_file="M")
+    ArchiveFileCopy.create(file=file, node=node2, has_file="Y", wants_file="Y")
+
+    result = cli(0, ["file", "find"])
+
+    assert_row_present(result.output, "acq1/file1", "Node1", "Present")
+    assert_row_present(result.output, "acq2/file3", "Node1", "Removable")
+    assert_row_present(result.output, "acq2/file3", "Node2", "Present")
+    assert result.output.count("acq") == 3
+
+
+def test_acq(clidb, cli, assert_row_present):
+    """Test limiting by --acq."""
+
+    group = StorageGroup.create(name="Group")
+    node = StorageNode.create(name="Node", group=group)
+
+    acq = ArchiveAcq.create(name="acq1")
+    file = ArchiveFile.create(name="file", acq=acq)
+    ArchiveFileCopy.create(file=file, node=node, has_file="Y", wants_file="Y")
+    acq = ArchiveAcq.create(name="acq2")
+    file = ArchiveFile.create(name="file", acq=acq)
+    ArchiveFileCopy.create(file=file, node=node, has_file="Y", wants_file="Y")
+
+    result = cli(0, ["file", "find", "--acq=acq1"])
+
+    assert_row_present(result.output, "acq1/file", "Node", "Present")
+    assert result.output.count("acq") == 1
+
+
+def test_node(clidb, cli, assert_row_present):
+    """Test limiting by --node."""
+
+    group = StorageGroup.create(name="Group")
+    node1 = StorageNode.create(name="Node1", group=group)
+    node2 = StorageNode.create(name="Node2", group=group)
+    node3 = StorageNode.create(name="Node3", 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="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", "find", "--node=Node1", "--node=Node2"])
+
+    assert_row_present(result.output, "acq/file1", "Node1", "Present")
+    assert_row_present(result.output, "acq/file1", "Node2", "Present")
+    assert_row_present(result.output, "acq/file2", "Node1", "Present")
+    assert_row_present(result.output, "acq/file3", "Node2", "Present")
+    assert result.output.count("acq") == 4
+
+
+def test_group(clidb, cli, assert_row_present):
+    """Test liming by --group."""
+
+    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")
+    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", "find", "--group=Group1", "--group=Group2"])
+
+    assert_row_present(result.output, "acq/file1", "Node1", "Present")
+    assert_row_present(result.output, "acq/file1", "Node2", "Present")
+    assert_row_present(result.output, "acq/file2", "Node1", "Present")
+    assert_row_present(result.output, "acq/file3", "Node2", "Present")
+    assert result.output.count("acq") == 4
+
+
+def test_node_group(clidb, cli, assert_row_present):
+    """Test --node and --group together."""
+
+    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")
+    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", "find", "--group=Group1", "--node=Node1", "--node=Node2"])
+
+    assert_row_present(result.output, "acq/file1", "Node1", "Present")
+    assert_row_present(result.output, "acq/file1", "Node2", "Present")
+    assert_row_present(result.output, "acq/file2", "Node1", "Present")
+    assert_row_present(result.output, "acq/file3", "Node2", "Present")
+    assert result.output.count("acq") == 4
+
+
+def test_state(clidb, cli, assert_row_present):
+    """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 rows we want to find for each state
+    state_rows = {
+        "corrupt": [
+            ("acq/FileXY", "Node", "Corrupt"),
+            ("acq/FileXM", "Node", "Corrupt"),
+        ],
+        "healthy": [
+            ("acq/FileYY", "Node", "Present"),
+            ("acq/FileYM", "Node", "Removable"),
+        ],
+        "suspect": [
+            ("acq/FileMY", "Node", "Suspect"),
+            ("acq/FileMM", "Node", "Suspect"),
+        ],
+        "missing": [("acq/FileNY", "Node", "Missing")],
+    }
+
+    # 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", "find"]
+        rows = []
+        if corrupt:
+            command.append("--corrupt")
+            rows += state_rows["corrupt"]
+        if suspect:
+            command.append("--suspect")
+            rows += state_rows["suspect"]
+        if missing:
+            command.append("--missing")
+            rows += state_rows["missing"]
+        if healthy:
+            command.append("--healthy")
+            rows += state_rows["healthy"]
+        # The default when no state is chosen
+        if not rows:
+            rows = state_rows["healthy"]
+
+        # Execute
+        result = cli(0, command)
+
+        # Check files
+        for row in rows:
+            assert_row_present(result.output, *row)
+        # Count rows to make sure there aren't extras
+        assert result.output.count("acq") == len(rows)
diff --git a/tests/conftest.py b/tests/conftest.py
index f52aaf489..cd467ce4c 100644
--- a/tests/conftest.py
+++ b/tests/conftest.py
@@ -441,7 +441,7 @@ def hostname(set_config):
     Returns the hostname."""
 
     config.config = config.merge_dict_tree(
-        set_config, {"base": {"hostname": "alpenhost"}}
+        config.config, {"base": {"hostname": "alpenhost"}}
     )
 
     return "alpenhost"
@@ -471,7 +471,7 @@ def test_chimedb(use_chimedb, dbproxy):
     cdb.test_enable()
 
     config.config = config.merge_dict_tree(
-        set_config, {"extensions": ["chimedb.core.alpenhorn"]}
+        config.config, {"extensions": ["chimedb.core.alpenhorn"]}
     )
 
 
@@ -484,6 +484,11 @@ def dbproxy(set_config):
     # Load extensions
     extensions.load_extensions()
 
+    # Set database.url if not already present
+    config.config = config.merge_dict_tree(
+        {"database": {"url": "sqlite:///:memory:"}}, config.config
+    )
+
     # DB start
     db.connect()
 
@@ -712,6 +717,18 @@ def clidb_noinit(clidb_uri):
 
     yield db
 
+    # Drop all the tables after the test
+    for table in [
+        StorageGroup,
+        StorageNode,
+        StorageTransferAction,
+        ArchiveAcq,
+        ArchiveFile,
+        ArchiveFileCopy,
+        ArchiveFileCopyRequest,
+        ArchiveFileImportRequest,
+    ]:
+        db.execute_sql(f"DROP TABLE IF EXISTS {table._meta.table_name};")
     db.close()