dirnotes/dirnotes

#!/usr/bin/python3
""" a simple gui or command line app
to view and create/edit file comments

comments are stored in an SQLite3 database
	default ~/.dirnotes.db
where possible, comments are duplicated in 
	xattr user.xdg.comment

	some file systems don't allow xattr, and even linux
	doesn't allow xattr on symlinks, so the database is
	considered primary

	these comments stick to the symlink, not the deref

nav tools are enabled, so you can double-click to go into a dir, and 
	there's an UP arrow to navigate back

"""


helpMsg = """<h1>Dirnotes</h1><h3>Overview</h3>
This app allows you to add comments to files. The comments are stored in 
a database, and where possible, saved in the xattr (hidden attributes) 
field of the file system.
<p>
You can sort the directory listing by clicking on the column heading.
<p>
Double click on directory names to navigate the file system. Hover over
fields for more information.
<p>
<h3>xattr extended attributes</h3>
The xattr property is handy, but suffers from a few problems:
<ul>
<li>is not implemented on FAT/VFAT/EXFAT file systems (like USB sticks)
<li>xattrs are not (by default) copied with the file when it's duplicated 
or backedup
<li>xattrs are not available for symlinks
<li>many programs which edit files do not preserve the xattrs during file-save
</ul>
When the database version of a comment differs from the xattr version, the
comment box gets a light yellow background.
"""

import sys,os,argparse,stat
#~ from dirWidget import DirWidget
from PyQt5.QtGui import *
from PyQt5.QtWidgets import *
from PyQt5.QtCore import Qt
import sqlite3, time

VERSION = "0.2"
COMMENT_KEY = "user.xdg.comment"
DATABASE_NAME = "~/.dirnotes.db"
# convert the ~/ form to a fully qualified path
DATABASE_NAME = os.path.expanduser(DATABASE_NAME)

class dnDataBase:
	''' the database is flat
		fileName: fully qualified name
		st_mtime: a float
		size: a long
		comment: a string
		comment_time: a float, the time of the comment save
		this is effectively a log file, as well as a resource for a restore
			(in case a file-move is done without comment)
		the database is associated with a user, in the $HOME dir
	'''
	def __init__(self):
		'''try to open the database; if not found, create it'''
		try:
			self.db = sqlite3.connect(DATABASE_NAME)
		except sqlite3.OperationalError:
			print("Database %s not found" % DATABASE_NAME)
			raise OperationalError
		c = self.db.cursor()
		try:
			c.execute("select * from dirnotes")
		except sqlite3.OperationalError:
			print("Table %s created" % ("dirnotes"))
			c.execute("create table dirnotes (name TEXT, date DATETIME, size INTEGER, comment TEXT, comment_date DATETIME)")
		
	def getData(self, fileName):
		c = self.db.cursor()
		c.execute("select * from dirnotes where name=? and comment<>'' order by comment_date desc",(os.path.abspath(fileName),))
		return c.fetchone()
	def setData(self, fileName, comment):
		''' TODO: convert filename to canonical '''
		c = self.db.cursor()
		s = os.lstat(fileName)
		print ("params: %s %s %d %s %s" % ((os.path.abspath(fileName), 
				dnDataBase.epochToDb(s.st_mtime), s.st_size, comment, 
				dnDataBase.epochToDb(time.time()))))
		try:
			c.execute("insert into dirnotes values (?,datetime(?,'unixepoch','localtime'),?,?,datetime(?,'unixepoch','localtime'))",
				(os.path.abspath(fileName), s.st_mtime, s.st_size, str(comment), time.time()))
			self.db.commit()
		except sqlite3.OperationalError:
			print("database is locked or unwriteable")
			#TODO: put up a message box for locked database


	@staticmethod
	def epochToDb(epoch):
		return time.strftime("%Y-%m-%d %H:%M:%S",time.localtime(epoch))
	@staticmethod
	def DbToEpoch(dbTime):
		return time.mktime(time.strptime(dbTime,"%Y-%m-%d %H:%M:%S"))
	@staticmethod
	def getShortDate(longDate):
		e = dnDataBase.DbToEpoch(longDate)
		sd = time.strptime(longDate,"%Y-%m-%d %H:%M:%S")
		# check for this year, or today
		ty = time.mktime((time.localtime()[0],1,1,0,0,0,0,1,-1))
		today = time.mktime((time.localtime()[0:3]+(0,0,0,0,1,-1)))
		if e < ty: 
			return time.strftime("%b %d %Y",sd)
		elif e < today:
			return time.strftime("%b %d",sd)
		else:
			return time.strftime("%X",sd)
	#~ test code for this routine
	#~ for i in range(int(time.time() - 370*24*3600),
		#~ int(time.time() + 48*3600),
		#~ 3599):
		#~ ds = dnDataBase.epochToDb(i)
		#~ print("%d\t%s\t%s" % (i,ds,dnDataBase.getShortDate(ds)))
				

		
