Skip to contents

Using SQLite Command Line

Source: vignettes/dev_Using_SQLite_Command_Line.Rmd
dev_Using_SQLite_Command_Line.Rmd

About SQLite

SQLite is a software library that provides a relational database management system. The lite in SQLite means lightweight in terms of setup, database administration, and required resources.

SQLite has the following noticeable features: self-contained, serverless, zero-configuration, transactional.

For more information, see What is SQLite

Why SQLite?

For one obvious reason – all the riskassessment App data is stored in a SQLite database. Also, one of SQLite’s advantages is that it can run nearly anywhere. This vignette will provide you with the means of accessing the database outside of the riskassessment App.

Download SQLite tools

Open the download page SQLite Download Page

To work with SQLite on Windows, you download the command-line shell program as shown in the screenshot below.

If you are using a Mac, you need to download this one: In either case, the downloaded file is in ZIP format and its size is quite small.

Run SQLite tools

Installing SQLite is simple and straightforward.

Create a new folder e.g., C:\sqlite.

Extract the content of the .zip file that you downloaded in the previous section to the C:\sqlite folder. You should see three programs as shown below:

Open the command line window.

On Windows, type “cmd” in the search bar.

Navigate to the C:\sqlite folder. 

C:\cd c:\sqlite
C:\sqlite>

Next, type sqlite3 and press enter, you should see the following output:

C:\sqlite>sqlite3

SQLite version 3.33.0 2020-08-14 13:23:32

Enter ".help" for usage hints.

Connected to a transient in-memory database.

Use ".open FILENAME" to reopen on a persistent database.

sqlite>

Navigate to the directory containing the riskassessment database. Note: the app’s default behavior is to call this database “database.sqlite”, unless you’ve specified otherwise using the assessment_db_name arg in run_app(). Next, use the .open FILENAME command to open it. For example:

.open database.sqlite;

To show the tables, type .tables

sqlite> .tables
comments                 metric                   package_metrics
community_usage_metrics  package
sqlite>

To show the structure of a table, type .schema TABLE

sqlite> .schema package_metrics
CREATE TABLE package_metrics (   id           INTEGER PRIMARY KEY AUTOINCREMENT,   package_id   INT,    metric_id    INT,   value        CHAR,   /* value == 'pkg_metric_error' indicates an error. */   /* value == NA indicates metric is not applicable for this package. */   weight       REAL,   FOREIGN KEY (package_id) REFERENCES package(id),   FOREIGN KEY (metric_id) REFERENCES metric(id));
sqlite>

You can also type .help from the sqlite> prompt to see all available commands.

sqlite> .help
.archive ...             Manage SQL archives
.auth ON|OFF             Show authorizer callbacks
.backup ?DB? FILE        Backup DB (default "main") to FILE
.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 database content as SQL
.echo on|off             Turn command echo on or off
.eqp on|off|full|...     Enable or disable automatic EXPLAIN QUERY PLAN
.excel                   Display the output of next command in spreadsheet
...
additional output not displayed

To exit, type .quitor .exit

Command Line Shell

You can also temporarily add C:\sqlite to the Windows path.

set PATH=%PATH%;C:\sqlite

Verify by typing

echo %PATH%

Then when you are in the riskassessment directory, you can just enter the following on the command line:

sqlite3 database.sqlite and then you will see

SQLite version 3.33.0 2020-08-14 13:23:32
Enter ".help" for usage hints.
sqlite>

You can also use the system2 command to execute SQLite commands on the RStudio command line.

> system2("C:/sqlite/sqlite3", args =c("database.sqlite", ".tables", ".quit"))
comments                 metric                   package_metrics        
community_usage_metrics  package

And you can build queries or commands for SQLite and execute them.

> table_name <- "package"
> query <- glue::glue("select * from { table_name } limit 1;")
> frst_row <- system2("C:/sqlite/sqlite3", args ="database.sqlite", input = query, stdout = TRUE)
> 
> frst_row
[1] "1|glue|1.6.2|glue: Interpreted String Literals|An implementation of interpreted string literals, inspired by   Pythons Literal String Interpolation   <https://www.python.org/dev/peps/pep-0498/> and Docstrings   <https://www.python.org/dev/peps/pep-0257/> and Julias Triple-Quoted   String Literals   <https://docs.julialang.org/en/v1.3/manual/strings/#Triple-Quoted-String-Literals-1>.|Jennifer Bryan  <jenny at rstudio.com>|Jim Hester  [aut], Jennifer Bryan    [aut, cre], RStudio [cph, fnd]|MIT + file LICENSE|2022-02-24|0.11|||2022-02-24"

For more info, see Command Line Shell for SQLite

SQLiteStudio

The SQLiteStudio tool is a free GUI tool for managing SQLite databases. It is free, portable, intuitive, and cross-platform. SQLite tool also provides some of the most important features to work with SQLite databases such as importing, exporting data in various formats including CSV, XML, and JSON.

Visit SQLite Studio

You can download the SQLiteStudio installer or its portable version by visiting the download page. Then, you can extract (or install) the download file to a folder e.g., C: and launch it.

The following picture illustrates how to launch the SQLiteStudio: