void db_add_tag_to_file(int tag_id, int file_id) {
char *query = NULL;
char query_outline[] = "INSERT INTO file_has_tag VALUES(, )";
int query_length = 0;
int rc = SQLITE_ERROR; /* return code of sqlite operation */
int written = 0; /* number of characters written */
sqlite3 *conn = NULL;
sqlite3_stmt *res = NULL;
DEBUG(ENTRY);
assert(tag_id >= 0);
assert(file_id > 0);
/* prepare query */
query_length = strlen(query_outline) + num_digits(tag_id) + num_digits(file_id);
query = malloc(query_length * sizeof(*query) + 1);
assert(query != NULL);
written = snprintf(query, query_length + 1, "INSERT INTO file_has_tag VALUES(%d, %d)", file_id, tag_id);
assert(written == query_length);
/* connect to database */
conn = db_connect();
assert(conn != NULL);
rc = db_execute_statement(conn, query, &res);
db_finalize_statement(conn, query, res);
db_disconnect(conn);
free_single_ptr((void **)&query);
DEBUG("Adding tag ID %d to file ID %d was %ssuccessful", tag_id, file_id, rc == SQLITE_DONE ? "" : "not ");
DEBUG(EXIT);
} /* db_add_tag_to_file */
void db_remove_file(int file_id) {
char *query = NULL;
char query_outline[] = "DELETE FROM files WHERE file_id = ";
int query_length = 0;
int rc = SQLITE_ERROR; /* return code of sqlite operation */
int written = 0; /* number of characters written */
sqlite3 *conn = NULL;
sqlite3_stmt *res = NULL;
DEBUG(ENTRY);
assert(file_id > 0);
DEBUG("Removing file ID %d", file_id);
/* prepare query */
query_length = strlen(query_outline) + num_digits(file_id);
query = malloc(query_length * sizeof(*query) + 1);
assert(query != NULL);
written = snprintf(query, query_length + 1, "DELETE FROM files WHERE file_id = %d", file_id);
assert(written == query_length);
/* connect to database */
conn = db_connect();
assert(conn != NULL);
rc = db_execute_statement(conn, query, &res);
db_finalize_statement(conn, query, res);
db_disconnect(conn);
free_single_ptr((void **)&query);
DEBUG("Removing file ID %d was %ssuccessful", file_id, rc == SQLITE_DONE ? "" : "not ");
DEBUG(EXIT);
} /* db_remove_file */
void db_delete_tag(int tag_id) {
char *query = NULL;
char query_outline[] = "DELETE FROM tags WHERE tag_id = \"\"";
int query_length = 0;
int rc = SQLITE_ERROR; /* return code of sqlite operation */
int written = 0; /* number of characters written */
sqlite3 *conn = NULL;
sqlite3_stmt *res = NULL;
DEBUG(ENTRY);
assert(tag_id > 0);
DEBUG("Deleting tag with tag ID %d", tag_id);
/* prepare query */
query_length = strlen(query_outline) + num_digits(tag_id);
query = malloc(query_length * sizeof(*query) + 1);
assert(query != NULL);
written = snprintf(query, query_length + 1, "DELETE FROM tags WHERE tag_id = \"%d\"", tag_id);
assert(written == query_length);
/* connect to database */
conn = db_connect();
assert(conn != NULL);
rc = db_execute_statement(conn, query, &res);
db_finalize_statement(conn, query, res);
db_disconnect(conn);
free_single_ptr((void **)&query);
DEBUG("File with ID %d was %sdeleted successfully.", tag_id, rc == 0 ? "" : "not ")
DEBUG(EXIT);
} /* db_delete_tag */
/**
* Insert the results of the specified query into the specified table. Only the results of the first column are entered into the table, and the values are assumed to be integers.
*
* @param conn A sqlite database handle.
* @param query An SQL statement, UTF-8 encoded.
*/
static void db_insert_query_results_into_hashtable(sqlite3 *conn, char *query, GHashTable *table) {
int rc = SQLITE_ERROR;
sqlite3_stmt *res = NULL;
unsigned long int_from_table = 0;
DEBUG(ENTRY);
assert(query != NULL);
assert(conn != NULL);
assert(table != NULL);
DEBUG("Inserting into table results from query: %s", query);
rc = db_execute_statement(conn, query, &res);
/* insert results into hashset */
while(rc == SQLITE_ROW) {
int_from_table = sqlite3_column_int(res, 0);
g_hash_table_insert(table, (gpointer)int_from_table, (gpointer)int_from_table);
rc = db_step_statement(conn, query, res);
if(int_from_table == 0)printf("%d\n", rc);
}
db_finalize_statement(conn, query, res);
DEBUG("Results entered into table from query: %s", query);
DEBUG(EXIT);
} /* db_insert_query_results_into_hashtable */
/*
* execute_sql_statement
*
* arguments:
* statement: statement to be executed
*
* returns/side-effects: int
*
* description:
*/
int
execute_sql_statement (FILE * fp, char *statement)
{
int db_error = NO_ERROR;
DB_SESSION *session;
DB_QUERY_RESULT *query_result;
int stmtid;
CUBRID_STMT_TYPE stmt_type;
session = db_open_buffer (statement);
if (session == NULL)
{
return db_error_code ();
}
stmtid = db_compile_statement (session);
if (stmtid < 0)
{
return db_error_code ();
}
stmt_type = db_get_statement_type (session, stmtid);
db_error = db_execute_statement (session, stmtid, &query_result);
if (db_error < 0)
{
return db_error_code ();
}
else if (stmt_type == CUBRID_STMT_UPDATE || stmt_type == CUBRID_STMT_DELETE)
{
fprintf (fp, "%d row%s %s\n", db_error, (db_error > 1 ? "s" : ""),
(stmt_type == CUBRID_STMT_SELECT ? "selected" : "affected"));
}
if (query_result != NULL)
{
if (stmt_type == CUBRID_STMT_SELECT || stmt_type == CUBRID_STMT_CALL)
{
my_db_csql_debug_print_result (fp, query_result, stmt_type);
}
fprintf (fp, "%d row%s %s\n", db_error, (db_error > 1 ? "s" : ""),
(stmt_type == CUBRID_STMT_SELECT ? "selected" : "affected"));
db_error = db_query_end (query_result);
if (db_error != NO_ERROR)
{
db_close_session (session);
return db_error;
}
}
db_close_session (session);
return NO_ERROR;
}
char *db_get_file_location(int file_id) {
char *file_location = NULL;
char *query = NULL;
char *tmp_file_directory = NULL; /* holds text from the query so it can be copied to a new memory location */
char *tmp_file_name = NULL; /* hold name of file until it can be copied to a new memory location */
char query_outline[] = "SELECT file_location, file_name FROM files WHERE file_id = ";
int file_location_length = 0; /* length of the file location to return */
int query_length = 0;
int written = 0; /* number of characters written */
sqlite3 *conn = NULL;
sqlite3_stmt *res = NULL;
DEBUG(ENTRY);
assert(file_id > 0);
DEBUG("Retrieving physical location for file with ID %d", file_id);
/* prepare query */
query_length = strlen(query_outline) + num_digits(file_id);
query = malloc(query_length * sizeof(*query) + 1);
written = snprintf(query, query_length + 1, "SELECT file_location, file_name FROM files WHERE file_id = %d", file_id);
assert(written == query_length);
/* connect to database */
conn = db_connect();
assert(conn != NULL);
db_execute_statement(conn, query, &res);
/* get file location and name */
tmp_file_directory = (char *)sqlite3_column_text(res, 0);
assert(tmp_file_directory != NULL);
tmp_file_name = (char *)sqlite3_column_text(res, 1);
assert(tmp_file_name != NULL);
/* build full file path from name and directory */
file_location_length = strlen(tmp_file_directory) + strlen(tmp_file_name) + 1;
file_location = malloc(file_location_length * sizeof(*file_location) + 1);
written = snprintf((char *)file_location, file_location_length + 1, "%s/%s", tmp_file_directory, tmp_file_name);
assert(written == file_location_length);
db_finalize_statement(conn, query, res);
db_disconnect(conn);
free_single_ptr((void *)&query);
DEBUG("File id %d corresponds to %s", file_id, file_location);
DEBUG(EXIT);
return file_location;
} /* db_get_file_location */
int db_tag_id_from_tag_name(char *tag_name) {
char *query = NULL;
int tag_id = -1;
char query_outline[] = "SELECT tag_id FROM tags WHERE tag_name = \"\"";
int query_length = 0;
int written = 0; /* number of characters written */
sqlite3 *conn = NULL;
sqlite3_stmt *res = NULL;
DEBUG(ENTRY);
assert(tag_name != NULL);
DEBUG("Retrieving tag name for tag ID %s", tag_name);
if(strcmp(tag_name, "/") == 0) {
tag_id = 0;
} else {
/* prepare query */
query_length = strlen(query_outline) + strlen(tag_name);
query = malloc(query_length * sizeof(*query) + 1);
assert(query != NULL);
written = snprintf(query, query_length + 1, "SELECT tag_id FROM tags WHERE tag_name = \"%s\"", tag_name);
assert(written == query_length);
/* connect to database */
conn = db_connect();
assert(conn != NULL);
db_execute_statement(conn, query, &res);
/* get name corresponding to tag_id */
tag_id = sqlite3_column_int(res, 0);
db_finalize_statement(conn, query, res);
db_disconnect(conn);
free_single_ptr((void **)&query);
}
DEBUG("Tag %s corresponds to tag ID %d", tag_name, tag_id);
DEBUG(EXIT);
return tag_id;
} /* db_tag_id_from_tag_name */
char *db_tag_name_from_tag_id(int tag_id) {
char *query = NULL;
char *tag_name = NULL;
char query_outline[] = "SELECT tag_name FROM tags WHERE tag_id = ";
int num_digits_in_id = 0;
int query_length = 0;
int query_outline_length = 0;
int written = 0; /* number of characters written */
sqlite3 *conn = NULL;
sqlite3_stmt *res = NULL;
DEBUG(ENTRY);
assert(tag_id > 0);
DEBUG("Retrieving tag name for tag ID %d", tag_id);
/* prepare query */
num_digits_in_id = num_digits(tag_id);
query_outline_length = strlen(query_outline);
query_length = query_outline_length + num_digits_in_id;
query = malloc(query_length * sizeof(*query) + 1);
assert(query != NULL);
written = snprintf(query, query_length + 1, "%s%d", query_outline, tag_id);
assert(written == query_length);
/* connect to database */
conn = db_connect();
assert(conn != NULL);
db_execute_statement(conn, query, &res);
/* get name corresponding to tag_id */
tag_name = strdup((char *)sqlite3_column_text(res, 0));
db_finalize_statement(conn, query, res);
db_disconnect(conn);
free_single_ptr((void **)&query);
DEBUG("Tag ID %d corresponds to tag %s", tag_id, tag_name);
DEBUG(EXIT);
return tag_name;
} /* db_tag_name_from_tag_id */
int db_count_from_query(char *query) {
char *count_query = NULL;
char select_count_outline[] = "SELECT COUNT(*) FROM ()";
int count = 0; /* number of rows returned from the count query */
int length = 0; /* length of the sqlite3 count query */
int written = 0; /* characters written by snprintf */
sqlite3 *conn = NULL;
sqlite3_stmt *res = NULL;
DEBUG(ENTRY);
assert(query != NULL);
DEBUG("Calculating number of results returned from query: %s", query);
/* calculate query length */
length = strlen(query) + strlen(select_count_outline);
/* build count query */
count_query = malloc(length * sizeof(*count_query) + 1);
assert(count_query != NULL);
written = snprintf(count_query, length + 1, "SELECT COUNT(*) FROM (%s)", query);
assert(written == length);
DEBUG("Complete query: %s", count_query);
conn = db_connect();
assert(conn != NULL);
/* compile prepared statement */
db_execute_statement(conn, count_query, &res);
/* get result of count */
count = sqlite3_column_int(res, 0);
db_finalize_statement(conn, count_query, res);
db_disconnect(conn);
free_single_ptr((void **)&count_query);
DEBUG("Query returns a count of %d", count);
DEBUG(EXIT);
return count;
} /* db_count_from_query */
void db_delete_empty_tags() {
int rc = SQLITE_ERROR; /* return code of sqlite operation */
sqlite3 *conn = NULL;
sqlite3_stmt *res = NULL;
char query[] = "DELETE FROM tags WHERE tag_name NOT IN (SELECT DISTINCT tag_name FROM all_tables)";
DEBUG(ENTRY);
DEBUG("Preparing to purge all empty tags from the database.");
/* connect to database */
conn = db_connect();
assert(conn != NULL);
rc = db_execute_statement(conn, query, &res);
db_finalize_statement(conn, query, res);
db_disconnect(conn);
DEBUG("Purging empty tags was %ssuccessful", rc == SQLITE_DONE ? "" : "not");
DEBUG(EXIT);
} /* db_delete_empty_tags */
/*
* ldr_exec_query_from_file - execute queries from file
* return: 0 if successful, non-zero otherwise
* file_name(in): file path
* file(in): FILE *
* start_line(in): start line
* commit_period(in): commit period
*/
static int
ldr_exec_query_from_file (const char *file_name, FILE * input_stream,
int *start_line, int commit_period)
{
DB_SESSION *session = NULL;
DB_QUERY_RESULT *res = NULL;
int error = 0;
int stmt_cnt, stmt_id = 0, stmt_type;
int executed_cnt = 0;
int parser_start_line_no;
int parser_end_line_no = 1;
int check_line_no = true;
if ((*start_line) > 1)
{
int line_count = *start_line - 1;
do
{
int c = fgetc (input_stream);
if (c == EOF)
{
print_log_msg (1, msgcat_message (MSGCAT_CATALOG_UTILS,
MSGCAT_UTIL_SET_LOADDB,
LOADDB_MSG_UNREACHABLE_LINE),
file_name, *start_line);
error = ER_GENERIC_ERROR;
goto end;
}
else if (c == '\n')
{
line_count--;
}
}
while (line_count > 0);
}
check_line_no = false;
session = db_make_session_for_one_statement_execution (input_stream);
if (session == NULL)
{
print_log_msg (1, "ERROR: %s\n", db_error_string (3));
error = er_errid ();
goto end;
}
util_arm_signal_handlers (&ldr_exec_query_interrupt_handler,
&ldr_exec_query_interrupt_handler);
while (1)
{
if (interrupt_query)
{
if (er_errid () != ER_INTERRUPTED)
{
er_set (ER_ERROR_SEVERITY, ARG_FILE_LINE, ER_INTERRUPTED, 0);
}
error = er_errid ();
db_close_session (session);
goto end;
}
parser_start_line_no = parser_end_line_no;
stmt_cnt = db_parse_one_statement (session);
if (stmt_cnt > 0)
{
db_get_parser_line_col (session, &parser_end_line_no, NULL);
stmt_id = db_compile_statement (session);
}
if (stmt_cnt <= 0 || stmt_id <= 0)
{
DB_SESSION_ERROR *session_error;
int line, col;
if ((session_error = db_get_errors (session)) != NULL)
{
do
{
session_error =
db_get_next_error (session_error, &line, &col);
if (line >= 0)
{
print_log_msg (1, "In %s line %d,\n", file_name,
line + (*start_line));
print_log_msg (1, "ERROR: %s \n", db_error_string (3));
error = er_errid ();
}
}
while (session_error);
}
db_close_session (session);
break;
}
stmt_type = db_get_statement_type (session, stmt_id);
res = (DB_QUERY_RESULT *) NULL;
error = db_execute_statement (session, stmt_id, &res);
if (error < 0)
{
print_log_msg (1, "ERROR: %s\n", db_error_string (3));
db_close_session (session);
break;
}
executed_cnt++;
error = db_query_end (res);
if (error < 0)
{
print_log_msg (1, "ERROR: %s\n", db_error_string (3));
db_close_session (session);
break;
}
if (stmt_type == CUBRID_STMT_COMMIT_WORK ||
(commit_period && (executed_cnt % commit_period == 0)))
{
db_commit_transaction ();
print_log_msg (Verbose_commit,
"%8d statements executed. Commit transaction at line %d\n",
executed_cnt, parser_end_line_no);
*start_line = parser_end_line_no + 1;
}
print_log_msg ((int) Verbose, "Total %8d statements executed.\r",
executed_cnt);
fflush (stdout);
}
end:
if (error < 0)
{
db_abort_transaction ();
}
else
{
*start_line = parser_end_line_no + 1;
print_log_msg (1, "Total %8d statements executed.\n", executed_cnt);
fflush (stdout);
db_commit_transaction ();
}
return error;
}
