If the prompt starts with (x1 or (x2, it means you’ve opened some parentheses and not closed them yet.

While writing my previous note, I noticed an unexpected prefix in the SQLite shell:

sqlite> CREATE TABLE KeyValuePairs (
(x1...>     Key   TEXT NOT NULL PRIMARY KEY,
(x1...>     Value TEXT NOT NULL
(x1...> );

What does that (x1 prefix mean? I couldn’t find any reference to it online, so I had to read the SQLite source code.

The relevant code is in shell.c.in. First I found the default prompts, and two variables where the main and continuation prompts are stored:

406/*
407** Prompt strings. Initialized in main. Settable with
408**   .prompt main continue
409*/
410#define PROMPT_LEN_MAX 128
411/* First line prompt.   default: "sqlite> " */
412static char mainPrompt[PROMPT_LEN_MAX];
413/* Continuation prompt. default: "   ...> " */
414static char continuePrompt[PROMPT_LEN_MAX];

Looking at where those variables get used leads to another interesting snippet, which names this feature as “dynamic continuation prompt”:

444/*
445** Optionally disable dynamic continuation prompt.
446** Unless disabled, the continuation prompt shows open SQL lexemes if any,
447** or open parentheses level if non-zero, or continuation prompt as set.
448** This facility interacts with the scanner and process_input() where the
449** below 5 macros are used.
450*/
451#ifdef SQLITE_OMIT_DYNAPROMPT
452# define CONTINUATION_PROMPT continuePrompt
⋮…
460#else
461# define CONTINUATION_PROMPT dynamicContinuePrompt()

And looking for the definition of that dynamicContinuePrompt function, I can see it updating a dynPrompt.dynamicPrompt variable with expressions like the (x1 I saw in my SQLite shell:

499/* Upon demand, derive the continuation prompt to display. */
500static char *dynamicContinuePrompt(void){
501  if( continuePrompt[0]==0
502      || (dynPrompt.zScannerAwaits==0 && dynPrompt.inParenLevel == 0) ){
503    return continuePrompt;
504  }else{
505    if( dynPrompt.zScannerAwaits ){
506      size_t ncp = strlen(continuePrompt);
507      size_t ndp = strlen(dynPrompt.zScannerAwaits);
508      if( ndp > ncp-3 ) return continuePrompt;
509      shell_strcpy(dynPrompt.dynamicPrompt, dynPrompt.zScannerAwaits);
510      while( ndp<3 ) dynPrompt.dynamicPrompt[ndp++] = ' ';
511      shell_strncpy(dynPrompt.dynamicPrompt+3, continuePrompt+3,
512              PROMPT_LEN_MAX-4);
513    }else{
514      if( dynPrompt.inParenLevel>9 ){
515        shell_strncpy(dynPrompt.dynamicPrompt, "(..", 4);
516      }else if( dynPrompt.inParenLevel<0 ){
517        shell_strncpy(dynPrompt.dynamicPrompt, ")x!", 4);
518      }else{
519        shell_strncpy(dynPrompt.dynamicPrompt, "(x.", 4);
520        dynPrompt.dynamicPrompt[2] = (char)('0'+dynPrompt.inParenLevel);
521      }
522      shell_strncpy(dynPrompt.dynamicPrompt+3, continuePrompt+3,
523                    PROMPT_LEN_MAX-4);
524    }
525  }
526  return dynPrompt.dynamicPrompt;
527}
528#endif /* !defined(SQLITE_OMIT_DYNAPROMPT) */

I don’t completely understand this function, but I think I get the general gist. The first branch is looking for “open SQL lexemes”, or unterminated strings, while the second branch is counting open parentheses. I can compare this to what I see in the SQLite shell:

• If you have between 1 to 9 unclosed parentheses, the prompt starts with (x and the number of unclosed parens:

  sqlite> (
  (x1...>
  sqlite> (((((((((
  (x9...>

• If you have 10 or more unclosed parentheses, the prompt starts with prints (..:

  sqlite> ((((((((((
  (x.....>

• If you have more closed parentheses than you've opened, the prompt starts with prints )x!:

  sqlite> )))
  )x!...>

If you’re in this state, I’m not sure if it's ever possible to get back to a valid SQL expression?

• If you have an unfinished string, square bracket, or multi-line comment, the prompt starts with the quote character you need to close the string:

  sqlite> SELECT '
  '  ...> hello world
  '  ...> '
  
  hello world
  
  sqlite> SELECT "
  "  ...> hello world
  "  ...> "
  
  hello world
  
  sqlite> [
  [  ...> 
  
  sqlite> /*
  /* ...> 
  

I wonder where this behaviour came from? It feels like the sort of thing that might have come from Lisp, which is famous for having lots of brackets and exactly where this sort of indicator might be useful, whereas I imagine writing a heavily nested expression in the SQLite shell interface is comparatively rare.