sqlstatements.h File Reference

This graph shows which files directly or indirectly include this file:

Go to the source code of this file.

Variables
const char *	check_scheduler_tables
const char *	url_checkout
const char *	select_upload_fk
const char *	upload_common
const char *	folder_name
const char *	parent_folder_name
const char *	upload_name
const char *	upload_pk
const char *	jobsql_email
const char *	jobsql_email_job
const char *	basic_checkout
const char *	jobsql_started
const char *	jobsql_complete
const char *	jobsql_restart
const char *	jobsql_failed
const char *	jobsql_processed
const char *	jobsql_paused
const char *	jobsql_log
const char *	jobsql_priority
const char *	jobsql_anyrunnable
const char *	jobsql_jobendbits
const char *	jobsql_resetqueue
const char *	jobsql_jobinfo
const char *	smtp_values

Detailed Description

This file declares all the sql statements used in the scheduler. This should only be included once in the databace.c file.

Definition in file sqlstatements.h.

Variable Documentation

basic_checkout

const char* basic_checkout

Initial value:

=
    " SELECT jq.jq_pk, jq.jq_job_fk, jq.jq_type, jq.jq_host,"
    "        jq.jq_runonpfile, jq.jq_args, jq.jq_cmd_args,"
    "        u.user_pk, j.job_priority, j.job_group_fk AS group_pk"
    " FROM jobqueue jq"
    " INNER JOIN job j     ON j.job_pk  = jq.jq_job_fk"
    " LEFT JOIN users u    ON u.user_pk = j.job_user_fk"
    " WHERE jq.jq_starttime IS NULL AND jq.jq_end_bits < 2"
    "   AND NOT EXISTS("
    "     SELECT 1 FROM jobdepends jd"
    "     INNER JOIN jobqueue dep ON dep.jq_pk = jd.jdep_jq_depends_fk"
    "     WHERE jd.jdep_jq_fk = jq.jq_pk"
    "       AND NOT (dep.jq_endtime IS NOT NULL AND dep.jq_end_bits < 2)"
    "   )"
    " ORDER BY j.job_priority DESC"
    " LIMIT 10;"

Fetch the next batch of schedulable jobs with their user and priority. The users table is LEFT JOINed so a job whose user was deleted shows up with a NULL user_pk and can be skipped instead of silently dropped.

Definition at line 111 of file sqlstatements.h.

check_scheduler_tables

const char* check_scheduler_tables

Initial value:

=
    " SELECT column_name FROM information_schema.columns "
    "   WHERE table_catalog = '%s' "
    "     AND table_schema = 'public' "
    "     AND table_name = "

Check column names for a given table

Definition at line 22 of file sqlstatements.h.

folder_name

const char* folder_name

Initial value:

=
    " SELECT folder_name, folder_pk FROM folder "
    "   LEFT JOIN foldercontents ON folder_pk = foldercontents.parent_fk "
    "   LEFT JOIN job ON child_id = job_upload_fk "
    "   LEFT JOIN jobqueue ON jq_job_fk = job_pk "
    "   WHERE jq_pk = %d;"

For a given job id, get the folder name and folder id

Definition at line 54 of file sqlstatements.h.

jobsql_anyrunnable

const char* jobsql_anyrunnable

Initial value:

=
    " SELECT 1 FROM jobqueue jq"
    " WHERE jq.jq_starttime IS NULL AND jq.jq_end_bits < 2"
    "   AND NOT EXISTS("
    "     SELECT 1 FROM jobdepends jd"
    "     INNER JOIN jobqueue dep ON dep.jq_pk = jd.jdep_jq_depends_fk"
    "     WHERE jd.jdep_jq_fk = jq.jq_pk"
    "       AND NOT (dep.jq_endtime IS NOT NULL AND dep.jq_end_bits < 2)"
    "   )"
    "   AND jq.jq_job_fk = (SELECT jq_job_fk FROM jobqueue WHERE jq_pk = %d)"
    " LIMIT 1;"

Check whether the job containing jq_pk still has any runnable queue entries.

Definition at line 215 of file sqlstatements.h.

jobsql_complete

const char* jobsql_complete

Initial value:

=
    " UPDATE jobqueue "
    "   SET jq_endtime = now(), "
    "       jq_end_bits = jq_end_bits | 1, "
    "       jq_schedinfo = null, "
    "       jq_endtext = 'Completed' "
    "   WHERE jq_pk = %d;"

Mark the given job id as completed

Definition at line 141 of file sqlstatements.h.

jobsql_email

const char* jobsql_email

Initial value:

=
    " SELECT user_name, user_email, email_notify FROM users, upload "
    "   WHERE user_pk = user_fk "
    "     AND upload_pk = %d;"

For a given upload id, get the user's name, email and email preference

Definition at line 92 of file sqlstatements.h.

jobsql_email_job

const char* jobsql_email_job

Initial value:

=
    " SELECT user_name, user_email, email_notify FROM users, job, jobqueue "
    "   WHERE user_pk = job_user_fk AND job_pk = jq_job_fk "
    "     AND jq_pk = %d;"

For a given job id, get the user's name, email and email preference

Definition at line 100 of file sqlstatements.h.

jobsql_failed

const char* jobsql_failed

Initial value:

=
    " UPDATE jobqueue "
    "   SET jq_endtime = now(), "
    "       jq_end_bits = jq_end_bits | 2, "
    "       jq_schedinfo = null, "
    "       jq_endtext = '%s' "
    "   WHERE jq_pk = %d;"

Mark the given job id as failed

Definition at line 165 of file sqlstatements.h.

jobsql_jobendbits

const char* jobsql_jobendbits

Initial value:

