Submit Hint Search The Forums LinksStatsPollsHeadlinesRSS
14,000 hints and counting!

10.4: Manually clean Safari RSS articles Web Browsers
Tiger only hintI was playing with my own RSS feed, changing titles and whatnot. When I was done, I found out that Safari could not remove the old RSS articles for my feed only! I had the choice to make them expire (I currently have that setting to remove old RSS articles to 'Never') or force the removal of all RSS articles for all the feeds I read daily. Not wanting to do either, I started looking for a way to remove specific articles from Safari's RSS database.

After some digging, I found out that Safari store the RSS information in a SQLite database found in ~/Library/Syndication. Here's the procedure I used to clean my Safari's RSS database of the articles I didn't want. In the Finder, browse to ~/Library/Syndication. Copy the file you find there (mine was called Database3) to your Desktop (as a backup).

Get a copy of SQLiteManager, close Safari, and start SQLiteManager. When asked What would you like to do?, answer Open A Database. Hit Command-Shift-G, enter ~/Library/Syndication, and open the file you see there. Click the Manage tab. In the first combo box, choose Sources and click the Query button. A list of all known RSS feeds is shown. Select a source you want to clean, and click the Remove button. Confirm the removal. Repeat until you removed all sources you wanted to clean.

Next, click the SQL tab, and enter the following query:
DELETE FROM Articles WHERE source_id IN 
(SELECT DISTINCT Articles.source_id FROM Articles 
LEFT JOIN Sources ON Articles.source_id = Sources.id 
WHERE Sources.id IS NULL)
Click the Execute button; this will remove all articles associated with the non-existent sources (the ones you just deleted). You're done! You can now close SQLiteManager and restart Safari.

You can also delete individual articles by browsing the Articles table in SQLiteManager. The trial version has a 50 results per query limit, so you won't be able to see more than 50 articles, unless you tweak the query setting at the top of the Manage tab. You can also delete a bunch of articles in one shot using appropriate SQL queries. Articles older than 2005-01-01 from the Apple Hot News feed, for example. Feel free to ask if you don't know SQL.
    •    
  • Currently 3.00 / 5
  • 1
  • 2
  • 3
  • 4
  • 5
  (2 votes cast)
 
[12,823 views]  

10.4: Manually clean Safari RSS articles | 8 comments | Create New Account
Click here to return to the '10.4: Manually clean Safari RSS articles' hint
The following comments are owned by whoever posted them. This site is not responsible for what they say.
10.4: Manually clean Safari RSS articles
Authored by: fungus on Aug 12, '05 12:36:28PM

Thanks for the amazing tip. Safari RSS feed info and articles are stored in an SQLite database! yay! The possibilities are almost endless now.

That SQL query to clear out articles of removed sources can be simplified to:
DELETE FROM Articles WHERE source_id NOT IN(SELECT id FROM Sources);



[ Reply to This | # ]
10.4: Manually clean Safari RSS articles
Authored by: dfbills on Aug 12, '05 01:06:35PM

I never would have guessed that.. wow. Very cool- now if you could sync that data with .Mac across multiple macs...

---
-d



[ Reply to This | # ]
10.4: Manually clean Safari RSS articles
Authored by: adrianm on Aug 12, '05 01:13:17PM
Not sure you need to download anything...

sqlite3 ~/Library/Syndication/Database3

from terminal did the trick for me.

Maybe sqlite3 comes with Xcode - not sure.

[ Reply to This | # ]

10.4: Manually clean Safari RSS articles
Authored by: guybrush on Aug 12, '05 06:48:20PM

Combined comments from above into 1 command :)

sqlite3 ~/Library/Database3 'DELETE FROM Articles WHERE source_id NOT IN(SELECT id FROM Sources)' Also @fungus, why are the possibilities endless now because its stored in a sqlite database?

[ Reply to This | # ]
10.4: Manually clean Safari RSS articles
Authored by: gaoshan on Aug 16, '05 01:09:21PM

You accidentally left out the "Syndication" directory from that... Here is the whole command:

sqlite3 ~/Library/Syndication/Database3 'DELETE FROM Articles WHERE source_id NOT IN(SELECT id FROM Sources)'



[ Reply to This | # ]
10.4: Manually clean Safari RSS articles
Authored by: fungus on Aug 19, '05 12:27:32PM

All RSS feeds that Safari RSS tracks are stored in this database. That means that Safari RSS does the heavy lifting of downloading and parsing the feeds. With direct access to the database one could write their own RSS aggregator without any knowledge of HTTP retrieval or RSS parsing.

Example: I wrote a ticker program to display article titles across the bottom of the screen, clicking the titles marked the item as read, and opened the URL in Safari. All this just required a few SQL queries, and absolutely no RSS parsing.



[ Reply to This | # ]
10.4: Manually clean Safari RSS articles
Authored by: gboudrea on Aug 12, '05 07:16:11PM

Don't think so. I don't have Xcode installed and sqlite3 works from Terminal indeed.



[ Reply to This | # ]
10.4: Manually clean Safari RSS articles
Authored by: gaoshan on Aug 16, '05 01:02:24PM

SQLiteManager is a $39 program! Yikes.

If you are not comfy with using the command line you can download the free, open source SQLite Database Browser and use that in place of SQLiteManager.



[ Reply to This | # ]