def test_backup_load(tmp_path, project_root):
# Prepare environment
src_path = project_root + os.sep + 'jal' + os.sep + Setup.INIT_SCRIPT_PATH
target_path = str(tmp_path) + os.sep + Setup.INIT_SCRIPT_PATH
copyfile(src_path, target_path)
init_and_check_db(str(tmp_path) + os.sep)
# Here backup is created without parent window - need to use with care
db_file_name = get_dbfilename(str(tmp_path) + os.sep)
backup = JalBackup(None, db_file_name)
backup.backup_name = project_root + os.sep + "tests" + os.sep + "test_data" + os.sep + "deals_set.tgz"
assert backup.validate_backup()
# Check validation
assert backup._backup_label_date == '2021/01/01 00:00:00+0300'
backup.do_restore()
# Check restoration
db = sqlite3.connect(db_file_name)
cursor = db.cursor()
cursor.execute("SELECT COUNT(*) FROM settings")
assert cursor.fetchone()[0] == 7
db.close()
os.remove(target_path) # Clean db init script
os.remove(get_dbfilename(str(tmp_path) + os.sep)) # Clean db file
def init_db(self, db_path) -> JalDBError:
db = QSqlDatabase.addDatabase("QSQLITE", Setup.DB_CONNECTION)
if not db.isValid():
return JalDBError(JalDBError.DbDriverFailure)
db.setDatabaseName(get_dbfilename(db_path))
db.setConnectOptions("QSQLITE_ENABLE_REGEXP=1")
db.open()
sqlite_version = readSQL("SELECT sqlite_version()")
if parse_version(sqlite_version) < parse_version(
Setup.SQLITE_MIN_VERSION):
db.close()
return JalDBError(JalDBError.OutdatedSqlite)
tables = db.tables(QSql.Tables)
if not tables:
logging.info("Loading DB initialization script")
error = self.run_sql_script(db_path + Setup.INIT_SCRIPT_PATH)
if error.code != JalDBError.NoError:
return error
schema_version = JalSettings().getValue('SchemaVersion')
if schema_version < Setup.TARGET_SCHEMA:
db.close()
return JalDBError(JalDBError.OutdatedDbSchema)
elif schema_version > Setup.TARGET_SCHEMA:
db.close()
return JalDBError(JalDBError.NewerDbSchema)
_ = executeSQL("PRAGMA foreign_keys = ON")
db_triggers_enable()
return JalDBError(JalDBError.NoError)
def test_invalid_backup(tmp_path, project_root):
# Prepare environment
src_path = project_root + os.sep + 'jal' + os.sep + Setup.INIT_SCRIPT_PATH
target_path = str(tmp_path) + os.sep + Setup.INIT_SCRIPT_PATH
copyfile(src_path, target_path)
init_and_check_db(str(tmp_path) + os.sep)
# Here backup is created without parent window - need to use with care
db_file_name = get_dbfilename(str(tmp_path) + os.sep)
invalid_backup = JalBackup(None, db_file_name)
invalid_backup.backup_name = project_root + os.sep + "tests" + os.sep + "test_data" + os.sep + "invalid_backup.tgz"
assert not invalid_backup.validate_backup()
os.remove(target_path) # Clean db init script
os.remove(get_dbfilename(str(tmp_path) + os.sep)) # Clean db file
def __init__(self, language):
QMainWindow.__init__(self, None)
self.running = False
self.setupUi(self)
self.restoreGeometry(base64.decodebytes(JalSettings().getValue('WindowGeometry', '').encode('utf-8')))
self.restoreState(base64.decodebytes(JalSettings().getValue('WindowState', '').encode('utf-8')))
self.ledger = Ledger()
# Customize Status bar and logs
self.ProgressBar = QProgressBar(self)
self.StatusBar.addPermanentWidget(self.ProgressBar)
self.ProgressBar.setVisible(False)
self.ledger.setProgressBar(self, self.ProgressBar)
self.Logs.setStatusBar(self.StatusBar)
self.logger = logging.getLogger()
self.logger.addHandler(self.Logs)
log_level = os.environ.get('LOGLEVEL', 'INFO').upper()
self.logger.setLevel(log_level)
self.currentLanguage = language
self.downloader = QuoteDownloader()
self.statements = Statements(self)
self.reports = Reports(self, self.mdiArea)
self.backup = JalBackup(self, get_dbfilename(get_app_path()))
self.estimator = None
self.price_chart = None
self.actionImportSlipRU.setEnabled(dependency_present(['pyzbar', 'PIL']))
self.actionAbout = QAction(text=self.tr("About"), parent=self)
self.MainMenu.addAction(self.actionAbout)
self.langGroup = QActionGroup(self.menuLanguage)
self.createLanguageMenu()
self.statementGroup = QActionGroup(self.menuStatement)
self.createStatementsImportMenu()
self.reportsGroup = QActionGroup(self.menuReports)
self.createReportsMenu()
self.setWindowIcon(load_icon("jal.png"))
self.connect_signals_and_slots()
self.actionOperations.trigger()
def test_db_creation(tmp_path, project_root):
# Prepare environment
src_path = project_root + os.sep + 'jal' + os.sep + Setup.INIT_SCRIPT_PATH
target_path = str(tmp_path) + os.sep + Setup.INIT_SCRIPT_PATH
copyfile(src_path, target_path)
error = init_and_check_db(str(tmp_path) + os.sep)
# Check that sqlite db file was created
result_path = str(tmp_path) + os.sep + Setup.DB_PATH
assert os.path.exists(result_path)
assert os.path.getsize(result_path) > 0
assert error.code == LedgerInitError.EmptyDbInitialized
os.remove(target_path) # Clean db init script
os.remove(get_dbfilename(str(tmp_path) + os.sep)) # Clean db file
def prepare_db(project_root, tmp_path, data_path):
# Prepare environment
src_path = project_root + os.sep + 'jal' + os.sep + Setup.INIT_SCRIPT_PATH
target_path = str(tmp_path) + os.sep + Setup.INIT_SCRIPT_PATH
copyfile(src_path, target_path)
# Activate db connection
error = init_and_check_db(str(tmp_path) + os.sep)
assert error.code == LedgerInitError.EmptyDbInitialized
error = init_and_check_db(str(tmp_path) + os.sep)
assert error.code == LedgerInitError.DbInitSuccess
db = QSqlDatabase.database(Setup.DB_CONNECTION)
assert db.isValid()
lang_id = JalDB().get_language_id('en')
assert lang_id == 1
yield
os.remove(target_path) # Clean db init script
os.remove(get_dbfilename(str(tmp_path) + os.sep)) # Clean db file
def __init__(self, language):
QMainWindow.__init__(self, None)
self.setupUi(self)
self.currentLanguage = language
self.current_index = None # this is used in onOperationContextMenu() to track item for menu
self.ledger = Ledger()
self.downloader = QuoteDownloader()
self.taxes = TaxesRus()
self.statements = StatementLoader()
self.backup = JalBackup(self, get_dbfilename(get_app_path()))
self.estimator = None
self.price_chart = None
self.actionImportSlipRU.setEnabled(
dependency_present(['pyzbar', 'PIL']))
self.actionAbout = QAction(text=self.tr("About"), parent=self)
self.MainMenu.addAction(self.actionAbout)
self.langGroup = QActionGroup(self.menuLanguage)
self.createLanguageMenu()
self.statementGroup = QActionGroup(self.menuStatement)
self.createStatementsImportMenu()
# Set icons
self.setWindowIcon(load_icon("jal.png"))
self.NewOperationBtn.setIcon(load_icon("new.png"))
self.CopyOperationBtn.setIcon(load_icon("copy.png"))
self.DeleteOperationBtn.setIcon(load_icon("delete.png"))
# Operations view context menu
self.contextMenu = QMenu(self.OperationsTableView)
self.actionReconcile = QAction(load_icon("reconcile.png"),
self.tr("Reconcile"), self)
self.actionCopy = QAction(load_icon("copy.png"), self.tr("Copy"), self)
self.actionDelete = QAction(load_icon("delete.png"), self.tr("Delete"),
self)
self.contextMenu.addAction(self.actionReconcile)
self.contextMenu.addSeparator()
self.contextMenu.addAction(self.actionCopy)
self.contextMenu.addAction(self.actionDelete)
# Customize Status bar and logs
self.ProgressBar = QProgressBar(self)
self.StatusBar.addWidget(self.ProgressBar)
self.ProgressBar.setVisible(False)
self.ledger.setProgressBar(self, self.ProgressBar)
self.NewLogEventLbl = QLabel(self)
self.StatusBar.addWidget(self.NewLogEventLbl)
self.Logs.setNotificationLabel(self.NewLogEventLbl)
self.Logs.setFormatter(
logging.Formatter('%(asctime)s - %(levelname)s - %(message)s'))
self.logger = logging.getLogger()
self.logger.addHandler(self.Logs)
log_level = os.environ.get('LOGLEVEL', 'INFO').upper()
self.logger.setLevel(log_level)
# Setup reports tab
self.reports = Reports(self.ReportTableView, self.ReportTreeView)
# Customize UI configuration
self.balances_model = BalancesModel(self.BalancesTableView)
self.BalancesTableView.setModel(self.balances_model)
self.balances_model.configureView()
self.holdings_model = HoldingsModel(self.HoldingsTableView)
self.HoldingsTableView.setModel(self.holdings_model)
self.holdings_model.configureView()
self.HoldingsTableView.setContextMenuPolicy(Qt.CustomContextMenu)
self.operations_model = OperationsModel(self.OperationsTableView)
self.OperationsTableView.setModel(self.operations_model)
self.operations_model.configureView()
self.OperationsTableView.setContextMenuPolicy(Qt.CustomContextMenu)
self.connect_signals_and_slots()
self.NewOperationMenu = QMenu()
for i in range(self.OperationsTabs.count()):
if hasattr(self.OperationsTabs.widget(i), "isCustom"):
self.OperationsTabs.widget(i).dbUpdated.connect(
self.ledger.rebuild)
self.OperationsTabs.widget(i).dbUpdated.connect(
self.operations_model.refresh)
self.NewOperationMenu.addAction(
self.OperationsTabs.widget(i).name,
partial(self.createOperation, i))
self.NewOperationBtn.setMenu(self.NewOperationMenu)
# Setup balance and holdings parameters
current_time = QDateTime.currentDateTime()
current_time.setTimeSpec(
Qt.UTC) # We use UTC everywhere so need to force TZ info
self.BalanceDate.setDateTime(current_time)
self.BalancesCurrencyCombo.setIndex(
JalSettings().getValue('BaseCurrency'))
self.HoldingsDate.setDateTime(current_time)
self.HoldingsCurrencyCombo.setIndex(
JalSettings().getValue('BaseCurrency'))
self.OperationsTabs.setCurrentIndex(TransactionType.NA)
self.OperationsTableView.selectRow(0)
self.OnOperationsRangeChange(0)
def test_fifo(tmp_path, project_root):
# Prepare environment
src_path = project_root + os.sep + 'jal' + os.sep + Setup.INIT_SCRIPT_PATH
target_path = str(tmp_path) + os.sep + Setup.INIT_SCRIPT_PATH
copyfile(src_path, target_path)
init_and_check_db(str(tmp_path) + os.sep)
db_file_name = get_dbfilename(str(tmp_path) + os.sep)
backup = JalBackup(None, db_file_name)
backup.backup_name = project_root + os.sep + "tests" + os.sep + "test_data" + os.sep + "deals_set.tgz"
backup.do_restore()
error = init_and_check_db(str(tmp_path) + os.sep)
assert error.code == LedgerInitError.DbInitSuccess
ledger = Ledger()
ledger.rebuild(from_timestamp=0)
# Check single deal
db_file_name = get_dbfilename(str(tmp_path) + os.sep)
db = sqlite3.connect(db_file_name)
cursor = db.cursor()
# Check single deal
cursor.execute("SELECT COUNT(*) FROM deals_ext WHERE asset_id=4")
assert cursor.fetchone()[0] == 1
cursor.execute("SELECT SUM(profit) FROM deals_ext WHERE asset_id=4")
assert cursor.fetchone()[0] == 994
cursor.execute("SELECT SUM(fee) FROM deals_ext WHERE asset_id=4")
assert cursor.fetchone()[0] == 6
# One buy multiple sells
cursor.execute("SELECT COUNT(*) FROM deals_ext WHERE asset_id=5")
assert cursor.fetchone()[0] == 2
cursor.execute("SELECT SUM(profit) FROM deals_ext WHERE asset_id=5")
assert cursor.fetchone()[0] == -56
cursor.execute("SELECT SUM(fee) FROM deals_ext WHERE asset_id=5")
assert cursor.fetchone()[0] == 6
# Multiple buy one sell
cursor.execute("SELECT COUNT(*) FROM deals_ext WHERE asset_id=6")
assert cursor.fetchone()[0] == 2
cursor.execute("SELECT SUM(profit) FROM deals_ext WHERE asset_id=6")
assert cursor.fetchone()[0] == -1306
cursor.execute("SELECT SUM(fee) FROM deals_ext WHERE asset_id=6")
assert cursor.fetchone()[0] == 6
# One sell multiple buys
cursor.execute("SELECT COUNT(*) FROM deals_ext WHERE asset_id=7")
assert cursor.fetchone()[0] == 2
cursor.execute("SELECT SUM(profit) FROM deals_ext WHERE asset_id=7")
assert cursor.fetchone()[0] == -78
cursor.execute("SELECT SUM(fee) FROM deals_ext WHERE asset_id=7")
assert cursor.fetchone()[0] == 3
# Multiple sells one buy
cursor.execute("SELECT COUNT(*) FROM deals_ext WHERE asset_id=8")
assert cursor.fetchone()[0] == 2
cursor.execute("SELECT SUM(profit) FROM deals_ext WHERE asset_id=8")
assert cursor.fetchone()[0] == 317
cursor.execute("SELECT SUM(fee) FROM deals_ext WHERE asset_id=8")
assert cursor.fetchone()[0] == 3
# Multiple buys and sells
cursor.execute("SELECT COUNT(*) FROM deals_ext WHERE asset_id=9")
assert cursor.fetchone()[0] == 11
cursor.execute("SELECT SUM(profit) FROM deals_ext WHERE asset_id=9")
assert cursor.fetchone()[0] == 3500
cursor.execute("SELECT SUM(fee) FROM deals_ext WHERE asset_id=9")
assert cursor.fetchone()[0] == 0
# Symbol change
cursor.execute("SELECT COUNT(*) FROM deals_ext WHERE asset_id=10")
assert cursor.fetchone()[0] == 1
cursor.execute("SELECT COUNT(*) FROM deals_ext WHERE asset_id=11")
assert cursor.fetchone()[0] == 1
cursor.execute("SELECT profit FROM deals_ext WHERE asset_id=11")
assert cursor.fetchone()[0] == 1200
# Spin-off
cursor.execute("SELECT COUNT(*) FROM deals_ext WHERE asset_id=12")
assert cursor.fetchone()[0] == 1
cursor.execute("SELECT profit FROM deals_ext WHERE asset_id=12")
assert cursor.fetchone()[0] == 0
# Multiple corp actions
cursor.execute("SELECT COUNT(*) FROM deals_ext WHERE asset_id=13 AND corp_action IS NOT NULL")
assert cursor.fetchone()[0] == 1
cursor.execute("SELECT profit FROM deals_ext WHERE asset_id=13")
assert cursor.fetchone()[0] == 0
cursor.execute("SELECT COUNT(*) FROM deals_ext WHERE asset_id=14")
assert cursor.fetchone()[0] == 3
cursor.execute("SELECT COUNT(*) FROM deals_ext WHERE asset_id=14 AND corp_action IS NOT NULL")
assert cursor.fetchone()[0] == 2
cursor.execute("SELECT profit FROM deals_ext WHERE asset_id=14 AND corp_action IS NULL")
assert cursor.fetchone()[0] == 75
cursor.execute("SELECT profit FROM deals_ext WHERE asset_id=14 AND corp_action IS NOT NULL")
assert cursor.fetchone()[0] == 0
cursor.execute("SELECT COUNT(*) FROM deals_ext WHERE asset_id=15 AND corp_action IS NOT NULL")
assert cursor.fetchone()[0] == 1
cursor.execute("SELECT profit FROM deals_ext WHERE asset_id=15")
assert cursor.fetchone()[0] == 274
# Stock dividend
cursor.execute("SELECT COUNT(*) FROM deals_ext WHERE asset_id=16")
assert cursor.fetchone()[0] == 3
cursor.execute("SELECT SUM(profit) FROM deals_ext WHERE asset_id=16")
assert cursor.fetchone()[0] == approx(1500)
cursor.execute("SELECT profit FROM deals_ext WHERE asset_id=16 AND close_timestamp=1608454800")
assert cursor.fetchone()[0] == approx(166.666667)
cursor.execute("SELECT profit FROM deals_ext WHERE asset_id=16 AND close_timestamp=1608541200")
assert cursor.fetchone()[0] == approx(1333.333333)
# Order of buy/sell
cursor.execute("SELECT COUNT(*) FROM deals_ext WHERE asset_id=17")
assert cursor.fetchone()[0] == 2
cursor.execute("SELECT SUM(profit) FROM deals_ext WHERE asset_id=17")
assert cursor.fetchone()[0] == 140
cursor.execute("SELECT COUNT(*) FROM deals_ext WHERE asset_id=18")
assert cursor.fetchone()[0] == 4
cursor.execute("SELECT SUM(qty) FROM deals_ext WHERE asset_id=18")
assert cursor.fetchone()[0] == -2
cursor.execute("SELECT SUM(profit) FROM deals_ext WHERE asset_id=18")
assert cursor.fetchone()[0] == 200
# totals
cursor.execute("SELECT COUNT(*) FROM deals AS d "
"LEFT JOIN sequence as os ON os.id = d.open_sid "
"LEFT JOIN sequence as cs ON cs.id = d.close_sid")
assert cursor.fetchone()[0] == 41
cursor.execute("SELECT COUNT(*) FROM deals AS d "
"LEFT JOIN sequence as os ON os.id = d.open_sid "
"LEFT JOIN sequence as cs ON cs.id = d.close_sid "
"WHERE os.type==3 AND cs.type==3")
assert cursor.fetchone()[0] == 27
cursor.execute("SELECT COUNT(*) FROM deals AS d "
"LEFT JOIN sequence as os ON os.id = d.open_sid "
"LEFT JOIN sequence as cs ON cs.id = d.close_sid "
"WHERE os.type!=5 OR cs.type!=5")
assert cursor.fetchone()[0] == 37
cursor.execute("SELECT COUNT(*) FROM deals AS d "
"LEFT JOIN sequence as os ON os.id = d.open_sid "
"LEFT JOIN sequence as cs ON cs.id = d.close_sid "
"WHERE os.type==5 AND cs.type==5")
assert cursor.fetchone()[0] == 4
# validate final amounts
cursor.execute("SELECT MAX(sid), asset_id, sum_amount, sum_value FROM ledger_sums "
"GROUP BY asset_id")
ledger_sums = cursor.fetchall()
for row in ledger_sums:
if row[1] == 1: # Checking money amount
assert row[2] == 16760
else:
assert row[2] == 0
assert row[3] == 0
db.close()
