MetaData SQL best practices

Overview

Each time you work with values stored in MetaData instances, an SQLite database is accessed in the background. Let's have a look at a few rules of thumb, which will ensure the good performance of your code.

Define columns at the construction time

While columns can be added to each MetaData at any time, this operation is rather costly. SQLite also does not support the addition of multiple columns at once. However, multiple columns can be defined at the time the table is created.

Noncompliant Code Example

auto md = MetaData();
std::vector<MDLabel> labels;
... // labels are filled
for (const auto &label : labels) md.addLabel(label);

Compliant Solution

std::vector<MDLabel> labels;
... // labels are filled
auto md = MetaData(labels);

Add / insert / read entire row / column at once

While columns and rows can be read/written one cell at the time, this would lead to unnecessary calls to SQLite.

Noncompliant Code Example

auto md = MetaData();
size_t id = md.addObject();
md.setValue(MDL_REF, getRef(id), id);
md.setValue(MDL_IMAGE, getImage(id), id);
...

Compliant Solution

auto md = MetaData();
auto row = MDRow();
row.setValue(MDL_REF, getRef(id), true);
row.setValue(MDL_IMAGE, getImage(id), true);
...
md.addRowOpt(row); // NOTE currently we have two methods - addRow() and addRowOpt(). Prefer addRowOpt, until the implementation is unified

Noncompliant Code Example

auto &md = getMetaData();
std::vector<float> rots;
FOR_ALL_OBJECTS_IN_METADATA(md) {
  float rot = 0.f;
  md.getValue(MDL_ANGLE_ROT, rot,__iter.objId);
  rots.emplace_back(rot);
  ...

Compliant Solution

auto &md = getMetaData();
std::vector<float> rots;
md.getColumnValuesOpt(MDL_ANGLE_ROT, h.rots); // NOTE currently we have two methods - getColumnValues() and getColumnValuesOpt(). Prefer getColumnValuesOpt, until the implementation is unified

Add multiple rows at once

If possible, fill the entire table at once.

Noncompliant Code Example

auto &md = getMetaData();
auto result = MetaData();
MDRow row;
FOR_ALL_OBJECTS_IN_METADATA(md)
  md.getRow(row, __iter.objId);
  fillRow(row, __iter.objId);
  result.addRowOpt(row);
}

Compliant Solution

auto &md = getMetaData();
std::vector<MDRow> rows(md.size());
FOR_ALL_OBJECTS_IN_METADATA(md)
  auto &r = rows.at(__iter.objIndex);
  md.getRow(r, __iter.objId);
  fillRow(r, __iter.objId);
}
if (0 != rows.size()) {
  const auto labels = rows.at(0).getLabels();
  MetaData result(&labels);
  result.addRows(rows);
}