int db_find_video_file(int cam_id, char *fname, time_t *timestamp) {
char query[256];
time_t start_ts;
int found, extension;
snprintf(query, sizeof(query),
"SELECT date_part('epoch', started_at at time zone 'UTC'), mp4 FROM videofiles WHERE camera_id = %d AND started_at <= to_timestamp(%ld) at time zone 'UTC' AND (finished_at >= to_timestamp(%ld) at time zone 'UTC' OR finished_at IS NULL);",
cam_id, *timestamp, *timestamp);
PGresult *result = exec_query(query);
if(PQresultStatus(result) != PGRES_TUPLES_OK) {
fprintf(stderr, "Selection failed(videofile): %s\n", PQresultErrorMessage(result));
exit(EXIT_FAILURE);
}
found = PQntuples(result);
if(found == 1) {
char *ts = PQgetvalue(result, 0, 0);
char *mp4 = PQgetvalue(result, 0, 1);
sscanf(ts, "%ld", &start_ts);
*timestamp -= start_ts;
extension = mk_file_name(cam_id, &start_ts, mp4, fname);
if(extension < 0) {
PQclear(result);
return -1;
}
fprintf(stderr, "Found file: %s at %d\n", fname, (int)*timestamp);
} else {
extension = -1;
fprintf(stderr, "Files found: %d\n", found);
}
PQclear(result);
return extension;
}
// START FUNC DECL
int
list_files(
char *in_data_dir
)
// STOP FUNC DECL
{
int status = 0;
struct stat st;
FILE *ofp = NULL;
int ddir_id = INT_MAX;
char data_dir[MAX_LEN_DIR_NAME+1];
char filename[32];
if ( ( in_data_dir == NULL ) || ( *in_data_dir == '\0' ) ) {
ddir_id = 0;
status = chdir(g_ddirs[0].name); cBYE(status);
}
else {
status = strip_trailing_slash(in_data_dir, data_dir, MAX_LEN_DIR_NAME+1);
cBYE(status);
status = get_ddir_id(data_dir, g_ddirs, g_n_ddir, false, &ddir_id);
cBYE(status);
if ( ddir_id < 0 ) { go_BYE(-1); }
status = chdir(in_data_dir); cBYE(status);
}
ofp = stdout;
for ( int i = 0; i < g_n_fld; i++ ) {
if ( g_flds[i].name[0] == '\0' ) { continue; } /* null entry */
if ( ddir_id != g_flds[i].ddir_id ) { continue; }
mk_file_name(filename, g_flds[i].fileno);
status = stat(filename, &st); cBYE(status);
unsigned long long size = st.st_size;
if ( size <= 0 ) { go_BYE(-1); }
fprintf(ofp, "%d,%llu,_%d\n", g_flds[i].is_external,size,g_flds[i].fileno);
}
BYE:
status = chdir(g_cwd);
return(status);
}
int db_unlink_oldest_file() {
int video_id, camera_id;
time_t start_ts;
char query[256], fname[1024];
strcpy(query, "SELECT id, camera_id, date_part('epoch', started_at at time zone 'UTC'), mp4 FROM videofiles WHERE finished_at IS NOT NULL AND deleted_at is NULL ORDER BY started_at DESC LIMIT 1;");
PGresult *result = exec_query(query);
if(PQntuples(result) == 1) {
char *id = PQgetvalue(result, 0, 0);
char *cam_id = PQgetvalue(result, 0, 1);
char *ts = PQgetvalue(result, 0, 2);
char *mp4 = PQgetvalue(result, 0, 3);
sscanf(id, "%d", &video_id);
sscanf(cam_id, "%d", &camera_id);
sscanf(ts, "%ld", &start_ts);
if(mk_file_name(camera_id, &start_ts, mp4, fname) < 0) {
PQclear(result);
return -1;
}
PQclear(result);
snprintf(query, sizeof(query), "UPDATE videofiles SET deleted_at = NOW() at time zone 'UTC' WHERE id = %d;", video_id);
PGresult *result = exec_query(query);
if(PQresultStatus(result) == PGRES_COMMAND_OK) {
fprintf(stderr, "Deleting: %s\n", fname);
unlink(fname);
return 1;
} else {
fprintf(stderr, "Update failed(videofile): %s\n", PQresultErrorMessage(result));
exit(EXIT_FAILURE);
}
}
PQclear(result);
return 0;
}