Coverage for src/backend/InvenTree/InvenTree/setting/db_backend.py: 48%

1"""Configuration settings specific to a particular database backend.""" 

2 

3from pathlib import Path 

4 

5import structlog 

6 

7from InvenTree.config import get_boolean_setting, get_config_value, get_setting 

8from InvenTree.ready import isInWorkerThread 

9 

10logger = structlog.get_logger('inventree') 

11 

12 

13def _get_conn_max_age() -> int | None: 

14 """Return the configured CONN_MAX_AGE value. 

15 

16 Accepts an integer number of seconds, or 'None' for unlimited persistence. 

17 Defaults to 0 (close the connection after each request). 

18 """ 

19 value = get_setting('INVENTREE_DB_CONN_MAX_AGE', 'database.conn_max_age', 0) 

20 if value is None or str(value).strip().lower() == 'none': 20 ↛ 21line 20 didn't jump to line 21 because the condition on line 20 was never true

21 return None 

22 return int(value) 

23 

24 

25def get_db_backend(): 

26 """Return the database backend configuration.""" 

27 # For the core database configuration values, we test for UPPERCASE configuration values as a backup, 

28 # due to legacy reasons (original config files were uppercase, 

29 # but we moved to lowercase for consistency with other settings. 

30 

31 db_config = { 

32 'ENGINE': get_setting( 

33 'INVENTREE_DB_ENGINE', 'database.engine', '', typecast=str 

34 ) 

35 or get_config_value('database.ENGINE') 

36 or '', 

37 'NAME': get_setting('INVENTREE_DB_NAME', 'database.name', '', typecast=str) 

38 or get_config_value('database.NAME') 

39 or '', 

40 'USER': get_setting('INVENTREE_DB_USER', 'database.user', '', typecast=str) 

41 or get_config_value('database.USER') 

42 or '', 

43 'PASSWORD': get_setting( 

44 'INVENTREE_DB_PASSWORD', 'database.password', '', typecast=str 

45 ) 

46 or get_config_value('database.PASSWORD') 

47 or '', 

48 'HOST': get_setting('INVENTREE_DB_HOST', 'database.host', '', typecast=str) 

49 or get_config_value('database.HOST') 

50 or '', 

51 'PORT': get_setting('INVENTREE_DB_PORT', 'database.port', '', typecast=str) 

52 or get_config_value('database.PORT') 

53 or '', 

54 'OPTIONS': get_setting( 

55 'INVENTREE_DB_OPTIONS', 'database.options', {}, typecast=dict 

56 ) 

57 or get_config_value('database.OPTIONS') 

58 or {}, 

59 # Seconds to keep idle connections open across requests (0 = close after each request). 

60 # Set to None for unlimited. Enable CONN_HEALTH_CHECKS alongside any non-zero value 

61 # so stale connections are detected before use rather than causing request failures. 

62 'CONN_MAX_AGE': _get_conn_max_age(), 

63 'CONN_HEALTH_CHECKS': get_boolean_setting( 

64 'INVENTREE_DB_CONN_HEALTH_CHECKS', 'database.conn_health_checks', False 

65 ), 

66 } 

67 

68 # Check for required keys 

69 required_keys = ['ENGINE', 'NAME'] 

70 

71 for key in required_keys: 

72 if not db_config[key]: 72 ↛ 73line 72 didn't jump to line 73 because the condition on line 72 was never true

73 raise ValueError( 

74 f'Missing required database configuration key: INVENTREE_DB_{key}' 

75 ) 

76 

77 DB_ENGINE = db_config['ENGINE'].lower() 

78 

79 # Correct common misspelling 

80 if DB_ENGINE == 'sqlite': 80 ↛ 81line 80 didn't jump to line 81 because the condition on line 80 was never true

81 DB_ENGINE = 'sqlite3' # pragma: no cover 

82 

83 if DB_ENGINE in ['sqlite3', 'postgresql', 'mysql']: 83 ↛ 88line 83 didn't jump to line 88 because the condition on line 83 was always true

84 # Prepend the required python module string 

85 DB_ENGINE = f'django.db.backends.{DB_ENGINE}' 

86 db_config['ENGINE'] = DB_ENGINE 

87 

