Repository: simonw/sqlite-utils

Issue:
`sqlite-utils transform` breaks DEFAULT string values and STRFTIME()
Very nice library! Our team found sqlite-utils through @simonw's [comment on the "Simple declarative schema migration for SQLite" article](https://news.ycombinator.com/item?id=31249823), and we were excited to use it, but unfortunately `sqlite-utils transform` seems to break our DB. 

Running `sqlite-utils transform` to modify a column mangles their DEFAULT values:

- Default string values are wrapped in extra single quotes
- Function expressions such as [`STRFTIME()`](https://www.sqlite.org/lang_datefunc.html) are turned into strings!

------

Here are steps to reproduce:

**Original database**

```
$ sqlite3 test.db << EOF
CREATE TABLE mytable (
    col1 TEXT DEFAULT 'foo',
    col2 TEXT DEFAULT (STRFTIME('%Y-%m-%d %H:%M:%f', 'NOW'))
)
EOF

$ sqlite3 test.db "SELECT sql FROM sqlite_master WHERE name = 'mytable';"
CREATE TABLE mytable (
    col1 TEXT DEFAULT 'foo',
    col2 TEXT DEFAULT (STRFTIME('%Y-%m-%d %H:%M:%f', 'NOW'))
)
```

**Modified database after sqlite-utils**

```
$ sqlite3 test.db "INSERT INTO mytable DEFAULT VALUES; SELECT * FROM mytable;"
foo|2022-11-02 02:26:58.038

$ sqlite-utils transform test.db mytable --rename col1 renamedcol1

$ sqlite3 test.db "SELECT sql FROM sqlite_master WHERE name = 'mytable';"
CREATE TABLE "mytable" (
   [renamedcol1] TEXT DEFAULT '''foo''',
   [col2] TEXT DEFAULT 'STRFTIME(''%Y-%m-%d %H:%M:%f'', ''NOW'')'
)

$ sqlite3 test.db "INSERT INTO mytable DEFAULT VALUES; SELECT * FROM mytable;"
foo|2022-11-02 02:26:58.038
'foo'|STRFTIME('%Y-%m-%d %H:%M:%f', 'NOW')
```

(Related: #336)

Discussion:

Patch:
diff --git a/.readthedocs.yaml b/.readthedocs.yaml
index ce66cbe..aa15c7c 100644
--- a/.readthedocs.yaml
+++ b/.readthedocs.yaml
@@ -3,8 +3,12 @@ version: 2
 sphinx:
   configuration: docs/conf.py
 
+build:
+  os: ubuntu-22.04
+  tools:
+    python: "3.11"
+
 python:
-  version: "3.8"
   install:
   - method: pip
     path: .
diff --git a/docs/_templates/base.html b/docs/_templates/base.html
index a9e670e..43c2003 100644
--- a/docs/_templates/base.html
+++ b/docs/_templates/base.html
@@ -3,4 +3,35 @@
 {% block site_meta %}
 {{ super() }}
 <script defer data-domain="sqlite-utils.datasette.io" src="https://plausible.io/js/plausible.js"></script>
-{% endblock %}
\ No newline at end of file
+{% endblock %}
+
+{% block scripts %}
+{{ super() }}
+<script>
+document.addEventListener("DOMContentLoaded", function() {
+  // Show banner linking to /stable/ if this is a /latest/ page
+  if (!/\/latest\//.test(location.pathname)) {
+    return;
+  }
+  var stableUrl = location.pathname.replace("/latest/", "/stable/");
+  // Check it's not a 404
+  fetch(stableUrl, { method: "HEAD" }).then((response) => {
+    if (response.status === 200) {
+      var warning = document.createElement("div");
+      warning.className = "admonition warning";
+      warning.innerHTML = `
+        <p class="first admonition-title">Note</p>
+        <p class="last">
+          This documentation covers the <strong>development version</strong> of Datasette.
+        </p>
+        <p>
+          See <a href="${stableUrl}">this page</a> for the current stable release.
+        </p>
+      `;
+      var mainArticle = document.querySelector("article[role=main]");
+      mainArticle.insertBefore(warning, mainArticle.firstChild);
+    }
+  });
+});
+</script>
+{% endblock %}
diff --git a/docs/cli-reference.rst b/docs/cli-reference.rst
index 153e5f9..8993213 100644
--- a/docs/cli-reference.rst
+++ b/docs/cli-reference.rst
@@ -124,6 +124,7 @@ See :ref:`cli_query`.
       --json-cols                 Detect JSON cols and output them as JSON, not
                                   escaped strings
       -r, --raw                   Raw output, first column of first row
+      --raw-lines                 Raw output, first column of each row
       -p, --param <TEXT TEXT>...  Named :parameters for SQL query
       --functions TEXT            Python code defining one or more custom SQL
                                   functions
@@ -192,6 +193,7 @@ See :ref:`cli_memory`.
       --json-cols                 Detect JSON cols and output them as JSON, not
                                   escaped strings
       -r, --raw                   Raw output, first column of first row
+      --raw-lines                 Raw output, first column of each row
       -p, --param <TEXT TEXT>...  Named :parameters for SQL query
       --encoding TEXT             Character encoding for CSV input, defaults to
                                   utf-8
@@ -631,6 +633,7 @@ See :ref:`cli_convert`.
       --output-type [integer|float|blob|text]
                                       Column type to use for the output column
       --drop                          Drop original column afterwards
+      --no-skip-false                 Don't skip falsey values
       -s, --silent                    Don't show a progress bar
       -h, --help                      Show this message and exit.
 
diff --git a/docs/cli.rst b/docs/cli.rst
index 1d67e88..572b0e5 100644
--- a/docs/cli.rst
+++ b/docs/cli.rst
@@ -239,6 +239,9 @@ For example, to retrieve a binary image from a ``BLOB`` column and store it in a
 
     $ sqlite-utils photos.db "select contents from photos where id=1" --raw > myphoto.jpg
 
+To return the first column of each result as raw data, separated by newlines, use ``--raw-lines``::
+
+    $ sqlite-utils photos.db "select caption from photos" --raw-lines > captions.txt
 
 .. _cli_query_parameters:
 
@@ -1339,6 +1342,8 @@ You can include named parameters in your where clause and populate them using on
 
 The ``--dry-run`` option will output a preview of the conversion against the first ten rows, without modifying the database.
 
+By default any rows with a falsey value for the column - such as ``0`` or ``null`` - will be skipped. Use the ``--no-skip-false`` option to disable this behaviour.
+
 .. _cli_convert_import:
 
 Importing additional modules
diff --git a/docs/conf.py b/docs/conf.py
index 8d49b81..859c6b9 100644
--- a/docs/conf.py
+++ b/docs/conf.py
@@ -41,7 +41,7 @@ autodoc_member_order = "bysource"
 autodoc_typehints = "description"
 
 extlinks = {
-    "issue": ("https://github.com/simonw/sqlite-utils/issues/%s", "#"),
+    "issue": ("https://github.com/simonw/sqlite-utils/issues/%s", "#%s"),
 }
 
 
diff --git a/docs/python-api.rst b/docs/python-api.rst
index 206e5e6..4d883db 100644
--- a/docs/python-api.rst
+++ b/docs/python-api.rst
@@ -916,6 +916,8 @@ This will add the new column, if it does not already exist. You can pass ``outpu
 
 If you want to drop the original column after saving the results in a separate output column, pass ``drop=True``.
 
+By default any rows with a falsey value for the column - such as ``0`` or ``None`` - will be skipped. Pass ``skip_false=False`` to disable this behaviour.
+
 You can create multiple new columns from a single input column by passing ``multi=True`` and a conversion function that returns a Python dictionary. This example creates new ``upper`` and ``lower`` columns populated from the single ``title`` column:
 
 .. code-block:: python
diff --git a/sqlite_utils/cli.py b/sqlite_utils/cli.py
index d25b1df..ce354e0 100644
--- a/sqlite_utils/cli.py
+++ b/sqlite_utils/cli.py
@@ -948,14 +948,15 @@ def insert_upsert_implementation(
 
     # The --sniff option needs us to buffer the file to peek ahead
     sniff_buffer = None
+    decoded_buffer = None
     if sniff:
         sniff_buffer = io.BufferedReader(file, buffer_size=4096)
-        decoded = io.TextIOWrapper(sniff_buffer, encoding=encoding)
+        decoded_buffer = io.TextIOWrapper(sniff_buffer, encoding=encoding)
     else:
-        decoded = io.TextIOWrapper(file, encoding=encoding)
+        decoded_buffer = io.TextIOWrapper(file, encoding=encoding)
 
     tracker = None
-    with file_progress(decoded, silent=silent) as decoded:
+    with file_progress(decoded_buffer, silent=silent) as decoded:
         if csv or tsv:
             if sniff:
                 # Read first 2048 bytes and use that to detect
@@ -993,9 +994,11 @@ def insert_upsert_implementation(
                     docs = json.load(decoded)
                     if isinstance(docs, dict):
                         docs = [docs]
-            except json.decoder.JSONDecodeError:
+            except json.decoder.JSONDecodeError as ex:
                 raise click.ClickException(
-                    "Invalid JSON - use --csv for CSV or --tsv for TSV files"
+                    "Invalid JSON - use --csv for CSV or --tsv for TSV files\n\nJSON error: {}".format(
+                        ex
+                    )
                 )
             if flatten:
                 docs = (_flatten(doc) for doc in docs)
@@ -1079,6 +1082,12 @@ def insert_upsert_implementation(
     if tracker is not None:
         db[table].transform(types=tracker.types)
 
+    # Clean up open file-like objects
+    if sniff_buffer:
+        sniff_buffer.close()
+    if decoded_buffer:
+        decoded_buffer.close()
+
 
 def _find_variables(tb, vars):
     to_find = list(vars)
@@ -1653,6 +1662,7 @@ def drop_view(path, view, ignore, load_extension):
 )
 @output_options
 @click.option("-r", "--raw", is_flag=True, help="Raw output, first column of first row")
+@click.option("--raw-lines", is_flag=True, help="Raw output, first column of each row")
 @click.option(
     "-p",
     "--param",
@@ -1677,6 +1687,7 @@ def query(
     fmt,
     json_cols,
     raw,
+    raw_lines,
     param,
     load_extension,
     functions,
@@ -1700,7 +1711,19 @@ def query(
         _register_functions(db, functions)
 
     _execute_query(
-        db, sql, param, raw, table, csv, tsv, no_headers, fmt, nl, arrays, json_cols
+        db,
+        sql,
+        param,
+        raw,
+        raw_lines,
+        table,
+        csv,
+        tsv,
+        no_headers,
+        fmt,
+        nl,
+        arrays,
+        json_cols,
     )
 
 
@@ -1728,6 +1751,7 @@ def query(
 )
 @output_options
 @click.option("-r", "--raw", is_flag=True, help="Raw output, first column of first row")
+@click.option("--raw-lines", is_flag=True, help="Raw output, first column of each row")
 @click.option(
     "-p",
     "--param",
@@ -1773,6 +1797,7 @@ def memory(
     fmt,
     json_cols,
     raw,
+    raw_lines,
     param,
     encoding,
     no_detect_types,
@@ -1816,40 +1841,43 @@ def memory(
     stem_counts = {}
     for i, path in enumerate(paths):
         # Path may have a :format suffix
+        fp = None
         if ":" in path and path.rsplit(":", 1)[-1].upper() in Format.__members__:
             path, suffix = path.rsplit(":", 1)
             format = Format[suffix.upper()]
         else:
             format = None
         if path in ("-", "stdin"):
-            csv_fp = sys.stdin.buffer
-            csv_table = "stdin"
+            fp = sys.stdin.buffer
+            file_table = "stdin"
         else:
-            csv_path = pathlib.Path(path)
-            stem = csv_path.stem
+            file_path = pathlib.Path(path)
+            stem = file_path.stem
             if stem_counts.get(stem):
-                csv_table = "{}_{}".format(stem, stem_counts[stem])
+                file_table = "{}_{}".format(stem, stem_counts[stem])
             else:
-                csv_table = stem
+                file_table = stem
             stem_counts[stem] = stem_counts.get(stem, 1) + 1
-            csv_fp = csv_path.open("rb")
-        rows, format_used = rows_from_file(csv_fp, format=format, encoding=encoding)
+            fp = file_path.open("rb")
+        rows, format_used = rows_from_file(fp, format=format, encoding=encoding)
         tracker = None
         if format_used in (Format.CSV, Format.TSV) and not no_detect_types:
             tracker = TypeTracker()
             rows = tracker.wrap(rows)
         if flatten:
             rows = (_flatten(row) for row in rows)
-        db[csv_table].insert_all(rows, alter=True)
+        db[file_table].insert_all(rows, alter=True)
         if tracker is not None:
-            db[csv_table].transform(types=tracker.types)
+            db[file_table].transform(types=tracker.types)
         # Add convenient t / t1 / t2 views
         view_names = ["t{}".format(i + 1)]
         if i == 0:
             view_names.append("t")
         for view_name in view_names:
             if not db[view_name].exists():
-                db.create_view(view_name, "select * from [{}]".format(csv_table))
+                db.create_view(view_name, "select * from [{}]".format(file_table))
+        if fp:
+            fp.close()
 
     if analyze:
         _analyze(db, tables=None, columns=None, save=False)
@@ -1879,12 +1907,36 @@ def memory(
         _register_functions(db, functions)
 
     _execute_query(
-        db, sql, param, raw, table, csv, tsv, no_headers, fmt, nl, arrays, json_cols
+        db,
+        sql,
+        param,
+        raw,
+        raw_lines,
+        table,
+        csv,
+        tsv,
+        no_headers,
+        fmt,
+        nl,
+        arrays,
+        json_cols,
     )
 
 
 def _execute_query(
-    db, sql, param, raw, table, csv, tsv, no_headers, fmt, nl, arrays, json_cols
+    db,
+    sql,
+    param,
+    raw,
+    raw_lines,
+    table,
+    csv,
+    tsv,
+    no_headers,
+    fmt,
+    nl,
+    arrays,
+    json_cols,
 ):
     with db.conn:
         try:
@@ -1903,6 +1955,13 @@ def _execute_query(
                 sys.stdout.buffer.write(data)
             else:
                 sys.stdout.write(str(data))
+        elif raw_lines:
+            for row in cursor:
+                data = row[0]
+                if isinstance(data, bytes):
+                    sys.stdout.buffer.write(data + b"\n")
+                else:
+                    sys.stdout.write(str(data) + "\n")
         elif fmt or table:
             print(
                 tabulate.tabulate(
@@ -2752,6 +2811,7 @@ def _generate_convert_help():
     type=click.Choice(["integer", "float", "blob", "text"]),
 )
 @click.option("--drop", is_flag=True, help="Drop original column afterwards")
+@click.option("--no-skip-false", is_flag=True, help="Don't skip falsey values")
 @click.option("-s", "--silent", is_flag=True, help="Don't show a progress bar")
 def convert(
     db_path,
@@ -2766,6 +2826,7 @@ def convert(
     output,
     output_type,
     drop,
+    no_skip_false,
     silent,
 ):
     sqlite3.enable_callback_tracebacks(True)
@@ -2823,6 +2884,7 @@ def convert(
                 output=output,
                 output_type=output_type,
                 drop=drop,
+                skip_false=not no_skip_false,
                 multi=multi,
                 show_progress=not silent,
             )
diff --git a/sqlite_utils/db.py b/sqlite_utils/db.py
index e819d17..dcd3b5f 100644
--- a/sqlite_utils/db.py
+++ b/sqlite_utils/db.py
@@ -542,6 +542,24 @@ class Database:
             '"{}"'.format(bit) if not bit.startswith('"') else bit for bit in bits
         )
 
+    def quote_default_value(self, value: str) -> str:
+        if any(
+            [
+                str(value).startswith("'") and str(value).endswith("'"),
+                str(value).startswith('"') and str(value).endswith('"'),
+            ]
+        ):
+            return value
+
+        if str(value).upper() in ("CURRENT_TIME", "CURRENT_DATE", "CURRENT_TIMESTAMP"):
+            return value
+
+        if str(value).endswith(")"):
+            # Expr
+            return "({})".format(value)
+
+        return self.quote(value)
+
     def table_names(self, fts4: bool = False, fts5: bool = False) -> List[str]:
         """
         List of string table names in this database.
@@ -813,6 +831,8 @@ class Database:
             pk = hash_id
         # Soundness check foreign_keys point to existing tables
         for fk in foreign_keys:
+            if fk.other_table == name and columns.get(fk.other_column):
+                continue
             if not any(
                 c for c in self[fk.other_table].columns if c.name == fk.other_column
             ):
@@ -837,7 +857,7 @@ class Database:
                 column_extras.append("NOT NULL")
             if column_name in defaults and defaults[column_name] is not None:
                 column_extras.append(
-                    "DEFAULT {}".format(self.quote(defaults[column_name]))
+                    "DEFAULT {}".format(self.quote_default_value(defaults[column_name]))
                 )
             if column_name in foreign_keys_by_column:
                 column_extras.append(
@@ -2018,7 +2038,9 @@ class Table(Queryable):
             col_type = str
         not_null_sql = None
         if not_null_default is not None:
-            not_null_sql = "NOT NULL DEFAULT {}".format(self.db.quote(not_null_default))
+            not_null_sql = "NOT NULL DEFAULT {}".format(
+                self.db.quote_default_value(not_null_default)
+            )
         sql = "ALTER TABLE [{table}] ADD COLUMN [{col_name}] {col_type}{not_null_default};".format(
             table=self.name,
             col_name=col_name,
@@ -2618,6 +2640,7 @@ class Table(Queryable):
         where: Optional[str] = None,
         where_args: Optional[Union[Iterable, dict]] = None,
         show_progress: bool = False,
+        skip_false: bool = True,
     ):
         """
         Apply conversion function ``fn`` to every value in the specified columns.
@@ -2660,7 +2683,7 @@ class Table(Queryable):
 
             def convert_value(v):
                 bar.update(1)
-                if not v:
+                if skip_false and not v:
                     return v
                 return jsonify_if_needed(fn(v))
 
@@ -2741,6 +2764,7 @@ class Table(Queryable):
         hash_id_columns,
         upsert,
         pk,
+        not_null,
         conversions,
         num_records_processed,
         replace,
@@ -2778,14 +2802,20 @@ class Table(Queryable):
                 pks = pk
             self.last_pk = None
             for record_values in values:
-                # TODO: make more efficient:
                 record = dict(zip(all_columns, record_values))
-                sql = "INSERT OR IGNORE INTO [{table}]({pks}) VALUES({pk_placeholders});".format(
+                placeholders = list(pks)
+                # Need to populate not-null columns too, or INSERT OR IGNORE ignores
+                # them since it ignores the resulting integrity errors
+                if not_null:
+                    placeholders.extend(not_null)
+                sql = "INSERT OR IGNORE INTO [{table}]({cols}) VALUES({placeholders});".format(
                     table=self.name,
-                    pks=", ".join(["[{}]".format(p) for p in pks]),
-                    pk_placeholders=", ".join(["?" for p in pks]),
+                    cols=", ".join(["[{}]".format(p) for p in placeholders]),
+                    placeholders=", ".join(["?" for p in placeholders]),
+                )
+                queries_and_params.append(
+                    (sql, [record[col] for col in pks] + ["" for _ in (not_null or [])])
                 )
-                queries_and_params.append((sql, [record[col] for col in pks]))
                 # UPDATE [book] SET [name] = 'Programming' WHERE [id] = 1001;
                 set_cols = [col for col in all_columns if col not in pks]
                 if set_cols:
@@ -2846,6 +2876,7 @@ class Table(Queryable):
         hash_id_columns,
         upsert,
         pk,
+        not_null,
         conversions,
         num_records_processed,
         replace,
@@ -2859,6 +2890,7 @@ class Table(Queryable):
             hash_id_columns,
             upsert,
             pk,
+            not_null,
             conversions,
             num_records_processed,
             replace,
@@ -2876,7 +2908,6 @@ class Table(Queryable):
                         self.add_missing_columns(chunk)
                         result = self.db.execute(query, params)
                     elif e.args[0] == "too many SQL variables":
-
                         first_half = chunk[: len(chunk) // 2]
                         second_half = chunk[len(chunk) // 2 :]
 
@@ -2889,6 +2920,7 @@ class Table(Queryable):
                             hash_id_columns,
                             upsert,
                             pk,
+                            not_null,
                             conversions,
                             num_records_processed,
                             replace,
@@ -2904,6 +2936,7 @@ class Table(Queryable):
                             hash_id_columns,
                             upsert,
                             pk,
+                            not_null,
                             conversions,
                             num_records_processed,
                             replace,
@@ -3113,6 +3146,7 @@ class Table(Queryable):
                 hash_id_columns,
                 upsert,
                 pk,
+                not_null,
                 conversions,
                 num_records_processed,
                 replace,
diff --git a/sqlite_utils/utils.py b/sqlite_utils/utils.py
index 4e5bbcc..3bd5db1 100644
--- a/sqlite_utils/utils.py
+++ b/sqlite_utils/utils.py
@@ -24,10 +24,11 @@ except ImportError:
 
     OperationalError = sqlite3.OperationalError
 
-
 SPATIALITE_PATHS = (
     "/usr/lib/x86_64-linux-gnu/mod_spatialite.so",
     "/usr/local/lib/mod_spatialite.dylib",
+    "/usr/local/lib/mod_spatialite.so",
+    "/opt/homebrew/lib/mod_spatialite.dylib",
 )
 
 # Mainly so we can restore it if needed in the tests:


Test patch:
diff --git a/tests/test_cli.py b/tests/test_cli.py
index 20fe99e..5360e56 100644
--- a/tests/test_cli.py
+++ b/tests/test_cli.py
@@ -13,6 +13,11 @@ import textwrap
 from .utils import collapse_whitespace
 
 
+def write_json(file_path, data):
+    with open(file_path, "w") as fp:
+        json.dump(data, fp)
+
+
 def _supports_pragma_function_list():
     db = Database(memory=True)
     try:
@@ -916,6 +921,21 @@ def test_query_raw(db_path, content, is_binary):
         assert result.output == str(content)
 
 
+@pytest.mark.parametrize(
+    "content,is_binary",
+    [(b"\x00\x0Fbinary", True), ("this is text", False), (1, False), (1.5, False)],
+)
+def test_query_raw_lines(db_path, content, is_binary):
+    Database(db_path)["files"].insert_all({"content": content} for _ in range(3))
+    result = CliRunner().invoke(
+        cli.cli, [db_path, "select content from files", "--raw-lines"]
+    )
+    if is_binary:
+        assert result.stdout_bytes == b"\n".join(content for _ in range(3)) + b"\n"
+    else:
+        assert result.output == "\n".join(str(content) for _ in range(3)) + "\n"
+
+
 def test_query_memory_does_not_create_file(tmpdir):
     owd = os.getcwd()
     try:
@@ -1001,7 +1021,7 @@ def test_upsert(db_path, tmpdir):
         {"id": 1, "name": "Cleo", "age": 4},
         {"id": 2, "name": "Nixie", "age": 4},
     ]
-    open(json_path, "w").write(json.dumps(insert_dogs))
+    write_json(json_path, insert_dogs)
     result = CliRunner().invoke(
         cli.cli,
         ["insert", db_path, "dogs", json_path, "--pk", "id"],
@@ -1014,7 +1034,7 @@ def test_upsert(db_path, tmpdir):
         {"id": 1, "age": 5},
         {"id": 2, "age": 5},
     ]
-    open(json_path, "w").write(json.dumps(upsert_dogs))
+    write_json(json_path, upsert_dogs)
     result = CliRunner().invoke(
         cli.cli,
         ["upsert", db_path, "dogs", json_path, "--pk", "id"],
@@ -1033,7 +1053,7 @@ def test_upsert_pk_required(db_path, tmpdir):
         {"id": 1, "name": "Cleo", "age": 4},
         {"id": 2, "name": "Nixie", "age": 4},
     ]
-    open(json_path, "w").write(json.dumps(insert_dogs))
+    write_json(json_path, insert_dogs)
     result = CliRunner().invoke(
         cli.cli,
         ["upsert", db_path, "dogs", json_path],
@@ -1076,14 +1096,14 @@ def test_upsert_alter(db_path, tmpdir):
     json_path = str(tmpdir / "dogs.json")
     db = Database(db_path)
     insert_dogs = [{"id": 1, "name": "Cleo"}]
-    open(json_path, "w").write(json.dumps(insert_dogs))
+    write_json(json_path, insert_dogs)
     result = CliRunner().invoke(
         cli.cli, ["insert", db_path, "dogs", json_path, "--pk", "id"]
     )
     assert 0 == result.exit_code, result.output
     # Should fail with error code if no --alter
     upsert_dogs = [{"id": 1, "age": 5}]
-    open(json_path, "w").write(json.dumps(upsert_dogs))
+    write_json(json_path, upsert_dogs)
     result = CliRunner().invoke(
         cli.cli, ["upsert", db_path, "dogs", json_path, "--pk", "id"]
     )
@@ -1752,7 +1772,8 @@ def test_insert_encoding(tmpdir):
     )
     assert latin1_csv.decode("latin-1").split("\n")[2].split(",")[1] == "São Paulo"
     csv_path = str(tmpdir / "test.csv")
-    open(csv_path, "wb").write(latin1_csv)
+    with open(csv_path, "wb") as fp:
+        fp.write(latin1_csv)
     # First attempt should error:
     bad_result = CliRunner().invoke(
         cli.cli,
diff --git a/tests/test_cli_convert.py b/tests/test_cli_convert.py
index d26ab8c..97988b9 100644
--- a/tests/test_cli_convert.py
+++ b/tests/test_cli_convert.py
@@ -626,3 +626,30 @@ def test_convert_initialization_pattern(fresh_db_and_path):
     assert list(db["names"].rows) == [
         {"id": 1, "name": "17"},
     ]
+
+
+@pytest.mark.parametrize(
+    "no_skip_false,expected",
+    (
+        (True, 1),
+        (False, 0),
+    ),
+)
+def test_convert_no_skip_false(fresh_db_and_path, no_skip_false, expected):
+    db, db_path = fresh_db_and_path
+    args = [
+        "convert",
+        db_path,
+        "t",
+        "x",
+        "-",
+    ]
+    if no_skip_false:
+        args.append("--no-skip-false")
+    db["t"].insert_all([{"x": 0}, {"x": 1}])
+    assert db["t"].get(1)["x"] == 0
+    assert db["t"].get(2)["x"] == 1
+    result = CliRunner().invoke(cli.cli, args, input="value + 1")
+    assert 0 == result.exit_code, result.output
+    assert db["t"].get(1)["x"] == expected
+    assert db["t"].get(2)["x"] == 2
diff --git a/tests/test_cli_insert.py b/tests/test_cli_insert.py
index 45ad362..f403328 100644
--- a/tests/test_cli_insert.py
+++ b/tests/test_cli_insert.py
@@ -10,7 +10,8 @@ import time
 def test_insert_simple(tmpdir):
     json_path = str(tmpdir / "dog.json")
     db_path = str(tmpdir / "dogs.db")
-    open(json_path, "w").write(json.dumps({"name": "Cleo", "age": 4}))
+    with open(json_path, "w") as fp:
+        fp.write(json.dumps({"name": "Cleo", "age": 4}))
     result = CliRunner().invoke(cli.cli, ["insert", db_path, "dogs", json_path])
     assert 0 == result.exit_code
     assert [{"age": 4, "name": "Cleo"}] == list(
@@ -42,9 +43,9 @@ def test_insert_invalid_json_error(tmpdir):
         input="name,age\nCleo,4",
     )
     assert result.exit_code == 1
-    assert (
-        result.output
-        == "Error: Invalid JSON - use --csv for CSV or --tsv for TSV files\n"
+    assert result.output == (
+        "Error: Invalid JSON - use --csv for CSV or --tsv for TSV files\n\n"
+        "JSON error: Expecting value: line 1 column 1 (char 0)\n"
     )
 
 
@@ -78,7 +79,8 @@ def test_insert_json_flatten_nl(tmpdir):
 
 def test_insert_with_primary_key(db_path, tmpdir):
     json_path = str(tmpdir / "dog.json")
-    open(json_path, "w").write(json.dumps({"id": 1, "name": "Cleo", "age": 4}))
+    with open(json_path, "w") as fp:
+        fp.write(json.dumps({"id": 1, "name": "Cleo", "age": 4}))
     result = CliRunner().invoke(
         cli.cli, ["insert", db_path, "dogs", json_path, "--pk", "id"]
     )
@@ -93,7 +95,8 @@ def test_insert_with_primary_key(db_path, tmpdir):
 def test_insert_multiple_with_primary_key(db_path, tmpdir):
     json_path = str(tmpdir / "dogs.json")
     dogs = [{"id": i, "name": "Cleo {}".format(i), "age": i + 3} for i in range(1, 21)]
-    open(json_path, "w").write(json.dumps(dogs))
+    with open(json_path, "w") as fp:
+        fp.write(json.dumps(dogs))
     result = CliRunner().invoke(
         cli.cli, ["insert", db_path, "dogs", json_path, "--pk", "id"]
     )
@@ -109,7 +112,8 @@ def test_insert_multiple_with_compound_primary_key(db_path, tmpdir):
         {"breed": "mixed", "id": i, "name": "Cleo {}".format(i), "age": i + 3}
         for i in range(1, 21)
     ]
-    open(json_path, "w").write(json.dumps(dogs))
+    with open(json_path, "w") as fp:
+        fp.write(json.dumps(dogs))
     result = CliRunner().invoke(
         cli.cli, ["insert", db_path, "dogs", json_path, "--pk", "id", "--pk", "breed"]
     )
@@ -134,7 +138,8 @@ def test_insert_not_null_default(db_path, tmpdir):
         {"id": i, "name": "Cleo {}".format(i), "age": i + 3, "score": 10}
         for i in range(1, 21)
     ]
-    open(json_path, "w").write(json.dumps(dogs))
+    with open(json_path, "w") as fp:
+        fp.write(json.dumps(dogs))
     result = CliRunner().invoke(
         cli.cli,
         ["insert", db_path, "dogs", json_path, "--pk", "id"]
@@ -182,7 +187,8 @@ def test_insert_ignore(db_path, tmpdir):
     db = Database(db_path)
     db["dogs"].insert({"id": 1, "name": "Cleo"}, pk="id")
     json_path = str(tmpdir / "dogs.json")
-    open(json_path, "w").write(json.dumps([{"id": 1, "name": "Bailey"}]))
+    with open(json_path, "w") as fp:
+        fp.write(json.dumps([{"id": 1, "name": "Bailey"}]))
     # Should raise error without --ignore
     result = CliRunner().invoke(
         cli.cli, ["insert", db_path, "dogs", json_path, "--pk", "id"]
@@ -212,7 +218,8 @@ def test_insert_ignore(db_path, tmpdir):
 def test_insert_csv_tsv(content, options, db_path, tmpdir):
     db = Database(db_path)
     file_path = str(tmpdir / "insert.csv-tsv")
-    open(file_path, "w").write(content)
+    with open(file_path, "w") as fp:
+        fp.write(content)
     result = CliRunner().invoke(
         cli.cli,
         ["insert", db_path, "data", file_path] + options,
@@ -233,7 +240,8 @@ def test_insert_csv_tsv(content, options, db_path, tmpdir):
 )
 def test_only_allow_one_of_nl_tsv_csv(options, db_path, tmpdir):
     file_path = str(tmpdir / "insert.csv-tsv")
-    open(file_path, "w").write("foo")
+    with open(file_path, "w") as fp:
+        fp.write("foo")
     result = CliRunner().invoke(
         cli.cli, ["insert", db_path, "data", file_path] + options
     )
@@ -251,7 +259,8 @@ def test_insert_replace(db_path, tmpdir):
         {"id": 2, "name": "Insert replaced 2", "age": 4},
         {"id": 21, "name": "Fresh insert 21", "age": 6},
     ]
-    open(json_path, "w").write(json.dumps(insert_replace_dogs))
+    with open(json_path, "w") as fp:
+        fp.write(json.dumps(insert_replace_dogs))
     result = CliRunner().invoke(
         cli.cli, ["insert", db_path, "dogs", json_path, "--pk", "id", "--replace"]
     )
diff --git a/tests/test_cli_memory.py b/tests/test_cli_memory.py
index bb50d23..41bb00a 100644
--- a/tests/test_cli_memory.py
+++ b/tests/test_cli_memory.py
@@ -24,7 +24,8 @@ def test_memory_csv(tmpdir, sql_from, use_stdin):
             sql_from = "stdin"
     else:
         csv_path = str(tmpdir / "test.csv")
-        open(csv_path, "w").write(content)
+        with open(csv_path, "w") as fp:
+            fp.write(content)
     result = CliRunner().invoke(
         cli.cli,
         ["memory", csv_path, "select * from {}".format(sql_from), "--nl"],
@@ -46,7 +47,8 @@ def test_memory_tsv(tmpdir, use_stdin):
     else:
         input = None
         path = str(tmpdir / "chickens.tsv")
-        open(path, "w").write(data)
+        with open(path, "w") as fp:
+            fp.write(data)
         path = path + ":tsv"
         sql_from = "chickens"
     result = CliRunner().invoke(
@@ -71,7 +73,8 @@ def test_memory_json(tmpdir, use_stdin):
     else:
         input = None
         path = str(tmpdir / "chickens.json")
-        open(path, "w").write(data)
+        with open(path, "w") as fp:
+            fp.write(data)
         path = path + ":json"
         sql_from = "chickens"
     result = CliRunner().invoke(
@@ -96,7 +99,8 @@ def test_memory_json_nl(tmpdir, use_stdin):
     else:
         input = None
         path = str(tmpdir / "chickens.json")
-        open(path, "w").write(data)
+        with open(path, "w") as fp:
+            fp.write(data)
         path = path + ":nl"
         sql_from = "chickens"
     result = CliRunner().invoke(
diff --git a/tests/test_convert.py b/tests/test_convert.py
index 31a9d28..9fd29c3 100644
--- a/tests/test_convert.py
+++ b/tests/test_convert.py
@@ -50,6 +50,16 @@ def test_convert_where(fresh_db, where, where_args):
     assert list(table.rows) == [{"id": 1, "title": "One"}, {"id": 2, "title": "TWO"}]
 
 
+def test_convert_skip_false(fresh_db):
+    table = fresh_db["table"]
+    table.insert_all([{"x": 0}, {"x": 1}])
+    assert table.get(1)["x"] == 0
+    assert table.get(2)["x"] == 1
+    table.convert("x", lambda x: x + 1, skip_false=False)
+    assert table.get(1)["x"] == 1
+    assert table.get(2)["x"] == 2
+
+
 @pytest.mark.parametrize(
     "drop,expected",
     (
diff --git a/tests/test_create.py b/tests/test_create.py
index 7252e48..6931f0e 100644
--- a/tests/test_create.py
+++ b/tests/test_create.py
@@ -288,6 +288,25 @@ def test_create_table_works_for_m2m_with_only_foreign_keys(
     )
 
 
+def test_self_referential_foreign_key(fresh_db):
+    assert [] == fresh_db.table_names()
+    table = fresh_db.create_table(
+        "test_table",
+        columns={
+            "id": int,
+            "ref": int,
+        },
+        pk="id",
+        foreign_keys=(("ref", "test_table", "id"),),
+    )
+    assert (
+        "CREATE TABLE [test_table] (\n"
+        "   [id] INTEGER PRIMARY KEY,\n"
+        "   [ref] INTEGER REFERENCES [test_table]([id])\n"
+        ")"
+    ) == table.schema
+
+
 def test_create_error_if_invalid_foreign_keys(fresh_db):
     with pytest.raises(AlterError):
         fresh_db["one"].insert(
@@ -297,6 +316,16 @@ def test_create_error_if_invalid_foreign_keys(fresh_db):
         )
 
 
+def test_create_error_if_invalid_self_referential_foreign_keys(fresh_db):
+    with pytest.raises(AlterError) as ex:
+        fresh_db["one"].insert(
+            {"id": 1, "ref_id": 3},
+            pk="id",
+            foreign_keys=(("ref_id", "one", "bad_column"),),
+        )
+        assert ex.value.args == ("No such column: one.bad_column",)
+
+
 @pytest.mark.parametrize(
     "col_name,col_type,not_null_default,expected_schema",
     (
diff --git a/tests/test_default_value.py b/tests/test_default_value.py
new file mode 100644
index 0000000..9ffdb14
--- /dev/null
+++ b/tests/test_default_value.py
@@ -0,0 +1,34 @@
+import pytest
+
+
+EXAMPLES = [
+    ("TEXT DEFAULT 'foo'", "'foo'", "'foo'"),
+    ("TEXT DEFAULT 'foo)'", "'foo)'", "'foo)'"),
+    ("INTEGER DEFAULT '1'", "'1'", "'1'"),
+    ("INTEGER DEFAULT 1", "1", "'1'"),
+    ("INTEGER DEFAULT (1)", "1", "'1'"),
+    # Expressions
+    (
+        "TEXT DEFAULT (STRFTIME('%Y-%m-%d %H:%M:%f', 'NOW'))",
+        "STRFTIME('%Y-%m-%d %H:%M:%f', 'NOW')",
+        "(STRFTIME('%Y-%m-%d %H:%M:%f', 'NOW'))",
+    ),
+    # Special values
+    ("TEXT DEFAULT CURRENT_TIME", "CURRENT_TIME", "CURRENT_TIME"),
+    ("TEXT DEFAULT CURRENT_DATE", "CURRENT_DATE", "CURRENT_DATE"),
+    ("TEXT DEFAULT CURRENT_TIMESTAMP", "CURRENT_TIMESTAMP", "CURRENT_TIMESTAMP"),
+    ("TEXT DEFAULT current_timestamp", "current_timestamp", "current_timestamp"),
+    ("TEXT DEFAULT (CURRENT_TIMESTAMP)", "CURRENT_TIMESTAMP", "CURRENT_TIMESTAMP"),
+    # Strings
+    ("TEXT DEFAULT 'CURRENT_TIMESTAMP'", "'CURRENT_TIMESTAMP'", "'CURRENT_TIMESTAMP'"),
+    ('TEXT DEFAULT "CURRENT_TIMESTAMP"', '"CURRENT_TIMESTAMP"', '"CURRENT_TIMESTAMP"'),
+]
+
+
+@pytest.mark.parametrize("column_def,initial_value,expected_value", EXAMPLES)
+def test_quote_default_value(fresh_db, column_def, initial_value, expected_value):
+    fresh_db.execute("create table foo (col {})".format(column_def))
+    assert initial_value == fresh_db["foo"].columns[0].default_value
+    assert expected_value == fresh_db.quote_default_value(
+        fresh_db["foo"].columns[0].default_value
+    )
diff --git a/tests/test_upsert.py b/tests/test_upsert.py
index cef8af0..b7267ea 100644
--- a/tests/test_upsert.py
+++ b/tests/test_upsert.py
@@ -28,6 +28,16 @@ def test_upsert_all_single_column(fresh_db):
     assert table.pks == ["name"]
 
 
+def test_upsert_all_not_null(fresh_db):
+    # https://github.com/simonw/sqlite-utils/issues/538
+    fresh_db["comments"].upsert_all(
+        [{"id": 1, "name": "Cleo"}],
+        pk="id",
+        not_null=["name"],
+    )
+    assert list(fresh_db["comments"].rows) == [{"id": 1, "name": "Cleo"}]
+
+
 def test_upsert_error_if_no_pk(fresh_db):
     table = fresh_db["table"]
     with pytest.raises(PrimaryKeyRequired):