How to Read Db Files in Linux

Fatmawati Achmad Zaenuri/Shutterstock

DB Browser for SQLite lets you view and edit SQLite databases on Linux. Y'all can design, create, and edit these database files, and peek inside the inner workings of other applications. Here's how to use this SQLite GUI.

The SQLite Database Project

The SQLite database library and tools are a phenomenally successful open-source Structured Query Language (SQL) database project. So successful, in fact, information technology can justifiably call itself the most widely deployed database engine in the world.

Since its initial release in 2000, SQLite has seen an absolutely staggering uptake. Information technology's inside every iPhone and Android phone, and Windows ten or Mac computer. Information technology'southward also in every cyberspace browser, including Chrome, Firefox, and Safari, besides equally countless other applications.

The incredible achieve of the SQLite database is due to its compages. Information technology's a fast, lightweight library that's incorporated (orlinked, in developer-speak) in other applications. The database engine becomes an integral office of the product. This means yous don't have to provide an external SQL database server, like MySQL, MariaDB, or Microsoft SQL Server.

SQLite also has a command-line tool for manipulating databases, but information technology's the library that'due south made it a triumph. Having a capable, self-contained database engine tucked away inside your awarding removes a lot of issues. It simplifies your awarding's installation routines and lowers the minimum hardware requirements for your application.

Additionally, considering SQLite uses a unmarried cross-platform file for its database tables, indexes, and schemas, the entire database can be moved to another reckoner. You tin can even move it to a machine running a different operating arrangement by copying one file.

In fact, SQLite's database file format is so well regarded, information technology's ane of just a handful recommended past the Library of Congress for long-term information storage.

Because SQLite is a developer's library, however, in that location isn't a forepart-stop for it, meaning it doesn't accept a Graphical User Interface. It'southward the awarding using the library that provides the user interface. The command-line utility can run in an interactive mode, only information technology still isn't a GUI.

DB Browser for SQLite (DB4S) fits the pecker nicely. It'southward a visual tool developed by another open up-source projection to allow the creation and manipulation of SQLite databases from within a GUI.

DB Browser for SQLite

DB Browser for SQLite has been effectually (in one incarnation or some other) since 2003 and has undergone several name changes. Previously, it was chosen SQLite Browser, but that acquired confusion. People thought it had been written by the SQLite team, and thus, were forwarding feature requests and back up queries almost DB4S to SQLite.

And so, SQLite Browser was renamed DB Browser for SQLite. You lot'll nevertheless see references to the old proper name here and in that location. In fact, the website for the project yet uses "sqlitebrowser" as its domain, and the onetime proper noun is also used during the installation of DB4S.

With DB4S you can:

• Create databases.
• Import and export database schemas, tables, and information in SQL format.
• Import and consign tables and data in CSV format.
• Create, edit, and delete tables and indexes.
• Add, edit, and delete records.
• Browse and search for database records.
• Edit and run SQL commands. You can make sure the command does what you lot recollect it'southward going to before you hard-lawmaking some SQL into your awarding.

Installing DB Browser for SQLite

To install DB4S on Ubuntu, use the following command (again, note the installation still uses the sometime name):

sudo apt-get install sqlitebrowser

On Fedora, you lot type:

sudo dnf install sqlitebrowser

On Manjaro, we use pacman:

sudo pacman -Sy sqlitebrowser

Importing a Database from an SQL File

When DB4S starts, information technology doesn't take a database loaded into information technology. Nosotros're going to look at ii ways y'all tin import both data and database table definitions, also every bit how you lot can create your own database.

Sometimes, you might be given or sent a database dump file in SQL format. This contains the instructions necessary to copy a database and insert its data into a database.

Some other common format used to import table definitions and data is the comma-separated values (CSV) format. Y'all can apply a data-generation site, such as Database Test Data, to generate dummy data for practicing purposes. You tin so export your data as SQL or CSV.

