void SQLI_cache_purge(struct db_cache *queue[], int index, struct insert_data *idata) { struct db_cache *LastElemCommitted = NULL; time_t start; int j, stop, ret; for (j = 0, stop = 0; (!stop) && preprocess_funcs[j]; j++) stop = preprocess_funcs[j](queue, &index, j); if (config.what_to_count & COUNT_CLASS) sql_invalidate_shadow_entries(queue, &index); idata->ten = index; if (config.debug) { Log(LOG_DEBUG, "( %s/%s ) *** Purging cache - START ***\n", config.name, config.type); start = time(NULL); } /* We check for variable substitution in SQL table */ if (idata->dyn_table) { char tmpbuf[LONGLONGSRVBUFLEN]; time_t stamp = idata->new_basetime ? idata->new_basetime : idata->basetime; strftime_same(insert_clause, LONGSRVBUFLEN, tmpbuf, &stamp); strftime_same(update_clause, LONGSRVBUFLEN, tmpbuf, &stamp); strftime_same(lock_clause, LONGSRVBUFLEN, tmpbuf, &stamp); if (config.sql_table_schema) sql_create_table(bed.p, &stamp); } // strncat(update_clause, set_clause, SPACELEFT(update_clause)); (*sqlfunc_cbr.lock)(bed.p); for (idata->current_queue_elem = 0; idata->current_queue_elem < index; idata->current_queue_elem++) { if (queue[idata->current_queue_elem]->valid) sql_query(&bed, queue[idata->current_queue_elem], idata); if (queue[idata->current_queue_elem]->valid == SQL_CACHE_COMMITTED) LastElemCommitted = queue[idata->current_queue_elem]; } /* multi-value INSERT query: wrap-up */ if (idata->mv.buffer_elem_num) { idata->mv.last_queue_elem = TRUE; sql_query(&bed, LastElemCommitted, idata); } /* rewinding stuff */ (*sqlfunc_cbr.unlock)(&bed); if (b.fail) Log(LOG_ALERT, "ALERT ( %s/%s ): recovery for SQLite3 daemon failed.\n", config.name, config.type); if (config.debug) { idata->elap_time = time(NULL)-start; Log(LOG_DEBUG, "( %s/%s ) *** Purging cache - END (QN: %u, ET: %u) ***\n", config.name, config.type, idata->qn, idata->elap_time); } if (config.sql_trigger_exec) { if (!config.debug) idata->elap_time = time(NULL)-start; SQL_SetENV_child(idata); } }
///创建数据表 int ZCE_General_Config_Table::create_table(unsigned int table_id) { //建表和建立索引 sql_create_table(table_id); int ret = 0; ret = sqlite_handler_->execute(sql_string_); if (ret != 0) { return ret; } return 0; }
static const char *create_table(struct subdbinfo *info, const char *suffix1, const char *suffix2, const char *definition) { make_name(info,suffix1,suffix2,1); if (sql_table_exists(info,name.s) > 0) return 0; stralloc_copys(&query,"CREATE TABLE "); stralloc_cats(&query,name.s); stralloc_cats(&query," ("); stralloc_cats(&query,definition); stralloc_cats(&query,")"); stralloc_0(&query); return sql_create_table(info,query.s); }
void PG_cache_purge(struct db_cache *queue[], int index, struct insert_data *idata) { PGresult *ret; struct logfile lf; struct db_cache **reprocess_queries_queue, **bulk_reprocess_queries_queue; char orig_insert_clause[LONGSRVBUFLEN], orig_update_clause[LONGSRVBUFLEN], orig_lock_clause[LONGSRVBUFLEN]; char orig_copy_clause[LONGSRVBUFLEN], tmpbuf[LONGLONGSRVBUFLEN], tmptable[SRVBUFLEN]; time_t start; int j, r, reprocess = 0, stop, go_to_pending, reprocess_idx, bulk_reprocess_idx, saved_index = index; struct primitives_ptrs prim_ptrs; struct pkt_data dummy_data; pid_t writer_pid = getpid(); if (!index) { Log(LOG_INFO, "INFO ( %s/%s ): *** Purging cache - START (PID: %u) ***\n", config.name, config.type, writer_pid); Log(LOG_INFO, "INFO ( %s/%s ): *** Purging cache - END (PID: %u, QN: 0/0, ET: 0) ***\n", config.name, config.type, writer_pid); return; } bed.lf = &lf; memset(&lf, 0, sizeof(struct logfile)); memset(&prim_ptrs, 0, sizeof(prim_ptrs)); memset(&dummy_data, 0, sizeof(dummy_data)); reprocess_queries_queue = (struct db_cache **) malloc(qq_size*sizeof(struct db_cache *)); bulk_reprocess_queries_queue = (struct db_cache **) malloc(qq_size*sizeof(struct db_cache *)); if (!reprocess_queries_queue || !bulk_reprocess_queries_queue) { Log(LOG_ERR, "ERROR ( %s/%s ): malloc() failed (reprocess_queries_queue). Exiting ..\n", config.name, config.type); exit_plugin(1); } for (j = 0, stop = 0; (!stop) && sql_preprocess_funcs[j]; j++) stop = sql_preprocess_funcs[j](queue, &index, j); if (config.what_to_count & COUNT_CLASS) sql_invalidate_shadow_entries(queue, &index); idata->ten = index; Log(LOG_INFO, "INFO ( %s/%s ): *** Purging cache - START (PID: %u) ***\n", config.name, config.type, writer_pid); start = time(NULL); /* re-using pending queries queue stuff from parent and saving clauses */ memcpy(pending_queries_queue, queue, index*sizeof(struct db_cache *)); pqq_ptr = index; strlcpy(orig_copy_clause, copy_clause, LONGSRVBUFLEN); strlcpy(orig_insert_clause, insert_clause, LONGSRVBUFLEN); strlcpy(orig_update_clause, update_clause, LONGSRVBUFLEN); strlcpy(orig_lock_clause, lock_clause, LONGSRVBUFLEN); start: memcpy(queue, pending_queries_queue, pqq_ptr*sizeof(struct db_cache *)); memset(pending_queries_queue, 0, pqq_ptr*sizeof(struct db_cache *)); index = pqq_ptr; pqq_ptr = 0; /* We check for variable substitution in SQL table */ if (idata->dyn_table) { time_t stamp = 0; memset(tmpbuf, 0, LONGLONGSRVBUFLEN); stamp = queue[0]->basetime; prim_ptrs.data = &dummy_data; primptrs_set_all_from_db_cache(&prim_ptrs, queue[0]); strlcpy(idata->dyn_table_name, config.sql_table, SRVBUFLEN); strlcpy(insert_clause, orig_insert_clause, LONGSRVBUFLEN); strlcpy(update_clause, orig_update_clause, LONGSRVBUFLEN); strlcpy(lock_clause, orig_lock_clause, LONGSRVBUFLEN); handle_dynname_internal_strings_same(tmpbuf, LONGSRVBUFLEN, copy_clause, &prim_ptrs); handle_dynname_internal_strings_same(tmpbuf, LONGSRVBUFLEN, insert_clause, &prim_ptrs); handle_dynname_internal_strings_same(tmpbuf, LONGSRVBUFLEN, update_clause, &prim_ptrs); handle_dynname_internal_strings_same(tmpbuf, LONGSRVBUFLEN, lock_clause, &prim_ptrs); handle_dynname_internal_strings_same(tmpbuf, LONGSRVBUFLEN, idata->dyn_table_name, &prim_ptrs); strftime_same(copy_clause, LONGSRVBUFLEN, tmpbuf, &stamp); strftime_same(insert_clause, LONGSRVBUFLEN, tmpbuf, &stamp); strftime_same(update_clause, LONGSRVBUFLEN, tmpbuf, &stamp); strftime_same(lock_clause, LONGSRVBUFLEN, tmpbuf, &stamp); strftime_same(idata->dyn_table_name, LONGSRVBUFLEN, tmpbuf, &stamp); if (config.sql_table_schema) sql_create_table(bed.p, &stamp, &prim_ptrs); } /* beginning DB transaction */ (*sqlfunc_cbr.lock)(bed.p); /* for each element of the queue to be processed we execute sql_query(); the function returns a non-zero value if DB has failed; then we use reprocess_queries_queue and bulk_reprocess_queries_queue to handle reprocessing of specific elements or bulk queue (of elements not being held in a pending_queries_queue) due to final COMMIT failure */ memset(reprocess_queries_queue, 0, qq_size*sizeof(struct db_cache *)); memset(bulk_reprocess_queries_queue, 0, qq_size*sizeof(struct db_cache *)); reprocess_idx = 0; bulk_reprocess_idx = 0; for (j = 0; j < index; j++) { go_to_pending = FALSE; if (idata->dyn_table) { time_t stamp = 0; memset(tmpbuf, 0, LONGLONGSRVBUFLEN); // XXX: pedantic? stamp = queue[idata->current_queue_elem]->basetime; strlcpy(tmptable, config.sql_table, SRVBUFLEN); prim_ptrs.data = &dummy_data; primptrs_set_all_from_db_cache(&prim_ptrs, queue[idata->current_queue_elem]); handle_dynname_internal_strings_same(tmpbuf, LONGSRVBUFLEN, tmptable, &prim_ptrs); strftime_same(tmptable, LONGSRVBUFLEN, tmpbuf, &stamp); if (strncmp(idata->dyn_table_name, tmptable, SRVBUFLEN)) { pending_queries_queue[pqq_ptr] = queue[idata->current_queue_elem]; pqq_ptr++; go_to_pending = TRUE; } } if (!go_to_pending) { if (queue[j]->valid) { r = sql_query(&bed, queue[j], idata); /* note down all elements in case of a reprocess due to COMMIT failure */ bulk_reprocess_queries_queue[bulk_reprocess_idx] = queue[j]; bulk_reprocess_idx++; } else r = FALSE; /* not valid elements are marked as not to be reprocessed */ if (r) { reprocess_queries_queue[reprocess_idx] = queue[j]; reprocess_idx++; if (!reprocess) sql_db_fail(&p); reprocess = REPROCESS_SPECIFIC; } } } /* Finalizing DB transaction */ if (!p.fail) { if (config.sql_use_copy) { if (PQputCopyEnd(p.desc, NULL) < 0) Log(LOG_ERR, "ERROR ( %s/%s ): COPY failed!\n\n", config.name, config.type); } ret = PQexec(p.desc, "COMMIT"); if (PQresultStatus(ret) != PGRES_COMMAND_OK) { if (!reprocess) sql_db_fail(&p); reprocess = REPROCESS_BULK; } PQclear(ret); } /* don't reprocess free (SQL_CACHE_FREE) and already recovered (SQL_CACHE_ERROR) elements */ if (p.fail) { if (reprocess == REPROCESS_SPECIFIC) { for (j = 0; j < reprocess_idx; j++) { if (reprocess_queries_queue[j]->valid == SQL_CACHE_COMMITTED) sql_query(&bed, reprocess_queries_queue[j], idata); } } else if (reprocess == REPROCESS_BULK) { for (j = 0; j < bulk_reprocess_idx; j++) { if (bulk_reprocess_queries_queue[j]->valid == SQL_CACHE_COMMITTED) sql_query(&bed, bulk_reprocess_queries_queue[j], idata); } } } if (b.connected) { if (config.sql_use_copy) { if (PQputCopyEnd(b.desc, NULL) < 0) Log(LOG_ERR, "ERROR ( %s/%s ): COPY failed!\n\n", config.name, config.type); } ret = PQexec(b.desc, "COMMIT"); if (PQresultStatus(ret) != PGRES_COMMAND_OK) sql_db_fail(&b); PQclear(ret); } /* rewinding stuff */ if (lf.file) PG_file_close(&lf); if (lf.fail || b.fail) Log(LOG_ALERT, "ALERT ( %s/%s ): recovery for PgSQL operation failed.\n", config.name, config.type); /* If we have pending queries then start again */ if (pqq_ptr) goto start; idata->elap_time = time(NULL)-start; Log(LOG_INFO, "INFO ( %s/%s ): *** Purging cache - END (PID: %u, QN: %u/%u, ET: %u) ***\n", config.name, config.type, writer_pid, idata->qn, saved_index, idata->elap_time); if (config.sql_trigger_exec) { if (!config.debug) idata->elap_time = time(NULL)-start; SQL_SetENV_child(idata); } }
// Functions void MainWindow::create_database() { QString app_path = QApplication::applicationDirPath(); QString dbase_path = app_path + "/base.bz"; { QSqlDatabase base = QSqlDatabase::addDatabase("QSQLITE", "create-new-tables"); base.setDatabaseName(dbase_path); base.open(); if(base.isOpen() != true){ QMessageBox msgbox; msgbox.setText("There was a problem with the database"); msgbox.setInformativeText("Due to unknown reason there was a problem with opening the database.\nThe problem accured during initial opening of the database."); msgbox.exec(); } else { // the database is open QSqlQuery sql_create_table(base); // create table for campaign sources sql_create_table.prepare( "CREATE TABLE IF NOT EXISTS sources (" "id INTEGER PRIMARY KEY, " "user TEXT, " "profile TEXT, " "campaign TEXT, " "source_name TEXT)" ); sql_create_table.exec(); sql_create_table.clear(); // create table for campaign medium sql_create_table.prepare( "CREATE TABLE IF NOT EXISTS medium (" "id INTEGER PRIMARY KEY, " "user TEXT, " "profile TEXT, " "campaign TEXT, " "medium_name TEXT)" ); sql_create_table.exec(); sql_create_table.clear(); // create table for page profile sql_create_table.prepare( "CREATE TABLE IF NOT EXISTS profile (" "id INTEGER PRIMARY KEY, " "user TEXT, " "profile_name TEXT, " "site_name TEXT, " "site_url TEXT)" ); sql_create_table.exec(); sql_create_table.clear(); // create table for page profile sql_create_table.prepare( "CREATE TABLE IF NOT EXISTS campaign (" "id INTEGER PRIMARY KEY, " "user TEXT, " "profile TEXT, " "campaign_name TEXT, " "campaign_description TEXT)" ); sql_create_table.exec(); sql_create_table.clear(); // create table for page profile sql_create_table.prepare( "CREATE TABLE IF NOT EXISTS data (" "id INTEGER PRIMARY KEY, " "user TEXT, " "profile TEXT, " "campaign TEXT, " "campaign_name TEXT, " "link_name TEXT, " "page_url TEXT, " "base_url TEXT, " "source TEXT, " "medium TEXT, " "content TEXT, " "preffix TEXT, " "comp_short TEXT, " "human_short TEXT, " "new_url TEXT, " "short_url TEXT)" ); sql_create_table.exec(); sql_create_table.clear(); } base.close(); } QSqlDatabase::removeDatabase("create-new-tables"); }
void MY_cache_purge(struct db_cache *queue[], int index, struct insert_data *idata) { struct db_cache *LastElemCommitted = NULL; struct logfile lf; time_t start; int j, stop, ret, go_to_pending, saved_index = index; char orig_insert_clause[LONGSRVBUFLEN], orig_update_clause[LONGSRVBUFLEN], orig_lock_clause[LONGSRVBUFLEN]; char tmpbuf[LONGLONGSRVBUFLEN], tmptable[SRVBUFLEN]; struct primitives_ptrs prim_ptrs; struct pkt_data dummy_data; pid_t writer_pid = getpid(); if (!index) { Log(LOG_INFO, "INFO ( %s/%s ): *** Purging cache - START (PID: %u) ***\n", config.name, config.type, writer_pid); Log(LOG_INFO, "INFO ( %s/%s ): *** Purging cache - END (PID: %u, QN: 0/0, ET: 0) ***\n", config.name, config.type, writer_pid); return; } bed.lf = &lf; memset(&lf, 0, sizeof(struct logfile)); memset(&prim_ptrs, 0, sizeof(prim_ptrs)); memset(&dummy_data, 0, sizeof(dummy_data)); for (j = 0, stop = 0; (!stop) && sql_preprocess_funcs[j]; j++) stop = sql_preprocess_funcs[j](queue, &index, j); if (config.what_to_count & COUNT_CLASS) sql_invalidate_shadow_entries(queue, &index); idata->ten = index; Log(LOG_INFO, "INFO ( %s/%s ): *** Purging cache - START (PID: %u) ***\n", config.name, config.type, writer_pid); start = time(NULL); /* re-using pending queries queue stuff from parent and saving clauses */ memcpy(pending_queries_queue, queue, index*sizeof(struct db_cache *)); pqq_ptr = index; strlcpy(orig_insert_clause, insert_clause, LONGSRVBUFLEN); strlcpy(orig_update_clause, update_clause, LONGSRVBUFLEN); strlcpy(orig_lock_clause, lock_clause, LONGSRVBUFLEN); start: memset(&idata->mv, 0, sizeof(struct multi_values)); memcpy(queue, pending_queries_queue, pqq_ptr*sizeof(struct db_cache *)); memset(pending_queries_queue, 0, pqq_ptr*sizeof(struct db_cache *)); index = pqq_ptr; pqq_ptr = 0; /* We check for variable substitution in SQL table */ if (idata->dyn_table) { time_t stamp = 0; memset(tmpbuf, 0, LONGLONGSRVBUFLEN); stamp = queue[0]->basetime; strlcpy(idata->dyn_table_name, config.sql_table, SRVBUFLEN); strlcpy(insert_clause, orig_insert_clause, LONGSRVBUFLEN); strlcpy(update_clause, orig_update_clause, LONGSRVBUFLEN); strlcpy(lock_clause, orig_lock_clause, LONGSRVBUFLEN); prim_ptrs.data = &dummy_data; primptrs_set_all_from_db_cache(&prim_ptrs, queue[0]); handle_dynname_internal_strings_same(tmpbuf, LONGSRVBUFLEN, insert_clause, &prim_ptrs); handle_dynname_internal_strings_same(tmpbuf, LONGSRVBUFLEN, update_clause, &prim_ptrs); handle_dynname_internal_strings_same(tmpbuf, LONGSRVBUFLEN, lock_clause, &prim_ptrs); handle_dynname_internal_strings_same(tmpbuf, LONGSRVBUFLEN, idata->dyn_table_name, &prim_ptrs); strftime_same(insert_clause, LONGSRVBUFLEN, tmpbuf, &stamp); strftime_same(update_clause, LONGSRVBUFLEN, tmpbuf, &stamp); strftime_same(lock_clause, LONGSRVBUFLEN, tmpbuf, &stamp); strftime_same(idata->dyn_table_name, LONGSRVBUFLEN, tmpbuf, &stamp); if (config.sql_table_schema) sql_create_table(bed.p, &stamp, &prim_ptrs); } if (idata->locks == PM_LOCK_EXCLUSIVE) (*sqlfunc_cbr.lock)(bed.p); for (idata->current_queue_elem = 0; idata->current_queue_elem < index; idata->current_queue_elem++) { go_to_pending = FALSE; if (idata->dyn_table) { time_t stamp = 0; memset(tmpbuf, 0, LONGLONGSRVBUFLEN); // XXX: pedantic? stamp = queue[idata->current_queue_elem]->basetime; strlcpy(tmptable, config.sql_table, SRVBUFLEN); prim_ptrs.data = &dummy_data; primptrs_set_all_from_db_cache(&prim_ptrs, queue[idata->current_queue_elem]); handle_dynname_internal_strings_same(tmpbuf, LONGSRVBUFLEN, tmptable, &prim_ptrs); strftime_same(tmptable, LONGSRVBUFLEN, tmpbuf, &stamp); if (strncmp(idata->dyn_table_name, tmptable, SRVBUFLEN)) { pending_queries_queue[pqq_ptr] = queue[idata->current_queue_elem]; pqq_ptr++; go_to_pending = TRUE; } } if (!go_to_pending) { if (queue[idata->current_queue_elem]->valid) sql_query(&bed, queue[idata->current_queue_elem], idata); if (queue[idata->current_queue_elem]->valid == SQL_CACHE_COMMITTED) LastElemCommitted = queue[idata->current_queue_elem]; } } /* multi-value INSERT query: wrap-up */ if (idata->mv.buffer_elem_num) { idata->mv.last_queue_elem = TRUE; sql_query(&bed, LastElemCommitted, idata); idata->qn--; /* increased by sql_query() one time too much */ } /* rewinding stuff */ if (idata->locks == PM_LOCK_EXCLUSIVE) (*sqlfunc_cbr.unlock)(&bed); if ((lf.fail) || (b.fail)) Log(LOG_ALERT, "ALERT ( %s/%s ): recovery for MySQL daemon failed.\n", config.name, config.type); /* If we have pending queries then start again */ if (pqq_ptr) goto start; idata->elap_time = time(NULL)-start; Log(LOG_INFO, "INFO ( %s/%s ): *** Purging cache - END (PID: %u, QN: %u/%u, ET: %u) ***\n", config.name, config.type, writer_pid, idata->qn, saved_index, idata->elap_time); if (config.sql_trigger_exec) { if (!config.debug) idata->elap_time = time(NULL)-start; SQL_SetENV_child(idata); } }