def init_and_check_db(db_path):
db = QSqlDatabase.addDatabase("QSQLITE", Setup.DB_CONNECTION)
if not db.isValid():
return LedgerInitError(LedgerInitError.DbDriverFailure)
db.setDatabaseName(get_dbfilename(db_path))
db.setConnectOptions("QSQLITE_ENABLE_REGEXP=1")
db.open()
tables = db.tables(QSql.Tables)
if not tables:
db.close()
connection_name = db.connectionName()
init_db_from_sql(get_dbfilename(db_path),
db_path + Setup.INIT_SCRIPT_PATH)
QSqlDatabase.removeDatabase(connection_name)
return LedgerInitError(LedgerInitError.EmptyDbInitialized)
schema_version = JalSettings().getValue('SchemaVersion')
if schema_version < Setup.TARGET_SCHEMA:
db.close()
return LedgerInitError(LedgerInitError.OutdatedDbSchema)
elif schema_version > Setup.TARGET_SCHEMA:
db.close()
return LedgerInitError(LedgerInitError.NewerDbSchema)
_ = executeSQL("PRAGMA foreign_keys = ON")
db_triggers_enable()
return LedgerInitError(LedgerInitError.DbInitSuccess)
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 db_connection():
db = QSqlDatabase.database(Setup.DB_CONNECTION)
if not db.isValid():
raise RuntimeError(f"DB connection '{Setup.DB_CONNECTION}' is invalid")
if not db.isOpen():
logging.fatal(f"DB connection '{Setup.DB_CONNECTION}' is not open")
return db
def __init__(self):
self.db = QSqlDatabase.database(Setup.DB_CONNECTION)
if not self.db.isValid():
self.db = None
logging.fatal("DB connection is invalid")
return
if not self.db.isOpen():
self.db = None
logging.fatal("DB connection is not open")
return
def connect(self) -> str:
if not self.db.isValid():
self.db = QSqlDatabase.addDatabase("QSQLITE")
if not self.db.isValid():
logger.error("Cannot add db")
write_dir = QDir("")
if not write_dir.mkpath("."):
logger.error("Failed to create writable directory")
return write_dir.absoluteFilePath(f"{self.name}.db")
def __init__(self,
name: str | None = None,
direc: Path | None = None):
match name:
case None: self.name = "isu-db"
case n: self.name = n
match direc:
case None: self.directory = QDir(".")
case d: self.directory = QDir(d)
self.db = QSqlDatabase.database()
self.db.setDatabaseName(self.name)
self.path = QFile(self.connect())
def init_db():
"""
init_db()
Initializes the database.
If tables "books" and "authors" are already in the database, do nothing.
Return value: None or raises ValueError
The error value is the QtSql error instance.
"""
def check(func, *args):
if not func(*args):
raise ValueError(func.__self__.lastError())
db = QSqlDatabase.addDatabase("QSQLITE")
db.setDatabaseName(":memory:")
check(db.open)
q = QSqlQuery()
check(q.exec_, BOOKS_SQL)
check(q.exec_, AUTHORS_SQL)
check(q.exec_, GENRES_SQL)
check(q.prepare, INSERT_AUTHOR_SQL)
asimovId = add_author(q, "Isaac Asimov", date(1920, 2, 1))
greeneId = add_author(q, "Graham Greene", date(1904, 10, 2))
pratchettId = add_author(q, "Terry Pratchett", date(1948, 4, 28))
check(q.prepare, INSERT_GENRE_SQL)
sfiction = add_genre(q, "Science Fiction")
fiction = add_genre(q, "Fiction")
fantasy = add_genre(q, "Fantasy")
check(q.prepare, INSERT_BOOK_SQL)
add_book(q, "Foundation", 1951, asimovId, sfiction, 3)
add_book(q, "Foundation and Empire", 1952, asimovId, sfiction, 4)
add_book(q, "Second Foundation", 1953, asimovId, sfiction, 3)
add_book(q, "Foundation's Edge", 1982, asimovId, sfiction, 3)
add_book(q, "Foundation and Earth", 1986, asimovId, sfiction, 4)
add_book(q, "Prelude to Foundation", 1988, asimovId, sfiction, 3)
add_book(q, "Forward the Foundation", 1993, asimovId, sfiction, 3)
add_book(q, "The Power and the Glory", 1940, greeneId, fiction, 4)
add_book(q, "The Third Man", 1950, greeneId, fiction, 5)
add_book(q, "Our Man in Havana", 1958, greeneId, fiction, 4)
add_book(q, "Guards! Guards!", 1989, pratchettId, fantasy, 3)
add_book(q, "Night Watch", 2002, pratchettId, fantasy, 3)
add_book(q, "Going Postal", 2004, pratchettId, fantasy, 3)
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 connect(self, db): #Tested
"""
Uses the Database Driver to create a connection with a local
database.If Db not avaliable will create new Db
>>> library_manager.connect("default.db")
Parameters
----------
db : String
path of a Valid Database or a Database name in order to create a blank one
Returns
-------
Boolean
returns true if connection was succesful
Raises
------
ConnectionError
if database fails to connect or fails checks
"""
if ((db == ":memory:") or PathUtils.isFileExt(db, ".db")):
self.db_driver = QSqlDatabase.addDatabase("QSQLITE")
self.db_driver.setDatabaseName(db)
else:
return False
self.DB_NAME = db
if self.db_driver.open() and self.IsConneted():
if self.db_driver.isOpenError():
raise ConnectionError()
if not self.StartUpChecks():
raise DBStructureError("Startup Checks Failed")
else:
return True
# only executes when connection fails and app closes
else: # pragma: no cover
raise ConnectionError()