Jonas John07

## chapter_marks.sh
# read a podcast mp3 file and print a list of its chapter marks
ffprobe -v quiet -show_chapters -pretty -print_format csv *.mp3

# more readable, timestamps with seconds
ffprobe -v quiet -show_chapters -pretty -print_format csv *.mp3 | awk -F "," '{print $5 " " $7 " " $8}' | sed 's/\.[0-9]*\ /\ /g'

# more readable, timestamps without seconds
ffprobe -v quiet -show_chapters -pretty -print_format csv *.mp3 | awk -F "," '{print $5 " " $7 " " $8}' | sed 's/\:[0-9][0-9]\.[0-9]*\ /\ /g'

## lightroom-exporting.sql
sqlite3 backup-catalog.lrcat
.output export.txt

-- get all keywords with their id
SELECT id_local, name FROM AgLibraryKeyword;

-- get list of all files with keyword id 88890
SELECT stackParent_fileName, stackParent____colorLabels, rating FROM Adobe_images AS a JOIN AgLibraryKeywordImage AS b WHERE b.tag=88890 AND a.id_local=b.image;

-- get list of all files for keyword Bob

## open_wunderlist_tasks.sh
# counts the number of open tasks in Wunderlist across all lists
# (pretty sure there's a more elegant way of doing this but works for now)
sqlite3 "/Users/USERNAME/Library/Containers/com.wunderkinder.wunderlistdesktop/Data/Library/Application Support/Wunderlist/WKModel.sqlite" "select ZTITLE from ZRESOURCE where ZOWNER1 is not 1 and ZPARENTTASK is null and ZCOMPLETEDAT is null" | wc -l

## HLS_dvr.sh
# required: ffmpeg (e.g. from homebrew), terminal-notifier from https://github.com/alloy/terminal-notifier
# you can schedule this with launchd to run e.g. weekly

# Specify in seconds how long the script should record (default here is 1 hour).
seconds=3600

# Date format for the recording file name
DATE=`date "+%d-%m-%y_%H-%M"`

# start ffmpeg recording

## HLS_to_mp4
ffmpeg -re -i http://url.com/playlist.m3u8 -c copy -bsf:a aac_adtstoasc output.mp4

## Instacast 2 favorites export.sh
# get the db.sqlite file from Instacast (e.g. with iExplorer 2 http://www.macroplant.com/downloads.php)
# copy it on your desktop and run
sqlite3 db.sqlite
.output instacast-favorites.txt
SELECT FEEDS.title, EPISODES.title, EPISODES.link, datetime(EPISODES.pubdate, 'unixepoch', 'localtime'), CONSUMABLES.guid
FROM CONSUMABLES
LEFT OUTER JOIN EPISODES on CONSUMABLES.guid = EPISODES.guid
LEFT OUTER JOIN FEEDS on EPISODES.feed_id = FEEDS.id
WHERE CONSUMABLES.starred = 1;

## iOS messages export.sh
# Note: this is not a fully functioning script! You will need to edit paths and other variables manually and it's adviced to run each command manually one after another

# install dependencies
# http://www.securitylearn.net/tag/deep-analysis-of-itunes-backup/
sudo ARCHFLAGS='-arch i386 -arch x86_64' easy_install pycrypto
sudo easy_install M2crypto construct progressbar

# clone the decrypt tool source code
# hg stands for mercurial
hg clone https://code.google.com/p/iphone-dataprotection/

## Wunderlist 2 sqlite Export.sh
# location of the Wunderlist 2 database (App Store version)
~/Library/Containers/com.wunderkinder.wunderlistdesktop/Data/Library/Application Support/Wunderlist/WKModel.sqlite

# example
# get title of all tasks in list 400
sqlite3 WKModel.sqlite "SELECT rowid, ZTITLE FROM ZRESOURCE WHERE ZTASKLIST = 400 ORDER BY rowid;"

## Short-URL converter.sh
#!/bin/bash
URL=$(pbpaste)
curl "$URL" --insecure --write-out %{redirect_url}

## open_chrome_tab_with_safari.applescript
--
-- open currently active Chrome tab with Safari
-- forked from https://gist.github.com/3151932 and https://gist.github.com/3153606
--
property theURL : ""
tell application "Google Chrome"
  set theURL to URL of active tab of window 0
