Queries in ‘Select’ statements vs Apply in MS SQL Server

There are many times working with SQL when you want to select just one row from a table which has many that satisfy a join condition. This could be when you want the latest record, or just one name from a large set of valid names. I’ve personally encountered this when writing BI reports.

For this example, I’m using a table called ‘Entities’ and a table called ‘Names’, which is referenced by the column Entities.EntityID. Names has a RecordedOn DateTime field.

The approach I’ve seen most often is to write an inline query in the select statement:

SELECT 
  e.EntityID
, (SELECT TOP 1 Name 
   FROM Names n 
   WHERE n.EntityID = e.EntityID 
   ORDER BY RecordedOn DESC) AS Name
FROM Entities e
WHERE e.EntityID = 1234

This query will get latest name (if a name exists in the Names table) for the entity I specify. If a name for the entity does not exist, the Name column will be NULL.

An alternate (and in my experience seldom-used) way to do this is to use the MS SQL Server-specific operator OUTER APPLY. In short, APPLY runs a function (or in our example a query) for each row in the outer expression. You can think of the difference between CROSS APPLY and OUTER APPLY like the difference between INNER JOIN and LEFT OUTER JOIN, where the former runs the function for matched rows while the latter for all rows in the outer query. Rewriting the above query using the APPLY:

SELECT 
  ent.*
, name.Name
FROM Entities e
 OUTER APPLY (
  SELECT TOP 1 Name
  FROM Names n
  WHERE n.EntityID = e.EntityID
  ORDER BY RecordedOn DESC) name
WHERE e.EntityID = 1234

This query returns the identical results as the first one. If we turn on STATISTICS IO we will see that they perform the exact same number of scans and logical reads on both tables.

So the question now is which one is better to use. While I feel that using APPLY makes for cleaner code, it is not ANSI-SQL compatible since it is a MSSQL-specific operation. Further, APPLY may not be familiar to DBAs coming from other SQL implementations.

Using The Android DownloadManager

Since Gingerbread, Android has included support for handling long-running downloads via the DownloadManager. Using previous versions apps had to manually handle threading, displaying progress to the user, connectivity issues, and registering the downloaded media with the MediaStore (if applicable). In this example, I’m going to use the built-in DownloadManager to download an MP3 and handle its completion via a BroadcastReceiver.

Before initiating the download, we want to query the DownloadManager to see whether the file is currently being downloaded so we prevent duplicates.

DownloadManager mgr = (DownloadManager) 
    context.getSystemService(Context.DOWNLOAD_SERVICE);
boolean isDownloading = false;
DownloadManager.Query query = new DownloadManager.Query();
query.setFilterByStatus(
    DownloadManager.STATUS_PAUSED|
    DownloadManager.STATUS_PENDING|
    DownloadManager.STATUS_RUNNING|
    DownloadManager.STATUS_SUCCESSFUL);
Cursor cur = mgr.query(query);
int col = cur.getColumnIndex(
    DownloadManager.COLUMN_LOCAL_FILENAME);
for(cur.moveToFirst(); !cur.isAfterLast(); cur.moveToNext()) {
    isDownloading = (“local file path” == cur.getString(col));
}
cur.close();

I use a DownloadManager.Query to filter the information I want to get from the DownloadManager. I only care about the downloads that are either in the queue, are running, or have been successfully finished, and check to see whether the local path where I’m going to save the file exists already exists. If it does, I will not continue with the next step, actually requesting the file for downloading. Keep in mind that querying any Android service, including the DownloadManager, is a blocking action, so you may want to consider moving this code to another thread if you’re downloading large amounts of files as to not block the UI and give the appearance of unresponsiveness.

if (!isDownloading) {
    Uri source = Uri.parse(“remote url”);
    Uri destination = Uri.fromFile(new File(“local file path”));
 
    DownloadManager.Request request = 
        new DownloadManager.Request(source);
    request.setTitle(“file title”);
    request.setDescription(“file description”);
    request.setDestinationUri(destination);
    request.setNotificationVisibility(DownloadManager
        .Request.VISIBILITY_VISIBLE_NOTIFY_COMPLETED);
    request.allowScanningByMediaScanner();
 
    long id = mgr.enqueue(request);
}

To initiate a download, you must create a DownloadManager.Request that contains the source, destination, and any optional parameters you specify. In this example, I’m setting the title and description so the notification contains something besides the filename. I’m also specifying that I want the download visible as a notification during the actual download and when it’s completed. Lastly, I specify that I want the file to be added to the MediaStore once it’s finished. Calling enqueue on the DownloadManager will add the file and start the download, returning a long that can be used to easily identify which file finished.

