Here we will set the mode and column widths for our table.

sqlite> .mode column 
sqlite> .width 20 60 
sqlite> SELECT * FROM Package_Descriptions LIMIT 10;
package_name          description                                                 
--------------------  ---------------------------------------------------------
a2ps                  GNU a2ps - 'Anything to PostScript' converter and pretty-
accountsservice       query and manipulate user account information               
acl                   Access control list utilities                               
acpi-support          scripts for handling many ACPI events                       
acpid                 Advanced Configuration and Power Interface event daemon     
adduser               add and remove users and groups                             
adium-theme-ubuntu    Adium message style for Ubuntu                              
adwaita-icon-theme    default icon theme of GNOME (small subset)                  
aisleriot             GNOME solitaire card game collection                        
alsa-base             ALSA driver configuration files                             

In addition to listing columns, SELECT can be used to perform various output tasks. For example, we can perform calculations such as counting the number of rows in the Package_Descriptions table.

sqlite> SELECT COUNT(package_name) FROM Package_Descriptions;
count(package_name) 
--------------------
1972                

Being Selective

We can make SELECT output rows based on some selection criteria. Either an exact match:

sqlite> SELECT * FROM Package_Descriptions WHERE package_name = 'bash';
package_name          description                                                 
--------------------  --------------------------------------------------------
bash                  GNU Bourne Again SHell                                  

A partial match using SQL wildcard characters:

sqlite> SELECT * FROM Package_Descriptions WHERE description LIKE '%bash%';
package_name          description
--------------------  --------------------------------------------------------
bash-completion       programmable completion for the bash shell
command-not-found     Suggest installation of packages in interactive bash ses

SQL supports two wildcard characters. The underscore (_), which matches any single character, and the percent sign (%), which matches zero or more instances of any character.

Notice too that strings are surrounded with single quotes. If a value is quoted this way, SQL treats it as a string. For example, the value '123' would be treated as a string rather than a number.

Sorting Output

Unless we tell SELECT to sort our data, it will be listed in the order it was inserted into the table. Our data appears in alphabetical order by package name because it was inserted that way, not because of anything SQLite is doing. The ORDER BY clause can be added to determine which column is used for sorting. To demonstrate, let’s sort the output by the description,

