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_information
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 jobqueue.* FROM jobqueue INNER JOIN job ON job_pk = jq_job_fk "
    " WHERE jq_starttime IS NULL AND jq_end_bits < 2 "
    "   AND NOT EXISTS(SELECT * FROM jobdepends, jobqueue jdep "
    "     WHERE jdep_jq_fk=jobqueue.jq_pk "
    "       AND jdep_jq_depends_fk=jdep.jq_pk"
    "       AND NOT(jdep.jq_endtime IS NOT NULL AND jdep.jq_end_bits < 2)) "
    " ORDER BY job_priority DESC "
    "   LIMIT 10;"

Get the jobs which are not yet queued by the scheduler

Definition at line 109 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 * FROM jobqueue "
    " WHERE jq_starttime IS NULL AND jq_end_bits < 2 "
    "   AND NOT EXISTS(SELECT * FROM jobdepends, jobqueue jdep "
    "     WHERE jdep_jq_fk=jobqueue.jq_pk "
    "       AND jdep_jq_depends_fk=jdep.jq_pk"
    "       AND NOT(jdep.jq_endtime IS NOT NULL AND jdep.jq_end_bits < 2))"
    "   AND jq_job_fk = (SELECT jq_job_fk FROM jobqueue queue WHERE queue.jq_pk = %d)"

Get all jobs from job queue which are runnable for a given job id

Definition at line 214 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 140 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 164 of file sqlstatements.h.

jobsql_information

const char* jobsql_information

Initial value:

=
    " SELECT user_pk, job_priority, job_group_fk as group_pk FROM users "
    "   LEFT JOIN job ON job_user_fk = user_pk "
    "   WHERE job_pk = '%s';"

For a given job id, get the job information

Definition at line 122 of file sqlstatements.h.

jobsql_jobendbits

const char* jobsql_jobendbits

Initial value:

=
    " SELECT jq_pk, jq_end_bits FROM jobqueue "
    "   WHERE jq_job_fk = ( "
    "     SELECT jq_job_fk FROM jobqueue "
    "       WHERE jq_pk = %d "
    "   );"

Get the job id and job end bits for the given job id

Definition at line 226 of file sqlstatements.h.

jobsql_jobinfo

const char* jobsql_jobinfo

Initial value:

=
    " SELECT * FROM jobqueue "
    "   WHERE jq_job_fk = ( "
    "     SELECT jq_job_fk FROM jobqueue "
    "       WHERE jq_pk = %d "
    "   );"

Get the job information for a given job id

Definition at line 246 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 196 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 183 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 204 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 175 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 236 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 151 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 130 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 job_upload_fk FROM job, jobqueue "
    "   WHERE jq_job_fk = job_pk "
    "     AND 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 256 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.