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;
}
