Discover how to use and probe a SQLite database
Managing Application Data
As I said at the beginning, several programs you already use on your desktop run SQLite under the hood. Knowing how to manage SQLite databases can help you get more from those programs than they offer you from their own interfaces. As just two of many examples, I'll look at Firefox and digiKam. If you look at the hidden folder in which Firefox stores its data, you will find a number of SQLite databases, which all have the .sqlite
file name extension. With the following simple shell script, you can periodically clean and compact all of these files to help Firefox run a bit faster:
cd $HOME/.mozilla/firefox/*.default for i in *.sqlite do echo "VACUUM;" | sqlite3 $i done
digiKam, on the other hand, stores location and all other metadata for each of the pictures it manages in one SQLite database called digikam4.db
(Figure 4).
![](/var/linux_magazin/storage/images/issues/2016/186/sqlite-tutorial/figure-4/669123-1-eng-US/Figure-4_large.png)
Assume, for example, that you have hundreds of scanned photographs from the past 30 years scattered among many different digiKam albums. What do you do if your grandparents ask for a copy of all pictures that were taken at your parents' house? Should you find all the corresponding albums manually? If they are properly geotagged (which is much easier than you might think, but that is a topic for another tutorial), you can tell SQLite to find those pictures for you.
Looking at the tables in the digiKam database with the metacommands .tables
and .schema
shows that the location of each album and the photograph file names are kept in the tables Albums and Images, respectively; another table, ImagePositions
, stores the latitude and longitude of each image. In these conditions, asking SQLite to list the locations of all the pictures whose latitude is the same as that of your parents' home (43,33 in this example) is relatively simple (Listing 2).
Listing 2
Selecting digiKam Images by Latitude
sqlite> .separator "/" sqlite> .mode list sqlite> .output photolist.txt sqlite> SELECT relativePath, name from ImagePositions as P JOIN (Albums as A JOIN Images AS I ON A.id = I.album) ON P.imageid = I.id WHERE latitude = '43,33' ORDER BY relativePath, name;
The SELECT
command concatenates the three tables with the JOIN statement, producing the photolist.txt
file with lines like those shown in Listing 3: a file, for example, that you can easily use in a tar
command or in a script that copies the photographs onto a DVD. On the other hand, if you use .mode html
, you can export your data as an HTML file (Figure 5).
Listing 3
photolist.txt
[marco@localhost ~]$ head -2 photolist.txt /marco/1978/family/birthdays/20080815-grandma-birthday/200808151951.jpg /marco/2002/holidays/20021225-christmas-dinner/200212251505.jpg
![](/var/linux_magazin/storage/images/issues/2016/186/sqlite-tutorial/figure-5/669126-1-eng-US/Figure-5_large.png)
Graphical SQLite Interfaces
Command-line tools are the most efficient, but not the best for every situation. The most convenient graphical interfaces for managing SQLite databases on Linux may be LibreOffice Base and Kexi [2]. Several drivers can connect a SQLite database with the LibreOffice Suite; the easiest to set up at the time of writing was the ODBC Driver Extension [3]. Kexi, on the other hand, can deal with SQLite out of the box.
If you want a graphical interface, however, I recommend you at least test drive DB Browser for SQLite [4], as shown in the figures for this article. Like its command-line counterpart sqlite3
, DB Browser is a multiplatform application and is available as a binary package for all the most common Linux distributions. For complex, raw queries, as well as heavy data processing tasks, I continue to prefer the command-line tool, but if you just need to tweak your SQLite configuration or study the structure of an existing database, DB Browser is a better choice.
Conclusion
If you need a database that is simple to set up or want better access to the data that your software is already handling with SQLite, you now know how to proceed. The next step is to look at the official SQLite documentation, especially the "SQL Syntax" and "Core SQL Functions" sections [5], and bookmark some handy cheat sheets that will help you create queries, starting with the resources listed in the Info section [6]-[9]. Enjoy your portable databases!
Infos
- SQLite: http://sqlite.org/
- Kexi: http://www.kexi-project.org
- LibreOffice SQLite ODBC driver: http://extensions.libreoffice.org/extension-center/libreoffice-sqlite-odbc-driver
- DB Browser: http://sqlitebrowser.org
- SQLite documentation: http://www.sqlite.org/docs.html
- SQLite syntax reference: http://www.sqlite.org/lang_expr.html
- "My SQLite cheat sheet" by Niklas Ottosson: http://blog.niklasottosson.com/?p=1342
- "15 SQLite3 SQL commands explained with examples" by Ramesh Natarajan: http://www.thegeekstuff.com/2012/09/sqlite-command-examples
- "Searchable SQLite3 cheat sheet" by richardjh: http://www.cheatography.com/richardjh/cheat-sheets/sqlite3/
« Previous 1 2 3 4
Buy this article as PDF
(incl. VAT)
Buy Linux Magazine
Subscribe to our Linux Newsletters
Find Linux and Open Source Jobs
Subscribe to our ADMIN Newsletters
Support Our Work
Linux Magazine content is made possible with support from readers like you. Please consider contributing when you’ve found an article to be beneficial.
![Learn More](https://www.linux-magazine.com/var/linux_magazin/storage/images/media/linux-magazine-eng-us/images/misc/learn-more/834592-1-eng-US/Learn-More_medium.png)
News
-
NVIDIA Released Driver for Upcoming NVIDIA 560 GPU for Linux
Not only has NVIDIA released the driver for its upcoming CPU series, it's the first release that defaults to using open-source GPU kernel modules.
-
OpenMandriva Lx 24.07 Released
If you’re into rolling release Linux distributions, OpenMandriva ROME has a new snapshot with a new kernel.
-
Kernel 6.10 Available for General Usage
Linus Torvalds has released the 6.10 kernel and it includes significant performance increases for Intel Core hybrid systems and more.
-
TUXEDO Computers Releases InfinityBook Pro 14 Gen9 Laptop
Sporting either AMD or Intel CPUs, the TUXEDO InfinityBook Pro 14 is an extremely compact, lightweight, sturdy powerhouse.
-
Google Extends Support for Linux Kernels Used for Android
Because the LTS Linux kernel releases are so important to Android, Google has decided to extend the support period beyond that offered by the kernel development team.
-
Linux Mint 22 Stable Delayed
If you're anxious about getting your hands on the stable release of Linux Mint 22, it looks as if you're going to have to wait a bit longer.
-
Nitrux 3.5.1 Available for Install
The latest version of the immutable, systemd-free distribution includes an updated kernel and NVIDIA driver.
-
Debian 12.6 Released with Plenty of Bug Fixes and Updates
The sixth update to Debian "Bookworm" is all about security mitigations and making adjustments for some "serious problems."
-
Canonical Offers 12-Year LTS for Open Source Docker Images
Canonical is expanding its LTS offering to reach beyond the DEB packages with a new distro-less Docker image.
-
Plasma Desktop 6.1 Released with Several Enhancements
If you're a fan of Plasma Desktop, you should be excited about this new point release.