Michael Jay Lissner

Reworking the CourtListener Datamodel

2014-06-18T00:00:00-07:00

Brian and I have been hard at work the past week figuring out how to make CourtListener able to understand more that one document type. Our goal right now is to make it possible to add:

oral arguments and other audio content,
video content if it’s available,
content from RECAP, and
thousands of ninth circuit briefs that Resource.org has recently scanned

The problem with our current database is that it’s not organized in a way that supports linkages between content. So, if we have the oral argument and the opinion from a single case, we have no way of pointing them at each other. Turns out this is a sticky problem.

The solution we’ve come up with is an architecture like the following:

(we also have a more detailed version and an editable version)

And eventually, this will also have a Case table above the docket that allows multiple dockets to be associated with a single case. For now though, that’s moot, as we don’t have anyway of figuring out which dockets go together.

The first stage of this will be to add support for oral arguments, since they make a simple case to work with. Once that’s complete the next stage will be either to add the RECAP documents or those from Resource.org.

URLs

Since this is such a big change, we’re also taking this opportunity to re-work our URLs. Currently, they look like this:

/$court/$alpha-numeric-id/case-name-of-the-case/

For example:

https://www.courtlistener.com/scotus/yjn/roe-v-wade/

A few things bug me about that. First, it doesn’t tell you anything about what kind of thing you can expect to see if you click that link. Second, the alpha-numeric ID is kind of lame. It’s just a reference to the database primary key for the item, and we should just show that value (in this case, “yjn” means “108713”). To fix both of these issues, the new URLs will be:

/opinion/$numeric-id/case-name/

So:

https://www.courtlistener.com/opinion/108713/roe-v-wade/

That should be easier to read and should tell you what type of item you’re about to look at. Don’t worry, the old URLs will keep working just fine.

And the rest of the new URLs will be:

/oral-argument/$numeric-id/case-name/
/docket/$numeric-id/case-name/

and eventually:

/recap/$numeric-id/case-name/

API

We expect these changes to come with changes to the API, so we’ll likely be releasing API version 1.1 that will add support for dockets and oral arguments.

The current version 1.0 should keep working just fine, since we’re not changing any of the underlying data, but I expect that it will have some changes to the URLs and things like that. I’ll be posting more about this in the CourtListener dev list. as the changes become more clear and as we sort out what a fair policy is for the deprecation of old APIs.

Changes and Plans at CourtListener.com

2010-11-04T23:30:21-07:00

A few weeks ago, we made a fairly major change at CourtListener.com to include ID numbers in all of our case URLs. This change meant that links that were previously like this:

http://courtlistener.com/scotus/Wong-v.-Smith/

Are now like this:

http://courtlistener.com/scotus/V5o/wong-v-smith/

Most of the old links should continue to work, but using the new links should be much faster and more reliable. The major difference between the two is the ID number, which is encoded as a set of numbers (in this case V5o). This ID corresponds directly with the ID number in our database, aiding us greatly in serving up cases quickly and accurately.

Around the same time as this change, we added social networking links to all of our case pages to make them easier to share with friends and colleagues. These links use our new tiny domain, http://crt.li/, and should thus be ideal for websites like Twitter or Reddit.

In the next few months we will be getting a major new server, and will be migrating our data to it. This will allow us to serve more data, and—drum roll please—will allow us to begin serving audio content on the site. That’s right, in the next few months, we will begin getting oral arguments from the circuit courts, and will be serving it directly to you on the case pages.

We also have plans to revisit our search interface in order to add date filtering and query building so look for that soon.

As always, we welcome your feedback and support, so don’t hesitate to get in touch with us if you have any questions or suggestions.

Script to Clean Up F-Spot Database

2009-10-14T00:30:12-07:00

One of the more popular photo management applications for Linux is f-spot, but unfortunately it has a rather glaring bug. It uses a sqlite3 database internally to track which pictures you’ve imported into it, what tags they have, etc. However, if you delete, move or rename any of the files that f-spot is tracking, the next time you browse to that photo within f-spot, it will crash the program. It’s annoying, and there’s no particularly easy way to deal with it…until now.

The script below simply iterates through all of the photos that are in f-spot’s database, and checks to see if those photos exist on your hard drive. If you run it in demo-mode, it will show you which files look problematic, and if you run it in normal mode, it will delete those database entries so that your database is cleaned up (but not before backing up your database, just in case).

In my very limited testing, it works very well, any additional feedback or bug reporting is more than welcome.

#!/bin/bash

