/**
* @brief
* Prepare all the server related sqls. Typically called after connect
* and before any other sql exeuction
*
* @param[in] conn - Database connection handle
*
* @return Error code
* @retval -1 - Failure
* @retval 0 - Success
*
*/
int
pg_db_prepare_svr_sqls(pbs_db_conn_t *conn)
{
sprintf(conn->conn_sql, "insert into pbs.server( "
"sv_name, "
"sv_hostname, "
"sv_numjobs, "
"sv_numque, "
"sv_jobidnumber, "
"sv_svraddr, "
"sv_svrport, "
"sv_savetm, "
"sv_creattm "
") "
"values "
"($1, $2, $3, $4, $5, $6, $7, localtimestamp, localtimestamp)");
if (pg_prepare_stmt(conn, STMT_INSERT_SVR, conn->conn_sql, 7) != 0)
return -1;
sprintf(conn->conn_sql, "update pbs.server set "
"sv_hostname = $2, "
"sv_numjobs = $3, "
"sv_numque = $4, "
"sv_jobidnumber = $5, "
"sv_svraddr = $6, "
"sv_svrport = $7, "
"sv_savetm = localtimestamp "
"where sv_name = $1");
if (pg_prepare_stmt(conn, STMT_UPDATE_SVR, conn->conn_sql, 7) != 0)
return -1;
sprintf(conn->conn_sql, "select "
"sv_name, "
"sv_hostname, "
"sv_numjobs, "
"sv_numque, "
"sv_jobidnumber, "
"extract(epoch from sv_savetm)::bigint as sv_savetm, "
"extract(epoch from sv_creattm)::bigint as sv_creattm "
"from "
"pbs.server where sv_name = $1");
if (pg_prepare_stmt(conn, STMT_SELECT_SVR, conn->conn_sql, 1) != 0)
return -1;
sprintf(conn->conn_sql, "insert into "
"pbs.server_attr "
"(sv_name, "
"attr_name, "
"attr_resource, "
"attr_value, "
"attr_flags) "
"values "
"($1, $2, $3, $4, $5)");
if (pg_prepare_stmt(conn, STMT_INSERT_SVRATTR, conn->conn_sql, 5) != 0)
return -1;
sprintf(conn->conn_sql, "update pbs.server_attr set "
"attr_resource = $3, "
"attr_value = $4, "
"attr_flags = $5 "
"where sv_name = $1 "
"and attr_name = $2");
if (pg_prepare_stmt(conn, STMT_UPDATE_SVRATTR, conn->conn_sql, 5) != 0)
return -1;
sprintf(conn->conn_sql, "update pbs.server_attr set "
"attr_value = $4, "
"attr_flags = $5 "
"where sv_name = $1 "
"and attr_name = $2 "
"and attr_resource = $3");
if (pg_prepare_stmt(conn, STMT_UPDATE_SVRATTR_RESC, conn->conn_sql, 5) != 0)
return -1;
sprintf(conn->conn_sql, "delete from "
"pbs.server_attr "
"where sv_name = $1");
if (pg_prepare_stmt(conn, STMT_DELETE_SVRATTR_ALL, conn->conn_sql, 1) != 0)
return -1;
sprintf(conn->conn_sql, "delete from "
"pbs.server_attr "
"where sv_name = $1 "
"and attr_name = $2");
if (pg_prepare_stmt(conn, STMT_DELETE_SVRATTR, conn->conn_sql, 2) != 0)
return -1;
sprintf(conn->conn_sql, "delete from "
"pbs.server_attr "
"where sv_name = $1 "
"and attr_name = $2 "
"and attr_resource = $3");
if (pg_prepare_stmt(conn, STMT_DELETE_SVRATTR_RESC, conn->conn_sql, 3) != 0)
return -1;
sprintf(conn->conn_sql, "select "
"attr_name, "
"attr_resource, "
"attr_value, "
"attr_flags "
"from pbs.server_attr "
"where sv_name = $1");
if (pg_prepare_stmt(conn, STMT_SELECT_SVRATTR, conn->conn_sql, 1) != 0)
return -1;
sprintf(conn->conn_sql, "select "
"pbs_schema_version "
"from "
"pbs.info");
if (pg_prepare_stmt(conn, STMT_SELECT_DBVER, conn->conn_sql, 0) != 0)
return -1;
sprintf(conn->conn_sql, "select nextval('pbs.svr_id_seq')");
if (pg_prepare_stmt(conn, STMT_SELECT_NEXT_SEQID, conn->conn_sql, 0) != 0)
return -1;
sprintf(conn->conn_sql, "select "
"sv_name "
"from "
"pbs.server where sv_hostname = $1");
if (pg_prepare_stmt(conn, STMT_SELECT_SVRID, conn->conn_sql, 1) != 0)
return -1;
return 0;
}
/**
* @brief
* Prepare all the scheduler related sqls. Typically called after connect
* and before any other sql exeuction
*
* @param[in] conn - Database connection handle
*
* @return Error code
* @retval -1 - Failure
* @retval 0 - Success
*
*/
int
pg_db_prepare_sched_sqls(pbs_db_conn_t *conn)
{
sprintf(conn->conn_sql, "insert into "
"pbs.scheduler( "
"sched_name, "
"sched_sv_name, "
"sched_savetm, "
"sched_creattm "
") "
"values ($1, $2, localtimestamp, localtimestamp)");
if (pg_prepare_stmt(conn, STMT_INSERT_SCHED, conn->conn_sql, 2) != 0)
return -1;
sprintf(conn->conn_sql, "update pbs.scheduler set "
"sched_sv_name = $2, "
"sched_savetm = localtimestamp "
"where sched_name = $1");
if (pg_prepare_stmt(conn, STMT_UPDATE_SCHED, conn->conn_sql, 2) != 0)
return -1;
sprintf(conn->conn_sql, "select "
"sched_name, "
"sched_sv_name, "
"extract(epoch from sched_savetm)::bigint as sched_savetm, "
"extract(epoch from sched_creattm)::bigint as sched_creattm "
"from "
"pbs.scheduler "
"where sched_name = $1");
if (pg_prepare_stmt(conn, STMT_SELECT_SCHED, conn->conn_sql, 1) != 0)
return -1;
sprintf(conn->conn_sql, "insert into "
"pbs.scheduler_attr "
"(sched_name, "
"attr_name, "
"attr_resource, "
"attr_value, "
"attr_flags) "
"values "
"($1, $2, $3, $4, $5)");
if (pg_prepare_stmt(conn, STMT_INSERT_SCHEDATTR, conn->conn_sql, 5) != 0)
return -1;
sprintf(conn->conn_sql, "update pbs.scheduler_attr set "
"attr_resource = $3, "
"attr_value = $4, "
"attr_flags = $5 "
"where sched_name = $1 "
"and attr_name = $2");
if (pg_prepare_stmt(conn, STMT_UPDATE_SCHEDATTR, conn->conn_sql, 5) != 0)
return -1;
sprintf(conn->conn_sql, "update pbs.scheduler_attr set "
"attr_value = $4, "
"attr_flags = $5 "
"where sched_name = $1 "
"and attr_name = $2 "
"and attr_resource = $3");
if (pg_prepare_stmt(conn, STMT_UPDATE_SCHEDATTR_RESC,
conn->conn_sql, 5) != 0)
return -1;
sprintf(conn->conn_sql, "delete from "
"pbs.scheduler_attr "
"where sched_name = $1 "
"and attr_name = $2");
if (pg_prepare_stmt(conn, STMT_DELETE_SCHEDATTR, conn->conn_sql, 2) != 0)
return -1;
sprintf(conn->conn_sql, "delete from "
"pbs.scheduler_attr "
"where sched_name = $1 "
"and attr_name = $2 "
"and attr_resource = $3");
if (pg_prepare_stmt(conn, STMT_DELETE_SCHEDATTR_RESC,
conn->conn_sql, 3) != 0)
return -1;
sprintf(conn->conn_sql, "select "
"attr_name, "
"attr_resource, "
"attr_value, "
"attr_flags "
"from "
"pbs.scheduler_attr "
"where sched_name = $1");
if (pg_prepare_stmt(conn, STMT_SELECT_SCHEDATTR, conn->conn_sql, 1) != 0)
return -1;
return 0;
}
/**
* @brief
* Prepare all the job related sqls. Typically called after connect
* and before any other sql exeuction
*
* @param[in] conn - Database connection handle
*
* @return Error code
* @retval -1 - Failure
* @retval 0 - Success
*
*/
int
pg_db_prepare_job_sqls(pbs_db_conn_t *conn)
{
snprintf(conn->conn_sql, MAX_SQL_LENGTH, "insert into pbs.job ("
"ji_jobid,"
"ji_state,"
"ji_substate,"
"ji_svrflags,"
"ji_numattr,"
"ji_ordering,"
"ji_priority,"
"ji_stime,"
"ji_endtBdry,"
"ji_queue,"
"ji_destin,"
"ji_un_type,"
"ji_momaddr,"
"ji_momport,"
"ji_exitstat,"
"ji_quetime,"
"ji_rteretry,"
"ji_fromsock,"
"ji_fromaddr,"
"ji_4jid,"
"ji_4ash,"
"ji_credtype,"
"ji_qrank,"
"ji_savetm,"
"ji_creattm,"
"attributes"
") "
"values ($1, $2, $3, $4, $5, $6, $7, $8, $9, "
"$10, $11, $12, $13,"
"$14, $15, $16, $17, $18, $19, $20, $21, $22, $23,"
"localtimestamp, localtimestamp,"
"hstore($24::text[]))");
if (pg_prepare_stmt(conn, STMT_INSERT_JOB, conn->conn_sql, 24) != 0)
return -1;
snprintf(conn->conn_sql, MAX_SQL_LENGTH, "update pbs.job set "
"ji_state = $2,"
"ji_substate = $3,"
"ji_svrflags = $4,"
"ji_numattr = $5,"
"ji_ordering = $6,"
"ji_priority = $7,"
"ji_stime = $8,"
"ji_endtBdry = $9,"
"ji_queue = $10,"
"ji_destin = $11,"
"ji_un_type = $12,"
"ji_momaddr = $13,"
"ji_momport = $14,"
"ji_exitstat = $15,"
"ji_quetime = $16,"
"ji_rteretry = $17,"
"ji_fromsock = $18,"
"ji_fromaddr = $19,"
"ji_4jid = $20,"
"ji_4ash = $21,"
"ji_credtype = $22,"
"ji_qrank = $23,"
"ji_savetm = localtimestamp,"
"attributes = attributes || hstore($24::text[]) "
"where ji_jobid = $1");
if (pg_prepare_stmt(conn, STMT_UPDATE_JOB, conn->conn_sql, 24) != 0)
return -1;
snprintf(conn->conn_sql, MAX_SQL_LENGTH, "update pbs.job set "
"ji_savetm = localtimestamp,"
"attributes = attributes - hstore($2::text[]) "
"where ji_jobid = $1");
if (pg_prepare_stmt(conn, STMT_REMOVE_JOBATTRS, conn->conn_sql, 2) != 0)
return -1;
snprintf(conn->conn_sql, MAX_SQL_LENGTH, "update pbs.job set "
"ji_state = $2,"
"ji_substate = $3,"
"ji_svrflags = $4,"
"ji_numattr = $5,"
"ji_ordering = $6,"
"ji_priority = $7,"
"ji_stime = $8,"
"ji_endtBdry = $9,"
"ji_queue = $10,"
"ji_destin = $11,"
"ji_un_type = $12,"
"ji_momaddr = $13,"
"ji_momport = $14,"
"ji_exitstat = $15,"
"ji_quetime = $16,"
"ji_rteretry = $17,"
"ji_fromsock = $18,"
"ji_fromaddr = $19,"
"ji_4jid = $20,"
"ji_4ash = $21,"
"ji_credtype = $22,"
"ji_qrank = $23,"
"ji_savetm = localtimestamp "
"where ji_jobid = $1");
if (pg_prepare_stmt(conn, STMT_UPDATE_JOB_QUICK, conn->conn_sql, 23) != 0)
return -1;
snprintf(conn->conn_sql, MAX_SQL_LENGTH, "select "
"ji_jobid,"
"ji_state,"
"ji_substate,"
"ji_svrflags,"
"ji_numattr,"
"ji_ordering,"
"ji_priority,"
"ji_stime,"
"ji_endtBdry,"
"ji_queue,"
"ji_destin,"
"ji_un_type,"
"ji_momaddr,"
"ji_momport,"
"ji_exitstat,"
"ji_quetime,"
"ji_rteretry,"
"ji_fromsock,"
"ji_fromaddr,"
"ji_4jid,"
"ji_4ash,"
"ji_credtype,"
"ji_qrank,"
"extract(epoch from ji_savetm)::bigint as ji_savetm, "
"extract(epoch from ji_creattm)::bigint as ji_creattm, "
"hstore_to_array(attributes) as attributes "
"from pbs.job where ji_jobid = $1");
if (pg_prepare_stmt(conn, STMT_SELECT_JOB, conn->conn_sql, 1) != 0)
return -1;
/*
* Use the sql encode function to encode the $2 parameter. Encode using
* 'escape' mode. Encode considers $2 as a bytea and returns a escaped
* string using 'escape' syntax. Refer to the following postgres link
* for details:
* http://www.postgresql.org/docs/8.3/static/functions-string.html
*/
snprintf(conn->conn_sql, MAX_SQL_LENGTH, "insert into "
"pbs.job_scr (ji_jobid, script) "
"values "
"($1, encode($2, 'escape'))");
if (pg_prepare_stmt(conn, STMT_INSERT_JOBSCR, conn->conn_sql, 2) != 0)
return -1;
/*
* Use the sql decode function to decode the script parameter. Decode
* using 'escape' mode. Decode considers script as encoded TEXT and
* decodes it using 'escape' syntax, returning a bytea. The :: is used
* to "typecast" the output to a bytea.
* Refer to the following postgres link for details:
* http://www.postgresql.org/docs/8.3/static/functions-string.html
*/
snprintf(conn->conn_sql, MAX_SQL_LENGTH, "select decode(script, 'escape')::bytea as script "
"from pbs.job_scr "
"where ji_jobid = $1");
if (pg_prepare_stmt(conn, STMT_SELECT_JOBSCR, conn->conn_sql, 1) != 0)
return -1;
snprintf(conn->conn_sql, MAX_SQL_LENGTH, "select "
"ji_jobid,"
"ji_state,"
"ji_substate,"
"ji_svrflags,"
"ji_numattr,"
"ji_ordering,"
"ji_priority,"
"ji_stime,"
"ji_endtBdry,"
"ji_queue,"
"ji_destin,"
"ji_un_type,"
"ji_momaddr,"
"ji_momport,"
"ji_exitstat,"
"ji_quetime,"
"ji_rteretry,"
"ji_fromsock,"
"ji_fromaddr,"
"ji_4jid,"
"ji_4ash,"
"ji_credtype,"
"ji_qrank,"
"extract(epoch from ji_savetm)::bigint as ji_savetm, "
"extract(epoch from ji_creattm)::bigint as ji_creattm, "
"hstore_to_array(attributes) as attributes "
"from pbs.job order by ji_qrank");
if (pg_prepare_stmt(conn, STMT_FINDJOBS_ORDBY_QRANK, conn->conn_sql, 0) != 0)
return -1;
snprintf(conn->conn_sql, MAX_SQL_LENGTH, "select "
"ji_jobid,"
"ji_state,"
"ji_substate,"
"ji_svrflags,"
"ji_numattr,"
"ji_ordering,"
"ji_priority,"
"ji_stime,"
"ji_endtBdry,"
"ji_queue,"
"ji_destin,"
"ji_un_type,"
"ji_momaddr,"
"ji_momport,"
"ji_exitstat,"
"ji_quetime,"
"ji_rteretry,"
"ji_fromsock,"
"ji_fromaddr,"
"ji_4jid,"
"ji_4ash,"
"ji_credtype,"
"ji_qrank,"
"extract(epoch from ji_savetm)::bigint as ji_savetm, "
"extract(epoch from ji_creattm)::bigint as ji_creattm, "
"hstore_to_array(attributes) as attributes "
"from pbs.job where ji_queue = $1"
" order by ji_qrank");
if (pg_prepare_stmt(conn, STMT_FINDJOBS_BYQUE_ORDBY_QRANK,
conn->conn_sql, 1) != 0)
return -1;
snprintf(conn->conn_sql, MAX_SQL_LENGTH, "delete from pbs.job where ji_jobid = $1");
if (pg_prepare_stmt(conn, STMT_DELETE_JOB, conn->conn_sql, 1) != 0)
return -1;
snprintf(conn->conn_sql, MAX_SQL_LENGTH, "delete from pbs.job_scr where ji_jobid = $1");
if (pg_prepare_stmt(conn, STMT_DELETE_JOBSCR, conn->conn_sql, 1) != 0)
return -1;
return 0;
}
