7. Debugging Databases

Written by Zahidur Rahman Faisal

Data is a collection of facts, such as numbers, words, measurements, observations or just descriptions of things. - Math is Fun

The word Data originates from the word datum which means a single piece of information. Data is the plural for datum.

Basically, data is a distinct, small unit of information that can be used in various forms like text, numbers, media or bytes etc. It can be stored in electronic memory or pieces of paper just like in a book!

In the previous chapter, you learned about debugging views and optimizing layouts to display details of the podcast channel that you’ve subscribed. At this point, you must be wondering if you could debug the way podcasts are stored in PodPlay.

In this chapter, you’ll check how your subscribed podcasts are stored and organized in your database, and you’ll resolve the episode ordering issue.

In this process, you’ll learn how to:

• Explore and update your existing data.
• Modify your queries and watch live updates.
• Export your Database.

Searching for The Data

Imagine a library that consists of many books where you can go and easily find a book about a topic you’re interested in or store a new book to read later. That’s your Database.

It wouldn’t be easy for you to find a book in a library if the books weren’t organized. You might get lost searching for a book that’s recently been added if someone just put the book randomly on any shelf. You might have faced the same situation in the PodPlay app! When using the app, you access a vast library of podcasts and can subscribe to your favorite ones to come back to later. The details of your subscribed podcast channels are stored locally in the app, just like a library. PodPlay uses a Room Database to store your subscriptions.

To start, open the Podplay starter project and run the app. Tap the search icon in PodPlay, type “RW” in the search bar and press Return. Choose any podcast and tap it to open its details. Finally, tap SUBSCRIBE to subscribe to the podcast.

You’ll now see the channel name in the subscribed channel list. Tap it to open a detailed screen, and you’ll see a list of episodes underneath the title and description of the channel. If you look closely, you may notice the episodes aren’t shown in the correct date order; you’d expect to see the recent episode on top of the list and then the older ones in chronological order.

There’s an integrated Database Inspector in Android Studio 4.1 and higher to assist your inspection of what’s gone wrong. The Database Inspector allows you to easily inspect, query, and modify the database in PodPlay, just like editing a spreadsheet; who wouldn’t want to take advantage of that!

The Database Inspector

You might be questioning how PodPlay’s database arranges your subscribed podcasts. Soon you’ll be able to see your table structure and schema using the Database Inspector. To open the Database Inspector in Android Studio, you need to:

1. Run your app on an emulator or connected device running API level 26 or higher.
2. Select View ▸ Tool Windows ▸ App Inspection from the menu bar.

3. Choose com.yourcompany.podplay from the running app process in the drop-down menu.

4. Then Select the Database Inspector tab.

Previewing Database

You’ll see a list of databases in your app and the tables each database contains within the Databases pane. The name of the database in PodPlay is PodPlayer.

The PodPlayer database consists of two tables as follows:

1. Episode: A list of episodes in subscribed channels including additional fields, such as mediaUrl, type, duration.
2. Podcast: Mainly contains the podcast URL, name and description of a subscribed podcast channel.

Simply double-click on the Podcast table to display its data in the inspector window.

Find the Schema

Here each column represents a field in your table. Expand the Podcast table to see how the schema was defined:

The marked area in red provides two important pieces of information:

1. id is the primary key for this table.
2. The data type for the primary key is Integer.

Looking at this, you can verify if you’ve defined the proper data types for your fields. This is useful to tackle issues where any unsupported data might have accidentally been inserted into a specific field in your table.

Updating Existing Data

Start by verifying if the data is in the correct order and getting stored in your table properly by following two simple steps:

1. To find your latest added podcast, click the lastUpdated column. It’s the timestamp of when you subscribed to the channel. Clicking on a column header sorts the data in the inspector window by that column, so it’ll display the rows in a chronological order based on your timestamp.

2. Double-click on the first item under the feedTitle column, then change the title to “My top favorite channel” and press Return.

PodPlay uses Room Database, and the UI observes the database with LiveData, so your update will be instantly visible in your running app. Otherwise, the changes would only be visible after you launch the app next time.

Observing Live Updates

So far, it seems the data in the Podcast table has been stored and retrieved as expected. Now take it one step further, type something in the search bar, select a channel and tap SUBSCRIBE. It’s supposed to be on your Podcast table, but you can’t see any change!

This is because you need to reload the contents of the current table that’s visible to you in the Database Inspector.

Now, click the highlighted icon in the above image. This will refresh the contents, and the channel you just subscribed to will show up in the Podcast table!

It’s convenient to see live updates while you interact with a running app; to enable that feature you need to click the Live updates checkbox beside the Refresh icon.

Enable the Live Updates feature and subscribe to something. The subscribed channel is immediately shown in your Podcast table, as the name suggests.

Note that the table in the inspector window becomes read-only, and you can’t modify its values with Live updates enabled. So, trying to double-click and change a value will have no effect while in Live updates mode.

