248 lines
5.6 KiB
C
248 lines
5.6 KiB
C
#include "./Include/simulateFlux.h"
|
|
#include "./Include/database.h"
|
|
|
|
int initializeTrialsTable()
|
|
{
|
|
sqlite3 *db;
|
|
int rc;
|
|
sqlite3_stmt *stmt;
|
|
|
|
rc = sqlite3_open("robotgowest.db", &db);
|
|
|
|
// char *sql = "CREATE TABLE Test(Id INT)";
|
|
|
|
char *sql =
|
|
"CREATE TABLE Trials(Id INTEGER PRIMARY KEY AUTOINCREMENT, Position TEXT, Start INT, Frequency NUMBER);";
|
|
|
|
rc = sqlite3_prepare_v2(db, sql, -1, &stmt, NULL);
|
|
rc = sqlite3_step(stmt);
|
|
rc = sqlite3_finalize(stmt);
|
|
sqlite3_close(db);
|
|
return 0;
|
|
}
|
|
|
|
int initializeCaptorMetadataTable()
|
|
{
|
|
sqlite3 *db;
|
|
int rc;
|
|
sqlite3_stmt *stmt;
|
|
|
|
rc = sqlite3_open("robotgowest.db", &db);
|
|
|
|
// char *sql = "CREATE TABLE Test(Id INT)";
|
|
|
|
char *sql =
|
|
"CREATE TABLE CaptorMetadata(Id INTEGER PRIMARY KEY AUTOINCREMENT, CaptorNumber INT, TrialId INT, FOREIGN KEY (TrialId) REFERENCES Trials(Id));";
|
|
|
|
rc = sqlite3_prepare_v2(db, sql, -1, &stmt, NULL);
|
|
rc = sqlite3_step(stmt);
|
|
rc = sqlite3_finalize(stmt);
|
|
sqlite3_close(db);
|
|
return 0;
|
|
}
|
|
|
|
char *generateDataTableName(int trialId, int captorNumber)
|
|
{
|
|
|
|
char *tableName;
|
|
tableName = (char *)malloc(8 * sizeof(char));
|
|
strcpy(tableName, "T");
|
|
strcat(tableName, convertIntegerToChar(trialId));
|
|
strcat(tableName, "C");
|
|
strcat(tableName, convertIntegerToChar(captorNumber));
|
|
strcat(tableName, "Data");
|
|
return tableName;
|
|
}
|
|
|
|
int createCaptorDataTable(int trialId, int captorNumber)
|
|
{
|
|
sqlite3 *db;
|
|
int rc;
|
|
sqlite3_stmt *stmt;
|
|
|
|
// Create Table Name
|
|
char *tableName = generateDataTableName(trialId, captorNumber);
|
|
|
|
rc = sqlite3_open("robotgowest.db", &db);
|
|
if (rc != SQLITE_OK)
|
|
{
|
|
return 1;
|
|
}
|
|
|
|
char sql[101] = "CREATE TABLE ";
|
|
strcat(sql, tableName);
|
|
strcat(sql, " (Id INTEGER PRIMARY KEY AUTOINCREMENT, Lux NUMBER, Mean NUMBER , Power NUMBER);");
|
|
|
|
rc = sqlite3_prepare_v2(db, sql, -1, &stmt, NULL);
|
|
rc = sqlite3_step(stmt);
|
|
rc = sqlite3_finalize(stmt);
|
|
sqlite3_close(db);
|
|
|
|
return 0;
|
|
}
|
|
|
|
int insertTrailElement(char *position, int start, double frequency)
|
|
{
|
|
|
|
sqlite3 *db;
|
|
sqlite3_stmt *stmt;
|
|
|
|
int rc;
|
|
|
|
rc = sqlite3_open("robotgowest.db", &db);
|
|
|
|
rc = sqlite3_prepare_v2(db, "INSERT INTO Trials(Position,Start,Frequency) VALUES ( :position , :start , :frequency )", -1, &stmt, NULL);
|
|
if (rc != SQLITE_OK)
|
|
return 1;
|
|
|
|
sqlite3_bind_text(stmt, 1, position, -1, SQLITE_STATIC);
|
|
|
|
sqlite3_bind_int(stmt, 2, start);
|
|
|
|
sqlite3_bind_double(stmt, 3, frequency);
|
|
|
|
rc = sqlite3_step(stmt);
|
|
rc = sqlite3_finalize(stmt);
|
|
sqlite3_close(db);
|
|
return 0;
|
|
}
|
|
|
|
int updateTrailElement(int id, char *position, int start, double frequency)
|
|
{
|
|
|
|
sqlite3 *db;
|
|
sqlite3_stmt *stmt;
|
|
|
|
int rc;
|
|
|
|
rc = sqlite3_open("robotgowest.db", &db);
|
|
|
|
rc = sqlite3_prepare_v2(db, "UPDATE Trials SET Position = :position, Start = :start, Frequency = :frequency WHERE Id = :id", -1, &stmt, NULL);
|
|
if (rc != SQLITE_OK)
|
|
return 1;
|
|
|
|
sqlite3_bind_text(stmt, 1, position, -1, SQLITE_STATIC);
|
|
|
|
sqlite3_bind_int(stmt, 2, start);
|
|
|
|
sqlite3_bind_double(stmt, 3, frequency);
|
|
|
|
sqlite3_bind_int(stmt, 4, id);
|
|
|
|
rc = sqlite3_step(stmt);
|
|
rc = sqlite3_finalize(stmt);
|
|
sqlite3_close(db);
|
|
return 0;
|
|
}
|
|
|
|
int insertCaptorMetadataElement(int captorNumber, int trialId)
|
|
{
|
|
|
|
sqlite3 *db;
|
|
sqlite3_stmt *stmt;
|
|
|
|
int rc;
|
|
|
|
rc = sqlite3_open("robotgowest.db", &db);
|
|
|
|
rc = sqlite3_prepare_v2(db, "INSERT INTO CaptorMetadata(CaptorNumber,TrialId) VALUES ( :captorNumber , :trialId)", -1, &stmt, NULL);
|
|
if (rc != SQLITE_OK)
|
|
return 1;
|
|
|
|
sqlite3_bind_int(stmt, 1, captorNumber);
|
|
|
|
sqlite3_bind_int(stmt, 2, trialId);
|
|
|
|
rc = sqlite3_step(stmt);
|
|
rc = sqlite3_finalize(stmt);
|
|
|
|
sqlite3_close(db);
|
|
return 0;
|
|
}
|
|
|
|
int insertCaptorDataElement(int trialId, int captorNumber, double lux, double mean, double power)
|
|
{
|
|
|
|
char *tableName = generateDataTableName(trialId, captorNumber);
|
|
|
|
sqlite3 *db;
|
|
sqlite3_stmt *stmt;
|
|
|
|
int rc;
|
|
|
|
rc = sqlite3_open("robotgowest.db", &db);
|
|
|
|
char sql[70] = "INSERT INTO ";
|
|
strcat(sql, tableName);
|
|
strcat(sql, "(Lux,Mean,Power) VALUES ( :lux , :mean , :power)");
|
|
|
|
rc = sqlite3_prepare_v2(db, sql, -1, &stmt, NULL);
|
|
if (rc != SQLITE_OK)
|
|
return 1;
|
|
|
|
sqlite3_bind_double(stmt, 1, lux);
|
|
sqlite3_bind_double(stmt, 2, mean);
|
|
sqlite3_bind_double(stmt, 3, power);
|
|
|
|
rc = sqlite3_step(stmt);
|
|
rc = sqlite3_finalize(stmt);
|
|
sqlite3_close(db);
|
|
return 0;
|
|
}
|
|
|
|
int createDb()
|
|
{
|
|
char *dbPosition = "./robotgowest.db";
|
|
if (access(dbPosition, F_OK))
|
|
{
|
|
initializeTrialsTable();
|
|
initializeCaptorMetadataTable();
|
|
return 0;
|
|
}
|
|
else
|
|
{
|
|
printf("Database already initialized");
|
|
return 1;
|
|
}
|
|
}
|
|
|
|
int getMaxTrialIdRow()
|
|
{
|
|
int rc;
|
|
sqlite3 *db;
|
|
sqlite3_stmt *stmt;
|
|
int data = -1;
|
|
|
|
rc = sqlite3_open("robotgowest.db", &db);
|
|
if (rc != SQLITE_OK)
|
|
{
|
|
return -1;
|
|
}
|
|
|
|
rc = sqlite3_prepare_v2(db, "SELECT MAX(Id) FROM Trials", -1, &stmt, NULL);
|
|
|
|
if (sqlite3_step(stmt) == SQLITE_ROW)
|
|
{
|
|
data = sqlite3_column_int(stmt, 0);
|
|
}
|
|
|
|
rc = sqlite3_finalize(stmt);
|
|
sqlite3_close(db);
|
|
|
|
return data;
|
|
}
|
|
|
|
int initiaizeNewTrial(char *position, double frequency, bool *captorOneHot)
|
|
{
|
|
insertTrailElement(position, -1, frequency);
|
|
int trialId = getMaxTrialIdRow();
|
|
for (int i = 0; i < 8; i++)
|
|
{
|
|
if (captorOneHot[i])
|
|
{
|
|
insertCaptorMetadataElement(i + 1, trialId);
|
|
createCaptorDataTable(trialId, i + 1);
|
|
}
|
|
}
|
|
return 0;
|
|
} |