1 // Copyright (c) 2018, ETH Zurich and UNC Chapel Hill.
2 // All rights reserved.
3 //
4 // Redistribution and use in source and binary forms, with or without
5 // modification, are permitted provided that the following conditions are met:
6 //
7 // * Redistributions of source code must retain the above copyright
8 // notice, this list of conditions and the following disclaimer.
9 //
10 // * Redistributions in binary form must reproduce the above copyright
11 // notice, this list of conditions and the following disclaimer in the
12 // documentation and/or other materials provided with the distribution.
13 //
14 // * Neither the name of ETH Zurich and UNC Chapel Hill nor the names of
15 // its contributors may be used to endorse or promote products derived
16 // from this software without specific prior written permission.
17 //
18 // THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
19 // AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
20 // IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
21 // ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT HOLDERS OR CONTRIBUTORS BE
22 // LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR
23 // CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF
24 // SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS
25 // INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN
26 // CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE)
27 // ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE
28 // POSSIBILITY OF SUCH DAMAGE.
29 //
30 // Author: Johannes L. Schoenberger (jsch-at-demuc-dot-de)
31
32 #include "base/database.h"
33
34 #include <fstream>
35
36 #include "util/sqlite3_utils.h"
37 #include "util/string.h"
38 #include "util/version.h"
39
40 namespace colmap {
41 namespace {
42
43 typedef Eigen::Matrix<float, Eigen::Dynamic, Eigen::Dynamic, Eigen::RowMajor>
44 FeatureKeypointsBlob;
45 typedef Eigen::Matrix<uint8_t, Eigen::Dynamic, Eigen::Dynamic, Eigen::RowMajor>
46 FeatureDescriptorsBlob;
47 typedef Eigen::Matrix<point2D_t, Eigen::Dynamic, 2, Eigen::RowMajor>
48 FeatureMatchesBlob;
49
SwapFeatureMatchesBlob(FeatureMatchesBlob * matches)50 void SwapFeatureMatchesBlob(FeatureMatchesBlob* matches) {
51 matches->col(0).swap(matches->col(1));
52 }
53
FeatureKeypointsToBlob(const FeatureKeypoints & keypoints)54 FeatureKeypointsBlob FeatureKeypointsToBlob(const FeatureKeypoints& keypoints) {
55 const FeatureKeypointsBlob::Index kNumCols = 6;
56 FeatureKeypointsBlob blob(keypoints.size(), kNumCols);
57 for (size_t i = 0; i < keypoints.size(); ++i) {
58 blob(i, 0) = keypoints[i].x;
59 blob(i, 1) = keypoints[i].y;
60 blob(i, 2) = keypoints[i].a11;
61 blob(i, 3) = keypoints[i].a12;
62 blob(i, 4) = keypoints[i].a21;
63 blob(i, 5) = keypoints[i].a22;
64 }
65 return blob;
66 }
67
FeatureKeypointsFromBlob(const FeatureKeypointsBlob & blob)68 FeatureKeypoints FeatureKeypointsFromBlob(const FeatureKeypointsBlob& blob) {
69 FeatureKeypoints keypoints(static_cast<size_t>(blob.rows()));
70 if (blob.cols() == 2) {
71 for (FeatureKeypointsBlob::Index i = 0; i < blob.rows(); ++i) {
72 keypoints[i] = FeatureKeypoint(blob(i, 0), blob(i, 1));
73 }
74 } else if (blob.cols() == 4) {
75 for (FeatureKeypointsBlob::Index i = 0; i < blob.rows(); ++i) {
76 keypoints[i] =
77 FeatureKeypoint(blob(i, 0), blob(i, 1), blob(i, 2), blob(i, 3));
78 }
79 } else if (blob.cols() == 6) {
80 for (FeatureKeypointsBlob::Index i = 0; i < blob.rows(); ++i) {
81 keypoints[i] = FeatureKeypoint(blob(i, 0), blob(i, 1), blob(i, 2),
82 blob(i, 3), blob(i, 4), blob(i, 5));
83 }
84 } else {
85 LOG(FATAL) << "Keypoint format not supported";
86 }
87 return keypoints;
88 }
89
FeatureMatchesToBlob(const FeatureMatches & matches)90 FeatureMatchesBlob FeatureMatchesToBlob(const FeatureMatches& matches) {
91 const FeatureMatchesBlob::Index kNumCols = 2;
92 FeatureMatchesBlob blob(matches.size(), kNumCols);
93 for (size_t i = 0; i < matches.size(); ++i) {
94 blob(i, 0) = matches[i].point2D_idx1;
95 blob(i, 1) = matches[i].point2D_idx2;
96 }
97 return blob;
98 }
99
FeatureMatchesFromBlob(const FeatureMatchesBlob & blob)100 FeatureMatches FeatureMatchesFromBlob(const FeatureMatchesBlob& blob) {
101 CHECK_EQ(blob.cols(), 2);
102 FeatureMatches matches(static_cast<size_t>(blob.rows()));
103 for (FeatureMatchesBlob::Index i = 0; i < blob.rows(); ++i) {
104 matches[i].point2D_idx1 = blob(i, 0);
105 matches[i].point2D_idx2 = blob(i, 1);
106 }
107 return matches;
108 }
109
110 template <typename MatrixType>
ReadStaticMatrixBlob(sqlite3_stmt * sql_stmt,const int rc,const int col)111 MatrixType ReadStaticMatrixBlob(sqlite3_stmt* sql_stmt, const int rc,
112 const int col) {
113 CHECK_GE(col, 0);
114
115 MatrixType matrix;
116
117 if (rc == SQLITE_ROW) {
118 const size_t num_bytes =
119 static_cast<size_t>(sqlite3_column_bytes(sql_stmt, col));
120 if (num_bytes > 0) {
121 CHECK_EQ(num_bytes, matrix.size() * sizeof(typename MatrixType::Scalar));
122 memcpy(reinterpret_cast<char*>(matrix.data()),
123 sqlite3_column_blob(sql_stmt, col), num_bytes);
124 } else {
125 matrix = MatrixType::Zero();
126 }
127 } else {
128 matrix = MatrixType::Zero();
129 }
130
131 return matrix;
132 }
133
134 template <typename MatrixType>
ReadDynamicMatrixBlob(sqlite3_stmt * sql_stmt,const int rc,const int col)135 MatrixType ReadDynamicMatrixBlob(sqlite3_stmt* sql_stmt, const int rc,
136 const int col) {
137 CHECK_GE(col, 0);
138
139 MatrixType matrix;
140
141 if (rc == SQLITE_ROW) {
142 const size_t rows =
143 static_cast<size_t>(sqlite3_column_int64(sql_stmt, col + 0));
144 const size_t cols =
145 static_cast<size_t>(sqlite3_column_int64(sql_stmt, col + 1));
146
147 CHECK_GE(rows, 0);
148 CHECK_GE(cols, 0);
149 matrix = MatrixType(rows, cols);
150
151 const size_t num_bytes =
152 static_cast<size_t>(sqlite3_column_bytes(sql_stmt, col + 2));
153 CHECK_EQ(matrix.size() * sizeof(typename MatrixType::Scalar), num_bytes);
154
155 memcpy(reinterpret_cast<char*>(matrix.data()),
156 sqlite3_column_blob(sql_stmt, col + 2), num_bytes);
157 } else {
158 const typename MatrixType::Index rows =
159 (MatrixType::RowsAtCompileTime == Eigen::Dynamic)
160 ? 0
161 : MatrixType::RowsAtCompileTime;
162 const typename MatrixType::Index cols =
163 (MatrixType::ColsAtCompileTime == Eigen::Dynamic)
164 ? 0
165 : MatrixType::ColsAtCompileTime;
166 matrix = MatrixType(rows, cols);
167 }
168
169 return matrix;
170 }
171
172 template <typename MatrixType>
WriteStaticMatrixBlob(sqlite3_stmt * sql_stmt,const MatrixType & matrix,const int col)173 void WriteStaticMatrixBlob(sqlite3_stmt* sql_stmt, const MatrixType& matrix,
174 const int col) {
175 SQLITE3_CALL(sqlite3_bind_blob(
176 sql_stmt, col, reinterpret_cast<const char*>(matrix.data()),
177 static_cast<int>(matrix.size() * sizeof(typename MatrixType::Scalar)),
178 SQLITE_STATIC));
179 }
180
181 template <typename MatrixType>
WriteDynamicMatrixBlob(sqlite3_stmt * sql_stmt,const MatrixType & matrix,const int col)182 void WriteDynamicMatrixBlob(sqlite3_stmt* sql_stmt, const MatrixType& matrix,
183 const int col) {
184 CHECK_GE(matrix.rows(), 0);
185 CHECK_GE(matrix.cols(), 0);
186 CHECK_GE(col, 0);
187
188 const size_t num_bytes = matrix.size() * sizeof(typename MatrixType::Scalar);
189 SQLITE3_CALL(sqlite3_bind_int64(sql_stmt, col + 0, matrix.rows()));
190 SQLITE3_CALL(sqlite3_bind_int64(sql_stmt, col + 1, matrix.cols()));
191 SQLITE3_CALL(sqlite3_bind_blob(sql_stmt, col + 2,
192 reinterpret_cast<const char*>(matrix.data()),
193 static_cast<int>(num_bytes), SQLITE_STATIC));
194 }
195
ReadCameraRow(sqlite3_stmt * sql_stmt)196 Camera ReadCameraRow(sqlite3_stmt* sql_stmt) {
197 Camera camera;
198
199 camera.SetCameraId(static_cast<camera_t>(sqlite3_column_int64(sql_stmt, 0)));
200 camera.SetModelId(sqlite3_column_int64(sql_stmt, 1));
201 camera.SetWidth(static_cast<size_t>(sqlite3_column_int64(sql_stmt, 2)));
202 camera.SetHeight(static_cast<size_t>(sqlite3_column_int64(sql_stmt, 3)));
203
204 const size_t num_params_bytes =
205 static_cast<size_t>(sqlite3_column_bytes(sql_stmt, 4));
206 const size_t num_params = num_params_bytes / sizeof(double);
207 CHECK_EQ(num_params, camera.NumParams());
208 memcpy(camera.ParamsData(), sqlite3_column_blob(sql_stmt, 4),
209 num_params_bytes);
210
211 camera.SetPriorFocalLength(sqlite3_column_int64(sql_stmt, 5) != 0);
212
213 return camera;
214 }
215
ReadImageRow(sqlite3_stmt * sql_stmt)216 Image ReadImageRow(sqlite3_stmt* sql_stmt) {
217 Image image;
218
219 image.SetImageId(static_cast<image_t>(sqlite3_column_int64(sql_stmt, 0)));
220 image.SetName(std::string(
221 reinterpret_cast<const char*>(sqlite3_column_text(sql_stmt, 1))));
222 image.SetCameraId(static_cast<camera_t>(sqlite3_column_int64(sql_stmt, 2)));
223
224 // NaNs are automatically converted to NULLs in SQLite.
225 for (size_t i = 0; i < 4; ++i) {
226 if (sqlite3_column_type(sql_stmt, i + 3) != SQLITE_NULL) {
227 image.QvecPrior(i) = sqlite3_column_double(sql_stmt, i + 3);
228 }
229 }
230
231 // NaNs are automatically converted to NULLs in SQLite.
232 for (size_t i = 0; i < 3; ++i) {
233 if (sqlite3_column_type(sql_stmt, i + 7) != SQLITE_NULL) {
234 image.TvecPrior(i) = sqlite3_column_double(sql_stmt, i + 7);
235 }
236 }
237
238 return image;
239 }
240
241 } // namespace
242
243 const size_t Database::kMaxNumImages =
244 static_cast<size_t>(std::numeric_limits<int32_t>::max());
245
246 std::mutex Database::update_schema_mutex_;
247
Database()248 Database::Database() : database_(nullptr) {}
249
Database(const std::string & path)250 Database::Database(const std::string& path) : Database() { Open(path); }
251
~Database()252 Database::~Database() { Close(); }
253
Open(const std::string & path)254 void Database::Open(const std::string& path) {
255 Close();
256
257 // SQLITE_OPEN_NOMUTEX specifies that the connection should not have a
258 // mutex (so that we don't serialize the connection's operations).
259 // Modifications to the database will still be serialized, but multiple
260 // connections can read concurrently.
261 SQLITE3_CALL(sqlite3_open_v2(
262 path.c_str(), &database_,
263 SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE | SQLITE_OPEN_NOMUTEX,
264 nullptr));
265
266 // Don't wait for the operating system to write the changes to disk
267 SQLITE3_EXEC(database_, "PRAGMA synchronous=OFF", nullptr);
268
269 // Use faster journaling mode
270 SQLITE3_EXEC(database_, "PRAGMA journal_mode=WAL", nullptr);
271
272 // Store temporary tables and indices in memory
273 SQLITE3_EXEC(database_, "PRAGMA temp_store=MEMORY", nullptr);
274
275 // Disabled by default
276 SQLITE3_EXEC(database_, "PRAGMA foreign_keys=ON", nullptr);
277
278 CreateTables();
279 UpdateSchema();
280 PrepareSQLStatements();
281 }
282
Close()283 void Database::Close() {
284 if (database_ != nullptr) {
285 FinalizeSQLStatements();
286 sqlite3_close_v2(database_);
287 database_ = nullptr;
288 }
289 }
290
ExistsCamera(const camera_t camera_id) const291 bool Database::ExistsCamera(const camera_t camera_id) const {
292 return ExistsRowId(sql_stmt_exists_camera_, camera_id);
293 }
294
ExistsImage(const image_t image_id) const295 bool Database::ExistsImage(const image_t image_id) const {
296 return ExistsRowId(sql_stmt_exists_image_id_, image_id);
297 }
298
ExistsImageWithName(std::string name) const299 bool Database::ExistsImageWithName(std::string name) const {
300 return ExistsRowString(sql_stmt_exists_image_name_, name);
301 }
302
ExistsKeypoints(const image_t image_id) const303 bool Database::ExistsKeypoints(const image_t image_id) const {
304 return ExistsRowId(sql_stmt_exists_keypoints_, image_id);
305 }
306
ExistsDescriptors(const image_t image_id) const307 bool Database::ExistsDescriptors(const image_t image_id) const {
308 return ExistsRowId(sql_stmt_exists_descriptors_, image_id);
309 }
310
ExistsMatches(const image_t image_id1,const image_t image_id2) const311 bool Database::ExistsMatches(const image_t image_id1,
312 const image_t image_id2) const {
313 return ExistsRowId(sql_stmt_exists_matches_,
314 ImagePairToPairId(image_id1, image_id2));
315 }
316
ExistsInlierMatches(const image_t image_id1,const image_t image_id2) const317 bool Database::ExistsInlierMatches(const image_t image_id1,
318 const image_t image_id2) const {
319 return ExistsRowId(sql_stmt_exists_two_view_geometry_,
320 ImagePairToPairId(image_id1, image_id2));
321 }
322
NumCameras() const323 size_t Database::NumCameras() const { return CountRows("cameras"); }
324
NumImages() const325 size_t Database::NumImages() const { return CountRows("images"); }
326
NumKeypoints() const327 size_t Database::NumKeypoints() const { return SumColumn("rows", "keypoints"); }
328
MaxNumKeypoints() const329 size_t Database::MaxNumKeypoints() const {
330 return MaxColumn("rows", "keypoints");
331 }
332
NumKeypointsForImage(const image_t image_id) const333 size_t Database::NumKeypointsForImage(const image_t image_id) const {
334 return CountRowsForEntry(sql_stmt_num_keypoints_, image_id);
335 }
336
NumDescriptors() const337 size_t Database::NumDescriptors() const {
338 return SumColumn("rows", "descriptors");
339 }
340
MaxNumDescriptors() const341 size_t Database::MaxNumDescriptors() const {
342 return MaxColumn("rows", "descriptors");
343 }
344
NumDescriptorsForImage(const image_t image_id) const345 size_t Database::NumDescriptorsForImage(const image_t image_id) const {
346 return CountRowsForEntry(sql_stmt_num_descriptors_, image_id);
347 }
348
NumMatches() const349 size_t Database::NumMatches() const { return SumColumn("rows", "matches"); }
350
NumInlierMatches() const351 size_t Database::NumInlierMatches() const {
352 return SumColumn("rows", "two_view_geometries");
353 }
354
NumMatchedImagePairs() const355 size_t Database::NumMatchedImagePairs() const { return CountRows("matches"); }
356
NumVerifiedImagePairs() const357 size_t Database::NumVerifiedImagePairs() const {
358 return CountRows("two_view_geometries");
359 }
360
ReadCamera(const camera_t camera_id) const361 Camera Database::ReadCamera(const camera_t camera_id) const {
362 SQLITE3_CALL(sqlite3_bind_int64(sql_stmt_read_camera_, 1, camera_id));
363
364 Camera camera;
365
366 const int rc = SQLITE3_CALL(sqlite3_step(sql_stmt_read_camera_));
367 if (rc == SQLITE_ROW) {
368 camera = ReadCameraRow(sql_stmt_read_camera_);
369 }
370
371 SQLITE3_CALL(sqlite3_reset(sql_stmt_read_camera_));
372
373 return camera;
374 }
375
ReadAllCameras() const376 std::vector<Camera> Database::ReadAllCameras() const {
377 std::vector<Camera> cameras;
378
379 while (SQLITE3_CALL(sqlite3_step(sql_stmt_read_cameras_)) == SQLITE_ROW) {
380 cameras.push_back(ReadCameraRow(sql_stmt_read_cameras_));
381 }
382
383 SQLITE3_CALL(sqlite3_reset(sql_stmt_read_cameras_));
384
385 return cameras;
386 }
387
ReadImage(const image_t image_id) const388 Image Database::ReadImage(const image_t image_id) const {
389 SQLITE3_CALL(sqlite3_bind_int64(sql_stmt_read_image_id_, 1, image_id));
390
391 Image image;
392
393 const int rc = SQLITE3_CALL(sqlite3_step(sql_stmt_read_image_id_));
394 if (rc == SQLITE_ROW) {
395 image = ReadImageRow(sql_stmt_read_image_id_);
396 }
397
398 SQLITE3_CALL(sqlite3_reset(sql_stmt_read_image_id_));
399
400 return image;
401 }
402
ReadImageWithName(const std::string & name) const403 Image Database::ReadImageWithName(const std::string& name) const {
404 SQLITE3_CALL(sqlite3_bind_text(sql_stmt_read_image_name_, 1, name.c_str(),
405 static_cast<int>(name.size()), SQLITE_STATIC));
406
407 Image image;
408
409 const int rc = SQLITE3_CALL(sqlite3_step(sql_stmt_read_image_name_));
410 if (rc == SQLITE_ROW) {
411 image = ReadImageRow(sql_stmt_read_image_name_);
412 }
413
414 SQLITE3_CALL(sqlite3_reset(sql_stmt_read_image_name_));
415
416 return image;
417 }
418
ReadAllImages() const419 std::vector<Image> Database::ReadAllImages() const {
420 std::vector<Image> images;
421 images.reserve(NumImages());
422
423 while (SQLITE3_CALL(sqlite3_step(sql_stmt_read_images_)) == SQLITE_ROW) {
424 images.push_back(ReadImageRow(sql_stmt_read_images_));
425 }
426
427 SQLITE3_CALL(sqlite3_reset(sql_stmt_read_images_));
428
429 return images;
430 }
431
ReadKeypoints(const image_t image_id) const432 FeatureKeypoints Database::ReadKeypoints(const image_t image_id) const {
433 SQLITE3_CALL(sqlite3_bind_int64(sql_stmt_read_keypoints_, 1, image_id));
434
435 const int rc = SQLITE3_CALL(sqlite3_step(sql_stmt_read_keypoints_));
436 const FeatureKeypointsBlob blob = ReadDynamicMatrixBlob<FeatureKeypointsBlob>(
437 sql_stmt_read_keypoints_, rc, 0);
438
439 SQLITE3_CALL(sqlite3_reset(sql_stmt_read_keypoints_));
440
441 return FeatureKeypointsFromBlob(blob);
442 }
443
ReadDescriptors(const image_t image_id) const444 FeatureDescriptors Database::ReadDescriptors(const image_t image_id) const {
445 SQLITE3_CALL(sqlite3_bind_int64(sql_stmt_read_descriptors_, 1, image_id));
446
447 const int rc = SQLITE3_CALL(sqlite3_step(sql_stmt_read_descriptors_));
448 const FeatureDescriptors descriptors =
449 ReadDynamicMatrixBlob<FeatureDescriptors>(sql_stmt_read_descriptors_, rc,
450 0);
451
452 SQLITE3_CALL(sqlite3_reset(sql_stmt_read_descriptors_));
453
454 return descriptors;
455 }
456
ReadMatches(image_t image_id1,image_t image_id2) const457 FeatureMatches Database::ReadMatches(image_t image_id1,
458 image_t image_id2) const {
459 const image_pair_t pair_id = ImagePairToPairId(image_id1, image_id2);
460 SQLITE3_CALL(sqlite3_bind_int64(sql_stmt_read_matches_, 1, pair_id));
461
462 const int rc = SQLITE3_CALL(sqlite3_step(sql_stmt_read_matches_));
463 FeatureMatchesBlob blob =
464 ReadDynamicMatrixBlob<FeatureMatchesBlob>(sql_stmt_read_matches_, rc, 0);
465
466 SQLITE3_CALL(sqlite3_reset(sql_stmt_read_matches_));
467
468 if (SwapImagePair(image_id1, image_id2)) {
469 SwapFeatureMatchesBlob(&blob);
470 }
471
472 return FeatureMatchesFromBlob(blob);
473 }
474
ReadAllMatches() const475 std::vector<std::pair<image_pair_t, FeatureMatches>> Database::ReadAllMatches()
476 const {
477 std::vector<std::pair<image_pair_t, FeatureMatches>> all_matches;
478
479 int rc;
480 while ((rc = SQLITE3_CALL(sqlite3_step(sql_stmt_read_matches_all_))) ==
481 SQLITE_ROW) {
482 const image_pair_t pair_id = static_cast<image_pair_t>(
483 sqlite3_column_int64(sql_stmt_read_matches_all_, 0));
484 const FeatureMatchesBlob blob = ReadDynamicMatrixBlob<FeatureMatchesBlob>(
485 sql_stmt_read_matches_all_, rc, 1);
486 all_matches.emplace_back(pair_id, FeatureMatchesFromBlob(blob));
487 }
488
489 SQLITE3_CALL(sqlite3_reset(sql_stmt_read_matches_all_));
490
491 return all_matches;
492 }
493
ReadTwoViewGeometry(const image_t image_id1,const image_t image_id2) const494 TwoViewGeometry Database::ReadTwoViewGeometry(const image_t image_id1,
495 const image_t image_id2) const {
496 const image_pair_t pair_id = ImagePairToPairId(image_id1, image_id2);
497 SQLITE3_CALL(
498 sqlite3_bind_int64(sql_stmt_read_two_view_geometry_, 1, pair_id));
499
500 const int rc = SQLITE3_CALL(sqlite3_step(sql_stmt_read_two_view_geometry_));
501
502 TwoViewGeometry two_view_geometry;
503
504 FeatureMatchesBlob blob = ReadDynamicMatrixBlob<FeatureMatchesBlob>(
505 sql_stmt_read_two_view_geometry_, rc, 0);
506
507 two_view_geometry.config = static_cast<int>(
508 sqlite3_column_int64(sql_stmt_read_two_view_geometry_, 3));
509
510 two_view_geometry.F = ReadStaticMatrixBlob<Eigen::Matrix3d>(
511 sql_stmt_read_two_view_geometry_, rc, 4);
512 two_view_geometry.E = ReadStaticMatrixBlob<Eigen::Matrix3d>(
513 sql_stmt_read_two_view_geometry_, rc, 5);
514 two_view_geometry.H = ReadStaticMatrixBlob<Eigen::Matrix3d>(
515 sql_stmt_read_two_view_geometry_, rc, 6);
516
517 SQLITE3_CALL(sqlite3_reset(sql_stmt_read_two_view_geometry_));
518
519 two_view_geometry.inlier_matches = FeatureMatchesFromBlob(blob);
520 two_view_geometry.F.transposeInPlace();
521 two_view_geometry.E.transposeInPlace();
522 two_view_geometry.H.transposeInPlace();
523
524 if (SwapImagePair(image_id1, image_id2)) {
525 two_view_geometry.Invert();
526 }
527
528 return two_view_geometry;
529 }
530
ReadTwoViewGeometries(std::vector<image_pair_t> * image_pair_ids,std::vector<TwoViewGeometry> * two_view_geometries) const531 void Database::ReadTwoViewGeometries(
532 std::vector<image_pair_t>* image_pair_ids,
533 std::vector<TwoViewGeometry>* two_view_geometries) const {
534 int rc;
535 while ((rc = SQLITE3_CALL(sqlite3_step(
536 sql_stmt_read_two_view_geometries_))) == SQLITE_ROW) {
537 const image_pair_t pair_id = static_cast<image_pair_t>(
538 sqlite3_column_int64(sql_stmt_read_two_view_geometries_, 0));
539 image_pair_ids->push_back(pair_id);
540
541 TwoViewGeometry two_view_geometry;
542
543 const FeatureMatchesBlob blob = ReadDynamicMatrixBlob<FeatureMatchesBlob>(
544 sql_stmt_read_two_view_geometries_, rc, 1);
545 two_view_geometry.inlier_matches = FeatureMatchesFromBlob(blob);
546
547 two_view_geometry.config = static_cast<int>(
548 sqlite3_column_int64(sql_stmt_read_two_view_geometries_, 4));
549
550 two_view_geometry.F = ReadStaticMatrixBlob<Eigen::Matrix3d>(
551 sql_stmt_read_two_view_geometries_, rc, 5);
552 two_view_geometry.E = ReadStaticMatrixBlob<Eigen::Matrix3d>(
553 sql_stmt_read_two_view_geometries_, rc, 6);
554 two_view_geometry.H = ReadStaticMatrixBlob<Eigen::Matrix3d>(
555 sql_stmt_read_two_view_geometries_, rc, 7);
556
557 two_view_geometry.F.transposeInPlace();
558 two_view_geometry.E.transposeInPlace();
559 two_view_geometry.H.transposeInPlace();
560
561 two_view_geometries->push_back(two_view_geometry);
562 }
563
564 SQLITE3_CALL(sqlite3_reset(sql_stmt_read_two_view_geometries_));
565 }
566
ReadTwoViewGeometryNumInliers(std::vector<std::pair<image_t,image_t>> * image_pairs,std::vector<int> * num_inliers) const567 void Database::ReadTwoViewGeometryNumInliers(
568 std::vector<std::pair<image_t, image_t>>* image_pairs,
569 std::vector<int>* num_inliers) const {
570 const auto num_inlier_matches = NumInlierMatches();
571 image_pairs->reserve(num_inlier_matches);
572 num_inliers->reserve(num_inlier_matches);
573
574 while (SQLITE3_CALL(sqlite3_step(
575 sql_stmt_read_two_view_geometry_num_inliers_)) == SQLITE_ROW) {
576 image_t image_id1;
577 image_t image_id2;
578 const image_pair_t pair_id = static_cast<image_pair_t>(
579 sqlite3_column_int64(sql_stmt_read_two_view_geometry_num_inliers_, 0));
580 PairIdToImagePair(pair_id, &image_id1, &image_id2);
581 image_pairs->emplace_back(image_id1, image_id2);
582
583 const int rows = static_cast<int>(
584 sqlite3_column_int64(sql_stmt_read_two_view_geometry_num_inliers_, 1));
585 num_inliers->push_back(rows);
586 }
587
588 SQLITE3_CALL(sqlite3_reset(sql_stmt_read_two_view_geometry_num_inliers_));
589 }
590
WriteCamera(const Camera & camera,const bool use_camera_id) const591 camera_t Database::WriteCamera(const Camera& camera,
592 const bool use_camera_id) const {
593 if (use_camera_id) {
594 CHECK(!ExistsCamera(camera.CameraId())) << "camera_id must be unique";
595 SQLITE3_CALL(
596 sqlite3_bind_int64(sql_stmt_add_camera_, 1, camera.CameraId()));
597 } else {
598 SQLITE3_CALL(sqlite3_bind_null(sql_stmt_add_camera_, 1));
599 }
600
601 SQLITE3_CALL(sqlite3_bind_int64(sql_stmt_add_camera_, 2, camera.ModelId()));
602 SQLITE3_CALL(sqlite3_bind_int64(sql_stmt_add_camera_, 3,
603 static_cast<sqlite3_int64>(camera.Width())));
604 SQLITE3_CALL(sqlite3_bind_int64(sql_stmt_add_camera_, 4,
605 static_cast<sqlite3_int64>(camera.Height())));
606
607 const size_t num_params_bytes = sizeof(double) * camera.NumParams();
608 SQLITE3_CALL(sqlite3_bind_blob(sql_stmt_add_camera_, 5, camera.ParamsData(),
609 static_cast<int>(num_params_bytes),
610 SQLITE_STATIC));
611
612 SQLITE3_CALL(sqlite3_bind_int64(sql_stmt_add_camera_, 6,
613 camera.HasPriorFocalLength()));
614
615 SQLITE3_CALL(sqlite3_step(sql_stmt_add_camera_));
616 SQLITE3_CALL(sqlite3_reset(sql_stmt_add_camera_));
617
618 return static_cast<camera_t>(sqlite3_last_insert_rowid(database_));
619 }
620
WriteImage(const Image & image,const bool use_image_id) const621 image_t Database::WriteImage(const Image& image,
622 const bool use_image_id) const {
623 if (use_image_id) {
624 CHECK(!ExistsImage(image.ImageId())) << "image_id must be unique";
625 SQLITE3_CALL(sqlite3_bind_int64(sql_stmt_add_image_, 1, image.ImageId()));
626 } else {
627 SQLITE3_CALL(sqlite3_bind_null(sql_stmt_add_image_, 1));
628 }
629
630 SQLITE3_CALL(sqlite3_bind_text(sql_stmt_add_image_, 2, image.Name().c_str(),
631 static_cast<int>(image.Name().size()),
632 SQLITE_STATIC));
633 SQLITE3_CALL(sqlite3_bind_int64(sql_stmt_add_image_, 3, image.CameraId()));
634
635 // NaNs are automatically converted to NULLs in SQLite.
636 SQLITE3_CALL(sqlite3_bind_double(sql_stmt_add_image_, 4, image.QvecPrior(0)));
637 SQLITE3_CALL(sqlite3_bind_double(sql_stmt_add_image_, 5, image.QvecPrior(1)));
638 SQLITE3_CALL(sqlite3_bind_double(sql_stmt_add_image_, 6, image.QvecPrior(2)));
639 SQLITE3_CALL(sqlite3_bind_double(sql_stmt_add_image_, 7, image.QvecPrior(3)));
640
641 // NaNs are automatically converted to NULLs in SQLite.
642 SQLITE3_CALL(sqlite3_bind_double(sql_stmt_add_image_, 8, image.TvecPrior(0)));
643 SQLITE3_CALL(sqlite3_bind_double(sql_stmt_add_image_, 9, image.TvecPrior(1)));
644 SQLITE3_CALL(
645 sqlite3_bind_double(sql_stmt_add_image_, 10, image.TvecPrior(2)));
646
647 SQLITE3_CALL(sqlite3_step(sql_stmt_add_image_));
648 SQLITE3_CALL(sqlite3_reset(sql_stmt_add_image_));
649
650 return static_cast<image_t>(sqlite3_last_insert_rowid(database_));
651 }
652
WriteKeypoints(const image_t image_id,const FeatureKeypoints & keypoints) const653 void Database::WriteKeypoints(const image_t image_id,
654 const FeatureKeypoints& keypoints) const {
655 const FeatureKeypointsBlob blob = FeatureKeypointsToBlob(keypoints);
656
657 SQLITE3_CALL(sqlite3_bind_int64(sql_stmt_write_keypoints_, 1, image_id));
658 WriteDynamicMatrixBlob(sql_stmt_write_keypoints_, blob, 2);
659
660 SQLITE3_CALL(sqlite3_step(sql_stmt_write_keypoints_));
661 SQLITE3_CALL(sqlite3_reset(sql_stmt_write_keypoints_));
662 }
663
WriteDescriptors(const image_t image_id,const FeatureDescriptors & descriptors) const664 void Database::WriteDescriptors(const image_t image_id,
665 const FeatureDescriptors& descriptors) const {
666 SQLITE3_CALL(sqlite3_bind_int64(sql_stmt_write_descriptors_, 1, image_id));
667 WriteDynamicMatrixBlob(sql_stmt_write_descriptors_, descriptors, 2);
668
669 SQLITE3_CALL(sqlite3_step(sql_stmt_write_descriptors_));
670 SQLITE3_CALL(sqlite3_reset(sql_stmt_write_descriptors_));
671 }
672
WriteMatches(const image_t image_id1,const image_t image_id2,const FeatureMatches & matches) const673 void Database::WriteMatches(const image_t image_id1, const image_t image_id2,
674 const FeatureMatches& matches) const {
675 const image_pair_t pair_id = ImagePairToPairId(image_id1, image_id2);
676 SQLITE3_CALL(sqlite3_bind_int64(sql_stmt_write_matches_, 1, pair_id));
677
678 // Important: the swapped data must live until the query is executed.
679 FeatureMatchesBlob blob = FeatureMatchesToBlob(matches);
680 if (SwapImagePair(image_id1, image_id2)) {
681 SwapFeatureMatchesBlob(&blob);
682 WriteDynamicMatrixBlob(sql_stmt_write_matches_, blob, 2);
683 } else {
684 WriteDynamicMatrixBlob(sql_stmt_write_matches_, blob, 2);
685 }
686
687 SQLITE3_CALL(sqlite3_step(sql_stmt_write_matches_));
688 SQLITE3_CALL(sqlite3_reset(sql_stmt_write_matches_));
689 }
690
WriteTwoViewGeometry(const image_t image_id1,const image_t image_id2,const TwoViewGeometry & two_view_geometry) const691 void Database::WriteTwoViewGeometry(
692 const image_t image_id1, const image_t image_id2,
693 const TwoViewGeometry& two_view_geometry) const {
694 const image_pair_t pair_id = ImagePairToPairId(image_id1, image_id2);
695 SQLITE3_CALL(
696 sqlite3_bind_int64(sql_stmt_write_two_view_geometry_, 1, pair_id));
697
698 const TwoViewGeometry* two_view_geometry_ptr = &two_view_geometry;
699
700 // Invert the two-view geometry if the image pair has to be swapped.
701 std::unique_ptr<TwoViewGeometry> swapped_two_view_geometry;
702 if (SwapImagePair(image_id1, image_id2)) {
703 swapped_two_view_geometry.reset(new TwoViewGeometry());
704 *swapped_two_view_geometry = two_view_geometry;
705 swapped_two_view_geometry->Invert();
706 two_view_geometry_ptr = swapped_two_view_geometry.get();
707 }
708
709 const FeatureMatchesBlob inlier_matches =
710 FeatureMatchesToBlob(two_view_geometry_ptr->inlier_matches);
711 WriteDynamicMatrixBlob(sql_stmt_write_two_view_geometry_, inlier_matches, 2);
712
713 SQLITE3_CALL(sqlite3_bind_int64(sql_stmt_write_two_view_geometry_, 5,
714 two_view_geometry_ptr->config));
715
716 // Transpose the matrices to obtain row-major data layout.
717 // Important: Do not move these objects inside the if-statement, because
718 // the objects must live until `sqlite3_step` is called on the statement.
719 const Eigen::Matrix3d Ft = two_view_geometry_ptr->F.transpose();
720 const Eigen::Matrix3d Et = two_view_geometry_ptr->E.transpose();
721 const Eigen::Matrix3d Ht = two_view_geometry_ptr->H.transpose();
722
723 if (two_view_geometry_ptr->inlier_matches.size() > 0) {
724 WriteStaticMatrixBlob(sql_stmt_write_two_view_geometry_, Ft, 6);
725 WriteStaticMatrixBlob(sql_stmt_write_two_view_geometry_, Et, 7);
726 WriteStaticMatrixBlob(sql_stmt_write_two_view_geometry_, Ht, 8);
727 } else {
728 WriteStaticMatrixBlob(sql_stmt_write_two_view_geometry_,
729 Eigen::MatrixXd(0, 0), 6);
730 WriteStaticMatrixBlob(sql_stmt_write_two_view_geometry_,
731 Eigen::MatrixXd(0, 0), 7);
732 WriteStaticMatrixBlob(sql_stmt_write_two_view_geometry_,
733 Eigen::MatrixXd(0, 0), 8);
734 }
735
736 SQLITE3_CALL(sqlite3_step(sql_stmt_write_two_view_geometry_));
737 SQLITE3_CALL(sqlite3_reset(sql_stmt_write_two_view_geometry_));
738 }
739
UpdateCamera(const Camera & camera) const740 void Database::UpdateCamera(const Camera& camera) const {
741 SQLITE3_CALL(
742 sqlite3_bind_int64(sql_stmt_update_camera_, 1, camera.ModelId()));
743 SQLITE3_CALL(sqlite3_bind_int64(sql_stmt_update_camera_, 2,
744 static_cast<sqlite3_int64>(camera.Width())));
745 SQLITE3_CALL(sqlite3_bind_int64(sql_stmt_update_camera_, 3,
746 static_cast<sqlite3_int64>(camera.Height())));
747
748 const size_t num_params_bytes = sizeof(double) * camera.NumParams();
749 SQLITE3_CALL(
750 sqlite3_bind_blob(sql_stmt_update_camera_, 4, camera.ParamsData(),
751 static_cast<int>(num_params_bytes), SQLITE_STATIC));
752
753 SQLITE3_CALL(sqlite3_bind_int64(sql_stmt_update_camera_, 5,
754 camera.HasPriorFocalLength()));
755
756 SQLITE3_CALL(
757 sqlite3_bind_int64(sql_stmt_update_camera_, 6, camera.CameraId()));
758
759 SQLITE3_CALL(sqlite3_step(sql_stmt_update_camera_));
760 SQLITE3_CALL(sqlite3_reset(sql_stmt_update_camera_));
761 }
762
UpdateImage(const Image & image) const763 void Database::UpdateImage(const Image& image) const {
764 SQLITE3_CALL(
765 sqlite3_bind_text(sql_stmt_update_image_, 1, image.Name().c_str(),
766 static_cast<int>(image.Name().size()), SQLITE_STATIC));
767 SQLITE3_CALL(sqlite3_bind_int64(sql_stmt_update_image_, 2, image.CameraId()));
768 SQLITE3_CALL(
769 sqlite3_bind_double(sql_stmt_update_image_, 3, image.QvecPrior(0)));
770 SQLITE3_CALL(
771 sqlite3_bind_double(sql_stmt_update_image_, 4, image.QvecPrior(1)));
772 SQLITE3_CALL(
773 sqlite3_bind_double(sql_stmt_update_image_, 5, image.QvecPrior(2)));
774 SQLITE3_CALL(
775 sqlite3_bind_double(sql_stmt_update_image_, 6, image.QvecPrior(3)));
776 SQLITE3_CALL(
777 sqlite3_bind_double(sql_stmt_update_image_, 7, image.TvecPrior(0)));
778 SQLITE3_CALL(
779 sqlite3_bind_double(sql_stmt_update_image_, 8, image.TvecPrior(1)));
780 SQLITE3_CALL(
781 sqlite3_bind_double(sql_stmt_update_image_, 9, image.TvecPrior(2)));
782
783 SQLITE3_CALL(sqlite3_bind_int64(sql_stmt_update_image_, 10, image.ImageId()));
784
785 SQLITE3_CALL(sqlite3_step(sql_stmt_update_image_));
786 SQLITE3_CALL(sqlite3_reset(sql_stmt_update_image_));
787 }
788
DeleteMatches(const image_t image_id1,const image_t image_id2) const789 void Database::DeleteMatches(const image_t image_id1,
790 const image_t image_id2) const {
791 const image_pair_t pair_id = ImagePairToPairId(image_id1, image_id2);
792 SQLITE3_CALL(sqlite3_bind_int64(sql_stmt_delete_matches_, 1,
793 static_cast<sqlite3_int64>(pair_id)));
794 SQLITE3_CALL(sqlite3_step(sql_stmt_delete_matches_));
795 SQLITE3_CALL(sqlite3_reset(sql_stmt_delete_matches_));
796 }
797
DeleteInlierMatches(const image_t image_id1,const image_t image_id2) const798 void Database::DeleteInlierMatches(const image_t image_id1,
799 const image_t image_id2) const {
800 const image_pair_t pair_id = ImagePairToPairId(image_id1, image_id2);
801 SQLITE3_CALL(sqlite3_bind_int64(sql_stmt_delete_two_view_geometry_, 1,
802 static_cast<sqlite3_int64>(pair_id)));
803 SQLITE3_CALL(sqlite3_step(sql_stmt_delete_two_view_geometry_));
804 SQLITE3_CALL(sqlite3_reset(sql_stmt_delete_two_view_geometry_));
805 }
806
ClearMatches() const807 void Database::ClearMatches() const {
808 SQLITE3_CALL(sqlite3_step(sql_stmt_clear_matches_));
809 SQLITE3_CALL(sqlite3_reset(sql_stmt_clear_matches_));
810 }
811
ClearTwoViewGeometries() const812 void Database::ClearTwoViewGeometries() const {
813 SQLITE3_CALL(sqlite3_step(sql_stmt_clear_two_view_geometries_));
814 SQLITE3_CALL(sqlite3_reset(sql_stmt_clear_two_view_geometries_));
815 }
816
Merge(const Database & database1,const Database & database2,Database * merged_database)817 void Database::Merge(const Database& database1, const Database& database2,
818 Database* merged_database) {
819 // Merge the cameras.
820
821 std::unordered_map<camera_t, camera_t> new_camera_ids1;
822 for (const auto& camera : database1.ReadAllCameras()) {
823 const camera_t new_camera_id = merged_database->WriteCamera(camera);
824 new_camera_ids1.emplace(camera.CameraId(), new_camera_id);
825 }
826
827 std::unordered_map<camera_t, camera_t> new_camera_ids2;
828 for (const auto& camera : database2.ReadAllCameras()) {
829 const camera_t new_camera_id = merged_database->WriteCamera(camera);
830 new_camera_ids2.emplace(camera.CameraId(), new_camera_id);
831 }
832
833 // Merge the images.
834
835 std::unordered_map<image_t, image_t> new_image_ids1;
836 for (auto& image : database1.ReadAllImages()) {
837 image.SetCameraId(new_camera_ids1.at(image.CameraId()));
838 CHECK(!merged_database->ExistsImageWithName(image.Name()))
839 << "The two databases must not contain images with the same name, but "
840 "the there are images with name "
841 << image.Name() << " in both databases";
842 const image_t new_image_id = merged_database->WriteImage(image);
843 new_image_ids1.emplace(image.ImageId(), new_image_id);
844 const auto keypoints = database1.ReadKeypoints(image.ImageId());
845 const auto descriptors = database1.ReadDescriptors(image.ImageId());
846 merged_database->WriteKeypoints(new_image_id, keypoints);
847 merged_database->WriteDescriptors(new_image_id, descriptors);
848 }
849
850 std::unordered_map<image_t, image_t> new_image_ids2;
851 for (auto& image : database2.ReadAllImages()) {
852 image.SetCameraId(new_camera_ids2.at(image.CameraId()));
853 CHECK(!merged_database->ExistsImageWithName(image.Name()))
854 << "The two databases must not contain images with the same name, but "
855 "the there are images with name "
856 << image.Name() << " in both databases";
857 const image_t new_image_id = merged_database->WriteImage(image);
858 new_image_ids2.emplace(image.ImageId(), new_image_id);
859 const auto keypoints = database2.ReadKeypoints(image.ImageId());
860 const auto descriptors = database2.ReadDescriptors(image.ImageId());
861 merged_database->WriteKeypoints(new_image_id, keypoints);
862 merged_database->WriteDescriptors(new_image_id, descriptors);
863 }
864
865 // Merge the matches.
866
867 for (const auto& matches : database1.ReadAllMatches()) {
868 image_t image_id1, image_id2;
869 Database::PairIdToImagePair(matches.first, &image_id1, &image_id2);
870
871 const image_t new_image_id1 = new_image_ids1.at(image_id1);
872 const image_t new_image_id2 = new_image_ids1.at(image_id2);
873
874 merged_database->WriteMatches(new_image_id1, new_image_id2, matches.second);
875 }
876
877 for (const auto& matches : database2.ReadAllMatches()) {
878 image_t image_id1, image_id2;
879 Database::PairIdToImagePair(matches.first, &image_id1, &image_id2);
880
881 const image_t new_image_id1 = new_image_ids2.at(image_id1);
882 const image_t new_image_id2 = new_image_ids2.at(image_id2);
883
884 merged_database->WriteMatches(new_image_id1, new_image_id2, matches.second);
885 }
886
887 // Merge the two-view geometries.
888
889 {
890 std::vector<image_pair_t> image_pair_ids;
891 std::vector<TwoViewGeometry> two_view_geometries;
892 database1.ReadTwoViewGeometries(&image_pair_ids, &two_view_geometries);
893
894 for (size_t i = 0; i < two_view_geometries.size(); ++i) {
895 image_t image_id1, image_id2;
896 Database::PairIdToImagePair(image_pair_ids[i], &image_id1, &image_id2);
897
898 const image_t new_image_id1 = new_image_ids1.at(image_id1);
899 const image_t new_image_id2 = new_image_ids1.at(image_id2);
900
901 merged_database->WriteTwoViewGeometry(new_image_id1, new_image_id2,
902 two_view_geometries[i]);
903 }
904 }
905
906 {
907 std::vector<image_pair_t> image_pair_ids;
908 std::vector<TwoViewGeometry> two_view_geometries;
909 database2.ReadTwoViewGeometries(&image_pair_ids, &two_view_geometries);
910
911 for (size_t i = 0; i < two_view_geometries.size(); ++i) {
912 image_t image_id1, image_id2;
913 Database::PairIdToImagePair(image_pair_ids[i], &image_id1, &image_id2);
914
915 const image_t new_image_id1 = new_image_ids2.at(image_id1);
916 const image_t new_image_id2 = new_image_ids2.at(image_id2);
917
918 merged_database->WriteTwoViewGeometry(new_image_id1, new_image_id2,
919 two_view_geometries[i]);
920 }
921 }
922 }
923
BeginTransaction() const924 void Database::BeginTransaction() const {
925 SQLITE3_EXEC(database_, "BEGIN TRANSACTION", nullptr);
926 }
927
EndTransaction() const928 void Database::EndTransaction() const {
929 SQLITE3_EXEC(database_, "END TRANSACTION", nullptr);
930 }
931
PrepareSQLStatements()932 void Database::PrepareSQLStatements() {
933 sql_stmts_.clear();
934
935 std::string sql;
936
937 //////////////////////////////////////////////////////////////////////////////
938 // num_*
939 //////////////////////////////////////////////////////////////////////////////
940 sql = "SELECT rows FROM keypoints WHERE image_id = ?;";
941 SQLITE3_CALL(sqlite3_prepare_v2(database_, sql.c_str(), -1,
942 &sql_stmt_num_keypoints_, 0));
943 sql_stmts_.push_back(sql_stmt_num_keypoints_);
944
945 sql = "SELECT rows FROM descriptors WHERE image_id = ?;";
946 SQLITE3_CALL(sqlite3_prepare_v2(database_, sql.c_str(), -1,
947 &sql_stmt_num_descriptors_, 0));
948 sql_stmts_.push_back(sql_stmt_num_descriptors_);
949
950 //////////////////////////////////////////////////////////////////////////////
951 // exists_*
952 //////////////////////////////////////////////////////////////////////////////
953 sql = "SELECT 1 FROM cameras WHERE camera_id = ?;";
954 SQLITE3_CALL(sqlite3_prepare_v2(database_, sql.c_str(), -1,
955 &sql_stmt_exists_camera_, 0));
956 sql_stmts_.push_back(sql_stmt_exists_camera_);
957
958 sql = "SELECT 1 FROM images WHERE image_id = ?;";
959 SQLITE3_CALL(sqlite3_prepare_v2(database_, sql.c_str(), -1,
960 &sql_stmt_exists_image_id_, 0));
961 sql_stmts_.push_back(sql_stmt_exists_image_id_);
962
963 sql = "SELECT 1 FROM images WHERE name = ?;";
964 SQLITE3_CALL(sqlite3_prepare_v2(database_, sql.c_str(), -1,
965 &sql_stmt_exists_image_name_, 0));
966 sql_stmts_.push_back(sql_stmt_exists_image_name_);
967
968 sql = "SELECT 1 FROM keypoints WHERE image_id = ?;";
969 SQLITE3_CALL(sqlite3_prepare_v2(database_, sql.c_str(), -1,
970 &sql_stmt_exists_keypoints_, 0));
971 sql_stmts_.push_back(sql_stmt_exists_keypoints_);
972
973 sql = "SELECT 1 FROM descriptors WHERE image_id = ?;";
974 SQLITE3_CALL(sqlite3_prepare_v2(database_, sql.c_str(), -1,
975 &sql_stmt_exists_descriptors_, 0));
976 sql_stmts_.push_back(sql_stmt_exists_descriptors_);
977
978 sql = "SELECT 1 FROM matches WHERE pair_id = ?;";
979 SQLITE3_CALL(sqlite3_prepare_v2(database_, sql.c_str(), -1,
980 &sql_stmt_exists_matches_, 0));
981 sql_stmts_.push_back(sql_stmt_exists_matches_);
982
983 sql = "SELECT 1 FROM two_view_geometries WHERE pair_id = ?;";
984 SQLITE3_CALL(sqlite3_prepare_v2(database_, sql.c_str(), -1,
985 &sql_stmt_exists_two_view_geometry_, 0));
986 sql_stmts_.push_back(sql_stmt_exists_two_view_geometry_);
987
988 //////////////////////////////////////////////////////////////////////////////
989 // add_*
990 //////////////////////////////////////////////////////////////////////////////
991 sql =
992 "INSERT INTO cameras(camera_id, model, width, height, params, "
993 "prior_focal_length) VALUES(?, ?, ?, ?, ?, ?);";
994 SQLITE3_CALL(
995 sqlite3_prepare_v2(database_, sql.c_str(), -1, &sql_stmt_add_camera_, 0));
996 sql_stmts_.push_back(sql_stmt_add_camera_);
997
998 sql =
999 "INSERT INTO images(image_id, name, camera_id, prior_qw, prior_qx, "
1000 "prior_qy, prior_qz, prior_tx, prior_ty, prior_tz) VALUES(?, ?, ?, ?, ?, "
1001 "?, ?, ?, ?, ?);";
1002 SQLITE3_CALL(
1003 sqlite3_prepare_v2(database_, sql.c_str(), -1, &sql_stmt_add_image_, 0));
1004 sql_stmts_.push_back(sql_stmt_add_image_);
1005
1006 //////////////////////////////////////////////////////////////////////////////
1007 // update_*
1008 //////////////////////////////////////////////////////////////////////////////
1009 sql =
1010 "UPDATE cameras SET model=?, width=?, height=?, params=?, "
1011 "prior_focal_length=? WHERE camera_id=?;";
1012 SQLITE3_CALL(sqlite3_prepare_v2(database_, sql.c_str(), -1,
1013 &sql_stmt_update_camera_, 0));
1014 sql_stmts_.push_back(sql_stmt_update_camera_);
1015
1016 sql =
1017 "UPDATE images SET name=?, camera_id=?, prior_qw=?, prior_qx=?, "
1018 "prior_qy=?, prior_qz=?, prior_tx=?, prior_ty=?, prior_tz=? WHERE "
1019 "image_id=?;";
1020 SQLITE3_CALL(sqlite3_prepare_v2(database_, sql.c_str(), -1,
1021 &sql_stmt_update_image_, 0));
1022 sql_stmts_.push_back(sql_stmt_update_image_);
1023
1024 //////////////////////////////////////////////////////////////////////////////
1025 // read_*
1026 //////////////////////////////////////////////////////////////////////////////
1027 sql = "SELECT * FROM cameras WHERE camera_id = ?;";
1028 SQLITE3_CALL(sqlite3_prepare_v2(database_, sql.c_str(), -1,
1029 &sql_stmt_read_camera_, 0));
1030 sql_stmts_.push_back(sql_stmt_read_camera_);
1031
1032 sql = "SELECT * FROM cameras;";
1033 SQLITE3_CALL(sqlite3_prepare_v2(database_, sql.c_str(), -1,
1034 &sql_stmt_read_cameras_, 0));
1035 sql_stmts_.push_back(sql_stmt_read_cameras_);
1036
1037 sql = "SELECT * FROM images WHERE image_id = ?;";
1038 SQLITE3_CALL(sqlite3_prepare_v2(database_, sql.c_str(), -1,
1039 &sql_stmt_read_image_id_, 0));
1040 sql_stmts_.push_back(sql_stmt_read_image_id_);
1041
1042 sql = "SELECT * FROM images WHERE name = ?;";
1043 SQLITE3_CALL(sqlite3_prepare_v2(database_, sql.c_str(), -1,
1044 &sql_stmt_read_image_name_, 0));
1045 sql_stmts_.push_back(sql_stmt_read_image_name_);
1046
1047 sql = "SELECT * FROM images;";
1048 SQLITE3_CALL(sqlite3_prepare_v2(database_, sql.c_str(), -1,
1049 &sql_stmt_read_images_, 0));
1050 sql_stmts_.push_back(sql_stmt_read_images_);
1051
1052 sql = "SELECT rows, cols, data FROM keypoints WHERE image_id = ?;";
1053 SQLITE3_CALL(sqlite3_prepare_v2(database_, sql.c_str(), -1,
1054 &sql_stmt_read_keypoints_, 0));
1055 sql_stmts_.push_back(sql_stmt_read_keypoints_);
1056
1057 sql = "SELECT rows, cols, data FROM descriptors WHERE image_id = ?;";
1058 SQLITE3_CALL(sqlite3_prepare_v2(database_, sql.c_str(), -1,
1059 &sql_stmt_read_descriptors_, 0));
1060 sql_stmts_.push_back(sql_stmt_read_descriptors_);
1061
1062 sql = "SELECT rows, cols, data FROM matches WHERE pair_id = ?;";
1063 SQLITE3_CALL(sqlite3_prepare_v2(database_, sql.c_str(), -1,
1064 &sql_stmt_read_matches_, 0));
1065 sql_stmts_.push_back(sql_stmt_read_matches_);
1066
1067 sql = "SELECT * FROM matches WHERE rows > 0;";
1068 SQLITE3_CALL(sqlite3_prepare_v2(database_, sql.c_str(), -1,
1069 &sql_stmt_read_matches_all_, 0));
1070 sql_stmts_.push_back(sql_stmt_read_matches_all_);
1071
1072 sql =
1073 "SELECT rows, cols, data, config, F, E, H FROM two_view_geometries WHERE "
1074 "pair_id = ?;";
1075 SQLITE3_CALL(sqlite3_prepare_v2(database_, sql.c_str(), -1,
1076 &sql_stmt_read_two_view_geometry_, 0));
1077 sql_stmts_.push_back(sql_stmt_read_two_view_geometry_);
1078
1079 sql = "SELECT * FROM two_view_geometries WHERE rows > 0;";
1080 SQLITE3_CALL(sqlite3_prepare_v2(database_, sql.c_str(), -1,
1081 &sql_stmt_read_two_view_geometries_, 0));
1082 sql_stmts_.push_back(sql_stmt_read_two_view_geometries_);
1083
1084 sql = "SELECT pair_id, rows FROM two_view_geometries WHERE rows > 0;";
1085 SQLITE3_CALL(sqlite3_prepare_v2(database_, sql.c_str(), -1,
1086 &sql_stmt_read_two_view_geometry_num_inliers_,
1087 0));
1088 sql_stmts_.push_back(sql_stmt_read_two_view_geometry_num_inliers_);
1089
1090 //////////////////////////////////////////////////////////////////////////////
1091 // write_*
1092 //////////////////////////////////////////////////////////////////////////////
1093 sql = "INSERT INTO keypoints(image_id, rows, cols, data) VALUES(?, ?, ?, ?);";
1094 SQLITE3_CALL(sqlite3_prepare_v2(database_, sql.c_str(), -1,
1095 &sql_stmt_write_keypoints_, 0));
1096 sql_stmts_.push_back(sql_stmt_write_keypoints_);
1097
1098 sql =
1099 "INSERT INTO descriptors(image_id, rows, cols, data) VALUES(?, ?, ?, ?);";
1100 SQLITE3_CALL(sqlite3_prepare_v2(database_, sql.c_str(), -1,
1101 &sql_stmt_write_descriptors_, 0));
1102 sql_stmts_.push_back(sql_stmt_write_descriptors_);
1103
1104 sql = "INSERT INTO matches(pair_id, rows, cols, data) VALUES(?, ?, ?, ?);";
1105 SQLITE3_CALL(sqlite3_prepare_v2(database_, sql.c_str(), -1,
1106 &sql_stmt_write_matches_, 0));
1107 sql_stmts_.push_back(sql_stmt_write_matches_);
1108
1109 sql =
1110 "INSERT INTO two_view_geometries(pair_id, rows, cols, data, config, F, "
1111 "E, H) VALUES(?, ?, ?, ?, ?, ?, ?, ?);";
1112 SQLITE3_CALL(sqlite3_prepare_v2(database_, sql.c_str(), -1,
1113 &sql_stmt_write_two_view_geometry_, 0));
1114 sql_stmts_.push_back(sql_stmt_write_two_view_geometry_);
1115
1116 //////////////////////////////////////////////////////////////////////////////
1117 // delete_*
1118 //////////////////////////////////////////////////////////////////////////////
1119 sql = "DELETE FROM matches WHERE pair_id = ?;";
1120 SQLITE3_CALL(sqlite3_prepare_v2(database_, sql.c_str(), -1,
1121 &sql_stmt_delete_matches_, 0));
1122 sql_stmts_.push_back(sql_stmt_delete_matches_);
1123
1124 sql = "DELETE FROM two_view_geometries WHERE pair_id = ?;";
1125 SQLITE3_CALL(sqlite3_prepare_v2(database_, sql.c_str(), -1,
1126 &sql_stmt_delete_two_view_geometry_, 0));
1127 sql_stmts_.push_back(sql_stmt_delete_two_view_geometry_);
1128
1129 //////////////////////////////////////////////////////////////////////////////
1130 // clear_*
1131 //////////////////////////////////////////////////////////////////////////////
1132 sql = "DELETE FROM matches;";
1133 SQLITE3_CALL(sqlite3_prepare_v2(database_, sql.c_str(), -1,
1134 &sql_stmt_clear_matches_, 0));
1135 sql_stmts_.push_back(sql_stmt_clear_matches_);
1136
1137 sql = "DELETE FROM two_view_geometries;";
1138 SQLITE3_CALL(sqlite3_prepare_v2(database_, sql.c_str(), -1,
1139 &sql_stmt_clear_two_view_geometries_, 0));
1140 sql_stmts_.push_back(sql_stmt_clear_two_view_geometries_);
1141 }
1142
FinalizeSQLStatements()1143 void Database::FinalizeSQLStatements() {
1144 for (const auto& sql_stmt : sql_stmts_) {
1145 SQLITE3_CALL(sqlite3_finalize(sql_stmt));
1146 }
1147 }
1148
CreateTables() const1149 void Database::CreateTables() const {
1150 CreateCameraTable();
1151 CreateImageTable();
1152 CreateKeypointsTable();
1153 CreateDescriptorsTable();
1154 CreateMatchesTable();
1155 CreateTwoViewGeometriesTable();
1156 }
1157
CreateCameraTable() const1158 void Database::CreateCameraTable() const {
1159 const std::string sql =
1160 "CREATE TABLE IF NOT EXISTS cameras"
1161 " (camera_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,"
1162 " model INTEGER NOT NULL,"
1163 " width INTEGER NOT NULL,"
1164 " height INTEGER NOT NULL,"
1165 " params BLOB,"
1166 " prior_focal_length INTEGER NOT NULL);";
1167
1168 SQLITE3_EXEC(database_, sql.c_str(), nullptr);
1169 }
1170
CreateImageTable() const1171 void Database::CreateImageTable() const {
1172 const std::string sql = StringPrintf(
1173 "CREATE TABLE IF NOT EXISTS images"
1174 " (image_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,"
1175 " name TEXT NOT NULL UNIQUE,"
1176 " camera_id INTEGER NOT NULL,"
1177 " prior_qw REAL,"
1178 " prior_qx REAL,"
1179 " prior_qy REAL,"
1180 " prior_qz REAL,"
1181 " prior_tx REAL,"
1182 " prior_ty REAL,"
1183 " prior_tz REAL,"
1184 "CONSTRAINT image_id_check CHECK(image_id >= 0 and image_id < %d),"
1185 "FOREIGN KEY(camera_id) REFERENCES cameras(camera_id));"
1186 "CREATE UNIQUE INDEX IF NOT EXISTS index_name ON images(name);",
1187 kMaxNumImages);
1188
1189 SQLITE3_EXEC(database_, sql.c_str(), nullptr);
1190 }
1191
CreateKeypointsTable() const1192 void Database::CreateKeypointsTable() const {
1193 const std::string sql =
1194 "CREATE TABLE IF NOT EXISTS keypoints"
1195 " (image_id INTEGER PRIMARY KEY NOT NULL,"
1196 " rows INTEGER NOT NULL,"
1197 " cols INTEGER NOT NULL,"
1198 " data BLOB,"
1199 "FOREIGN KEY(image_id) REFERENCES images(image_id) ON DELETE CASCADE);";
1200
1201 SQLITE3_EXEC(database_, sql.c_str(), nullptr);
1202 }
1203
CreateDescriptorsTable() const1204 void Database::CreateDescriptorsTable() const {
1205 const std::string sql =
1206 "CREATE TABLE IF NOT EXISTS descriptors"
1207 " (image_id INTEGER PRIMARY KEY NOT NULL,"
1208 " rows INTEGER NOT NULL,"
1209 " cols INTEGER NOT NULL,"
1210 " data BLOB,"
1211 "FOREIGN KEY(image_id) REFERENCES images(image_id) ON DELETE CASCADE);";
1212
1213 SQLITE3_EXEC(database_, sql.c_str(), nullptr);
1214 }
1215
CreateMatchesTable() const1216 void Database::CreateMatchesTable() const {
1217 const std::string sql =
1218 "CREATE TABLE IF NOT EXISTS matches"
1219 " (pair_id INTEGER PRIMARY KEY NOT NULL,"
1220 " rows INTEGER NOT NULL,"
1221 " cols INTEGER NOT NULL,"
1222 " data BLOB);";
1223
1224 SQLITE3_EXEC(database_, sql.c_str(), nullptr);
1225 }
1226
CreateTwoViewGeometriesTable() const1227 void Database::CreateTwoViewGeometriesTable() const {
1228 if (ExistsTable("inlier_matches")) {
1229 SQLITE3_EXEC(database_,
1230 "ALTER TABLE inlier_matches RENAME TO two_view_geometries;",
1231 nullptr);
1232 } else {
1233 const std::string sql =
1234 "CREATE TABLE IF NOT EXISTS two_view_geometries"
1235 " (pair_id INTEGER PRIMARY KEY NOT NULL,"
1236 " rows INTEGER NOT NULL,"
1237 " cols INTEGER NOT NULL,"
1238 " data BLOB,"
1239 " config INTEGER NOT NULL,"
1240 " F BLOB,"
1241 " E BLOB,"
1242 " H BLOB);";
1243 SQLITE3_EXEC(database_, sql.c_str(), nullptr);
1244 }
1245 }
1246
UpdateSchema() const1247 void Database::UpdateSchema() const {
1248 if (!ExistsColumn("two_view_geometries", "F")) {
1249 SQLITE3_EXEC(database_,
1250 "ALTER TABLE two_view_geometries ADD COLUMN F BLOB;", nullptr);
1251 }
1252
1253 if (!ExistsColumn("two_view_geometries", "E")) {
1254 SQLITE3_EXEC(database_,
1255 "ALTER TABLE two_view_geometries ADD COLUMN E BLOB;", nullptr);
1256 }
1257
1258 if (!ExistsColumn("two_view_geometries", "H")) {
1259 SQLITE3_EXEC(database_,
1260 "ALTER TABLE two_view_geometries ADD COLUMN H BLOB;", nullptr);
1261 }
1262
1263 // Update user version number.
1264 std::unique_lock<std::mutex> lock(update_schema_mutex_);
1265 const std::string update_user_version_sql =
1266 StringPrintf("PRAGMA user_version = %d;", COLMAP_VERSION_NUMBER);
1267 SQLITE3_EXEC(database_, update_user_version_sql.c_str(), nullptr);
1268 }
1269
ExistsTable(const std::string & table_name) const1270 bool Database::ExistsTable(const std::string& table_name) const {
1271 const std::string sql =
1272 "SELECT name FROM sqlite_master WHERE type='table' AND name = ?;";
1273
1274 sqlite3_stmt* sql_stmt;
1275 SQLITE3_CALL(sqlite3_prepare_v2(database_, sql.c_str(), -1, &sql_stmt, 0));
1276
1277 SQLITE3_CALL(sqlite3_bind_text(sql_stmt, 1, table_name.c_str(),
1278 static_cast<int>(table_name.size()),
1279 SQLITE_STATIC));
1280
1281 const bool exists = SQLITE3_CALL(sqlite3_step(sql_stmt)) == SQLITE_ROW;
1282
1283 SQLITE3_CALL(sqlite3_finalize(sql_stmt));
1284
1285 return exists;
1286 }
1287
ExistsColumn(const std::string & table_name,const std::string & column_name) const1288 bool Database::ExistsColumn(const std::string& table_name,
1289 const std::string& column_name) const {
1290 const std::string sql =
1291 StringPrintf("PRAGMA table_info(%s);", table_name.c_str());
1292
1293 sqlite3_stmt* sql_stmt;
1294 SQLITE3_CALL(sqlite3_prepare_v2(database_, sql.c_str(), -1, &sql_stmt, 0));
1295
1296 bool exists_column = false;
1297 while (SQLITE3_CALL(sqlite3_step(sql_stmt)) == SQLITE_ROW) {
1298 const std::string result =
1299 reinterpret_cast<const char*>(sqlite3_column_text(sql_stmt, 1));
1300 if (column_name == result) {
1301 exists_column = true;
1302 break;
1303 }
1304 }
1305
1306 SQLITE3_CALL(sqlite3_finalize(sql_stmt));
1307
1308 return exists_column;
1309 }
1310
ExistsRowId(sqlite3_stmt * sql_stmt,const sqlite3_int64 row_id) const1311 bool Database::ExistsRowId(sqlite3_stmt* sql_stmt,
1312 const sqlite3_int64 row_id) const {
1313 SQLITE3_CALL(
1314 sqlite3_bind_int64(sql_stmt, 1, static_cast<sqlite3_int64>(row_id)));
1315
1316 const bool exists = SQLITE3_CALL(sqlite3_step(sql_stmt)) == SQLITE_ROW;
1317
1318 SQLITE3_CALL(sqlite3_reset(sql_stmt));
1319
1320 return exists;
1321 }
1322
ExistsRowString(sqlite3_stmt * sql_stmt,const std::string & row_entry) const1323 bool Database::ExistsRowString(sqlite3_stmt* sql_stmt,
1324 const std::string& row_entry) const {
1325 SQLITE3_CALL(sqlite3_bind_text(sql_stmt, 1, row_entry.c_str(),
1326 static_cast<int>(row_entry.size()),
1327 SQLITE_STATIC));
1328
1329 const bool exists = SQLITE3_CALL(sqlite3_step(sql_stmt)) == SQLITE_ROW;
1330
1331 SQLITE3_CALL(sqlite3_reset(sql_stmt));
1332
1333 return exists;
1334 }
1335
CountRows(const std::string & table) const1336 size_t Database::CountRows(const std::string& table) const {
1337 const std::string sql =
1338 StringPrintf("SELECT COUNT(*) FROM %s;", table.c_str());
1339
1340 sqlite3_stmt* sql_stmt;
1341 SQLITE3_CALL(sqlite3_prepare_v2(database_, sql.c_str(), -1, &sql_stmt, 0));
1342
1343 size_t count = 0;
1344 const int rc = SQLITE3_CALL(sqlite3_step(sql_stmt));
1345 if (rc == SQLITE_ROW) {
1346 count = static_cast<size_t>(sqlite3_column_int64(sql_stmt, 0));
1347 }
1348
1349 SQLITE3_CALL(sqlite3_finalize(sql_stmt));
1350
1351 return count;
1352 }
1353
CountRowsForEntry(sqlite3_stmt * sql_stmt,const sqlite3_int64 row_id) const1354 size_t Database::CountRowsForEntry(sqlite3_stmt* sql_stmt,
1355 const sqlite3_int64 row_id) const {
1356 SQLITE3_CALL(sqlite3_bind_int64(sql_stmt, 1, row_id));
1357
1358 size_t count = 0;
1359 const int rc = SQLITE3_CALL(sqlite3_step(sql_stmt));
1360 if (rc == SQLITE_ROW) {
1361 count = static_cast<size_t>(sqlite3_column_int64(sql_stmt, 0));
1362 }
1363
1364 SQLITE3_CALL(sqlite3_reset(sql_stmt));
1365
1366 return count;
1367 }
1368
SumColumn(const std::string & column,const std::string & table) const1369 size_t Database::SumColumn(const std::string& column,
1370 const std::string& table) const {
1371 const std::string sql =
1372 StringPrintf("SELECT SUM(%s) FROM %s;", column.c_str(), table.c_str());
1373
1374 sqlite3_stmt* sql_stmt;
1375 SQLITE3_CALL(sqlite3_prepare_v2(database_, sql.c_str(), -1, &sql_stmt, 0));
1376
1377 size_t sum = 0;
1378 const int rc = SQLITE3_CALL(sqlite3_step(sql_stmt));
1379 if (rc == SQLITE_ROW) {
1380 sum = static_cast<size_t>(sqlite3_column_int64(sql_stmt, 0));
1381 }
1382
1383 SQLITE3_CALL(sqlite3_finalize(sql_stmt));
1384
1385 return sum;
1386 }
1387
MaxColumn(const std::string & column,const std::string & table) const1388 size_t Database::MaxColumn(const std::string& column,
1389 const std::string& table) const {
1390 const std::string sql =
1391 StringPrintf("SELECT MAX(%s) FROM %s;", column.c_str(), table.c_str());
1392
1393 sqlite3_stmt* sql_stmt;
1394 SQLITE3_CALL(sqlite3_prepare_v2(database_, sql.c_str(), -1, &sql_stmt, 0));
1395
1396 size_t max = 0;
1397 const int rc = SQLITE3_CALL(sqlite3_step(sql_stmt));
1398 if (rc == SQLITE_ROW) {
1399 max = static_cast<size_t>(sqlite3_column_int64(sql_stmt, 0));
1400 }
1401
1402 SQLITE3_CALL(sqlite3_finalize(sql_stmt));
1403
1404 return max;
1405 }
1406
DatabaseTransaction(Database * database)1407 DatabaseTransaction::DatabaseTransaction(Database* database)
1408 : database_(database), database_lock_(database->transaction_mutex_) {
1409 CHECK_NOTNULL(database_);
1410 database_->BeginTransaction();
1411 }
1412
~DatabaseTransaction()1413 DatabaseTransaction::~DatabaseTransaction() { database_->EndTransaction(); }
1414
1415 } // namespace colmap
1416