Mercurial > hg > orthanc-databases

comparison Odbc/Plugins/OdbcIndex.cpp @ 366:cd9521e04249 attach-custom-data

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
DatabaseBackendAdapterV4: added support for customData + revision when not already done
author Alain Mazy <am@osimis.io>
date Thu, 15 Sep 2022 18:12:34 +0200
parents b5fb8b77ce4d
children 82f73188b58d
comparison
equal deleted inserted replaced
365:7671fa7f099e 366:cd9521e04249
158 IDatabaseFactory* OdbcIndex::CreateDatabaseFactory() 158 IDatabaseFactory* OdbcIndex::CreateDatabaseFactory()
159 { 159 {
160 return OdbcDatabase::CreateDatabaseFactory(maxConnectionRetries_, connectionRetryInterval_, connectionString_, true); 160 return OdbcDatabase::CreateDatabaseFactory(maxConnectionRetries_, connectionRetryInterval_, connectionString_, true);
161 } 161 }
162 162
163 static void AdaptTypesToDialect(std::string& sql, Dialect dialect)
164 {
165 switch (dialect)
166 {
167 case Dialect_SQLite:
168 boost::replace_all(sql, "${LONGTEXT}", "TEXT");
169 boost::replace_all(sql, "${AUTOINCREMENT_TYPE}", "INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT");
170 boost::replace_all(sql, "${AUTOINCREMENT_INSERT}", "NULL, ");
171 break;
172
173 case Dialect_PostgreSQL:
174 boost::replace_all(sql, "${LONGTEXT}", "TEXT");
175 boost::replace_all(sql, "${AUTOINCREMENT_TYPE}", "BIGSERIAL NOT NULL PRIMARY KEY");
176 boost::replace_all(sql, "${AUTOINCREMENT_INSERT}", "DEFAULT, ");
177 break;
178
179 case Dialect_MySQL:
180 boost::replace_all(sql, "${LONGTEXT}", "LONGTEXT");
181 boost::replace_all(sql, "${AUTOINCREMENT_TYPE}", "BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY");
182 boost::replace_all(sql, "${AUTOINCREMENT_INSERT}", "NULL, ");
183 break;
184
185 case Dialect_MSSQL:
186 /**
187 * cf. OMSSQL-5: Use VARCHAR(MAX) instead of TEXT: (1)
188 * Microsoft issued a warning stating that "ntext, text, and
189 * image data types will be removed in a future version of
190 * SQL Server"
191 * (https://msdn.microsoft.com/en-us/library/ms187993.aspx),
192 * and (2) SQL Server does not support comparison of TEXT
193 * with '=' operator (e.g. in WHERE statements such as
194 * IndexBackend::LookupIdentifier())."
195 **/
196 boost::replace_all(sql, "${LONGTEXT}", "VARCHAR(MAX)");
197 boost::replace_all(sql, "${AUTOINCREMENT_TYPE}", "BIGINT IDENTITY NOT NULL PRIMARY KEY");
198 boost::replace_all(sql, "${AUTOINCREMENT_INSERT}", "");
199 break;
200
201 default:
202 throw Orthanc::OrthancException(Orthanc::ErrorCode_NotImplemented);
203 }
204 }
205
163 206
164 void OdbcIndex::ConfigureDatabase(DatabaseManager& manager) 207 void OdbcIndex::ConfigureDatabase(DatabaseManager& manager)
165 { 208 {
166 uint32_t expectedVersion = 6; 209 uint32_t expectedVersion = 6;
167 210
183 226
184 if (!db.DoesTableExist("resources")) 227 if (!db.DoesTableExist("resources"))
185 { 228 {
186 std::string sql; 229 std::string sql;
187 Orthanc::EmbeddedResources::GetFileResource(sql, Orthanc::EmbeddedResources::ODBC_PREPARE_INDEX); 230 Orthanc::EmbeddedResources::GetFileResource(sql, Orthanc::EmbeddedResources::ODBC_PREPARE_INDEX);
188 231
189 switch (db.GetDialect()) 232 AdaptTypesToDialect(sql, db.GetDialect());
190 {
191 case Dialect_SQLite:
192 boost::replace_all(sql, "${LONGTEXT}", "TEXT");
193 boost::replace_all(sql, "${AUTOINCREMENT_TYPE}", "INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT");
194 boost::replace_all(sql, "${AUTOINCREMENT_INSERT}", "NULL, ");
195 break;
196
197 case Dialect_PostgreSQL:
198 boost::replace_all(sql, "${LONGTEXT}", "TEXT");
199 boost::replace_all(sql, "${AUTOINCREMENT_TYPE}", "BIGSERIAL NOT NULL PRIMARY KEY");
200 boost::replace_all(sql, "${AUTOINCREMENT_INSERT}", "DEFAULT, ");
201 break;
202
203 case Dialect_MySQL:
204 boost::replace_all(sql, "${LONGTEXT}", "LONGTEXT");
205 boost::replace_all(sql, "${AUTOINCREMENT_TYPE}", "BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY");
206 boost::replace_all(sql, "${AUTOINCREMENT_INSERT}", "NULL, ");
207 break;
208
209 case Dialect_MSSQL:
210 /**
211 * cf. OMSSQL-5: Use VARCHAR(MAX) instead of TEXT: (1)
212 * Microsoft issued a warning stating that "ntext, text, and
213 * image data types will be removed in a future version of
214 * SQL Server"
215 * (https://msdn.microsoft.com/en-us/library/ms187993.aspx),
216 * and (2) SQL Server does not support comparison of TEXT
217 * with '=' operator (e.g. in WHERE statements such as
218 * IndexBackend::LookupIdentifier())."
219 **/
220 boost::replace_all(sql, "${LONGTEXT}", "VARCHAR(MAX)");
221 boost::replace_all(sql, "${AUTOINCREMENT_TYPE}", "BIGINT IDENTITY NOT NULL PRIMARY KEY");
222 boost::replace_all(sql, "${AUTOINCREMENT_INSERT}", "");
223 break;
224
225 default:
226 throw Orthanc::OrthancException(Orthanc::ErrorCode_NotImplemented);
227 }
228 233
229 { 234 {
230 DatabaseManager::Transaction t(manager, TransactionType_ReadWrite); 235 DatabaseManager::Transaction t(manager, TransactionType_ReadWrite);
231 236
232 db.ExecuteMultiLines(sql); 237 db.ExecuteMultiLines(sql);
234 if (db.GetDialect() == Dialect_MySQL) 239 if (db.GetDialect() == Dialect_MySQL)
235 { 240 {
236 // Switch to the collation that is the default since MySQL 241 // Switch to the collation that is the default since MySQL
237 // 8.0.1. This must be *after* the creation of the tables. 242 // 8.0.1. This must be *after* the creation of the tables.
238 db.ExecuteMultiLines("ALTER DATABASE CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci"); 243 db.ExecuteMultiLines("ALTER DATABASE CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci");
244 }
245
246 { // v 4.X: add customData
247 int patchLevel;
248
249 if (!LookupGlobalIntegerProperty(patchLevel, manager, MISSING_SERVER_IDENTIFIER, Orthanc::GlobalProperty_DatabasePatchLevel))
250 {
251 std::string sqlAddCustomData = "ALTER TABLE AttachedFiles ADD customData ${LONGTEXT};"
252 "ALTER TABLE DeletedFiles ADD customData ${LONGTEXT}";
253
254 AdaptTypesToDialect(sqlAddCustomData, db.GetDialect());
255
256 db.ExecuteMultiLines(sqlAddCustomData);
257
258 SetGlobalIntegerProperty(manager, MISSING_SERVER_IDENTIFIER, Orthanc::GlobalProperty_DatabasePatchLevel, 1);
259 }
239 } 260 }
240 261
241 t.Commit(); 262 t.Commit();
242 } 263 }
243 } 264 }