samedi 20 juin 2015

SQLite C++: Creating temporary table while still accessing other tables


I've got 2 or more databases ATTACHed to one SQLite database connection. Each database consists of 3 tables. To have better access for searching/filtering, I create a huge temporary table over all tables and databases like this:

"CREATE TEMPORARY TABLE temp_table AS " \
"SELECT * FROM pb.name_table " \
"LEFT JOIN pb.phone_table ON (pb.name_table.id=pb.phone_table.id) " \
"LEFT JOIN pb.email_table ON (pb.name_table.id=pb.email_table.id) " \
"UNION SELECT * FROM name_table " \
"LEFT JOIN phone_table ON (name_table.id=phone_table.id) " \
"LEFT JOIN email_table ON (name_table.id=email_table.id);";

If something changes in a table, I have to recreate the temporary table.
With an increasing amount of data, creating a temporary table takes some time, but since I'm having continuous read access to the table, my idea was as follows:

  1. Create a thread, which creates a second temp table in background
  2. Block access for the reading clients
  3. DROP first temp table
  4. Rename the second temp table

The problem is now: Creating a temporary table is a write access to the database, which blocks automatically all reading threads also.

Has anyone a good idea how I can handle this? I need read access while recreating the temporary table.

Thanks!

Aucun commentaire:

Enregistrer un commentaire