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:
- Create a thread, which creates a second temp table in background
- Block access for the reading clients
- DROP first temp table
- 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