def parse():
	parser = argparse.ArgumentParser(description='dirnotes application')
	parser.add_argument('dirname',metavar='dirname',type=str,
		help='directory [default=current dir]',default=".",nargs='?')
	parser.add_argument('dirname2',help='comparison directory, shows two-dirs side-by-side',nargs='?')
	parser.add_argument('-n','--nogui',action="store_const",const="1",
		help='use text base interface')
	parser.add_argument('-v','--version',action='version',version='%(prog)s '+VERSION)
	group = parser.add_mutually_exclusive_group()
	group.add_argument('-s','--sort-by-name',metavar='sort',action="store_const",const='n')
	group.add_argument('-m','--sort-by-date',metavar='sort',action='store_const',const='d')
	return parser.parse_args()


#~ class FileObj(QtCore.QObject):
class FileObj():
	FILE_IS_DIR = -1
	FILE_IS_LINK = -2
	def __init__(self, fileName):
		self.fileName = fileName
		s = os.lstat(fileName)
		self.date = dnDataBase.epochToDb(s.st_mtime)
		if stat.S_ISDIR(s.st_mode):
			self.size = FileObj.FILE_IS_DIR
		elif stat.S_ISLNK(s.st_mode):
			self.size = FileObj.FILE_IS_LINK
		else:
			self.size = s.st_size
		self.xattrComment = ''
		try:
			self.xattrComment = os.getxattr(fileName,COMMENT_KEY,follow_symlinks=False).decode()
		except Exception as e:
			#print("comment read on %s failed, execption %s" % (self.fileName,e)) 
			pass

	def getName(self):
		return self.fileName
	def getFileName(self):
		return os.path.split(self.fileName)[1]

	def getXattrComment(self):
		return self.xattrComment
	def setXattrComment(self,newComment):
		self.xattrComment = newComment
		try:
			os.setxattr(self.fileName,COMMENT_KEY,bytes(self.xattrComment,'utf8'),follow_symlinks=False)
			return True
		# we need to move these cases out to a handler 
		except Exception as e:
			print("problem setting the comment on file %s" % (self.fileName,))
			print("error "+repr(e))
			## todo: elif file.is_sym() the kernel won't allow comments on symlinks....stored in database
			if self.size == FileObj.FILE_IS_LINK:
				print("Linux does not allow comments on symlinks; comment is stored in database")
			elif os.access(self.fileName, os.W_OK)!=True:
				print("you don't appear to have write permissions on this file")
				# change the listbox background to yellow
				self.displayBox.notifyUnchanged()		
			elif "Errno 95" in str(e):
				print("is this a VFAT or EXFAT volume? these don't allow comments")
			return False

	def getDbComment(self,db):
		self.dbComment = ""
		row = db.getData(self.fileName)
		if row and len(row)>1:
			self.dbComment = row[3]
	def setDbComment(self,db,comment):
		db.setData(self.fileName,comment)

	def getDate(self):
		return self.date
	def getSize(self):
		return self.size

