Creating temporary table while still accessing other tables

Question

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 " \<br>
      "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.

Answer 1

As long as all threads are part of the same program, you have control over all database connections. So you can write the data in a completely separate database, and ATTACH is quickly later.

Answer 2

Thanks a lot for your answer. I changed my code and found out that I need a new database connection (sqlite3_open) in the working thread not to block the other thread. Also creating a TEMPORARY TABLE in the attached "temporary database" was not possible, because creating a temporary table doesn't allow a qualifier (like: x.temp_table), so I had to create a real table which consumes a lot of memory in our flash file system (which is not allowed).
But wait! I've got an idea
(2 hours later)
I did it! I open an empty database and attach alll relevant databases to the connection. I create a temporary table "in" the empty database which consumes no memory in flash, because it's temporary.
When I have to create a new table, I open another empty database and attach the relevant databases to the connection. When the operation is finished, I switch the old and the new connection. Code as follows:

    #include <iostream>
    #include "dbAccess.h"
    #include <cstdio>
    #include <string>
    #include <cstring>

    using namespace std;

    bool inProgress = true;

    DWORD WINAPI createTempTableThread(void *param);

    int callback(void *NotUsed, int argc, char **argv, char **azColName)
    {
        cout << "*";
        return 0;
    }

    int main(void)
    {
        sqlite3* db = NULL;
        HANDLE hThreadHandle = NULL;
        CdbAccess *dba = new CdbAccess();
        int i = 0;
        db = dba->dbaConnect();
        dba->dbaSetDatabase(db);

        cout << "INFO: Creating initial temporary table. " << endl;
        sqlite3_exec(dba->dbaGetDatabase(), "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 intern.name_table " \
                      "LEFT JOIN intern.phone_table ON (intern.name_table.id=intern.phone_table.id) " \
                      "LEFT JOIN intern.email_table ON (intern.name_table.id=email_intern.table.id);", NULL, NULL, NULL);
        cout << "INFO: Creating initial temporary table finished. " << endl;

        while(1)
        {
            hThreadHandle = CreateThread(0, 0, createTempTableThread, dba, 0, 0);

            while(inProgress)
            {
            sqlite3_exec(dba->dbaGetDatabase(), "SELECT * FROM name_table WHERE id LIKE 1;", callback, NULL, NULL);
            }

            for(i = 0; i < 5; i++)
            {
                sqlite3_exec(dba->dbaGetDatabase(), "SELECT * FROM name_table WHERE id LIKE 2;", callback, NULL, NULL);
            }

            inProgress = true;

            CloseHandle(hThreadHandle);
        }


        dba->dbaDisconnect();

        return 0;
    }


CdbAccess::CdbAccess()
{
    hSemaphore = CreateSemaphore(NULL, 1, 1, 0);
}

CdbAccess::~CdbAccess()
{
}

sqlite3 *CdbAccess::dbaConnect()
{
    sqlite3 *db;
    static int num = 1;
    int err = SQLITE_OK;
    string attach = "ATTACH DATABASE \"";
    string internal = "cbInternal.db";

    if(num == 1)
    {
        cout << endl << "INFO: cbTemp1.db";
        err = sqlite3_open("cbTemp1.db", &db);
        num = 2;
    }
    else
    {
        cout << endl << "INFO: cbTemp2.db";
        err = sqlite3_open("cbTemp2.db", &db);
        num = 1;
    }

    if(err == SQLITE_OK)
    {
        cout << endl << "INFO: Temp database opened.";
        err = sqlite3_exec(db, "ATTACH DATABASE \"cbInternal.db\" AS intern;", NULL, NULL, NULL);
        if(err == SQLITE_OK)
        {
            cout << endl << "INFO: Internal database attached.";

            err = sqlite3_exec(db, "ATTACH DATABASE \"0123456789.db\" AS pb;", NULL, NULL, NULL);
            if(err == SQLITE_OK)
            {
                cout << endl << "INFO: Phone book attached.";
            }
            else
            {
                cout << endl << "ERROR: Attaching phone book: " << sqlite3_errmsg(db);
            }
        }
        else
        {
            cout << endl << "ERROR: Attaching internal database: " << sqlite3_errmsg(db);
        }
    }
    else
    {
        cout << endl << "ERROR: Opening database: " << sqlite3_errmsg(db);
    }

    return db;
}

int CdbAccess::dbaDisconnect(void)
{
    int err = SQLITE_OK;

    err = sqlite3_exec(db, "DETACH DATABASE pb;", NULL, NULL, NULL);
    if(err == SQLITE_OK)
    {
        cout << endl << "INFO: Phone book detached.";
        err = sqlite3_exec(db, "DETACH DATABASE intern;", NULL, NULL, NULL);
        if(err == SQLITE_OK)
        {
            cout << endl << "INFO: Internal database detached.";
            err = sqlite3_close(db);
            if(err == SQLITE_OK)
            {
                cout << endl << "INFO: Database connection closed.";
            }
            else
            {
                cout << endl << "ERROR: Could not close database: " << sqlite3_errmsg(db);
            }
        }
        else
        {
            cout << endl << "ERROR: Could not detach internal database: " << sqlite3_errmsg(db);
        }
    }
    else
    {
        cout << endl << "ERROR: Could not detach phone book: " << sqlite3_errmsg(db);
    }

    return err;
}

sqlite3* CdbAccess::dbaGetDatabase(void)
{
    return db;
}

void CdbAccess::dbaSetDatabase(sqlite3 * sqldb)
{
    db = sqldb;
}

int CdbAccess::dbaGetTempTableAccess(void)
{
    cout << endl << "INFO: Access requested.";
    WaitForSingleObject(hSemaphore, INFINITE);
    return 0;
}

int CdbAccess::dbaReleaseTempTableAccess(void)
{
    cout << endl << "INFO: Access released.";
    ReleaseSemaphore(hSemaphore, 1, NULL);
    return 0;
}


DWORD WINAPI createTempTableThread(void *param)
{
    int err = SQLITE_OK;
    CdbAccess *d = (CdbAccess *)param;
    sqlite3 *db;

    cout << endl << "INFO: createTempTable: IN";

    inProgress = true; // global variable for test porpose only

    db = d->dbaConnect();

    if(db != NULL)
    {
        cout << endl << "Thread: INFO: Creating temporary table. ";
        err = sqlite3_exec(db, "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 intern.name_table " \
                      "LEFT JOIN intern.phone_table ON (intern.name_table.id=intern.phone_table.id) " \
                      "LEFT JOIN intern.email_table ON (intern.name_table.id=intern.email_table.id);", NULL, NULL, NULL);
    }

    if(err != SQLITE_OK)
    {
        cout << endl << "Thread: ERROR: Creating temporary table: " << sqlite3_errmsg(db);
    }
    else
    {
        cout << endl << "Thread: INFO: Creating temporary table finished. ";
    }

    d->dbaSetDatabase(db);

    inProgress = false; // global variable for test porpose only

    cout << endl << "Thread: INFO: createTempTable: OUT";

    return 0;
}