def initDb(self, name, encoding):
"""Initializing SQLite database"""
self.dbName = name
self.db = SQLite(self.dbName, encoding)
self.priority = Priority(self.db)
self.task = Task(self.db)
# for testing purposes
if self.task.count() == 0:
self.db.execSql(
"insert into {} (name, priority, deadline) values(?, ?, ?)".
format(self.task._tableName),
("Low Test", 1, date.today() + timedelta(2)))
self.db.commit()
def get_users_by_phone_number(phones_number) -> list:
query = f"SELECT user_id FROM {SQLITE_DB_TABLE} WHERE "
for i in range(len(phones_number)):
query += f"phone = '{phones_number[i]}' OR "
if i == len(phones_number) - 1:
query += f"phone = '{phones_number[i]}'"
logging.info(f"Func /db/queries/select.get_users_by_phone_number with {query}")
response = SQLite(SQLITE_DB_NAME).select_query(query)
return response
def setUp(self):
with threading.Lock():
self.dbName = "test.sqlite3"
self.db = SQLite(self.dbName)
self.priority = Priority(self.db)
self.task = Task(self.db)
self.task.exec( "insert into {} (name, priority, deadline) values(?, ?, ?)".format(self.task._tableName),
("Low Test", 1, date.today() + timedelta(2)) )
self.task.exec( "insert into {} (name, priority, deadline) values(?, ?, ?)".format(self.task._tableName),
("Medium Test", 2, date.today() + timedelta(3)) )
self.task.exec( "insert into {} (name, priority, deadline) values(?, ?, ?)".format(self.task._tableName),
("High Test", 3, date.today() + timedelta(4)) )
def initDb(self, name, encoding):
"""Initializing SQLite database"""
self.dbName = name
self.db = SQLite(self.dbName, encoding)
self.priority = Priority(self.db)
self.task = Task(self.db)
# for testing purposes
if self.task.count() == 0:
self.db.execSql(
"insert into {} (name, priority, deadline) values(?, ?, ?)".format(self.task._tableName),
("Low Test", 1, date.today() + timedelta(2)),
)
self.db.commit()
def test_sqlite():
with SQLite(DB_PATH) as my_conn:
assert os.path.isfile(DB_PATH)
my_conn.execute("SELECT 1")
res = list(my_conn.fetchall()[0])
assert res == [1]
def insert_new_user(user_id, first_name, last_name, phone):
query = f"""INSERT OR IGNORE INTO {SQLITE_DB_TABLE}
(user_id, first_name, second_name, phone)
VALUES ({user_id}, '{first_name}', '{last_name}', '{phone}');"""
logging.info(f"Func /db/queries/insert.insert_new_user with {query}")
SQLite(SQLITE_DB_NAME).insert_query(query)
def setUp(self):
with threading.Lock():
self.dbName = "test.sqlite3"
self.db = SQLite(self.dbName)
self.createTable()
class SQLiteDatabase(unittest.TestCase):
def setUp(self):
with threading.Lock():
self.dbName = "test.sqlite3"
self.db = SQLite(self.dbName)
self.createTable()
def tearDown(self):
with threading.Lock():
if os.path.exists(self.dbName):
self.db.__del__()
os.unlink(self.dbName)
def createTable(self):
self.db.execSql("""create table if not exists test (
id integer primary key autoincrement not null,
user_id text unique not null);""")
def dropTable(self):
self.db.execSql("drop table if exists test;")
def test_tables(self):
self.assertIn("test", self.db.getTables())
def test_exec_sql(self):
self.dropTable()
self.assertNotIn("test", self.db.getTables())
self.createTable()
self.assertIn("test", self.db.getTables())
self.dropTable()
self.assertNotIn("test", self.db.getTables())
def test_table_exists(self):
self.assertTrue(self.db.tableExists("test"))
self.assertFalse(self.db.tableExists("try_test"))
def test_columns(self):
cols = self.db.getFields("test")
self.assertEqual(["id", "user_id"], cols)
def test_indices(self):
indices = self.db.getIndices("test")
self.assertEqual([('sqlite_autoindex_test_1', True)], indices)
self.db.execSql("create index test_user_id on test(user_id);")
indices = self.db.getIndices("test")
self.assertEqual([('sqlite_autoindex_test_1', True),
('test_user_id', False)], indices)
def test_sequences(self):
result = self.db.execSql(
"insert into test (user_id) values ('orlmon');")
self.assertIn(("test", 1), self.db.getSequences())
def test_insert(self):
result = self.db.execSql(
"insert into test (user_id) values ('orlmon');")
self.assertEqual([], result)
self.db.execSql("insert into test (user_id) values ('orlpuv');")
self.db.execSql("insert into test (user_id) values ('orljuk');")
rows = self.db.execSql("select count(*) from test;")
# rows = [(3,)]
self.assertEqual(3, rows[0][0])
def test_executemany(self):
self.db.conn.executemany("insert into test (user_id) values (?)",
[('orlmon', ), ('orlpuv', ), ('orljuk', ),
('orltest', )])
row = self.db.execSql("select count(*) as cnt from test;")[0]
self.assertEqual(4, row['cnt'])
def test_select(self):
self.db.conn.executemany("insert into test (user_id) values (?)",
[('orlmon', ), ('orlpuv', ), ('orljuk', )])
row = self.db.execSql("select user_id from test where id=?;", (2, ))[0]
# row = ('orlpuv',)
self.assertEqual("orlpuv", row['user_id'])
def test_update(self):
self.db.conn.executemany("insert into test (user_id) values (?)",
[('orlmon', ), ('orlpuv', ), ('orljuk', )])
row = self.db.execSql("select user_id from test where id=?;", (2, ))[0]
self.assertEqual("orlpuv", row["user_id"])
result = self.db.execSql(
"update test set user_id='orldrz' where id=?;", (2, ))
self.assertEqual([], result)
row = self.db.execSql("select user_id from test where id=?;", (2, ))[0]
self.assertEqual("orldrz", row["user_id"])
def test_rollback(self):
self.db.execSql("insert into test (user_id) values ('orlmon');")
self.db.execSql("insert into test (user_id) values ('orlpuv');")
self.db.execSql("insert into test (user_id) values ('orljuk');")
self.db.rollback()
row = self.db.execSql("select count(*) from test;")[0]
self.assertEqual(0, row[0])
self.db.conn.executemany("insert into test (user_id) values (?)",
[('orlmon', ), ('orlpuv', ), ('orljuk', )])
row = self.db.execSql("select id from test where user_id=?;",
('orljuk', ))[0]
self.assertEqual(3, row[0])
self.db.rollback()
row = self.db.execSql("select count(*) from test;")[0]
self.assertEqual(0, row[0])
self.db.conn.executemany("insert into test (user_id) values (?)",
[('orlmon', ), ('orlpuv', ), ('orljuk', )])
self.db.commit()
self.db.rollback()
row = self.db.execSql("select id from test where user_id=?;",
('orlpuv', ))[0]
self.assertEqual(2, row[0])
def test_shrink(self):
self.db.conn.executemany("insert into test (user_id) values (?)",
[('orlmon', ), ('orlpuv', ), ('orljuk', )])
for i in range(1000):
self.db.execSql(
"insert into test (user_id) values ('orltst_%d');" % i)
self.db.commit()
self.db.execSql("delete from test where user_id like ('orltst%');")
self.db.commit()
self.assertEqual((41984, 4096), self.db.shrink())
def test_dump(self):
dump = """BEGIN TRANSACTION;
DELETE FROM "sqlite_sequence";
INSERT INTO "sqlite_sequence" VALUES('test',3);
CREATE TABLE test (
id integer primary key autoincrement not null,
user_id text unique not null);
INSERT INTO "test" VALUES(1,'orlmon');
INSERT INTO "test" VALUES(2,'orlpuv');
INSERT INTO "test" VALUES(3,'orljuk');
COMMIT;
"""
self.db.conn.executemany("insert into test (user_id) values (?)",
[('orlmon', ), ('orlpuv', ), ('orljuk', )])
dumpName = "{}.dump".format(self.db.name.split(".")[0])
self.db.dump(dumpName)
try:
with open(dumpName, "r") as fh:
self.assertEqual(dump, fh.read())
finally:
if os.path.exists(dumpName):
os.unlink(dumpName)
def test_md5(self):
value = self.db.conn.execute("select md5(?)",
(b"testing", )).fetchone()[0]
self.assertEqual(value, 'ae2b1fca515949e5d54fb22b8ed95575')
class MainWindow(QtGui.QMainWindow):
"""The main window of the TODO application"""
def __init__(self):
QtGui.QMainWindow.__init__(self)
self.setWindowTitle(self.tr("Todo"))
self.createActions()
self.createMenus()
self.createToolBars()
self.createStatusBar()
# read settings
settings = QtCore.QSettings("todo.conf", QtCore.QSettings.IniFormat)
size = settings.value("MainWindow/Size", QtCore.QSize(800, 600))
self.resize(size)
position = settings.value("MainWindow/Position", QtCore.QPoint(10, 10))
self.move(position)
# initializing database
dbName = settings.value("Default/DB_NAME")
if not dbName:
dbName = "db/todo.sqlite3"
settings.setValue("Default/DB_NAME", dbName)
dbName = os.path.normpath(os.path.join(APP_DIR, dbName))
dbEncoding = settings.value("Default/DB_ENCODING")
if not dbEncoding:
dbEncoding = "utf8"
settings.setValue("Default/DB_ENCODING", dbEncoding)
debug = settings.value("Default/DEBUG", "False")
self.debug = False if debug in ("0", "False") else True
self.createDockWindows()
self.logger = QCCLog(self.logWidget)
# sys.stdout = sys.stderr = self.logger
self.initDb(dbName, dbEncoding)
if self.debug:
self.logger.write("{} database initialized".format(now()))
self.createTableWidget()
self.setCentralWidget(self.tableWidget)
def closeEvent(self, event):
settings = QtCore.QSettings("todo.conf", QtCore.QSettings.IniFormat)
settings.setValue("MainWindow/Size", self.size())
settings.setValue("MainWindow/Position", self.pos())
def initDb(self, name, encoding):
"""Initializing SQLite database"""
self.dbName = name
self.db = SQLite(self.dbName, encoding)
self.priority = Priority(self.db)
self.task = Task(self.db)
# for testing purposes
if self.task.count() == 0:
self.db.execSql(
"insert into {} (name, priority, deadline) values(?, ?, ?)".format(self.task._tableName),
("Low Test", 1, date.today() + timedelta(2)),
)
self.db.commit()
def createActions(self):
"""Create actions (for menu etc)."""
self.newTaskAction = QtGui.QAction(
QtGui.QIcon("images/editadd.png"),
self.tr("Add TODO item"),
self,
shortcut=QtGui.QKeySequence.New,
statusTip=self.tr("Adding new TODO item"),
triggered=self.newTask,
)
self.completeAction = QtGui.QAction(
QtGui.QIcon("images/editedit.png"),
self.tr("Complete TODO item"),
self,
statusTip=self.tr("Completing TODO item"),
triggered=self.completeTask,
)
self.deleteAction = QtGui.QAction(
QtGui.QIcon("images/editdelete.png"),
self.tr("Delete TODO item"),
self,
statusTip=self.tr("Deleting TODO item"),
triggered=self.deleteTask,
)
self.editAction = QtGui.QAction(
QtGui.QIcon("images/filenew.png"),
self.tr("Edit TODO item"),
self,
shortcut=QtCore.Qt.CTRL | QtCore.Qt.Key_E,
statusTip=self.tr("Editing TODO item"),
triggered=self.editTask,
)
self.exitAction = QtGui.QAction(QtGui.QIcon("images/exit.png"), self.tr("Exit"), self)
self.exitAction.setShortcut("Ctrl+Q")
self.exitAction.setStatusTip(self.tr("Exit application"))
self.aboutAction = QtGui.QAction(
QtGui.QIcon("images/about.png"),
self.tr("&About"),
self,
statusTip=self.tr("Show the application's About box"),
triggered=self.about,
)
self.aboutQtAction = QtGui.QAction(
QtGui.QIcon("images/qt-logo.png"),
self.tr("About &Qt"),
self,
statusTip=self.tr("Show the Qt library's About box"),
triggered=QtGui.qApp.aboutQt,
)
self.connect(self.exitAction, QtCore.SIGNAL("triggered()"), QtCore.SLOT("close()"))
def createMenus(self):
menubar = self.menuBar()
fileMenu = menubar.addMenu(self.tr("&File"))
fileMenu.addAction(self.newTaskAction)
fileMenu.addAction(self.editAction)
fileMenu.addAction(self.completeAction)
fileMenu.addSeparator()
fileMenu.addAction(self.exitAction)
self.viewMenu = menubar.addMenu(self.tr("&View"))
menubar.addSeparator()
helpMenu = menubar.addMenu(self.tr("&Help"))
helpMenu.addAction(self.aboutAction)
helpMenu.addAction(self.aboutQtAction)
def createToolBars(self):
taskToolBar = self.addToolBar("Task")
taskToolBar.setObjectName("TaskToolbar")
taskToolBar.addAction(self.newTaskAction)
taskToolBar.addAction(self.editAction)
taskToolBar.addAction(self.completeAction)
exitToolBar = self.addToolBar("Exit")
exitToolBar.setObjectName("ExitToolbar")
exitToolBar.addAction(self.exitAction)
def createStatusBar(self):
self.sizeLabel = QtGui.QLabel(" ")
self.sizeLabel.setFrameStyle(QtGui.QFrame.StyledPanel | QtGui.QFrame.Sunken)
self.sizeLabel.setFrameStyle(QtGui.QFrame.Sunken)
status = self.statusBar()
status.setSizeGripEnabled(True)
status.addPermanentWidget(self.sizeLabel)
status.showMessage(self.tr("Ready"), 5000)
def openContextMenu(self, position):
menu = QtGui.QMenu(self)
menu.addAction(self.newTaskAction)
menu.addAction(self.editAction)
menu.addAction(self.completeAction)
menu.addSeparator()
menu.addAction(self.deleteAction)
# menu.exec_(self.tableWidget.mapToGlobal(position))
menu.popup(QtGui.QCursor.pos())
def createTableWidget(self):
"""Table widget creation to display TODO lists"""
self.tableWidget = QtGui.QTableWidget(0, 6)
self.tableWidget.setSelectionBehavior(QtGui.QAbstractItemView.SelectRows)
# select one row at a time
self.tableWidget.setSelectionMode(QtGui.QAbstractItemView.SingleSelection)
# no editing values
self.tableWidget.setEditTriggers(QtGui.QAbstractItemView.NoEditTriggers)
self.tableWidget.setHorizontalHeaderLabels(
(
self.tr("ID"),
self.tr("Name"),
self.tr("Priority"),
self.tr("Deadline"),
self.tr("Completed"),
self.tr("Created"),
)
)
# self.tableWidget.horizontalHeader().setResizeMode(0, QHeaderView.Stretch)
self.tableWidget.verticalHeader().hide()
self.tableWidget.setShowGrid(True)
# self.tableWidget.setContextMenuPolicy(QtCore.Qt.ActionsContextMenu)
# self.addActions(self.tableWidget, (self.newAction, self.aboutAction))
self.tableWidget.setContextMenuPolicy(QtCore.Qt.CustomContextMenu)
self.tableWidget.customContextMenuRequested.connect(self.openContextMenu)
self.tableWidget.cellDoubleClicked.connect(self.rowDblClick)
self.refreshTable()
def refreshTable(self):
self.tableWidget.setRowCount(0)
rows = self.db.execSql("select * from TodoTask where status=0")
for row in rows:
cnt = self.tableWidget.rowCount()
self.tableWidget.insertRow(cnt)
self.tableWidget.setItem(cnt, 0, QtGui.QTableWidgetItem(str(row["id"])))
self.tableWidget.setItem(cnt, 1, QtGui.QTableWidgetItem(row["name"]))
self.tableWidget.setItem(cnt, 2, QtGui.QTableWidgetItem(self.priority.getName(row["priority"])))
self.tableWidget.setItem(cnt, 3, QtGui.QTableWidgetItem(str(row["deadline"])))
self.tableWidget.setItem(cnt, 4, QtGui.QTableWidgetItem(str(row["completed"]) if row["completed"] else ""))
self.tableWidget.setItem(cnt, 5, QtGui.QTableWidgetItem(str(row["created"])))
QtGui.qApp.processEvents()
self.tableWidget.resizeColumnsToContents()
def createDockWindows(self):
dockWidget = QtGui.QDockWidget(self.tr("Log"), self)
dockWidget.setObjectName("LogDockWidget")
dockWidget.setAllowedAreas(QtCore.Qt.LeftDockWidgetArea | QtCore.Qt.RightDockWidgetArea)
self.logWidget = QtGui.QListWidget()
dockWidget.setWidget(self.logWidget)
self.addDockWidget(QtCore.Qt.LeftDockWidgetArea, dockWidget)
self.viewMenu.addAction(dockWidget.toggleViewAction())
if not self.debug:
dockWidget.close()
def newTask(self):
dialog = NewTaskDialog(self)
dialog.priority.addItems(self.priority.listNames())
if dialog.exec_():
args = {}
args["name"] = dialog.name.text()
args["priority"] = self.priority.getCode(dialog.priority.currentText())
args["deadline"] = dialog.deadline.date().toPyDate()
args = self.task.save("", **args)
self.refreshTable()
def rowDblClick(self, row, col):
self._updateTask(row)
def editTask(self):
# index of currently selected row
row = self.tableWidget.currentRow()
if row == -1:
msgBox = QtGui.QMessageBox()
msgBox.setWindowTitle(self.tr("Edit"))
msgBox.setText(self.tr("Select a task to edit!"))
msgBox.exec_()
else:
self._updateTask(row)
def _updateTask(self, row):
# id of currently selected row
_id = int(self.tableWidget.item(row, 0).text())
row = self.task.read(_id)
# open dialog for editing record
dialog = NewTaskDialog(self)
dialog.name.setText(row["name"])
dialog.priority.addItems(self.priority.listNames())
dialog.priority.setCurrentIndex(row["priority"] - 1)
dialog.deadline.setDate(row["deadline"])
if dialog.exec_():
row["name"] = dialog.name.text()
row["priority"] = self.priority.getCode(dialog.priority.currentText())
row["deadline"] = dialog.deadline.date().toPyDate()
args = self.task.save(_id, **row)
self.refreshTable()
def completeTask(self):
# index of currently selected row
row = self.tableWidget.currentRow()
if row == -1:
msgBox = QtGui.QMessageBox()
msgBox.setWindowTitle(self.tr("Complete"))
msgBox.setText(self.tr("Select a task to complete!"))
msgBox.exec_()
return
# id of currently selected row
_id = self.tableWidget.item(row, 0).text()
if (
QtGui.QMessageBox.question(
self,
self.tr("Complete"),
self.tr("Are you sure to complete this task (id={})?").format(_id),
QtGui.QMessageBox.Yes,
QtGui.QMessageBox.No,
)
== QtGui.QMessageBox.Yes
):
self.db.execSql("update TodoTask set status=1, completed=? where id=?", (datetime.now(), int(_id)))
self.db.commit()
self.refreshTable()
def deleteTask(self):
# index and id of currently selected row
row = self.tableWidget.currentRow()
if row == -1:
msgBox = QtGui.QMessageBox()
msgBox.setWindowTitle(self.tr("Delete"))
msgBox.setText(self.tr("Select a task to delete!"))
msgBox.exec_()
return
_id = self.tableWidget.item(row, 0).text()
if (
QtGui.QMessageBox.question(
self,
self.tr("Delete"),
self.tr("Are you sure to delete this record (id={})?").format(_id),
QtGui.QMessageBox.Yes,
QtGui.QMessageBox.No,
)
== QtGui.QMessageBox.Yes
):
# remove row from table widget
self.tableWidget.removeRow(row)
# remove record from database
self.logger.write("{} delete from TodoTask where id={}".format(now(), _id))
self.task.deleteId(_id)
self.statusBar().showMessage("Deleted record id=" + _id, 5000)
def about(self):
QtGui.QMessageBox.about(
self,
self.tr("About Application"),
self.tr(
"The purpose of the <b>Application</b> is:<br>"
" - the management of TODO lists..."
"<br><br>author: <a href='mailto:[email protected]'>Igor A.Vetrov</a> © 2012"
"<hr>Version: {}"
).format(getVersion()),
)
def get_user_by_id(user_id) -> int:
query = f"SELECT user_id FROM {SQLITE_DB_TABLE} WHERE user_id = {user_id};"
logging.info(f"Func /db/queries/select.get_user_by_id with {query}")
response = SQLite(SQLITE_DB_NAME).select_query(query)
if len(response) > 0:
return response[0][0]
def setUp(self):
with threading.Lock():
self.dbName = "test.sqlite3"
self.db = SQLite(self.dbName)
self.createTable()
class SQLiteDatabase(unittest.TestCase):
def setUp(self):
with threading.Lock():
self.dbName = "test.sqlite3"
self.db = SQLite(self.dbName)
self.createTable()
def tearDown(self):
with threading.Lock():
if os.path.exists(self.dbName):
self.db.__del__()
os.unlink(self.dbName)
def createTable(self):
self.db.execSql( """create table if not exists test (
id integer primary key autoincrement not null,
user_id text unique not null);"""
)
def dropTable(self):
self.db.execSql("drop table if exists test;")
def test_tables(self):
self.assertIn( "test", self.db.getTables() )
def test_exec_sql(self):
self.dropTable()
self.assertNotIn( "test", self.db.getTables() )
self.createTable()
self.assertIn( "test", self.db.getTables() )
self.dropTable()
self.assertNotIn( "test", self.db.getTables() )
def test_table_exists(self):
self.assertTrue( self.db.tableExists("test") )
self.assertFalse( self.db.tableExists("try_test") )
def test_columns(self):
cols = self.db.getFields("test")
self.assertEqual( ["id", "user_id"], cols )
def test_indices(self):
indices = self.db.getIndices("test")
self.assertEqual( [('sqlite_autoindex_test_1', True)], indices )
self.db.execSql("create index test_user_id on test(user_id);")
indices = self.db.getIndices("test")
self.assertEqual( [('sqlite_autoindex_test_1', True), ('test_user_id', False)], indices )
def test_sequences(self):
result = self.db.execSql( "insert into test (user_id) values ('orlmon');" )
self.assertIn( ("test", 1), self.db.getSequences() )
def test_insert(self):
result = self.db.execSql( "insert into test (user_id) values ('orlmon');" )
self.assertEqual( [], result )
self.db.execSql( "insert into test (user_id) values ('orlpuv');" )
self.db.execSql( "insert into test (user_id) values ('orljuk');" )
rows = self.db.execSql("select count(*) from test;")
# rows = [(3,)]
self.assertEqual( 3, rows[0][0] )
def test_executemany(self):
self.db.conn.executemany( "insert into test (user_id) values (?)", [('orlmon',), ('orlpuv',), ('orljuk',), ('orltest',)] )
row = self.db.execSql("select count(*) as cnt from test;")[0]
self.assertEqual( 4, row['cnt'] )
def test_select(self):
self.db.conn.executemany( "insert into test (user_id) values (?)", [('orlmon',), ('orlpuv',), ('orljuk',)] )
row = self.db.execSql( "select user_id from test where id=?;", (2,) )[0]
# row = ('orlpuv',)
self.assertEqual( "orlpuv", row['user_id'] )
def test_update(self):
self.db.conn.executemany( "insert into test (user_id) values (?)", [('orlmon',), ('orlpuv',), ('orljuk',)] )
row = self.db.execSql( "select user_id from test where id=?;", (2,) )[0]
self.assertEqual( "orlpuv", row["user_id"] )
result = self.db.execSql( "update test set user_id='orldrz' where id=?;", (2,) )
self.assertEqual( [], result )
row = self.db.execSql( "select user_id from test where id=?;", (2,) )[0]
self.assertEqual( "orldrz", row["user_id"] )
def test_rollback(self):
self.db.execSql( "insert into test (user_id) values ('orlmon');" )
self.db.execSql( "insert into test (user_id) values ('orlpuv');" )
self.db.execSql( "insert into test (user_id) values ('orljuk');" )
self.db.rollback()
row = self.db.execSql( "select count(*) from test;" )[0]
self.assertEqual( 0, row[0] )
self.db.conn.executemany( "insert into test (user_id) values (?)", [('orlmon',), ('orlpuv',), ('orljuk',)] )
row = self.db.execSql( "select id from test where user_id=?;", ('orljuk',) )[0]
self.assertEqual( 3, row[0] )
self.db.rollback()
row = self.db.execSql( "select count(*) from test;" )[0]
self.assertEqual( 0, row[0] )
self.db.conn.executemany( "insert into test (user_id) values (?)", [('orlmon',), ('orlpuv',), ('orljuk',)] )
self.db.commit()
self.db.rollback()
row = self.db.execSql( "select id from test where user_id=?;", ('orlpuv',) )[0]
self.assertEqual( 2, row[0] )
def test_shrink(self):
self.db.conn.executemany( "insert into test (user_id) values (?)", [('orlmon',), ('orlpuv',), ('orljuk',)] )
for i in range(1000):
self.db.execSql( "insert into test (user_id) values ('orltst_%d');" % i )
self.db.commit()
self.db.execSql( "delete from test where user_id like ('orltst%');" )
self.db.commit()
self.assertEqual( (41984, 4096), self.db.shrink() )
def test_dump(self):
dump = """BEGIN TRANSACTION;
DELETE FROM "sqlite_sequence";
INSERT INTO "sqlite_sequence" VALUES('test',3);
CREATE TABLE test (
id integer primary key autoincrement not null,
user_id text unique not null);
INSERT INTO "test" VALUES(1,'orlmon');
INSERT INTO "test" VALUES(2,'orlpuv');
INSERT INTO "test" VALUES(3,'orljuk');
COMMIT;
"""
self.db.conn.executemany( "insert into test (user_id) values (?)", [('orlmon',), ('orlpuv',), ('orljuk',)] )
dumpName = "{}.dump".format(self.db.name.split(".")[0])
self.db.dump( dumpName )
try:
with open(dumpName, "r") as fh:
self.assertEqual( dump, fh.read() )
finally:
if os.path.exists(dumpName):
os.unlink(dumpName)
def test_md5(self):
value = self.db.conn.execute("select md5(?)", (b"testing",)).fetchone()[0]
self.assertEqual( value, 'ae2b1fca515949e5d54fb22b8ed95575' )
class MainWindow(QtGui.QMainWindow):
"""The main window of the TODO application"""
def __init__(self):
QtGui.QMainWindow.__init__(self)
self.setWindowTitle(self.tr('Todo'))
self.createActions()
self.createMenus()
self.createToolBars()
self.createStatusBar()
# read settings
settings = QtCore.QSettings("todo.conf", QtCore.QSettings.IniFormat)
size = settings.value("MainWindow/Size", QtCore.QSize(800, 600))
self.resize(size)
position = settings.value("MainWindow/Position", QtCore.QPoint(10, 10))
self.move(position)
# initializing database
dbName = settings.value("Default/DB_NAME")
if not dbName:
dbName = "db/todo.sqlite3"
settings.setValue("Default/DB_NAME", dbName)
dbName = os.path.normpath(os.path.join(APP_DIR, dbName))
dbEncoding = settings.value("Default/DB_ENCODING")
if not dbEncoding:
dbEncoding = "utf8"
settings.setValue("Default/DB_ENCODING", dbEncoding)
debug = settings.value("Default/DEBUG", "False")
self.debug = False if debug in ("0", "False") else True
self.createDockWindows()
self.logger = QCCLog(self.logWidget)
#sys.stdout = sys.stderr = self.logger
self.initDb(dbName, dbEncoding)
if self.debug:
self.logger.write("{} database initialized".format(now()))
self.createTableWidget()
self.setCentralWidget(self.tableWidget)
def closeEvent(self, event):
settings = QtCore.QSettings("todo.conf", QtCore.QSettings.IniFormat)
settings.setValue("MainWindow/Size", self.size())
settings.setValue("MainWindow/Position", self.pos())
def initDb(self, name, encoding):
"""Initializing SQLite database"""
self.dbName = name
self.db = SQLite(self.dbName, encoding)
self.priority = Priority(self.db)
self.task = Task(self.db)
# for testing purposes
if self.task.count() == 0:
self.db.execSql(
"insert into {} (name, priority, deadline) values(?, ?, ?)".
format(self.task._tableName),
("Low Test", 1, date.today() + timedelta(2)))
self.db.commit()
def createActions(self):
"""Create actions (for menu etc)."""
self.newTaskAction = QtGui.QAction(
QtGui.QIcon('images/editadd.png'),
self.tr("Add TODO item"),
self,
shortcut=QtGui.QKeySequence.New,
statusTip=self.tr("Adding new TODO item"),
triggered=self.newTask)
self.completeAction = QtGui.QAction(
QtGui.QIcon('images/editedit.png'),
self.tr("Complete TODO item"),
self,
statusTip=self.tr("Completing TODO item"),
triggered=self.completeTask)
self.deleteAction = QtGui.QAction(
QtGui.QIcon('images/editdelete.png'),
self.tr("Delete TODO item"),
self,
statusTip=self.tr("Deleting TODO item"),
triggered=self.deleteTask)
self.editAction = QtGui.QAction(QtGui.QIcon('images/filenew.png'),
self.tr("Edit TODO item"),
self,
shortcut=QtCore.Qt.CTRL
| QtCore.Qt.Key_E,
statusTip=self.tr("Editing TODO item"),
triggered=self.editTask)
self.exitAction = QtGui.QAction(QtGui.QIcon('images/exit.png'),
self.tr('Exit'), self)
self.exitAction.setShortcut('Ctrl+Q')
self.exitAction.setStatusTip(self.tr('Exit application'))
self.aboutAction = QtGui.QAction(
QtGui.QIcon('images/about.png'),
self.tr("&About"),
self,
statusTip=self.tr("Show the application's About box"),
triggered=self.about)
self.aboutQtAction = QtGui.QAction(
QtGui.QIcon('images/qt-logo.png'),
self.tr("About &Qt"),
self,
statusTip=self.tr("Show the Qt library's About box"),
triggered=QtGui.qApp.aboutQt)
self.connect(self.exitAction, QtCore.SIGNAL('triggered()'),
QtCore.SLOT('close()'))
def createMenus(self):
menubar = self.menuBar()
fileMenu = menubar.addMenu(self.tr('&File'))
fileMenu.addAction(self.newTaskAction)
fileMenu.addAction(self.editAction)
fileMenu.addAction(self.completeAction)
fileMenu.addSeparator()
fileMenu.addAction(self.exitAction)
self.viewMenu = menubar.addMenu(self.tr("&View"))
menubar.addSeparator()
helpMenu = menubar.addMenu(self.tr("&Help"))
helpMenu.addAction(self.aboutAction)
helpMenu.addAction(self.aboutQtAction)
def createToolBars(self):
taskToolBar = self.addToolBar("Task")
taskToolBar.setObjectName("TaskToolbar")
taskToolBar.addAction(self.newTaskAction)
taskToolBar.addAction(self.editAction)
taskToolBar.addAction(self.completeAction)
exitToolBar = self.addToolBar("Exit")
exitToolBar.setObjectName("ExitToolbar")
exitToolBar.addAction(self.exitAction)
def createStatusBar(self):
self.sizeLabel = QtGui.QLabel(" ")
self.sizeLabel.setFrameStyle(QtGui.QFrame.StyledPanel
| QtGui.QFrame.Sunken)
self.sizeLabel.setFrameStyle(QtGui.QFrame.Sunken)
status = self.statusBar()
status.setSizeGripEnabled(True)
status.addPermanentWidget(self.sizeLabel)
status.showMessage(self.tr("Ready"), 5000)
def openContextMenu(self, position):
menu = QtGui.QMenu(self)
menu.addAction(self.newTaskAction)
menu.addAction(self.editAction)
menu.addAction(self.completeAction)
menu.addSeparator()
menu.addAction(self.deleteAction)
#menu.exec_(self.tableWidget.mapToGlobal(position))
menu.popup(QtGui.QCursor.pos())
def createTableWidget(self):
"""Table widget creation to display TODO lists"""
self.tableWidget = QtGui.QTableWidget(0, 6)
self.tableWidget.setSelectionBehavior(
QtGui.QAbstractItemView.SelectRows)
# select one row at a time
self.tableWidget.setSelectionMode(
QtGui.QAbstractItemView.SingleSelection)
# no editing values
self.tableWidget.setEditTriggers(
QtGui.QAbstractItemView.NoEditTriggers)
self.tableWidget.setHorizontalHeaderLabels(
(self.tr("ID"), self.tr("Name"), self.tr("Priority"),
self.tr("Deadline"), self.tr("Completed"), self.tr("Created")))
#self.tableWidget.horizontalHeader().setResizeMode(0, QHeaderView.Stretch)
self.tableWidget.verticalHeader().hide()
self.tableWidget.setShowGrid(True)
#self.tableWidget.setContextMenuPolicy(QtCore.Qt.ActionsContextMenu)
#self.addActions(self.tableWidget, (self.newAction, self.aboutAction))
self.tableWidget.setContextMenuPolicy(QtCore.Qt.CustomContextMenu)
self.tableWidget.customContextMenuRequested.connect(
self.openContextMenu)
self.tableWidget.cellDoubleClicked.connect(self.rowDblClick)
self.refreshTable()
def refreshTable(self):
self.tableWidget.setRowCount(0)
rows = self.db.execSql("select * from TodoTask where status=0")
for row in rows:
cnt = self.tableWidget.rowCount()
self.tableWidget.insertRow(cnt)
self.tableWidget.setItem(cnt, 0,
QtGui.QTableWidgetItem(str(row["id"])))
self.tableWidget.setItem(cnt, 1,
QtGui.QTableWidgetItem(row["name"]))
self.tableWidget.setItem(
cnt, 2,
QtGui.QTableWidgetItem(self.priority.getName(row["priority"])))
self.tableWidget.setItem(
cnt, 3, QtGui.QTableWidgetItem(str(row["deadline"])))
self.tableWidget.setItem(
cnt, 4,
QtGui.QTableWidgetItem(
str(row["completed"]) if row["completed"] else ""))
self.tableWidget.setItem(
cnt, 5, QtGui.QTableWidgetItem(str(row["created"])))
QtGui.qApp.processEvents()
self.tableWidget.resizeColumnsToContents()
def createDockWindows(self):
dockWidget = QtGui.QDockWidget(self.tr("Log"), self)
dockWidget.setObjectName("LogDockWidget")
dockWidget.setAllowedAreas(QtCore.Qt.LeftDockWidgetArea
| QtCore.Qt.RightDockWidgetArea)
self.logWidget = QtGui.QListWidget()
dockWidget.setWidget(self.logWidget)
self.addDockWidget(QtCore.Qt.LeftDockWidgetArea, dockWidget)
self.viewMenu.addAction(dockWidget.toggleViewAction())
if not self.debug: dockWidget.close()
def newTask(self):
dialog = NewTaskDialog(self)
dialog.priority.addItems(self.priority.listNames())
if dialog.exec_():
args = {}
args["name"] = dialog.name.text()
args["priority"] = self.priority.getCode(
dialog.priority.currentText())
args["deadline"] = dialog.deadline.date().toPyDate()
args = self.task.save("", **args)
self.refreshTable()
def rowDblClick(self, row, col):
self._updateTask(row)
def editTask(self):
# index of currently selected row
row = self.tableWidget.currentRow()
if row == -1:
msgBox = QtGui.QMessageBox()
msgBox.setWindowTitle(self.tr('Edit'))
msgBox.setText(self.tr('Select a task to edit!'))
msgBox.exec_()
else:
self._updateTask(row)
def _updateTask(self, row):
# id of currently selected row
_id = int(self.tableWidget.item(row, 0).text())
row = self.task.read(_id)
# open dialog for editing record
dialog = NewTaskDialog(self)
dialog.name.setText(row["name"])
dialog.priority.addItems(self.priority.listNames())
dialog.priority.setCurrentIndex(row['priority'] - 1)
dialog.deadline.setDate(row['deadline'])
if dialog.exec_():
row["name"] = dialog.name.text()
row["priority"] = self.priority.getCode(
dialog.priority.currentText())
row["deadline"] = dialog.deadline.date().toPyDate()
args = self.task.save(_id, **row)
self.refreshTable()
def completeTask(self):
# index of currently selected row
row = self.tableWidget.currentRow()
if row == -1:
msgBox = QtGui.QMessageBox()
msgBox.setWindowTitle(self.tr('Complete'))
msgBox.setText(self.tr('Select a task to complete!'))
msgBox.exec_()
return
# id of currently selected row
_id = self.tableWidget.item(row, 0).text()
if QtGui.QMessageBox.question(
self, self.tr('Complete'),
self.tr('Are you sure to complete this task (id={})?').format(
_id), QtGui.QMessageBox.Yes,
QtGui.QMessageBox.No) == QtGui.QMessageBox.Yes:
self.db.execSql(
'update TodoTask set status=1, completed=? where id=?',
(datetime.now(), int(_id)))
self.db.commit()
self.refreshTable()
def deleteTask(self):
# index and id of currently selected row
row = self.tableWidget.currentRow()
if row == -1:
msgBox = QtGui.QMessageBox()
msgBox.setWindowTitle(self.tr('Delete'))
msgBox.setText(self.tr('Select a task to delete!'))
msgBox.exec_()
return
_id = self.tableWidget.item(row, 0).text()
if QtGui.QMessageBox.question(
self, self.tr('Delete'),
self.tr('Are you sure to delete this record (id={})?').format(
_id), QtGui.QMessageBox.Yes,
QtGui.QMessageBox.No) == QtGui.QMessageBox.Yes:
# remove row from table widget
self.tableWidget.removeRow(row)
# remove record from database
self.logger.write("{} delete from TodoTask where id={}".format(
now(), _id))
self.task.deleteId(_id)
self.statusBar().showMessage('Deleted record id=' + _id, 5000)
def about(self):
QtGui.QMessageBox.about(
self, self.tr("About Application"),
self.
tr("The purpose of the <b>Application</b> is:<br>"
" - the management of TODO lists..."
"<br><br>author: <a href='mailto:[email protected]'>Igor A.Vetrov</a> © 2012"
"<hr>Version: {}").format(getVersion()))
def setUp(self):
with threading.Lock():
self.dbName = "test.sqlite3"
self.db = SQLite(self.dbName)
self.table = Priority(self.db)
class PriorityTable(unittest.TestCase):
def setUp(self):
with threading.Lock():
self.dbName = "test.sqlite3"
self.db = SQLite(self.dbName)
self.table = Priority(self.db)
def tearDown(self):
with threading.Lock():
if os.path.exists(self.dbName):
self.db.__del__()
os.unlink(self.dbName)
def test_table_exists(self):
self.assertTrue( self.db.tableExists(self.table._tableName) )
def test_create_sql(self):
sql = "create table TodoPriority(\n" \
"\tcode integer primary key not null,\n" \
"\tname text not null,\n" \
"\tcreated timestamp default (datetime('now', 'localtime'))\n" \
");"
self.assertEqual( sql, self.table.createSql() )
def test_count(self):
cnt = self.table.count()
self.assertEqual( cnt, 3 )
def test_id_name(self):
self.assertEqual( self.table._idName, "code" )
def test_low(self):
row = self.table.select( "select name from {} where code=?;".format(self.table._tableName), (1,) )[0]
self.assertEqual( row["name"], "Low" )
def test_medium(self):
row = self.table.select( "select name from {} where code=?;".format(self.table._tableName), (2,) )[0]
self.assertEqual( row["name"], "Medium" )
def test_high(self):
row = self.table.select( "select name from {} where code=?;".format(self.table._tableName), (3,) )[0]
self.assertEqual( row["name"], "High" )
def test_openId(self):
self.table.openId(1)
self.assertEqual( self.table.name, "Low" )
def test_read(self):
args = self.table.read(2)
self.assertEqual( args["code"], 2 )
self.assertEqual( args["name"], "Medium" )
self.assertEqual( args["id"], 2 )
def test_save(self):
args = dict(code=9, name="Unused")
args = self.table.save("", **args)
self.assertEqual( self.table.count(), 4 )
self.assertEqual( args["id"], 9 )
del args
args = self.table.read(9)
self.assertEqual( args["id"], 9 )
self.assertEqual( args["code"], 9 )
self.assertEqual( args["name"], "Unused" )
def test_update(self):
args = dict(code=9, name="Unused")
args = self.table.save("", **args)
del args
args = self.table.read(9)
args["name"] = "Used"
args = self.table.save(9, **args)
del args
args = self.table.read(9)
self.assertEqual( args["id"], 9 )
self.assertEqual( args["code"], 9 )
self.assertEqual( args["name"], "Used" )
def test_delete(self):
args = self.table.deleteId(2)
self.assertEqual( self.table.count(), 2 )
self.assertFalse( self.table.existsId(2) )
def test_getValue(self):
value = self.table.getValue(2, "code", "name")
self.assertEqual( value, (2, "Medium") )
def test_setValue(self):
self.table.setValue(2, name="Changing Medium")
value = self.table.getValue(2, "name")
self.assertEqual( value, ("Changing Medium",) )
def test_getCode(self):
code = self.table.getCode("High")
self.assertEqual( code, 3 )
def test_getName(self):
name = self.table.getName(3)
self.assertEqual( name, "High" )
def test_listNames(self):
values = self.table.listNames()
self.assertEqual( values, ["Low", "Medium", "High"] )
def test_repr(self):
self.table.open(2)
dt = self.table['created']
value = str(self.table)
self.assertEqual( value, "Priority([('created', {}, ('id', 2), ('code', 2), ('name', 'Medium')])".format(dt) )
def test_keys(self):
self.table.open(2)
self.assertEqual( set(list(self.table.keys())), set(["id", "code", "name", "created"]) )
def test_open(self):
obj = self.table
obj.open(3)
self.assertEqual( obj["name"], "High" )
class TaskTable(unittest.TestCase):
def setUp(self):
with threading.Lock():
self.dbName = "test.sqlite3"
self.db = SQLite(self.dbName)
self.priority = Priority(self.db)
self.task = Task(self.db)
self.task.exec( "insert into {} (name, priority, deadline) values(?, ?, ?)".format(self.task._tableName),
("Low Test", 1, date.today() + timedelta(2)) )
self.task.exec( "insert into {} (name, priority, deadline) values(?, ?, ?)".format(self.task._tableName),
("Medium Test", 2, date.today() + timedelta(3)) )
self.task.exec( "insert into {} (name, priority, deadline) values(?, ?, ?)".format(self.task._tableName),
("High Test", 3, date.today() + timedelta(4)) )
def tearDown(self):
with threading.Lock():
if os.path.exists(self.dbName):
self.db.__del__()
os.unlink(self.dbName)
def test_table_exists(self):
self.assertTrue( self.db.tableExists(self.priority._tableName) )
self.assertTrue( self.db.tableExists(self.task._tableName) )
def test_create_sql(self):
sql = "create table TodoTask(\n" \
"\tid integer primary key autoincrement not null,\n" \
"\tname text not null,\n" \
"\tpriority integer references TodoPriority(code) default 2,\n" \
"\tdeadline date not null default (date('now', 'localtime')),\n" \
"\tstatus integer default 0,\n" \
"\tcompleted timestamp,\n" \
"\tcreated timestamp default (datetime('now', 'localtime'))\n" \
");"
self.assertEqual( sql, self.task.createSql() )
def test_index_exists(self):
self.assertIn( "status", self.task._indices )
def test_id_name(self):
self.assertEqual( self.task._idName, "id" )
def test_count(self):
cnt = self.task.count()
self.assertEqual( cnt, 3 )
def test_low(self):
row = self.task.select( "select * from {} where id=?;".format(self.task._tableName), (1,) )[0]
self.assertEqual( row["name"], "Low Test" )
self.assertEqual( row["priority"], 1 )
self.assertEqual( row["deadline"], date.today() + timedelta(2) )
def test_medium(self):
row = self.task.select( "select * from {} where id=?;".format(self.task._tableName), (2,) )[0]
self.assertEqual( row["name"], "Medium Test" )
self.assertEqual( row["priority"], 2 )
self.assertEqual( row["deadline"], date.today() + timedelta(3) )
def test_high(self):
row = self.task.select( "select * from {} where id=?;".format(self.task._tableName), (3,) )[0]
self.assertEqual( row["name"], "High Test" )
self.assertEqual( row["priority"], 3 )
self.assertEqual( row["deadline"], date.today() + timedelta(4) )
def test_integrity(self):
self.assertRaises( IntegrityError, self.db.execSql,
"insert into {} (name, priority, deadline) values(?, ?, ?)".format(self.task._tableName),
("Highest Test", 4, date.today() + timedelta(4)) )
def test_openId(self):
self.task.openId(2)
self.assertEqual( self.task.name, "Medium Test" )
def test_read(self):
args = self.task.read(2)
self.assertEqual( args["id"], 2 )
self.assertEqual( args["name"], "Medium Test" )
self.assertEqual( args["priority"], 2 )
self.assertEqual( args["deadline"], date.today() + timedelta(3) )
def test_save(self):
args = dict(name="Highest Test", priority=3, deadline=date.today()+timedelta(5))
args = self.task.save("", **args)
self.assertEqual( self.task.count(), 4 )
self.assertEqual( args["id"], 4 )
del args
args = self.task.read(4)
self.assertEqual( args["id"], 4 )
self.assertEqual( args["status"], 0 )
self.assertEqual( args["name"], "Highest Test" )
self.assertEqual( args["priority"], 3 )
self.assertEqual( args["deadline"], date.today() + timedelta(5) )
def test_delete(self):
args = self.task.deleteId(3)
self.assertEqual( self.task.count(), 2 )
self.assertFalse( self.task.existsId(3) )
def test_getValue(self):
value = self.task.getValue(3, "name")
self.assertEqual( value, ("High Test",) )
def test_setValue(self):
self.task.setValue(3, name="Changing High Test", deadline=date.today() + timedelta(8))
value = self.task.getValue(3, "name", "deadline")
self.assertEqual( value, ("Changing High Test", date.today() + timedelta(8)) )