# A script to find missing files in the f-spot database, and then delete them.
# At present these files crash f-spot. It's frustrating as all hell.

echo "Welcome to the f-spot database cleaner. All the usual disclaimers apply, as you might imagine."
echo "What would you like to do: " 
echo "  1) Run in demo-mode " 
echo "  2) Clean up your f-spot database" 
echo "  3) Quit" 
read -p "Your choice: " choice

case $choice in 
    1) demomode="true";;
    2) demomode="false";;
    3) exit 0;;
esac

# With that beginning stuff out of the way, let us do some functions
# First, a function to gather the database contents and to print out the ones that are orphans

function findAndFixOrphans {
    # find our db, and set a var. Checking for XDG path first, since it's the more recent location of the db
    if [ -f $XDG_CONFIG_DIR/f-spot/photos.db ] #checks if the $XDG_CONFIG_DIR variable is in use
    then
        DBPATH=$XDG_CONFIG_DIR/f-spot/photos.db
    elif [ -f $HOME/.config/f-spot/photos.db ] #uses the default $XDG location, if that's being used.
    then
        DBPATH=$HOME/.config/f-spot/photos.db
    elif [ -f $HOME/.gnome2/f-spot/photos.db ] #uses the old location of the DB, if the former aren't in use.
    then
        DBPATH=$HOME/.gnome2/f-spot/photos.db
    else
        echo "Error: Could not find database. Damn." 
        exit 1
    fi

    # Select the filenames, and put them in a variable.
    filenames=$(sqlite3 $DBPATH "SELECT URI FROM PHOTOS")
    filenames_versions=$(sqlite3 $DBPATH "SELECT URI FROM PHOTO_VERSIONS")

    # Chomp off the first instance of file://, and replace the rest with newlines.
    filenames=$(echo $filenames | sed 's/file:\/\///' | sed 's/file:\/\//\n/g'  )
    filenames_versions=$(echo $filenames_versions | sed 's/file:\/\///' | sed 's/file:\/\//\n/g' )

    if [ $demomode == "true" ]
    then            
        while read -r line
        do
            # Decode the filename
            decodedLine=$(echo -e "${line//\%/\\x}")
            if [ ! -f  "$decodedLine" ] 
            then
                # If the file doesn't exist, we output the filename, if in demomode, or we fix it if we are not in demomode.
                echo  "Errant record found in the photos table: $decodedLine"
            fi
        done <<< "$filenames"

        # We do the same for the photo_versions table
        while read -r line
        do
            # Decode filename
            decodedLine=$(echo -e "${line//\%/\\x}")
            if [ ! -f "$decodedLine" ]
            then
                # If the file doesn't exist, we output the filename
                echo "Errant record found in the photo_versions table: $decodedLine"
            fi
        done <<< "$filenames_versions"

    else
        # We backup the database, and make the correction
        cp $DBPATH $DBPATH.`date -I`.bak
        if [ $? -eq 0 ]
        then
            #The backup worked, tell the user.
            echo "Your database has been backed up to $DBPATH.`date -I`.bak"
        else
            echo "Error backing up your database."
            exit 3
        fi

        # First we do the photos table
        while read -r line
        do
            # Decode the filename
            decodedLine=$(echo -e "${line//\%/\\x}")

            if [ ! -f "$decodedLine" ]
            then
                # Do some sql here.
                foo="file://${line}"
                echo -n "Deleting URI $line from the database table photos..."
                sqlite3 $DBPATH "DELETE FROM PHOTOS WHERE uri = '$foo'"
                echo "done."
            fi
        done <<< "$filenames"

        # Then we do the photo_versions table
        while read -r line
        do
            # Decode the filename
            decodedLine=$(echo -e "${line//\%/\\x}")

            if [ ! -f "$decodedLine" ]
            then
                #Do some sql stuff
                foo="file://${line}"
                echo -n "Deleting URI $line from the database table photo_versions..."
                sqlite3 $DBPATH "DELETE FROM PHOTO_VERSIONS WHERE uri = '$foo'"
                echo "done."
            fi
        done <<< "$filenames_versions"

    fi

}

if [ "$demomode" == "true" ]
then
    echo "Great. Proceeding in demomode."

    findAndFixOrphans

    echo "Demomode successfully finished. Exiting."
    exit 0;
elif [ "$demomode" == "false" ]
then
    echo "Great. Cleaning up your database."

    findAndFixOrphans

    echo "Database cleaned successfully."
    exit 0;
else
    echo "Something strange happened. See the script for details. Exiting."
    exit 2;
fi