notedb.rb

As the number of notes I've taken for class increases, searching has become increasingly difficult. I wrote this script a little while ago, and it's made keeping track of my notes both easy and efficient.

notedb.rb

#!/usr/bin/ruby
require 'sqlite3'

def init_db(filename)
db = SQLite3::Database.new(filename)
db.execute('CREATE TABLE IF NOT EXISTS notes(id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, dir TEXT, mtime TEXT, content TEXT)')
db.execute('PRAGMA auto_vacuum = 1')
return db
end


def recurse_add(db, root)
rdir = Dir.open(root)
rdir.each do |file|
fpath = File.join(root, file)
if File.directory?(fpath)
if file != '.' and file != '..'
recurse_add(db, fpath)
end
elsif File.file?(fpath)
if file =~ /^[0-9]{4,}-[0-1][0-9]-[0-3][0-9]:.*\.tex$/
count = db.get_first_value('SELECT COUNT(*) FROM notes WHERE name = ? AND dir = ?', file, root).to_i
if count == 0
puts 'NEW: ' + file
f = File.open(fpath, 'r')
mtime = f.mtime
db.execute('INSERT INTO notes(name, dir, mtime, content) VALUES(?, ?, ?, ?)', file, root, mtime.strftime('%FT%T%z'), f.read)
f.close
else
f = File.open(fpath, 'r')
mtime = f.mtime
content = f.read
f.close
db.execute('SELECT id, content FROM notes WHERE name = ? AND dir = ?', file, root) do |info|
if info[1] != content
puts 'UPDATE: ' + file
db.execute('UPDATE notes SET content = ?, mtime = ? WHERE id = ?', content, mtime.strftime('%FT%T%z'), info[0])
end
end
end
end
else
puts 'No such file or directory: ' + file
end
end
rdir.close
end


if ARGV.length > 0 and ENV.key?('EDITOR')
db = nil
begin
db = init_db(File.join(ENV['HOME'], '.notedb'))
mode = ARGV.shift
case mode
when 'add'
if ARGV.length > 0
ARGV.each do |file|
puts file
if File.directory?(file)
recurse_add(db, File.expand_path(file))
elsif File.file?(file)
f = File.open(file, 'r')
db.execute('INSERT INTO notes(name, dir, content)', file, File.expand_path(File.dirname(file)), f.read)
f.close
else
$stderr.puts 'No such file or directory: ' + file
end
end
else
$stderr.puts 'Usage: ' + $0 + ' add f1 [ f2 .. fn ]'
end
when 'search'
if ARGV.length > 0
term = Regexp.compile(ARGV.shift)
if ARGV.length > 0
ARGV.each do |name|
db.execute('SELECT name, dir, content FROM notes WHERE dir GLOB ?', '*' + File.expand_path(name) + '*') do |row|
row[2].split(/\n/).each do |line|
if line =~ term
puts row[0] + ':' + line
end
end
end
end
else
db.execute('SELECT name, dir, content FROM notes') do |row|
row[2].split(/\n/).each do |line|
if line =~ term
puts row[0] + ':' + line
end
end
end
end
else
$stderr.puts 'Usage: ' + $0 + ' search term [ dir ]'
end
when 'get'
if ARGV.length > 1
mode = ARGV.shift
case mode
when 'name', 'iname'
qstring = ''
if mode == 'iname'
qstring = 'SELECT name, dir, mtime FROM notes WHERE name LIKE ?'
else
qstring = 'SELECT name, dir, mtime FROM notes WHERE name GLOB ?'
end
ARGV.each do |name|
if mode == 'iname'
name = '%' + name + '%'
else
name = '*' + name + '*'
end
db.execute(qstring, name) do |row|
mtime = DateTime.strptime(row[2])
printf "%-70s (modified %s)\n", row[0], mtime.strftime('%Y-%m-%d')
end
end
when 'dir', 'idir'
qstring = ''
if mode == 'idir'
qstring = 'SELECT name, dir, mtime FROM notes WHERE dir LIKE ?'
else
qstring = 'SELECT name, dir, mtime FROM notes WHERE dir GLOB ?'
end
ARGV.each do |name|
if mode == 'iname'
name = '%' + name + '%'
else
name = '*' + name + '*'
end
db.execute(qstring, name) do |row|
mtime = DateTime.strptime(row[2])
printf "%-70s (modified %s)\n", row[0], mtime.strftime('%Y-%m-%d')
end
end
when 'latest', 'ilatest', 'elatest'
pnewest = DateTime.new
rep = ''
case mode
when 'elatest'
qstring = 'SELECT name, dir, mtime FROM notes WHERE dir = ?'
when 'ilatest'
qstring = 'SELECT name, dir, mtime FROM notes WHERE dir LIKE ?'
when 'latest'
qstring = 'SELECT name, dir, mtime FROM notes WHERE dir GLOB ?'
end
ARGV.each do |name|
case mode
when 'ilatest'
name = '%' + name + '%'
when 'latest'
name = '*' + name + '*'
end
db.execute(qstring, name) do |row|
mtime = DateTime.strptime(row[2])
if mtime > pnewest
pnewest = mtime
rep = row[0]
end
end
end
if rep.length
puts rep
end
else
$stderr.puts 'Usage: ' + $0 + ' get [ name | iname | dir | idir | latest | ilatest | elatest ] match'
end
else
$stderr.puts 'Usage: ' + $0 + ' get [ name | iname | dir | idir | latest | ilatest | elatest ] match'
end
when 'list'
db.execute('SELECT name, mtime FROM notes') do |row|
mtime = DateTime.strptime(row[1])
printf "%-70s (modified %s)\n", row[0], mtime.strftime('%Y-%m-%d')
end
when 'help'
$stderr.puts 'Usage: ' + $0 + ' [ add | search | get | list ]'
$stderr.puts "\tEDITOR must be set: currently \"" + (ENV['EDITOR'] and ENV['EDITOR'] or '') + "\""
else
raise 'Unknown mode: ' + mode
end

ensure
if db
db.close
end
end
else
$stderr.puts 'Usage: ' + $0 + ' [ add | search | get | list ]'
$stderr.puts "\tEDITOR must be set: currently \"" + (ENV['EDITOR'] and ENV['EDITOR'] or '') + "\""
end