=
    " SELECT jq2.jq_pk, jq2.jq_end_bits"
    " FROM jobqueue jq1"
    " INNER JOIN jobqueue jq2 ON jq2.jq_job_fk = jq1.jq_job_fk"
    " WHERE jq1.jq_pk = %d;"

Get jq_pk and jq_end_bits for every queue entry of the same parent job as jq_pk.

Definition at line 230 of file sqlstatements.h.

jobsql_jobinfo

const char* jobsql_jobinfo

Initial value:

=
    " SELECT jq2.*"
    " FROM jobqueue jq1"
    " INNER JOIN jobqueue jq2 ON jq2.jq_job_fk = jq1.jq_job_fk"
    " WHERE jq1.jq_pk = %d;"

Get every queue entry of the same parent job as jq_pk.

Definition at line 249 of file sqlstatements.h.

jobsql_log

const char* jobsql_log

Initial value:

=
    " UPDATE jobqueue "
    "   SET jq_log = '%s' "
    "   WHERE jq_pk = %d;"

Get the log location for the given job id

Definition at line 197 of file sqlstatements.h.

jobsql_paused

const char* jobsql_paused

Initial value:

=
    " UPDATE jobqueue "
    "   SET jq_endtext = 'Paused', "
    "       jq_starttime = ( CASE "
    "         WHEN jq_starttime IS NULL "
    "         THEN CAST('9999-12-31' AS timestamp with time zone) "
    "         ELSE jq_starttime "
    "       END ) "
    "   WHERE jq_pk = %d;"

Mark the given job id as paused

Definition at line 184 of file sqlstatements.h.

jobsql_priority

const char* jobsql_priority

Initial value:

=
    " UPDATE job "
    "   SET job_priority = %d "
    "   WHERE job_pk IN ( "
    "     SELECT jq_job_fk FROM jobqueue "
    "     WHERE jq_pk = %d);"

Change the priority of the given job id

Definition at line 205 of file sqlstatements.h.

jobsql_processed

const char* jobsql_processed

Initial value:

=
    " Update jobqueue "
    "   SET jq_itemsprocessed = %d "
    "   WHERE jq_pk = %d;"

Update the items processed for the given job id

Definition at line 176 of file sqlstatements.h.

jobsql_resetqueue

const char* jobsql_resetqueue

Initial value:

=
    "UPDATE jobqueue "
    "  SET jq_starttime=null, "
    "      jq_endtext=null, "
    "      jq_schedinfo=null "
    "  WHERE jq_endtime is NULL;"

Reset the job queue for jobs with end time as NULL

Definition at line 239 of file sqlstatements.h.

jobsql_restart

const char* jobsql_restart

Initial value:

=
    " UPDATE jobqueue "
    "   SET jq_endtext = 'Restarted', "
    "       jq_starttime = ( CASE "
    "         WHEN jq_starttime = CAST('9999-12-31' AS timestamp with time zone) "
    "         THEN null "
    "         ELSE jq_starttime "
    "       END ) "
    "   WHERE jq_pk = %d;"

Mark the given job id as restarted

Definition at line 152 of file sqlstatements.h.

jobsql_started

const char* jobsql_started

Initial value:

=
    " UPDATE jobqueue "
    "   SET jq_starttime = now(), "
    "       jq_schedinfo ='%s.%d', "
    "       jq_endtext = 'Started' "
    "   WHERE jq_pk = %d;"

Mark the given job id as started

Definition at line 131 of file sqlstatements.h.

parent_folder_name

const char* parent_folder_name

Initial value:

=
    " SELECT folder_name, folder_pk FROM folder "
    "   INNER JOIN foldercontents ON folder_pk=foldercontents.parent_fk "
    "   WHERE child_id = %d AND foldercontents_mode = 1;"

For a given folder id, get the folder name and folder id of the immediate parent

Definition at line 65 of file sqlstatements.h.

select_upload_fk

const char* select_upload_fk

Initial value:

=
    " SELECT j.job_upload_fk FROM job j"
    "   INNER JOIN jobqueue jq ON jq.jq_job_fk = j.job_pk"
    "   WHERE jq.jq_pk = %d;"

For a given job queue id, get the upload id

Definition at line 38 of file sqlstatements.h.

smtp_values

const char* smtp_values

Initial value:

=
    " SELECT conf_value, variablename FROM sysconfig "
    "   WHERE variablename LIKE 'SMTP%';"

Get the SMTP (email) values for the sysconfig table

Definition at line 258 of file sqlstatements.h.

upload_common

const char* upload_common

Initial value:

=
    " SELECT * FROM jobqueue "
    "   LEFT JOIN job ON jq_job_fk = job_pk"
    "   WHERE job.job_upload_fk = %d;"

For a given upload id, get job and job queue

Definition at line 46 of file sqlstatements.h.

upload_name

const char* upload_name

Initial value:

=
    " SELECT upload_filename FROM upload "
    "   LEFT JOIN job ON upload_pk = job_upload_fk "
    "   LEFT JOIN jobqueue ON jq_job_fk = job_pk "
    "   WHERE jq_pk = %d;"

For a given job id, get the upload file name

Definition at line 73 of file sqlstatements.h.

upload_pk

const char* upload_pk

Initial value:

=
    " SELECT upload_fk, uploadtree_pk FROM uploadtree "
    "   LEFT JOIN job ON upload_fk = job_upload_fk "
    "   LEFT JOIN jobqueue ON jq_job_fk = job_pk "
    "   WHERE parent IS NULL"
    "     AND jq_pk = %d;"

For a given job id, get the upload id and upload tree

Definition at line 82 of file sqlstatements.h.

url_checkout

const char* url_checkout

Initial value:

=
    " SELECT conf_value FROM sysconfig "
    "   WHERE variablename = 'FOSSologyURL';"

Get the FOSSologyURL from sysconfig table

Definition at line 31 of file sqlstatements.h.