Below is an SQL file nosotros created on that site. Later information technology was exported, we edited it and added a line at the elevation of the file, which is required for SQLite:

Begin TRANSACTION;

We then saved the file. In DB4S, nosotros click File > Import > Database from SQL File.

A file selection dialog opens and then we can choose our SQL file. In our example, it'southward chosen "database_dump.sql," and it's located in the root of our habitation directory.

With the file selected, we click "Open," and a file-save dialog opens. Y'all now accept to proper noun your new database and decide where to save information technology. We called ours "geekbase.sqlite3," and we're saving it in our home directory.

Click "Save" when you're prepare to proceed. We've identified our source SQL file and named our new database, and then the import process can now begin. Later it's complete, you'll see the notification dialog beneath.

Because nosotros've added tables and data to the database, we're prompted to save those changes, so nosotros click "Save" to do and so.

The master DB4S window volition at present brandish the structure of our database.

There are two tables created, although there was only a definition for one in the SQL file. This is because the "id" field was divers every bit an machine-incrementing field. It will be added automatically whenever a new tape is added to the database. SQLite creates a table to proceed track of auto-incrementing fields.

Click the "Browse Data" tab to run across your newly added records.

Of course, the power of a database lies in its ability to search and extract records. With an SQL-enabled database, we use the SQL linguistic communication to exercise this. To become started, the "Execute SQL" tab.

Nosotros've added the following SQL command:

SELECT * FROM account_details WHERE last_name Like "%ll%" ORDER BY country

This will search for people with a double "50" in their last name, and the results will be sorted by state. Click the bluish pointer (information technology looks similar a "Play" button) to run your SQL command. The results are displayed in the lower pane.

Nosotros have four records that contain double "l" in the terminal name, and they're sorted alphabetically by state, from Arizona to Wisconsin.

Importing a Database Table from a CSV File

We tin can also import tables from suitable CSV files. The most convenient mode to do then is to accept the table field names in the CSV file every bit the first row of text. Below is a short section of a CSV file.

The first line holds the field names: first_name, last_name, created, email, state, and ID. The other lines hold the data values for each record that volition be added to the table. This is the same data as before; but the file format has changed.

When you import CSV data, y'all have to create an empty database so you accept something to import it to. To do so, click "New Database" in the toolbar.

A file-salvage dialog opens. Name your new database and decide where to save it. Nosotros're going to phone call ours "howtogeek.sqlite3" and save it in our dwelling house directory.

When the "Edit Table Definition" dialog appears, click "Abolish." Back in the main DB4S window, click File > Import > Tabular array From CSV File. A file option dialog opens in which you tin choose your CSV file.

In our case, it'south called "users.csv," and information technology's located in the root of our home directory. Click "Open," and a preview dialog appears to show you how DB4S volition interpret the CSV data.

The proper noun of the file is used as the name of the table. You can edit this if you want, merely make sure you select the checkbox next to "Column Names in Showtime Line."

