def initial_schema_and_setup(i_db_conn):
"""Auto-increment is not needed in our case: https://www.sqlite.org/autoinc.html
"""
i_db_conn.execute("CREATE TABLE " + Schema.PhrasesTable.name + "(" +
Schema.PhrasesTable.Cols.id + " INTEGER PRIMARY KEY, " +
Schema.PhrasesTable.Cols.title + " TEXT NOT NULL, " +
Schema.PhrasesTable.Cols.ib_phrase + " TEXT NOT NULL, " +
Schema.PhrasesTable.Cols.ob_phrase + " TEXT NOT NULL, " +
Schema.PhrasesTable.Cols.vertical_order +
" INTEGER NOT NULL" + ")")
i_db_conn.execute("CREATE TABLE " + Schema.RestActionsTable.name + "(" +
Schema.RestActionsTable.Cols.id +
" INTEGER PRIMARY KEY, " +
Schema.RestActionsTable.Cols.title + " TEXT NOT NULL, " +
Schema.RestActionsTable.Cols.image_path +
" TEXT NOT NULL, " +
Schema.RestActionsTable.Cols.vertical_order +
" INTEGER NOT NULL" + ")")
i_db_conn.execute("CREATE TABLE " + Schema.SettingsTable.name + "(" +
Schema.SettingsTable.Cols.id + " INTEGER PRIMARY KEY, " +
Schema.SettingsTable.Cols.rest_reminder_active +
" INTEGER NOT NULL" + " DEFAULT " +
str(SQLITE_TRUE_INT) + ", " +
Schema.SettingsTable.Cols.rest_reminder_interval +
" INTEGER NOT NULL" + " DEFAULT " +
str(DEFAULT_REST_REMINDER_INTERVAL_MINUTES_INT) + ", " +
Schema.SettingsTable.Cols.breathing_reminder_active +
" INTEGER NOT NULL" + " DEFAULT " +
str(SQLITE_TRUE_INT) + ", " +
Schema.SettingsTable.Cols.breathing_reminder_interval +
" INTEGER NOT NULL" + " DEFAULT " +
str(DEFAULT_BREATHING_REMINDER_INTERVAL_MINUTES_INT) +
", " +
Schema.SettingsTable.Cols.breathing_reminder_length +
" INTEGER NOT NULL" + " DEFAULT " +
str(DEFAULT_BREATHING_REMINDER_LENGTH_SECONDS_INT) + ")")
db_connection = Helper.get_db_connection()
db_cursor = db_connection.cursor()
db_cursor.execute(
"INSERT OR IGNORE INTO " + Schema.SettingsTable.name + "(" +
Schema.SettingsTable.Cols.id + ") VALUES (?)",
(SINGLE_SETTINGS_ID_INT, ))
# -please note "OR IGNORE"
db_connection.commit()
if mc_global.testing_bool:
model.populate_db_with_test_data()
else:
if not mc_global.db_file_exists_at_application_startup_bl:
model.populate_db_with_setup_data()
else:
pass # -default, the user has started the application before and is not testing
def get_db_connection() -> sqlite3.Connection:
if Helper.__db_connection is None:
Helper.__db_connection = sqlite3.connect(
mc_global.get_database_filename())
# Upgrading the database
# Very good upgrade explanation:
# http://stackoverflow.com/questions/19331550/database-change-with-software-update
# More info here: https://www.sqlite.org/pragma.html#pragma_schema_version
current_db_ver_it = get_schema_version(Helper.__db_connection)
target_db_ver_it = max(upgrade_steps)
database_tables_dropped_bool = False
if current_db_ver_it < min(
upgrade_steps
) and mc_global.db_file_exists_at_application_startup_bl:
database_tables_dropped_bool = True
backup_db_file()
mc_global.db_upgrade_message_str = (
"Database upgraded, all user entries have been removed. "
"The old db has been backed up, and is available in the user_files directory. "
)
try:
model.export_all()
# -please note that there is a high likelihood that this fails, the reason is that the tables
# have now been changed
mc_global.db_upgrade_message_str += (
"Some of the old db contents has also been exported to a text file "
"that you can find here: /user_files/exported.csv")
except:
pass
Helper.drop_all_db_tables(Helper.__db_connection)
for upgrade_step_nr_int in range(current_db_ver_it + 1,
target_db_ver_it + 1):
if upgrade_step_nr_int in upgrade_steps:
upgrade_steps[upgrade_step_nr_int](Helper.__db_connection)
set_schema_version(Helper.__db_connection,
upgrade_step_nr_int)
if mc_global.testing_bool:
model.populate_db_with_test_data()
else:
if database_tables_dropped_bool:
model.populate_db_with_setup_data()
elif not mc_global.db_file_exists_at_application_startup_bl:
model.populate_db_with_setup_data()
else:
pass # -default, the user has started the application before and is not testing
# TODO: Where do we close the db connection? (Do we need to close it?)
# http://stackoverflow.com/questions/3850261/doing-something-before-program-exit
return Helper.__db_connection
def get_db_connection() -> sqlite3.Connection:
if Helper.__db_connection is None:
Helper.__db_connection = sqlite3.connect(mc_global.get_database_filename())
# Upgrading the database
# Very good upgrade explanation:
# http://stackoverflow.com/questions/19331550/database-change-with-software-update
# More info here: https://www.sqlite.org/pragma.html#pragma_schema_version
current_db_ver_it = get_schema_version(Helper.__db_connection)
target_db_ver_it = max(upgrade_steps)
database_tables_dropped_bool = False
if current_db_ver_it < min(upgrade_steps) and mc_global.db_file_exists_at_application_startup_bl:
database_tables_dropped_bool = True
backup_db_file()
mc_global.db_upgrade_message_str = (
"Database upgraded, all user entries have been removed. "
"The old db has been backed up, and is available in the user_files directory. "
)
try:
model.export_all()
# -please note that there is a high likelihood that this fails, the reason is that the tables
# have now been changed
mc_global.db_upgrade_message_str += (
"Some of the old db contents has also been exported to a text file "
"that you can find here: /user_files/exported.csv"
)
except Exception:
pass
Helper.drop_all_db_tables(Helper.__db_connection)
for upgrade_step_nr_int in range(current_db_ver_it + 1, target_db_ver_it + 1):
if upgrade_step_nr_int in upgrade_steps:
upgrade_steps[upgrade_step_nr_int](Helper.__db_connection)
set_schema_version(Helper.__db_connection, upgrade_step_nr_int)
if mc_global.testing_bool:
model.populate_db_with_test_data()
else:
if database_tables_dropped_bool:
model.populate_db_with_setup_data()
elif not mc_global.db_file_exists_at_application_startup_bl:
model.populate_db_with_setup_data()
else:
pass # -default, the user has started the application before and is not testing
# TODO: Where do we close the db connection? (Do we need to close it?)
# http://stackoverflow.com/questions/3850261/doing-something-before-program-exit
return Helper.__db_connection