sqlite> SELECT * FROM Package_Descriptions ORDER BY description LIMIT 10;
package_name          description
--------------------  -------------------------------------------------------
network-manager-conf
fonts-noto-cjk        "No Tofu" font families with large Unicode coverage (CJ
fonts-noto-mono       "No Tofu" monospaced font family with large Unicode cov
udev                  /dev/ and hotplug management daemon
procps                /proc file system utilities
alsa-base             ALSA driver configuration files
libasound2-plugins:a  ALSA library additional plugins
libhyphen0:amd64      ALTLinux hyphenation library - shared library
apcupsd               APC UPS Power Management (daemon)
apcupsd-doc           APC UPS Power Management (documentation/examples)

The default sorting order is ascending, but we can also sort in descending order by including DESC after the column name. Multiple columns can be named and ASC can be used to specify ascending order.

sqlite> SELECT * FROM Package_Descriptions ORDER BY description DESC LIMIT 10;
package_name  description                                       
------------  --------------------------------------------------
xsane-common  xsane architecture independent files              
libx264-152:  x264 video coding library                         
libevdev2:am  wrapper library for evdev devices                 
wireless-reg  wireless regulatory database                      
crda          wireless Central Regulatory Domain Agent          
libmutter-2-  window manager library from the Mutter window mana
libwayland-s  wayland compositor infrastructure - server library
libwayland-c  wayland compositor infrastructure - cursor library
libwayland-c  wayland compositor infrastructure - client library
libwayland-e  wayland compositor infrastructure - EGL library   

Adding Another Table

To demonstrate more of what we can do with SELECT, we’re going to need a bigger database. We have a second .tsv file that we can add. To save a step, we’ll filter the file into SQL and pipe it directly into sqlite3.

me@linuxbox:~/advemture-sql$ ./insert_package_files.awk \ 
                                < package_files-deb.tsv \
                                | sqlite3 adv-sql.sqlite

The second table is named Package_Files. Here is its schema:

sqlite> .schema Package_Files
CREATE TABLE Package_Files (
    package_name VARCHAR(60),
    file         VARCHAR(120),
    size_bytes   INTEGER
);

As we can see, this table has 3 columns; the package name, the name of a file installed by the package, and the size of the installed file in bytes. Let’s do a SELECT to see how this table works.

sqlite> .headers on
sqlite> .mode column
sqlite> .width 15 50 -10
sqlite> SELECT * FROM Package_Files WHERE package_name = 'bash';
package_name     file                                                size_bytes
---------------  -------------------------------------------------  ----------
bash             /bin/bash                                             1113504
bash             /etc/bash.bashrc                                         2319
bash             /etc/skel/.bash_logout                                    220
bash             /etc/skel/.bashrc                                        3771
bash             /etc/skel/.profile                                        807
bash             /usr/bin/bashbug                                         7115
bash             /usr/bin/clear_console                                  10312
bash             /usr/share/doc/bash/COMPAT.gz                            7853
bash             /usr/share/doc/bash/INTRO.gz                             2921
bash             /usr/share/doc/bash/NEWS.gz                             27983
bash             /usr/share/doc/bash/POSIX.gz                             3702
bash             /usr/share/doc/bash/RBASH                                1693
bash             /usr/share/doc/bash/README                               3839
bash             /usr/share/doc/bash/README.Debian.gz                     1919
bash             /usr/share/doc/bash/README.abs-guide                     1105
bash             /usr/share/doc/bash/README.commands.gz                   3021
bash             /usr/share/doc/bash/changelog.Debian.gz                  1357
bash             /usr/share/doc/bash/copyright                           10231
bash             /usr/share/doc/bash/inputrc.arrows                        727
bash             /usr/share/lintian/overrides/bash                         156
bash             /usr/share/man/man1/bash.1.gz                           86656
bash             /usr/share/man/man1/bashbug.1.gz                          804
bash             /usr/share/man/man1/clear_console.1.gz                   1194
bash             /usr/share/man/man1/rbash.1.gz                            154
bash             /usr/share/man/man7/bash-builtins.7.gz                    508
bash             /usr/share/menu/bash                                      194
bash             /bin/rbash                                                  4

Notice the .width dot command above. A negative value causes the corresponding column to be right-aligned.

Subqueries

The SELECT statement can be used to produce many kinds of output. For example, it can be used to simply print literal strings.

sqlite> .mode column
sqlite> .header off
sqlite> SELECT 'String 1', 'String 2';
string 1    string 2

As we saw before, SELECT can produce calculated values.

sqlite> .header on
sqlite> SELECT 2 + 2;
2 + 2
--------------------
4
sqlite> SELECT COUNT(file), AVG(size_bytes) FROM Package_Files;
count(file)  avg(size_bytes)
-----------  ----------------
153506       33370.3488658424

To make complicated expressions more readable, we can assign their results to aliases by using the AS clause.

sqlite> SELECT COUNT(file) AS Files,
   ...> AVG(size_bytes) AS 'Average Size'
   ...> FROM Package_Files;
Files       Average Size    
----------  ----------------
153506      33370.3488658424

An important feature of SELECT is the ability to produce results by combining data from multiple tables. This process is done by performing joins and subqueries. We’ll talk about joins a little later, but for now let’s concentrate on subqueries. SELECT allows us to include another SELECT statement as an item to output. To demonstrate this, we will produce a table that includes columns for package name, number of files in the package, and the total size of the package. The SELECT statement to do this is rather formidable. We’ll open our text editor and create a file named subquery_demo1.sql with the following content:

-- subquery_demo1.sql

-- Query to list top 20 packages with the greatest numbers of files

.mode column
.header on
.width 20 40 10 10
SELECT package_name, description,
       (SELECT COUNT(file)
          FROM Package_Files
         WHERE Package_Descriptions.package_name = Package_Files.package_name)
            AS files,
       (SELECT SUM(size_bytes)
          FROM Package_Files
         WHERE Package_Descriptions.package_name = Package_Files.package_name)
            AS size
  FROM Package_Descriptions ORDER BY files DESC LIMIT 20;

We’ll next run this query and view the results.

me@linuxbox:~/adventure-sql$ sqlite3 adv-sql.sqlite < subquery_demo1.sql
package_name          description                       files       size      
--------------------  --------------------------------  ----------  ----------
linux-headers-4.15.0  Header files related to Linux ke  14849       63991787  
linux-headers-4.15.0  Header files related to Linux ke  14849       64001943  
humanity-icon-theme   Humanity Icon theme               8014        14213715  
linux-headers-4.15.0  Linux kernel headers for version  7861        9015084   
linux-headers-4.15.0  Linux kernel headers for version  7860        9025673   
linux-modules-extra-  Linux kernel extra modules for v  4173        165921470 
linux-modules-extra-  Linux kernel extra modules for v  4172        165884678 
libreoffice-common    office productivity suite -- arc  3551        76686149  
gnome-accessibility-  High Contrast GTK+ 2 theme and i  3464        3713621   
ubuntu-mono           Ubuntu Mono Icon theme            3025        3755093   
ncurses-term          additional terminal type definit  2727        1987483   
manpages-dev          Manual pages about using GNU/Lin  2101        2192620   
linux-firmware        Firmware for Linux kernel driver  1938        331497257 
tzdata                time zone and daylight-saving ti  1834        1210058   
vim-runtime           Vi IMproved - Runtime files       1642        27941732  
codium                Code editing. Redefined.          1307        271907088 
zsh-common            architecture independent files f  1256        12261077  
perl-modules-5.26     Core Perl modules                 1144        18015966  
adwaita-icon-theme    default icon theme of GNOME (sma  1127        4848678   
gimp-data             Data files for GIMP               1032        45011675

The query takes some time to run (it has a lot to do) and from the results we see that it produces 4 columns: package name, description, number of files in the package, and total size of the package. Let’s take this query apart and see how it works. At the uppermost level we see that the query follows the normal pattern of a SELECT statement.

SELECT list_of_items FROM Package_Descriptions
ORDER BY total_files DESC
LIMIT 20;

The basic structure is simple. What’s interesting is the list_of_items part. We know the list of items is a comma-separated list of items to output, so if we follow the commas we can see the list:

1. package_name
2. description
3. (SELECT COUNT(file) FROM Package_Files WHERE Package_Descriptions.package_name = Package_Files.package_name) AS files
4. (SELECT SUM(size_bytes) FROM Package_Files WHERE Package_Descriptions.package_name = Package_Files.package_name) AS size

It’s also possible to use a subquery in a WHERE clause. Consider this query that we will name subquery_demo2.sql:

-- subquery_demo2.sql

-- Query to list all packages containing more than 1000 files

.mode column
.header on
.width 20 60
SELECT package_name, description
  FROM Package_Descriptions
 WHERE 1000 < (SELECT COUNT(file)
          FROM Package_Files
         WHERE Package_Descriptions.package_name = Package_Files.package_name)
 ORDER BY package_name;

When we execute this, we get the following results:

me@linuxbox:~/adventure-sql$ sqlite3 adv-sql.sqlite < subquery_demo2.sql
package_name          description                                                 
--------------------  --------------------------------------------------------
adwaita-icon-theme    default icon theme of GNOME (small subset)              
codium                Code editing. Redefined.                                
gimp-data             Data files for GIMP                                     
gnome-accessibility-  High Contrast GTK+ 2 theme and icons                    
humanity-icon-theme   Humanity Icon theme                                     
inkscape              vector-based drawing program                            
libreoffice-common    office productivity suite -- arch-independent files     
linux-firmware        Firmware for Linux kernel drivers                       
linux-headers-4.15.0  Header files related to Linux kernel version 4.15.0     
linux-headers-4.15.0  Linux kernel headers for version 4.15.0 on 64 bit x86 SM
linux-headers-4.15.0  Header files related to Linux kernel version 4.15.0     
linux-headers-4.15.0  Linux kernel headers for version 4.15.0 on 64 bit x86 SM
linux-modules-4.15.0  Linux kernel extra modules for version 4.15.0 on 64 bit 
linux-modules-4.15.0  Linux kernel extra modules for version 4.15.0 on 64 bit 
linux-modules-extra-  Linux kernel extra modules for version 4.15.0 on 64 bit 
linux-modules-extra-  Linux kernel extra modules for version 4.15.0 on 64 bit 
manpages-dev          Manual pages about using GNU/Linux for development      
ncurses-term          additional terminal type definitions                     
perl-modules-5.26     Core Perl modules                                        
tzdata                time zone and daylight-saving time data                  
ubuntu-mono           Ubuntu Mono Icon theme                                   
vim-runtime           Vi IMproved - Runtime files                              
zsh-common            architecture independent files for Zsh

Updating Tables

The UPDATE statement is used to change values in one or more existing rows. We will demonstrate this by adding 100 to the size of each file in the sqlite3 package. First, let’s look at the files in the package.

sqlite> .mode column
sqlite> .header on
sqlite> .width 50 -10
sqlite> SELECT file, size_bytes FROM Package_Files
   ...> WHERE package_name = 'sqlite3';
file                                                size_bytes
--------------------------------------------------  ----------
/usr/bin/sqldiff                                       1103280
/usr/bin/sqlite3                                       1260976
/usr/share/doc/sqlite3/copyright                          1261
/usr/share/man/man1/sqlite3.1.gz                          3596
/usr/share/doc/sqlite3/changelog.Debian.gz                  35

Next, we’ll update the table, adding 100 to the size of each file.

sqlite> UPDATE Package_Files SET size_bytes = size_bytes + 100
   ...> WHERE package_name = 'sqlite3';

When we examine the rows now, we see the change.

sqlite> SELECT file, size_bytes FROM Package_Files
   ...> WHERE package_name = 'sqlite3';
file                                                size_bytes
--------------------------------------------------  ----------
/usr/bin/sqldiff                                       1103380
/usr/bin/sqlite3                                       1261076
/usr/share/doc/sqlite3/copyright                          1361
/usr/share/man/man1/sqlite3.1.gz                          3696
/usr/share/doc/sqlite3/changelog.Debian.gz                 135

Finally, we’ll subtract 100 from each row to return the sizes to their original values.

sqlite> UPDATE Package_Files SET size_bytes = size_bytes - 100
   ...> WHERE package_name = 'sqlite3';

UPDATE can modify multiple values at once. To demonstrate this, we will create a new table called Package_Stats and use UPDATE to fill in the values. Since this one is a little complicated, we will put this in a file named create_Package_Stats.sql.

-- create_Package_Stats.sql

DROP TABLE IF EXISTS Package_Stats;
CREATE TABLE Package_Stats (
    package_name  VARCHAR(60),
    count         INTEGER,
    tot_size      INTEGER,
    min_size      INTEGER,
    max_size      INTEGER,
    avg_size      REAL
);

INSERT INTO Package_Stats (package_name)
     SELECT package_name
       FROM Package_Descriptions;

UPDATE Package_Stats
   SET count = (SELECT COUNT(file)
                  FROM Package_Files
                 WHERE Package_Files.package_name =
                       Package_Stats.package_name),
    tot_size = (SELECT SUM(size_bytes)
                  FROM Package_Files
                 WHERE Package_Files.package_name = 
                       Package_Stats.package_name),
    min_size = (SELECT MIN(size_bytes)
                  FROM Package_Files
                 WHERE Package_Files.package_name = 
                       Package_Stats.package_name),
    max_size = (SELECT MAX(size_bytes)
                  FROM Package_Files
                 WHERE Package_Files.package_name = 
                       Package_Stats.package_name),
    avg_size = (SELECT AVG(size_bytes)
                  FROM Package_Files
                 WHERE Package_Files.package_name = 
                       Package_Stats.package_name);

This file consists of four SQL statements. The first two are used to create the new table, as we have seen before. The third statement is an alternate form of the INSERT statement. This form is useful, as it copies a value from one table into another. This INSERT will create all the rows we need but only fill in the package_name column. To fill in the rest, we will use an UPDATE that fills in the remaining five values based on the results of some queries of the Package_Files table. Note that without a WHERE clause, UPDATE applies changes to every row.

Once the table is constructed, we can examine its contents.

sqlite> .width 25 -5 -10 -8 -8 -10
sqlite> SELECT * FROM Package_Stats LIMIT 10;
package_name               count    tot_size  min_size  max_size    avg_size
-------------------------  -----  ----------  --------  --------  ----------
a2ps                         299     3455890       117    388096  11558.1605
accountsservice               19      261704        42    182552  13773.8947
acl                           11       91106        30     35512  8282.36363
acpi-support                  18       13896        67      4922       772.0
acpid                         19       86126       115     52064  4532.94736
adduser                       81      246658         7     37322  3045.16049
adium-theme-ubuntu           137      126759        25     12502  925.248175
adwaita-icon-theme          1127     4848678        30     87850  4302.28748
aisleriot                    316     1890864        47    281544  5983.74683
alsa-base                     42      195295        24     34160  4649.88095

We’ll come back to this table a little later when we take a look at joins.

Deleting Rows

Deleting rows is pretty easy in SQL. There is a DELETE statement with a WHERE clause to specify a target. We’ll demonstrate that, but first there’s a nifty trick that SQLite supports.

We can change the output mode to write out INSERT statements. Let’s try it out.

sqlite> .mode insert Package_Files

If we use this .mode setting, we tell SQLite that we want INSERT statements directed at the specified table, in this case, Package_Files. Once we set this output mode, we can see the result.

sqlite> SELECT * FROM Package_Files WHERE package_name = 'sqlite3';
INSERT INTO Package_Files VALUES('sqlite3','/usr/bin/sqldiff',1103380);
INSERT INTO Package_Files VALUES('sqlite3','/usr/bin/sqlite3',1261076);
INSERT INTO Package_Files VALUES('sqlite3','/usr/share/doc/sqlite3/copyright',1
361);
INSERT INTO Package_Files VALUES('sqlite3','/usr/share/man/man1/sqlite3.1.gz',3
696);
INSERT INTO Package_Files VALUES('sqlite3','/usr/share/doc/sqlite3/changelog.De
bian.gz',135);

We’ll repeat this SELECT, but first we’ll change the output from standard output to a file named insert_sqlite3.sql.

sqlite> .output insert_sqlite3.sql
sqlite> select * from Package_Files where package_name = 'sqlite3';

This will write the stream of INSERT statements to the specified file. Next we’ll set the output back to standard output by issuing the .output dot command without an output file name.

sqlite> .output

Now let’s delete the rows in the Package_Files table.

sqlite> DELETE FROM Package_Files WHERE package_name = 'sqlite3';

We can confirm the deletion by running our query again and we see an empty result.

sqlite> .header on
sqlite> .mode column
sqlite> .width 12 50 -10
sqlite> SELECT * FROM Package_Files WHERE package_name = 'sqlite3';
sqlite>

Since we saved an SQL stream that can restore the deleted rows, we can now put them back in the table. The .read dot command can read the stream and execute it as though it came from standard input.

sqlite> .read insert_sqlite3.sql

Now when we run our query, we see that the rows have been restored.

sqlite> SELECT * FROM Package_Files WHERE package_name = 'sqlite3';
package_name  file                                                size_bytes
------------  --------------------------------------------------  ----------
sqlite3       /usr/bin/sqldiff                                       1103280
sqlite3       /usr/bin/sqlite3                                       1260976
sqlite3       /usr/share/doc/sqlite3/copyright                          1261
sqlite3       /usr/share/man/man1/sqlite3.1.gz                          3596
sqlite3       /usr/share/doc/sqlite3/changelog.Debian.gz                  35

Adding and Deleting Columns

SQL provides the ALTER TABLE statement to modify table’s schema. To demonstrate this, we will add a couple of columns to the Package_Descriptions table and fill them with values calculated from the Package_Files table. We’ll place the necessary SQL in the add_column.sql file.

-- add_column.sql

-- Add and populate columns to Package_Descriptions

ALTER TABLE Package_Descriptions ADD COLUMN files INTEGER;
ALTER TABLE Package_Descriptions ADD COLUMN size INTEGER;

UPDATE Package_Descriptions
   SET files = (SELECT COUNT(file)
                  FROM Package_Files
                 WHERE Package_Files.package_name =
                       Package_Descriptions.package_name),
        size = (SELECT SUM(size_bytes)
                  FROM Package_Files
                 WHERE Package_Files.package_name =
                       Package_Descriptions.package_name);

We’ll execute the statements and examine resulting schema.

sqlite> .read add_column.sql
sqlite> .schema
CREATE TABLE Package_Descriptions (
    package_name VARCHAR(60),
    description  VARCHAR(120),
    files        INTEGER,
    size         INTEGER);

sqlite> SELECT * FROM Package_Descriptions WHERE package_name = 'sqlite3';
package_name  description                          files       size      
------------  -----------------------------------  ----------  ----------
sqlite3       Command line interface for SQLite 3  5           2369648 

SQL provides another ALTER TABLE statement for deleting columns from a table. It has the following form:

ALTER TABLE table_name DROP COLUMN column_name;

Unfortunately, SQLite does not support this so we have to do it the hard way. This is accomplished in four steps:

1. Create a new temporary table with the desired schema.
2. Copy the columns we want to keep into the temporary table.
3. Drop the original table.
4. Rename the temporary table.

Here is a file called drop_column.sql that does the job.

-- drop_column.sql

-- Remove extra columns from Package_Descriptions

BEGIN TRANSACTION;

-- Create new table with temporary name

DROP TABLE IF EXISTS temp;
CREATE TABLE temp (
    package_name VARCHAR(60),
    description  VARCHAR(120));

-- Copy columns we want into new table

INSERT INTO temp
    (package_name, description)
    SELECT package_name, description
      FROM Package_Descriptions;

-- Get rid of old table and rename the new replacement table

DROP TABLE Package_Descriptions;

ALTER TABLE temp RENAME TO Package_Descriptions;

COMMIT;

We again use the alternate form of the INSERT statement for copying values from one table to another. We copy the package_name and description columns from the Package_Descriptions table to the correspondingly named columns in the temp table.

Joins

A join is a method by which we perform a query and produce a result that combines the data from two tables. SQLite supports several types of joins but we’re going to focus on the most commonly used type called an inner join. We can think of an inner join as the intersection of two tables. In the example below, a file called join_demo.sql, we will produce a query result that matches our earlier example when we added columns to the Package_Descriptions table.

-- join_demo.sql

-- Demonstrate join by selecting columns from 2 tables

.header on
.mode column
.width 20 35 -5 -10

SELECT Package_Descriptions.package_name AS Package,
       description AS Description,
       count AS Files,
       tot_size AS Size
  FROM Package_Descriptions
  INNER JOIN Package_Stats
          ON Package_Descriptions.package_name =
             Package_Stats.package_name
LIMIT 10;

The results of this query are as follows:

Package               Description                          Files        Size
--------------------  -----------------------------------  -----  ----------
a2ps                  GNU a2ps - 'Anything to PostScript'    299     3455890
accountsservice       query and manipulate user account i     19      261704
acl                   Access control list utilities           11       91106
acpi-support          scripts for handling many ACPI even     18       13896
acpid                 Advanced Configuration and Power In     19       86126
adduser               add and remove users and groups         81      246658
adium-theme-ubuntu    Adium message style for Ubuntu         137      126759
adwaita-icon-theme    default icon theme of GNOME (small    1127     4848678
aisleriot             GNOME solitaire card game collectio    316     1890864
alsa-base             ALSA driver configuration files         42      195295

If we break down this query, we see that it starts out as we expect, then it is followed by the INNER JOIN statement. The INNER keyword is optional as an inner join is the default. After the INNER JOIN we have to specify the relationship on which the join is based. In this example, we are looking for matching instances of the package_name in both tables. Any expression my be used to convey the table relationship, provided that the result of the expression is true or false.

Since package_name is a column in both the Package_Descriptions and Package_Stats tables, we must precede it with the name of the respective table to avoid ambiguity.

Views

The join example above is a pretty useful query for our tables, but due to its complexity it’s best executed from a file rather than as an ad hoc query. SQL addresses this issue by providing a feature called views that allows a complex query to be stored in the database and used to produce a virtual table that can be used with simple query commands. In the following example we will create a view using our INNER JOIN query above to create a virtual table called Stats that we can use as the target of subsequent queries.

-- view_demo.sql

DROP VIEW IF EXISTS Stats;
CREATE VIEW Stats
AS
    SELECT Package_Descriptions.package_name AS Package,
           description AS Description,
           count AS Files,
           tot_size AS Size
      FROM Package_Descriptions
      INNER JOIN Package_Stats
              ON Package_Descriptions.package_name =
                 Package_Stats.package_name
        ORDER BY Package;

Once our view is created, we can treat Stats as just another table in our database even though it does not really exist as such.

sqlite> .header on
sqlite> .mode column
sqlite> .width 20 35 -5 -10
sqlite> SELECT * FROM Stats LIMIT 10;
Package               Description                          Files        Size      
--------------------  -----------------------------------  -----  ----------
a2ps                  GNU a2ps - 'Anything to PostScript'    299     3455890   
accountsservice       query and manipulate user account i     19      261704    
acl                   Access control list utilities           11       91106     
acpi-support          scripts for handling many ACPI even     18       13896     
acpid                 Advanced Configuration and Power In     19       86126     
adduser               add and remove users and groups         81      246658    
adium-theme-ubuntu    Adium message style for Ubuntu         137      126759    
adwaita-icon-theme    default icon theme of GNOME (small    1127     4848678   
aisleriot             GNOME solitaire card game collectio    316     1890864   
alsa-base             ALSA driver configuration files         42      195295

To delete a view we use the DROP VIEW statement as follows:

sqlite> DROP VIEW Stats;

Indexes

It’s been said that the three most important features of a database system are “performance, performance, and performance.” While this in not exactly true (things like data integrity and reliability are important, too), complex operations on large databases can get really slow, so it’s important to make things as fast as we can. One feature we can take advantage of are indexes.

An index is a data structure the database maintains that speeds up database searches. It’s a sorted list of rows in a table ordered by elements in one or more columns. Without an index, a table is sorted according to values in a hidden column called rowid. The values in this column are integers that start with 1 and increment each time a row is added to the table. Here we see a query that selects the 100th row from the Package_Stats table.

sqlite> .header on
sqlite> .mode column
sqlite> .width 20 -5 -8 -8 -8 -8
sqlite> SELECT * FROM Package_Stats WHERE rowid = 100;
package_name          count  tot_size  min_size  max_size  avg_size
--------------------  -----  --------  --------  --------  --------
cups-server-common      595   1996400         0    370070  3355.294

Our database server can locate this row in the table very quickly because it already knows where to find the 100th row. However, if we want to search for the row that contains package name cups-server-common, SQLite must examine every row in the table to locate the matching row. To facilitate performance tuning, SQLite provides a way to see what search strategy is used during a query.

sqlite> .width -8 -5 -4 55 
sqlite> EXPLAIN QUERY PLAN
   ...> SELECT * FROM Package_Stats WHERE package_name = 'cups-server-common';
selectid  order  from  detail
--------  -----  ----  ------------------------------------------------------- 
       0      0     0  SCAN TABLE Package_Stats

We can see from the SCAN TABLE Package_Stats the SQLite performs a sequential search of the table during this query.

To create an index to allow faster searches of package_name we can do the following:

sqlite> CREATE INDEX idx_package_name
   ...> ON Package_Stats (package_name);

After doing this, we’ll look at the query plan and see the difference.

sqlite> EXPLAIN QUERY PLAN
   ...> SELECT * FROM Package_Stats WHERE package_name = 'cups-server-common';
   selectid  order  from  detail                                                 
   --------  -----  ----  -------------------------------------------------------
          0      0     0  SEARCH TABLE Package_Stats USING INDEX idx_package_name

Hereafter, when we search the table for a package name, SQLite will use the index to directly get to the row rather than looking at every row searching for a match. So why don’t we just index everything? The reason we don’t is that indexes impose overhead every time a row is inserted, deleted, or updated since the indexes must be kept up to date. Indexes are best used on tables that are read more often than written to.

We probably won’t see much of a performance improvement when searching the Package_Stats table because it’s just not that big, but on a large table the improvement can be substantial.

We can see the index when we examine the table’s schema.

sqlite> .schema Package_Stats
CREATE TABLE Package_Stats (
    package_name  VARCHAR(60),
    count         INTEGER,
    tot_size      INTEGER,
    min_size      INTEGER,
    max_size      INTEGER,
    avg_size      REAL
);
CREATE INDEX idx_package_name
ON Package_Stats (package_name);

SQLite also has a dot command.

sqlite> .indexes
idx_package_name

Another benefit of using an index is that it’s kept in sorted order (that’s how it performs searches quickly). The side effect is that when an index is used during a query the results of the query will be sorted as well. To demonstrate, we’ll create another index for the Package_Stats table, this time using the tot_size column. Notice that when we perform a select based on that column, the results are in ascending order.

sqlite> CREATE INDEX idx_tot_size
   ...> ON Package_Stats (tot_size);
sqlite> .width 20 -5 -10 -8 -8 -8
sqlite> SELECT * FROM Package_Stats
   ...> WHERE tot_size > 100000000;
   package_name          count    tot_size  min_size  max_size avg_size
   --------------------  -----  ----------  --------  -------- --------
   inkscape               1025   127507308         0  19599216 124397.3
   libreoffice-core        119   135106135        26  66158968 1135345.
   linux-modules-extra-   4172   165884678      1292   4216105 39761.42
   linux-modules-extra-   4173   165921470      1292   4216105 39760.71
   thunderbird              69   180861838        27  12163098 2621186.
   firefox                  74   203393773        23  12408360 2748564.
   google-chrome-stable    100   235727530        25  16288078 2357275.
   libgl1-mesa-dri:amd6     20   237774005        36  19548840 11888700
   codium                 1307   271907088        17  11551467 208039.0
   linux-firmware         1938   331497257         6  19922416 171051.2

To delete our indexes, we use the DROP INDEX statement.

sqlite> DROP INDEX idx_package_name;
sqlite> DROP INDEX idx_tot_size;

Triggers and Stored Procedures

As we saw earlier during our discussion of views, SQL allow us to store SQL code in the database. Besides views, SQL provides for two other ways of storing code. These two methods are stored procedures and triggers. Stored procedures, as the name implies, allows a block of SQL statements to be stored and treated as a subroutine available to other SQL programs, or for use during ad hoc interactions with the database. Creating a stored procedure is done with this syntax:

CREATE PROCEDURE procudure_name
AS
    [block of SQL code];

Parameters can be passed to stored procedures. Here is an example:

CREATE PROCEDURE list_pkg_files @package VARCHAR(60)
AS
    SELECT package_name, file
      FROM Package_Files
     WHERE package_name = @package; 

To call this procedure, we would do this:

EXEC list_package_files @package = 'bash';

Unfortunately, SQLite does not support stored procedures. It does, however, support the second method of code storage, triggers.

Triggers are stored blocks of code that are automatically called when some event occurs and a specified condition is met. Triggers are typically used to perform certain maintenance tasks to keep the database in good working order.

Triggers can be set to activate before, after, or instead of the execution of INSERT, DELETE, or UPDATE statements. In the example below, we will have a trigger activate before a DELETE is performed on the Package_Files table.

/*
    trigger_demo.sql

    Trigger demo where we create a "trash can" for the
    Package_Files table and set a trigger to copy rows
    to the PF_Backup table just before they are deleted
    from Package_Files.
*/

-- Create backup table with the same schema as Package_Files

CREATE TABLE IF NOT EXISTS PF_Backup (
    package_name VARCHAR(60),
    file         VARCHAR(120),
    size_bytes   INTEGER
);

-- Define trigger to copy rows into PF_Backup as they are
-- deleted from Package_Files

CREATE TRIGGER backup_row_before_delete
BEFORE DELETE ON Package_Files
BEGIN
    INSERT INTO PF_Backup
    VALUES (OLD.package_name, OLD.file, OLD.size_bytes);
END;

The first thing we do is create a table to hold our deleted rows. We use a slightly different form of the CREATE TABLE statement to create the table only if it does not already exist. This will ensure that an existing table of saved rows will persist, even if we reload the trigger.

After creating the table, we create a trigger called backup_row_before_delete to copy data from the Package_Files table to the PF_Backup table just before any row in Package_Files is deleted.

In order to reference data that might be used by the trigger, SQL provides the NEW reference for new data that is inserted or updated, and the OLD reference for previous data that is updated or deleted. In our example, we use the OLD reference to refer to the data about to be deleted.

Performing Backups

Since SQLite uses an ordinary file to store each database (as opposed to the exotic methods used by some other systems), we can use regular command line tools such as cp to perform database backups. There is an interesting SQL method we can use, too. The .dump dot command will produce a stream of SQL statements that will fully reproduce the database including tables, views, triggers, etc. To output the database this way, we need only do the following:

sqlite> .dump

The stream will appear on standard output or we can use the .output dot command to direct the stream to the file of our choice.

One interesting application of this technique would be to combine tables from multiple databases into one. For example, let’s imagine we had several Raspberry Pi computers each performing data logging of an external sensor. We could collect dumps from each machine and combine all of the tables into a single database for data analysis and reporting.

Generating Your Own Datasets

Below are the programs used to create the datasets used in this adventure. They are included in the archive for those who want to create their own datasets.

For Deb-based Systems (Debian, Ubuntu, Mint, Raspberry Pi OS)

The first program named mk_package_descriptions-deb, extracts package information and outputs a .tsv file.

#!/bin/bash

# mk_package_descriptions-deb - Output tsv list of installed debian/ubnutu
#                               packages on standard output

phase1() { # Replace repeated spaces with a tab
  awk '
  {
    gsub(/[ ][ ]+/, "\t")
    print $0
  }'
  return
}

phase2() { # Output field 2 and 5 separated by a tab
  awk '
    BEGIN {
      FS = "\t"
    }

    $1 == "ii" {
      print $2 "\t" $5
    }'
  return
}

dpkg-query -l | phase1 | phase2

The second program, mk_package_files-deb outputs all the files included in each package.

#!/bin/bash

# mk_package_files - make list of files in all packages
# Reads *.list files in LIST_DIR. Outputs stream of tsv to stdout.

LIST_DIR=/var/lib/dpkg/info

mk_list () {

  local list_file="$1"
  local lf_length="${#list_file}"
  local len
  local package
  local -a files

  ((len = lf_length - 5))
  package="${list_file:0:$len}" # strip '.list' extension
  package="${package##*/}" # strip leading pathname
  mapfile files < "$list_file" # load list into array
  for i in "${files[@]}"; do
    i="${i//[$'\t\r\n']}" # strip trailing newlines
    if [[ -f "$i" ]] ; then # write tsv file
      printf "%s\t%s\t%s\n" \
        "$package" \
        "$i" \
        "$(stat --printf '%s' "$i")" # size of file
    fi
  done
  return
}

for i in "$LIST_DIR"/*.list; do
  mk_list "$i"
done

For RPM-based Systems (RHEL, CentOS, Fedora)

The mk_package_descriptions-rpm script:

#!/bin/bash

# mk_package_descriptions-rpm - Output tsv list of installed Fedora/CentOS
#                               packages on standard output

while read package; do
  description=$(dnf info "$package" \
    | awk '$1 == "Summary" { gsub(/Summary      : /, ""); print $0; exit }')
  printf "%s\t%s\n" \
    "$package" \
    "$description"
done < <( dnf list installed | awk 'NF == 3 { print $1 }' )

The mk_package_files-rpm script:

#!/bin/bash

# mk_package_files-rpm - Output tsv list of installed Fedora/CentOS files
#                        on standard output

while read package; do
  while read package_file; do
    if [[ -r "$package_file" ]]; then # not all files are present/readable
      printf "%s\t%s\t%s\n" \
        "$package" \
        "$package_file" \
        "$(stat --printf '%s' "$package_file")"
    fi
  done < <( rpm -ql "$package" )
done < <( dnf list installed | awk 'NF == 3 { print $1 }' )

Converting .tsv to SQL

Below are two AWK programs used to convert the .tsv files into SQL. First, the insert_package_descriptions.awk program:

#!/usr/bin/awk -f

# insert_package_descriptions.awk - Insert records from
#                                   package_descriptions.tsv

BEGIN {
    FS="\t"
    print "DROP TABLE IF EXISTS Package_Descriptions;"
    print "CREATE TABLE Package_Descriptions ("
    print "    package_name VARCHAR(60),"
    print "    description  VARCHAR(120)"
    print ");"
    print "BEGIN TRANSACTION;" # vastly improves performance
}

{
    gsub(/'/, "''") # double up single quotes to escape them
    print "INSERT INTO Package_Descriptions"
    print "     VALUES ( '" $1 "', '" $2 "');"
}

END {
    print "COMMIT;"
}

Second, the insert_package_files.awk program:

#!/usr/bin/awk -f

# insert_package_files.awk - Insert records from
#                            package_files.tsv

BEGIN {
    FS="\t"
    print "DROP TABLE IF EXISTS Package_Files;"
    print "CREATE TABLE Package_Files ("
    print "    package_name VARCHAR(60),"
    print "    file         VARCHAR(120),"
    print "    size_bytes   INTEGER"
    print ");"
    print "BEGIN TRANSACTION;" # vastly improves performance
}

{
    gsub(/'/, "''") # double up single quotes to escape them
    print "INSERT INTO Package_Files"
    print "     VALUES ('" $1 "', '" $2 "','" $3 "');"
}

END {
    print "COMMIT;"
}

Summing Up

SQL is an important and widely used technology. It’s kinda fun too. While we looked at the general features and characteristics of SQL, there is much more to learn. For example, there are the more advanced concepts such as normalization, referential integrity, and relational algebra. Though we didn’t get to the really heady stuff, we did cover enough to get some real work done whenever we need to integrate data storage into our scripts and projects.

Further Reading

• SQL Style Guide by Simon Holywell The style guide that influenced the formatting of the SQL presented in this adventure. https://www.sqlstyle.guide/
• SQL Tutorial A good general tutorial on SQL. Covers most of the major SQL dialects (though not SQLite). https://www.w3schools.com/sql/
• SQLite project home page Includes detailed documentation. https://www.sqlite.org/index.html
• SQLite Tutorial An excellent detailed tutorial and reference for the SQLite dialect of SQL. Definitely worth checking out if you plan to use SQLite seriously. https://www.sqlitetutorial.net/
• MySQL home page A very popular multi-user database system used by many websites. https://mysql.com/
• MariaDB Foundation home page MariaDB is a fork of the open source version of MySQL. The project came about out of concern over Oracle’s purchase of the company behind MySQL. https://mariadb.org/
• PostgreSQL project home page Another very popular open source multi-user database. https://www.postgresql.org/
• Various Wikipedia articles providing background for the topics covered in this adventure:
  • SQL https://en.wikipedia.org/wiki/SQL
  • Relational Database https://en.wikipedia.org/wiki/Relational_database
  • Relational Model https://en.wikipedia.org/wiki/Relational_model
  • LAMP Software Stack https://en.wikipedia.org/wiki/LAMP_(software_bundle)
  • MySQL https://en.wikipedia.org/wiki/MySQL
  • MariaDB https://en.wikipedia.org/wiki/MariaDB
  • PostgreSQL https://en.wikipedia.org/wiki/PostgreSQL
• Stanford University offers an online course called “Introduction to Databases” taught by Professor Jennifer Widom. I took this course; it’s quite good. The course videos are available in this YouTube playlist: https://www.youtube.com/playlist?list=PLroEs25KGvwzmvIxYHRhoGTz9w8LeXek0
• Finally, the phrase “gird your grid for a big one” is taken from the 1971 comedy album I Think We’re All Bozos on This Bus by the Firesign Theater. Besides being very funny to retro-futurism fans like myself, the album is notable because it contains one of the first pop culture references to computer hacking. You can read more about it at Wikipedia: https://en.wikipedia.org/wiki/I_Think_We%27re_All_Bozos_on_This_Bus