Click "OK" (it's off-screen in the image above). The information is imported and, if all is well, you should encounter the "Import Completed" dialog; click "OK."

Click "Scan Data," and you'll run across the imported data.

Nosotros still have a small tweak to make, though. Click the "Database Construction" tab, select the name of the tabular array, and then click "Modify Table" in the toolbar.

In the "Edit Table Definition" dialog, select the "AI" (auto-incrementing) checkbox in the "id" field.

The "PK" (Principal Key) checkbox is automatically selected for you lot; click "OK." This sets the "id" field to be motorcar-incrementing. We can now add a new record to the database to verify it's working.

Click the "Execute SQL" tab, and and then type the following SQL in the upper pane (notation that we're supplying values for all the fields except "id"):

INSERT INTO "users"  ("first_name","last_name","created","e-mail","country") VALUES ('Dave','McKay','12/08/2020','dave@llk.com','Idaho');

Click the blue arrow (that looks a Play button) to run your SQL command. Click "Browse Data" and scroll to the bottom. Y'all should see your newly added record with an automatically provided "id" field belongings a value that'due south ane higher than the previous highest "id" value.

Creating a Database Manually

If y'all don't have an SQL or CVS file to import, you'll have to create your database manually. To go started, click "New Database" and a file-save dialog appears. Type the proper noun of your new database and where y'all want to relieve information technology.

Nosotros've named ours "geeksrock.sqlite3," and we're saving it in the "Documents" directory. After y'all name your database and navigate to the location in which you want to save it, click "Salvage."

When asked to confirm your choices, click "Save" over again.

The "Edit Table Definition" dialog appears. Name your new tabular array (we've called ours "eagles"), and so click "Add Field." You tin can now type a proper noun for the field and select the type of information information technology will contain from the "Type" drop-down menu.

We've added a text field to agree the proper noun of the hawkeye, and a real (floating point) numeric field to agree the wingspan.

The checkboxes and other options adjacent to each field let you to add together the post-obit behaviors:

• NN (Not Nada): When this selection is gear up, the field tin can't be left empty. If y'all try to add a record without providing a value for this field, it will be rejected.
• PK(Primary Key): A field (or group of fields) that provides a unique identifier for the records in the table. This might be a simple numeric value, similar the auto-incrementing integer fields we covered above. In a tabular array of user accounts, though, it might exist a username. There can only be 1 primary key in a table.
• AI(Auto-Incrementing): Numeric fields can be automatically filled in, with the adjacent highest unused value. We used this in the "id" field in the case nosotros covered to a higher place.
• U (Unique): This field in each tape must concord a unique value, meaning there tin can't be any duplicates in this field in the table.
• Default: A default value will be provided if a record is added that has no value in this field.
• Cheque: A field can have a check performed on it when a record is added. For example, you might check that the value in a phone number field has at least 10 characters.
• Collation: Sqlite can use different methods of string comparison. The default is BINARY. Other options are NOCASE, which is instance-insensitive, and RTRIM, which ignores abaft white space. In almost cases, you can leave this to the default value.
• Foreign Key: A field or group of fields in a record that must match a cardinal in some other tabular array. For example, in a database of workshop tools, you might have a table of individual tools and another tabular array of tool categories. If the tool categories are "hammer," "spanner," and "screwdriver," y'all can't add a record of type "chisel."

Click "OK" later you lot add the fields you want. Afterwards your database is created and your showtime table is added, you can add some records.

In the "Execute SQL" tab, we used the SQL INSERT statement several times to add some records to the table.

We can besides use the "Browse Data" tab to encounter our new records.

If you prefer to add new records through the user interface, click "New Record" on the toolbar. You can and so input the values for the new record without agreement SQL.

Other Application's Databases

You can as well use DB4S to run across the SQLite databases that belong to other applications. Examining the structure or contents of other databases can be instructive (or simply interesting). It's important that you don't brand changes to databases endemic past other applications, though, or you might adversely affect the operation of that application.

Allow's look at one of the SQLite databases Firefox creates and maintains. Click "Open Database" on the toolbar, and a file-open dialog volition appear. Firefox keeps its files in a directory called "firefox," which is inside a hidden directory chosen ".mozilla" that sits in your dwelling directory.

On our exam machine, we found the Firefox SQLite databases at this location: "home/dave/.mozilla/firefox/vpvuy438.default-release"; yours will exist in a similar location.

We're going to open the "cookies.sqlite" database, and then nosotros highlight the file, and then click "Open." With the database opened, you can examine its table structure, field definitions, and data.

Here, we can see a diverseness of Google and YouTube cookies.

Exporting Provides Insights

Exporting a database (by clicking File > Export > Database to SQL File) is useful, as well. By looking at the SQL dump of a database, y'all tin see the entire schema of the database rendered in SQL statements.

sowellpannour.blogspot.com

Source: https://www.howtogeek.com/704243/how-to-use-db-browser-for-sqlite-on-linux/

Share this post