bool SqlDB::change_state(const char *pr_number, PrState new_state) { PrInfo pr; PrState old_state; bool flag = false; char query_state_change[512]; snprintf(query_state_change, 512, "UPDATE TODOLIST SET PR_STATE=%d WHERE PR_NUMBER='%s';", new_state, pr_number); if(status == false) { snprintf(last_error_msg, 1024, "First open the database before any query"); return false; } if(get_pr(pr_number, &pr) == false) { snprintf(last_error_msg, 1024, "Pr '%s' does not exists", pr_number); return false; } old_state = pr.pr_state; if(old_state == new_state) { snprintf(last_error_msg, 1024, "new and old state are the same"); return false; } flag = execute_stmt(query_state_change); if(flag == true) { char msg[1024]; snprintf(msg, 1024, "change state from %s to %s", PrState_string[old_state], PrState_string[new_state]); flag = add_update(pr_number, msg); } return flag; }
bool SqlDB::connect(const char *filepath) { const char *filename = (NULL == filepath) ? DEFAULT_FILE_NAME : filepath; const char *query_enable_foreign_keys = "PRAGMA foreign_keys = ON;"; const char *query_create_todo_table = "CREATE TABLE IF NOT EXISTS TODOLIST (" "PR_NUMBER TEXT PRIMARY KEY," "PR_HEADER TEXT NOT NULL," "PR_DATE INTEGER NOT NULL," "PR_STATE INTEGER NOT NULL" ");"; const char *query_create_desc_table = "CREATE TABLE IF NOT EXISTS PR_DESC (" "PR_NUMBER TEXT NOT NULL," "DESCRIPTION TEXT NOT NULL," "UPDATED_DATE INTEGER NOT NULL," "FOREIGN KEY(PR_NUMBER) REFERENCES TODOLIST(PR_NUMBER)" ");"; status = false; if(sqlite3_open(filename, &database) != SQLITE_OK) { snprintf(last_error_msg, 1024, "sqlite3_open(%s) failed", filepath); database = NULL; return false; } else { status = execute_stmt(query_enable_foreign_keys); if(true == status) status = execute_stmt(query_create_todo_table); if(true == status) status = execute_stmt(query_create_desc_table); } if(status == false) { sqlite3_close(database); } else { strcpy(sqldb, filename); } return status; }
bool SqlDB::add_update(const char *pr_number, const char *msg) { bool flag; char query_insert_update[1024]; time_t ti; time(&ti); snprintf(query_insert_update, 1024, "INSERT INTO PR_DESC (PR_NUMBER, DESCRIPTION, UPDATED_DATE)" "VALUES('%s', '%s', %lu);", pr_number, msg, ti); if(status == false) { snprintf(last_error_msg, 1024, "First open the database before any query"); return false; } flag = execute_stmt(query_insert_update); return flag; }
int main() { char sql[1024]; time_t start, stop; FILE *fp = fopen("/Users/carybourgeois/flights_exercise/flights_from_pg.csv", "r") ; CassError rc = CASS_OK; CassCluster* cluster = create_cluster(); CassSession* session = NULL; CassFuture* close_future = NULL; rc = connect_session(cluster, &session); if(rc != CASS_OK) { return -1; } execute_stmt(session, "CREATE KEYSPACE IF NOT EXISTS exercise WITH \ replication = {'class': 'SimpleStrategy','replication_factor': '1'};"); execute_stmt(session, "USE exercise;"); execute_stmt(session, "DROP TABLE IF EXISTS flights;"); execute_stmt(session, "CREATE TABLE flights ( \ id int, year int, day_of_month int, fl_date varchar, \ airline_id int, carrier varchar, fl_num int, origin_airport_id int, \ origin varchar, origin_city_name varchar, origin_state_abr varchar, dest varchar, \ dest_city_name varchar, dest_state_abr varchar, dep_time int, arr_time int, \ actual_elapsed_time int, air_time int, distance int, air_time_grp int, \ PRIMARY KEY (carrier, origin, air_time_grp, id));"); time(&start); if ( fp != NULL ) { int i = 0; while(!feof(fp)) { i++; fscanf(fp, "%d, %d, %d, %[^,], %d, %[^,], %d, %d, %[^,], %[^,], %[^,], %[^,], %[^,], %[^,], %d, %d, %d, %d, %d \n", &Flight.id, &Flight.year, &Flight.day_of_month, Flight.fl_date, &Flight.airline_id, Flight.carrier, &Flight.fl_num, &Flight.origin_airport_id, Flight.origin, Flight.origin_city_name, Flight.origin_state_abr, Flight.dest, Flight.dest_city_name, Flight.dest_state_abr, &Flight.dep_time, &Flight.arr_time, &Flight.actual_elapsed_time, &Flight.air_time, &Flight.distance); sprintf(sql, "INSERT INTO flights (id, year, day_of_month, fl_date, airline_id, carrier, fl_num, origin_airport_id, origin, origin_city_name, origin_state_abr, dest, dest_city_name, dest_state_abr, dep_time, arr_time, actual_elapsed_time, air_time, distance, air_time_grp) VALUES (%d, %d, %d, \'%s\', %d, \'%s\', %d, %d, \'%s\', \'%s\', \'%s\', \'%s\', \'%s\', \'%s\', %d, %d, %d, %d, %d, %d);\n", Flight.id, Flight.year, Flight.day_of_month, Flight.fl_date, Flight.airline_id, Flight.carrier, Flight.fl_num, Flight.origin_airport_id, Flight.origin, Flight.origin_city_name, Flight.origin_state_abr, Flight.dest, Flight.dest_city_name, Flight.dest_state_abr, Flight.dep_time, Flight.arr_time, Flight.actual_elapsed_time, Flight.air_time, Flight.distance, Flight.air_time/10 ); /* printf("%s", sql); */ execute_stmt(session, sql); /* if (i > 999) break; */ } /* EOF */ printf("%d Records loaded.\n", i); } /* File exists */ time(&stop); printf("%.f Seconds total load time.\n", difftime(start, stop)); close_future = cass_session_close(session); cass_future_wait(close_future); cass_future_free(close_future); cass_cluster_free(cluster); fclose( fp ); return 0; }