sqlstatements.h

Go to the documentation of this file.

/*
 SPDX-FileCopyrightText: © 2012 Hewlett-Packard Development Company, L.P.
 SPDX-FileCopyrightText: © 2015, 2018 Siemens AG
 
 SPDX-License-Identifier: GPL-2.0-only
*/
 
#ifndef SQLSTATEMENTS_H
#define SQLSTATEMENTS_H
 
const char* check_scheduler_tables =
    " SELECT column_name FROM information_schema.columns "
    "   WHERE table_catalog = '%s' "
    "     AND table_schema = 'public' "
    "     AND table_name = ";
 
const char* url_checkout =
    " SELECT conf_value FROM sysconfig "
    "   WHERE variablename = 'FOSSologyURL';";
 
const char* select_upload_fk =
    " 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;";
 
const char* upload_common =
    " SELECT * FROM jobqueue "
    "   LEFT JOIN job ON jq_job_fk = job_pk"
    "   WHERE job.job_upload_fk = %d;";
 
const char* folder_name =
    " 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;";
 
const char* parent_folder_name =
    " SELECT folder_name, folder_pk FROM folder "
    "   INNER JOIN foldercontents ON folder_pk=foldercontents.parent_fk "
    "   WHERE child_id = %d AND foldercontents_mode = 1;";
 
const char* upload_name =
    " 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;";
 
const char* upload_pk =
    " 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;";
 
const char* jobsql_email =
    " SELECT user_name, user_email, email_notify FROM users, upload "
    "   WHERE user_pk = user_fk "
    "     AND upload_pk = %d;";
 
const char* jobsql_email_job =
    " 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;";
 
/* job queue related sql */
const char* basic_checkout =
    " 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 jq.jq_pk NOT IN (%s)"
    "   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 %d;";
 
const char* jobsql_started =
    " UPDATE jobqueue "
    "   SET jq_starttime = now(), "
    "       jq_schedinfo ='%s.%d', "
    "       jq_endtext = 'Started' "
    "   WHERE jq_pk = %d;";
 
const char* jobsql_complete =
    " UPDATE jobqueue "
    "   SET jq_endtime = now(), "
    "       jq_end_bits = jq_end_bits | 1, "
    "       jq_schedinfo = null, "
    "       jq_endtext = 'Completed' "
    "   WHERE jq_pk = %d;";
 
const char* jobsql_restart =
    " 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;";
 
const char* jobsql_failed =
    " UPDATE jobqueue "
    "   SET jq_endtime = now(), "
    "       jq_end_bits = jq_end_bits | 2, "
    "       jq_schedinfo = null, "
    "       jq_endtext = '%s' "
    "   WHERE jq_pk = %d;";
 
const char* jobsql_fail_dependents =
    " WITH RECURSIVE deps(jq_pk, depth) AS ("
    "   SELECT jdep_jq_fk, 1 FROM jobdepends"
    "     WHERE jdep_jq_depends_fk = %d"
    "   UNION ALL"
    "   SELECT jd.jdep_jq_fk, deps.depth + 1 FROM jobdepends jd"
    "     INNER JOIN deps ON deps.jq_pk = jd.jdep_jq_depends_fk"
    "     WHERE deps.depth < 20"
    " )"
    " UPDATE jobqueue"
    "   SET jq_endtime = now(),"
    "       jq_end_bits = jq_end_bits | 2,"
    "       jq_schedinfo = null,"
    "       jq_endtext = 'dependency failed'"
    "   WHERE jq_pk IN (SELECT jq_pk FROM deps)"
    "     AND jq_endtime IS NULL;";
 
const char* jobsql_processed =
    " Update jobqueue "
    "   SET jq_itemsprocessed = %d "
    "   WHERE jq_pk = %d;";
 
const char* jobsql_paused =
    " 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;";
 
const char* jobsql_log =
    " UPDATE jobqueue "
    "   SET jq_log = '%s' "
    "   WHERE jq_pk = %d;";
 
const char* jobsql_priority =
    " UPDATE job "
    "   SET job_priority = %d "
    "   WHERE job_pk IN ( "
    "     SELECT jq_job_fk FROM jobqueue "
    "     WHERE jq_pk = %d);";
 
const char* jobsql_anyrunnable =
    " 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;";
 
const char* jobsql_jobendbits =
    " 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;";
 
const char* jobsql_resetqueue =
    "UPDATE jobqueue "
    "  SET jq_starttime=null, "
    "      jq_endtext=null, "
    "      jq_schedinfo=null "
    "  WHERE jq_endtime is NULL;";
 
const char* jobsql_jobinfo =
    " SELECT jq2.*"
    " FROM jobqueue jq1"
    " INNER JOIN jobqueue jq2 ON jq2.jq_job_fk = jq1.jq_job_fk"
    " WHERE jq1.jq_pk = %d;";
 
const char* smtp_values =
    " SELECT conf_value, variablename FROM sysconfig "
    "   WHERE variablename LIKE 'SMTP%';";
 
#endif /* SQLSTATEMENTS_H */