Создание временной таблицы при доступе к другим таблицам
У меня есть 2 или более баз данных, подключенных к одному соединению с базой данных SQLite. Каждая база данных состоит из 3 таблиц. Чтобы иметь лучший доступ для поиска / фильтрации, я создаю огромную временную таблицу для всех таблиц и баз данных, например:
"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);";
Если что-то меняется в таблице, я должен воссоздать временную таблицу.
С увеличением объема данных создание временной таблицы занимает некоторое время, но, поскольку у меня есть постоянный доступ для чтения таблицы, моя идея заключалась в следующем:
- Создать поток, который создает вторую временную таблицу в фоновом режиме
- Блокировать доступ для чтения клиентов
- DROP первый временный стол
- Переименуйте вторую временную таблицу
Теперь проблема в том, что создание временной таблицы — это доступ на запись в базу данных, который автоматически блокирует также все потоки чтения.
У кого-нибудь есть хорошая идея, как я могу справиться с этим? Мне нужен доступ для чтения при воссоздании временной таблицы.
Решение
Пока все потоки являются частью одной и той же программы, вы можете контролировать все соединения с базой данных.
Таким образом, вы можете записать данные в совершенно отдельную базу данных, и ATTACH быстро позже.
Другие решения
Большое спасибо за ответ. Я изменил свой код и обнаружил, что мне нужно новое соединение с базой данных (sqlite3_open) в рабочем потоке, чтобы не блокировать другой поток. Также создание TEMPORARY TABLE во вложенной «временной базе данных» было невозможно, потому что создание временной таблицы не допускает квалификатор (например: x.temp_table), поэтому мне пришлось создать реальную таблицу, которая потребляет много памяти в наша флеш файловая система (что не разрешено).
Но ждать! у меня есть идея
(спустя 2 часа)
Я это сделал! Я открываю пустую базу данных и присоединяю все соответствующие базы данных к соединению. Я создаю временную таблицу «в» пустой базе данных, которая не использует память во флэш-памяти, потому что она временная.
Когда мне нужно создать новую таблицу, я открываю другую пустую базу данных и присоединяю соответствующие базы данных к соединению. Когда операция закончена, я переключаю старое и новое соединение. Код следующим образом:
#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;
}