2.6. Indexes

Indexes allow fast finding and sorting at the price of slower data manipulation, and increased memory usage. For every index, the compiler will generate code that automatically updates an std::map or an std::multimap.

2.6.1. Defining Indexes

Indexes are defined in the .joedbc configuration file. The syntax is:

create[_unique]_index <index_name> <table> <field>[,<field>...]

Note that there must be no space around the comma.

For example, the joedb tutorial has these index definitions:

namespace tutorial
create_unique_index city_by_name city name
create_index person_by_name person last_name,first_name

So, two cities can’t have the same name, but two persons can. An update that would create a duplicate city name will throw an exception.

2.6.2. Using Indexes

The most universal way to use an index is to directly use the const reference to the std::map or std::multimap returned by the get_index_of_<index_name> function of the database. The compiler also generates a convenient find_<index_name> function for finding rows.

Note: a new entry in the index is created at the first update of a field of the index. Uninitialized rows are not entered into the index.

Here is an example with the tutorial database:

#include "tutorial.h"

#include <iostream>

/////////////////////////////////////////////////////////////////////////////
int main()
/////////////////////////////////////////////////////////////////////////////
{
 joedb::Memory_File file;
 tutorial::Generic_File_Database db(file);

 //
 // Insert some cities
 //
 db.new_city("Paris");
 db.new_city("Tokyo");
 db.new_city("Chicago");

 try
 {
  std::cout << "Trying to insert another Paris:\n";
  db.new_city("Paris");
 }
 catch(const std::runtime_error &e)
 {
  std::cout << "Exception: " << e.what() << '\n';
 }

 //
 // Finding a city by name
 //
 const auto Paris = db.find_city_by_name("Paris");
 const auto Tokyo = db.find_city_by_name("Tokyo");
 if (db.find_city_by_name("Monte Carlo").is_null())
  std::cout << "\nMonte Carlo is not in the database\n";

 //
 // Inserting persons
 //
 db.new_person("John", "Smith", Paris);
 db.new_person("John", "Smith", Tokyo);
 db.new_person("Hiroshi", "Yamada", Tokyo);
 db.new_person("René", "Dubois", Tokyo);
 db.new_person("Hélène", "Dubois", db.null_city());
 db.new_person("Daniel", "Dubois", db.null_city());
 db.new_person("Laurent", "Dubois", db.null_city());
 db.new_person("Albert", "Camus", Paris);

 //
 // Finding persons with the index
 //
 std::cout << "\nFinding all the John Smiths:\n";
 for (const auto person: db.find_person_by_name("Smith", "John"))
 {
  std::cout << db.get_first_name(person) << ' ';
  std::cout << db.get_last_name(person) << ", ";
  std::cout << db.get_name(db.get_home(person)) << '\n';
 }

 //
 // Using the index to sort persons
 //
 std::cout << "\nSorted list of persons:\n";
 for (auto key_and_value: db.get_index_of_person_by_name())
 {
  auto person = key_and_value.second;
  std::cout << db.get_last_name(person) << ", ";
  std::cout << db.get_first_name(person) << '\n';
 }

 db.checkpoint();

 return 0;
}

And here is its output:

Trying to insert another Paris:
Exception: city_by_name unique index failure: ("Paris") at id = 4 was already at id = 1

Monte Carlo is not in the database

Finding all the John Smiths:
John Smith, Paris
John Smith, Tokyo

Sorted list of persons:
Camus, Albert
Dubois, Daniel
Dubois, Hélène
Dubois, Laurent
Dubois, René
Smith, John
Smith, John
Yamada, Hiroshi