Querying Your Databases

Since you verified that the subscribed podcasts are getting stored and retrieved correctly, the next step is to check if your database queries are correct.

The Database Inspector can run queries on your app’s database while you’re running the app. The tool can easily execute DAO queries that you’ve written for your Room database, but it also allows you to write custom SQL queries to validate your ideas!

Running DAO Queries

Data Access Object (DAO) is an interface that executes CRUD operations in your database, hiding the complexity behind it.

Open PodcastDao.kt within the db package in your project folder; PodcastDao is a simple DAO used to interact with the database in PodPlay.

Looking at the queries here, it’s obvious that loadEpisodes() is responsible for providing a list of episodes to your UI layer.

The podcastId parameter is the id of your podcast channel from the Podcast table. The query filters out episodes from the entries in the Episode table based on the provided podcastId here.

You can easily find out what this query returns by clicking on the button next to @Query like below:

Upon clicking, a prompt appears for you to select the database to execute the query on. Select PodPlayer from the drop-down list.

Next, there will be another popup showing the query you choose to execute and asking for a value for your query parameter, if any, which is podcastId.

Input “1” to load episodes from your first subscribed podcast channel.

If your query method includes more than one parameter, Android Studio requests values for each parameter before running the query.

You’ll see the results of the query in a new tab within the Database Inspector like below:

Running Custom Queries

Focus on the highlighted area in your query above; you’ll see your results in ascending order of each episode’s releaseDate. This must be why you don’t see the latest episode at the top of your list!

To fix this, you need to have the episode listed in descending order by releaseDate when you execute this query. You can quickly verify this by running a custom query without even modifying your DAO file by following the below steps:

1. Open the Open New Query tab at the top of the Databases pane.

   

2. Select the database PodPlayer to query from the drop-down list on the New Query tab.

3. Type the following SQL query into the text field at the top of the New Query tab: “SELECT * FROM Episode WHERE podcastId = ‘1’ ORDER BY releaseDate DESC”

4. Click Run to execute the query.

Here’s another simple approach to doing this since your query hasn’t changed much apart from a keyword:

1. Click the Show query history icon beside the database name in your custom query pane to see a list of queries that you previously ran.

   

2. Click a query in the list to see a preview of the full query in the editor, and press Return to copy it to the editor.
3. Modify the query part you want. In this case, change the order from ASC to DESC in your query.

   

4. Click Run to execute the statement.

Look at the releaseDate column now; the items are in descending order. That means the custom query will return the latest episode first and the others subsequently, which solves your issue!

You can limit the number of items on a page for simplicity by clicking on the highlighted area.

Now you’re certain the new query works and it will deliver your expected results, open PodcastDao.kt and update the @Query above loadEpisodes() like below:

SELECT * FROM Episode WHERE podcastId = '1' ORDER BY releaseDate DESC

With this code, you change the order of podcasts.

Exporting Data from the Database Inspector

The Database Inspector allows you to export databases, tables or query results. It’s a powerful feature to save, share or replicate your data. You can export using any of the following ways:

• Select a database in the Database pane, then click ‘Export as File…’ near the top-left corner to export the whole database.

• Right-click on a table in the Databases panel and select ‘Export as File…’ from the context menu to export a specific table in the database.

• Click ‘Export as File…’ query results while viewing a table or query results in a tab.

To complete the export action, you need to choose a format for the exported file. Depending on whether you’re trying to export a database, table, or query results, the Database Inspector will popup three options for exporting the data in the following formats:

1. DB: Preferable format if you’re going to import or use the database in mobile applications.
2. SQL: Easy to open or modify the exported file on any desktop or web-based database system.
3. CSV: Simple, lightweight and widely accepted format, good for batch reading/data writing.

If you select CSV format, you can also choose how the values are separated in a row from the drop-down below:

Then the Database Inspector will accumulate the tables from the PodPlayer database in different .csv files and create a single .zip on your selected path to export.

This feature can save your day in an emergency by simplifying the backup and sharing process. You can quickly restore your entire database with a few clicks!

Key Points

• You can look into your existing database easily with Database Inspector.
• Check your database schema carefully and validate your assumptions about updating fields.
• Using the Live updates feature will save you time to observe changes, but you can’t modify data in this mode.
• Save time running your queries directly from the Android Studio DAO.
• Copy and modify a query or write a custom one to see quick results.
• Export your database for easy backup or sharing your data.

Where to Go From Here?

You’re now an expert on Database Debugging! The Database Inspector offers you other powerful features such as:

• Offline mode
• Keeping database connections open

If you’re interested in mastering the ways of storing data on Android, these are the best starting point for you:

• Saving Data on Android
• Room Database Tutorial

At this point, you’re probably curious to know how fetching and saving data works in the background! In the next chapter, you’ll learn to inspect on background tasks using Background Task Inspector, so stay tuned!