Just downloading the file may be enough for some, but since I want to trigger an action once it’s completed (such as open the file, or add it to an app’s database), I will need to use a BroadcastReceiver to handle the DownloadManager’s completion.

public class DownloadReceiver extends BroadcastReceiver{
    @Override
    public void onReceive(Context context, Intent intent) {
        long receivedID = intent.getLongExtra(
            DownloadManager.EXTRA_DOWNLOAD_ID, -1L);
        DownloadManager mgr = (DownloadManager)
            context.getSystemService(Context.DOWNLOAD_SERVICE);
 
        DownloadManager.Query query = new DownloadManager.Query();
        query.setFilterById(receivedID);
        Cursor cur = mgr.query(query);
        int index = cur.getColumnIndex(
            DownloadManager.COLUMN_STATUS);
        if(cur.moveToFirst()) {
            if(cur.getInt(index) == 
                DownloadManager.STATUS_SUCCESSFUL){
                // do something
            }
        }
        cur.close();
    }
}

Once this class is registered to receive the broadcast intent “android.intent.action.DOWNLOAD_COMPLETE” (either programmatically or in our AndroidManifest.xml file), the onReceive() method will execute whenever a download is finished. The intent has the download ID as an extra, so I use that to again query the DownloadManager and check to see whether it completed successfully (a failure will trigger this intent as well, so this step is important). Since I stored the download ID when I queued it (returned from enqueue()), I can use it to easily figure out which file finished downloading. However, you can use the other columns in the DownloadManager to get the file’s title or filename.

Querying And Removing Media From The Android MediaStore

Android provides a way to register different type of media, such as audio, video, and images, for consumption by any app. This is convenient if your app is, say, a music player or an image editor. Android’s MediaStore is the provider for this meta data, and includes information about the media such as title, artist, size, and location.

If your application does any sort of media content creation, such as image editing or downloading audio from an external website, then you generally want to make that content accessible from any other apps that can consume it. When you create a file you can use the MediaScannerConnection to add the file and its metadata to the MediaStore.

If you delete the file from the file system, the metadata remains in the MediaStore until Android scans the system for new media, which typically happens when the system first boots up or can be called explicitly called in such a way:

sendBroadcast(new Intent(Intent.ACTION_MEDIA_MOUNTED, 
    Uri.parse("file://" + Environment.getExternalStorageDirectory() )));

While this method works, it is time and resource consuming, as basically the entire file system must be re-scanned. An alternative is to explicitly delete the file from the MediaStore. We’re going to discuss two ways to do this. The first is to query to MediaStore for the content, based on some predicate, and delete based on the unique ID the MediaStore identifies it by. The second, and easier, way to do it is to just specify the predicate in the delete statement. In these example I’m going to be deleting an audio file based on it’s file name and path, but you can easily use this to delete any type of media based on any known information (such as video duration, or image dimensions).

In querying the MediaStore, you should think of it as a SQL database. You need to form your query by specifying the table (the MediaStore’s external content table), the columns you need (the content’s ID), and the where clause (how to identify the content). To perform the actual query, we’re going to use the ContentResolver‘s query() method.

String[] retCol = { MediaStore.Audio.Media._ID };
Cursor cur = context.getContentResolver().query(
    MediaStore.Audio.Media.EXTERNAL_CONTENT_URI, 
    retCol, 
    MediaStore.MediaColumns.DATA + "='" + filePath + "'", null, null);
if (cur.getCount() == 0) {
    return;
}
cur.moveToFirst();
int id = cur.getInt(cur.getColumnIndex(MediaStore.MediaColumns._ID));
cur.close();

The first argument to query() specifies the columns we want returned, which in this case is only “_ID”. The second argument specifies that we want to look at the media stored on the external SD card (which would be internal storage on deices with no SD card). The third argument is the predicate which specifies what content we’re looking for. In this case, I’m identifying the file by it’s path in the file system (which is what is stored in the MediaColumns.DATA column). The fourth and fifth columns are the predicate’s arguments and the ordering, respectively. I’m including my predicate’s arguments in the predicate itself so that’s not necessary, and if your only looking for one piece of content and your predicate is specific enough to just return one row then the ordering doesn’t matter.

It is very important to make the predicate specific enough so that you’re guaranteed to get the exact ID you’re looking for. In my case I know that there can be only one file at a particular location, but you could use a combination of any columns (such as title, artist, and album) to find the content. Check out the MediaColumns for all the possibilities.