88 if 'sqlite' in DB_ENGINE: 88 ↛ 92line 88 didn't jump to line 92 because the condition on line 88 was always true

89 db_name = str(Path(db_config['NAME']).resolve()) 

90 db_config['NAME'] = db_name 

91 

92 logger.info('DB_ENGINE: %s', DB_ENGINE) 

93 logger.info('DB_NAME: %s', db_config['NAME']) 

94 logger.info('DB_HOST: %s', db_config.get('HOST', "''")) 

95 

96 # Set testing options for the database 

97 db_config['TEST'] = {'CHARSET': 'utf8'} 

98 

99 # Set collation option for mysql test database 

100 if 'mysql' in DB_ENGINE: 100 ↛ 101line 100 didn't jump to line 101 because the condition on line 100 was never true

101 db_config['TEST']['COLLATION'] = 'utf8_general_ci' # pragma: no cover 

102 

103 # Specify database specific configuration 

104 set_db_options(DB_ENGINE, db_config['OPTIONS']) 

105 

106 return db_config 

107 

108 

109def set_db_options(engine: str, db_options: dict): 

110 """Update database options based on the specified database backend. 

111 

112 Arguments: 

113 engine: The database engine (e.g. 'sqlite3', 'postgresql', etc.) 

114 db_options: The database options dictionary to update 

115 """ 

116 logger.debug('Setting database options: %s', engine) 

117 

118 if 'postgres' in engine: 118 ↛ 119line 118 didn't jump to line 119 because the condition on line 118 was never true

119 set_postgres_options(db_options) 

120 elif 'mysql' in engine: 120 ↛ 121line 120 didn't jump to line 121 because the condition on line 120 was never true

121 set_mysql_options(db_options) 

122 elif 'sqlite' in engine: 122 ↛ 125line 122 didn't jump to line 125 because the condition on line 122 was always true

123 set_sqlite_options(db_options) 

124 else: 

125 raise ValueError(f'Unknown database engine: {engine}') 

126 

127 

128def set_postgres_options(db_options: dict): 

129 """Set database options specific to postgres backend.""" 

130 from django.db.backends.postgresql.psycopg_any import IsolationLevel 

131 

132 # Connection timeout 

133 if 'connect_timeout' not in db_options: 

134 # The DB server is in the same data center, it should not take very 

135 # long to connect to the database server 

136 # Note: 2 seconds is minimum allowed by libpq 

137 db_options['connect_timeout'] = max( 

138 2, int(get_setting('INVENTREE_DB_TIMEOUT', 'database.timeout', 10)) 

139 ) 

140 

141 # Setup TCP keepalive 

142 # DB server is in the same DC, it should not become unresponsive for 

143 # very long. With the defaults below we wait 5 seconds for the network 

144 # issue to resolve itself. If that doesn't happen, whatever happened 

145 # is probably fatal and no amount of waiting is going to fix it. 

146 # # 0 - TCP Keepalives disabled; 1 - enabled 

147 if 'keepalives' not in db_options: 

148 db_options['keepalives'] = int( 

149 get_setting('INVENTREE_DB_TCP_KEEPALIVES', 'database.tcp_keepalives', 1) 

150 ) 

151 

152 # Seconds after connection is idle to send keep alive 

153 if 'keepalives_idle' not in db_options: 

154 db_options['keepalives_idle'] = int( 

155 get_setting( 

156 'INVENTREE_DB_TCP_KEEPALIVES_IDLE', 'database.tcp_keepalives_idle', 5 

157 ) 

158 ) 

159 

160 # Seconds after missing ACK to send another keep alive 

161 if 'keepalives_interval' not in db_options: 

162 db_options['keepalives_interval'] = int( 

163 get_setting( 

164 'INVENTREE_DB_TCP_KEEPALIVES_INTERVAL', 

165 'database.tcp_keepalives_interval', 

166 '5', 

167 ) 

168 ) 

169 

170 # Number of missing ACKs before we close the connection 

171 if 'keepalives_count' not in db_options: 

172 db_options['keepalives_count'] = int( 

173 get_setting( 

174 'INVENTREE_DB_TCP_KEEPALIVES_COUNT', 

175 'database.tcp_keepalives_count', 

176 '5', 

177 ) 

178 ) 

179 

180 # # Milliseconds for how long pending data should remain unacked by the remote server 

