やってみる

アウトプットすべく己を導くためのブログ。その試行錯誤すらたれ流す。

📌 ？
トップ > SQLite3 > SQLite3ドットコマンド（.help）

SQLite3ドットコマンド（.help）

SQLite3

　一部SQLite3博士にしかわかならいヘルプがある。助けて。

成果物

.help

.help ?-all? ?PATTERN?   Show help text for PATTERN

.help

?PATTERN?

sqlite3 :memory: ".help back"
.backup ?DB? FILE        Backup DB (default "main") to FILE
       --append            Use the appendvfs
       --async             Write to FILE without a journal and without fsync()

?-all?

sqlite3 :memory: ".help -all"
.archive ...             Manage SQL archives
   Each command must have exactly one of the following options:
     -c, --create               Create a new archive
     -u, --update               Add files or update files with changed mtime
     -i, --insert               Like -u but always add even if mtime unchanged
     -t, --list                 List contents of archive
     -x, --extract              Extract files from archive
   Optional arguments:
     -v, --verbose              Print each filename as it is processed
     -f FILE, --file FILE       Operate on archive FILE (default is current db)
     -a FILE, --append FILE     Operate on FILE opened using the apndvfs VFS
     -C DIR, --directory DIR    Change to directory DIR to read/extract files
     -n, --dryrun               Show the SQL that would have occurred
   Examples:
     .ar -cf archive.sar foo bar  # Create archive.sar from files foo and bar
     .ar -tf archive.sar          # List members of archive.sar
     .ar -xvf archive.sar         # Verbosely extract files from archive.sar
   See also:
      http://sqlite.org/cli.html#sqlar_archive_support
.auth ON|OFF             Show authorizer callbacks
.backup ?DB? FILE        Backup DB (default "main") to FILE
       --append            Use the appendvfs
       --async             Write to FILE without a journal and without fsync()
.bail on|off             Stop after hitting an error.  Default OFF
.binary on|off           Turn binary output on or off.  Default OFF
.cd DIRECTORY            Change the working directory to DIRECTORY
.changes on|off          Show number of rows changed by SQL
.check GLOB              Fail if output since .testcase does not match
.clone NEWDB             Clone data into NEWDB from the existing database
.databases               List names and files of attached databases
.dbconfig ?op? ?val?     List or change sqlite3_db_config() options
.dbinfo ?DB?             Show status information about the database
.dump ?TABLE? ...        Render all database content as SQL
   Options:
     --preserve-rowids      Include ROWID values in the output
     --newlines             Allow unescaped newline characters in output
   TABLE is a LIKE pattern for the tables to dump
.echo on|off             Turn command echo on or off
.eqp on|off|full|...     Enable or disable automatic EXPLAIN QUERY PLAN
   Other Modes:
      trigger               Like "full" but also show trigger bytecode
.excel                   Display the output of next command in a spreadsheet
.exit ?CODE?             Exit this program with return-code CODE
.expert                  EXPERIMENTAL. Suggest indexes for specified queries
.filectrl CMD ...        Run various sqlite3_file_control() operations
                           Run ".filectrl" with no arguments for details
.fullschema ?--indent?   Show schema and the content of sqlite_stat tables
.headers on|off          Turn display of headers on or off
.help ?-all? ?PATTERN?   Show help text for PATTERN
.import FILE TABLE       Import data from FILE into TABLE
.imposter INDEX TABLE    Create imposter table TABLE on index INDEX
.indexes ?TABLE?         Show names of indexes
                           If TABLE is specified, only show indexes for
                           tables matching TABLE using the LIKE operator.
.limit ?LIMIT? ?VAL?     Display or change the value of an SQLITE_LIMIT
.lint OPTIONS            Report potential schema issues.
     Options:
        fkey-indexes     Find missing foreign key indexes
.load FILE ?ENTRY?       Load an extension library
.log FILE|off            Turn logging on or off.  FILE can be stderr/stdout
.mode MODE ?TABLE?       Set output mode
   MODE is one of:
     ascii    Columns/rows delimited by 0x1F and 0x1E
     csv      Comma-separated values
     column   Left-aligned columns.  (See .width)
     html     HTML <table> code
     insert   SQL insert statements for TABLE
     line     One value per line
     list     Values delimited by "|"
     quote    Escape answers as for SQL
     tabs     Tab-separated values
     tcl      TCL list elements
.nullvalue STRING        Use STRING in place of NULL values
.once (-e|-x|FILE)       Output for the next SQL command only to FILE
     If FILE begins with '|' then open as a pipe
     Other options:
       -e    Invoke system text editor
       -x    Open in a spreadsheet
.open ?OPTIONS? ?FILE?   Close existing database and reopen FILE
     Options:
        --append        Use appendvfs to append database to the end of FILE
        --deserialize   Load into memory useing sqlite3_deserialize()
        --hexdb         Load the output of "dbtotxt" as an in-memory database
        --maxsize N     Maximum size for --hexdb or --deserialized database
        --new           Initialize FILE to an empty database
        --readonly      Open FILE readonly
        --zip           FILE is a ZIP archive
.output ?FILE?           Send output to FILE or stdout if FILE is omitted
     If FILE begins with '|' then open it as a pipe.
