From ec5a274436bc8dda0b55d2c4da1411ff3c52434d Mon Sep 17 00:00:00 2001 From: iximeow Date: Sat, 1 Jul 2023 14:08:00 -0700 Subject: add a notion of runs distinct from jobs, lets see how well this goes over --- src/sql.rs | 127 ++++++++++++++++++++++++++++++++++++++++++++----------------- 1 file changed, 93 insertions(+), 34 deletions(-) (limited to 'src/sql.rs') diff --git a/src/sql.rs b/src/sql.rs index 91edafb..cc33ad4 100644 --- a/src/sql.rs +++ b/src/sql.rs @@ -2,6 +2,9 @@ use std::convert::TryFrom; +use crate::dbctx::Run; +use crate::dbctx::Job; + #[derive(Debug, Clone)] pub enum JobResult { Pass = 0, @@ -9,7 +12,7 @@ pub enum JobResult { } #[derive(Debug, Clone, PartialEq)] -pub enum JobState { +pub enum RunState { Pending = 0, Started = 1, Finished = 2, @@ -17,38 +20,48 @@ pub enum JobState { Invalid = 4, } -impl TryFrom for JobState { +impl TryFrom for RunState { type Error = String; fn try_from(value: u8) -> Result { match value { - 0 => Ok(JobState::Pending), - 1 => Ok(JobState::Started), - 2 => Ok(JobState::Finished), - 3 => Ok(JobState::Error), - 4 => Ok(JobState::Invalid), + 0 => Ok(RunState::Pending), + 1 => Ok(RunState::Started), + 2 => Ok(RunState::Finished), + 3 => Ok(RunState::Error), + 4 => Ok(RunState::Invalid), other => Err(format!("invalid job state: {}", other)), } } } +pub(crate) fn row2run(row: &rusqlite::Row) -> Run { + let (id, job_id, artifacts_path, state, run_host, build_token, create_time, start_time, complete_time, run_timeout, build_result, final_text) = row.try_into().unwrap(); + let state: u8 = state; + Run { + id, + job_id, + artifacts_path, + state: state.try_into().unwrap(), + run_host, + create_time, + start_time, + complete_time, + build_token, + run_timeout, + build_result, + final_text, + } +} + // remote_id is the remote from which we were notified. this is necessary so we know which remote // to pull from to actually run the job. pub const CREATE_JOBS_TABLE: &'static str = "\ CREATE TABLE IF NOT EXISTS jobs (id INTEGER PRIMARY KEY AUTOINCREMENT, - artifacts_path TEXT, - state INTEGER NOT NULL, - run_host TEXT, - build_token TEXT, - remote_id INTEGER, - commit_id INTEGER, - created_time INTEGER, - started_time INTEGER, - complete_time INTEGER, - job_timeout INTEGER, source TEXT, - build_result INTEGER, - final_status TEXT);"; + created_time INTEGER, + remote_id INTEGER, + commit_id INTEGER);"; pub const CREATE_METRICS_TABLE: &'static str = "\ CREATE TABLE IF NOT EXISTS metrics (id INTEGER PRIMARY KEY AUTOINCREMENT, @@ -82,35 +95,63 @@ pub const CREATE_REMOTES_TABLE: &'static str = "\ pub const CREATE_ARTIFACTS_TABLE: &'static str = "\ CREATE TABLE IF NOT EXISTS artifacts (id INTEGER PRIMARY KEY AUTOINCREMENT, - job_id INTEGER, + run_id INTEGER, name TEXT, desc TEXT, created_time INTEGER, completed_time INTEGER);"; +pub const CREATE_RUN_TABLE: &'static str = "\ + CREATE TABLE IF NOT EXISTS runs (id INTEGER PRIMARY KEY AUTOINCREMENT, + job_id INTEGER, + artifacts_path TEXT, + state INTEGER NOT NULL, + run_host TEXT, + build_token TEXT, + created_time INTEGER, + started_time INTEGER, + complete_time INTEGER, + run_timeout INTEGER, + build_result INTEGER, + final_status TEXT);"; + pub const CREATE_REMOTES_INDEX: &'static str = "\ CREATE INDEX IF NOT EXISTS 'repo_to_remote' ON remotes(repo_id);"; pub const CREATE_REPO_NAME_INDEX: &'static str = "\ CREATE UNIQUE INDEX IF NOT EXISTS 'repo_names' ON repos(repo_name);"; -pub const PENDING_JOBS: &'static str = "\ - select id, artifacts_path, state, run_host, remote_id, commit_id, created_time, source from jobs where state=0;"; - -pub const ACTIVE_JOBS: &'static str = "\ - select * from jobs where state=1 or state=0;"; - -pub const LAST_ARTIFACTS_FOR_JOB: &'static str = "\ - select * from artifacts where job_id=?1 and (name like \"%(stderr)%\" or name like \"%(stdout)%\") order by id desc limit ?2;"; +pub const PENDING_RUNS: &'static str = "\ + select id, job_id, artifacts_path, state, run_host, created_time from runs where state=0;"; + +pub const ACTIVE_RUNS: &'static str = "\ + select id, + job_id, + artifacts_path, + state, + run_host, + build_token, + created_time, + started_time, + complete_time, + run_timeout, + build_result, + final_status from runs where state=1 or state=0;"; + +pub const LAST_ARTIFACTS_FOR_RUN: &'static str = "\ + select * from artifacts where run_id=?1 and (name like \"%(stderr)%\" or name like \"%(stdout)%\") order by id desc limit ?2;"; pub const JOB_BY_COMMIT_ID: &'static str = "\ - select * from jobs where commit_id=?1;"; + select id, source, created_time, remote_id, commit_id from jobs where commit_id=?1;"; pub const ARTIFACT_BY_ID: &'static str = "\ - select * from artifacts where id=?1 and job_id=?2;"; + select * from artifacts where id=?1 and run_id=?2;"; -pub const METRICS_FOR_JOB: &'static str = "\ - select * from metrics where job_id=?1 order by id asc;"; +pub const JOB_BY_ID: &'static str = "\ + select id, source, created_time, remote_id, commit_id from jobs where id=?1"; + +pub const METRICS_FOR_RUN: &'static str = "\ + select * from metrics where run_id=?1 order by id asc;"; pub const COMMIT_TO_ID: &'static str = "\ select id from commits where sha=?1;"; @@ -122,5 +163,23 @@ pub const ALL_REPOS: &'static str = "\ select * from repos;"; pub const LAST_JOBS_FROM_REMOTE: &'static str = "\ - select * from jobs where remote_id=?1 order by created_time desc limit ?2;"; - + select id, source, created_time, remote_id, commit_id from jobs where remote_id=?1 order by created_time desc limit ?2;"; + +pub const LAST_RUN_FOR_JOB: &'static str = "\ + select id, + job_id, + artifacts_path, + state, + run_host, + build_token, + created_time, + started_time, + complete_time, + run_timeout, + build_result, + final_status from runs where job_id=?1;"; + +pub const SELECT_ALL_RUNS_WITH_JOB_INFO: &'static str = "\ + select jobs.id as job_id, runs.id as run_id, runs.state, runs.created_time, jobs.commit_id + from jobs join runs on jobs.id=runs.job_id + oder by runs.created_time asc;"; -- cgit v1.1