Once you perform the actual query, you’ll want to check to see whether the MediaStore actually contains the content you’re trying to delete. If you don’t handle this in some way your app will crash while trying to iterate through the cursor. Once you confirm that the query returned some data, grab the ID by moving the cursor to it’s first position, reading the “_ID” column, and closing the cursor. It’s very important that you remember to close the cursor once you’ve finished using it. Your app won’t crash, but you’ll get memory leaks and complaints in LogCat.

Now that we have the ID that the MediaStore associates with our content, we can call ContentResolver’s delete() method similar to how we called its query() method.

Uri uri = ContentUris.withAppendedId(MediaStore.Audio.Media.EXTERNAL_CONTENT_URI, 
    id);
context.getContentResolver().delete(uri, null, null);

The delete() method takes 3 arguments: the Uri to be deleted, the predicate, and the predicate arguments. We form the Uri by appending the ID we discovered by querying the MediaStore to the Uri of the audio files on external storage. Since we know exactly which row we want to delete, we don’t need to specify the predicate or the predicate’s arguments.

The second method to delete the content from the MediaStore takes advantage of the fact that querying and deleting from it are performed almost identically.

context.getContentResolver().delete(MediaStore.Audio.Media.EXTERNAL_CONTENT_URI, 
    MediaStore.MediaColumns.DATA + "='" + path + "'", null);

We can use the predicate of the delete() method to specify exactly what we want to delete, rather than having to query for it beforehand. While this method is more efficient (no extra query, no cursors to deal with), it has some pitfalls. You have no way of explicitly confirming what you’re deleting. You’re also not able to do advanced queries with this method, such as if you wanted to delete the most recently added content (which you could do by ordering the query based on the DATE_ADDED column). However, both ways give you a way to confirm what you’ve deleted since the delete() method returns the number of rows that it deleted as an integer.

Attaching Lasers To A Syma S107G RC Helicopter

Syma 107G With LasersI received the Syma 107G R/C Helicopter as a present recently. After a few days of flying it around my apartment giddily (and tormenting the person who got it for me) I naturally began to think about different modifications I could perform to make it unique. I thought about adding stronger motors, replacing or a second battery, and changing the propellers. While all of these are still good mods, they weren’t immediately practical because I didn’t have the materials to do any of them.

What I do have are over a dozen laser modules from an unfinished project. If you are coming up short thinking of reasons to add lasers to an RC helicopter, this guide is not for you. If you can’t stop thinking of reasons to do so, let’s get started.

Before we get started, we need to figure out the parts and tools requirements for this project:

  • - Laser module(s), must operate at 4.2V (I am using these)
  • - Phillips head screwdriver, size 00
  • - Soldering iron
  • - Super glue or equivalent

The goal is to attach the lasers in parallel to the on/off switch and to ground. This way the lasers will turn off when you turn the helicopter off. Optionally you can install a separate switch just for the lasers, which would just require putting the switch in place of the one built into the helicopter, but I’m intending this as a permanent mod.

We start by removing the front plastic body by removing the two screws near the top. Be careful when removing the body because the LED in the nose of the body is attached with double-sided tape. Ripping any wires could not only damage the LED but the entire helicopter.

Next, take off the metal panel on the right side by removing these five screws. Be careful with the support beam connecting the main body and the tail of the helicopter.

With the innards exposed, we can see where the switch connects to the main circuit board, and the appropriate pad to attach the positive lead of the laser modules.

I recommend soldering a spare wire to the pad and running it to the back of the helicopter, as it creates a single point to which you can attach the laser modules’ leads once you’ve secured them to the undercarriage.

Next, look on the other side and find where the battery’s negative lead connects to the main circuit board. We want to connect the lasers’ negative lead directly to this pad. Again, I recommend using a spare wire and running it to the back of the helicopter.

After soldering, turn the helicopter on to make sure you didn’t short anything. If it fails to turn on after soldering those two points, closely examine your work to see if you damaged a nearby resistor or transistor.

If everything works properly, it’s time to glue the lasers to the undercarriage. You must pay attention to exactly where on the undercarriage you glue them so you maintain proper balance, as well as make sure the lasers are both pointing the same direction. Since there wasn’t quite enough clearance with the laser modules I put some cardboard between them and the body of the helicopter. Once they were glued and dried, I soldered the positive leads of the module to the wire I attached to the switch and the negative leads to the wire I attached to the negative lead of the battery.

That’s about it. If everything went as planned you should have some bright laser cannons flying around your room. Leave me comments with any questions or suggestions for other mods.

Welcome

to my website. Here I will be discussing electronics, android development and programming in general, and music. Hopefully these posts will help you in some fashion. If not, at least I’ve increased my page count.