.parameter CMD ...       Manage SQL parameter bindings
   clear                   Erase all bindings
   init                    Initialize the TEMP table that holds bindings
   list                    List the current parameter bindings
   set PARAMETER VALUE     Given SQL parameter PARAMETER a value of VALUE
                           PARAMETER should start with '$', ':', '@', or '?'
   unset PARAMETER         Remove PARAMETER from the binding table
.print STRING...         Print literal STRING
.progress N              Invoke progress handler after every N opcodes
   --limit N                 Interrupt after N progress callbacks
   --once                    Do no more than one progress interrupt
   --quiet|-q                No output except at interrupts
   --reset                   Reset the count for each input and interrupt
.prompt MAIN CONTINUE    Replace the standard prompts
.quit                    Exit this program
.read FILE               Read input from FILE
.recover                 Recover as much data as possible from corrupt db.
.restore ?DB? FILE       Restore content of DB (default "main") from FILE
.save FILE               Write in-memory database into FILE
.scanstats on|off        Turn sqlite3_stmt_scanstatus() metrics on or off
.schema ?PATTERN?        Show the CREATE statements matching PATTERN
     Options:
         --indent            Try to pretty-print the schema
.selftest ?OPTIONS?      Run tests defined in the SELFTEST table
    Options:
       --init               Create a new SELFTEST table
       -v                   Verbose output
.separator COL ?ROW?     Change the column and row separators
.session ?NAME? CMD ...  Create or control sessions
   Subcommands:
     attach TABLE             Attach TABLE
     changeset FILE           Write a changeset into FILE
     close                    Close one session
     enable ?BOOLEAN?         Set or query the enable bit
     filter GLOB...           Reject tables matching GLOBs
     indirect ?BOOLEAN?       Mark or query the indirect status
     isempty                  Query whether the session is empty
     list                     List currently open session names
     open DB NAME             Open a new session on DB
     patchset FILE            Write a patchset into FILE
   If ?NAME? is omitted, the first defined session is used.
.sha3sum ...             Compute a SHA3 hash of database content
    Options:
      --schema              Also hash the sqlite_master table
      --sha3-224            Use the sha3-224 algorithm
      --sha3-256            Use the sha3-256 algorithm.  This is the default.
      --sha3-384            Use the sha3-384 algorithm
      --sha3-512            Use the sha3-512 algorithm
    Any other argument is a LIKE pattern for tables to hash
.shell CMD ARGS...       Run CMD ARGS... in a system shell
.show                    Show the current values for various settings
.stats ?on|off?          Show stats or turn stats on or off
.system CMD ARGS...      Run CMD ARGS... in a system shell
.tables ?TABLE?          List names of tables matching LIKE pattern TABLE
.testcase NAME           Begin redirecting output to 'testcase-out.txt'
.testctrl CMD ...        Run various sqlite3_test_control() operations
                           Run ".testctrl" with no arguments for details
.timeout MS              Try opening locked tables for MS milliseconds
.timer on|off            Turn SQL timer on or off
.trace ?OPTIONS?         Output each SQL statement as it is run
    FILE                    Send output to FILE
    stdout                  Send output to stdout
    stderr                  Send output to stderr
    off                     Disable tracing
    --expanded              Expand query parameters
    --plain                 Show SQL as it is input
    --stmt                  Trace statement execution (SQLITE_TRACE_STMT)
    --profile               Profile statements (SQLITE_TRACE_PROFILE)
    --row                   Trace each row (SQLITE_TRACE_ROW)
    --close                 Trace connection close (SQLITE_TRACE_CLOSE)
.vfsinfo ?AUX?           Information about the top-level VFS
.vfslist                 List all available VFSes
.vfsname ?AUX?           Print the name of the VFS stack
.width NUM1 NUM2 ...     Set column widths for "column" mode
     Negative values right-justify

　.filectrlのところ詳細が出てない。.testctrlも同様。

.filectrl

sqlite3 :memory: .filectrl
Available file-controls:
  .filectrl size_limit [LIMIT]
  .filectrl chunk_size SIZE
  .filectrl persist_wal [BOOLEAN]
  .filectrl psow [BOOLEAN]
  .filectrl tempfilename 
  .filectrl has_moved 
  .filectrl lock_timeout MILLISEC

　使い方わからんかったヤツ。

.testctrl

sqlite3 :memory: .testctrl
Available test-controls:
  .testctrl always BOOLEAN
  .testctrl assert BOOLEAN
  .testctrl byteorder 
  .testctrl imposter SCHEMA ON/OFF ROOTPAGE
  .testctrl internal_functions BOOLEAN
  .testctrl localtime_fault BOOLEAN
  .testctrl never_corrupt BOOLEAN
  .testctrl optimizations DISABLE-MASK
  .testctrl pending_byte OFFSET  
  .testctrl prng_reset 
  .testctrl prng_restore 
  .testctrl prng_save 
  .testctrl reserve BYTES-OF-RESERVE

　使い方わからんかったヤツ。

所感

　一部ヘルプになってない。コマンドを表出するだけならcommand-listとでもして欲しい。説明ならexplainに。helpなら助けろ。rescueコマンドで救ってくれ。

対象環境

$ uname -a
Linux raspberrypi 4.19.42-v7+ #1218 SMP Tue May 14 00:48:17 BST 2019 armv7l GNU/Linux

前回まで