diff sat/memory/sqla.py @ 4003:1a77e1f866f9

core (memory/sqla): activate Write-Ahead Logging: Write-Ahead Logging (WAL) journal mode for SQLite (see https://www.sqlite.org/wal.html). This should improve performances.
author Goffi <goffi@goffi.org>
date Fri, 10 Mar 2023 17:22:45 +0100
parents 045af0eeda3f
children 524856bd7b19
line wrap: on
line diff
--- a/sat/memory/sqla.py	Fri Mar 10 17:22:45 2023 +0100
+++ b/sat/memory/sqla.py	Fri Mar 10 17:22:45 2023 +0100
@@ -156,7 +156,7 @@
 
             raise exceptions.DatabaseError(msg)
 
-    async def createDB(self, engine: AsyncEngine, db_config: dict) -> None:
+    async def create_db(self, engine: AsyncEngine, db_config: dict) -> None:
         """Create a new database
 
         The database is generated from SQLAlchemy model, then stamped by Alembic
@@ -170,17 +170,24 @@
         await self.migrateApply("stamp", "head")
         log.debug("stamping done")
 
-    def _checkDBIsUpToDate(self, conn: Connection) -> bool:
+    def _check_db_is_up_to_date(self, conn: Connection) -> bool:
         al_ini_path = migration_path / "alembic.ini"
         al_cfg = al_config.Config(al_ini_path)
         directory = al_script.ScriptDirectory.from_config(al_cfg)
         context = al_migration.MigrationContext.configure(conn)
         return set(context.get_current_heads()) == set(directory.get_heads())
 
-    async def checkAndUpdateDB(self, engine: AsyncEngine, db_config: dict) -> None:
+    def _sqlite_set_journal_mode_wal(self, conn: Connection) -> None:
+        """Check if journal mode is WAL, and set it if necesssary"""
+        result = conn.execute(text("PRAGMA journal_mode"))
+        if result.scalar() != "wal":
+            log.info("WAL mode not activated, activating it")
+            conn.execute(text("PRAGMA journal_mode=WAL"))
+
+    async def check_and_update_db(self, engine: AsyncEngine, db_config: dict) -> None:
         """Check that database is up-to-date, and update if necessary"""
         async with engine.connect() as conn:
-            up_to_date = await conn.run_sync(self._checkDBIsUpToDate)
+            up_to_date = await conn.run_sync(self._check_db_is_up_to_date)
         if up_to_date:
             log.debug("Database is up-to-date")
         else:
@@ -202,9 +209,12 @@
         new_base = not db_config["path"].exists()
         if new_base:
             log.info(_("The database is new, creating the tables"))
-            await self.createDB(engine, db_config)
+            await self.create_db(engine, db_config)
         else:
-            await self.checkAndUpdateDB(engine, db_config)
+            await self.check_and_update_db(engine, db_config)
+
+        async with engine.connect() as conn:
+            await conn.run_sync(self._sqlite_set_journal_mode_wal)
 
         self.session = sessionmaker(
             engine, expire_on_commit=False, class_=AsyncSession