end tell

if appIsRunning("Safari") then
	# read a podcast mp3 file and print a list of its chapter marks
	ffprobe -v quiet -show_chapters -pretty -print_format csv *.mp3

	# more readable, timestamps with seconds
	ffprobe -v quiet -show_chapters -pretty -print_format csv .mp3 \| awk -F "," '{print $5 " " $7 " " $8}' \| sed 's/\.[0-9]\ /\ /g'

	# more readable, timestamps without seconds
	ffprobe -v quiet -show_chapters -pretty -print_format csv .mp3 \| awk -F "," '{print $5 " " $7 " " $8}' \| sed 's/\:[0-9][0-9]\.[0-9]\ /\ /g'
	sqlite3 backup-catalog.lrcat
	.output export.txt

	-- get all keywords with their id
	SELECT id_local, name FROM AgLibraryKeyword;

	-- get list of all files with keyword id 88890
	SELECT stackParent_fileName, stackParent____colorLabels, rating FROM Adobe_images AS a JOIN AgLibraryKeywordImage AS b WHERE b.tag=88890 AND a.id_local=b.image;

	-- get list of all files for keyword Bob
	# counts the number of open tasks in Wunderlist across all lists
	# (pretty sure there's a more elegant way of doing this but works for now)
	sqlite3 "/Users/USERNAME/Library/Containers/com.wunderkinder.wunderlistdesktop/Data/Library/Application Support/Wunderlist/WKModel.sqlite" "select ZTITLE from ZRESOURCE where ZOWNER1 is not 1 and ZPARENTTASK is null and ZCOMPLETEDAT is null" \| wc -l
	# required: ffmpeg (e.g. from homebrew), terminal-notifier from https://github.com/alloy/terminal-notifier
	# you can schedule this with launchd to run e.g. weekly

	# Specify in seconds how long the script should record (default here is 1 hour).
	seconds=3600

	# Date format for the recording file name
	DATE=`date "+%d-%m-%y_%H-%M"`

	# start ffmpeg recording
	# get the db.sqlite file from Instacast (e.g. with iExplorer 2 http://www.macroplant.com/downloads.php)
	# copy it on your desktop and run
	sqlite3 db.sqlite
	.output instacast-favorites.txt
	SELECT FEEDS.title, EPISODES.title, EPISODES.link, datetime(EPISODES.pubdate, 'unixepoch', 'localtime'), CONSUMABLES.guid
	FROM CONSUMABLES
	LEFT OUTER JOIN EPISODES on CONSUMABLES.guid = EPISODES.guid
	LEFT OUTER JOIN FEEDS on EPISODES.feed_id = FEEDS.id
	WHERE CONSUMABLES.starred = 1;
	# Note: this is not a fully functioning script! You will need to edit paths and other variables manually and it's adviced to run each command manually one after another

	# install dependencies
	# http://www.securitylearn.net/tag/deep-analysis-of-itunes-backup/
	sudo ARCHFLAGS='-arch i386 -arch x86_64' easy_install pycrypto
	sudo easy_install M2crypto construct progressbar

	# clone the decrypt tool source code
	# hg stands for mercurial
	hg clone https://code.google.com/p/iphone-dataprotection/
	# location of the Wunderlist 2 database (App Store version)
	~/Library/Containers/com.wunderkinder.wunderlistdesktop/Data/Library/Application Support/Wunderlist/WKModel.sqlite

	# example
	# get title of all tasks in list 400
	sqlite3 WKModel.sqlite "SELECT rowid, ZTITLE FROM ZRESOURCE WHERE ZTASKLIST = 400 ORDER BY rowid;"
	#!/bin/bash
	URL=$(pbpaste)
	curl "$URL" --insecure --write-out %{redirect_url}
	--
	-- open currently active Chrome tab with Safari
	-- forked from https://gist.github.com/3151932 and https://gist.github.com/3153606
	--
	property theURL : ""
	tell application "Google Chrome"
	set theURL to URL of active tab of window 0
	end tell

	if appIsRunning("Safari") then