0001-wip-.import-nulls.patch

From 10bc706b2630cb9f607f2b5d55923fdfc7c6da6e Mon Sep 17 00:00:00 2001
From: Thomas Sibley <tsibley@fredhutch.org>
Date: Tue, 18 Jun 2024 13:27:03 -0700
Subject: [PATCH] wip! .import --nulls

---
 src/shell.c.in | 10 +++++++++-
 1 file changed, 9 insertions(+), 1 deletion(-)

diff --git a/src/shell.c.in b/src/shell.c.in
index 58475dd6d..117e38661 100644
--- a/src/shell.c.in
+++ b/src/shell.c.in
@@ -4729,6 +4729,7 @@ static const char *(azHelp[]) = {
   "   Options:",
   "     --ascii               Use \\037 and \\036 as column and row separators",
   "     --csv                 Use , and \\n as column and row separators",
+  "     --nulls               Replace \".nullvalue\" string in input with nulls",
   "     --skip N              Skip the first N rows of input",
   "     --schema S            Target table to be S.TABLE",
   "     -v                    \"Verbose\" - increase auxiliary output",
@@ -5779,6 +5780,7 @@ struct ImportCtx {
   int cTerm;          /* Character that terminated the most recent field */
   int cColSep;        /* The column separator character.  (Usually ",") */
   int cRowSep;        /* The row separator character.  (Usually "\n") */
+  const char *zNullValue;     /* The null value string, if --nulls is given.  (Usually "") */
 };
 
 /* Clean up resourced used by an ImportCtx */
@@ -8844,6 +8846,8 @@ static int do_meta_command(char *zLine, ShellState *p){
         sCtx.cRowSep = '\n';
         xRead = csv_read_one_field;
         useOutputMode = 0;
+      }else if( cli_strcmp(z,"-nulls")==0 ){
+        sCtx.zNullValue = p->nullValue;
       }else{
         oputf("ERROR: unknown option: \"%s\".  Usage:\n", z);
         showHelp(p->out, "import");
@@ -9064,7 +9068,11 @@ static int do_meta_command(char *zLine, ShellState *p){
         if( z==0 && (xRead==csv_read_one_field) && i==nCol-1 && i>0 ){
           z = "";
         }
-        sqlite3_bind_text(pStmt, i+1, z, -1, SQLITE_TRANSIENT);
+        if( sCtx.zNullValue!=0 && cli_strcmp(z,sCtx.zNullValue)==0 ){
+            sqlite3_bind_null(pStmt, i+1);
+        }else{
+            sqlite3_bind_text(pStmt, i+1, z, -1, SQLITE_TRANSIENT);
+        }
         if( i<nCol-1 && sCtx.cTerm!=sCtx.cColSep ){
           eputf("%s:%d: expected %d columns but found %d"
                 " - filling the rest with NULL\n",
-- 
2.45.1

https://sqlite.org/forum/forumpost/5ae1a28b59f76a02