Mercurial > libervia-backend
comparison 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 |
comparison
equal
deleted
inserted
replaced
| 4002:5245b675f7ad | 4003:1a77e1f866f9 |
|---|---|
| 154 msg += f":\nstdout: {log_out.decode()}\nstderr: {log_err.decode()}" | 154 msg += f":\nstdout: {log_out.decode()}\nstderr: {log_err.decode()}" |
| 155 log.error(msg) | 155 log.error(msg) |
| 156 | 156 |
| 157 raise exceptions.DatabaseError(msg) | 157 raise exceptions.DatabaseError(msg) |
| 158 | 158 |
| 159 async def createDB(self, engine: AsyncEngine, db_config: dict) -> None: | 159 async def create_db(self, engine: AsyncEngine, db_config: dict) -> None: |
| 160 """Create a new database | 160 """Create a new database |
| 161 | 161 |
| 162 The database is generated from SQLAlchemy model, then stamped by Alembic | 162 The database is generated from SQLAlchemy model, then stamped by Alembic |
| 163 """ | 163 """ |
| 164 # the dir may not exist if it's not the XDG recommended one | 164 # the dir may not exist if it's not the XDG recommended one |
| 168 | 168 |
| 169 log.debug("stamping the database") | 169 log.debug("stamping the database") |
| 170 await self.migrateApply("stamp", "head") | 170 await self.migrateApply("stamp", "head") |
| 171 log.debug("stamping done") | 171 log.debug("stamping done") |
| 172 | 172 |
| 173 def _checkDBIsUpToDate(self, conn: Connection) -> bool: | 173 def _check_db_is_up_to_date(self, conn: Connection) -> bool: |
| 174 al_ini_path = migration_path / "alembic.ini" | 174 al_ini_path = migration_path / "alembic.ini" |
| 175 al_cfg = al_config.Config(al_ini_path) | 175 al_cfg = al_config.Config(al_ini_path) |
| 176 directory = al_script.ScriptDirectory.from_config(al_cfg) | 176 directory = al_script.ScriptDirectory.from_config(al_cfg) |
| 177 context = al_migration.MigrationContext.configure(conn) | 177 context = al_migration.MigrationContext.configure(conn) |
| 178 return set(context.get_current_heads()) == set(directory.get_heads()) | 178 return set(context.get_current_heads()) == set(directory.get_heads()) |
| 179 | 179 |
| 180 async def checkAndUpdateDB(self, engine: AsyncEngine, db_config: dict) -> None: | 180 def _sqlite_set_journal_mode_wal(self, conn: Connection) -> None: |
| 181 """Check if journal mode is WAL, and set it if necesssary""" | |
| 182 result = conn.execute(text("PRAGMA journal_mode")) | |
| 183 if result.scalar() != "wal": | |
| 184 log.info("WAL mode not activated, activating it") | |
| 185 conn.execute(text("PRAGMA journal_mode=WAL")) | |
| 186 | |
| 187 async def check_and_update_db(self, engine: AsyncEngine, db_config: dict) -> None: | |
| 181 """Check that database is up-to-date, and update if necessary""" | 188 """Check that database is up-to-date, and update if necessary""" |
| 182 async with engine.connect() as conn: | 189 async with engine.connect() as conn: |
| 183 up_to_date = await conn.run_sync(self._checkDBIsUpToDate) | 190 up_to_date = await conn.run_sync(self._check_db_is_up_to_date) |
| 184 if up_to_date: | 191 if up_to_date: |
| 185 log.debug("Database is up-to-date") | 192 log.debug("Database is up-to-date") |
| 186 else: | 193 else: |
| 187 log.info("Database needs to be updated") | 194 log.info("Database needs to be updated") |
| 188 log.info("updating…") | 195 log.info("updating…") |
| 200 ) | 207 ) |
| 201 | 208 |
| 202 new_base = not db_config["path"].exists() | 209 new_base = not db_config["path"].exists() |
| 203 if new_base: | 210 if new_base: |
| 204 log.info(_("The database is new, creating the tables")) | 211 log.info(_("The database is new, creating the tables")) |
| 205 await self.createDB(engine, db_config) | 212 await self.create_db(engine, db_config) |
| 206 else: | 213 else: |
| 207 await self.checkAndUpdateDB(engine, db_config) | 214 await self.check_and_update_db(engine, db_config) |
| 215 | |
| 216 async with engine.connect() as conn: | |
| 217 await conn.run_sync(self._sqlite_set_journal_mode_wal) | |
| 208 | 218 |
| 209 self.session = sessionmaker( | 219 self.session = sessionmaker( |
| 210 engine, expire_on_commit=False, class_=AsyncSession | 220 engine, expire_on_commit=False, class_=AsyncSession |
| 211 ) | 221 ) |
| 212 | 222 |