Nested queries and Iterators

[oranja]

Hello again Michael, I've encountered another small problem with Quince that I would like to share the details of, at least to save some other people's time perhaps.

The issue is as such: Let's assume that in one SQLite database we have two tables: T1 and T2. Now let's assume that we want to query T1, and for each record returned we want to run some code that involves the execution of a query on T2 and processing its records. Iterators are used to actually receive the data from both queries. Unfortunately, the outcome is an infinite loop. For some reason, each internal query puts the outer iterator back at the begin()ning.

I should note that I found no explanation or hints for this behavior in the documentation. Specifically I went through Chapter 7. Queries and Chapter 11. Connection Management several times to make sure I did not miss anything.

The following code reproduces the problem, although it is not a realistic use case. In a more complex code and a very specific case, I decided to choose client-side nested queries over server-side joins, for a better separation of responsibilities (preferred design over engineering).

Makefile

CXX=g++
CXXFLAGS=-std=c++11
LDLIBS=-ldl -lquince -lquince-sqlite -lboost_chrono -lboost_date_time -lboost_filesystem -lboost_system -lboost_thread

all: quince_nested movies.db

%.db:
    touch $@

clean:
    rm -f quince_nested *.o *.db

.PHONY: all

quince_nested.cpp

#include <string>
#include <quince/quince.h>
#include <quince_sqlite/database.h>

using namespace quince;
using namespace std;

struct movie_t {
    serial movie_id;
    serial director_id;
};
QUINCE_MAP_CLASS(movie_t, (movie_id)(director_id))

struct director_t {
    serial id;
    string first_name;
    string last_name;
};
QUINCE_MAP_CLASS(director_t, (id)(first_name)(last_name))

serial make_serial(int index) {
    serial id;
    id.assign(index);
    return id;
}

struct schema_t {
    schema_t(const quince_sqlite::database &db)
        : directors(db, "directors"),
          movies(db, "movies")
    {
        directors.specify_key_from_ptkm(&director_t::id);
        movies.specify_key(movies->movie_id, movies->director_id);

        directors.specify_unique(directors->first_name, directors->last_name);

        movies.specify_foreign(movies->director_id, directors);

        directors.open();
        movies.open();
    }
    serial_table<director_t> directors;
    table<movie_t> movies;
};

int main() {
    const quince_sqlite::database db("movies.db");

    schema_t schema (db);

    // Insert test data
    const serial george = schema.directors.insert( {serial(), "George", "Lucas"} );
    const serial peter = schema.directors.insert( {serial(), "Peter", "Jackson"} );
    const serial martin = schema.directors.insert( {serial(), "Martin", "Scorsese"} );
    int movie_index = 1;
    schema.movies.insert({make_serial(movie_index++), george});
    schema.movies.insert({make_serial(movie_index++), peter});
    schema.movies.insert({make_serial(movie_index++), martin});

    // Two dead-simple queries.
    const query<director_t> all_directors =
        schema.directors;

    const query<movie_t> all_movies =
        schema.movies;

    // The problem is nesting one query in another (same database, but unrelated);
    for (const director_t director : all_directors) {
        cout << director.last_name << endl;
        for (const movie_t &movie : all_movies) {
            if (movie.director_id == director.id) {
                cout << movie.movie_id << endl;
            }
        }
    }

    return 0;
}

Then...

$ make
g++ -std=c++11  -L/usr/local/lib/quince  quince_nested.cpp  -ldl -lquince -lquince-sqlite -lboost_chrono -lboost_date_time -lboost_filesystem -lboost_system -lboost_thread -o quince_nested
touch movies.db

$ ./quince_nested
Lucas
1
Lucas
1
Lucas
1
<goes on forever>

[mshepanski]

Thanks for this. Unfortunately I won't be able to work on it until early January, so you might need to work around it temporarily (e.g. those joins that you mentioned).

[mshepanski]

Okay, I've put a fix on the dev branch. Please check that this does what you need, and if so I'll merge it to master.

[mshepanski]

I assume that no news is good news, so I have merged dev to master.

[oranja]

Sorry for not providing feedback earlier. I moved to another module in the project for a while, so I postponed it until I was done.

I pulled from master today, rebuilt and tested both the sample code and the project linked against the new binaries. The new version works great and the bug is indeed not present anymore as far as I can tell.

Thank you. :pray: