1 /* ************************************************************** 2 Copyright (C) 2012 Hewlett-Packard Development Company, L.P. 3 Copyright (C) 2015, 2018 Siemens AG 4 5 This program is free software; you can redistribute it and/or 6 modify it under the terms of the GNU General Public License 7 version 2 as published by the Free Software Foundation. 8 9 This program is distributed in the hope that it will be useful, 10 but WITHOUT ANY WARRANTY; without even the implied warranty of 11 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the 12 GNU General Public License for more details. 13 14 You should have received a copy of the GNU General Public License along 15 with this program; if not, write to the Free Software Foundation, Inc., 16 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA. 17 ************************************************************** */ 18 19 /** 20 * @file 21 * 22 * This file declares all the sql statements used in the scheduler. This should 23 * only be included once in the databace.c file. 24 */ 25 26 27 #ifndef SQLSTATEMENTS_H 28 #define SQLSTATEMENTS_H 29 30 /** 31 * Check column names for a given table 32 */ 33 const char* check_scheduler_tables = 34 " SELECT column_name FROM information_schema.columns " 35 " WHERE table_catalog = '%s' " 36 " AND table_schema = 'public' " 37 " AND table_name = "; 38 39 /** 40 * Get the FOSSologyURL from sysconfig table 41 */ 42 const char* url_checkout = 43 " SELECT conf_value FROM sysconfig " 44 " WHERE variablename = 'FOSSologyURL';"; 45 46 /** 47 * For a given job queue id, get the upload id 48 */ 49 const char* select_upload_fk = 50 " SELECT job_upload_fk FROM job, jobqueue " 51 " WHERE jq_job_fk = job_pk " 52 " AND jq_pk = %d;"; 53 54 /** 55 * For a given upload id, get job and job queue 56 */ 57 const char* upload_common = 58 " SELECT * FROM jobqueue " 59 " LEFT JOIN job ON jq_job_fk = job_pk" 60 " WHERE job.job_upload_fk = %d;"; 61 62 /** 63 * For a given job id, get the folder name and folder id 64 */ 65 const char* folder_name = 66 " SELECT folder_name, folder_pk FROM folder " 67 " LEFT JOIN foldercontents ON folder_pk = foldercontents.parent_fk " 68 " LEFT JOIN job ON child_id = job_upload_fk " 69 " LEFT JOIN jobqueue ON jq_job_fk = job_pk " 70 " WHERE jq_pk = %d;"; 71 72 /** 73 * For a given folder id, get the folder name and folder id of the immediate 74 * parent 75 */ 76 const char* parent_folder_name = 77 " SELECT folder_name, folder_pk FROM folder " 78 " INNER JOIN foldercontents ON folder_pk=foldercontents.parent_fk " 79 " WHERE child_id = %d AND foldercontents_mode = 1;"; 80 81 /** 82 * For a given job id, get the upload file name 83 */ 84 const char* upload_name = 85 " SELECT upload_filename FROM upload " 86 " LEFT JOIN job ON upload_pk = job_upload_fk " 87 " LEFT JOIN jobqueue ON jq_job_fk = job_pk " 88 " WHERE jq_pk = %d;"; 89 90 /** 91 * For a given job id, get the upload id and upload tree 92 */ 93 const char* upload_pk = 94 " SELECT upload_fk, uploadtree_pk FROM uploadtree " 95 " LEFT JOIN job ON upload_fk = job_upload_fk " 96 " LEFT JOIN jobqueue ON jq_job_fk = job_pk " 97 " WHERE parent IS NULL" 98 " AND jq_pk = %d;"; 99 100 /** 101 * For a given upload id, get the user's name, email and email preference 102 */ 103 const char* jobsql_email = 104 " SELECT user_name, user_email, email_notify FROM users, upload " 105 " WHERE user_pk = user_fk " 106 " AND upload_pk = %d;"; 107 108 /** 109 * For a given job id, get the user's name, email and email preference 110 */ 111 const char* jobsql_email_job = 112 " SELECT user_name, user_email, email_notify FROM users, job, jobqueue " 113 " WHERE user_pk = job_user_fk AND job_pk = jq_job_fk " 114 " AND jq_pk = %d;"; 115 116 /* job queue related sql */ 117 /** 118 * Get the jobs which are not yet queued by the scheduler 119 */ 120 const char* basic_checkout = 121 " SELECT jobqueue.* FROM jobqueue INNER JOIN job ON job_pk = jq_job_fk " 122 " WHERE jq_starttime IS NULL AND jq_end_bits < 2 " 123 " AND NOT EXISTS(SELECT * FROM jobdepends, jobqueue jdep " 124 " WHERE jdep_jq_fk=jobqueue.jq_pk " 125 " AND jdep_jq_depends_fk=jdep.jq_pk" 126 " AND NOT(jdep.jq_endtime IS NOT NULL AND jdep.jq_end_bits < 2)) " 127 " ORDER BY job_priority DESC " 128 " LIMIT 10;"; 129 130 /** 131 * For a given job id, get the job information 132 */ 133 const char* jobsql_information = 134 " SELECT user_pk, job_priority, job_group_fk as group_pk FROM users " 135 " LEFT JOIN job ON job_user_fk = user_pk " 136 " WHERE job_pk = '%s';"; 137 138 /** 139 * Mark the given job id as started 140 */ 141 const char* jobsql_started = 142 " UPDATE jobqueue " 143 " SET jq_starttime = now(), " 144 " jq_schedinfo ='%s.%d', " 145 " jq_endtext = 'Started' " 146 " WHERE jq_pk = '%d';"; 147 148 /** 149 * Mark the given job id as completed 150 */ 151 const char* jobsql_complete = 152 " UPDATE jobqueue " 153 " SET jq_endtime = now(), " 154 " jq_end_bits = jq_end_bits | 1, " 155 " jq_schedinfo = null, " 156 " jq_endtext = 'Completed' " 157 " WHERE jq_pk = '%d';"; 158 159 /** 160 * Mark the given job id as restarted 161 */ 162 const char* jobsql_restart = 163 " UPDATE jobqueue " 164 " SET jq_endtext = 'Restarted', " 165 " jq_starttime = ( CASE " 166 " WHEN jq_starttime = CAST('9999-12-31' AS timestamp with time zone) " 167 " THEN null " 168 " ELSE jq_starttime " 169 " END ) " 170 " WHERE jq_pk = '%d';"; 171 172 /** 173 * Mark the given job id as failed 174 */ 175 const char* jobsql_failed = 176 " UPDATE jobqueue " 177 " SET jq_endtime = now(), " 178 " jq_end_bits = jq_end_bits | 2, " 179 " jq_schedinfo = null, " 180 " jq_endtext = '%s' " 181 " WHERE jq_pk = '%d';"; 182 183 /** 184 * Update the items processed for the given job id 185 */ 186 const char* jobsql_processed = 187 " Update jobqueue " 188 " SET jq_itemsprocessed = %d " 189 " WHERE jq_pk = '%d';"; 190 191 /** 192 * Mark the given job id as paused 193 */ 194 const char* jobsql_paused = 195 " UPDATE jobqueue " 196 " SET jq_endtext = 'Paused', " 197 " jq_starttime = ( CASE " 198 " WHEN jq_starttime IS NULL " 199 " THEN CAST('9999-12-31' AS timestamp with time zone) " 200 " ELSE jq_starttime " 201 " END ) " 202 " WHERE jq_pk = '%d';"; 203 204 /** 205 * Get the log location for the given job id 206 */ 207 const char* jobsql_log = 208 " UPDATE jobqueue " 209 " SET jq_log = '%s' " 210 " WHERE jq_pk = '%d';"; 211 212 /** 213 * Change the priority of the given job id 214 */ 215 const char* jobsql_priority = 216 " UPDATE job " 217 " SET job_priority = '%d' " 218 " WHERE job_pk IN ( " 219 " SELECT jq_job_fk FROM jobqueue " 220 " WHERE jq_pk = '%d');"; 221 222 /** 223 * Get all jobs from job queue which are runnable for a given job id 224 */ 225 const char* jobsql_anyrunnable = 226 " SELECT * FROM jobqueue " 227 " WHERE jq_starttime IS NULL AND jq_end_bits < 2 " 228 " AND NOT EXISTS(SELECT * FROM jobdepends, jobqueue jdep " 229 " WHERE jdep_jq_fk=jobqueue.jq_pk " 230 " AND jdep_jq_depends_fk=jdep.jq_pk" 231 " AND NOT(jdep.jq_endtime IS NOT NULL AND jdep.jq_end_bits < 2))" 232 " AND jq_job_fk = (SELECT jq_job_fk FROM jobqueue queue WHERE queue.jq_pk = %d)"; 233 234 /** 235 * Get the job id and job end bits for the given job id 236 */ 237 const char* jobsql_jobendbits = 238 " SELECT jq_pk, jq_end_bits FROM jobqueue " 239 " WHERE jq_job_fk = ( " 240 " SELECT jq_job_fk FROM jobqueue " 241 " WHERE jq_pk = %d " 242 " );"; 243 244 /** 245 * Reset the job queue for jobs with end time as NULL 246 */ 247 const char* jobsql_resetqueue = 248 "UPDATE jobqueue " 249 " SET jq_starttime=null, " 250 " jq_endtext=null, " 251 " jq_schedinfo=null " 252 " WHERE jq_endtime is NULL;"; 253 254 /** 255 * Get the job information for a given job id 256 */ 257 const char* jobsql_jobinfo = 258 " SELECT * FROM jobqueue " 259 " WHERE jq_job_fk = ( " 260 " SELECT jq_job_fk FROM jobqueue " 261 " WHERE jq_pk = %d " 262 " );"; 263 264 /** 265 * Get the SMTP (email) values for the sysconfig table 266 */ 267 const char* smtp_values = 268 " SELECT conf_value, variablename FROM sysconfig " 269 " WHERE variablename LIKE 'SMTP%';"; 270 271 #endif /* SQLSTATEMENTS_H */ 272 273