Tester for foreign keys PRAGMA evaluating during prep

flake.lock

{
  "nodes": {
    "flake-utils": {
      "inputs": {
        "systems": "systems"
      },
      "locked": {
        "lastModified": 1689068808,
        "narHash": "sha256-6ixXo3wt24N/melDWjq70UuHQLxGV8jZvooRanIHXw0=",
        "owner": "numtide",
        "repo": "flake-utils",
        "rev": "919d646de7be200f3bf08cb76ae1f09402b6f9b4",
        "type": "github"
      },
      "original": {
        "id": "flake-utils",
        "type": "indirect"
      }
    },
    "nixpkgs": {
      "locked": {
        "lastModified": 1692067901,
        "narHash": "sha256-kq8Pf/nmlXECDWMkQSRGQkjWsA6G0pjzZkfUEaTmXJE=",
        "owner": "NixOS",
        "repo": "nixpkgs",
        "rev": "ea95c0917609e5c48023cc7c6141bea2fdf13970",
        "type": "github"
      },
      "original": {
        "id": "nixpkgs",
        "type": "indirect"
      }
    },
    "root": {
      "inputs": {
        "flake-utils": "flake-utils",
        "nixpkgs": "nixpkgs"
      }
    },
    "systems": {
      "locked": {
        "lastModified": 1681028828,
        "narHash": "sha256-Vy1rq5AaRuLzOxct8nz4T6wlgyUR7zLU309k9mBC768=",
        "owner": "nix-systems",
        "repo": "default",
        "rev": "da67096a3b9bf56a91d16901293e51ba5b49a27e",
        "type": "github"
      },
      "original": {
        "owner": "nix-systems",
        "repo": "default",
        "type": "github"
      }
    }
  },
  "root": "root",
  "version": 7
}

flake.nix

{
  inputs = {
    nixpkgs.url = "nixpkgs";
    flake-utils.url = "flake-utils";
  };

  outputs = { self, nixpkgs, flake-utils, ... }:
    flake-utils.lib.eachDefaultSystem (system:
      let
        pkgs = import nixpkgs { inherit system; };
      in {
        packages.default = pkgs.stdenv.mkDerivation {
          name = "foo";
          buildInputs = [ pkgs.sqlite.dev ];

          src = builtins.path {
            name = "foo-source";
            path = ./.;
            filter = (path: _: pkgs.lib.hasSuffix ".c" path);
          };

          buildPhase = ''
            $CC -o foo foo.c -lsqlite3
          '';

          installPhase = ''
            mkdir -p $out/bin
            cp foo $out/bin/foo
          '';
        };
      }
    );
}

foo.c

#include <stdio.h>
#include <sqlite3.h>

static int showEnabled(void *data, int argc, char **argv, char** colNames) {
  if (argc == 0) {
    printf("no columns returned\n");
    return 0;
  }
  printf("enabled = %s\n", argv[0]);
  return 0;
}

int main(int argc, char *argv[]) {
  sqlite3* db;
  sqlite3_stmt* pOffStmt = NULL;
  char *zErrMsg = NULL;
  printf("sqlite version = %s\n", SQLITE_VERSION);
  if (sqlite3_open(":memory:", &db) != SQLITE_OK) {
    fprintf(stderr, "open: %s\n", sqlite3_errmsg(db));
    sqlite3_close(db);
    return 1;
  }
  if (sqlite3_exec(db, "PRAGMA foreign_keys = on;", NULL, NULL, &zErrMsg) != SQLITE_OK) {
    fprintf(stderr, "enable foreign keys: %s\n", sqlite3_errmsg(db));
    sqlite3_close(db);
    return 1;
  }
  if (sqlite3_exec(db, "PRAGMA foreign_keys;", showEnabled, NULL, &zErrMsg) != SQLITE_OK) {
    fprintf(stderr, "test foreign keys: %s\n", sqlite3_errmsg(db));
    sqlite3_close(db);
    return 1;
  }

  /*
    Sigh. As per <https://www.sqlite.org/pragma.html>,
    "Some pragmas take effect during the SQL compilation stage, not the execution stage."
  */
  if (sqlite3_prepare_v3(db, "PRAGMA foreign_keys = off;", -1, 0, &pOffStmt, NULL) != SQLITE_OK) {
    fprintf(stderr, "prep turning off foreign keys: %s\n", sqlite3_errmsg(db));
    sqlite3_close(db);
  }
  if (sqlite3_exec(db, "PRAGMA foreign_keys;", showEnabled, NULL, &zErrMsg) != SQLITE_OK) {
    fprintf(stderr, "test foreign keys: %s\n", sqlite3_errmsg(db));
    sqlite3_close(db);
    return 1;
  }
  sqlite3_close(db);
  return 0;
}