class HelpWidget(QDialog):
	def __init__(self, parent):
		super(QDialog, self).__init__(parent)
		self.layout = QVBoxLayout(self)
		self.tb = QLabel(self)
		self.tb.setWordWrap(True)
		self.tb.setText(helpMsg)
		self.tb.setFixedWidth(500)
		self.pb = QPushButton('OK',self)
		self.pb.setFixedWidth(200)
		self.layout.addWidget(self.tb)
		self.layout.addWidget(self.pb)
		self.pb.pressed.connect(self.close)
		self.show()

# sortable TableWidgetItem, based on idea by Aledsandar
# http://stackoverflow.com/questions/12673598/python-numerical-sorting-in-qtablewidget
# NOTE: the QTableWidgetItem has setData() and data() which may allow data bonding
# in Qt5, data() binding is more awkward, so do it here
class SortableTableWidgetItem(QTableWidgetItem):
	def __init__(self, text, sortValue, file_object):
		QTableWidgetItem.__init__(self, text, QTableWidgetItem.UserType)
		self.sortValue = sortValue
		self.file_object = file_object
	def __lt__(self, other):
		return self.sortValue < other.sortValue
		
		
class DirNotes(QMainWindow):
	''' the main window of the app
		has 3 list boxes: dir_left, dir_right (may be invisible) and files
		
		'''
	def __init__(self, argFilename, db, parent=None):
		super(DirNotes,self).__init__(parent)
		self.db = db
		self.refilling = False

		win = QWidget()
		self.setCentralWidget(win)

		lb = QTableWidget()
		self.lb = lb
		lb.setColumnCount(4)
		lb.horizontalHeader().setSectionResizeMode( 3, QHeaderView.Stretch );
		lb.verticalHeader().setDefaultSectionSize(20);	# thinner rows
		lb.verticalHeader().setVisible(False)
		
		# resize the comments column
		# and resize the parent window to match the directory size

		# allow multiple entries on the line at this point
		#d = os.listdir(p.filename[0])
		#d.sort()
		longPathName = os.path.abspath(argFilename)
		print("longpathname is {}".format(longPathName))
		if os.path.isdir(longPathName):
			self.curPath = longPathName
			filename = ''
		else:
			self.curPath, filename = os.path.split(longPathName)
		print("working on <"+self.curPath+"> and <"+filename+">")
		
		lb.setHorizontalHeaderItem(0,QTableWidgetItem("File"))
		lb.setHorizontalHeaderItem(1,QTableWidgetItem("Date/Time"))
		lb.setHorizontalHeaderItem(2,QTableWidgetItem("Size"))		
		lb.setHorizontalHeaderItem(3,QTableWidgetItem("Comment"))
		lb.setSortingEnabled(True)

		self.refill()
		lb.resizeColumnsToContents()
		
		if len(filename)>0:
			for i in range(lb.rowCount()):
				if filename == lb.item(i,0).data(32).toPyObject().getFileName():
					lb.setCurrentCell(i,3)
					break
		
		layout = QVBoxLayout()

		e = QLabel("View and edit file comments stored in extended attributes user.xdg.comment",win)
		layout.addWidget(e)

		bup = QPushButton("up",win)
		bup.setIcon(QIcon.fromTheme('go-up')) # or go-previous
		bup.setFixedWidth(100)
		b1 = QPushButton("restore from database",win)

		upperLayout = QHBoxLayout()
		upperLayout.addWidget(bup)
		upperLayout.addWidget(b1)
		layout.addLayout(upperLayout)
		layout.addWidget(lb)
		win.setLayout(layout)
		
		lb.itemChanged.connect(self.change)
		lb.cellDoubleClicked.connect(self.double)
		b1.pressed.connect(self.restore_from_database)
		bup.pressed.connect(self.upOneLevel)

		mb = self.menuBar()
		mf = mb.addMenu('&File')
		mf.addAction("Sort by name", self.sbn, "Ctrl+N")
		mf.addAction("Sort by date", self.sbd, "Ctrl+D")
		mf.addAction("Sort by size", self.sbs, "Ctrl+Z")
		mf.addAction("Sort by comment", self.sbc, "Ctrl+.")
		mf.addAction("Restore comment from database", self.restore_from_database, "Ctrl+R")
		mf.addSeparator()
		mf.addAction("Quit", self.close, "Ctrl+Q")
		mf.addAction("About", self.about, "Ctrl+H")

		#~ QShortcut(QKeySequence("Ctrl+Q"), self, self.close)	
		self.setWindowTitle("DirNotes   Alt-F for menu  Dir: "+self.curPath)
		self.setMinimumSize(600,700)
		lb.setFocus()
	def closeEvent(self,e):
		print("closing")
	def sbd(self):
		print("sort by date")
		self.lb.sortItems(1,Qt.DescendingOrder)
	def sbs(self):
		print("sort by size")
		self.lb.sortItems(2)
	def sbn(self):
		print("sort by name")
		self.lb.sortItems(0)
	def about(self):
		HelpWidget(self)
	def sbc(self):
		print("sort by comment")
		self.lb.sortItems(3,Qt.DescendingOrder)
	def newDir(self):
		print("change dir to "+self.dirLeft.currentPath())
	def double(self,row,col):
		print("double click {} {}".format(row, col))
		fo = self.lb.item(row,0).file_object
		if col==0 and fo.getSize() == FileObj.FILE_IS_DIR:
			print("double click on {}".format(fo.getName()))
			self.curPath = fo.getName()
			self.refill()
	def upOneLevel(self):
		self.curPath = os.path.abspath(self.curPath + "/..")
		self.refill()
			
	def refill(self):
		self.refilling = True
		self.lb.sortingEnabled = False
		self.lb.clearContents()
		small_font = QFont("",8)
		dirIcon = QIcon.fromTheme('folder')
		fileIcon = QIcon.fromTheme('text-x-generic')
		linkIcon = QIcon.fromTheme('emblem-symbolic-link')
		current, dirs, files = next(os.walk(self.curPath,followlinks=True))
		dirs = list(map(lambda x:x+'/', dirs))
		dirs.sort()
		files.sort()
		
		d = dirs + files
		self.lb.setRowCount(len(d))

		#~ self.files = {}
		self.files = []
		# this is a list of all the file
		#~ print("insert {} items into cleared table {}".format(len(d),current))
		for i in range(len(d)):
			this_file = FileObj(current+'/'+d[i])
			this_file.getDbComment(self.db)
			print("FileObj created as {} and the db-comment is <{}>".format(this_file.fileName, this_file.dbComment))
			#~ print("insert order check: {} {} {} {}".format(d[i],i,this_file.getName(),this_file.getDate()))
			#~ self.files.update({this_file.getName(),this_file})
			self.files = self.files + [this_file]
			if this_file.getSize() == FileObj.FILE_IS_DIR:
				item = SortableTableWidgetItem(d[i],' '+d[i], this_file)	# directories sort first
			else:
				item = SortableTableWidgetItem(d[i],d[i], this_file)
			item.setFlags(Qt.ItemIsEnabled)
			#item.setData(32,this_file)	# keep a hidden copy of the file object
			item.setToolTip(this_file.getName())
			self.lb.setItem(i,0,item)
			#lb.itemAt(i,0).setFlags(Qt.ItemIsEnabled) #NoItemFlags) 

			# get the comment from database & xattrs, either can fail
			comment = "" 
			row = self.db.getData(this_file.getName())
			if row and len(row)>0:
				comment = row[3]
			xattrComment = this_file.getXattrComment()
			self.lb.setItem(i,3,QTableWidgetItem(comment))
			if xattrComment != comment:
				self.lb.item(i,3).setBackground(QBrush(Qt.yellow))
				print("got two comments <{}> and <{}>".format(comment, xattrComment))

			dt = this_file.getDate()
			da = SortableTableWidgetItem(dnDataBase.getShortDate(dt),dt,this_file)
			#da.setFont(small_font)
			da.setToolTip(dt)
			da.setFlags(Qt.ItemIsEnabled)
			self.lb.setItem(i,1,da)
			si = this_file.getSize()
			if si==FileObj.FILE_IS_DIR:
				sa = SortableTableWidgetItem('',0,this_file)
				item.setIcon(dirIcon)
			elif si==FileObj.FILE_IS_LINK:
				sa = SortableTableWidgetItem('symlink',-1,this_file)
				item.setIcon(linkIcon)
				dst = os.path.realpath(this_file.getName())
				sa.setToolTip("symlink: " + dst)
			else:
				sa = SortableTableWidgetItem(str(si),si,this_file)
				item.setIcon(fileIcon)
			sa.setTextAlignment(Qt.AlignRight)
			sa.setFlags(Qt.ItemIsEnabled)
			self.lb.setItem(i,2,sa)
		self.refilling = False
		self.lb.sortingEnabled = True
		self.lb.resizeColumnToContents(1)
			
	def change(self,x):
		if self.refilling:
			return
		print("debugging " + x.text() + " r:" + str(x.row()) + " c:" + str(x.column()))
		print("      selected file: "+self.lb.item(x.row(),0).file_object.getName())
		the_file = self.lb.item(x.row(),0).file_object
		print("      and the row file is "+the_file.getName())
		r = the_file.setXattrComment(str(x.text())) # TODO: change priority
		if r:
			self.db.setData(the_file.getName(),x.text())

	def restore_from_database(self):
		print("restore from database")
		# retrieve the full path name
		fileName = str(self.lb.item(self.lb.currentRow(),0).file_object.getName())
		print("using filename: "+fileName)
		existing_comment = str(self.lb.item(self.lb.currentRow(),3).text())
		print("restore....existing="+existing_comment+"=")
		if len(existing_comment) > 0:
			m = QMessageBox() 
			m.setText("This file already has a comment. Overwrite?")
			m.setStandardButtons(QMessageBox.Ok | QMessageBox.Cancel);
			if m.exec_() != QMessageBox.Ok:
				return
		fo_row = self.db.getData(fileName)
		if fo_row and len(fo_row)>1:
			comment = fo_row[3]
			print(fileName,fo_row[0],comment) 
			the_file = dn.files[self.lb.currentRow()]
			the_file.setComment(comment)
			self.lb.setItem(self.lb.currentRow(),3,QTableWidgetItem(comment))
		
