Scrubbing Assets from Podcast Producer

At some point, you may find that you would like to remove all episodes from Podcast Producer that were brought in using a specific workflow, or based on a specific keyword, a string in the title, a date, or the user that created the episodes. All of these attributes are trapped in the db.sqlite3 database for Podcast Producer. This database is stored in the Server directory of your shared library. Within this database there is a table called episodes. Using that table you can locate all episodes that match the given pattern. To query, you will use the sqlite3 command and identify the database path. A very basic incantation of this command would be to show all of the data for a given table, which in our example would be “episodes”:

sqlite3 /Volumes/xsanVolume/Library/PodcastProducer/Shared/Server/db.sqlite3 ‘SELECT * FROM episodes’

You could then expand this to limit results based on a pattern match. Here, we will have sqlite3 return the uuid for episodes that have a workflow_uuid of AAAAAAAA-BBBB-CCCC-DDDD-EEEEEEEEEEEE:

sqlite3 /Volumes/xsanVolume/Library/PodcastProducer/Shared/Server/db.sqlite3 ‘SELECT uuid FROM episodes WHERE workflow_uuid=”AAAAAAAA-BBBB-CCCC-DDDD-EEEEEEEEEEEE”‘

We could also change it up slightly and look for episodes that were created by a user with a shortname of cedge:

sqlite3 /Volumes/xsanVolume/Library/PodcastProducer/Shared/Server/db.sqlite3 ‘SELECT uuid FROM episodes WHERE author_shortname=”cedge”‘

The sqlite3 commands will return a set of uuids. Once you have a list of uuids, you can go into the Content directory in your shared library and you will see each listed there based on the date they were created (inside of their date folders). Date is also a field in the sqlite3 database, but given the ease of recursively performing tasks I’m not sure it will be required. The uuids will have a .prb extension and you can then piped paths with the added extension out of your command, into an array or use them for some other action, thus allowing for archiving, removing and even performing further tasks to the assets that live within the actual bundle, including hooking into transmogrifier to link to Final Cut Server.