/*
* _create_function_add_step_start - create a PL/pgSQL function to
* add job step record
* IN db_conn: database connection
* RET: error code
*/
static int
_create_function_add_step_start(PGconn *db_conn, char *cluster)
{
char *create_line = xstrdup_printf(
"CREATE OR REPLACE FUNCTION %s.add_step_start (rec %s.%s) "
"RETURNS VOID AS $$"
"BEGIN LOOP "
" BEGIN "
" INSERT INTO %s.%s (job_db_inx, id_step, time_start, step_name, state, "
" cpus_alloc, nodes_alloc, task_cnt, nodelist, node_inx, task_dist) "
" VALUES (rec.job_db_inx, rec.id_step, rec.time_start, rec.step_name,"
" rec.state, rec.cpus_alloc, rec.nodes_alloc, rec.task_cnt, "
" rec.nodelist, rec.node_inx, rec.task_dist);"
" RETURN;"
" EXCEPTION WHEN UNIQUE_VIOLATION THEN "
" UPDATE %s.%s SET cpus_alloc=rec.cpus_alloc, nodes_alloc=rec.nodes_alloc, "
" task_cnt=rec.task_cnt, time_end=0, state=rec.state, "
" nodelist=rec.nodelist, node_inx=rec.node_inx, "
" task_dist=rec.task_dist, deleted=0 "
" WHERE job_db_inx=rec.job_db_inx AND id_step=rec.id_step;"
" IF FOUND THEN RETURN; END IF;"
" END;"
"END LOOP; END; $$ LANGUAGE PLPGSQL;", cluster, cluster,
step_table, cluster, step_table, cluster, step_table);
return create_function_xfree(db_conn, create_line);
}
/*
* _create_function_add_qos - create a PL/pgSQL function to add qos
*
* IN db_conn: database connection
* RET: error code
*/
static int
_create_function_add_qos(PGconn *db_conn)
{
char *create_line = xstrdup_printf(
"CREATE OR REPLACE FUNCTION public.add_qos "
"(rec %s) RETURNS INTEGER AS $$"
"DECLARE qos_id INTEGER; "
"BEGIN LOOP "
" BEGIN "
" INSERT INTO %s (creation_time, mod_time, deleted, id_qos,"
" name, description, max_jobs_per_user, "
" max_submit_jobs_per_user, max_cpus_per_job, "
" max_nodes_per_job, max_wall_duration_per_job, "
" max_cpu_mins_per_job, max_cpu_run_mins_per_user, "
" grp_jobs, grp_submit_jobs, grp_cpus, grp_mem, grp_nodes, "
" grp_wall, grp_cpu_mins, grp_cpu_run_mins, preempt, "
" preempt_mode, priority, usage_factor) "
" VALUES (rec.creation_time, rec.mod_time, "
" 0, DEFAULT, rec.name, rec.description, "
" rec.max_jobs_per_user, "
" rec.max_submit_jobs_per_user, "
" rec.max_cpus_per_job, rec.max_nodes_per_job, "
" rec.max_wall_duration_per_job, "
" rec.max_cpu_mins_per_job, "
" rec.max_cpu_run_mins_per_user, "
" rec.grp_jobs, rec.grp_submit_jobs, rec.grp_cpus, rec.grp_mem, "
" rec.grp_nodes, rec.grp_wall, rec.grp_cpu_mins, "
" rec.grp_cpu_run_mins, rec.preempt, rec.preempt_mode, "
" rec.priority, rec.usage_factor) "
" RETURNING id_qos INTO qos_id;"
" RETURN qos_id;"
" EXCEPTION WHEN UNIQUE_VIOLATION THEN"
" UPDATE %s SET"
" (deleted, mod_time, description, max_jobs_per_user, "
" max_submit_jobs_per_user, max_cpus_per_job, "
" max_nodes_per_job, max_wall_duration_per_job, "
" max_cpu_mins_per_job, max_cpu_run_mins_per_user, "
" grp_jobs, grp_submit_jobs, grp_cpus, grp_mem, grp_nodes, "
" grp_wall, grp_cpu_mins, grp_cpu_run_mins, "
" preempt, preempt_mode, priority, usage_factor) = "
" (0, rec.mod_time, rec.description, "
" rec.max_jobs_per_user, "
" rec.max_submit_jobs_per_user, "
" rec.max_cpus_per_job, rec.max_nodes_per_job, "
" rec.max_wall_duration_per_job, "
" rec.max_cpu_mins_per_job, "
" rec.max_cpu_run_mins_per_user, "
" rec.grp_jobs, rec.grp_submit_jobs, rec.grp_cpus, rec.grp_mem, "
" rec.grp_nodes, rec.grp_wall, rec.grp_cpu_mins, "
" rec.grp_cpu_run_mins, rec.preempt, rec.preempt_mode, "
" rec.priority, rec.usage_factor) "
" WHERE name=rec.name "
" RETURNING id_qos INTO qos_id;"
" IF FOUND THEN RETURN qos_id; END IF;"
" END; "
"END LOOP; END; $$ LANGUAGE PLPGSQL;",
qos_table, qos_table, qos_table);
return create_function_xfree(db_conn, create_line);
}
/*
* _create_function_add_job_start - create a PL/pgSQL function to
* add job start record
* IN db_conn: database connection
* RET: error code
*/
static int
_create_function_add_job_start(PGconn *db_conn, char *cluster)
{
char *create_line = xstrdup_printf(
"CREATE OR REPLACE FUNCTION %s.add_job_start (rec %s.%s) "
"RETURNS INTEGER AS $$"
"DECLARE dbid INTEGER; "
"BEGIN LOOP "
" BEGIN "
" INSERT INTO %s.%s (job_db_inx, deleted, id_job, id_assoc, wckey, "
" id_wckey, uid, gid, partition, id_block, "
" account, time_eligible, time_submit, time_start, time_end, time_suspended, "
" timelimit, job_name, track_steps, state, exit_code, "
" priority, cpus_req, cpus_alloc, nodes_alloc, nodelist, "
" node_inx, kill_requid, id_qos, id_resv) "
" VALUES (DEFAULT, 0, rec.id_job, "
" rec.id_assoc, rec.wckey, rec.id_wckey, rec.uid, "
" rec.gid, rec.partition, rec.id_block, "
" rec.account, rec.time_eligible, rec.time_submit, rec.time_start, "
" rec.time_end, rec.time_suspended, rec.timelimit, rec.job_name, "
" rec.track_steps, rec.state, rec.exit_code, "
" rec.priority, rec.cpus_req, rec.cpus_alloc, "
" rec.nodes_alloc, rec.nodelist, rec.node_inx, "
" rec.kill_requid, rec.id_qos, rec.id_resv) "
" RETURNING job_db_inx INTO dbid; "
" RETURN dbid;"
" EXCEPTION WHEN UNIQUE_VIOLATION THEN "
" \n-- create a new dbid for job?\n "
" \n-- id=nextval('%s.%s_id_seq'), \n"
" UPDATE %s.%s SET deleted=0, "
" wckey=rec.wckey, id_wckey=rec.id_wckey, uid=rec.uid, "
" gid=rec.gid, "
" partition=(CASE WHEN rec.partition!='' "
" THEN rec.partition ELSE partition END), "
" id_block=(CASE WHEN rec.id_block!='' "
" THEN rec.id_block ELSE id_block END), "
" account=(CASE WHEN rec.account!='' "
" THEN rec.account ELSE account END),"
" time_eligible=rec.time_eligible, time_submit=rec.time_submit,"
" time_start=rec.time_start, "
" timelimit=rec.timelimit, job_name=rec.job_name, "
" track_steps=rec.track_steps,"
" state=GREATEST(state, rec.state), "
" cpus_req=rec.cpus_req, cpus_alloc=rec.cpus_alloc,"
" nodes_alloc=rec.nodes_alloc,"
" node_inx=(CASE WHEN rec.node_inx!='' "
" THEN rec.node_inx ELSE node_inx END),"
" id_qos=rec.id_qos, id_resv=rec.id_resv "
" WHERE id_job=rec.id_job AND id_assoc=rec.id_assoc AND "
" time_submit=rec.time_submit"
" RETURNING job_db_inx INTO dbid; "
" IF FOUND THEN RETURN dbid; END IF;"
" END;"
"END LOOP; END; $$ LANGUAGE PLPGSQL;", cluster,
cluster, job_table, cluster, job_table,
cluster, job_table, cluster, job_table);
return create_function_xfree(db_conn, create_line);
}
static int
_create_function_add_acct(PGconn *db_conn)
{
char *create_line = xstrdup_printf(
"CREATE OR REPLACE FUNCTION public.add_acct "
"(rec %s) RETURNS VOID AS $$ "
"BEGIN LOOP "
" BEGIN "
" INSERT INTO %s VALUES (rec.*); RETURN;"
" EXCEPTION WHEN UNIQUE_VIOLATION THEN"
" UPDATE %s SET "
" (deleted, mod_time, description, organization) = "
" (0, rec.mod_time, rec.description, rec.organization) "
" WHERE name=rec.name;"
" IF FOUND THEN RETURN; END IF;"
" END; "
"END LOOP; END; $$ LANGUAGE PLPGSQL;",
acct_table, acct_table, acct_table);
return create_function_xfree(db_conn, create_line);
}
/*
* _create_function_add_cluster - create a PL/PGSQL function to add cluster
*
* IN db_conn: database connection
* RET: error code
*/
static int
_create_function_add_cluster(PGconn *db_conn)
{
char *create_line = xstrdup_printf(
"CREATE OR REPLACE FUNCTION public.add_cluster "
"(rec %s) RETURNS VOID AS $$"
"BEGIN LOOP "
" BEGIN "
" INSERT INTO %s VALUES (rec.*); RETURN;"
" EXCEPTION WHEN UNIQUE_VIOLATION THEN "
" UPDATE %s "
" SET (deleted, mod_time, control_host, control_port, "
" classification, flags) ="
" (0, rec.mod_time, '', 0, rec.classification, "
" rec.flags)"
" WHERE name=rec.name;"
" IF FOUND THEN RETURN; END IF;"
" END; "
"END LOOP; END; $$ LANGUAGE PLPGSQL;",
cluster_table, cluster_table, cluster_table);
return create_function_xfree(db_conn, create_line);
}
/*
* _create_function_get_job_suspend_time - create a PL/pgSQL function to
* get suspended time of given job during specified period
* IN db_conn: database connection
* RET: error code
*/
static int
_create_function_get_job_suspend_time(PGconn *db_conn, char *cluster)
{
char *create_line = xstrdup_printf(
"CREATE OR REPLACE FUNCTION %s.get_job_suspend_time "
"(dbid INTEGER, st INTEGER, et INTEGER) "
"RETURNS INTEGER AS $$"
"DECLARE susp INTEGER; "
"BEGIN "
" IF et<=st THEN RETURN 0; END IF;"
" SELECT SUM((CASE WHEN (time_end=0 OR time_end>et) THEN et "
" ELSE time_end END) "
" - (CASE WHEN time_start>st THEN time_start "
" ELSE st END) "
" ) FROM %s.%s "
" INTO susp"
" WHERE (time_start!=0 AND time_start<et) AND "
" (time_end>=st OR time_end=0) AND job_db_inx=dbid "
" GROUP BY job_db_inx; "
" RETURN susp;"
"END; $$ LANGUAGE PLPGSQL;", cluster, cluster,
suspend_table);
return create_function_xfree(db_conn, create_line);
}