if __name__=="__main__":
	p = parse()
	if len(p.dirname)>1 and p.dirname[-1]=='/':
		p.dirname = p.dirname[:-1]
	if os.path.isdir(p.dirname):
		p.dirname = p.dirname + '/'
	print(p.dirname)
	
	db = dnDataBase()

	# TODO: if there's a file specified, jump to it
	a = QApplication([])
	dn = DirNotes(p.dirname,db)
	dn.show()
	
	a.exec_()
	
	#xattr.setxattr(filename,COMMENT_KEY,commentText)

''' files from directories
use os.isfile()
os.isdir()
current, dirs, files = os.walk("path").next()
possible set folllowLinks=True'''

''' notes from the wdrm project
table showed 
filename, size, date size, date, desc

at start, fills the list of all the files
skip the . entry
'''

''' should we also do user.xdg.tags="TagA,TagB" ?
user.charset
user.creator=application_name or user.xdg.creator
user.xdg.origin.url
user.xdg.language=[RFC3066/ISO639]
user.xdg.publisher
'''

''' TODO: add cut-copy-paste for comments '''

''' TODO: also need a way to display-&-restore comments from the database '''

''' TODO: implement startup -s and -m for size and date '''

''' TODO: add an icon for the app '''

''' TODO: create 'show comment history' popup '''

''' TODO: add dual-pane for file-move, file-copy '''
	
''' commandline xattr
getfattr -h (don't follow symlink) -d (dump all properties)
'''
''' if the args line contains a file, jump to it '''