181 if 'tcp_user_timeout' not in db_options: 

182 db_options['tcp_user_timeout'] = int( 

183 get_setting( 

184 'INVENTREE_DB_TCP_USER_TIMEOUT', 'database.tcp_user_timeout', '2000' 

185 ) 

186 ) 

187 

188 # Postgres's default isolation level is Read Committed which is 

189 # normally fine, but most developers think the database server is 

190 # actually going to do Serializable type checks on the queries to 

191 # protect against simultaneous changes. 

192 # https://www.postgresql.org/docs/devel/transaction-iso.html 

193 # https://docs.djangoproject.com/en/3.2/ref/databases/#isolation-level 

194 if 'isolation_level' not in db_options: 

195 serializable = get_boolean_setting( 

196 'INVENTREE_DB_ISOLATION_SERIALIZABLE', 'database.serializable', False 

197 ) 

198 db_options['isolation_level'] = ( 

199 IsolationLevel.SERIALIZABLE 

200 if serializable 

201 else IsolationLevel.READ_COMMITTED 

202 ) 

203 

204 # Specify the application name for the database connection 

205 # This can be useful for debugging and monitoring purposes 

206 if 'application_name' not in db_options: 

207 db_options['application_name'] = ( 

208 'inventree-worker' if isInWorkerThread() else 'inventree-server' 

209 ) 

210 

211 

212def set_mysql_options(db_options: dict): 

213 """Set database options specific to mysql backend.""" 

214 # Timeout values 

215 if 'connect_timeout' not in db_options: 

216 db_options['connect_timeout'] = int( 

217 get_setting('INVENTREE_DB_TIMEOUT', 'database.timeout', 10) 

218 ) 

219 

220 # MariaDB's default isolation level is Repeatable Read which is 

221 # normally fine, but most developers think the database server is 

222 # actually going to Serializable type checks on the queries to 

223 # protect against simultaneous changes. 

224 # https://mariadb.com/kb/en/mariadb-transactions-and-isolation-levels-for-sql-server-users/#changing-the-isolation-level 

225 # https://docs.djangoproject.com/en/3.2/ref/databases/#mysql-isolation-level 

226 if 'isolation_level' not in db_options: 

227 serializable = get_boolean_setting( 

228 'INVENTREE_DB_ISOLATION_SERIALIZABLE', 'database.serializable', False 

229 ) 

230 db_options['isolation_level'] = ( 

231 'serializable' if serializable else 'read committed' 

232 ) 

233 

234 

235def set_sqlite_options(db_options: dict): 

236 """Set database options specific to sqlite backend. 

237 

238 References: 

239 - https://docs.djangoproject.com/en/5.0/ref/databases/#sqlite-notes 

240 - https://docs.djangoproject.com/en/6.0/ref/databases/#database-is-locked-errors 

241 """ 

242 import InvenTree.ready 

243 

244 # Specify minimum timeout behavior for SQLite connections 

245 if 'timeout' not in db_options: 245 ↛ 253line 245 didn't jump to line 253 because the condition on line 245 was always true

246 db_options['timeout'] = int( 

247 get_setting('INVENTREE_DB_TIMEOUT', 'database.timeout', 10) 

248 ) 

249 

250 # Specify the transaction mode for the database 

251 # For the backend worker thread, IMMEDIATE mode is used, 

252 # it has been determined to provide better protection against database locks in the worker thread 

253 db_options['transaction_mode'] = ( 

254 'IMMEDIATE' if InvenTree.ready.isInWorkerThread() else 'DEFERRED' 

255 ) 

256 

257 # SQLite's default isolation level is Serializable due to SQLite's 

258 # single writer implementation. Presumably as a result of this, it is 

259 # not possible to implement any lower isolation levels in SQLite. 

260 # https://www.sqlite.org/isolation.html 

261 

262 if get_boolean_setting('INVENTREE_DB_WAL_MODE', 'database.wal_mode', True): 262 ↛ exitline 262 didn't return from function 'set_sqlite_options' because the condition on line 262 was always true

263 # Specify that we want to use Write-Ahead Logging (WAL) mode for SQLite databases, as this allows for better concurrency and performance 

264 db_options['init_command'] = 'PRAGMA journal_mode=WAL;'